MySQL 常用函数

发布于 更新于
本文目录
  1. 准备数据
  2. 表名和字段
  3. 1.学生表
  4. 2.课程表
  5. 3.教师表
  6. 4. 成绩表
  7. 测试数据
  8. 一、流程控制函数
  9. 1. CASE
  10. 2. IF()
  11. 3. IFNULL()
  12. 4. NULLIF()
  13. 二、数学函数与操作符
  14. 三、字符串函数
  15. 1.CONCAT()
  16. 2.CONCAT_WS()
  17. 3.FORMAT()
  18. 4.LEFT()
  19. 5.RIGHT
  20. 6.CHAR_LENGTH()
  21. 7.LOWER()
  22. 8.UPPER()
  23. 9.TRIM()
  24. 10.SUBSTR()/MID()/SUBSTRING()
  25. 四、时间日期函数
  26. 1. NOW()
  27. 2.CURTIME()
  28. 3. CURDATE()
  29. 4. DATE
  30. 5. TIME
  31. 6.YEAR/MONTH/DAY
  32. 7. HOUR/MINUTE/SECOND
  33. 8.WEEKDAY
  34. 9.DATE_FORMAT
  35. 10.FROM_UNIXTIME
  36. 11. STR_TO_DATE()
  37. 12.UNIX_TIMESTAMP
  38. 五、窗口函数
  39. 1.RANK
  40. 2.DENSE_RANK
  41. 3. ROW_NUMBER
  42. 4.LAG()
  43. 5.LEAD()
  44. 6.FIRST_VALUE()
  45. 7.LAST_VALUE()

技术文章Linux与数据库

MySQL提供了很多的内置函数，可以方便的实现很多复杂的功能。

MySQL提供了很多的内置函数，可以方便的实现很多复杂的功能。

准备数据

表名和字段

1.学生表

  • student(s_id,s_name,s_birth, s_sex)

  • 学生编号，学生姓名，出生年月，学生性别

2.课程表

  • course(c_id, c_name, t_id)

  • 课程编号，课程名称，教师编号

3.教师表

  • teacher（t_id, t_name)

  • 教师编号，教师姓名

4. 成绩表

Score(s_id, c_id, s_score)

  • 学生编号，课程编号，分数

