简介这份PDF为数据库原理与应用课程的实验报告整理稿适合正在学习SQL Server、数据库创建与管理、数据完整性等知识的高校学生和自学者参考。内容围绕上海电力学院“数据库原理与应用”课程实际实验展开覆盖学生管理数据库XSGL的创建、属性修改、备份还原与删除以及六类完整性约束、通用默认值、规则、索引与视图的实施方法步骤记录细致便于对照练习。资源包内含1份PDF文件大小约4.64MB内容以图文实验步骤与总结为主可直接用阅读器查看。目前已有81人浏览学习可供需要完成同类数据库实验或准备数据库原理考试复习的读者梳理实验流程、核对操作要点。1. 数据库原理实践报告一份能直接照做的 SQL Server 实验全流程笔记这份PDF是一份真实的《数据库原理与应用》实验报告来自上海电力学院信息管理专业完整记录了SQL Server企业管理器Enterprise Manager下的建库、约束、索引、视图、查询全流程。别看是课程作业里面XSGL数据库的创建参数、六类完整性约束、聚簇索引与填充因子、GROUP BY聚合查询放到现在依然是数据库课程设计和入门实操的经典套路甚至不少培训机构的练习题还在用同一套表结构。最难得的是报告把每一步操作路径和当时的报错经历都留了下来不是只有结论。适合三类人正在写数据库课程设计报告的学生想用SQL Server练手但懒得设计实验步骤的从业者以及想对照检查自己建库参数是否合理的初级DBA。这份报告最大的价值不是知识点本身而是照着走一遍就能把环境搭起来的完整实验路径。2. 数据库的创建与管理从 XSGL 建库到备份还原的完整生命周期数据库实验的第一步永远是建库。这份报告用企业管理器创建了一个名为XSGL学生管理信息的数据库参数给得很细数据文件初始大小20MB、文件增长增量5MB、自动增长、上限200MB日志文件初始5MB、增长增量1MB、上限20MB。很多人第一次建库就是一路点下一步用默认值结果建出来的库数据文件只有3MB、日志文件1MB跑一次数据导入就频繁触发自动增长磁盘I/O卡顿。实验给出了一个好习惯建库前先规划物理参数而不是依赖默认值。2.1 建库参数决策为什么要区分数据文件和日志文件先看报告给出的完整参数我整理成了表格配置项数据文件.mdf日志文件.ldf初始大小20MB5MB增长增量5MB1MB增长方式自动增长自动增长增长上限200MB20MB这里要理解的不是数字本身而是SQL Server为什么把两类文件分开管理。数据文件存表和索引查询时是随机读日志文件记录每次事务的变更写入时是顺序追加。两种I/O模式完全不同如果混在一个文件里频繁的日志追加会干扰数据读反之亦然。这也是建库时不要把日志文件放到和数据文件同一个繁忙磁盘上的原因生产环境尤其要注意磁盘分离。除了企业管理器的图形操作我一般会同步用T-SQL建一遍两边的参数一一对应方便以后写脚本复用CREATE DATABASE XSGL ON PRIMARY ( NAME NXSGL, FILENAME ND:\SQLServerData\XSGL.mdf, SIZE 20MB, MAXSIZE 200MB, FILEGROWTH 5MB ) LOG ON ( NAME NXSGL_log, FILENAME ND:\SQLServerData\XSGL_log.ldf, SIZE 5MB, MAXSIZE 20MB, FILEGROWTH 1MB ) GO这段脚本里SIZE就是初始大小MAXSIZE对应企业管理器里的增长上限FILEGROWTH对应增长增量ON PRIMARY指明这是主数据文件组LOG ON单独定义日志文件。还有一个容易忽略的细节FILENAME必须指向SQL Server有写入权限的路径路径不存在或权限不足创建会直接报错。图形界面把路径这一层藏起来了脚本却会暴露出来不少新手栽在这。关于自动增长有一个机制容易被忽略设置FILEGROWTH为5MB时如果文件已满SQL Server会等待下一次写入尝试然后一次性扩展5MB这个扩展过程会短暂阻塞写入。所以对生产库常见做法是手动在维护窗口预扩展文件而不是依赖自动增长。实验里把增长上限设为200MB本质上是一个安全阀——防止失控的任务把数据文件无限膨胀撑爆磁盘。200MB对学生管理信息这个规模够用但换成生产库这个上限要根据数据量重新评估。2.2 修改数据库属性百分比增长与固定增量的取舍实验第二步是把XSGL数据文件的初始大小改为30MB、最大值500MB、增长方式改为5%日志文件初始改为20MB、最大值30MB、增长6%。这里有个值得聊的转变增长方式从固定增量5MB换成了百分比5%。固定增量的好处是增长幅度可预期每次扩展5MB几乎不会出现一次扩展几百MB的尖峰缺点是爆发式写入时频繁触发自动增长。百分比则相反文件越大单次扩展越大比如文件到200MB时5%就是10MB到2GB时一次扩100MB。所以百分比适合数据量持续增长、能容忍较大扩展抖动的场景。实验让学生改这两个参数目的就是体会两种增长模式的差异。修改属性后一个常见疑问是初始大小改到30MB已有数据怎么办答案是SQL Server不会动你已有的数据页它只是在物理文件层面做扩展。如果文件里已用空间超过30MB改小会失败改大则立即扩展。最大值是上限不是目标值数据库平时就在初始大小附近浮动写入压力上来才向上增长。另外用企业管理器改属性时系统可能提示需要独占访问原因是其它会话正在使用这个库比如查询分析器窗口没关。处理办法是关掉其它连接再试或者用脚本切到单用户模式ALTER DATABASE XSGL SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE XSGL SET MULTI_USER GO第一句把正在执行的会话立即回滚并断开让当前会话独占数据库改完属性后第二句恢复多用户。生产环境不要随便用ROLLBACK IMMEDIATE会杀掉正在跑的事务需要提前评估影响。2.3 备份、还原与删除增删改查之外最不可逆的操作实验还要求完成备份、还原和删除。数据库的增删改查前三个操作大多针对单条数据而备份还原针对的是整个库地位完全不同。备份在企业管理器里是右键数据库→所有任务→备份数据库还原是所有任务→还原数据库。备份的本质是把数据库一致性状态复制到备份介质还原则是用备份文件覆盖当前库。这里有一个新手最容易踩的坑还原时如果目标库正被其它会话使用会报数据库正在使用无法获得独占访问权。处理方式跟前面一样先切单用户模式再还原。删除数据库是四个操作里最需要谨慎的。在企业管理器里删除XSGL会连带删除物理文件.mdf和.ldf数据彻底没了没有后悔药。所以删除前要确认两件事一是库真的不用了二是备份已经做过。如果只是想清空数据保留结构用TRUNCATE TABLE或者DELETE不要删库重建。备份还原还要注意备份类型的选择。完整备份备份整个库适合日常恢复差异备份只备份自上次完整备份以来的变化恢复时需要先还原完整备份再还原差异备份日志备份用于时间点恢复。实验只要求做完整备份但我建议把差异备份和日志备份也各做一次因为它们是生产环境真正常用的组合。做数据库同步和迁移时这套备份组合也是基础只做完整备份的迁移方案在数据量上来之后会非常被动。3. 数据完整性六类约束、默认值与规则的落地操作数据库质量好不好数据完整性是核心衡量标准。实验报告开篇就给了定义数据完整性指数据的正确性、完备性和一致性分为域完整性、实体完整性和参照完整性三类。这三类的划分不是用来背诵的它们直接决定你用什么数据库对象去实施——域完整性对应CHECK约束和默认值实体完整性对应主键约束参照完整性对应外键约束。分清楚这个对应关系做课程设计时就不至于把约束建错位置。3.1 三类完整性的边界什么情况用哪种约束域完整性管的是这一列的值是否合法。年龄必须是15到30之间的整数性别只能取男或女邮编必须是6位数字这些都属于域完整性用CHECK约束和默认值实现。实体完整性管的是每一行能不能被唯一区分。一张学生表里学号必须唯一否则两条记录无法区分用主键约束或唯一性约束实现。参照完整性管的是表与表之间的引用关系是否成立。SC表的学号必须存在于student表课程号必须存在于course表否则就是孤儿数据用外键约束实现。实验明确列出了六种约束类型非空约束、默认值约束、CHECK约束、主键约束、外键约束、唯一性约束。其中非空和默认值服务域完整性主键服务实体完整性外键服务参照完整性CHECK和唯一性可以同时服务多种完整性需求。六种约束不是六个孤立的对象它们组合起来才构成一张表的完整数据防线。3.2 六种约束的添加与删除记住操作的先后顺序实验用企业管理器逐条实施约束。以student表的年龄字段为例添加CHECK约束让年龄大于15岁且小于30岁ALTER TABLE student ADD CONSTRAINT CK_Student_Age CHECK (Age 15 AND Age 30) GO删除时ALTER TABLE student DROP CONSTRAINT CK_Student_Age GO这段操作背后有个细节值得注意CHECK约束用的是开区间15岁和30岁本身是不合法的。如果业务要求包含边界应该写成Age 15 AND Age 30。做实验按老师要求来但做真实项目时一定要先确认业务上年龄区间的开闭这是需求文档里最容易含糊的地方我见过因为区间开闭理解不一致导致上线后数据校验翻车的。约束添加的顺序也很有讲究。我一般按这个顺序走先建主键约束再建外键约束然后建CHECK和默认值最后建唯一性约束。原因是外键依赖主键存在CHECK依赖列已经定义唯一性约束要等主键数据清理完再建否则已有重复数据会导致创建失败。实验里反复出现若原有约束请先删除再重设这个提示说的就是在约束冲突时先清理再重建的工作流这是SQL Server教学里一个很重要的习惯。默认值约束是另一个容易迷的点。实验给student表的Splace所在系字段设置默认值内蒙。默认值有两种实施方式直接在列定义里写DEFAULT这是列级默认值或者先创建独立的默认值对象再绑定这是通用默认值。实验用的是后者原因是SQL Server的独立默认值对象可以被多个表的多个列复用比如邮编默认值210000可以绑到student的postcode列也可以绑到其它表的邮编列一次创建多处绑定。3.3 通用默认值与规则绑定、解除与删除的顺序通用默认值的完整生命周期是创建对象→绑定到列→解除绑定→删除对象。实验里创建了邮编默认值210000绑定到student表的postcode列。关键问题来了能否不解除绑定直接删除默认值对象答案是不能。SQL Server会拒绝删除还在被引用的对象报错信息类似无法删除默认值因为它已绑定到列。必须先执行sp_unbindefault student.postcode GO DROP DEFAULT DF_Postcode GO规则Rule也是独立对象实验里创建了一个性别规则要求取值只能是男或女。T-SQL写法如下CREATE RULE RULE_Sex AS Ssex 男 OR Ssex 女 GO sp_bindrule RULE_Sex, student.sex GO原文写的是Ssex LIKE [男,女]这是一个典型的坑方括号在LIKE里属于字符集匹配逗号会被当成一个可匹配字符也就是说这个规则会允许插入逗号。正确的写法应该用等值判断或者IN (男,女)。这个细节实验报告本身是错的如果你照着原报告做往sex字段插入一个逗号规则根本不会拦。规则的删除和默认值一样必须先解除绑定sp_unbindrule student.sex GO DROP RULE RULE_Sex GO3.4 级联删除与级联更新方便与危险并存实验最后一部分是外键约束的级联设置。在SC表和student、course表之间建立外键并允许级联删除与级联更新。这意味着删除student表里学号为1001的学生时SC表里所有1001的选课记录会被自动删除如果把1001改成1002SC表里对应的外键值也会自动改成1002。这个特性很直观但生产环境里我几乎不用级联删除。原因是真实业务中删除一条主表记录可能连带删掉大量从表数据且删除不可见、不可控。比如删除一个客户连带删光他所有的订单、发票、操作日志——如果哪天发现删错了数据已经没了。常见做法是逻辑删除在表上加is_deleted字段查询时过滤掉已删除标记的记录这样数据永远在只是不可见。实验要求掌握级联操作目的是理解外键的引用关系不是让你在真实系统里滥用这个边界要分清。4. 索引与视图从聚簇索引到 WITH CHECK OPTION 的实操细节索引和视图是查询性能的两个关键工具。实验前半部分是索引要求创建唯一聚簇索引、复合索引并设置填充因子后半部分是视图要求创建投影视图、修改视图和管理视图数据。这部分是最容易照抄错的地方因为实验报告在索引类型上存在逻辑矛盾需要自己判断。4.1 聚簇索引与非聚簇索引一张表只能有一个聚簇索引实验第一个要求是给student表创建以sno为关键字的唯一聚簇索引。聚簇索引决定表数据的物理存储顺序数据行会按索引键值的顺序排列在磁盘上所以每张表只能有一个聚簇索引。非聚簇索引则是独立于数据页的索引结构不改变物理顺序一张表可以建多个。创建唯一聚簇索引的T-SQLCREATE UNIQUE CLUSTERED INDEX sno_index ON student(sno ASC) GO为什么实验选sno做唯一聚簇索引因为学号是主键查询通常按学号定位。聚簇索引的叶子节点就是数据行本身按sno做范围查询时只需要顺序扫描一段物理页I/O次数少。如果sno做的是非聚簇索引范围查询要先查索引再逐行回表代价高得多。这个选择思路放到今天依然成立主键通常是查询最频繁的键把物理存储顺序和主键顺序统一是性价比最高的索引设计。4.2 复合索引与填充因子70%到底在防什么实验第二个要求是在student表上创建以sex、sname为索引关键字的索引sname升序、sex降序填充因子70%索引名ss_index。原文写的类型也是聚簇索引但这里有个矛盾表已经有一个sno聚簇索引了不可能再建第二个聚簇索引SQL Server会直接报错表上已存在聚簇索引。正确的做法是建成非聚簇索引CREATE NONCLUSTERED INDEX ss_index ON student(sex DESC, sname ASC) WITH FILLFACTOR 70 GO填充因子FILLFACTOR是一个容易被忽略但很有用的参数。它表示创建索引时索引页预先填满的百分比。70%意味着每个索引页留30%空间给未来的插入减少因插入导致的索引页分裂次数。页分裂的代价很高要移动数据行并更新索引指针所以频繁插入更新的表填充因子通常设60到80几乎只读的表设100。实验里用70%是针对学生表这种有持续插入操作的场景的合理选择。如果这张表只是读多写少设70%反而浪费空间白留了30%的页空着。索引的维护操作也包含在实验里重命名和删除。重命名索引不能用ALTER TABLE要用系统存储过程EXEC sp_rename student.sno_index, sno_index1, INDEX GO删除索引DROP INDEX student.sno_index1 GO注意DROP INDEX的语法是表名.索引名这和创建时的书写习惯不同我在辅导课程设计时见过不少人在这一步报语法错误。另外重命名索引在生产环境要格外谨慎应用里如果写死了索引名重命名会导致SQL语句走不到这个索引。4.3 视图保存的是查询不是数据视图是数据库课程设计的常客也是实验的重点。实验要求创建一个名为studview的视图从student表查出籍贯为内蒙的所有学生的学号、姓名、性别、籍贯、年龄。视图的本质是一个保存的SELECT语句不占额外存储查询视图时SQL Server会实时执行背后的语句。创建视图CREATE VIEW studview AS SELECT sno, sname, ssex, splace, sage FROM student WHERE splace 内蒙 GO修改视图用ALTER VIEW查看视图定义用EXEC sp_helptext studview。视图的价值在于封装业务侧只暴露视图不暴露底层表结构可以用视图限制用户只能看某些列作为安全层复杂查询封装成视图后应用层查询语句变得简单。但要注意视图不是性能优化工具一个视图背后如果是多张大表的JOIN查询视图的代价一样高不能在视图上建索引来加速索引视图除外那套规则更复杂。实验里还有一步用视图管理数据把视图中学号为1001的学生姓名改成许华然后返回底层表确认变化。这是一个很多人会搞混的点——通过视图做DML修改会作用到底层表因为视图本质就是底层表的一个投影。但有一个限制如果视图的定义里用了聚合、GROUP BY或者多表JOINSQL Server通常不允许通过视图做增删改。视图更新规则比较严格只支持可更新视图具体能不能改要看视图定义是否保持了与基础表的直接映射关系。4.4 WITH CHECK OPTION防止视图出现看不见的数据实验最后要求创建视图stuview1从student表查出性别为男的学生并且使用WITH CHECK OPTION。这个子句的作用是通过视图插入或修改数据时必须满足视图定义的WHERE条件。加了这个子句后如果通过视图插入一条性别为女的记录SQL Server会直接拒绝因为插入后的数据不满足视图的查询条件会造成数据从视图里消失的诡异现象。不加的话可以插入违反视图条件的记录但这条记录在视图里永远查不到。这个边界认知很重要——不加WITH CHECK OPTION的视图插入数据后可能查不到自己刚插入的记录这种问题排查起来非常隐蔽。5. 避坑手册数据库实验里最常见的五个翻车现场这一章写这次实验里反复出现、以及我做数据库课程设计辅导时见别人踩得最多的五个坑。每条按现象、原因、解决三个环节记录照着排查能省掉大量DEBUG时间。5.1 还原数据库报无法获得独占访问权现象在企业管理器里右键还原XSGL弹窗提示数据库正在使用无法获得独占访问权还原卡住不动。原因还原的本质是用备份文件整体覆盖当前数据库文件SQL Server要求还原期间没有任何其它会话连接到这个库。只要有一个查询分析器窗口、一个应用连接没关独占就拿不到。最常见的是自己忘了关掉刚才建库时打开的查询窗口。解决先关掉所有连接再用脚本把库切到单用户模式还原完成再切回来ALTER DATABASE XSGL SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO -- 执行还原操作 ALTER DATABASE XSGL SET MULTI_USER GO我每次还原前都会先查一下SELECT * FROM sys.dm_exec_sessions WHERE database_id DB_ID(XSGL)确认没有其它会话再动手这样可以避免反复开关窗口的麻烦。5.2 删除默认值对象报已绑定到列现象在企业管理器里右键删除默认值对象系统提示无法删除默认值因为它已绑定到列删除操作被拦下来。原因SQL Server不允许删除仍被引用的对象。默认值对象在用sp_bindefault绑定到postcode列之后对象和列之间存在引用关系必须先解除绑定。解决先解除绑定再删除sp_unbindefault student.postcode GO DROP DEFAULT DF_Postcode GO规则对象同理先sp_unbindrule再DROP RULE。这个先解绑再删除的顺序是通用默认值和规则这两类对象特有的。实验报告专门问了一句若未解除绑定能否删除默认值可见老师很清楚学生一定会在这里撞一次。5.3 创建第二个聚簇索引报错现象按照实验要求先建了sno唯一聚簇索引再建sex、sname复合索引时SQL Server报错表上已存在聚簇索引无法创建。原因聚簇索引决定表数据的物理存储顺序每张表只能有一个。实验报告里把复合索引也写成了聚簇索引这是报告本身的逻辑错误照抄必然失败。这个不是操作问题是实验设计的问题需要自己判断改过来。解决把复合索引改为非聚簇索引CREATE NONCLUSTERED INDEX ss_index ON student(sex DESC, sname ASC) WITH FILLFACTOR 70 GO如果确实需要按sex、sname做物理排序的聚簇索引那就必须放弃sno聚簇索引两个方案二选一。实际业务中通常保留主键聚簇索引复合查询走非聚簇索引加回表很少为了一个复合查询牺牲主键的物理顺序。5.4 性别规则形同虚设逗号也能插进去现象绑定性别规则后测试插入数据发现往sex字段插入一个逗号竟然成功了规则没有拦住。原因实验报告里规则定义写的是Ssex LIKE [男,女]方括号是LIKE的字符集匹配语法逗号被当成了一个可匹配的字符。这个写法能匹配男、女、,三个字符等于给数据开了个后门。这属于规则定义本身的写法错误不是SQL Server的问题。解决改成等值判断或者直接用CHECK约束替代规则CREATE RULE RULE_Sex AS Ssex 男 OR Ssex 女 GO更推荐用CHECK约束因为规则是SQL Server的老特性可移植性差现在的主流做法是CHECK约束ALTER TABLE student ADD CONSTRAINT CK_Student_Sex CHECK (Ssex IN (男, 女)) GO5.5 修改数据库属性提示磁盘空间不足现象把XSGL数据文件初始大小从20MB改成30MB系统提示磁盘空间不足操作失败。原因扩展数据文件需要在磁盘上分配连续空间。如果数据文件所在磁盘分区可用空间小于10MB或者文件碎片严重扩展就会失败。很多时候表面看磁盘总空间够但连续可用空间不够。解决先检查磁盘可用空间和文件当前大小EXEC sp_spaceused GO SELECT name, size, max_size, growth FROM sys.database_files GO如果空间确实紧张可以把数据文件迁移到其它空闲盘ALTER DATABASE XSGL MODIFY FILE ( NAME XSGL, FILENAME NE:\SQLData\XSGL.mdf ) GO注意迁移物理文件需要先分离数据库再操作文件或者使用文件和文件组的在线迁移功能数据库在线状态下直接改FILENAME是会报错的这个步骤容易翻车。6. 验证实验结果的四个检查点让每一步操作都有据可查做完实验不等于做对了实验。我自己的习惯是每完成一个环节用系统视图和脚本验证一遍确认参数和对象真的按预期生效。这里给出四个直接可用的检查点配合企业管理器的图形界面双保险新手也能照着走。第一个检查点验证数据库文件和增长参数。建库或修改属性后查sys.database_files确认初始大小、最大值、增长增量SELECT name, type_desc, size * 8 / 1024 AS 当前大小MB, max_size * 8 / 1024 AS 最大大小MB, growth, is_percent_growth FROM sys.database_files GOtype_desc为ROWS是数据文件LOG是日志文件size单位是8KB页所以要乘8除以1024换算成MBgrowth为0表示禁用自动增长is_percent_growth为1表示按百分比增长。这四个字段能完整还原企业管理器属性页里的所有设置图形界面改了之后用这段脚本确认最稳妥。第二个检查点验证索引类型和填充因子SELECT name, type_desc, is_unique, fill_factor FROM sys.indexes WHERE object_id OBJECT_ID(student) GOtype_desc里CLUSTERED是聚簇索引NONCLUSTERED是非聚簇索引is_unique为1是唯一索引fill_factor就是填充因子。如果发现sex和sname的索引显示CLUSTERED说明之前创建的聚簇索引没删干净或者创建顺序有误需要回头清理。第三个检查点验证约束是否真的在拦数据。约束不是建完就完要用一条违反约束的INSERT去撞一下-- 期望报错年龄12岁违反CHECK约束 INSERT INTO student (sno, sname, age) VALUES (1009, 测试, 12) GO -- 期望报错性别值未知违反规则/CHECK约束 INSERT INTO student (sno, sname, ssex) VALUES (1010, 测试, 未知) GO如果两条都报错说明CHECK和性别规则都生效了。如果某条没报错回查约束定义大概率是规则写法出了问题。这个故意写错数据去撞约束的方法是验证数据完整性最直接的手段比看定义可靠得多——定义写得再漂亮不实际拦一条数据都不能算数。第四个检查点验证视图的WITH CHECK OPTION。创建视图后尝试通过视图插入一条不满足条件的记录-- 期望报错通过stuview1插入性别为女的记录违反视图条件 INSERT INTO stuview1 (sno, sname, ssex) VALUES (1011, 测试, 女) GO报错说明WITH CHECK OPTION生效如果不报错说明视图定义里漏了这个子句要回企业管理器重新检查。这一步能验证视图封装的边界是否真的可靠也能防止插进去却查不到的诡异数据问题。四个检查点跑完这份实验报告里的所有操作才算真正闭环。说实话我第一次做这类实验时建完库和索引就直接交功课从不去验证结果被老师问起你怎么确定索引是聚簇的就答不上来。从那以后我每次做完数据库改动都强制走一遍sys.database_files、sys.indexes和反向INSERT验证花不了两分钟但能确认每一步操作都落到了实处。做数据库这行最怕的就是界面点了确定就当成功数据不会撒谎系统字典才是最终答案。希望帮到你。本文还有配套的精品资源点击获取
简介这份PDF为数据库原理与应用课程的实验报告整理稿适合正在学习SQL Server、数据库创建与管理、数据完整性等知识的高校学生和自学者参考。内容围绕上海电力学院“数据库原理与应用”课程实际实验展开覆盖学生管理数据库XSGL的创建、属性修改、备份还原与删除以及六类完整性约束、通用默认值、规则、索引与视图的实施方法步骤记录细致便于对照练习。资源包内含1份PDF文件大小约4.64MB内容以图文实验步骤与总结为主可直接用阅读器查看。目前已有81人浏览学习可供需要完成同类数据库实验或准备数据库原理考试复习的读者梳理实验流程、核对操作要点。1. 数据库原理实践报告一份能直接照做的 SQL Server 实验全流程笔记这份PDF是一份真实的《数据库原理与应用》实验报告来自上海电力学院信息管理专业完整记录了SQL Server企业管理器Enterprise Manager下的建库、约束、索引、视图、查询全流程。别看是课程作业里面XSGL数据库的创建参数、六类完整性约束、聚簇索引与填充因子、GROUP BY聚合查询放到现在依然是数据库课程设计和入门实操的经典套路甚至不少培训机构的练习题还在用同一套表结构。最难得的是报告把每一步操作路径和当时的报错经历都留了下来不是只有结论。适合三类人正在写数据库课程设计报告的学生想用SQL Server练手但懒得设计实验步骤的从业者以及想对照检查自己建库参数是否合理的初级DBA。这份报告最大的价值不是知识点本身而是照着走一遍就能把环境搭起来的完整实验路径。2. 数据库的创建与管理从 XSGL 建库到备份还原的完整生命周期数据库实验的第一步永远是建库。这份报告用企业管理器创建了一个名为XSGL学生管理信息的数据库参数给得很细数据文件初始大小20MB、文件增长增量5MB、自动增长、上限200MB日志文件初始5MB、增长增量1MB、上限20MB。很多人第一次建库就是一路点下一步用默认值结果建出来的库数据文件只有3MB、日志文件1MB跑一次数据导入就频繁触发自动增长磁盘I/O卡顿。实验给出了一个好习惯建库前先规划物理参数而不是依赖默认值。2.1 建库参数决策为什么要区分数据文件和日志文件先看报告给出的完整参数我整理成了表格配置项数据文件.mdf日志文件.ldf初始大小20MB5MB增长增量5MB1MB增长方式自动增长自动增长增长上限200MB20MB这里要理解的不是数字本身而是SQL Server为什么把两类文件分开管理。数据文件存表和索引查询时是随机读日志文件记录每次事务的变更写入时是顺序追加。两种I/O模式完全不同如果混在一个文件里频繁的日志追加会干扰数据读反之亦然。这也是建库时不要把日志文件放到和数据文件同一个繁忙磁盘上的原因生产环境尤其要注意磁盘分离。除了企业管理器的图形操作我一般会同步用T-SQL建一遍两边的参数一一对应方便以后写脚本复用CREATE DATABASE XSGL ON PRIMARY ( NAME NXSGL, FILENAME ND:\SQLServerData\XSGL.mdf, SIZE 20MB, MAXSIZE 200MB, FILEGROWTH 5MB ) LOG ON ( NAME NXSGL_log, FILENAME ND:\SQLServerData\XSGL_log.ldf, SIZE 5MB, MAXSIZE 20MB, FILEGROWTH 1MB ) GO这段脚本里SIZE就是初始大小MAXSIZE对应企业管理器里的增长上限FILEGROWTH对应增长增量ON PRIMARY指明这是主数据文件组LOG ON单独定义日志文件。还有一个容易忽略的细节FILENAME必须指向SQL Server有写入权限的路径路径不存在或权限不足创建会直接报错。图形界面把路径这一层藏起来了脚本却会暴露出来不少新手栽在这。关于自动增长有一个机制容易被忽略设置FILEGROWTH为5MB时如果文件已满SQL Server会等待下一次写入尝试然后一次性扩展5MB这个扩展过程会短暂阻塞写入。所以对生产库常见做法是手动在维护窗口预扩展文件而不是依赖自动增长。实验里把增长上限设为200MB本质上是一个安全阀——防止失控的任务把数据文件无限膨胀撑爆磁盘。200MB对学生管理信息这个规模够用但换成生产库这个上限要根据数据量重新评估。2.2 修改数据库属性百分比增长与固定增量的取舍实验第二步是把XSGL数据文件的初始大小改为30MB、最大值500MB、增长方式改为5%日志文件初始改为20MB、最大值30MB、增长6%。这里有个值得聊的转变增长方式从固定增量5MB换成了百分比5%。固定增量的好处是增长幅度可预期每次扩展5MB几乎不会出现一次扩展几百MB的尖峰缺点是爆发式写入时频繁触发自动增长。百分比则相反文件越大单次扩展越大比如文件到200MB时5%就是10MB到2GB时一次扩100MB。所以百分比适合数据量持续增长、能容忍较大扩展抖动的场景。实验让学生改这两个参数目的就是体会两种增长模式的差异。修改属性后一个常见疑问是初始大小改到30MB已有数据怎么办答案是SQL Server不会动你已有的数据页它只是在物理文件层面做扩展。如果文件里已用空间超过30MB改小会失败改大则立即扩展。最大值是上限不是目标值数据库平时就在初始大小附近浮动写入压力上来才向上增长。另外用企业管理器改属性时系统可能提示需要独占访问原因是其它会话正在使用这个库比如查询分析器窗口没关。处理办法是关掉其它连接再试或者用脚本切到单用户模式ALTER DATABASE XSGL SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE XSGL SET MULTI_USER GO第一句把正在执行的会话立即回滚并断开让当前会话独占数据库改完属性后第二句恢复多用户。生产环境不要随便用ROLLBACK IMMEDIATE会杀掉正在跑的事务需要提前评估影响。2.3 备份、还原与删除增删改查之外最不可逆的操作实验还要求完成备份、还原和删除。数据库的增删改查前三个操作大多针对单条数据而备份还原针对的是整个库地位完全不同。备份在企业管理器里是右键数据库→所有任务→备份数据库还原是所有任务→还原数据库。备份的本质是把数据库一致性状态复制到备份介质还原则是用备份文件覆盖当前库。这里有一个新手最容易踩的坑还原时如果目标库正被其它会话使用会报数据库正在使用无法获得独占访问权。处理方式跟前面一样先切单用户模式再还原。删除数据库是四个操作里最需要谨慎的。在企业管理器里删除XSGL会连带删除物理文件.mdf和.ldf数据彻底没了没有后悔药。所以删除前要确认两件事一是库真的不用了二是备份已经做过。如果只是想清空数据保留结构用TRUNCATE TABLE或者DELETE不要删库重建。备份还原还要注意备份类型的选择。完整备份备份整个库适合日常恢复差异备份只备份自上次完整备份以来的变化恢复时需要先还原完整备份再还原差异备份日志备份用于时间点恢复。实验只要求做完整备份但我建议把差异备份和日志备份也各做一次因为它们是生产环境真正常用的组合。做数据库同步和迁移时这套备份组合也是基础只做完整备份的迁移方案在数据量上来之后会非常被动。3. 数据完整性六类约束、默认值与规则的落地操作数据库质量好不好数据完整性是核心衡量标准。实验报告开篇就给了定义数据完整性指数据的正确性、完备性和一致性分为域完整性、实体完整性和参照完整性三类。这三类的划分不是用来背诵的它们直接决定你用什么数据库对象去实施——域完整性对应CHECK约束和默认值实体完整性对应主键约束参照完整性对应外键约束。分清楚这个对应关系做课程设计时就不至于把约束建错位置。3.1 三类完整性的边界什么情况用哪种约束域完整性管的是这一列的值是否合法。年龄必须是15到30之间的整数性别只能取男或女邮编必须是6位数字这些都属于域完整性用CHECK约束和默认值实现。实体完整性管的是每一行能不能被唯一区分。一张学生表里学号必须唯一否则两条记录无法区分用主键约束或唯一性约束实现。参照完整性管的是表与表之间的引用关系是否成立。SC表的学号必须存在于student表课程号必须存在于course表否则就是孤儿数据用外键约束实现。实验明确列出了六种约束类型非空约束、默认值约束、CHECK约束、主键约束、外键约束、唯一性约束。其中非空和默认值服务域完整性主键服务实体完整性外键服务参照完整性CHECK和唯一性可以同时服务多种完整性需求。六种约束不是六个孤立的对象它们组合起来才构成一张表的完整数据防线。3.2 六种约束的添加与删除记住操作的先后顺序实验用企业管理器逐条实施约束。以student表的年龄字段为例添加CHECK约束让年龄大于15岁且小于30岁ALTER TABLE student ADD CONSTRAINT CK_Student_Age CHECK (Age 15 AND Age 30) GO删除时ALTER TABLE student DROP CONSTRAINT CK_Student_Age GO这段操作背后有个细节值得注意CHECK约束用的是开区间15岁和30岁本身是不合法的。如果业务要求包含边界应该写成Age 15 AND Age 30。做实验按老师要求来但做真实项目时一定要先确认业务上年龄区间的开闭这是需求文档里最容易含糊的地方我见过因为区间开闭理解不一致导致上线后数据校验翻车的。约束添加的顺序也很有讲究。我一般按这个顺序走先建主键约束再建外键约束然后建CHECK和默认值最后建唯一性约束。原因是外键依赖主键存在CHECK依赖列已经定义唯一性约束要等主键数据清理完再建否则已有重复数据会导致创建失败。实验里反复出现若原有约束请先删除再重设这个提示说的就是在约束冲突时先清理再重建的工作流这是SQL Server教学里一个很重要的习惯。默认值约束是另一个容易迷的点。实验给student表的Splace所在系字段设置默认值内蒙。默认值有两种实施方式直接在列定义里写DEFAULT这是列级默认值或者先创建独立的默认值对象再绑定这是通用默认值。实验用的是后者原因是SQL Server的独立默认值对象可以被多个表的多个列复用比如邮编默认值210000可以绑到student的postcode列也可以绑到其它表的邮编列一次创建多处绑定。3.3 通用默认值与规则绑定、解除与删除的顺序通用默认值的完整生命周期是创建对象→绑定到列→解除绑定→删除对象。实验里创建了邮编默认值210000绑定到student表的postcode列。关键问题来了能否不解除绑定直接删除默认值对象答案是不能。SQL Server会拒绝删除还在被引用的对象报错信息类似无法删除默认值因为它已绑定到列。必须先执行sp_unbindefault student.postcode GO DROP DEFAULT DF_Postcode GO规则Rule也是独立对象实验里创建了一个性别规则要求取值只能是男或女。T-SQL写法如下CREATE RULE RULE_Sex AS Ssex 男 OR Ssex 女 GO sp_bindrule RULE_Sex, student.sex GO原文写的是Ssex LIKE [男,女]这是一个典型的坑方括号在LIKE里属于字符集匹配逗号会被当成一个可匹配字符也就是说这个规则会允许插入逗号。正确的写法应该用等值判断或者IN (男,女)。这个细节实验报告本身是错的如果你照着原报告做往sex字段插入一个逗号规则根本不会拦。规则的删除和默认值一样必须先解除绑定sp_unbindrule student.sex GO DROP RULE RULE_Sex GO3.4 级联删除与级联更新方便与危险并存实验最后一部分是外键约束的级联设置。在SC表和student、course表之间建立外键并允许级联删除与级联更新。这意味着删除student表里学号为1001的学生时SC表里所有1001的选课记录会被自动删除如果把1001改成1002SC表里对应的外键值也会自动改成1002。这个特性很直观但生产环境里我几乎不用级联删除。原因是真实业务中删除一条主表记录可能连带删掉大量从表数据且删除不可见、不可控。比如删除一个客户连带删光他所有的订单、发票、操作日志——如果哪天发现删错了数据已经没了。常见做法是逻辑删除在表上加is_deleted字段查询时过滤掉已删除标记的记录这样数据永远在只是不可见。实验要求掌握级联操作目的是理解外键的引用关系不是让你在真实系统里滥用这个边界要分清。4. 索引与视图从聚簇索引到 WITH CHECK OPTION 的实操细节索引和视图是查询性能的两个关键工具。实验前半部分是索引要求创建唯一聚簇索引、复合索引并设置填充因子后半部分是视图要求创建投影视图、修改视图和管理视图数据。这部分是最容易照抄错的地方因为实验报告在索引类型上存在逻辑矛盾需要自己判断。4.1 聚簇索引与非聚簇索引一张表只能有一个聚簇索引实验第一个要求是给student表创建以sno为关键字的唯一聚簇索引。聚簇索引决定表数据的物理存储顺序数据行会按索引键值的顺序排列在磁盘上所以每张表只能有一个聚簇索引。非聚簇索引则是独立于数据页的索引结构不改变物理顺序一张表可以建多个。创建唯一聚簇索引的T-SQLCREATE UNIQUE CLUSTERED INDEX sno_index ON student(sno ASC) GO为什么实验选sno做唯一聚簇索引因为学号是主键查询通常按学号定位。聚簇索引的叶子节点就是数据行本身按sno做范围查询时只需要顺序扫描一段物理页I/O次数少。如果sno做的是非聚簇索引范围查询要先查索引再逐行回表代价高得多。这个选择思路放到今天依然成立主键通常是查询最频繁的键把物理存储顺序和主键顺序统一是性价比最高的索引设计。4.2 复合索引与填充因子70%到底在防什么实验第二个要求是在student表上创建以sex、sname为索引关键字的索引sname升序、sex降序填充因子70%索引名ss_index。原文写的类型也是聚簇索引但这里有个矛盾表已经有一个sno聚簇索引了不可能再建第二个聚簇索引SQL Server会直接报错表上已存在聚簇索引。正确的做法是建成非聚簇索引CREATE NONCLUSTERED INDEX ss_index ON student(sex DESC, sname ASC) WITH FILLFACTOR 70 GO填充因子FILLFACTOR是一个容易被忽略但很有用的参数。它表示创建索引时索引页预先填满的百分比。70%意味着每个索引页留30%空间给未来的插入减少因插入导致的索引页分裂次数。页分裂的代价很高要移动数据行并更新索引指针所以频繁插入更新的表填充因子通常设60到80几乎只读的表设100。实验里用70%是针对学生表这种有持续插入操作的场景的合理选择。如果这张表只是读多写少设70%反而浪费空间白留了30%的页空着。索引的维护操作也包含在实验里重命名和删除。重命名索引不能用ALTER TABLE要用系统存储过程EXEC sp_rename student.sno_index, sno_index1, INDEX GO删除索引DROP INDEX student.sno_index1 GO注意DROP INDEX的语法是表名.索引名这和创建时的书写习惯不同我在辅导课程设计时见过不少人在这一步报语法错误。另外重命名索引在生产环境要格外谨慎应用里如果写死了索引名重命名会导致SQL语句走不到这个索引。4.3 视图保存的是查询不是数据视图是数据库课程设计的常客也是实验的重点。实验要求创建一个名为studview的视图从student表查出籍贯为内蒙的所有学生的学号、姓名、性别、籍贯、年龄。视图的本质是一个保存的SELECT语句不占额外存储查询视图时SQL Server会实时执行背后的语句。创建视图CREATE VIEW studview AS SELECT sno, sname, ssex, splace, sage FROM student WHERE splace 内蒙 GO修改视图用ALTER VIEW查看视图定义用EXEC sp_helptext studview。视图的价值在于封装业务侧只暴露视图不暴露底层表结构可以用视图限制用户只能看某些列作为安全层复杂查询封装成视图后应用层查询语句变得简单。但要注意视图不是性能优化工具一个视图背后如果是多张大表的JOIN查询视图的代价一样高不能在视图上建索引来加速索引视图除外那套规则更复杂。实验里还有一步用视图管理数据把视图中学号为1001的学生姓名改成许华然后返回底层表确认变化。这是一个很多人会搞混的点——通过视图做DML修改会作用到底层表因为视图本质就是底层表的一个投影。但有一个限制如果视图的定义里用了聚合、GROUP BY或者多表JOINSQL Server通常不允许通过视图做增删改。视图更新规则比较严格只支持可更新视图具体能不能改要看视图定义是否保持了与基础表的直接映射关系。4.4 WITH CHECK OPTION防止视图出现看不见的数据实验最后要求创建视图stuview1从student表查出性别为男的学生并且使用WITH CHECK OPTION。这个子句的作用是通过视图插入或修改数据时必须满足视图定义的WHERE条件。加了这个子句后如果通过视图插入一条性别为女的记录SQL Server会直接拒绝因为插入后的数据不满足视图的查询条件会造成数据从视图里消失的诡异现象。不加的话可以插入违反视图条件的记录但这条记录在视图里永远查不到。这个边界认知很重要——不加WITH CHECK OPTION的视图插入数据后可能查不到自己刚插入的记录这种问题排查起来非常隐蔽。5. 避坑手册数据库实验里最常见的五个翻车现场这一章写这次实验里反复出现、以及我做数据库课程设计辅导时见别人踩得最多的五个坑。每条按现象、原因、解决三个环节记录照着排查能省掉大量DEBUG时间。5.1 还原数据库报无法获得独占访问权现象在企业管理器里右键还原XSGL弹窗提示数据库正在使用无法获得独占访问权还原卡住不动。原因还原的本质是用备份文件整体覆盖当前数据库文件SQL Server要求还原期间没有任何其它会话连接到这个库。只要有一个查询分析器窗口、一个应用连接没关独占就拿不到。最常见的是自己忘了关掉刚才建库时打开的查询窗口。解决先关掉所有连接再用脚本把库切到单用户模式还原完成再切回来ALTER DATABASE XSGL SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO -- 执行还原操作 ALTER DATABASE XSGL SET MULTI_USER GO我每次还原前都会先查一下SELECT * FROM sys.dm_exec_sessions WHERE database_id DB_ID(XSGL)确认没有其它会话再动手这样可以避免反复开关窗口的麻烦。5.2 删除默认值对象报已绑定到列现象在企业管理器里右键删除默认值对象系统提示无法删除默认值因为它已绑定到列删除操作被拦下来。原因SQL Server不允许删除仍被引用的对象。默认值对象在用sp_bindefault绑定到postcode列之后对象和列之间存在引用关系必须先解除绑定。解决先解除绑定再删除sp_unbindefault student.postcode GO DROP DEFAULT DF_Postcode GO规则对象同理先sp_unbindrule再DROP RULE。这个先解绑再删除的顺序是通用默认值和规则这两类对象特有的。实验报告专门问了一句若未解除绑定能否删除默认值可见老师很清楚学生一定会在这里撞一次。5.3 创建第二个聚簇索引报错现象按照实验要求先建了sno唯一聚簇索引再建sex、sname复合索引时SQL Server报错表上已存在聚簇索引无法创建。原因聚簇索引决定表数据的物理存储顺序每张表只能有一个。实验报告里把复合索引也写成了聚簇索引这是报告本身的逻辑错误照抄必然失败。这个不是操作问题是实验设计的问题需要自己判断改过来。解决把复合索引改为非聚簇索引CREATE NONCLUSTERED INDEX ss_index ON student(sex DESC, sname ASC) WITH FILLFACTOR 70 GO如果确实需要按sex、sname做物理排序的聚簇索引那就必须放弃sno聚簇索引两个方案二选一。实际业务中通常保留主键聚簇索引复合查询走非聚簇索引加回表很少为了一个复合查询牺牲主键的物理顺序。5.4 性别规则形同虚设逗号也能插进去现象绑定性别规则后测试插入数据发现往sex字段插入一个逗号竟然成功了规则没有拦住。原因实验报告里规则定义写的是Ssex LIKE [男,女]方括号是LIKE的字符集匹配语法逗号被当成了一个可匹配的字符。这个写法能匹配男、女、,三个字符等于给数据开了个后门。这属于规则定义本身的写法错误不是SQL Server的问题。解决改成等值判断或者直接用CHECK约束替代规则CREATE RULE RULE_Sex AS Ssex 男 OR Ssex 女 GO更推荐用CHECK约束因为规则是SQL Server的老特性可移植性差现在的主流做法是CHECK约束ALTER TABLE student ADD CONSTRAINT CK_Student_Sex CHECK (Ssex IN (男, 女)) GO5.5 修改数据库属性提示磁盘空间不足现象把XSGL数据文件初始大小从20MB改成30MB系统提示磁盘空间不足操作失败。原因扩展数据文件需要在磁盘上分配连续空间。如果数据文件所在磁盘分区可用空间小于10MB或者文件碎片严重扩展就会失败。很多时候表面看磁盘总空间够但连续可用空间不够。解决先检查磁盘可用空间和文件当前大小EXEC sp_spaceused GO SELECT name, size, max_size, growth FROM sys.database_files GO如果空间确实紧张可以把数据文件迁移到其它空闲盘ALTER DATABASE XSGL MODIFY FILE ( NAME XSGL, FILENAME NE:\SQLData\XSGL.mdf ) GO注意迁移物理文件需要先分离数据库再操作文件或者使用文件和文件组的在线迁移功能数据库在线状态下直接改FILENAME是会报错的这个步骤容易翻车。6. 验证实验结果的四个检查点让每一步操作都有据可查做完实验不等于做对了实验。我自己的习惯是每完成一个环节用系统视图和脚本验证一遍确认参数和对象真的按预期生效。这里给出四个直接可用的检查点配合企业管理器的图形界面双保险新手也能照着走。第一个检查点验证数据库文件和增长参数。建库或修改属性后查sys.database_files确认初始大小、最大值、增长增量SELECT name, type_desc, size * 8 / 1024 AS 当前大小MB, max_size * 8 / 1024 AS 最大大小MB, growth, is_percent_growth FROM sys.database_files GOtype_desc为ROWS是数据文件LOG是日志文件size单位是8KB页所以要乘8除以1024换算成MBgrowth为0表示禁用自动增长is_percent_growth为1表示按百分比增长。这四个字段能完整还原企业管理器属性页里的所有设置图形界面改了之后用这段脚本确认最稳妥。第二个检查点验证索引类型和填充因子SELECT name, type_desc, is_unique, fill_factor FROM sys.indexes WHERE object_id OBJECT_ID(student) GOtype_desc里CLUSTERED是聚簇索引NONCLUSTERED是非聚簇索引is_unique为1是唯一索引fill_factor就是填充因子。如果发现sex和sname的索引显示CLUSTERED说明之前创建的聚簇索引没删干净或者创建顺序有误需要回头清理。第三个检查点验证约束是否真的在拦数据。约束不是建完就完要用一条违反约束的INSERT去撞一下-- 期望报错年龄12岁违反CHECK约束 INSERT INTO student (sno, sname, age) VALUES (1009, 测试, 12) GO -- 期望报错性别值未知违反规则/CHECK约束 INSERT INTO student (sno, sname, ssex) VALUES (1010, 测试, 未知) GO如果两条都报错说明CHECK和性别规则都生效了。如果某条没报错回查约束定义大概率是规则写法出了问题。这个故意写错数据去撞约束的方法是验证数据完整性最直接的手段比看定义可靠得多——定义写得再漂亮不实际拦一条数据都不能算数。第四个检查点验证视图的WITH CHECK OPTION。创建视图后尝试通过视图插入一条不满足条件的记录-- 期望报错通过stuview1插入性别为女的记录违反视图条件 INSERT INTO stuview1 (sno, sname, ssex) VALUES (1011, 测试, 女) GO报错说明WITH CHECK OPTION生效如果不报错说明视图定义里漏了这个子句要回企业管理器重新检查。这一步能验证视图封装的边界是否真的可靠也能防止插进去却查不到的诡异数据问题。四个检查点跑完这份实验报告里的所有操作才算真正闭环。说实话我第一次做这类实验时建完库和索引就直接交功课从不去验证结果被老师问起你怎么确定索引是聚簇的就答不上来。从那以后我每次做完数据库改动都强制走一遍sys.database_files、sys.indexes和反向INSERT验证花不了两分钟但能确认每一步操作都落到了实处。做数据库这行最怕的就是界面点了确定就当成功数据不会撒谎系统字典才是最终答案。希望帮到你。本文还有配套的精品资源点击获取
![[从入门到入土!!!] Ubuntu20.04 系统开发环境配置（系统配置，显卡驱动，CUDA，ROS，VSCode，Anaconda等）](http://pic.xiahunao.cn/yaotu/[从入门到入土!!!] Ubuntu20.04 系统开发环境配置（系统配置，显卡驱动，CUDA，ROS，VSCode，Anaconda等）)
