对于MySQL数据库的加深学习

最新推荐文章于 2024-06-25 22:59:16 发布

zhao_to

最新推荐文章于 2024-06-25 22:59:16 发布

阅读量109

点赞数

分类专栏：数据库学习记录文章标签： mysql 数据库

本文链接：https://blog.csdn.net/zhao_to/article/details/116500264

版权

学习记录同时被 2 个专栏收录

17 篇文章 0 订阅

订阅专栏

数据库

4 篇文章 0 订阅

订阅专栏

MySQl数据库

1.多表之间的关系

多表之间的关系其实也是我们数据库设计所要研究的重点。

8.1 一对一：

例子：人和身份证，一个人对应一个身份证，一个身份证对应一个人，无论从那个角度都成立。这种关系就是一对一的关系

实现：一对一关系实现，可以在任意一方添加唯一外键

8.2 一对多（多对一）：

例子：班级和学生，一个班级对应多个学生，一个学生只能对应一个班级。这种关系就是一对多（多对一）的关系。

在这里插入图片描述

处理：设置外键，多的一方关联到少的一方

8.3 多对多

例子：学生和课程，一个学生可以选择多门课，一门课可以被多个学生选择，这种关系就是多对多的关系。

在这里插入图片描述

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

2.范式

2.1概述

设计数据库时，需要遵循的一些规范。

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

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

2.2 分类：

**第一范式：**数据库表的每一列都是不可分割的原子数据项
**第二范式：**在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
**第三范式：**在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

简单来说，后一个范式都是在前一个范式的基础上添加了额外的要求，就好比我给你们布置作业：

第一范式：提交学习笔记.

第二范式：提交学习笔记，提交课堂演示代码

第三范式：提交学习笔记，提交课堂演示代码，提交课后练习代码

2.3范式详解

我们用表格来讲解：

在这里插入图片描述

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

在这里插入图片描述

2.第二范式：在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。
例如：学号 —>系名，系名—> 系主任
- 码：如果在一张表中，一个属性或一个属性组，被其他所有属性都完全依赖，则称这个属性（属性值）为该表的码。
例如：该表中码为：（学号，课程名称）。
- 主属性：码属性组中的所有属性
- 非主属性：除过码属性组的属性

3.数据库的备份和还原

该操作一般是由我们数据库管理员（DBA）来完成的。为什么要进行数据库的备份与还原，就是为了防止在数据库出现不正常状况时，出现数据丢失等问题，为了保证数据的安全性，我们每一天都要对数据库进行备份。

我们可以通过两种方式来备份数据库：

3.1方式1-命令行

备份：

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

在这里插入图片描述

还原：

1. 登录数据库 

2. 创建数据库 
3. 使用数据库 
4. 执行文件：source 备份文件路径

在这里插入图片描述

3.2方式2- 图形化界面

备份：

在这里插入图片描述

还原：

