SQL语言与MySQL数据库（四）

数据库设计

1.多表关系

一对一关系：人和身份证

一个人只能有一个身份证，一个身份证只能对应一个人

实现方式：一对一关系的实现，可以在任意一方添加唯一外键指向另一方的主键。而一般情况下一对一关系放在一张表中。

一对多关系：员工和部门

一个部门可以有多个员工，而一个员工对应一个部门

实现方式：在多的一方设置外键对应一的一方的主键即可。

多对多关系：学生和课程

一个学生可以选择多门课程，而一门课程可以有多个学生

实现方式：多对多关系的实现需要借助第三张中间表实现。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

案例：一对多、多对多关系

-- 分类表

CREATE TABLE tab_category(

cid INT PRIMARY KEY AUTO_INCREMENT,

cname VARCHAR(100) NOT NULL UNIQUE

);

-- 线路表

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)  -- 简化形式，省略外键名称

);

-- 用户表

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)

);

-- 中间表

CREATE TABLE tab_favorite(

rid INT,

DATE DATETIME,

uid INT,

PRIMARY KEY(rid,uid), -- 联合主键

FOREIGN KEY (rid) REFERENCES tab_route(rid),

FOREIGN KEY (uid) REFERENCES tab_user(uid)

);

2.数据库设计三大范式

概念：设计数据库时，需要遵循的一些规范。要遵循后边的范式，就必须先遵循前边的范式。

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

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

名词解释：

1. 函数依赖：A-->B，如果通过A属性（属性组）的值，可以确定唯一B属性的值。则称B依赖于A；
2. 完全函数依赖：A-->B，如果A是一个属性组，则B属性值需要依赖于A属性组中所有的属性值；
3. 部分函数依赖：A-->B，如果A是一个属性组，则B属性值只需要依赖于A属性组中某一些的属性值；
4. 传递函数依赖：A-->B B-->C，如果通过A属性（属性组）的值，可以唯一确定B属性的值，再通过B属性（属性组）的值可以为确定C属性的值，则称C传递依赖于A；
5. 码：如果在一张表中，一个属性或属性组，被其他所有属性完全依赖，则称这个属性（属性组）为该表的码；主属性是码属性组中的所有属性，非主属性是除码属性组的属性。

第一范式（1NF）：每一列都是不可分割的原子数据项；

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）；

第三范式（2NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

三大范式的案例：

                                                                                               原表

学号	姓名	系		课程名称	分数
		系名	系主任
10010	张三	经济系	刘欢	高等数学	95
10010	张三	经济系	刘欢	大学英语	87
10010	张三	经济系	刘欢	计算机基础	75
10011	李四	法律系	赵静	法理学	68
10011	李四	法律系	赵静	大学英语	87
10011	李四	法律系	赵静	法律社会学	69
10012	王五	法律系	赵静	法律社会学	84
10012	王五	法律系	赵静	法理学	82
10012	王五	法律系	赵静	大学英语	91

存在问题：原表不满足第一范式，第三列存在可分割的原子数据项

                                                                                        第一范式1NF

学号	姓名	系名	系主任	课程名称	分数
10010	张三	经济系	刘欢	高等数学	95
10010	张三	经济系	刘欢	大学英语	87
10010	张三	经济系	刘欢	计算机基础	75
10011	李四	法律系	赵静	法理学	68
10011	李四	法律系	赵静	大学英语	87
10011	李四	法律系	赵静	法律社会学	69
10012	王五	法律系	赵静	法律社会学	84
10012	王五	法律系	赵静	法理学	82
10012	王五	法律系	赵静	大学英语	91

存在问题：

存在非常严重的数据冗余，姓名、系名、系主任；

数据添加存在问题，添加一个新的系和系主任时数据不合法；

数据删除存在问题，一个学生退学后，所在的系就会消失。

                                                                                  第二范式2NF

学号	课程名称	分数
10010	高等数学	95
10010	大学英语	87
10010	计算机基础	75
10011	法理学	68
10011	大学英语	87
10011	法律社会学	69
10012	法律社会学	84
10012	法理学	82
10012	大学英语	91

学号	姓名	系名	系主任
10010	张三	经济系	刘欢
10011	李四	法律系	赵静
10012	王五	法律系	赵静

存在问题：

数据添加存在问题，添加一个新的系和系主任时数据不合法；

数据删除存在问题，一个学生退学后，所在的系就会消失。

                                                                                    第三范式3NF

第三范式在第二范式的基础上消除了传递依赖，解决了添加和删除的问题。

学号	课程名称	分数
10010	高等数学	95
10010	大学英语	87
10010	计算机基础	75
10011	法理学	68
10011	大学英语	87
10011	法律社会学	69
10012	法律社会学	84
10012	法理学	82
10012	大学英语	91

学号	姓名
10010	张三
10011	李四
10012	王五

系名	系主任
经济系	刘欢
法律系	赵静

3.数据库的备份与还原

备份数据库(cmd)：mysqldump -u root  -p 数据库名>路径备份文件名;      #备份整个数据库

                 mysqldump -u root -p 数据库名 表名字>路径备份文件名;      #备份整个表

         实例：  mysqldump -u root -p mysql_shiyan > D:\bak.sql;

恢复数据库：第一种方法：
                    

-- 先要新建一个数据库，test为一个新建的空的数据库。

                     use test;

                     source D:\bak.sql

                     第二种方法(cmd)：
                   

 -- 先要新建一个数据库，test为一个新建的空的数据库。

  mysql -u root  -p test < D:\bak.sql