MySQL函数；事务

5、MySQL函数

官网：https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html

5.1、常用函数

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

-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串长度
SELECT CONCAT('真','想','吃') -- 拼接字符串
SELECT INSERT('我爱编程',1,2,'超级热爱') -- 从某个位置开始替换某个长度
SELECT LOWER('WangTengFei') -- 转小写
SELECT UPPER('WangTengFei') -- 转大写
SELECT INSTR('WangTengFei','e') -- 返回第一次出现的字符串的索引
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换指定出现的字符串
SELECT SUBSTR('123456789',4,5) -- 返回指定的字符串；(a,b)从第a个开始，返回b个字符串
SELECT REVERSE('坚持就能成功') -- 反转字符串

-- 查询姓 大 的同学，名字皱
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()
SELECT VERSION()

5.2、聚合函数（常用）

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

-- =============聚合函数=======================
-- 下面三个都能够统计表中的数据（药查询一个表中有多少条记录 就使用这个 count()）
SELECT COUNT(studentname) FROM student -- count(字段)，会忽略所有的NULL值
SELECT COUNT(*) FROM result     -- 不会忽略null值
SELECT COUNT(1) FROM `subject`  --  


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,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')
-- 加密
UPDATE `testmd5` SET `pwd` = MD5(pwd) WHERE id = 1
UPDATE `testmd5` SET `pwd` = MD5(pwd) WHERE id != 1
UPDATE `testmd5` SET `pwd` = MD5(pwd) -- 加密全部的密码

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

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

相同的密码第一次加密后的密码是相同的

Select 小结

Select：完整语法

SELECT[ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
	[left | right | inner join table_name2] -- 联合查询
	[WHERE ...]		-- 指定结果需满足的条件
	[GROUP BY ...]  -- 指定结果按照哪几个字段来分组
	[HAVING]		-- 过滤分组的记录必须满足的次要条件
	[ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
	[LIMIT {[offset,]rom_count | row_countOFFSET offset}];
	-- 指定查询的记录从哪条至哪条

顺序很重要，要按照上面的顺序

select 去重 要查询的字段 from 表 （注意：表和字段可以取别名）

XXXX join 要连接的表 on 等值判断

where （具体的值，子查询语句）

Group By （通过哪个子段来分组）

Having （过滤分组后的信息，条件和where是一样的，位置不同）

Order By （通过哪个子段排序 升序/降序）

limit (查询起始值，页面大小 )； limit （起始值，pagesize）

6、事务

6.1、什么是事务

要么都成功，要么都失败

---

就像是银行转账，比如 A 给 B 转账，要满足两个条件才能转账成功，

1. A的余额减少
2. B的余额增加

---

将一组SQL放到一个批次去执行

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

**参考博客链接：**https://blog.youkuaiyun.com/dengjili/article/details/82468576

原子性（Atomicity）：

要么都成功，要么都失败

原子性（Atomicity）

事务前后的数据完整性要保证一致。

持久性（Durability）—事务提交

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

隔离性（Isolation）

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

隔离所导致的一些问题

脏读：

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

不可重复度：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对

虚读（幻读）：

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

执行事务

MySQL 是默认开启事务自动提交的

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

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

-- 手动处理事务
SET autocommit = 0 -- 1.关闭自动提交
-- 事务开启
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'  -- B加500

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

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