《遇见狂神说》MySQL从入门到删库(二)

0.概述

======================================================================

1. MySQL基础笔记，复习回顾使用

2. 秦疆老师配套视频直达：MySQL最新教程通俗易懂

1、事务

======================================================================

1. 什么是事务？

一个事务就是一个完整的业务逻辑。

2. 什么是一个完整的业务逻辑？

• 假设转账，从A账户向B账户中转账10000

• 将A账户中的钱减去10000(update)

• 将B账户中的钱加上10000(update)

• 这就是一个完整的业务逻辑

以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，这两个update语句要求必须同时成功或同时失败，这样才能保证钱是正确的。

1.1、事务的ACID原则

---

1. 原则性(Atomic)

• 整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节

• 事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。

2. 一致性(Consist)

• 事务前后的数据完整性要保持一致

• 以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

3. 持久性(Durable)

• 事务一旦提交则不可逆，被持久化到数据库中！

4. 隔离性(Isolated)

• 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

隔离所导致的一些问题(了解)

脏读：指一个事务读取了另外一个事务未提交的数据

不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同

虚读：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致

1.2、事务实现

---

注意：

1. MySQL中事务默认是自动提交

2. 使用事务时应先关闭自动提交

3. 使用set语句来改变自动提交模式

SET autocommit = 0; /关闭/

SET autocommit = 1; /开启/

4. 开始一个事务，标记事务的起点，从这个之后的sql，都在一个事务内

start transaction

5. 提交一个事务给数据库(持久化成功)

commit

6. 回滚事务，将数据回到本次事务的初始状态

rollback

7. 还原MySQL数据库的自动提交

SET autocommit =1;

1.3、模拟事务

---

A在线买一款价格为500元商品，网上银行转账，A的银行卡余额为2000，然后给商家B支付500，商家B一开始的银行卡余额为10000

• – 创建数据库shop和创建表account并插入2条数据

– character set utf8 设置数据库编码为utf-8

– collate utf8_general_ci 设置数据库校对规则

CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;

USE shop;

– 创建表account

CREATE TABLE account (

id INT(11) NOT NULL AUTO_INCREMENT,

name VARCHAR(32) NOT NULL,

cash DECIMAL(9,2) NOT NULL,

PRIMARY KEY (id)

) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO account (name,cash) VALUES

(‘A’,2000.00),

(‘B’,10000.00)

– 转账实现

SET autocommit = 0; – 关闭自动提交

START TRANSACTION; – 开始一个事务,标记事务的起始点

UPDATE account SET cash=cash-500 WHERE name=‘A’;

UPDATE account SET cash=cash+500 WHERE name=‘B’;

COMMIT; – 提交事务,数据就被持久化了！

rollback;

SET autocommit = 1; – 恢复自动提交

2、索引

======================================================================

2.1、索引的分类

---

• 主键索引（Primary Key）

• 唯一的标识，主键不可重复，只能有一个列作为主键

• 唯一索引（Unique）

• 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引

• 常规索引（Index）

• 默认的

• 全文索引（FullText）

• 在特定的数据库引擎下才有

2.2、唯一索引

---

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别

• 主键索引只能有一个

• 唯一索引可能有多个

CREATE TABLE Grade(

GradeID INT(11) AUTO_INCREMENT PRIMARYKEY,

GradeName VARCHAR(32) NOT NULL UNIQUE

– 或 UNIQUE KEY GradeID (GradeID)

)

2.3、常规索引

---

作用 : 快速定位特定数据

注意：

• index 和 key 关键字都可以设置常规索引

• 应加在查询找条件的字段

• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

CREATE TABLE result(

– 省略一些代码

INDEX/KEY ind (studentNo,subjectNo) – 创建表时添加

)

– 创建后添加

ALTER TABLE result ADD INDEX ind(studentNo,subjectNo);

2.4、全文索引

---

百度搜索：全文索引

作用 : 快速定位特定数据

注意 :

• 只能用于CHAR , VARCHAR , TEXT数据列类型

• 适合大型数据集

2.5、索引准则

---

• 索引不是越多越好

• 不要对经常变动的数据加索引

• 小数据量的表建议不要加索引

• 索引一般应加在查找条件的字段

3、权限管理

========================================================================

3.1、SQLyog可视化操作

---

1. 添加新用户

2. 创建用户

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

3. 选择用户权限

4. 使用创建的新用户名与密码进行连接

5. 可以选择删除创建的用户

