SQL之函数

算数函数

（本文主要针对MySQL而写）

■ABS——绝对值

SELECT m,ABS(m) AS abs_col FROM SampleMath;

■MOD——求余

SELECT n, p, MOD(n, p) AS mod_col FROM SampleMath;

■ROUND——四舍五入

SELECT m, n, ROUND(m, n) AS round_col FROM SampleMath;

字符串函数

■||——拼接

Oracle DB2 PostgreSQL
SELECT str1, str2, str1 | | str2 AS str_concat FROM SampleStr;
SQL Server
SELECT str1, str2, str3, str1 + str2 + str3 AS str_concat FROM SampleStr;
MySQL SQL Server 2012 及之后SELECT
str1, str2, str3, CONCAT(str1, str2, str3) AS str_concat FROM SampleStr;

■LENGTH——字符串长度

SELECT str1, LENGTH(str1) AS len_str FROM SampleStr;

■LOWER——小写转换

SELECT str1, LOWER(str1) AS low_str

■REPLACE——字符串的替换

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

■SUBSTRING——字符串的截取

PostgreSQL MySQL
SELECT str1, SUBSTRING(str1 FROM 3 FOR 2) AS sub_str FROM SampleStr;

■UPPER——大写转换

SELECT str1, UPPER(str1) AS up_str FROM SampleStr WHERE str1 IN (‘ABC’, ‘aBC’, ‘abc’, ‘山田’);

日期函数

■CURRENT_DATE——当前日期

SELECT CURRENT_DATE;

■CURRENT_TIME——当前时间

SELECT CURRENT_TIME;

■CURRENT_TIMESTAMP——当前日期和时间

SELECT CURRENT_TIMESTAMP;
Oracle
– 指定临时表（DUAL）
SELECT CURRENT_TIMESTAMP FROM dual;

■EXTRACT——截取日期元素

PostgreSQL MySQL
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——类型转换

SQL Server PostgreSQL
SELECT CAST(‘0001’ AS INTEGER) AS int_col;
MySQL
SELECT CAST(‘0001’ AS SIGNED INTEGER) AS int_col;
Oracle
SELECT CAST(‘0001’ AS INTEGER) AS int_col

将字符串类型转换为日期类型
SQL Server PostgreSQL MySQL
SELECT CAST(‘2009-12-14’ AS DATE) AS date_col;
Oracle
SELECT CAST(‘2009-12-14’ AS DATE) AS date_col
FROM DUAL;

■COALESCE——将NULL转换为其他值

SQL Server PostgreSQL MySQL
SELECT COALESCE(NULL, 1) AS col_1, COALESCE(NULL, ‘test’, NULL) AS col_2, COALESCE(NULL, NULL, ‘2009-11-01’) AS col_3;
Oracle
SELECT COALESCE(NULL, 1) AS col_1, COALESCE(NULL, ‘test’, NULL) AS col_2, COALESCE(NULL, NULL, ‘2009-11-01’) AS col_3
FROM DUAL;