多表查询
查询也就是DQL语句,接下来要进行多张表进行一个联查操作。
多表关系
- 一对多(多对一)
例如:一个部门有多个员工,而一个员工只能在一个部门,其中部门表就是一,员工表就是多。
如何实现:在员工表中建立外键,指向部门表中的主键进行联系
- 多对多
例如:一个学生可以选择多门课程,一门课程也可以由多个学生选择
实现:建立中间表,包含两个外键,用来关联两个表的主键
- 一对一
例如:用户表和用户详情表
实现:一张表中加外键,关联另一张表的主键,设置外键为UNIQUE
多表查询
概述:多张表联查得到数据
例如:select * from emp,dept;
这句sql你可要注意了,这句话将会导致出现笛卡尔积现象。
何为笛卡尔积?
两张表直接进行连接,会导致emp表和dept表的有些数据没有意义。所以要消除笛卡尔积。
多表查询分类
连接查询
emp表:
dept表:
(1)内连接
取两个表的交集部分
- 隐式内连接:
select 字段列表 from 表1,表2 where 条件;
-- 查询每一个员工的姓名,及关联的部门的名称
select e.name,d.id
from emp e,dept d
where e.dept_id=d.id;
结果:
- 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件
-- 查询每一个员工的姓名,及关联的部门的名称
select e.name,d.id
from emp e
join dept d
on e.dept_id=d.id;
结果和隐式内连接相同,此处不再重复
(2)外连接
<1>左外连接
查询左表的数据以及左表和右表交集的数据
语法:select 字段列表 from 表1 left [outer] join 表2 on 条件;
-- 查询emp表的所有数据和对应部门的信息
select e.*,d.name
from emp e
left join dept d
on e.dept_id=d.id;
结果:
此处注意表的记录由17条,而刚刚隐式和显式的内连接记录只有16条,是因为第17条记录中的员工没有对应的部门号,但是对emp表中的记录进行一个保留,没有部门,则用null值来进行填充
以emp为主,以dept表为辅
<2>右外连接
右外连接和左外连接差不多,将左外连接dept表和emp表互换,left改为right,即可形成同样的结果
-- 查询emp表的所有数据和对应部门的信息
select e.*,d.name
from dept d
right join emp e
on d.id=e.dept_id;
右外连接中主要看右边,也就是emp表,所以结果和刚刚的左外连接结果是一样的
(3)自连接
自己连接自己,也就是将一张表看成几张表进行连接查询
自连接,可以内连接查询,也可以外连接查询
-- 查询员工及其领导的名字
select a.name,b.name
from emp a
left join emp b
on a.managerid=b.id;
你可要注意了,这里把a当作了员工表,b当作了领导表,查出来的是员工的名字和领导的名字,其中加left保证了即使该员工没有领导(也就是该员工就是老板)也会显示出来
不加left会导致,没有老板的员工将不会显示,此处不再赘述。
联合查询
概述: 也就是将多次查询结果进行合并得到新的查询结果
语法:
select 字段列表 from 表A
union [all]
select 字段列表 from 表B
注意:
- 对于联合查询多表的列数和字段类型保持一致
- union all 将数据直接合并,而union会进行一个去重操作
- 直接将两次查询的记录拼起来(从上到下直接拼到一起)
例1:
-- 将薪资低于5000的员工和年龄大于50岁的员工全部查询处出来
select *
from emp
where salary<5000
union all
select *
from emp
where age>50;
用到了union all数据由重复记录
例2:
-- 将薪资低于5000的员工和年龄大于50岁的员工全部查询处出来
select *
from emp
where salary<5000
union
select *
from emp
where age>50;
利用union无重复记录
子查询
概念:sql中继续嵌套select语句,也就是嵌套查询(子查询)
种类 | 含义 |
---|---|
标量子查询 | 子查询结果为单个值 |
列子查询 | 子查询结果为一列 |
行子查询 | 子查询结果为一行 |
表子查询 | 子查询结果为多行多列 |
两张表:
emp
dept
标量子查询
概念:子查询返回的是单个值(数字,字符串)
例1:
-- 需求:查询销售部的所有员工信息
-- 1、查询销售部的部门号(部门号为4)
select id
from dept
where name='销售部';
-- 2、根据销售部门的id查询所有员工信息
select *
from emp
where dept_id=4;
-- 直接子查询
select *
from emp
where dept_id=(select id from dept where name='销售部');
例2:
-- 方东白入职之后的员工信息
-- 1、查询东方白入职时间
select entrydate from emp where name='方东白';
-- 2、根据入职时间查询所有该时间的员工信息
select * from emp where entrydate='2009-02-12';
-- 子查询
select * from emp where entrydate=(select entrydate from emp where name='方东白');
列子查询
概念:子查询的返回结果是一列(多行)
常用操作符:
操作符 | 描述 |
---|---|
in | 在指定的集合范围内,多选一 |
not in | 不在指定的集合范围之内 |
any | 子查询返回列表中,任意满足一个即可 |
some | 与any等同 |
all | 子查询返回列表的所有值必须满足 |
例1:
-- 查询销售部和市场部的所有员工信息
-- 1、查询销售部和市场部们的id(2,4)
select id from dept where name='销售部' or name='市场部';
-- 2、根据部门id查询两个部门的员工信息
select * from emp where dept_id in (2,4);
-- 直接子查询
select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部');
例2:
-- 查询比财务部所有人工资都高的员工信息
-- 1、查询财务部的所有人工资
select id from dept where name='财务部';
select salary from emp where dept_id=3;
-- 2、查询笔财务部所有人工资都高的员工信息
-- 直接一步到位(嵌套)
select * from emp where salary > all ( select salary from emp where dept_id =
(select id from dept where name = '财务部') );
没有比财务部工资更高的,所以查出空表
例3:
-- 查询比研发部任意一人工资高的员工信息
-- 1、查研发部的所有人工资
select id from dept where name='研发部';
select salary from emp where dept_id=(select id from dept where name='研发部');
-- 2、比研发部任意一人工资高的员工信息
select * from emp where salary > any(select salary from emp where dept_id=(select id from dept where name='研发部'));
行子查询
概念:子查询返回的结果是一行(可以是多列)
例子:
-- 查询与张无忌薪资机器直属领导相同的员工信息
-- 1、查询张无忌的薪资以及直属领导
select salary,managerid from emp where name='张无忌';
-- 2、查询与张无忌薪资以及直属领导相同的员工信息(一步到位)
select * from emp where (salary,managerid) =(select salary,managerid from emp where name='张无忌');
结果:
表子查询
概念:返回的结果是多行多列
例1:
-- 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
-- 1、查询 "鹿杖客" , "宋远桥"职位以及薪资
select job,salary from emp where name='鹿杖客' or name='宋远桥';
-- 2、查询和他俩职位,薪资相同的员工信息
select * from emp where (job,salary) in (select job,salary from emp where name='鹿杖客' or name='宋远桥') ;
结果:
例2:
-- 1、入职日期是2006-01-01之后的员工信息
select * from emp where entrydate>'2006-01-01';
-- 2、这些员工的部门信息
select e.*,d.*
from (
select * from emp where entrydate>'2006-01-01'
) e
left join dept d
on e.dept_id=d.id;
结果: