一、數據定義語言（DDL）

用于創建、修改和刪除數據庫對象（如表、索引、視圖等）。

1. 創建數據庫

CREATE DATABASE DatabaseName; -- 創建數據庫
CREATE DATABASE DatabaseName ON PRIMARY -- 指定主數據文件
(NAME = 'LogicalFileName', -- 邏輯文件名FILENAME = 'PhysicalFilePath.mdf', -- 物理文件路徑SIZE = 10MB, -- 初始大小MAXSIZE = 50MB, -- 最大大小FILEGROWTH = 5MB -- 自動增長幅度
);

2. 修改數據庫

ALTER DATABASE DatabaseName ADD FILE -- 添加數據文件
(NAME = 'NewLogicalFileName',FILENAME = 'NewPhysicalFilePath.ndf',SIZE = 5MB,MAXSIZE = 20MB,FILEGROWTH = 2MB
);ALTER DATABASE DatabaseName MODIFY FILE -- 修改文件屬性（需指定 NAME）
(NAME = 'LogicalFileName',SIZE = 15MB,MAXSIZE = UNLIMITED -- 無限制增長
);ALTER DATABASE DatabaseName ADD LOG FILE -- 添加日志文件
(NAME = 'LogLogicalFileName',FILENAME = 'LogPhysicalFilePath.ldf',SIZE = 2MB,MAXSIZE = 10MB,FILEGROWTH = 1MB
);

3. 刪除數據庫

DROP DATABASE IF EXISTS DatabaseName; -- 存在時刪除（SQL Server 2016+）
DROP DATABASE DatabaseName; -- 直接刪除（需確保數據庫未被使用）

4. 創建表

CREATE TABLE dbo.Students -- dbo為架構名，可省略
(StudentID INT PRIMARY KEY IDENTITY(1,1), -- 主鍵（自增列）StudentName NVARCHAR(50) NOT NULL, -- 非空字符串Gender NVARCHAR(10) CHECK (Gender IN ('男', '女')), -- 檢查約束Age INT DEFAULT 18, -- 默認值約束EnrollDate DATE NOT NULL DEFAULT GETDATE() -- 默認值為當前日期
);

5. 修改表結構

ALTER TABLE Students ADD Address NVARCHAR(200); -- 添加列ALTER TABLE Students ALTER COLUMN Age INT NOT NULL; -- 修改列屬性（非空）ALTER TABLE Students DROP COLUMN Address; -- 刪除列sp_rename 'Students.Age', 'StudentAge', 'COLUMN'; -- 重命名列（存儲過程方式）
EXEC sp_rename 'Students', 'NewStudents'; -- 重命名表

6. 刪除表

DROP TABLE IF EXISTS dbo.Students; -- 存在時刪除

二、數據操作語言（DML）

用于操作表中的數據（增、刪、改）。

1. 插入數據

-- 插入指定列數據
INSERT INTO Students (StudentName, Gender, Age)
VALUES ('張三', '男', 20);-- 插入所有列數據（按表結構順序）
INSERT INTO Students
VALUES ('李四', '女', 19, '2023-09-01');-- 批量插入（從其他表查詢數據插入）
INSERT INTO NewStudents (StudentID, StudentName)
SELECT StudentID, StudentName FROM OldStudents;

2. 更新數據

-- 更新單條記錄
UPDATE Students
SET Age = 21, Gender = '男'
WHERE StudentID = 1;-- 更新所有記錄（無 WHERE 條件時需謹慎！）
UPDATE Students
SET Age = Age + 1;

3. 刪除數據

-- 刪除單條記錄
DELETE FROM Students
WHERE StudentID = 1;-- 刪除所有記錄（保留表結構）
DELETE FROM Students; -- 逐行刪除，記錄日志
TRUNCATE TABLE Students; -- 快速刪除，不記錄日志（不可回滾）

三、數據查詢語言（DQL）

用于從表中檢索數據，是 SQL 的核心部分。

1. 基礎查詢

