数据库设计

1.多表之间的关系

2.数据库设计的范式

多表之间的关系

1、一对一的关系（了解）

比如，人和身份证。一个人对应一个身份证，一个身份证只能被一个人使用

2、一对多（多对一）的关系

比如，员工和部门。一个员工只能在一个部门，一个部门可以有多个员工。员工相对部门是多对一的关系，部门相对员工是一对多的关系。

3、多对多的关系

比如，学生和课程。一个学生可以选择多个课程，一个课程可以被多个学生选择。

实现关系

一对一的关系实现

一对一关系的实现，可以在任意一方添加唯一的外键来指向另一方的主键。

image-20200926225458347

一对多的关系实现

一对多关系的实现，在多（n）的一方，建立外键，来指向一的一方的主键。

image-20200926230326010

多对多的关系实现

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

image-20200926231202323

多表关系案例

问题描述

三个表：旅游线路分类，旅游线路，游客。在数据库中实现。

image-20200926232543530

表之间的关系

image-20200926233322624

具体操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
-- 建立分类表
-- 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)
);

-- 创建用户表
/*
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) NOT NULL UNIQUE,
	PASSWORD VARCHAR(30) NOT NULL,
	NAME VARCHAR(100),
	birthday DATE,
	sex VARCHAR(1) DEFAULT '男',-- default 指定默认值
	telephone VARCHAR(11),
	email VARCHAR(100)
);
-- 添加数据  insert into 表名[(列名,...)] values(值),(值),...;

INSERT INTO tab_category (cname) VALUES ('周边游'), ('出境游'), ('国内游'), ('港澳游');
SELECT * FROM tab_category;

INSERT INTO tab_route VALUES
(NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁 3 天 惠贵团】尝味友鸭面线 住 1 晚鼓浪屿', 1499,'2018-01-27', 1),
(NULL, '【浪漫桂林 阳朔西街高铁 3 天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-02-22', 3),
(NULL, '【爆款￥1699 秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州往返 特价团】', 1699, '2018-01-27', 2),
(NULL, '【经典•狮航 ￥2399 秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2017-12-23',2),
(NULL, '香港迪士尼乐园自由行 2 天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店暨会议中心标准房 1 晚住宿】', 799, '2018-04-10', 4);
SELECT * FROM tab_route;


INSERT INTO tab_user VALUES
(NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@qq.com'),
(NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@qq.com');
SELECT * FROM tab_user;

-- 创建线路表和用户表的中间表 -- 收藏表
/*
创建收藏表 tab_favorite
rid 旅游线路 id，外键
date 收藏时间
uid 用户 id，外键
rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
CREATE TABLE tab_favorite(
	rid INT,
	DATE DATE,
	uid INT,
	-- 创建联合主键
	PRIMARY KEY(rid,uid),
	FOREIGN KEY(rid) REFERENCES tab_route(rid),-- 添加外键的简化形式
	FOREIGN KEY(uid) REFERENCES tab_user(uid)
);

-- 添加收藏数据
INSERT INTO tab_favorite VALUES
(1, '2018-01-01', 1), -- 老王选择厦门
(2, '2018-02-11', 1), -- 老王选择桂林
(3, '2018-03-21', 1), -- 老王选择泰国
(2, '2018-04-21', 2), -- 小王选择桂林
(3, '2018-05-08', 2), -- 小王选择泰国
(5, '2018-06-02', 2); -- 小王选择迪士尼

SELECT * FROM tab_favorite;

三大范式

概念

设计数据库时需要遵循的规则。要满足后边的范式，必须遵循前面的范式。

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

分类

满足前三大范式，数据库设计基本就可以了。

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

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

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

三大范式详解

第一范式

image-20200929081704933

在如图示的学生信息表中，系这一列还可以分为系名和系主任，因此这个表不是每一项都是不可分割的原子项。（不满足第一范式）。

要让这个表满足第一范式，可以这样操作，直接将系名和系主任作为单独的列：

image-20200929082005067

不满足第一范式，在数据库中连表都创建不出。

满足第一范式存在的问题：

1.存在非常严重的数据冗余（重复）：姓名，系名，系主任。

2.数据添加存在问题：比如添加新开设的系和系主任时，数据不合法。

image-20200929082329457

3.删除数据存在问题：比如张无忌毕业了，删除数据，系名，系主任都会被删除。

第二范式

几个概念：

1.函数依赖：A–>B:如果通过A属性或属性组的值，可以确定唯一B属性的值，则说B函数依赖A

例如：学号–>姓名，<学号，课程名称>–>分数

2.完全函数依赖：A–>B,如果A是一个属性组，B属性的确定需要依赖于A属性组中所有属性的值，则称B完全依赖于A。

例如：<学号，课程名称>–>分数

3.部分函数依赖：A–>B，如果A是一个属性组，B属性的确定只需要依赖于A属性组中部分属性的值，则称B部分依赖于A。

例如：<学号，课程名称>–>姓名（只需学号就能确定姓名）

4。传递函数依赖：A–>B，B–>C：如果通过A属性或属性组的值，可以确定唯一B属性或属性组的值，同时，通过B属性或属性组的值，可以唯一确定C属性或属性组的值，则称C传递函数依赖于A

例如：学号–>系名，系名–>系主任

5.码：如果在一张表中，一个属性或属性组，被其他所有属性完全依赖，则称该属性或属性组为这个表的码。

例如：该表的码为：（学号，课程名称）

主属性：码属性组中的所有属性

非主属性：除码属性组的属性之外的属性。

满足第二范式的要求就是在第一范式基础上消除部分函数依赖。

image-20200929092617983

（学号，课程名称）作为码的话，姓名，系名，系主任都是部分依赖于码（姓名，系名完全依赖于学号，系主任传递依赖于学号），只有分数完全依赖于（学号，课程名称）。所以，可以把表进行拆分，以满足第二范式：

image-20200929092915704

image-20200929092951398

满足第二范式存在的问题：

2.数据添加存在问题：添加新开设的系和系主任时，数据不合法
3.数据删除存在问题：张无忌同学毕业了，删除数据，会将系的数据一起删除。

第三范式

满足第三范式需要在遵循第二范式的基础上消除传递依赖。

从第二范式的学生表来看，系主任传递依赖于学号，所以可以继续对表进行拆分。

image-20200929093230866

image-20200929093248278

image-20200929093308020

以上几个问题都不存在了。

数据库的备份和还原

命令行

备份

1
mysqldump -u用户名 -p密码 数据库名称 > 保存路径\文件名.sql

image-20200929094414760

image-20200929094616356

image-20200929094639576

还原

1.登录数据库

1
mysql -u用户名 -p;

2.创建数据库

1
create database 数据库名;

3.使用数据库：

1
use 数据库名

4.执行文件：

1
source 保存路径\文件名.sql

image-20200929095755781

image-20200929095815725

图形化界面

备份

image-20200929100430802

image-20200929100454200

还原

image-20200929100558175

多表查询

1
2
3
4
5
select
	列名列表
from 
	表名列表
where...

创建两个表：部门表，员工表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 创建部门表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);

-- 添加数据
INSERT INTO dept(NAME) VALUES('开发部'),('市场部'),('财务部');

-- 创建员工表
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	gender CHAR(1),
	salary DOUBLE,
	join_date DATE,
	dept_id INT,
	FOREIGN KEY (dept_id) REFERENCES dept(id)
);

-- 添加数据
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
1
select * from dept,emp;

image-20200929103312328

笛卡尔积：简单的说就是两个集合相乘的结果。

要完成多表查询需要消除无用查询。

多表查询的分类

内连接查询

组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

img

隐式内连接

使用where条件来消除无用查询。

1
2
-- 查询所有员工信息和对应的部门信息
SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`; -- 注意，不是单引号，是大键盘1旁边的符号 ，加或不加都一样

image-20200929104250106

1
2
-- 查询员工表的名称，性别，部门表的名称
select emp.name,emp.gender,dept.name from emp,dept where emp.dept_id = dept.id;

更常用的格式

1
2
3
4
5
6
7
8
9
select
	t1.name,
	t1.gender,
	t2.name
from
	emp t1, -- 起别名
	dept t2 
where
	t1.dept_id = t2.id;

image-20200929151319902

显式内连接

1
select 字段列表 from 表名1 [inner] join 表名2 on 条件;
1
2
-- 查询所有员工信息和对应的部门信息
select * from emp [inner] join dept on emp.`dept_id` = dept.`id`;

image-20200929152049136

image-20200929152113819

注意事项

从哪些表中查询数据；查询的条件是什么；查询哪些字段。

外连接

左外连接

与显式内连接类似

1
select 字段列表 from 表1名 left [outer] join 表2名 on 条件;

假设新增了一个员工，但是未指定部门（即dept_id为NULL）

image-20200929210431997

1
2
3
4
5
6
7
8
-- 查询所有员工信息，如果员工有部门，则显示部门名称，没有部门则不显示
select
	t1.*,t2.name
from
	emp t1,
	dept t2
where
	t1.dept_id = t2.id;

image-20200929210906698

会发现上述查询并不显示部门为NULL的员工信息。

1
2
3
4
5
6
7
8
select
	t1.*,t2.name
from
	emp t1
left join
	dept t2
on
	t1.dept_id = t2.id;

image-20200929211218700

左(外)连接，左表(相对)的记录将会全部表示出来，而右表(相对)只会显示符合搜索条件（两表交集）的记录。右表记录不足的地方均为NULL。

img

右外连接

与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件（两表交集 ）的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

1
2
3
4
5
6
7
8
9
-- 右外连接
SELECT 
	t1.*,t2.`name`
FROM
	emp t1
RIGHT JOIN
	dept t2
ON
	t1.`dept_id` = t2.`id`;

image-20200929213603281

子查询

查询中嵌套查询，称嵌套查询为子查询。

1
2
3
4
5
6
-- 查询工资最高的员工信息
-- 1.查询工资最高是多少？9000
select max(salary) from emp;
-- 2.查询员工信息，并且工资等于9000
select * from emp where emp.salary = 9000;

image-20200929214500246

1
2
-- 一步到位，不分开查询
select * from emp where emp.salary = (select max(salary) from emp);

image-20200929214730544

子查询的不同情况

1.子查询的结果是单行单列的：

​ 子查询可以作为条件，使用运算符（>、＞＝、<、＜=、=）去判断

1
2
-- 查询小于平均工资的员工信息
select * from emp where salary < (select avg(salary) from emp);

image-20200929221835583

2.子查询的结果是多行单列的：

​ 子查询可以作为条件，使用运算符in来判断。

1
2
3
-- 查询财务部和市场部所有员工的信息
select id from dept where name = '财务部' or name = '市场部';
select * from emp where dept_id = 2 or dept_id = 3;

image-20200929222714568

1
2
-- 子查询
select * from emp where dept_id in (select id from dept where name in('市场部','财务部'));

image-20200929223223480

3.子查询的结果是多行多列的：

子查询可以作为一张虚拟表来进行表的操作。

1
2
3
4
5
-- 查询入职日期是2011-11-11之后的员工信息和部门信息
-- 日期可以直接使用逻辑运算符来判断大小
select * from emp where emp.join_date > '2011-11-11';-- 作为虚拟表
select * from dept t1,(select * from emp where emp.join_date > '2011-11-11') t2 where t1.id = t2.dept_id;

image-20200929224039502