SQL Server核心知識總結

🎯 本文總結了SQL Server核心知識點,每個主題都提供實際可運行的示例代碼。

一、SQL Server基礎精要

1. 數據庫核心操作

-- 1. 創建數據庫（核心配置）
CREATE DATABASE 學生管理系統
ON PRIMARY
(NAME = '學生管理系統_數據',FILENAME = 'D:\Data\學生管理系統.mdf',SIZE = 100MB,FILEGROWTH = 100MB
)
LOG ON
(NAME = '學生管理系統_日志',FILENAME = 'D:\Data\學生管理系統.ldf',SIZE = 50MB,FILEGROWTH = 50MB
);
GO-- 2. 創建核心表結構
CREATE TABLE 學生表
(學號 CHAR(10) PRIMARY KEY,           -- 主鍵（最重要）姓名 NVARCHAR(20) NOT NULL,         -- 必填字段性別 CHAR(2),出生日期 DATE,班級 NVARCHAR(20)
);CREATE TABLE 成績表
(ID INT IDENTITY(1,1) PRIMARY KEY,    -- 自增主鍵學號 CHAR(10),課程號 CHAR(5),成績 DECIMAL(5,2),CONSTRAINT FK_成績表_學生表 FOREIGN KEY (學號) REFERENCES 學生表(學號)  -- 外鍵關系
);-- 3. 基本數據操作（最常用）
-- 插入數據
INSERT INTO 學生表 (學號, 姓名, 性別, 班級)
VALUES ('2021001', '張三', '男', '計算機1班');-- 更新數據
UPDATE 學生表 
SET 班級 = '計算機2班'
WHERE 學號 = '2021001';-- 刪除數據
DELETE FROM 學生表 
WHERE 學號 = '2021001';

🔑 核心要點：

數據庫設計三要素：
- 主數據文件(.mdf)：存儲數據
- 日志文件(.ldf)：記錄事務
- 合理的初始大小和增長設置
表設計核心原則：
- 必須有主鍵（唯一標識）
- 建立合適的外鍵關系
- 選擇合適的數據類型
- 添加必要的約束
最常用的SQL操作：
- INSERT：添加數據
- UPDATE：修改數據
- DELETE：刪除數據

2. 數據類型和查詢

讓我們學習最常用的數據類型和SELECT查詢：

-- 1. 最常用數據類型示例
CREATE TABLE 數據類型示例
(-- 整數類型（最常用）ID INT IDENTITY(1,1),         -- 自增整數，常用主鍵數量 SMALLINT,               -- 較小范圍整數-- 精確數值（金融計算必用）金額 DECIMAL(12,2),          -- 總12位，小數2位單價 MONEY,                  -- 專用于金融計算-- 字符串（最常用）名稱 NVARCHAR(50),           -- Unicode變長，最常用編號 CHAR(10),              -- 定長，如學號工號等描述 VARCHAR(MAX),          -- 大文本數據-- 日期時間（最常用）創建日期 DATE,               -- 僅日期更新時間 DATETIME2           -- 日期時間，推薦使用
);-- 2. 核心查詢語句
-- 基礎查詢（最常用）
SELECT 學號, 姓名, 成績
FROM 學生表
WHERE 班級 = '計算機1班'
ORDER BY 成績 DESC;-- 多表聯接（重要）
SELECT s.姓名, c.課程名, g.成績
FROM 學生表 s
INNER JOIN 成績表 g ON s.學號 = g.學號
INNER JOIN 課程表 c ON g.課程號 = c.課程號
WHERE g.成績 >= 60;-- 分組統計（常用）
SELECT 班級, COUNT(*) AS 人數,AVG(成績) AS 平均分,MAX(成績) AS 最高分
FROM 學生表 s
JOIN 成績表 g ON s.學號 = g.學號
GROUP BY 班級
HAVING AVG(成績) >= 60;-- 子查詢（重要）
SELECT 姓名, 成績
FROM 學生表 s
JOIN 成績表 g ON s.學號 = g.學號
WHERE 成績 > (SELECT AVG(成績)FROM 成績表
);

