MySQL day02

MySQL day02

1 分组

分组：在一些情况下，需要根据某一列对表中的数据进行分类（分组），把该列值相同的数据分成一组，值不同的分到不同组中，然后以组为单位进行数据处理。

需求：查询各部门的员工最高薪资

1.1 简单分组

分组语法：

 select 组函数,字段（出现在group by后面的列）
 from 表名
 group by 分组的字段（列）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zzWcb7PX-1631064787009)(MySQL day02.assets/image-20210504213054429.png)]

1. 先根据department_id分组
select ...
from employees
group by department_id
2. 分组后查询时使用组函数获取每组的最高薪资
select max(salary)
from ...

组合后：
select department_id,max(salary)
from employees
group by department_id;

1.2 带条件的分组

语法：
select 列,...
from 表名
where 条件
group by 分组依据;

-- 查询60 90 100 部门的平均薪资
select department_id,avg(salary) --3 使用组函数
from employees  
where department_id in(60,90,100) --1 先筛选
group by department_id;--2 分组

2 having子句

having子句：在group by分组后执行的条件判断

-- 查询平均薪资>=8000的部门
select department_id,avg(salary)
from employees
group by department_id --先分组
having avg(salary) >= 8000 --后筛选

having和where的区别：

• where在group by之前执行，having在group by之后执行

• 如果组函数做条件，那么必须使用having

• 如果既可以使用where又可以使用having时，优先使用where （效率更好）

  需求：查询60、90、100部门的平均薪资
  1.先过滤，再分组计算
  select department_id,avg(salary)
  from employees
  where department_id in (60,90,100)
  group by department_id;
  
  2.先分组计算，再过滤
  select department_id,avg(salary)
  from employees
  group by department_id
  having department_id in (60,90,100)
  
  

通过上述示例可分析出优先使用where效率更高

3 limit关键字

limit关键字用来限制查询结果的条目数，通常用于分页查询。

语法: limit后跟2个数字，offset表示查询条目的起始下标，rows表示最多显示多少条

select 列...
from 表名
limit offset,rows

示例：

-- 获取前10行
select * from employees limit 0,10;
-- 获取11行~20行
select * from employees limit 10,10;
注意：当下标为0时，下标可以省略
select * from employees limit 10;

-- limit可以和之前的任意查询关键字连用，注意将其放到最后
select * 
from employees
where salary > 10000
limit 10,10

4 查询SQL关键字的执行顺序

查询sql完整结构：

语法顺序            执行顺序
select				5
from 				1
where				2
group by 			3
having				4
order by 			6
limit				7

5 子查询

当一个查询SQL的条件需要使用另外一个查询SQL的结果时，需要在一个SQL语句中嵌套另外一个SQL语句。

5.1 where单值子查询

被嵌套的sql返回一行一列的结果。

--查询employee_id 150的员工所在的部门信息
select * from employees;--员工表
select * from departments;--部门表

--如果知道150员工所在的部门的id
select *
from departments
where department_id = ?;

--获取150员工所在的部门id
select department_id
from employees
where employee_id = 150;

--合并
select *
from departments
where department_id = (select department_id
from employees
where employee_id = 150);

5.2 where多值子查询

被嵌套的sql返回多行一列的结果。

查询last_name为'King'的员工所在部门的信息
--如果知道'King'员工所在部门id
select *
from departments
where department_id in(?);

--获取'King'员工所在部门id
select department_id
from employees
where last_name = 'King';

--合并
select *
from departments
where department_id in(select department_id
from employees
where last_name = 'King');

5.3 from子查询

被嵌套的sql返回多行多列的结果。

--对员工按薪资降序排列，统计出薪资前10名的总薪资
--如果一个查询SQL返回多行多列，那么可以把该SQL当做一张表看待
select sum(salary) 
from 
(select * from employees 
	order by salary desc
limit 10
) e;

6 表连接

表连接：当查询的数据需要从多张表中获取时，需要将多张表连接起来进行查询。
例如：查询员工及所在部门的信息，需要从员工表和部门表中同时获取数据

6.1 外键

表连接的前提：连接的表之间要有关系。

外键：

• 特殊的列，用来记录表和表之间的关系。
• 外键的值一般来源于另外一张表主键的值，并且要满足参照完整性。
• 外键在表连接时通常作为连接条件使用。

6.2 内连接

语法：
select 表1.列名,... ,表2.列名,...
from 表1 inner join 表2
on 连接条件;

-- 查询员工和部门的信息
select e.*,d.*
from employees e inner join departments d
on e.department_id = d.department_id;

特点：两张表中只要满足连接条件的数据才显示，不管是哪一张表的数据，只要不满足连接条件一定不显示。

6.3 左外连接

语法：
select 左表.列名,... ，右表.列名
from 左表 left outer join 右表 
on 连接条件；
-- 查询员工及所属部门信息
select e.*,d.*
from employees e left outer join departments d
on e.department_id = d.department_id;

特点：左表中数据无论是否满足条件一定显示，右表中的数据满足条件显示，不满足条件不显示。

6.4 右外连接

语法：
select 左表.列名，... ,右表.列名，...
from 左表 right outer join 右表
on 连接条件;
-- 查询员工及所在部门信息
select e.*,d.*
from employees e right outer join departments d
on e.department_id = d.department_id;

特点：右表中无论是否满足连接条件都显示，左表中数据满足连接条件才显示，不满足就不显示。

注意：实战开发时，左外连接使用最多。
内连接的inner关键字和外连接的outer关键字可以省略

6.5 多表连接

语法：
select 表1.列名,...,表2.列名,...,表3.列名,...
from 表1 left join 表2
on 连接条件
left join 表3
on 连接条件
left join...

-- 查询员工所在部门以及部门地址的信息
select e.*,d.*,l.*
from employees e left join departments d
on e.department_id = d.department_id
left join locations l
on d.location_id = l.location_id;

注意：实战中，表连接不能超过3张表，否则会有性能问题。

重点：

1. 分组
2. having自居
3. limit
4. SQL关键字执行顺序
5. from子查询
6. 左外连接

in…

– 查询员工所在部门以及部门地址的信息
select e.,d.,l.*
from employees e left join departments d
on e.department_id = d.department_id
left join locations l
on d.location_id = l.location_id;

注意：实战中，表连接不能超过3张表，否则会有性能问题。

重点：

1. 分组
2. having自居
3. limit
4. SQL关键字执行顺序
5. from子查询
6. 左外连接