SELECT StudentID, StudentName, Age -- 查詢指定列
FROM Students
WHERE Age > 18 AND Gender = '男'; -- 條件過濾SELECT * -- 查詢所有列
FROM Students
ORDER BY Age DESC, StudentName ASC; -- 按年齡降序、姓名升序排序

2. 去重與聚合函數

SELECT DISTINCT Gender -- 去重查詢
FROM Students;SELECT COUNT(*) AS TotalStudents -- 統計總行數
FROM Students;SELECT MAX(Age) AS MaxAge, MIN(Age) AS MinAge -- 最大值、最小值
FROM Students;SELECT SUM(Score) AS TotalScore -- 求和
FROM Exams;SELECT AVG(Score) AS AverageScore -- 平均值
FROM Exams;

3. 分組查詢（GROUP BY/HAVING）

SELECT Gender, COUNT(*) AS StudentCount -- 按性別分組統計人數
FROM Students
GROUP BY Gender;SELECT ClassID, AVG(Score) AS AvgScore -- 按班級分組，查詢平均分≥80的班級
FROM Exams
GROUP BY ClassID
HAVING AVG(Score) >= 80;

4. 連接查詢（JOIN）

-- 內連接：查詢學生及其課程成績
SELECT S.StudentName, C.CourseName, E.Score
FROM Students S
INNER JOIN Exams E ON S.StudentID = E.StudentID
INNER JOIN Courses C ON E.CourseID = C.CourseID;-- 左外連接：查詢所有學生（包括無成績的）
SELECT S.StudentName, E.Score
FROM Students S
LEFT OUTER JOIN Exams E ON S.StudentID = E.StudentID;-- 右外連接、全外連接類似，分別用 RIGHT JOIN/FULL JOIN

5. 子查詢

-- 查詢年齡大于平均年齡的學生
SELECT StudentName, Age
FROM Students
WHERE Age > (SELECT AVG(Age) FROM Students);-- 存在性查詢（IN/EXISTS）
SELECT StudentName
FROM Students
WHERE StudentID IN (SELECT StudentID FROM Exams WHERE Score > 90);

6. 分頁查詢（SQL Server 特有的分頁方式）

-- 使用 OFFSET-FETCH（SQL Server 2012+）
SELECT StudentID, StudentName, Age
FROM Students
ORDER BY StudentID
OFFSET 10 ROWS -- 跳過前10條
FETCH NEXT 5 ROWS ONLY; -- 取接下來的5條（共5條）-- 舊版使用 ROW_NUMBER()
WITH PagedData AS
(SELECT StudentID, StudentName, Age,ROW_NUMBER() OVER (ORDER BY StudentID) AS RowNumFROM Students
)
SELECT *
FROM PagedData
WHERE RowNum BETWEEN 11 AND 15;

四、觸發器（Triggers）

觸發器是一種特殊的存儲過程，它會在特定的表發生 INSERT、UPDATE、DELETE 操作時自動執行。常用于實現復雜的業務規則、數據審計或級聯操作。

1. 觸發器類型

• DML 觸發器：響應 INSERT、UPDATE、DELETE 操作。
• DDL 觸發器：響應 CREATE、ALTER、DROP 等數據定義語言操作。
• 登錄觸發器：響應 LOGON 事件（SQL Server 2008+）。

2. 觸發器中的特殊表

• INSERTED：存儲插入或更新后的新數據。
• DELETED：存儲刪除或更新前的舊數據。

1. 插入后觸發器（AFTER INSERT）

CREATE TRIGGER trg_InsertStudent
ON Students
AFTER INSERT
AS
BEGIN-- 記錄日志INSERT INTO StudentLog (StudentID, Action, LogDate)SELECT StudentID, 'INSERT', GETDATE()FROM INSERTED;
END;

2. 更新前觸發器（INSTEAD OF UPDATE）

