SQL 朝花夕拾 [保持更新]

SQL 朝花夕拾

最后更新：2022.09.13

MySQL 高级

Navicat

1. SQL 去重

1. DISTINCT

SELECT
DISTINCT director
FROM movies;

1. GROUP BY

SELECT director
FROM movies
GROUP BY director;

2. 查找前 n 条数据

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

# MySQL
SELECT *
FROM tablename
LIMIT n OFFSET num_offset;

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

LIMIT num_offset, n

坑： num_offset 从 0 开始！

3. 连接查询

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

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

在表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;

方法 2：嵌套查询

SELECT DISTINCT building_name,Capacity
FROM Buildings
WHERE building_name IN (SELECT DISTINCT building
						FROM Employees);

总结

• 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;

5. GROUP BY 分组细节

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

6. HAVING 和 WHERE

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

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

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;

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;

1393. 股票的资本损益

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

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;

9. IFNULL 函数

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

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

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

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

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
    );

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