MYSQL学习_函数
参考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