简介数据库课后习题答案第四版是一份面向数据库原理课程学习者的PDF文档适合本科生、自考或备考人员用于巩固知识、检验掌握程度。内容覆盖SQL Server 2000常用管理工具服务管理器、企业管理器、查询分析器、Windows与SQL Server两种身份验证模式以及使用T-SQL语句创建、修改、删除数据库和数据表的操作重点涉及主数据文件、次要数据文件、事务日志文件三类文件类型并配有S_T、company等经典数据库实例的习题与实验要点解析可与常见数据库实验手册配合使用。整个资源共1个PDF文件压缩包大小仅856KB轻量便携、便于随时查阅。目前已有1829人次学习下载在数据库初学者中有一定参考价值。通过习题解析与实验要点梳理读者可系统回顾数据库建设与查询的关键步骤加深对主键、外键、UNIQUE、CHECK等约束的理解有效提升备考效率。1. 拿到这份数据库实验手册先别急着敲键盘这份《数据库课后习题答案(第四版)》PDF核心内容其实是 SQL Server 2000 的六个实验从启动服务、连查询分析器开始一路做到创建数据库、建表加约束、单表查询、分组聚合和多表连接。别被“2000”这个年份劝退至今仍有一批制造业和中小企业的 ERP 跑在这套老引擎上而且数据库面试题库里大量关于身份验证、文件类型和外键约束的老题都能在这份手册里找到原始出处。手册用三套数据把知识点串起来pubs 是微软自带的样例库用来熟悉工具S_T 是经典的“学生-课程-选课”三表结构覆盖数据库原理教材的核心例题company 是一个订单销售库五张表的关系比 S_T 更接近真实业务。要交数据库课程设计报告的学生、要维护老系统的工程师或者想快速过一遍 SQL 增删改查和查询优化的初级 DBA都可以照着这份手册练。2. 环境搭建与身份验证服务管理器、企业管理器、查询分析器的分工2.1 三个管理工具各自负责什么SQL Server 2000 的日常操作被拆成了三个工具拆法到今天还在影响 SQL Server 的产品形态。服务管理器负责启动、暂停和停止 MSSQLSERVER 服务装完实例后服务默认不是开机自启要先用它把服务点亮否则后面所有连接都会报“服务器不存在或访问被拒绝”。企业管理器相当于今天的 SSMS 对象资源管理器左边树状目录列出服务器、数据库、表、视图右边内容窗口显示选中对象的明细右键菜单里几乎能找到全部管理功能。查询分析器就是写 T-SQL 的地方和 SSMS 的查询窗口一样支持多结果集、语法着色、执行计划和索引提示。这三个工具的分工可以记成一句话服务管理器管进程企业管理器管对象查询分析器管语句。实验一要求把树状窗口逐层展开、挨个右键点菜单看起来像熟悉界面实际是在建立“对象树到操作入口”的映射关系。后面用 T-SQL 建库建表时回头还要在企业管理器里刷新确认两条路径都通才算真正掌握。2.2 两种身份验证模式与第一次连库SQL Server 2000 支持两种登录方式这也是数据库原理考试里的老考点。Windows 身份验证直接使用操作系统登录令牌SQL Server 不校验密码适合域内环境和本机学习SQL Server 身份验证由 SQL Server 自己维护登录名和密码适合程序通过连接字符串访问数据库的场景。两者判断依据和适用场景对比如下。身份验证模式校验依据适用场景主要风险Windows 身份验证Windows 登录令牌域环境、单机学习跨域或非域机器可能拿不到令牌SQL Server 身份验证登录名 密码程序连接、异构客户端sa 空密码易被扫描暴破实验一用的是 Windows 身份验证登录查询分析器再打开 pubs 数据库跑 EXEC sp_help。这里有个容易忽略的细节企业管理器注册服务器时选的验证模式与查询分析器登录时用的模式如果不一致会直接报 18456 登录失败。我一般会先在服务管理器确认服务已启动再用 SQL Server 身份验证登一次排除密码问题然后切回 Windows 验证两步就能定位是服务没起来还是模式不匹配。2.2.1 用查询分析器连接 pubs 并验证环境登录后第一件事是确认当前上下文。手册里第 4 到第 6 步是连上 pubs 样例库并执行三条语句USE pubs GO EXEC sp_help GO SELECT * FROM authors GOUSE 把当前数据库上下文切到 pubsGO 表示一个批处理的结束SQL Server 2000 的查询分析器里很多语句必须分批提交。EXEC sp_help 不带参数时返回当前数据库所有对象的名字、类型和创建时间比在企业管理器里逐层展开要快得多SELECT * FROM authors 验证连接是否真正可用。实验一第 7 步要求从“帮助→目录与索引”打开联机丛书分别查 sp_help、exec、select 三个关键字这一步很多人直接跳过但它是学习老版本函数签名的唯一入口。2.3 用 pubs 建立实验数据基线手册实验一后半段要求手动创建 S_T 数据库再建 student、course、sc 三张表并录入数据。这块内容我在后面展开这里先给一个操作顺序建议先在企业管理器左侧右键“数据库”新建 S_T再切到查询分析器用脚本建表最后回到企业管理器刷新看结果。图形界面和脚本交替使用既能看到对象变化又能练 T-SQL 语法数据库增删改查的完整流程就是这样一遍一遍跑熟的。3. 数据库物理设计CREATE DATABASE、文件组与容量参数3.1 三种文件类型决定数据库的物理布局CREATE DATABASE 在 SQL Server 2000 里不是简单生成一个文件而是一套文件规划。主数据文件扩展名 .mdf是数据库的起点系统表也放在里面每个库有且只有一个次要数据文件扩展名 .ndf用来扩充存储空间可以建多个也可以分散到不同物理磁盘事务日志文件扩展名 .ldf顺序记录所有更改操作用于崩溃恢复和还原。三种文件的区别几乎是数据库原理课程和面试题库的标配题。文件类型扩展名数量作用主数据文件.mdf每个数据库 1 个存储系统信息和用户数据库的起点次要数据文件.ndf0 到多个扩展存储空间可跨磁盘分布事务日志文件.ldf至少 1 个记录事务日志用于恢复和还原理解 .ndf 的意义对数据库优化很重要。常见做法是把数据文件放到高速磁盘、日志文件放到另一块盘写日志和读数据互不抢占 I/O当单文件超过操作系统限制或需要把不同表分到不同磁盘时再引入次要数据文件。3.2 用 T-SQL 创建带容量参数的 testdb手册实验二示例一是创建 testdb包含一个初始 2MB、最大 8MB、按 1MB 递增的数据文件以及一个初始 1MB、最大 5MB 的日志文件CREATE DATABASE testdb ON ( NAME testdb_data, FILENAME d:\DATA\testdb_data.mdf, SIZE 2MB, MAXSIZE 8MB, FILEGROWTH 1MB ) LOG ON ( NAME testdb_log, FILENAME d:\DATA\testdb_log.ldf, SIZE 1MB, MAXSIZE 5MB, FILEGROWTH 1MB ) GONAME 是逻辑文件名T-SQL 语句里引用的都是这个名字FILENAME 是操作系统物理路径目录必须事先存在否则报错 5123。SIZE 是初始容量MAXSIZE 限制文件上限超过会停止增长并报 1105 磁盘空间错误FILEGROWTH 是自动增长步长可以按 MB 递增也可以写成百分比。实验二第 2 题要求 company 数据文件初始 5MB、最大 15MB、步长 1MB日志初始 5MB、最大 10MB、步长 1MB照这个模板替换参数即可。需要提醒的是MAXSIZE 和 FILEGROWTH 不是越大越好。自动增长会带来文件碎片和额外的 I/O 开销生产环境一般会预先分配足够空间把自动增长当作兜底手段而不是常态学习环境为了演示容量参数反而可以特意设小观察它抛错这对理解文件增长机制很有帮助。3.3 ALTER DATABASE 修改文件、文件组与删除库实验二后半段要求对 testdb 添加一个数据文件 testdb2_data初始 1MB、最大 5MB、步长 1MBALTER DATABASE testdb ADD FILE ( NAME testdb2_data, FILENAME d:\DATA\testdb2_data.ndf, SIZE 1MB, MAXSIZE 5MB, FILEGROWTH 1MB ) GO新建文件时把扩展名写成 .ndf和主文件区分开。ALTER DATABASE ADD FILE 只加文件不加文件组时文件会落到默认文件组 PRIMARY 里如果要把文件放进自定义文件组需要先建组再挂文件。实验二第 4 题的 company 库就是这么设计的ALTER DATABASE company ADD FILEGROUP TempGroup GO ALTER DATABASE company ADD FILE ( NAME company3_data, FILENAME d:\DATA\company3_data.ndf, SIZE 3MB, MAXSIZE 10MB, FILEGROWTH 1MB ) TO FILEGROUP TempGroup GOTO FILEGROUP 把数据文件关联到指定文件组文件组可以理解为“磁盘目录”的逻辑抽象。把数据文件和日志文件放在不同文件组、甚至不同物理磁盘上能有效降低 I/O 竞争这是数据库课程设计和生产调优都会用到的思路。删除操作分两档删除数据文件用 ALTER DATABASE ... REMOVE FILE只能删空文件文件里还有数据时会报错删除整个数据库用 DROP DATABASE执行前要确认没有活动连接。实验二要求先删除 company 再用默认设置重建默认设置就是直接写 CREATE DATABASE company不写 ON 和 LOG ON所有文件参数取系统默认值这样操作能直观看到两种建库方式的差别。4. 表与约束设计主键、CHECK、UNIQUE 与自引用外键4.1 学生选课库三张表的主键与检查约束S_T 数据库的 student、course、sc 三张表是数据库原理教材的经典案例。建表前先理清约束的类型和定义位置后面写脚本才不会乱。约束类型作用定义位置典型写法PRIMARY KEY主键唯一且非空列级或表级CONSTRAINT PK_sc PRIMARY KEY (Sno, Cno)FOREIGN KEY外键保证参照完整性表级FOREIGN KEY (Sno) REFERENCES student(Sno)CHECK检查约束限制取值范围列级或表级CHECK (Sage BETWEEN 14 AND 38)UNIQUE唯一约束允许 NULL 但不重复表级UNIQUE (invoice_no)student 表以 Sno 为主键学号限定为 5 位数字性别只能取“男”或“女”年龄限制在 14 到 38 岁course 表以 Cno 为主键课程号同样限定为数字sc 表以 (Sno, Cno) 组合作为主键成绩限定在 0 到 100。建表语句如下CREATE TABLE student ( Sno CHAR(5) NOT NULL CONSTRAINT PK_student PRIMARY KEY CONSTRAINT CK_student_sno CHECK (Sno LIKE [0-9][0-9][0-9][0-9][0-9]), Sname VARCHAR(20) NOT NULL, Ssex CHAR(2) NOT NULL CONSTRAINT CK_student_sex CHECK (Ssex IN (男, 女)), Sage SMALLINT NOT NULL CONSTRAINT CK_student_age CHECK (Sage BETWEEN 14 AND 38), Sdept VARCHAR(20) NULL ) GO CREATE TABLE course ( Cno CHAR(4) NOT NULL PRIMARY KEY, Cname VARCHAR(40) NOT NULL, Cpno CHAR(4) NULL, Ccredit SMALLINT NOT NULL ) GO CREATE TABLE sc ( Sno CHAR(5) NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALLINT NULL CONSTRAINT CK_sc_grade CHECK (Grade BETWEEN 0 AND 100), CONSTRAINT PK_sc PRIMARY KEY (Sno, Cno) ) GOCHECK 约束在 SQL Server 2000 里没有正则支持学号校验只能用 LIKE [0-9][0-9][0-9][0-9][0-9] 这种字符集匹配方括号里写字符范围每位对应一个字符。组合主键必须用表级约束声明写在所有列定义之后取名为 PK_sc 方便后面按名字删除。学号、课程号这类编号字段用 CHAR 而不是 VARCHAR定长字符串少了长度判断索引查找更稳定这也是数据库增删改查场景里的一个实用细节。4.2 course 表的自引用外键course 表的 Cpno 存“先行课编号”参照的是 course 表自己的主键 Cno这种设计叫自引用外键。实验三要求为 Cpno 添加外键约束 FK_CpnoALTER TABLE course ADD CONSTRAINT FK_Cpno FOREIGN KEY (Cpno) REFERENCES course(Cno) GO外键保证了 Cpno 里出现的值一定存在于 Cno 列不会出现“引用了不存在的课程”这种脏数据。自引用表在插入时有顺序要求必须先插父课程再插子课程否则外键检查直接拒绝删除时反过来先删子行再删父行或者先把 Cpno 置 NULL。菜单表、部门树、分类表都是这种结构遇到这类表的数据维护我一般会写一个递归或分批的录入脚本而不是手动逐条插。sc 表的外键分别参照 student 和 course 两表用两条 ALTER 语句完成ALTER TABLE sc ADD CONSTRAINT FK_Sno FOREIGN KEY (Sno) REFERENCES student(Sno) GO ALTER TABLE sc ADD CONSTRAINT FK_Cno FOREIGN KEY (Cno) REFERENCES course(Cno) GO4.3 company 库的约束设计与一个列宽陷阱company 库五张表的关系更贴近真实销售系统employee 员工、customer 客户、sales 销售主表、sale_item 销售明细、product 产品。销售主表连员工和客户销售明细连销售主表和产品构成两张经典的一对多关系。实验三要求给 sales 添加发票号码列再补外键、唯一约束和检查约束ALTER TABLE sales ADD invoice_no CHAR(10) NOT NULL GO给已有数据的表加 NOT NULL 列SQL Server 2000 会填上空字符串让语句执行成功这个行为很容易被忽略。常见做法是分三步先加可空列UPDATE 填上真实值再 ALTER COLUMN 改成 NOT NULL避免产生一批发票号为空的历史数据。列宽陷阱在 employee 表上更明显。手册里 emp_no 定义为 char(5)但检查约束却要求“E 开头后面跟 5 位数字”E00001 这样的编号至少需要 6 个字符char(5) 根本放不下。这是原始实验文档里一个真实的定义矛盾。处理方式是把列改成 char(6)再挂约束ALTER TABLE employee ALTER COLUMN emp_no CHAR(6) NOT NULL GO ALTER TABLE employee ADD CONSTRAINT CK_emp_no CHECK (emp_no LIKE E[0-9][0-9][0-9][0-9][0-9]), CONSTRAINT CK_sex CHECK (sex IN (男, 女)), CONSTRAINT CK_salary CHECK (salary BETWEEN 1000 AND 10000) GO ALTER TABLE sales ADD CONSTRAINT FK_sale_id FOREIGN KEY (sale_id) REFERENCES employee(emp_no), CONSTRAINT FK_cust_id FOREIGN KEY (cust_id) REFERENCES customer(cust_id), CONSTRAINT UN_inno UNIQUE (invoice_no), CONSTRAINT CK_inno CHECK (invoice_no LIKE I[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]) GO一条 ALTER TABLE 可以挂多个约束逗号分隔。FOREIGN KEY 保证子表值必须存在于父表主键UNIQUE 保证发票号全表不重复CHECK 限定格式。给约束起名字CK_xxx、FK_xxx、UN_xxx不是为了好看而是后面做数据库优化或数据清理时靠名字就能知道约束管的是哪一列不用逐条去翻定义。5. 查询与聚合分析WHERE 过滤、GROUP BY 分组与 ROLLUP5.1 SELECT 基础子句TOP、DISTINCT、LIKE 与排序单表查询的核心是四个子句的组合SELECT 决定输出列WHERE 决定行范围ORDER BY 决定排序TOP 限制返回行数。手册实验四里的几条语句覆盖了最常用的写法SELECT emp_no, emp_name, dept, salary FROM employee WHERE emp_name LIKE 王% ORDER BY salary ASC GO SELECT TOP 10 PERCENT * FROM sales ORDER BY tot_amt DESC GO SELECT DISTINCT dept FROM employee GOLIKE 王% 匹配姓王的员工% 匹配任意长度字符下划线“_”匹配单个字符能精确控制字数。TOP 10 PERCENT 返回前 10% 行不写 PERCENT 时按数量取TOP 必须配合 ORDER BY 才有确定意义否则返回的行随机。DISTINCT 做整行去重SELECT DISTINCT dept 取到的就是部门清单“DISTINCT 单列 其他多列”这种混写会按整行判断达不到预期查重时直接写 GROUP BY 更清晰。WHERE 里还能组合 BETWEEN、IN、IS NULL 和逻辑运算。IN 适合离散值列表BETWEEN 适合闭区间范围IS NULL 判断空值。一个容易犯的错用 NULL 判断空值结果永远为空必须写 IS NULL。5.2 GROUP BY 与 HAVING 的分组逻辑聚合函数 SUM、COUNT、AVG、MAX、MIN 与 GROUP BY 配合能按维度做汇总统计。写分组查询前先记住子句的执行顺序很多逻辑错误都出在这里。子句或函数作用执行阶段WHERE行级过滤分组前GROUP BY按列分组行过滤后HAVING分组后过滤分组后SUM / AVG / COUNT汇总计算分组时ORDER BY排序全部计算完成后手册实验五第 3 题是“找出销售业绩超过 10000 元的业务员编号及业绩”SELECT sale_id, SUM(tot_amt) AS 销售业绩 FROM sales GROUP BY sale_id HAVING SUM(tot_amt) 10000 ORDER BY 销售业绩 DESC GO执行顺序是先 WHERE 过滤、再 GROUP BY 分组、然后 HAVING 过滤组、最后 ORDER BY 排序。因此 WHERE 里不能写聚合函数HAVING 里才能写 SUM(...) 这类表达式ORDER BY 可以直接用别名“销售业绩”SQL Server 2000 支持这个写法。如果还要统计各部门人数和平均薪水就是 COUNT(*) 和 AVG(salary) 的组合GROUP BY dept 一行就能拿到结果。5.3 派生表与加权平均单价手册实验五第 4 题要求“计算每一产品销售数量总和与平均销售单价”。这里有个经典陷阱单价不能直接 AVG(unit_price)因为每笔销售数量不同加权平均才准确SELECT prod_id, SUM(qty) AS 总数量, SUM(qty * unit_price) / SUM(qty) AS 加权平均单价 FROM sale_item GROUP BY prod_id GOAVG(unit_price) 只是对所有订单行做算术平均忽略了数量权重SUM(qty * unit_price) / SUM(qty) 才是真实成交均价。数据库课程设计的统计报表里这个区别直接影响结论很多学生在这里丢分。需要按月统计时在 SELECT 和 GROUP BY 里都写 MONTH(order_date)就能得到“产品 月份”的交叉汇总。5.4 CUBE 与 ROLLUP多维汇总的差异手册实验五第 5、6 题要求统计“各部门不同性别、或各部门、或所有员工”的平均薪水对应 SQL Server 2000 的多维汇总语法SELECT dept, sex, AVG(salary) AS 平均薪水 FROM employee GROUP BY dept, sex WITH ROLLUP GO SELECT dept, sex, AVG(salary) AS 平均薪水 FROM employee GROUP BY dept, sex WITH CUBE GOWITH ROLLUP 按分组列从左到右逐层汇总产生“deptsex 细目 → dept 小计 → 总计”三档结果WITH CUBE 在 ROLLUP 基础上额外生成“sex 维度的小计”即所有列组合的小计结果里会出现 NULL 表示“该维度的全部”。新版 SQL Server 把语法改成了 GROUP BY ROLLUP(C1, C2) 和 GROUP BY CUBE(C1, C2)语义不变。面试题里问“ROLLUP 和 CUBE 的区别”本质就是考察ROLLUP 只有分层小计CUBE 包含全部组合的小计。6. 多表连接查询与老版本 SQL 的兼容排错6.1 内连接与自身连接连接查询是数据库面试题的重灾区。内连接只保留两表匹配的行自身连接则是一张表和自己匹配。手册实验六第 1 题要查 employee 表中“部门相同且住址相同”的女员工必须用自身连接SELECT a.emp_no, a.emp_name, a.sex, a.title, a.salary, a.addr FROM employee a INNER JOIN employee b ON a.addr b.addr AND a.emp_no ! b.emp_no WHERE a.sex 女 AND b.sex 女 GO自身连接必须给表起别名a、b否则无法区分同名列连接条件里要有 a.emp_no ! b.emp_no否则每行都会和自己匹配。这段 SQL 的坑在于 ON 里写了列值比较、WHERE 里又写性别条件混着混着一不小心就把连接条件漏掉结果集变成笛卡尔积。排查这类问题时先数结果行数如果行数接近两表行数之积多半是连接条件丢了。6.2 三种外连接的差异连接类型返回内容典型用途INNER JOIN两表都匹配的行有对应关系的业务明细LEFT JOIN左表全部 右表匹配行以左表为主体补右表信息RIGHT JOIN右表全部 左表匹配行以右表为主体补左表信息FULL JOIN两表全部行无匹配补 NULL数据对账、差异分析LEFT JOIN 的重点是 NULL 扩展右表没匹配上时右表列全部为 NULL。手册实验六第 8 题要求分别用左外、右外、全外连接查 product 和 sale_item 中单价高于 2400 元的相同产品结果集行数会明显不同。写 WHERE 时要注意对右表列加过滤条件会“吃掉”NULL 行让外连接退化成内连接这是做数据对账时最常见的逻辑错误。6.3 被遗忘的 * 符号与迁移排错SQL Server 2000 时代还流行一种旧式外连接写法WHERE 里用 表示左外连接表示右外连接。老代码里经常能看到SELECT p.prod_id, s.qty FROM product p, sale_item s WHERE p.prod_id * s.prod_id GO这个语法从 SQL Server 2005 开始不再支持新版本直接报语法错误。接手老项目时用文本搜索把全库的 和 找出来改成 LEFT JOIN / RIGHT JOIN是迁移兼容检查的第一步。改完做回归对拍跑一遍旧环境的查询结果再跑一遍新写法逐行比对结果集。本文还有配套的精品资源点击获取 SQL Server数据库实验手册：从建库到多表查询的完整实践
简介数据库课后习题答案第四版是一份面向数据库原理课程学习者的PDF文档适合本科生、自考或备考人员用于巩固知识、检验掌握程度。内容覆盖SQL Server 2000常用管理工具服务管理器、企业管理器、查询分析器、Windows与SQL Server两种身份验证模式以及使用T-SQL语句创建、修改、删除数据库和数据表的操作重点涉及主数据文件、次要数据文件、事务日志文件三类文件类型并配有S_T、company等经典数据库实例的习题与实验要点解析可与常见数据库实验手册配合使用。整个资源共1个PDF文件压缩包大小仅856KB轻量便携、便于随时查阅。目前已有1829人次学习下载在数据库初学者中有一定参考价值。通过习题解析与实验要点梳理读者可系统回顾数据库建设与查询的关键步骤加深对主键、外键、UNIQUE、CHECK等约束的理解有效提升备考效率。1. 拿到这份数据库实验手册先别急着敲键盘这份《数据库课后习题答案(第四版)》PDF核心内容其实是 SQL Server 2000 的六个实验从启动服务、连查询分析器开始一路做到创建数据库、建表加约束、单表查询、分组聚合和多表连接。别被“2000”这个年份劝退至今仍有一批制造业和中小企业的 ERP 跑在这套老引擎上而且数据库面试题库里大量关于身份验证、文件类型和外键约束的老题都能在这份手册里找到原始出处。手册用三套数据把知识点串起来pubs 是微软自带的样例库用来熟悉工具S_T 是经典的“学生-课程-选课”三表结构覆盖数据库原理教材的核心例题company 是一个订单销售库五张表的关系比 S_T 更接近真实业务。要交数据库课程设计报告的学生、要维护老系统的工程师或者想快速过一遍 SQL 增删改查和查询优化的初级 DBA都可以照着这份手册练。2. 环境搭建与身份验证服务管理器、企业管理器、查询分析器的分工2.1 三个管理工具各自负责什么SQL Server 2000 的日常操作被拆成了三个工具拆法到今天还在影响 SQL Server 的产品形态。服务管理器负责启动、暂停和停止 MSSQLSERVER 服务装完实例后服务默认不是开机自启要先用它把服务点亮否则后面所有连接都会报“服务器不存在或访问被拒绝”。企业管理器相当于今天的 SSMS 对象资源管理器左边树状目录列出服务器、数据库、表、视图右边内容窗口显示选中对象的明细右键菜单里几乎能找到全部管理功能。查询分析器就是写 T-SQL 的地方和 SSMS 的查询窗口一样支持多结果集、语法着色、执行计划和索引提示。这三个工具的分工可以记成一句话服务管理器管进程企业管理器管对象查询分析器管语句。实验一要求把树状窗口逐层展开、挨个右键点菜单看起来像熟悉界面实际是在建立“对象树到操作入口”的映射关系。后面用 T-SQL 建库建表时回头还要在企业管理器里刷新确认两条路径都通才算真正掌握。2.2 两种身份验证模式与第一次连库SQL Server 2000 支持两种登录方式这也是数据库原理考试里的老考点。Windows 身份验证直接使用操作系统登录令牌SQL Server 不校验密码适合域内环境和本机学习SQL Server 身份验证由 SQL Server 自己维护登录名和密码适合程序通过连接字符串访问数据库的场景。两者判断依据和适用场景对比如下。身份验证模式校验依据适用场景主要风险Windows 身份验证Windows 登录令牌域环境、单机学习跨域或非域机器可能拿不到令牌SQL Server 身份验证登录名 密码程序连接、异构客户端sa 空密码易被扫描暴破实验一用的是 Windows 身份验证登录查询分析器再打开 pubs 数据库跑 EXEC sp_help。这里有个容易忽略的细节企业管理器注册服务器时选的验证模式与查询分析器登录时用的模式如果不一致会直接报 18456 登录失败。我一般会先在服务管理器确认服务已启动再用 SQL Server 身份验证登一次排除密码问题然后切回 Windows 验证两步就能定位是服务没起来还是模式不匹配。2.2.1 用查询分析器连接 pubs 并验证环境登录后第一件事是确认当前上下文。手册里第 4 到第 6 步是连上 pubs 样例库并执行三条语句USE pubs GO EXEC sp_help GO SELECT * FROM authors GOUSE 把当前数据库上下文切到 pubsGO 表示一个批处理的结束SQL Server 2000 的查询分析器里很多语句必须分批提交。EXEC sp_help 不带参数时返回当前数据库所有对象的名字、类型和创建时间比在企业管理器里逐层展开要快得多SELECT * FROM authors 验证连接是否真正可用。实验一第 7 步要求从“帮助→目录与索引”打开联机丛书分别查 sp_help、exec、select 三个关键字这一步很多人直接跳过但它是学习老版本函数签名的唯一入口。2.3 用 pubs 建立实验数据基线手册实验一后半段要求手动创建 S_T 数据库再建 student、course、sc 三张表并录入数据。这块内容我在后面展开这里先给一个操作顺序建议先在企业管理器左侧右键“数据库”新建 S_T再切到查询分析器用脚本建表最后回到企业管理器刷新看结果。图形界面和脚本交替使用既能看到对象变化又能练 T-SQL 语法数据库增删改查的完整流程就是这样一遍一遍跑熟的。3. 数据库物理设计CREATE DATABASE、文件组与容量参数3.1 三种文件类型决定数据库的物理布局CREATE DATABASE 在 SQL Server 2000 里不是简单生成一个文件而是一套文件规划。主数据文件扩展名 .mdf是数据库的起点系统表也放在里面每个库有且只有一个次要数据文件扩展名 .ndf用来扩充存储空间可以建多个也可以分散到不同物理磁盘事务日志文件扩展名 .ldf顺序记录所有更改操作用于崩溃恢复和还原。三种文件的区别几乎是数据库原理课程和面试题库的标配题。文件类型扩展名数量作用主数据文件.mdf每个数据库 1 个存储系统信息和用户数据库的起点次要数据文件.ndf0 到多个扩展存储空间可跨磁盘分布事务日志文件.ldf至少 1 个记录事务日志用于恢复和还原理解 .ndf 的意义对数据库优化很重要。常见做法是把数据文件放到高速磁盘、日志文件放到另一块盘写日志和读数据互不抢占 I/O当单文件超过操作系统限制或需要把不同表分到不同磁盘时再引入次要数据文件。3.2 用 T-SQL 创建带容量参数的 testdb手册实验二示例一是创建 testdb包含一个初始 2MB、最大 8MB、按 1MB 递增的数据文件以及一个初始 1MB、最大 5MB 的日志文件CREATE DATABASE testdb ON ( NAME testdb_data, FILENAME d:\DATA\testdb_data.mdf, SIZE 2MB, MAXSIZE 8MB, FILEGROWTH 1MB ) LOG ON ( NAME testdb_log, FILENAME d:\DATA\testdb_log.ldf, SIZE 1MB, MAXSIZE 5MB, FILEGROWTH 1MB ) GONAME 是逻辑文件名T-SQL 语句里引用的都是这个名字FILENAME 是操作系统物理路径目录必须事先存在否则报错 5123。SIZE 是初始容量MAXSIZE 限制文件上限超过会停止增长并报 1105 磁盘空间错误FILEGROWTH 是自动增长步长可以按 MB 递增也可以写成百分比。实验二第 2 题要求 company 数据文件初始 5MB、最大 15MB、步长 1MB日志初始 5MB、最大 10MB、步长 1MB照这个模板替换参数即可。需要提醒的是MAXSIZE 和 FILEGROWTH 不是越大越好。自动增长会带来文件碎片和额外的 I/O 开销生产环境一般会预先分配足够空间把自动增长当作兜底手段而不是常态学习环境为了演示容量参数反而可以特意设小观察它抛错这对理解文件增长机制很有帮助。3.3 ALTER DATABASE 修改文件、文件组与删除库实验二后半段要求对 testdb 添加一个数据文件 testdb2_data初始 1MB、最大 5MB、步长 1MBALTER DATABASE testdb ADD FILE ( NAME testdb2_data, FILENAME d:\DATA\testdb2_data.ndf, SIZE 1MB, MAXSIZE 5MB, FILEGROWTH 1MB ) GO新建文件时把扩展名写成 .ndf和主文件区分开。ALTER DATABASE ADD FILE 只加文件不加文件组时文件会落到默认文件组 PRIMARY 里如果要把文件放进自定义文件组需要先建组再挂文件。实验二第 4 题的 company 库就是这么设计的ALTER DATABASE company ADD FILEGROUP TempGroup GO ALTER DATABASE company ADD FILE ( NAME company3_data, FILENAME d:\DATA\company3_data.ndf, SIZE 3MB, MAXSIZE 10MB, FILEGROWTH 1MB ) TO FILEGROUP TempGroup GOTO FILEGROUP 把数据文件关联到指定文件组文件组可以理解为“磁盘目录”的逻辑抽象。把数据文件和日志文件放在不同文件组、甚至不同物理磁盘上能有效降低 I/O 竞争这是数据库课程设计和生产调优都会用到的思路。删除操作分两档删除数据文件用 ALTER DATABASE ... REMOVE FILE只能删空文件文件里还有数据时会报错删除整个数据库用 DROP DATABASE执行前要确认没有活动连接。实验二要求先删除 company 再用默认设置重建默认设置就是直接写 CREATE DATABASE company不写 ON 和 LOG ON所有文件参数取系统默认值这样操作能直观看到两种建库方式的差别。4. 表与约束设计主键、CHECK、UNIQUE 与自引用外键4.1 学生选课库三张表的主键与检查约束S_T 数据库的 student、course、sc 三张表是数据库原理教材的经典案例。建表前先理清约束的类型和定义位置后面写脚本才不会乱。约束类型作用定义位置典型写法PRIMARY KEY主键唯一且非空列级或表级CONSTRAINT PK_sc PRIMARY KEY (Sno, Cno)FOREIGN KEY外键保证参照完整性表级FOREIGN KEY (Sno) REFERENCES student(Sno)CHECK检查约束限制取值范围列级或表级CHECK (Sage BETWEEN 14 AND 38)UNIQUE唯一约束允许 NULL 但不重复表级UNIQUE (invoice_no)student 表以 Sno 为主键学号限定为 5 位数字性别只能取“男”或“女”年龄限制在 14 到 38 岁course 表以 Cno 为主键课程号同样限定为数字sc 表以 (Sno, Cno) 组合作为主键成绩限定在 0 到 100。建表语句如下CREATE TABLE student ( Sno CHAR(5) NOT NULL CONSTRAINT PK_student PRIMARY KEY CONSTRAINT CK_student_sno CHECK (Sno LIKE [0-9][0-9][0-9][0-9][0-9]), Sname VARCHAR(20) NOT NULL, Ssex CHAR(2) NOT NULL CONSTRAINT CK_student_sex CHECK (Ssex IN (男, 女)), Sage SMALLINT NOT NULL CONSTRAINT CK_student_age CHECK (Sage BETWEEN 14 AND 38), Sdept VARCHAR(20) NULL ) GO CREATE TABLE course ( Cno CHAR(4) NOT NULL PRIMARY KEY, Cname VARCHAR(40) NOT NULL, Cpno CHAR(4) NULL, Ccredit SMALLINT NOT NULL ) GO CREATE TABLE sc ( Sno CHAR(5) NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALLINT NULL CONSTRAINT CK_sc_grade CHECK (Grade BETWEEN 0 AND 100), CONSTRAINT PK_sc PRIMARY KEY (Sno, Cno) ) GOCHECK 约束在 SQL Server 2000 里没有正则支持学号校验只能用 LIKE [0-9][0-9][0-9][0-9][0-9] 这种字符集匹配方括号里写字符范围每位对应一个字符。组合主键必须用表级约束声明写在所有列定义之后取名为 PK_sc 方便后面按名字删除。学号、课程号这类编号字段用 CHAR 而不是 VARCHAR定长字符串少了长度判断索引查找更稳定这也是数据库增删改查场景里的一个实用细节。4.2 course 表的自引用外键course 表的 Cpno 存“先行课编号”参照的是 course 表自己的主键 Cno这种设计叫自引用外键。实验三要求为 Cpno 添加外键约束 FK_CpnoALTER TABLE course ADD CONSTRAINT FK_Cpno FOREIGN KEY (Cpno) REFERENCES course(Cno) GO外键保证了 Cpno 里出现的值一定存在于 Cno 列不会出现“引用了不存在的课程”这种脏数据。自引用表在插入时有顺序要求必须先插父课程再插子课程否则外键检查直接拒绝删除时反过来先删子行再删父行或者先把 Cpno 置 NULL。菜单表、部门树、分类表都是这种结构遇到这类表的数据维护我一般会写一个递归或分批的录入脚本而不是手动逐条插。sc 表的外键分别参照 student 和 course 两表用两条 ALTER 语句完成ALTER TABLE sc ADD CONSTRAINT FK_Sno FOREIGN KEY (Sno) REFERENCES student(Sno) GO ALTER TABLE sc ADD CONSTRAINT FK_Cno FOREIGN KEY (Cno) REFERENCES course(Cno) GO4.3 company 库的约束设计与一个列宽陷阱company 库五张表的关系更贴近真实销售系统employee 员工、customer 客户、sales 销售主表、sale_item 销售明细、product 产品。销售主表连员工和客户销售明细连销售主表和产品构成两张经典的一对多关系。实验三要求给 sales 添加发票号码列再补外键、唯一约束和检查约束ALTER TABLE sales ADD invoice_no CHAR(10) NOT NULL GO给已有数据的表加 NOT NULL 列SQL Server 2000 会填上空字符串让语句执行成功这个行为很容易被忽略。常见做法是分三步先加可空列UPDATE 填上真实值再 ALTER COLUMN 改成 NOT NULL避免产生一批发票号为空的历史数据。列宽陷阱在 employee 表上更明显。手册里 emp_no 定义为 char(5)但检查约束却要求“E 开头后面跟 5 位数字”E00001 这样的编号至少需要 6 个字符char(5) 根本放不下。这是原始实验文档里一个真实的定义矛盾。处理方式是把列改成 char(6)再挂约束ALTER TABLE employee ALTER COLUMN emp_no CHAR(6) NOT NULL GO ALTER TABLE employee ADD CONSTRAINT CK_emp_no CHECK (emp_no LIKE E[0-9][0-9][0-9][0-9][0-9]), CONSTRAINT CK_sex CHECK (sex IN (男, 女)), CONSTRAINT CK_salary CHECK (salary BETWEEN 1000 AND 10000) GO ALTER TABLE sales ADD CONSTRAINT FK_sale_id FOREIGN KEY (sale_id) REFERENCES employee(emp_no), CONSTRAINT FK_cust_id FOREIGN KEY (cust_id) REFERENCES customer(cust_id), CONSTRAINT UN_inno UNIQUE (invoice_no), CONSTRAINT CK_inno CHECK (invoice_no LIKE I[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]) GO一条 ALTER TABLE 可以挂多个约束逗号分隔。FOREIGN KEY 保证子表值必须存在于父表主键UNIQUE 保证发票号全表不重复CHECK 限定格式。给约束起名字CK_xxx、FK_xxx、UN_xxx不是为了好看而是后面做数据库优化或数据清理时靠名字就能知道约束管的是哪一列不用逐条去翻定义。5. 查询与聚合分析WHERE 过滤、GROUP BY 分组与 ROLLUP5.1 SELECT 基础子句TOP、DISTINCT、LIKE 与排序单表查询的核心是四个子句的组合SELECT 决定输出列WHERE 决定行范围ORDER BY 决定排序TOP 限制返回行数。手册实验四里的几条语句覆盖了最常用的写法SELECT emp_no, emp_name, dept, salary FROM employee WHERE emp_name LIKE 王% ORDER BY salary ASC GO SELECT TOP 10 PERCENT * FROM sales ORDER BY tot_amt DESC GO SELECT DISTINCT dept FROM employee GOLIKE 王% 匹配姓王的员工% 匹配任意长度字符下划线“_”匹配单个字符能精确控制字数。TOP 10 PERCENT 返回前 10% 行不写 PERCENT 时按数量取TOP 必须配合 ORDER BY 才有确定意义否则返回的行随机。DISTINCT 做整行去重SELECT DISTINCT dept 取到的就是部门清单“DISTINCT 单列 其他多列”这种混写会按整行判断达不到预期查重时直接写 GROUP BY 更清晰。WHERE 里还能组合 BETWEEN、IN、IS NULL 和逻辑运算。IN 适合离散值列表BETWEEN 适合闭区间范围IS NULL 判断空值。一个容易犯的错用 NULL 判断空值结果永远为空必须写 IS NULL。5.2 GROUP BY 与 HAVING 的分组逻辑聚合函数 SUM、COUNT、AVG、MAX、MIN 与 GROUP BY 配合能按维度做汇总统计。写分组查询前先记住子句的执行顺序很多逻辑错误都出在这里。子句或函数作用执行阶段WHERE行级过滤分组前GROUP BY按列分组行过滤后HAVING分组后过滤分组后SUM / AVG / COUNT汇总计算分组时ORDER BY排序全部计算完成后手册实验五第 3 题是“找出销售业绩超过 10000 元的业务员编号及业绩”SELECT sale_id, SUM(tot_amt) AS 销售业绩 FROM sales GROUP BY sale_id HAVING SUM(tot_amt) 10000 ORDER BY 销售业绩 DESC GO执行顺序是先 WHERE 过滤、再 GROUP BY 分组、然后 HAVING 过滤组、最后 ORDER BY 排序。因此 WHERE 里不能写聚合函数HAVING 里才能写 SUM(...) 这类表达式ORDER BY 可以直接用别名“销售业绩”SQL Server 2000 支持这个写法。如果还要统计各部门人数和平均薪水就是 COUNT(*) 和 AVG(salary) 的组合GROUP BY dept 一行就能拿到结果。5.3 派生表与加权平均单价手册实验五第 4 题要求“计算每一产品销售数量总和与平均销售单价”。这里有个经典陷阱单价不能直接 AVG(unit_price)因为每笔销售数量不同加权平均才准确SELECT prod_id, SUM(qty) AS 总数量, SUM(qty * unit_price) / SUM(qty) AS 加权平均单价 FROM sale_item GROUP BY prod_id GOAVG(unit_price) 只是对所有订单行做算术平均忽略了数量权重SUM(qty * unit_price) / SUM(qty) 才是真实成交均价。数据库课程设计的统计报表里这个区别直接影响结论很多学生在这里丢分。需要按月统计时在 SELECT 和 GROUP BY 里都写 MONTH(order_date)就能得到“产品 月份”的交叉汇总。5.4 CUBE 与 ROLLUP多维汇总的差异手册实验五第 5、6 题要求统计“各部门不同性别、或各部门、或所有员工”的平均薪水对应 SQL Server 2000 的多维汇总语法SELECT dept, sex, AVG(salary) AS 平均薪水 FROM employee GROUP BY dept, sex WITH ROLLUP GO SELECT dept, sex, AVG(salary) AS 平均薪水 FROM employee GROUP BY dept, sex WITH CUBE GOWITH ROLLUP 按分组列从左到右逐层汇总产生“deptsex 细目 → dept 小计 → 总计”三档结果WITH CUBE 在 ROLLUP 基础上额外生成“sex 维度的小计”即所有列组合的小计结果里会出现 NULL 表示“该维度的全部”。新版 SQL Server 把语法改成了 GROUP BY ROLLUP(C1, C2) 和 GROUP BY CUBE(C1, C2)语义不变。面试题里问“ROLLUP 和 CUBE 的区别”本质就是考察ROLLUP 只有分层小计CUBE 包含全部组合的小计。6. 多表连接查询与老版本 SQL 的兼容排错6.1 内连接与自身连接连接查询是数据库面试题的重灾区。内连接只保留两表匹配的行自身连接则是一张表和自己匹配。手册实验六第 1 题要查 employee 表中“部门相同且住址相同”的女员工必须用自身连接SELECT a.emp_no, a.emp_name, a.sex, a.title, a.salary, a.addr FROM employee a INNER JOIN employee b ON a.addr b.addr AND a.emp_no ! b.emp_no WHERE a.sex 女 AND b.sex 女 GO自身连接必须给表起别名a、b否则无法区分同名列连接条件里要有 a.emp_no ! b.emp_no否则每行都会和自己匹配。这段 SQL 的坑在于 ON 里写了列值比较、WHERE 里又写性别条件混着混着一不小心就把连接条件漏掉结果集变成笛卡尔积。排查这类问题时先数结果行数如果行数接近两表行数之积多半是连接条件丢了。6.2 三种外连接的差异连接类型返回内容典型用途INNER JOIN两表都匹配的行有对应关系的业务明细LEFT JOIN左表全部 右表匹配行以左表为主体补右表信息RIGHT JOIN右表全部 左表匹配行以右表为主体补左表信息FULL JOIN两表全部行无匹配补 NULL数据对账、差异分析LEFT JOIN 的重点是 NULL 扩展右表没匹配上时右表列全部为 NULL。手册实验六第 8 题要求分别用左外、右外、全外连接查 product 和 sale_item 中单价高于 2400 元的相同产品结果集行数会明显不同。写 WHERE 时要注意对右表列加过滤条件会“吃掉”NULL 行让外连接退化成内连接这是做数据对账时最常见的逻辑错误。6.3 被遗忘的 * 符号与迁移排错SQL Server 2000 时代还流行一种旧式外连接写法WHERE 里用 表示左外连接表示右外连接。老代码里经常能看到SELECT p.prod_id, s.qty FROM product p, sale_item s WHERE p.prod_id * s.prod_id GO这个语法从 SQL Server 2005 开始不再支持新版本直接报语法错误。接手老项目时用文本搜索把全库的 和 找出来改成 LEFT JOIN / RIGHT JOIN是迁移兼容检查的第一步。改完做回归对拍跑一遍旧环境的查询结果再跑一遍新写法逐行比对结果集。本文还有配套的精品资源点击获取