1、多表关系
-
概述
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
-
一对多(多对一)
-
多对多
-
案例:学生与课程之间
-
关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
-
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
-
# 多对多
create table tb_student (
id int primary key auto_increment comment '唯一标识',
name varchar(10) comment '学生姓名',
no varchar(10) comment '学号'
)comment '学生表';
insert into tb_student(name,no )
values
('黛绮丝','2021420'),
('谢逊','2021421'),
('殷天正','2021422'),
('韦一笑','2021423');
create table tb_course (
id int primary key auto_increment comment '唯一标识',
name varchar(10) comment '课程名称'
)comment '课程表';
insert into tb_course(name)
values
('Java'),('Python'),('C'),('MySQL');
create table tb_stu_cour (
id int primary key auto_increment comment '唯一标识',
stuId int not null comment '学生ID',
courId int not null comment '课程ID',
constraint fk_courId foreign key (courId) references tb_course(id),
constraint fk_stuId foreign key (stuId) references tb_student(id)
)comment '学生课程中间表';
insert into tb_stu_cour(stuId,courId)
values
(1,1),(1,2),(1,3),(2,2),(2,3),(3,4);
-
一对一
-
案例:用户与用户详情之间的关系
-
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升效率。
-
- 实现:在任意一方加入外键,关联另一方,并且设置外键为唯一标识(unique)
# 一对一
create table tb_user(
id int primary key auto_increment comment '唯一标识',
name varchar(10) comment '姓名',
age int comment '年龄',
gender char(1) comment '1:男,2:女',
phone char(11) comment '手机号'
)comment '用户基本信息表';
create table tb_user_edu(
id int primary key auto_increment comment '主键',
degree varchar(20) comment '学历',
major varchar(50) comment '专业',
primaruschool varchar(50) comment '小学',
middleschool varchar(50) comment '中学',
university varchar(50) comment '大学',
userid int unique comment '用户ID',
# 添加外键
constraint fk_userid foreign key (userid) references tb_user(id)
)comment '用户教育信息表';
2、多表查询概述
-
概述:指从多张表中查询数据
-
笛卡尔积:笛卡尔乘积是指在数学中,两个集合A集合 和B集合的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积)
-
多表查询分类
-
连接查询
-
内连接:相当于查询A、B交集部分数据
-
外连接:
-
左外连接:查询左表所有数据,以及两张表交集部分数据
-
右外连接:查询右表所有数据,以及两张表交集部分数据
-
-
自连接:当前表与自身表的链接查询,自连接必须使用表别名(tb_emp e)
-
-
子查询
3、连接查询–内连接
-
内连接查询语法:
-
隐式内连接
select 字段列表 from 表1 ,表2 where 条件...;
-
显式外链接
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
# 查询每个员工的姓名,以及关联的部门的名称(隐式内连接实现)
select tb_emp.name,tb_dept.name from tb_dept,tb_emp where tb_emp.dept_id =tb_dept.id;
# 查询每个员工的姓名,以及关联的部门的名称(显式内连接实现)
select e.name,d.name from tb_emp e inner join tb_dept d on e.dept_id = d.id;
4、连接查询–外连接
-
外连接查询语法:
-
左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件 ...;
相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据
-
右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
相当于查询表2(右表)的所有数据,包含表1和表2交集部分的数据
# 查询 tb_emp 表中所有数据,和对应的部门信息(左外连接)
select e.*,d.name from tb_emp e left outer join tb_dept d on e.dept_id =d.id;
# 查询 tb_dept 表中所有数据,和对应的部门信息(右外连接)
select d.*,e.* from tb_emp e right outer join tb_dept d on e.dept_id = d.id;
5.1、连接查询–自连接
自连接查询语法:
select 字段列表 from 表A 别名A join 表A 别名B on 条件...;
自连接查询,可以是内连接查询,也可以是外连接查询。
5.2、联合查询 union,union all
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
select 字段列表 from 表A...
union [all]
select 字段列表 from 表B...;
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
# union all,union
# 1,将薪资低于5000的员工和年龄大于50岁的员工全部查出来
select * from tb_emp where salary < 5000
union
select *from tb_emp where age > 60;
6、子查询
-
概念:SQL语句中嵌套select 语句,成为嵌套查询,又称为子查询。
select *from t1 column1 = (select column1 from t2);
子查询外部的语句可以是insert/update/delete/select中的任何一个。
2. 根据子查询结果不同,分为:
名称 | 特征 |
---|---|
标量子查询 | 子查询结果为单个值 |
列子查询 | 子查询结果为一列 |
行子查询 | 子查询结果为一行 |
表子查询 | 子查询结果为多行多列 |
3. 根据子查询位置,分为:where之后、from之后、select之后。
4. 标量子查询
-
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询
-
常用的操作符:=、<>、>、>= 、<、<=
# 标量子查询
# 查询“销售部”的所有员工信息
select *from tb_emp where dept_id = (select id from tb_dept where name = '销售部');
# 查询在赵敏之后入职的员工信息
# 查询赵敏的入职日期
# 查询所有员工信息
select *from tb_emp where entrydate > (select entrydate from tb_emp where name='赵敏');
5. 列子查询
-
子查询返回的结果是一列(可以是多行),这种子查询成为
列子查询
。 -
常用的操作符:
操作符 | 描述 |
---|---|
in | 在指定的集合范围之内,多选一 |
not in | 不在指定的集合范围之内 |
any | 子查询返回列表中,右任意一个满足即可 |
some | 与any等同,使用some的地方可以使用any |
all | 子查询返回列表的所有值都必须满足 |
# 列子查询
# 1.查询“销售部”和“市场部”的所有员工的信息
# a.查询销售部和市场部的部门ID
select id from tb_dept where name = '销售部' or name = '市场部';
# 根据部门ID查询员工信息
select *from tb_emp where tb_emp.dept_id in (select id from tb_dept where name = '销售部' or name = '市场部');
# 2.查询比财务部所有人工资都高的员工信息
# 查询财务部所有人的工资
select id from tb_dept where name = '财务部'
select salary from tb_emp where dept_id = ( select id from tb_dept where name = '财务部');
select *from tb_emp where salary > all( select salary from tb_emp where dept_id = ( select id from tb_dept where name = '财务部'));
# 1.查询比调研部其中任意一人工资高的员工信息
# a.查询研发部部门ID
select id from tb_dept where name ='调研部';
# b.查询研发部员工工资
select salary from tb_emp where dept_id = (select id from tb_dept where name ='调研部');
# c.最终实现
select *from tb_emp where salary > some(select salary from tb_emp where dept_id = (select id from tb_dept where name ='调研部'));
select *from tb_emp where salary > any(select salary from tb_emp where dept_id = (select id from tb_dept where name ='调研部'));
6. 行子查询
-
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
-
常用操作符:=、<>、in、not in
# 行子查询
# 查询与张无忌薪资及直属领导相同的员工信息
# a.查询张无忌的薪资和直属领导
select salary,managerid from tb_emp where name = '张无忌';
# b.最终实现
select *from tb_emp where (salary,managerid) = (select salary,managerid from tb_emp where name = '张无忌');
7. 表子查询
-
子查询返回的结果是多行多列,这种子查询称为表子查询。
-
常用的操作符:
in
# 表子查询
# 1.查询与张无忌,张三丰职位和薪资相同的员工信息
select job,salary from tb_emp where name= '张无忌' or name='张三丰';
select *from tb_emp where (salary,job) in (select job,salary from tb_emp where name= '张无忌' or name='张三丰');
# 2.查询入职日期是2006-01-01之后 员工信息,及其部门信息
# 查询入职日期是2006-01-01之后的员工信息
select *from tb_emp where entrydate > '2006-01-01' ;
# 查询这部分员工对应的部门
select e.*,d.name from (select *from tb_emp where entrydate > '2006-01-01') e left join tb_dept d on e.dept_id = d.id;
7、多表查询案例
-
准备(tb_emp,tb_dept,tb_salgrade)
create table if not EXISTS tb_dept(
id int PRIMARY KEY not null COMMENT '部门编号',
name varchar(10) not null COMMENT '姓名'
)COMMENT '部门编号表';
insert into tb_dept(id,name)
VALUES
(1,'调研部'),
(2,'市场部'),
(3,'财务部'),
(4,'销售部'),
(5,'总经办');
create table if not EXISTS tb_emp(
id int PRIMARY KEY auto_increment COMMENT 'ID唯一标识',
name varchar(10) COMMENT '姓名',
age int UNSIGNED CHECK(age > 0 && age <= 120) COMMENT '年龄',
job varchar(20) COMMENT '职位',
salary int COMMENT '薪资',
entrydate date COMMENT '入职时间',
managerid int COMMENT '工号' ,
dept_id int COMMENT '部门编号'
)COMMENT '员工表';
insert into db_query.tb_emp (id, name, age, job, salary, entrydate, managerid, dept_id)
values (1, '金庸', 66, '总裁', 20000, '1997-05-15', null, 5),
(2, '张无忌', 20, '项目经理', 12500, '2024-05-06', 1, 1),
(3, '杨潇', 33, '开发', 8400, '2024-01-06', 2, 1),
(4, '韦一笑', 20, '开发', 11000, '2024-08-06', 2, 1),
(5, '常遇春', 53, '开发', 10500, '2024-06-06', 3, 1),
(6, '小昭', 19, '程序员鼓励师', 6600, '2024-11-06', 2, 1),
(7, '灭绝', 60, '财务总监', 8500, '2024-05-06', 1, 3),
(8, '周芷若', 18, '会计', 4800, '2024-07-06', 7, 3),
(9, '丁敏君', 23, '出纳', 5200, '2024-01-06', 7, 3),
(10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 3),
(11, '鹿杖客', 56, '职员', 3750, '2024-05-06', 10, 2),
(12, '鹤笔翁', 68, '职员', 3750, '2024-05-06', 10, 2),
(13, '东风白', 19, '职员', 3750, '2024-05-06', 10, 2),
(14, '张三丰', 88, '销售总监', 14000, '2005-05-11', 1, 4),
(15, '俞莲舟', 38, '销售', 4600, '2024-05-06', 14, 4),
(16, '宋远桥', 40, '销售', 4600, '2024-05-06', 14, 4),
(17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null);
--创建薪资等级表
CREATE TABLE `tb_salgrade` (
`grade` int NOT NULL AUTO_INCREMENT COMMENT '薪资等级',
`losal` int DEFAULT NULL COMMENT '最低薪资',
`hisal` int DEFAULT NULL COMMENT '最高薪资',
PRIMARY KEY (`grade`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='薪资等级表'"
--插入表数据
insert into db_query.tb_salgrade (grade, losal, hisal)
values (1, 0, 3000),
(2, 3001, 5000),
(3, 5001, 8000),
(4, 8001, 10000),
(5, 10001, 15000),
(6, 15001, 20000),
(7, 20001, 25000),
(8, 25001, 30000);
CREATE TABLE `tb_course` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
`name` varchar(10) DEFAULT NULL COMMENT '课程名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='课程表';
insert into tb_course (id, name)
values (1, 'Java'),
(2, 'Python'),
(3, 'C'),
(4, 'MySQL');
CREATE TABLE `tb_student` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
`name` varchar(10) DEFAULT NULL COMMENT '学生姓名',
`no` varchar(10) DEFAULT NULL COMMENT '学号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表';
insert into tb_student (id, name, no)
values (1, '黛绮丝', '2021420'),
(2, '谢逊', '2021421'),
(3, '殷天正', '2021422'),
(4, '韦一笑', '2021423');
CREATE TABLE `tb_stu_cour` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
`stuId` int NOT NULL COMMENT '学生ID',
`courId` int NOT NULL COMMENT '课程ID',
PRIMARY KEY (`id`),
KEY `fk_courId` (`courId`),
KEY `fk_stuId` (`stuId`),
CONSTRAINT `fk_courId` FOREIGN KEY (`courId`) REFERENCES `tb_course` (`id`),
CONSTRAINT `fk_stuId` FOREIGN KEY (`stuId`) REFERENCES `tb_student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生课程中间表';
insert into tb_stu_cour (id, stuId, courId)
values (1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 2, 2),
(5, 2, 3),
(6, 3, 4);
2. 练习
# 1、查询员工的姓名、年龄、职位、部门信息(隐式内连接)
select e.name '姓名',e.age '年龄',e.job '职位',d.name '所属部门' from tb_emp e,tb_dept d where e.dept_id = d.id;
# 2、查询年龄小于30岁的员工姓名、年龄、职位、部门信息(显示内连接)
select e.name '姓名',e.age '年龄',e.job '职位',d.name '所属部门' from tb_emp e inner join tb_dept d on e.dept_id = d.id where e.age < 30;
# 3、查询拥有员工的部门ID、部门名称
-- 表:tb_emp 、tb_dept
-- 连接条件:e.dept_id = d.id
select distinct d.id,d.name from tb_emp e,tb_dept d where e.dept_id = d.id;
# 4、查询所有年龄大于40岁的员工,以及其归属的部门名称;如果员工没有分配部门也要展示出来
-- 表:tb_emp 、tb_dept
-- 连接条件:e.dept_id = d.id ,e.age >40
select e.* , d.name from tb_emp e left join tb_dept d on e.dept_id = d.id where e.age > 40;
# 5、查询所有员工的工资等级
-- 表:tb_emp 、tb_salgrade
-- 连接条件: e.salary between s.losal and s.hisal
select e.* ,s.grade from tb_emp e,tb_salgrade s where e.salary between s.losal and s.hisal;
# 6、查询“研发部”所有员工的信息及工资等级
# 第一种
-- 表:tb_emp 、tb_salgrade,tb_dept
-- 连接条件: e.salary between s.losal and s.hisal,e.dept_id = d.id
-- 查询条件:d.name = '研发部'
select e.*,s.grade from tb_emp e,tb_salgrade s,tb_dept d where ( e.salary between s.losal and s.hisal) and (e.dept_id = d.id) and (d.name = '研发部');
# 第二种
select d.id from tb_dept d where d.name = '研发部';
select *from tb_emp e,tb_salgrade s where ( e.salary between s.losal and s.hisal) and (e.dept_id = (select d.id from tb_dept d where d.name = '研发部'));
# 7、查询“研发部”员工的平均工资
-- 表:tb_emp 、 ,tb_dept
-- 连接条件: e.dept_id = d.id
-- 查询条件:d.name = '研发部'
select avg(e.salary) from tb_emp e,tb_dept d where e.dept_id = d.id and d.name = '研发部';
# 8、查询工资比“灭绝”高的员工信息
select * from tb_emp where salary > (select salary from tb_emp where name = '灭绝');
# 9、查询比平均工资高的员工信息
# a.查询平均工资
select avg(salary) from tb_emp ;
# b.查询比平均工资高的员工信息
select *from tb_emp where salary > (select avg(salary) from tb_emp );
# 10、查询低于本部门平均工资的员工信息
# a.查询指定部门平均工资
select avg(e1.salary) from tb_emp e1 where e1.dept_id = 1;
# b.查询比当前平均工资低的员工信息
select e2.* , (select avg(e1.salary) from tb_emp e1 where e1.dept_id = e2.dept_id) '平均薪资' from tb_emp e2 where e2.salary < ( select avg(e1.salary) from tb_emp e1 where e1.dept_id = e2.dept_id);
# 11、查询所有部门信息,并统计部门的员工人数
select d.id,d.name,(select count(*) from tb_emp e where e.dept_id = d.id) '人数' from tb_dept d;
select count(*) from tb_emp e where e.dept_id = 1;
# 12、查询所有学生的选课情况,展示出学生的名字、学号、课程名称
# 表:tb_student s,tb_course s,tb_stu_cour sc
# 连接条件:s.id = sc.stuId ,c.id = sc.courId
select s.name,s.no,c.name from tb_student s ,tb_stu_cour sc,tb_course c where s.id = sc.stuId and c.id = sc.courId;
总结
如有侵权,请联系删除
今日又学一点,谨以此篇献给黑马,感谢黑马!!!
最后放松一下吧,欣赏一下空中展翅——散兵!!!