SQL 常用的函數：算術函數、字元串函數、日期函數和轉換函數

来源:https://www.cnblogs.com/vin-c/archive/2022/06/10/16363829.html
-Advertisement-
Play Games

不僅 SQL，對所有的編程語言來說，函數都起著至關重要的作用。函數就像是編程語言的“道具箱”，每種編程語言都準備了非常多的函數。 使用函數，我們可以實現計算、字元串操作、日期計算等各種各樣的運算。 本文重點 根據用途，函數可以大致分為算術函數、字元串函數、日期函數、轉換函聚合函數。 函數的種類很多， ...


目錄

不僅 SQL，對所有的編程語言來說，函數都起著至關重要的作用。函數就像是編程語言的“道具箱”，每種編程語言都準備了非常多的函數。

使用函數，我們可以實現計算、字元串操作、日期計算等各種各樣的運算。

本文重點

  • 根據用途，函數可以大致分為算術函數、字元串函數、日期函數、轉換函聚合函數。

  • 函數的種類很多，無需全都記住，只需要記住具有代表性的函數就可以了，其他的可以在使用時再進行查詢。

一、函數的種類

前幾篇和大家一起學習了 SQL 的語法結構等必須要遵守的規則。本文將會進行一點改變，來學習一些 SQL 自帶的便利工具——函數。

我們在 SQL 如何對錶進行聚合和分組查詢並對查詢結果進行排序 中已經學習了函數的概念，這裡再回顧一下。所謂函數，就是輸入某一值得到相應輸出結果的功能，輸入值稱為參數（parameter），輸出值稱為返回值。

函數大致可以分為以下幾種。

  • 算術函數（用來進行數值計算的函數）

  • 字元串函數（用來進行字元串操作的函數）

  • 日期函數（用來進行日期操作的函數）

  • 轉換函數（用來轉換數據類型和值的函數）

  • 聚合函數（用來進行數據聚合的函數）

我們已經在 SQL 如何對錶進行聚合和分組查詢並對查詢結果進行排序 中學習了聚合函數的相關內容，大家應該對函數有初步的瞭解了吧。

聚合函數基本上只包含 COUNT、SUM、AVG、MAX、MIN 這 5 種，而其他種類的函數總數則超過 200 種。

可能大家會覺得怎麼會有那麼多函數啊，但其實並不需要擔心，雖然數量眾多，但常用函數只有 30 ~ 50 個。不熟悉的函數大家可以查閱參考文檔（詞典）來瞭解 [1]。

本文我們將學習一些具有代表性的函數。大家並不需要一次全部記住，只需要知道有這樣的函數就可以了，實際應用時可以查閱參考文檔。

接下來，讓我們來詳細地看一看這些函數。

二、算術函數

算術函數是最基本的函數，其實之前我們已經學習過了，可能有些讀者已經想起來了。沒錯，就是 算術運算符和比較運算符 介紹的加減乘除四則運算。

  • +（加法）

  • -（減法）

  • *（乘法）

  • /（除法）

由於這些算術運算符具有“根據輸入值返回相應輸出結果”的功能，因此它們是出色的算術函數。在此我們將會給大家介紹除此之外的具有代表性的函數。

為了學習算術函數，我們首先根據代碼清單 1 創建一張示例用表（SampleMath）。

NUMERIC 是大多數 DBMS 都支持的一種數據類型，通過 NUMBERIC ( 全體位數, 小數位數 ) 的形式來指定數值的大小。

接下來，將會給大家介紹常用的算術函數——ROUND 函數，由於 PostgreSQL 中的 ROUND 函數只能使用 NUMERIC 類型的數據，因此我們在示例中也使用了該數據類型。

代碼清單 1 創建 SampleMath 表

-- DDL ：創建表
CREATE TABLE SampleMath
(m  NUMERIC (10,3),
 n  INTEGER,
 p  INTEGER);

SQL Server PostgreSQL