CREATE TRIGGER trg_PreventSalaryDecrease
ON Employees
INSTEAD OF UPDATE
AS
BEGINIF EXISTS (SELECT * FROM INSERTED i JOIN DELETED d ON i.EmployeeID = d.EmployeeID WHERE i.Salary < d.Salary)BEGINRAISERROR('不能降低員工工資！', 16, 1);ROLLBACK TRANSACTION;ENDELSEBEGINUPDATE EmployeesSET Salary = i.SalaryFROM Employees eJOIN INSERTED i ON e.EmployeeID = i.EmployeeID;END
END;

3. 級聯刪除觸發器

CREATE TRIGGER trg_CascadeDeleteOrders
ON Customers
AFTER DELETE
AS
BEGINDELETE FROM OrdersWHERE CustomerID IN (SELECT CustomerID FROM DELETED);
END;

4. 管理觸發器

-- 禁用觸發器
ALTER TABLE Students DISABLE TRIGGER trg_InsertStudent;-- 啟用觸發器
ALTER TABLE Students ENABLE TRIGGER trg_InsertStudent;-- 刪除觸發器
DROP TRIGGER IF EXISTS trg_InsertStudent;

五、索引（Indexes）

1. 索引類型

• 聚集索引（Clustered Index）：決定表中數據的物理存儲順序，一張表只能有一個。
• 非聚集索引（Non-Clustered Index）：存儲索引鍵和指向數據行的指針，一張表可有多。
• 唯一索引（Unique Index）：確保索引鍵不重復。
• 覆蓋索引（Covering Index）：包含查詢所需的所有列，避免回表。
• 復合索引（Composite Index）：基于多列創建的索引。

2. 創建索引

-- 創建聚集索引
CREATE CLUSTERED INDEX IX_Products_Price
ON Products (Price);-- 創建唯一非聚集索引
CREATE UNIQUE NONCLUSTERED INDEX UQ_Products_SKU
ON Products (SKU);-- 創建復合索引（注意列順序：最左匹配原則）
CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate
ON Orders (CustomerID, OrderDate DESC);-- 創建包含列的覆蓋索引
CREATE NONCLUSTERED INDEX IX_Employees_Department
ON Employees (DepartmentID)
INCLUDE (EmployeeName, Salary); -- 包含列不參與索引鍵，但可直接返回結果

3. 查看索引

-- 查看表的所有索引
EXEC sp_helpindex 'Products';-- 查看索引使用情況
SELECT * FROM sys.dm_db_index_usage_stats
WHERE object_id = OBJECT_ID('Products');

4. 索引優化建議

• 對查詢頻繁的列（如 WHERE、JOIN、ORDER BY 子句中的列）創建索引。
• 避免對更新頻繁的表創建過多索引。
• 使用 INCLUDE 關鍵字創建覆蓋索引，減少回表操作。
• 通過執行計劃（SET SHOWPLAN_ALL ON）分析索引使用情況。

六、函數（Functions）

（一）內置函數

SQL Server 提供了豐富的內置函數，按功能可分為以下幾類：

1. 字符串函數

-- LEN: 返回字符串長度
SELECT LEN('Hello World') AS StringLength; -- 輸出: 11-- SUBSTRING: 截取子字符串
SELECT SUBSTRING('Hello', 2, 3) AS SubStr; -- 輸出: ell-- UPPER/LOWER: 大小寫轉換
SELECT UPPER('hello') AS UpperCase; -- 輸出: HELLO-- REPLACE: 替換字符串
SELECT REPLACE('Hello World', 'World', 'SQL') AS Replaced; -- 輸出: Hello SQL-- TRIM/LTRIM/RTRIM: 去除空格
SELECT TRIM('  SQL  ') AS Trimmed; -- 輸出: SQL

2. 數值函數

-- ROUND: 四舍五入
SELECT ROUND(3.14159, 2) AS Rounded; -- 輸出: 3.14-- ABS: 絕對值
SELECT ABS(-10) AS Absolute; -- 輸出: 10-- CEILING/FLOOR: 向上/向下取整
SELECT CEILING(3.1) AS CeilingVal; -- 輸出: 4
SELECT FLOOR(3.9) AS FloorVal; -- 輸出: 3-- RAND: 生成隨機數
SELECT RAND() AS RandomNum; -- 輸出: 0-1之間的隨機數