📝 查詢要點：

SELECT語句核心組成（按執行順序）：
- FROM：指定數據來源
- WHERE：行級過濾
- GROUP BY：分組
- HAVING：組級過濾
- ORDER BY：排序
常用聯接類型：
- INNER JOIN：內聯接（最常用）
- LEFT JOIN：左外聯接（保留左表所有行）
- RIGHT JOIN：右外聯接（保留右表所有行）
常用聚合函數：
- COUNT()：計數
- SUM()：求和
- AVG()：平均值
- MAX()/MIN()：最大/最小值
性能優化要點：
- 只查詢需要的列
- 合理使用索引
- 避免SELECT *
- 適當使用WHERE條件

3. 索引和性能優化

讓我們學習最核心的性能優化技術：

-- 1. 創建最常用的索引類型
-- 聚集索引（主鍵，每表僅一個）
CREATE TABLE 訂單表
(訂單號 INT PRIMARY KEY,  -- 自動創建聚集索引客戶ID INT,訂單日期 DATE,總金額 DECIMAL(12,2)
);-- 非聚集索引（最常用的查詢優化方式）
CREATE NONCLUSTERED INDEX IX_訂單表_客戶ID
ON 訂單表(客戶ID);-- 覆蓋索引（包含所有需要的列）
CREATE NONCLUSTERED INDEX IX_訂單表_日期_金額
ON 訂單表(訂單日期)
INCLUDE (總金額);-- 2. 查看索引使用情況
-- 查看索引的使用統計
SELECT OBJECT_NAME(i.object_id) AS 表名,i.name AS 索引名,ius.user_seeks + ius.user_scans AS 使用次數,ius.last_user_seek AS 最后查詢時間
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE database_id = DB_ID();-- 3. 性能診斷（最常用）
-- 查看執行計劃
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO-- 慢查詢示例
SELECT * FROM 訂單表 
WHERE 訂單日期 BETWEEN '2024-01-01' AND '2024-01-31';-- 優化后的查詢
SELECT 訂單號, 訂單日期, 總金額 
FROM 訂單表 WITH(INDEX(IX_訂單表_日期_金額))
WHERE 訂單日期 BETWEEN '2024-01-01' AND '2024-01-31';

🚀 性能優化核心要點：

索引使用原則：
- 經常查詢的列建立索引
- 外鍵列必建索引
- 避免對頻繁更新的列建索引
- 選擇性高的列適合建索引
最重要的優化技巧：
- 使用覆蓋索引避免回表
- 避免索引列上使用函數
- 避免隱式類型轉換
- 合理使用索引提示
常見性能問題：
- 索引碎片化：定期重建或重組
- 統計信息過期：更新統計信息
- 參數嗅探：使用OPTIMIZE FOR
- 死鎖：合理的事務處理
性能監控工具：
- 執行計劃
- STATISTICS IO/TIME
- sys.dm_db_index_usage_stats
- 數據庫引擎優化顧問

4. 事務和并發控制

讓我們學習如何保證數據的一致性：