-- DML ：插入數據
BEGIN TRANSACTION; -----①

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);

COMMIT;

特定的 SQL

不同的 DBMS 事務處理的語法也不盡相同。代碼清單 1 中的 DML 語句在 MySQL 中執行時，需要將 ① 部分更改為“STARTTRANSACTION;”，在 Oracle 和 DB2 中執行時，無需用到 ① 的部分（請刪除）。

詳細內容請大家參考 什麼是 SQL 事務 中的“創建事務”。

下麵讓我們來確認一下創建好的表中的內容，其中應該包含了 m、n、p 三列。

SELECT * FROM SampleMath;

執行結果：

    m    | n | p
---------+---+--
 500.000 | 0 |
-180.000 | 0 |
         |   |
         | 7 | 3
         | 5 | 2
         | 4 |
   8.000 |   | 3
   2.270 | 1 |
   5.555 | 2 |
         | 1 |
   8.760 |   |

2.1 ABS——絕對值

語法 1 ABS 函數

ABS(數值)

ABS 是計算絕對值的函數。絕對值（absolute value）不考慮數值的符號，表示一個數到原點的距離。

簡單來講，絕對值的計算方法就是：0 和正數的絕對值就是其本身，負數的絕對值就是去掉符號後的結果。

代碼清單 2 計算數值的絕對值

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

執行結果：

執行結果

右側的 abs_col 列就是通過 ABS 函數計算出的 m 列的絕對值。請大家註意，-180 的絕對值就是去掉符號後的結果 180。

通過上述結果我們可以發現，ABS 函數的參數為 NULL 時，結果也是 NULL。並非只有 ABS 函數如此，其實絕大多數函數對於 NULL 都返回 NULL [2]。

2.2 MOD——求餘

語法 2 MOD 函數

MOD(被除數，除數)

MOD 是計算除法餘數（求餘）的函數，是 modulo 的縮寫。例如，7/3 的餘數是 1，因此 MOD(7, 3) 的結果也是 1（代碼清單 3）。

因為小數計算中並沒有餘數的概念，所以只能對整數類型的列使用 MOD 函數。

代碼清單 3 計算除法（n ÷ p）的餘數

Oracle DB2 PostgreSQL MySQL

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

執行結果：

 n | p | mod_col
---+---+--------
 0 |   |
 0 |   |
   |   |
 7 | 3 |    1
 5 | 2 |    1
 4 |   |
   | 3 |
 1 |   |
 2 |   |
 1 |   |
   |   |

這裡有一點需要大家註意：主流的 DBMS 都支持 MOD 函數，只有 SQL Server 不支持該函數。

特定的 SQL

SQL Server 使用特殊的運算符（函數）“%”來計算餘數，使用如下的專用語法可以得到與代碼清單 3 相同的結果。需要使用 SQL Server 的讀者需要特別註意。

SQL Server

SELECT n, p,
      n % p AS mod_col
FROM SampleMath;

2.3 ROUND——四捨五入

語法 3 ROUND 函數

ROUND(對象數值，保留小數的位數)

ROUND 函數用來進行四捨五入操作。四捨五入在英語中稱為 round。

如果指定四捨五入的位數為 1，那麼就會對小數點第 2 位進行四捨五入處理。如果指定位數為 2，那麼就會對第 3 位進行四捨五入處理（代碼清單 4）。

代碼清單 4 對 m 列的數值進行 n 列位數的四捨五入處理

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

執行結果：

    m    | n | round_col
---------+---+----------
 500.000 | 0 |       500
-180.000 | 0 |      -180
         |   |
         | 7 |
         | 5 |
         | 4 |
   8.000 |   |
   2.270 | 1 |       2.3
   5.555 | 2 |      5.56
         | 1 |
   8.760 |   |

三、字元串函數

截至目前，我們介紹的函數都是主要針對數值的算術函數，但其實算術函數只是 SQL（其他編程語言通常也是如此）自帶的函數中的一部分。

