MYSQL学习12

参考MICK的SQL基础教程，进行SQL的基础学习。

函数

算术函数

+、-、*、/

绝对值(ABS)

SELECT
	m,
	ABS(m) AS abs_col
FROM
	sampleMath

求余(MOD)

SELECT
	n,
	p,
	MOD (n, p) AS mod_col
FROM
	sampleMath
WHERE
	n IS NOT NULL
AND p IS NOT NULL

四舍五入(ROUND)

SELECT
	m,
	n,
	ROUND(m, n) AS ROUND_col
FROM
	samplemath
WHERE
	m AND n IS NOT NULL

字符串函数

拼接(CONCAT)

SELECT
	str1,
	str2,
	str3,
	CONCAT(str1, str2, str3) AS str_concat
FROM
	sampleStr

字符串长度(LENGTH)

SELECT
	str1,
	LENGTH(str1)
FROM
	sampleStr

小写转换(LOWER)

SELECT
	str1,
	LOWER(str1)
FROM
	sampleStr

字符串的替换(REPLACE)

REPLACE(对象字符串，替换前的字符串，替换后的字符串)

SELECT
 str1,
 str2,
 str3,
 REPLACE (str1, str2, str3) AS rep_str
FROM
 SampleStr;

字符串的截取(SUBSTRING)

SELECT
	str1,
	SUBSTRING(str1 FROM 1 FOR 3)
FROM
	SampleStr

注：从1开始。

日期函数(EXTRACT)

SELECT 
CURRENT_TIMESTAMP,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second

转换函数

CAST函数

SELECT
	n,
	CAST(n AS SIGNED INTEGER) AS int_col
FROM
	samplemath1

COALESCE函数

将NULL转换为其他值

SELECT
	COALESCE (NULL, 1) AS col_1,
	COALESCE (NULL, 'test', NULL) AS col_2,
	COALESCE (NULL, NULL, '2009-11-01') AS col_3;

注：COALESCE语法说明：
COALESCE(x,y,z) = x if x is not NULL
COALESCE(x,y,z) = y if x is NULL and y is not NULL
COALESCE(x,y,z) = z if x and y are NULL but z is not NULL
COALESCE(x,y,z) = NULL if x and y and z are all NULL