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`