MySQL（五）MySQL常用函数、聚合函数、数据库级别的MD5加密、事务四大特性ACID、事务隔离级别（脏读、不可重复读、幻读）

五、MySQL函数

跳转官网
5.1、 常用函数

-- ==================== 常用函数 =============================

-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个 0~1 之间的随机数
SELECT SIGN(10) -- 判断一个数的符号 0-0 负数返回-1，正数返回 1

-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串长度
SELECT CONCAT('我','爱','你') -- 没有拼接符的拼接字符串
concat_ws(分隔符,str1,str2,...)   含有分隔符的拼接字符串
group_concat()       可以将同一组的不同行的字符拼接在同一行
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 查询，从某个位置开始替换某个长
度
SELECT LOWER('YaSE') -- 小写字母
SELECT UPPER('YaSE') -- 大写字母
SELECT INSTR('yase','a') -- 返回第一次出现的子串的索引
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('坚持就能成功',4,6) -- 返回指定的子字符串 (源字符串,截取的位置，截
取的长度)
SELECT REVERSE('有意思') -- 反转
length()     返回字符串长度
substring(str，index(1开始)，len)    截取字符串 
substr(str，index，len)、mid(str，index，len)   截取字符串 
left(str,len)   从左侧开始取指定个数的字符串

ord()   转换成ASCII码
rand()     返回0-1之间的随机浮点数
round(x)    返回参数x接近的整数
load_file( )     读取文件，并返回文件内容作为一个字符串
sleep( )      睡眠时间为指定的秒数
if(true,t,f)     if 判断
find_in_set()     返回字符串在字符串列表中的位置
benchmark( )     指定语句执行的次数
name_const( )     返回表作为结果
limit m,n     分页，m是索引，从m开始返回n行数据

-- 查询姓 周的同学，名字 邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'

-- 时间和日期函数
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前的时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER()   系统用户名
SELECT USER()   当前用户名
current_user()   当前用户名
SELECT VERSION() 数据库版本
@@datadir     数据库路径
@@versoin_compile_os     操作系统版本

5.2、聚合函数

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

-- =========== 聚合函数 ===============
-- 都能够统计 表中的数据 （ 想查询一个表中有多少个记录，就使用这个count() ）
SELECT COUNT(`BornDate`) FROM student; -- Count(字段)，会忽略所有的 null 值
SELECT COUNT(*) FROM student; -- Count（*），不会忽略 null 值， 本质 计算行数
SELECT COUNT(1) FROM result; -- Count（1），不会忽略忽略所有的 null 值 本质 计算
行数

SELECT SUM(`StudentResult`) AS 总和 FROM result
SELECT AVG(`StudentResult`) AS 平均分 FROM result
SELECT MAX(`StudentResult`) AS 最高分 FROM result
SELECT MIN(`StudentResult`) AS 最低分 FROM result

5.3、数据库级别的MD5加密（扩展）
什么是MD5(百度百科)？
主要增强算法复杂度和不可逆性。
MD5 不可逆，具体的值和 md5 是一样的
MD5 破解网站的原理，背后有一个字典， 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,'zhangsan','123456'),(2,'lisi','123456'),
(3,'wangwu','123456')

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1

UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))


-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

六、事务

要么都成功，要么都失败

---

1、SQL 执行 A 给 B 转账 A 1000 —>200 B 200
2、SQL 执行 B 收到 A 的钱 A 800 —> B 400

---

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

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

原子性（Atomicity）
要么都成功，要么都失败
一致性（Consistency）
事务前后的数据完整性要保证一致 ，没有被破坏
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）— 事务提交
事务一旦提交则不可逆，被持久化到数据库中！不会被回滚！

隔离所导致的一些问题（数据库事务隔离级别）

数据库事务的隔离级别有4种：

• Read uncommitted：读未提交（A事务做了一些操作但是还未提交，B事务可以读取到，一旦A事务回滚后再提交，就会产生脏读（B看到的是A未提交的数据，不作数））
• Read committed：读提交（A事务进行提交之后，B事务才可以读取数据，解决了脏读问题，但是会出现不可重复读（就是A事务在进行操作时，B事务对数据进行了修改））
• Repeatable read：重复读（就是在读取事务（事务开启）时，不再允许修改update操作），解决了不可重复读，但是解决不了幻读问题，幻读是插入insert操作
• Serializable：序列化，最高的事务隔离级别，事务串行化顺序执行，可以避免脏读、幻读、不可重复读。但是效率低、耗数据库性能，一般不使用。

脏读：
指一个事务读取了另外一个事务未提交的数据。
例子：今天发工资，工资本来1800，老板错误输入了2000，这时候我去查工资发现给我涨工资了，结果老板在提交前发现不对劲，进行了回滚，然后输入1800进行提交，我白高兴了一场。
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。
例子：我拿着工资卡准备去取现金，插入卡读取到余额1800，我准备提出来时我new出来的对象买了一个包把钱给花完了，然后我点击提现系统会再次查询（买包事务完成之后再进行查询事务避免脏读）发现余额为0
虚读(幻读):
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
例子：比如我消费了200元，然后对象检查了我的消费记录（查询事务开启），看到确实花了200，这时候我又去买了一包烟花了50，新增了一条消费记录（insert）并提交，当对象打印消费清单的时候发现花了250元，像tm做梦一样出现了幻觉，就是幻读。

执行事务

-- ==================== 事务 =========================

-- mysql 是默认开启事务自动提交的
SET autocommit = 0 /* 关闭 */
SET autocommit = 1 /* 开启（默认的） */

-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交

-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的 sql 都在同一个事务内


INSERT xx
INSERT xx

-- 提交： 持久化 （成功！）
COMMIT
-- 回滚： 回到的原来的样子 （失败！）
ROLLBACK

-- 事务结束
SET autocommit = 1 -- 开启自动提交

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

模拟场景

-- 转账
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' -- A加500


COMMIT; -- 提交事务，就被持久化了！
ROLLBACK; -- 回滚

SET autocommit = 1; -- 恢复默认值