Table

Now, when we have a database, it's time to create our first table:

create table `user`
(
    `id` int auto_increment primary key,
    `name` varchar(100),
    `email` varchar(100),
    `password` varchar(100),
    `registered` datetime
);

Query OK, 0 rows affected (0.64 sec)

There are bunch of new things. Let's go one by one.

To create a table you should use create table statement. In our case user is a table name. We will store our website's user information in this table.

A table consists of a set of columns. Each column has a name. Values stored in the same column have same type. In the example above we've created a table with 5 columns.

First you write a column name, then its type, and after that some optional properties.

id

This column has int type which mean it holds integer values. auto_increment means that the field is a sort of counter. It will increment automatically when we insert new records into the table. primary key is, well, a primary key. We will discuss keys and indices in details some later. As for now — just think of it as a primary unique identifier of a record. By the way: only primary key column can have auto_increment property.

name, email, password

These columns have varchar type which mean they hold string values. (100) is a limit of those strings length.

registered

This column has datetime type and that mean we're going to keep date and time there.

You can also use if not exists option when creating a table:

create table if not exists `user`
(
    ...
)

To drop a table you can use drop table statement:

drop table `user`

if exists option is also applicable:

drop table if exists `user`