【狂神】SQL笔记5

MySQL函数

mySQL官方手册链接

1 常用函数

=======数学运算=======
select abs(-8) --绝对值
select ceiling(9.4) --向上取整
select floor()9.4 --向下取整
select rand() --返回一个0~1之间的随机数
select sign(-10) --判断一个数的符号，0-0，负数返回-1，整数返回1

======字符串函数======
select char_length('agdfgaga') --字符串长度
select concat('aad','adf','sdf') --拼接字符串
select insert('hello',3,2,'a') --查询，替换 把第三个字符开始的两个字符替换为1个a，即hello变为halo
select lower('Abc')		--转小写字母
select uplower('Abc')	--转大写字母
select instr('Abcdef','d') --返回第一次出现的子串的索引
select repace('abcdefg','abc','a')  --替换出现的指定字符串,a替换abc
select substr('abcdefg',4,3) --截取子串，第4个开始截取3个字符
select reverse('abc') --反转字符

======时间和日期======
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()

2 聚合函数(常用)

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

想查询一个表中有多少记录:
select count(studentname) from student;  --count(字段)，会忽略所有的null值
select count(*) from student;  --count(*)，包括了所有的列，计算行数不会忽略null值
select count(1) from result;	--count(1)会统计表中的所有的记录数，包含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

查询不同课程的平均分，最高分，最低分，平均分大于80
核心： （根据不同的课程分组）
select `SubjectName`, avg(StudentResult) as 平均分,max(StudentResult),min(StudentResult)
from result r
inner join `subject` sub
on r.`SubjectNo` = sub.`SubjectNo`
group by r.SubjectNo --通过某字段分组
having avg(StudentResult) > 80
或 having 平均分 > 80

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

MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数。
主要增强算法复杂度和不可逆性
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'),(3,'wangwu','123456')

--加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1
UPDATE testmd5 SET pwd=MD5(pwd) --加密全部的密码

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'小明',MD5(123456))

--