在SQL中，DDL、DML、DQL、DCL、TCL是按操作类型划分的五大核心语言类别，缩写及简介如下：

• DDL（Data Definition Language，数据定义语言）：用于定义和管理数据库结构，如创建、修改、删除表、索引等。常见命令有CREATE（创建）、ALTER（修改）、DROP（删除）。
• DML（Data Manipulation Language，数据操纵语言）：用于对表中的数据进行操作。常见命令有INSERT（插入）、UPDATE（更新）、DELETE（删除）。
• DQL（Data Query Language，数据查询语言）：用于查询数据库中的数据，核心命令是SELECT，可通过条件、排序、聚合等方式获取所需数据。
• DCL（Data Control Language，数据控制语言）：用于管理数据库的访问权限和安全。常见命令有GRANT（授予权限）、REVOKE（收回权限）。
• TCL（Transaction Control Language，事务控制语言）：用于管理数据库事务，确保数据操作的一致性。常见命令有COMMIT（提交事务）、ROLLBACK（回滚事务）、SAVEPOINT（设置保存点）。

下面详细介绍SQL五大操作类别：DDL、DML、DQL、DCL、TCL的基础语法，仅供参考：

先创建一个模拟数据库，心急的朋友直接飘到(二)，那是正文开始：(以下代码块与表格均可左右滚动)

一、创建模拟数据库

1. 数据库结构

-- 创建数据库
CREATE DATABASE company_db;
USE company_db;

-- DDL：部门表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL UNIQUE,
    location VARCHAR(100) DEFAULT 'Headquarters',
    annual_budget DECIMAL(15,2) CHECK (annual_budget >= 0),
    established_date DATE NOT NULL
);

-- DDL：员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE DEFAULT (CURRENT_DATE),
    dept_id INT,
    salary DECIMAL(10,2) NOT NULL CHECK (salary > 0),
    bonus DECIMAL(10,2) DEFAULT 0.00,
    CONSTRAINT fk_dept 
        FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
) ENGINE=InnoDB;

-- DDL：薪资历史表
CREATE TABLE salary_history (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_id INT NOT NULL,
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2) NOT NULL,
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_emp_salary 
        FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

-- DDL：用户表
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    role VARCHAR(50) DEFAULT 'staff'
);

2. 插入模拟数据

-- DML：插入部门数据
INSERT INTO departments (dept_name, location, annual_budget, established_date) 
VALUES 
    ('Engineering', 'San Francisco', 1500000.00, '2010-05-15'),
    ('Marketing', 'New York', 800000.00, '2012-02-20'),
    ('Human Resources', 'Chicago', 500000.00, '2015-08-10');

-- DML：插入员工数据
INSERT INTO employees (first_name, last_name, email, hire_date, dept_id, salary) 
VALUES
    ('Alice', 'Chen', 'alice.chen@company.com', '2020-03-15', 1, 95000.00),
    ('Robert', 'Wilson', 'robert.w@company.com', '2018-11-22', 1, 115000.00),
    ('Emily', 'Davis', 'emily.davis@company.com', '2022-01-10', 2, 75000.00),
    ('James', 'Johnson', 'james.j@company.com', '2019-06-05', 3, 65000.00);

-- DML：插入用户数据
INSERT INTO users (username, role)
VALUES
    ('admin1', 'administrator'),
    ('hr_user', 'hr_manager'),
    ('dev_user', 'engineering');

二、DDL（数据定义语言）

1. CREATE TABLE

基础语法：

CREATE TABLE table_name (
    column1 datatype [column_constraint],
    column2 datatype [column_constraint],
    ...
    [table_constraints]
);

语法元素：

应用示例：

