Câu lệnh CREATE TABLE trong MySQL

Câu lệnh CREATE TABLE trong MySQL được sử dụng để tạo bảng mới trong cơ sở dữ liệu. Việc hiểu rõ cú pháp và cách sử dụng câu lệnh này rất quan trọng khi bạn muốn thiết kế cơ sở dữ liệu. Chúng ta sẽ bắt đầu từ ví dụ cơ bản và dần đến các tình huống nâng cao hơn.

1. Cú pháp cơ bản của câu lệnh CREATE TABLE

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

Trong đó:

• table_name: Tên của bảng.
• column1, column2: Tên các cột trong bảng.
• datatype: Kiểu dữ liệu của cột (như INT, VARCHAR, DATE, v.v.).
• constraints: Các ràng buộc như PRIMARY KEY, NOT NULL, UNIQUE, v.v.

Ví dụ cơ bản:

Tạo bảng employees với các cột id, name, age, và salary:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    salary DECIMAL(10, 2)
);

• id: Cột kiểu INT, là khóa chính (PRIMARY KEY), và tự động tăng giá trị (AUTO_INCREMENT).
• name: Cột kiểu VARCHAR với độ dài tối đa 100 ký tự, không cho phép giá trị NULL.
• age: Cột kiểu INT, lưu trữ độ tuổi của nhân viên.
• salary: Cột kiểu DECIMAL lưu trữ lương của nhân viên với tối đa 10 chữ số, trong đó có 2 chữ số thập phân.

2. Thêm các ràng buộc (Constraints)

a. Khóa chính (PRIMARY KEY)

Khóa chính là một ràng buộc giúp xác định duy nhất mỗi bản ghi trong bảng.

CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(100) NOT NULL
);

Trong ví dụ trên, id là khóa chính, mỗi phòng ban sẽ có một mã số duy nhất.

b. Khóa ngoại (FOREIGN KEY)

Khóa ngoại liên kết hai bảng với nhau, giúp duy trì tính toàn vẹn dữ liệu.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

Cột department_id trong bảng employees là khóa ngoại, liên kết với cột id của bảng departments. Điều này đảm bảo rằng mỗi nhân viên thuộc một phòng ban có tồn tại trong bảng departments.

c. Ràng buộc NOT NULL

Ràng buộc NOT NULL không cho phép giá trị rỗng trong một cột.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL
);

Trong bảng này, cột name và age không được phép để trống.

d. Ràng buộc UNIQUE

Ràng buộc UNIQUE đảm bảo rằng tất cả các giá trị trong cột đó là duy nhất.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE
);

Cột email chỉ có thể chứa các giá trị không trùng lặp.

e. Ràng buộc DEFAULT

Ràng buộc DEFAULT thiết lập giá trị mặc định cho một cột khi không có giá trị được cung cấp.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age INT DEFAULT 30
);

Nếu không nhập tuổi cho nhân viên khi thêm dữ liệu, giá trị mặc định sẽ là 30.

3. Các kiểu dữ liệu trong MySQL

Trong MySQL, có nhiều loại kiểu dữ liệu mà bạn có thể sử dụng cho các cột trong bảng:

• Kiểu số: INT, DECIMAL, FLOAT, DOUBLE, BIGINT, v.v.
• Kiểu chuỗi: VARCHAR, CHAR, TEXT, BLOB, v.v.
• Kiểu ngày tháng: DATE, DATETIME, TIMESTAMP, TIME, YEAR.

Ví dụ sử dụng kiểu dữ liệu:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(10, 2),
    hire_date DATE
);

• salary: Kiểu DECIMAL(10, 2) lưu trữ các số có tối đa 10 chữ số, trong đó có 2 chữ số thập phân.
• hire_date: Kiểu DATE lưu trữ ngày.

4. Tạo bảng với chỉ mục (INDEX)

