# 系统常用函数

使用系统函数能大大提高我们的工作效率，将原来在程序中完成的工作，可以由 MySQL 的函数来完成

# 常用的时间函数

# 当前日期：CURDATE、CURTIME、NOW

CURDATE()、CURTIME()：返回当前日期/返回当前时间
NOW()：返回当前日期时间

SELECT
	CURDATE( ),
	CURTIME( ),
	NOW( )
-- 响应的数据
CURDATE( )	CURTIME( )	NOW( )
2020-04-20	11:51:22	2020-04-20 11:51:22

1
2
3
4
5
6
7

###日期格式化：DATE_FORMAT

DATE_FORMAT(date,fmt)：按照 fmt 的格式，对日期 date 进行格式化

常用的 fmt 格式有：

%Y：四位的年
%m：月份，00~12
%d：天，00~31
%H：小时，00~24
%i：分钟，00~59
%s：秒， 00~59

SELECT
	DATE_FORMAT( NOW( ), '%Y-%m-%d %H:%i:%s' )

-- 响应数据
DATE_FORMAT( NOW( ), '%Y-%m-%d %H:%i:%s' )
2020-04-20 11:59:57

1
2
3
4
5
6

# 秒小时互转：SEC_TO_TIME、TIME_TO_SEC

SEC_TO_TIME(seconds)：把秒数转换为小时:分:秒
TIME_TO_SEC(time)：把时间小时:分:秒转换为秒数

他们是一对相反的函数

SELECT
	SEC_TO_TIME( 3600 ),
	TIME_TO_SEC( '1:00:00' )

-- 响应数据
SEC_TO_TIME( 3600 )	TIME_TO_SEC( '1:00:00' )
01:00:00	3600

1
2
3
4
5
6
7

# 相差天数：DATEDIFF

DATEDIFF(date1,date2)：返回两个日期相差的天数

-- 计算每门课程，上线时间距离当前时间的天数
SELECT
	title,
	DATEDIFF( now( ), online_time ) 
FROM
	imc_course

-- 响应的数据
title	DATEDIFF( now( ), online_time )
MySQL课程-88343	625
PostgreSQL课程-42349	496
PostgreSQL课程-69538	708

1
2
3
4
5
6
7
8
9
10
11
12

###日期加减：DATE_ADD

DATE_ADD(date,INTERVAL expr unit)：对给定的日期增加或减少指定的时间单元（unit）:

DAY ：天
HOUR：小时
MINUTES：分钟
SECOND：秒

SELECT
	NOW( ),
	DATE_ADD( NOW( ), INTERVAL 1 DAY ),-- 当前时间加 1 天
	DATE_ADD( NOW( ), INTERVAL 1 YEAR ),-- 当前时间加 1 年
	DATE_ADD( NOW( ), INTERVAL - 1 DAY ),-- 当前时间减 1 天
	DATE_ADD( NOW( ), INTERVAL '1:30' HOUR_MINUTE ) -- 当前时间减 1 小时 30 分
-- 响应数据
NOW( )	DATE_ADD( NOW( ), INTERVAL 1 DAY )	DATE_ADD( NOW( ), INTERVAL 1 YEAR )	DATE_ADD( NOW( ), INTERVAL - 1 DAY )	DATE_ADD( NOW( ), INTERVAL '1:30' HOUR_MINUTE )
2020-04-20 12:36:14	2020-04-21 12:36:14	2021-04-20 12:36:14	2020-04-19 12:36:14	2020-04-20 14:06:14

1
2
3
4
5
6
7
8
9

# 日期提取： EXTRACT

EXTRACT(unit FROM date)：返回日期 date 的指定部分

-- 提取出年份
SELECT EXTRACT(YEAR FROM '2019-07-02');
        -> 2019
-- 提取出 年月 
SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');
        -> 201907
-- 提取出分钟
SELECT EXTRACT(DAY_MINUTE FROM '2019-07-02 01:02:03');
        -> 20102
SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');
        -> 123

1
2
3
4
5
6
7
8
9
10
11

# UNIX_TIMESTAMP

UNIX_TIMESTAMP(): 返回 unix 时间戳
FROM_UNIXTIME()：把 unix 时间戳转换为日期时间

现在不建议使用他们了。

# 常用的字符串函数

官方文档字符串函数

# 字符串连接 CONCAT & CONCAT_WS

CONCAT(str1,str2,...)：把字符串 str1.. 连成一个字符串
CONCAT_WS(sep,str1,str2..)：用指定的分隔符 sep 链接字符串

-- 出于 seo 优化的目的，需要合并显示课程分类名称和课程标题
SELECT
	CONCAT_WS( ',', b.class_name, a.title ) 
FROM
	imc_course a
	JOIN imc_class b ON a.course_id = b.class_id

-> MySQL,MySQL课程-79889
-> Redis,MySQL课程-69546

1
2
3
4
5
6
7
8
9