-- 创建项目表
CREATE TABLE projects (
    project_id INT AUTO_INCREMENT PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL UNIQUE,
    start_date DATE NOT NULL,
    end_date DATE,
    dept_id INT NOT NULL,
    budget DECIMAL(10,2) CHECK (budget > 0),
    status ENUM('Planning', 'Active', 'Completed') DEFAULT 'Planning',
    CONSTRAINT fk_dept_projects 
        FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

模拟输出：

DESCRIBE projects;
/*
+--------------+-------------------------------+------+-----+------------+----------------+
| Field        | Type                          | Null | Key | Default    | Extra          |
+--------------+-------------------------------+------+-----+------------+----------------+
| project_id   | int(11)                       | NO   | PRI | NULL       | auto_increment |
| project_name | varchar(100)                  | NO   | UNI | NULL       |                |
| start_date   | date                          | NO   |     | NULL       |                |
| end_date     | date                          | YES  |     | NULL       |                |
| dept_id      | int(11)                       | NO   | MUL | NULL       |                |
| budget       | decimal(10,2)                 | YES  |     | NULL       |                |
| status       | enum('Planning','Active','Completed') | YES |     | Planning |                |
+--------------+-------------------------------+------+-----+------------+----------------+
*/

2. ALTER TABLE

基础语法：

-- 添加列
ALTER TABLE table_name 
ADD COLUMN column_name datatype [constraints];

-- 修改列
ALTER TABLE table_name 
MODIFY COLUMN column_name new_datatype; -- MySQL
ALTER TABLE table_name 
ALTER COLUMN column_name TYPE new_datatype; -- PostgreSQL

-- 删除列
ALTER TABLE table_name 
DROP COLUMN column_name;

-- 添加约束
ALTER TABLE table_name 
ADD CONSTRAINT constraint_name constraint_definition;

-- 删除约束
ALTER TABLE table_name 
DROP CONSTRAINT constraint_name;

应用示例：

-- 添加项目经理列
ALTER TABLE projects
ADD COLUMN manager_id INT,
ADD CONSTRAINT fk_project_manager
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id);

-- 修改预算精度
ALTER TABLE projects
MODIFY budget DECIMAL(12,2);

-- 添加检查约束
ALTER TABLE employees
ADD CONSTRAINT chk_bonus 
    CHECK (bonus >= 0 AND bonus <= salary * 0.3);

模拟输出：

DESCRIBE projects;
/*
+-------------+----------------+------+-----+------------+----------------+
| Field       | Type           | Null | Key | Default    | Extra          |
+-------------+----------------+------+-----+------------+----------------+
| ...         | ...            | ...  | ... | ...        | ...            |
| manager_id  | int(11)        | YES  | MUL | NULL       |                |
+-------------+----------------+------+-----+------------+----------------+
*/

3. DROP & TRUNCATE

基础语法：

-- 删除表
DROP TABLE table_name [RESTRICT|CASCADE];

-- 清空表
TRUNCATE TABLE table_name;

应用示例：

-- 删除临时表
DROP TABLE temp_report_data;

-- 清空日志表
TRUNCATE TABLE debug_logs;

注意事项：

• DROP删除整个表结构和数据
• TRUNCATE保留表结构，删除所有数据
• MySQL中TRUNCATE不能回滚，PostgreSQL中可以

三、DML（数据操作语言）

1. INSERT

基础语法：

-- 完整形式
INSERT INTO table_name VALUES (value1, value2, ...);

-- 指定列
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

-- 多行插入
INSERT INTO table_name (column1, column2, ...)
VALUES 
    (value1a, value2a, ...),
    (value1b, value2b, ...);

-- 从查询插入
INSERT INTO table_name (column1, column2, ...)
SELECT ...;

应用示例：

-- 插入项目数据
INSERT INTO projects (project_name, start_date, dept_id, budget)
VALUES
    ('CRM System', '2023-01-15', 1, 200000.00),
    ('Marketing Campaign', '2023-03-01', 2, 50000.00);

-- 为经理分配项目
UPDATE projects
SET manager_id = (
    SELECT emp_id FROM employees 
    WHERE email = 'robert.w@company.com'
)
WHERE project_name = 'CRM System';

模拟输出：

SELECT * FROM projects;
/*
+------------+-------------------+------------+----------+---------+-----------+----------+------------+
| project_id | project_name      | start_date | end_date | dept_id | budget    | status   | manager_id |
+------------+-------------------+------------+----------+---------+-----------+----------+------------+
|          1 | CRM System        | 2023-01-15 | NULL     |       1 | 200000.00 | Planning |          2 |
|          2 | Marketing Campaign| 2023-03-01 | NULL     |       2 |  50000.00 | Planning |       NULL |
+------------+-------------------+------------+----------+---------+-----------+----------+------------+
*/

