Mysql进阶2

事务操作的SQL语句

• 开启事务： start transaction
• 执行一组SQL语句
• 成功：commit #提交事务
• 失败：rollback #回滚事务

事务的原理：

• 核心点：利用临时日志文件实现
  • 事务开启后，写入数据时先写入到临时日志文件中
  • commit时，从临时日志文件中同步到数据库

千万级数据脚本：

-- 1. 准备表
CREATE TABLE user(
    id INT,
    username VARCHAR(32),
    password VARCHAR(32),
    sex VARCHAR(6),
    email VARCHAR(50)
);

-- 2. 创建存储过程，实现批量插入记录
DELIMITER $$ 
-- 声明存储过程的结束符号为$$
-- 可以将下面的存储过程理解为java中的一个方法，插入千万条数据之后，在调用存储过程
CREATE PROCEDURE auto_insert()
BEGIN
DECLARE i INT DEFAULT 1;
START TRANSACTION; -- 开启事务
WHILE(i<=10000000)DO
INSERT INTO user VALUES(i,CONCAT('jack',i),MD5(i),'male',CONCAT('jack',i,'@itcast.cn'));
SET i=i+1;
END WHILE;
COMMIT; 
-- 提交
END$$ 
-- 声明结束
DELIMITER ; 
-- 重新声明分号为结束符号
-- 3. 查看存储过程
SHOW CREATE PROCEDURE auto_insert;
-- 4. 调用存储过程
CALL auto_insert();

字符串函数：

