MySQL —— 多表联合查询

介绍

多表查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的时候，需要显示的数据来自多张表，多表查询有以下分类：

• 交叉连接查询：

select * from A,B；

• 内连接查询(使用的关键字inner join --inner可以省略)

#隐式内连接(SQL92标准)
select * from A,B where 条件;
#显示内连接(SQL99标准)
select * from A inner join B on 条件;

• 外连接查询(使用的关键字outer join --outer可以省略)

#左外连接
select * from A left outer join B on 条件;
#右外连接
select * from A right outer join B on 条件;
#满外连接
select * from A full outer join B on 条件;

• 子查询：select的嵌套
• 表自关联：将一张表当成多张表来用

注意： 外键约束对于多表查询并无影响

交叉连接查询

• 交叉连接查询返回被连接的两个表所有数据行的笛卡尔积
• 笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配
• 假如A表有M行数据，B表有N行数据，则返回M*N行数据
• 笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选

#格式:
select * from 表1,表2,表3...;
#实现：
select * from dept,emp;

内连接查询

内连接查询求多张表的交集

#格式：
select * from A,B where 条件; --隐式内连接(SQL92标准)
select * from A inner join B on 条件; --显示内连接(SQL99标准)
#实现：
//查询每个部门的所属员工
select * from dept,emp where dept.deptno = emp.dept_id;
select * from dept innter join emp on dept.deptno = emp.dept_id;

外连接查询

外连接分为左外连接(left outer join)、右外连接(right outer join)、满外连接(full outer join)
注意： oracle里面有full join,可是在mysql对full join支持的不好，我们可以使用union来达到目的

#格式：
select * from A left outer join B on 条件; --左外连接
select * from A right outer join B on 条件; --右外连接
select * from A full outer join B on 条件--满外连接
#实现：
//查询哪些部门有员工，哪些部门没有员工
select * from dept left outer join emp on dept.deptno = emp.dept_id; --左外连接
//查询员工有没有对应的部门
select * from dept  right outer join emp  on dept.deptno = emp.dept_id; --右外连接
//使用union实现满外连接,区并集
select * from dept left outer join emp on dept.deptno = emp.dept_id
union
select * from dept  right outer join emp  on dept.deptno = emp.dept_id;

子查询

子查询就是指的在一个完整的查询语句中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询
特点： 子查询可以返回的数据类型一共分为四种

• 单行单列：返回的是一个具体列的内容，可以理解为一个单值数据
• 单行多列：返回一行数据中多个列的内容
• 多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围
• 多行多列：查询返回的结果是一张临时表

例如：

#获取张三所在的部门详情
select * from dept where deptno = (select dept_id from emp where ename='张三')

子查询关键字：
在子查询中，有一些常用的逻辑关键字，这些关键字可以给我们提供更丰富的查询功能，主要关键字如下

• all
• any
• some
• in
• exists

子查询关键字-all：
格式：

select ... from ... where c > all(查询语句)
#等价于：
select ... from ... where c > result1 and c > result2 and c > result3 ...

特点：

• all:与子查询返回的所有值比较，如果为true，则返回true，反之则返回false
• all可以与=、>、>=、<、<=、<>结合来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于子查询返回结果中的所有数据
• all表示指定列中的值必须大于子查询集的每一个值，即必须要大于子查询集的最大值；如果是小于号即小于子查询集的最小值，同理可以推出其它的比较运算符的情况。

实现：

#查询年龄大于'1003'部门所有年龄的员工信息
select * from emp where age > all(select age from emp where dept_id = '1003');
#查询不属于任何一个部门的员工信息
select * from emp where dept_id != all(select deptno from dept);

子查询关键字-any和some：
格式：

select ... from ... where c > any(查询语句)
#等价于：
select ... from ... where c > result1 or c > result2 or c > result3 ...

特点：

• any:与子查询返回的任何值比较为true，则返回true
• any可以与=、>、>=、<、<=、<>结合来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于子查询返回结果中的任何一个数据
• any表示指定列中的值要大于子查询中的任意一个值，即必须要大于子查询集中的最小值，同理可以推出其他的比较运算符的情况
• some和any的作用一样，some可以理解为any的别名

实现：

#查询年龄大于'1003'部门任意一个员工年龄的员工信息
select * from emp where age > any(select age from emp where dept_id = '1003') and dept
_id != '1003';

子查询关键字-in：
格式：

select ... from ... where c in(查询语句)
#等价于：
select ... from ... where c = result1 or c = result2 or c = result3 ...

特点：

• in关键字，用于判断某个记录的值是否值指定的集合中
• 在in关键字前面加上not可以将条件反过来

实现：

#查询研发部门和销售部的员工信息，包含员工号、员工名字
select eid,ename from emp where dept_id in (select deptno from dept where name = '研发部' or '销售部');

子查询关键字-exists：
格式：

select ... from ... where exists(查询语句)

特点：

• 该子查询如果“有数据结果”，则返回true，外层查询执行
• 该子查询如果“没有数据结果”，则返回false，外层查询不执行
• exists后面的子查询不返回任何实际数据，只返回真或假，当返回真时，where条件成立
• exists关键字比in关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐使用exists关键字

实现：

#查询公司是否有大于60岁的员工，有则输出
select * from emp a where exists(select * from emp b where a.age > 60);
#查询有所属部门的员工信息
select * from emp a where exists(select * from dept b wehre a.dept_id = b.deptno);

自关联查询

MySQL有时在信息查询时需要对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用，注意自关联时必须给表起别名
格式：

select 字段列表 from 表1 a,表1 b where 条件;
#或
select 字段列表 from 表1 a [left] join 表1 b on 条件;

实现：

#创建表，并建立自关联约束
create table t_sanguo(
	eid int primary key,
	ename varchar(20),
	manager_id int,
	foreign key(manager_id) references t_sanguo(eid) --添加自关联约束
);