多表关系、数据库设计的范式、数据库的备份和还原、多表查询、事务、DCL（了解）

Posted on by hackdl

多表关系

多表之间的关系分类和实现及模型图：

  1. 一对一(了解)：
  • 如：人和身份证
  • 分析：一个人只有一个身份证，一个身份证只能对应一个人
  • 实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。（但这样的话我们为什么不把信息建在一张表上呢，所以，我们只要了解，实现要随情况而定）
  • 模型图

  1. 一对多(多对一)：
  • 如：部门和员工
  • 分析：一个部门有多个员工，一个员工只能对应一个部门
  • 实现方式：在多的一方建立外键，指向一的一方的主键。（其实就是我们上一篇提到的外键的创建方式）
  • 模型图

  1. 多对多：
  • 如：学生和课程
  • 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
  • 实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。
  • 模型图

一对一我们就了解，想做的话就普通定义就行了，一对多就是我们上一篇定义的，我们讲一下多对多

如：我们访问了一个旅游网站，我们选择了一个风景游，然后我们可以看到不同风景的旅游，我们（用户）收藏了几个风景旅游的线路表，那么，这时候，就会出现 一个用户收藏多个线路，一个线路被多个用户收藏 这种情况，这时用户和路线就是多对多的情况，收藏作为中间表。

该模型图大概如下

然后就是我们着手建立了

步骤：

  1. 由上图可只，线路表和分类是多对一的关系，所以我们先定义出分类表和线路表，然后用线路表的cid作为外键与分类表的cid相连

创建旅游线路分类表 tab_category

-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键，自动增长
-- cname 旅游线路分类名称非空，唯一，字符串 100
CREATE TABLE tab_category (
 cid INT PRIMARY KEY AUTO_INCREMENT,
 cname VARCHAR(100) NOT NULL UNIQUE
)
-- 添加旅游线路分类数据：
INSERT INTO tab_category (cname) VALUES ('周边游'), ('出境游'), ('国内游'), ('港澳游');
SELECT * FROM tab_category;

创建旅游线路表 tab_route

