数据分析课程笔记 - 14 - MySQL（三）

大家好呀~ 今天继续学习 MySQL 的几个高阶用法：条件函数、表连接查询、子查询以及窗口函数。这些内容相对比较抽象，大家可以先了解概念和基本语法，结合每个知识点后面的练习进行理解，我们会在下节课通过一套实际的案例需求帮助大家理解和运用这些函数。

这节课的主要内容有：

1、条件函数
（1）IF
（2）CASE WHEN

2、表连接查询
（1）UNION&UNION ALL
（2）内外表连接
① INNER JOIN
② LEFT JOIN 左连接
③ FULL JOIN 全连接 【不直接支持】

3、子查询
（1）WHERE子查询
（2）FROM子查询

4、窗口函数
（1）ROW_NUMBER()
（2）DENSE_RANK()
（3）RANK()
（4）分区排序PARTITION BY
（5）平均分组NTILE(n)

注：本节课要用到的数据源可以在公众号后台回复“数据分析14”获取。

一、条件函数

1、IF

-- 语法结构
if(condition, true, false)

-- 从员工表中查询“工资等级”⼀列，薪资大于10000的为“高收入”，否则为“低收入”
select ename as '员工姓名', 
    sal as '员工工资',
    if(sal>10000,'高收入','低收入') as '工资等级'
from employee;

2、CASE WHEN

-- 语法结构
case when 条件1 then '结果1'
    when 条件2 then '结果2'
    when 条件3 then '结果3'
    ......
    else '默认值'
end as '新字段名'

练习： 从员工表中查询“工龄等级”⼀列，“元老级”员工(2011
年1月1日前入职)、“老员工”(2011年1月1日~2016年12月31
日)、“新员工”(2016年12月31日之后入职)

select *,
    case when hiredate < '2011-01-01' then '元老级'
    when hiredate between '2011-01-01' and '2016-12-31' then '老员工'
    else '新员工' end as '工龄等级'
from employee_new;

二、表连接查询

连接是关系型数据库模型的主要特点。连接查询时关系型数据库中
最主要的查询，主要包括内连接、外连接等。通过连接运算符可以
实现多个表查询。

1、UNION & UNION ALL

-- 语法结构
SELECT column,... from table1
    [where ...]
UNION [ALL]
SELECT column,... from table2
    [where ...]
...;

注：将多个查询的结果合并在⼀起，合并时，两个查询对应的列数
和数据类型必须相同；各个查询子句使用UNION或UNION ALL关键
字分隔。当使用UNION时，合并的结果会删除重复的记录，所有返
回的行都是唯⼀的，排列好顺序的；当使用UNION ALL时，不会删
除重复的记录，也不对结果进行自动排序！

-- 示例：将员工表employee和员工新表employee_new合并
-- 1.使用UNION合并
select * from employee
UNION
select * from employee_new;

select eno,ename from employee
UNION
select eno,ename from employee_new;

-- 2.使用UNION ALL合并
select * from employee
UNION ALL
select * from employee_new;

select eno,ename from employee
UNION ALL
select eno,ename from employee_new;

对比
1、union的结果是去重且排序的。
2、union all的结果没去重，也不排序。
3、在使用它们进行连接的时候，这些查询语句所对应的列数和数据类型要一致。

2、内外表连接

内连接- JOIN和inner join⼀样
示例:表1 join 表2 ，返回表1和表2共同的行

左连接- LEFT JOIN
示例:表1 left join 表2，以表1为主体，匹配表2的相关信息，用的最多

右连接- RIGHT JOIN
示例:表1 right join 表2，以表2为基础，匹配表1的相关信息，可以用左连接替代

全连接- MySQL中无直接实现全连接，间接可以实现。

SQL表连接图例

SQL表连接图例

（1） INNER JOIN

-- 内连接：返回表的交集
-- 案例演示：将员工新表、部门表连接成⼀张大表。
select *
from employee_new
JOIN department
ON employee_new.deptno = department.deptno;

-- 练习：查询员工及其主管的基本信息
select e.ename as 员工姓名,
    f.ename as 主管姓名
from employee_new as e
inner join employee_new as f
on e.mgno = f.eno;

注意： 这里有点难度，首先，在 employee 表中，员工有对应的主管号mgno，而最高一级主管没有对应的mgno，所以先把这个表自己和自己 inner join 一下，目的是从左边表中取出员工，右边表中取出对应的主管，关联的条件就是 左边表的 mgno 要等于右边表的 eno。由于 inner join 是取交集，所以左边员工 mgno 为 null 的就不会被取出来。

（二）LEFT JOIN 左连接

-- 内连接
select count(*)
from employee_new
JOIN department
ON employee_new.deptno = department.deptno;

-- 左连接
select count(*)
from employee_new
LEFT JOIN department
ON employee_new.deptno = department.deptno;

-- 增加⼀条数据
insert into employee_new (eno,ename,job) values
(3000,"赵小牛","未来董事长");

-- 练习：查找员工表中没有直接领导的员工
select *
from employee_new as e
left join employee_new as f
on e.mgno = f.eno
where isnull();

注意：这个题跟前面取主管的区别在于，由于 left join 是以左边为准，所以不管有没有主管，每一个员工都会被取出来，而我们最终选择 mgno 为 null 的就是没有直接领导的员工。

（3）FULL JOIN 全连接 【不直接支持】

