MySQL_Note8

1.范式

1.1总结

注意

完全按照理论的设计不一定就是最优,还要根据实际情况来决定,按照业务优先的原则

1.2思考

1.这个表存在数据冗余,应该如何优化设计呢,为什么

原因：
1. 商品名称、价格只依赖于商品编号，可以拆分成单独的表
2. 销售金额=数量*价格，可以去掉
1
2
3

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
3
4
5
6
7

关系

一对多的关系
1.门店与仓库的拥有关系
2.门店与雇员的雇佣关系

多对多的关系
1.仓库和商品的库存关系
2.员工,仓库和商品的盘点关系
1
2
3
4
5
6
7

属性

门店:编号,名称,地址
门店：编号、名称、地址、电话、联系人
员工：编号、名称、工号、身份证、电话、地址
仓库：编号、名称
商品：编号、条码、名称、售价、规格、单位
库存关系：库存数量
盘点关系：盘存数量、结存数量、盈亏数量
1
2
3
4
5
6
7

ER模型

3.查询语句调优

3.1查询分析语句

1.语法

{explain | describe | desc} 查询语句;
1

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'
1
2
3
4
5

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 '%书%';
1
2
3
4

答

select * from demo.trans as a join demo.goodsmaster as b on a.itemnumber = b.itemnumber where b.goodsname LIKE '%书%';
1