雖然算術函數是我們經常使用的函數，但是字元串函數也同樣經常被使用。

在日常生活中，我們經常會像使用數字那樣，對字元串進行替換、截取、簡化等操作，因此 SQL 也為我們提供了很多操作字元串的功能。

為了學習字元串函數，我們再來創建一張表（SampleStr），參見代碼清單 5。

代碼清單 5 創建 SampleStr 表

-- DDL ：創建表
CREATE TABLE SampleStr
(str1   VARCHAR(40),
 str2   VARCHAR(40),
 str3   VARCHAR(40));

SQL Server PostgreSQL

-- DML ：插入數據
BEGIN TRANSACTION; -------------①

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');

COMMIT;

特定的 SQL

不同的 DBMS 事務處理的語法也不盡相同。代碼清單 5 中的 DML 語句在 MySQL 中執行時，需要將 ① 部分更改為“START TRANSACTION;”。在 Oracle 和 DB2 中執行時，無需用到 ① 的部分（請刪除）。

詳細內容請大家參考 什麼是 SQL 事務 中的“創建事務”。

下麵讓我們來確認一下創建好的表中的內容，其中應該包含了 str1、str2、str3 三列。

SELECT * FROM SampleStr;

執行結果：

   str1    | str2 | str3
-----------+------+-----
 opx       | rt   |
 abc       | def  |
 山田      | 太郎  | 是我
 aaa       |      |
           | xyz  |
 @!#$%     |      |
 ABC       |      |
 aBC       |      |
 abc太郎   | abc  | ABC
 abcdefabc | abc  | ABC
 micmic    | i    | I

3.1 ||——拼接

語法 4 || 函數

字元串1||字元串2

在實際業務中，我們經常會碰到 abc + de = abcde 這樣希望將字元串進行拼接的情況。在 SQL 中，可以通過由兩條併列的豎線變換而成的“||”函數來實現（代碼清單 6）。

代碼清單 6 拼接兩個字元串（str1+str2）

Oracle DB2 PostgreSQL

SELECT str1, str2,
       str1 || str2 AS str_concat
  FROM SampleStr;

執行結果：

   str1    | str2 | str_concat
-----------+------+------------
 opx       | rt   | opxrt
 abc       | def  | abcdef
 山田      | 太郎  | 山田太郎
 aaa       |      |
           | xyz  |
 @!#$%     |      |
 ABC       |      |
 aBC       |      |
 abc太郎   | abc  | abc太郎abc
 abcdefabc | abc  | abcdefabcabc
 micmic    | i    | micmaci

進行字元串拼接時，如果其中包含 NULL，那麼得到的結果也是 NULL。這是因為“||”也是變了形的函數。當然，三個以上的字元串也可以進行拼接（代碼清單 7）。

代碼清單 7 拼接三個字元串（str1+str2+str3）

Oracle DB2 PostgreSQL

SELECT str1, str2, str3,
       str1 || str2 || str3 AS str_concat
  FROM SampleStr
 WHERE str1 = '山田';

執行結果：

 str1 | str2 | str3 | str_concat
------+------+------+-----------
 山田 | 太郎 | 是我 | 山田太郎是我

這裡也有一點需要大家註意，|| 函數在 SQL Server 和 MySQL 中無法使用。

特定的 SQL

SQL Server 使用“+”運算符（函數）來連接字元串。MySQL 使用 CONCAT 函數來完成字元串的拼接。

使用如下 SQL Server/MySQL 的專用語法能夠得到與代碼清單 7 相同的結果。另外，在 SQL Server 2012 及其之後的版本中也可以使用 CONCAT 函數。

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;

3.2 LENGTH——字元串長度

語法 5 LENGTH 函數

LENGTH(字元串)

想要知道字元串中包含多少個字元時，可以使用 LENGTH（長度）函數（代碼清單 8）。

代碼清單 8 計算字元串長度

