文章目录
1. SQL 应掌握内容
参考链接 - 冷凡社长 -【避坑指南】数据分析师的SQL到底应该掌握到什么程度
select
数据提取- SQL的 书写规则 与 执行顺序 链接 - 1.4.3 SELECT 的执行顺序
where
语句的使用(包含like
,between
,in\or
、逻辑判断)- 表的内联、外联
- 分组子查询
- 排序和去重
- 聚合函数
case when
的用法- 窗口函数 如何使用
- 变量的使用
- 分组查询 严格模式
- 空值 的影响处理
count(*)
和count(列)
exist
- 临时表
- 自联接
- 文本时间函数
1.1 知识点归纳
窗口函数
2. SQL 查询问题
2.1 简单查询
2.1.1 查找-删除重复数据
参考链接 - 猴子数据分析- 《图解SQL面试题》- 图解面试题:如何查找重复数据?
知识点:
- 重复 ⇒ \Rightarrow ⇒ group by(重复项) ⇒ \Rightarrow ⇒ count()
having
语句更高效,- where 语句无法与聚合函数count 一起使用,
where
子句的运行顺序排在第二,运行到 where 时,表还没有被分组。
- where 语句无法与聚合函数count 一起使用,
- sql 子句的书写顺序和运行顺序
- 关键字的顺序
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...
- SELECT 语句的执行顺序
FROM > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT
- 关键字的顺序
⭐️ ⭐️ LeetCode - 196. 删除重复的电子邮箱
知识点:
- 使用
group by
找出重复项,再用max(id)
挑选出需保留的项,注意在挑选时需再次使用select
构建出一个表 - 方法二 使用自连接删除
2.1.2 查找超过经理收入的员工
知识点:
inner join
: 内连接
2.2 复杂查询
2.2.1 查找第N高的数据
参考链接 - 猴子数据分析- 《图解SQL面试题》- 图解面试题:如何查找第N高的数据?
知识点:
-
limit
和offset
limit n
子句表示查询结果返回前 n 条数据,offset n
表示跳过 n 条语句;limit y offset x
分句表示查询结果跳过 x 条数据,读取前 y 条数据;
-
ifnull(a, b)
- 如果 value1 不是空,结果返回
a
;如果 value1 是空,结果返回b
。
- 如果 value1 不是空,结果返回
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N := N-1; # 设置 N-1
RETURN (
# Write your MySQL query statement below.
select ifnull(
(select distinct Salary
from Employee
group by Salary
order by Salary desc
limit N, 1), null) as getNthHighestSalary
);
END
2.2.2 连续出现N次数字
方法二: 使用 自连接 连续复制3次 👍
参考链接 - 猴子数据分析 - 《图解SQL面试题》图解面试题:找出连续出现 N 次的内容?
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num;
注意:id
需为 int 型
扩展
猴子数据分析 - 拼多多面试题:如何找出连续出现N次的内容?
2.2.3 比较日期数据
参考链接 - 猴子数据分析 - 《图解SQL面试题》- 图解面试题:如何比较日期数据?
⭐️ LeetCode - 197. 上升的温度
SQL 代码实现
知识点:
- 交叉联结(
cross join
) - 时间计算函数
datediff()
- datediff(日期 1, 日期 2):得到的结果是日期 1 与日期 2 相差的天数。
- 如果日期 1 比日期 2 大,结果为正;如果日期 1 比日期 2 小,结果为负。
- 时间计算的函数
timestampdiff()
- timestampdiff(时间类型, 日期 1, 日期 2),这个函数和上面 diffdate 的正、负号规则刚好相反。
- 日期 1 大于日期 2,结果为 负,日期 1 小于日期 2,结果为 正。时间类型有:day,hour,second。
2.3 多表查询
2.3.1 多表联结
参考链接 - 猴子数据分析- 《图解SQL面试题》- 图解面试题:多表如何查询?
知识点:
- 左联结(
left join
),联结结果保留左表的全部数据; - 右联结(
right join
),联结结果保留右表的全部数据; - 内联结(
inner join
),取两表的公共数据;
2.3.2 查找不在表里的数据
参考链接 - 猴子数据分析- 《图解SQL面试题》- 图解面试题:如何查找不在表里的数据?
2.3.3 涨工资
参考链接 - 猴子数据分析- 《图解SQL面试题》- 图解面试题:你有多久没涨过工资了?
CREATE table `薪水表`(`雇员编号` VARCHAR(10), `薪水` int(10), `起始日期` datetime, `结束日期` datetime);
insert into `薪水表` values('10002', '72527', '2001-08-02', '2003-01-01');
insert into `薪水表` values('10002', '75432', '2003-01-01', '2004-01-01');
insert into `薪水表` values('10005', '94692', '2001-09-09', '2003-01-01');
insert into `薪水表` values('10006', '43311', '2001-08-02', '2004-01-01');
CREATE table `雇员表`(`雇员编号` VARCHAR(10), `出生日期` datetime, `姓名` VARCHAR(10), `性别` VARCHAR(10), `雇用日期` datetime);
insert into `雇员表` values('10002', '1976-09-09', '小明', '男','2001-08-02');
insert into `雇员表` values('10005', '1973-08-07', '小红', '女','2001-09-09');
insert into `雇员表` values('10006', '1980-08-28', '小兰', '女','2001-08-02');
select m.雇员编号,当前薪水-入职薪水 as 薪水涨幅
from
(select 雇员编号,薪水 as 当前薪水
from 薪水表
where 结束日期 = '2004-01-01') as m
left join
(select a.雇员编号,薪水 as 入职薪水
from 雇员表 as a
left join 薪水表 as b
on a.雇员编号 = b.雇员编号
where a.雇用日期 = b.起始日期 and a.雇员编号 in
(select 雇员编号
from 薪水表
where 结束日期 = '2004-01-01')) as n
on m.雇员编号 = n.雇员编号
order by 薪水涨幅;
2.3.4 分数排名
参考链接 - 数据蛙 SQL45道例题 - 15. 按各科成绩进行排序,并显示排名,score重复时也继续排名
知识点:
ROW_NUMBER()
: 依次排序,不会出现相同排名RANK()
: 出现相同排名时,跳跃排名DENSE_RANK()
: 出现相同排名时,连续排名