/*
创建旅游线路表 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)
)
-- 添加旅游线路数据
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;
  1. 然后就是线路表和用户之间多对多的关系了，我们要把用户表定义出来。然后用中间表的rid和uid做外键分别与线路表的rid与用户的id相连

创建用户表 tab_user

/*创建用户表 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)
);
-- 添加用户数据
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（即中间表）

/*
创建收藏表 tab_favorite
rid 旅游线路 id，外键
date 收藏时间
uid 用户 id，外键
rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/服务器托管网
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)
);
-- 增加收藏表数据
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;

上述提到了联合主键

PRIMARY KEY(字段1,字段2);和主建的功能差不多，但去重复是两个字段完全一样才去重

根据架构设计器各表之间的关系为

数据库设计的范式

概念：

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

分类及各自存在的问题：

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

如下图：表头行的系被分割为系名和系主任，这就不符合第一范式

要符合第一范式的话要把系给去掉，系名和系主任各执掌一列，如下图

第一范式存在问题

  • 存在非常严重的数据冗余(重复): 姓名、系名、系主任
  • 数据添加存在问题: 添加新开设的系和系主任时，数据不合法
  • 数据删除存在问题: 张无忌同学毕业了，删除数据，会将系的数据一起删除。
  1. 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）

我们要了解以上标黄的几个概念，用例子来理解

  • 函数依赖：A–>B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A

例如：学号–>姓名。 （学号，课程名称） –> 分数

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

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

  • 部分函数依赖：A–>B， 如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中某一些值即可。

例如：（学号，课程名称） –> 姓名

  • 传递函数依赖：A–>B, B –>C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A

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

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

例如：下表选课表中码为：（学号，课程名称）

  • 主属性：码属性组中的所有属性
  • 非主属性：除过码属性组的属性

由上述可知，第二范式就是分表（把总表分为选课表和学生表）

选课表和学生表都符合第一范式，选课表中：分数完全依赖于（学号和课程）这个属性组；学生表中：（姓名，系名，系主任）完全依赖于学号

第二范式存在问题

  • 数据添加存在问题: 添加新开设的系和系主任时，数据不合法
  • 数据删除存在问题: 张无忌同学毕业了，删除数据，会将系的数据一起删除。
  1. 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

比如：第二范式中非主属性系主任依赖于非主属性系名

所以我们要继续分表，如下

这样，以上的三个问题就都解决了

数据库的备份和还原

我们有两种方式

（1）命令行：

语法：

  • 备份：

mysqldump -u用户名 -p密码 数据库名称 > 保存的路径

  • 还原：步骤①登录数据库②创建数据库
    ③使用数据库
    ④执行文件：source 文件路径

（2）图形化工具（SQLyog）：

①在数据库列表处选择要备份的数据库

②选择要备份到的路径

③然后删除我们备份过的数据库

④在数据库列表出右键，然后选择之星SQL脚本

⑤填写我们备份的路径后执行就OK了

多表查询

查询语法：

select列名列表from表名列表
where…

先准备一些数据

-- 创建部门表
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);

两张表如下：

部门表

员工表

我们进行多表查询就要知道笛卡尔积，这是我们在查询时会出现的一种情况

笛卡尔积：

  • 有两个集合A,B ，取这两个集合的所有组成情况。
  • 要完成多表查询，需要消除无用的数据

笛卡尔积实例：

SELECT*FROM emp,dept;

我们会发现有许多数据是错误的，所以我们要实现有意义的多表查询就要进行条件判断

多表查询的分类：内连接查询

内连接分为隐式内连接和显式内连接。

隐式内连接：使用where条件消除无用数据

例如：

  • 查询所有员工信息和对应的部门信息
SELECT*FROM emp,dept WHERE dept.`id`=emp.`dept_id`; -- 撇号可以不要

查询结果如下图

  • 查询员工表的名称，性别，部门表的名称
SELECT emp.`NAME`,emp.`gender`,dept.`NAME` FROM emp,dept WHERE dept.`id`=emp.`dept_id`;

一般为了方便加注释和理解，我们通常每个关键字占一行，内容占一行，有些表名过长我们同时改别名

SELECT 
	t1.`NAME`,-- 员工表的姓名
	t1.`gender`,-- 员工表的性别
	t2.`NAME` -- 部门的名称
FROM 
	emp t1,dept t2 
WHERE 
	t1.`dept_id`=t2.`id`

显式内连接

语法：

select 字段列表 from 表名1 inner join 表名2 on 条件（inner可省略）

例如：

  • 查询员工表的名称，性别，部门表的名称
SELECT
	t1.`NAME`,
	t1.`gender`,
	t2.`NAME`
FROM
	emp t1
INNER JOIN
	dept t2
ON
	t1.`dept_id`=t2.`id`

-- 不加inner
SELECT
	t1.`NAME`,
	t1.`gender`,
	t2.`NAME`
FROM
	emp t1
JOIN
	dept t2
ON
	t1.`dept_id`=t2.`id`

内连接查询步骤：

  • ①要知道从哪些表中查询数据
  • ②要得到想要的结果的条件是什么
  • ③要查询哪些字段

多表查询的分类：外连接查询

外连接查询分为左外连接和右外连接

左外连接

语法：

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

查询两张以上的表的语法：

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

查询的是左表所有数据以及其交集部分。（如果我们向员工表里添加了一行数据，员工表又在左边，但并未给外键赋值（为null），这个时候新加的数据也会被查询出来，但部门表的信息为null）

【左表的判断为哪个表先写哪个是左表】

例子：

-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门
SELECT*FROM 
emp t1 
LEFT JOIN 
dept t2 
ON
t1.`dept_id`=t2.`id`;

结果如下

右外连接

语法：

select 字段列表 from 表1 right [outer] join 表2 on 条件；

查询的是右表所有数据以及其交集部分。

例子：

SELECT*FROM dept t2  RIGHT JOIN emp t1 ON t1.`dept_id`=t2.`id`;

结果如下

满外连接

注意：Oracle数据库里面有full join，可是在mysql数据库中对full join支持的不好。我们可以使用union来达到目的。

Oracle语法：

select *from A full outer join B on 条件

MySQL使用union语法

左外连接UNION右外连接注意：左右两张表要一样，且顺序不能变

SELECT*FROM dept LEFT OUTER JOIN emp ON dept_id=deptno
UNION 
SELECT*FROM dept RIGHT OUTER JOIN emp ON dept_id=deptno;

注意：使用union可以起到去重的效果，使用union all 是将两张表直接拼接。

多表查询的分类：子查询

概念：

  • 查询中嵌套查询，称嵌套查询为子查询。（这个嵌套的查询要用括号括起来）

比如说：我们要查询员工最高工资的人的信息，以往查询是分两步

①查询最高的工资是多少

SELECT MAX(salary) FROM emp;

② 查询员工信息，并且工资等于9000的

SELECT * FROM emp WHERE emp.`salary` = 9000;

我们学习子查询后，一条SQL就完成这个操作

SELECT*FROM emp WHERE emp.`salary`=(SELECT MAX(salary) FROM emp);

子查询有不同情况

①子查询的结果是单行单列是：

  • 子查询可以作为条件，使用运算符去判断。 运算符： >、>=、=、

如：查询员工工资小于平均工资的人

SELECT * FROM emp WHERE emp.salary

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

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

如：查询“财务部”和“市场部”所有的员工信息

我们以前是先查询部门与员工外键相连的id数据，然后根据id数据查询员工表的数据，得到的id数据一般是多行单列的

SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;

用子查询的话，多行当列的数据就相当于in后（）中的数据

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

③子查询的结果是多行多列的：

  • 子查询可以作为一张虚拟表参与查询

如：查询员工入职日期是2011-11-11日之后的员工信息和部门信息

用普通内连接查询是

SELECT * FROM 
	emp t1,
	dept t2 
WHERE 
	t1.`dept_id` = t2.`id` 
AND 
	t1.`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;

事务

事务的概念：

  • 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

事物的操作：

  • 开启事务： start transaction或begin;
  • 回滚：rollback;
  • 提交：commit;

如下面这个例子：zhangsan和lisi进行转账操作，初始化都为1000元，开启事务（start transaction）后张三给李四转账500元，那么zhangsan和lisi的账户分别为500和1500，但是这个过程可能会出现错误，比如zhangsan给lisi转完帐后，系统出现了异常，那么zhangsan的钱变为500，而lisi的钱并未增加，但此时的事务并未被commit提交，所以我们可以进行回滚操作（rollback）回到开启事务前的状态，这样就可以避免错误的发生

CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);


SELECT * FROM account;
UPDATE account SET balance = 1000;
-- 张三给李四转账 500 元

-- 0. 开启事务
START TRANSACTION;
-- 1. 张三账户 -500

UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
-- 2. 李四账户 +500
-- 出错了...
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';

-- 发现执行没有问题，提交服务器托管网事务
COMMIT;

-- 发现出问题了，回滚事务
ROLLBACK;

MySQL数据库中事务默认自动提交

事务提交的两种方式：

①自动提交：

  • MySQL就是自动提交的
  • 一条DML(增删改)语句会自动提交一次事务

②手动提交：

  • Oracle 数据库默认是手动提交事务
  • 需要先开启事务，再提交

修改事务的默认提交方式：

  • 查看事务的默认提交方式：
SELECT @@autocommit;-- 1 代表自动提交 ，0 代表手动提交
  • 修改默认提交方式：
set @@autocommit = 0;  -- set autocommit=0; 也可以
set @@autocommit = 1;  -- set autocommit=1; 也可以

事务的四大特征和隔离级别

四大特征：

  • 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
  • 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
  • 隔离性：多个事务之间。相互独立。（与事务的隔离级别有关）
  • 一致性：事务操作前后，数据总量不变

对该四大特征的解释可以看 m旧裤子 博主的这篇博客链接: 事务四大特性详解

事务的隔离级别（了解）

概念：

  • 多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

设置隔离级别会存在一些问题：

  • ①脏读：一个事务，读取到另一个事务中没有提交的数据
  • ②不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
  • ③幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

对这三种问题的解释可以看 qq_41776884 博主的这篇博客链接: 数据库事务隔离级别（脏读、幻读、不可重复读）

四种隔离级别：

  1. read uncommitted：读未提交
  • 产生的问题：脏读、不可重复读、幻读
  1. read committed：读已提交 （Oracle）
  • 产生的问题：不可重复读、幻读
  1. repeatable read：可重复读 （MySQL默认）
  • 产生的问题：幻读
  1. serializable：串行化
  • 可以解决所有的问题

注意：隔离级别从小到大安全性越来越高，但是效率越来越低，所以我们需要选择适当的隔离级别，这样相对的既保证了安全性，效率还不低。

数据库查询隔离级别：

select @@tx_isolation;

数据库设置隔离级别：（数据库设置隔离级别后要断开连接重新打开才能生效）

语法：set global transaction isolation level 级别字符串;

DCL（了解）

前面我们提到SQL的分类：

  • DDL：操作数据库和表
  • DML：增删改表中数据
  • DQL：查询表中数据
  • DCL：管理用户，授权

我只说了前三种，因为DCL是由 DBA（数据库管理员）操作的，我们只需要了解一下就行

DCL的作用为：管理用户和授权

管理用户包括：

  • 添加用户：

语法： CREATE USER ‘用户名’@’主机名’ IDENTIFIED BY ‘密码’;

  • 删除用户：

语法：DROP USER ‘用户名’@’主机名’;

  • 修改用户密码：

语法：UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’;

如：

UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';

MySQL5.7 不能用update user set

解决方案可以看简书上 似朝朝我心 的文章：

链接: update user set Password = password(‘12345678’) where User = ‘root’; ERROR 1054 (42S22): Unknown …

语法：SET PASSWORD FOR ‘用户名’@’主机名’ = PASSWORD(‘新密码’); //DCL特有方式

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

MySQL8修改密码:

语法：ALTER USER ‘用户名’@’主机名’ IDENTIFIED BY ‘新密码’;

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

如果mysql中忘记了root用户的密码，我们可以进行如下操作

  1. cmd — > net stop mysql 停止mysql服务（需要管理员运行该cmd）
  2. 使用无验证方式启动mysql服务： mysqld –skip-grant-tables
  3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
  4. use mysql;
  5. update user set password = password(‘你的新密码’) where user = ‘root’;
  6. 关闭两个窗口
  7. 打开任务管理器，手动结束mysqld.exe 的进程
  8. 启动mysql服务
  9. 使用新密码登录。

mysql8.0版本下命令行mysqld –skip-grant-tables 失效，无法登陆

该问题的解决可以看博主 Kante_616 的这篇文章：

链接: mysql8.0版本下命令行mysqld –skip-grant-tables 失效，无法登陆的问题

  • 查询用户

步骤：

①切换到mysql数据库

USE mysql;

② 查询user表

SELECT * FROM USER;
  • 通配符： % 表示可以在任意主机使用用户登录数据库

权限管理：

  1. 查询权限：

语法：SHOW GRANTS FOR ‘用户名’@’主机名’;

如：

SHOW GRANTS FOR 'lisi'@'%';
  1. 授予权限：

语法：grant 权限列表 on 数据库名.表名 to ‘用户名’@’主机名’;

如：

-- 给张三用户授予所有权限，在任意数据库任意表上
GRANT ALL ON *.* TO 'zhangsan'@'localhost';
  1. 撤销权限：

语法：revoke 权限列表 on 数据库名.表名 from ‘用户名’@’主机名’;

如：

REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: