前言
文章内容输出来源:拉勾教育JAVA就业训练营
1. 多表
1.1 概述
- 员工表中有一个字段dept_id 与部门表中的主键对应,员工表的这个字段就叫做 外键
- 拥有外键的员工表 被称为 从表 , 与外键对应的主键所在的表叫做 主表
1.2 外键约束
#1. 新建表时添加外键
-- [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
#1. 已有表添加外键
-- ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES
主表(主 键字段名)
1.2.1 外键约束的注意事项
- 从表外键类型必须与主表主键类型一致 否则创建失败.
- 添加数据时, 应该先添加主表中的数据.
- 删除数据时,应该先删除从表中的数据.
1.2.2 级联删除操作(了解)
- 如果想实现删除主表数据的同时,也删除掉从表数据,可以使用级联删除操作
-- 级联删除
ON DELETE CASCADE
-- 重新创建添加级联操作
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT,
CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
-- 添加级联删除
ON DELETE CASCADE
)
2. 多表关系设计
- 表与表之间的三种关系
- 一对多关系: 最常见的关系, 学生对班级,员工对部门
- 多对多关系: 学生与课程, 用户与角色
- 一对一关系: 使用较少,因为一对一关系可以合成为一张表
3. 多表查询
3.1 多表查询的分类
3.1.1 内连接查询
- 内连接的特点:
通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示
比如通过: 从表的外键 = 主表的主键 方式去匹配
3.1.1.1 隐式内连接
- from子句 后面直接写 多个表名 使用where指定连接条件的 这种连接方式是 隐式内连接.
使用where条件过滤无用的数据
SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;
# 隐式内连接
SELECT * FROM products,category WHERE category_id = cid;
3.1.1.2 显式内连接
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
-- inner 可以省略
# 显式内连接查询
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid;
3.1.2 外连接查询
3.1.2.1 左外连接
- 左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略0
- 左外连接的特点
- 以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据
- 如果匹配不到, 左表中的数据正常展示, 右边的展示为null.
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
-- 左外连接查询
SELECT * FROM category c LEFT JOIN products p ON c.`cid`= p.`category_id`;
3.1.2.2 右外连接
- 右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略
- 右外连接的特点
- 以右表为基准,匹配左边表中的数据,如果能匹配到,展示匹配到的数据
- 如果匹配不到,右表中的数据正常展示, 左边展示为null
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
-- 右外连接查询
SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;
3.1.3 各种连接方式的总结
- 内连接: inner join , 只获取两张表中 交集部分的数据.
- 左外连接: left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分
- 右外连接: right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分
4. 子查询 (SubQuery)
4.1 什么是子查询
- 子查询概念
- 一条select 查询语句的结果, 作为另一条 select 语句的一部分
- 子查询的特点
- 子查询必须放在小括号中
- 子查询一般作为父查询的查询条件使用
- 子查询常见分类
- where型 子查询: 将子查询的结果, 作为父查询的比较条件
- from型 子查询 : 将子查询的结果, 作为 一张表,提供给父层查询使用
- exists型 子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果
4.2 子查询的结果作为查询条件
SELECT 查询字段 FROM 表 WHERE 字段=(子查询);
# 通过子查询的方式, 查询价格最高的商品信息
-- 1.先查询出最高价格
SELECT MAX(price) FROM products;
-- 2.将最高价格作为条件,获取商品信息
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);
4.3 子查询的结果作为一张表
SELECT 查询字段 FROM (子查询)表别名 WHERE 条件;
-- 1. 先查询分类表的数据
SELECT * FROM category;
-- 2.将上面的查询语句 作为一张表使用
SELECT
p.`pname`,
p.`price`,
c.cname
FROM products p
-- 子查询作为一张表使用时 要起别名 才能访问表中字段
INNER JOIN (SELECT * FROM category) c
ON p.`category_id` = c.cid WHERE p.`price` > 500;
注意: 当子查询作为一张表的时候,需要起别名,否则无法访问表中的字段。
4.4 子查询结果是单列多行
- 子查询的结果类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果
SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);
# 查询价格小于两千的商品,来自于哪些分类(名称)
-- 先查询价格小于2000 的商品的,分类ID
SELECT DISTINCT category_id FROM products WHERE price < 2000;
-- 在根据分类的id信息,查询分类名称
-- 报错: Subquery returns more than 1 row
-- 子查询的结果 大于一行
SELECT * FROM category
WHERE cid = (SELECT DISTINCT category_id FROM products WHERE price < 2000);
4.5 子查询总结
- 子查询如果查出的是一个字段(单列), 那就在where后面作为条件使用.
- 子查询如果查询出的是多个字段(多列), 就当做一张表使用(要起别名).
5. 数据库设计
5.1 数据库三范式(空间最省)
- 概念: 三范式就是设计数据库的规则.
- 为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库,必须满足一定的范式
- 满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF) , 其余范式以此类推。一般说来,数据库只需满足第三范式(3NF)就行了
5.1.1 第一范式 1NF
- 概念:
- 原子性, 做到列不可拆分
- 第一范式是最基本的范式。数据库表里面字段都是单一属性的,不可再分, 如果数据表中每个字段都是不可再分的最小数据单元,则满足第一范式。
- 示例:
- 地址信息表中, contry这一列,还可以继续拆分,不符合第一范式
- 地址信息表中, contry这一列,还可以继续拆分,不符合第一范式
5.1.2 第二范式 2NF
- 概念:
- 在第一范式的基础上更进一步,目标是确保表中的每列都和主键相关。
一张表只能描述一件事.
- 在第一范式的基础上更进一步,目标是确保表中的每列都和主键相关。
- 示例:
- 学员信息表中其实在描述两个事物 , 一个是学员的信息,一个是课程信息
如果放在一张表中,会导致数据的冗余,如果删除学员信息, 成绩的信息也被删除了
- 学员信息表中其实在描述两个事物 , 一个是学员的信息,一个是课程信息
5.1.3 第三范式 3NF
- 概念:
- 消除传递依赖表的信息,如果能够被推导出来,就不应该单独的设计一个字段来存放
- 示例
- 通过number 与 price字段就可以计算出总金额,不要在表中再做记录(空间最省)
- 通过number 与 price字段就可以计算出总金额,不要在表中再做记录(空间最省)
5.2 数据库反三范式
5.2.1 概念
- 反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能
浪费存储空间,节省查询时间 (以空间换时间)
5.2.2 什么是冗余字段 ?
- 设计数据库时,某一个字段属于一张表,但它同时出现在另一个或多个表,且完全等同于它在其本来所属表的意义表示,那么这个字段就是一个冗余字段
5.2.4 总结
- 创建一个关系型数据库设计,我们有两种选择
1,尽量遵循范式理论的规约,尽可能少的冗余字段,让数据库设计看起来精致、优雅、让人心醉。
2,合理的加入冗余字段这个润滑剂,减少join,让数据库执行性能更高更快。