Chỉ mục (INDEX) giúp tăng tốc độ truy vấn dữ liệu, đặc biệt với các bảng lớn.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    salary DECIMAL(10, 2),
    INDEX (name)
);

Trong bảng này, cột name được tạo chỉ mục, giúp cải thiện hiệu năng truy vấn trên cột này.

5. Tạo bảng với bảng TEMPORARY

Bảng tạm thời (TEMPORARY) chỉ tồn tại trong phiên làm việc hiện tại. Khi phiên làm việc kết thúc hoặc bảng bị xóa, nó sẽ không còn tồn tại.

CREATE TEMPORARY TABLE temp_employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

Bảng này sẽ bị xóa tự động khi phiên làm việc kết thúc hoặc người dùng thoát khỏi cơ sở dữ liệu.

6. Tạo bảng với phân vùng (PARTITION)

Phân vùng giúp chia nhỏ dữ liệu trong bảng để quản lý và truy vấn hiệu quả hơn.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
)
PARTITION BY RANGE (salary) (
    PARTITION p0 VALUES LESS THAN (3000),
    PARTITION p1 VALUES LESS THAN (6000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

Trong bảng này, dữ liệu được phân chia theo khoảng lương (salary), giúp tối ưu hóa việc truy vấn trên dữ liệu lớn.

7. Tạo bảng với mối quan hệ một-nhiều

Ví dụ tạo hai bảng categories và products với mối quan hệ một-nhiều (một danh mục có thể chứa nhiều sản phẩm):

CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

Bảng products chứa khóa ngoại category_id để liên kết với bảng categories, tạo mối quan hệ một danh mục nhiều sản phẩm.

8. Tạo bảng với AUTO_INCREMENT bắt đầu từ giá trị tùy chỉnh

Bạn có thể chỉ định giá trị bắt đầu cho cột AUTO_INCREMENT.

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INT
) AUTO_INCREMENT = 1000;

Trong bảng orders, cột order_id sẽ bắt đầu từ giá trị 1000 thay vì 1.

9. Tạo bảng kết hợp với ràng buộc CHECK

Ràng buộc CHECK giới hạn các giá trị mà cột có thể nhận.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age INT CHECK (age >= 18)
);

Ràng buộc CHECK trong bảng employees đảm bảo rằng giá trị của age phải lớn hơn hoặc bằng 18.

10. Sử dụng IF NOT EXISTS để tránh lỗi khi bảng đã tồn tại

Khi tạo bảng, bạn có thể sử dụng mệnh đề IF NOT EXISTS để tránh lỗi nếu bảng đã tồn tại.

CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

Nếu bảng employees đã tồn tại, câu lệnh này sẽ không gây ra lỗi.

Kết luận:

Câu lệnh CREATE TABLE trong MySQL là nền tảng của việc thiết kế và tạo cơ sở dữ liệu. Việc hiểu rõ cú pháp và sử dụng các ràng buộc như PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK sẽ giúp bạn thiết kế cơ sở dữ liệu chặt chẽ và tối ưu hóa hiệu năng truy vấn. Hơn nữa, việc sử dụng các tính năng nâng cao như phân vùng (PARTITION), chỉ mục (INDEX), và bảng tạm thời (TEMPORARY) giúp bạn quản lý dữ liệu hiệu quả hơn trong các tình huống phức tạp.

Các ví dụ nâng cao khác về CREATE TABLE

Dưới đây là một số ví dụ nâng cao về câu lệnh CREATE TABLE trong MySQL, tập trung vào các tình huống phức tạp hơn để bạn có thể ứng dụng trong các dự án thực tế hoặc khi làm việc với cơ sở dữ liệu lớn.

1. Tạo bảng với phân vùng theo HASH

Phân vùng bảng theo HASH giúp phân chia dữ liệu theo các nhóm giá trị hash của cột. Điều này đặc biệt hữu ích khi dữ liệu không có tính tuần tự rõ ràng.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    department_id INT
)
PARTITION BY HASH(department_id)
PARTITIONS 4;