测试数据

 -- ----------------------------
 -- Table structure for student
 -- 学生表的结构
 -- ----------------------------
 CREATE TABLE `student`  (
   `s_id` varchar(20)  NOT NULL,
   `s_name` varchar(48)  DEFAULT NULL,
   `s_birth` varchar(24)  DEFAULT NULL,
   `s_sex` tinyint(255) DEFAULT NULL,
   PRIMARY KEY (`s_id`) USING BTREE
 ) ENGINE = InnoDB CHARACTER SET = utf8mb4;
 
 -- ----------------------------
 -- Records of student
 -- 学生表数据
 -- ----------------------------
 INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01', 1);
 INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21', 1);
 INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20', 1);
 INSERT INTO `student` VALUES ('04', '李云', '1990-08-06', 1);
 INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01', 0);
 INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01', 0);
 INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01', 0);
 INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20', 0);
 
 -- ----------------------------
 -- Table structure for course
 -- 课程表的结构
 -- ----------------------------
 CREATE TABLE `course`  (
   `c_id` varchar(20)  NOT NULL,
   `c_name` varchar(128) DEFAULT NULL,
   `t_id` varchar(20) DEFAULT NULL,
   PRIMARY KEY (`c_id`) USING BTREE
 ) ENGINE = InnoDB CHARACTER SET = utf8mb4;
 
 -- ----------------------------
 -- Records of course
 -- 课程表数据
 -- ----------------------------
 INSERT INTO `course` VALUES ('01', '语文', '02');
 INSERT INTO `course` VALUES ('02', '数学', '01');
 INSERT INTO `course` VALUES ('03', '英语', '03');
 
 -- ----------------------------
 -- Table structure for teacher
 -- 老师表结构
 -- ----------------------------
 CREATE TABLE `teacher`  (
   `t_id` varchar(20) NOT NULL,
   `t_name` varchar(128) DEFAULT NULL,
   PRIMARY KEY (`t_id`) USING BTREE
 ) ENGINE = InnoDB CHARACTER SET = utf8mb4;
 
 -- ----------------------------
 -- Records of teacher
 -- 老师表数据
 -- ----------------------------
 INSERT INTO `teacher` VALUES ('01', '张三');
 INSERT INTO `teacher` VALUES ('02', '李四');
 INSERT INTO `teacher` VALUES ('03', '王五');
 
 -- ----------------------------
 -- Table structure for score
 -- 成绩表结构
 -- ----------------------------
 CREATE TABLE `score`  (
   `s_id` varchar(20) NOT NULL,
   `c_id` varchar(20) NOT NULL,
   `s_score` tinyint(255) UNSIGNED DEFAULT NULL,
   PRIMARY KEY (`s_id`, `c_id`)
 ) ENGINE = InnoDB CHARACTER SET = utf8mb4;
 
 -- ----------------------------
 -- Records of score
 -- 成绩表数据
 -- ----------------------------
 INSERT INTO `score` VALUES ('01', '01', 80);
 INSERT INTO `score` VALUES ('01', '02', 90);
 INSERT INTO `score` VALUES ('01', '03', 99);
 INSERT INTO `score` VALUES ('02', '01', 70);
 INSERT INTO `score` VALUES ('02', '02', 60);
 INSERT INTO `score` VALUES ('02', '03', 80);
 INSERT INTO `score` VALUES ('03', '01', 80);
 INSERT INTO `score` VALUES ('03', '02', 80);
 INSERT INTO `score` VALUES ('03', '03', 80);
 INSERT INTO `score` VALUES ('04', '01', 50);
 INSERT INTO `score` VALUES ('04', '02', 30);
 INSERT INTO `score` VALUES ('04', '03', 20);
 INSERT INTO `score` VALUES ('05', '01', 76);
 INSERT INTO `score` VALUES ('05', '02', 87);
 INSERT INTO `score` VALUES ('06', '01', 31);
 INSERT INTO `score` VALUES ('06', '03', 34);
 INSERT INTO `score` VALUES ('07', '02', 89);
 INSERT INTO `score` VALUES ('07', '03', 98);

一、流程控制函数

1. CASE

案例运算符，通常用来做多分支判断

语法：

 # 1
 CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
 # 返回第一个和value相等的compare_value 对应的result
 # 2
 CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
 # 返回第一个和condition为True对应的result

案例：

 # 1. 查询学生信息，要求把性别字段s_sex中的1转换成'男'，0转换成'女'，其他值转换成'空'
 select s_id, s_name, 
 CASE s_sex
 WHEN 1 THEN '男'
 WHEN 0 THEN '女'
 ELSE '空'
 END AS sex
 FROM student;
 # 2. 查询课程编号为'01'的课程的学生成绩评价，
 SELECT s_id,c_id,
 CASE 
 WHEN s_score < 60 THEN '不及格'
 WHEN 60and s_score70 THEN '及格'
 WHEN 70and s_score80 THEN '一般'
 WHEN 80and s_score90 THEN '良好'
 ELSE '优秀' END as `评价`
 from score;

2. IF()

条件运算函数

语法：

 IF(expr1, expr2, expr3)
 #如果expr1表达式为真，或者（expr1 <>0 and expr1<>null),则返回expr2，否则返回expr3

案例：

 # 查询学生信息，性别字段s_sex 中1表示'男',0表示'女'
 select s_id, s_name,
 if(s_sex, '男', '女') as sex 
 from student;
 
 select s_id, s_name,
 if(s_sex=1, '男', '女') as sex 
 from st udent;

3. IFNULL()

是否NULL

