MYSQL增删改查操作、查询语句执行顺序、力扣试题解析

一、MYSQL的增删改查操作

注：本一小节参考廖雪峰的MYSQL教程

样例用到的原始数据表如下
（注：如无特别说明，每一步操作均是在原始数据表的基础上）：
classes表：
students表：

*

1.增：

INSERT语句的基本语法是：

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

例如向students表插入若干条新记录：

INSERT INTO students 
(class_id, name, gender, score) 
VALUES
(1, '张三', 'M', 100),
(2, '李四', 'M', 99);

2.删：

DELETE语句的基本语法是：

DELETE FROM <表名> WHERE ...;

例如向students表删除score不小于90的学生：

DELETE FROM students 
WHERE score>=90;

3.改：

UPDATE语句的基本语法是：

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

例如把所有60分以下的同学的成绩加10分：

UPDATE students 
SET score=score+10 
WHERE score<60;

4.查：

4.1 查询数据库表的数据：

SELECT * FROM <表名>

假设表名是students，要查询students表的所有行：

SELECT * FROM students;

4.2 投影查询

查询指定列id, score, name的数据（score points即用points名称重命名score字段）：

SELECT id, score points, name FROM students;

4.3 排序ORDER BY与分页LIMIT OFFSET

查询指定列id, name, name的数据，按score列倒序，gender排序，显示3条数据（从索引0条开始）

SELECT id, name, score FROM students 
ORDER BY score DESC, gender
LIMIT 3 OFFSET 0;

4.4 聚合查询COUNT()、SUM()、AVG()、MAX()、MIN()，参数为字段名

查询表students的样本数据条数（同COUNT(id)，注意此处返回的是一行一列的表，不是数）

SELECT COUNT(*) FROM students;

统计男生的平均成绩

SELECT AVG(score) average FROM students 
WHERE gender = 'M';

4.5 分组查询GROUP BY

（分组查询的结果仅含有被分组的字段、聚合函数COUNT()、SUM()、AVG()、MAX()、MIN()字段）
使用多个列进行分组。例如，我们想统计各班的男生和女生人数

SELECT class_id, gender, COUNT(*) FROM students 
GROUP BY class_id, gender;

4.6 多表查询

SELECT * FROM students, classes;

查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积（部分样本如下）

结果表的重命名

查询一班(s.class_id=1)的男性(s.gender = ‘M’)学生信息，包含班级名称(cname)
通过条件过滤无用样本 s.class_id=c.id;

SELECT s.id sid, s.class_id, s.name, s.gender, s.score, c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND s.class_id= 1 AND s.class_id=c.id;

4.7 连接查询JOIN：将两个表建立起关联后再查询

4.7.1 INNER JOIN

查询所有学生，并包含班级名称cname字段

SELECT s.id, s.name, s.class_id, c.name cname, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

用法：
1>先确定主表，仍然使用FROM <表1>的语法；
2>再确定需要连接的表，使用INNER JOIN <表2>的语法；
3>然后确定连接条件，使用ON <条件…>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
4>可选：加上WHERE子句、ORDER BY等子句(先连接，再筛选排序)。

4.7.2 RIGHT JOIN

SELECT s.id, s.name, s.class_id, c.name cname, s.gender, s.score
FROM students s
RIGHT JOIN classes c
ON s.class_id = c.id;

4.7.3 各种JOIN的区别

(均只返回满足ON条件字段满足的样本，区别在于对某一方该字段空缺时的处理)

INNER JOIN:
只返回同时存在于两张表的行数据，由于students表的class_id包含1，2，3，classes表的id包含1，2，3，4，所以，INNER JOIN根据条件s.class_id = c.id返回的结果集仅包含1，2，3。

RIGHT JOIN:
返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

LEFT JOIN:
则返回左表都存在的行。如果我们给students表增加一行，并添加class_id=5，由于classes表并不存在id=5的行，所以，LEFT JOIN的结果会增加一行，对应的class_name是NULL.

OUTER JOIN:
它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL：

4.8将两个查询结果进行组合（堆叠）在一起：UNION

将多个查询结果进行组合，基本语法：

SELECT expression1, expression2, ... expression_n
FROM tables A
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables B
[WHERE conditions];

例一：
分别查询students表中的id列，class表中的id列，得到两个包含id字段的表，进行组合（UNION对结果去重，UNION ALL不去重）
结果表字段名以左表为准；比较去重时要比较两个表的所有字段值，忽略字段名称。

SELECT s.id sid
FROM students s
UNION
SELECT c.id cid
FROM classes c;

