5.MySQL函数

MySQL函数

常用函数

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

-- 字符串函数
SELECT CHAR_LENGTH('小筠喜欢小宇') -- 字符串长度
SELECT CONCAT('小筠','喜欢','小宇')  -- 拼接字符串
SELECT INSERT('小筠喜欢小宇',3,2,'超级喜欢')  -- 插入替换，字符串从1开始
-- 从某个位置开始插入替换某个长度

SELECT LOWER('XIAOyun') -- 全部转换为小写
SELECT UPPER('XIAoyu') -- 全部转换为大写
SELECT INSTR('xiaoyun','y') -- 返回第一次出现的字串的索引，从1开始
SELECT REPLACE('小筠喜欢小宇','喜欢','超级喜欢')  -- 替换出现的指定字符串
SELECT SUBSTR('小筠超级喜欢小宇吧',1,8)  -- 返回指定的子字符串(源字符串，截取的位置，截取的长度)
SELECT REVERSE('你是年少的欢喜')  -- 反转字符串

-- 查询姓张的同学 ，名字 冯
SELECT REPLACE(`userName`,'张','冯') FROM `student`
WHERE `userName` LIKE '张%'

-- 时间和日期函数（记住）
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()
SELECT VERSION()

聚合函数（常用）

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

-- =====================聚合函数===================
-- 都能统计表中的数据
SELECT COUNT(`grade`) FROM `student`  -- COUNT(字段)，会忽略所有的NULL
SELECT COUNT(*) FROM `student`  -- COUNT（*），不会忽略NULL，本质 计算行数
SELECT COUNT(1) FROM `course`  -- COUNT（1），不会忽略所有的NULL 本质 计算行数


SELECT SUM(`mark`) AS '总和' FROM `selectedcourse`
SELECT AVG(`mark`) AS `avg` FROM `selectedcourse`

分组和过滤

-- 查询不同的课程的平均分，最高分 最低分
-- 核心：（根据不同的课程分组）
SELECT `courseName`,AVG(`mark`) AS 平均分,MAX(`mark`),MIN(`mark`)
FROM `course` c
INNER JOIN `selectedcourse` s
ON s.`courseID`=c.`courseID`
-- where 后面不能跟group by（分组函数）
GROUP BY s.`courseID` -- 通过什么字段来分组
HAVING 平均分>=80  -- 过滤条件

数据库级别的md5加密（扩展）

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,'xiaoyun','123456'),(2,'xiaoyu','123456'),(3,'xiaoxiao','123456')
 -- 加密
 UPDATE `testmd5` SET pwd=MD5(pwd) WHERE id=1

UPDATE `testmd5` SET pwd=MD5(pwd) -- 加密所有项

-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4,'xiaoxiaoyun',MD5('520'))

-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='xiaoxiaoyun' AND pwd=MD5('520')