Bảng này sẽ được chia thành 4 phân vùng dựa trên giá trị hash của cột department_id. Khi bạn thêm một bản ghi mới, MySQL sẽ tính toán giá trị hash của department_id và tự động phân loại vào một trong các phân vùng.

2. Tạo bảng với phân vùng theo LIST

Phân vùng theo LIST giúp chia bảng dựa trên danh sách các giá trị cụ thể.

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE,
    status VARCHAR(50)
)
PARTITION BY LIST COLUMNS(status) (
    PARTITION p_pending VALUES IN ('Pending', 'Awaiting Payment'),
    PARTITION p_processed VALUES IN ('Processed', 'Shipped'),
    PARTITION p_completed VALUES IN ('Completed', 'Delivered')
);

Bảng orders được phân chia theo trạng thái đơn hàng (status). Các đơn hàng có trạng thái Pending và Awaiting Payment sẽ được lưu vào phân vùng p_pending, tương tự cho các phân vùng còn lại.

3. Tạo bảng với ràng buộc nhiều khóa ngoại (FOREIGN KEY)

Trong các ứng dụng lớn, bảng có thể liên kết với nhiều bảng khác thông qua nhiều khóa ngoại.

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

Bảng order_items liên kết với bảng orders và products thông qua các cột order_id và product_id. Điều này tạo ra một mối quan hệ nhiều-nhiều giữa các đơn hàng và sản phẩm, cho phép một đơn hàng có nhiều sản phẩm và một sản phẩm xuất hiện trong nhiều đơn hàng.

4. Tạo bảng với các kiểu dữ liệu JSON

MySQL hỗ trợ kiểu dữ liệu JSON, cho phép lưu trữ các đối tượng JSON phức tạp trực tiếp trong cột của bảng.

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    attributes JSON
);

Trong bảng này, cột attributes có thể lưu trữ các thông tin đặc biệt về sản phẩm dưới dạng JSON, ví dụ như kích thước, màu sắc, và các thuộc tính khác. Ví dụ:

INSERT INTO products (name, attributes) 
VALUES ('Laptop', '{"brand": "Dell", "color": "Black", "specs": {"ram": "16GB", "storage": "512GB SSD"}}');

Lệnh này lưu trữ dữ liệu về một laptop Dell với các thuộc tính đặc biệt trong định dạng JSON.

5. Tạo bảng với khóa ngoại có hành vi ON DELETE CASCADE

Hành vi ON DELETE CASCADE được sử dụng để tự động xóa tất cả các bản ghi liên quan khi một bản ghi trong bảng cha bị xóa.

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 
    ON DELETE CASCADE
);

Khi một khách hàng trong bảng customers bị xóa, tất cả các đơn hàng liên quan trong bảng orders sẽ bị xóa theo.

6. Tạo bảng với cột VIRTUAL hoặc STORED

MySQL hỗ trợ các cột ảo (VIRTUAL) hoặc cột lưu trữ (STORED) để tính toán giá trị dựa trên các cột khác trong bảng. Các cột này giúp bạn không cần phải tính toán lại các giá trị mỗi lần truy vấn.

• Cột VIRTUAL: Giá trị của cột này được tính toán động mỗi khi truy vấn.
• Cột STORED: Giá trị được tính toán một lần và lưu trữ trong cơ sở dữ liệu.

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    price DECIMAL(10, 2),
    discount DECIMAL(10, 2),
    final_price DECIMAL(10, 2) GENERATED ALWAYS AS (price - discount) VIRTUAL
);

Trong bảng này, final_price được tính tự động bằng công thức price - discount mà không cần phải lưu trữ trực tiếp trong cơ sở dữ liệu.

7. Tạo bảng với chỉ mục không duy nhất (INDEX)

Chỉ mục không duy nhất (INDEX) giúp cải thiện tốc độ truy vấn nhưng không bắt buộc giá trị trong cột đó phải duy nhất.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    department_id INT,
    INDEX (department_id)
);

