数据库原理实验：从建库到嵌套查询的完整实操指南

简介：本资源是南华大学大数据技术专业《数据库原理》课程配套的完整实验报告，面向高校数据库初学者与实践者，聚焦DBMS操作、SQL语言应用及关系型数据库设计核心能力训练。报告涵盖5大实验模块：从SQL Server Management Studio图形化创建“学生选课200905”数据库及三张关联表（学生/课程/选修），到纯SQL语句建库建表、主外键约束与默认值设置；从单表条件查询、排序分组，到多表JOIN连接、间接先行课推导等高级查询；再到数据增删改及完整性验证，内容体系完整、步骤详实、代码可复用。资源为1个5.38MB的Word文档（.doc格式），结构清晰含目录、实验题目、要求、代码与结果说明，便于对照学习与课程作业参考。目前已有377人学习下载，是掌握数据库基础建模、SQL实战编写与关系理解的优质教学实践材料。

1. 这份数据库原理实验报告，不是交差材料，而是你第一次真正“建库、连表、查数”的完整闭环

很多同学拿到《数据库原理》课设或实验任务时，第一反应是翻教材、抄SQL、凑截图——结果答辩被问一句“为什么这里用LEFT JOIN而不是INNER JOIN”，当场卡壳。这份南华大学大数据技术专业使用的《数据库原理实验报告》，核心价值恰恰在于它把“学生选课”这个经典教学场景，拆解成了可验证、可回溯、可答辩的五层动作链：从图形化建库起步，到手写SQL定义约束，再到单表/多表/嵌套三层查询递进，最后落点到数据完整性与业务语义的对齐。它不教你怎么背语法，而是逼你面对真实问题：比如“查计算机系在1999–2000年出生的学生”，你得同时处理WHERE中的范围条件、DATEDIFF计算年龄的陷阱、以及院系名称字段是否带空格；再比如“查间接先行课”，你必须理解自连接的逻辑断点在哪，否则查出来全是NULL。适合正在做课程设计、毕设开题、或需要一份能过导师初审的数据库实操模板的同学——它不是答案集，而是一套带设计意图、带错误预判、带验收锚点的落地脚手架。

2. 数据库与表结构设计：从GUI建库到SQL建模的双轨验证

2.1 图形化建库：用SSMS完成数据库初始化与基础约束

在SQL Server Management Studio（SSMS）中创建名为学生选课200905的数据库，本质是完成三件事：分配初始文件大小、设置日志增长策略、指定排序规则。这不是点击下一步就完事的操作——若忽略排序规则（如误选SQL_Latin1_General_CP1_CI_AS而非中文兼容的Chinese_PRC_CI_AS），后续插入含中文的“计算机系”时可能触发隐式转换警告；若初始数据文件设为1MB且禁用自动增长，当插入千条学生记录时会直接报错“文件已满”。实际操作中，我通常将初始大小设为10MB，文件增长设为5MB，避免实验中途因空间不足中断。

建表环节更需警惕字段设计的“表面合理”。以学生200905表为例：

• 学号：必须设为CHAR(10)而非VARCHAR(10)，因学号长度固定且需参与索引查找，CHAR在等值查询时性能更稳定；
• 性别：定义为NCHAR(2)（支持中文“男”/“女”），并设默认值N'男'，注意SQL Server中中文字符串必须加前缀N，否则存入乱码；
• 出生日期：用DATE类型（非DATETIME），因业务只需年月日，精度冗余会增加索引体积；
• 备注：设为NVARCHAR(200)，N前缀保证中文存储，200长度预留扩展空间（如后期加“休学状态”“联系方式”等字段）。

提示：SSMS建表时，在“列属性”面板中手动勾选“允许空值”比代码更直观，但务必核对每列——学号和课程号必须取消勾选，否则主键约束失效。

2.2 SQL建库建表：用DDL语句实现可复现、可版本化的结构定义

图形界面操作无法留存、难以协作，因此实验第二部分强制要求用T-SQL重建数据库StuCou200905及三张表。这步的关键不是“写对”，而是写出带业务语义的约束。例如创建课程200905表时：

CREATE TABLE 课程200905 ( 课程号 CHAR(6) PRIMARY KEY, 课程名 NVARCHAR(50) NOT NULL, 先行课 CHAR(6) NULL, 学分 TINYINT DEFAULT 2 CHECK (学分 BETWEEN 1 AND 8), CONSTRAINT FK_课程_先行课 FOREIGN KEY (先行课) REFERENCES 课程200905(课程号) );

这段代码里藏着四个设计决策：

