Java物件導向系列[v1.0.0][SQL語法之資料庫函式]

資料庫函式

每個資料庫都會在標準SQL基礎上擴充套件一些函式，用於進行資料處理和複雜計算，例如前邊的連結字串函式concat，函式可以出現在SQL語句的各個位置，比較常用的是在select之後和where子句中

單行函式

單行函式對每行輸入值單獨計算，每行得到一個計算結果返回給使用者，不同的資料庫單行函式差別比較大，在MySQL中的單行函式：

• 單行函式的引數可以是變數、常量或資料列，單行函式可以接收多個引數，但返回一個值
• 單行函式會對每行單獨起作用，每行（可能包含多個引數）返回一個結果
• 使用單行函式可以改變引數的資料型別，單行函式支援巢狀使用，即內層函式的返回值是外層函式的引數
  
  MySQL資料庫的資料型別大致分為數值型、字元型和日期時間型，MySQL也提供了對應的函式；轉換函式主要負責完成型別轉換，除此之外還有位函式、流程控制函式、加解密函式、資訊函式

char_length()

# 選出davieyang_test表中davieyang_name列的字元長度
select char_length(davieyang_name) from davieyang_test;

sin()

# 計算davieyang_name列的字元長度的sin值
select sin(char_length(davieyang_name)) from davieyang_test;
select sin(1.57);

DATE_ADD()

# 為指定日期新增一定的時間
# 在這種用法下interval是關鍵字，需要一個數值和單位
select DATE_ADD('2020-01-02', interval 2 MONTH);

ADDDATE()

select ADDDATE('2020-01-02', 3);

獲取當前日期

select CURDATE();

獲取當前時間

select curtime();

MD5()

select MD5('test');

處理null

• ifnull(expr1, expr2):如果expr1為null，則返回expr2，否則返回expr1
• nullif(expr1, expr2):如果expr1和expr2相等，則返回null, 否則返回expr1
• if(expr1,expr2,expr3):類似三目運算，如果expr1為true，不等於0，且不等於null，則返回expr2，否則返回expr3
• isnull(expr1):判斷expr1是否為null，如果為null則返回true否則返回false

# 如果davieyang_name為null，則返回'沒有名字'
select ifnull(davieyang_name, '沒有名字') from davieyang_test;

# 如果davieyang_name等於'davieyang'，則返回null
select nullif(davieyang_name, 'davieyang') from davieyang_test;

# 如果davieyang_name為null，則返回'沒有名字',否則返回'有名字'
select if(isnull(davieyang_name), '沒有名字', '有名字') from davieyang_test;

流程控制函式case

case value
when compare_value1 then result1
when compare_value2 then result2
...
else result
end

select davieyang_name, case davieyang_age
when 1 then '1'
when 2 then '2'
else '3'
end
from davieyang_test;

case
when condition1 then result1
when condition2 then result2
...
else result
end

select davieyang_name, case
when davieyang_age>3 then '大於3'
when davieyang_age<=6 then '小於等於6'
else '要啥沒啥就這個'
end
from davieyang_test;

通常不建議在程式中使用特定的資料庫函式，這將導致程式程式碼與特定資料庫耦合，移植到其他資料庫上將會是非常麻煩的事

多行函式

多行函式對多行輸入值整體計算，最後得到一個結果，多行函式也稱為聚集函式，分組函式主要用於完成一些統計功能，在大部分資料庫中基本相同

• avg([distinct|all]expr):計算多行expr的平均值，expr可以是變數，常量，資料列，但資料型別必須是數值型，還可以在變數、列前使用distinct或all關鍵字，使用distinct表明不計算重複值，all用和不用效果一樣表明需要計算重複值
• count({*|[distinct|all]expr}):計算多行expr的總條數，expr可以是變數、常量、資料列，資料型別可以是任意型別，用星號表示統計該表內的記錄行數，distinct表示不計算重複值，使用count統計行數時null不會被計算在內
• max(expr):計算多行expr的最大值，expr可以使變數、常量、資料列，資料型別可以是任意型別
• min(expr):計算多行expr的最小值，expr可以使變數、常量、資料列，資料型別可以是任意型別
• sum([distinct|all]expr):計算多行expr的綜合，expr可以是變數、常量、資料列，資料型別必須是數值型，distinct表示不計算重複值

# 計算davieyang_test表中的記錄條數
select count(*) from davieyang_test;

# 計算davieyang_age列總用有多少個值
select count(distinct davieyang_age) from davieyang_test;

# 統計所有davieyang_id的總和
select sum(davieyang_id) from davieyang_test;

# 計算的結果是20*記錄的行數
select sum(20) from davieyang_test;

# 因為sum裡的expr是常量34，所以每行的值都相同
# 使用distinct強制不計算重複值，所以計算結果為34
select sum(distinct 34) from davieyang_test;

select max(davieyang_id) from davieyang_test;

select min(davieyang_id) from davieyang_test;

# 計算davieyang_age列所有記錄的平均值
select avg(ifnull(davieyang_age, 0)) from davieyang_test;

預設情況下，組函式會把所有記錄當成一組，如果想顯示的進行分組，可以使用group by子句，其後跟一個或多個列名，表明查詢結果是根據一列或多列進行分組，當一列或多列組合的值完全相同時，系統會把這些記錄當成一組

# count(*)將會對每組得到一個結果
select count(*) from davieyang_test
# 將davieyang_age列值相同的記錄當成一組
group by davieyang_age;

如果對多列進行分組，則要求多列的值完全相同才會被當成一組

select count(*) from davieyang_test
# 當davieyang_age, davieyang_name兩列值完全相同時才會被當成一組
group by davieyang_age, davieyang_name;

對於很多資料庫而言，分組計算有嚴格的規則即如果查詢列表中使用了組函式，或者select語句使用了group by分組子句，則要求出現在select列表中的欄位，要麼使用函式包起來，要麼必須出現在group by子句中，但MySQL沒有這個要求，如果某個資料列既沒有出現在group by之後，也沒有使用組函式包起來，則MySQL會輸出該列的第一條記錄的值

---

如果要對分組進行過濾，應該使用having子句，它也是一個條件表示式，只有滿足該條件表示式的分組才會被選出來，having和where很容易混淆，區別如下：

• 不能在where子句中過濾組，只能用where過濾行，過濾組必須用having
• 不能在where子句中使用組函式，having子句中才能使用組函式

select * from davieyang_test group by davieyang_age having count(*)>2;