当前位置：首页 > news >正文

SQL详细语法教程（二）--DML（数据操作语言）和DQL（数据查询语言）

news 2025/8/14 11:54:27

一、DML（数据操作语言）深度解析

1. 插入（INSERT）

语法细节拓展：
- 多行插入（MySQL、PostgreSQL 等支持）：
```
INSERT INTO students(name, age) 
VALUES('张三', 18), ('李四', 20), ('王五', 19);
```
  相比循环单条插入，批量插入效率更高，适合初始化大量测试数据、业务数据批量导入（如电商订单批量录入）。
- 插入查询结果（借助 SELECT 动态生成值）：
```
-- 从临时表/其他表取数据插入当前表
INSERT INTO students(name, age) 
SELECT name, age FROM temp_students; 
```
  常用于数据迁移、同步不同表结构相似的数据（如归档表向主表同步）。
约束影响：若表有主键、唯一约束、非空约束，插入值必须满足规则，否则报错。比如 age 列设为非空，插入时必须给值；主键列值重复会触发唯一约束冲突。

2. 更新（UPDATE）

执行逻辑：
- 先找到 WHERE 条件匹配的行，再逐个修改 SET 子句指定列的值，同一条语句里，更新后的值不会影响本次其他行的条件判断（避免逻辑混乱）。
- 多表关联更新（部分数据库支持，如 MySQL）：
```
-- 关联 students 和 classes 表，更新学生班级名称
UPDATE students s
JOIN classes c ON s.class_id = c.id 
SET s.class_name = c.name 
WHERE c.grade = '高一';
```
  适用于数据同步场景，比如订单表关联用户表，更新订单的用户最新信息。
性能与风险：
- 无 WHERE 条件会全表更新，生产库执行前务必加条件、做好备份，可通过事务（BEGIN/COMMIT/ROLLBACK ）控制，若更新出错及时回滚。
- 大表更新建议分批处理（结合 LIMIT 或按主键分段），避免锁表时间过长阻塞业务。

3. 删除（DELETE）

与 TRUNCATE 区别：
- DELETE 是 DML 操作，可回滚（在事务中执行 DELETE 后，ROLLBACK 能恢复数据），逐行删除触发触发器（若表有触发器），适合删部分数据；
- TRUNCATE TABLE 表名 是 DDL 操作，不可回滚（MySQL 中因实现为 “删除表重建” ），执行快、不触发触发器，会清空全表数据，常用于测试环境重置表、生产环境清空日志表（确认无业务数据后）。
索引影响：删除数据会维护表索引，大量删除后，部分数据库索引会产生碎片，可通过 OPTIMIZE TABLE（MySQL）等语句整理（需谨慎，会锁表）。

二、DQL（数据查询语言）深度解析

1. 基础查询（SELECT 基础）

SELECT * 弊端：
- 实际开发尽量避免用 SELECT *，一方面查询所有列可能包含无用字段，增加网络传输、内存消耗；另一方面若表结构变更（新增列），依赖 * 的代码可能因多余字段报错（如程序解析结果集逻辑固定时）。
- 推荐显式指定需要的列，如 SELECT name, age FROM students，清晰且性能更可控。
表达式计算：SELECT 中可直接写计算逻辑，比如：
```
SELECT name, age + 1 AS next_year_age 
FROM students;
```
用于简单数据转换（如日期加一天、数值运算），减少应用层计算压力。

2. 条件查询（WHERE 子句）

运算符进阶：
- LIKE 模糊匹配优化：
  - 前缀模糊（LIKE '%张'）无法走索引，查询慢；后缀模糊（LIKE '张%' ）可利用索引，若需复杂模糊查询，可结合全文索引（如 MySQL 的 FULLTEXT ）优化。
  - 示例：
```
-- 慢查询（全表扫描）
SELECT * FROM students WHERE name LIKE '%张'; 
-- 快查询（走索引，若 name 列有索引）
SELECT * FROM students WHERE name LIKE '张%'; 
```
- IN 与 EXISTS 选择：
  - 当子查询结果集小，用 IN 简洁：SELECT * FROM students WHERE class_id IN (1,2,3);
  - 当主表数据少、子查询结果集大，EXISTS 更高效（因 EXISTS 找到匹配就停止，无需全量比对）：
```
SELECT * FROM students s
WHERE EXISTS (SELECT 1 FROM classes c WHERE c.id = s.class_id AND c.grade = '高一'
);
```
空值判断：NULL 需用 IS NULL/IS NOT NULL 判断，不能用 = 或 !=，比如：
```
SELECT * FROM students WHERE age IS NULL;
```

3. 聚合函数

