Detailed explanation of several ways to create tables in SQL

introduction

Have you ever thought it was a bit difficult to create a database table? Do you think the word "SQL table creation" sounds very high-end, but it always stumblings when it comes to actual operation? Don’t worry, today we will explore the creation methods of SQL tables, which will make you easy to get started and be able to handle them in actual work!

1. Creation of basic tables

Creating a SQL (Structured Query Language) table is like building a house. First, you have to have a "blueprint" and then build the table structure based on the design blueprint. The basic table creation syntax is very simple. First, let’s review the most basic “CREATE TABLE” statement.

Example:
Suppose we want to create a table that stores "user information", and the table fields include: user_id, name, email, and birthday.

CREATE TABLE users (
    user_id INT PRIMARY KEY,         -- userID，Primary key
    name VARCHAR(100) NOT NULL,       -- user名字，Can't be empty
    email VARCHAR(100),               -- Email address
    birthdate DATE                    -- Date of birth
);

Analysis:

CREATE TABLE users: This part is the command to create a table, and users are the table name.
user_id INT PRIMARY KEY: Define user_id as an integer type and is the primary key (the primary key guarantees uniqueness).
name VARCHAR(100) NOT NULL: Defines a variable character type with a name of up to 100 characters and cannot be empty.
birthdate DATE: Defines the date of birth to the DATE type.

In SQL, CREATE TABLE is the keyword that creates a table, and the type of the field can be adjusted according to the needs. Common field types are: INT (integer), VARCHAR (n) (variable length string, up to n characters), DATE (date), etc.

2. Table creation with constraints

In addition to basic field definitions, table design often requires some "rules" to ensure the validity and consistency of data. For example, we need to limit the uniqueness of a certain field, or require that a certain field cannot be empty.

Common constraints:

NOT NULL: NULL value is not allowed
UNIQUE: Guaranteed fields unique
CHECK: Used to set the range limit of fields
DEFAULT: Set default values for fields
FOREIGN KEY: Foreign key constraints to ensure data integrity
PRIMARY KEY: Primary key, automatically unique and non-empty

Example: Create a table with constraints
Suppose we want to create a table that stores "order information", including fields: order_id, user_id, amount (order amount), and require the order amount to be greater than zero, user_id is a foreign key, referring to user_id in the users table.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,         -- OrderID，Primary key
    user_id INT,                      -- userID
    amount DECIMAL(10, 2) CHECK(amount &amp;gt; 0), -- Order金额，And greater than zero
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Default current time
    FOREIGN KEY (user_id) REFERENCES users(user_id) -- Foreign key constraints
);

Analysis:

CHECK(amount > 0): Make sure amount is greater than zero.
DEFAULT CURRENT_TIMESTAMP: If order_date is not specified, the current time is defaulted.
FOREIGN KEY (user_id) REFERENCES users(user_id):
Associate the user_id field in the orders table with the user_id in the users table to ensure that the user for each order exists.

3. Default value and self-increment field of the table

When creating tables, we often need to set default values or self-increment fields for certain fields, especially for primary keys, and the automatically generated ID is very useful.

Self-increasing field

For example, we want the user_id field to automatically increase each time new data is inserted, without having to enter it manually every time.

Example:

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,   -- Self-increasing field
    name VARCHAR(100) NOT NULL,                   -- Product Name
    price DECIMAL(10, 2) DEFAULT 0.00             -- The default price is0
);

Analysis:

AUTO_INCREMENT: Let the product_id field automatically increment when data is inserted.
DEFAULT 0.00: If no price is specified, the default setting is 0.00.

Note: Different database systems may have different autoincrement syntax, such as MySQL uses AUTO_INCREMENT, while SQL Server uses IDENTITY.

4. Creation and application of partition tables

When the amount of data is particularly large (such as tens of millions of data), ordinary tables may become inefficient. At this time, the partition table comes in handy. A partition table can divide data into sections according to some rule, each partition storing a portion of the data. This can improve query efficiency and reduce the pressure on a single table.

The basic principles of partition tables

The partition table distributes data to different storage locations based on a certain field (such as date, range, etc.). Common partitioning methods are:

Range Partitioning: distinguishes data based on the value of a certain field.
List Partitioning: Separate data according to the specific value of a field.
Hash Partitioning: Assign data to different partitions according to the hash value of the field.

Example: Create an order table partitioned by date range

Suppose we want to create an order table partitioned by year (the annual orders are in a separate partition).

CREATE TABLE orders_partitioned (
    order_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    order_date DATE
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);

Analysis:

PARTITION BY RANGE (YEAR(order_date)): Partition the table according to the year of the order_date field.
PARTITION p2020 VALUES LESS THAN (2021):
Save all data with order_date years less than 2021 into the p2020 partition.
And so on, partitions of different years are created.

In this way, we can make querying order data for a specific year more efficient, because each query only accesses partitions for the corresponding year.

5. Summary: You are already a master in table creation!

congratulations! Through the study of this article, you have mastered several common methods of SQL table creation. Whether it is the creation of basic tables, the tables with constraints, default values and self-increment fields, or even more complex partition table creation, you can easily control them.

Remember, database design is a process of continuous optimization. Whenever you face a larger amount of data, the partition table will become your best friend; and when you need to ensure data integrity, constraints and foreign key constraints will help you lock everything.

This is the end of this article about several methods of creating SQL tables. For more related contents of SQL table creation, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!