MySQL学习（9）：多表查询

1.多表关系

1.1一对多

1.2多对多

1.3一对一

设置外键唯一，是为了让两张表的数据一一对应

2.多表查询

2.1多表查询案例

现有父表（dept）如下：

子表（emp）如下：

让子表的dept_id作为外键与主表的id产生联系。

*如果同时查询2个表的信息：

select * from emp,dept;

则数据库并不会报错，而是会给出2个表每行数据排列组合出的结果：下图中截取了产生的102行数据的一部分。

显然这种结果是没有意义的。

要去除冗余结果，需要使用下面的语句：

select * from emp,dept where emp.dept_id = dept.id;

这条语句产生了16行结果，因为子表中第17行数据的dept_id是null

2.2内连接

内连接查询指的是查询2张表的交集部分。

内连接查询有2种语句：隐式内连接、显式内连接

select 字段列表 from 表1,表2 where 条件...;
#隐式内连接

select 字段列表 from 表1 join 表2 [inner] on 连接条件...;
#显式内连接。inner可以忽略
#二者除了语法不同，并没有本质区别

以前面的例子为例，其2种内连接查询写法如下：

显式内连接语句中给2张表起了别名

得到结果一部分如下：

2.3外连接

外连接有2种：左外连接、右外连接

select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;
#左外连接，查询表1的所有数据以及2张表的交集部分

select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;
#右外连接，查询表2的所有数据以及2张表的交集部分

继续以2.1的例子为例，其左外连接查询写法如下：

左连接结果如下：

其右外连接查询写法如下：

上图第2条语句是以左外连接代替右外连接的写法

右连接结果如下：

2.4自连接

以下图的表为例，managerid字段的数据填的是id字段的数据，自身的字段之间产生了联系。可以理解为managerid字段是外键与主键id产生了联系。

因此，自连接可以使用内连接与外连接的语句，只需要对自身起2个不同的别名

*内连接案例语句如下：

*外连接案例语句如下：

外连接可以把外键处数据值为null的数据也查询出来

*2.5联合查询

联合查询（union查询）就是把多次查询的结果合并

select 字段列表 from 表A...
union [all]
select 字段列表 from 表B...;
#2条select语句后的字段列表要保持列数相同，也就是字段个数相同

以下图为例：

其结果为：

从上图可以看出，数据可以产生重复，如果想避免重复，可以使用下面的语句：

结果为：

union all会将所有结果合并在一起，union会对合并后的结果去重

3子查询

sql语句（select、update、delete）中嵌套select语句，称为嵌套查询，又称子查询

3.1标量子查询

子查询返回的结果为单个值，则称为标量子查询

以下图为例：

a语句先查询出东方白的入职日期为2009-02-12。再由b语句进行条件筛选查询。那么就可以通过子查询将2者合并为1条语句：

3.2列子查询

子查询返回的结果为一列，则称为列子查询。

重用操作符：in、not in、any、some、any

in	在指定的集合范围内多选一
not in	不在指定的集合范围内
any	子查询返回列表中，有任意一个满足即可
some	同any
all	子查询返回列表中所有值都必须满足

in案例：

all案例：

any案例：

3.3行子查询

子查询返回的结果为一行，则称为行子查询。

重用操作符：in、not in、=、！=

使用案例如下：

*上图中出现了(salary,managerid)这样的（字段1,字段2）形式的条件判断方法

3.4表子查询

子查询返回的结果为多行多列，则称为表子查询。

常用操作符为in

in使用案例如下：

结合外连接使用案例如下：

4.多表查询总分类

连接查询	内连接（查询A、B交集）	隐式内连接
		显式内连接
	外连接	左外连接（查询左表数据以及2表的交集数据）
		右外连接（查询右表数据以及2表的交集数据）
	自连接（当前表与自身的连接查询）
子查询	标量子查询	返回的结果为单个值
	列子查询	返回的结果为一列
	行子查询	返回的结果为一行
	表子查询	返回的结果为多行多列