SoFunction
Updated on 2025-03-03

Detailed explanation of SQL table creation statement usage

Detailed explanation of SQL table creation statements

In SQL, creating tables (Tables) is the basis of database design. Tables are the basic units for storing data, and each table consists of rows and columns.

The process of creating a table involves defining the structure of the table, including column names, data types, constraints, etc.

This article will introduce the table creation statements in SQL in detail and help readers better understand through examples.

1. Basic syntax

The basic syntax for creating a table is as follows:

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ...
);
  • table_name: The name of the table.
  • column1, column2, column3: The name of the column.
  • datatype: The data type of the column.
  • constraint: Constraints for columns.

2. Data Type

SQL supports a variety of data types, common ones include:

  • Integer Type：INT, BIGINT, SMALLINT, TINYINT
  • Floating point number type：FLOAT, DOUBLE, DECIMAL(p, s)
  • String type：CHAR(n), VARCHAR(n), TEXT
  • Date and time type：DATE, TIME, DATETIME, TIMESTAMP
  • Boolean type：BOOLEAN

3. Constraints

Constraints are used to ensure the integrity and consistency of data. Common constraints include:

  • Primary key constraints：PRIMARY KEY, uniquely identifies each row in the table.
  • Unique constraints：UNIQUE, make sure all values ​​in the column are unique.
  • Non-empty constraints：NOT NULL, make sure that the values ​​in the column cannot be empty.
  • Default value constraints：DEFAULT, provide default values ​​for columns.
  • Foreign key constraints：FOREIGN KEY, make sure that the data in one table is consistent with the data in another table.
  • Check constraints：CHECK, make sure that the values ​​in the column meet specific conditions.

4. Example

The following is a specific example to demonstrate how to create a table.

Suppose we want to create a name calledstudentsThe table is used to store student information, including student number, name, gender, date of birth and class.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    birth_date DATE,
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

In this example:

  • student_idis the primary key, uniquely identifying each student.
  • nameIt is the student's name and cannot be empty.
  • genderIt is the gender of the student, it can only be ‘M’ or ‘F’.
  • birth_dateIt is the date of birth of the student.
  • class_idIt is the class where the student is located, it is a foreign key, quoteclassesThe tableclass_id。

5. Create a table with default values

Sometimes we need to set default values ​​for certain columns, such as the student's enrollment date.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    birth_date DATE,
    class_id INT,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

In this example,enrollment_dateThe default value of the column is the current date.

6. Create a table with a composite primary key

Sometimes we need to use multiple columns as primary keys, such as order number and product number in the order table.

CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

In this example,order_idandproduct_idTogether, make up the primary key.

7. Create a table with check constraints

Check constraints are used to ensure that values ​​in columns meet specific conditions, such as the age of a student must be between 18 and 30 years old.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    birth_date DATE,
    class_id INT,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    age INT CHECK (age >= 18 AND age <= 30),
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

In this example,ageThe column's value must be between 18 and 30 years old.

8. Create a table with unique constraints

Unique constraints are used to ensure that all values ​​in the column are unique, such as the student's email address.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    birth_date DATE,
    class_id INT,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    email VARCHAR(100) UNIQUE,
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

In this example,emailThe values ​​of the column must be unique.

9. Create a table with self-incremented columns

The self-increasing column is used to automatically generate unique identifiers, such as the student's student number.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    birth_date DATE,
    class_id INT,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

In this example,student_idColumns are self-increasing columns, and a unique student number will be automatically generated every time a new record is inserted.

10. Create a table with comments

Comments are used to provide instructions for tables and columns for easy understanding and maintenance.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'The unique identification of the student',
    name VARCHAR(50) NOT NULL COMMENT 'The student's name',
    gender CHAR(1) CHECK (gender IN ('M', 'F')) COMMENT 'Gender of the Student',
    birth_date DATE COMMENT 'Student's date of birth',
    class_id INT COMMENT 'The class where the student is located',
    enrollment_date DATE DEFAULT CURRENT_DATE COMMENT 'Student's admission date',
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
) COMMENT='Student Information Table';

In this example, comments are added to the table and columns to understand their meaning.

Summarize

Through the explanation of this article, we introduce in detail the table building statements in SQL, including basic syntax, data types, constraints, examples, etc.

I hope readers can better understand and master the use of SQL table building statements through these contents.

In practical applications, selecting the appropriate data type and constraints according to specific needs can effectively ensure the integrity and consistency of the data.