语法：

 IFNULL(expr1,expr2)
 # 如果expr1不为NULL则返回expr1，否则返回expr2

案例：

 # 查询学生信息，性别字段s_sex中为null时返回3
 select s_id, s_name, 
 IFNULL(s_sex,3)
 FROM student;

4. NULLIF()

语法：

 NULLIF(expr1,expr2)
 # 如果expr1=expr2返回NULL 否则返回expr1

案例：

 mysql> SELECT NULLIF(1,1);
         -> NULL
 mysql> SELECT NULLIF(1,2);
         -> 1

二、数学函数与操作符

常见的数学函数与算数操作符比较简单，详情请查看官方文档

三、字符串函数

1.CONCAT()

返回串联的字符串

语法：

 CONCAT(str1,str2,...)
 # 返回连接参数产生的字符串
 # 注意如果参数中有NULL返回NULL

案例：

 # 查询学生信息输出'学号姓名性别'的形式
 select 
 CONCAT(s_id,s_name,if(s_sex,'男','女')) 
 FROM student;

2.CONCAT_WS()

返回用分隔符连接的字符串

语法：

 CONCAT_WS(separator,str1,str2,...)
 # 第一个参数是分隔符，分隔符被添加到要连接的字符串之间
 # 分隔符为NULL则返回NULL

案例：

 # 查询学生信息输出'学号-姓名-性别'的形式
 select 
 CONCAT_WS('-',s_id,s_name,if(s_sex,'男','女')) 
 FROM student;

3.FORMAT()

返回格式化为指定小数位数的数字

语法：

 FORMAT(X,D)
 # 将数字X格式化为'#,###,###.##',将其舍入到D小数位，然后将结果作为字符串返回。如果D为0，则结果没有小数部分。

案例：

 # 统计课程编号为'01'的课程的平均成绩，结果保留两位小数
 SELECT 
 FORMAT(avg(s_score),2) 
 FROM score WHERE c_id='01';

4.LEFT()

返回指定数量的最左边的字符

语法：

 LEFT(str,len)
 返回字符串中最左边的len个字符，如果任何参数为NULL返回NULL

案例：

 # 按出生年份统计学生人数
 SELECT 
   LEFT( s_birth, 4 ) AS `year`,
   count( 1 ) 
 FROM
   student 
 GROUP BY
   `year`;

5.RIGHT

返回指定数量的最右边的字符

语法：

 RIGHT(str,len)
 返回字符串中最右边的len个字符，如果任何参数为NULL返回NULL

案例：

 # 1-10号为上旬，11-20号为中旬，20-31号为下旬
 # 统计上，中，下旬过生日的学生数量
 SELECT
   CASE
   WHEN RIGHT( s_birth, 2 )>=1 AND RIGHT( s_birth, 2 )10
   THEN '上旬'
   WHEN RIGHT( s_birth, 2 )>=11 AND RIGHT( s_birth, 2 )20
   THEN '中旬'
   ELSE '下旬'
   END AS `period`,
   count( 1 ) 
 FROM
   student 
 GROUP BY
   `period`;

6.CHAR_LENGTH()

返回字符串的长度（以字符为单位）

语法：

 CHAR_LENGTH(str)
 # 返回字符串str的长度，以字符为单位。

案例：

 # 统计名字为三个字的学生的数量
 SELECT COUNT(1) FROM student
 WHERE CHAR_LENGTH(s_name) = 3;

7.LOWER()

以小写形式返回参数

语法：

 LOWER(str)
 # 返回str的小写形式

案例：

 SELECT LOWER('HELLO WORLD!');

8.UPPER()

以大写形式返回参数

语法：

 UPPER(str)
 # 返回str的小写形式

案例：

 SELECT UPPER('hello world!');

9.TRIM()

删除前后空格

语法：

 TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
 TRIM([remstr FROM] str)
 # 返回已删除str所有remstr前缀或后缀的字符串。如果为指定BOTH，LEADING或TRAILING
 # 则默认为BOTH，remstr可选，未指定则删除空格

