SQL学习笔记3

SQL SQL
发布时间 :
字数:1.6k 阅读 :
  1. 条件过滤 WHERE
  2. 排序与分页
  3. CASE WHEN 条件表达式
  4. JOIN 连接查询
  5. 示例表
  6. INNER JOIN（内连接）
  7. LEFT JOIN（左连接）
  8. 多表连接
  9. 聚合函数与分组
  10. 常用聚合函数
  11. GROUP BY 分组
  12. HAVING 过滤分组
  13. 子查询
  14. 标量子查询（返回单个值）
  15. 行子查询 + IN
  16. EXISTS 子查询
  17. FROM 子句中的子查询（派生表）
  18. 公用表表达式 CTE（更清晰的子查询）
  19. 视图
  20. 索引与性能
  21. 创建索引
  22. 什么时候加索引？
  23. 用 EXPLAIN 分析查询
  24. 事务
  25. ACID 特性
  26. 基本用法
  27. 常见陷阱与最佳实践
  28. 陷阱 🕳️
  29. 最佳实践 ✅
  30. 结语

条件过滤 WHERE

SELECT * FROM users
WHERE age > 18
  AND email LIKE ''%@example.com''
  AND id IN (1, 2, 3);

常用运算符：

运算符 说明 示例
=, <>, != 等于、不等于 status <> ''deleted''
>, <, >=, <= 比较 age >= 18
AND, OR, NOT 逻辑运算 a > 1 AND b < 10
BETWEEN x AND y 范围（含边界） age BETWEEN 18 AND 30
IN (...) 在集合中 id IN (1, 3, 5)
LIKE 模式匹配 name LIKE ''张%''
IS NULL / IS NOT NULL 空值判断 email IS NOT NULL

排序与分页

-- 排序
SELECT * FROM users ORDER BY age DESC, username ASC;

-- 分页（LIMIT + OFFSET）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
-- 等价写法（MySQL/PostgreSQL）
SELECT * FROM users ORDER BY id LIMIT 20, 10;

CASE WHEN 条件表达式

SELECT
    username,
    age,
    CASE
        WHEN age < 18 THEN ''未成年''
        WHEN age BETWEEN 18 AND 60 THEN ''成年人''
        ELSE ''老年人''
    END AS age_group
FROM users;

JOIN 连接查询

这是我刚学 SQL 时觉得最绕的一部分，画个图就清楚了：

INNER JOIN:       LEFT JOIN:        RIGHT JOIN:       FULL JOIN:
┌───┐ ┌───┐      ┌───┐              ┌───┐             ┌───┐ ┌───┐
│ A∩B │           │ A │              │ B │              │ A∪B │
└───┘ └───┘      └───┘              └───┘             └───┘ └───┘
   只返回      A 全保留，B 补充     B 全保留，A 补充    全部保留
   匹配行      无匹配补 NULL        无匹配补 NULL      无匹配补 NULL

示例表

-- 学生表
CREATE TABLE students (
    id   INT PRIMARY KEY,
    name VARCHAR(20)
);
INSERT INTO students VALUES (1, ''张三''), (2, ''李四''), (3, ''王五'');

-- 成绩表
CREATE TABLE scores (
    student_id INT,
    subject    VARCHAR(20),
    score      INT
);
INSERT INTO scores VALUES
    (1, ''语文'', 85), (1, ''数学'', 90),
    (2, ''语文'', 78), (3, ''英语'', 88);

INNER JOIN（内连接）

只返回两表都有匹配的行。

SELECT s.name, sc.subject, sc.score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id;

-- 结果：
-- 张三 | 语文 | 85
-- 张三 | 数学 | 90
-- 李四 | 语文 | 78
-- 王五 | 英语 | 88

LEFT JOIN（左连接）

左表全保留，右表无匹配时补 NULL。

SELECT s.name, sc.subject, sc.score
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id;

-- 结果：张三、李四、王五 都在
-- 注意：李四只有一条成绩，王五只有一条

多表连接

SELECT
    s.name,
    sc.subject,
    sc.score,
    g.grade_level
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id
LEFT JOIN grade_standard g ON sc.score BETWEEN g.min_score AND g.max_score;

聚合函数与分组

常用聚合函数

函数 说明 示例
COUNT() 计数 COUNT(*), COUNT(DISTINCT col)
SUM() 求和 SUM(score)
AVG() 平均值 AVG(score)
MAX() / MIN() 最大/最小 MAX(score)

GROUP BY 分组

-- 每个学生的平均分
SELECT
    student_id,
    AVG(score) AS avg_score,
    COUNT(*)   AS subject_count
FROM scores
GROUP BY student_id;

HAVING 过滤分组

WHERE 过滤行，HAVING 过滤分组结果。

-- 找出平均分 >= 80 的学生
SELECT
    student_id,
    AVG(score) AS avg_score
FROM scores
GROUP BY student_id
HAVING AVG(score) >= 80;

