資料庫學習筆記（四）2017.9.19

weixin_34320159發表於2017-09-19
資料庫筆記

寫在前面：本篇部落格大部分內容參考資料庫系統概念（本科教學版）第三章(第三章部分的多表操作沒有在此處講，準備挪到第四章再一起討論)
下面的例子中的測試都是在MySQL資料庫中測試的

集合運算

此處集合運算的概念與數學中集合的概念類似，可以藉助文氏圖加深理解。故此處對並運算進行展開，其他啊兩個自己類比，後面還會對集合運算的幾個注意點進提醒。

  • union(集合並運算)

    • union 預設去除重複，並升序排序（因為涉及到排序，故而會有效率上的劣勢）
    • union all 就可保留重複項，並且保留原序（不進行排序）
    • 舉個栗子（現有兩張表如下）
      • stu_name age score
        Sunny 18 98
        Robbin 18 96
        James 19 67
        Jane 17 82
        Student表資訊如上
      • tech_name age
        James 32
        Marry 33
        Lory 33
        Teacher表的資訊如上
      • 執行以下運算 
        SELECT age
FROM Student
UNION
SELECT age
FROM Teacher
        得到如下結果(得到的結果沒有重複元素，但是沒有排序，說明MySQL資料庫在此處沒有對其進行排序輸出，但是Oracle資料庫是會對資料進行升序排序的)
      • age
        18
        19
        17
        32
        33
      • 執行以下運算 
        SELECT age
FROM Student
UNION ALL
SELECT age  
FROM Teacher
        得到如下結果(可以知道，UNION ALL 保留了重複元素)
      • age
        18
        18
        19
        17
        32
        33
        33

  • intersect(集合交運算)

  • except(集合差運算)

  • Note:

    • 參與集合運算的兩個檢視的列數要一致
      • 舉個栗子 
        -- 下面的語句執行就會出錯
-- 因為第一個檢視有兩個欄位，而第二個檢視只有一個欄位，無法進行集合運算
SELECT age, score
FROM Student
UNION ALL
SELECT age  
FROM Teacher
    • 參與集合運算的兩個檢視對應位置的欄位的資料型別應該是一致的（資料型別相容即可，欄位名無需一致）
      • 舉個栗子 
        -- 按上面的說法下面語句執行是非法的
-- 因為第二個欄位的資料型別不相容，一個是浮點型，一個是字串型別
SELECT age, score
FROM Student
UNION ALL
SELECT age, tech_name  
FROM Teacher
        上面的書法在Oracle資料庫裡得到了證實，確實執行是會報錯的，
        那MySQL資料庫呢？我們執行以下，得到以下結果
      • age score
        18 98
        18
        19 67
        17 82
        32 James
        33 Marry
        33 Lory
        說明該寫法在Mysql裡面竟然是合法的（所以所有的特性都要視不同的資料庫，視情況而分析）
    • 若無欄位可加，又需保證列數相同，可控NULL做填充
      • 舉個栗子 
        SELECT age, score
FROM Student
UNION ALL
SELECT age, NULL  
FROM Teacher
        得到如下結果哦
      • age score
        18 98
        18
        19 67
        17 82
        32
        33
        33
    • 集合運算的結果檢視的欄位名以第一個結果集的欄位名為主
      • 舉個栗子 
        SELECT stu_name
FROM Student
UNION
SELECT tech_name
FROM Teacher
        得到如下結果：
      • stu_name
        Sunny
        Robbin
        Marry
        Lory
        Jane
        James
    • Oracle資料庫中，差運算不是except， 而是minus；Oracle資料庫union可以後面跟all， 但是interscet和minus後面不可以（不同資料庫不同）

空值NULL

  • NULL就是不確定的值，參與數值運算和字串運算時，不同的資料庫採取的處理不同；
  • Oracle 資料庫中，NULL無論參與數值運算還是字串運算，都會使整體為NULL
  • 獲取系統時間
    • Oracle資料庫的查詢語句必須有from
    • 故採用如下方法獲取系統時間 
      -- 其中dual是Oracle資料庫系統自帶的一行一列的表
