MySQL函数

MySQL函数

常用函数

-- ========= 常用函数 ============
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个 [0,1) 之间的随机数
SELECT SIGN(15) -- 判断一个数的符号，0-->0; 负数-->-1;正数-->1

-- 字符串函数
SELECT CHAR_LENGTH('人无法同时拥有青春和对青春的感受') -- 字符串长度
SELECT CONCAT('我觉得','是','这样的') -- 字符串拼接
-- 插入或替换字符串 INSERT(str,要插在第几个位置，覆盖后面几位（0的话就是纯插入），要插入的新字符串)
SELECT INSERT('青春的',4,0,'感受')
SELECT LOWER('abcABC') -- 大写转小写
SELECT UPPER('abcABC') -- 大写转小写
-- 替换指定字符串
SELECT REPLACE('人能够同时拥有青春和对青春的感受','能够','无法');
-- 截取返回指定的字符串（字符串，从哪里开始截取，截取的长度）
SELECT SUBSTRING('关关难过关关过' FROM 5 FOR 3)
SELECT REVERSE('年少的欢喜是你') -- 翻转字符串

-- 查询姓赵的同学把赵换成邹
SELECT REPLACE(studentname,'赵','邹') FROM student
WHERE studentname LIKE '赵%'

-- 时间和日期函数（重要）
SELECT CURRENT_DATE() -- 获取当前日期
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() -- 获取MySQL版本

聚合函数（常用）

官方文档：MySQL ：： MySQL 8.0 参考手册 ：： 12.1 内置函数和运算符参考

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

-- ======== 聚合函数 ==========
SELECT COUNT(studentname) FROM student; -- COUNT(字段) 查询有多少个学生，会忽略所有的 null 值
-- COUNT(*)和COUNT(*)本质都是计算行数
SELECT COUNT(*) FROM student; -- COUNT(*) 计算行数，不会忽略 null 值
SELECT COUNT(*) 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

三种COUNT都能统计表中的数据，区别如下：

1. count(*) 和 count(1)基本没有差别
2. count(*)包括了所有的列，相当于行数，在统计结果的时候， 不会忽略列为 NULL 的值
3. count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
4. count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数， 即某个字段值为NULL时，不统计。

执行效率上：

1. 列名为主键，count(列名)会比count(1)快
2. 列名不为主键，count(1)会比count(列名)快
3. 如果表多个列并且没有主键，则 count(1) 的执行效率优于count(*)
4. 如果有主键，则 select count(主键) 的执行效率是最优的
5. 如果表只有一个字段，则 select count(*)最优。

数据库级别的MD5加密（拓展）

JS中使用过MD5，数据库中也有MD5，比如user表中的密码就是加密过的。

MD5由MD4、MD3、MD2改进而来，主要增强算法复杂度和不可逆性。MD5算法因其普遍、稳定、快速的特点，仍广泛应用于普通数据的加密保护领域。

MD5加密不可逆，但两个相同的MD5对应的值是一样的。比如说，所有的123456经过一次加密后的值肯定都是一样的。

一些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 -- 只加密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')