MySQL – CREATE TABLE examples

A very basic CREATE TABLE statement which should work in any SQL database:

mysql> CREATE TABLE table1 (
         id INT,
         data VARCHAR(100)
       );
Query OK, 0 rows affected (0.02 sec)

MySQL provides a variety of different table types with differing levels of functionality. The usual default, and most widely used, is MyISAM. Other storage types must be explicitly defined:

mysql>  CREATE TABLE table1_innodb (
          id INT,
          data VARCHAR(100)
        ) TYPE=innodb;
Query OK, 0 rows affected (0.03 sec)

Note that beginning with MySQL 4.1 ENGINE=innodb is the preferred method of defining the storage type.

Often you’ll want to be able to automatically assign a sequential value to a column:

mysql> CREATE TABLE table2_autoincrement (
         id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         data VARCHAR(100)
       );
Query OK, 0 rows affected (0.01 sec)

Inserting data into table2_autoincrement,

mysql> INSERT INTO table2_autoincrement (data)
    ->      VALUES (‘Linux Articles’);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM  table2_autoincrement;
+—-+———————+
| id  | data               |
+—-+——————–+
|  1  | Linux Articles |
+—-+——————–+
1 row in set (0.01 sec)