mysql 多表查询

在关系型数据库中，比较常见的就是多表查询，涉及到多张表多条件进行查询。

多表关系

一对一、一对多、多对多

多表查询类型

内连接

取两表交集数据，分为隐式内连接，显式内连接。

隐式内连接：select * from 表1，表2，where 条件 
显式内连接：select * from 表1 inner join 表2 on 连接条件 where 条件

外链接

分为左连接、右连接、自连接

左连接：select * from 表1 left join 表2 on 条件 # 左表中数据优先全部展示
右连接：select * from 表1 right join 表2 on 条件 # 右表中数据优先全部展示
自连接：select * from 表1 as 别名A，表1 as 别名 B on 条件 # 在一张表进行查询，注意需要给表设置别名

联合查询

把多次查询的结果合并起来，形成一个新的查询结果集
关键字：union(使用这个可以去重)、union all(不考虑去重)
注意：对于联合查询多张表的数列必须保持一致，字段类型保持一致

子查询分类

标量子查询

子查询返回的结果是单个值，最简单的形式
常用操作符号：=、<>、<、>、<=、>=

eg:查询销售部员工的所有信息
拆解：先查询部门表中销售部的id，在查询员工表对应id的员工
select * from emp where dept_id = (select id from dept where name = "销售部")

列子查询

子查询返回的结果为多列
常用操作符：

1. in：在指定的结合范围内
2. not in：不在指定的集合范围内
3. any： 子查询返回多个，满足任意一个即可
4. some：与any等同
5. all：子查询返回多个，必须都要满足

eg：查询比财务部所有人员工资都高的员工信息
拆解：获取财务部所有人员工资，存在多个用户多列，查找比财务部薪资都高的员工，全部满足使用all
select * from emp where salary >all(select salary from emp where dept_id=(select id from dept where name= "财务部"))

行子查询

子查询返回的结果是一行，多字段条件
常用操作符号：<>、= 、in、not in

eg:查询与 张无忌的薪资及直属领导相同的员工信息
拆解：查询张无忌的薪资及直属领导，在查询相同的员工
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = "张无忌")

练习题

练习题：
dept：部门表；(id,name)
emp：员工表；(id,name,age,job,salary,entrydata,managerid,dept_id)
salgrade：薪资等级表；
1、查询员工的姓名、年龄、职位、部门信息
select emp.name,emp.age,emp.job,emp.managerid from emp,dept where emp.dept_id = dept.id

2、查询年龄小于30岁的员工的姓名，年龄，职位，部门信息
select emp.name,emp.age,emp.job,emp.managerid  from emp inner join dept on d emp.dept_id = dept.id where emp.age<30
 
3、查询拥有员工部门ID、部门名称
select distinct  dept.id,dept.name from emp,dept where emp.dept_id = dept.id

4、查询所有年龄大于40岁的员工，及其归属的部门名称，如果员工没有分配部门，也需要展示出来
select emp.*,dept.name from emp left join dept on emp.dept_id = dept.id where emp.age>40

5、查询所有员工的工资等级
select e.*,s.grade,s.losal,s.hisal from emp e,salgrade s where e.salary between s.losal and s.hisal

6、查询研发部所有员工的信息及工资等级
连接条件，注意：这里涉及到多少个表，就有多少个表减1个连接条件
查询条件 
select e.*,s.grage from emp e,dept d,salgrade s where e.dept_id = d.id and s.salary between s.losal and s.hisal and d.name = "研发部"

7、查询研发部的平均工资
先获取研发部员工，聚合函数avg
select ave(e.salary) from emp e,dept d where e.dept_id = d.id and d.name = "研发部"
       
8、查询工资比 张无忌 高的员工信息
先查询张无忌薪资，在查找大于这个薪资的员工信息
select * from emp where salary >(select salary from emp where name = "张无忌") 

9、查询比平均工资高的员工信息
先查询平均工资，在查询大于平均信息的员工
select * from emp where salary >(select avg(salary) from emp)

10、查询低于本部门平均信息的员工
先根据部门聚合获取平均值，在查询小于本部门薪资的值
select * from emp e2 where e2.salary<(select avg(el.salary) from emp e1 where e1.dept_id = e2.dept_id)

11、查询所有的部门信息，并统计部门的员工人数
 先查询所有部门，在去找部门下所有的员工
select d.id,d.name,(select count(*) from emp e where e.dept_id=d.id) '人数' from detp d