SQLyog基本操作（七）-MySQL常用函数、聚合函数、MD5加密
5 MySQL函数
官网：查看MySQL技术文档：
-
5.7版本：MySQL :: MySQL 5.7 Reference Manual :: 12.1 Built-In Function and Operator Reference https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html
-
8.0版本：MySQL :: MySQL 8.0 Reference Manual :: 12.1 Built-In Function and Operator Reference https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html
-- 数学函数
SELECT ABS(-1) -- 绝对值
SELECT CEILING(1.1) -- 向上取整
SELECT FLOOR(1.1) -- 向下取整
SELECT RAND() -- 产生0~1之间的随机小数
SELECT SIGN(0) -- 判断一个数的符号：0返回0
SELECT SIGN(10) -- 判断一个数的符号：正数返回1
SELECT SIGN(-10) -- 判断一个数的符号：负数返回-1
-- 字符串函数
SELECT CHAR_LENGTH('字符串函数') -- 获取字符串长度
SELECT CONCAT('字符串','函','数','拼接') -- 进行字符串拼接
SELECT INSERT('hello world!',6,4,'，你好!') -- 插入，从某个位置开始插入某个长度的字符串，索引下标位置从1开始
SELECT LOWER('Hello') -- 全部转小写字母
SELECT UPPER('Hello') -- 全部转大写字母
SELECT INSTR('Hello','l') -- 返回第一次出现的该子串的索引下标，索引下标位置从1开始
SELECT REPLACE('*1*2*3*','*','加油') -- 替换出现的指定字符串内容，前面为待替换的内容，后面为要替换的内容
SELECT SUBSTR('12345654321',4) -- 从某个位置开始截取内容直到末尾，索引下标位置从1开始
SELECT SUBSTR('12345654321',4,5) -- 从某个位置开始截取指定长度的子串内容，索引下标位置从1开始
SELECT REVERSE('你是猪') -- 反转字符串
-- 从student表中查询姓名为郭%的学生的全部信息
SELECT * FROM `student` WHERE `StudentName` LIKE '郭%'
-- 从student表中查找姓名为郭%的学生，将其姓氏“郭”替换为“李”
SELECT REPLACE(`StudentName`,'郭','李') FROM `student`
WHERE `StudentName` LIKE '郭%'
-- 查询时间和日期函数
SELECT CURRENT_DATE -- 获取当前日期 2021-06-30
SELECT CURDATE() -- 同上
SELECT NOW() -- 获取当前时间 2021-06-30 16：16:16
SELECT LOCALTIME() -- 获取本地时间 2021-06-30 16:16:16
SELECT LOCALTIME -- 同上
SELECT SYSDATE() -- 获取系统时间 2021-06-30 16:16:16
SELECT YEAR(NOW()) -- 获取当前年份 2021
SELECT MONTH(NOW()) -- 获取当前月份 6
SELECT DAY(NOW()) -- 获取当前天数 30
SELECT HOUR(NOW()) -- 获取当前小时 16
SELECT MINUTE(NOW()) -- 获取当前分钟 16
SELECT SECOND(NOW()) -- 获取当前秒钟 16
-- 查询系统
SELECT SYSTEM_USER() -- 获取系统作者 root@localhost
SELECT USER() -- 同上
SELECT VERSION() -- 获取版本号 5.7.19
5.2 聚合函数（常用）
-- 统计表中的数据（想查询一个表中有多少个记录，就使用这个COUNT()）
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
5.3 数据库级别的MD5加密
MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。MD5由MD4、MD3、MD2改进而来，主要增强算法复杂度和不可逆性。MD5算法因其普遍、稳定、快速的特点，仍广泛应用于普通数据的加密保护领域。
-- 创建testmd5表
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','111111'),
(2,'lisi','222222'),
(3,'wangwu','333333')
-- 对id=1的密码进行加密
UPDATE `testmd5` SET `pwd`=MD5(`pwd`) WHERE `id`=1
-- 对全部密码进行加密
UPDATE `testmd5` SET `pwd`=MD5(`pwd`)
-- 应该在插入数据的时候对密码进行加密，MD5()本身是个函数
INSERT INTO `testmd5` VALUES(4,'zhaoliu',MD5('444444'))
-- 如何校验：将用户传递进来的密码，进行MD5加密，然后对比加密后的值
SELECT * FROM `testmd5` WHERE `name`='zhaoliu' AND `pwd`=MD5('444444')