COUNT() 细节：
- COUNT(*) 统计包含 NULL 行的总数，COUNT(age) 只统计 age 列非 NULL 的行数，若表有大量 NULL 值，两者结果差异大。
- 示例：
```
-- 统计所有学生（含 age 为 NULL 的）
SELECT COUNT(*) FROM students; 
-- 统计 age 有值的学生数
SELECT COUNT(age) FROM students; 
```
聚合函数与 NULL：SUM()、AVG() 会自动忽略 NULL 值参与计算，比如 age 列有 NULL，AVG(age) 只算非 NULL 行的平均值。

4. 分组查询（GROUP BY）

分组字段要求：
- 在严格模式（如 MySQL 的 ONLY_FULL_GROUP_BY ）下，SELECT 中非聚合列必须是 GROUP BY 的分组列，否则报错。比如：
```
-- 报错（name 不是分组列，也不是聚合结果）
SELECT name, COUNT(*) FROM students GROUP BY age; 
-- 正确（name 是分组列，或用聚合函数包裹）
SELECT age, COUNT(*) FROM students GROUP BY age; 
```
  避免因随意选列导致结果混乱（不同行同名但其他列不同时，无法确定展示哪行值）。
ROLLUP 与 CUBE（扩展分组）：
- ROLLUP 生成多级分组统计，比如按部门、岗位分组后，自动加 “总计” 行：
```
SELECT department, job, COUNT(*) 
FROM employees 
GROUP BY ROLLUP(department, job);
```
- CUBE 会生成所有可能的分组组合，更适合复杂多维度统计（如电商按地区、商品分类、时间的多维统计），不过部分数据库（如 MySQL 需版本支持）。

5. 排序查询（ORDER BY）

排序规则控制：
- 字符串排序受字符集、排序规则影响，比如 utf8_general_ci 不区分大小写，utf8_bin 区分。示例：
```
-- 按姓名排序，区分大小写（假设用 utf8_bin 排序规则）
SELECT * FROM students 
ORDER BY name COLLATE utf8_bin; 
```
- 数值与字符串混合排序易出问题，比如 ORDER BY age，若 age 存成字符串（'18'、'20' ），实际会按字符编码排序（'100' 会排在 '20' 前），需提前转换类型（ORDER BY CAST(age AS SIGNED) ）。
多字段排序优先级：先按第一列排序，第一列值相同时，再按第二列排序，以此类推。比如：
```
-- 先按年龄升序，同年龄按姓名降序
SELECT * FROM students 
ORDER BY age ASC, name DESC; 
```

6. 分页查询（LIMIT 等）

语法差异对比：

数据库	分页语法	示例（取第 11 - 20 条，假设主键 id 排序）
MySQL	`LIMIT 偏移量, 行数`	`LIMIT 10, 10`（偏移量从 0 开始，`10` 表示跳过前 10 条）
Oracle	`ROWNUM` 伪列	`SELECT * FROM (SELECT t., ROWNUM r FROM (SELECT FROM students ORDER BY id) t WHERE ROWNUM <= 20) WHERE r > 10`
SQL Server	`OFFSET/FETCH`（2005+）	`SELECT * FROM students ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY`

性能优化：
- 大表分页，直接用 LIMIT 10000, 10 会因偏移量大变慢（需扫描前 10010 行再丢弃），可优化为索引覆盖 + 主键定位：
```
-- 利用 id 主键索引快速定位
SELECT * FROM students 
WHERE id > (SELECT id FROM students ORDER BY id LIMIT 10000, 1) 
ORDER BY id LIMIT 10; 
```
- 适合高并发分页场景（如论坛帖子列表、电商商品列表），减少数据扫描范围。

7. 执行顺序（核心逻辑串联）

记住执行顺序，能帮你解决复杂查询的语法、逻辑问题，拆解如下（从左到右、从上到下依次执行）：

FROM：确定要操作的表，处理表连接（JOIN ），生成 “中间结果集”。
WHERE：对 FROM 生成的结果集，逐行过滤不满足条件的行（此时还没分组，不能用聚合函数）。
GROUP BY：按指定列分组，相同分组列的行合并为一组。
HAVING：对分组后的结果过滤，可使用聚合函数（因分组已完成，聚合结果可访问）。
SELECT：选择要展示的列，计算表达式、聚合函数，生成最终列（此时才确定哪些列会输出）。
ORDER BY：对 SELECT 生成的结果集排序（可使用别名、聚合结果排序）。
LIMIT：截取排序后的结果集，返回指定行数。

示例验证：

SELECT department, COUNT(*) AS emp_count 
FROM employees 
WHERE salary > 5000 
GROUP BY department 
HAVING COUNT(*) > 5 
ORDER BY emp_count DESC 
LIMIT 3;

执行流程：