面向 SQL 刷题和数据库开发技术复习，偏速查笔记风格。

基本SQL语句

SELECT

1
2
3
4
5
6
7
SELECT 属性
FROM 表名
JOIN 表名 ON 连接方式
WHERE 条件
GROUP BY 属性 HAVING 条件
ORDER BY 属性
LIMIT 数量

说明：

  • 条件可以用 AND、OR、括号连接，也可以写 WHERE xxx IN ('choice1', 'choice2')。
  • 属性可以用 MAX、MIN、COUNT、ROUND(属性, 3) 等函数修饰。

查询

  • 判空：xx IS NULL
  • 查询结果去重：SELECT DISTINCT xx FROM table
  • varchar 长度字符数：CHAR_LENGTH(xx) > 5

GROUP BY 分组

  • SELECT 后面出现的非聚合字段，必须全部写进 GROUP BY 中。
  • GROUP BY 之后的结果集结构：
    • 行数 = 分组数
    • 列包括：分组列和聚合列
  • COUNT(*) 统计每组的总行数，包括该列为 NULL 的行。
  • COUNT(列名) 统计该列非 NULL 值的行数。
  • 也可以对多个字段分组：GROUP BY 字段1, 字段2
  • 如果只想统计种类数，要加 DISTINCT，否则 COUNT 统计的是总行数。

连接时注意主表选择：

  • “平均答题数”的分母如果是“大学总人数”，人数来源于用户表，所有用户都必须计入，所以用户表是主表。
  • 如果题目改成“计算有答题记录的用户的平均答题数”，则可以用 INNER JOIN。

多表连接查询

一定要注意所有属性尽量写成 表名.属性名。

常见连接：

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN

看到“保留所有记录”时，优先考虑 LEFT JOIN。

你的需求限制条件放哪，关键看是放在 WHERE 还是 ON 后面。

需求类型 条件放置位置 说明
必须保留左表全部名单 ON 后面 如：统计全班成绩，包括缺考的
只需要特定范围的结果 WHERE 后面 如：只看 2015 年的数据，不看其他的
对左表（主表）本身的限制 WHERE 后面 如：只要“三年级”的学生

  1. 内连接 INNER JOIN
    只返回两个表中匹配成功的行，也就是取交集。

  2. LEFT JOIN
    以 FROM 后面的左表为准，返回左表的所有记录。即使右表中没有匹配，结果中右表字段也会显示为 NULL。

如果左表一行记录，在右表中找到了 N 行满足 ON 条件的记录，那么结果中左表这一行会重复出现 N 次。

注意：多表连接想保留第一个表，要使用 LEFT JOIN 链式连接。

如下：列出所有学生。如果他有班级，就显示班级名；如果没有班级，班级那一栏就空着（NULL）。

1
2
3
SELECT Students.name, Classes.class_name
FROM Students
LEFT JOIN Classes ON Students.class_id = Classes.class_id;
  1. 自连接
1
2
FROM 表名 别名1
JOIN 表名 别名2

当要对同一张表的数据进行条件对比时，一般使用自连接。

ON 后面的条件用来确定“哪两行应该被配对”。

例：比昨天温度更高的记录。

1
2
3
4
5
6
SELECT today.id
FROM Weather today
JOIN Weather yesterday
    ON DATEDIFF(today.recordDate, yesterday.recordDate) = 1
WHERE DATEDIFF(today.recordDate, yesterday.recordDate) = 1
    AND yesterday.temperature < today.temperature

自连接本质上是将一张表当作两张独立的表来处理，通过别名区分。在本题中，我们把“开始记录”和“结束记录”看成两个不同的集合，用连接条件将它们关联到同一行，从而可以计算每对的时间差。这就是自连接解决“行转列”或“配对”问题的典型应用。

1
2
3
4
5
6
7
8
9
10
SELECT 
    a.machine_id,
    ROUND(AVG(b.timestamp - a.timestamp), 3) AS processing_time
FROM Activity a
JOIN Activity b 
    ON a.machine_id = b.machine_id 
    AND a.process_id = b.process_id
    AND a.activity_type = 'start' 
    AND b.activity_type = 'end'
GROUP BY a.machine_id;

EXISTS

例题：

有一个船员租赁船只的系统，表结构如下：

  • Sailors(sid, sname, rating, age)
  • Boats(bid, bname, color)
  • Reserves(sid, bid, day)