-- 其他資料庫沒有這個表
SELECT SYSDATE
FROM dual
    • MySQL就沒有這個限制 
      -- 在MySQL和SQL Server中用下面的語句就可獲得系統時間
SELECT SYSDATE
  • 日期型別的運算
    • 可與數值型別做加減運算（在Oracle資料庫裡面單位為天，·.·在MySQL裡面竟然是秒）
      • 舉個栗子（MySQL裡面測試） 
        SELECT SYSDATE() - 1, SYSDATE(), SYSDATE() + 1
        得到如下結果
      • 'SYSDATE() - 1' 'SYSDATE()' 'SYSDATE() + 1'
        20170919183000 2017-09-19 18:30:01 20170919183002
    • 可與日期型別做減運算，得到連個日期之前的差值
    • 不可與日期型別做加運算（在Oracle資料庫執行日期間加運算直接報錯，Mysql資料庫直接真的返回兩個日期加以後的值，不過巨耗時，mmp）
    • 與NULL值運算得NULL值（在Oracle和MySQL上測試結果都正確）
  • where 子句，判斷是否為空
    • 用is NULL 和 is not NULL 判斷
    • 而不用 = NULL 和 != NULL
  • 布林變數有三個取值：TRUE，FALSE, NULL
    • 三種取值以and，or相連時的結果，與並聯串聯電路的判斷類
    • TRUE and NULL = NULL
    • TRUE OR NULL = TRUE
    • FALSE and NULL = FALSE
    • FALSE OR NULL = NULL
    • 舉個栗子 
      -- 下面的語句將會返回Student表中的所有資訊
-- 因為where子句後面的條件為真
SELECT *
FROM Student
WHERE TRUE OR NULL

去重關鍵字 distinct

  • 跟在SELECT的後面，並且置於所有欄位的前面
  • 會將其後的欄位都作為判斷重複的條件
  • 舉個栗子 
    -- 下面的語句就是列出學生表中的資料，並去除stu_name和age都相同的資料重複
SELECT DISTINCT stu_name, age
FROM Student

聚集函式（Aggregate Functions）

多行輸入，一行輸出

  • 此類函式有MIN，MAX，COUNT，AVG，SUM。其中AVG和SUM只能參與數值運算
  • 聚集函式在使用時會忽略空值NULL
    • 除了COUNT(*)，COUNT(*)在統計的時候是不忽略空值的

  • Count

    • 在計數時，忽略空值項
    • 預設是統計重複項的，其中ALL是預設的，顧可以不顯示指明 
      COUNT(ALL age)
    • 如果統計去除重複以後的結果,可如下面寫法 
      COUNT(DISTINCT age)
    • Oracle中用作對null值資料處理的函式nvl 
      # 下面的語句表示對資料表中的分數做加和，如果遇到為空的項，則取其值為0
SELECT SUM(NVL(score, 0))
FROM Student
    • COUNT(*)==>可返回滿足where子句條件的所有資料的數量
      • 這是COUNT獨有的用法，其他聚集函式裡面只能放欄位或表示式

  • SUM

    • SUM(age) + SUM(score) >= SUM(age + score)
    • 因為聚集函式在使用時會忽略空值，而NULL值直接參與運算可能會導致整體為空，顧有上述結論
    • 當且僅當資料中沒有空值時，上述等號成立

分組聚集（Aggregation）

  • GROUP BY 欄位序列
    • GROUP BY 後面跟的欄位序列作為分組條件，值相同的為一組
    • 可以是多個欄位（順序不影響結果）
  • 當SELECT列表中出現了聚集函式，select中能出現以下欄位
    • 可以放group by 後面的欄位
    • 可以放聚集函式處理了的欄位或表示式

    上面兩種情況下的欄位在每組的取值都是唯一的，故而可以保證結果集中每一項的行數是一致的

having， 解決where子句中不能包含聚集函式的問題

相關文章