1. 数据库的建立、更改、删除

创建数据库

-- 基本创建
CREATE DATABASE SchoolDB;
GO

-- 带详细配置的创建
CREATE DATABASE SchoolDB
ON PRIMARY (
    NAME = 'SchoolDB_Data',
    FILENAME = 'C:\Data\SchoolDB.mdf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB
)
LOG ON (
    NAME = 'SchoolDB_Log',
    FILENAME = 'C:\Data\SchoolDB.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 2MB
);
GO

修改数据库

-- 修改数据库名称
ALTER DATABASE SchoolDB MODIFY NAME = UniversityDB;
GO

-- 添加数据文件
ALTER DATABASE UniversityDB
ADD FILE (
    NAME = 'UniversityDB_Data2',
    FILENAME = 'C:\Data\UniversityDB2.ndf',
    SIZE = 5MB,
    MAXSIZE = 20MB,
    FILEGROWTH = 2MB
);
GO

-- 修改文件大小
ALTER DATABASE UniversityDB
MODIFY FILE (
    NAME = 'UniversityDB_Data',
    SIZE = 15MB
);
GO

删除数据库

-- 删除数据库
DROP DATABASE UniversityDB;
GO

-- 安全删除（先检查是否存在）
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'UniversityDB')
    DROP DATABASE UniversityDB;
GO

2. 表的建立、更改、删除

创建表

USE SchoolDB;
GO

CREATE TABLE Students (
    StudentID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    DateOfBirth DATE,
    Email NVARCHAR(100) UNIQUE,
    EnrollmentDate DATETIME DEFAULT GETDATE(),
    GPA DECIMAL(3,2) CHECK (GPA >= 0.0 AND GPA <= 4.0),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
GO

-- 带有计算列的表
CREATE TABLE OrderDetails (
    OrderDetailID INT IDENTITY(1,1) PRIMARY KEY,
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    UnitPrice MONEY NOT NULL,
    Quantity INT NOT NULL,
    Discount DECIMAL(4,2) DEFAULT 0.00,
    TotalPrice AS (UnitPrice * Quantity * (1 - Discount)) PERSISTED
);
GO

修改表

-- 添加列
ALTER TABLE Students ADD PhoneNumber NVARCHAR(20);
GO

-- 修改列
ALTER TABLE Students ALTER COLUMN Email NVARCHAR(150);
GO

-- 删除列
ALTER TABLE Students DROP COLUMN PhoneNumber;
GO

-- 添加约束
ALTER TABLE Students ADD CONSTRAINT CHK_GPA CHECK (GPA >= 0.0 AND GPA <= 5.0);
GO

-- 添加外键约束
ALTER TABLE Students WITH CHECK ADD CONSTRAINT FK_Students_Departments 
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);
GO

-- 启用/禁用约束
ALTER TABLE Students NOCHECK CONSTRAINT FK_Students_Departments; -- 禁用
ALTER TABLE Students CHECK CONSTRAINT FK_Students_Departments; -- 启用
GO

删除表

-- 删除表
DROP TABLE Students;
GO

-- 安全删除（先检查是否存在）
IF OBJECT_ID('Students', 'U') IS NOT NULL
    DROP TABLE Students;
GO

3. 数据的插入、查询、删除、更新

插入数据

-- 基本插入
INSERT INTO Students (FirstName, LastName, DateOfBirth, Email, GPA, DepartmentID)
VALUES ('张', '明', '2000-05-15', 'zhangming@example.com', 3.75, 1);
GO

-- 批量插入
INSERT INTO Students (FirstName, LastName, DateOfBirth, Email, GPA, DepartmentID)
VALUES 
    ('李', '华', '1999-08-22', 'lihua@example.com', 3.90, 2),
    ('王', '芳', '2001-03-10', 'wangfang@example.com', 3.50, 1),
    ('赵', '强', '2000-11-30', 'zhaoqiang@example.com', 3.80, 3);
GO

-- 从其他表插入
INSERT INTO StudentsArchive (StudentID, FirstName, LastName, GraduationDate)
SELECT StudentID, FirstName, LastName, GETDATE()
FROM Students
WHERE GPA > 3.8;
GO

查询数据

-- 基本查询
SELECT StudentID, FirstName, LastName, GPA
FROM Students
WHERE DepartmentID = 1 AND GPA > 3.5
ORDER BY GPA DESC;
GO

-- 聚合函数
SELECT 
    DepartmentID,
    COUNT(*) AS StudentCount,
    AVG(GPA) AS AverageGPA,
    MIN(GPA) AS MinGPA,
    MAX(GPA) AS MaxGPA
