聚合函数及分组与过滤（GROUP BY … HAVING）

MySQL参考手册官网：

https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html

常用函数

1. 数学运算

   --数学运算
   SELECT ABS(-2)  -- 绝对值
   SELECT CEILING(5.2)  -- 向上取整数
   SELECT FLOOR(5.2)  -- 向下取整数
   SELECT RAND()  -- 返回一个 0~1之间的随机数
   
   -- 字符串函数
   SELECT CHAR_LENGTH('MySQL') -- 字符串长度
   SELECT CONCAT('My','S','QL') -- 拼接字符串
   SELECT INSERT('MySQL',1,3,'WWW')  -- 查询，从一个位置开始替换某个长度（1,3表示从第一个字符开始，替换3个字符）
   SELECT INSTR('MySQL','S')  -- 返回第一次出现的字符串的索引
   SELECT LOWER('MySQL')  -- 全转为小写字母
   SELECT UPPER('MySQL')  -- 全转为大写字母
   SELECT REPLACE('MySQL','My','www')  -- 替换出现的指定字符串
   SELECT SUBSTR('MySQL',2,3)  -- 返回指定的字符串   代表从一个位置开始，截取的长度（2,3代表从第2个字符开始，截取3个字符）
   SELECT REVERSE('MySQL')  -- 反转
   
   -- 时间和日期函数
   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()  -- 用户 （简写）
   
   
   

2. 聚合函数（常用）

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

-- 聚合函数
SELECT COUNT(BornDate) FROM student  -- COUNT(字段)，此格式会忽略所有的null值
SELECT COUNT(*) FROM student;  -- 不会忽略null值
SELECT COUNT(1) FROM student  -- 不会忽略null值

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

分组与过滤

GROUP BY … HAVING

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
SELECT `subjectname`,AVG(studentresult) AS 平均分,MIN(studentresult) AS 最低分,MAX(studentresult) AS 最高分
FROM result r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
GROUP BY r.subjectno  -- 通过哪个字段来分组
HAVING 平均分 > 80

MD5加密

-- 未加密
INSERT INTO testmd5 VALUES (1,'zhang','123456'),(2,'liu','123456'),(3,'ma','123456')

-- 加密
UPDATE testmd5 SET pwd = MD5(pwd) WHERE id = 1  -- 只为id为1的加密
UPDATE testmd5 SET pwd = MD5(pwd)  -- 全部加密 

-- 插入时加入
INSERT INTO testmd5 VALUES (5,'zhang',MD5('123456'))

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