Oracle DB2 PostgreSQL MySQL

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

執行結果：

   str1    | len_str
-----------+--------
 opx       |    3
 abc       |    3
 山田      |    2
 aaa       |    3
           |
 @!#$%     |    5
 ABC       |    3
 aBC       |    3
 abc太郎   |    5
 abcdefabc |    9
 micmic    |    6

需要註意的是，該函數也無法在 SQL Server 中使用。

特定的 SQL

SQL Server 使用 LEN 函數來計算字元串的長度。使用如下 SQL Server 的專用語法能夠得到與代碼清單 8 相同的結果。

SQL Server

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

我想大家應該逐漸明白“SQL 中有很多特定的用法”這句話的含義了吧。

專欄

對 1 個字元使用 LENGTH 函數有可能得到 2 位元組以上的結果

LENGTH 函數中，還有一點需要大家特別註意，那就是該函數究竟以什麼為單位來計算字元串的長度。這部分是初級以上階段才會學習到的內容，在此先簡單介紹一下。

可能有些讀者已經有所瞭解，與半形英文字母占用 1 位元組不同，漢字這樣的全形字元會占用 2 個以上的位元組（稱為多位元組字元）。

因此，使用 MySQL 中的 LENGTH 這樣以位元組為單位的函數進行計算時，“LENGTH(山田)”的返回結果是 4。同樣是 LENGTH 函數，不同 DBMS 的執行結果也不盡相同。

3.3 LOWER——小寫轉換

語法 6 LOWER 函數

LOWER(字元串)

LOWER 函數只能針對英文字母使用，它會將參數中的字元串全都轉換為小寫（代碼清單 9）。

因此，該函數並不適用於英文字母以外的場合。此外，該函數並不影響原本就是小寫的字元。

代碼清單 9 大寫轉換為小寫

SELECT str1,
       LOWER(str1) AS low_str
  FROM SampleStr
 WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');

執行結果：

 str1 | low_str
------+--------
 abc  | abc
 山田 | 山田
 ABC  | abc
 aBC  | abc

既然存在小寫轉換函數，那麼肯定也有大寫轉換函數，UPPER 就是大寫轉換函數。

3.4 REPLACE——字元串的替換

語法 7 REPLACE 函數

REPLACE(對象字元串，替換前的字元串，替換後的字元串)

使用 REPLACE 函數，可以將字元串的一部分替換為其他的字元串（代碼清單 10）。

代碼清單 10 替換字元串的一部分

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

執行結果：

   str1    | str2 | str3 | rep_str
-----------+------+------+---------
 opx       | rt   |      |
 abc       | def  |      |
 山田      | 太郎  | 是我 | 山田
 aaa       |      |      |
           | xyz  |      |
 @!#$%     |      |      |
 ABC       |      |      |
 aBC       |      |      |
 abc太郎   | abc  | ABC  | ABC太郎
 abcdefabc | abc  | ABC  | ABCdefABC
 micmic    | i    | I    | mIcmIc

3.5 SUBSTRING——字元串的截取

語法 8 SUBSTRING 函數（PostgreSQL/MySQL 專用語法）

SUBSTRING（對象字元串 FROM 截取的起始位置 FOR 截取的字元數）

使用 SUBSTRING 函數可以截取出字元串中的一部分字元串（代碼清單 11）。截取的起始位置從字元串最左側開始計算 [3]。

代碼清單 11 截取出字元串中第 3 位和第 4 位的字元

PostgreSQL MySQL

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

執行結果：

   str1    | sub_str
-----------+--------
 opx       | x
 abc       | c
 山田      |
 aaa       | a
           |
 @!#$%     | #$
 ABC       | C
 aBC       | C
 abc太郎   | c太
 abcdefabc | cd
 micmic    | cm

雖然上述 SUBSTRING 函數的語法是標準 SQL 承認的正式語法，但是現在只有 PostgreSQL 和 MySQL 支持該語法。

特定的 SQL