3. 日期和時間函數

-- GETDATE: 獲取當前日期和時間
SELECT GETDATE() AS CurrentDateTime; -- 輸出: 當前時間戳-- DATEADD: 日期加減
SELECT DATEADD(DAY, 7, '2023-01-01') AS NextWeek; -- 輸出: 2023-01-08-- DATEDIFF: 計算日期差
SELECT DATEDIFF(YEAR, '2000-01-01', GETDATE()) AS YearsPassed; -- 輸出: 當前年份-2000-- FORMAT: 格式化日期
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate; -- 輸出: 2023-06-05

4. 聚合函數

-- SUM: 求和
SELECT SUM(Price) AS TotalPrice FROM Products;-- AVG: 平均值
SELECT AVG(Age) AS AverageAge FROM Employees;-- COUNT: 計數
SELECT COUNT(*) AS TotalRows FROM Customers;-- MAX/MIN: 最大值/最小值
SELECT MAX(Salary) AS MaxSalary, MIN(Salary) AS MinSalary FROM Employees;

5. 邏輯函數

-- CASE: 條件判斷
SELECT ProductName,CASE WHEN Price > 100 THEN 'Expensive'WHEN Price > 50 THEN 'Medium'ELSE 'Cheap'END AS PriceCategory
FROM Products;-- ISNULL: 處理 NULL 值
SELECT ISNULL(Email, 'No Email') AS ContactInfo FROM Customers;-- COALESCE: 返回第一個非 NULL 值
SELECT COALESCE(Phone, Email, 'No Contact') AS Contact FROM Customers;

（二）自定義函數

用戶可以創建自己的函數，分為以下兩類：

1. 標量函數（Scalar Functions）

返回單個值：

-- 創建計算年齡的標量函數
CREATE FUNCTION dbo.CalculateAge(@BirthDate DATE)
RETURNS INT
AS
BEGINRETURN YEAR(GETDATE()) - YEAR(@BirthDate) - CASE WHEN MONTH(@BirthDate) > MONTH(GETDATE()) OR (MONTH(@BirthDate) = MONTH(GETDATE()) AND DAY(@BirthDate) > DAY(GETDATE())) THEN 1 ELSE 0 END;
END;-- 使用標量函數
SELECT EmployeeName, dbo.CalculateAge(BirthDate) AS Age FROM Employees;

2. 表值函數（Table-Valued Functions）

返回表數據：

-- 內聯表值函數（簡單查詢）
CREATE FUNCTION dbo.GetProductsByCategory(@Category NVARCHAR(50))
RETURNS TABLE
AS
RETURN (SELECT ProductID, ProductName, PriceFROM ProductsWHERE Category = @Category
);-- 使用內聯表值函數
SELECT * FROM dbo.GetProductsByCategory('Electronics');-- 多語句表值函數（復雜邏輯）
CREATE FUNCTION dbo.GetEmployeeDetails()
RETURNS @Result TABLE (EmployeeID INT,FullName NVARCHAR(100),Department NVARCHAR(50),Salary DECIMAL(10,2)
)
AS
BEGININSERT INTO @ResultSELECT e.EmployeeID,e.FirstName + ' ' + e.LastName AS FullName,d.DepartmentName,e.SalaryFROM Employees eJOIN Departments d ON e.DepartmentID = d.DepartmentID;RETURN;
END;-- 使用多語句表值函數
SELECT * FROM dbo.GetEmployeeDetails();

（四）最佳實踐

1. 避免在 WHERE 子句中調用標量函數：可能導致全表掃描，例如：

   -- 低效：對每一行調用函數
   SELECT * FROM Employees WHERE dbo.CalculateAge(BirthDate) > 30;-- 優化：先過濾再計算
   SELECT * FROM Employees WHERE BirthDate < DATEADD(YEAR, -30, GETDATE());
   

2. 優先使用內聯表值函數：性能優于多語句表值函數。