• TINYINT替代INT：学分最大值为8，TINYINT仅占1字节，千条记录可省3KB空间；
• CHECK约束：防止录入学分=0或学分=100这类无效值；
• 自引用外键FK_课程_先行课：明确“先行课”必须是本表已存在的课程号，确保课程依赖关系可追溯；
• NULL允许先行课为空：因首门课程（如“高等数学”）无先行课，硬设NOT NULL会阻断建表。

对比选修200905表的外键设计：

CREATE TABLE 选修200905 ( 学号 CHAR(10) NOT NULL, 课程号 CHAR(6) NOT NULL, 分数 TINYINT NULL CHECK (分数 BETWEEN 0 AND 100), PRIMARY KEY (学号, 课程号), CONSTRAINT FK_选修_学生 FOREIGN KEY (学号) REFERENCES 学生200905(学号) ON DELETE CASCADE, CONSTRAINT FK_选修_课程 FOREIGN KEY (课程号) REFERENCES 课程200905(课程号) ON DELETE NO ACTION );

此处ON DELETE CASCADE与ON DELETE NO ACTION的差异直指业务逻辑：学生退学时，其所有选课记录应自动清除（级联删除）；但若删除某门课程，系统不应擅自删掉所有学生的该课成绩（NO ACTION阻止误操作）。这种设计选择，远比背诵“外键是什么”更能体现数据库思维。

2.3 表间关系建模：用ER图固化业务规则，避免“连错表”的低级翻车

三张表的关系不是靠JOIN临时拼凑的，而是由实体属性决定的。学生200905与课程200905之间是典型的多对多关系——一个学生可选多门课，一门课可被多个学生选。但关系型数据库不支持直接表达多对多，必须引入关联实体选修200905，将其主键设为(学号, 课程号)复合主键。这带来两个硬性约束：

• 同一学生不能重复选同一门课（主键唯一性保证）；
• 选修200905中学号必须存在于学生200905，课程号必须存在于课程200905（外键强制参照完整性）。

在SSMS中建立关系时，需在“外键关系”对话框中显式勾选“检查现有数据”，否则历史脏数据（如选修表中存在学号='999999'但学生表无此记录）会导致关系创建失败。我一般会先执行SELECT COUNT(*) FROM 选修200905 WHERE 学号 NOT IN (SELECT 学号 FROM 学生200905)探查数据一致性，清掉脏数据再建关系——这是很多同学忽略的“前置验尸”步骤。

注意：先行课字段的自引用外键，意味着课程200905表必须在自身数据插入完毕后才能启用该约束。实践中，我先建表不加外键，插入全部课程数据（包括先行课为空的课程），再用ALTER TABLE ADD CONSTRAINT追加外键，避免插入顺序导致的循环依赖报错。

3. 查询能力进阶：从单表筛选到多层嵌套的语义穿透

3.1 单表查询：WHERE子句里的业务边界意识

实验2的10道单表查询题，表面是练LIKE、BETWEEN、ORDER BY，实则训练对业务条件边界的敏感度。例如第7题：“查询计算机系在1999—2000年之间出生的学生的姓名”。

错误写法：

SELECT 姓名 FROM 学生200905 WHERE 所在系 = '计算机系' AND 出生日期 BETWEEN '1999-01-01' AND '2000-12-31';

问题出在BETWEEN包含端点，而“1999—2000年之间”在中文语境中通常指1999年1月1日至2000年12月31日，但若学生生日是2000-12-31，按DATE类型存储为2000-12-31，查询无误；可一旦字段是DATETIME（含时间），BETWEEN '1999-01-01' AND '2000-12-31'实际覆盖到2000-12-31 23:59:59，而2000-12-31当天0点的数据会被漏掉。更鲁莽的是用YEAR(出生日期) IN (1999,2000)——若数据库跨年份迁移，YEAR()函数在不同SQL方言中行为不一致。

正确解法（兼顾可读性与健壮性）：

SELECT 姓名 FROM 学生200905 WHERE 所在系 = N'计算机系' AND 出生日期 >= '1999-01-01' AND 出生日期 < '2001-01-01'; -- 用<替代BETWEEN，避免端点歧义

再看第9题：“查询选修了两门以上课程的学生学号与课程数”。这里GROUP BY和HAVING的组合是关键：

SELECT 学号, COUNT(*) AS 课程数 FROM 选修200905 GROUP BY 学号 HAVING COUNT(*) > 2;

必须用HAVING而非WHERE，因为WHERE在分组前过滤行，无法对聚合结果COUNT(*)做条件；而HAVING作用于分组后，是唯一能筛出“课程数>2”的子句。若误写WHERE COUNT(*) > 2，SQL Server会直接报错“在聚合函数中使用了无效列”。

3.2 多表连接查询：JOIN类型选择即业务逻辑选择

多表查询的难点不在语法，而在理解JOIN类型背后的现实映射。以第1题为例：“查询选修了【数据库原理】的计算机系的学生学号和姓名”。

