基本SQL语句

SELECT

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

其中，条件可以用AND,OR,括号链接，也可以选择范围WHERE XXX IN (‘choice1’, ‘choice2’)
属性可以用maxmincount round(属性，3)修饰

查询

  • xx为空 xx is NULL
  • 查询结果去重 SELECT DISTINCT xx FROM table
  • varchar长度字符数 char_length(xx) > 5

GROUP BY 分组

SELECT 后面出现的非聚合字段，必须全部写进 GROUP BY 中。
GROUP BY 之后的结果集结构是：
行数 = 分组数（用多少种user_id）
列包括：分组列（user_id）和聚合列（order_count、total_amount）
注意两表连接方式：本例中，“平均答题数”分母是“大学总人数”，人数来源于用户表，所有用户必须计入，所以用户表是主表。
如果题目改成“计算有答题记录的用户的平均答题数”，则可以用 INNER JOIN（交集）。

COUNT(*) 统计每组的总行数（包括该列中为 NULL 的行）。
COUNT(列名) 统计该列非 NULL 值的行数。
你也可以对多个字段分组：GROUP BY 字段1, 字段2。
计数时候注意：去重只计算种类数时，要加DISTINCT，否则COUNT的是所有行的行数

多表连接查询

一定要注意所有的属性都要带表名.属性名！
INNER/LEFT/RIGHT/FULL JOIN 表名 ON 连接方式，
看到所有记录 找LEFT JOIN GROUP BY
你的需求,限制条件放哪？WHERE 还是 ON后面？

需求类型 条件放置位置 说明
必须保留左表全部名单 ON 后面 如：统计全班成绩，包括缺考的
只需要特定范围的结果 WHERE 后面 如：只看 2015 年的数据，不看其他的
对左表（主表）本身的限制 WHERE 后面 如：只要“三年级”的学生
  1. 内连接 (INNER JOIN) —— 只返回两个表中匹配成功的行（取交集）
  2. LEFT JOIN （以左表FROM后面的主表为准）。返回左表（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. 自连接

FROM 表名 取名1
JOIN 表名 取名2
当要对同一张表的数据进行某个条件的对比时，一般使用自联结

ON后面的条件确定连接到同一行的条件
比昨天温度更高的记录

1
2
3
4
# Write your MySQL query statement below
SELECT today.id FROM Weather today
JOIN Weather yesterday
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;

EXIST

例题：
有一个船员租赁船只的系统，表结构如下：Sailors(sid, sname, rating, age); Boats(bid, bname, color); Reserves(sid, bid, day)，
用嵌套査询完成以下两个査询，并从 SQL 优化的角度分析如何根据一些过滤条件的好坏来选择使用不同嵌套查询关键字。

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

注意：关联外层只能用where on只能连接两表的属性

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

1
2
WHERE EXISTS (子查询)

执行逻辑
对于外层查询的每一行，数据库执行子查询，判断是否至少存在一条满足条件的记录。

一旦找到一条匹配记录，EXISTS 立即返回 TRUE，并停止继续扫描（可提前终止，提高效率）。

NOT EXISTS 则相反：如果子查询扫描完所有记录都没有找到匹配行，则返回 TRUE。

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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;


--- 用嵌套查询
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 EXIST 逻辑相反：如果子查询没有返回任何行，则 NOT EXISTS 为 TRUE。

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

双重 NOT EXISTS 的核心思想是：
对于每个水手，找出他没有预订的船。
如果不存在这样的船，则他预定了所有船。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT sname
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) 用种类计数来判断
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)

条件函数

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
5
6
/* Oracle and DB2(MySQL已经支持了) */
select ename, sal,
  sum(sal) over (order by sal,empno) --这个窗口按sal，empno排序
    as running_total
    from emp
order by sal

计算众数

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

1
2
3
4
5
6
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
4
5
/* Oracle */
select median(sal)
  from emp
where deptno=20

去掉最大最小的均值

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

1
2
3
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
9
/* MySQL */
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
2
3
4
5
6
7
8
9
/* MySQL */
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

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

lead()OVER () 窗口函数 获取当前行后面一行的 salary 值（即紧随其后的员工工资）。salary - LEAD(salary) 就是当前员工工资减去后一个员工工资，即为所求差值。最后一名员工因无后续行，结果为 NULL，符合题目要求的“N/S”。

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

17

职员表EMP（eno，ename，job，hire，salary，dno）eno是员工编号，dno是部门编号，hire是雇佣日期就是进公司的日期。
部门表DEPT（dno，dname，loc）这个loc是啥意思我也忘了······

  1. dname=RI的员工工资的中间数。中间数就是若有九个员工，将员工工资从大到小排列，中间数就是第五个，若有十个就是从大到小排列的第五和第六个数的平均数。
