mysql 常用函数聚合函数以及md5加密算法

5.mysql函数

常用函数

--绝对值
select abs(-8);
--向上取整
select ceiling(9.4);
--向下取整
select floor(9.4);
--随机数
select rand();
--判断正负数
select sign(-1);
--字符串长度
select char_length('lllll');
--拼接字符串
select concat('w','s');
--字符串替换
select insert('woaibiancheng',1,4,'llll');
--转换大小写
select lower('SS');
select upper('hh');
--第一次出现的字串的索引
select replace('sss','ss','ww');
--返回指定的字符串 从第几个开始截取几个
select substr('ssssss',4,6);
--获取当前时间
select current_date();
select now();
select localtime();
select current();
select year(now());
select month(now());
       ......

聚合函数

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

select count(*) from student;
select count(1) from student;
select count(name) from student;
select sum(name) from student;
select avg(name) from student;
select min(name) from student;
select max(name) from student;

数据库级别的md5加密算法

--建表
mysql> 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','123123'),(2,'lisi','123123'),(3,'wangwu','123123');

--更新密码为MD5
update testmd5 set pwd=md5(pwd);

--插入密码为MD5
insert into testmd5 values(4,'xixi',md5('123123'));

--查询密码为MD5
select * from testmd5 where name='xixi' and pwd=md5('123123');
+----+------+----------------------------------+
| id | name | pwd                              |
+----+------+----------------------------------+
|  4 | xixi | 4297f44b13955235245b2497399d7a93 |
+----+------+----------------------------------+
1 row in set