MySQL_Note8

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 '%书%';