mysql -- 多表查询

多表关系

各个表结构之间的关系:
一对多 (多对一)  case：部门和员工

多对多   学生和课程  
- 需要创建一张中间表，这个表中至少需要两个外键，分别关联两方主键

一对一   用户 和 用户详细的关系(单表拆分)
实现： 在任意一方加入外键，关联另一方的主键，并且设置外键为唯一(unique)

多表查询概述：从多张表中查询数据

笛卡尔积：在数学中，两个集合A，B的所有组合情况

case：
查看笛卡尔积：select *from emp,dept;  -- 又多又重复

多表查询：select *from emp,dept where emp.dept_id=dept.id;

多表查询分类

连接查询：{

内连接：  相当与A,B交集的部分

外连接：
     左外连接：查询左表所有数据，以及两表交集部分数据
     右外连接:  查询右表所有数据，以及两表交集部分数据

自连接：当前表与自身连接查询，自连接必须使用表别名

}

子查询

连接查询

内连接：    查询两张表之间交集的部分

语法：
隐式内连接：select 字段列表 from 表1，表2where 条件...；

显式内连接： select 字段列表 from表1 [inner]  join 表2 on 条件...；

case： 
查询每一个员工的姓名和关联的部门名称
1.隐式内连接查询
select emp.name , dept.name from emp,dept where emp.dept_id=dept.id;

2.显式查询:
select e.name,d.name from emp e join dept d on e.dept_id = d.id;
 

外连接：查询一边表的所有数据包含二表交集的部分

语法:
左外连接；
selset 字段列表 from 表1 left [outer] join 表2 on 条件...;

右外连接；
selset 字段列表 from 表1 right [outer] join 表2 on 条件...;

case:
查询emp表的所有数据和对应部门信息(左)
select e.* ,d.* from emp e left join dept d on e.dept_id = d.id;

查询dept表的所有数据，和对应员工信息(右)
select d.* ,e.* from emp e right join dept d on e.dept_id = d.id;

一般我们都采用左外连接，因为右外爷可以改成左外，比如上面的
select d.* ,e.* from  dept d left join emp e on e.dept_id = d.id;
 

自连接:

语法：select 字段列表 from 表a 别名a join 表a别名b on 条件...；
//自连接查询可以是内连接也可以是外连接查询

case:
查询员工及其所属领导的名字：自连接 + 内连接

select  a.name '员工姓名',b.name'领导姓名'  from emp a join emp b on a.managerid=b.id;
 或者select  a.name '员工姓名',b.name'领导姓名'  from emp a , emp b where a.managerid=b.id;

解析:把同一张表看成是两张表，一张员工表一张领导表，
可以看出他们的  关联条件  就是员工的managerid 就是领导的的id

查询所有员工的名字和他的领导的 名字，如果员工没有领导也需要查询: 自连接 + 外连接
    select  a.name '员工姓名',b.name'领导姓名'  from emp a left join emp b on a.managerid=b.id;

联合查询: -union ,union all

对于union查询，就是把多次查询的结果结合起来，形成一个新的查询结果集

语法

select 字段列表 from 表A ...
union [all]

select 字段列表 from 表B ...;

case:
把薪资低于5000 和  年龄大于50岁的员工全部查询出来

select * from emp  where  salary<5000
union 
select  * from emp where age >50;

注:
对于联合查询多表的列数必须保持一致，字段类型也必须保持一致

//union all: 将查询结果直接合并 
// union : 合并并去重

 

子查询:

概念: SQL语句嵌套select 语句，称为嵌套查询也叫子查询

语法：
select * from t1 where column1 = (select column1 from t2);
子查询外部的语句可以是insert/update/delete/select的任何一个

分类

根据子查询结果不同,分为:
标量子查询 -- 子查询的结果为单个值(数字，字符串，日期)
列子查询   -- 列
行子查询  -- 行
表子查询  -- 表

根据子查询的位置分为：
where 之后, from之后，select之后

标量子查询: > = <  >=  <=

case：查询‘销售部’的使用所有员工信息: = 修饰
select * from emp where dept_id=(select id from dept where name='销售部' );

查询方东白入职后的员工信息: 入职后这里我们用 > 修饰
select *  from emp where entrydate>(select entrydate from emp where name ='方东白');

列子查询: 

常用操作符: in ，not in, any,some,all

-any/some : 子查询返回列表中有任意个条件满足均可
-all：子查询返回列表中所有条件都必须满足

case:查询销售部和市场部的有员工信息:

select *from emp where dept_id in (select id from dept where name='销售部' || name ='市场部')

查询比财务部所有员工工资都高的员工信息:

select * from emp where salary> all(select emp.salary from emp where dept_id=(select id from dept where name='财务部'));

查询比研发部任意一人工资高的员工信息：

select * from emp where salary> any(select emp.salary from emp where dept_id=(select id from dept where name='研发部'));

行子查询:

常用操作符:  = <> in   not in

case:
查询与张无忌的薪资及其 直属领导相同 的员工信息:   --行的意思就是() 里的信息都在一行 这里的salary 和mangerid 是在一行的

select * from emp where (salary,managerid) = (select salary,managerid from emp  where  name='张无忌');

表子查询: in

case:
查询与'鹿杖客'，‘宋远桥’的职位和薪资相同的员工信息
select * from emp where (job,salary)in(select job,salary from emp where name ='鹿杖客'|| name='宋远桥');

查询入职日期是'2006-01-01'之后的员工信息，及其部门信息
select e.*,d.* from (select * from emp where entrydate >'2006-01-01') e left join dept  d on e.dept_id=d.id;

case练习合集 :  

1.查询员工的姓名，年龄，职位，部门信息(隐式内连接)
select e.name,e.age,e.job,d.* from emp e ,dept d where e.dept_id=d.id;s

2.查询年龄< 30的员工的姓名，年龄，职位，部门信息(显式内连接)
select e.name,e.age,e.job,d.* from emp e join dept d on e.dept_id=d.id where  e.age<30;

3. 查询拥有员工的部门ID和部门名称
//查询拥有员工的部门就是内连接取交集即可
//distinct关键字去重

select distinct d.id , d.name from emp e , dept d where e.dept_id=d.id;

4.查询所有年龄>40的员工，以及归属的部门名称，如果没有分配部门，也要展示出来

select e.*,d.name from emp e left join dept d on e.dept_id=d.id where age>40;

5.查询所有员工工资等级:
select  e.name,e.salary,s.grade from emp e , salgrade s where e.salary >=s.losal and e.salary<= s.hisal order by e.salary;

select  e.name,e.salary,s.grade from emp e , salgrade s where e.salary between  s.losal and s.hisal order by e.salary;

6.查询研发部所有员工的信息及工资等级:

 select e.*,s.grade from emp e join dept d join salgrade s on e.dept_id=d.id and e.salary between s.losal and s.hisal where  d.name ='研发部';

7.查询研发部的平均薪资：
 select e.*,s.grade from emp e join dept d join salgrade s on e.dept_id=d.id and e.salary between s.losal and s.hisal where  d.name ='研发部';

8.查询比平均薪资高的员工信息：
select * from emp where salary>(select avg(salary) from emp);

9.查询比当前部门平均薪资低 的员工信息：
select e2.*,(select  avg(salary) from emp where emp.dept_id=e2.dept_id) ‘平均薪资’ from emp e2,dept d where  e2.dept_id  =d.id
                             and
            e2.salary<(select  avg(salary) from emp where emp.dept_id=e2.dept_id);

10.查询所有部门的信息，并且统计每个部门的总人数
select d.id,d.name, (select count(*) from emp e where e.dept_id= d.id) '人数' from dept d;