Day5: MySQL的事务和索引

一、事务(Transaction)

  • 事务:将一组SQL放在一个批次中去执行。
  • 事务的原则:ACID原则:原子性(atomicity),一致性(consistency),隔离性(isolation),持久性(durability)
    参考连接:ACID理解的博客
  • 事务的隔离级别:

(1)脏读: 指一个事务读取了另外一个事务未提交的数据。
(2) 不可重复读: 在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)。
(3) 虚读(幻读): 是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致(一般是行影响,多了一行)。

事务隔离的级别和应用场景的参考博客:事务隔离级别的博客

(MySQL是默认开启事务自动提交的,需要手动关闭)

1.1事务开启和关闭流程

在这里插入图片描述

  • step1:手动关闭自动提交;
-- MySQL是默认开启事务自动提交的,需要手动关闭
SET autocommit = 0  -- 关闭
SET autocommit = 1  -- 开启(默认的)
  • step2:开启事务,之后的语句都在一个事务内;
-- 事务开启
SET TRANSACTION  -- 标记一个事务的开始,从这个之后的sql 都在同一个事务内
  • step3:提交事务或回滚事务;
-- 提交: 持久化(成功)
COMMIT
-- 回滚: 回到原来的样子(失败)
ROLLBACK
  • step4:结束,手动开启自动提交;
-- 事务结束
SET autocommit = 1  -- 开启自动提交

1.2 用事务模拟银行转账的操作

-- 转账数据库
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_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=utf8

INSERT INTO `account` (`name`,`money`) VALUES ('A',2000.00),('B',10000.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;  -- 开启自动提交,标志事务的结束

1.3 保存点的设置(了解)

可以参考游戏中的存档点进行理解:设置,加载和撤销。

SAVEPOINT 保存点名称 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点  -- 撤销保存点

二、索引

MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构,提取句子主干,就可以得到索引的本质: 索引就是数据结构

2.1 索引的分类

  • 主键索引(PRIMARY KEY)
    – 唯一的标识,主键不可重复,只能有一个列作为主键。
  • 唯一索引(UNIQUE KEY)
    – 避免重复的字段出现,可以有多个唯一索引,多个字段都可以标识为唯一索引。
  • 常规索引(KEY / INDEX)
    – 默认的,KEY / INDEX 来设置。
  • 全文索引(FullText)
    – 在特定的数据库引擎下才有,快速定义数据。

2.2 索引的创建

  1. 可以在创建表的时候直接指定:
CREATE TABLE `student`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
    `loginpwd` VARCHAR(20) DEFAULT NULL,
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `sex` TINYINT(1) DEFAULT NULL COMMENT '性别,0或1',
    `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
    `phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空',
    `address` VARCHAR(255) NOT NULL COMMENT '地址,允许为空',
    `borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
    `email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
    `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    
    PRIMARY KEY (`studentno`),                  -- 指定主键索引:PRIMARY KEY (列名)
    UNIQUE KEY `identitycard`(`identitycard`),  -- 指定唯一索引:UNIQUE KEY 索引名(列名)
    KEY `email` (`email`)                       -- 指定普通索引:KEY 索引名(列名)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
  1. 可以通过create语句来进行创建索引:CREATE INDEX indexName ON table_name (column_name)

  2. 可以通过ALTER命令来添加和删除索引:

  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
  • ALTER TABLE tbl_name ADD INDEX index_name (column_list)添加普通索引,索引值可出现多次。
  • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)该语句指定了索引为 FULLTEXT ,用于全文索引。

添加索引的实例,也可以在ALTER语句中使用DROP子句来删除索引:

-- 增加一个全文索引 索引名 (列名)
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`); 
-- 删除索引
ALTER TABLE `student` DROP INDEX `studentname`;

2.3 索引的测试

创建一百万条数据来测试索引的:通过查询某一条数据可以看出,加上索引后的效果要更加快速。

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用户表'

SET GLOBAL log_bin_trust_function_creators=1; -- 开启创建函数功能

-- 插入100万条数据
DELIMITER $$  -- 写函数之前必写的
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	
	WHILE i<num DO
		-- 插入语句
		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)
		VALUES (CONCAT('用户',i),'2473743@qq.com','15811372982',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`='用户99999';
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户99999';

-- 创建索引
CREATE INDEX id_app_user_name ON `app_user`(`name`);
-- 通过索引查询
SELECT * FROM `app_user` WHERE `name`='用户99999';
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户99999';

2.4 索引的原则

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

三、权限管理和备份

3.1 SQL命令操作

用户表:mysql库下的user表,本质就是对这张表进行增删改查。

常见的操作如下:

-- 创建用户: CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER hurunqiao IDENTIFIED BY '123456'

-- 用户重命名: RENAME USER 原名 TO 新名字;
RENAME USER hurunqiao TO hurunqiao2;

-- 用户授权:授予所有库.表全部权限
GRANT ALL PRIVILEGES  ON *.* TO hurunqiao2;

-- 查看权限
SHOW GRANTS FOR hurunqiao2;
SHOW GRANTS FOR root@localhost;  -- 查看管理员权限

-- 撤销权限: 撤销全部权限
REVOKE ALL PRIVILEGES  ON *.* FROM hurunqiao2;

-- 删除用户
DROP USER hurunqiao2;

3.2 数据库备份

  1. 数据库备份的作用:
  • 保证重要的数据不丢失
  • 数据转移时需要
  1. MySQL数据库备份的方式:
  • 直接拷贝物理文件
  • 在SQLyog这种可视化工具中导出
  • 使用命令行导出 mysqldump
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表明 >位置
mysqldump -hlocalhost -uroot -p123456 school student >d:/a.sql

四、数据库设计规范

4.1 数据库三大范式

参考博客:三大范式通俗理解
1. 第一范式(1NF)

  • 原子性:保证每一列都不可再分。

2. 第二范式(2NF)
前提:满足第一范式。

  • 非码属性必须完全依赖于候选码(消除非主属性对主码的部分函数依赖)。
    (非主键列完全依赖于主键,而不能是主键的一部分)

3. 第三范式(3NF)
前提:满足第一范式和第二范式。

  • 属性不依赖于其他非主属性(消除依赖传递)
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值