数据库设计

一、三范式

注：设计只是一种思想、一种理念，我们按照规范的设计方式设计数据库对我们来说有好处，但绝对不是说一定要严格遵守，三范式能极大的减少数据冗余，但是相对编写sql而言是增加了难度的，所以所有好的设计都是要权衡利弊的，要对编码难度，存储大小，执行效率等多方面进行综合考量，但是在学习初期最好紧紧的遵循三范式，在后续的编码中体会和总结自己的经验。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

设计数据库表的时候所依据的规范中，前三个规范即可满足：

第一范式：要求有主键，并且要求每一个字段原子性不可再分
第二范式：第二范式是建立在第一范式基础上的，要求所有非主键字段完全依赖主键，不能产生部分依赖
第三范式：建立在第二范式基础上的，所有非主键字段和主键字段之间不能产生传递依赖

第一范式

数据库表中不能出现重复记录，每个字段是原子性的不能再分

不符合第一范式的实例：

学生编号	学生姓名	联系方式
1001	白杰	bj@qq.com,18565987896
1002	杨春旺	ycw@qq.com,13659874598
1003	张志伟	zzw@qq.com,12598745698

解决方案

学生编号	学生姓名	邮箱地址	联系电话
1001	白杰	bj@qq.com	18565987896
1002	杨春旺	ycw@qq.com	13659874598
1003	张志伟	zzw@qq.com	12598745698

不符合第一范式的实例，不是说他错哈：

学生编号	学生姓名	联系地址
1001	白杰	西安市长安区恒山路108号
1002	杨春旺	西安市莲湖区迎泽大家100号
1003	张志伟	西安市雁塔区北大街152号

解决方案：

学生编号	学生姓名	市	区	详细地址
1001	白杰	西安市	长安区	恒山路108号
1002	杨春旺	西安市	莲湖区	迎泽大街100号
1003	张志伟	西安市	雁塔区	北大街152号

必须有主键，这是数据库设计的最基本要求，主要采用数值型或定长字符串表示，关于列不可再分，应该根据具体的情况来决定。如联系方式，为了开发上的便利可能就采用一个字段。

关于第一范式，每一行必须唯一，也就是每个表必须有主键，这是数据库设计的最基本要求，主要采用数值型或定长字符串表示，关于列不可再分，应该根据具体的情况来决定。如联系方式，为了开发上的便利可能就采用一个字段。

第二范式

第二范式是建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖

不符合第二范式的案例：

其中学生编号和课程编号为联合主键

学生编号	性别	学生姓名	课程编号	课程名称	教室	成绩
1001	男	白杰	2001	java	3004	89
1002	男	杨春旺	2002	mysql	3003	88
1003	女	刘慧慧	2003	html	3005	90
1001	男	白杰	2002	mysql	3003	77
1001	男	白杰	2003	html	3005	89
1003	女	刘慧慧	2001	java	3004	90

以上虽然确定了主键，但此表会出现大量的数据冗余，出现冗余的原因在于，学生信息部分依赖了主键的一个字段学生编号，和课程id没有毛线关系。同时课程的信息只是依赖课程id，和学生id没有毛线关系。只有成绩一个字段完全依赖主键的两个部分，这就是第二范式部分依赖。

解决方案：

学生表：学生编号为主键

学生编号	性别	学生姓名
1001	男	白杰
1002	男	杨春旺
1003	女	刘慧慧

课程表：课程编号为主键

课程编号	课程名称	教室
2001	java	3003
2002	mysql	3003
2003	html	3005

成绩表：学生编号和课程编号为联合主键

学生编号	课程编号	成绩
1001	2001	89
1002	2002	88
1003	2003	90
1001	2002	77
1001	2003	89
1003	2001	90

如果一个表是单一主键，那么它就是复合第二范式，部分依赖和主键有关系

以上是典型的“多对多”设计

第三范式

建立在第二范式基础上的，非主键字段不能传递依赖于主键字段（不要产生传递依赖）

不满足第三范式的例子：

其中学生编号是主键

学生编号	学生姓名	专业编号	专业名称
1001	白杰	2001	计算机
1002	杨春旺	2002	自动化
1003	张志伟	2001	计算机