SELECT s.id sid
FROM students s
UNION ALL
SELECT c.id cid
FROM classes c;

例二：
分别查询students表中的id列、score列，class表中的id列、name列，得到两个包含两个字段的表，进行组合（UNION对结果去重，UNION ALL不去重）
结果表字段名以左表为准；比较去重时要比较两个表的所有字段值，忽略字段名称，因为此处没有样本的两个字段均一致，故去重前后返回结果一致。

SELECT s.id sid, s.score score
FROM students s
UNION
SELECT c.id cid, c.name cname
FROM classes c;

SELECT s.id sid, s.score score
FROM students s
UNION ALL
SELECT c.id cid, c.name cname
FROM classes c;

4.9 if null函数的用法

表达式为：IFNULL(expression, alt_value)
如果第一个参数的表达式 expression 为 NULL，则返回第二个参数的备用值

例：查询并返回3班第4高分，如果不存在，则返回60，字段名为forth_score

SELECT IFNULL(
(
SELECT DISTINCT score FROM students
WHERE class_id = 3
ORDER BY score DESC
LIMIT 1 OFFSET 3
)
, 60) forth_score;

二、查询语句执行顺序

执行顺序如下：

(8) SELECT (9)DISTINCT<select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2)         ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
(11) LIMIT <limit_number>

1-3步：进行表的连接操作JOIN：
首先，对left_table、right_table进行笛卡尔乘积；
再针对连接条件ON筛选出对应字段相同的样本；
最后针对连接形式（内外左右连接）对某表在ON中字段未匹配的样本进行处理；

4步：WHERE筛选：优先级还是挺高的

5步：GROUP BY分组：如果应用此功能，后续的操作只能针对分组字段或count、sum、avg、min进行操作。因为分组的结果集中只为每个组包含一行

6步：WITH语句，功能待学习补充

7步：HAVING筛选，类似WHERE，优先级较低

8步：截止目前样本筛选部分基本完成，SELECE语句进行字段选取

9步：DISTINCT针对字段去重

10步：ORDER BY排序

11步：LIMIT、OFFSET 将结果进行分页截取

三、力扣例题：

例175：组合两个表

表1: Person

±------------±--------+
| 列名 | 类型 |
±------------±--------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
±------------±--------+
PersonId 是上表主键

表2: Address

±------------±--------+
| 列名 | 类型 |
±------------±--------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
±------------±--------+
AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

分析解答：

用两表连接JOIN，ON PersonId字段，此处应该左连

SELECT P.FirstName, P.LastName, A.City, A.State
FROM Person P 
LEFT JOIN Address A
ON P.PersonId = A.PersonId

例176：第二高的薪水

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。

±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

±--------------------+
| SecondHighestSalary |
±--------------------+
| 200 |
±--------------------+

分析解答：

ORDER BY、LIMIT OFFSET排序与分页显示
IFNULL函数的用法
注意点：DISTINCT 对相同的Salary去重；倒序排列DESC

SELECT IFNULL(
(
SELECT DISTINCT Salary FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
)
, NULL) SecondHighestSalary;

排名类题目拓展：

1.本题是同薪排名相同，且排名连续问题，可以利用GROUP BY语句对Salary去重（虽然GROUP BY返回的结果集中仅含Salary字段，但原表中本来就只有Salary字段）

SELECT IFNULL(
(
SELECT Salary FROM Employee
GROUP BY Salary
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
)
, NULL) SecondHighestSalary;

2.可以利用两个employee表（e1和e2）的连接JOIN:
首先，对e1、e2进行笛卡尔乘积；
笛卡尔乘积：

再针对连接条件ON筛选出e1.Salary <= e2.Salary的样本；
筛选后：

最后针对连接形式（内外左右连接）对某表在ON中字段未匹配的样本进行处理；此处筛选后的e1.Salary和 e2.Salary样本种类一致，故各种连接形式结果相同；
接着，GROUP BY针对e1.Salary进行分组，并利用聚合函数COUNT()，统计每个分组e2.Salary去重的数目；

最后，HAVING 筛选count(DISTINCT e2.Salary) = 2的样本.

SELECT IFNULL(
(
SELECT e1.Salary FROM Employee e1 
INNER JOIN Employee e2 
ON e1.Salary <= e2.Salary
GROUP BY e1.Salary
HAVING count(DISTINCT e2.Salary) = 2
)
, NULL) SecondHighestSalary;

3.类似连接JOIN ON语句，直接使用两个表的笛卡尔积，然后WHERE筛选