-- 1. 基本事務處理（最常用）
-- 轉賬示例
BEGIN TRYBEGIN TRANSACTION;-- 從賬戶A扣款UPDATE 賬戶表SET 余額 = 余額 - 1000WHERE 賬戶ID = 'A';-- 給賬戶B存款UPDATE 賬戶表SET 余額 = 余額 + 1000WHERE 賬戶ID = 'B';-- 記錄交易日志INSERT INTO 交易日志(交易類型, 金額, 時間)VALUES ('轉賬', 1000, GETDATE());COMMIT TRANSACTION;
END TRY
BEGIN CATCHROLLBACK TRANSACTION;INSERT INTO 錯誤日志(錯誤信息, 時間)VALUES (ERROR_MESSAGE(), GETDATE());
END CATCH;-- 2. 事務隔離級別（重要）
-- 設置隔離級別
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;-- 處理并發訪問
BEGIN TRANSACTION;-- 使用鎖提示SELECT * FROM 訂單表 WITH (UPDLOCK, ROWLOCK)WHERE 訂單號 = '001';-- 更新訂單UPDATE 訂單表SET 狀態 = '已處理'WHERE 訂單號 = '001';
COMMIT TRANSACTION;-- 3. 死鎖處理（常見問題）
-- 設置死鎖優先級
SET DEADLOCK_PRIORITY HIGH;-- 使用表鎖提示避免死鎖
UPDATE 訂單表 WITH (ROWLOCK)
SET 狀態 = '處理中'
WHERE 訂單號 = '001';-- 4. 并發控制最佳實踐
-- 使用樂觀并發控制
CREATE TABLE 商品表
(商品ID INT PRIMARY KEY,名稱 NVARCHAR(50),庫存 INT,版本號 ROWVERSION  -- 用于樂觀并發控制
);-- 樂觀并發更新示例
UPDATE 商品表
SET 庫存 = 庫存 - 1
WHERE 商品ID = 1 
AND 版本號 = @原版本號;  -- 確保數據未被其他事務修改

🔒 事務管理核心要點：

事務ACID特性：
- 原子性：要么全做要么全不做
- 一致性：保持數據完整
- 隔離性：事務間互不干擾
- 持久性：提交后永久保存
最常用的隔離級別：
- READ COMMITTED（默認）：防止臟讀
- REPEATABLE READ：防止不可重復讀
- SERIALIZABLE：最高隔離級別
- READ UNCOMMITTED：性能最好但不安全
并發控制策略：
- 悲觀鎖：適用于高并發更新
- 樂觀鎖：適用于讀多寫少
- 行級鎖：粒度小，并發高
- 表級鎖：粒度大，阻塞多
實踐建議：
- 事務盡可能短小
- 合理設置隔離級別
- 避免長時間持有鎖
- 正確的錯誤處理

5. 備份和恢復

讓我們學習如何保護數據安全：

-- 1. 完整備份（最基礎最重要）
-- 創建完整備份
BACKUP DATABASE 學生管理系統
TO DISK = 'D:\Backup\學生管理系統_Full.bak'
WITH COMPRESSION,                 -- 啟用壓縮CHECKSUM,                   -- 驗證備份完整性DESCRIPTION = '完整備份';    -- 備份描述-- 2. 差異備份（節省空間和時間）
BACKUP DATABASE 學生管理系統
TO DISK = 'D:\Backup\學生管理系統_Diff.bak'
WITH DIFFERENTIAL,               -- 差異備份COMPRESSION;-- 3. 日志備份（保證時間點恢復）
BACKUP LOG 學生管理系統
TO DISK = 'D:\Backup\學生管理系統_Log.bak'
WITH COMPRESSION;-- 4. 數據庫恢復（最常用場景）
-- 完整恢復
RESTORE DATABASE 學生管理系統
FROM DISK = 'D:\Backup\學生管理系統_Full.bak'
WITH NORECOVERY;  -- 允許繼續還原其他備份-- 還原差異備份
RESTORE DATABASE 學生管理系統
FROM DISK = 'D:\Backup\學生管理系統_Diff.bak'
WITH NORECOVERY;-- 還原日志備份到指定時間點
RESTORE LOG 學生管理系統
FROM DISK = 'D:\Backup\學生管理系統_Log.bak'
WITH STOPAT = '2024-01-15 14:30:00',  -- 指定恢復時間點RECOVERY;  -- 完成恢復，數據庫可用-- 5. 自動化備份維護（生產環境必備）
-- 清理過期備份文件
DECLARE @cmd NVARCHAR(500);
SET @cmd = 'forfiles /p "D:\Backup" /s /m *.bak /d -30 /c "cmd /c del @path"';
EXEC xp_cmdshell @cmd;-- 驗證備份有效性
RESTORE VERIFYONLY 
FROM DISK = 'D:\Backup\學生管理系統_Full.bak';