2. UPDATE

基础语法：

UPDATE table_name
SET 
    column1 = value1,
    column2 = value2,
    ...
[WHERE condition]
[ORDER BY ...]
[LIMIT count];

参数详解：

• WHERE：指定更新哪些行（不指定则更新所有行）
• ORDER BY/LIMIT：MySQL特有，用于分批更新大数据集

应用示例：

-- 普通更新
UPDATE employees
SET bonus = salary * 0.1
WHERE hire_date < '2022-01-01';

-- 关联更新
UPDATE employees e
JOIN projects p ON e.emp_id = p.manager_id
SET e.salary = e.salary * 1.15
WHERE p.project_name = 'CRM System';

-- 基于子查询更新
UPDATE projects
SET end_date = DATE_ADD(start_date, INTERVAL 6 MONTH),
    status = 'Active'
WHERE project_id = 1;

模拟输出：

-- 更新后查询
SELECT emp_id, first_name, salary, bonus 
FROM employees 
WHERE emp_id = 2;
/*
+--------+------------+-----------+--------+
| emp_id | first_name | salary    | bonus  |
+--------+------------+-----------+--------+
|      2 | Robert     | 132250.00 | 11500  |
+--------+------------+-----------+--------+
*/

3. DELETE

基础语法：

DELETE FROM table_name
[WHERE condition]
[ORDER BY ...]
[LIMIT row_count];

应用示例：

-- 删除测试用户
DELETE FROM users 
WHERE username LIKE 'test_%';

-- 删除无项目的部门
DELETE FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM projects)
AND established_date < '2020-01-01';

-- 删除预算过低的项目
DELETE FROM projects
WHERE budget < 10000;

模拟输出：

-- 删除后查询部门
SELECT * FROM departments;
/*
+---------+----------------+---------------+--------------+-----------------+
| dept_id | dept_name      | location      | annual_budget| established_date|
+---------+----------------+---------------+--------------+-----------------+
|       1 | Engineering    | San Francisco |   1500000.00 | 2010-05-15      |
|       2 | Marketing      | New York      |    800000.00 | 2012-02-20      |
+---------+----------------+---------------+--------------+-----------------+
*/

四、DQL（数据查询语言）

1. SELECT基础结构

完整语法：

SELECT 
    [DISTINCT] column1 [AS alias],
    aggregate_function(column2),
    expression
FROM table1
[JOIN table2 ON join_condition]
[WHERE conditions]
[GROUP BY grouping_columns]
[HAVING group_conditions]
[ORDER BY sort_columns [ASC|DESC]]
[OFFSET start]
[LIMIT count]
[FOR UPDATE];

2. JOIN连接

连接类型：

-- 内连接（默认）
SELECT ... FROM table1 
[INNER] JOIN table2 ON condition

-- 左外连接
SELECT ... FROM table1 
LEFT [OUTER] JOIN table2 ON condition

-- 右外连接
SELECT ... FROM table1 
RIGHT [OUTER] JOIN table2 ON condition

-- 全外连接
SELECT ... FROM table1 
FULL [OUTER] JOIN table2 ON condition

-- 交叉连接
SELECT ... FROM table1 
CROSS JOIN table2

应用示例：

-- 多表连接查询
SELECT 
    e.emp_id,
    CONCAT(e.first_name, ' ', e.last_name) AS employee,
    d.dept_name,
    p.project_name,
    p.budget
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN projects p ON e.emp_id = p.manager_id;

模拟输出：

+--------+-----------------+--------------+-------------------+-----------+
| emp_id | employee        | dept_name    | project_name      | budget    |
+--------+-----------------+--------------+-------------------+-----------+
|      1 | Alice Chen      | Engineering  | NULL              |      NULL |
|      2 | Robert Wilson   | Engineering  | CRM System        | 200000.00 |
|      3 | Emily Davis     | Marketing    | NULL              |      NULL |
|      4 | James Johnson   | Human Resources| NULL              |      NULL |
+--------+-----------------+--------------+-------------------+-----------+