3. 保持函數簡潔：避免在函數中實現復雜業務邏輯。

4. 使用 SCHEMABINDING：創建函數時綁定架構，防止依賴對象被修改：

   CREATE FUNCTION dbo.GetProducts()
   RETURNS TABLE
   WITH SCHEMABINDING
   AS
   RETURN (SELECT ProductID, ProductName FROM dbo.Products);
   

（五）管理函數

-- 修改函數
ALTER FUNCTION dbo.CalculateAge(@BirthDate DATE)
RETURNS INT
AS
BEGIN-- 修改后的邏輯RETURN DATEDIFF(YEAR, @BirthDate, GETDATE());
END;-- 刪除函數
DROP FUNCTION IF EXISTS dbo.CalculateAge;-- 查看函數定義
EXEC sp_helptext 'dbo.GetProductsByCategory';

七、存儲過程（Stored Procedures）

1. 創建存儲過程

-- 帶參數的存儲過程
CREATE PROCEDURE sp_GetProductsByPrice@MinPrice DECIMAL(10,2),@MaxPrice DECIMAL(10,2) = 1000.00 -- 默認值
AS
BEGINSELECT ProductID, ProductName, PriceFROM ProductsWHERE Price BETWEEN @MinPrice AND @MaxPriceORDER BY Price DESC;
END;

2. 執行存儲過程

-- 帶參數執行
EXEC sp_GetProductsByPrice @MinPrice = 50.00, @MaxPrice = 200.00;-- 使用默認參數
EXEC sp_GetProductsByPrice @MinPrice = 100.00;

3. 帶輸出參數的存儲過程

CREATE PROCEDURE sp_CalculateTotalSales@StartDate DATE,@EndDate DATE,@TotalSales DECIMAL(10,2) OUTPUT -- 輸出參數
AS
BEGINSELECT @TotalSales = SUM(Amount)FROM SalesWHERE SaleDate BETWEEN @StartDate AND @EndDate;
END;-- 調用帶輸出參數的存儲過程
DECLARE @Total DECIMAL(10,2);
EXEC sp_CalculateTotalSales @StartDate = '2023-01-01', @EndDate = '2023-12-31', @TotalSales = @Total OUTPUT;
PRINT '總銷售額: ' + CAST(@Total AS VARCHAR);

4. 存儲過程中的事務處理

CREATE PROCEDURE sp_TransferFunds@FromAccount INT,@ToAccount INT,@Amount DECIMAL(10,2)
AS
BEGINSET NOCOUNT ON; -- 減少消息開銷BEGIN TRYBEGIN TRANSACTION;-- 檢查余額IF (SELECT Balance FROM Accounts WHERE AccountID = @FromAccount) < @AmountBEGINRAISERROR('余額不足', 16, 1);RETURN;END-- 轉賬操作UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @FromAccount;UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ToAccount;COMMIT TRANSACTION;END TRYBEGIN CATCHIF @@TRANCOUNT > 0ROLLBACK TRANSACTION;PRINT '錯誤: ' + ERROR_MESSAGE();END CATCH
END;

5. 修改和刪除存儲過程

-- 修改存儲過程（使用 ALTER 而非 CREATE）
ALTER PROCEDURE sp_GetProductsByPrice@MinPrice DECIMAL(10,2),@MaxPrice DECIMAL(10,2) = 1000.00
AS
BEGIN-- 修改后的邏輯SELECT ProductID, ProductName, Price, CategoryFROM ProductsWHERE Price BETWEEN @MinPrice AND @MaxPrice;
END;-- 刪除存儲過程
DROP PROCEDURE IF EXISTS sp_GetProductsByPrice;

八、約束（Constraints）

約束用于強制數據的完整性，確保數據符合業務規則。SQL Server 支持以下約束類型：

1. 主鍵約束（PRIMARY KEY）

確保列值唯一且不為空，一張表只能有一個主鍵。

CREATE TABLE Products (ProductID INT PRIMARY KEY, -- 隱式約束ProductName NVARCHAR(50)
);-- 或顯式定義約束名
CREATE TABLE Products (ProductID INT,ProductName NVARCHAR(50),CONSTRAINT PK_Products PRIMARY KEY (ProductID)
);

