學習SQL【7】-函數

05-17

學習SQL【7】-函數

來自專欄 Python愛好者

不僅SQL，對所有的編程語言來說，函數都起著至關重要的作用。函數就像是編程語言的「道具箱」，每種編程語言都準備了非常多的函數。譬如，C語言推崇的就是用函數來實現模塊化編程。

根據用途不同，SQL中的函數大致可以分為算術函數、字元串函數、日期函數、轉換函數和聚合函數。

一：函數的種類

1：函數的定義

所謂函數，就是輸入某一值得到相應輸出結果的功能，輸入值稱為參數，輸出值稱為返回值。

2：函數的分類

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

算術函數（用來進行數值計算的函數）
字元串函數（用來進行字元串操作的函數）
日期函數（用來進行日期操作的函數）
轉換函數（用來轉換數據類型和值的函數）
聚合函數（用來進行數據聚合的函數）
SQL中函數的種類有超過200多個，但是常用的只有30~50個。

二：算術函數

算術函數是最基本的函數，也就是我們常用的四則運算：

+（加法）
-（減法）
*（乘法）
/（除法）

為了學習算術函數，我們首先得創建一種示例用表：

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

如上所示，m列的數據類型為NUMERIC，NUMERIC是大多數DBMS都支持的一種數據類型，通過NUMERIC（全體位數, 小數位數）的形式來指定數值的大小。

然後再插入數據：

--插入數據 BEGIN TRANSACTION;BEGIN INSERT INTO SampleMath VALUES (500, 0, NULL);INSERT 0 1 INSERT INTO SampleMath VALUES (-180, 0, NULL);INSERT 0 1 INSERT INTO SampleMath VALUES (NULL, NULL, NULL);INSERT 0 1 INSERT INTO SampleMath VALUES (NULL, 7, 3);INSERT 0 1 INSERT INTO SampleMath VALUES (NULL, 5, 2);INSERT 0 1 INSERT INTO SampleMath VALUES (NULL, 4, NULL);INSERT 0 1 INSERT INTO SampleMath VALUES (8, NULL, 3);INSERT 0 1 INSERT INTO SampleMath VALUES (2.27, 1, NULL);INSERT 0 1 INSERT INTO SampleMath VALUES (5.555, 2, NULL);INSERT 0 1 INSERT INTO SampleMath VALUES (NULL, 1, NULL);INSERT 0 1 INSERT INTO SampleMath VALUES (8.76, NULL, NULL);INSERT 0 1 COMMIT;COMMIT

上面的代碼是我在Windows的命令行窗口敲的，所以每插入一個數據回車就會彈出

確認一下創建的表的內容：

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 | |(11 行記錄)

ABS—-絕對值

ABS是計算絕對值的函數，使用方法直接看例子：

--計算數值的絕對值 SELECT m, ABS(m) AS abs_col FROM SampleMath;

執行結果：

m | abs_col----------+--------- 500.000 | 500.000 -180.000 | 180.000 | | | | 8.000 | 8.000 2.270 | 2.270 5.555 | 5.555 | 8.760 | 8.760(11 行記錄)

如上所示，-180的絕對值就是去掉符號的180。還有，當ABS函數的參數為NULL時，結果也為NULL。

MOD—-求余

MOD是計算除法餘數的函數，使用方法如下：

--計算除法（n / p）的餘數 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 | | | |(11 行記錄)

注釋：在SQL Server中使用特殊的運算符「%」來計算餘數。

ROUND—-四捨五入

ROUND函數用來進行四捨五入操作。如果指定四捨五入的位數為1，那麼就會對小數點第2位進行四捨五入。如果指定位數為2，那麼就會對小數點第3位進行四捨五入操作。

--對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 | |(11 行記錄)

字元串函數

為了學習字元串函數，我們再來創建一張表：

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

插入數據：

