mysql06常见函数

1.字符函数

2.数字函数

3.日期

4.条件判断CASE

 

1.字符函数

 

 

 1.1 LOWER 大写转小写

 

SELECT activity_code,LOWER(activity_code) lowerst FROM jsnh_activity

 

 

 

1.2 UPPER 小写转大写

 

 

SELECT activity_code,UPPER(activity_code) upperst FROM jsnh_activity

 

 

 

1.3 CONCAT 拼接字符串

 

SELECT activity_code,activity_id,CONCAT(activity_code,'-',activity_id ) concatst FROM jsnh_activity

 

 

 

1.4 SUBSTR

SUBSTR (str, pos)

SUBSTR (str, pos, len)

str为列名/字符串；

pos为起始位置；mysql中的起始位置pos是从1开始的；如果为正数，就表示从正数的位置往下截取字符串（起始坐标从1开始），反之如果起始位置pos为负数，那么 表示就从倒数第几个开始截取；

len为截取字符个数/长度

 

1)从第11个字符开始截取到末尾

SELECT activity_code ,SUBSTR(activity_code,  11 ) sunstrst FROM jsnh_activity

 

 

2)从倒数第6个字符开始截取到末尾

SELECT activity_code ,SUBSTR(activity_code,  -6 ) sunstrst FROM jsnh_activity

 

 

3)从第11个字符开始截取，截取10个字符

SELECT activity_code ,SUBSTR(activity_code,  11,10 ) sunstrst FROM jsnh_activity

 

4)从倒数第6个字符开始截取，截取3个字符

SELECT activity_code ,SUBSTR(activity_code,  -6,3 ) sunstrst FROM jsnh_activity

 

1.5 LENGTH 获取字符串长度

 

SELECT activity_code ,LENGTH(activity_code) le FROM jsnh_activity

 

 

 

1.6 INSTR 查找

在字符串中查找某字符串，返回子字符串在字符串中首次出现的位置；如果没有找到，则返回0

 

SELECT activity_code ,INSTR(activity_code,'XX') le FROM jsnh_activity

 

 

 

1.7LPAD和RPAD 填充

LPAD(sourceStr, length, newStr)，将字符串newStr填补到sourceStr左边，直到sourceStr长度达到length

RPAD(sourceStr, length, newStr)，将字符串newStr填补到sourceStr右边，直到sourceStr长度达到length

SELECT activity_id ,LPAD(activity_id,10,'LEFT') le, RPAD(activity_id,10,'RIGHT') ri FROM jsnh_activity

 

 

1.8 TRIM和LTRIM和RTRIM 去除空格

TRIM()         #删除字符串前后空格
RTRIM()        #删除字符串结尾空格
LTRIM()        #删除字符串起始空格

字符串' abc def '前后中间都有空格，使用三个函数去除空格

SELECT TRIM('  abc def  ') tr,LTRIM('  abc def  ') ltr,RTRIM('  abc def  ') rlt

 

 

 

TRIM()左右两侧的空格都去掉了，中间的空格还在

LRIM()左侧的空格都去掉了，中间和右侧的空格还在

RRIM()由侧的空格都去掉了，中间和左侧的空格还在

 

1.9 REPLACE 替换

使用字符Y替换字符XX

 

SELECT activity_code,REPLACE(activity_code,'XX','Y') FROM jsnh_activity

 

 

 

 

2.数字函数

2.1 ROUND 四舍五入

SELECT ROUND(3.65)

 

 

2.2 TRUNCATE 保留几位小数

 

SELECT TRUNCATE(123.456,2)

 

 

 

2.3 MOD 取余

 

SELECT MOD(100,3)

 

 

 

3.日期函数

3.1 now 当前日期

 

 

SELECT NOW()

 

 

 

3.2 str_to_date 字符串转换为日期

 

SELECT STR_TO_DATE('2001.12.01','%Y.%m.%d') da

 

 

 

3.3 date_format 日期转换为指定格式字符串

 

 

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') dast

 

 

 

3.4格式化表达式

 

%a    缩写星期名
%b    缩写月名
%c    月，数值
%D    带有英文前缀的月中的天
%d    月的天，数值(00-31)
%e    月的天，数值(0-31)
%f    微秒
%H    小时 (00-23)
%h    小时 (01-12)
%I    小时 (01-12)
%i    分钟，数值(00-59)
%j    年的天 (001-366)
%k    小时 (0-23)
%l    小时 (1-12)
%M    月名
%m    月，数值(00-12)
%p    AM 或 PM
%r    时间，12-小时（hh:mm:ss AM 或 PM）
%S    秒(00-59)
%s    秒(00-59)
%T    时间, 24-小时 (hh:mm:ss)
%U    周 (00-53) 星期日是一周的第一天
%u    周 (00-53) 星期一是一周的第一天
%V    周 (01-53) 星期日是一周的第一天，与 %X 使用
%v    周 (01-53) 星期一是一周的第一天，与 %x 使用
%W    星期名
%w    周的天 （0=星期日, 6=星期六）
%X    年，其中的星期日是周的第一天，4 位，与 %V 使用
%x    年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y    年，4 位
%y    年，2 位

 

 

 

 

4.条件判断

4.1.CASE

 

1)简单函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

SELECT activity_id,
    CASE(activity_id) 
     WHEN 2 THEN activity_id * -1
     WHEN 6 THEN activity_id * -2
     ELSE  activity_id
     END ca
FROM jsnh_activity

 

 

2)搜索函数
CASE WHEN [expr] THEN [result1]…ELSE [default] END,搜索函数可以写判断，并且搜索函数只会返回第一个符合条件的值，其他case被忽略

 

 

SELECT activity_id,
    CASE(activity_id) 
     WHEN activity_id < 6   THEN activity_id * -1
     ELSE  activity_id
     END ca
FROM jsnh_activity

 

 

符合<6的应该有多条数据，但是只有第一条数据乘以-1了，其它的都忽略了

 

4.2 if()函数

IF(表达式1，值1，值2)
如果表达式1成立，返回表达式2的值，否则返回表达式3的值

SELECT activity_id,IF(MOD(activity_id,2)=0,'偶数','奇数') id FROM jsnh_activity