💾 備份恢復核心要點：

三種主要備份類型：
- 完整備份：整個數據庫的完整副本
- 差異備份：自上次完整備份后的變化
- 日志備份：記錄詳細的事務日志
常用備份策略（最佳實踐）：
- 每周一次完整備份
- 每天一次差異備份
- 每小時一次日志備份
- 定期驗證備份有效性
關鍵恢復場景：
- 系統崩潰：使用最新的一致備份
- 數據誤刪：使用時間點恢復
- 硬件故障：完整恢復流程
- 測試環境：快速還原生產數據
備份管理要點：
- 異地存儲重要備份
- 定期清理過期備份
- 監控備份執行狀態
- 測試恢復流程

6. 安全管理

讓我們學習如何保護數據庫安全：

-- 1. 用戶和角色管理（基礎安全）
-- 創建登錄名
CREATE LOGIN 教師登錄 
WITH PASSWORD = 'P@ssw0rd123',CHECK_POLICY = ON;  -- 啟用密碼策略-- 創建數據庫用戶
USE 學生管理系統;
CREATE USER 教師用戶 FOR LOGIN 教師登錄;-- 創建角色并分配權限
CREATE ROLE 教師角色;
GRANT SELECT, UPDATE ON 成績表 TO 教師角色;
GRANT SELECT ON 學生表 TO 教師角色;-- 將用戶添加到角色
ALTER ROLE 教師角色 ADD MEMBER 教師用戶;-- 2. 數據加密（敏感數據保護）
-- 創建主密鑰
CREATE MASTER KEY ENCRYPTION 
BY PASSWORD = 'YourStr0ngP@ssw0rd';-- 創建證書
CREATE CERTIFICATE 學生信息證書
WITH SUBJECT = '學生敏感信息加密證書';-- 創建加密密鑰
CREATE SYMMETRIC KEY 學生信息加密密鑰
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE 學生信息證書;-- 加密數據示例
CREATE TABLE 學生敏感信息
(學號 CHAR(10) PRIMARY KEY,姓名 NVARCHAR(20),身份證號 VARBINARY(256),  -- 加密存儲聯系電話 VARBINARY(256)   -- 加密存儲
);-- 插入加密數據
OPEN SYMMETRIC KEY 學生信息加密密鑰
DECRYPTION BY CERTIFICATE 學生信息證書;INSERT INTO 學生敏感信息 
VALUES ('2021001', '張三',EncryptByKey(Key_GUID('學生信息加密密鑰'), '320123199901011234'),EncryptByKey(Key_GUID('學生信息加密密鑰'), '13912345678')
);CLOSE SYMMETRIC KEY 學生信息加密密鑰;-- 3. 審計跟蹤（最重要的安全措施）
-- 創建服務器審計
CREATE SERVER AUDIT 數據庫審計
TO FILE (FILEPATH = 'D:\Audit\');-- 創建數據庫審計規范
CREATE DATABASE AUDIT SPECIFICATION 學生數據審計
FOR SERVER AUDIT 數據庫審計
ADD (SELECT, UPDATE, DELETE ON 學生表 BY PUBLIC),
ADD (SELECT, UPDATE ON 成績表 BY PUBLIC);-- 啟用審計
ALTER SERVER AUDIT 數據庫審計 WITH (STATE = ON);
ALTER DATABASE AUDIT SPECIFICATION 學生數據審計 WITH (STATE = ON);-- 查看審計日志
SELECT * FROM fn_get_audit_file
('D:\Audit\*', DEFAULT, DEFAULT);-- 4. 安全最佳實踐
-- 定期修改密碼
ALTER LOGIN 教師登錄 
WITH PASSWORD = 'NewP@ssw0rd456';-- 禁用不用的賬戶
ALTER LOGIN 教師登錄 DISABLE;-- 回收不需要的權限
REVOKE UPDATE ON 成績表 FROM 教師角色;-- 監控登錄失敗
SELECT * FROM sys.dm_exec_sessions
WHERE login_time > DATEADD(HOUR, -1, GETDATE())
AND login_name = '教師登錄';

🔐 安全管理核心要點：

訪問控制基礎：
- 最小權限原則
- 基于角色的授權
- 定期審查權限
- 密碼策略管理
數據加密策略：
- 敏感數據加密存儲
- 傳輸數據加密
- 密鑰定期輪換
- 證書安全管理
審計要點：
- 重要操作必須審計
- 定期檢查審計日志
- 異常行為告警
- 審計日志安全存儲
安全維護：
- 定期安全評估
- 及時安裝補丁
- 監控可疑活動
- 制定應急預案

二、高級特性

讓我們學習SQL Server最常用的高級功能：

1. 存儲過程和函數

-- 1. 存儲過程（最常用的封裝方式）
-- 創建成績統計存儲過程
CREATE PROCEDURE sp_統計學生成績@班級 NVARCHAR(20),@及格率 DECIMAL(5,2) OUTPUT
AS
BEGINSET NOCOUNT ON;-- 計算及格率SELECT @及格率 = CONVERT(DECIMAL(5,2),SUM(CASE WHEN 成績 >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*))FROM 成績表 gJOIN 學生表 s ON g.學號 = s.學號WHERE s.班級 = @班級;-- 返回詳細統計SELECT COUNT(*) AS 總人數,AVG(成績) AS 平均分,MAX(成績) AS 最高分,MIN(成績) AS 最低分FROM 成績表 gJOIN 學生表 s ON g.學號 = s.學號WHERE s.班級 = @班級;
END;-- 調用存儲過程
DECLARE @及格率 DECIMAL(5,2);
EXEC sp_統計學生成績 @班級 = '計算機1班',@及格率 = @及格率 OUTPUT;
PRINT '及格率: ' + CAST(@及格率 AS VARCHAR(10)) + '%';-- 2. 自定義函數（常用計算封裝）
-- 創建年齡計算函數
CREATE FUNCTION fn_計算年齡
(@出生日期 DATE
)
RETURNS INT
AS
BEGINRETURN DATEDIFF(YEAR, @出生日期, GETDATE()) -CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @出生日期, GETDATE()), @出生日期) > GETDATE()THEN 1 ELSE 0 END;
END;-- 創建成績等級函數
CREATE FUNCTION fn_計算等級
(@成績 DECIMAL(5,2)
)
RETURNS CHAR(1)
AS
BEGINRETURN CASE WHEN @成績 >= 90 THEN 'A'WHEN @成績 >= 80 THEN 'B'WHEN @成績 >= 70 THEN 'C'WHEN @成績 >= 60 THEN 'D'ELSE 'F'END;
END;-- 使用函數
SELECT 姓名,dbo.fn_計算年齡(出生日期) AS 年齡,成績,dbo.fn_計算等級(成績) AS 等級
FROM 學生表 s
JOIN 成績表 g ON s.學號 = g.學號;

