mysql常用函数

MYSQL常用函数

字符函数

①length(str)函数                               :字符串长度
②concat(str1,str2,...)函数                     :字符串连接
③upper(str)、lower(str)函数                     :转大写
④substr(str,start,len)函数                      :从指定位置截取指定个数的字符串
⑤instr(str,要查找的子串)函数                      :判断是否存在 返回 索引值 或者 0
⑥trim(str)函数                                  : 去除两端空格
⑦lpad(str,len,填充字符)、rpad(str,len,填充字符)函数:左右两边指定个数填充.
⑧replace(str,子串,另一个字符串)函数                : 字符串替换.

select length('hello') ;# 查询传入字符串的的长度.
select length(pname) from product where pid = 'p001';#如果是中文.又是utf8编码一个汉字占用3个字节

# 连接商品名称和商拼价格中间,号分隔.
select concat('hello','123','world');#hello123world
select concat(pname,',',price)from product;# "联想,6000"

# 把pid内容变大写.pname变小写
select upper(pid),lower(pname) from product;#P001 ,jack jones

# 把t_book 表中的书名截取前6个字符
select substr('hello',0,3);# 开始位置必须从1开始
select substr(bname,1,6) from tb_book;# 包1包6 [1,6]

#判断商品名称是否包含 '花'
select instr(pname,'花') from product; # 存在返回1 ,不存在返回 0

#去掉空格.一般做数据清洗
select trim(pname) from product;

select lpad('aaa',4,'#');#: #aaa 左右两边指定字符填充.然后获取指定长度.
select rpad('aaa',4,'#');#: aaa#

select replace('hello','ll','xx');#hexxo

#把霸改成霸王
select replace(pname,'霸','霸王') from product where pid = 'p007';

数学函数

①round(x,保留位数)函数: 四舍五入保留小数位.
②ceil(x)函数        :天花板函数
③floor(x)函数       :地板函数
④truncate(x,D)函数  : 按照D的值截取小数部分.
⑤mod(被除数,除数)函数 : 求余数
⑥pow(x,D)函数       : x的D次方

select round(3.1415926,2);# 3.14
select round(3.1415926,3);# 3.142 四舍五入保留小数位.

select ceil(3.14);#4
select floor(3.94);#3
select truncate(3.1415,3);
select truncate(3.1415,4);# 不考虑四舍五入
select mod(10,3);#1
select mod(9,3);#0
select pow(2,2);
select pow(2,3);

#把书的价格调整为整数销售.
select ceil(bprice) from tb_book;

时间与日期函数

①日期格式
②now()函数                          :当前日期和时间
③curdate()函数                      :当前日期
④curtime()函数                      :当前时间
⑤获取日期和时间中的年、月、日、时、分、秒
⑥weekofyear()函数                   :一年的第几周
⑦ quarter()函数                     :一年中的季度
⑧ str_to_date()函数                 :字符串转时间类型
⑨date_format()函数                  :格式化时间字符串
⑩date_add(日期,interval num 时间)函数 :添加日期
⑪last_day()函数                      :月度最后一天
⑫datediff(end_date,start_date)函数   : 时间差
⑬timestampdiff(unit,start_date,end_date)函数计算两个时间返回的年/月/天数；
unix_timestamp(date) : 把日期转毫秒值
from_unixtime(int): 把毫秒值转 日期

select now();#2024-03-27 16:09:49
select curdate();#2024-03-27
select curtime();#16:09:49
select year('24-11-11');#2024
select year(now());#2024# 从年月日时分秒中获取年.
select substr(now(),1,4);#2024 -- 探索函数实现原理
select substr(now(),6,2);#03
select month(now()) ;# 3

#查询今日是一年的第几周
select weekofyear(now());# 13周
#查询今日是一年的那个季度
select quarter(now());#1
select quarter('2024-06-09');#2

#字符串时间转日期类型
SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');# str_to_date要求格式比较固定
select str_to_date('04-06-2024','%m-%d-%Y');

# date_format()函数  :格式化时间字符串
select now();#2024-03-27 16:28:12
select date_format(now(),'%Y/%m/%d %H:%i:%s');#2024/03/27 16:34:02
select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');#2024年03月27日 16时33分15秒

#查询明天的日期
select date_add(now(),interval 1 day );# ;2024-03-28 16:37:35
#查询明年的日期
select date_add(now(),interval 1 year );# ;2025-03-27 16:37:35
#查询去年的日期
select date_add(now(),interval -1 year );# ;2023-03-27 16:37:35

#计算本月最后一天
select last_day('2024-02-01');# 参数为date
select last_day('2024-12-01');

#  datediff(end_date,start_date)函数   : 时间差
# 计算到今年5月1还有几天
select datediff('2024-05-01',now());
select timestampdiff(day,now(),'2024-05-01');#34天
select timestampdiff(month ,now(),'2024-06-01');# 1
select timestampdiff(year ,now(),'2025-06-01');# 1

# unix_timestamp(date) : 把日期转毫秒值
select unix_timestamp(now());#1711529268
# from_unixtime(int): 把毫秒值转 日期
select from_unixtime(1711529268);#2024-03-27 16:47:48 日期格式是默认格式.
select from_unixtime(1711529268,'%Y年%m月%d日 %H时%i分%s秒');#2024年03月27日 16时47分48秒 日期格式是指定格式

流程操作

①if(expr,v1,v2)函数   : 判断数据给出返回值
②ifnull()函数         : 判断空给出返回值
④nullif(expr1,expr2)  : 相同返回null 不同保留表达式1的原值.
③case…when函数用法     : 多条件判断给出返回值.
    #区间范围的判断.
    # Case
    # When condition1 Then result1
    # When condition2 Then result2
    # ...
    # Else result_n
    # End
    #--------------------------------------
    # 固定值的判断.
    # case 列名
    # when 值 then 值
    # when 值 then 值
    # ....
    # else 值 end
④nullif(expr1,expr2)   : 相同返回null 不同保留表达式1的原值.

/**
  ④case…when函数用法     : 多条件判断给出返回值.
    #区间范围的判断.
    Case
        When condition1 Then result1
        When condition2 Then result2
        ...
        Else result_n
    End
    --------------------------------------
    固定值的判断.
    case 列名
        when 值 then 值
        when 值 then 值
        ....
        else 值
    end
 */

select
case
 when 50>=90 then '优秀'
 when 50>= 70 then '良好'
 when 50>= 60 then '及格'
 else '不及格'
end ;

# 使用case when来查询商品表.如果价格在1000以上是奢侈品.500-1000属于高端品.500以下就是平价商品.
select pname,price,
        case
        when price > 1000 then '奢侈品'
        when price >=500 and price <= 1000 then '高端品'
        when price < 500 then '平价商品'
        end as type
from product;


# 固定值的判断.
#     case 列名
#         when 值 then 值
#         when 值 then 值
#         ....
#         else 值
#     end

# 商品表.如果商品类别是c001显示电器,是c002显示服装,是c003显示化妆品.
select pname,price,
       case category_id
            when 'c001' then '电器'
            when 'c002' then '服装'
            when 'c003' then '化妆品'
            else '其它'
       end category_name
       from product;