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

Davieyang.D.Y發表於2020-09-29

資料庫函式

每個資料庫都會在標準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;

相關文章