# 字符串长度：CHAR_LENGTH

CHAR_LENGTH(str)：返回字符串 str 的字符个数
LENGTH(STR)：返回字符串 str 的字节个数

SELECT
	LENGTH( 'word hello' ),
	CHAR_LENGTH( 'word hello' ),
	LENGTH( 'my 数据库教程' ),
	CHAR_LENGTH( 'my 数据库教程' )

-> 10	10	18	8
-- 可以看到字节和字符在中英字符下还是相差挺多的

1
2
3
4
5
6
7
8

# 数字式化：FORMAT

FORMAT(X,D[,locale])：将数字 N 格式化为格式，如 #,###,###.###，并舍如到 D 位小数

SELECT
	FORMAT( 123456.789, 4 )
	
-> 123,456.7890			-- 格式化为 4 位小数，不够的用 0 补齐

1
2
3
4

# 字符串截取：LEFT/RIGHT

LEFT(str,len)：从字符串的左边起返回 len 长度的子字符串
RIGHT（str,len）：从右边起

SELECT LEFT
	( 'www.baidu.com', 3 ),
	RIGHT ( 'www.baidu.com', 3 )
	
-> www	com

1
2
3
4
5

# 字符串截取：SUBSTRING

SUBSTRING（str,pos[,len]）：从字符串 str 的 pos 位置起返回长度为 len 的子串

SELECT
	SUBSTRING( 'www.baidu.com', 1 ),
	SUBSTRING( 'www.baidu.com', 1, 3 )

-> www.baidu.com	www
-- 位置是正常的正数，不像 JAVA 中的是从 0 开始，还包含头不包含尾。

1
2
3
4
5
6

# 按分隔符截取：SUBSTRING_INDEX

SUBSTRING_INDEX(str,delim,count)：按 delim 分割字符串 str 后，取 count 个子字串

SELECT
	SUBSTRING_INDEX( 'www.baidu.com', '.', 2 ),  -- 正数：从左边起 2 个
	SUBSTRING_INDEX( 'www.baidu.com', '.', - 2 ) -- 负数：从右边起 2 个
	
-> www.baidu	baidu.com

1
2
3
4
5

# 查找字符串出现的位置：LOCATE

LOCATE(substr,str)：在字符串 str 中返回子串 substr 第一次出现的位置

SELECT
	LOCATE( 'b', 'www.baidu.com' ),  -- b 是第 5 个字符
	LOCATE( 'w', 'www.baidu.com' )
	
-> 5	1

1
2
3
4
5

-- 截取课程标题里中横线之前的部分
-- 使用两个函数进行配合

SELECT
	title,
	LOCATE( '-', title ),
	SUBSTRING( title, 1, LOCATE( '-', title ) - 1 ) 
FROM
	imc_course

-> MySQL课程-04045	8	MySQL课程
-- 当然，还可以使用前面提到过的 SUBSTRING_INDEX 等函数

1
2
3
4
5
6
7
8
9
10
11
12

# 两段删除/去空格 TRIM

SELECT TRIM([remstr FROM] str)：从字符串 str 两端删除不需要的字符 remstr，常见用来删除字符串两端的空格

SELECT
	TRIM( '     mrcode  ')
	
-> mrcode

SELECT
	TRIM( 'x' FROM 'xxmrcodexx' )

-> mrcode

1
2
3
4
5
6
7
8
9

# 其他常用函数

ROUND（X,D）：对数值 x 进行四舍五入保留 D 位小数
RAND（）：返回一个在 0 和 1 之间的随机数

前面使用该函数用在了 order by 中，这个是不建议的，因为在大量数据的表中，性能特别差

# 数据流控制：CASE WHIEN ... THEN

用于实现其他语言中的 case.. when 功能，提供数据流控制，语法如下

CASE WHEN [condition]
	 THEN result
	[WEEN [condition] THEN resule ...] 
	[ELSE resule] 
END

1
2
3
4
5

由于是用函数实现的，可以出现在 form 、where 等语句中

-- 显示每个用户的昵称与性别
SELECT
	user_nick,
	sex,
CASE
		
		WHEN sex = 1 THEN
		'男' 
		WHEN sex = 0 THEN
		'女' 
	END AS '性别'  - 取
FROM
	imc_user

->
  user_nick	sex	性别
  沿舟侨	1	男
  窦貌	0	女
 
-- 显示每个用户的昵称与性别，把 case 语句用在 where 子句中
SELECT
	user_nick,
	sex,
CASE
		WHEN sex = 1 THEN
		'男' 
		WHEN sex = 0 THEN
		'女' 
	END AS '性别' 
FROM
	imc_user 
WHERE
CASE
		WHEN sex = 1 THEN
		'男' 
		WHEN sex = 0 THEN
	'女' 
	END = '男'  -- 只查询男

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

# MD5 函数

MD5(str)：返回 str 的 MD5 值

← DML 语句 SQL 高级特性 →