SQL Server 全知识体系导图_sql server 数据库知识体系-CSDN博客

CREATE DATABASE SalesDB
ON PRIMARY (
    NAME = SalesData,
    FILENAME = 'D:\Data\SalesDB.mdf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 50MB
)
LOG ON (
    NAME = SalesLog,
    FILENAME = 'E:\Logs\SalesDB.ldf',
    SIZE = 50MB,
    MAXSIZE = 2GB,
    FILEGROWTH = 25%
);

详细解释

接下来是ON PRIMARY子句，这里定义了主文件组中的文件。主文件组是默认的，通常包含主要数据文件（.mdf）。NAME指定了逻辑文件名SalesData，FILENAME是物理路径D:\Data\SalesDB.mdf。SIZE设置为100MB，这是初始大小。MAXSIZE=UNLIMITED意味着文件可以无限增长，但需要注意磁盘空间限制。FILEGROWTH=50MB表示每次增长50MB，当空间不足时自动扩展。

然后是LOG ON子句，定义事务日志文件。逻辑名称SalesLog，物理路径E:\Logs\SalesDB.ldf。初始大小50MB，最大2GB，增长25%。这里需要解释为什么日志文件通常比数据文件小，以及百分比增长和固定值增长的优缺点。

右键点击数据库—>新建数据库

2.表约束示例

创建测试表，主键自增，时间自动添加

CREATE TABLE [dbo].[test] (
  [id] int  IDENTITY(1,1) NOT NULL,
  [data] float(53)  NULL,
  [types] int  NULL,
  [insert_time] datetime DEFAULT getdate() NULL
)

非聚集索引

非聚集索引是数据库优化的重要工具，权衡利弊再选择，请看示例以及解释

-- 为 insert_time 字段创建非聚集索引
CREATE NONCLUSTERED INDEX IDX_test_insert_time ON [dbo].[test] ([insert_time]);

-- 为 types 字段创建非聚集索引
CREATE NONCLUSTERED INDEX IDX_test_log_types ON [dbo].[test] ([types]);

好处

允许为非主键列创建索引，支持复杂查询条件（如 WHERE name LIKE 'A%'）

可创建多个非聚集索引，适应不同查询场景（如按姓名、邮箱、日期等查询）

通过 INCLUDE 子句包含非键列，形成覆盖索引，避免回表查询（Covering Index）

自动优化 ORDER BY 和 GROUP BY 操作（需匹配索引列顺序）

示例：SELECT ProductID, SUM(Quantity) FROM Sales GROUP BY ProductID

索引结构与数据行物理分离，适合频繁查询但少更新的列

避免因数据插入导致聚集索引频繁分裂（如自增主键外的其他列）

坏处

每个索引占用独立存储空间（约等于索引列大小 + 指针）

内存需缓存索引页，过多索引可能导致内存压力

INSERT/UPDATE/DELETE 需维护所有相关索引

示例：更新主键时，聚集索引和非聚集索引均需更新指针

索引碎片（Fragmentation）需定期重建或重组

统计信息更新频率增加，影响自动优化器决策

非聚集索引需通过书签查找（Bookmark Lookup）获取数据行

堆表（无聚集索引）使用 RID 指针，聚集表使用聚集键指针

二、T-SQL 高级编程

1.窗口函数查询解析

SELECT 
    EmployeeID,
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS SalaryRank,
    AVG(Salary) OVER (PARTITION BY DepartmentID) AS DeptAvgSalary
FROM Employees;

核心功能

ROW_NUMBER() OVER (ORDER BY Salary DESC)

为每个员工生成一个全局排名（从1开始），按薪资降序排列

示例结果：最高薪员工SalaryRank=1，次高=2，依此类推

AVG(Salary) OVER (PARTITION BY DepartmentID)

按部门分区计算薪资平均值

每个部门内的所有员工显示相同的部门平均薪资

2.存储过程带事务

CREATE PROCEDURE TransferFunds
    @FromAccount INT,
    @ToAccount INT,
    @Amount DECIMAL(18,2)
AS
BEGIN
    BEGIN TRANSACTION
    UPDATE Accounts SET Balance -= @Amount WHERE AccountID = @FromAccount
    UPDATE Accounts SET Balance += @Amount WHERE AccountID = @ToAccount
    COMMIT TRANSACTION
END

转账存储过程解析

BEGIN TRANSACTION + COMMIT TRANSACTION

保证两个UPDATE操作的原子性：要么全部成功，要么全部回滚

防止因程序崩溃/断电导致单边扣款问题

输入参数：@FromAccount, @ToAccount, @Amount

防止SQL注入攻击，提高代码可维护性

先从转出账户扣除金额

再向转入账户增加金额

金额字段使用精确数值类型DECIMAL(18,2)