多表查询

1. 表与表之间的关系

<1> 一对一

• 用户表和身份信息表，用户表是主表

• 男人表、女人表

<2> 一对多

• 最常见得表关系，用户表和订单表

• 员工表、部门表 create table emp(empno int primary key auto_increment,ename varchar(32)deptno int);

<3> 多对多

• 学生表和课程表，通常情况都是将多对多的关系拆分为一对多或者多对一的关系

• 至少需要三张表

2. 为什么要使用多张表

• 避免出现大量的数据的冗余

• 并不是表拆的越多越好，需要根据实际情况进行拆分

3. 概念

• 同时查询多张表

4. 多表查询的分类

<1> 合并查询

• union, union all

• 合并结果集，就是把两个 select 语句的查询结果合并到一起。（相当于并集）

• 合并的两个结果，列数和列的顺序，类型需要一致

<2> 连接查询

-- 员工表create table emp(  empno int primary key auto_increment,  # 员工编号  ename varchar(32),  # 员工姓名  job varchar(32),    # 员工职位  mgr int,              # 上级编号  hiredate date,      # 入职时间  sal double,          # 薪资  comm double,        # 奖金  deptno int          # 员工所属部门);
-- 部门表create table dept(  deptno int primary key auto_increment,  # 部门编号  dname varchar(32),  # 部门名称  loc varchar(32)      # 部门地址);

• 内连接

• inner join .... on 、 join , ,

• inner join 是一个比较运算符，只返回符合条件的行

• 例如：

• select * from emp inner join dept on emp.deptno=dept.deptno;

• select * from emp e, dept d where e.deptno = d.deptno;

• select * from emp e join dept d where e.deptno = d.deptno;

• 外连接

• 左外连接 LEFT OUTER JOIN | left join .... on

• 代表查询，左边行的全部，右边没有则 null

• select * from emp e LEFT OUTER JOIN dept d on e.deptno = d.deptno;

• 右外连接 right join | right outer join .... on

• 有连接包含 right join 右表所有的行，如果左表中某行在右表没有匹配，则结果中对应的左表的部分全部为空(null)

• select * from emp e RIGHT OUTER JOIN dept d on e.deptno = d.deptno;

• 自连接

• 自连接就是说，在同一个数据表中，看作是两个表，表示查找每个人的领导，如果没有领导，则显示无领导

• 把一张表看作成两张表，一张员工表，一张领导表，都是 emp 表

• select e.ename, el.ename from emp e left join emp el on e.mgr = el.empno;

• 自然连接: natural join (join) | natural left join (同 left join) | natural right join (同 right join)

• 自然连接会自动判断，以两个表中相同的字段为连接条件，返回查询结果。

• 注意： 内连接不写连接条件会出现笛卡尔积的结果，应该避免这种情况，而外连接不写连接条件会报错

• select * from emp natural join dept;

• select * from emp NATURAL left join dept;

• select * from emp NATURAL right join dept;

<3> 子查询 (ANY 子查询、IN 子查询、SOME 子查询、ALL 子查询)

• 子查询解决的问题：

• 谁的薪资比张三高

• select sal from emp where ename='张三'

• 定义

• 子查询允许把一个查询嵌套在另一个查询当中

• 子查询又叫做内部查询，相当于内部查询，包含内部查询的就成为外部查询，子查询的结果被主查询所使用。

• 注意的问题：

• 括号

• 可以在主查询的 where select having from 后面，都可以使用子查询

• 不可以在 group by 后面使用子查询

• 主查询和子查询可以不是同一张表；只有子查询返回的值，主查询可以使用需求：查询部门名称是人力部的员工信息-- 第一种方式：利用子查询 select * from emp where deptno=(select deptno from dept where dname='人力部')-- 第二种方式：利用关联查询 select * from emp e, dept d where e.deptno = d.deptno and d.dname = '人力部';

• from 后面的子查询需求：查询员工号 姓名 薪资 select empno, ename, sal from emp;

• 一般不在子查询中进行排序

• 一般先执行子查询，再去执行主查询

ANY 关键字

假设any内部的查询返回结果个数是三个，如: result1, result2, result3, 那么select .... from .... where a > any(....)->select .... from .... where a > result1 or a > result2 or a > result3
需求：  查询工资比1号部门中任意一个员工高的员工信息  select * from emp where sal > any(select sal from emp where deptno = 1);

ALL 关键字

• ALL 关键字与 ANY 关键字类似，只不过把上面的 or 改成 and

SOME 关键字

some 关键字和 any 关键字是一样的功能，所以：select .... from .... where a > any(....)->select .... from .... where a > result1 or a > result2 or a > result3

IN 关键字

In 运算符用于where 表达式中，以列表项的形式支持多个选择，语法如下：  where column in (v1, v2, v3 ....);  where column not in (v1, v2, v3 ....);  当in 前面加上not 运算符时，表示与in 相反的意思，即不在这些列表项中选择。  
案例：  查询部门名称是人力部和研发部的员工select * from emp where deptno in (select deptno from dept where dname = '人力部' or dname = '研发部');