详细介绍：SQL Server相关的sql语句

目录

一、数据定义语言（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 RowNum
FROM 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
BEGIN
IF
EXISTS (
SELECT *
FROM INSERTED i
JOIN DELETED d ON i.EmployeeID = d.EmployeeID
WHERE i.Salary < d.Salary)
BEGIN
RAISERROR('不能降低员工工资！'
, 16
, 1
)
;
ROLLBACK
TRANSACTION
;
END
ELSE
BEGIN
UPDATE Employees
SET Salary = i.Salary
FROM Employees e
JOIN INSERTED i ON e.EmployeeID = i.EmployeeID;
END
END
;

3. 级联删除触发器

CREATE
TRIGGER trg_CascadeDeleteOrders
ON Customers
AFTER
DELETE
AS
BEGIN
DELETE
FROM Orders
WHERE 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
BEGIN
RETURN
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, Price
FROM Products
WHERE 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
BEGIN
INSERT
INTO @Result
SELECT
e.EmployeeID,
e.FirstName + ' ' + e.LastName AS FullName,
d.DepartmentName,
e.Salary
FROM Employees e
JOIN 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
BEGIN
SELECT ProductID, ProductName, Price
FROM Products
WHERE Price BETWEEN @MinPrice
AND @MaxPrice
ORDER
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
BEGIN
SELECT @TotalSales
= SUM(Amount)
FROM Sales
WHERE 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
BEGIN
SET NOCOUNT ON
;
-- 减少消息开销
BEGIN TRY
BEGIN
TRANSACTION
;
-- 检查余额
IF (
SELECT Balance FROM Accounts WHERE AccountID = @FromAccount
) <
@Amount
BEGIN
RAISERROR('余额不足'
, 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 TRY
BEGIN CATCH
IF @@TRANCOUNT
>
0
ROLLBACK
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, Category
FROM Products
WHERE 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
)
, -- 工资必须≥2000
Age 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 TRY
BEGIN
TRANSACTION
;
-- 执行可能出错的操作
UPDATE Accounts SET Balance = Balance - 100
WHERE AccountID = 1
;
UPDATE Accounts SET Balance = Balance + 100
WHERE AccountID = 2
;
COMMIT
TRANSACTION
;
END TRY
BEGIN CATCH
ROLLBACK
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. 缺点

  • 性能开销：复杂视图可能导致查询变慢。
  • 维护成本：视图依赖关系可能变得复杂。
  • 更新限制：可更新视图需满足严格条件。
posted @ 2025-07-19 22:11  yfceshi  阅读(18)  评论(0)    收藏  举报