1 分组
分组:在一些情况下,需要根据某一列对表中的数据进行分类(分组),把该列值相同的数据分成一组,值不同的分到不同组中,然后以组为单位进行数据处理。
需求:查询各部门的员工最高薪资
1.1 简单分组
分组语法:
select 组函数,字段(出现在group by后面的列)
from 表名
group by 分组的字段
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)
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
select 5
from 1
where 2
group by 3
having 4
order by 6
limit 7
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;
特点:右表中无论是否满足连接条件都显示,左表中数据满足连接条件才显示,不满足就不显示。
6.5 全外连接
语法:
select 表1.列名,...,表2.列名,...
from 表1 full outer join 表2
on 连接条件;
--查询员工及所在部门信息
select e.*,d.*
from employees e full outer join departments d
on e.department_id = d.department_id;
特点:2张表中的数据无论是否满足连接条件都显示。 注意:实战开发时,左外连接使用最多。 内连接的inner关键字和外连接的outer关键字可以省略
6.6 多表连接
语法:
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张表,否则会有性能问题。