2. 唯一約束（UNIQUE）

確保列值唯一，但允許 NULL（只能有一個 NULL）。

CREATE TABLE Employees (EmployeeID INT PRIMARY KEY,Email NVARCHAR(50) UNIQUE, -- 隱式約束Phone NVARCHAR(20)
);-- 或顯式定義約束名
CREATE TABLE Employees (EmployeeID INT PRIMARY KEY,Email NVARCHAR(50),Phone NVARCHAR(20),CONSTRAINT UQ_Employees_Email UNIQUE (Email)
);

3. 外鍵約束（FOREIGN KEY）

確保一個表中的列值匹配另一個表的主鍵或唯一鍵。

CREATE TABLE Orders (OrderID INT PRIMARY KEY,CustomerID INT,OrderDate DATE,CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

4. 檢查約束（CHECK）

確保列值滿足特定條件。

CREATE TABLE Employees (EmployeeID INT PRIMARY KEY,Salary DECIMAL(10,2) CHECK (Salary >= 2000), -- 工資必須≥2000Age INT CHECK (Age BETWEEN 18 AND 65) -- 年齡范圍
);-- 或使用命名約束
ALTER TABLE Employees
ADD CONSTRAINT CK_Employees_Age CHECK (Age BETWEEN 18 AND 65);

5. 默認約束（DEFAULT）

為列提供默認值，當插入數據未指定值時自動使用。

CREATE TABLE Orders (OrderID INT PRIMARY KEY,OrderDate DATE DEFAULT GETDATE(), -- 默認當前日期Status NVARCHAR(20) DEFAULT 'Pending' -- 默認狀態
);```###  6. 管理約束```sql
-- 添加約束
ALTER TABLE Products
ADD CONSTRAINT CK_Products_Price CHECK (Price > 0);-- 刪除約束
ALTER TABLE Products
DROP CONSTRAINT CK_Products_Price;-- 禁用約束（臨時允許違反規則）
ALTER TABLE Orders
NOCHECK CONSTRAINT FK_Orders_Customers;-- 啟用約束并驗證現有數據
ALTER TABLE Orders
CHECK CONSTRAINT FK_Orders_Customers;

九、授權（Permissions）

（一）安全主體（Security Principals）

SQL Server 中的安全主體是可以被授予權限的實體，包括：

1. 服務器級主體

• 登錄賬戶（Logins）：訪問 SQL Server 實例的憑證。

  -- 創建 SQL 認證登錄（密碼驗證）
  CREATE LOGIN AppUser WITH PASSWORD = 'StrongPassword123!';-- 創建 Windows 認證登錄（域賬戶）
  CREATE LOGIN [DOMAIN\UserName] FROM WINDOWS;
  

2. 數據庫級主體

• 數據庫用戶（Users）：登錄賬戶在特定數據庫中的映射。

  -- 在當前數據庫創建用戶并關聯登錄
  CREATE USER AppUser FOR LOGIN AppUser;
  

• 數據庫角色（Roles）：預定義或自定義的用戶組，便于批量授權。

  -- 創建自定義數據庫角色
  CREATE ROLE DataReader;-- 將用戶添加到角色
  ALTER ROLE DataReader ADD MEMBER AppUser;
  

（二）權限分類

SQL Server 的權限分為三類：

1. 對象權限

針對表、視圖、存儲過程等數據庫對象的操作權限：

-- 授予 SELECT 權限
GRANT SELECT ON dbo.Products TO AppUser;-- 授予多個權限
GRANT INSERT, UPDATE, DELETE ON dbo.Orders TO DataWriterRole;-- 授予對所有表的查詢權限
GRANT SELECT ON ALL TABLES IN SCHEMA dbo TO DataReader;

2. 語句權限

執行特定 SQL 語句的權限（如創建數據庫、備份等）：

-- 授予創建表的權限
GRANT CREATE TABLE TO DeveloperRole;-- 授予備份數據庫的權限
GRANT BACKUP DATABASE TO BackupOperator;

3. 架構權限

針對架構（Schema）的權限控制：

-- 授予對架構中所有對象的全部權限
GRANT CONTROL ON SCHEMA::dbo TO DBA;

（三）權限管理語句

1. 授予權限（GRANT）

-- 授予用戶查詢 Products 表的權限
GRANT SELECT ON dbo.Products TO AppUser;-- 授予用戶執行存儲過程的權限
GRANT EXECUTE ON dbo.sp_GetOrderDetails TO ReportUser;

2. 撤銷權限（REVOKE）

-- 撤銷用戶的 DELETE 權限
REVOKE DELETE ON dbo.Customers FROM JuniorDBA;

3. 拒絕權限（DENY）

拒絕權限會覆蓋已授予的權限（即使通過角色獲得權限也無效）：

-- 拒絕用戶修改 Products 表
DENY UPDATE ON dbo.Products TO ReadOnlyUser;

（四）預定義角色

SQL Server 提供了預定義的服務器角色和數據庫角色，便于快速授權：

1. 服務器級角色

-- 將登錄添加到 sysadmin 角色（超級管理員）
ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\AdminUser];-- 將登錄添加到 bulkadmin 角色（允許 BULK INSERT）
ALTER SERVER ROLE bulkadmin ADD MEMBER AppUser;

