Create a table

Tables are subcontainers within a database that store the actual data. The database is created without any tables. To create a table, the following command is used:

CREATE TABLE name(
 column_list
 ) ENGINE name

Here is the explanation of the syntax:

name – specifies the name of the table.
column_list – specifies the list and data types of columns, separated by commas.
ENGINE name – specifies the type of database engine to use for this table. If you don’t specify this parameter explicitly, MySQL will use InnoDB by default.

Here is an example. Let’s say we want to specify a table called testtable with three colums – name, surname, and birth year. We can use the following command:

mysql> USE testdb;
Database changed
mysql> CREATE TABLE testtable (name VARCHAR(128), surname VARCHAR(128), year CHAR(4)) ENGINE MyISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW tables;
+------------------+
| Tables_in_testdb |
+------------------+
| testtable |
+------------------+
1 row in set (0.00 sec)

To check how our new table looks like, we can use the following command:

mysql> DESCRIBE testtable;

+---------+--------------+------+-----+---------+-------+

| Field   | Type         | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| name    | varchar(128) | YES  |     | NULL    |       |

| surname | varchar(128) | YES  |     | NULL    |       |

| year    | char(4)      | YES  |     | NULL    |       |

+---------+--------------+------+-----+---------+-------+

3 rows in set (0.03 sec)

Note how each column has a specific data type and the size (e.g.,VARCHAR(255)). This data type is very useful, as it serves a guideline for MySQL to understand what type of data is expected inside of each column, and it also identifies how MySQL interacts with the stored data.. For example, the column year has predictable values, so instead of VARCHAR we have used the more efficient CHAR(4) data type. This parameter of 4 allows for 4 bytes of data, supporting all years from –999 to 9999.

Create a table

MySQL Course