一、文本处理函数

编号函数名作用
1LEFT(s,n) 返回字符串s前n个字符
2RIGHT(s,n) 返回字符串s后n个字符
3LENGTH(s) 返回字符串s的长度
4LOCATE(s1,s2) 从字符串 s2 中获取 子串s1 的开始位置
5LOWER(s) 大写转小写
6UPPER(s) 小写转大写
7LTRIM(s) 去掉字符串s左面的空格
8RTRIM(s) 去掉字符串s右面的空格
9TRIM(s) 去掉字符串s两边的空格
10ASCII(s)返回字符串s的第一个字符的 ASCII 码
11 CONCAT(s1,s2…sn) 字符串 s1,s2 等多个字符串合并为一个字符串
12 FIND_IN_SET(s1,s2) 返回在字符串s2中与s1匹配的字符串的位置(多句话)
13FORMAT(x,n)可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入
14INSERT(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 码）
20REPLACE (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() 返回当前日期
2CURRENT_TIME()/CURTIME()返回当前时间
3CURRENT_TIMESTAMP()返回当前日期和时间
4ADDDATE(d,n)计算起始日期 d 加上 n 天的日期
5ADDTIME(t,n)时间 t 加上 n 秒的时间
6DATE()从日期或日期时间表达式中提取日期值
7DAY(d)返回日期值 d 的日期部分
8DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数
9DATE_FORMAT按表达式 f的要求显示日期 d
10DAYNAME(d)返回日期 d 是星期几，如 Monday,Tuesday
11DAYOFMONTH(d)计算日期 d 是本月的第几天
12DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推
13DAYOFYEAR(d)计算日期 d 是本年的第几天
14UNIX_TIMESTAMP()得到时间戳
15FROM_UNIXTIME()时间戳转日期
16NOW()返回当前的日期和时间
17STR_TO_DATE()将日期格式的字符转换成指定格式的日期
18DATE_FORMAT()将日期转换成字符(支持：- . /分割年月日)

日期格式化占位符

示例


# 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日');

三、数值处理函数

编号函数名作用
1ABS(x)返回x的绝对值
2AVG(expression)返回一个表达式的平均值，expression 是一个字段
3CEIL(x)/CEILING(x)返回大于或等于 x 的最小整数
4FLOOR(x)返回小于或等于 x 的最大整数
5EXP(x)返回 e 的 x 次方
6GREATEST(expr1, expr2, expr3, …)返回列表中的最大值
7LEAST(expr1, expr2, expr3, …)返回列表中的最小值
8LN返回数字的自然对数
9LOG(x)返回自然对数(以 e 为底的对数)
10MAX(expression)返回字段 expression 中的最大值
11MIN(expression)返回字段 expression 中的最大值
12POW(x,y)/POWER(x,y)返回 x 的 y 次方
13RAND()返回 0 到 1 的随机数
14ROUND(x)返回离 x 最近的整数
15SIGN(x)返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1
16SQRT(x)返回x的平方根
17SUM(expression)返回指定字段的总和
18TRUNCATE(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(*)的效率高
    最后修改日期:2020-12-10

    作者

    留言

    撰写回覆或留言

    发布留言必须填写的电子邮件地址不会公开。