案例：

 mysql> SELECT TRIM('  bar   ');
         -> 'bar'
 mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
         -> 'barxxx'
 mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
         -> 'bar'
 mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
         -> 'barx'

10.SUBSTR()/MID()/SUBSTRING()

返回指定的子字符串

语法：

 SUBSTR(str,pos)
 SUBSTR(str FROM pos)
 # 不带len参数表示从 str 的pos 位置开始的子字符串
 # pos从1开始
 SUBSTR(str,pos,len)
 SUBSTR(str FROM pos FOR len)
 # 带len 表示从str 的pos位置开始的长为len 的子字符串

案例：

 mysql> SELECT SUBSTRING('Quadratically',5);
         -> 'ratically'
 mysql> SELECT SUBSTRING('foobarbar' FROM 4);
         -> 'barbar'
 mysql> SELECT SUBSTRING('Quadratically',5,6);
         -> 'ratica'
 # 按出生月份统计学生人数
 SELECT MID(s_birth from 6 FOR 2) as `month`, count(1) 
 from student GROUP BY `month`;

四、时间日期函数

1. NOW()

返回当前日期和时间

语法：

 NOW([fsp])
 # 返回当前日期和时间，返回的数据类型，取决于该函数是在字符串上下文还是在数字上下文中使用。字符串形式为'YYYY-MM-DD hh:mm:ss'，数字形式为YYYYMMDDhhmmss
 # 参数fsp给定0-6位小数的秒精度

案例：

 mysql> SELECT NOW();
         -> '2007-12-15 23:50:26'
 mysql> SELECT NOW() + 0;
         -> 20071215235026.000000

2.CURTIME()

返回当前时间

语法：

 CURTIME([fsp])
 # 返回当前时间，值的形式为'hh:mm:ss'或 hhmmss格式，具体取决于函数是在字符串还是数字上下文中使用
 # 参数fsp给定0-6位小数的秒精度

案例：

 mysql> SELECT CURTIME();
         -> '23:50:26'
 mysql> SELECT CURTIME() + 0;
         -> 235026.000000

3. CURDATE()

返回当前日期

语法：

 CURDATE()
 # 返回当前日期，值的形式为'YYYY-MM-DD' 或YYYYMMDD，具体取决于函数是在字符串还是数字上下文中使用

案例：

 mysql> SELECT CURDATE();
         -> '2008-06-13'
 mysql> SELECT CURDATE() + 0;
         -> 20080613

4. DATE

提取日期或日期时间表达式的日期部分

语法：

 DATE(expr)
 # 提取日期或日期时间表达式的日期部分

案例：

 mysql> SELECT DATE('2003-12-31 01:02:03');
         -> '2003-12-31'

5. TIME

提取时间部分

语法：

 TIME(expr)
 # 提取时间或日期时间表达式的时间部分，并将其作为字符串返回

案例：

 mysql> SELECT TIME('2003-12-31 01:02:03');
         -> '01:02:03'

6.YEAR/MONTH/DAY

提取年，月,日

语法：

 YEAR(date)
 # 返回日期或日期字符串的年，范围1000至9999
 MONTH(date)
 # 返回日期或日期字符串的月，范围1-12
 DAY(date)
 # 返回日期或日期字符串中的某天，范围1-31

案例：

 # 按出生年份统计学生人数
 SELECT YEAR(s_birth) as `year`, count(1)  
 FROM student
 GROUP BY `year`;
 # 按出生月份统计学生人数
 SELECT MONTH(s_birth) as `month`, count(1)  
 FROM student
 GROUP BY `month`;
 # 按出生日期的天统计学生人数
 SELECT DAY(s_birth) as `day`, count(1)  
 FROM student
 GROUP BY `day`;

7. HOUR/MINUTE/SECOND

提取时分秒

