四、多表查询

多表关系

• 一对一：适用于单表拆分
• 多对一：
• 多对多：需建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

多表查询概述

在多表查询时，需消除无效的笛卡尔积

内连接(交集)

如果为表起了别名，则不能通过表原来的名字限制字段

• 隐式内连接：
  select 字段列表 from 表1，表2 where 条件；

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;

• 显示内连接：
  select 字段列表 from 表1 [ inner ] join 表2 on 连接条件;

select e.name,d.name from emp e inner join dept d on e.dept_id=d.id;

外连接

• 左外连接：
  select 字段列表 from 表1 left [outer] join 表2 on 条件

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

左右连接相对于join而言，右连接通常可变为左连接

• 右外连接：
  select 字段列表 from 表1 right [outer] join 表2 on 条件

select d.*,e.* from emp e right outer join dept d on e.dept_id=d.id;

• 左外连接保留左表的全部数据，右表只返回匹配的数据，以左表为主；右外连接则与之相反。

自连接

自连接查询可以是内连接，也可以是外连接（可查询所有内容）
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;

联合查询

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

-- 仅合并
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;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

子查询（嵌套查询）

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

标量子查询（单行单列）

列子查询

操作符	描述
any	子查询返回列表中，有任意一个满足即可
some	与any等同
all	子查询返回列表的所有值都必须满足

行子查询

-- 查询与“张无忌”的薪资及直属领导相同的员工信息；

-- a.查询“张无忌”的薪资及直属领导
select salary,managerid from emp where name="张无忌";
-- b.查询与“张无忌”的薪资及直属领导相同的员工信息
select * from emp where salary=12500 and managerid=1;
select * from emp where (salary,managerid)=(12500,1);

-- 行子查询
select * from emp where (salary,managerid)=(select salary,managerid from emp where name="张无忌");

表子查询

常用关键字in

select * from emp where (job,salary) in (select job,salary from emp where name='lu' or name = 'song');

表子查询的结果通常作为一张临时表

select e.*,d.* from (select * from emp where entrydate >'2006-01-01') e left join dept d on e.dept_id=d.id;