SQL Server 將語法 a 中的內容進行了簡化（語法 b）。

語法 a SUBSTRING 函數（SQL Server 專用語法）

SUBSTRING(對象字元串，截取的起始位置，截取的字元數)

Oracle 和 DB2 將該語法進一步簡化，得到瞭如下結果。

語法 b SUBSTR 函數（Oracle/DB2 專用語法）

SUBSTR(對象字元串，截取的起始位置，截取的字元數)

SQL 有這麼多特定的語法，真是有些讓人頭疼啊。各 DBMS 中能夠得到與代碼清單 11 相同結果的專用語法如下所示。

SQL Server

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

Oracle DB2

SELECT str1,
      SUBSTR(str1, 3, 2) AS sub_str
 FROM SampleStr;

3.6 UPPER——大寫轉換

語法 9 UPPER 函數

UPPER(字元串)

UPPER 函數只能針對英文字母使用，它會將參數中的字元串全都轉換為大寫（代碼清單 12）。

因此，該函數並不適用於英文字母以外的情況。此外，該函數並不影響原本就是大寫的字元。

代碼清單 12 將小寫轉換為大寫

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

執行結果：

 str1 | up_str
------+--------
 abc  | ABC
 山田 | 山田
 ABC  | ABC
 aBC  | ABC

與之相對，進行小寫轉換的是 LOWER 函數。

四、日期函數

雖然 SQL 中有很多日期函數，但是其中大部分都依存於各自的 DBMS，因此無法統一說明 [4]。本節將會介紹那些被標準 SQL 承認的可以應用於絕大多數 DBMS 的函數。

4.1 CURRENT_DATE——當前日期

語法 10 CURRENT_DATE 函數

CURRENT_DATE

CURRENT_DATE 函數能夠返回 SQL 執行的日期，也就是該函數執行時的日期。由於沒有參數，因此無需使用括弧。

執行日期不同，CURRENT_DATE 函數的返回值也不同。如果在 2009 年 12 月 13 日執行該函數，會得到返回值“2009-12-13”。如果在 2010 年 1 月 1 日執行，就會得到返回值“2010-01-01”（代碼清單 13）。

代碼清單 13 獲得當前日期

SELECT CURRENT_DATE;

執行結果：

    date
------------
 2016-05-25

該函數無法在 SQL Server 中執行。此外，Oracle 和 DB2 中的語法略有不同。

特定的 SQL

SQL Server 使用如下的 CURRENT_TIMESTAMP（後述）函數來獲得當前日期。

SQL Server

-- 使用CAST（後述）函數將CURRENT_TIMESTAMP轉換為日期類型
SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;

執行結果：

  CUR_DATE
  ----------
  2010-05-25

在 Oracle 中使用該函數時，需要在 FROM 子句中指定臨時表（DUAL）。

而在 DB2 中使用時，需要在 CRUUENT 和 DATE 之間添加半形空格，並且還需要指定臨時表 SYSIBM.SYSDUMMY1（相當於 Oracle 中的 DUAL）。

這些容易混淆的地方請大家多加註意。

Oracle

SELECT CURRENT_DATE
FROM dual;

DB2

SELECT CURRENT DATE
FROM SYSIBM.SYSDUMMY1;

4.2 CURRENT_TIME——當前時間

語法 11 CURRENT_TIME 函數

CURRENT_TIME

CURRENT_TIME 函數能夠取得 SQL 執行的時間，也就是該函數執行時的時間（代碼清單 14）。由於該函數也沒有參數，因此同樣無需使用括弧。

代碼清單 14 取得當前時間

PostgreSQL MySQL

SELECT CURRENT_TIME;

執行結果：

    timetz
-----------------
17:26:50.995+09

該函數同樣無法在 SQL Server 中執行，在 Oracle 和 DB2 中的語法同樣略有不同。

特定的 SQL

SQL Server 使用如下的 CURRENT_TIMESTAMP 函數（後述）來獲得當前日期。