用嵌套查询完成以下查询，并从 SQL 优化角度分析如何根据过滤条件的好坏选择不同嵌套查询关键字。

  1. 找出年龄在 35 岁以上、一个月内没有预定红色船只的水手名字。
  2. 找出年龄在 35 岁以上、一个月内同时预定了红色船和绿色船的水手姓名。
  3. 附加题：找出预定了所有船的水手名字。

注意：关联外层通常写在 WHERE 中；ON 主要连接两表属性。

EXISTS 的基本用法

EXISTS 检查子查询是否至少返回一行。语法：

1
WHERE EXISTS (子查询)

执行逻辑：

  • 对于外层查询的每一行，数据库执行子查询，判断是否至少存在一条满足条件的记录。
  • 一旦找到一条匹配记录，EXISTS 立即返回 TRUE，并停止继续扫描。
  • NOT EXISTS 则相反：如果子查询扫描完所有记录都没有找到匹配行，则返回 TRUE。

解：1. 找出一个月内预订过红色船的 35 岁以上的水手（用 EXISTS）

1
2
3
4
5
6
7
8
9
10
SELECT sname
FROM Sailors s
WHERE EXISTS (
    SELECT 1
    FROM Reserves r
    JOIN Boats b ON r.bid = b.bid
    WHERE r.sid = s.sid AND b.color = 'red'
    AND r.day >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
)
AND s.age > 35;

用嵌套查询：

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT sname
FROM Sailors s
WHERE s.age > 35
AND sid IN (
    SELECT sid
    FROM Reserves r
    WHERE bid IN (
        SELECT bid
        FROM Boats
        WHERE color = 'red'
    )
    AND r.day >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
)

思路：

  1. 先找出所有红色的船（从 Boats 表得到 bid）。
  2. 再找出预订了这些红色船的水手（从 Reserves 表得到 sid）。
  3. 最后在这些水手里筛选年龄大于 35 岁的水手（从 Sailors 表得到 sname）。

解释：

  1. 对于 Sailors 表的每一行（每个水手 s），执行子查询，查找 Reserves 表中是否存在一条记录满足 r.sid = s.sid 且对应的船是红色。
  2. 如果找到至少一条，则 EXISTS 为 TRUE，该水手被选中；否则不选。
  3. 注意，子查询中的 SELECT 1 只是占位，因为只关心有无返回行。

NOT EXISTS 逻辑相反：如果子查询没有返回任何行，则 NOT EXISTS 为 TRUE。

关系除法是关系代数中的一种操作，用于回答“对于所有”类型的查询。它通常用于找出那些与另一个集合中的每一个元素都有关联的元素。

双重 NOT EXISTS 的核心思想：

  • 对于每个水手，找出他没有预订的船。
  • 如果不存在这样的船，则他预定了所有船。
1
2
3
4
5
6
7
8
9
10
11
SELECT sname
FROM Sailors s
WHERE NOT EXISTS (
    SELECT *
    FROM Boats b
    WHERE NOT EXISTS (
        SELECT *
        FROM Reserves r
        WHERE r.sid = s.sid AND r.bid = b.bid
    )
)

另解：COUNT(*) = (SELECT COUNT(*) FROM Boats) 用种类计数来判断。

1
2
3
4
5
6
7
8
SELECT sname
FROM Sailors s 
JOIN
(SELECT r.sid, COUNT(DISTINCT r.bid) AS book_cnt
 FROM Reserves r
 GROUP BY r.sid) AS t
ON t.sid = s.sid
WHERE t.book_cnt = (SELECT COUNT(*) FROM Boats)

条件函数

1
2
3
4
5
CASE
    WHEN XXX THEN XXX
    WHEN XXX THEN XXX
    ELSE XXX
END AS 别名
  • SELECT IF(条件, 条件为真时的值, 条件为假时的值) AS 别名
  • SUM(IF(条件, 条件为真时的值, 条件为假时的值)) AS 别名：条件计数
1
2
3
4
5
6
7
8
SELECT 
    name,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age >= 18 AND age < 60 THEN '成年人'
        ELSE '老年人'
    END AS age_group
FROM Users;

日期函数

DATE(b.bill_time) = '2015-11-11'：锁定日期。