FROM Students
GROUP BY DepartmentID
HAVING COUNT(*) > 5;
GO

-- 连接查询
SELECT 
    s.StudentID, s.FirstName, s.LastName, 
    d.DepartmentName,
    c.CourseName, sc.Grade
FROM Students s
INNER JOIN Departments d ON s.DepartmentID = d.DepartmentID
INNER JOIN StudentCourses sc ON s.StudentID = sc.StudentID
INNER JOIN Courses c ON sc.CourseID = c.CourseID
WHERE d.DepartmentName = '计算机科学';
GO

-- 子查询
SELECT StudentID, FirstName, LastName, GPA
FROM Students
WHERE GPA > (SELECT AVG(GPA) FROM Students);
GO

-- 分页查询 (SQL Server 2012+)
SELECT StudentID, FirstName, LastName, GPA
FROM Students
ORDER BY StudentID
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
GO

更新数据

-- 基本更新
UPDATE Students
SET GPA = 3.95, Email = 'zhangming_new@example.com'
WHERE StudentID = 1;
GO

-- 基于连接的更新
UPDATE s
SET s.GPA = s.GPA + 0.1
FROM Students s
INNER JOIN Departments d ON s.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = '计算机科学' AND s.GPA < 4.0;
GO

-- 使用子查询更新
UPDATE Students
SET GPA = (SELECT AVG(GPA) FROM Students)
WHERE StudentID IN (SELECT StudentID FROM ProbationStudents);
GO

删除数据

-- 基本删除
DELETE FROM Students
WHERE StudentID = 5;
GO

-- 带条件的删除
DELETE FROM Students
WHERE EnrollmentDate < '2018-01-01' AND GPA < 2.0;
GO

-- 基于连接的删除
DELETE s
FROM Students s
INNER JOIN Departments d ON s.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = '已关闭部门';
GO

-- 截断表（删除所有数据，重置标识列）
TRUNCATE TABLE StudentLogs;
GO

4. 视图的建立、更改和删除

创建视图

-- 简单视图
CREATE VIEW ComputerScienceStudents AS
SELECT StudentID, FirstName, LastName, GPA
FROM Students
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = '计算机科学');
GO

-- 带聚合的视图
CREATE VIEW DepartmentStatistics AS
SELECT 
    d.DepartmentName,
    COUNT(s.StudentID) AS StudentCount,
    AVG(s.GPA) AS AverageGPA,
    MAX(s.GPA) AS HighestGPA
FROM Departments d
LEFT JOIN Students s ON d.DepartmentID = s.DepartmentID
GROUP BY d.DepartmentName;
GO

-- 加密视图（防止查看定义）
CREATE VIEW EncryptedStudentView
WITH ENCRYPTION
AS
SELECT StudentID, FirstName, LastName, Email
FROM Students;
GO

修改视图

-- 修改视图定义
ALTER VIEW ComputerScienceStudents AS
SELECT 
    s.StudentID, 
    s.FirstName + ' ' + s.LastName AS FullName, 
    s.GPA,
    d.DepartmentName
FROM Students s
INNER JOIN Departments d ON s.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = '计算机科学' AND s.GPA >= 3.0;
GO

删除视图

-- 删除视图
DROP VIEW ComputerScienceStudents;
GO

-- 安全删除
IF OBJECT_ID('DepartmentStatistics', 'V') IS NOT NULL
    DROP VIEW DepartmentStatistics;
GO

使用视图

-- 查询视图
SELECT * FROM DepartmentStatistics;
GO

-- 通过视图更新数据（有条件限制）
UPDATE ComputerScienceStudents
SET GPA = 4.0
WHERE StudentID = 1;
GO

5. 索引的建立、更改和删除

创建索引

-- 创建非聚集索引
CREATE INDEX IX_Students_LastName
ON Students (LastName);
GO

-- 创建复合索引
CREATE INDEX IX_Students_Name
ON Students (LastName, FirstName);
GO

-- 创建唯一索引
CREATE UNIQUE INDEX IX_Students_Email
ON Students (Email);
GO

-- 创建聚集索引（每表只能一个）
CREATE CLUSTERED INDEX IX_Students_EnrollmentDate
ON Students (EnrollmentDate DESC);
GO

-- 创建筛选索引
CREATE INDEX IX_Students_Active
ON Students (LastName, FirstName)
WHERE IsInactive = 0;
GO

-- 创建包含列的索引
CREATE INDEX IX_Students_GPA_Include
ON Students (GPA)
INCLUDE (FirstName, LastName, Email);
GO

修改索引

