【SQL】SQL多表查询

多表查询案例联系点击此处

🎄概念

一般我们说的多表查询都涉及外键和父子表之间的关系。
比如一对多:一般前面指的是父表后面指的是子表。

⭐分类

一对多(多对一)
多对多
一对一

⭐一对多

📢案例：部门与员工的关系

📢关系：一个部门对应多个员工，一个员工对应一个部门

📢实现：在多的一方建立外键，指向一的一方的主键(例如上一章节的SQL约束示例)

⭐多对多

📢案例：学生与课程的关系

📢关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

📢实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键，使用中间表来维护二者之间的关联关系

创建学生表

id为学生表的主键。并插入三条数据。

create table student(id int auto_increment primary key comment '主键 学生ID',name varchar(10) comment '姓名',no varchar(10) comment '学号')
comment '学生表';insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊',
'2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');

2.创建课程表

create table course(id int auto_increment primary key comment '课程表主键ID',name varchar(10) comment '课程名称'
)comment '课程表';insert into course values (null, 'Java'), (null, 'C++'), (null , 'MySQL') ,(null, 'Hadoop');

3.创建关联表

这里创建是会失败的，是因为我们关联的是学生表中的no学号字段和课程表中的name名称字段，但这两个字段并不是主键以及唯一约束。
外键所关联的字段必须要具有唯一性。

create table student_course(id int  auto_increment primary key comment '主键',studentno varchar(10) not null comment '学生表学号',courseno  varchar(10) not null comment '课程表课程名',constraint fk_studentNo foreign key (studentno) references student (no),constraint fk_courseno foreign key (courseno) references course (name)
)comment '学生课程中间表';

4. 增加唯一约束，保证外键创建成功。

alter table student add constraint unique_No unique (no);
alter table course add constraint unique_name unique (name);


insert into student_course values (null,'2000100101','C++'),(null,'2000100102','Hadoop'),(null,'2000100103','C++'),(null,'2000100104','Java')

5.关联图

从显示图中可以看出，中间表的studentno对应student的no字段。courserno对应course表的name字段。

⭐一对一

📢案例：用户与用户详情的关系

📢关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。

📢 实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的 (UNIQUE)

