SQL 朝花夕拾 [保持更新]

SQL 问题记录

最后更新：2022.10.18

Navicat

0. MySQL

MySQL 高级

Mysql 下载地址

Mysql 创建用户并授权

1. SQL 去重

1. DISTINCT

SELECT 
DISTINCT director 
FROM movies;
1
2
3

2. GROUP BY

SELECT director 
FROM movies 
GROUP BY director;
1
2
3

2. 查找前 n 条数据

从 num_offset 开始查找前 n 条数据。

# MySQL
SELECT * 
FROM tablename 
LIMIT n OFFSET num_offset;
1
2
3
4

限制条件可以更换为以下 SQL，表示取从 num_offset 开始前 n 条数据：

LIMIT num_offset, n
1

坑： num_offset 从 0 开始！

3. 连接查询

INNER JOIN 取两个集合的交集，也即自然连接：

SELECT *
FROM movies as m INNER JOIN Boxoffice as b 
				 ON m.id = b.movie_id;
1
2
3

在表A 连接 B, LEFT JOIN 保留A的所有行，不管有没有能匹配上B 反过来 RIGHT JOIN 则保留所有 B 里的行。最后 FULL JOIN 不管有没有匹配上，同时保留 A 和 B 里的所有行。

找到所有有雇员的办公室(buildings)和对应的容量：
方法 1：LEFT JOIN

SELECT building, Capacity
FROM  Buildings as b LEFT JOIN employees as e 
					 ON b.Building_name = e.building
WHERE building IS NOT NULL
GROUP BY building;
1
2
3
4
5

方法 2：嵌套查询

SELECT DISTINCT building_name,Capacity 
FROM Buildings
WHERE building_name IN (SELECT DISTINCT building 
						FROM Employees);
1
2
3
4

总结

• INNER JOIN：适用于两个表值都存在。

• 外连接：适用于附表中值可能存在 NULL 的情况。

1. A INNER JOIN B：取交集
2. A LEFT JOIN B：取 A 全部，B 没有对应的值，则为 NULL
3. A RIGHT JOIN B：取 B 全部，A 没有对应的值，则为 NULL
4. A FULL JOIN B：取并集，彼此没有对应的值为 NULL

上述 4 种的对应条件，在 ON 后填写。

4. 不选定 NULL 值

WHERE col_name IS NOT NULL

SELECT Building
FROM employees 
WHERE building IS NOT NULL
GROUP BY building;
1
2
3
4

5. GROUP BY 分组细节

GROUP BY 分组会自动根据我们分组的字段进行排序，默认升序。

6. HAVING 和 WHERE

在 GROUP BY 分组语法中，我们知道数据库是先对数据做WHERE，然后对结果做分组，如果我们要对分组完的数据再筛选出几条如何办？ （想一下按年份统计电影票房，要筛选出票房 > 100万的年份？）
HAVING 语法将用来解决这个问题，他可以对分组之后的数据再做 SELECT 筛选。

SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM mytable
WHERE condition
GROUP BY column
HAVING group_condition;
1
2
3
4
5

按导演分组计算销售总额,求出平均销售额冠军（统计结果过滤掉只有单部电影的导演，列出导演名，总销量，电影数量，平均销量)：

SELECT Director, SUM(Domestic_sales+International_sales) AS Total,
                 COUNT(*) AS CNT, 
                 SUM(Domestic_sales+International_sales)/COUNT(*) AS Per_sales
FROM Movies INNER JOIN Boxoffice ON Movies.id = Boxoffice.movie_id
GROUP BY Director
HAVING CNT > 1
ORDER BY Per_sales DESC
LIMIT 1;
1
2
3
4
5
6
7
8

7. 判断语句

判断语句，选择结构：

• CASE WHEN THEN ELSE END
• IF(condition, a, b)

按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计）

 SELECT Role,COUNT(*), CASE 
 						WHEN Building IS NOT NULL THEN 1 
                        ELSE 0 END AS inBuilding
 FROM Employees 
 GROUP BY Role, Building;
1
2
3
4
5

1393. 股票的资本损益

SELECT stock_name, SUM(IF(operation='Buy', -price, price)) AS capital_gain_loss
FROM Stocks 
GROUP BY stock_name;
1
2
3

8. 嵌套查询

找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额：

SELECT Title, 
	   (SELECT MAX(Domestic_sales + International_sales) 
        FROM Boxoffice) - Domestic_sales - International_sales AS maxSales
FROM Movies INNER JOIN Boxoffice ON Movies.id = Boxoffice.movie_id
GROUP BY Title;
1
2
3
4
5

9. IFNULL 函数

584. 寻找用户推荐人
写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都 不是 2：
方法一(IS NULL)，注意 NULL 不能用 = 或者 != 判定：

SELECT name
FROM customer 
WHERE referee_id IS NULL 
   OR referee_id != 2;
1
2
3
4

方法二(IFNULL)，IFNULL(x, y) 表示若 x 为 NULL 则返回 y，若 x 不为 NULL 返回 x：

SELECT name
FROM customer 
WHERE IFNULL(referee_id, 0) != 2;
1
2
3

10. EXITS 用法

EXISTS 是只有 1 个参数的谓词，只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。如果子查询返回任何行，EXISTS 子查询为 TRUE，若为空行则为 FALSE。
183. 从不订购的客户

SELECT Name AS Customers 
FROM Customers AS c
WHERE NOT EXISTS (
    SELECT 1 FROM orders AS o WHERE o.Customerid = c.id
    );
1
2
3
4
5

11. LIMIT 后面不能出现运算

177. 第N高的薪水
写 SQL 那么久，第一次用函数的写法：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N := N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT salary 
      FROM Employee
      GROUP BY salary
      ORDER BY salary DESC
      LIMIT N, 1
  );
END
1
2
3
4
5
6
7
8
9
10
11
12

12. 排名函数

178. 分数排名

SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS 'rank'
FROM Scores;
1
2

1. rank() over：排名相同的两名是并列，但是占两个名次，1 1 3 4 4 6 这种
2. dense_rank() over：排名相同的两名是并列，共占一个名词，1 1 2 3 3 4 这种
3. row_number() over 这个函数不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名 1 2 3 4 5

13. 计算时间差（坑）

千万别用直接相减的方式！！
直接相减会将时间拼接为一个十进制数，然后相减，然后导致结果错误，比如：

2021-10-01 11:00:05 - 2021-10-01 10:59:05 等同于 20211001110005 - 20211001105905 = 4100
1

正确的方式应该用：

TIMESTAMPDIFF(SECOND, start_time, end_time)
1

第一个参数可以设置的值：

FRAC_SECOND：表示间隔是毫秒，SECOND：秒，MINUTE：分钟，HOUR：小时，DAY：天，WEEK：星期，MONTH：月，QUARTER：季度，YEAR：年