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