多表查询
一.概述与简介
*1. 多表查询顾名思义就是一条命令查询多张表的操作.
单表查询时: select *from 表1;
多表查询时: select *from 表1,表2......;

*但是在多表查询中会出现重复记录出现,这时因为笛卡尔积的原因
*笛卡尔积:是指将A,B两个集合的所有的组合情况展现出来。
*因此在多表查询的时需要消除笛卡尔积,只保留两张表相关联的记录,去除其他的记录。
*去除笛卡尔积只需要在表操作的时候添加连接查询的条件就可以了
修改前:

修改后:
*例如select * from emp , dept where emp.dept_id = dept.id;
二.多表查询的分类
*分类情况如下
一.连接查询:
*1.内连接:相当于查询A,B两张表的交集部分数据
*内连接的语法结构有两种:显示内连接和隐式内连接
*1)首先先介绍隐式内连接的语句结构
*select 字段列表 from 表1,表2 where 条件
案例:
A. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
表结构: emp , dept
连接条件: emp.dept_id = dept.id
*查询: select e.name,d.name from emp e,dept d where dept_id = dept.id ;
*2)继续介绍显示内连接
语句结构 : select 字段列表 from 表1 inner join 表2 on 条件
B. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) --- INNER JOIN ...
ON ...
表结构: emp , dept
连接条件: emp.dept_id = dept.id
select e.name,d.name from emp e inner join dept d on on dept_id = dept.id;
*注意:一旦为表起了别名,就不能再使用表名来指定对应的字段了,此时只能够使用别名来指定字 段。
内连接的介绍就告此段落了,接下来我门介绍外连接
*2.外连接包括左外连接和右外连接两种方式
*用途: 在查询多表时,需要展现一张表的全部数据,
*1)左外连接的语法结构: select 字段列表 from 表1 left join 表2 on 条件 (表1的全部数据和表二的部分数据)
A. 查询emp表的所有数据, 和对应的部门信息
由于需求中提到,要查询emp的所有数据,所以是不能内连接查询的,需要考虑使用外连接查询。
表结构: emp, dept
连接条件: emp.dept_id = dept.id
- 查询: select e.*,d.name from emp e left dept d on dept_id =dept.id ;
*2)右外连接原理和左外连接相同,就是把left换成right,代表的是左表的部分数据和右表的全部数据;
注意事项:
左外连接和右外连接是可以相互替换的,只需要调整在连接查询时SQL中,表结构的先后顺
序就可以了。而我们在日常开发使用时,更偏向于左外连接
*3.自连接查询
-
子连接查询顾名思义就是自己连接自己,把一张表查询多次
-
在子连接语法中必须给表起别名,因为需要对同一张表查询多次
-
语法结构: select 字段列表 from 表1 别名1 join 表1 别名2 on 连接条件
-
注意:对于子查询来说可以是内连接也可以是外连接
案例: A. 查询员工 及其 所属领导的名字 表结构: emp
*查询: select a.name,b.name from emp a,emp b where a.managerid = b.id;
B. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
表结构: emp a , emp b
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =
b.id;
*4.联合查询
** 联合查询顾名思义就是将多次查询的结果同时展现出来形成一个新的查询结果集
** 语法结构: select 字段列表 from 表名1 where 条件
union(all) 注意:当没有all时会进行去重操作 有all时不会进行去重操作 就像all的表面意思一样
select 字段列表 from 表名2 where条件
注意:
如果多条查询语句查询出来的结果,字段数量不一致,在进行union/union all联合查询时,将会报
错。如
二.子查询
** 子查询是个重点 包括标量,列,行,表子查询
根据子查询位置,分为:
A. WHERE之后
B. FROM之后
C. SELECT之后
-
标量子查询
介绍: 子查询返回的值是单值,最简单的形式就是标量子查询
例如: 查询销售部员工的信息
分析: 先查询销售部的id值 在根据查询到的id 值进行在tb_user 查询条件为 dept_id = id
select id from dept where name = ‘销售部’;
select * from tb_user where dept_id = id
简单写就是嵌套循环
select * from tb_user where dept_id = (select id from dept where name = ‘销售部’)
2.案例二 查询东方白入职之后的员工信息
分析: 先查询入职日期, 在根据条件下一步操作
select date from tb_user where name = '东方白'.
select * from tb_user where date > date
嵌套循环
select * from tb_user where date > (select date from tb_user where name = '东方白'.)
2.列子查询
介绍: 子查询的结果是一列或者多行
常用操作符
IN
在指定的集合范围之内,多选一
NOT IN
不在指定的集合范围之内
ANY
子查询返回列表中,有任意一个满足即可
SOME
与ANY等同,使用SOME的地方都可以使用ANY
ALL
子查询返回列表的所有值都必须满足
案例1.A. 查询 "销售部" 和 "市场部" 的所有员工信息
select id from dept where name = 销售部 or name = 市场部 查询id
select * from tb_user where dept_id in (select id from dept where name = 销售部 or name = 市场部 ) 使用in
案例.2.查询比 财务部 所有人工资都高的员工信息
分析: 查询id 接着查询工资 最后查询员工信息
select id from dept where name = 财务部
select salary from tb_user where dept_id = id
select * from tb_user where salary > all(select salary from tb_user where dept_id = (select id from dept where name = 财务部) 关键词 all 如果题目要求是大于其中一个就写any就可以
3.行子查询
查询的结果是一行或者多列
常用的操作符:= 、<> 、IN 、NOT IN
A. 查询与 "张无忌" 的薪资及直属领导相同的员工信息
分析: 分为两步
1.查询薪资和领导信息
select salary,manaderid from emp where name = '张'
2.查询员工信息 进行嵌套循环
select * from emp where (saraly,managerid) = (select saraly,managerid from emp where name = '张')
4.表子查询
介绍: 查询的结果返回的是多行多列
常用的操作符 in
A. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
一样分两部分析
select saraly,managerid from emp where name = 鹿 or name =宋
select * from emp where ( saraly,managerid) in (select saraly,managerid from emp where name = 鹿 or name =宋)
小结: 行子查询和表子查询一样,表子查询是在行子查询的基础上完成的