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.
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
name, email, password
These columns have
varchar type which mean they hold string values.
(100) is a limit of those strings length.
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`