3. SQL语言基础：SQL标准、DDL、DML、DQL、DCL的基本语法与使用

SQL（Structured Query Language，结构化查询语言）是关系数据库管理系统（RDBMS）的核心操作语言。无论是Oracle、MySQL、SQL Server还是PostgreSQL，其底层数据操作逻辑均遵循SQL标准。本章将系统梳理SQL标准的演进脉络，并深入解析DDL（数据定义语言）、DML（数据操作语言）、DQL（数据查询语言）与DCL（数据控制语言）的核心语法、执行逻辑及典型应用场景。掌握本章内容，是后续理解索引优化、事务控制、存储过程与数据库设计的基础前提。

上图展示了SQL语言的四大核心分类及其内在关联。DDL负责定义数据库对象的“骨架”，DML负责操作“血肉”（数据行），DQL是数据检索与分析的核心工具，而DCL则从安全维度控制访问权限。需要特别指出的是，事务控制语句（如COMMIT、ROLLBACK）在多数数据库产品中独立于四大分类，但因其与DML操作紧密相关，本章将在DML部分一并讨论。

3.1 SQL标准演进与数据库方言

3.1.1 核心原理：标准与方言的博弈

SQL标准由ANSI（美国国家标准协会）和ISO（国际标准化组织）联合制定。自1986年首个标准发布以来，经历了SQL-92、SQL:1999、SQL:2003、SQL:2008、SQL:2016等多次重大修订。标准定义了语法规则、数据类型、隔离级别等规范，但各数据库厂商在实现时往往增加“方言”特性。例如，Oracle的CONNECT BY用于层次查询，MySQL的LIMIT子句用于分页，SQL Server的TOP关键字实现类似功能。理解标准与方言的差异，是编写可移植SQL的前提。

3.1.2 判断方法：如何识别方言特性

判断一段SQL是否依赖特定方言，可从以下维度检查：

• 数据类型差异：MySQL的TINYINT、Oracle的NUMBER、SQL Server的DATETIME2均非标准类型。
• 字符串拼接：标准SQL使用||，但MySQL默认使用CONCAT()函数，SQL Server使用+。
• 分页实现：标准SQL:2008定义了OFFSET...FETCH，但MySQL 5.7及以下版本仅支持LIMIT。
• 布尔值表示：PostgreSQL支持TRUE/FALSE关键字，MySQL用1/0，Oracle不支持布尔类型。

3.2 DDL：数据定义语言

3.2.1 核心语法与操作流程

DDL用于创建、修改和删除数据库对象。以MySQL为例，核心操作包括：

-- 创建数据库
CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4;

-- 创建表（含主键、外键、约束）
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    stu_no VARCHAR(20) NOT NULL UNIQUE COMMENT '学号',
    name VARCHAR(50) NOT NULL,
    age TINYINT CHECK (age BETWEEN 15 AND 60),
    dept_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES department(id)
) ENGINE=InnoDB;

-- 修改表结构
ALTER TABLE student ADD COLUMN email VARCHAR(100) AFTER name;
ALTER TABLE student MODIFY COLUMN age TINYINT UNSIGNED;
ALTER TABLE student DROP COLUMN email;

-- 删除表
DROP TABLE IF EXISTS student;

-- 清空表（保留结构）
TRUNCATE TABLE student;

3.2.2 案例拆解：在线教育平台表结构设计

某在线教育平台需要设计课程与选课模块。初始需求包括：课程信息（课程号、名称、学分）、学生信息（学号、姓名、专业）、选课记录（学生选课时间、成绩）。设计约束：一个学生可选多门课，一门课可被多个学生选，成绩范围为0-100。

CREATE TABLE course (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(10) NOT NULL UNIQUE,
    title VARCHAR(100) NOT NULL,
    credit DECIMAL(2,1) CHECK (credit > 0)
);

CREATE TABLE student (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    stu_no VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(50) NOT NULL,
    major VARCHAR(50)
);

CREATE TABLE enrollment (
    student_id INT,
    course_id INT,
    enroll_date DATE DEFAULT (CURRENT_DATE),
    score DECIMAL(5,2) CHECK (score BETWEEN 0 AND 100),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES course(course_id) ON DELETE RESTRICT
);

该设计体现了DDL的关键决策点：复合主键确保选课唯一性；ON DELETE CASCADE实现学生删除时自动清理选课记录；ON DELETE RESTRICT防止删除已被选用的课程。实际部署时，还需考虑索引策略：enrollment表的course_id列应建立辅助索引，以加速按课程查询选课名单。

3.3 DML：数据操作语言

3.3.1 核心语法与事务边界

DML包含INSERT、UPDATE、DELETE三种基本操作，以及MERGE（合并）操作。所有DML操作均在事务上下文中执行，需显式或隐式提交。

-- 插入单行
INSERT INTO student (stu_no, name, age, dept_id) VALUES ('2024001', '张三', 20, 1);

-- 批量插入
INSERT INTO course (course_code, title, credit) VALUES 
('CS101', '数据库原理', 3.0),
('CS102', '操作系统', 3.5),
('MA201', '高等数学', 4.0);

-- 从查询结果插入
INSERT INTO student_archive (stu_no, name, age)
SELECT stu_no, name, age FROM student WHERE age > 25;