-- 重新生成索引（碎片整理）
ALTER INDEX IX_Students_LastName ON Students REBUILD;
GO

-- 重新组织索引
ALTER INDEX IX_Students_LastName ON Students REORGANIZE;
GO

-- 禁用索引
ALTER INDEX IX_Students_LastName ON Students DISABLE;
GO

-- 启用索引
ALTER INDEX IX_Students_LastName ON Students REBUILD;
GO

删除索引

-- 删除索引
DROP INDEX IX_Students_LastName ON Students;
GO

-- 安全删除
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_Students_Email' AND object_id = OBJECT_ID('Students'))
    DROP INDEX IX_Students_Email ON Students;
GO

6. T-SQL (Transact-SQL)

变量和批处理

DECLARE @StudentCount INT;
DECLARE @AvgGPA DECIMAL(3,2);

SELECT @StudentCount = COUNT(*), @AvgGPA = AVG(GPA)
FROM Students
WHERE DepartmentID = 1;

PRINT '计算机科学系学生数量: ' + CAST(@StudentCount AS VARCHAR);
PRINT '平均GPA: ' + CAST(@AvgGPA AS VARCHAR);
GO

控制流

-- IF...ELSE
DECLARE @HighGPAStudents INT;
SELECT @HighGPAStudents = COUNT(*) FROM Students WHERE GPA > 3.8;

IF @HighGPAStudents > 10
    PRINT '优秀学生较多';
ELSE
    PRINT '需要提高学生表现';
GO

-- WHILE循环
DECLARE @Counter INT = 1;
DECLARE @TotalStudents INT;
SELECT @TotalStudents = COUNT(*) FROM Students;

WHILE @Counter <= @TotalStudents
BEGIN
    PRINT '处理学生ID: ' + CAST(@Counter AS VARCHAR);
    SET @Counter = @Counter + 1;
END
GO

-- CASE表达式
SELECT 
    StudentID,
    FirstName,
    LastName,
    GPA,
    PerformanceLevel = CASE
        WHEN GPA >= 3.8 THEN '优秀'
        WHEN GPA >= 3.5 THEN '良好'
        WHEN GPA >= 3.0 THEN '中等'
        WHEN GPA >= 2.0 THEN '及格'
        ELSE '不及格'
    END
FROM Students;
GO

事务处理

BEGIN TRY
    BEGIN TRANSACTION;
    
    -- 删除学生
    DELETE FROM Students WHERE StudentID = 100;
    
    -- 删除关联的课程注册
    DELETE FROM StudentCourses WHERE StudentID = 100;
    
    COMMIT TRANSACTION;
    PRINT '事务成功提交';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT '事务回滚: ' + ERROR_MESSAGE();
END CATCH
GO

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

7. 触发器、存储过程

存储过程

-- 创建简单存储过程
CREATE PROCEDURE GetStudentsByDepartment
    @DepartmentName NVARCHAR(50)
AS
BEGIN
    SELECT s.StudentID, s.FirstName, s.LastName, s.GPA
    FROM Students s
    INNER JOIN Departments d ON s.DepartmentID = d.DepartmentID
    WHERE d.DepartmentName = @DepartmentName
    ORDER BY s.LastName, s.FirstName;
END
GO

-- 带输出参数的存储过程
CREATE PROCEDURE GetDepartmentStatistics
    @DepartmentName NVARCHAR(50),
    @StudentCount INT OUTPUT,
    @AverageGPA DECIMAL(3,2) OUTPUT
AS
BEGIN
    SELECT 
        @StudentCount = COUNT(*),
        @AverageGPA = AVG(GPA)
    FROM Students s
    INNER JOIN Departments d ON s.DepartmentID = d.DepartmentID
    WHERE d.DepartmentName = @DepartmentName;
END
GO

-- 执行存储过程
EXEC GetStudentsByDepartment @DepartmentName = '计算机科学';
GO

DECLARE @Count INT, @AvgGPA DECIMAL(3,2);
EXEC GetDepartmentStatistics '计算机科学', @Count OUTPUT, @AvgGPA OUTPUT;
PRINT '学生数量: ' + CAST(@Count AS VARCHAR) + ', 平均GPA: ' + CAST(@AvgGPA AS VARCHAR);
GO

触发器

