数据库sql进阶操作

• sql中的函数使用

group_concat函数详解

聚合函数：统计总数count(),统计最大值max(),统计最小值：min(),平均值avg, 统计综合sum()

使用分组+聚合函数配合使用

1. 查询部门名以及各部门的平均薪资
select post , avg(salary) from employee group by post;
2. 查询部门名以及各部门的最高薪资
select post , max(salary) from employee group by post;
3. 查询部门名以及各部门的最低薪资
select post , min(salary) from employee group by post;
4. 查询公司内男员工和女员工的个数
select sex,count(*) from employee group by sex

having 查询数据之后在进行过滤,一般是配合group by使用, 主要用分组后过滤

找出各部门的平均薪资,并且大于10000以上的所有部门
select post,avg(salary) from employee group by post having avg(salary) > 10000;
1.查询各岗位内包含的员工个数小于2的岗位名,员工名,个数
select post,group_concat(emp_name),count(*) from employee group by post having count(*) < 2
2.查询各岗位平均薪资小于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) < 10000
3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000

• 多表连接查询

左连接 ，右连接，内连接和全外连接的4者区别
数据库中的内连接、自然连接、和外连接的区别
inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。
natural join自然连接是一种特殊的等值连接，他要求两个关系表中进行比较的必须是相同的属性列，无须添加连接条件，并且在结果中消除重复的属性列。

# 基本语法 inner join on 接的表与表之间的必要连接条件
select * from employee inner join department on employee.dep_id = department.id
# 用as 起别名 (推荐)
select * from employee as e inner join department as d on e.dep_id = d.id
# 可以省略as
select * from employee e inner join department d on e.dep_id = d.id

# where 实现的就是内联查询
select * from employee,department where employee.dep_id = department.id
select * from employee as e,department as d where e.dep_id = d.id

# 外连接
# (1) 左连接 (左联查询 left join ) : 以左表为主,右表为辅,完整查询左表所有数据,右表没有的数据补NULL
""" select 字段 from 表1 left join 表2 on 条件 """
select * from employee left join department on employee.dep_id = department.id

# (2) 右连接 (右联查询 right join) : 以右表为主,左表为辅,完整查询右表所有数据,左表没有的数据补NULL
""" select 字段 from 表1 right join 表2 on 条件 """
select * from employee right join department on employee.dep_id = department.id
# (3) 全连接 (union) 所有数据全都合并起来
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id

• 子查询（嵌套查询）

(1) 子查询是查询的语句当中又嵌套的另外一条sql语句,用括号()抱起来,表达一个整体
(2) 一般应用在from 子句后面表达一张表,或者 where 子句后面表达一个条件
(3) 速度从快到慢 单表查询速度最快 -> 联表查询 -> 子查询

 普通的where 相当于内联查询
select 
d.id,d.name
from 
employee e,department d
where
e.dep_id = d.id
group by 
d.id,d.name
having 
avg(e.age) > 25;

# (2) inner join 
select 
d.id,d.name
from 
employee e inner join department d on e.dep_id = d.id
group by 
d.id,d.name
having 
avg(e.age) > 25;

# (3) 子查询
# 1.先选出平均年龄大于25岁的部门id
select dep_id from employee group by dep_id having avg(age) > 25;
# 2.通过部门id,找部门名字
select name from department where id in (201,202)
# 3.综合拼接:
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25)

查看哪个部门没员工

# 联表写法
select 
d.id,d.name
from 
employee e right join department d on e.dep_id = d.id
where
e.dep_id is NULL

# 子查询
# 1.先查询,员工都在哪些部门
select dep_id from employee group by dep_id => (200,201,202,204)
# 2.把不在部门列表中的数据找出来
select from department where id not in (1)
# 3.综合拼接
select id,name from department where id not in (select dep_id from employee group by dep_id)

• EXISTS关键字的子查询

使用 EXISTS 的子查询
带有exists谓词的子查询 嵌套查询
SQL 子查询 EXISTS 和 NOT EXISTS
https://blog.csdn.net/zhangxing52077/article/details/81168441
exists 关键字表达存在
如果内层sql 能够查到数据, 返回True , 外层sql执行查询语句
如果内层sql 不能查到数据, 返回False, 外层sql不执行查询语句

select * from employee where exists (select * from employee where id = 1)