语法：

 HOUR(time)
 # 返回时间time的小时
 MINUTE(time)
 # 返回时间time的分钟，范围0到59。
 SECOND(time)
 # 返回时间time的秒，范围0到59

案例：

 mysql> SELECT HOUR('10:05:03');
         -> 10
 mysql> SELECT HOUR('272:59:59');
         -> 272
         
 mysql> SELECT MINUTE('2008-02-03 10:05:03');
         -> 5        
         
 mysql> SELECT SECOND('10:05:03');
         -> 3

8.WEEKDAY

返回date （0=星期一，1=星期二，… 6=星期日）的工作日索引。

语法：

 WEEKDAY(date)

案例：

 mysql> SELECT WEEKDAY('2008-02-03 22:23:00');
         -> 6
 mysql> SELECT WEEKDAY('2007-11-06');
         -> 1
 # 按星期几统计学生出生人数
 SELECT CASE WEEKDAY(s_birth)
        WHEN 0 THEN '星期一'
        WHEN 1 THEN '星期二'
        WHEN 2 THEN '星期三'
        WHEN 3 THEN '星期四'
        WHEN 4 THEN '星期五'
        WHEN 5 THEN '星期六'
        WHEN 6 THEN '星期日'
        END AS `weekday`,
        count(1)
 FROM student
 GROUP BY `weekday`;

9.DATE_FORMAT

日期格式化输出

语法：

 DATE_FORMAT(date,format)
 # 根据格式化字符串format输出日期date的形式

案例：

 mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
         -> 'Sunday October 2009'
 mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
         -> '22:23:00'
 mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
     ->                 '%D %y %a %d %m %b %j');
         -> '4th 00 Thu 04 10 Oct 277'
 mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
     ->                 '%H %k %I %r %T %S %w');
         -> '22 22 10 10:23:00 PM 22:23:00 00 6'
 mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
         -> '1998 52'
 mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
         -> '00'

格式化规则字符串见官方文档

10.FROM_UNIXTIME

转换时间戳为格式化的日期时间

语法：

 FROM_UNIXTIME(unix_timestamp[,format])
 # 转换时间戳（秒数）为日期时间，默认格式为'YYYY-MM-DD hh:mm:ss'
 # 如果format给出了格式规则字符串，则按该格式

案例：

 mysql> SELECT FROM_UNIXTIME(1447430881);
         -> '2015-11-13 10:08:01'
 mysql> SELECT FROM_UNIXTIME(1447430881) + 0;
         -> 20151113100801
 mysql> SELECT FROM_UNIXTIME(1447430881,
     ->                      '%Y %D %M %h:%i:%s %x');
         -> '2015 13th November 10:08:01 2015'

11. STR_TO_DATE()

将字符串转换为日期

语法：

 STR_TO_DATE(str,format)
 # 这是DATE_FORMAT()的逆函数，它接受一个日期时间字符串和对应的格式字符串
 # 返回对应的日期时间值
 # 如果日期字符串和格式字符串不匹配，则返回NULL

案例：

 mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
         -> '2013-05-01'
 mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
         -> '2013-05-01'
 mysql> SELECT STR_TO_DATE('a09:30:17','%h:%i:%s');
         -> NULL

12.UNIX_TIMESTAMP

返回Unix时间戳

语法：

 UNIX_TIMESTAMP([date])
 # 不传入参数，它将返回当前UNIX时间戳，表示自'1970-01-01 00:00:00'UTC 以来的秒数。

案例：

 mysql> SELECT UNIX_TIMESTAMP();
         -> 1447431666
 mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');
         -> 1447431619
 mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012');
         -> 1447431619.012

更多的日期时间函数请看官方文档

五、窗口函数

注意：窗口函数仅支持MySQL8.0+

1.RANK

语法：

 RANK() over_clause
 # 返回当前行在其分区内的排名
 # 如果存在大于1的组，则不会将连续的等级分配给对等的组

