MySQL学习31-45

数据库级别的MD5加密

• MD5由MD4、MD3、MD2改进而来，主要增强算法复杂度和不可逆性
• MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密前的值

CREATE TABLE `testMD5`(
	`id` INT(4) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET =utf8;

-- 明文密码
INSERT INTO `testMD5` VALUES(1,'zsr','200024'),
(2,'gcc','000421'),(3,'bareth','123456');

-- 加密
UPDATE `testMD5` SET `pwd`=MD5(pwd) WHE RE id=1;
UPDATE `testMD5` SET `pwd`=MD5(pwd); -- 加密全部的密码

-- 插入的时候加密
INSERT INTO `testMD5` VALUES(4,'barry',MD5('654321'));

-- 如何校验:将用户传递进来的密码，进行MD5加密，然后对比加密后的值
SELECT * FROM `testMD5` WHERE `name`='barry' AND `pwd`=MD5('654321');

事务

什么是事务：要么都成功，要么都失败。例如：转账

——-——

1.SQL 执行 A 给 B 转账

2.SQL 执行 B 收到 A 的钱

——-——

将一组 SQL 放在一个批次中执行

事务原则：ACID 原则 原子性，一致性，隔离性，持久性（脏读，幻读……）

• 原子性（Atomicity）：要么都成功要么都失败
• 一致性（Consistency）：事务前后的数据完整性保持一致
• 持久性（Durablility）: 事务一旦提交则不可逆，会持久化到数据库中
• 隔离性（lsolation）：用户并发访问时，不能被其他事务的操作数据所干扰
• 脏读：一个事务读取了另一个未完成事务未提交的数据
• 不可重复读：多次读取结果不同（不一定时错误，场合不对）
• 虚读：在一个事务读取到了别的事务插入 d 数，导致数据不一致

事务执行

-- mysql 是默认开启事务自动提交的
SET autocommit =0 /*关闭*/
SET autocommit =1/*开启（默认的）*/
-- 手动处理事务
 
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
INSERT xx 
INSERT xx
-- 提交 ，持久化（）成功
COMMIT
-- 回滚：回到原来的样子（失败！）
ROLLBACK
-- 事务结束
SET autocommit=1 -- 开启自动提交
 
-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点

事务执行模拟

-- 转账
-- 创建数据库
CREATE DATABASE shop CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci
USE shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb3
 
INSERT INTO ACCOUNT(`name`,`money`)
VALUES ('A',2000.00),('B',1000.00)
-- 模拟转账：事务
SET autocommit =0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务
UPDATE `account` SET money=money-500 WHERE `name`='A' -- A减500
UPDATE `account` SET money=money+500 WHERE `name`='B'-- B加500
COMMIT; -- 提交事务
ROLLBACK;-- 回滚
SET autocommit=1; -- 恢复默认值

---

索引

索引是帮助 MySQL 高效获取数据的数据结构

提取句子主干，就可以得到索引的本质：索引是数据结构

索引的分类

索引类型不填默认未常规索引

主键索引（PRIMARY KEY）

唯一的标识，主键不可重复，只有一个列作为主键（如 id）

唯一索引（UNIQUE KEY）

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

常规索引 (KEY/INDEX)

默认的，index，key 关键字设置

全文索引 (FullText)

快速定位数据

-- 1.创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引
 
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加索引   索引名（列名）  (在school下的student加入名为studentname的全文索引)
ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentname`(`studentname`);
-- EXPLAIN分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 常规索引（非全文索引）
 
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('赵');-- 只查一条数据

测试索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
 
 
-- 百万数据插入
DROP FUNCTION IF EXISTS mock_data; -- 写函数之前必须要写，标志:$$
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT -- 注意returns，否则报错。
DETERMINISTIC -- 8.0版本需要多这么一行
BEGIN
        DECLARE num INT DEFAULT 1000000; -- num 作为截止数字，定义为百万，
        DECLARE i INT DEFAULT 0;
        WHILE i < num DO
         INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)VALUES(CONCAT('用户', i),'965499224@qq.com', CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
        SET i = i + 1;
        END WHILE;
        RETURN i;
END;
SELECT mock_data();
 
SELECT *FROM app_user WHERE `name`='用户9999'; -- 近一秒
EXPLAIN SELECT *FROM app_user WHERE `name`='用户9999'
SELECT * FROM student
 
-- id_表名 _字段名
-- create index 索引名 on 表（字段）
-- 创建索引后查询速度增加明显
CREATE INDEX id_app_user_name ON app_user(`name`)

索引原则

• 索引不是越多越好
• 经常变动的进程不要加索引
• 小数据量的表不需要加索引
• 索引一般夹在常用来查询的字段上

索引的数据结构
hash 类型的索引

Btree:innoDB 的默认数据结构

---

权限管理

用户管理

SQL命令操作
用户表：mysql

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY 密码
CREATE USER fazhu IDENTIFIED BY '123456'
 
-- 修改密码 （修改当前用户密码）
SET fassword =PASSWORD('123456')
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR kuangshen =PASSWORD('123456')
 
-- 重命名
RENAME USER kuangshen TO kuangshen2
 
-- 用户授权 ALL PRIVILEGE 除了给权限其他都能干
GRANT ALL PRIVILEGES ON *.* TO kuangshen2
 
-- 查询权限
SHOW GRANTS FOR kuangshen2 -- 查看指定用户权限
SHOW GRANTS FOR root@localhost
 
-- 撤销权限 revoke  哪些权限，在哪个库，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2
 
-- 删除用户
DROP USER kuangshen2

MySQL 数据库备份

备份作用

保证重要数据不丢失

数据转移

MySQL 数据备份方法

直接拷贝物理文件

在 sqlyog 这种可视化工具中手动导出（右键）

设置按默认即可

使用命令行导出 mysqlddump 命令行使用

# mysq1dump -h 主机 -u用户名 ―p 密码数据库﹑表名>物理磁盘位置/文件名
mysq1dump -hlocalhost -uroot -p123456 school student >D :/a.sq1
#mysq1dump-h主机-u用户名-p密码数据库、表名>物理磁盘位置/文件名
mysq1dump-hlocalhost-uroot-p 123456学生>D：/a.sq1
 
# mysq1dump -h 主机 -u用户名 -p 密码数据库表1表2表3>物理磁盘位置/文件名
mysq7dump -h1oca1host -uroot -p123456 schoo1 student >D:/a.sq1
#mysq1dump-h主机-u用户名-p密码数据库表1表2表3>物理磁盘位置/文件名
mysq7dump-h1ka1host-uroot-p 123456学校1学生>D：/a.sq1
 
#导入，在登录的情况下切换到指定的数据库
source 备份文件
mysql -u用户 -p密码 库名< 备份文件