Ở đây, chỉ mục được tạo trên cột department_id để tăng tốc độ truy vấn dữ liệu mà không bắt buộc giá trị phải duy nhất.

8. Tạo bảng với chỉ mục duy nhất (UNIQUE INDEX)

Chỉ mục duy nhất (UNIQUE INDEX) đảm bảo rằng các giá trị trong một cột phải duy nhất nhưng không phải là khóa chính.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100),
    UNIQUE INDEX (email)
);

Chỉ mục UNIQUE trên cột email đảm bảo rằng không có hai nhân viên nào có cùng địa chỉ email.

9. Tạo bảng với kiểu dữ liệu không gian (SPATIAL)

MySQL hỗ trợ các loại dữ liệu không gian như POINT, LINESTRING, và POLYGON để lưu trữ thông tin địa lý.

CREATE TABLE locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    coordinates POINT,
    SPATIAL INDEX (coordinates)
);

Trong bảng này, cột coordinates lưu trữ các tọa độ địa lý dưới dạng POINT. Chỉ mục không gian (SPATIAL INDEX) giúp tăng tốc độ truy vấn các dữ liệu không gian.

10. Tạo bảng với phân vùng kết hợp (SUBPARTITION)

Bạn có thể kết hợp nhiều loại phân vùng, ví dụ như phân vùng RANGE kết hợp với phân vùng HASH.

CREATE TABLE sales (
    sale_id INT PRIMARY KEY AUTO_INCREMENT,
    sale_date DATE,
    total DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) 
SUBPARTITION BY HASH(MONTH(sale_date)) 
SUBPARTITIONS 4 (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

Trong bảng này:

• Dữ liệu được phân vùng dựa trên năm (RANGE theo YEAR(sale_date)).
• Sau đó, mỗi phân vùng lại được chia nhỏ thành 4 phân vùng con dựa trên tháng (HASH theo MONTH(sale_date)).

Điều này giúp quản lý dữ liệu doanh số theo năm và tháng một cách hiệu quả.

11. Tạo bảng với bảng kế thừa (InnoDB vs MyISAM)

MySQL hỗ trợ nhiều loại storage engine, trong đó phổ biến nhất là InnoDB và MyISAM. Bạn có thể chỉ định storage engine khi tạo bảng:

CREATE TABLE blog_posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    content TEXT
) ENGINE=MyISAM;

Trong ví dụ này, bảng blog_posts được lưu trữ dưới dạng MyISAM. InnoDB thường được sử dụng với các bảng có nhiều khóa ngoại và giao dịch (transaction), còn MyISAM thường được sử dụng cho các bảng không cần giao dịch hoặc quan hệ khóa ngoại.

12. Tạo bảng sử dụng kiểu dữ liệu SET và ENUM

MySQL hỗ trợ các kiểu dữ liệu SET và ENUM để lưu trữ các tập giá trị cố định.

• Kiểu dữ liệu ENUM: Lưu trữ một giá trị duy nhất trong danh sách cố định.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    gender ENUM('Male', 'Female', 'Other')
);

Cột gender chỉ có thể nhận một trong các giá trị: 'Male', 'Female', hoặc 'Other'.

• Kiểu dữ liệu SET: Lưu trữ một hoặc nhiều giá trị từ một danh sách cố định.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    skills SET('PHP', 'JavaScript', 'Python', 'Java')
);

Cột skills có thể lưu trữ nhiều kỹ năng cho một nhân viên, ví dụ: PHP, JavaScript.

Những ví dụ trên bao quát nhiều tình huống sử dụng nâng cao cho câu lệnh CREATE TABLE trong MySQL, giúp tối ưu hóa hiệu suất, quản lý dữ liệu hiệu quả và phù hợp với các yêu cầu cụ thể trong các hệ thống phức tạp.

• Câu Lệnh Trong MySQL
• MySQL