Day20-函数和事务

最新推荐文章于 2024-05-21 22:44:12 发布

玄月拾忆

最新推荐文章于 2024-05-21 22:44:12 发布

阅读量152

点赞数 1

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/ghy0911/article/details/126065922

版权

Day20

函数

[参考手册](MySQL ：： MySQL 5.7 参考手册)

常用函数

数学运算

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

字符串函数

-- 字符串函数
-- 计算字符串长度
SELECT CHAR_LENGTH('小赵最强') -- 4
-- 拼接字符串
SELECT CONCAT('小赵','最','强') -- 小赵最强
-- 替换，从某个位置开始+替换多少长度+替换的字符串
-- 如：最是第3个字符，替换最强2个字，
SELECT INSERT('小赵最强',3,2,'宇宙最强') -- 小赵宇宙最强
-- 全部小写
SELECT LOWER('XIAOZHAO') -- xiaozhao
-- 全部大写
SELECT UPPER('xiaozhao')  -- XIAOZHAO
-- 返回第一次出现的子串的索引
SELECT INSTR('XIAOZHAO','H') -- 6
-- 替换出现的指定字符串
SELECT REPLACE('小赵最强','最强','宇宙最强') -- 小赵宇宙最强
-- 返回指定的子字符串（源字符串，截取的位置，截取的长度）
SELECT SUBSTR('小赵最强',3,2) -- 最强
-- 反转
SELECT REVERSE('强最赵小') -- 小赵最强

使用

-- 查询姓张的同学
SELECT * FROM `student`
WHERE `studentname` LIKE '张%'
-- 将姓张德同学改名姓赵
SELECT REPLACE(`studentname`,'张','赵') FROM `student`
WHERE `studentname` LIKE '张%'

在这里插入图片描述

时间和日期函数

-- 时间和日期函数（记住）
-- 截取当前日期
SELECT CURRENT_DATE() -- 2022-07-29
-- 获取当前日期
SELECT CURDATE() -- 2022-07-29
-- 获取当前的时间
SELECT NOW() -- 2022-07-29 19:00:01
-- 获取本地时间
SELECT LOCALTIME() -- 2022-07-29 19:00:02
-- 获取系统时间
SELECT SYSDATE(); -- 2022-07-29 19:00:05

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()

聚合函数（常用）

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

COUNT()

-- 想查询一个表中有多少个记录，就使用这个count()
-- COUNT(指定列,字段)，会忽略所有的null值
SELECT COUNT(`studentno`) FROM `student`
-- COUNT(*)，不会忽略null值，本质计算行数
SELECT COUNT(*) FROM `student`
-- COUNT(1)，忽略所有的null值，本质计算行数
SELECT COUNT(1) FROM `student`

其他

-- 求成绩总和
SELECT SUM(`studentresult`) AS 成绩总和 FROM `result`
-- 求成绩平均分
SELECT AVG(`studentresult`) AS 成绩平均分 FROM `result`
-- 求成绩最高分
SELECT MAX(`studentresult`) AS 成绩最高分 FROM `result`
-- 求成绩最低分
SELECT MIN(`studentresult`) AS 成绩最低分 FROM `result`

GROUP BY

-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同的课程分组）
SELECT `subjectname` AS '课程',
AVG(`studentresult`) AS '平均分',
MAX(`studentresult`) AS '最高分',
MIN(`studentresult`) AS '最低分'
FROM result AS r
INNER JOIN `subject` sb
ON r.`subjectno`=sb.`subjectno`
GROUP BY r.`subjectno`

在这里插入图片描述

分组和过滤

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

在这里插入图片描述

数据库级别的MD5加密

什么MD5？

主要增加算法复杂度和不可逆性。
MD5不可逆，具体的值的md5是一样的
MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密前的值

-- 测试MD5数据库
CREATE TABLE `test`(
    `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 test 
VALUES(1,'小赵',123456),
(2,'中赵',123456),
(3,'大赵',123456)

在这里插入图片描述

加密

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

在这里插入图片描述

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

在这里插入图片描述

如何校验：将用户传递进来的密码，进行MD5加密，然后比对加密后的值

SELECT * FROM `testmd5` WHERE `name`='小赵' AND pwd = MD5('123456');

select小结

在这里插入图片描述

事务

什么是事务

ACID：参考

要么都成功，要么都失败

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

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

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

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

持久性（Durability）—事务提交

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

隔离性（Isolation）

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

隔离导致的问题

脏读

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

不可重复读

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

虚读（幻读）

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

执行事务

事务

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

流程

-- 手动处理事务
-- 关闭自动提交
SET autocommit = 0
-- 事务开启
-- 标记一个事务的开始，从这个之后的SQL都在同一个事务内

INSERT xx
INSERT xx
-- 提交：持久化（成功）
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK
-- 事务结束
-- 开启自动提交
SET autocommit = 1

了解

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

在这里插入图片描述

模拟场景

-- 模拟转账
-- 关闭自动提交
SET autocommit = 0
START TRANSACTION -- 开启事务
-- A转账五事转给B
UPDATE `account` SET money =money-50 WHERE `name` = 'A'
-- B收到A转账的五十
UPDATE `account` SET money =money+50 WHERE `name` = 'B'

COMMIT;-- 提交事务
ROLLBACK;-- 回滚

-- 开启自动提交
SET autocommit = 1