A2-03-04.DDL-MySQL CREATE TABLE Statement By Examples
转载自：http://www.mysqltutorial.org/mysql-create-table/
MySQL CREATE TABLE Statement By Examples
Summary: in this tutorial, we will show you how to create a new table in a database by using the MySQL CREATE TABLE statement.
MySQL CREATE TABLE syntax
To create a new table within a database, you use the MySQL CREATE TABLE statement. The CREATE TABLE statement is one of the most complex statements in MySQL.
The following illustrates the syntax of the simplified version of the CREATE TABLE statement:
|
1
2
3
|
CREATE TABLE [IF NOT EXISTS] table_name(
column_list
) ENGINE=storage_engine
|
Let’s examine the syntax in greater detail.
First, you specify the name of the table that you want to create after the CREATE TABLE clause. The table name must be unique within a database. The IF NOT EXISTS is an optional clause that allows you to check if the table that you are creating already exists in the database. If this is the case, MySQL will ignore the whole statement and will not create any new table. It is highly recommended that you use IF NOT EXISTS in every CREATE TABLE statement to avoid an error of creating a new table that already exists.
Second, you specify a list of columns for the table in the column_list section, columns are separated by commas.
Third, you can optionally specify the storage engine for the table in the ENGINE clause. You can use any storage engine such as InnoDB and MyISAM. If you don’t explicitly declare the storage engine, MySQL will use InnoDB by default.
InnoDB became the default storage engine since MySQL version 5.5. The InnoDB storage engine brings many benefits of a relational database management system such as ACID transaction, referential integrity, and crash recovery. In the previous versions, MySQL used MyISAM as the default storage engine.
To define a column for the table in the CREATE TABLE statement, you use the following syntax:
|
1
|
column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
|
The most important components of the syntax above are:
- The
column_namespecifies the name of the column. Each column has a specific data type and maximum length e.g.,VARCHAR(255) - The
NOT NULLindicates that the column does not allowNULL. - The
DEFAULT valueis used to specify the default value of the column. - The
AUTO_INCREMENTindicates that the value of the column is generated by one automatically whenever a new row is inserted into the table. Each table has one and only oneAUTO_INCREMENTcolumn.
If you want to set a column or a set of columns as the primary key, you use the following syntax:
|
1
|
PRIMARY KEY (col1,col2,...)
|
MySQL CREATE TABLE statement example
The following statement creates a new table named tasks:
|
1
2
3
4
5
6
7
8
9
10
|
CREATE TABLE IF NOT EXISTS tasks (
task_id INT AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
start_date DATE,
due_date DATE,
status TINYINT NOT NULL,
priority TINYINT NOT NULL,
description TEXT,
PRIMARY KEY (task_id)
) ENGINE=INNODB;
|
The tasks table has the following columns:
- The
task_idis an auto-increment column. If you use theINSERTstatement to add a new row to the table without specifying a value for the task_id column, the task_id column will take an auto-generated integer beginning with one. Thetask_idis the primary key column. - The
titlecolumn is a variable character string column whose maximum length is 255. It means that you cannot insert a string whose length is greater than 255 into this column. TheNOT NULLindicates that the column must have a value. In other words, you have to provide a value when you insert or update this column. - The
start_dateanddue_dateare date columns which accept NULL. - The
statusandpriorityare theTINYINTcolumns which do not allow NULL. - The
descriptioncolumn is aTEXTcolumn that accepts NULL.
In this tutorial, you have learned how to use MySQL CREATE TABLE statement to create a new table in a database.
