MySQL学习笔记（五）------常用函数

Select完整语法

select [all | distinct]
{* | table.* }
from 表名 [as 别名]
	[left | right | inner join 表名]   --联合查询
	[where ...]   --指定结果需满足条件
	[group by ...]   --指定结果按照哪几个字段来分组
	[having]   --过滤分组的记录必须满足的次要条件
	[order by ...]   --指定查询记录按哪一个或多个条件排序
	[limit 开始位置,页面容量]   --指定查询记录从哪条到哪条

常用函数（不常用）

数学运算

• 绝对值

select abs(-8)

• 向上取整

select ceiling(9.4) //10

• 向下取整

select floor(9.4)  //9

• 返回0~1之间的随机数（常用）

select rand()

• 判断一个数的符号 0返回0 正数返回1 负数返回-1

select sign(10)

字符串函数

• 返回字符串长度

select char_length('zzz')  //3 

• 合并字符串

select concat('a','b','cd')  //abcd

• 查询，替换

select insert('Java是最好的语言',1,4,'Python')   -- 1:某个位置 4：长度

• 转小写

select lower('ZZZ')  //zzz

• 转大写

select upper('zzz')   //ZZZ

• 返回第一次出现的子串的索引

select instr('zhang','a')   //3

• 替换出现的字符串

select replace('好好学习，天天向上','好好','快快')  //快快学习，天天向上

• 返回指定字符串（‘原字符串’，开始截取的位置，截取的长度）

select substr('好好学习，天天向上',4,5)   //习，天天向

• 反转字符串

select reverse('好好学习')   //习学好好

时间和日期函数（背）

• 获取当前日期（年月日）

select current_date()

• 获取当前日期（年月日）

select curdate()

• 获取当前时间（年月日 时分秒）

select now()

• 本地时间（年月日 时分秒）

select localtime()

• 系统时间（年月日 时分秒）

select sysdate()

系统

• 用户

select system_user()
select user()

• 版本

select version()

实例

• 查询姓周的同学，把周改成邹

select replace(studentname,'周','邹') from student
where studentname like '周%'

聚合函数（常用）

函数名称	描述
count()	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值
…	…

实例

• 查询表中有多少条记录，count(指定列)，count(*)，count(1)

select count(studentname) from student  --会忽略null
select count(*) from student    --不会忽略null，本质是计算行数
select count(1) from student    --不会忽略null，本质是计算行数

• 求成绩总和

select sum(`studentresult`) as 总和 from result

• 计算平均分

select avg(`studentresult`) as 总和 from result

• 求最高分

select max(`studentresult`) as 总和 from result

• 求最低分

select min(`studentresult`) as 总和 from result

• 查询不同课程的平均分，最高分，最低分
• 核心：根据不同的课程进行分组

SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
 FROM result AS r
 INNER JOIN `subject` AS s
 ON r.subjectno = s.subjectno
 GROUP BY r.subjectno
 HAVING 平均分>80;

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

什么是MD5？

• MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。MD5由美国密码学家罗纳德·李维斯特（Ronald Linn Rivest）设计，于1992年公开，用以取代MD4算法。这套算法的程序在 RFC 1321 标准中被加以规范。1996年后该算法被证实存在弱点，可以被加以**，对于需要高度安全性的数据，专家一般建议改用其他算法，如SHA-2。2004年，证实MD5算法无法防止碰撞（collision），因此不适用于安全性认证，如SSL公开**认证或是数字签名等用途。
• 主要增强算法复杂度和不可逆性
• MD5不可逆，具体的值的md5是一样的

实例

• 明文密码

INSERT INTO testmd5 VALUES(1,'张三','123456'),(2,'李四','456789'),(3,'王五','789101')

• 加密

update testmd5 set pwd = md5(pwd) where id = 1;   --加密某个条件的密码
update testmd5 set pwd = md5(pwd);   --加密全部的密码

• 插入新数据时，自动加密

INSERT INTO testmd5 VALUES(4,'赵六',MD('123456'))

• 如何校验
  • 将用户传递进来的密码进行MD5加密，然后比对加密后的值