SELECT IFNULL(
(
SELECT e1.Salary FROM Employee e1, Employee e2 
WHERE e1.Salary <= e2.Salary
GROUP BY e1.Salary
HAVING count(DISTINCT e2.Salary) = 2
)
, NULL) SecondHighestSalary;

例178. 分数排名

编写一个 SQL 查询来实现分数排名。

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

±—±------+
| Id | Score |
±—±------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
±—±------+

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

±------±-----+
| Score | Rank |
±------±-----+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
±------±-----+

分析解答

首先想到上面JOIN ON语句加上GROUP BY语句分组的形式，Count(DISTINCT s2.Score)即排名“Rank”，但是此处相同的Score仅出现一次，需要进一步，例如笛卡尔乘积再筛选

SELECT s1.Score, Count(DISTINCT s2.Score) 'Rank' FROM Scores s1 
INNER JOIN Scores s2 
ON s1.Score <= s2.Score
GROUP BY s1.Score

正确解答：
将上述表作为表B（含B_Score、B_Rank 字段），原始表作为表s1；
筛选两表中满足 s1.Score = B.B_Score 条件的s1.Score、B.B_Rank字段，倒序排序即可

SELECT s1.Score, B.B_Rank 'Rank'
FROM Scores s1,
(
SELECT s1.Score B_Score, Count(DISTINCT s2.Score) B_Rank FROM Scores s1 
INNER JOIN Scores s2 
ON s1.Score <= s2.Score
GROUP BY s1.Score
) B
WhERE s1.Score = B.B_Score
ORDER BY s1.Score DESC

例180. 连续出现的数字

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

±—±----+
| Id | Num |
±—±----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
±—±----+

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

±----------------+
| ConsecutiveNums |
±----------------+
| 1 |
±----------------+

分析解答：

三表联合查询，结果表为各表字段相加，样本相乘；
对ID、Num字段进行筛选，结果去重

SELECT distinct A.Num ConsecutiveNums
FROM Logs A, Logs B, Logs C 
WHERE
(
A.Id = B.Id-1 AND A.Id = C.Id+1 AND 
A.Num = B.Num AND A.Num = C.Num
) 

例181. 超过经理收入的员工

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

±—±------±-------±----------+
| Id | Name | Salary | ManagerId |
±—±------±-------±----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
±—±------±-------±----------+

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

±---------+
| Employee |
±---------+
| Joe |
±---------+

分析解答：

两表联合查询，结果表为各表字段相加，样本相乘；
对 e1.ManagerId = e2.Id 和 e1.Salary > e2.Salary 进行筛选

SELECT e1.Name Employee FROM Employee e1, Employee e2
WHERE e1.ManagerId is NOT NULL AND e1.Salary > e2.Salary AND e1.ManagerId = e2.Id

同样地，多表联合查询+WHERE筛选的形式，可以直接替换为JOIN ON 语句实现：

SELECT e1.Name Employee FROM Employee e1
INNER JOIN Employee e2
ON e1.Salary > e2.Salary AND e1.ManagerId = e2.Id

例182. 查找重复的电子邮箱

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

±—±--------+
| Id | Email |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+

根据以上输入，你的查询应返回以下结果：

±--------+
| Email |
±--------+
| a@b.com |
±--------+

说明：所有电子邮箱都是小写字母。

分析解答：

分组GROUP BY ，然后HAVING语句筛选COUNT(*)大于1的Email返回：

SELECT P.Email
FROM Person P
GROUP BY P.Email
HAVING COUNT(*)>1

例183. 从不订购的客户

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

±—±------+
| Id | Name |
±—±------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
±—±------+

Orders 表：

±—±-----------+
| Id | CustomerId |
±—±-----------+
| 1 | 3 |
| 2 | 1 |
±—±-----------+

例如给定上述表格，你的查询应返回：

±----------+
| Customers |
±----------+
| Henry |
| Max |
±----------+

分析解答：

选择在Customers表中的Name 字段所有值，
通过WHERE语句筛选去掉在Orders表中出现的所有CustomerId
（Orders表中出现的所有CustomerId： SELECT O.CustomerId FROM Orders O）

SELECT C.Name Customers
FROM Customers C
WHERE C.Id NOT IN 
(
SELECT O.CustomerId FROM Orders O
)

例184. 部门工资最高的员工

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

±—±------±-------±-------------+
| Id | Name | Salary | DepartmentId |
±—±------±-------±-------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
±—±------±-------±-------------+

Department 表包含公司所有部门的信息。

