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;