1.范式
1.1总结
范式 | 规则 |
---|---|
第一范式 | 所有的字段都是基本数据字段,不可进一步拆分 |
第二范式 | 确定主键,所有字段,都必须完全依赖主键,不能只依赖主键的一部分(主键可能是由两个字段组成,从而形成唯一标识) |
第三范式 | 不能存在依赖于非主键字段的字段 |
注意
完全按照理论的设计不一定就是最优,还要根据实际情况来决定,按照业务优先的原则
1.2思考
1.这个表存在数据冗余,应该如何优化设计呢,为什么
id | transaction | transno | itemnumber | quantity | cardno |
---|---|---|---|---|---|
itemnumber | goodsname | price |
---|---|---|
原因:
1. 商品名称、价格只依赖于商品编号,可以拆分成单独的表
2. 销售金额=数量*价格,可以去掉
2.ER模型
前提:
局部最优的表,不仅有可能存在进一步拆分的情况,还有可能会出现数据缺失
目的:
在开发基于数据库的信息系统的设计阶段,通常使用 ER 模型来描述信息需求和信息特性,帮助我们理清业务逻辑,从而设计出优秀的数据库。
2.1 ER模型的要素
在ER模型里面,有三个要素,分别是实体,属性和关系
要素 | 含义 | 表示 |
---|---|---|
实体 | 数据对象,往往代表现实中真实的个体 强实体,弱实体 强实体:不依赖于其他实体的实体; 弱实体:对另一个实体有很强的依赖关系的实体 | 矩形,粗框矩形表示弱实体 |
属性 | 指实体的特性,且不可在分 | 椭圆形 |
关系 | 实体与实体之间的关联 一对一 一对多 多对多 | 菱形,粗框菱形表示弱关系 |
2.2 ER模型细化
2.3 ER模型图转换成数据
1.一个实体通常转换成一个数据表;
2.一个多对多的关系,通常也转换成一个数据表;
3.一个 1 对 1,或者 1 对多的关系,往往通过表的外键来表达,而不是设计一个新的数据表;
4.属性转换成表的字段。
2.4 思考题
超市经营者每个月都要进行库房盘点,也就是在一个月的最后一天的营业结束之后,所有的员工一起把库房里的货品都数一遍,然后跟电脑上的库存比对,查看库存损耗
在这个业务模块中,涉及了哪些实体,属性和关系?另外,请你设计一下 ER 模型,通过它来整理一下数据库设计的思路
实体
门店
员工
仓库
商品
关系
一对多的关系
1.门店与仓库的拥有关系
2.门店与雇员的雇佣关系
多对多的关系
1.仓库和商品的库存关系
2.员工,仓库和商品的盘点关系
属性
门店:编号,名称,地址
门店:编号、名称、地址、电话、联系人
员工:编号、名称、工号、身份证、电话、地址
仓库:编号、名称
商品:编号、条码、名称、售价、规格、单位
库存关系:库存数量
盘点关系:盘存数量、结存数量、盈亏数量
ER模型
3.查询语句调优
3.1查询分析语句
1.语法
{explain | describe | desc} 查询语句;
2.示例
explain select itemnumber,quantity,price,transdate -- 分析查询执行结果
from demo.trans
where itemnumber=1
and transdate>'2020-06-18 09:00:00'
and transdate<'2020-06-18 12:00:00'
字段 | 含义 |
---|---|
id | 查询序列号 |
select_type | SIMPLE:表示简单查询,不包含子查询和联合查询。 PRIMARY:表示是最外层的查询。 UNION:表示联合查询中的第二个或者之后的查询 DEPENDENT UNION:表示联合查询中的第二个或者之后的查询,而且这个查询受外查询的影响 |
table | 表示与查询结果相关的表名称 |
partitions | 表示查询访问的分区 |
type | 表示表是如何连接的。至于具体的内容 |
possible_key | 表示 MySQL 可以通过哪些索引找到查询的结果记录 |
key | 表示优化器最终决定使用的索引 |
key_len | 表示优化器选择的索引字段按照字节计算的长度 |
ref | 表示哪个字段或者常量被用来与索引字段对比,没有索引,这个值就是空 |
rows | 表示为了得到查询结果,必须扫描多少行记录 |
filtered | 表示查询筛选出的记录占全部表记录数的百分比 |
extra | 表示 MySQL 执行查询中的附加信息 |
3.2 两种查询优化方法
1.通配符在前面的筛选条件是不能用索引的
WHERE字段LIKE‘%aa’和WHERE字段 LIKE ‘%aa%’都不能使用索引
2.只有当条件语句中只有关键字“OR”,并且“OR”前后的表达式中的字段都建有索引的时候,查询才能用到索引
子查询的执行效率不高.因为 MySQL 会用临时表把子查询的结果保存起来,然后再使用临时表的内容完成查询
3.3思考
1.假设现在有一个这样的查询,请你把这个包含子查询的查询改成 2 个表的连接查询
EXPLAIN SELECT * FROM demo.trans
WHERE itemnumber IN
SELECT itemnumber FROM demo.goodsmaster
WHERE goodsname LIKE '%书%';
答
select * from demo.trans as a join demo.goodsmaster as b on a.itemnumber = b.itemnumber where b.goodsname LIKE '%书%';