±—±---------+
| Id | Name |
±—±---------+
| 1 | IT |
| 2 | Sales |
±—±---------+

编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

±-----------±---------±-------+
| Department | Employee | Salary |
±-----------±---------±-------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
±-----------±---------±-------+

解释：

Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。

分析解答：

首先通过E1.DepartmentId字段分组，利用聚合函数MAX(E1.Salary)得到各个部门的最高工资，即表B；
然后联合原表，进行WHERE 筛选即可

SELECT E1.DepartmentId, MAX(E1.Salary)
FROM Employee E1, Department D1
GROUP BY E1.DepartmentId

上述表B在联合查询与分组之间，应该通过WHERE语句条件 D1.Id = E1.DepartmentId 筛选出正确的样本；
然后将表B与原始表Employee E 进行联合查询，WHERE筛选出符合条件
E1.DepartmentId = B.B_DepartmentId AND E1.Salary = B.max_salary 即可。

SELECT B.D_name Department, E1.Name Employee, E1.Salary
FROM Employee E1,
(
SELECT E1.DepartmentId B_DepartmentId, MAX(E1.Salary) max_salary, D1.Name D_name
FROM Employee E1, Department D1
WHERE D1.Id = E1.DepartmentId
GROUP BY E1.DepartmentId
) B
WHERE E1.DepartmentId = B.B_DepartmentId AND E1.Salary = B.max_salary

**

例185. 部门工资前三高的所有员工

**
Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。

±—±------±-------±-------------+
| Id | Name | Salary | DepartmentId |
±—±------±-------±-------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
±—±------±-------±-------------+

Department 表包含公司所有部门的信息。

±—±---------+
| Id | Name |
±—±---------+
| 1 | IT |
| 2 | Sales |
±—±---------+

编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

±-----------±---------±-------+
| Department | Employee | Salary |
±-----------±---------±-------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
±-----------±---------±-------+

分析解答：

首先将Employee 表e1、e2进行双表联合，利用条件e1.Salary <= e2.Salary AND e1.DepartmentId = e2.DepartmentId 加入部门筛选，即得到各个部门前三高薪水，
接着进行排序倒序即可.

SELECT Department.NAME Department, e1.NAME Employee, e1.Salary Salary 
FROM Employee e1, Department 
WHERE
	e1.DepartmentId = Department.Id 
	AND 3 >= (SELECT  count( DISTINCT e2.Salary ) 
			 FROM	Employee e2 
			 WHERE	e1.Salary <= e2.Salary 	AND e1.DepartmentId = e2.DepartmentId 	) 
ORDER BY Department.NAME,Salary DESC;

方法二：
其实，可以直接用高级语句DENSE_RANK(), PARTITION BY实现：
排名问题分三种：
dense_rank排名连续，rank排名遇到同一排名会间断，row_number即编号排序，此处用dense_rank函数.
具体步骤如下：
第一：利用PARTITION BY对部门分区；利用ORDER DESC对薪水降序排序；得到的新表在原始表Employee基础上新增字段RANKi，即按分区的排名连续。

SELECT * , DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) RANKi 
FROM Employee

第二：通过WHERE语句筛选出RANKi字段小于等于3的样本，即各部门前三高薪水的员工样本

SELECT DepartmentId, Name, Salary
FROM 
(
SELECT * , DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) RANKi 
FROM Employee
) A
WHERE RANKi<=3

第三：将上述表命名B，与Department表进行联合查询，筛选出B.DepartmentId=Department.Id的样本即可.

SELECT Department.Name Department, B.Name Employee, B.Salary
FROM
(
SELECT DepartmentId, Name, Salary
FROM 
(
SELECT * , DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) RANKi 
FROM Employee
) A
WHERE RANKi<=3
) B, Department
WHERE B.DepartmentId=Department.Id

**

例196. 删除重复的电子邮箱

**
编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

±—±-----------------+
| Id | Email |
±—±-----------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
±—±-----------------+
Id 是这个表的主键。

例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

±—±-----------------+
| Id | Email |
±—±-----------------+
| 1 | john@example.com |
| 2 | bob@example.com |
±—±-----------------+

分析解答：

两表联合，通过 P1.Id > P2.Id 筛选出需要筛选的样本，即：

SELECT P1.Id, P1.Email
FROM Person P1, Person P2
WHERE P1.Email = P2.Email AND P1.Id > P2.Id;

删除上述样本，即：

DELETE P1,
FROM Person P1, Person P2
WHERE P1.Email = P2.Email AND P1.Id > P2.Id;

谢谢阅读！！！