-- MySQL不支持全连接，只能实现左右连接，通过观察左右连接
的结果数据发现，我们可以根据左右连接的结果实现全连接
select * from employee_new
LEFT JOIN department
ON employee_new.deptno = department.deptno
UNION
select * from employee_new
RIGHT JOIN department
ON employee_new.deptno = department.deptno;

三、子查询

1、WHERE 子查询

当⼀个SELECT查询是另⼀个查询的⼀部分时，我们把内层的查询称
为子查询，外层的查询称为父查询。通过子查询可以实现多表查询，该查询语句可能包括 in、any、all 和 exists 等关键字。除此之外还可能包含比较运算符 < <= > >= 等。

create table tbl1(num1 INT NOT NULL);
create table tbl2(num2 INT NOT NULL);

INSERT INTO tbl1 values(1),(5),(13),(27);
INSERT INTO tbl2 values(6),(14),(11),(20);

-- 1.带ANY、SOME关键字的子查询
-- 示例：只要大于tbl2表的num2列任何⼀个值，就返回

select num1 from tbl1 where num1 > ANY(select num2 from tbl2);
13、27

select num1 from tbl1 where num1 > SOME(select num2 from tbl2);
13、27

-- 2.带ALL关键字的子查询
-- 示例：大于tbl2表的num2列所有的值，才返回。

select num1 from tbl1 where num1 > ALL(select num2 from tbl2);
27

-- 3.带EXISTS关键字的子查询(也有NOT EXISTS)

-- 注：EXISTS关键字后面的参数是⼀个任意的子查询，系统对子查询进行运算以判断它是否返回行

select num1 from tbl1 where exists(select num2 from tbl2 where num2 = 13);

注意：

EXISTS 这里说实话我运行完都没太明白它的逻辑，后来在知乎上看到一个回答才明白。

EXISTS的执行步骤是：

1、先把tb1里面所有的数取出来；
2、逐行针对tb1的数据，去看子查询的条件，如果子查询有返回结果，那就返回tb1的一行数据；
3、重复遍历，直到结束。

那上面的 SQL 语句就很好理解了，子查询 tb2 中并没有数据等于13，所以子查询为 false，那对应 tb1 的每一行数据都不会返回，所以结果是null。如果把子查询的 where 条件改为 num2 = 11，那最终的结果就是返回 tb1 的所有数据。

-- 4.带IN关键字的子查询(也有NOT IN)

select * from employee_new where deptno in (select deptno from department);
select * from employee_new where deptno in (10,20,30);

-- 5.带比较运算符的子查询
-- 查询员工表中比“王亮”工资高的员工姓名
select ename
from employee_new
where sal > (select sal from employee_new where ename = '王亮');

2、FROM 子查询

-- 语法结构
SELECT t1.*
from
(select 字段 from 表名 ...) t1
where 条件
...

练习： 求每个工龄等级的平均薪资

我们在前面 CASE WHEN 部分做了一个工龄等级的分类：

select *,
    case when hiredate < '2011-01-01' then '元老级'
    when hiredate between '2011-01-01' and '2016-12-31' then '老员工'
    else '新员工' end as '工龄等级'
from employee_new;

现在我们要求每个工龄等级的平均薪资，那就要把这个分类好的表作为我们的数据源表，也就是子查询，并针对不同的工龄等级进行分组，再计算不同组的平均薪资。为了方便引用，我们可以将工龄等级分类列字段重命名为 type，将分类好的子查询表重命名为 e，这个需求的 SQL 语句就可以这样写（重命名的 as 可以省略）：

select e.type '工龄等级',
    avg(e.sal) '平均薪资'
from
    (select *,case when hiredate < '2011-01-01' then '元老级'
    when hiredate BETWEEN '2011-01-01' and '2016-12-31' then '老员工'
    else '新员工' end as type
from employee_new) e
GROUP BY e.type;

四、窗口函数

1、ROW_NUMBER()

-- 将员工薪资进行排序，从大到小，并且将排序的结果同时显示在查
询结果中，相同数字依序排，比如 1,2,3,4...
select ename,
    sal,
    row_number() OVER(ORDER BY sal DESC) as 'row_number'
from employee_new;

2、DENSE_RANK()

-- 相同的值排名相同，序号是连续的，比如 1,2,2,3...
select ename,
    sal,
    DENSE_RANK() OVER(ORDER BY sal DESC) as 'dense_rank'
from employee_new;

3、RANK()

-- 相同的值排名相同，序号是跳跃的，比如 1,2,2,4...
select ename,
    sal,
    RANK() OVER(ORDER BY sal DESC) as 'rank'
from employee_new;

4、分区排序PARTITION BY

-- 将员工薪资按照部门进行排序
select ename,
    sal,
    deptno,
    row_number() OVER(PARTITION BY deptno ORDER BY sal DESC) as '本部门工资排名'
from employee_new;

5、平均分组NTILE(n)

-- 将所有的员工按照工资分成三个等级
select ename,
    sal,
    ntile(5) OVER(ORDER BY sal DESC) as '工资等级'
from employee_new;

比如一共有24个人，就会按 sal 降序排列之后，每5个人一个等级，最后一个等级4个人。

好啦，以上就是这次课的全部内容，其中子查询和窗口函数是重点也是难点，不容易理解，需要多多练习。那 Mysql 的知识点就是这三次课的这些内容。下次课我们会结合一个金融数据案例的多种需求，来复习一下这些知识点，敬请期待~