MySQL-多表查询

多表关系

• 概念 ：由于业务的之间相互关联，所以存在表结构之间的各种联系基本分为

1.一对多（多对一）
2.多对多
3.一对一

• 一对多

案例：部门和员工的关系
一个部门可以对应多个员工，但一个员工只能处于一个部门
实现：在多的一方（员工）建立外键 指向一的（部门）的主键。

• 多对多

案例：学生与课程的关系
一个学生可选修多门课程，一门课程也可供多个学生选择
实现：建立中间表 ，至少包含两个外键，分别关联两方的主键。

• 一对一

案例：用户与用户之间的关系
一对一的关系 多用于单表拆分，将一张表的基础字段放在一张表中，其他详细字段放在另一张表中，提升效率操作。
实现：在任意一方加入外键 ，关联另一方的主键，并设置外键为唯一（UNIQUE）

多表查询概述

• 概述：指从多张表中查询
• 笛卡尔积：笛卡尔乘积是指在数学在中，两个集合 A集合和 B集合的所有祝贺情况（在多表查询时，需要消除无效的笛卡尔积）

-- 消除笛卡儿积
select * from emp , dept where emp.dept_id = dept.id;
--去重
select distinct d.id , d,name from emp e, dept d where e.dept_id = d.id;

• 多表查询分类
  1.内连接查询
  内连接 : 相当于查询 A、B 交际部分数据

内连接查询语法

• 隐式内连接
  select 字段列表 from 表1， 表2 where 条件 ...;
• 显式内连接
  select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

-- 隐式内连接 连接条件：emp.dept_id = dept.id
 select emp.name, dept.name from emp , dept where emp.dept_id = dept.id;

select e.name , d.name from emp e ,dept d where e.dept_id = d.id;
//最先执行的是from 语句 起了别名之后 就无法使本名
--  显式内连接shixian
select e.name , d.name from emp e inner join dept d on e.dept_id == d.id;

select e.name , d.name from emp e join dept d on e.dept_id == d.id;
//其中 inner 关键字可省略

2.外连接查询

外连接查询语法：
左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件 ..;
相当于查询表1 （左表）的所有数据 包含表1 和表2交集部分

右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
相当于查询表2 （右表）的所有数据 包含表1 和表2交集部分

-- 查询emp 表的所有数据,和对应的部门信息（左外连接）
select e.*, d.name from emp e left outer join dept d on e.dept = d.id;

select e.*, d.name from emp e left join dept d on e.dept = d.id;

--查询dept 表的所有数据,和对应的员工信息（右外连接）
select d.*,e.*  from emp e right join dept d on e.dept = d.id;

--相同效果改为左外连接
select d.*,e.*  from dept d left join emp e on e.dept = d.id;
//通常可改为左外连接

3. 自连接查询

自连接语法：
select 字段列表 from 表A 别名A join 表A 别名B on 条件...;
主语：自连接需要给表起别名 否者不清楚条件 以及返回的字段是哪张表的内容

-- 查询员工名字 及其所属领导的名字
select a.name, b.name from emp a, emp b where a.managerid = b.id;

-- 查询员工名字 及其所属领导的名字；若没有领导 也查询出来
select a.name'员工', b.name'领导' from emp a left join emp b on a.managerid = b.id;

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

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

联合查询多张表的列数必须保持一致，字段类型也需要保持一致。
union all 会将全部的数据直接合并一起，union 会对合并之后的数据去重

-- 将薪资低于我、5千的员工和 年领大于50岁的员工 全部动用起来
select * from emp where salary < 5000
union all  //将查询结果直接合并
select * from emp where age > 50;

select * from emp where salary < 5000
union   //去除相同的结果再合并
select * from emp where age > 50;

5.子查询
概念： Sql语句中嵌套select 语句，称为嵌套查询，又称子查询

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

根据子查询结果不同 ，分为：

1.标量子查询（子查询结果为单个值）
2.列量子查询（子查询结果为一列）
3.行量子查询（子查询结果为一行）
4.表子查询（子查询结果为多行多列）

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

1.标量子查询
返回的结果是单个值（数字、 字符串、日期等等）最简单的形式，称为标量子查询。

--1.查询所有员工的信息
--a.查询 “销售部” 部门ID
 select id from dept where name  = '销售部';
--b.根据销售部门 ID 查询员工信息
 select id from emp  where dept_id = ( select id from dept where name  = '销售部');

--2.查询在“***” 入职之后的员工信息
--a.查询 “yy” 入职日期
 select entrydate  from emp  where name  = 'yy';

--b.查询指定入职时间之后入职的员工信息
 select * from emp  where entrydate  > '2008-11-20';
--c.合并
 select * from emp  where entrydate  > (select entrydate  from emp  where name  = 'yy');

2.列子查询

子查询返回的是一行（多行），这种子查询称为列子查询

-- 1.查询销售部和市场部的所有员工信息
-- a. 查询 “销售部” 和 “市场部” 的部门ID
select id from dept where name = '销售部' or name = '市场部';

--b. 根据部门ID ，查询员工信息
select * from emp where dept_id in (2, 4);

--c. 合并
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

-- 2.查询比 财务部所有人工资都高的员工信息
-- a.查询所有 财务部 人员工资
select id from dept where name = '财务部';
 
select salary  from emp  where dept_id = (select id from dept where name = '财务部');

-- b. 比 财务部 所有人工、工资都高的员工信息
select * from emp where salary > all (select salary  from emp  where dept_id = (select id from dept where name = '财务部'));

-- 3.查询比 研发部 所有人工资都高的员工信息
-- a.查询所有 研发部 人员工资
select salary  from emp  where dept_id = (select id from dept where name = '研发部');

-- b.比研发部中任意一个人薪资都高的员工信息
select * from emp where salary > any (select salary  from emp  where dept_id = (select id from dept where name = '研发部' ));

3.行子查询
子查询返回的结果是一行也可以是多列 ,即为行子查询
常用操作符为：= 、<> 、in 、not in

--1.查询 与yy 薪资 及其直属领导相同的员工信息
--a.查询 yy 的薪资及其所属领导
select salary ，managerid from emp where name = 'yy';

--b. 查询 与“yy”  的薪资及其所属领导相同的员工信息
select * from emp where (salary, managerid) = (select salary ，managerid from emp where name = 'yy'); 

4.表子查询
子查询返回的是多行多列，称为表子查询
常用操作符为 IN

将表子查询返回的结果作为临时表，再和其他表进行联查操作

--1.查询与 smq zyx 职位和薪资相同的员工信息
--a.查询 smq 和zyx 的工资
select job, salary from emp where name  = 'smq' or name = 'zyx';

--b.查询与 smq zyx 职位和薪资相同的员工信息
select * from emp where (job, salary) in (select job, salary from emp where name  = 'smq' or name = 'zyx');

-- 2.查询入职日期为 2006-01-01 之后入职的员工信息
--a.入职时间再 2006-01-01之后的员工信息
select * from emp where entrydata > '2006-01-01';

--b.查询这部分员工，对应的部门信息
select e.*, d.* from (select * from emp where entrydata >'2006-01-01') e left join dept d on e.dept_id = d.id;
//再 from之后运用了 子查询 将返回的表和另外的表结合