![在这里插入图片描述](https://img-blog.csdnimg.cn/20210507190748173.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9fdG8=,size_16,color_FFFFFF,t_70#pic_center)

选择刚才备份的sql脚本执行即可。

在这里插入图片描述

4.多表查询

USE test;
-- 创建部门表
CREATE TABLE dept (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
/*
解决插入数据乱码问题
*/
SHOW FULL COLUMNS FROM dept;-- 查看所有字段的编码集
ALTER TABLE dept CONVERT TO CHARACTER SET utf8; -- 将所有字段的编码集统一修改为:utf8
INSERT INTO dept (NAME) VALUES ('财务部'),('市场部'),('开发部');

-- 创建员工表
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL, -- 员工编号
	gender CHAR(1),-- 性别
	salary DOUBLE,-- 工资
	join_date DATE,-- 入职日期
	dept_id INT,-- 部门id
	CONSTRAINT deptid_dept_fk FOREIGN KEY (dept_id) REFERENCES dept(id)-- 外键，关联部门表id
);
INSERT INTO emp (NAME,gender,salary,join_date,dept_id) VALUES
('周杰伦','男',20000,'2015-5-28',2),
('林俊杰','男',16500,'2016-8-13',2),
('马化腾','男',25000,'2014-3-02',3),
('蔡依林','女',9800,'2016-12-1',1),
('马云','男',22000,'2014-5-8',3),
('埃隆马斯克','男',10000,'2016-1-17',2),
('蔡健雅','女',9000,'2016-7-1',1);


SELECT * FROM dept;
SELECT * FROM emp;

简单的多表查询：select * from emp,dept；

查询所得结果为笛卡尔积（有两个集合A，B，取A集合和 B集合中所有元素的组合数），共21条数据

在这里插入图片描述

通过观察结果，我们发现，多表查询中出现了许多无用的数据，比如周杰伦的部门信息，即出现了 2，还数显了 1和 3，这种情况很显然不是我们想要的。

4.1多表查询的分类

内连接查询

隐式内连接：使用where

-- 隐式内连接
-- 查询所有员工信息及对应的部门信息
SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

-- 查询员工表的名称，性别。部门表的名称
SELECT emp.`id`,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`

显式内连接：

语法：select 字段列表 from 表名1 [inner] join 表名2 on 加入的条件。

-- 显式内连接
SELECT 
	emp.`id`,emp.`name`,emp.`dept_id`,dept.`name` 
FROM 
    emp 
INNER JOIN 
	dept 
ON 
	emp.`dept_id` = dept.`id`;
####################################################################

SELECT 
	emp.`id`,emp.`name`,emp.`dept_id`,dept.`name` 
FROM 
    emp 
JOIN 
	dept 
ON 
	emp.`dept_id` = dept.`id`;

内连接查询注意事项
- 从哪些表中查询数据
- 连接条件是什么
- 查询哪些字段

外连接查询

左外连接：查询的是左表所有数据以及它其他表交集部分

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

-- 左外连接
-- 我们先插入一条数据，部门id为null
INSERT INTO emp (NAME,gender,salary) VALUES ('邓紫棋','女',8500);
-- 使用内连接先查看
SELECT
	t1.`name`,t1.`gender`,t2.`name`
FROM
	emp t1 , dept t2
WHERE
	t1.`dept_id` = t2.`id`;
-- 没有邓紫棋这名员工信息，因为内连接会将数据为null的情况排除
-- 接下来我们使用左外连接
SELECT
	t1.`id`,
	t1.`name`,
	t1.`gender`,
	t2.`id`,
	t2.`name`
FROM
	emp t1
LEFT OUTER JOIN
	dept t2
ON
	t1.`dept_id` = t2.`id`;

右外连接：查询的是右表所有数据以及它其他表交集部分

-- 接下来我们使用右外连接
SELECT
	t2.`id`,
	t2.`name`,
	t2.`gender`,
	t1.`id`,
	t1.`name`
FROM
	dept t1
RIGHT OUTER JOIN
	emp t2
ON
	t2.`dept_id` = t1.`id`;

左外连接和右外连接是一个相对概念，关键字左右的表位置调换一下效果是一样的。

子查询

**概述：**查询中嵌套查询，其中嵌套查询成为子查询。

-- 查询工资最高的员工信息
-- 1. 查询最高工资
SELECT MAX(emp.`salary`) FROM emp;
-- 2. 查询员工信息，其工资=最高工资
SELECT * FROM emp WHERE emp.`salary`=25000;
-- 子查询（嵌套查询）
SELECT * FROM emp WHERE emp.`salary` =(SELECT MAX(emp.`salary`) FROM emp);

分类：

子查询结果是单行单列的
- 子查询可以作为判断条件，与运算符一起使用。运算符 > , >= , < , <=

-- 查询工资低于平均工资的员工
SELECT * FROM emp WHERE emp.`salary` < (SELECT AVG(emp.`salary`) FROM emp);

子查询结果是多行单列的
- 查询可以作为判断条件，与运算符 in 一起使用

-- 2. 查询部门编号是2 或 3的员工信息
SELECT * FROM emp WHERE emp.`dept_id`=2 OR emp.`dept_id`=3;
-- 简化
SELECT * FROM emp WHERE emp.`dept_id` IN (2,3);
-- 子查询简化
SELECT * FROM emp WHERE emp.`dept_id` IN (SELECT id FROM dept WHERE NAME ='市场部' OR NAME ='开发部')

子查询结果是多行多列的
- 子查询可以作为一张虚拟表参与查询

-- 查询 入职日期在 2015-5-28 之后入职的员工信息和部门信息
-- 1. 先查询日期在2015-5-28 之后入职的员工信息
SELECT * FROM emp WHERE emp.`join_date`>'2015-05-28';-- 多行多列查出来的结果相当于一张表
-- 2. 子查询
SELECT * FROM (SELECT * FROM emp WHERE emp.`join_date`>'2015-05-28') t2,dept t1 WHERE t1.`id` = t2.dept_id;

5.事务

5.1事务基础介绍

概述：

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

在这里插入图片描述

根据上图，我们使用事务的三个操作

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

事务代码演示：

1.先准备一个表，插入两条数据

CREATE TABLE test(
	NAME VARCHAR(10),
	money DOUBLE(7,2)
);
INSERT INTO test (NAME,money) VALUES ('张三',1000),('李四',1000);

SELECT * FROM test;

2.我们用sql模拟一下转账过程（2，3步）

UPDATE test SET test.`money` = 1000 - 500 WHERE NAME='张三';
UPDATE test SET test.`money` = 1000 + 500 WHERE NAME='李四';

SELECT * FROM test;

模拟在第二步时出错

UPDATE test SET test.`money` = money - 500 WHERE NAME='张三';
出错了。。。
UPDATE test SET test.`money` = money + 500 WHERE NAME='李四';

SELECT * FROM test;

这样张三的钱被减了500，但是李四账户金额并没有加

为了解决这类问题，我们在业务开始前开启事务，首先先将数据还原回去

START TRANSACTION;-- 开启事务
UPDATE test SET test.`money` = money - 500 WHERE NAME='张三';
出错了。。。
UPDATE test SET test.`money` = money + 500 WHERE NAME='李四';

SELECT * FROM test;

	我们分别开启两个sqlyog进行该表的查询，一个数据是（500，1000） 一个是 （1000，1000）， (500,1000)这个数据是临时数据，当你把连接断开时，该临时数据会消失。

5. 我们 添加回滚

-- 还原数据
UPDATE test SET test.`money` = money WHERE NAME='张三';
UPDATE test SET test.`money` = money WHERE NAME='李四';
START TRANSACTION;-- 开启事务
UPDATE test SET test.`money` = money - 500 WHERE NAME='张三';
出错了。。。
UPDATE test SET test.`money` = money + 500 WHERE NAME='李四';
-- 有问题，数据回滚，回滚到开启事务时的状态
ROLLBACK;

添加回滚后数据又回到了开启时的状态，并且两个窗口查询结果都为（1000，1000）

去掉错误，添加事务提交

START TRANSACTION;-- 开启事务
UPDATE test SET test.`money` = 1000 - 500 WHERE NAME='张三';
-- 出错了。。。
UPDATE test SET test.`money` = 1000 + 500 WHERE NAME='李四';
-- 没有问题，提交事务
COMMIT;

	commit之前，一个窗口显示（500，1500），另一个窗口显示（1000，1000），是因为此时 （500，1500）还是临时数据，commit之后，两个窗口数据就统一了，因为此时会把这真正的数据进 行一个修改，这样就保证了数据的安全性。

注意事项：

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

事务提交方式：
1. 自动提交：
  - MySQL默认就是自动提交
  - 一条DML（增删改)语句会自动提交一次事务。
2. 手动提交
  - 需要先手动开启事务，再提交
3. 修改事务的默认提交方式：
  - 查看事务默认提交方式：

-- 查看事务默认提交方式
SELECT @@autocommit; -- 1:自动提交 0:手动提交

SET @@autocommit=0;-- 设置为手动提交方式

UPDATE test SET test.`money`=100; -- 修改完查看，因为有临时数据，所以效果是正常被修改了
SELECT * FROM test;-- 断开连接重新打开sqlyog，因为事务没有提交，所以数据又会恢复到之前的1000

5.2事务的四大特征（面试常考题）

原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
一致性：事务操作前后，事务要求所有的DML语句（insert、update、delete）操作的时候，必须保证同时成功或者同时失败。
隔离性：多个事务之间，相互独立。一个事务不能影响其他事务的运行
持久性：当事务提交或回滚后，数据库会持久化的保存数据。

事务的隔离级别

概述：

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

存在问题：

脏读：一个事务，读取到另一个数据没有提交的数据
不可重复度（虚读）：在同一个事务中，两次读取到的数据不一致
幻读：一个事务1操作（DML）数据表中的所有数据，另一个事务2此时插入数据，事务1查询数据发现与自己修改的数据不一致，就好像产生了幻觉一样。

隔离级别：

read uncommitted：读未提交
- 问题：脏读，不可重复读，幻读
read committed：读已提交（Oracle 默认）
- 问题：不可重复读，幻读
repeatable read: 可重复读（MySQL默认）
- 问题：幻读
serializable: 串行化
- 可以解决所有问题

注意：

隔离级别从1-5安全性越来越高，但是效率越来越低。我们在选择隔离级别时需要兼顾安全和效率。

查询隔离级别：

-- 查询隔离级别
SELECT @@tx_isolation;

设置隔离级别：（需重新连接，才可生效。）

-- 设置隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

问题演示：

read uncommitted：读未提交

窗口1：

-- 设置隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 查看隔离级别
SELECT @@tx_isolation;
USE test;
-- 开启事务（两窗口同时开启事务）
START TRANSACTION;
-- 更新完数据后，在窗口二种查看该表中的数据
UPDATE test SET test.`money` = test.`money`- 500 WHERE NAME='张三';
UPDATE test SET test.`money` = test.`money` + 500 WHERE NAME='李四';
ROLLBACK;

窗口2：

-- 查看隔离级别
SELECT @@tx_isolation;
USE test;
-- 开启事务
START TRANSACTION ;
-- 查询两次
-- 回滚前：500，1500 （脏读）
-- 回滚后：1000，1000 （不可重复读）
SELECT * FROM test;

read committed：读已提交

窗口1：

-- 2. read committed： 读已提交
-- 设置隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 查看隔离级别
SELECT @@tx_isolation;
USE test;
-- 开启事务（两窗口同时开启事务）
START TRANSACTION;
UPDATE test SET test.`money` = test.`money`- 500 WHERE NAME='张三';
UPDATE test SET test.`money` = test.`money` + 500 WHERE NAME='李四';
SELECT * FROM test;
COMMIT

窗口2：

-- 查看隔离级别
SELECT @@tx_isolation;
USE test;
-- 开启事务
START TRANSACTION ;
-- 查询两次
-- 提交前：1000，1000
-- 提交后：500，1500 不可重复读，因为两次结果不同
SELECT * FROM test;

repeatable read: 可重复读

窗口1：

-- 1.repeatable read: 可重复读
-- 设置隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 查看隔离级别
SELECT @@tx_isolation;
USE test;
-- 开启事务（两窗口同时开启事务）
START TRANSACTION;
UPDATE test SET test.`money` = test.`money`- 500 WHERE NAME='张三';
UPDATE test SET test.`money` = test.`money` + 500 WHERE NAME='李四';
SELECT * FROM test;
COMMIT;

窗口2：

-- 查看隔离级别
SELECT @@tx_isolation;
USE test;
-- 开启事务
START TRANSACTION ;
-- 查询三次
-- 提交前：1000，1000
-- 提交后：1000，1000 不可重复读，因为两次结果不同
commit;-- 本窗口再次提交，再次查询：500，1500 不可重复度问题解决。
SELECT * FROM test;

serializable: 串行化

该级别类似于java中的锁，一个事务1在操作表时，另一个事务2进行查询，该查询会被阻塞，直到事务1结束操作，事务2的查询动作才能继续向下进行。

6.数据库控制语言 - DCL

回顾：

DDL（Data Definition Language）:数据库定义语言

用来定义数据库对象：数据库，表，列表等。关键字create , drop , alter 等
DML（Data Manipulation Language）数据库操作语言

用来对数据库中表的数据进行增删改。关键字：insert , delete , updata等
DQL（Data Query Language）数据库查询语言

用来查询表中的记录。关键字：select , where 等
DCL（Data Control Language）数据库控制语言

用来定义数据库的访问权限和安全级别，及创建用户。关键字 GRANT，REVOKE

DCL的使用一般是公司DBA-数据库管理员使用的。

6.1管理用户

1.查询用户

-- 切换数据库
USE mysql;

-- 查询user表
SELECT * FROM USER;

2.创建用户

create user ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’；

-- 创建用户
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123456';

-- % 通配符 任意主机都可以登录
CREATE USER 'lisi'@'%' IDENTIFIED BY '123456';

3.删除用户

**DROP USER '用户名'@'主机名';**

-- 删除用户
DROP USER 'zhangsan'@'localhost';

4.修改密码

SET PASSWORD FOR ‘用户名’@‘主机’ = PASSWORD(‘新密码’);

-- 修改密码
SET PASSWORD FOR 'lisi'@'%'=PASSWORD('aaa');

6.2 权限管理

1.查询权限

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

-- 查看用户权限
SHOW GRANTS FOR 'lisi'@'%';
SHOW GRANTS FOR 'root'@'localhost';

2.授予权限

用户在没有赋权时，可以登录mysql，但是无法操作数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4RSRvUYq-1620385487515)(D:\图片\a3.png)]

GRANT 权限列表 ON 数据库.表名 to ‘用户名’@‘主机名’;

GRANT SELECT ON test.`test` TO 'lisi'@'%';-- 该操作只能查询，其他任何操作都会被拒绝
GRANT SELECT, DELETE,UPDATE ON test.`test` TO 'lisi'@'%';
-- 给zhangsan 用户赋予所有权限，在任意数据库和任意表上
GRANT ALL ON *.* TO 'zhangsan'@'%';

3.撤销权限

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

REVOKE ALL ON *.* FROM 'lisi'@'%';

zhao_to

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
对于MySQL数据库的加深学习

MySQl数据库1.多表之间的关系多表之间的关系其实也是我们数据库设计所要研究的重点。8.1 一对一：例子：人和身份证，一个人对应一个身份证，一个身份证对应一个人，无论从那个角度都成立。这种关系就是一对一的关系实现：一对一关系实现，可以在任意一方添加唯一外键8.2 一对多（多对一）：例子：班级和学生，一个班级对应多个学生，一个学生只能对应一个班级。这种关系就是一对多（多对一）的关系。处理：设置外键，多的一方关联到少的一方8.3 多对多例子：学生和课程，一个学生可以选择
复制链接

扫一扫

专栏目录