MySQL 常用函数

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

准备数据

表名和字段

1.学生表
2.课程表
3.教师表
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)
回帖
请输入回帖内容 ...