📦 存儲過程和函數要點：

存儲過程優勢：
- 減少網絡流量
- 重用業務邏輯
- 提高執行效率
- 增強安全性
函數使用場景：
- 復雜計算封裝
- 數據轉換處理
- 業務規則統一
- 代碼重用

2. 觸發器

-- 1. 數據審計觸發器（最常用）
CREATE TRIGGER tr_學生表_審計
ON 學生表
AFTER INSERT, UPDATE, DELETE
AS
BEGINSET NOCOUNT ON;-- 插入操作審計INSERT INTO 審計日志(表名, 操作類型, 操作時間, 操作用戶, 數據)SELECT '學生表','INSERT',GETDATE(),SYSTEM_USER,(SELECT * FROM inserted FOR JSON AUTO)FROM insertedWHERE EXISTS (SELECT 1 FROM inserted);-- 刪除操作審計INSERT INTO 審計日志(表名, 操作類型, 操作時間, 操作用戶, 數據)SELECT '學生表','DELETE',GETDATE(),SYSTEM_USER,(SELECT * FROM deleted FOR JSON AUTO)FROM deletedWHERE EXISTS (SELECT 1 FROM deleted);
END;-- 2. 業務規則觸發器（數據驗證）
CREATE TRIGGER tr_成績表_驗證
ON 成績表
INSTEAD OF INSERT
AS
BEGINSET NOCOUNT ON;-- 驗證成績范圍IF EXISTS (SELECT 1 FROM inserted WHERE 成績 < 0 OR 成績 > 100)BEGINRAISERROR ('成績必須在0-100之間', 16, 1);RETURN;END;-- 驗證通過后插入數據INSERT INTO 成績表(學號, 課程號, 成績)SELECT 學號, 課程號, 成績FROM inserted;
END;

