5、MySQL函数

5.1、常用函数

 -- ---------- 常用函数 ---------------
 -- 数学运算
 SELECT ABS(-2) -- 绝对值
 SELECT CEILING(2.5) -- 向上取整
 SELECT FLOOR(2.5) -- 向下取整
 SELECT RAND() -- 返回一个0-1之间的随机数 
 SELECT SIGN(10) -- 判断一个数的符号，负数返回-1，正数返回1
 SELECT SIGN(-10)
 
 -- 字符串函数
 SELECT CHAR_LENGTH('又是美好的一天') -- 字符串长度
 SELECT CONCAT('天','气','预报')
 SELECT INSERT('你好，今天你吃饭了吗',4,2,'今天天气不错, ') -- 从指定索引开始替换指定长度
 SELECT LOWER('abcdEFG') -- 转小写
 SELECT UPPER('ABCDefg') -- 转大写
 SELECT INSTR('abcd,efg',',e') -- 返回第一次出现子串的索引
 SELECT SUBSTR('你好，今天你吃饭了吗',6,5) -- 返回指定字符串（原字符串，截取索引，截取长度）
 SELECT REPLACE('你好，今天你吃饭了吗','今天','111昨天111') -- 替换指定字符串
 SELECT REVERSE('我想在你而') -- 逆转
 
 SELECT REPLACE(`studentname`,'小','大') FROM student
 WHERE `studentname` LIKE '小%'
 
 -- 时间和日期函数（重要）
 SELECT CURRENT_TIME() -- 当前时间
 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()
 SELECT COUNT(`borndate`) FROM student -- count(字段) 会忽略所有的null值
 SELECT COUNT(1) FROM student -- 不会忽略null值，本质是计算行数
 SELECT COUNT(*) FROM student -- 不会忽略null值，本质是计算行数
 
 SELECT SUM(`studentresult`) AS 总和 FROM score
 SELECT AVG(`studentresult`) AS 平均分 FROM score
 SELECT MAX(`studentresult`) AS 最高分 FROM score
 SELECT MIN(`studentresult`) AS 最低分 FROM score

5.3、数据库MD5加密

• 什么是MD5？

• 一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。MD5由MD4、MD3、MD2改进而来，主要增强算法复杂度和不可逆性。MD5算法因其普遍、稳定、快速的特点，仍广泛应用于普通数据的加密保护领域

• 原理可简要的叙述为：MD5码以512位分组来处理输入的信息，且每一分组又被划分为16个32位子分组，经过了一系列的处理后，算法的输出由四个32位分组组成，将这四个32位分组级联后将生成一个128位散列值。

  -- ----------- 测试MD5加密 ------------
 CREATE TABLE `testmd5`(
 `id` INT(4) NOT NULL,
 `name` VARCHAR(20) NOT NULL,
 `pwd` VARCHAR(30) 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)
 
 -- 插入数据时加密
 INSERT INTO testmd5 VALUES(4, '小明', MD5('123456'))
 
 -- 如何查询？将传递进来的 数据（密码） 进行MD5加密，然后比较加密后的数据
 SELECT * FROM testmd5 WHERE `name`='小明' AND pwd=MD5(123456)

6、事物

6.1、初识事物？

所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

要么都成功，要么都失败

• 1、SQL执行 A给B 转账  A 800-200 B 200

• 2、SQL执行 B收到A的转账  A 600 B 200+400

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

事物原则：ACID原则 原子性、一致性、隔离性、持久性 （脏读、虚读）

原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

要么都成功，要么都失败

一致性（Consistency）事务前后数据的完整性必须保持一致。（比如转账中两者钱的总额）隔离性（Isolation）事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。事物一旦被提交则不可逆，被持久化到数据库中

隔离导致问题

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

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

虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。（一般是行影响）

6.2 、操作事物

执行事物

 -- -------------- 事物 -----------------
 -- mysql默认开启事物自动提交
 
 SET autocommit = 0  -- 关闭
 SET autocommit = 1  -- 开启（默认是开启的）
 
 -- 手动处理事物 TRANSACTION
 SET autocommit = 0  -- 关闭自动提交
 
 START TRANSACTION -- 标记一个事物的开始，从这条语句后的SQL都属于同一个事物
 
 INSERT xxx
 INSERT xxx
 
 -- 提交：持久化
 COMMIT
 -- 回滚：回到提交之前
 ROLLBACK
 -- 事物结束
 SET autocommit = 1  -- 开启自动提交（默认是开启的）
 
 SAVEPOINT 保存点名 -- 设置一个事物的保存点
 ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
 RELEASE SAVEPOINT 保存点名 -- 撤销保存点

模拟转账

 -- ------- 模拟转账事物
 SET autocommit = 0 -- 关闭自动提交
 START TRANSACTION -- 开启事物(一组事物)
 
 UPDATE `account` SET money=money-200 WHERE `name`='A' -- A-200
 UPDATE `account` SET money=money+200 WHERE `name`='B' -- B+200
 
 COMMIT -- 提交事物，提交后就被持久化了
 ROLLBACK -- 回滚（恢复）
 
 SET autocommit = 0 -- 恢复默认值