MySQL函数

1.常用函数

-- 常用函数

-- 数学运算
SELECT ABS(-8)  -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回0-1之间的随机数
SELECT SIGN(-5) -- 判断一个数的符号 1 0 -1

-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串长度
SELECT CONCAT('你','们','好') -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 查询，从某个位置替换长度
SELECT LOWER('NiHao') -- 小写字母
SELECT UPPER('NiHao') -- 大写字母
SELECT INSTR('nihao','a') -- 返回第一次出现的子串的索引
SELECT REPLACE('坚持就是力量','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('坚持就是力量',3,4) -- 返回中的子字符串(源字符串)
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()

2.聚合函数

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

-- 聚合函数
-- 都能够统计表中的数据
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


-- 查询不同课程的平均分，最高分和最低分
-- 根据不同的课程分组
SELECT SubjectName,AVG(StudentResult),MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.SubjectNo -- 通过设么字字段分组
HAVING AVG(StudentResult)>80 

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

什么是MD5？
主要增强算法复杂度和不可逆性
MD5不可逆，具体的值得md5是一样的
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','1234566'),(2,'lisi','1234566'),(3,'wangwu','1234566'),(4,'zhaoliu','1234566')

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1 -- 加密第一行密码
UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码

-- 插入的时候加密
INSERT INTO testmd5 VALUES(5,'xiaominig',MD5('123456'))

-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd = MD5('123456')