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'的课程的学生成绩评价，<60为'不及格',60-70为'及格'，70-80为'一般'，80-90为'良好',90-100为'优秀'
 SELECT s_id,c_id,
 CASE 
 WHEN s_score < 60 THEN '不及格'
 WHEN 60<=s_score and s_score<70 THEN '及格'
 WHEN 70<=s_score and s_score<80 THEN '一般'
 WHEN 80<=s_score and s_score<90 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)