3.2、SQL基本命令操作

---

用户的信息表是：mysql.user

3.2.1、创建用户

语法：create user 用户名 inentified by '密码'

• 用户名需要加引号

• 密码也需要加引号，纯数字密码也要加引号

– 新创建一个用户名为’kuangshen’,密码为’123456’

CREATE USER kuangshen IDENTIFIED BY ‘123456’;

3.2.2、修改当前用户密码

格式：set password = password('密码')

SET PASSWORD = PASSWORD(‘111111’);

3.2.3、修改指定用户密码

格式：set password for 用户名 = password('密码')

– 修改kuangshen用户的密码为111111

SET PASSWORD FOR kuangshen = PASSWORD(‘111111’);

3.2.4、重命名用户名

格式：rename user 原来的名字 to 新的名字

RENAME USER kuangshen TO kuangshen2;

3.2.5、给用户授权

格式：grant 权限列表 on 表名 to 用户名

grant all privileges on . to kuangshen;

– all privileges 表示所有权限,除了不能给别人授权，拥有其余所有权限

– . 表示所有库的所有表

– 库名.表名 表示某库下面的某表

3.2.6、查看权限

格式：show grants for 用户名

– 查看管理员权限

show grants for root@localhost;

– 查看指定账户kuangshen的权限

show grants for kuangshen;

3.2.7、撤销权限

格式：revoke 哪些权限 on 表名 from 用户名

– 撤销kuangshen的全部权限

revoke all privileges on . from kuangshen;

3.2.8、删除用户

格式：drop user 用户名

– 删除用户kuangshen

drop user kuangshen;

4、MySQL备份方式

=============================================================================

1. 为什么要备份？

• 保证重要的数据不丢失

• 数据转移

4.1、物理拷贝导出

---

• 我们创建的数据库都在 mysql 目录下的 data 目录

• 直接拷贝data 目录下的数据库即可进行备份

4.2、SQLyog可视化操作导出

---

• 在SQLyog等这种可视化数据库管理软件中右键导出

4.3、命令行mysqldump导出

---

1. win + r 输入cmd

• 导出一张表

mysqldump -h主机 -u用户名 -p密码 数据库 表名 > 物理磁盘位置/文件名

mysqldump -hlocalhost -uroot -p123456 school student > D:/a.sql

• 导出多张表

mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名

mysqldump -hlocalhost -uroot -p123456 school student1 student2 > D:/b.sql

• 导出数据库

mysqldump -h主机 -u用户名 -p密码 数据库 > 物理磁盘位置/文件名

mysqldump -hlocalhost -uroot -p123456 school > D:/c.sql

4.4、导入

---

1. 导入之前最好先登录数据库

mysql -uroot -p123456;

2. 在登录的情况下,切换到指定的数据库

use school;

3. 导入表

source d:/a.sql;

5、三大范式

========================================================================

5.1、第一范式（1NF）

---

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

举例说明：

• 在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求，故不满足第一范式，调整如下：

5.2、第二范式（2NF）

---

第二范式（2NF）：在满足第一范式的前提下每张表只描述一件事情

举例说明：

• 在上表中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成

• 产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，这就不满足第二范式的要求，调整如下，需分成两个表

5.3、第三范式（3NF）

---

第三范式（3NF）：在满足第一和第二范式的前提下，第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

举例说明：

• 上表中，所有属性都完全依赖于学号，所以满足第二范式

• 但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，而不是主键“学号”，所以需做如下调整

5.4、三大范式问题

---

面试问数据库三大范式，该怎么回答才好？

1. 范式的作用

• 消除重复数据减少冗余数据，从而让数据库内的数据能划分的更合理，让磁盘空间得到更有效利用的一种标准化标准；

• 消除潜在的异常（插入异常，更新异常，删除异常）

2. 性能与规范

• 阿里规范：关联查询的表不得超过三张表
• 越符合三大范式，则表会越来越多，表多了不一定是好事，查询时需要连接多个表，增加查询复杂度，降低数据库的查询性能
• 因此，并不是应用的范式越高越好，要看实际情况而定
• 我个人观点认为，大多数情况应用到第三范式已经足够，在一定情况下第二范式或第一范式也是可以的。甚至有时为了提高运行效率，可以让数据冗余(比如数据表里存放了语文数学英语成绩，但是如果在某个时间经常要得到它的总分，每次都要进行计算会降低性能，不如加上总分这个冗余字段)