Mysql基础5-常用函数

5. MySql函数

5.1 常用函数

-- 数值
SELECT ABS(-666);  -- 绝对值
SELECT CEIL(5.12) -- 返回不小于参数的最小整数值   向上取整
SELECT CEILING(5.15)  -- 返回不小于参数的最小整数值 向上取整
SELECT FLOOR(5.45)  -- 向下取整
SELECT RAND() -- 返回一个0-1之间随机数
SELECT SIGN(2) -- 返回参数的符号 0-0 负数--1 正数-1
-- 字符串
SELECT CHAR_LENGTH('张三') -- 字符串长度  中、英文-字的个数
SELECT LENGTH('张三')-- 字符串长度  英文-字节数。中文-字节数   *2
SELECT CONCAT('a','b','c','...')  -- 拼接
SELECT INSERT('123456789',2,3,'abcd')  -- 从字符串某个位置（从1开始）开始用子字符串替换某个长度
SELECT LOWER('ASD');  -- 转小写
SELECT UPPER('asd');  -- 转大写
SELECT INSTR('abcdefg','d'); -- 返回第一次出现的子串索引1开始
SELECT REPLACE('abcdefg','ab','123'); -- 用指定字符串替换原字符串的特定字符
SELECT SUBSTR('abcdefg' ; 2 FOR 3)-- 返回指定子字符串（元字符串，开始位置，截取长度）
SELECT SUBSTRING('abcdefg', 2 , 3)  -- 返回指定子字符串（元字符串，开始位置，截取长度）
SELECT REVERSE('abcdefg')  -- 返回

-- 时间日期
SELECT CURRENT_DATE()  -- 返回当前日期
SELECT CURDATE()  -- 返回当前日期
SELECT NOW() -- 返回当前时间
SELECT LOCALTIME() -- 返回本地当前时间
SELECT SYSDATE() -- 系统时间
SELECT SYSTEM_USER() -- 当前用户
SELECT USER() -- 当前用户
SELECT YEAR(NOW()) -- 当前年
SELECT DAY(NOW()) -- 当前年
SELECT VERSION() -- 系统版本

5.2 聚合函数

count()

sum()

avg()

max()

min()

5.2.1 count

count(collum)只包括查的那一列，忽略null值；
count(*) 不会忽略null值，包含所有列，相当于行数
count(1) 不会忽略null值，忽略所有列，用1代表代码行；
collum为主键，快于count(1);不为主键，相反；
表有多列没主键，count(1) 快与 count(*) ；
表有主键，count(主键) 执行最快
表只有一个字段，count(*)最优
count(＊)默认为count0理论和1相同，本质计算行数

5.2.2 分组

SELECT subjectname,avg(studentresult),max(studentresult),min(studentresult)
FROM result r INNER JOIN subject sub
on r.subjectno = sub.subjectno
GROUP BY sub.subjectname

如果不分组按总体计算，合成一行
简单的说来，在此模式下，target list中的值要么是来自于聚合函数（sum、avg、max等）的结果，要么是来自于group by list中的表达式的值
MySQL提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝

SELECT subjectname,avg(studentresult) avgresult,max(studentresult),min(studentresult)
FROM result r INNER JOIN subject sub
on r.subjectno = sub.subjectno
GROUP BY sub.subjectname
HAVING avgresult>70

where 的条件不能使用聚合函数要在groupby 后的 having 中使用
平均分在group by之后才会计算，但where在group by之前起作用，where 平均分找不到值
having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数
having和where一样，位置不同

5.3 数据库级别的MD5加密

MD5：第5代加密算法。信息摘要算法，密码散列函数，产生128位（16字节）的散列值，确保信息传输完整一致。可以破解不适用于高度安全性的数据，用SHA-2,也不防止碰撞不适合安全性认证，如ssl公开密钥认证或数字签名的用途。
主要增强算法复杂度和不可逆性。
md5不可逆，具体值的md5是一样的
破解网站的本质：背后有个字典，md5加密后的值，加密前的值。暴力匹配。复杂度提升后就匹配不到

-- 加密
UPDATE testmd5 set pwd=MD5(`pwd`)
SELECT LENGTH('e10adc3949ba59abbe56e057f20f883e')
-- 录入数据时进行加密
INSERT INTO testmd5(`name`,`pwd`) VALUES('张8',MD5('123456'))
-- 登录验证
SELECT * FROM testmd5 WHERE pwd=MD5('123456')