[大数据]MySQL基础学习（五）——mysql中的系统函数

前言

上一篇博客简单总结了一些mysql中常用的SQL语句，这一篇博客总结一下mysql中常用的函数，以及mysql8.0中新增的公共表达式等内容。mysql中常用的系统函数不多，主要分为时间系统函数，字符串系统函数以及一些其他的系统函数。

常用的时间函数

直接获取时间的函数

直接上表格

实例说明

SELECT CURDATE(),CURTIME(),NOW();
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s');

时间计算函数

实例

-- sec_to_time,time_to_sec,UNIX_TIMESTAMP,FROM_UNIXTIME
-- datediff,extract
SELECT SEC_TO_TIME(3600),TIME_TO_SEC('02:30:00')
,UNIX_TIMESTAMP('2021-12-03 15:45:22')
,FROM_UNIXTIME(1638517522)
,DATEDIFF('2022-02-03','2022-01-01')
,EXTRACT(YEAR FROM '2021-12-03');

-- date_add函数
SELECT NOW()
,DATE_ADD(NOW(),INTERVAL 1 DAY)-- 当前时间加一天
,DATE_ADD(NOW(),INTERVAL 1 YEAR) -- 当前时间加1年
,DATE_ADD(NOW(),INTERVAL -1 DAY) -- 当前时间减1天
,DATE_ADD(NOW(),INTERVAL '-1:30' HOUR_MINUTE) -- 当前时间减1:30

-- 计算每门课程距离当前时间的天数
SELECT title,DATEDIFF(NOW(),online_time)
FROM t_class_course;

常用的字符串函数

直接上表格

实例

-- 字符串拼接
SELECT CONCAT('prefix','subfix');
SELECT CONCAT_WS('||','prefix','subfix');

-- 字符长度
SELECT class_name,LENGTH(class_name),CHAR_LENGTH(class_name)
FROM imc_class

-- 指定四舍五入的小数位
SELECT FORMAT(12345.7896,3,'#');

-- 字符串截取
SELECT LEFT('www.mysql.learn',3),RIGHT('www.mysql.learn',5)
SELECT SUBSTRING('www.mysql.learn',5); -- 输出mysql.learn
SELECT SUBSTRING_INDEX('192.168.0.100','.',-2); -- 输出 0.100 从右边起第几个用负数表示

-- trim删除空格或者指定的字符
SELECT TRIM('    learnMySQL'),TRIM('j' FROM 'jjjjjjjLearnjjjjjjj');

-- 截取课程标题中横线之前的部分
SELECT title,LOCATE('-',title),SUBSTRING(title,1,LOCATE('-',title)-1)
FROM t_class_course

其他系统函数

表格

实例

-- case when 的实例
SELECT 
  user_nick,
  CASE
    WHEN sex = 1 THEN '男' 
    WHEN sex = 0 THEN '女' 
    ELSE '未知' 
  END AS '性别' 
FROM
  t_calss_user 
  
-- case when在where中的使用 
-- 在上面的基础上，查询出所有男性的用户信息
SELECT 
  user_nick,
  CASE
    WHEN sex = 1 
    THEN '男' 
    WHEN sex = 0 
    THEN '女' 
    ELSE '未知' 
  END AS '性别' 
FROM
  t_calss_user 
WHERE 
  CASE
    WHEN sex = 1 
    THEN '男' 
    WHEN sex = 0 
    THEN '女' 
    ELSE '未知' 
  END = '男' 

公共表表达式

公共表表达式(Common Table Expression)，简称CTE是mysql8.0中的新特性，个人认为这个是比子查询更好的一种查询方式，相比子查询，CTE会在查询的时候生成一个临时表，并且在查询的时候，可以多次引用以及自引用（递归引用）。其性能和可读性要比子查询好得多。

公共表表达式语法

WITH [RECURSIVE] -- 可以递归定义
cte_name [(column_list)] AS (
	query
)
[,cte_name [(column_list)] as ( -- 可以同时定义多个
	query
)]

-- 这个不能少
SELECT * FROM cte_name;

基础实例

-- 一个简单的公共表表达式
WITH cte AS(
SELECT title,study_cnt,class_id
FROM t_class_course 
WHERE study_cnt > 200
)

SELECT * FROM cte;

-- 公共表表达式可以多次引用，而子查询是不行的
SELECT * FROM cte
UNION ALL
SELECT * FROM cte 
ORDER BY title;

递归的公共表表达式实例

-- 公共表表达式递归生成自增序列
WITH recursive cte_test AS(
SELECT 1 AS n 
UNION ALL
SELECT 1+n FROM cte_test -- 自己引用自己
WHERE n<10
)

SELECT * FROM cte_test;

执行结果

递归常用与树形结构数据的遍历，复杂的实例这里不再提供，这个基于不同的业务有不同的处理方式。

窗口函数

窗口函数也是mysql8.0以上的专属，窗口函数是相对于聚合函数而言的，聚合函数是将一组数据汇总为单个结果，而窗口函数是针对查询中的每一行数据，基于和它相关的一组数据计算出一个结果

这个可以参考这篇博客：掘金小册——窗口函数。

小结

mysql系统函数的简单总结