What are the database table creation statements?

Database table creation statements include CREATE TABLE statement, PRIMARY KEY constraint, UNIQUE constraint, FOREIGN KEY constraint, NOT NULL constraint, CHECK constraint, DEFAULT constraint, etc. Detailed introduction: 1. CREATE TABLE statement, used to create a new database table; 2. PRIMARY KEY constraint, used to define a primary key column to ensure that each row of data has a unique identity; 3. UNIQUE constraint, used to ensure that a certain The values ​​in the column are unique etc.

#The operating environment of this article: Windows 10 system, MySQL 8 version, Dell G3 computer.

The database table creation statement is a SQL statement used to create a database table. The following are some common database table creation statements:

CREATE TABLE statement: is used to create a new database table. The syntax is as follows:

CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
…
);

For example:

CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);

This statement creates a table named students, containing three columns: id, name, and age.

PRIMARY KEY constraint: is used to define a primary key column to ensure that each row of data has a unique identity. The syntax is as follows:

column_name datatype PRIMARY KEY

For example:

id INT PRIMARY KEY

This statement defines the id column as the primary key column.

UNIQUE constraint: Used to ensure that the values ​​in a column are unique. The syntax is as follows:

column_name datatype UNIQUE

For example:

email VARCHAR(50) UNIQUE

This statement defines the email column as unique.

FOREIGN KEY constraints: Used to define associations with other tables. The syntax is as follows:

column_name datatype REFERENCES table_name(column_name)

For example:

student_id INT REFERENCES students(id)

This statement defines the student_id column as a foreign key and associates it with the id column of the students table.

NOT NULL constraint: Used to ensure that the value in a column cannot be empty. The syntax is as follows:

column_name datatype NOT NULL

For example:

name VARCHAR(50) NOT NULL

This statement defines the name column as not being empty.

CHECK constraints: Used to define conditional restrictions on column values. The syntax is as follows:

column_name datatype CHECK (condition)

For example:

age INT CHECK (age >= 0)

This statement defines the age column as must be greater than or equal to 0.

DEFAULT constraint: Used to set default values ​​for columns. The syntax is as follows:

column_name datatype DEFAULT default_value

For example:

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

This statement defines the created_at column as the default value is the current timestamp.

These are common database table creation statements, and different constraints can be used to define the table structure according to specific needs.

The above is the detailed content of What are the database table creation statements?. For more information, please follow other related articles on the PHP Chinese website!