-- 使用CAST函數（後述）將CURRENT_TIMESTAMP轉換為時間類型
SELECT CAST(CURRENT_TIMESTAMP AS TIME) AS CUR_TIME;

執行結果：

  CUR_TIME
  ----------------
  21:33:59.3400000

在 Oracle 和 DB2 中使用時的語法如下所示。需要註意的地方和 CURRENT_DATE 函數相同。在 Oracle 中使用時所得到的結果還包含日期。

Oracle

-- 指定臨時表（DUAL）
SELECT CURRENT_TIMESTAMP
 FROM dual;

DB2

/* CURRENT和TIME之間使用了半形空格，指定臨時表SYSIBM.SYSDUMMY1 */
SELECT CURRENT TIME
 FROM SYSIBM.SYSDUMMY1;

4.3 CURRENT_TIMESTAMP——當前日期和時間

語法 12 CURRENT_TIMESTAMP 函數

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP 函數具有 CURRENT_DATE + CURRENT_TIME 的功能。使用該函數可以同時得到當前的日期和時間，當然也可以從結果中截取日期或者時間。

代碼清單 15 取得當前日期和時間

SQL Server PostgreSQL MySQL

SELECT CURRENT_TIMESTAMP;

執行結果：

           now
---------------------------
2016-04-25 18:31:03.704+09

該函數可以在 SQL Server 等各個主要的 DBMS 中使用 [5]。但是，與之前的 CURRENT_DATE 和 CURRENT_TIME 一樣，在 Oracle 和 DB2 中該函數的語法略有不同。

特定的 SQL

Oracle 和 DB2 使用如下寫法可以得到與代碼清單 15 相同的結果。其中需要註意的地方與 CURRENT_DATE 時完全相同。

Oracle

-- 指定臨時表（DUAL）
SELECT CURRENT_TIMESTAMP
 FROM dual;

DB2

/* CURRENT和TIME之間使用了半形空格，指定臨時表SYSIBM.SYSDUMMY1 */
SELECT CURRENT TIMESTAMP
 FROM SYSIBM.SYSDUMMY1;

4.4 EXTRACT——截取日期元素

語法 13 EXTRACT 函數

EXTRACT(日期元素 FROM 日期)

使用 EXTRACT 函數可以截取出日期數據中的一部分，例如“年”“月”，或者“小時”“秒”等（代碼清單 16）。該函數的返回值並不是日期類型而是數值類型。

代碼清單 16 截取日期元素

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;

執行結果：

           now             | year | month | day | hour | minute | second
---------------------------+------+-------+-----+------+--------+-------
2010-04-25 19:07:33.987+09 | 2010 |     4 |  25 |   19 |      7 | 33.987

需要註意的是 SQL Server 也無法使用該函數。

特定的 SQL

SQL Server 使用如下的 DATEPART 函數會得到與代碼清單 16 相同的結果。

SQL Server

SELECT CURRENT_TIMESTAMP,
      DATEPART(YEAR   , CURRENT_TIMESTAMP) AS year,
      DATEPART(MONTH  , CURRENT_TIMESTAMP) AS month,
      DATEPART(DAY    , CURRENT_TIMESTAMP) AS day,
      DATEPART(HOUR   , CURRENT_TIMESTAMP) AS hour,
      DATEPART(MINUTE , CURRENT_TIMESTAMP) AS minute,
      DATEPART(SECOND , CURRENT_TIMESTAMP) AS second;

Oracle 和 DB2 想要得到相同結果的話，需要進行如下改變。註意事項與 CURRENT_DATE 時完全相同。

Oracle

-- 在FROM子句中指定臨時表（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;

DB2

/* CURRENT和TIME之間使用了半形空格，指定臨時表SYSIBM.SYSDUMMY1 */
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 SYSIBM.SYSDUMMY1;

五、轉換函數

