mysql的函数

大小写转换函数

lower 转换小写函数

select lower(name) as name from student

upper转换大写函数

select upper(name) as name from student

截取字符串函数 substr

select substr(name,1,1) as name from student

拼接字符串函数 concat

注：拼接字符串如果拼接的字段中有任意参数为null，则返回null
select name,position,concat(name,position) as namePosition from student

null设置值 ifnull

select name,position,concat(name,ifnull(position,"")) as namePosition from student

获取长度函数 length

select name,length(name) as namelength from student

去除空格 trim

select * from student where name = trim(" 三藏法师 ")

字符串转化成日期 str_to_date

select * from student where birthdate = str_to_date("2010-12-30","%Y-%m-%d")

字符串格式化日期 date_fromat

select * from student where birthdate = date_format("20101230","%Y-%m-%d")

四舍五入 round

select score,round(score) as roundScore from student

保留一位小数形式
select score,round(score,1) as roundScore from student

随机数 rand

student表中有多少条数据随机多少个随机数
select rand() from student

随机数改成100以内
select round(rand()*100) from student

最大值 max

select max(score) from student

最小值 min

select min(score) from student

总和 sum

select sum(score) from student

平均数 avg

select avg(score) from student

个数 count

查询数据库中有几个学生
select count(name) from student