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

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

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


目錄

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

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

本文重點

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

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

一、函數的種類

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

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

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

  • 算術函數(用來進行數值計算的函數)

  • 字元串函數(用來進行字元串操作的函數)

  • 日期函數(用來進行日期操作的函數)

  • 轉換函數(用來轉換數據類型和值的函數)

  • 聚合函數(用來進行數據聚合的函數)

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

聚合函數基本上只包含 COUNTSUMAVGMAXMIN 這 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 事務 中的“創建事務”。

下麵讓我們來確認一下創建好的表中的內容,其中應該包含了 mnp 三列。

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 事務 中的“創建事務”。

下麵讓我們來確認一下創建好的表中的內容,其中應該包含了 str1str2str3 三列。

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 中使用時,需要在 CRUUENTDATE 之間添加半形空格,並且還需要指定臨時表 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_DATECURRENT_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_DATECURRENT_TIME 函數。可能是因為在 SQL Server 中,CURRENT_TIMESTAMP 已經涵蓋了這兩者的功能吧。 ↩︎

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

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


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

-Advertisement-
Play Games
更多相關文章
  • 【SignalR全套系列】之在.Net Core 中實現SignalR實時通信 ...
  • Cgroup Freezer cgroup freezer對於批量啟動和停止任務集合的任務管理系統來說是很有用的,這個程式經常被用在HPC族上來調度訪問。cgroup freezer使用cgroups來描述被批處理任務管理系統啟動和停止的任務集合。他也提供了方法來啟動和停止任務。 cgroup fr ...
  • Block IO Controller 1 概覽 cgroup子系統blkio實現了block io控制器。無論是對存儲結構上的葉子節點和還是中間節點,它對各種IO控制策略(proportional BW, max BW)都是必須的。設計規劃就是使用同樣的cgroup,基於blkio控制器的管理介面 ...
  • 鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 一:虛擬機宿主機互ping不通 問題一:防火牆 略去,建議主機和宿主機都關閉防火牆,並關閉seLinux(Linux的安全系統) 問題二:網卡未生效 表現 輸入命令 ifcongig,若輸出的網卡信息不含inet [ip地址],則說明網卡未生效 ...
  • 鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 由於我使用ubuntu20.04的火狐瀏覽器時,總是播放不了視頻。說是要下載Flash,但是我順著網址進去,發現並沒有linux版本的(也可能是我沒找到而已?)。於是一直放著沒管,看不了就看不了,真要看我就用筆記本的win10看好了。但是偶爾看到 ...
  • 本文參考書:操作系統真像還原 什麼是malloc? malloc 是用戶態申請記憶體時使用的函數。 malloc在哪裡申請? 堆中。 什麼是堆? 程式運行過程中需要申請額外的記憶體都會在堆中分配,堆中的記憶體分為幾個規格類型的塊用鏈表保存,程式需要記憶體就分配一個大於等於所需記憶體大小的塊。如果一個規格的塊用 ...
  • 資料庫概述 資料庫的概念 名稱 簡稱 資料庫 DataBase(DB) 資料庫管理系統 DataBase Management System(DBMS) SQL Structured Query Language(SQL) MySQL的啟動、停止 啟動: net start mysql80 停止: ...
  • 近十年來,中國基礎軟體發展勢頭迅猛,市場前景看高,越來越多的企業也正在進行基礎軟體升級。那中國基礎軟體行業目前在國際市場上有什麼優勢,面臨哪些困境,以及未來基礎軟體行業會如何發展呢?騰訊雲資料庫邀請沙利文中國高級分析師胡竣傑、華雲中盛資料庫事業部總經理楊光、中軟國際資料庫業務總監範利軍及騰訊雲資料庫 ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...