【多表查询】---------------------三大范式

1.笛卡尔积

#查询的是笛卡尔积
select * from tb_emp,tb_dept;
#消除无效的笛卡尔积
select * from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;

.cn/direct/79fe67bf97b243f89da085406db8ceb6.png)

在这里插入图片描述

2.内连接外连接

内连接

内连接：交集
左外连接、右外连接
在这里插入图片描述

#隐式内连接
select tb_emp.name ,tb_dept.name from tb_emp ,tb_dept where tb_emp.dept_id = tb_dept.id;
#显示内连接
select tb_emp.name ,tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;#表起别名
select e.name ,d.name from tb_emp e inner join tb_dept d on e.dept_id = d.id;

外连接

左外连接：左表全部数据，即使数据没有和右表产生关联
右外连接：右表全部数据，即使数据没有和左表产生关联

select e.name ,d.name from tb_emp e left join tb_dept d on e.dept_id = d.id;select e.name ,d.name from tb_emp e right join tb_dept d on e.dept_id = d.id;

3.子查询

在这里插入图片描述

1.标量子查询，子查询返回的值是单个值

# 返回校验部所有员工的信息
select id from  tb_dept where name = '教研部'; # id = 2
select * from tb_emp where dept_id = 2;
#合并
select * from tb_emp where dept_id = (select id from  tb_dept where name = '教研部');#方东白入职之后的员工信息
select entrydate from tb_emp where name = '方东白';
select * from tb_emp where entrydate > '2012-11-01';select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');

2.列子查询

返回值是一列多行

#查询教研部 咨询部的所有员工信息
# a.查询部门id
# b.根据id，查询员工信息select id from tb_dept where name = '教研部' or name = '咨询部';
select * from tb_emp where dept_id in(3,2);
#合并
select * from tb_emp where dept_id in(select id from tb_dept where name = '教研部' or name = '咨询部');

3.行子查询

select entrydate,job from tb_emp where name = '韦一笑';
select * from tb_emp where entrydate = '2007-01-01' and job = 2;select * from tb_emp where (entrydate,job) = ('2007-01-01',2);
select * from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name = '韦一笑');

4.表子查询


# 查询入职时间是2006-01-01之后的员工信息，及其部门名称
# a.查询入职时间是2006-01-01之后的员工信息
# b插叙这部分员工信息及其部门名称select * from tb_emp where entrydate > '2006-01-01';
select e.* ,d.name from (select * from tb_emp where entrydate > '2006-01-01') e, tb_dept d where e.dept_id = d.id;