如果 date 是日期类型属性，则：

  • YEAR(date) 返回年份
  • MONTH(date) 返回月份
  • DAY(date) 返回日期
  • DATEDIFF(date1, date2) 返回两个日期之间的天数差（date1 - date2）
  • TIMESTAMPDIFF(interval, datetime1, datetime2) 返回datetime2 - datetime1 的差值interval 指定时间单位（YEAR、MONTH、DAY、HOUR、MINUTE、SECOND 等）。例如：
    TIMESTAMPDIFF(YEAR, date1, date2) 返回两个日期之间的年

窗口函数

窗口函数会保留原有行数，并在每一行上计算一个聚合值。示例：

1
2
3
4
SELECT e.ename, e.dno, d.dname, e.salary AS sal, e.hire,
       FIRST_VALUE(e.salary) OVER (PARTITION BY e.dno ORDER BY e.hire DESC) AS latest_salary
FROM EMP e
JOIN DEPT d ON e.dno = d.dno;
  • PARTITION BY dno：将数据按部门分成独立的窗口。
  • ORDER BY hire DESC：在每个部门窗口内按入职日期倒序排列。
  • FIRST_VALUE(salary) / SUM(...) 等：用于取窗口中的值或做聚合。
  • 排序函数：
    • ROW_NUMBER()：排序序号
    • RANK()：排名，相同值排名相同，后续排名跳过
    • DENSE_RANK()：密集排名，相同值排名相同，后续排名不跳过

这个值会附加到每一行上，而不会减少行数。最终结果仍然是每个员工一行，同时每一行都带有该部门的最新工资。

其他窗口函数：

  • LAG(salary, 1, 0) OVER (PARTITION BY dept ORDER BY hire)：获取同一部门前一个员工的工资，若没有则返回 0
  • LEAD(salary, 1, 0) OVER (PARTITION BY dept ORDER BY hire)：获取同一部门后一个员工的工资，若没有则返回 0
  • FIRST_VALUE(column)：获取窗口内第一行的列值

与 GROUP BY 的区别

对比项 GROUP BY 窗口函数
行数 减少（压缩） 保持不变
作用 聚合整个组 对窗口内计算
输出 每组一行 每行都显示结果
典型场景 统计报表 排名、累计、移动平均

数据库开发技术-SQL题

数值问题

累计求和

窗口内的 ORDER BY 要能唯一确定行的顺序，通常做法是 ORDER BY 排序键, 主键。这样累积和才是确定且正确的，外层 ORDER BY 可以独立控制显示。

牢记：窗口函数是为每一行创建一个数据，这个数据根据窗口限定条件计算得出。

1
2
3
4
SELECT ename, sal,
       SUM(sal) OVER (ORDER BY sal, empno) AS running_total
FROM emp
ORDER BY sal;

计算众数

记忆：GROUP BY sal HAVING COUNT(*) >= ALL(子查询中的 COUNT)。子查询会计算每个工资出现的次数，返回一个数量列表，ALL 表示与列表中的所有值比较。

1
2
3
4
5
6
7
8
9
10
SELECT sal
FROM emp
WHERE deptno = 10
GROUP BY sal 
HAVING COUNT(*) >= ALL (
    SELECT COUNT(*)
    FROM emp
    WHERE deptno = 10
    GROUP BY sal
)

中位数

1
2
3
SELECT MEDIAN(sal)
FROM emp
WHERE deptno = 20

去掉最大最小的均值

记忆：NOT IN 最大值和最小值的子查询。

1
2
3
4
SELECT AVG(sal)
FROM emp
WHERE sal NOT IN (SELECT MAX(sal) FROM emp)
  AND sal NOT IN (SELECT MIN(sal) FROM emp)

日期

日期加减

记忆：INTERVAL 5 DAY / MONTH / YEAR

1
2
3
4
5
6
7
8
SELECT hiredate - INTERVAL 5 DAY   AS hd_minus_5D,
       hiredate + INTERVAL 5 DAY   AS hd_plus_5D,
       hiredate - INTERVAL 5 MONTH AS hd_minus_5M,
       hiredate + INTERVAL 5 MONTH AS hd_plus_5M,
       hiredate - INTERVAL 5 YEAR  AS hd_minus_5Y,
       hiredate + INTERVAL 5 YEAR  AS hd_plus_5Y
FROM emp
WHERE deptno = 10

日期差距