🔄 觸發器使用要點：

常用場景：
- 數據審計跟蹤
- 業務規則驗證
- 數據同步更新
- 自動計算匯總
設計原則：
- 觸發器要簡單
- 避免長事務
- 注意性能影響
- 合理使用事務

3. 視圖

讓我們繼續學習視圖的應用：

-- 1. 基礎視圖（最常用）
-- 創建學生成績匯總視圖
CREATE VIEW v_學生成績匯總
AS
SELECT s.學號,s.姓名,s.班級,COUNT(g.課程號) AS 課程數,AVG(g.成績) AS 平均分,SUM(CASE WHEN g.成績 >= 60 THEN 1 ELSE 0 END) AS 及格課程數
FROM 學生表 s
LEFT JOIN 成績表 g ON s.學號 = g.學號
GROUP BY s.學號, s.姓名, s.班級;-- 2. 帶檢查選項的視圖（數據驗證）
CREATE VIEW v_優秀學生
WITH SCHEMABINDING
AS
SELECT 學號, 姓名, 班級, 成績
FROM dbo.成績表 g
JOIN dbo.學生表 s ON g.學號 = s.學號
WHERE 成績 >= 90
WITH CHECK OPTION;-- 3. 索引視圖（提高查詢性能）
CREATE VIEW v_課程平均分
WITH SCHEMABINDING
AS
SELECT 課程號,COUNT_BIG(*) AS 學生數,AVG(CONVERT(DECIMAL(5,2), 成績)) AS 平均分
FROM dbo.成績表
GROUP BY 課程號;-- 在視圖上創建唯一聚集索引
CREATE UNIQUE CLUSTERED INDEX IX_課程平均分
ON v_課程平均分(課程號);-- 4. 分區視圖（大表分區）
-- 創建分區表
CREATE TABLE 歷史成績表_2023
(學號 CHAR(10),課程號 CHAR(5),成績 DECIMAL(5,2),學年 CHAR(4) CHECK (學年 = '2023')
);CREATE TABLE 歷史成績表_2024
(學號 CHAR(10),課程號 CHAR(5),成績 DECIMAL(5,2),學年 CHAR(4) CHECK (學年 = '2024')
);-- 創建分區視圖
CREATE VIEW v_歷史成績
AS
SELECT * FROM 歷史成績表_2023
UNION ALL
SELECT * FROM 歷史成績表_2024;-- 5. 視圖的使用示例
-- 查詢優秀學生
SELECT * FROM v_優秀學生
WHERE 班級 = '計算機1班'
ORDER BY 成績 DESC;-- 更新視圖數據
UPDATE v_學生成績匯總
SET 班級 = '計算機2班'
WHERE 學號 = '2021001';-- 通過視圖插入數據
INSERT INTO v_優秀學生(學號, 姓名, 班級, 成績)
VALUES ('2021010', '李四', '計算機1班', 95);

👁? 視圖使用要點：