3. 聚合与分组

常用聚合函数：

应用示例：

-- 部门薪资分析
SELECT 
    d.dept_name,
    COUNT(e.emp_id) AS num_employees,
    SUM(e.salary + e.bonus) AS total_compensation,
    AVG(e.salary) AS avg_salary,
    MIN(e.hire_date) AS earliest_hire,
    GROUP_CONCAT(e.first_name SEPARATOR ', ') AS team_members
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id
HAVING COUNT(e.emp_id) > 1;

模拟输出：

+--------------+---------------+---------------+-----------+----------------+-----------------+
| dept_name    | num_employees | total_compensation | avg_salary | earliest_hire | team_members    |
+--------------+---------------+---------------+-----------+----------------+-----------------+
| Engineering  |             2 |    252750.00   | 105000.00 | 2018-11-22    | Alice, Robert   |
+--------------+---------------+---------------+-----------+----------------+-----------------+

4. 窗口函数

常用窗口函数：

应用示例：

-- 部门内薪资排名
SELECT
    emp_id,
    CONCAT(first_name, ' ', last_name) AS employee,
    dept_id,
    salary,
    bonus,
    salary + bonus AS total_comp,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank,
    ROUND(salary * 100 / SUM(salary) OVER (PARTITION BY dept_id), 2) AS salary_percent,
    AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;

模拟输出：

+--------+----------------+---------+-----------+--------+-----------+-----------+---------------+----------+
| emp_id | employee       | dept_id | salary    | bonus  | total_comp| dept_rank| salary_percent| dept_avg |
+--------+----------------+---------+-----------+--------+-----------+-----------+---------------+----------+
|      2 | Robert Wilson  |       1 | 132250.00 | 11500  | 143750.00 |        1 |         55.74 | 113625.00|
|      1 | Alice Chen     |       1 |  95000.00 | 9500   | 104500.00 |        2 |         44.26 | 113625.00|
|      3 | Emily Davis    |       2 |  75000.00 | 7500   |  82500.00 |        1 |        100.00 |  75000.00|
|      4 | James Johnson  |       3 |  65000.00 | 6500   |  71500.00 |        1 |        100.00 |  65000.00|
+--------+----------------+---------+-----------+--------+-----------+-----------+---------------+----------+

五、DCL（数据控制语言）

权限管理

基础语法：

-- 创建角色
CREATE ROLE role_name;

-- 授予权限
GRANT privilege_type ON object TO user_or_role
[WITH GRANT OPTION];

-- 撤销权限
REVOKE privilege_type ON object 
FROM user_or_role;

-- 查看权限
SHOW GRANTS FOR username;

权限类型：

应用示例：

-- 创建HR管理员角色
CREATE ROLE hr_admin;

-- 授予权限
GRANT SELECT, INSERT, UPDATE 
ON employees 
TO hr_admin;

GRANT SELECT 
ON departments 
TO hr_admin;

-- 角色分配给用户
GRANT hr_admin TO hr_user;

-- 回收权限
REVOKE UPDATE 
ON employees 
FROM hr_admin;

模拟输出：

SHOW GRANTS FOR 'hr_user';
/*
+-------------------------------------------------------------------+
| Grants for hr_user@%                                              |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `hr_user`@`%`                               |
| GRANT SELECT, INSERT ON `company_db`.`employees` TO `hr_user`@`%`|
| GRANT SELECT ON `company_db`.`departments` TO `hr_user`@`%`       |
+-------------------------------------------------------------------+
*/

六、TCL（事务控制语言）

1. 事务基础语法

-- 开始事务
BEGIN;  -- 或 START TRANSACTION

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT savepoint_name;

-- 回滚到保存点
ROLLBACK TO savepoint_name;

-- 设置事务级别
SET TRANSACTION ISOLATION LEVEL level;

2. 隔离级别

3. 应用示例

-- 薪资调整事务
BEGIN;

-- 设置保存点
SAVEPOINT before_updates;