2. 數據庫級角色

-- 將用戶添加到 db_datareader 角色（允許查詢所有表）
ALTER ROLE db_datareader ADD MEMBER AppUser;-- 將用戶添加到 db_ddladmin 角色（允許執行 DDL 語句）
ALTER ROLE db_ddladmin ADD MEMBER Developer;

十、事務控制語言（TCL）

用于管理事務，確保數據的一致性和完整性。

1. 開啟事務

BEGIN TRANSACTION; -- 開始事務

2. 提交事務（永久保存更改）

COMMIT TRANSACTION; -- 提交事務

3. 回滾事務（撤銷更改）

ROLLBACK TRANSACTION; -- 回滾到事務開始前的狀態

4. 帶錯誤處理的事務（TRY…CATCH）

BEGIN TRYBEGIN TRANSACTION;-- 執行可能出錯的操作UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;COMMIT TRANSACTION;
END TRY
BEGIN CATCHROLLBACK TRANSACTION; -- 出錯時回滾PRINT '事務執行失敗：' + ERROR_MESSAGE(); -- 打印錯誤信息
END CATCH

十一、視圖（Views）

創建視圖

CREATE VIEW vw_StudentScores AS
SELECT S.StudentName, C.CourseName, E.Score
FROM Students S
JOIN Exams E ON S.StudentID = E.StudentID
JOIN Courses C ON E.CourseID = C.CourseID;-- 使用視圖
SELECT * FROM vw_StudentScores WHERE Score > 80;

以下是 SQL Server 中 視圖（Views） 的詳細介紹及示例，涵蓋視圖的創建、使用、優化及最佳實踐：

（一）視圖基礎

視圖是虛擬表，其內容由查詢定義。它不存儲實際數據，而是在查詢時動態生成結果。

1. 創建視圖

-- 創建簡單視圖
CREATE VIEW vw_ProductSummary AS
SELECT p.ProductID,p.ProductName,c.CategoryName,p.Price
FROM Products p
JOIN Categories c ON p.CategoryID = c.CategoryID
WHERE p.Price > 100;-- 創建帶聚合的視圖
CREATE VIEW vw_CategorySales AS
SELECT c.CategoryName,COUNT(p.ProductID) AS ProductCount,SUM(p.Price * od.Quantity) AS TotalSales
FROM Categories c
JOIN Products p ON c.CategoryID = p.CategoryID
JOIN OrderDetails od ON p.ProductID = od.ProductID
GROUP BY c.CategoryName;

2. 查詢視圖

