【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 department
    on employee.dept_id = department.id

⭐右外连接

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

📢语法

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

📢示例

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

select department.*,employee.* from employee right outer join department
    on 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;