何为传递依赖

专业编号依赖学生编号，应为该学生学的就是这个专业啊。但是专业名称和学生其实没多大关系，专业名称依赖于专业编号。这就叫传递依赖，就是某一个字段不直接依赖主键，而是依赖 依赖主键的另一个字段。

解决方法：

学生表，学生编号为主键：

学生编号为主键：

学生编号	学生姓名	专业编号
1001	白杰	2001
1002	杨春旺	2002
1003	张志伟	2001

专业表，专业编号为主键：

专业编号	专业名称
2001	计算机
2002	自动化

以上设计是典型的一对多的设计，一存储在一张表中，多存储在一张表中，在多的那张表中添加外键指向一的一方。

二、常见表关系

1. 一对一 (用的不多，了解即可)

一个表和另一张表存在的关系是一对一，此种设计不常用，应为此种关系经常会将多张表合并为一张表。

举例：

学生信息表可以分为基本信息表，和详细信息表。

可能有这种需求，需要给个某个账户对学生表的操作，但是有些私密信息又不能暴露，就可以拆分。

第一种方案：分两张表存储，共享主键
第二种方案：分两张表存储，外键唯一

2. 一对多（多对一）

如：部门和员工
分析：一个部门有多个员工，一个员工只能对应一个部门

第三范式的例子

两张表 外键建在多的一方

分两张表存储，在多的一方添加外键，
这个外键字段引用一的一方中的主键字段

3. 多对多

如：学生和课程
分析：一个学生可以选择很多课程，一个课程可以被很多学生选择
第二范式的例子

分三张表存储，在学生表中存储学生信息，在课程表中存储课程信息，
在成绩表中存储学生和课程的关系信息

4. 案例

思路：

		-- 创建旅游线路分类表 tab_category
		-- cid 旅游线路分类主键，自动增长
		-- cname 旅游线路分类名称非空，唯一，字符串 100
		CREATE TABLE tab_category (
			cid INT PRIMARY KEY AUTO_INCREMENT,
			cname VARCHAR(100) NOT NULL UNIQUE
		);
		-- 创建旅游线路表 tab_route
		/*
		rid 旅游线路主键，自动增长
		rname 旅游线路名称非空，唯一，字符串 100
		price 价格
		rdate 上架时间，日期类型
		cid 外键，所属分类
		*/
		CREATE TABLE tab_route(
			rid INT PRIMARY KEY AUTO_INCREMENT,
			rname VARCHAR(100) NOT NULL UNIQUE,
			price DOUBLE,
			rdate DATE,
			cid INT,
			FOREIGN KEY (cid) REFERENCES tab_category(cid)
		);
		/*创建用户表 tab_user
		uid 用户主键，自增长
		username 用户名长度 100，唯一，非空
		password 密码长度 30，非空
		name 真实姓名长度 100
		birthday 生日
		sex 性别，定长字符串 1
		telephone 手机号，字符串 11
		email 邮箱，字符串长度 100
		*/
		CREATE TABLE tab_user (
			uid INT PRIMARY KEY AUTO_INCREMENT,
			username VARCHAR(100) UNIQUE NOT NULL,
			PASSWORD VARCHAR(30) NOT NULL,
			NAME VARCHAR(100),
			birthday DATE,
			sex CHAR(1) DEFAULT '男',
			telephone VARCHAR(11),
			email VARCHAR(100)
		);
		/*
		多对多的中间表
		创建收藏表 tab_favorite
		rid 旅游线路 id，外键
		date 收藏时间
		uid 用户 id，外键
		rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
		*/
		CREATE TABLE tab_favorite (
			rid INT, -- 线路id
			DATE DATETIME,
			uid INT, -- 用户id
			-- 创建复合主键
			PRIMARY KEY(rid,uid), -- 联合主键
			FOREIGN KEY (rid) REFERENCES tab_route(rid),
			FOREIGN KEY(uid) REFERENCES tab_user(uid)
		);

三、数据库的备份和还原

1. 命令行：
   • 语法：
     • 备份： mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
     • 还原：
       1. 登录数据库
       2. 创建数据库
       3. 使用数据库
       4. 执行文件。source 文件路径
2. 图形化工具：