Java Web 02 — MySQL_02（数据库的设计、数据库的备份和还原、多表查询、事务、DCL）-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43508544/article/details/94635471

本文深入讲解数据库设计原理，包括多表关系、范式规范、备份还原等，详解事务特性及隔离级别，探讨DCL在用户管理和权限分配中的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

四、数据库的设计
- 4.1 多表之间的关系
- 4.2 数据库设计的范式
五、数据库的备份与还原
- 5.1 命令行
- 5.2 图形化工具
六、多表查询
七、事务
八、DCL
- 8.1 管理用户
- 8.2 权限管理

四、数据库的设计

4.1 多表之间的关系

分类：
1. 一对一（了解）：
  - 如：人和身份证
  - 分析：一个人只有一个身份证，一个身份证只能对应一个人
2. 一对多（多对一）：
  - 如：部门和员工
  - 分析：一个部门有多个员工，一个员工只能对应一个部门。
3. 多对多
  - 如：学生和课程
  - 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择。
实现关系
1. 一对多（多对一）
  - 如：部门和员工
  - 实现方式：在多的一方建立外键，指向1的一方的主键

在这里插入图片描述

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

在这里插入图片描述

一对一（了解）
- 如：人和身份证
- 实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方主键。

在这里插入图片描述

案例：

在这里插入图片描述

4.2 数据库设计的范式

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

在这里插入图片描述

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
- 几个概念：
  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. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性值）为该表的码。
    - 例如：学号、高等数学。
    1. 主属性：码属性组中的所有属性
    2. 非主属性：除码属性组的属性。

在这里插入图片描述

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

在这里插入图片描述

五、数据库的备份与还原

5.1 命令行

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

5.2 图形化工具

六、多表查询

查询的语法：
1. select 列名列表 from 表名列表;
准备SQL

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(20),
	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，取这两个集合的所有组成情况。
- 要完成多表查询，需要消除无用的数据

多表查询的分类：

内连接查询

隐式内连接：使用where来消除无用的数据。

-- 消除无用的数据

-- 查询所有员工信息和对应的部门信息
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;

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

SELECT 
	t1.name,t1.gender,t2.name
FROM
	emp t1, dept t2
WHERE 
	t1.dept_id = t2.id;

显式内连接：
- 语法1：select 字段列表 from 表1 inner join 表名2 on 条件;
- 语法1：select 字段列表 from 表1 join 表名2 on 条件;
```
SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
```
内连接查询：
1. 从哪些表中查询数据
2. 条件是什么
3. 查询哪些字段

外连接查询
1. 左外连接
  - 语法：select 字段列表 from 表1 left outer join 表2 on 条件;
  - 查询的是左表所有数据以及其交集部分
2. 右外连接
  - 语法：select 字段列表 from 表1 right outer join 表2 on 条件;
  - 查询的是右表所有数据以及其交集部分

子查询

概念：查询中嵌套查询，成为嵌套查询为子查询

-- 查询工资最高的员工信息
-- 1. 查询最高的工资是多少 9000
SELECT MAX(salary) FROM emp;

-- 2. 查询员工信息，并且工资等于9000
SELECT * FROM emp WHERE emp.salary = 9000;

-- 1条SQL就完成这个操作 —— 子查询
SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);

子查询的不同情况

子查询的结果是单行单列的：
- 子查询可以作为条件，使用运算符去判断。运算符：> >= < <= =
```
-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
```

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

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

-- 查询财务部和市场部所有的员工信息
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
SELECT * FROM emp WHERE dept_id IN (3,2);
-- 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

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

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

-- 查询员工入职日期是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;

-- 普通内连接
SELECT * FROM emp t1, dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > '2011-11-11';

七、事务

7.1 事务的基本介绍

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

操作

开启事务：start transaction
回滚：rollback
提交：commit

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;

-- 张三 给 李四 转账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数据库中事务默认自动提交
- 事务的提交有两种方式
  1. 自动提交
    - mysql就是自动提交的
    - 一条DML(增删改)语句会自动调焦一次事务
  2. 手动提交
    - oracle数据库默认是手动提交事务。
    - 需要先开启事务，再提交。
- 修改事务的默认提交方式
  - 查看事务的默认提交方式
```
SELECT @@autocommit; -- 1代表自动提交 0代表手动提交
```
  - 修改默认提交方式
```
SET @@autocommit = 0;
```

7.2 事务的四大特征

7.2.1 原子性

不可分割的最小操作单位，要么同时成功，要么同时失败。

7.2.2 持久性

当事务提交或回滚后，数据库会持久化的保存数据。

7.2.3 隔离性

多个事务之间，相互独立。

7.2.4 一致性

事务操作前后，数据总量不变。

7.3 事务的隔离级别（了解）

概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
存在的问题：
1. 脏的：一个事务读取到另一个事务中没有提交的数据。
2. 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样。
3. 幻读：一个事务操作（DML）数据表中所有的记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
隔离级别：
1. read uncommitted：读未提交
  - 产生的问题：脏读、不可重复读、幻读
2. read committed：读已提交（Oracle默认）
  - 产生的问题：不可重复读、幻读
3. repeatable read：可重复读（MySQL默认）
  - 幻读
4. serializable：串行化(相当于加锁)
  - 可以解决所有的问题
- 注意：隔离级别从小到大，安全性越来越高，但是效率越来越低。
- 数据库查询隔离级别：
  - select @@tx_isolation;
- 数据库设置隔离级别：
  - set global transaction isolation level 级别字符串;

八、DCL

SQL分类：
1. DDL：操作数据库和表
2. DML：增删改表中数据
3. DQL：查询表中的数据
4. DCL：管理用户，授权
DBA：数据库管理员
DCL：管理用户，授权

8.1 管理用户

用户的添加：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

   -- 创建用户
   CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
   CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123';
   CREATE USER 'lisi'@'%' IDENTIFIED BY '123';

删除用户：DROP USER '用户名'@'主机名';

   DROP USER '用户名'@'主机名';
   DROP USER 'zhangsan'@'localhost';

修改用户密码:
1. UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
2. SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
- mysql中忘记了root用户的密码？
  1. cmd ——> net stop mysql 停止mysql服务
  2. 使用无验证方式启动mysql服务：mysqld --skip-grant-tables
  3. 新窗口开启mysql，无需用户名和密码，输入mysql后直接回车
```
use mysql;
UPDATE USER SET PASSWORD = PASSWORD('root') WHERE USER = 'root';
```
  1. 打开任务管理器手动结束mysqld.exe
  2. 重新以管理员身份打开cmd登陆mysql

   UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
   UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
    
   SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
   SET PASSWORD FOR 'lisi'@'localhost' = PASSWORD('123');

查询用户

   -- 1. 切换到mysql数据库
   USE mysql;
   -- 2. 查询user表
   SELECT * FROM USER;
   
   -- 通配符：% 表示可以在任意主机用户

8.2 权限管理

查询权限

-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'root'@'%';

授予权限

-- 授予权限
GRANT 权限列表 ON 数据库.表名 TO '用户名'@'主机名';
GRANT SELECT,DELETE,UPDATE ON db3.account TO 'lisi'@'%';

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

撤销权限

-- 撤销权限
REVOKE 权限列表 ON 数据库.表名 FROM '用户名'@'主机名';
REVOKE UPDATE ON db3.account FROM 'zhangsan'@'%';