一对多/多对一关系
典型的一对多关系是部门表和员工表之间的关系，一个员工只能有一个部门，一个部门可以有多个员工：
这种关系体现在员工表（emp）通过一个外键（dept_id）关联部门表（dept）。
可以在 DataGrip 中通过表结构右键->图->显示图查看表之间的关联关系图表。
多对多关系
典型的多对多关系是学生选课，一个学生可以选择多门课程，一个课程也可以被多个学生选择：
通过表示关联关系的学生-课程表（student_course）将学生表（student）和课程表（course）关联起来，并分别通过外键（studentid 和 courseid）关联两张表。
一对一关系
一对一关系常用于表拆分，比如出于读写性能的考虑，通常会将一张表中会频繁修改的字段单独拆分出来，比如下面的用户表和用户详情表：
用户详情表（t_user_detail）通过外键（user_id）关联到用户表（t_user），这和多对一/一对多关系的实现类似，不同的是作为外键的字段（user_id）是不会重复的，因此可以添加一个唯一约束：
create table t_user_detail
(
id bigint unsigned auto_increment comment '主键'
primary key,
address varchar(50) null comment '家庭住址',
phone varchar(10) not null comment '联系电话',
user_id bigint unsigned null comment '员工id',
constraint t_user_id
unique (user_id),
constraint t_user_fk
foreign key (user_id) references t_user (id)
)
comment '员工详情表';
除了这种方式，更直观的方式是让用户详情表的主键与用户表一致，即不使用自增主键，直接使用用户表的主键值。但这样做存在一些限制，比如可能某些持久层框架不支持这种方式的主键。
多表查询
多表查询实际上就是将多张表数据进行笛卡尔积，然后按照需要取集合的交集（内连接）、或补集（外连接）等。
这里演示用的表数据：
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称'
)comment '部门表';
create table emp(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID'
)comment '员工表';
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部');
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);
内连接
内连接是取两张表笛卡尔积的交集，从实现上有隐式内连接和显式内连接两种。
隐式内连接：
select emp.*, dept.name as dept_name
from emp,dept
where emp.dept_id=dept.id;
显式内连接：
select emp.*, dept.name as dept_name
from emp inner join dept on emp.dept_id = dept.id;
外连接
外连接实际上是包含了一张表的完整数据以及和另一张表的交集部分，从实现上分为左外连接和右边外连接。
select emp.*, dept.name as dept_name
from emp left join dept on emp.dept_id = dept.id;
其等效于：
select emp.*, dept.name as dept_name
from dept right join emp on emp.dept_id = dept.id;
在一对多关系中，通常是基于从表进行外连接（显示从表的所有数据以及交集），基于主表进行外连接没有实际意义，但依然可以这么做：
select dept.*, emp.id as emp_id, emp.name as emp_name
from emp right join dept on emp.dept_id = dept.id;
查询结果：
自连接
一张表可以和自身进行联表查询，比如员工表中存在一个 managerid 字段，表示员工的领导，该字段的值实际上也是员工表的主键值。如果我们要通过 SQL 获取员工表以及员工的领导姓名，就需要通过自连接进行查询：
select a.id, a.name, a.managerid, b.name as manager_name
from emp as a, emp as b
where a.managerid=b.id;
需要注意的是，这样做查询结果中不包含 managerid 为 null 的员工信息，因为这是内连接。如果要包含，需要使用外连接进行自连接：
select a.id, a.name, a.managerid, b.name as manager_name
from emp as a left join emp as b
on a.managerid=b.id;
联合查询
可以利用联合查询将多个查询结果进行合并：
select *
from emp
where age>50
union all
select *
from emp
where salary>2000;
查询结果中同时包含了年龄大于50以及工资大于2000的员工。
需要注意的是，使用 union all 实现的联合查询中可能包含重复数据，如果要对联合查询结果进行去重，要使用 union 而非 union all：
select *
from emp
where age>50
union
select *
from emp
where salary>2000;
子查询
如果子查询的结果是一个值，可以作为查询条件使用：
select *
from emp
where salary>(select salary from emp where name='张无忌');
这里利用子查询查询了工资比张无忌高的员工信息。
如果子查询的结果是一列，可以作为条件 in 使用：
select *
from emp
where dept_id in (select id from dept where name='研发部' or name='财务部');
这里查询的是部门是研发部或财务部的员工信息。
除了常见的 in 以外，还可以通过 any/some 或 all 结合普通的条件进行使用，比如：
select *
from emp
where salary>all(select salary from emp where job='开发');
当然，也可以结合 not in 使用。
all 表示条件要满足 all 中的所有列数据，也就是说，上面查询的是工资大于所有开发人员的员工信息。
当然，其实也可以使用聚合函数：
select *
from emp
where salary>(select max(salary) from emp where job='开发');
相应的，any 或 some 表示条件满足列数据中的任意一个数据即可：
select *
from emp
where salary>any(select salary from emp where job='开发');
这里查询到的是比任意开发人员工资（即开发人员最低工资）高的员工。
如果子查询的结果是一行，可以作为多个条件与=结合使用：
select *
from emp
where (managerid,salary)=(select managerid,salary from emp where name='张无忌');
这里查询的是领导和工资都与张无忌相同的员工信息。
如果子查询的结果是一张表（多行多列），可以将子查询作为临时表使用：
select emp_tmp.*, dept.name as dept_name
from (select * from emp where age>50) as emp_tmp
left join dept
on emp_tmp.dept_id=dept.id;
比较不常见的是，也可以结合 in 进行多列数据的匹配：
select *
from emp
where (job, salary) in (select job, salary from emp where name='鹿杖客' or name='宋远桥');
这个 SQL 等价于：
select emp.*
from emp, (select * from emp where name='鹿杖客' or name='宋远桥') as emp_tmp
where emp.job=emp_tmp.job and emp.salary=emp_tmp.salary;
文章评论