目录
一、多表关系介绍
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间互相关联,所以各个表结构之间也存在各种联系,基本上分为三种:
-
一对多(多对一)
-
多对多
-
一对一
1、一对多(多对一)
案例:部门与员工的关系
关系:一个部门对应多个员工,一个员工对应一个部门
实现:在多的一方建立外键,指向一的一方主键
2、多对多
案例:学生与课程的关系
关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
通过案例理解多对多的关系:
-- 多表关系 - 多对多
CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
name VARCHAR(20) COMMENT '姓名',
no VARCHAR(10) COMMENT '学号'
) COMMENT '学生表';
INSERT INTO student VALUES (null, '黛绮丝', '2000100101'),
(null, '谢逊', '2000100102'),
(null, '殷天正', '2000100103'),
(null, '韦一笑', '2000100104');
CREATE TABLE course(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
name VARCHAR(10) COMMENT '课程名称'
) COMMENT '课程表';
INSERT INTO course (id, name) VALUES (null, 'Java'),
(null, 'CPP'),
(null, 'MySQL'),
(null, 'Hadoop');
CREATE TABLE student_course(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
student_id INT NOT NULL COMMENT '学生ID',
course_id INT NOT NULL COMMENT '课程ID',
CONSTRAINT fk_course_id FOREIGN KEY (course_id) REFERENCES course(id),
CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES student(id)
) COMMENT '学生课程中间表';
INSERT INTO student_course (id, student_id, course_id)
VALUES (null, 1, 1), (null, 1, 2), (null, 1, 3),
(null, 2, 2), (null, 2, 3), (null, 3, 4);
3、一对一
案例:用户与用户详情的关系
关系:一对一关系,用于单标拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
实现:在任意一方加入外键,关联另一方的主键,并设置外键为唯一的UNIQUE
二、多表查询概述
概述:指从多张表中查询数据
1、笛卡尔积
笛卡尔积:笛卡尔积是指在数学中,两个集合A集合和B集合的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积)
消除无效的笛卡尔积:
多表查询分类:
-
连接查询
-
内连接:相当于查询A、B交集部分的数据
-
外连接:
-
左外连接:查询左表所有数据,以及两张表交集部分数据
-
右外连接:查询右表所有数据,以及两张表交集部分数据
-
-
自连接:当前表与自身的连接查询,自连接必须使用表别名
-
-
子查询
2、案例
重新创建员工信息表emp和部门信息表dept,并查询每位员工的信息及其所属部门。
代码:
-- ----------------- 多表查询概述 -----------------------
-- 准备数据
USE my_database;
# 删除dept和emp,重新创建
DROP TABLE IF EXISTS dept, emp;
# 创建部门信息表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
)
COMMENT '部门表';
# 插入6条部门数据
INSERT INTO dept (id, name) VALUES
(1, '研发部'),
(2, '市场部'),
(3, '财务部'),
(4, '销售部'),
(5, '总经办'),
(6, '人事部');
# 查看dept中的部门信息
SELECT * FROM dept;
# 创建员工信息表
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
name VARCHAR(10) NOT NULL COMMENT '姓名',
age TINYINT UNSIGNED COMMENT '年龄',
job VARCHAR(10) COMMENT '职务',
salary INT CHECK ( salary > 0 ) COMMENT '薪资',
entry_date DATE,
manager_id INT COMMENT '直属领导ID',
dept_id INT COMMENT '部门ID'
) COMMENT '员工信息表';
# 插入17条员工数据
INSERT INTO emp (id, age, name, job, salary, entry_date, manager_id, dept_id) VALUES
(1, 66, '金庸', '总裁', 20000, '2000-01-01', NULL, 5),
(2, 20, '张无忌', '项目经理', 12500, '2005-12-05', 1, 1),
(3, 33, '杨逍', '开发', 8400, '2000-11-10', 2, 1),
(4, 48, '韦一笑', '开发', 11000, '2002-02-09', 2, 1),
(5, 43, '常遇春', '开发', 10500, '2004-09-07', 3, 1),
(6, 19, '小昭', '程序员鼓励师', 6600, '2004-07-11', 2, 1),
(7, 60, '灭绝', '财务总监', 8500, '2002-08-13', 1, 3),
(8, 19, '周芷若', '会计', 4800, '2006-07-20', 7, 3),
(9, 23, '丁敏君', '出纳', 5250, '2009-07-24', 7, 3),
(10, 20, '赵敏', '市场总监', 12500, '2004-09-16', 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-22', 1, 4),
(15, 38, '俞莲舟', '销售', 4600, '2004-10-22', 14, 4),
(16, 40, '宋远桥', '销售', 4600, '2004-10-22', 14, 4),
(17, 42, '陈友谅', '销售', 2000, '2011-10-22', 1, NULL);
# 查看员工数据
SELECT * FROM emp;
-- 笛卡尔积
SELECT * FROM emp, dept;
# 共查到102(17x6)条数据,这肯定是不对的
-- 为了消除无效的笛卡尔积,需要加入条件,表emp和dept之间通过dept_id建立联系
SELECT * FROM emp, dept WHERE emp.dept_id = dept.id;
# 此时输出16条数据,'陈友谅'因为dept_id=null,不满足条件emp.dept_id和dept.id而没有被查询到
三、内连接
1、内连接查询语法
隐式内连接:
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;
显式内连接:
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;
内连接查询的是两张表的交集部分。
2、案例
查询每位员工所处的部门,列举出员工的id,姓名和部门名称
-- ---------------------多表查询-内连接--------------------
USE my_database;
-- 任务:查询每位员工所处的部门,列举出员工的id,姓名和部门名称
-- 隐式内连接
SELECT e.id, e.name, d.name FROM emp AS e, dept AS d WHERE e.dept_id = d.id;
# 共输出16条数据
-- 显示内连接
SELECT e.id, e.name, d.name FROM emp e INNER JOIN dept d on e.dept_id = d.id;
# 共输出16条数据
这里有一个问题:员工“陈友谅”的部门信息为null,没有被查到。因为案例中的内连接查询条件是e.dept_id = d.id,“陈友谅”的dept_id为null,不满足条件,所以不能被查到。
这就是“内连接查询的是两张表的交集部分”的含义。
如果想查到“陈友谅”的部门信息呢?需要使用外连接,下一节。
四、外连接
1、外连接查询语法
- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;
查询表1(左表)所有数据 包含 表1和表2交集部分的数据
- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;
查询表2 (右表)所有数据 包含 表1和表2交集部分的数据
2、案例
- 查询emp表中的所有员工数据,和对应的部门信息
- 查询dept表中的所有部门数据,和对应的员工信息
代码:
-- ----------------多表查询 外连接---------------------
USE my_database;
-- 1.查询emp表中的所有员工数据,和对应的部门信息
SELECT e.*, d.name FROM emp e LEFT JOIN dept d on d.id = e.dept_id;
# 输出17条数据,陈友谅部门为null
-- 2.查询dept表中的所有部门数据,和对应的员工信息
# 使用右外连接
SELECT d.name, e.* FROM emp e RIGHT JOIN dept d on d.id = e.dept_id;
# 输出17条数据,其中人事部没有员工,也被查询出来,满足条件
# 使用左外连接
SELECT d.name, e.* FROM dept d LEFT JOIN emp e on d.id = e.dept_id;
# 输出与左外连接相同
五、自连接
1、自连接语法
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;
自连接查询,可以是内连接查询,也可以是外连接查询。
个人理解:自连接查询就是一种特殊的多表查询,两张表都是自己。
注意:自连接查询,一定要用别名AS!
2、案例
- 查询所有员工及其直属领导的姓名
- 查询所有员工信息,如果员工没有领导,也需要查询出来
-- ----------------- 多表查询 自连接 -------------------
USE my_database;
-- 1.查询所有员工及其直属领导的姓名
SELECT ei.*, em.name AS '直属领导' FROM emp ei JOIN emp em ON ei.manager_id = em.id;
# 输出16条数据,'金庸'因没有直属领导而没有被查询到(内连接)
-- 2.查询所有员工信息,如果员工没有领导,也需要查询出来
SELECT ei.name AS '员工', em.name AS '领导' FROM emp ei LEFT JOIN emp em ON ei.manager_id = em.id;
# 输出17条数据,'金庸'的直属领导是null(外连接)
六、联合查询(union,union all)
1、联合查询语法
对于union查询,就是吧多查询的结果合并起来,形成一个新的查询结果。
SELECT 字段序列 FROM 表A ...
UNION [ALL]
SELECT 字段序列 FROM 表B ...;
注意:
- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
- union all会将全部查询的数据直接合并在一起,union会对合并之后的数据去重。
2、案例
任务:将薪资低于5000的员工和年龄大于50岁的员工全部查询出来
-- ---------------- 多表查询 - 联合查询 -----------------
USE my_database;
-- 1. 将薪资低于5000的员工和年龄大于50岁的员工全部查询出来
SELECT name, salary, age FROM emp WHERE salary < 5000
UNION ALL
SELECT name, salary, age FROM emp WHERE age > 50;
# 员工“鹿杖客”两个条件都满足,所以在上述查询结果中出现了两次,如果想去掉重复,则可以:
# 去掉重复的查询结果,使用union
SELECT name, salary, age FROM emp WHERE salary < 5000
UNION
SELECT name, salary, age FROM emp WHERE age > 50;
七、子查询
概念:SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
语法:
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
子查询的外部语句可以是INSERT / UPDATE / DELETE / SELECT 中的任何一个。
根据子查询结果不同,可分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置,可分为:
- WHERE之后
- FROM之后
- SELECT之后
1、标量子查询
(1) 介绍
子查询结果是单个值(数字、字符串、日期等),是最简单的形式,这种子查询称为标量子查询。
常用操作符:
- =
- <>
- >
- >=
- <
- <=
(2) 案例
- 查询“销售部”所有的员工信息
- 查询在入职日期在“方东白”之后的员工信息
-- -----------------------子查询-标量子查询------------------------
USE my_database;
-- 1.查询“销售部”所有的员工信息
SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '销售部');
# 子查询结果返回的是“销售部”的id,是一个标量,所以称为标量子查询
-- 2.查询在入职日期在“方东白”之后的员工信息
SELECT * FROM emp WHERE entry_date > (SELECT entry_date FROM emp WHERE name = '方东白');
2、列子查询
(1) 介绍
列子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用操作符:
- IN
- NOT IN
- ANY
- SOME
- ALL
操作符 | 描述 |
IN | 在指定的集合范围之内,多选一 |
NOT IN | 不在指定集合范围之内 |
ANY | 子查询返回列表中,有任何一个满足即可 |
SOME | 与ANY等同,使用SOME的地方都可以使用ANY |
ALL | 子查询返回列表的所有值必须都满足 |
(2) 案例
- 查询“销售部”和“市场部”的所有员工信息
- 查询比财务部所有人工资都高的员工信息
- 查询比研发部中任意一人工资高的员工信息
-- --------------------子查询-列子查询------------------------------
# 常用操作符: IN NOT IN ANY SOME ALL
USE my_database;
# 1.查询“销售部”和“市场部”的所有员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name = '市场部' OR name = '销售部');
# 2.查询比财务部所有人工资都高的员工信息
SELECT * FROM emp WHERE salary > ALL (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部'));
# 3.查询比研发部中任意一人工资高的员工信息
SELECT * FROM emp WHERE salary > ANY (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部'));
3、行子查询
(1) 介绍
子查询返回的结果是一行(可以是多列)
常用操作符:
- =
- <>
- IN
- NOT IN
(2) 案例
- 查询与“张无忌”的薪资及直属领导相同的员工信息
-- ------------------- 子查询 行子查询 --------------------
USE my_database;
-- 行子查询
# 1.查询与“张无忌”的薪资及直属领导相同的员工信息
SELECT * FROM emp WHERE (salary, manager_id) = (SELECT salary, manager_id FROM emp WHERE name = '张无忌');
4、表子查询
(1) 介绍
子查询结果返回的是多行多列,这种子查询称为表子查询。
常用操作符:IN
(2) 案例
- 查询与“鹿杖客”,“宋远桥”的职位与薪资相同的员工信息
- 查询入职日期是“2006-01-01”之后的员工信息及其部门信息
-- --------------- 子查询 -- 表子查询 ------------------
USE my_database;
# 1.查询与“鹿杖客”,“宋远桥”的职位与薪资相同的员工信息
SELECT * FROM emp WHERE ((job, salary) IN (SELECT job, salary FROM emp WHERE name = '宋远桥' OR name = '鹿杖客'));
# 2.查询入职日期是“2006-01-01”之后的员工信息及其部门信息
# 先查询入职日期是“2006-01-01”之后的员工信息
SELECT * FROM emp WHERE entry_date > '2006-01-01';
# 再根据上述查询结果获取部门信息
SELECT *, dept.name FROM (SELECT * FROM emp WHERE entry_date > '2006-01-01') AS e , dept WHERE e.dept_id = dept.id;
# 或者用left join
SELECT *, d.name FROM (SELECT * FROM emp WHERE entry_date > '2006-01-01') AS e LEFT JOIN dept AS d on d.id = e.dept_id;
八、子查询综合案例
任务需求查看代码注释,这里要提的是,实现任务需求可能有多种方式,这里是为了举例说明子查询语句的使用方法:
-- ------------------ 多表查询综合练习 -----------------
USE my_database;
# 创建一张新表“员工薪资等级表”
CREATE TABLE salary_grade
(
grade INT,
low_salary INT,
high_salary INT
) COMMENT '员工薪资等级表';
# 插入数据
INSERT INTO salary_grade
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);
SELECT *
FROM salary_grade;
# 1. 查询员工的姓名、年龄、职位、部门信息
# 外连接查询(可以查到“陈友谅”)
SELECT e.name, e.age, e.job, d.name
FROM emp e
LEFT JOIN dept d on d.id = e.dept_id;
# 内连接查询(查不到“陈友谅”)
SELECT e.name, e.age, e.job, d.name
FROM emp e
INNER JOIN dept d on e.dept_id = d.id;
# 2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息
SELECT e.*, d.name
FROM emp e
INNER JOIN dept d ON d.id = e.dept_id
WHERE age < 30;
# 3. 查询拥有员工的部门ID、部门名称(没有员工的部门不要被查询到)
# 使用列子查询
SELECT *
FROM dept
WHERE id IN (SELECT DISTINCT dept_id FROM emp);
# 或者使用内连接
SELECT DISTINCT d.id, d.name
FROM emp e,
dept d
WHERE e.dept_id = d.id
ORDER BY d.id ASC;
# 4. 查询所有年龄大于40岁的员工,及其归属部门名称;如果员工没有被分配部门,也需要展示出来
SELECT e.*, d.name
FROM emp e
LEFT JOIN dept d on d.id = e.dept_id
WHERE e.age > 40;
# 5. 查询所有员工的工资等级
SELECT e.name, e.salary, s.grade, s.low_salary, s.high_salary
FROM emp e
LEFT JOIN salary_grade s
ON e.salary BETWEEN s.low_salary AND s.high_salary;
# 6. 查询“研发部”所有员工的信息及工资等级
# 隐式内连接
SELECT e.name, e.salary, d.name, s.grade, s.low_salary, s.high_salary
FROM emp e,
dept d,
salary_grade s
WHERE e.dept_id = d.id
AND (e.salary BETWEEN s.low_salary AND s.high_salary)
AND d.name = '研发部';
# 显示内连接
SELECT e.name, e.salary, d.name, s.grade, s.low_salary, s.high_salary
FROM emp e
INNER JOIN dept d on e.dept_id = d.id
INNER JOIN salary_grade s ON e.salary BETWEEN s.low_salary AND s.high_salary
WHERE d.name = '研发部';
# 7. 查询“研发部”所有员工的平均工资
SELECT AVG(e.salary)
FROM emp e INNER JOIN dept d on e.dept_id = d.id WHERE d.name = '研发部';
# 8. 查询工资比“灭绝”高的员工信息
# (标量子查询)
SELECT *
FROM emp
WHERE salary > (SELECT salary FROM emp WHERE name = '灭绝');
# 9. 查询比平均薪资高的员工信息
SELECT *
FROM emp
WHERE salary > (SELECT AVG(emp.salary) FROM emp);
# 10. 查询低于本部门平均工资的员工信息
# a. 查询指定部门的平均工资
# 例如:查询dept_id=1的部门平均薪资
SELECT AVG(e.salary) FROM emp e WHERE e.dept_id = 1;
# b. 查询比本部门平均薪资低的员工信息
SELECT einfo.dept_id AS '部门ID', einfo.name, einfo.salary, (SELECT AVG(esalary.salary) FROM emp esalary WHERE esalary.dept_id = einfo.dept_id) AS '部门平均薪资'
FROM emp einfo
WHERE einfo.salary < (SELECT AVG(esalary.salary) FROM emp esalary WHERE esalary.dept_id = einfo.dept_id);
# 11. 查询所有的部门信息,并统计部门的员工人数
SELECT COUNT(*) FROM emp WHERE dept_id = 1;
SELECT d.id, d.name, (SELECT COUNT(*) FROM emp e WHERE e.dept_id = d.id) AS '人数' FROM dept d;