正文

mysql常用单行函数

IP云
IP云V管理员/0 阅读
12/02
小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

一、大小写控制函数

LOWER(str)    将str的值全部置为小写字母

  1. select LOWER('ABC');
  3. --结果：
  5. LOWER('ABC')
  6. abc
UPPER(str)    将str的值全部置为大写字母
  1. select UPPER('abc');
  3. --结果：
  5. UPPER('abc')
  6. ABC
二、字符控制函数
CONCAT(str1,str2,...)    将str1、str2等字符串连接起来
  1. select CONCAT('a','b','c');
  3. --结果：
  5. CONCAT('a','b','c')
  6. abc
SUBSTR(str,pos,len)    从str的第pos位（范围：1~str.length）开始，截取长度为len的字符串
  1. select SUBSTR('abc',1,2);
  3. --结果：
  5. SUBSTR('abc',1,2)
  6. ab
LENGTH(str)    获取str的长度
  1. select LENGTH('abc');
  3. --结果：
  5. LENGTH('abc')
  6. 3
INSTR(str,substr)    获取substr在str中的位置
  1. select INSTR('abc','a');
  3. --结果：
  5. INSTR('abc','a')
  6. 1
LPAD(str,len,padstr)/RPAD(str,len,padstr)    首先判断str是否达到了len的长度，如果没有达到，那么在str的左侧/右侧对应添加padstr来使结果达到len的长度
注：padstr常用的值有：'*'和' '（空格）
  1. select name from person;
  3. --结果：
  5. name
  6. xiaoP
  7. xiaoPP
  8. xiao_p
  10. --
  12. select LPAD(name,10,'*')from person;
  14. --结果：
  16. LPAD(name,10,'*')
  17. *****xiaoP
  18. ****xiaoPP
  19. ****xiao_p
TRIM(remstr FROM str)    从str中删除开头和结尾的remstr（不会处理字符串中间含有的remstr）
TRIM(str)    从str中删除开头和结尾的空格（不会处理字符串中间含有的空格）
LTRIM(str)    从str中删除左侧开头的空格
RTRIM(str)    从str中删除右侧结尾的空格
  1. select TRIM('a' FROM 'abca');
  3. --结果：
  5. TRIM('a' FROM 'abca')
  6. bc
  8. --
  9. select TRIM('a' FROM 'abab');
  11. --结果：
  13. TRIM('a' FROM 'abab')
  14. bab
  16. --
  18. select LTRIM(' abc ');
  20. --结果：
  22. LTRIM(' abc ')
  23. abc --abc后面是有空格的
  25. --
  26. select RTRIM(' abc ');
  28. --结果：
  30. RTRIM(' abc ')
  31. abc
REPLACE(str,from_str,to_str)    将str中的from_str替换为to_str（会替换掉所有符合from_str的字符串）
  1. SELECT REPLACE('abcbc','b','m');
  3. --结果：
  5. REPLACE('abcbc','b','m')
  6. amcmc
三、数字函数
ROUND(X,D)    根据D指定的（小数）位数对X进行四舍五入（注意，D可以为负数，为负数的时候即为对个位以上四舍五入）
ROUND(X)    对X进行取整，根据十分位进行四舍五入，精确到个位
  1. SELECT ROUND(2.3456,3);
  3. --结果：
  5. ROUND(2.3456,3)
  6. 2.346
TRUNCATE(X,D)    根据D指定的（小数）位数来对X进行截断（不进行四舍五入）（注意，D可以为负数，为负数的时候即为对个位以上截断）
  1. SELECT TRUNCATE(10.88888,2);
  3. --结果：
  5. TRUNCATE(10.88888,2)
  6. 10.88
MOD(N,M)    对N/M求余
  1. select MOD(5.7,2.8);
  3. --结果：
  5. MOD(5.7,2.8)
  6. 0.1
四、日期函数
SYSDATE()或者NOW()   返回当前系统时间，格式为YYYY-MM-DD hh-mm-ss
  1. select SYSDATE();
  3. --结果：
  5. SYSDATE()
  6. 2015-06-2021:17:25