案例：

 # 查询课程编号为01的课程的分数排名，输出学生编号，分数和排名
 # 当有并列名次时，下一个名次不会连续排名
 # 例如有两个并列第一名，下一个名次为第三名
 SELECT s_id, s_score,
  RANK() over(ORDER BY s_score DESC) AS 'rank'
 FROM score
 WHERE c_id='01';
 
 # 按各科成绩进行排序，并显示排名
 SELECT *,
 RANK() OVER(PARTITION BY c_id ORDER BY s_score DESC) as 'rank'
 FROM score;

2.DENSE_RANK

语法：

 DENSE_RANK() over_clause
 # 返回当前行在其分区内的排名
 # 该功能将连续的等级分配给对等组
 # 例如有两个并列第一，下一个名次为第二名

案例：

 # 查询课程编号为01的课程的分数排名，输出学生编号，分数和排名
 # 当有并列名次时，下一个名次会连续排名
 # 例如有两个并列第一名，下一个名次为第二名
 SELECT s_id, s_score,
  DENSE_RANK() over(ORDER BY s_score DESC) AS 'rank'
 FROM score
 WHERE c_id='01';
 
 # 按各科成绩进行排序，并显示排名
 SELECT *,
 DENSE_RANK() OVER(PARTITION BY c_id ORDER BY s_score DESC) as 'rank'
 FROM score;

3. ROW_NUMBER

语法：

 ROW_NUMBER() over_clause
 # 返回其分区内当前行的编号

案例：

 # 按分数对课程编号为01的课程成绩进行排序并加上序号
 SELECT s_id, s_score,
  ROW_NUMBER() over(ORDER BY s_score DESC) AS 'rank'
 FROM score
 WHERE c_id='01';

4.LAG()

语法：

 LAG(expr [, N[, default]])
 # expr从滞后于当前行的N行的行中返回该行中的值。如果没有这样的行，则返回值为default。
 #如果N或default没有指定，则默认分别为1和NULL

案例：

 # 按日期统计销售汇总表中的数据，添加lag列返回下一天的销售额
 # 添加lead列返回上一天的销售额
 SELECT 
 piv_date,
 sum(PIV_YS_AMT) as 'sum',
 LAG(sum(PIV_YS_AMT)) over w as 'lag',
 LEAD(sum(PIV_YS_AMT)) over w as 'lead'
 
 FROM `xzj2月销售汇总表`
 GROUP BY PIV_DATE
 window w as ();
 

5.LEAD()

语法：

 LEAD(expr [, N[, default]])
 # expr从超前于当前行的N行的行中返回该行中的值。如果没有这样的行，则返回default。
 # 如果N或default没有指定，则默认分别为1和null

案例：

 # 按日期统计销售汇总表中的数据，添加lag列返回下一天的销售额
 # 添加lead列返回上一天的销售额
 SELECT 
 piv_date,
 sum(PIV_YS_AMT) as 'sum',
 LAG(sum(PIV_YS_AMT)) over w as 'lag',
 LEAD(sum(PIV_YS_AMT)) over w as 'lead'
 
 FROM `xzj2月销售汇总表`
 GROUP BY PIV_DATE
 window w as ();

6.FIRST_VALUE()

语法：

 FIRST_VALUE(expr) over_clause
 # 返回窗口的第一行

案例：

 # 按课程返回最高分和最低分
 SELECT  DISTINCT c_id, 
 FIRST_VALUE(s_score) over w as 'first',
 LAST_VALUE(s_score) over w as 'last'
 FROM score
 window w as (PARTITION by c_id ORDER BY s_score DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

7.LAST_VALUE()

语法：

 LAST_VALUE(expr) over_clause
 # 返回窗口的最后一行

案例：

 # 按课程返回最高分和最低分
 SELECT  DISTINCT c_id, 
 FIRST_VALUE(s_score) over w as 'first',
 LAST_VALUE(s_score) over w as 'last'
 FROM score
 window w as (PARTITION by c_id ORDER BY s_score DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

来源：testingpai

继续阅读