萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL Server 資料庫核心知識體系:從基礎架構到 T-SQL 進階完全指南

前言

SQL Server 作為微軟旗艦級關聯式資料庫,廣泛應用於企業級應用。建立完整知識體系,是從初學者邁向資深 DBA/開發者的必經之路。本文按學習路徑梳理五大核心模組:基礎架構、SQL 語句、進階查詢、視圖/索引/事務、T-SQL 程式設計。

一、SQL 基礎架構與物件

1. 版本與版次

Enterprise / Standard / Developer / Express / Web 等,依授權、HA、資料壓縮、分區、線上索引等功能差異選型。

2. 核心資料類型

分類型別備註
整數tinyint, smallint, int, bigint1/2/4/8 bytes
定點/浮點decimal(p,s), money, float, real金融用 decimal/money
日期時間datetime, datetime2, date, time, smalldatetimedatetime2 精度更高、範圍更廣
字串char/varchar, nchar/nvarcharn* 儲存 Unicode,雙倍空間
二進位binary/varbinary, image (棄用)大物件用 varbinary(max)
其他bit, uniqueidentifier, xml, json, hierarchyid現代型別支援半結構化資料

3. 完整性約束五大類

  • 主鍵 (PK) —— 實體完整性
  • 唯一 (Unique) —— 候選鍵
  • 預設 (Default) —— 域完整性
  • 檢查 (Check) —— 域/業務規則
  • 外鍵 (FK) —— 參考完整性

4. 正規化三範式

  1. 1NF:無重複欄位,原子性。
  2. 2NF:非主鍵屬性完全依賴主鍵(無部分依賴)。
  3. 3NF:非主鍵屬性不傳遞依賴其他非主鍵屬性。

反正規化(冗余)僅在讀密集、寫輕量場景權衡使用。

二、SQL 四大語言分類

類別縮寫指令用途
資料定義DDLCREATE, ALTER, DROP定義結構
資料操縱DMLINSERT, UPDATE, DELETE, MERGE操作資料
資料查詢DQLSELECT檢索資料
資料控制DCLGRANT, REVOKE, DENY權限控制

三、進階查詢技巧

1. 子查詢分類

  • 獨立子查詢:執行一次,結果傳給外層(IN, =, > ANY)。
  • 相關子查詢:外層每行執行一次,引用外層欄位(EXISTS, CORRELATED)。

2. 多表聯結

  • INNER JOIN:交集
  • LEFT/RIGHT/FULL OUTER JOIN:保留單側/雙側
  • CROSS JOIN:笛卡兒積
  • SELF JOIN:自聯結(遞迴階層)

3. 集合運算

UNION (去重) / UNION ALL (保留重複) / INTERSECT / EXCEPT。

4. 窗口函數 (SQL Server 2012+)

ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, FIRST_VALUE, LAST_VALUE, SUM() OVER() 等,解決 Top-N、同比環比、累計等複雜分析需求。

四、視圖、索引與事務

視圖

  • 虛擬表,簡化複雜查詢、安全控管(欄位/行級)、支援 WITH CHECK OPTION 防止違規更新。
  • 系統表:sys.views, sys.columns, sys.sql_modules。

索引

類型特徵限制
叢集索引資料頁按鍵值物理排序每表僅一個
非叢集索引獨立 B+ 樹,葉層含叢集鍵/RowID可多個,含覆蓋索引
篩選索引WHERE 子句篩選子集稀疏資料極佳
Columnstore欄位式壓縮,OLAP 神器批次模式查詢極快

事務 ACID

  • 原子性:全做或全不做
  • 一致性:約束滿足
  • 隔離性:Read Uncommitted / Read Committed / Repeatable Read / Serializable / Snapshot
  • 持久性:Commit 後寫入記錄檔

分類:自動提交 / 顯式 BEGIN TRAN / 隱性 SET IMPLICIT_TRANSACTIONS ON。

五、T-SQL 程式設計精華

變數與流程控制

  • 全域變數 @@ROWCOUNT, @@ERROR, @@IDENTITY, @@TRANCOUNT…
  • 區域變數 DECLARE @v INT,SET/SELECT 賦值。
  • IF...ELSE, WHILE, BREAK/CONTINUE, TRY...CATCH, THROW (2012+)。

常用函數分類

  • 轉換:CAST, CONVERT, TRY_CAST, TRY_CONVERT (安全轉換)
  • 日期:DATEADD, DATEDIFF, EOMONTH, DATETRUNC (2022+)
  • 字串:STRING_SPLIT, STRING_AGG, CONCAT_WS, TRANSLATE, TRIM (2017+)
  • 分析:PERCENTILE_CONT, PERCENTILE_DISC

動態 SQL 安全模式

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ' WHERE id = @id';
EXEC sp_executesql @sql, N'@id INT', @id = @paramId;  -- 參數化,防注入、重用計畫

結語

SQL Server 知識體系龐大且持續演進。建議按「核心引擎 → 索引統計 → 查詢優化 → 高可用/災備 → 雲原生/大數據整合」五階段深耕,並持續追蹤 Microsoft Learn 官方文檔與社群最佳實踐。

copyright © 萬盛學電腦網 all rights reserved