简介这份《华科数据库实验报告》出自华中科技大学数据库系统概论课程是面向高校计算机专业学生及SQL Server初学者的完整实验范本。报告围绕SQL Server 2008展开系统覆盖DDL数据定义、DML数据操作、DCL权限控制及数据库备份恢复四大核心模块并包含基本表创建与数据插入、多表查询与聚合函数、数据更新删除、视图操作、内置函数与授权控制等六项实验的详细操作过程。每项实验都配有可复现的步骤与运行逻辑能帮助读者对照练习快速掌握数据库的建表、查询、权限管理和安全维护等关键技能。资源包内为1个doc文档大小294KB内容结构完整包含实验目的、原理、内容、过程与心得体会既可作为课程实验报告写作范本也可作为SQL Server 2008上机复习的参考资料。目前已有251人学习下载适合正在学习数据库课程、需要完成类似实验或准备考试的同学参考使用。1. 一份直接照着跑的数据库实验报告SQL Server 2008 从建表到备份恢复这份华科数据库系统概论实验报告第一次看的时候我就觉得它像一份被很多届学生传下来的“祖传代码”——六个实验把 SQL Server 2008 的建表、插数、查询、修改删除、视图、授权和备份恢复全串了一遍几乎覆盖了数据库课程里所有必考操作。对要交实验报告的人来说它是能直接照着复现的参考答案对我这种多年没碰 T-SQL 的老手它也是十分钟找回增删改查手感的最小样例。不过照着敲之前先留个心原文代码里藏了至少四个坑比如小写 c2、审题误差、Oracle 式授权语法下面逐章讲清楚。2. 建表与插数DDL 和 DML 的第一步怎么才不返工2.1 创建数据库与三张表主键、外键和列类型一次定好先把 SQL Server 2008 Management Studio 打开新建一个查询窗口。这个窗口在实验报告里叫查询分析器所有 SQL 都在这里输入、编辑、运行。第一步是建库和切库按顺序执行下面两段。create database ems; go use ems; gocreate database会在实例默认路径上创建数据库 ems数据文件和日志文件的位置由服务器配置决定go是 SSMS 的批处理分隔符不是 T-SQL 语法本身。use ems把当前上下文切到新建库后面建表都在这条命令之后执行。然后是报告里的三张表我加了关键注释create table students( sno char(9) primary key, -- 学号主键 sname char(20) not null, -- 姓名 age char(3), -- 年龄 sex char(6) -- 性别 ); create table courses( cno char(9) primary key, -- 课程号主键 cname char(20) not null, -- 课程名 score int, -- 学分原报告把列名写成了 score pc char(3) -- 先行课号 ); create table sc( sno char(9) foreign key references students(sno), -- 选修表引用学生 cno char(9), grade int, foreign key(cno) references courses(cno) -- 选修表引用课程 );逻辑说明students 和 courses 各有一个主键主键列默认非空且唯一对应实体完整性sc 表的两个外键分别指向两张主表保证参照完整性——sc 里出现的每个 sno 必须已经在 students 中存在每个 cno 必须已经在 courses 中存在。sc 的 grade 没加约束允许 NULL对应原表里 S2 的 C2 成绩、S4 的 C4 成绩这些空缺值。参数说明三张表的列都用char定长字符型。char(n)存入短内容会自动右补空格SQL Server 查询比较时会忽略尾随空格但用len()函数、字符串拼接或导出数据时你就能看到那些空格。实验数据里学号才两位字符用char(9)纯属预留空间实际开发中这种短码用varchar(9)更合理能省存储、少踩坑。这里有两个值得注意的点。第一age列定义成char(3)但插入的是 20、19 这些数字字面量SQL Server 会隐式转成字符20再存。数据都是两位数时看不出问题一旦出现一位数年龄where age 20这种比较就会按字符串逐字符比较9 20会得到你完全没想到的结果。第二courses表的score列是int但原数据里 C4 的学分是 3.5插入时会发生数值转换3.5 会被舍入成 4。如果学分需要保留 0.5 精度正确做法是把该列定义成numeric(4,1)。这是原报告“表结构照抄、数据照插”没暴露出来的隐藏问题。注意char(n)会右补空格年龄这类将来可能参与比较的字段建议用varchar或tinyint别用定长字符列。2.2 批量 INSERT省略列名、NULL 和外键插入顺序数据插入原报告是一行一行 insert 的这里把有代表性的语句合在一起。use ems; go insert into students values(S1,LU,20,M); insert into students values(S2,YIN,19,M); insert into students values(S3,XU,18,F); insert into students values(S4,QU,18,F); insert into students values(S6,PAN,14,M); insert into students values(S8,DONG,24,M); insert into courses values(C1,数学,4,M); insert into courses values(C2,英语,8,M); insert into courses values(C3,数据结构,4,F); insert into courses values(C4,数据库,3.5,F); insert into courses values(C5,网络,4,M); insert into sc values(S1,C1,85); insert into sc values(S2,C1,90); insert into sc values(S3,C1,89); insert into sc values(S4,C1,84); insert into sc values(S6,C1,88); insert into sc values(S8,C1,87); insert into sc values(S1,C2,73); insert into sc values(S2,C2,NULL); insert into sc values(S3,C2,86); insert into sc values(S4,C2,82); insert into sc values(S6,C2,75); insert into sc values(S8,C2,85); insert into sc values(S1,C3,88); insert into sc values(S2,C3,80); insert into sc values(S6,C3,90); insert into sc values(S8,C3,NULL); insert into sc values(S1,C4,89); insert into sc values(S2,C4,85); insert into sc values(S4,C4,NULL); insert into sc values(S6,C4,92); insert into sc values(S8,C4,88); insert into sc values(S1,C5,73); insert into sc values(S2,C5,NULL); insert into sc values(S8,C5,87);执行顺序很有讲究先插 students 和 courses再插 sc。sc 的外键约束要求在插入时对应的主表记录已经存在反着来 SQL Server 会抛“INSERT 语句与 FOREIGN KEY 约束冲突”这是新手最容易遇到的第一条报错。INSERT 省略列名时values 列表必须严格对应建表时的列顺序students 是 sno、sname、age、sexcourses 是 cno、cname、score、pcsc 是 sno、cno、grade。写错一位就把数据塞到别的列里这种错不报异常但结果全错最难排查。关于 NULL 的插入S2 的 C2 成绩、S4 的 C4 成绩都是 NULLINSERT 时直接写 NULL 就行。注意 NULL 不是 0后面聚合查询里count(grade)和avg(grade)会把它跳过这是第 3 章要说的关键点。另外 courses 里 C3 和 C4 的 pc 是F看起来是照抄了表 2 的说明列先行课号并未指向某个实际存在的课程号原报告也没对这个字段加外键所以它只是个普通字符列不会真的去校验 F 是不是合法课程。完整性约束不是每个字段都要加但要清楚哪些加了、哪些没加。3. SELECT 查询连接查询与 NOT EXISTS 双否定的四种写法3.1 旧式连接查询的执行逻辑为什么用表名.列名实验 2 前两个查询都是典型的多表查询原报告用的是 where 等值连接的旧写法在 SQL Server 2008 里没问题但读代码时要知道它等价于什么。第一个查询select sc.sno, sname from students, sc where sc.cnoC2 and sc.snostudents.sno;逻辑说明from 后面直接跟两张表相当于先做笛卡尔积再用 where 里的两个条件过滤sc.cnoC2筛掉不是 C2 的行sc.snostudents.sno把选修记录和学生信息对上。这种写法就是 inner join 的老版本执行计划最终也会转成嵌套循环或哈希连接但可读性差。参数说明sc.sno前面带了表名前缀是因为 sno 在 students 和 sc 两张表里都存在不加前缀 SQL Server 会报“列名不明确”sname只存在于 students所以省略了前缀。建议不管有没有二义性都写成“表名.列名”将来重构表结构时不用回去猜。如果不想用这种旧式写法SQL Server 2008 也支持显式 inner join同样的查询可以写成下面这样两种写法在优化器眼里基本等价但显式连接的表关系更直观select sc.sno, students.sname from sc inner join students on students.sno sc.sno where sc.cno C2;第二种查询加了 courses 表条件变成三张表串联整体思路是一样的。select sc.sno, sname from students, sc, courses where courses.cname数学 and courses.cnosc.cno and students.snosc.sno;执行逻辑是按课程名找到 C1再通过 sc 的 cno 找到选课记录再通过 sno 找到学生。三个等值条件缺一个要么笛卡尔积爆炸要么结果错乱。要注意表里恰好只有一门“数学”如果存在同名课程这门课的多个记录会全部返回所以生产环境中更稳妥的过滤键是课程号 cno而不是课程名 cname。3.2 NOT EXISTS 双否定选修全部课程的经典解法实验 2 的第34题是 NOT EXISTS 的主场。第三个查询select sname, age from students where not exists( select * from sc where sc.cnoC2 and snostudents.sno );逻辑说明内层子查询是相关子查询sc 行的 sno 和当前 students 行的 sno 做等值比较。如果该学生选了 C2内层返回至少一行NOT EXISTS 结果为假该学生被排除如果没选 C2内层为空NOT EXISTS 为真进入结果集。所以这个查询的语义是“不存在一条选课记录能证明我选过 C2”等价于反连接anti join。这里有一个排序规则相关的坑原报告代码里写的是sc.cnoc2小写。SQL Server 默认的Chinese_PRC_CI_AS排序规则里 CI 表示不区分大小写所以小写 c2 也能匹配到 C2但如果你把数据库排序规则改成了Chinese_PRC_CS_AS或者这个库是从大小写敏感的实例恢复过来的c2 就匹配不到任何数据查询结果变成所有人。不要把“默认能跑”当成“这个写法没问题”写 SQL 时字符串常量的大小写要和表数据保持一致。第四个查询是双 NOT EXISTS选修全部课程的经典解法select sname from students where not exists( select * from courses where not exists( select * from sc where snostudents.sno and cnocourses.cno ) );逻辑说明从最内层读起。这门课courses.cno该学生students.sno没选内层 select 为空整个子查询的意思是“存在一门课这个学生没选”外面再套一层 NOT EXISTS变成“不存在一门课是学生没选的”翻译过来就是“所有课都选了”。这是用关系除法的思路做全称量词判断比先 count 再比较总数的方式更贴近关系代数也是很多数据库笔试面试的常客。参数说明这个写法依赖两处相关引用students.sno 来自最外层courses.cno 来自中间层。执行计划里通常会出现多次嵌套循环数据量大时性能并不好实验数据只有十几行完全不用考虑优化。如果要说优化可以换成group by sc.sno having count(distinct cno) (select count(*) from courses)但语义上有 NULL 和重复课程的差异不是完全等价。我一般会在面试里先讲 NOT EXISTS 语义再提性能边界。3.3 聚合查询COUNT、AVG 与 NULL 的相处方式实验 5 的第一个任务是统计每个学生的选修门数和平均成绩这段出现在报告靠后的位置但它本质上是 SELECT 里的分组聚合放到查询这章一起分析更顺。select students.sno, students.sname, count(cno) 选修门数, avg(grade) 平均成绩 from students, sc where students.snosc.sno group by students.sno, sname;逻辑说明from 加 where 先把选课记录和学生连接起来group by students.sno, sname把所有行按学生分组每组再用count(cno)数选修门数、avg(grade)算平均成绩。select 里两个非聚合列 sno、sname 都出现在 group by 中符合 SQL 的分组规则如果漏掉其中一个SQL Server 会直接报错而不会像某些数据库那样“宽容”地返回随机值。参数说明count(cno)统计的是每个分组里 cno 非空的记录数在这个连接结果里 cno 来自 sc 表且外键非空所以不会漏数。avg(grade)只对非 NULL 的 grade 求平均S2 的 C2、S4 的 C4 这些 NULL 成绩会被跳过而不是被当成 0 拉低平均分。如果业务上要求“没成绩算 0 分”得先写coalesce(grade,0)再求平均两个结果完全不同。这也是实验里最容易出现“看着代码没问题结果对不上”的隐藏原因之一。关于这个查询还有一个原报告没提的细节语句没写排序输出顺序不保证稳定。要固定结果顺序就得在 group by 后面加order by 选修门数 desc之类的条件否则不同版本的 SQL Server 可能给出不同顺序提交实验截图时最好连 order by 一起写了。4. 修改与删除的避坑记录UPDATE 和 DELETE 最容易翻车的四个地方原报告里实验 3 和实验 4 的代码量不大但恰恰是这里聚集了最多的翻车现场。下面四条是我复现时踩过、以及帮别人排查时见过的常见问题每条按现象、原因、解决三步说清楚。4.1 坑一UPDATE 的 WHERE 写错列非空成绩没按题意更新现象原报告实验 3 第1题要求“把 C2 课程的非空成绩提高 10%”代码写的是update sc set grade grade * 1.1 where sc.cnoc2 and sc.cno is not null;执行后一看结果C2 课程的成绩确实有部分变了但 NULL 依旧 NULL复习时才发现自己根本没把“非空成绩”这个条件落到 grade 上。原因这里的sc.cno is not null是个无效条件——等值比较cnoc2本身就已经排除了 cno 为 NULL 的行NULL 不满足任何等值比较真正该过滤的是 grade 非空也就是“有成绩的那些行才提高”。这是典型的审题偏差把题目的“非空成绩”理解成了“cno 非空”。解决update sc set grade grade * 1.1 where cno C2 and grade is not null;改完可以用这条语句核对受影响行数select count(*) from sc where cnoC2 and grade is not null;另外还要注意 grade 是 int 列grade*1.1会得到带小数的数值再写回 int 列时 SQL Server 会做舍入85 会变成 94 而不是 93.5。要保留精确小数把 grade 列定义成numeric(6,1)再更新。我一般会先跑 select 把命中行数和更新后的值看一遍再执行 update 语句避免一次 update 把所有行都改错。提示任何 update 和 delete 之前先跑 select 确认命中行数这是成本最低的后悔药。4.2 坑二DELETE 删了个寂寞0 行受影响现象原报告实验 3 第2题执行delete from sc where cno in (select cno from courses where cname物理);结果窗口显示“(0 行受影响)”SC 表数据没有任何变化。原因courses 表里根本没有叫“物理”的课程原数据里只插了数学、英语、数据结构、数据库、网络五门题目的“物理”是从课本例题里搬过来的实验数据没跟上。delete 的 where 命中 0 行自然什么也不删。解决要么先把物理课程和对应选课记录插进去再删要么把删除条件改成实际存在的课程。更重要的习惯是任何 delete 之前先用同样 where 条件的 select 确认要删哪些行select * from sc where cno in (select cno from courses where cname物理);看到行数再决定删不删。这条顺手成本很低能拦住大部分手抖。4.3 坑三先删主表被外键约束拦下现象执行删除 S8 的两条语句时如果先执行delete from students where snoS8;报错内容类似“DELETE 语句与 REFERENCE 约束冲突”S8 删不掉。原因sc 表的 sno 外键引用 students.snoS8 在 sc 里还有多条选课记录主表记录被引用时不允许直接删除这是参照完整性的正常保护。解决先删子表数据再删主表数据原报告的顺序就是对的delete from sc where snoS8; delete from students where snoS8;如果建表时给外键加了on delete cascade也可以只删 students 让 SC 级联删除但级联删除容易在复杂表结构里误伤实验里手动按顺序删更直观可控。删除前同样建议先用 select 确认 S8 在 sc 里的记录条数避免删错。4.4 坑四视图里查“平均成绩大于 80”查成了“单科成绩大于 80”现象原报告实验 4 第2题建好男生视图后执行的是select distinct students.sno, students.sname from student_m, students where student_m.snostudents.sno and grade80;结果拿到的学生里有些人的平均成绩并不大于 80和题目要求对不上。原因题目要的是“平均成绩大于 80 分的学生”而这段代码的 where 条件是grade80语义是“至少有一门课成绩大于 80 分”。distinct 只能去掉重复行不能把多行成绩变成平均成绩。解决先用 group by 按学生分组再having avg(grade)80过滤分组select sno, sname from student_m group by sno, sname having avg(grade) 80;如果还想带上平均分一起展示可以写成select sno, sname, avg(grade) 平均分 from student_m group by sno, sname having avg(grade) 80;这也解释了为什么前面的聚合查询不用 distinct 而是用 group by——distinct 管行去重group by 管分组聚合两者不能互相替代。5. 视图、授权与备份恢复DCL 和安全兜底怎么一次打通5.1 视图实验里外模式的最小落地实验 4 的第一个任务是建男生视图代码很简单use ems; go create view student_m(sno, sname, cname, grade) as select students.sno, students.sname, cname, grade from sc, students, courses where students.snosc.sno and courses.cnosc.cno and sexM;逻辑说明视图在 SQL Server 里只保存定义不保存数据每次查询视图时系统会把视图定义展开成底层表的 select 再执行。这里 view 名后面的括号里显式定义了四个列名 sno、sname、cname、grade和 select 列表一一对应如果不写这组列名视图列就用 select 里的原始列名也是这四列显式写出主要是为了让下游使用方不依赖底层表结构。参数说明建视图时 where 条件sexM把性别过滤提前做掉视图对外暴露的只有男生数据。底层三张表通过 sno 和 cno 关联学生没有选课记录就不会出现在视图里这符合“视图是终端用户数据来源”的定义。视图建好后后续查询可以直接把它当一张表用比如 4.4 里对 student_m 做 group by having。这里补充一个实际使用中要注意的点视图列 cname 来自 courses 表如果以后 courses 里插入重复课程名视图会出现语义上重复的行所以查询视图时酌情加 distinct 或按 cno 分组是合理防御。原报告在后续查询里加了 distinct方向是对的只是没解决平均成绩的真正语义。5.2 登录、用户与授权sp_addlogin 引号和 GRANT 写法差异实验 5 要求建立一个合法用户并授权。SQL Server 的权限链路是三层在服务器层建登录账号login在数据库里建映射用户user再给用户或角色授权GRANT。原报告的代码方向没错但写法上有两个新手必踩的点。第一处是创建登录的存储过程调用use ems; go exec sp_addlogin ems, ems; go use ems; go exec sp_grantdbaccess ems, ems;说明原报告写的是exec sp_addlogin ems,ems登录名和密码都没有加引号。T-SQL 里存储过程的字符串参数必须用单引号括起来或者先用变量赋值再传变量不带引号的 ems 会被当成标识符或变量名解析大部分环境里直接报“必须声明标量变量”之类的错。另外 sp_addlogin 是服务器级操作和当前数据库是谁没有关系第一句 use ems 是多余的真正必须在 ems 数据库上下文里执行的是 sp_grantdbaccess它把登录账号映射成当前数据库的用户。参数说明sp_addlogin ems,ems第一个参数是登录名第二个是密码这里密码也是 ems属于教学演示级别真实环境绝不能这样设。sp_grantdbaccess ems,ems第一个参数是服务器登录名第二个是数据库用户名一般保持一致。注意如果建立了名为 ems 的登录却没有在本库建立用户映射那这个登录能连上服务器但看不到 ems 库的数据。第二处是授权语句的写法差异。原报告写的是GRANT all privileges ON Courses TO guest;这明显是 Oracle 或 MySQL 的授权习惯。SQL Server 2008 的 GRANT 语法接受权限列表更推荐把需要授权的操作显式列出来避免用 all privileges 这种跨数据库语义不一致的写法。稳妥的写法是use ems; go GRANT SELECT ON sc TO ems; GRANT SELECT, INSERT, UPDATE, DELETE ON students TO ems; GRANT SELECT ON courses TO guest;说明GRANT 后面可以直接写多个权限用逗号分隔再 ON 到具体对象最后 TO 到用户。guest 是每个数据库里默认存在的特殊用户给 guest 授权意味着所有能登录 SQL Server 的账号都可以通过 guest 访问这张表实验环境无所谓生产环境要克制。查看当前数据库用户信息用sp_helpuser列出的字段包含用户名、登录名和默认 schema做验收比手工联系统视图更快。实验后想清理授权用revoke select on sc from ems;即可。5.3 备份与恢复BACKUP 和 RESTORE 的最小可运行链路实验 6 要求备份到软盘这个存储介质已经退出历史舞台了现在备份一律走磁盘或磁带。完整备份的最小链路是先建备份设备再执行 backup模拟损坏后 drop最后 restore 回来。exec sp_addumpdevice DISK, ems_backup, d:\backup\ems.bak; go backup database ems to ems_backup;说明sp_addumpdevice 三个参数分别是设备类型、逻辑设备名、物理文件路径。d:\backup目录必须提前建好否则备份直接失败逻辑设备名 ems_backup 只是给 backup 语句用的别名实际数据落在 ems.bak 里。模拟“数据库损坏”最简单粗暴的做法是直接删库drop database ems; go restore database ems from ems_backup with replace;说明drop database 把 ems 的数据文件和日志文件一起删掉restore 从备份设备读回完整备份。with replace的作用是允许覆盖现有的同名数据库如果 ems 还在而且没有 droprestore 会报无法覆盖加 replace 可以直接顶掉。恢复后建议立刻做一次行数核对也就是最后一章要说的事。备份类型上完整备份是基础差异备份基于最近一次完整备份、只备份变化部分事务日志备份可以做到时间点恢复。实验里完整备份足够真实系统里至少要完整备份加日志备份的组合否则从上一次备份到故障点之间的数据改动全会丢。原报告提到的几种备份类型实际场景里的优先级是完整备份大于差异备份差异备份大于日志备份文件和文件组备份用于超大库的分段备份。6. 验证实验结果的三个习惯从系统视图到还原核对6.1 用系统目录视图确认建表和授权真的生效命令执行成功不代表结构就是你要的。实验做完我固定会用这几个查询来验收。select name, type_desc from sys.tables where is_ms_shipped 0;这一句能列出库里所有用户表看是不是 students、courses、sc 三张都在。select name, is_primary_key, is_foreign_key from sys.key_constraints;确认主键和外键约束都建上了别建表时报错被忽略。再想看某张表的列定义直接exec sp_help sc;它会一次性把列、类型、约束、索引都列出来。授权是否生效查数据库权限视图select grantee_name, permission_name, state_desc from sys.database_permissions where grantee_name ems;如果查到 ems 对 sc 有 SELECT 权限说明第 5 章的授权链路是通的。6.2 恢复后的行数核对与收尾清理备份恢复最容易出的问题不是跑不起来而是恢复出来的库和原来的对不上。我会用一条 union all 语句把三张表的行数一次性核完select students 表名, count(*) 行数 from students union all select courses, count(*) from courses union all select sc, count(*) from sc;和实验 1 插入的数据量对一下对得上才能说明备份恢复链路完整。收尾清理有固定顺序先删视图再删子表再删主表最后从数据库里移除用户再从服务器层删登录。use ems; go drop view student_m; drop table sc; drop table courses; drop table students; go exec sp_revokedbaccess ems; exec sp_droplogin ems;顺序有两层含义drop table 时 sc 是外键子表必须先删sp_revokedbaccess 删除数据库用户后登录账号仍然存在必须再 sp_droplogin 才能把服务器层的登录也删掉。顺序反了约束和依赖会把你拦在门口。我记得大学第一次交数据库实验时只看到每个窗口都显示“命令已成功执行”就提交了结果老师打开 sys.tables 一问备份恢复后表行数和原始数据对不上当场翻车。从那以后我每次做完实验都强制走一遍系统视图核对和还原核对把“能跑”变成“核对过”。这份报告的六段实验正好可以串成这条闭环希望帮到你。本文还有配套的精品资源点击获取