5.MySQL函数（常用函数、聚合函数、MD5加密）

1.常用函数

文档
1.常用函数

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

-- ==============字符串函数================
-- 返回字符串的长度
SELECT CHAR_LENGTH('即使再小的帆也能远航')		-- 10
-- 拼接字符串
SELECT CONCAT('i','u')												-- iu
-- 从某个位置开始替换某个长度
SELECT INSERT('wddo',1,2,'hh')		-- hhdo
-- 转小写
SELECT LOWER('HelloWorld')				-- helloworld
--  转大写
SELECT UPPER('HelloWorld')				-- HELLOWORLD
-- 返回第一次出现子串的位置
SELECT INSTR('hello','h')					-- 1
-- 替换出现的指定字符串
SELECT REPLACE('hellowoold','oo','or')				-- helloworld
-- 返回指定字符串的子字符串（源字符串，截取的位置，截取的长度(不写则表示截取到最后))
SELECT SUBSTR('乘风破浪会有时',1,4)						-- 乘风破浪
-- 反转
SELECT REVERSE('直挂云帆济沧海')	-- 海沧济帆云挂直

-- 查询姓 张的同学，名字 周
SELECT REPLACE(studentname,'张','周') FROM student
WHERE studentname LIKE '张%'

-- ==============时间和日期函数==============
-- 获取当前日期
SELECT CURRENT_DATE()
SELECT CURDATE()
-- 获取当前时间
SELECT NOW()					-- 2021-12-31 10:18:41
-- 本地时间
SELECT LOCALTIME()		-- 2021-12-31 10:19:29
-- 获取系统时间
SELECT SYSDATE()			-- 2021-12-31 10:19:59
-- 分别获取当前的年，月，日，时，分，秒
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
-- 系统
SELECT SYSTEM_USER()		-- root@localhost
SELECT USER()						-- root@localhost
SELECT VERSION()				-- 5.5.57

2.聚合函数（常用）

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

-- ==============聚合函数================
-- COUNT(指定列(字段))，会忽略所有的null值
SELECT COUNT(studentname) FROM student		-- 4
-- COUNT(*)，不会忽略null值
SELECT COUNT(*) FROM student							-- 4
-- COUNT(1)，不会忽略所有的null值
SELECT COUNT(1) FROM student							-- 4

SELECT SUM(studentresult) AS 总和 FROM result
SELECT AVG(studentresult) AS 平均分 FROM result
SELECT MAX(studentresult) AS 最高分 FROM result
SELECT MIN(studentresult) AS 最低分 FROM result

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

count(1)和count(字段)的区别：
count(1)会统计表中的所有记录数量，包括字段为null的记录
count(字段)会统计该字段在表中间出现的次数，忽略字段为null的记录
count(*)、count(1)和count(字段)的区别：
1.执行结果上：
count(*)：包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为null的数据
count(1)：包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为null的记录
count(列名)：只包含列名那一列，在统计结果的时候，如果某个字段值为NULL,不统计
2.执行效率上：
列名为主键：count(列名)会比count(1)快
列名不为主键：count(1)会比count(列名)快
如果表多个列并且没有主键：count(1)的执行效率优于count(*)
如果有主键：select count(主键)的执行效率最优
如果表只有一个字段：select count(*)最优

3.数据库级别的MD5加密

什么是MD5：

MD5信息摘要算法（Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。
主要增强算法复杂度和不可逆性
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','123455'),(3,'wangwu','123345')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1
-- 全部的密码都加密:此时id为1的密码被加密了两次
UPDATE testmd5 SET pwd=MD5(pwd)	
SELECT * FROM testmd5

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123455'))
-- 如何校验:将用户传递进来的密码进行md5加密，然后比对加密后的值
SELECT * from testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123455')