多表查询案例联系点击此处
🎄概念
- 一般我们说的多表查询都涉及外键和父子表之间的关系。
- 比如一对多:一般前面指的是父表后面指的是子表。
⭐分类
- 一对多(多对一)
- 多对多
- 一对一
⭐一对多
📢案例：部门与员工的关系
📢关系：一个部门对应多个员工，一个员工对应一个部门
📢实现：在多的一方建立外键，指向一的一方的主键(例如上一章节的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;
