MySql多表查询 多表关系 内连接 外连接 子查询 模板及例子

多表查询

1.多表关系

一对多（多对一）：
案例 部门和员工的关系
关系 一个部门可有多个员工 一个员工对应一个部门
实现 在多的一方建立外键 指向一的一方的主键

多对多：
案例 学生与课程的关系
关系 一个学生可以选秀多门课程 一门课程也可以提供给多个学生选择
实现 建立第三张中间表 中间表至少包涵两个外键 分别关联两方主键

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

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

多表查询语法 ----笛卡尔积
消除无效的笛卡尔积
select * from emp ,dept where emp.dept_id=dept.id;

多表查询的分类
连接查询 
 内连接：
 隐式内连接 ：select 字段列表 from 表1，表2 where 条件；
显示内连接  ：select 字段列表 from 表1[inner] join 表2 on 连接条件...;
1.查询每一个员工的姓名 及其关联部门的名称 （隐式内连接）
表 结构  ：emp ,dept
连接条件：emp.dept_id=dept.id
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;

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

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

例子：
查询emp表的所有数据 和对应的部门的信息（左外连接）
select e.*,d.name  from emp  e left outer join dept   p on e.dept_id=p.id;
查询dept表的所有数据 和对应的员工的信息（右外连接）
select d.*,   e.*  from emp  e right outer join dept   p on e.dept_id=p.id;

自连接：当前表与自身的连接查询，自连接必须使用表别名
查询语法： 
select 字段列表 from 表A 别名A  join 表A 别名B on 条件...;
自连接就是可内可外 
例子： 
1. 查询员工和直属领导的名字
表结构 emp
select a.name ,b.name  from  emp a,emp.b where a.managerid=b.id;

联合查询 -union,union all
对于union 查询 就是把多次查询的结果合并起来 形成一个新的查询结果集
select 字段列表 from 表A ....
union [all]
select 字段列表 from 表B....;

查询薪资低于5000 和年龄大于50的查询出

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

子查询 ：
概念 SQL语句中嵌套select 语句 称为 嵌套查询 又称子查询
 select * from t1 where column1=( select coumn1 from t2); 
子查询外部的语句可以是 insert /update/delete/select 的任意一个。    

根据子查询的结果不同 分为：
标量子查询 （子查询结果为单个值）
列子查询 （子查询结果为一列）
行子查询 （子查询结果为一行）
表子查询 （子查询结果为多行多列）

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

标量子查询 （子查询结果为单个值 单行单列），最简单的形式 这种子查询的值可以是日期 时间等等

例：
根据部门id      查询员工信息
select id from dept where name='销售部'；

select * from emp where dept_id=4;
拆分成两条语句
select * from emp where dept_id=(select id from dept where name='销售部')

列子查询 （子查询结果为一列）
常用操作符 ：in, not in ,any ,some.all 
 例：
查询 销售部和市场部的所有员工信息 
a.select id  from dept where name ='销售部' or name='市场部'；
根据部门id 查询员工信息
b.select * from emp where dept_id in (2,4);
 合并：select * from emp where dept_id in (select id  from dept where name ='销售部' or name='市场部');

行子查询 （子查询结果为一行）
常用的操作符 ：= <> in not  in 
例：
查询emp表中与 "张无忌" 的薪资及直属领导相同的员工信息；
 拆分
1. 查询与张无忌 的薪资及直属领导
select salary， managerid from emp where name='张无忌'；
2.查询与 张无忌 的薪资及直属领导相同的员工信息
select  * from emp where (salary ,managerid)=(12500,1);
3.合并
select  * from emp where (salary ,managerid)=(select salary， managerid from emp where name='张无忌');

表子查询 （子查询结果为多行多列）