MySQL - creating and deleting a database

Following this MySQL series will see us creating a database called 'pickled1', adding tables, columns, rows (data) and specifying MySQL users for the 'pickled1' database.

We can start by deleting any redundant databases and creating our new 'pickled1' database.

Log into MySQL:

mysql -u root -p

We know what databases are installed by default (see listing databases) and discovered that some OS's have a database called 'test' installed by default.

Drop

Let's delete the 'test' database straight away:

DROP DATABASE test;

That was pretty simple. The entire 'test' database has gone.

Have a look:

SHOW databases;

Create

Let's go ahead and create a new database called 'pickled1'.

CREATE DATABASE pickled1;

Have a look:

SHOW databases;

Shortcuts

You know what? I still find that too much work. You have to log into MySQL and then create the database and then logout. Seems like a long way around.

Luckily there is a shortcut you can use when creating databases (you know, I think the plural should databi, like ocutpi...).

Anyway, to create the same database with just one line would be:

mysqladmin -u root -p create pickled1

Enter your password (if you have one) and that's it. The 'pickled1' database has been created. Now that's more like it.

What about dropping databases with one line?

OK. Let's do this:

mysqladmin -u root -p drop pickled1

Again, it'll ask for a password and is then kind enough to ask if we really want to drop the database. It has a point - it is potentially a very bad thing to do.

Database "pickled1" dropped

Nice.

Naming Conventions

There are some rules you have to follow when creating a MySQL databases but they are pretty simple:

The database name must be no longer than 64 bytes.

Note this is not characters but bytes as some characters can contain more than one byte. Don't be too concerned about this - just don't use ridiculously long names.

Secondly, the name must not contain:

• forward slash (/)
• back slash ()
• periods (.)

That's it. As before, I am sure you can see the advantage of using the command line rather than a MySQL gui for these sorts of queries.

The next article discusses creating, renaming and deleting tables within the new database.

PickledOnion.

Digg it | del.icio.us | reddit | StumbleUpon