最後將要給大家介紹一類比較特殊的函數——轉換函數。雖說有些特殊，但是由於這些函數的語法和之前介紹的函數類似，數量也比較少，因此很容易記憶。

“轉換”這個詞的含義非常廣泛，在 SQL 中主要有兩層意思：一是數據類型的轉換，簡稱為類型轉換，在英語中稱為 cast [6] ；另一層意思是值的轉換。

5.1 CAST——類型轉換

語法 14 CAST 函數

CAST（轉換前的值 AS 想要轉換的數據類型）

進行類型轉換需要使用 CAST 函數。

之所以需要進行類型轉換，是因為可能會插入與表中數據類型不匹配的數據，或者在進行運算時由於數據類型不一致發生了錯誤，又或者是進行自動類型轉換會造成處理速度低下。

這些時候都需要事前進行數據類型轉換（代碼清單 17、代碼清單 18）。

代碼清單 17 將字元串類型轉換為數值類型

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
  FROM DUAL;

DB2

SELECT CAST('0001' AS INTEGER) AS int_col
  FROM SYSIBM.SYSDUMMY1;

執行結果：

int_col
---------
      1

代碼清單 18 將字元串類型轉換為日期類型

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;

DB2

SELECT CAST('2009-12-14' AS DATE) AS date_col
  FROM SYSIBM.SYSDUMMY1;

執行結果：

 date_col
------------
2009-12-14

從上述結果可以看到，將字元串類型轉換為整數類型時，前面的“000”消失了，能夠切實感到發生了轉換。

但是，將字元串轉換為日期類型時，從結果上並不能看出數據發生了什麼變化，理解起來也比較困難。

從這一點我們也可以看出，類型轉換其實並不是為了方便用戶使用而開發的功能，而是為了方便 DBMS 內部處理而開發的功能。

5.2 COALESCE——將 NULL 轉換為其他值

語法 15 COALESCE 函數

COALESCE(數據1，數據2，數據3……)

COALESCE 是 SQL 特有的函數。該函數會返回可變參數 [7] 中左側開始第 1 個不是 NULL 的值。參數個數是可變的，因此可以根據需要無限增加。

其實轉換函數的使用還是非常頻繁的。在 SQL 語句中將 NULL 轉換為其他值時就會用到轉換函數（代碼清單 19、代碼清單 20）。

就像之前我們學習的那樣，運算或者函數中含有 NULL 時，結果全都會變為 NULL。能夠避免這種結果的函數就是 COALESCE。

代碼清單 19 將 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;

DB2

SELECT COALESCE(NULL, 1)                  AS col_1,
       COALESCE(NULL, 'test', NULL)       AS col_2,
       COALESCE(NULL, NULL, '2009-11-01') AS col_3
  FROM SYSIBM.SYSDUMMY1;

執行結果：

 col_1 | col_2 |    col_3
-------+-------+-----------
     1 |  test | 2009-11-01

代碼清單 20 使用 SampleStr 表中的列作為例子

SELECT COALESCE(str2, 'NULL')
  FROM SampleStr;

執行結果：

 coalesce
----------
 rt
 def
 太郎
 'NULL'
 xyz
 'NULL'
 'NULL'
 'NULL'
 abc
 abc
 i

這樣，即使包含 NULL 的列，也可以通過 COALESCE 函數轉換為其他值之後再應用到函數或者運算當中，這樣結果就不再是 NULL 了。

此外，多數 DBMS 中都提供了特有的 COALESCE 的簡化版函數（如 Oracle 中的 NVL 等），但由於這些函數都依存於各自的 DBMS，因此還是推薦大家使用通用的 COALESCE 函數。

原文鏈接：https://www.developerastrid.com/sql/sql-commonly-used-functions/