視圖的優勢：
- 簡化復雜查詢
- 控制數據訪問
- 提供數據獨立性
- 實現數據安全
常用視圖類型：
- 基礎視圖：簡化查詢
- 索引視圖：提升性能
- 分區視圖：管理大數據
- 更新視圖：維護數據
設計原則：
- 避免過于復雜的視圖
- 合理使用索引視圖
- 注意更新限制
- 控制視圖嵌套層數
性能考慮：
- 適當使用SCHEMABINDING
- 避免使用SELECT *
- 合理使用索引
- 控制視圖復雜度

4. XML和JSON

讓我們學習如何處理結構化數據：

-- 1. XML數據處理（常用于數據交換）
-- 創建包含XML列的表
CREATE TABLE 學生檔案
(學號 CHAR(10) PRIMARY KEY,基本信息 XML,成績記錄 XML
);-- 插入XML數據
INSERT INTO 學生檔案(學號, 基本信息)
VALUES ('2021001','<學生><姓名>張三</姓名><性別>男</性別><聯系方式><電話>13912345678</電話><郵箱>zhangsan@example.com</郵箱></聯系方式></學生>'
);-- 查詢XML數據
SELECT 學號,基本信息.value('(/學生/姓名)[1]', 'NVARCHAR(20)') AS 姓名,基本信息.value('(/學生/聯系方式/電話)[1]', 'VARCHAR(20)') AS 聯系電話
FROM 學生檔案;-- 使用XML索引提高查詢性能
CREATE PRIMARY XML INDEX PX_學生檔案_基本信息
ON 學生檔案(基本信息);-- 2. JSON數據處理（更現代的選擇）
-- 將查詢結果轉為JSON
SELECT 學號,姓名,班級,成績
FROM 學生表 s
JOIN 成績表 g ON s.學號 = g.學號
FOR JSON PATH;-- 創建包含JSON的表
CREATE TABLE 學生信息擴展
(學號 CHAR(10) PRIMARY KEY,擴展信息 NVARCHAR(MAX)CHECK (ISJSON(擴展信息) = 1)  -- 確保是有效的JSON
);-- 插入JSON數據
INSERT INTO 學生信息擴展
VALUES ('2021001','{"興趣愛好": ["編程", "籃球", "音樂"],"獲獎記錄": [{"時間": "2023-06", "獎項": "編程大賽一等獎"},{"時間": "2023-12", "獎項": "優秀學生"}],"實習經歷": {"公司": "科技公司","職位": "開發實習生","時間": "2023-07至2023-09"}}'
);-- 查詢JSON數據
SELECT 學號,JSON_VALUE(擴展信息, '$.實習經歷.公司') AS 實習公司,JSON_QUERY(擴展信息, '$.興趣愛好') AS 興趣愛好
FROM 學生信息擴展;-- 3. 結構化數據轉換（常用場景）
-- 行轉列（XML方式）
SELECT 學號,姓名,(SELECT 課程號 AS '@課程', 成績 AS '@分數'FROM 成績表WHERE 學號 = s.學號FOR XML PATH('課程'), ROOT('成績記錄')) AS 成績XML
FROM 學生表 s;-- 行轉列（JSON方式）
SELECT 學號,姓名,(SELECT 課程號, 成績FROM 成績表WHERE 學號 = s.學號FOR JSON PATH) AS 成績JSON
FROM 學生表 s;-- 4. 數據導入導出
-- 導出XML數據
SELECT 學號, 姓名, 班級
FROM 學生表
FOR XML PATH('學生'), ROOT('學生列表');-- 導出JSON數據
SELECT 學號, 姓名, 班級
FROM 學生表
FOR JSON PATH, ROOT('學生列表');-- 解析JSON數組
SELECT 學號,興趣
FROM 學生信息擴展
CROSS APPLY OPENJSON(擴展信息, '$.興趣愛好')WITH (興趣 NVARCHAR(50) '$');