create table user_base(id int auto_increment primary key comment '用户id主键',name varchar(10) comment '用户姓名',age int comment '年龄',gender char(1) comment '性别1 男 2 女',phone char(11) comment '手机号'
)comment '用户基本信息表'create table user_base_all(id int auto_increment primary key comment '主键ID',degree varchar(20) comment '学历',major varchar(50) comment '专业',primaryschool varchar(50) comment '小学',middleschool varchar(50) comment '中学',university varchar(50) comment '大学',userid int unique comment '用户表id',constraint fk_userud foreign key (userid) references user_base (id)
)comment '用户详情表'insert into user_base(id, name, age, gender, phone) values
(null,'黄渤',45,'1','18800001111'),
(null,'冰冰',35,'2','18800002222'),
(null,'码云',55,'1','18800008888'),
(null,'李彦宏',50,'1','18800009999');insert into user_base_all(id, degree, major, primaryschool, middleschool,university, userid) values
(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
(null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

📢关联图

🎄多表查询引入

⭐数据准备

📢创建部门表并插入数据

create table department(id int auto_increment primary key comment '主键 自增ID',name varchar(20) not null comment '部门名称'
)comment '部门表';insert into department values (null,'研发部'),(null,'市场部'),(null,'财务部')
,(null,'销售部')
,(null,'总经办')
,(null,'人事部')

📢创建员工表并插入数据

create table employee(id int auto_increment primary key comment '主键 自增ID',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',constraint fk_employee_deptid foreign key (dept_id) references department (id)
)comment '员工表';insert into employee 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);

⭐笛卡尔积

📢如果我们针对上面的员工和部门表进行多表查询时，没有给顶足够多的条件，就会产生笛卡尔积

📢比如部门表有6条记录，而员工表有12条记录，那么我们利用这条SQL查出来的将是17*6=102条记录。这显然是不对的了。

📢显而易见我们在多表查询中需要消除掉无效的笛卡尔积

select * from employee,department;

⭐消除笛卡尔积

select * from employee,department where employee.dept_id = department.id;

🎄多表查询

⭐分类

📢连接查询

内连接：相当于查询A、B交集部分数据
外连接：

左外连接：查询左表所有数据，以及两张表交集部分数据
右外连接：查询右表所有数据，以及两张表交集部分数据

自连接：当前表与自身的连接查询，自连接必须使用表别名

📢子查询

🎄内连接

📢内连接查询的是两张表交集的部分
📢内连接分为隐式内连接和显式内连接
📢二者的查询结果是一致的。

⭐隐式内连接

📢语法

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

📢案例:

A. 查询每一个员工的姓名 , 及关联的部门的名称 ( 隐式内连接实现 )

select employee.name, department.name from employee,department
where employee.dept_id = department.id;

⭐显式内连接

📢语法

inner关键字可以省略

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

📢案例:

A. 查询每一个员工的姓名 , 及关联的部门的名称 ( 显式内连接实现 )

select employee.name, department.name from employee inner join department
on employee.dept_id = department.id;

🎄外连接

📢外连接分为两种,分别是左外连接和右外连接。

📢对于外连接，想查的表在右边就是右连接,想查的表在左边就是左连接。

📢对于左右连接是可以调换顺序的，以开发者的习惯为主，如果习惯把要查的表放在左表那就只需要记住左外连接一种即可。

⭐左外连接

查询左表的所有数据以及和右表交集的数据。
outer可以省略，直接使用left join

📢语法

select 字段列表 FROM 左表 left [outer] join 右表 on 条件...

📢示例

A：查询 emp 表的所有数据 , 和对应的部门信息

由于需求中提到，要查询 emp 的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。

select employee.*, department.name from employee left outer join departmenton employee.dept_id = department.id

⭐右外连接

查询右表的所有数据以及和左表交集的数据。

📢语法

select 字段列表 from 左表 right [outer] join 右表 on 条件...

📢示例

A：查询dept表的所有数据, 和对应的员工信息(右外连接)

select department.*,employee.* from employee right outer join departmenton employee.dept_id = department.id

🎄自连接

顾名思义，就是自己连接自己，也就是把一张表连接查询多次。
对于自连接接查询，可以是内连接查询，也可以是外连接查询

⭐自连接查询

📢语法

必须起别名
否则不清楚所指定的条件、返回的字段，到底

是哪一张表的字段。

select 字段列表 from 表A 别名a JOIN 表A 别名b on 条件...

📢案例

A: 查询员工及其所属领导的名字

select a.name '员工',b.name '领导' from employee as a, employee as b where a.managerid = b.id;

B:查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来

对于案例A 查询并没有查到没有领导的员工,比如总裁的信息。

select a.name '员工',b.name '领导' from employee a left join  employee b on a.managerid = b.id;

⭐联合查询

对于 union（联合）查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
联合查询的多张表列数以及字段类型都必须一致
union all会将查到的所有数据合并，不会自动去重。union会自动去重。

📢语法

select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...

📢案例

A：将薪资低于 5000 的员工 , 和年龄大于 50 岁的员工全部查询出来 .

select * from employee where salary < 5000
union all
select * from employee where age > 50

但是使用union all是不会自动去重的,这时候我们需要使用union

select * from employee where salary < 5000
union
select * from employee where age > 50

🎄子查询

SQL语句中嵌套的select查询语句,称为子查询。

⭐语法

子查询的外部语句可以是insert/update/delete/select中的任何一个。

select * from 表1 where 字段列 = (select 查询列 from 表2);

⭐分类

根据子查询结果不同可以分为4类

标量子查询->即子查询的结果为单个值
列子查询-> 即子查询的结果为一列
行子查询-> 即子查询的结果为一行
表子查询-> 即子查询的结果为多行多列

根据子查询的为准不同，分为：

where之后
from之后
select之后

⭐标量子查询

子查询返回的是单个值，比如(数字字符串日期)等

📢常用符号

这里的<>是不等于和!= 是一样的。

常用的标量子查询操作符号: = <> > >= < <=

📢案例

A : 查询 " 销售部 " 的所有员工信息

select * from employee where dept_id =  (select id from department where name = '销售部')

B 查询在 " 方东白 " 入职之后的员工

select * from employee where entrydate > (select entrydate from employee where name = '方东白');

🎄列子查询

子查询返回的结果是一列(可以是多行)。

📢常用符号

常见的操作符号: in, not in,any,some, all

常见操作符
操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与 ANY 等同，使用 SOME 的地方都可以使用 ANY
ALL	子查询返回列表的所有值都必须满足

📢案例

A. 查询 " 销售部 " 和 " 市场部 " 的所有员工信息

首先查询出两个部门的部门id,这返回的将是一个列。

select id from department where name = '销售部' or name = '市场部';

然后根据返回的部门id来查询员工信息

select * from employee where dept_id in (select id from department where name = '销售部' or name = '市场部');

B:查询比财务部所有人工资都高的员工信息

select * from employee where salary > all (select salary from employee where dept_id = (select id from department where name = '财务部'))

C: 查询比研发部其中任意一人工资高的员工信息

select * from employee where salary >  any (select salary from employee where dept_id = (select id from department where name = '研发部'))

🎄行子查询

子查询返回的结果是一行(可以是多列)。

📢常用符号

常见的操作符号: =, <>,in,not in

📢示例

A. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;

首先查询出“张无忌”的薪资和他的直属领导。

select salary,managerid from employee where name = '张无忌'

然后查出“张无忌”和其直属领导薪资相同的员工信息。

select * from employee where (salary,managerid) = (
select salary,managerid from employee where name = '张无忌')

🎄表子查询

子查询返回的结果是多行多列。

📢常用符号

常见的操作符号: in

📢示例

A：与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

select * from employee where (job,salary) in(select job,salary from employee where name = '鹿杖客' or name = '宋远桥')

B：查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息

这个相当于是把子查询的记过当成一个表再次查询。

select e.*,d.name from (select * from employee where entrydate > '2006-01-01') e
left outer join department d on e.dept_id = d.id;