Mysql 多表关联查询

1. Mysql中表之间的关系

1.1 多表关系

Mysql多表之间具有三种关系：一对一、一对多、多对多

• 一对一：一张表中的一条数据对应另外一张表中的一条数据，比如一个人只有一张身份证，一张身份证对应一个人。一对一关系比较少见，因为一对一这种关系的表可以合并成一张表
• 一对多：一张表中的一条数据可以和另外一张表中多条数据关联，拿部门表和员工表举例，一个部门有多个员工，一个员工只能对应一个部门；可以在多的一方建立外键，指向唯一的一方的主键进行关联
• 多对多：拿学生表和课程表举例，一个学生可以选择多门课程，一个课程也可以被很多学生选择；多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，将多对多的关系，拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键

1.2 外键约束

外键约束（foreign key）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。在有外键约束的前提下，不能直接删除主表中的数据，因为从表对主表有关联关系，但是可以删除从表中的数据。在实际sql查询中，外键约束对查询无影响。

外键约束的创建：

语法1，在创建表的时候设置外键约束，在 create table 语句中，通过 foreign key 关键字来指定外键：
[constraint <外键名>] foreign key 字段名 [，字段名2，…] references <主表名> 主键列1 [，主键列2，…]

示例1：
-- 部门表
create table if not exists dept(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);
-- 员工表
create table if not exists emp(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20),  -- 员工所属部门
  constraint emp_fk foreign key (dept_id) references dept (deptno) –- 外键约束
);

语法2，创建表之后创建外键约束
alter table <数据表名> add constraint <外键名> foreign key(<列名>) references 
<主表名> (<列名>);

示例2：
-- 创建部门表
create table if not exists dept2(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);
-- 创建员工表
create table if not exists emp2(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20)  -- 员工所属部门
 
);
-- 创建外键约束
alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2 (deptno);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

在表的多对多关系中，我们只需要在中间表创建两个外键约束即可。

外键约束的删除：

语法：
alter table <表名> drop foreign key <外键约束名>;

示例：
alter table emp2 drop foreign key dept_id_fk;
1
2
3
4
5

2. 多表联合查询

2.1 交叉连接查询：笛卡尔积

select * from A,B; 
1

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

2.2 内连接查询：inner join

-- 隐式内连接：
select * from A,B where 条件; 

-- 显示内连接：
select * from A inner join B on 条件;

-- 示例：
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
1
2
3
4
5
6
7
8
9

2.3 外连接查询

2.3.1 左连接：

-- 语法：
select * from A left outer join B on 条件;

-- 示例：
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
1
2
3
4
5

2.3.2 右连接：

-- 语法：
 select * from A right outer join B on 条件;
 
-- 示例：
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
1
2
3
4
5

2.3.4 满外连接：

在mysql中，我们经常使用union关键字实现两个表（两个查询）的满外连接，两者的区别如下：

• union： 会自动压缩多个结果集合中的重复结果，对两个结果集进行并集操作，不包括重复行
• union all： 则将所有的结果全部显示出来，不管是不是重复，包括重复行

使用union关键字需要注意：

• union 和 union all 内部的 select 语句必须拥有相同数量的列
• 每条 select 语句中列的顺序必须相同

-- 使用union关键字实现左外连接和右外连接的并集
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id
union 
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
1
2
3
4

2.4 子查询：

子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询。

-- 1. 子查询返回单行单列
select eid,ename,age from emp3 where age = (select max(age) from emp3);

-- 2. 单行多列
select eid,ename,t.name from emp3 where dept_id in (select deptnofrom dept3 where name = '研发部' or name = '销售部') ;

-- 3. 多行多列
select eid,age,ename,name from (select * from dept where name = '研发部 ')t1,(select * from emp3 where age <20)t2
1
2
3
4
5
6
7
8

子查询中的关键字：

-- 1. all关键字：与子查询返回的所有值比较为true 则返回true
-- 查询年龄大于‘1003’部门所有年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003’);

-- 2. any（some）关键字：与子查询返回的任何值比较为true 则返回true
-- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息
select * from emp3 where age > any(select age from emp3 where dept_id = '1003’)

-- 3. in关键字：用于判断某个记录的值，是否在指定的集合中
-- 查询研发部和销售部的员工信息，包含员工号、员工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部') ;

-- 4. exists关键字：EXISTS后面的子查询不返回任何实际数据，只返回真或假，当返回真时 where条件成立,EXISTS关键字，比IN关键字的运算效率高
-- 查询公司是否有大于60岁的员工，有则输出
select * from emp3 a where exists(select * from emp3 b where a.age > 60);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2.5 表自关联

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

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