MySQL 函数
红色标题 TAG 中附有 ORACLE 特殊语句，与 MySQL 中的语句书写方法略有不同
建表语句
PS：NUMERIC 是大多数 DBMS 都支持的一种数据类型，通过 NUMBERIC ( 全体位数 , 小数位数 ) 的形式来指定数值的大小。
-- DDL ：创建表
CREATE TABLE SampleMath
(m NUMERIC (10,3),
n INTEGER,
p INTEGER);
-- NUMERIC(10,3) 和 DECIMAL(10,3) 是等效的， 即 10个有效位数字，3个小数位数字
INSERT INTO SampleMath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO SampleMath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (8.76, NULL, NULL);
-- DDL ：创建表
CREATE TABLE SampleStr
(str1 VARCHAR(40),
str2 VARCHAR(40),
str3 VARCHAR(40);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('opx','rt',NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc','def' ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('山田','太郎' ,'是我');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aaa',NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES (NULL,'xyz',NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('@!#$%',NULL,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('ABC',NULL,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aBC',NULL,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc太郎','abc','ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc','abc','ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('micmic','i','I');
绝对值函数 ABS()
-- 获取 m 的绝对值 m_abs
select m,
abs(m) as m_abs
from samplemath;
求余函数 MOD()
-- n % p 也可以获得结果（SQL-Server 中只支持这种用法）
select n,
p,
mod(n, p)
FROM samplemath;
大小写转换函数 LOWER() & UPPER()
-- 将 str 字符串全部转成小写
SELECT str1,LOWER(str1) FROM samplestr;
-- 将 str 字符串全部转成大写
SELECT str1,UPPER(str1) FROM samplestr;
计算字符串长度函数 LENGTH()
-- SQL-Server 中把 LENGTH 替换成 LEN
SELECT str1,LENGTH(str1) FROM samplestr;
替换字符串函数 REPLACE()
-- REPLACE(对象字符串，替换前的字符串，替换后的字符串)
SELECT str1,str2,str3,REPLACE(str1,str2,str3) AS str1_replace FROM samplestr;
-- 可以观察 ID = 9,10,11 的记录
-- 可以常量替换
SELECT str1,REPLACE(str1,"mic","wtf") AS str1_replace FROM samplestr;
字符串拼接函数 CONCAT() 和 ||
-- ORACLE PostGreSQL 中才能使用的语法
/*
SELECT str1, str2,
str1 || str2 AS str_concat
FROM SampleStr;
SELECT str1, str2, str3,
str1 || str2 || str3 AS str_concat
FROM SampleStr
WHERE str1 = '山田';
*/
-- SQL-SERVER 中可以使用的语法
/*
SELECT str1, str2, str3,
str1 + str2 + str3 AS str_concat
FROM SampleStr;
*/
-- MYSQL 中可以使用的语法
SELECT str1, str2, str3,
CONCAT(str1,str2,str3) AS str_concat
FROM SampleStr;
字符串切割函数 SUBSTRING() & SUBSTR()
-- SUBSTRING函数（PostgreSQL/MySQL专用语法）
-- SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
SELECT str1,SUBSTRING(str1 FROM 3 FOR 2) AS str1_substring
from samplestr;
-- SQL Server 中写法做了简化
SELECT str1,
SUBSTRING(str1, 3, 2) AS sub_str
FROM SampleStr;
-- Oracle 中再次做了简化
SELECT str1,
SUBSTR(str1, 3, 2) AS sub_str
FROM SampleStr;
四舍五入函数 ROUND()
-- 针对 m 数字四舍五入到小数点后 n 位
-- ROUND(对象数值，保留小数的位数)
SELECT m,
n,
ROUND(m, n) AS m_round FROM samplemath;
日期函数 CURRENT_DATE/CURRENT_TIME/CURRENT_TIMESTAMP
-- 当前日期
SELECT CURRENT_DATE;
-- ORACLE 中需要指定临时表 DUAL
SELECT CURRENT_DATE FROM DUAL;
-- 当前时间
SELECT CURRENT_TIME;
-- ORACLE 中需要指定临时表 DUAL
SELECT CURRENT_TIME FROM DUAL;
-- CURRENT_TIMESTAMP 函数 = CURRENT_DATE + CURRENT_TIME
SELECT CURRENT_TIMESTAMP;
-- ORACLE 中需要指定临时表 DUAL
SELECT CURRENT_TIMESTAMP FROM DUAL;
日期切割函数 EXTRACT()
-- 使用 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;
-- 同样地， ORACLE 中需要指定临时表 DUAL
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
FROM DUAL;
NULL 转换函数 COALESCE(),IFNULL(),NVL()
NULL 转换函数，即将值为 NULL 的值转换为指定值
COALESCE 函数在流行的 RDBMS 中都带有， IFNULL() 是 MySQL 自带的简化版函数，NVL 是 ORACLE 自带的简化版函数
-- 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;
-- 如果 str2 字段值为 NULL 那么将其置为 0
SELECT str2,COALESCE(str2,"0") AS str2_coalesce
FROM SampleStr;
-- 上一条语句可以用 IFNULL 函数替换
SELECT str2,IFNULL(str2,"0") AS str2_coalesce
FROM SampleStr;
-- 如果 str1 为 NULL 置为 str2，如果 str2 为 NULL 置为 str1，如果都为 NULL 置为 0
SELECT str1,str2,COALESCE(str1,str2,'0') AS str1_str2_coalesce
FROM SampleStr;
-- COALESCE 函数的逻辑是，CLALESCE(value1,value2,value3,....) 其中可以包含无限个值
-- 但是会获取第一个非 NULL 的值作为该函数的返回值
-- 相比 IFNULL 函数，COALESCE 函数的可用性更强，但 IFNULL 函数更加简化
-- ORACLE 中与 IFNULL 函数相对应的是 NVL 函数
类型转换函数 CAST()
-- SQL Server PostgreSQL
-- SELECT CAST('0001' AS INTEGER) AS int_col;
-- MySQL 需要加 SIGNED
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
-- Oracle
-- SELECT CAST('0001' AS INTEGER) AS int_col FROM DUAL;
-- 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;
记录成长过程