💡 执行顺序口诀：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

子查询

标量子查询（返回单个值）

-- 查询分数高于平均分的学生成绩
SELECT * FROM scores
WHERE score > (SELECT AVG(score) FROM scores);

行子查询 + IN

-- 查询有成绩记录的学生
SELECT * FROM students
WHERE id IN (SELECT DISTINCT student_id FROM scores);

EXISTS 子查询

-- 查询至少有一门成绩的学生（比 IN 效率更高）
SELECT * FROM students s
WHERE EXISTS (
    SELECT 1 FROM scores sc WHERE sc.student_id = s.id
);

FROM 子句中的子查询（派生表）

SELECT avg_score_table.student_id, avg_score_table.avg_s
FROM (
    SELECT student_id, AVG(score) AS avg_s
    FROM scores
    GROUP BY student_id
) AS avg_score_table
WHERE avg_score_table.avg_s > 80;

公用表表达式 CTE（更清晰的子查询）

WITH avg_scores AS (
    SELECT student_id, AVG(score) AS avg_s
    FROM scores
    GROUP BY student_id
)
SELECT s.name, a.avg_s
FROM students s
JOIN avg_scores a ON s.id = a.student_id
WHERE a.avg_s > 80;

视图

视图是对查询的封装，像一个”虚拟表”。简化复杂查询，不存储数据。

-- 创建视图
CREATE VIEW student_summary AS
SELECT
    s.id,
    s.name,
    COUNT(sc.subject) AS subject_count,
    AVG(sc.score)     AS avg_score
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id
GROUP BY s.id, s.name;

-- 像查表一样查视图
SELECT * FROM student_summary WHERE avg_score > 80;

-- 删除视图
DROP VIEW IF EXISTS student_summary;

索引与性能

索引是 SQL 性能调优最重要的手段，相当于书的目录。

创建索引

-- 单列索引
CREATE INDEX idx_users_email ON users(email);

-- 唯一索引（同时保证唯一性）
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 复合索引（多列联合，注意"最左前缀"原则）
CREATE INDEX idx_scores_student_subject ON scores(student_id, subject);

什么时候加索引？

  • ✅ WHERE 条件中频繁过滤的列
  • ✅ JOIN 的关联列
  • ✅ ORDER BY / GROUP BY 的列
  • ❌ 数据量很小的表（几百行）
  • ❌ 频繁更新的列（维护成本高）
  • ❌ 区分度很低的列（如性别，只有男/女两种值）

用 EXPLAIN 分析查询

EXPLAIN SELECT * FROM users WHERE email = ''test@example.com'';
-- 看 type 列：ALL（全表扫描）→ index → range → ref → const（最优）

事务

事务保证一组操作要么全部成功，要么全部回滚。

ACID 特性

特性 含义
Atomicity 原子性 操作不可分割
Consistency 一致性 数据状态前后一致
Isolation 隔离性 并发事务互不干扰
Durability 持久性 提交后数据永久保存

基本用法

-- 开启事务
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 如果没问题就提交
COMMIT;

-- 如果有问题就回滚
ROLLBACK;

常见陷阱与最佳实践

陷阱 🕳️

  1. NULL 的三值逻辑

    -- 错误！NULL 不能用 = 比较
SELECT * FROM users WHERE email = NULL;           -- ❌ 永远为空

-- 正确
SELECT * FROM users WHERE email IS NULL;           -- ✅

  2. WHERE vs HAVING 混淆

    -- 错误！WHERE 中不能用聚合函数
SELECT student_id, AVG(score) FROM scores WHERE AVG(score) > 80 GROUP BY student_id;  -- ❌

-- 正确
SELECT student_id, AVG(score) FROM scores GROUP BY student_id HAVING AVG(score) > 80; -- ✅

  3. DELETE / UPDATE 忘加 WHERE

    -- 养成先 SELECT 确认的习惯
SELECT * FROM users WHERE id = 1;     -- 先看要删哪些
DELETE FROM users WHERE id = 1;       -- 确认无误再执行

  4. COUNT(*) vs COUNT(col)

    COUNT(*)     -- 统计所有行（含 NULL）
COUNT(col)   -- 统计 col 非 NULL 的行数

最佳实践 ✅

  1. 永远显式写出列名，别用 SELECT *
  2. 别名要语义化：AS avg_score 而不 AS a
  3. SQL 关键字大写，表名列名小写，方便阅读
  4. 复杂查询拆成 CTE，逐步调试
  5. 生产环境先用 EXPLAIN 分析，避免慢查询拖垮数据库
  6. 敏感操作先开事务，确认无误再 COMMIT

结语

SQL 是一门”上手容易、精通难”的语言。基础 CRUD 一天就能学会，但要写出高性能、可维护的查询，需要持续练习和对数据库原理的理解。

这份笔记我会持续更新，当作自己的速查手册。

sunrtnj@163.com

©2019-2025

SunRT个人主页