聚合函式及分組與過濾（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')