-- 像查詢表一樣查詢視圖
SELECT * FROM vw_ProductSummary;-- 帶條件查詢視圖
SELECT CategoryName, ProductName 
FROM vw_ProductSummary 
WHERE CategoryName = 'Electronics';

（二）視圖的高級特性

1. 索引視圖（物化視圖）

預計算并存儲視圖結果，提高查詢性能：

-- 創建索引視圖（需滿足特定條件）
CREATE VIEW vw_IndexedProduct
WITH SCHEMABINDING -- 綁定架構，防止依賴對象被修改
AS
SELECT ProductID,ProductName,Price,CategoryID
FROM dbo.Products
WHERE Price > 50;-- 為視圖創建聚集索引（自動物化視圖）
CREATE UNIQUE CLUSTERED INDEX idx_vw_Product ON vw_IndexedProduct (ProductID);

2. 可更新視圖

滿足特定條件的視圖允許 DML 操作：

-- 創建可更新視圖
CREATE VIEW vw_Employees AS
SELECT EmployeeID,FirstName,LastName,DepartmentID
FROM Employees
WHERE DepartmentID = 1;-- 通過視圖插入數據
INSERT INTO vw_Employees (FirstName, LastName, DepartmentID)
VALUES ('John', 'Doe', 1);

3. 分區視圖

跨多個服務器或表分布數據：

-- 示例：將數據按區域分區
CREATE VIEW vw_Orders_All AS
SELECT * FROM Orders_East
UNION ALL
SELECT * FROM Orders_West
UNION ALL
SELECT * FROM Orders_North;

（三）視圖的管理與優化

1. 修改視圖

-- 修改視圖定義
ALTER VIEW vw_ProductSummary AS
SELECT p.ProductID,p.ProductName,c.CategoryName,p.Price,p.Discount
FROM Products p
JOIN Categories c ON p.CategoryID = c.CategoryID
WHERE p.Price > 50; -- 修改了價格過濾條件

2. 刪除視圖

DROP VIEW IF EXISTS vw_ProductSummary;

3. 查看視圖依賴關系

-- 查看視圖依賴的對象
EXEC sp_depends 'vw_ProductSummary';-- 查看依賴某個表的所有視圖
SELECT OBJECT_NAME(object_id) AS ViewName
FROM sys.sql_dependencies
WHERE referenced_object_id = OBJECT_ID('Products');

4. 視圖優化

• 避免在視圖中使用函數：可能導致全表掃描。
• 對索引視圖使用 WITH (NOEXPAND)：強制 SQL Server 使用預計算結果。

  SELECT * FROM vw_IndexedProduct WITH (NOEXPAND) WHERE Price > 100;
  

（四）視圖的應用場景

1. 簡化復雜查詢

將常用的多表連接封裝為視圖：

CREATE VIEW vw_OrderDetails AS
SELECT o.OrderID,c.CustomerName,p.ProductName,od.Quantity,od.UnitPrice,od.Quantity * od.UnitPrice AS TotalAmount
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID;

2. 數據安全控制

通過視圖限制用戶對敏感列的訪問：

CREATE VIEW vw_SafeEmployees AS
SELECT EmployeeID,FirstName,LastName,DepartmentID
FROM Employees; -- 不包含 Salary 等敏感列

3. 兼容舊表結構

在表結構變更時保持接口兼容性：

-- 舊表結構有 ProductName 列
-- 新表拆分為 FirstName 和 LastName 列
CREATE VIEW vw_LegacyProducts AS
SELECT ProductID,FirstName + ' ' + LastName AS ProductName,Price
FROM NewProducts;

（五）視圖的優缺點

1. 優點

• 簡化查詢：封裝復雜邏輯，提高代碼復用性。
• 安全性：限制用戶對底層數據的直接訪問。
• 邏輯獨立性：表結構變更時無需修改應用代碼。
• 性能優化：索引視圖可提高頻繁查詢的速度。

2. 缺點

• 性能開銷：復雜視圖可能導致查詢變慢。
• 維護成本：視圖依賴關系可能變得復雜。
• 更新限制：可更新視圖需滿足嚴格條件。