DAYOFMONTH(date)    返回指定时间的月份
DAYOFWEEK(date)    返回指定时间是星期几（注意，这个和中国的习惯有不符，此返回值如果是1，则代表星期日）
DAYOFYEAR(date)    返回指定时间是哪一年
以上函数中，date可以用NOW()或者其他的date格式的字段
日期可以和数字（作为天数）做加减；日期和日期之间只可以做减法；数字除以24可以作为天数和日期相加减；
注：经实测，日期只要是和数字进行了交互，格式就会发生变化（由YYYY-MM-DD hh-mm-ss变为YYYYMMDDhhmmss），然后再进行运算
DAYNAME(date)    返回date日期是星期几
LAST_DAY(date)    返回date日期当月的最后一天
五、转换函数
TO_DAYS(date)    将date格式的日期转换为天数（'0000-01-01'转换结果为1，以此类推）
TO_SECONDS(expr)    将表达式expr转换成秒（'0000-01-01'转换结果为86400，为一天的秒数）
DATE_FORMAT(date,format)    将日期转换成字符串（类似oracle中的to_char()）
STR_TO_DATE(str,format)    将字符串转换成日期（类似oracle中的to_date()）
  1. SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %T'),STR_TO_DATE(NOW(),'%Y-%m-%d %T');
  3. --结果：
  5. DATE_FORMAT(NOW(),'%Y-%m-%d %T') STR_TO_DATE(NOW(),'%Y-%m-%d %T')
  6. 2015-06-2113:52:06               2015-06-2113:52:06
UNIX_TIMESTAMP([datetime])    将datetime格式的日期时间转换为unix时间戳（如果参数为空，则转换当前系统时间）
六、通用函数
IFNULL(expr1,expr2)    判断expr1是否为null，如果为null，则用expr2来代替null（类似oracle的NVL()函数）
  1. SELECT
  2. id,
  3. NAME,
  4. dept_id,
  5. FORMAT(
  6. salary *12*(IFNULL(commission_pct,1)),
  7. 0
  8. )
  9. FROM
  10. person
  11. WHERE
  12. dept_id IN (101,106,109);
  14. --结果：
  16. id name dept_id      FORMAT    (salary*12*(IFNULL(commission_pct,1)),0)
  17. 0001    LiLei        101       13,200
  18. 0002    HanMeimei    106       27,600
  19. 0003    Lucy         109       36,000
IF(expr1,expr2,expr3)    判断expr1是否为真（是否不为null），如果为真，则使用expr2替代expr1；如果为假，则使用expr3替代expr1（类似oracle的NVL2()函数）
  1. SELECT
  3. IF (
  4. dept_id,
  5. '有部门','没有部门'
  6. ) dept
  7. FROM
  8. person;
  10. --结果：
  12. dept
  13. 有部门
  14. 有部门
  15. 有部门
  16. 没有部门
NULLIF(expr1,expr2)    判断expr1和expr2是否相等，如果相等则返回null，如果不相等则返回expr1
  1. select NULLIF(1,1),NULLIF(1,2);
  3. --结果：
  5. NULLIF(1,1)   NULLIF(1,2)
  6. (NULL)        1
COALESCE(value,...)    判断value的值是否为null，如果不为null，则返回value；如果为null，则判断下一个value是否为null……直至出现不为null的value并返回或者返回最后一个为null的value。
  1. SELECT
  2. COALESCE (
  3. dept_id,
  4. commission_pct,
  5. NAME
  6. )
  7. FROM
  8. person
  9. WHERE
  10. NAME ='Lily';
  12. --结果：
  14. COALESCE (dept_id,commission_pct,name)
  15. Lily
CASE WHEN THEN ELSE END    条件函数
CASE 指定字段 WHEN 需要符合的条件1 THEN 表达式1 WHEN 需要符合的条件2 THEN 表达式2 ... ELSE 其他情况的表达式 END
  1. SELECT
  2. id,
  3. NAME,
  4. dept_id,
  5. CASE dept_id
  6. WHEN 101 THEN
  7. salary *1.1
  8. WHEN 106 THEN
  9. salary *1.2
  10. ELSE
  11. salary *1.3
  12. END salary
  13. FROM
  14. person
  15. WHERE
  16. dept_id IN (101,106,109);
  18. --结果：
  20. id       NAME       dept_id     salary
  21. 0001     LiLei      101         1100
  22. 0002     HanMeimei  106         2400
  23. 0003     Lucy       109         3900

相关阅读

目录[+]