MySQL 常用函数
官方文档：https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html
常用函数
其实并不常用
数学运算
取绝对值：abs()
SELECT ABS(-8) -- 值为8向上取整：ceiling()
SELECT CEILING(9.4) -- 值为10向下取整 ：floor()
SELECT FLOOR(9.4) -- 值为9获取随机数：rand()
SELECT RAND() -- 返回一个0-1之间的随机数判断一个数的符号：sign()
SELECT SIGN(10)-- 值为1，返回一个数的符号，负数返回-1，正数返回1字符串函数
取字符串长度：char_length()
SELECT CHAR_LENGTH('我在学习MySQL数据库！') -- 返回值13拼接字符串：concat()
SELECT CONCAT('我','爱','你') -- 返回'我爱你'查询替换：insert(字符串,开始的位置,某个长度,替换的内容)
-- 替换'爱'为'超级热爱'，替换第2个字符开始，共1个字符长度
SELECT INSERT('我爱MySQL',2,1,'超级热爱') -- 返回'我超级热爱MySQL'
-- 替换'MySQL'为'数据库'，替换第3个字符开始，共5个字符长度
SELECT INSERT('我爱MySQL',3,5,'数据库') -- 返回'我爱数据库'字符转大写/小写：upper() 、lower()
SELECT UPPER('AbC') -- 返回'ABC'
SELECT LOWER('AbC') -- 返回'abc'字符串索引： instr(字符串,关键词字符串)
SELECT INSTR('我爱MySQL！','M') -- 返回'3'，第一次出现的字符串的索引
SELECT INSTR('我爱MySQL！','！') -- 返回'8'，第一次出现的字符串的索引替换字符：replace(字符串，关键词，替换内容)
select replace('我爱MySQL','MySQL','数据库') --返回'我爱数据库' 替换所有关键词字符串截取字符串：substr(字符串,起始位置,截取长度)
SELECT SUBSTR('我爱MySQL',3,5) -- 返回'MySQL'
-- 有4种用法，通过文档查询即可反转字符串：reverse()
select reverse('球拍') -- 返回'拍球'函数无需记忆，需要用的时候，会查询文档正确使用达到预期效果即可
时间和日期函数
获取当前日期：current_date() ... 有很多
SELECT CURRENT_DATE() -- 返回 2021-2-7
SELECT CURDATE() -- 返回 2021-2-7
SELECT NOW() -- 返回 2021-2-7 11:12:13
SELECT LOCALTIME() -- 返回 2021-2-7 11:12:13
SELECT SYSDATE() -- 返回 2021-2-7 11:12:13
SELECT YEAR(NOW()) -- 返回 2021
SELECT MONTH(NOW()) -- 返回 2
SELECT DAY(NOW()) -- 返回 7
SELECT HOUR(NOW()) -- 返回 11
SELECT MINUTE(NOW()) -- 返回 12
SELECT SECOND(NOW()) -- 返回 13系统函数
SELECT SYSTEM_USER() -- 返回 'root@localhost'
SELECT USER() -- 返回 'root@localhost'
SELECT VERSION() -- 返回 '5.7.32'聚合函数
常用！
函数名称 | 描述 |
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
…… | …… |
计数函数：count()
SELECT COUNT(`StudentName`) FROM `Student` -- 返回20
SELECT COUNT(*) FROM `Student` -- 返回20
SELECT COUNT(1) FROM `Student` -- 返回20结果：
区别：
count(列字段):会忽略NULL值，若该列为主键，则效率最高。
count(*):统计所有的列，不会忽略NULL
count(1):统计所有的行，不会忽略NULL，当统计非主键字段时，比count(列字段)效率更高
综合案例
分组：group by
分组后的条件：having，不能用where
查询不同课程的平均分，最高分，最低分。根据不同的课程分组
SELECT `SubjectName` AS '课程名称',AVG(`StudentScore`) AS '平均分',MAX(`StudentScore`) AS '最高分',MIN(`StudentScore`) AS '最低分'
FROM `Score` AS `sc`
INNER JOIN `Subject` AS `su`
ON sc.`SubjectNo`=su.`SubjectNo`
GROUP BY sc.`SubjectNo` -- 通过SubjectNo字段来分组结果：
查询平均分超过60分的课程
SELECT `SubjectName` AS '课程名称',AVG(`StudentScore`) AS '平均分',MAX(`StudentScore`) AS '最高分',MIN(`StudentScore`) AS '最低分'
FROM `Score` AS `sc`
INNER JOIN `Subject` AS `su`
ON sc.`SubjectNo`=su.`SubjectNo`
GROUP BY sc.`SubjectNo` -- 通过SubjectNo字段来分组
HAVING `平均分`>=60 --平均分及格的分组结果：
数据库级的加密：MD5()
特点：不可逆
具体值的MD5是一样的。
MD网站原理，有一个MD5字典，保存常见密码的MD5（使用穷举法暴力）
--创建测试表
CREATE TABLE `MD5Test`(
`id` INT(4) NOT NULL COMMENT '账号',
`password` VARCHAR(50) NOT NULL COMMENT '密码',
`name` VARCHAR(10) NOT NULL COMMENT '姓名',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 创建数据
INSERT INTO `md5test` VALUES(1,'123456','张三'),(2,'123456','李四'),(3,'123456','王五')
-- 加密id为1的密码
UPDATE md5test SET `password`=MD5(`password`) WHERE id=1
-- 插入时加密结果：
校验：所有涉及操作均使用md5()，即可保证结果一致。
-- 加密id为1的密码
INSERT INTO `md5test` VALUES(4,MD5('123456'),'王二麻子')
-- 读取
SELECT `name` , `password` FROM `md5test` WHERE `password`=MD5('123456')结果：
以上是本次函数学习整理及相关案例。