函数	描述	实例
CHAR_LENGTH(s)	返回字符串 s 的字符数	SELECT CHAR_LENGTH(‘itheima’) AS ‘长度’;
CONCAT(s1,s2,…,sn)	字符串 s1,s2 等多个字符串合并为一个字符串	SELECT CONCAT(‘I’,‘love’,‘you’);
LOWER(s)	将字符串 s 的所有字母变成小写字母	SELECT LOWER(‘ITHEIMA’);
UPPER(s)	将字符串转换为大写	SELECT UPPER(“itheima”);
SUBSTR(s, start,length)	从字符串 s 的 start 位置(从1开始)截取长度为 length 的子字符串	SELECT SUBSTR(“itheima”,1,2);
TRIM(s)	去掉字符串 s 开始和结尾处的空格	SELECT TRIM(’ itheima ')

字符串函数：

函数	描述	实例
CHAR_LENGTH(s)	返回字符串 s 的字符数	SELECT CHAR_LENGTH(‘itheima’) AS ‘长度’;
CONCAT(s1,s2,…,sn)	字符串 s1,s2 等多个字符串合并为一个字符串	SELECT CONCAT(‘I’,‘love’,‘you’);
LOWER(s)	将字符串 s 的所有字母变成小写字母	SELECT LOWER(‘ITHEIMA’);
UPPER(s)	将字符串转换为大写	SELECT UPPER(“itheima”);
SUBSTR(s, start,length)	从字符串 s 的 start 位置(从1开始)截取长度为 length 的子字符串	SELECT SUBSTR(“itheima”,1,2);
TRIM(s)	去掉字符串 s 开始和结尾处的空格	SELECT TRIM(’ itheima ')

数字函数：

函数	描述	实例
RAND()	返回 0 到 1 的随机数	SELECT RAND();
ROUND(小数 , 小数点后保留小数位数)	四舍五入保留几位小数	SELECT ROUND(3.1415926,2) ;
TRUNCATE(小数 , 小数点后保留小数位数)	不会四舍五入保留几位小数	SELECT TRUNCATE(3.1415926,3);
LEAST(expr1, expr2, expr3, …)	返回列表中的最小值	SELECT LEAST(13, 14, 521, 74, 1)
GREATEST(expr1, expr2,expr3, …)	返回列表中的最大值	SELECT GREATEST(13, 14, 521, 74, 1)

日期函数

函数名	描述	实例
NOW() 和 SYSDATE()	返回系统的当前日期和时间	SELECT NOW(); 或 SELECT SYSDATE();
CURDATE()	返回当前日期	SELECT CURDATE();
CURTIME()	返回当前系统时间	SELECT CURTIME();
YEAR(d)	返回d的中的年份	SELECT YEAR(NOW());
MONTH(d)	返回d的中的月份	SELECT MONTH(NOW());
DAY(d)	返回d中的日	SELECT DAY(NOW());

高级函数

函数名	描述	实例
CURRENT_USER()	返回当前用户	SELECT CURRENT_USER();
IFNULL(v1,v2)	如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。	SELECT IFNULL(null,‘Hello Word’)
ISNULL(expression)	判断表达式是否为 NULL,为空则为1,不为空则为0	SELECT ISNULL(NULL);

事务：指的是逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败

事务作用：保证在一个事务中多次操作数据库表中数据时，要么全都成功,要么全都失败。

MySQL中有两种事务提交的方式：

1. 手动提交事务：先开启，再提交；

2. 自动提交事务：即执行一条sql语句提交一次事务 (MySQL默认事务提交机制)

MySQL事务有关的SQL语句：

SQL****语句	描述
start transaction;	开启手动控制事务
commit;	提交事务
rollback;	回滚事务

事务的回滚：

在之前学习的事务操作中，如果回滚，直接回滚到事务开始执行之前

那如果，不希望回滚到事务执行前，而是希望回滚到某个特定位置

就需要设置事务的回滚点，回滚到事务执行中的某个位置

事务回滚点：

回滚点的操作语句	语法
设置回滚点	savepoint 回滚点名字
回到回滚点	rollback to 回滚点名字

数据库的事务具备ACID四大特性： （面试）

lAtomicity（原子性）

lConsistensy（一致性）

lIsolation（隔离性）

lDurability（持久性）

1、隔离性（Isolation）：多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互隔离 。

2、持久性(Durability)：指一个事务一旦被提交，它对数据库的改变将是永久性的，哪怕数据库发生异常，重启之后数据依然存在 。

3、原子性(Atomicity) ：原子性是指事务包装的一组sql(一组业务逻辑)是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生 。

4、一致性(Consistency)：一致性是指数据处于一种语义上有意义且正确的状态 （事务前后数据的完整性必须保持一致）。

事务一致性是指事务执行的结果必须是使数据从一个一致性状态变到另一个一致性状态。

事务的成功与失败，最终数据库的数据都是符合实际生活的业务逻辑。一致性绝大多数依赖业务逻辑和原子性。

事务的并发访问引发的三个问题：(面试)

并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中多次读取的数据内容不一致； 要求的是一个事务中多次读取时数据是不一致的，这是事务update时引发的问题
幻读（虚读）	一个事务内读取到了别的事务插入或者删除的数据，导致前后读取记录行数不同。 这是insert或delete时引发的问题

MySQL数据库规范规定了4种隔离级别： （用于解决上述出现的事务并发问题）

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle、SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

-- 查修隔离级别：
show variables like '%isolation%';
-- 或
select @@tx_isolation;

-- 设置事务隔离级别
set global transaction isolation level 隔离级别;
-- 如：
set golbal transaction isolation level read uncommitted;
-- 注意：客户端需要重新连接，刷新事务隔离级别

-- 查询累计插入和返回数据条数
show global status like 'Innodb_rows%';

MySQL索引分类：

主键索引（主键约束）
主键约束(唯一+非空)+提高查询效率

唯一索引
唯一约束+提高查询效率

普通索引
仅提高查询效率

组合索引（联合索引）
多个字段组成索引
[联合主键索引、联合唯一索引、联合普通索引]

全文索引 TEXT BIGTEXY
Mysql全文索引使用较少。基本针对文档类数据会选择solr、es等文档搜索类数据库

hash索引
根据key-value等值查询效率非常高，但是不适合范围查询

MySQL索引语法

-- 创建普通索引
create index 索引名 on 表名(字段);

-- 创建唯一索引
create unique index 索引名 on 表名(字段);
-- 创建普通组合索引
create index 索引名 on 表名(字段1,字段2,..);
-- 创建唯一组合索引
create unique index 索引名 on 表名(字段1,字段2,..);


-- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(字段);  --默认索引名：primary
-- 添加唯一索引（除了NULL外，NULL可能会出现多次）
alter table 表名 add unique(字段); -- 默认索引名：字段名
-- 添加普通索引，索引值可以出现多次。
alter table 表名 add index(字段); -- 默认索引名：字段名

-- 1) 查看索引
show index from 表名; 

-- 2) 删除索引
-- 方式1: 直接删除
drop index 索引名 on 表名;
-- 方式2: 修改表时删除
alter table 表名 drop index 索引名;