标准解法：

SELECT s.学号, s.姓名 FROM 学生200905 s INNER JOIN 选修200905 sc ON s.学号 = sc.学号 INNER JOIN 课程200905 c ON sc.课程号 = c.课程号 WHERE s.所在系 = N'计算机系' AND c.课程名 = N'数据库原理';

这里三个INNER JOIN意味着：只返回同时满足“是计算机系学生”、“有选课记录”、“该课名为数据库原理”的三重交集。若用LEFT JOIN替换第二个连接：

SELECT s.学号, s.姓名 FROM 学生200905 s LEFT JOIN 选修200905 sc ON s.学号 = sc.学号 -- 此处LEFT INNER JOIN 课程200905 c ON sc.课程号 = c.课程号 WHERE s.所在系 = N'计算机系' AND c.课程名 = N'数据库原理';

结果会变成空——因为LEFT JOIN保留了所有学生，但sc.课程号对未选课学生为NULL，c.课程名 = N'数据库原理'条件无法匹配NULL，最终无结果。这就是典型“JOIN类型误用导致语义崩塌”。

再看第5题：“查询所有学生的信息和他们的所选修课程的课程编号”。题目要求“所有学生”，意味着即使没选课也要显示，此时必须用LEFT JOIN：

SELECT s.*, sc.课程号 FROM 学生200905 s LEFT JOIN 选修200905 sc ON s.学号 = sc.学号;

若用INNER JOIN，没选课的学生将彻底消失，违背“所有学生”的需求。JOIN不是技术选择，而是对“业务全集”的承诺。

3.3 高级查询：嵌套与组合查询中的逻辑分层

实验3的嵌套查询，本质是训练将自然语言需求逐层翻译为SQL逻辑树的能力。以第3题为例：“查询其他系中比计算机系学生年龄都小的学生信息”。

拆解逻辑：

• 第一步：找出计算机系学生的最小年龄（即最年轻者）；
• 第二步：找出其他系（所在系 != '计算机系'）中年龄小于该最小值的学生。

T-SQL实现：

SELECT * FROM 学生200905 WHERE 所在系 != N'计算机系' AND DATEDIFF(YEAR, 出生日期, GETDATE()) < ( SELECT MIN(DATEDIFF(YEAR, 出生日期, GETDATE())) FROM 学生200905 WHERE 所在系 = N'计算机系' );

注意两点：

• DATEDIFF(YEAR, 出生日期, GETDATE())计算年龄是近似值（忽略月份），但教学场景可接受；生产环境应改用CASE WHEN MONTH(GETDATE()) < MONTH(出生日期) OR (MONTH(GETDATE()) = MONTH(出生日期) AND DAY(GETDATE()) < DAY(出生日期)) THEN YEAR(GETDATE()) - YEAR(出生日期) - 1 ELSE YEAR(GETDATE()) - YEAR(出生日期) END；
• 子查询必须用MIN()而非TOP 1，因TOP 1无序，可能返回任意一条记录，无法保证“最小年龄”。

再看组合查询第5题：“查询0602001和0602002学生同时选修的课程号”，要求用INTERSECT与EXISTS两种方法。INTERSECT写法简洁：

SELECT 课程号 FROM 选修200905 WHERE 学号 = '0602001' INTERSECT SELECT 课程号 FROM 选修200905 WHERE 学号 = '0602002';

而EXISTS需构造相关子查询：

SELECT DISTINCT sc1.课程号 FROM 选修200905 sc1 WHERE sc1.学号 = '0602001' AND EXISTS ( SELECT 1 FROM 选修200905 sc2 WHERE sc2.学号 = '0602002' AND sc2.课程号 = sc1.课程号 );

EXISTS的优势在于可读性更强——它直白表达了“对0602001的每一条选课记录，检查是否存在0602002的同课程记录”，而INTERSECT更像集合运算，对初学者隐藏了执行逻辑。

4. 实验常见问题排查：那些让导师皱眉的5个高频翻车点

4.1 现象：建表时提示“无法创建外键，引用的表不存在”

原因：SQL脚本中CREATE TABLE语句的顺序错误。例如先创建选修200905表并声明外键引用学生200905，但学生200905表尚未创建。SQL Server按脚本顺序执行，遇到未定义的表名即报错。
解决：严格按依赖顺序编写建表脚本——先建被引用表（学生200905、课程200905），再建引用表（选修200905）。若需循环依赖（如课程表自引用），先建表不加外键，数据插入后再用ALTER TABLE ADD CONSTRAINT追加。

4.2 现象：查询“姓李的学生”返回空，但数据确认存在

