Mysql数据库 常用函数

一 常见数据库的函数

1 聚合函数

count()  统计行数
sum()    计算总和
max()    最大值
min()    最小值
avg()    平均值

2 字符函数

concat(a, b) 将a和b拼接成一个字符串
length("子符串") 计算字符串的字节长度
char_length()  计算字符个数
lower()  小写转换
upper()  大写转换
replace(str, sub, replace_str) 替换子子符串
trim() 同python的strip()函数，删除两连的空白
instr(str, sub) 返回sub在str的首次出现的位置， 索引位置是从1开始
lpad(str,len,sub)  如果str的长度不足时，在左边填充sub字符， 同Python的str对象的rjust()
rpad(str, len, sub) 如果str的长度不足时，在右边填充sub字符, 同Python的str对象的ljust()

char(N) 将AscII数值转成字符， N可以是一个字符，也可以是数值
ord(str) 将ASCII字符转成数值
format(n, 小数位长) 将n转成带千位符‘#，###.##’格式的字符串

3 数值函数

round(n, 小数位长)  四舍五入
ceil(n) 上行取整，比n大的，最小的整数
floor() 下行取整，比n小的，最大的整数
pow(n, c) 计算n的c次方   
mod(n1, n2) 计算两者的余数
rand() 随机产生[0,1)之间数，如 ceil(rand()*15) + 1,生成[1,16]之间的数
abs()  求绝对值
sin()/asin()/cos()/acos()/tan()/atan()/con() 数学相关的三角函数

4 日期函数

current_date()/curdate()  当前日期
current_timestamp()/now() 当前日期时间
curerent_time()/curtime() 当前时间

year()  获取日期中年
month() 获取日期中的月
day()   获取日期中的天
dayofweek()  获取星期几， 1是星期日， 7是星期六
dayofyear()  获取一年中的第几天
weekofyear() 获取一年中第几周
hour()
minute()
second()

datediff(now(), "2019-10-11")  获取两个日期之间的间隔天数
timediff("12:10:09", "15:10:09") 获取两时间之间的问隔时间

str_to_date(日期字符串， "格式")  将字符串转成日期， 格式： %Y-%m-%d %H:%i:%s
date_format(data, "格式")  将时间转成字符串

date_add(date, interval n 时间关键字) 某一时间添加 n的间隔（关键字）时长后的日期时间
		时间关键字： year,month, day, hour, minute, second, week
date_sub(data, interval n 时间关键字) 某一时间减少n的间隔（关键字）时长后的日期时间

-- 15天之后是几号？星期几？ 是一年中第几天？
select date_add(now(), interval 15 day) "15天后的日期", dayofweek(date_add(now(), interval 15 day))-1 "星期", dayofyear(date_add(now(), interval 15 day)) "年的天"; 

-- 46、查询各学生的年龄
 select sn,name,sex,ceil(datediff(now(), age)/365) from student;
 
-- 47、查询本周过生日的学生
select * 
from student
where weekofyear(now()) = weekofyear(age);

-- 48、查询下周过生日的学生
select * 
from student
where weekofyear(now())+1 = weekofyear(age);

-- 49、查询本月过生日的学生
select * 
from student
where month(now()) = month(age);

-- 50、查询下月过生日的学生
select * 
from student
where month(now())+1 = month(age);

5 条件函数

if(条件, 条件为真的结果， 条件为假的结果)
ifnull(v1, v2) 如果第一个值为null时，返回v2， 反之返回v1。
case 分支语句：

-- MySQL 不支持全外连接
select s.*, ifnull(sc.score, 0) score
from student s
left join score sc on (sc.sn = s.sn);

select sn, name, sex, age, 
       case month(age) when 1 then "A"
                       when 2 then "B"
                       when 3 then "C"
                       when 4 then "D"
                       when 5 then "E"
                       when 6 then "F"
                       when 7 then "G"
                       when 8 then "H"
                       when 9 then "I"
                       when 10 then "J"
                       when 11 then "K"
                       when 12 then "L"
                       else "Z" end "level"
from student
order by level;  -- 只有order by语句可以使用 查询中的新增列名
-- where level = "A"; where 语句只针对原有表字段

select sn, name, sex, age, 
       case when month(age)>=2 and month(age)<=4 then "A"
            when  month(age)>=5 and month(age)<=7 then "B"
            when month(age)>=8 and month(age)<=10 then "C"
            else "D" end "level"
            
from student
order by level;

6 加密算法函数

password()  -- MySQL5存在的， MySQL 8不存在
md5(str)  将str转成md5编码字符，长度32位 
sha1()   将str转成sha1编码字符，长度40位 
uuid()   获取uuid的字符串， 字符串中带有`-`符号，可以使用replace()函数将其去除。