MySQL学习笔记6：聚合函数与事务初识

1、前言

学习视频源自：【狂神说Java】MySQL最新教程通俗易懂

B站视频链接：https://www.bilibili.com/video/BV1NJ411J79W

【狂神说Java】MySQL最新教程通俗易懂

笔记为看视频自行撰写，仅限学习交流使用

1.1、上一篇文章

MySQL学习笔记5：分页、排序、子查询与常用函数

6、MySQL函数

官网：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

6.2、聚合函数

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

-- 聚合函数
-- 都能够统计表中的数据(想查询一个表中有多少个记录，就使用这个)
SELECT COUNT(studentname) FROM student --COUNT(字段)，会忽略所有的null值
SELECT COUNT(*) FROM student --COUNT(*)，不会忽略null值，本质 计算行数
SELECT COUNT(1) FROM student --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

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：根据不同的课程分组
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分
FROM result r
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
GROUP BY r.subjectno --通过什么字段来分组
HAVING 平均分 > 80

6.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')

SELECT完整的语法

7、事务

7.1、什么是事务

要么都成功，要么都失败

1、SQL执行 A给B转账 A 1000 -> 200 B 200

2、SQL执行 B收到A的钱 A 800 ->B 400

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

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

参考链接：https://blog.csdn.net/dengjili/article/details/82468576

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

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

隔离性（Isolation）

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

**持久性（Durability） **–事务提交

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

隔离所导致的一些问题

**脏读 **

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

不可重复读

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

幻读

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。（一般是行影响，多了一行）

执行事务

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 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; --恢复默认值