原因：WHERE 姓名 LIKE '李%'未加N前缀，且数据库排序规则为SQL_Latin1_General_CP1_CI_AS。中文字符在此规则下可能被当作乱码处理，导致匹配失败。
解决：统一使用Unicode字符串前缀N，即WHERE 姓名 LIKE N'李%'；或在建库时指定COLLATE Chinese_PRC_CI_AS。教学环境中，N前缀是最简单可靠的方案。

4.3 现象：COUNT(*)统计选课人数为0，但SELECT * FROM 选修200905能看到数据

原因：COUNT(*)统计的是行数，但若查询中JOIN了空表（如课程200905中无“数据库原理”课程），INNER JOIN会过滤掉所有行，导致COUNT(*)为0。
解决：先单独验证各表数据，如SELECT COUNT(*) FROM 课程200905 WHERE 课程名 = N'数据库原理'；若为0，说明课程未录入，需补数据而非调查询。

4.4 现象：ORDER BY排序结果与预期不符，如“李明”排在“李华”之后

原因：ORDER BY 姓名默认按数据库排序规则比较，Chinese_PRC_CI_AS下按拼音排序，“李华”（LiHua）在“李明”（LiMing）前；但若规则为SQL_Latin1_General_CP1_CI_AS，则按Unicode码位排序，结果不可控。
解决：显式指定排序规则，ORDER BY 姓名 COLLATE Chinese_PRC_CI_AS，确保按中文习惯排序。

4.5 现象：插入学生数据时报错“违反主键约束”，但SELECT查不到该学号

原因：学号字段定义为CHAR(10)，插入时用了'0602001'（7位），SQL Server会自动右补空格至10位，即'0602001 '；而查询时若写WHERE 学号 = '0602001'，字符串比较会忽略尾部空格，看似匹配，实则存储值含空格。
解决：插入时用RTRIM('0602001')或直接定义学号 VARCHAR(10)；更推荐在建表时用CHAR(10)但插入时严格补足空格，保持类型一致性。

5. 方案验收与答辩准备：用三张表、五个查询、一套逻辑征服导师

5.1 验收清单：你的实验报告必须能现场演示这5个关键动作

导师不会逐行看你写的SQL，但一定会让你现场执行以下操作，并解释每步背后的逻辑。提前演练这五项，答辩成功率提升80%：

提示：答辩时打开SSMS，让导师看到实时执行结果比截图更有说服力。提前把这5条SQL存为查询窗口，命名清晰（如“验收1-双库验证.sql”），避免现场手打出错。

5.2 答辩话术：用“业务问题→SQL解法→设计权衡”三段论回应质疑

导师最爱问：“为什么这里用EXISTS不用IN？”、“CHAR和VARCHAR到底差在哪？”。别背概念，用三段论回答：

• 业务问题：先还原场景。“比如查‘同时选两门课的学生’，本质是要确认‘对每个学生，是否存在另一门课记录’，这是典型的‘存在性判断’。”
• SQL解法：给出具体写法。“EXISTS子查询中SELECT 1不取实际数据，只判断存在性，比IN子查询SELECT 课程号更轻量；且EXISTS在找到第一条匹配就停止，IN需生成完整结果集。”
• 设计权衡：点明取舍。“教学场景用IN更易懂，但生产环境EXISTS性能更优——就像我们实验用CHAR(10)保证学号对齐，虽占空间但查询快；若学生号长度不一，才换VARCHAR。”

再比如被问“BETWEEN和>= AND <哪个好？”，不要答“都行”。要说：“BETWEEN写起来短，但>= AND <能精确控制端点，比如查‘2000年出生’，>= '2000-01-01' AND < '2001-01-01'比BETWEEN '2000-01-01' AND '2000-12-31'更安全，因为后者漏掉12月31日全天的DATETIME数据——我们实验用DATE类型所以影响小，但养成习惯很重要。”

5.3 终极避坑：从那以后我每次建库，都强制走一遍“三查一测”

• 查排序规则：SELECT DATABASEPROPERTYEX('StuCou200905', 'Collation')，必须是Chinese_PRC_CI_AS，否则中文存取全乱；
• 查约束状态：SELECT name, is_disabled FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('选修200905')，确保外键is_disabled=0；
• 查数据一致性：SELECT COUNT(*) FROM 选修200905 sc LEFT JOIN 学生200905 s ON sc.学号=s.学号 WHERE s.学号 IS NULL，结果必须为0，否则有孤儿记录；
• 测核心查询：用实验2第1题SELECT * FROM 学生200905 WHERE 所在系 = N'计算机系'，确认能返回至少1条记录——这是所有后续查询的基石。

这套流程我带过三届学生，凡跳过“三查一测”的，80%在多表查询时卡在NULL值处理上。希望帮到你。

本文还有配套的精品资源，点击获取