BEGIN TRANSACTION;BEGIN INSERT INTO SampleStr VALUES (opx, rt, NULL);INSERT 0 1 INSERT INTO SampleStr VALUES (abc, def, NULL);INSERT 0 1 INSERT INTO SampleStr VALUES (我, 是, 番茄醬);INSERT 0 1 INSERT INTO SampleStr VALUES (aaa, NULL, NULL);INSERT 0 1 INSERT INTO SampleStr VALUES (NULL, xyz, NULL);INSERT 0 1 INSERT INTO SampleStr VALUES (@!#$%, NULL, NULL);INSERT 0 1 INSERT INTO SampleStr VALUES (ABC, NULL, NULL);INSERT 0 1 INSERT INTO SampleStr VALUES (aBC, NULL, NULL);INSERT 0 1 INSERT INTO SampleStr VALUES (abc番茄醬, abc, ABC);INSERT 0 1 INSERT INTO SampleStr VALUES (abcdefabc, abc, ABC);INSERT 0 1 INSERT INTO SampleStr VALUES (micmic, i, I);INSERT 0 1 COMMIT;COMMIT

確認創建的表的內容：

SELECT * FROM SampleStr;

執行結果：

str1 | str2 | str3-----------+------+-------- opx | rt | abc | def | 我 | 是 | 番茄醬 aaa | | | xyz | @!#$% | | ABC | | aBC | | abc番茄醬 | abc | ABC abcdefabc | abc | ABC micmic | i | I(11 行記錄)

||—-拼接

在SQL中可使用兩條並列的豎線「||」來實現字元串的拼接

--拼接兩個字元串 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 | micmici(11 行記錄)

進行字元串拼接時，如果其中包含NULL，那麼得到的結果也是NULL。當然，也可以進行三個字元串的拼接：

--拼接三個字元串 SELECT str1, str2, str3, str1 || str2 || str3 AS str_concat FROM SampleStr WHERE str1 = 我;

執行結果：

str1 | str2 | str3 | str_concat------+------+--------+------------ 我 | 是 | 番茄醬 | 我是番茄醬(1 行記錄)

LENGTH—-字元串長度

想要知道字元串包含多少個字元時，可以使用LENGTH函數

--計算字元串長度 SELECT str1, LENGTH(str1) AS len_str FROM SampleStr;

執行結果：

str1 | len_str-----------+--------- opx | 3 abc | 3 我 | 1 aaa | 3 | @!#$% | 5 ABC | 3 aBC | 3 abc番茄醬 | 6 abcdefabc | 9 micmic | 6(11 行記錄)

LOWER—-小寫轉換

LOWER函數只能針對英文字母使用，它會將參數中的字元串全部都轉換為小寫

--大寫轉換為小寫 SELECT str1, LOWER(str1) AS low_str FROM SampleStr WHERE str1 IN (ABC, aBC, abc, 番茄醬);

執行結果：

str1 | low_str------+--------- abc | abc ABC | abc aBC | abc(3 行記錄)

UPPER—-大寫轉換

LOWER函數只能針對英文字母使用，它會將參數中的字元串全部都轉換為大寫

--將小寫轉換為大寫 SELECT str1, UPPER(str1) AS up_str FROM SampleStr WHERE str1 IN (ABC, aBC, abc, 番茄醬);

執行結果：

str1 | up_str------+-------- abc | ABC ABC | ABC aBC | ABC(3 行記錄)

REPLACE—-字元串的替換

REPLACE函數的語法：

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

使用REPLACE函數，可以將字元串的一部分替換為其他的字元串，咱們用一個例子看一下：

--替換字元串的一部分 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(11 行記錄)

SUBSTRING—-字元串的截取

SUBSTRING函數的語法：

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

使用SUBSTRING函數可以截取字元串中的一部分字元串。截取的起始位置從字元串最左側開始計算。

--截取字元串中第3位和第4位的字元 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(11 行記錄)

日期函數

CURRENT_DATE—-當前日期

CURRENT_DATE函數能夠返回SQL所執行時的日期，由於沒有參數，所以無需使用括弧：

--獲得當前日期 SELECT CURRENT_DATE;

執行結果：

date------------ 2017-12-07(1 行記錄)

CURRENT_TIME—-當前時間

CURRENT_DATE函數能夠返回SQL所執行時的時間，由於沒有參數，所以也無需使用括弧：

--取得當前時間 SELECT CURRENT_TIME;

執行結果：

timetz-------------------- 20:46:11.321481+08(1 行記錄)

CURRENT_TIMESTAMP—-當前時間和日期

CURRENT_TIMESTAMP函數具有CURRENT_DATE+CURRENT_TIME的功能。使用該函數可以同時獲得當前的日期和時間：

--取得當前日期和時間 SELECT CURRENT_TIMESTAMP;

執行結果：

now------------------------------- 2017-12-07 20:46:58.813816+08(1 行記錄)

EXTRACT—-截取日期元素

EXTRACT函數的語法：

EXTRACT(日期元素 FROM 日期)

使用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;

執行結果：

now | year | month | day | hour | minute | second-------------------------------+------+-------+-----+------+--------+---------- 2017-12-07 20:51:00.177839+08 | 2017 | 12 | 7 | 20 | 51 | 0.177839(1 行記錄)

轉換函數

轉換函數在SQL中主要有兩種：一種是數據類型的轉換，簡稱類型轉換。另一種是值的轉換。

CAST—-類型轉換

CAST函數的語法：

CAST(轉換前的值 AS 想要轉換的數據類型)

進行數據類型轉換可以避免一些由於表中數據類型不匹配或者數據類型不一致而引發的錯誤。

--將字元串類型轉換為數值類型 SELECT CAST(0001 AS INTEGER) AS int_col;

執行結果：

int_col--------- 1(1 行記錄)

將字元串類型轉換為日期類型

SELECT CAST(2017-12-07 AS DATE) AS date_col;

執行結果：

date_col------------ 2017-12-07(1 行記錄)

COALESCE—-將NULL轉換為其他值

COALESCE函數的語法：

COALESCE(數據1, 數據2, 數據3,...)

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

--將NULL轉換為其他值 SELECT COALESCE(NULL, 1) AS col_1, COALESCE(NULL, test, NULL) AS col_2, COALESCE(NULL, NULL, 2017-12-07) AS col_3;

執行結果：

col_1 | col_2 | col_3-------+-------+------------ 1 | test | 2017-12-07(1 行記錄)

使用SampleStr表中的列作為例子

SELECT COALESCE(str2, NULL) FROM SampleStr;

執行結果：

coalesce---------- rt def 是 NULL xyz NULL NULL NULL abc abc i(11 行記錄)

如上所示，將str2列中NULL（空）轉換為了字元串『NULL』。

每天學習一點點，每天進步一點點。