mysql 函数切割机_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;