记忆：DATEDIFF(date1, date2)
DATE(b.bill_time) = '2015-11-11'：锁定日期。

如果 date 是日期类型属性，则：

  • YEAR(date) 返回年份
  • MONTH(date) 返回月份
  • DAY(date) 返回日期
  • DATEDIFF(date1, date2) 返回两个日期之间的天数差（date1 - date2）
  • TIMESTAMPDIFF(interval, datetime1, datetime2) 返回datetime2 - datetime1 的差值interval 指定时间单位（YEAR、MONTH、DAY、HOUR、MINUTE、SECOND 等）。例如：
    TIMESTAMPDIFF(YEAR, date1, date2) 返回两个日期之间的年
1
SELECT DATEDIFF(date1, date2)

易忘：与下一条数据的差距

LEAD() OVER () 可以获取当前行后面一行的值。
例如 salary - LEAD(salary) 就是当前员工工资减去后一个员工工资。最后一名员工因无后续行，结果为 NULL，符合题目要求的 N/S。

常见场景：

  • 计算当前记录和下一条记录之间的日期差
  • 计算 deptno = 10 的部门中，每一个员工与下一位员工入职时间相差多少天

例题

职员表：EMP(eno, ename, job, hire, salary, dno)
其中 eno 是员工编号，dno 是部门编号，hire 是进公司日期。

部门表：DEPT(dno, dname, loc)
loc 应该是部门地点。

  1. dname = 'RI' 的员工工资中位数。若有九个员工，工资从大到小排列，中间数就是第五个；若有十个员工，则是第五和第六个的平均数。
1
2
SELECT MID(e.salary) FROM EMP e JOIN DEPT d ON e.dno = d.dno
WHERE dname = 'RI'

扩展：各部门内员工工资的众数

思路：用 GROUP BY 分部门，统计每个部门每个工资的数量，找出数量最大的那个工资。如果有多个众数，则取其中一个即可。

1
2
3
SELECT e.dname, e.salary AS mode_salary
FROM EMP e JOIN DEPT d ON e.dno = d.dno
GROUP BY e.dno, e.salary
  1. 各部门除去最高最低工资外的部门所有员工的平均工资。

思路：用 NOT IN 排除该部门中的最大值和最小值。

1
2
3
4
SELECT AVG(e.salary) FROM EMP e
WHERE e.salary NOT IN (SELECT MAX(e2.salary) FROM EMP e2 WHERE e2.dno = e.dno)
AND e.salary NOT IN (SELECT MIN(e3.salary) FROM EMP e3 WHERE e3.dno = e.dno)
GROUP BY e.dno
  1. 每个部门第一个进部门和最后一个进部门的员工的时间上的时间差换算成年是差几年，换算成月又是差几个月。
1
2
3
SELECT TIMESTAMPDIFF(YEAR,MIN(e.hire),MAX(e.hire)) AS year_diff,
TIMESTAMPDIFF(MONTH,MIN(e.hire),MAX(e.hire)) AS month_diff
FROM EMP e
  1. 计算每个员工的 dno、ename、salary，以及与同一部门员工（DEPTNO 值相同）的工资差。该差值在当前员工及同部门内紧随其后的员工之间计算而来，对最后聘用的员工这个差值是 N/S。
1
2
3
4
5
6
SELECT 
    dno,
    ename,
    salary,
    IFNULL(salary - LEAD(salary) OVER (PARTITION BY dno ORDER BY hire), 'N/S') AS salary_diff
FROM EMP;
  • PARTITION BY dno：按部门分组，每个部门独立计算。
  • ORDER BY hire：按入职日期升序排列，最早的员工在前，最晚的在后。
  • LEAD(salary)：获取当前行后面一行的 salary 值。
  • salary - LEAD(salary)：当前员工工资减去后一个员工工资。最后一名员工因无后续行，结果为 NULL，符合题目要求的 N/S。
  1. 每个部门中所有员工的 ename、dno、dname、sal、hire、latest_salary。这里的 latest_salary 可以理解为该员工所在部门最新入职员工的工资。
1
2
3
SELECT e.ename,e.dno,d.dname,e.sal,e.hire,
FIRST_VALUE(e.salary) OVER (PARTITION BY e.dno ORDER BY e.hire DESC) AS latest_salary
FROM EMP e JOIN DEPT d ON e.dno = d.dno