多表关系 概述:项目开发中,在进行数据库表结构设计时,会根据业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种: 一对多(多对一) 多对多 一对一 一对多(多对一) 案例:部门和员工的关系 关系:一个部门对应多个员工,一个员工对应一个部门 实现:在多的一方建立外键,指向一的一方主键 多对多 案例:学生与课程的关系 关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择 实现:建立第三张中间表,中间表至少包含2个外键,分别关联两方主键 一对一 案例:用户与用户详情的关系 关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提高操作效率 实现:在任意一方加入外键,关联另一方的主键,并且设置外键为唯一的(UNIQUE) 多表查询 概述:指从多张表中查询数据 笛卡儿积:笛卡儿乘积是值数学中,两个集合A集合和B集合的所有组合情况。(在多表查询时,需要消除无效的笛卡儿积) 消除笛卡儿积样例: SELECT * FROM emp,dept WHERE emp.dept_id = dept.dept_id 用关联字段去消除无效的笛卡儿积 多表查询分类 内连接:相当于查询A、B交集部分数据 外连接: 左外连接:查询左表所有数据,以及两张表交集部分数据 右外连接:查询右表所有数据,以及两张表交集部分数据 自连接:当前表与自身的连接查询,自连接必须使用表别名 子查询 内连接查询语法 隐式内连接 SELECT 字段列表 FROM 表1,表二 WHERE 条件..... 显式内连接 SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件..... 2个语法区别是 表的连接形式 、连接条件的表示形式 显式内连接运行速度比隐式内连接快 显式内连接的 [INNER] 可以省略 样例:查询 emp 表的每一位员工的姓名,及关联的 dept 部门的名称 -- 表结构:emp,dept -- 连接条件:emp.dept_id = dept.id 隐式: SELECT e.name , d.name FROM emp e,dept d WHERE e.dept_id = d.id 显式: SELECT e.name , d.name FROM emp e JOIN dept d ON e.dept_id = d.id 外连接查询语法 左外连接:查询表1的所以数据 包含表1 和 表2交集部分的数据 SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 右外连接:查询表2的所以数据 包含表1 和 表2交集部分的数据 SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 OUTER 可以省略 样例: -- 表结构:emp,dept -- 连接条件:emp.dept_id = dept.id 左外连接:查询 emp 表的所有数据,和对应部门的信息 SELECT e.* , d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id 右外连接:查询 dept 表的部门数据,和对应的员工所有数据 SELECT d.name,e.* FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id 自连接 自连接查询语法:自连接查询,可以是内连接,也可以是外连接查询 SELECT 字段列表 FROM 表A 别名 (A) JOIN 表A 别名 (B) ON 条件 样例: -- 表结构:emp 查询员工 及其 所属领导的名字 SELECT A.name '员工', B.name ‘领导’ FROM emp A , emp B WHERE A.领导id = B.id. 查询所有员工 及其 领导的名字,如果没有领导,也要查询出来 SELECT A.name ‘员工’, B.name ‘领导’ FROM emp A LEFT JOIN emp B ON A.领导id = B.id. 联合查询 -- UNION,UNION ALL 概述:对于UNION查询, 就是把多次查询的结果合并起来,形成一个新的查询结果集 语法: SELECT 字段列表 FROM 表A .... UNION [ALL] SELECT 字段列表 FROM 表B .... 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致 UNION 和 UNION ALL 区别在没加 ALL 会对查询拼接去重 加了 ALL 就是把查询拼接 样例:将薪资低于5000的员工,和年龄大于50岁的员工全部查询出来 SELECT * FROM emp WHERE 薪资 < 5000 UNION ALL \ UNION SELECT * FROM emp WHERE 年龄 < 5000 子查询 概念:SQL语句中嵌套 SELECT 语句,称为嵌套查询,又称子查询 语法: SELECT * FROM 表1 WHERE x = (SELECT ... FROM 表2 ) 子查询外部的语句可以是 INSERT / UPDATE / DELETE /SELECT 的任何一个 根据子查询结果不同,分为: 标量子查询(子查询的结果为单个值) 列子查询(子查询结果为一列) 行子查询(子查询结果为一行) 表子查询(子查询结果为多行多列) 列子查询 子查询返回的结果是一列(可以多行) 常用的操作符: 样例:查询A部门 和 B部门的所有员工消息 -- 表结构 emp 员工表 dept 部门表 SELECT * FROM emp WHERE emp.dept_id IN (SELECT id FROM dept WHERE name = 'A' or name = 'B) 行子查询 子查询返回的结果是一行(可以是多列),这种子查询称为行子查询 常用的操作符:= 、<>、IN、NOT IN 样例:查询与 A 的薪资及直属领导相同的员工信息 SELECT * FROM emp WHERE (薪资,领导) = (SELECT 薪资,领导 FROM emp WHERE name = 'A') 表子查询 子查询返回的结果是多行多列,这种子查询称为表子查询 常用的操作符:IN 样例1:查询 A 和 B 的职位相同和薪资相同的员工信息 SELECT * FROM emp WHERE (职位,薪资) IN (SELECT 职位,薪资 FROM emp WHERE name = ‘A’ OR name = ‘B’) 样例2:查询入职日期是“2022-12-05”之后的员工信息,对应的部门信息 SELECT e.* , d.name FROM (SELECT * FROM emp WHERE 日期 > ‘2022-12-05’) e LEFT JOIN dept d ON e.部门id = d.id