-- 更新数据
UPDATE student SET age = age + 1 WHERE dept_id = 2;

-- 删除数据
DELETE FROM enrollment WHERE score IS NULL AND enroll_date < '2024-01-01';

-- 合并操作（Oracle/PostgreSQL/SQL Server）
MERGE INTO target_table t
USING source_table s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

3.3.2 实战场景：库存扣减的并发控制

电商系统库存表inventory包含product_id、stock_qty字段。用户下单时需扣减库存，但高并发下直接执行UPDATE inventory SET stock_qty = stock_qty - 1 WHERE product_id = 100可能导致超卖。解决方案是使用条件更新与行锁：

-- 原子操作：仅当库存充足时扣减
UPDATE inventory 
SET stock_qty = stock_qty - 1 
WHERE product_id = 100 AND stock_qty > 0;

-- 检查受影响行数
-- 若影响行数为0，说明库存不足，需回滚订单事务

该方案利用数据库行级锁保证同一商品库存更新的串行化，避免了应用层加锁的复杂性。但需注意：当库存扣减与订单创建分属不同事务时，应使用分布式事务或消息队列保证最终一致性。

3.4 DQL：数据查询语言

3.4.1 查询执行逻辑与语法顺序

SELECT语句是SQL中使用最频繁、语法最灵活的部分。其逻辑执行顺序与书写顺序不同：

1. FROM：确定数据源，执行表连接
2. WHERE：行级过滤
3. GROUP BY：分组
4. HAVING：分组后过滤
5. SELECT：投影列，计算表达式
6. DISTINCT：去重
7. ORDER BY：排序
8. LIMIT/OFFSET：分页

-- 多表连接查询
SELECT s.name, c.title, e.score
FROM student s
JOIN enrollment e ON s.student_id = e.student_id
JOIN course c ON e.course_id = c.course_id
WHERE e.score >= 60
ORDER BY e.score DESC;

-- 聚合查询
SELECT dept_id, COUNT(*) AS stu_cnt, AVG(age) AS avg_age
FROM student
GROUP BY dept_id
HAVING COUNT(*) > 10;

-- 窗口函数（SQL:2003+）
SELECT name, score,
       RANK() OVER (PARTITION BY course_id ORDER BY score DESC) AS rk
FROM enrollment;

3.4.2 案例拆解：学生成绩分析报表

教务系统需要生成“各专业不及格率统计”报表。数据表：student（含major字段）、enrollment（含score字段）。目标输出：专业名称、考试人数、不及格人数、不及格率。

SELECT 
    s.major,
    COUNT(*) AS total_exams,
    SUM(CASE WHEN e.score < 60 THEN 1 ELSE 0 END) AS fail_cnt,
    ROUND(SUM(CASE WHEN e.score < 60 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS fail_rate
FROM student s
JOIN enrollment e ON s.student_id = e.student_id
WHERE e.score IS NOT NULL
GROUP BY s.major
HAVING COUNT(*) >= 50
ORDER BY fail_rate DESC;

该查询展示了DQL的核心技巧：CASE WHEN实现条件计数，HAVING过滤样本量过小的专业，ROUND控制精度。实际执行时，需确保student_id和major列有索引，否则大表连接将产生全表扫描。

3.5 DCL：数据控制语言与权限管理

3.5.1 权限模型与核心语法

DCL管理用户对数据库对象的访问权限。主流数据库采用基于角色的访问控制（RBAC）模型。核心操作包括：

-- 创建用户（MySQL）
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongP@ss2024';

-- 授予权限
GRANT SELECT, INSERT, UPDATE ON school.* TO 'app_user'@'%';
GRANT ALL PRIVILEGES ON school.student TO 'admin_user'@'localhost' WITH GRANT OPTION;

-- 回收权限
REVOKE DELETE ON school.* FROM 'app_user'@'%';

-- 角色管理（SQL:2003+）
CREATE ROLE 'read_only';
GRANT SELECT ON school.* TO 'read_only';
GRANT 'read_only' TO 'report_user'@'%';

-- 查看权限
SHOW GRANTS FOR 'app_user'@'%';

3.5.2 权限管理实战：多环境隔离

某公司数据库分为开发、测试、生产三套环境。开发人员需要读写权限，测试人员需要只读权限，运维人员需要DDL权限。通过DCL实现：

-- 开发环境
CREATE USER 'dev_team'@'10.0.%' IDENTIFIED BY 'Dev@2024';
GRANT SELECT, INSERT, UPDATE, DELETE ON dev_db.* TO 'dev_team'@'10.0.%';

-- 测试环境
CREATE USER 'qa_team'@'10.0.%' IDENTIFIED BY 'Qa@2024';
GRANT SELECT ON test_db.* TO 'qa_team'@'10.0.%';

-- 生产环境
CREATE USER 'ops_team'@'192.168.%' IDENTIFIED BY 'Ops@2024';
GRANT SELECT, CREATE, ALTER, DROP ON prod_db.* TO 'ops_team'@'192.168.%';
-- 注意：生产环境DDL操作应通过变更管理流程，而非直接授权

该方案通过IP白名单和账号分离实现环境隔离。但需注意：GRANT语句本身也是DDL操作，执行后立即生效，无需提交事务。在MySQL 8.0+中，CREATE USER和GRANT语句会自动隐式提交。

3.6 常见误区与总结复盘