📊 結構化數據處理要點：

XML使用場景：
- 數據交換接口
- 配置文件存儲
- 復雜數據結構
- 遺留系統集成
JSON優勢：
- 更輕量級的格式
- 更好的可讀性
- 前后端數據傳輸
- 現代API集成
性能考慮：
- 適當使用XML索引
- JSON數據類型驗證
- 避免過大的文檔
- 合理的查詢方式
最佳實踐：
- 選擇合適的格式
- 規范的數據結構
- 有效的錯誤處理
- 定期數據維護

5. 全文檢索

讓我們學習如何實現高效的文本搜索：

-- 1. 全文檢索配置（基礎設置）
-- 創建全文目錄
CREATE FULLTEXT CATALOG 文章目錄
WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT;-- 創建包含大文本的表
CREATE TABLE 文章表
(文章ID INT PRIMARY KEY,標題 NVARCHAR(200),內容 NVARCHAR(MAX),作者 NVARCHAR(50),發布時間 DATETIME2
);-- 創建全文索引
CREATE FULLTEXT INDEX ON 文章表
(標題 LANGUAGE 2052,  -- 簡體中文內容 LANGUAGE 2052
)
KEY INDEX PK__文章表__ID
ON 文章目錄
WITH CHANGE_TRACKING AUTO;-- 2. 基本全文搜索（最常用）
-- 簡單匹配
SELECT 文章ID, 標題, 作者
FROM 文章表
WHERE CONTAINS(內容, '數據庫');-- 多個關鍵詞（任意匹配）
SELECT 文章ID, 標題
FROM 文章表
WHERE CONTAINS(內容, 'SQL OR 數據庫');-- 精確短語匹配
SELECT 文章ID, 標題
FROM 文章表
WHERE CONTAINS(內容, '"SQL Server 優化"');-- 3. 高級搜索功能
-- 近似匹配
SELECT 文章ID, 標題
FROM 文章表
WHERE CONTAINS(內容, 'NEAR((數據庫, 優化), 10)');-- 通配符搜索
SELECT 文章ID, 標題
FROM 文章表
WHERE CONTAINS(內容, '"SQL*"');-- 加權搜索
SELECT 文章ID, 標題,RANK
FROM 文章表
INNER JOIN CONTAINSTABLE(文章表, (標題, 內容), '數據庫 OR 優化',LANGUAGE 2052
) AS KEY_TBL
ON 文章表.文章ID = KEY_TBL.[KEY]
ORDER BY RANK DESC;-- 4. 全文搜索最佳實踐
-- 創建復合全文索引
CREATE FULLTEXT INDEX ON 文章表
(標題 LANGUAGE 2052 STATISTICAL_SEMANTICS,內容 LANGUAGE 2052 STATISTICAL_SEMANTICS,作者 LANGUAGE 2052
)
KEY INDEX PK__文章表__ID
ON 文章目錄
WITH CHANGE_TRACKING AUTO;-- 使用停用詞
CREATE FULLTEXT STOPLIST 自定義停用詞
FROM SYSTEM STOPLIST;ALTER FULLTEXT STOPLIST 自定義停用詞
ADD '的' LANGUAGE 2052;-- 更新全文索引
ALTER FULLTEXT INDEX ON 文章表
SET STOPLIST 自定義停用詞;-- 5. 性能優化示例
-- 重建全文索引
ALTER FULLTEXT INDEX ON 文章表
START FULL POPULATION;-- 增量更新
ALTER FULLTEXT INDEX ON 文章表
START INCREMENTAL POPULATION;-- 查看索引狀態
SELECT OBJECT_NAME(object_id) AS 表名,FULLTEXTCATALOGPROPERTY('文章目錄', 'ItemCount') AS 索引文檔數,FULLTEXTCATALOGPROPERTY('文章目錄', 'PopulateStatus') AS 填充狀態
FROM sys.fulltext_indexes
WHERE object_id = OBJECT_ID('文章表');