MySQL - creating and deleting tables

Once you have your database created you will want some tables in there.

There are some application frameworks, such as Ruby on Rails, where all you have to do is create the database: the rest is done within the framework itself. However, it is always a good idea to know how to create tables.

So now we have the database 'pickled1' created (see creating/deleting databases), we need to add a couple of tables.

So, log into MySQL:

mysql -u root -p

Use

First of all, we need to tell MySQL which database we are going to be working with. If we just told it to create some tables, it would not know which database to add them to:

use pickled1;

Create

Our first table is very simple and is called 'friends'. All this table will do is hold a unique ID for each friend and their email address (Let's not worry about how to enter the friend's details - at least not yet anyway).

CREATE TABLE `friends` (
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   email VARCHAR(45)
  );

OK. So what happened there?

The first column (id) was defined as an integer (INT), it must have a value (NOT NULL) and is the PRIMARY KEY (this uniquely identifies the row) and will be incremented automatically.

The second column will contain an email address and we have limited the text input to 45 characters.

Show

Let's take a look and see if the table was created:

SHOW tables;

and to show the columns:

SHOW columns FROM friends;

The output is nicely formatted and shows all the details we just entered.

Rename

Naturally, we want to be able to manipulate the table and we've now decided that the table name is a bit misleading as the list will include people from work.

So let's rename it 'colleagues':

RENAME TABLE friends TO colleagues;

That's better.

Drop

Should you decide you want to delete the table entirely, it's just a case of 'dropping' the table:

DROP TABLE colleagues;

I'm sure you are beginning to see that the syntax is fairly logical - show, rename, drop and so on.

That's it. In the next article we will start to add records (data) to our table, manipulate them, delete them and so on.

PickledOnion.

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