五、MySQL函數(shù)

MySQL 5.7官方參考手冊(cè)：https://dev.mysql.com/doc/refman/5.7/en/
SQL函數(shù)和運(yùn)算符操作：https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html

1.常用函數(shù)（不常用）

1）數(shù)學(xué)運(yùn)算

SELECT ABS() --絕對(duì)值
SELECT CEILING() --向上取整
SELECT FLOOR() --向下取整
SELECT RAND() --返回一個(gè)0-1之間的隨機(jī)數(shù)
SELECT SIGN() --判斷一個(gè)數(shù)的符號(hào)：0返回0，負(fù)數(shù)返回-1，正數(shù)返回1

2）字符串函數(shù)

SELECT CHAR_LENGTH(str) --字符串長度
SELECT CONCAT(str,str,…) --拼接字符串
SELECT INSERT(str,初始位置,替換長度,替代的str) --從某個(gè)位置開始替換某個(gè)長度
SELECT LOWER(str) --轉(zhuǎn)化成小寫字母
SELECT UPPER(str) --轉(zhuǎn)化成大寫字母
SELECT INSTR(str,子str) --返回第一次出現(xiàn)的子字符串的索引
SELECT REPLACE(str,出現(xiàn)的str,替換的str) --替換出現(xiàn)的指定字符串
SELECT SUBSTR(str,截取位置，截取長度) --返回指定的子字符串
SELECT REVERSE(str) --反轉(zhuǎn)字符串

例：查詢姓周的同學(xué)，并將姓改成鄒。

SELECT REPLACE(StudentName,'周','鄒') FROM student
WHERE StudentName LIKE '周%'

3）時(shí)間和日期函數(shù)

--獲取當(dāng)前日期的三種方式
SELECT CURRENT_DATE()
SELECT CURDATE()
SELECT NOW()
SELECT LOCALTIME() --獲取本地時(shí)間
SELECT SYSDATE() --獲取系統(tǒng)時(shí)間
--分別獲取當(dāng)前日期的年、月、日、時(shí)、分、秒
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

4）系統(tǒng)

--獲取用戶的兩種方式
SELECT SYSTEM_USER() 
SELECT USER()
SELECT VERSION() --獲取版本號(hào)

2.聚合函數(shù)（常用）

1）count()：使用其可查詢一個(gè)表中有多少記錄
例：查詢student表中的所有記錄

SELECT COUNT(`BornDate`) FROM student; --Count(字段)，會(huì)忽略此字段中所有的null值的
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;
-- Count(*)和Count(1)，不會(huì)忽略含有null值的，本質(zhì)上都是計(jì)算行數(shù)

2）總和、平均、最大、最小四種函數(shù)
例：分別求學(xué)生成績的總和、平均分、最高分和最低分。

SELECT SUM(`StudentResult`) AS 總和 FROM result
SELECT AVG(`StudentResult`) AS 平均分 FROM result
SELECT MAX(`StudentResult`) AS 最高分 FROM result
SELECT MIN(`StudentResult`) AS 最低分 FROM result

補(bǔ)充：四、7.分組和過濾

例：查詢不同課程的平均分、最高分、最低分，平均分要大于80分。

SELECT SubjectName,AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 最高分,
MIN(StudentResult) AS 最低分
FORM result r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.SubjectNo --指定結(jié)果通過什么字段來分組
HAVING 平均分>=80 --使用分組需指定結(jié)果滿足的條件，用HAVING而不是WHERE

3.數(shù)據(jù)庫級(jí)別的MD5加密（擴(kuò)展）

什么是MD5？
MD5信息摘要算法，一種被廣泛使用的密碼散列函數(shù)，可以產(chǎn)生出一個(gè)128位（16字節(jié)）的散列值，用于確保信息傳輸完整一致。
MD5由MD4、MD3、MD2改進(jìn)而來，主要增強(qiáng)算法復(fù)雜度和不可逆性。MD5算法因其普遍、穩(wěn)定、快速的特點(diǎn)，仍廣泛應(yīng)用于普通數(shù)據(jù)的加密保護(hù)領(lǐng)域。
具體的值在MD5中是一樣的。
MD5破解網(wǎng)站的原理，背后有一個(gè)字典，查詢MD5加密后的值，查詢到了就返回加密前的值。
例：創(chuàng)建測試表，測試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'),(1,'wangwu','123456')
--加密全部的密碼（可以用WHERE只加密一部分）
UPDATE testmd5 SET pwd=MD5(pwd)
--在插入的時(shí)候就加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))
--如何校驗(yàn)：將用戶傳遞進(jìn)來的密碼，進(jìn)行md5加密，然后比對(duì)加密后的值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

補(bǔ)充：四、8.Select小結(jié)

注：順序很重要！
①select 去重 要查詢的字段 from 表
注意：表和字段可以取別名。
②XXX join 要連接的表 on 等值判斷
③where（具體的值，子查詢語句）
④Group By（通過哪個(gè)字段來分組）
⑤Having（過濾分組后的信息，條件和where是一樣的，位置不同）
⑥Order By…（通過哪個(gè)字段排序：升序\降序）
⑦Limit startindex,pagesize
業(yè)務(wù)層面：查詢：跨表、跨數(shù)據(jù)庫…