MySQL Create Table Tutorial and Examples

A table is the basic unit of data storage in a relational database. In MySQL, CREATE TABLE statement is used to create tables.

CREATE TABLE syntax

We use the CREATE TABLE statement to create a new table in the database. The syntax of the CREATE TABLE statement is as follows:

CREATE TABLE [IF NOT EXISTS] table_name (
   column_name data_type [NOT NULL | NULL] [DEFAULT expr],
   column_name data_type [NOT NULL | NULL] [DEFAULT expr],
   ...,
   [table_constraints]
) [ENGINE=storage_engine];

Here:

The CREATE TABLE statement creates a table named table_name.
- The table names can consist of letters, numbers, underscores, and dollar signs, and column names can be up to 64 characters long.
- The table names must be unique within a database.
- The newly created table will be in the current default database. If you haven’t already selected a database, use the db_name.table_name format to specify the database where the new table is located.
The IF NOT EXISTS indicates to create only if the given table does not exist. It is optional.

If you give an already existing table name without using the IF NOT EXISTSclause, MySQL server will return an error.
The column_name data_type [NOT NULL | NULL] [DEFAULT expr] [AUTO_INCREMENT] defines a column. Multiple columns are separated by commas.
- The column_name is the name of the column. The column name can consist of letters, numbers, underscores, and dollar signs, and can be up to 64 characters long. The column name must be unique within a table.
- The data_type Is the data type of the column, which can be CHAR, VARCHAR, INT, DATE, DATETIME, BIT, TEXT, ENUM, JSON, BOLB, etc.
- The [NOT NULL | NULL] indicates whether the column can be NULL. It is optional. If this option is not specified, this column can be NULL. If set to NOT NULL, the column must have a value when inserting a new row.
- The [DEFAULT expr] indicates the default value for this column. It is optional. If this option is not specified, the default for this column is NULL.
- The [AUTO_INCREMENT] indicates whether the column is an auto-incrementing column. If this option is used, the value of this column can be automatically generated and populated by the server. The value of this column starts with 1 and is incremented for each additional 1. There can only be one auto-incrementing column in a table.
The [table_constraints] after the column definition, it defines the constraints of the table. It is optional. Table constraints include primary key, foreign key, CHECK, UNIQUE, etc.
The ENGINE=storage_engine clause specifies the storage engine used by the table. It is optional.

If this option is not specified, the server’s default storage engine is used. Since MySQL version 5.5, the server’s default engine has changed from MyISAM to InnoDB.
If the table name or field name contains spaces or other special characters, please enclose it with `. For example: `test 1`.

MySQL CREATE TABLE Examples

The following example will create two tables user and user_hobby in the testdb database. Please follow the steps below.

Log in to the MySQL database as the root user:
```
mysql -u root -p
```
Enter the password of the root user.

Note: You can also log in as any other user with appropriate database privileges.
Select the testdb database using the following statement:
```
USE testdb;
```
If you haven’t created the database yet, run the following statement first:
```
CREATE DATABASE testdb;
```
Create the user table:
```
CREATE TABLE `user` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `age` INT NULL,
  `locked` TINYINT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`user_id`));
```
If you are not working with the select database in the Step 2, replace the table name with `testdb`.`user`.

The user table has 5 columns:
- The data type of the user_id column is INT, it cannot be NULL, and it is an auto-incrementing column.
- The data type of the name column is VARCHAR that it can be up to 45 characters. It can’t be NULL.
- The data type of the age column is INT. It can be NULL.
- The data type of the locked column is TINYINT. It can’t be NULL, but it has default values 0.
- The data type of the created_at column is DATETIME. It can’t be NULL.
The constraints of the user table include:
- PRIMARY KEY (`user_id`) clause indicating that user_id the column is the primary key.
Create the user_hobby table:
```
CREATE TABLE `testdb`.`user_hobby` (
  `hobby_id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `hobby` VARCHAR(45) NOT NULL,
  `created_at` DATETIME NOT NULL,
  INDEX `fk_user_idx` (`user_id` ASC) VISIBLE,
  PRIMARY KEY (`hobby_id`),
  CONSTRAINT `fk_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `testdb`.`user` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE RESTRICT);
```
The user_hobby table has 4 columns:
- The data type of the hobby_id column is INT, it cannot be NULL, and it is an auto-incrementing column.
- The data type of the user_id column is INT. It can’t be NULL. It points to the user_id column of the user table.
- The data type of the hobby column is VARCHAR that it can be up to 45 characters. It can’t be NULL.
- The data type of the created_at column is DATETIME. It can’t be NULL.
The constraints of the user_hobby table are:
- PRIMARY KEY (`hobby_id`) clause indicates that hobby_id the column is the primary key.
- INDEX `fk_user_idx` clause indicates that there is a index for the user_id column.
- CONSTRAINT `fk_user` A foreign key is set. This foreign user_id key references the user_id column to the user table.

After we have created user and user_hobby, we can insert rows into the tables. Please use the following statement:

INSERT INTO user (name, age, created_at) VALUES ('Jim', 18, NOW());

INSERT INTO user_hobby (user_id, hobby, created_at) VALUES (1, 'Football', NOW());
INSERT INTO user_hobby (user_id, hobby, created_at) VALUES (1, 'Swimming', NOW());

Create a table using the `CREATE TABLE … LIKE` statement

The CREATE TABLE ... LIKE statement can be used to clone the definition of another table. It creates a new empty table based on the definition of another table, containing the column attributes and indexes defined in the original table. The syntax of the CREATE TABLE ... LIKE statement is as follows:

CREATE TABLE new_table LIKE original_table;

The CREATE TABLE ... LIKE statement creates an empty table.

Create a table using the `CREATE TABLE … SELECT` statement

You can use the CREATE TABLE ... SELECT statement to create a new table from another table. This statement creates a new table with the columns in the SELECT clause and inserts the result set of the SELECT into the new table. The syntax of the CREATE TABLE ... SELECT statement is as follows:

CREATE TABLE new_table [AS] SELECT * FROM original_table;

The CREATE TABLE ... SELECT statement can be used to copy a table, containing column attributes and data.

Conclusion

In this article, we discussed using the CREATE TABLE statement. The main points of this article are as follows:

The CREATE TABLE keyword is followed by the name of the table to create. If no database is selected, the table name needs to be specified as db_name.table_name.
A table can always contain multiple columns, separated by commas.
The NOT NULL | NULL indicates whether the column can be NULL.
The AUTO_INCREMENT indicates whether the column is an auto-incrementing column.
The DEFAULT clause sets a default value for the column.
The PRIMARY KEY clause sets one or more columns as primary keys.
The FOREIGN KEY clause sets one or more columns as foreign keys.

In addition to creating tables, we can also modify existing tables and delete tables.

CREATE TABLE syntax

MySQL CREATE TABLE Examples

Create a table using the CREATE TABLE … LIKE statement

Create a table using the CREATE TABLE … SELECT statement

Conclusion

On this page

Create a table using the `CREATE TABLE … LIKE` statement

Create a table using the `CREATE TABLE … SELECT` statement