1
2
3
--1
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排除salary在该部门中（用内外值相等连接）最大值、最小值

1
2
3
4
5
--2
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

18.1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT ename,age FROM Emp e JOIN Works w ON e.eid = w.eid 
WHERE EXISTS  (SELECT d.did FROM Dept d WHERE dname = '硬件' AND d.did = w.did) 
AND EXISTS (SELECT d.did FROM Dept d WHERE dname = '软件' AND d.did = w.did);



SELECT eid FROM Emp e JOIN Works w ON e.eid = w.eid
WHERE w.did in (SELECT did FROM Dept d WHERE d.budget > e.salary*20%)

SELECT managerid FROM Dept d 
ORDER BY budget DESC LIMIT 5

SELECT managerid FROM Dept d
GROUP BY managerid HAVING SUM(budget) > 500000

18.2

18.3

考虑以下数据库设计模式，orderstatus 是记录订单状态的表，主键是oid 和status 的复合键，订单
包含多种状态，同事某一订单可能会有某一状态多次出现的情况，比如换货的过程。
Customers(cid, cname, city)
Orders(oid,cid,date)
Orderstatus(oid,status,statusdate)
请完成查询，列出所有尚未标记为完成状态的订单的以下字段：oid，canme，订单的最后状态status
以及设置状态的时间statusdate。

注意窗口函数是为每一行都新创建一份数据，所以要去重

1
2
3
4
5
6
7
8
9
10
SELECT o.oid,c.cname, latest.status,latest.statusdate
FROM Orders o JOIN Customers c ON o.cid = c.cid
JOIN (
    SELECT DISTINCT os1.oid,FIRST_VALUE(os1.status) OVER (PARTITION BY os1.oid ORDER BY os1.statusdate DESC) AS status,
    FIRST_VALUE(os1.statusdate) OVER (PARTITION BY os1.oid ORDER BY os1.statusdate DESC) AS statusdate 
    FROM Orderstatus os1 
) AS latest ON latest.oid=o.oid
WHERE NOT EXISTS (
SELECT 1 FROM Orderstatus os WHERE os.oid = o.oid AND os.status = '完成'
)

19.1

使用 INSERT INTO … SELECT 结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
INSERT INTO bill b (bill_id,saler_id,com_id,purchase_quantity,total_price,bill_time) SELECT(
    1,
    1,
    1,
    1 * (SELECT price FROM commodity WHERE com_id = 1),
    NOW()
)

SELECT 
    s.saler_id, 
    s.saler_name, -- 建议带上名字，老板看着更方便
    COUNT(b.bill_id) AS bill_count, 
    IFNULL(SUM(b.total_price), 0) AS daily_sum -- 处理没开单的情况，显示为0
FROM saler s
-- 注意：这里过滤日期要写在 JOIN 的 ON 条件里，否则 LEFT JOIN 会失效变成 INNER JOIN
LEFT JOIN bill b ON s.saler_id = b.saler_id AND DATE(b.bill_time) = '2015-11-11'
WHERE s.store_id = 'MCS_A'
GROUP BY s.saler_id, s.saler_name;

SELECT 
    st.store_name, 
    st.store_floor, 
    st.store_id, 
    SUM(b.total_price) AS daily_amount
FROM store st
JOIN commodity c ON st.store_id = c.store_id  -- 桥梁：商品表
JOIN bill b ON c.com_id = b.com_id             -- 关联：账单表
WHERE DATE(b.bill_time) = '2015-11-11'
GROUP BY st.store_id, st.store_name, st.store_floor
ORDER BY daily_amount DESC                     -- 按金额倒序
LIMIT 1;                                       -- 取第一名

19.2

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

记住这个公式：

LEFT JOIN + ON = “如果不符合条件，请留个空位(NULL) 给我，但别把我删了”。

LEFT JOIN + WHERE = “如果不符合条件，整行直接消失”。

区别：第一问 要查所有学生，选课课程要在连接表ON时候过滤，这样能给学生留出空位
第二问，查所有符合条件的课程

1
2
3
4
5
6
7
8
9
10
SELECT s.student_id, s.student_name, student_grade,COUNT(DISTINCT c.course_id) AS course_count
FROM student s LEFT JOIN course_election ce ON s.student_id = ce.student_id
LEFT JOIN course c ON ce.course_id = c.course_id AND c.term = 1 AND c.year='2015' 
WHERE s.student_grade = '大三'

CREATE VIEW course_view AS 
SELECT c.course_id,c.course_name,c.teacher_id,c.term,c.year, IFNULL(COUNT(ce.student_id),0) AS student_count
FROM course c LEFT JOIN course_election ce ON c.course_id = ce.course_id 
WHERE c.term = 1 AND c.year='2015'
GROUP BY c.course_id,c.course_name,c.teacher_id,c.term,c.year