（完）

  1. 參考文檔是 DBMS 手冊的一部分。大家也可以從介紹各種函數的書籍以及 Web 網站上獲取相關信息。 ↩︎

  2. 但是轉換函數中的 COALESCE 函數除外。 ↩︎

  3. 需要大家註意的是，該函數也存在和 LENGTH 函數同樣的多位元組字元的問題。詳細內容請大家參考專欄“對 1 個字元使用 LENGTH 函數有可能得到 2 位元組以上的結果”。 ↩︎

  4. 如果想要瞭解日期函數的詳細內容，目前只能查閱各個 DBMS 的手冊。 ↩︎

  5. 之前我們已經介紹過，在 SQL Server 中無法使用 CURRENT_DATE 和 CURRENT_TIME 函數。可能是因為在 SQL Server 中，CURRENT_TIMESTAMP 已經涵蓋了這兩者的功能吧。 ↩︎

  6. 類型轉換在一般的編程語言中也會使用，因此並不是 SQL 特有的功能。 ↩︎

  7. 參數的個數並不固定，可以自由設定個數的參數。 ↩︎


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 【SignalR全套系列】之在.Net Core 中實現SignalR實時通信
    【SignalR全套系列】之在.Net Core 中實現SignalR實時通信 ...
  • Linux Cgroup v1(中文翻譯)(5)：cgroup freezer
    Cgroup Freezer cgroup freezer對於批量啟動和停止任務集合的任務管理系統來說是很有用的，這個程式經常被用在HPC族上來調度訪問。cgroup freezer使用cgroups來描述被批處理任務管理系統啟動和停止的任務集合。他也提供了方法來啟動和停止任務。 cgroup fr ...
  • Linux Cgroup v1(中文翻譯)(4)：Block IO Controller
    Block IO Controller 1 概覽 cgroup子系統blkio實現了block io控制器。無論是對存儲結構上的葉子節點和還是中間節點，它對各種IO控制策略(proportional BW, max BW)都是必須的。設計規劃就是使用同樣的cgroup，基於blkio控制器的管理介面 ...
  • 虛擬機ping不通的幾種原因及解決辦法
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 一：虛擬機宿主機互ping不通 問題一：防火牆 略去，建議主機和宿主機都關閉防火牆，並關閉seLinux（Linux的安全系統） 問題二：網卡未生效 表現 輸入命令 ifcongig，若輸出的網卡信息不含inet [ip地址]，則說明網卡未生效 ...
  • ubuntu apt-get install xxx報錯無法下載的解決方法
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 由於我使用ubuntu20.04的火狐瀏覽器時，總是播放不了視頻。說是要下載Flash，但是我順著網址進去，發現並沒有linux版本的（也可能是我沒找到而已？）。於是一直放著沒管，看不了就看不了，真要看我就用筆記本的win10看好了。但是偶爾看到 ...
  • 操作系統實現：malloc 與 堆 實現
    本文參考書：操作系統真像還原 什麼是malloc？ malloc 是用戶態申請記憶體時使用的函數。 malloc在哪裡申請？ 堆中。 什麼是堆？ 程式運行過程中需要申請額外的記憶體都會在堆中分配，堆中的記憶體分為幾個規格類型的塊用鏈表保存，程式需要記憶體就分配一個大於等於所需記憶體大小的塊。如果一個規格的塊用 ...
  • 資料庫概述
    資料庫概述 資料庫的概念 名稱 簡稱 資料庫 DataBase（DB） 資料庫管理系統 DataBase Management System（DBMS） SQL Structured Query Language（SQL） MySQL的啟動、停止 啟動： net start mysql80 停止： ...
  • SqlServer中的鎖
    近十年來，中國基礎軟體發展勢頭迅猛，市場前景看高，越來越多的企業也正在進行基礎軟體升級。那中國基礎軟體行業目前在國際市場上有什麼優勢，面臨哪些困境，以及未來基礎軟體行業會如何發展呢？騰訊雲資料庫邀請沙利文中國高級分析師胡竣傑、華雲中盛資料庫事業部總經理楊光、中軟國際資料庫業務總監範利軍及騰訊雲資料庫 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類