MySQL知识汇总（第三篇）常用函数+聚合函数+GROUP BY分组+MD5加密

前言：部分内容学习自B站视频（https://www.bilibili.com/video/BV1NJ411J79W?p=20），
部分内容参考自《MySQL必知必会》
本文用于回顾MySQL知识点

一、MySQL函数

1.常用函数

数据函数

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

字符串函数

• 字符串包含的字符个数：SELECT CHAR_LENGTH(‘测试字符串’);
• 合并字符串：SELECT CONCAT(‘你’, ‘好’, ‘世’, ‘界’);
• 替换字符串，从某位置开始，替换某个长度：SELECT INSERT(‘我爱编程helloworld’,1,2,‘超级热爱’);
• 小写转换：SELECT LOWER(‘teSt’);
• 大写转换：SELECT UPPER('test);
• 从左边截取：SELECT LEFT(‘hello,world’, 5);
• 从右边截取：SELECT RIGHT(‘hello,world’, 5);
• 替换字符串：SELECT REPLACE(‘星期一’, ‘一’, ‘二’);
• 截取字符串，开始和长度：SELECT SUBSTR(‘hello,world’, 2, 3);
• 反转：SELECT REVERSE(‘test’);

日期和时间函数

• 获取当前时间：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 VERSION();
• 查看当前用户：SELECT USER();

2.聚合函数

• COUNT()：返回SELECT条件的记录的总行数
• SUM()：返回一列和
• AVG()
• MAX()
• MIN()

SELECT COUNT(studentName) FROM student;
SELECT COUNT(*) FROM;
SELECT COUNT(1) FROM;

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

• COUNT(字段名)：行数（忽略NULL）
• COUNT(*)：行数（包括NULL）
• COUNT(1)：行数（包括NULL）

很多人认为count(1)执行的效率会比count(*)高，原因是count(*)会存在全表扫描，而count(1)可以针对一个字段进行查询。其实不然，count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。而count(字段)则与前两者不同，它会统计该字段不为null的记录条数。

最佳适用：

• 在表没有主键时，count(1)快
• 有主键时，主键作为计算条件，count(主键)效率最高；
• 若表格只有一个字段，则count(*)效率较高。

3.GROUP BY分组

实例：

SELECT subjectName, AVG(studentResult) AS 平均分, MAX(studentResult) AS 最高分, MIN(studentResult) AS 最低分
FROM result AS r
INNER JOIN `subject` AS sub
ON r.subjectNo = sub.subjectNo
GROUP BY r.subjectNo
HAVING 平均分 > 80;

二、MD5加密

CREATE TABLE testmd5(
	id INT(4) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20) NOT NULL,
	pwd VARCHAR(50) NOT NULL,
	PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;


-- 明文密码
INSERT INTO testmd5 (`name`, pwd) VALUES ('a', '123456'), ('b', '123456'), ('c', '123456');

给pwd列所有数据用md5加密

-- 给pwd列所有数据用md5加密
UPDATE testmd5 SET pwd = MD5(pwd);

插入的同时，进行密码加密

INSERT INTO testmd5 (`name`, pwd) VALUES ('d', MD5('123'));

关于如何校验：将后端传递来的用户输入的密码，也进行md5加密，比对加密后的值即可

SELECT `name`, pwd FROM testmd5 WHERE `name` = '用户名' AND pwd = MD5('后端传来的密码')