常用函数
数据函数
-- 绝对值
SELECT ABS(-10)
-- 向上取整
SELECT CEILING(10.2)
-- 向下取整
SELECT FLOOR(10.2)
-- 随机数(返回一个0~1之间的随机数)
SELECT RAND()
-- 符号函数(附属返回-1,正数返回1,0返回0)
SELECT SIGN(3)
字符串函数
-- 返回字符串包含的字符数
SELECT CHAR_LENGTH('hello world')
-- 合并字符串,参数可以有多个
SELECT CONCAT('hell','o,wor','ld')
-- 替换字符串,从某个位置开始替换某个长度
SELECT INSERT('hellworld',5,0,'o,')
-- 转换成小写
SELECT LOWER('DASD')
-- 转换成大写
SELECT UPPER('dsad')
-- 从左边截取
SELECT LEFT('hello,world',2)
-- 从右边截取
SELECT RIGHT('hello,world',2)
-- 替换字符串
SELECT REPLACE('hello,world','hello','goodbye')
-- 截取字符串开始和长度
SELECT SUBSTR('hello,world',2,6)
-- 反转字符串
SELECT REVERSE('hello,world')
-- 查询姓周的同学姓名,改成邹
SELECT REPLACE('周%','周','邹') AS 新名字
FROM student WHERE studentname 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 VERSION()
-- 用户
SELECT USER()
聚合函数
函数名称 | 描述 |
---|---|
COUNT() | 返回满足Select条件的记录总和数,如select count()【不建议使用,效率低】 |
SUM() | 返回数字字段或表达式列作统计,返回—列的总和。 |
AVG() | 通常为数值字段或表达列作统计,返回—列的平均值 |
MAX() | 可以为数值字段,字符字段或表达式列作统计,返回最大的值。 |
MIN() | 可以为数值字段,字符字段或表达式列作统计,返回最小的值。 |
-- 计数
SELECT COUNT(studentname) FROM student
SELECT COUNT(*) FROM student
-- 推荐使用
SELECT COUNT(1) FROM student
-- 从含义上讲,count(1) 与 count(*) 都表示对全部数据行的查询。
-- count(字段) 会统计该字段在表中出现的次数,忽略字段为null 的情况。即不统计字段为null 的记录。
-- count(*) 包括了所有的列,相当于行数,在统计结果的时候,包含字段为null 的记录;
-- count(1) 用1代表代码行,在统计结果的时候,包含字段为null 的记录 。
/*
很多人认为count(1)执行的效率会比count(*)高,原因是count(*)会存在全表扫描,而count(1)可以针对一个字段进行查询。其实不然,count(1)和count(*)都会对全表进行扫描,统计所有记录的条数,包括那些为null的记录,因此,它们的效率可以说是相差无几。而count(字段)则与前两者不同,它会统计该字段不为null的记录条数。
下面它们之间的一些对比:
1)在表没有主键时,count(1)比count(*)快
2)有主键时,主键作为计算条件,count(主键)效率最高;
3)若表格只有一个字段,则count(*)效率较高。
*/
-- 求和
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 r
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
GROUP BY r.subjectno
HAVING 平均分>=80
-- where写在group by前面.
-- 要是放在分组后面的筛选
-- 要使用HAVING..
-- 因为having是从前面筛选的字段再筛选,而where是从数据表中的>字段直接进行的筛选的
select小结
-- ================ 内置函数 ================
-- 数值函数
abs(x) -- 绝对值 abs(-10.9) = 10
format(x, d) -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x) -- 向上取整 ceil(10.1) = 11
floor(x) -- 向下取整 floor (10.1) = 10
round(x) -- 四舍五入去整
mod(m, n) -- m%n m mod n 求余 10%3=1
pi() -- 获得圆周率
pow(m, n) -- m^n
sqrt(x) -- 算术平方根
rand() -- 随机数
truncate(x, d) -- 截取d位小数
-- 时间日期函数
now(), current_timestamp(); -- 当前日期时间
current_date(); -- 当前日期
current_time(); -- 当前时间
date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分
time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间
unix_timestamp(); -- 获得unix时间戳
from_unixtime(); -- 从时间戳获得时间
-- 字符串函数
length(string) -- string长度,字节
char_length(string) -- string的字符个数
substring(str, position [,length]) -- 从str的position开始,取length个字符
replace(str ,search_str ,replace_str) -- 在str中用replace_str替换search_str
instr(string ,substring) -- 返回substring首次在string中出现的位置
concat(string [,...]) -- 连接字串
charset(str) -- 返回字串字符集
lcase(string) -- 转换成小写
left(string, length) -- 从string2中的左边起取length个字符
load_file(file_name) -- 从文件读取内容
locate(substring, string [,start_position]) -- 同instr,但可指定开始位置
lpad(string, length, pad) -- 重复用pad加在string开头,直到字串长度为length
ltrim(string) -- 去除前端空格
repeat(string, count) -- 重复count次
rpad(string, length, pad) --在str后用pad补充,直到长度为length
rtrim(string) -- 去除后端空格
strcmp(string1 ,string2) -- 逐字符比较两字串大小
-- 聚合函数
count()
sum();
max();
min();
avg();
group_concat()
-- 其他常用函数
md5();
default();
MD5加密
一、MD5简介
MD5即Message-Digest Algorithm 5(信息-摘要算法5),用于确保信息传输完整一致。是计算机广泛使用的杂凑算法之一(又译摘要算法、哈希算法),主流编程语言普遍已有MD5实现。将数据(如汉字)运算为另一固定长度值,是杂凑算法的基础原理,MD5的前身有MD2、MD3和MD4。
二、实现数据加密
-- 创表
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(100) NOT NULL,
`pwd` VARCHAR(100) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET = utf8
-- 插入数据
INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
-- 如果我们要对pwd这一列数据进行加密,语法是:
UPDATE testmd5 SET pwd =MD5(pwd)
-- 如果单独对某个用户(如kuangshen)的密码加密:
UPDATE testmd5 SET pwd =MD5(pwd) WHERE `name`='zhangsan'
-- 插入新的数据自动加密
INSERT INTO testmd5 VALUES(4,'xiaobai',MD5('123456'))
-- 查询登录用户信息(md5对比使用,查看用户输入加密后的密码进行比对)
SELECT * FROM testmd5 WHERE `name`='xiaobai' AND pwd=MD5('123456')