1. 数据库的设计
1.数据库设计步骤
1)需求分析阶段
准确了解与分析用户需求(包括数据与处理)。
是整个设计过程的基础,是最困难、最耗费时间的一步。
2)概念结构设计阶段
是整个数据库设计的关键
设计数据库的E-R模型图,确认需求信息的正确和完整
Entity – Relationship
3)逻辑设计阶段
将E-R图转换为逻辑模型(逻辑模型为关系模型则体现为多张表)
应用数据库设计的三大范式进行审核
设计外模式,建立视图
4)物理设计阶段
确定存取方法(例如索引的设定)
确定存储结构(文件存放的位置等),并编写代码实现前端应用
5)数据库实施阶段
将数据载入,并对数据库进行调试。
6)运行和维护阶段
使用和维护数据库。
2. ER图
ER图(实体关系图)
提供了表示实体类型、属性和关系的方法,用于描述现实世界的概念模型。
ER图的构成
矩形框:表示实体,在框中写实体名
菱形框:表示关系,在框中写关系
椭圆形框:表示实体或者关系的属性,将属性名写入框中。对于主属性名,在其下面添加下划线。
连线:实体与属性之间,实体与关系之间,关系与属性之间,用直线连接,并且在直线上标注关系类型。
(1对1:两端都写1)
(1对多:1端写1,多端写n)
(多对多:一端写m,一端写n)
ER图示例:
3. 实体之间的关系
一对一:住户和车库
household carport
hid ower doornum cid portnum price
实现方式:唯一性 (给carport添加 hid列 并制定唯一约束)
外键约束 (给carport添加 hid列 并添加外键约束)
主键作为外键 (直接把cid同时作为主键和外键)
一对多(多对一):学生和班级
student class
sid sum sname cid cname cloc
实现方式:直接添加外键 (给student添加cid作为外键,且添加外键约束)
多对多:学生和课程
student course
sid sname cid cname
1 zs 1 math
2 ls 2 english
实现方式:增加关系表。在关系表中设置外键和联合主键
src
pk
fk fk
sid cid
1 1
1 2
2 1
2 2
4. 数据库设计遵循的3个范式
第一范式:表中的域应该具有原子性,不可再分割。
两列的属性相近或一样,尽量合并属性一样的列,确保不产生冗余数据。
举例:地址字段
Stuinfo
Id name address
1 zs 河北省石家庄市**区**街道
2 ls 山东省济南市**区 **街道
把address改为province city detail
举例: productName1 productNum1 productName2 productNum2 (不合理)
改为 productNum productName
第二范式:非主键字段必须和主键是相关的(一张表只能存储一类数据),而且不能部分相关(不能只和联合主键的1个键相关)。
举例:学生表(id name age classnum) 班级表(classnum classname classloc)
classloc不能定义在学生表中
或
客房订单表
第三范式:非主键字段必须和主键直接相关而不是间接相关。(非主键字段之间不能相关)
举例:学生表(id name age classnum) 班级表(classnum classname classloc)
如果把classname和classloc定义在学生表中,则classname和classloc就是通过classnum间接和主键id相关,所以应该拆分成两张表。
说明:三大范式只是一般设计数据库的基本理念,可以建立冗余较小、结构合理的数据
库。如果有特殊情况,当然要特殊对待,数据库设计最重要的是看需求跟性能,
需求>性能>表结构。所以不能一味的去追求范式建立数据库。
5. Powerdesigner的使用
2. Sql语句的优化
1) SELECT子句中避免使用(*),在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间
2) 索引失效的情况:
① Not Null/Null 如果某列建立索引,当进行Select * from emp where depto is not null/is null。 则会是索引失效。
② 索引列上不要使用函数,
SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = ‘ABC’(不)
SELECT Col FROM tbl WHERE name LIKE ‘%ABC%’ (不)
SELECT Col FROM tbl WHERE name LIKE ‘ABC%’ (使用)。
③ 索引列上不能进行计算
SELECT Col FROM tbl WHERE col / 10 > 10 则会使索引失效
应该改成SELECT Col FROM tbl WHERE col > 10 * 10
④ 索引列上不要使用NOT ( != 、 <> )
如:SELECT Col FROM tbl WHERE col ! = 10 应该
改成:union。
5) 用UNION替换OR(适用于索引列)
union:是将两个查询的结果集进行追加在一起,它不会引起列的变化。 由于是追加操作,需要两个结果集的列数应该是相关的,并且相应列的数据类型也应该相当的。
union 返回两个结果集,同时将两个结果集重复的项进行消除。 如果不进行消除,用UNOIN ALL.
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描.
注意, 以上规则只针对多个索引列有效.
如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.
6) 用EXISTS替代IN、用NOT EXISTS替代NOT IN
(exists先执行主查询且子查询不返回结果集只作为条件
in先执行子查询)
#查询薪水大于2000的员工的部门名称
select dname from dept where deptno
in(select deptno from emp where sal>2000);
select dname from dept where exists
(select sal from emp where sal>2000 and emp.deptno=dept.deptno);
在许多基于基础表的查询中, 为了满足一个条件, 往往需要对另一个表进行联接. 在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.
在子查询中, NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下, NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).
为了避免使用NOT IN, 我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
高效: SELECT * FROM EMP (基础表) WHERE EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
低效: SELECT * FROM EMP (基础表) WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)
3. 数据库的备份
mysqldump -uroot -proot dbname > d:/my.sql;
mysql -uroot -p < C:\my.sql