-- 更新员工薪资
UPDATE employees
SET salary = salary * 1.05
WHERE dept_id = 1;

-- 记录薪资历史
INSERT INTO salary_history (emp_id, old_salary, new_salary)
SELECT emp_id, salary/1.05, salary 
FROM employees 
WHERE dept_id = 1;

-- 检查总薪资预算
IF (SELECT SUM(salary) FROM employees) > 500000 THEN
    ROLLBACK TO before_updates;
    INSERT INTO error_log (message) VALUES ('Salary budget exceeded');
END IF;

COMMIT;

模拟输出：

-- 事务执行后查询薪资历史
SELECT * FROM salary_history;
/*
+-----------+--------+------------+------------+---------------------+
| record_id | emp_id | old_salary | new_salary | change_date         |
+-----------+--------+------------+------------+---------------------+
|         1 |      1 |    95000.00|    99750.00| 2023-06-20 10:30:15 |
|         2 |      2 |   132250.00|   138862.50| 2023-06-20 10:30:15 |
+-----------+--------+------------+------------+---------------------+
*/

七、高级功能

1. 递归查询（CTEs）

-- 组织结构递归查询
WITH RECURSIVE org_hierarchy AS (
    -- 初始查询
    SELECT 
        emp_id,
        first_name,
        last_name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归成员
    SELECT 
        e.emp_id,
        e.first_name,
        e.last_name,
        e.manager_id,
        oh.level + 1
    FROM employees e
    INNER JOIN org_hierarchy oh ON e.manager_id = oh.emp_id
)
SELECT * FROM org_hierarchy;

2. JSON函数（MySQL/PostgreSQL）

-- 生成员工JSON信息
SELECT 
    emp_id,
    JSON_OBJECT(
        'name', CONCAT(first_name, ' ', last_name),
        'email', email,
        'position', job_title,
        'compensation', JSON_OBJECT(
            'base', salary,
            'bonus', bonus
        )
    ) AS employee_json
FROM employees
WHERE dept_id = 1;

模拟输出：

[
  {
    "emp_id": 1,
    "employee_json": {
      "name": "Alice Chen",
      "email": "alice.chen@company.com",
      "position": "Senior Developer",
      "compensation": {
        "base": 95000.00,
        "bonus": 9500.00
      }
    }
  },
  {
    "emp_id": 2,
    "employee_json": {
      "name": "Robert Wilson",
      "email": "robert.w@company.com",
      "position": "Engineering Manager",
      "compensation": {
        "base": 132250.00,
        "bonus": 11500.00
      }
    }
  }
]

八、安全性与性能实践

1. 预防SQL注入

-- 错误方式（易受攻击）
SET @name = 'Alice; DROP TABLE employees;';
SET @sql = CONCAT('SELECT * FROM employees WHERE first_name = ''', @name, '''');
PREPARE stmt FROM @sql;
EXECUTE stmt;

-- 正确方式（参数化查询）
SET @name = 'Alice';
SET @sql = 'SELECT * FROM employees WHERE first_name = ?';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @name;

2. 索引优化策略

-- 添加组合索引
CREATE INDEX idx_emp_dept_salary 
ON employees(dept_id, salary DESC);

-- 分析查询计划
EXPLAIN ANALYZE
SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id;

EXPLAIN输出示例：

+----+-------------+-----------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | index | idx_emp_dept_salary| idx_emp_dept_salary| 10      | NULL  |    4 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+

九、数据库差异参考表

-- MySQL vs PostgreSQL 分页查询示例
/* MySQL */
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 3 OFFSET 2;

/* PostgreSQL */
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 3 OFFSET 2;

/* SQL Server */
SELECT * FROM employees 
ORDER BY hire_date DESC 
OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;

十、总结

SQL五大操作对比表：

应用建议：

1. 开发环境中DDL变更使用版本控制工具（如Flyway）
2. 批量DML操作采用分批提交策略（每1000-10000行）
3. 复杂DQL查询先使用EXPLAIN分析执行计划
4. 敏感DCL操作实施两人复核机制
5. 核心业务TCL事务设置死锁检测与超时回滚机制