MySQL之MySQL函数

五、MySQL函数

MySQL 5.7官方参考手册：https://dev.mysql.com/doc/refman/5.7/en/
SQL函数和运算符操作：https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html

1.常用函数（不常用）

1）数学运算

SELECT ABS() --绝对值
SELECT CEILING() --向上取整
SELECT FLOOR() --向下取整
SELECT RAND() --返回一个0-1之间的随机数
SELECT SIGN() --判断一个数的符号：0返回0，负数返回-1，正数返回1

2）字符串函数

SELECT CHAR_LENGTH(str) --字符串长度
SELECT CONCAT(str,str,…) --拼接字符串
SELECT INSERT(str,初始位置,替换长度,替代的str) --从某个位置开始替换某个长度
SELECT LOWER(str) --转化成小写字母
SELECT UPPER(str) --转化成大写字母
SELECT INSTR(str,子str) --返回第一次出现的子字符串的索引
SELECT REPLACE(str,出现的str,替换的str) --替换出现的指定字符串
SELECT SUBSTR(str,截取位置，截取长度) --返回指定的子字符串
SELECT REVERSE(str) --反转字符串

例：查询姓周的同学，并将姓改成邹。

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

3）时间和日期函数

--获取当前日期的三种方式
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())

4）系统

--获取用户的两种方式
SELECT SYSTEM_USER() 
SELECT USER()
SELECT VERSION() --获取版本号

2.聚合函数（常用）

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

1）count()：使用其可查询一个表中有多少记录
例：查询student表中的所有记录

SELECT COUNT(`BornDate`) FROM student; --Count(字段)，会忽略此字段中所有的null值的
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;
-- Count(*)和Count(1)，不会忽略含有null值的，本质上都是计算行数

2）总和、平均、最大、最小四种函数
例：分别求学生成绩的总和、平均分、最高分和最低分。

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

补充：四、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 --指定结果通过什么字段来分组
HAVING 平均分>=80 --使用分组需指定结果满足的条件，用HAVING而不是WHERE

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

什么是MD5？
MD5信息摘要算法，一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值，用于确保信息传输完整一致。
MD5由MD4、MD3、MD2改进而来，主要增强算法复杂度和不可逆性。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','123456'),(2,'lisi','123456'),(1,'wangwu','123456')
--加密全部的密码（可以用WHERE只加密一部分）
UPDATE testmd5 SET pwd=MD5(pwd)
--在插入的时候就加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))
--如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

补充：四、8.Select小结

注：顺序很重要！
①select 去重 要查询的字段 from 表
注意：表和字段可以取别名。
②XXX join 要连接的表 on 等值判断
③where（具体的值，子查询语句）
④Group By（通过哪个字段来分组）
⑤Having（过滤分组后的信息，条件和where是一样的，位置不同）
⑥Order By…（通过哪个字段排序：升序\降序）
⑦Limit startindex,pagesize
业务层面：查询：跨表、跨数据库…