-- 创建AFTER触发器（记录学生表更改历史）
CREATE TRIGGER trg_Students_Audit
ON Students
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 插入操作
    IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
    BEGIN
        INSERT INTO StudentsAudit (StudentID, Action, ActionDate, ChangedBy)
        SELECT StudentID, 'INSERT', GETDATE(), SUSER_SNAME()
        FROM inserted;
    END
    
    -- 删除操作
    IF EXISTS(SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted)
    BEGIN
        INSERT INTO StudentsAudit (StudentID, Action, ActionDate, ChangedBy)
        SELECT StudentID, 'DELETE', GETDATE(), SUSER_SNAME()
        FROM deleted;
    END
    
    -- 更新操作
    IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
    BEGIN
        INSERT INTO StudentsAudit (StudentID, Action, ActionDate, ChangedBy)
        SELECT i.StudentID, 'UPDATE', GETDATE(), SUSER_SNAME()
        FROM inserted i
        INNER JOIN deleted d ON i.StudentID = d.StudentID;
    END
END
GO

-- 创建INSTEAD OF触发器（自定义插入逻辑）
CREATE TRIGGER trg_Students_InsteadOfInsert
ON Students
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO Students (FirstName, LastName, DateOfBirth, Email, GPA, DepartmentID, EnrollmentDate)
    SELECT 
        FirstName, 
        LastName, 
        DateOfBirth, 
        Email, 
        CASE WHEN GPA > 4.0 THEN 4.0 ELSE GPA END, -- 确保GPA不超过4.0
        DepartmentID,
        ISNULL(EnrollmentDate, GETDATE()) -- 如果未提供，使用当前日期
    FROM inserted;
END
GO

8. 数据库安全相关

用户和角色管理

-- 创建登录账户
CREATE LOGIN SchoolAdmin WITH PASSWORD = 'Str0ngP@ssw0rd!123';
GO

-- 在数据库中创建用户
USE SchoolDB;
GO
CREATE USER SchoolAdminUser FOR LOGIN SchoolAdmin;
GO

-- 创建数据库角色
CREATE ROLE StudentManager;
GO

-- 授予权限给角色
GRANT SELECT, INSERT, UPDATE ON Students TO StudentManager;
GRANT SELECT ON Departments TO StudentManager;
GO

-- 将用户添加到角色
ALTER ROLE StudentManager ADD MEMBER SchoolAdminUser;
GO

-- 撤销权限
REVOKE UPDATE ON Students FROM StudentManager;
GO

-- 拒绝特定权限
DENY DELETE ON Students TO StudentManager;
GO

行级安全性

-- 创建行级安全函数
CREATE FUNCTION fn_SecurityPredicate(@DepartmentID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS AccessResult
WHERE @DepartmentID = ISNULL(SESSION_CONTEXT(N'DepartmentFilter'), @DepartmentID);
GO

-- 创建安全策略
CREATE SECURITY POLICY DepartmentFilterPolicy
ADD FILTER PREDICATE fn_SecurityPredicate(DepartmentID) ON Students
WITH (STATE = ON);
GO

-- 设置会话上下文
EXEC sp_set_session_context @key = N'DepartmentFilter', @value = 1;
GO

数据加密

-- 创建数据库主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyP@ssw0rd!';
GO

-- 创建证书
CREATE CERTIFICATE StudentDataCertificate
WITH SUBJECT = 'Student SSN Encryption';
GO

-- 创建对称密钥
CREATE SYMMETRIC KEY StudentSSN_Key
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE StudentDataCertificate;
GO

-- 使用对称密钥加密数据
OPEN SYMMETRIC KEY StudentSSN_Key
DECRYPTION BY CERTIFICATE StudentDataCertificate;

UPDATE Students
SET SSN = EncryptByKey(Key_GUID('StudentSSN_Key'), SSN_PlainText);

CLOSE SYMMETRIC KEY StudentSSN_Key;
GO

-- 解密数据
OPEN SYMMETRIC KEY StudentSSN_Key
DECRYPTION BY CERTIFICATE StudentDataCertificate;

SELECT 
    StudentID,
    FirstName,
    LastName,
    CONVERT(NVARCHAR(11), DecryptByKey(SSN)) AS DecryptedSSN
FROM Students;

CLOSE SYMMETRIC KEY StudentSSN_Key;
GO

审计

-- 创建服务器审计
USE master;
GO
CREATE SERVER AUDIT Compliance_Audit
TO FILE (FILEPATH = 'C:\Audits\')
WITH (ON_FAILURE = SHUTDOWN);
GO

-- 启用服务器审计
ALTER SERVER AUDIT Compliance_Audit WITH (STATE = ON);
GO

-- 创建数据库审计规范
USE SchoolDB;
GO
CREATE DATABASE AUDIT SPECIFICATION StudentData_Audit
FOR SERVER AUDIT Compliance_Audit
ADD (SELECT, INSERT, UPDATE, DELETE ON Students BY PUBLIC)
WITH (STATE = ON);
GO