《SQL基礎教程》簡要總結

onephone發表於2017-12-06
SQL

偶然間從朋友那看到了這本書（《SQL基礎教程》MICK著），感覺還不錯，於是花了4個小時認真的過了一遍，在這裡簡單的總結一下要點（個人觀點）

基礎知識點

  1. SQL不區分關鍵字的大小寫
  2. SQL語句中表示字串用單引號'將字元括起來
  3. 修改表名
    • PostgreSQL: ALTER TABLE oldname RENAME TO newname
    • Mysql: RENAME TABLE oldname TO newname
  4. SQL註釋
    • --：單行註釋，即-- 加上半形空格
    • /* */：可進行多行註釋
  5. NOT運算子
    • 不建議用 ...... where not xxx>0
  6. AND運算子的優先順序高於OR
  7. SQL中的邏輯運算是三值邏輯，即真、假和不確定（NULL）
  8. 聚合函式會預設將NULL值排除
    • count(*)：查詢的是包含NULL在內的全部資料行數
    • count(1)或者count(column_name)：不包含NULL在內的資料行數
    • sum(column_name)：column_name為NULL或為零，其計算效果是一樣的
    • avg(column_name)：NULL值和零值，其計算效果是不一樣的（分母不一樣）
    • 聚合函式可以在SELECT子句、HAVING子句和ORDER BY子句中使用
    • COUNT等函式對錶中的資料進行聚合操作時，DBMS內部會先進行排序處理
    • GROUP BY子句的聚合結果是無序的
  9. ORDER BY字句中可以使用SELECT字句中已定義的列的別名
    • 可使用SELECT子句中未出現的列或者聚合函式
    • ORDER BY 子句中不建議使用編號（將來會被編號會被取消）
  10. 兩個詞：INSERT INTO, DELETE FROM, UPDATE SET, DROP TABLE
    • UPDATE tblname SET (name1, name2, name3) = (Name1, Name2, Name3) WHERE ......
    • UPDATE tblname SET name1 = Name11, name2 = Name12 WHERE ......
  11. 事務是需要在同一個處理單元中執行的一系列更新處理的集合
    • BEGIN TRANSCATION, START TRANSCATION(mysql), COMMIT;
  12. ACID
    • Atomicity
    • Consistency：滿足約束
    • Isolation: 不同事務間不相干擾
    • Durability
  13. 檢視的定義中不能包含ORDER BY子句
  14. 標量子查詢就是返回單一值的子查詢
    • scalar subquery，即返回一行一列的結果
    • 標量子查詢的位置可以是在SELECT子句、GROUP BY子句、HAVING子句、ORDER BY子句、WHERE字句等地方
  15. 字串拼接函式||在SQL和MySQL中無法使用，等價的函式是CONCAT(str1, str2, str3)
    • LENGTH(str1)
    • SUBSTRING(str1 FROM 3 FOR 2)：擷取字串str1中第三位和第四位字元（字元位置從1開始）
  16. EXTRACT(日期元素 FROM 日期)
    • EXTRACT(YEAR FROM CURRENT_TIMESTAMP)
  17. 型別轉換函式：CAST(xx AS yy)
  18. NULL值轉換函式：COALESCE(str1, str2, str3) 會返回可變引數中左側開始第一個不是NULL的值
  19. 謂詞
    • LIKE: %, _
    • EXIST, NOT EXIST
  20. CASE表示式
    • 簡單的CASE表示式：CASE 表示式 WHEN ... THEN ... END
    • 搜尋CASE表示式：CASE WHEN ... THEN ... WHEN THEN ... END
  21. 集合運算
    • UNION：可以使用任何一個select語句，但order by字句只能在最後使用 UNION ALL 不會消除重複行
    • INTERSET：求交集
    • EXCEPT：求差集
  22. JOIN
    • 內連線：INNER JOIN
    • 外連線：OUTER JOIN 選擇主表
    • 交叉連線：CROSS JOIN -- 笛卡爾積

視窗函式

視窗函式：OLAP（OnLine Analytical Processing）函式，對資料庫資料進行實時分析處理，如市場分析、財務報表、建立計劃等； 可以進行排序、生成序列等一般的聚合函式無法實現的高階操作

  1. 語法 <視窗函式> OVER ([PARTITION BY <列清單>] ORDER BY <排序清單>)
    • PARTITION BY 對錶的橫向進行分組
    • ORDER BY 決定縱向的排序規則
    • PARTITION BY 分組後的記錄集合成為“視窗”
  2. 專用視窗函式
    • RANK: 計算排序時，如果存在相同位次的記錄，則會跳過之後的位次
    • DENSE_RANK: 即使存在相同的位次記錄，也不回跳過之後的位次
    • ROW_NUMBER: 賦予唯一的連續位次
    • 專用視窗函式無序引數，故括號都是空的
  3. 聚合函式作為視窗函式使用
    • SUM, AVG, COUNT, MIN, MAX等 都是進行累計統計的，和GROUP BY 不同
  4. 指定框架（統計範圍）
    • AVG(col1) OVER (ORDER BY col2 ROWS 2 PRECEDING)
    • 使用了ROWS和PRECEDING關鍵字制定了框架，即統計物件先定位“截止到之前2行”
    • AVG(col1) OVER (ORDER BY col2 ROWS 2 FOLLOWING) “截止到之後～行”
  5. OVER 字句中的 ORDER BY 只是用來決定視窗函式按照什麼順序進行計算的，對最後結果的排序沒有影響！

GROUPING 運算子

  1. GROUPING運算子可以同時計算出小計值、合計值
  2. ROLL UP同時計算出合計值和小計值
    • GROUP BY ROLL UP(col1, col2), 在MySQL中要改寫為GROUP BY col1 with ROLLUP
    • 上述語句即相當於GROUP BY ()（超級分組記錄）和GROUP BY (col1)和GROUP BY (col1, col2)的結果
  3. GROUPING函式 可判斷超級分組記錄中的NULL值
    • GROUPING(col1) 如果col1是超級分組記錄所產生的NULL值時返回1， 其他返回0
  4. CUBE即將GROUP BY子句中聚合鍵的所有“可能組織”的聚合結果集中到一個結果中
  5. GROUPING SETS 可以從 ROLLUP或者CUBE的記過中取出部分記錄
    • 不想得到合計記錄和使用多個聚合鍵的記錄時可以使用
    • GROUP BY GROUPING SETS(col1, col2) 結果集中的每個記錄只包含col1（或col2）的單個合計

相關文章