SQL Server 学习笔记速查手册：建库建表、视图索引与存储过程实战

简介这是一份面向数据库初学者与运维人员的 SQL Server 学习笔记以 doc 文档形式系统梳理了关系数据库管理系统的核心知识点适合零基础入门或需要快速查阅语法与概念的读者。压缩包内共 1 个 doc 文件约 499KB内容涵盖数据库对象的创建、删除与修改C/S 体系结构以及表、列、索引、触发器、存储过程等对象的容量限制。笔记还整理了关系数据库模型中的实体、属性、码、域等概念层次、关系、网状与面向对象四类数据模型以及主键、外键、默认值、check、unique 等约束的用法。系统数据库 Master、Model、Tempdb、Msdb、Pubs、Northwind 的作用数据文件、次要文件、日志文件与文件组等存储机制以及新建用户、角色与授权的操作也有涉及。目前已有 440 人学习适合作为课堂笔记补充或日常查阅手册。1. 从一份 SQL Server 学习笔记说起为什么我建议你把它当“速查手册”而不是“教材”如果你手头正放着一份 SQL Server 学习笔记大概率是两种情况要么刚接手一个老系统数据库跑在 SQL Server 上需要快速把语法和对象体系捡起来要么在准备面试或课程设计想找一份能覆盖建库、建表、约束、视图、索引、存储过程、游标的浓缩材料。这份笔记的价值不在于它有多“新”而在于它把 SQL Server 的核心对象和 T-SQL 语法用一条主线串了起来——从 Create/Drop/Alter 这类 DDL到 Select/Insert/Update/Delete 这类 DML再到 Grant/Revoke 权限控制最后落到约束、范式、存储过程和游标。它更像一本可以随时翻的速查手册而不是从头读到尾的教科书。适合谁适合已经有一点数据库概念、需要快速把 SQL Server 的“对象地图”和“语法骨架”搭起来的从业者。下面我按实际复现的顺序把这份笔记拆成能直接抄作业的步骤。2. 建库建表与数据类型把笔记里的语法变成可执行的 T-SQL2.1 数据库文件与文件组的落地写法笔记里提到数据库存储被映射成操作系统文件主要文件 .mdf、次要文件 .ndf、日志文件 .ldf还有文件组的概念。这部分如果只背概念实际建库时很容易翻车。常见做法是建库时显式指定数据文件和日志文件的初始大小、增长增量和最大大小避免默认配置在生产环境里把磁盘撑爆。-- 创建数据库显式指定数据文件和日志文件的存储参数 CREATE DATABASE ShopDB ON PRIMARY ( NAME ShopDB_Data, -- 逻辑文件名 FILENAME D:\SQLData\ShopDB.mdf, -- 物理路径按实际环境改 SIZE 10MB, -- 初始大小 MAXSIZE 500MB, -- 上限防止无限增长 FILEGROWTH 5MB -- 每次增长量 ) LOG ON ( NAME ShopDB_Log, FILENAME D:\SQLData\ShopDB_Log.ldf, SIZE 5MB, MAXSIZE 200MB, FILEGROWTH 5MB );逻辑说明ON PRIMARY 定义主文件组的数据文件LOG ON 定义事务日志文件。参数里 SIZE 是初始分配MAXSIZE 是硬上限FILEGROWTH 是自动增长步长。笔记里说“一般用户建立数据库时不指定可选项”这在学习环境没问题但一旦放到真实磁盘上不指定 MAXSIZE 就是给自己埋雷。文件组的作用是方便管理比如把频繁读写的表放到独立文件组备份和恢复时可以按文件组粒度操作。2.2 数据类型选择与建表约束笔记里列了 char、varchar、int、bigint、smallint、tinyint、numeric、money、datetime、table 这些类型。实际建表时char 和 varchar 的区别是高频踩坑点char(n) 是固定长度实际存储 n 个字节不足补空格varchar(n) 是可变长度存储实际字符字节数。如果拿 char(50) 存一个 3 个字符的姓名剩下 47 个位置全是空格查询时用等号匹配还可能因为尾部空格产生玄学结果。-- 建表时把主键、外键、默认值、check、unique 一次写全 CREATE TABLE Goods ( goods_id VARCHAR(10) NOT NULL, goods_name VARCHAR(50) NOT NULL, goods_type_name VARCHAR(20) DEFAULT 生活用品, goods_number INT NOT NULL, goods_price MONEY NOT NULL, bid_date DATETIME DEFAULT GETDATE(), CONSTRAINT PK_Goods PRIMARY KEY (goods_id), CONSTRAINT CK_Goods_Number CHECK (goods_number 0), CONSTRAINT UQ_Goods_Name UNIQUE (goods_name) );逻辑说明PRIMARY KEY 定义主键主键列不能为空且值唯一DEFAULT 在插入时未指定该列则自动填默认值CHECK 限制 goods_number 不能为负UNIQUE 保证商品名不重复。笔记里强调“插入的列值必须与表中的列个数和顺序匹配”所以更稳妥的写法是 insert 时显式列出列名而不是依赖顺序。-- 显式列名的插入避免顺序错位 INSERT INTO Goods (goods_id, goods_name, goods_number, goods_price) VALUES (G001, 课桌, 100, 199.00);参数说明goods_type_name 和 bid_date 没写会走 DEFAULT 约束。如果建表时这两列没有默认值且允许为空不写就是 NULL如果既不允许为空又没默认值这条 insert 直接失败。这就是笔记里说的“如果创建表时列的条件设置可以为空那么插入数据时没有此项数据可以以 null 代替”的实际含义。3. 视图、索引与存储过程三个最容易被“会用”掩盖的细节3.1 视图定义里的 With Check Option 到底卡住了什么笔记里给了好几个视图例子其中 With Check Option 是最容易只抄不理解的。它的作用是对视图做插入、修改时必须满足视图定义中 Where 子句的条件否则操作被拒绝。比如基于“电子产品”条件建的视图你往视图里插一条“生活用品”的记录With Check Option 会直接拦下来。-- 基于 Goods 表建视图只暴露电子产品并强制检查条件 CREATE VIEW Goods_Electronics_View AS SELECT goods_name, goods_type_name FROM Goods WHERE goods_type_name 电子产品 WITH CHECK OPTION;逻辑说明查询这个视图只能看到电子产品。如果尝试 INSERT 或 UPDATE 让 goods_type_name 变成非电子产品With Check Option 会报错。笔记里还提到定义视图的查询表达式不能含 INTO、ORDER BY、COMPUTE、COMPUTE BY不能引用临时表或表变量。这些限制在实际写视图时如果忽略创建语句直接失败报错信息往往不够直白需要回头逐条核对。3.2 簇索引与非簇索引的选型边界笔记里说簇索引与表的物理顺序相同一个表只能有一个簇索引非簇索引按完全不同的结构排序先找到数据位置再取数据。实际选型时簇索引一般给主键或最频繁的范围查询列因为簇索引决定了数据行的物理排列顺序。非簇索引适合高频的等值查询和覆盖查询。-- 唯一簇索引按 goods_id 物理排序 CREATE UNIQUE CLUSTERED INDEX IX_Goods_Id ON Goods (goods_id); -- 复合非簇索引覆盖 goods_id 和 goods_type_name 的查询 CREATE INDEX IX_Goods_Type ON Goods (goods_id, goods_type_name);参数说明UNIQUE 表示索引键值不允许重复CLUSTERED 表示簇索引ASC/DESC 控制排序方向。笔记里列了“需要创建索引的地方”经常搜索的列、需要排序的列、主键或外键、经常出现在 Where 里的列。但索引不是越多越好每个索引都会增加 insert、update、delete 的维护成本。我一般会先看执行计划确认全表扫描是瓶颈再补索引而不是建表时先把能建的索引全建一遍。3.3 存储过程的参数与嵌套层级笔记里提到一个存储过程最多 1024 个参数、32 级嵌套。实际写存储过程时参数命名用 前缀调用时按位置或按名称传参。下面这个存储过程模拟“修改商品库存并写入历史表”的操作对应笔记里 modify_process 的思路。CREATE PROCEDURE Modify_Goods_Process bid_user VARCHAR(10), bid_date DATETIME, bid_number INT, bid_goods_id VARCHAR(10), goods_price MONEY AS BEGIN -- 扣减库存 UPDATE Goods SET goods_number goods_number - bid_number WHERE goods_id bid_goods_id; -- 写入用户历史表 INSERT INTO User_History (user_name, amount, bid_date) VALUES (bid_user, goods_price * bid_number, bid_date); END; GO -- 调用 EXEC Modify_Goods_Process bid_user U001, bid_date 2025-01-01, bid_number 2, bid_goods_id G001, goods_price 199.00;逻辑说明存储过程把多条 SQL 封装成一个调用单元减少网络往返。参数用 声明调用时推荐按名称传参避免顺序错位。笔记里说“存储过程中的 SQL 语句已被封装所以无法对其所引用的表进行存在性验证”意思是创建存储过程时不会检查引用的表是否存在只有执行时才报错。这个特性在重构表结构时特别容易踩坑表改了名存储过程创建时不报错运行时才炸。4. 避坑与排查这份笔记里没写透的五个翻车点4.1 现象插入中文变成问号原因varchar 没指定排序规则或列长度不够解决改用 nvarchar 并确认数据库排序规则笔记里只列了 char 和 varchar没提 nvarchar。实际存中文时如果数据库排序规则不是中文相关varchar 可能丢字符。常见做法是中文列用 nvarchar(n)n 按字符数算不是字节数。如果已经建了 varchar 列改类型用 ALTER TABLE ... ALTER COLUMN但要注意长度只能增大不能减小且改类型前要确认没有索引依赖。4.2 现象删除列时报错“对象依赖于列”原因该列被约束、索引或默认值绑定解决先删约束再删列笔记里明确写了“要删除商品数量需要先删除商品的约束条件”。实际操作顺序是先 ALTER TABLE ... DROP CONSTRAINT 约束名再 ALTER TABLE ... DROP COLUMN 列名。如果列上还有索引也要先 DROP INDEX。被复制列、用在索引中的列、有绑定默认值或规则的列都不能直接删。4.3 现象视图更新失败原因视图定义里含聚合函数、DISTINCT、GROUP BY、UNION 或派生列解决改基表或重建可更新视图笔记里列了更新视图必须满足的条件From 子句至少引用一个表选择列表没有派生列没有聚合函数不含 TOP、GROUP BY、UNION、DISTINCT。如果视图不满足这些条件Update 语句会直接失败。排查方法是看视图定义逐条对照这些限制。4.4 现象存储过程调用报参数数量不匹配原因按位置传参时漏传或顺序错解决改用按名称传参笔记里说存储过程最多 1024 个参数但没说调用时的传参方式。按位置传参时参数顺序必须和定义完全一致漏一个就报错。按名称传参用 参数名 值 的形式顺序可以打乱可读性也更好。我一般写存储过程调用时强制用名称传参尤其是参数超过三个的时候。4.5 现象游标循环结束后资源未释放原因只 Close 没 Deallocate解决Close 之后必须 Deallocate笔记里的游标示例有 OPEN、FETCH、CLOSE、DEALLOCATE。Close 只是关闭游标释放结果集Deallocate 才是释放游标占用的资源。如果只 Close 不 Deallocate游标定义还在重复执行会报“游标已存在”。排查方法是查 sys.dm_exec_cursors 看当前会话的游标状态。5. 从范式到模糊查询把笔记里的零散知识点串成一条可复用的排查链5.1 用范式检查表设计第一范式到 BCNF 的落地判断笔记里把第一范式、第二范式、第三范式、BCNF 都过了一遍。实际工作中不需要背定义而是用它们当检查清单。第一范式每一列不可再分不能有组合列。第二范式非主属性完全依赖码不能只依赖联合主键的一部分。第三范式不存在非主属性对码的传递依赖。BCNF所有决定因素都包含码。下面这张表可以用来快速判断。范式检查问题典型违规修正方式1NF列是否可再分一列存多个商品类别拆列或拆行2NF非主属性是否完全依赖码联合主键下部分依赖拆表把部分依赖列移出3NF是否存在传递依赖A→B→CC 不依赖 A把 B、C 拆到独立表BCNF所有决定因素是否包含码非码列决定码列进一步拆表判断顺序是先看列是否原子再看主键和依赖关系最后看传递依赖。笔记里用“商品编号、商品所属类别”的例子演示了第一范式的拆分实际项目中遇到最多的是第二范式和第三范式问题尤其是联合主键表里塞了不该塞的描述性字段。5.2 模糊查询与数据导出两个高频需求的写法笔记最后提到模糊查询和数据导出。模糊查询用 LIKE 配合通配符% 匹配任意字符_ 匹配单个字符。如果关键字在列中间用 %关键字%如果只匹配开头用 关键字%。下面这个查询把标题匹配关键字的排前面不匹配但包含关键字的排后面对应笔记里 union 的写法。-- 标题以关键字开头的优先其余包含关键字的排后 SELECT title, 1 AS rank_order FROM MyDB WHERE title LIKE 关键字% UNION ALL SELECT title, 2 AS rank_order FROM MyDB WHERE title NOT LIKE 关键字% AND CHARINDEX(关键字, title) 0 ORDER BY rank_order, title;逻辑说明UNION ALL 不去重比 UNION 快rank_order 用来控制排序优先级CHARINDEX 返回关键字在字符串中的位置大于 0 表示包含。数据导出可以用 SQL Server 的导入导出向导也可以用 SELECT ... INTO OUTFILE 的思路先查出来再走前台导出。笔记里说“可以通过前台来实现数据的导出”实际就是应用层查数据后写文件数据库层只负责查询。5.3 权限管理与系统数据库授权和角色别混着用笔记里提到授权通过新建用户实现Sp_addlogin、Sp_adduser、Sp_addrole 是三个不同层级的操作。登录名是服务器级用户是数据库级角色是权限集合。常见做法是先建登录名再映射到数据库用户然后把用户加入角色最后给角色授权。Grant 给权限Revoke 回收权限。系统数据库里 Master 存登录账号和配置Model 是新建数据库的模板Tempdb 存临时表Msdb 存调度作业和警报信息。Pubs 和 Northwind 是示例库新版本里默认不一定装需要单独附加。从那以后我每次拿到一份数据库学习笔记都会先挑一个最小场景把建库、建表、约束、视图、索引、存储过程、游标各跑一遍跑不通的地方才是真正需要补的。希望帮到你。本文还有配套的精品资源点击获取