Day31 MySQL中级

最新推荐文章于 2023-06-28 18:18:21 发布

王小c

最新推荐文章于 2023-06-28 18:18:21 发布

阅读量93

点赞数

分类专栏：日记数据库

本文链接：https://blog.csdn.net/qq_31707969/article/details/104527557

版权

日记同时被 2 个专栏收录

131 篇文章 0 订阅

订阅专栏

数据库

9 篇文章 0 订阅

订阅专栏

数据库的设计

1. 多表之间的关系

1. 分类：
       1. 一对一(了解)：
           * 如：人和身份证
           * 分析：一个人只有一个身份证，一个身份证只能对应一个人
       2. 一对多(多对一)：
           * 如：部门和员工
           * 分析：一个部门有多个员工，一个员工只能对应一个部门
       3. 多对多：
           * 如：学生和课程
           * 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
   2. 实现关系：
       1. 一对多(多对一)：
           * 如：部门和员工
           * 实现方式：在多的一方建立外键，指向一的一方的主键。
       2. 多对多：
           * 如：学生和课程
           * 实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
       3. 一对一(了解)：
           * 如：人和身份证
           * 实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

2. 数据库设计的范式

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

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

分类：

       1. 第一范式（1NF）：每一列都是不可分割的原子数据项
       2. 第二范式（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. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
                   例如：该表中码为：（学号，课程名称）
                   * 主属性：码属性组中的所有属性
                   * 非主属性：除过码属性组的属性
       3. 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

数据库的备份和还原

1. 命令行：
   * 语法：
       * 备份： mysqldump -u用户名 -p密码数据库名称 > 保存的路径
       * 还原：
           1. 登录数据库
           2. 创建数据库
           3. 使用数据库
           4. 执行文件。source 文件路径
2. 图形化工具：SQLyog

数据库的三范式是什么？

概念：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求要遵循后边的范式要求，必须先遵循前边种范式呈递次规范，越高的范式数据库冗余越小。目前关系数据库有六种范式。
第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
第三范式：任何非主属性不依赖于其它非主属性。

不符合第一范式

符合第一范式，这是所有关系型数据库的最基本要求

符合第二范式

符合第三范式

事务

MySql事务

什么是事务？

一套操作叫事务，那么这些操作要么同时成功，要么同时失败。

为什么要有事务？

比如A向B转账是包括（A-500）和（B+500）两个操作，可以整合为一个事务，整体的管理，如果中间环节发生错误，事务管理功能可以让数据恢复成原来一样（A，B都没变）

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;

开启事务后，除非事务提交，一系列操作才奏效，中途出错的话，每个操作都不会执行的

事务的四大特征（ACID）？

Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
Consistency（一致性）：事务前后数据的完整性必须保持一致。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

数据库的事务隔离？
   * 概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
   * 存在问题：
       1. 脏读：一个事务，读取到另一个事务中没有提交的数据
       2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
       3. 幻读：在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。
   * 隔离级别：
       1. read uncommitted：读未提交
           * 产生的问题：脏读、不可重复读、幻读
       2. read committed：读已提交（Oracle）
           * 产生的问题：不可重复读、幻读
       3. repeatable read：可重复读（MySQL默认）
           * 产生的问题：幻读
       4. serializable：串行化
           * 可以解决所有的问题

       * 注意：隔离级别从小到大安全性越来越高，但是效率越来越低
       * 数据库查询隔离级别：
           * select @@tx_isolation;
       * 数据库设置隔离级别：
           * set global transaction isolation level 级别字符串;

   * 演示：
       set global transaction isolation level read uncommitted;
       start transaction;
       -- 转账操作
       update account set balance = balance - 500 where id = 1;
       update account set balance = balance + 500 where id = 2;

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：transaction-isolation = REPEATABLE-READ

可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

READ-UNCOMMITTED：未提交读，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。
READ-COMMITTED：提交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。
REPEATABLE-READ：可重复读，默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。
SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

脏读：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

不可重复读：是指在一个事务内，多次读同一数据。

幻读：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。