数据库设计
前言
数据库设计(Database Design)
是指对于一个给定的应用环境,构造最优的数据库模式,建立数据库及其应用系统,使之能够有效地存储数据,满足各种用户的应用需求(信息要求和处理要求)。在数据库领域内,常常把使用数据库的各类系统统称为数据库应用系统。
1.了解数据库设计的步骤
数据库设计的作用 :随着项目规模变大,设计变得越来越重要。
- 良好的数据库设计:能接受数据的存储空间、能够保证数据的完整性、方便进行数据应用系统的开发;
- 糟糕的数据库设计:会产生数据冗余、存储空间浪费、内存空间浪费、数据更新和插入的异常。
数据库设计的步骤(生命周期):
-
需求分析:分析客户需求。
进行数据库设计首先必须准确了解和分析用户需求(包括数据与处理)。
需求分析是整个设计过程的基础,也是最困难,最耗时的一步。
需求分析是否做得充分和准确,决定了在其上构建数据库大厦的速度与质量。
需求分析做的不好,会导致整个数据库设计返工重做。 -
概要设计:设计E-R模型图,确认需求完整。
在这阶段,E-R图显得异常重要。大家要学会各个实体定义的属性来画出总体的E-R图。
各分E-R图之间的冲突主要有三类:属性冲突,命名冲突,和结构冲突。
E-R图向关系模型的转换,要解决的问题是如何将实体性和实体间的联系转换为关系模式,如何确定这些关系模式的属性和码。 -
详细设计:利用三大范式进行完善,规范。
详细设计(物理设计)是为逻辑数据结构模型选取一个最适合应用环境的物理结构(包括存储结构和存取方法)。
首先要对运行的事务详细分析,获得选择物理数据库设计所需要的参数,其次,要充分了解所用的RDBMS的内部特征,特别是系统提供的存取方法和存储结构。 -
代码编写:构建真实的数据库,添加测试数据。
-
软件测试:测试数据库是否能满足真实的场景需求。
软件测试阶段,设计人员运营DBMS提供的数据库语言(如sql)及其宿主语言,根据逻辑设计和物理设计的结果建立数据库,编制和调试应用程序,组织数据入库,并进行试运行。
-
安装部署:试运行阶段。
-
生产环境使用。
例如:需求:设计一个酒店管理系统,能够实现旅客入住手续和客房管理。
- 收集信息:
与有关人员进行沟通、座谈、详细了解相应的需求。 - 标识实体 、存储各类信息
旅客信息、客房信息。 - 实体属性
(1)、旅客:编号、姓名、性别、身份证、手机号、入住时间、退房时间、押金、消费总金额、入住客房 …
(2)、客房:编号、客房号、客房类型、客房床位费、客房入住人数、客房状态 … - 标识实体关系
旅客入住客房
2.掌握绘制数据库的E-R图
- 首先下载visio,打开visio。
- 在搜索栏里输入“数据库”,搜索完后选择“数据库 Cheney’s 表示法”。
- 打开后,在左侧选择对应的属性,鼠标左键单击拖动到中间空白区域。
酒店管理系统的E-R图
3.掌握数据库设计的三大范式
- 确保每列的原子性(不可分割) 即每一张表中每个字段存储的信息必须足够单一
- 一张表只能表示/描述一件事 如果在一张表中描述过多的事情 会导致数据冗余和不完整
- 表的字段之间不允许出现传递依赖。
A -> B -> C A和C就是传递依赖
可以到时候将传递依赖的信息抽取到多张表或者一张数据字典表
数据字典表:
类型:大床房、双人间、单人间、豪华双人间…
客房状态:已预订、已入住、空闲。
酒店设施:有早餐、有无空调、有无外窗、有无wifi、有无停车位
规范化和实际性能/复杂度等方面的关系
为了降低查询复杂度和提升性能:
- 允许一定的冗余列。
- 允许一些特别的计算列。
根据实际情况和需求决定规范和功能的取舍。
4.掌握数据库设计的三大范式
- PRIMARY KEY:设置主键约束;
- UNIQUE:设置唯一性约束,不能有重复值;
- DEFAULT 默认值约束,height DOUBLE(3,2)DEFAULT 1.2 height不输入是默认为1,2
- NOT NULL:设置非空约束,该字段不能为空;
- FOREIGN key :设置外键约束。
【主键】
- 主键的注意事项?
主键默认非空,默认唯一性约束,只有主键才能设置自动增长,自动增长一定是主键,主键不一定需要自动增长; - 设置主键的方式?
在定义列时设置:ID INT PRIMARY KEY
在列定义完之后设置:primary KEY(id)
【外键】
- 设置外键的注意事项:
只有INNODB的数据库引擎支持外键,修改my.ini文件设置default-storage-engine=INNODB 外键必须与参照列的数据类型必须相同(数值型要求长度和无符号都相同,字符串要求类型相同,长度可以不同)。
-
设置外键的语法:
CONSTRAINT 外键名 FOREIGN KEY (外键字段)REFERENCES 参照表 (参照字段) ON DELETE SET NULL ON UPDATE CASCADE 设置参照完整性
-
外键约束的参照操作
参照表的完整性操作:当对参照表的参照字段进行删除或更新时,外键表中的外键如何应对;
参照操作可选值:
RESTRICT拒绝参照表删除或更新参照字段;
RESTRICT和NO ACTION相同,但这个指令只在mysql生效;
CASCADE删除或更新参照表的参照字段时,外键表的记录同步删除更新;
SET NULL 删除或更新参照表的参照字段时,外键表的外键设为NULL;
5.掌握用户管理和用户授权
- 创建用户 %表示任意机器 localhost 表示 本机
CREATE USER 用户名@localhost IDENTIFIED BY '用户密码';
- 给用户授权(可以只是授权,也可以授权的同时创建对应的用户)
GRANT SESECT ON 数据库名.数据库表名 TO '用户名'@'localhost';
- 授权时创建用户(ALL 表示 所有权限)(*表示所有)
GRANT ALL ON 数据库名.* TO 用户名@localhost IDENTIFIED BY '密码'
- 删除用户 可以同时删除多个用户 以,隔开
DROP USER 用户名@localhost;
- 修改用户密码
(1). 找到mysql的user表 然后修改对应的用户密码(root用户才能修改 修改自己或者普通用户)
use mysql;
update user set password = password('密码')where user = xx and host = xx;
(2). 普通用户和root都可以使用
SET PASSWORD = PASSWORD('密码');
(3). root才能改其它的
SET PASSWORD FOR 用户名@主机地址 = PASSWORD('密码');
(4).利用DOS程序mysqladmin
mysqladmin -u用户名 -p旧密码 password'新密码';
(5).当丢失密码如何找回?root
在mysql的配置文件 my.ini 添加一行sip-grant-table
这样的话登陆root不在需要密码,进去之后在按照上方规则可以修改密码
记得用完之后再将选项删除