MySQL常用函数

我是莫提
287 阅读11分钟

一、文本处理函数

编号 函数名 作用
1 LEFT(s,n) 返回字符串s前n个字符
2 RIGHT(s,n) 返回字符串s后n个字符
3 LENGTH(s) 返回字符串s的长度
4 LOCATE(s1,s2) 从字符串 s2 中获取 子串s1 的开始位置
5 LOWER(s) 大写转小写
6 UPPER(s) 小写转大写
7 LTRIM(s) 去掉字符串s左面的空格
8 RTRIM(s) 去掉字符串s右面的空格
9 TRIM(s) 去掉字符串s两边的空格
10 ASCII(s) 返回字符串s的第一个字符的 ASCII 码
11 CONCAT(s1,s2…sn) 字符串 s1,s2 等多个字符串合并为一个字符串
12 FIND_IN_SET(s1,s2) 返回在字符串s2中与s1匹配的字符串的位置(多句话)
13 FORMAT(x,n) 可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入
14 INSERT(s1,x,len,s2) 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串
15 SUBSTR(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串
16 POSITION(s1 IN s) 从字符串 s 中获取 s1 的开始位置
17 REPEAT(s,n) 将字符串 s 重复 n 次
18 REVERSE(s) 将字符串s的顺序反过来
19 STRCMP(s1,s2) 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1（比较的是字符串首字母的 ASCII 码）
20 REPLACE (s1,s2,s3) 替换字符串；将s1中的s2内容替换为s3

示例

# 1、返回字符串前几个字符
SELECT LEFT('abcdefg',3);

# 2、返回字符串后几个字符
SELECT RIGHT('abcdefg',3);

# 3、返回字符串的长度
SELECT LENGTH('abcdefg');

# 4、从字符串 s2 中获取 子串s1 的开始位置
SELECT LOCATE('bc','abcdefg');

# 5、大写转小写
SELECT LOWER('ABCD');

# 6、小写转大写
SELECT UPPER('abcd');

# 7、去掉字符串左面的空格
SELECT LENGTH(LTRIM('  abc'));

# 8、去掉字符串右面的空格
SELECT LENGTH(RTRIM('abc  '));

# 9、去掉字符串两边的空格
SELECT LENGTH(TRIM(' abc '));

# 10、返回字符串第一个字符的 ASCII 码
SELECT ASCII('AB');

# 11、字符串 s1,s2 等多个字符串合并为一个字符串 
SELECT CONCAT('hel','llo');

# 12、返回在字符串s2中与s1匹配的字符串的位置
SELECT FIND_IN_SET('c', 'abcde');

# 13、可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入
SELECT FORMAT(250500.5634, 2); 

# 14、字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串
SELECT INSERT("google.com", 1, 6, "runnob");

# 15、从字符串 s 的 start 位置截取长度为 length 的子字符串
SELECT SUBSTR("abcdefg", 2, 3);

# 16、从字符串 s 中获取 s1 的开始位置
SELECT POSITION('b' IN 'abc');

# 17、将字符串 s 重复 n 次
SELECT REPEAT('MySQl',3);

# 18、将字符串s的顺序反过来
SELECT REVERSE('abc');

# 19、比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1（比较的是字符串首字母的 ASCII  码） 
SELECT STRCMP("ABC", "BCD");

# 20、替换字符串；将s1中的s2内容替换为s3
SELECT REPLACE('abcdefg','cd','CD');

二、日期和时间处理函数

MySQL存储日期的格式 2020-01-05 12:30:05

编号 函数名 作用
1 CURDATE()/CURRENT_DATE() 返回当前日期
2 CURRENT_TIME()/CURTIME() 返回当前时间
3 CURRENT_TIMESTAMP() 返回当前日期和时间
4 ADDDATE(d,n) 计算起始日期 d 加上 n 天的日期
5 ADDTIME(t,n) 时间 t 加上 n 秒的时间
6 DATE() 从日期或日期时间表达式中提取日期值
7 DAY(d) 返回日期值 d 的日期部分
8 DATEDIFF(d1,d2) 计算日期 d1->d2 之间相隔的天数
9 DATE_FORMAT 按表达式 f的要求显示日期 d
10 DAYNAME(d) 返回日期 d 是星期几，如 Monday,Tuesday
11 DAYOFMONTH(d) 计算日期 d 是本月的第几天
12 DAYOFWEEK(d) 日期 d 今天是星期几，1 星期日，2 星期一，以此类推
13 DAYOFYEAR(d) 计算日期 d 是本年的第几天
14 UNIX_TIMESTAMP() 得到时间戳
15 FROM_UNIXTIME() 时间戳转日期
16 NOW() 返回当前的日期和时间
17 STR_TO_DATE() 将日期格式的字符转换成指定格式的日期
18 DATE_FORMAT() 将日期转换成字符(支持：- . /分割年月日)

日期格式化占位符

img
img

示例

# 1、CURDATE()/CURRENT_DATE()返回当前日期
SELECT CURDATE();
SELECT CURRENT_DATE();

# 2、CURRENT_TIME()/CURTIME()返回当前时间
SELECT CURRENT_TIME();

# 3、CURRENT_TIMESTAMP()返回当前日期和时间
SELECT CURRENT_TIMESTAMP();

# 4、ADDDATE(d,n)计算起始日期 d 加上 n 天的日期
SELECT ADDDATE("2017-06-15",5);

# 5、ADDTIME(t,n)时间 t 加上 n 秒的时间
SELECT ADDTIME('2011-11-11 11:11:11', 5);

# 6、DATE()从日期或日期时间表达式中提取日期值
SELECT DATE("2017-06-15 11:11:16");

# 7、DAY(d)返回日期值 d 的日期部分
SELECT DAY("2017-06-15"); 

# 8、DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数
SELECT DATEDIFF('2001-01-01','2001-02-02'); 

# 9、DATE_FORMAT按表达式 f的要求显示日期 d
SELECT DATE_FORMAT('2011.11.11 11:11:11','%Y-%m-%d %r');

# 10、DAYNAME(d)返回日期 d 是星期几，如 Monday,Tuesday
SELECT DAYNAME('2011-11-11 11:11:11');

# 11、DAYOFMONTH(d)计算日期 d 是本月的第几天
SELECT DAYOFMONTH('2011-11-11 11:11:11');

# 12、DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推
SELECT DAYOFWEEK('2011-11-11 11:11:11');

# 13、DAYOFYEAR(d)计算日期 d 是本年的第几天
SELECT DAYOFYEAR('2011-11-11 11:11:11');

# 14、UNIX_TIMESTAMP()得到时间戳
SELECT UNIX_TIMESTAMP('2019-2-19');

# 15、FROM_UNIXTIME()时间戳转日期
SELECT FROM_UNIXTIME(1550505600);
SELECT FROM_UNIXTIME(1550505600, '%Y-%m-%d');

# 16、NOW() 返回当前的日期和时间
SELECT NOW();

# 17、将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y');

# 18、将日期转换成字符(支持：- . /分割年月日)
SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日');

三、数值处理函数

编号 函数名 作用
1 ABS(x) 返回x的绝对值
2 AVG(expression) 返回一个表达式的平均值，expression 是一个字段
3 CEIL(x)/CEILING(x) 返回大于或等于 x 的最小整数
4 FLOOR(x) 返回小于或等于 x 的最大整数
5 EXP(x) 返回 e 的 x 次方
6 GREATEST(expr1, expr2, expr3, …) 返回列表中的最大值
7 LEAST(expr1, expr2, expr3, …) 返回列表中的最小值
8 LN 返回数字的自然对数
9 LOG(x) 返回自然对数(以 e 为底的对数)
10 MAX(expression) 返回字段 expression 中的最大值
11 MIN(expression) 返回字段 expression 中的最大值
12 POW(x,y)/POWER(x,y) 返回 x 的 y 次方
13 RAND() 返回 0 到 1 的随机数
14 ROUND(x) 返回离 x 最近的整数
15 SIGN(x) 返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1
16 SQRT(x) 返回x的平方根
17 SUM(expression) 返回指定字段的总和
18 TRUNCATE(x,y) 返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入） 
# 1、ABS(x)返回x的绝对值
SELECT ABS(-1);

# 2、AVG(expression) 返回一个表达式的平均值，expression 是一个字段
SELECT AVG(salary) FROM employees;

# 3、CEIL(x)/CEILING(x) 返回大于或等于 x 的最小整数
SELECT CEIL(1.5);
SELECT CEILING(1.5);

# 4、FLOOR(x) 返回小于或等于 x 的最大整数
SELECT FLOOR(1.5);

# 5、EXP(x) 返回 e 的 x 次方
SELECT EXP(3);

# 6、GREATEST(expr1, expr2, expr3, …) 返回列表中的最大值
SELECT GREATEST(3, 12, 34, 8, 25);

# 7、LEAST(expr1, expr2, expr3, …) 返回列表中的最小值
SELECT LEAST(3, 12, 34, 8, 25);

# 8、LN 返回数字的自然对数
SELECT LN(2);

# 9、LOG(x) 返回自然对数(以 e 为底的对数)
SELECT LOG(20.085536923188);

# 10、MAX(expression)返回字段 expression 中的最大值
SELECT MAX(salary) FROM employees;

# 11、MIN(expression)返回字段 expression 中的最大值
SELECT MIN(salary) FROM employees;

# 12、POW(x,y)/POWER(x,y)返回 x 的 y 次方
SELECT POW(2,3);
SELECT POWER(2,3);

# 13、RAND()返回 0 到 1 的随机数
SELECT RAND();

# 14、ROUND(x)返回离 x 最近的整数
SELECT ROUND(1.23456);

# 15、SIGN(x)返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1
SELECT SIGN(-10);

# 16、SQRT(x)返回x的平方根
SELECT SQRT(25);

# 17、SUM(expression)返回指定字段的总和
SELECT SUM(salary) FROM employees;

# 18、TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）
SELECT TRUNCATE(1.23456,3);

四、流程控制函数

if函数： if else 的效果

SELECT IF(10<5,'大','小');

SELECT
   last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
FROM
   employees;

case函数的使用

  • switch case 的效果
 case 要判断的字段或表达式
 when 常量1 then 要显示的值1或语句1;
 when 常量2 then 要显示的值2或语句2;
 …
 else 要显示的值n或语句n;
 end
案例：查询员工的工资，要求

部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
---------------------------------------------
SELECT
    salary 原始工资,department_id,
    CASE department_id
        WHEN 30 THEN salary*1.1
        WHEN 40 THEN salary*1.2
        WHEN 50 THEN salary*1.3
        ELSE salary
    END
        AS 新工资
FROM 
    employees;
  • 类似于 多重if
 case 
 when 条件1 then 要显示的值1或语句1
 when 条件2 then 要显示的值2或语句2
 。。。
 else 要显示的值n或语句n
 end
案例：查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示C级别
否则，显示D级别
---------------------------------------------
SELECT
    salary,
    CASE 
        WHEN salary>20000 THEN 'A'
        WHEN salary>15000 THEN 'B'
        WHEN salary>10000 THEN 'C'
        ELSE 'D'
    END
    AS 工资级别
FROM
    employees;

五、Count()函数

count(1)和count(*)是最简单基本的用法，就是统计待处理的结果集有多少数据行，也包括空行。

SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;
# 两者结果是一样的

效率相比

  • 当存储引擎为InnoDB 时，两者效率差不多，但都比count(字段)效率高
  • 当存储引擎为 MyISAM时， count(*)的效率高
avatar
文章
阅读
粉丝