简介南华大学数据库原理实验报告面向大数据技术等专业学生及数据库入门学习者围绕DBMS认识、建库建表、表间关系与SQL查询展开适合课程实验对照、期末复习和上机练习。压缩包内为1个doc文档约5.38MB按实验1至实验4及实验总结组织涵盖利用SQL Server Management Studio与SQL语句创建“学生选课”数据库、定义主码外码和默认值并进行单表查询、多表连接、高级查询与数据更新等训练。文中配有实验题目、要求、代码和结果展示可帮助读者梳理实体完整性与参照完整性、SELECT筛选排序分组、JOIN多表连接等知识点也能作为撰写实验报告和复盘上机操作的参考。目前已有377人学习。1. 先弄明白数据库原理实验到底在练什么很多人第一次翻开数据库原理实验报告看到的是几张建表截图和一串 SELECT第一反应是“这不就是背语法吗”。其实不是。这套实验从头到尾在练一件很朴素的事把现实世界里的人和事学生、课程、谁选了什么课考了多少分拆成几张规整的表存起来再按需要把它们拼回来。整份数据库原理实验报告走的就是这条路先用图形界面和 SQL 语句各建一遍库和表再练单表查询接着练多表连接最后用子查询和集合运算处理更绕的问题。对零基础的人来说它更像一张可以照着走一遍的地图而不是一份要背下来的作业。如果你正在搜“数据库原理入门”或者“SQL 零基础怎么学”下面这条路径可以直接当成你第一周的练手清单。2. 从零建第一个库认识 DBMS、建表、把数据塞进去2.1 数据库、表、DBMS三个词一次说清先别急着敲代码把三个词分清楚后面就顺了。数据库Database就是一个仓库专门用来存数据表Table是仓库里的货架每一排货架有自己的格子列和一层层抽屉行DBMS 则是这个仓库的管理系统负责帮你开仓、上架、取货、记账。实验里用到的 SQL Server 就是一套 DBMS而你打开的那个带树形菜单、能点鼠标建库建表的窗口是它附带的管理工具通常叫 SQL Server Management Studio简称 SSMS。这层关系想通了你就明白为什么实验要你先用鼠标建一遍再用 SQL 语句建一遍鼠标点的是仓库管理系统的遥控器SQL 语句是直接对仓库管理员下的命令。两种方式做的事完全一样但命令能写进文件、能被别人复现、能改动一个参数就重跑所以真正干活时大家更认 SQL。新手先用鼠标建是为了看清每一步到底发生了什么再用 SQL 建是为了把“看得见”变成“记得住”。2.2 先用图形界面建库建表把“手感”练出来打开 SSMS 连上本地实例右键“数据库”选择新建库名填成实验要求的那个比如 学生选课200905点确定左边树形菜单里就会多出一个节点。这一步看着没有技术含量但它能让你直观确认库确实被创建出来了位置在哪文件落在哪。接着在库里新建三张表字段照实验的定义来学生表放学号、姓名、性别、出生日期、院系名称、备注课程表放课程号、课程名、先行课、学分选修表放学号、课程号、分数。鼠标操作时你会被问到几个关键问题——这个字段用字符还是日期、多长、能不能为空、要不要设默认值。性别可以给个默认值‘男’学分可以给个默认值 4这样以后插入数据时漏写这两列系统会自动补上不至于空着。这一步最容易忽略的是主码。学生表的学号要设成主键课程表的课程号要设成主键选修表则要把学号和课程号两列一起设成主键。为什么选修表要两列一起因为单独一个学号会重复一个学生选多门课单独一个课程号也会重复一门课被多人选只有“谁选了什么”这一对组合才是唯一的。这个判断标准在整门课里会反复用到。2.3 换成 SQL 语句再建一遍主码、外码、默认值打开新建查询窗口把刚才鼠标点过的动作换成语句重做一遍。注意每一步执行完看一眼消息窗口有没有报错别一路 F5 到底。-- 建库并切换过去 CREATE DATABASE StuCou200905; GO USE StuCou200905; GO -- 学生表sno 作主码ssex 给默认值 CREATE TABLE Stu200905 ( sno CHAR(9) NOT NULL PRIMARY KEY, sname VARCHAR(20) NOT NULL, ssex CHAR(2) NOT NULL DEFAULT 男, sbirthday DATE, sdept VARCHAR(30), sremark VARCHAR(100) ); -- 课程表cpno 指向本表的 cno这就是自引用外码 CREATE TABLE Cou200905 ( cno CHAR(6) NOT NULL PRIMARY KEY, cname VARCHAR(30) NOT NULL, cpno CHAR(6), ccredit SMALLINT DEFAULT 4, CONSTRAINT fk_cou_cpno FOREIGN KEY (cpno) REFERENCES Cou200905(cno) ); -- 选修表两个列合起来当主码各自又是外码 CREATE TABLE SC200905 ( sno CHAR(9) NOT NULL, cno CHAR(6) NOT NULL, grade SMALLINT, CONSTRAINT pk_sc PRIMARY KEY (sno, cno), CONSTRAINT fk_sc_sno FOREIGN KEY (sno) REFERENCES Stu200905(sno), CONSTRAINT fk_sc_cno FOREIGN KEY (cno) REFERENCES Cou200905(cno) );几个参数值得单独说一下。CHAR(9) 表示定长 9 位学号这种长度固定的适合用它VARCHAR(20) 是变长姓名长短不一用它不浪费空间。NOT NULL 是“这一列不许空着”主码天然不能为空所以 PRIMARY KEY 已经隐含了这层意思。DEFAULT ‘男’ 就是刚才说的默认值。CONSTRAINT 后面跟的那串名字是给约束起的名字起名不是为了好看是为了以后出错时能一眼看出是哪条约束在拦你——这一点在第五章的排查里会再遇到。外码的作用是把三张表拴在一起。SC200905 里的 sno 只能填学生表里已经存在的学号cno 只能填课程表里已经存在的课程号你往选修表里塞一个不存在的学号系统会直接拒绝。这就是实验里反复强调的参照完整性说白了就是“不许出现查无此人的选课记录”。2.4 把数据插进去顺便认识插入、修改、删除表建好是空的得先喂点数据不然查询查出来全是零行你会误以为自己写错了。-- 一次插入多行列顺序要和 VALUES 里对得上 INSERT INTO Stu200905 (sno, sname, ssex, sbirthday, sdept) VALUES (0602001, 李勇, 男, 1999-03-12, 计算机系), (0602002, 王敏, 女, 2000-07-05, 计算机系), (0602003, 李娜, 女, 1998-11-23, 外语系); INSERT INTO Cou200905 (cno, cname, cpno, ccredit) VALUES (C01, 数据库原理, NULL, 4), (C02, 数据结构, C01, 4), (C03, 操作系统, C02, 3); INSERT INTO SC200905 (sno, cno, grade) VALUES (0602001, C01, 88), (0602001, C02, 55), (0602002, C01, 92), (0602003, C03, NULL);注意 C01 的先行课填的是 NULL表示这门课没有先行课C02 的先行课是 C01表示要先学数据库原理C03 的先行课是 C02。这个链条在第四章讲自连接时会用到。除了插入实验里还包含数据的更新与删除语句本身很短-- 改分数一定要带 WHERE否则整张表都被改 UPDATE SC200905 SET grade 60 WHERE sno 0602001 AND cno C02; -- 删记录同样必须带 WHERE DELETE FROM SC200905 WHERE sno 0602003 AND cno C03;这两条语句几乎是所有新手事故的高发区。不带 WHERE 的 UPDATE 会把整张表的分数改成同一个值不带 WHERE 的 DELETE 会清空整张表而且默认没有撤销按钮。我第一次练的时候就把一张表刷成了一样的分数只能重新插一遍数据。所以从今天起养成一个习惯写 UPDATE 和 DELETE 时先把 WHERE 条件写好再回头补前面的 SET 或 FROM。3. 单表查询入门把 SELECT 当成一把筛子用3.1 SELECT 的三件套要什么、从哪来、什么条件单表查询的所有题目拆开看都是三个空SELECT 后面填“我要哪些列”FROM 后面填“从哪张表拿”WHERE 后面填“满足什么条件才留下”。实验里的第一题“查询计算机系全体学生的信息”翻成这三件套就是要全部列从学生表拿条件是院系名称等于计算机系。-- * 表示所有列实际项目里建议把列名写全别偷懒 SELECT sno, sname, ssex, sbirthday, sdept, sremark FROM Stu200905 WHERE sdept 计算机系;WHERE 里能用的比较运算符有 、、、、、不等于逻辑运算符有 AND、OR、NOT。写多个条件时记得加括号因为 AND 的优先级高于 ORa OR b AND c和你以为的意思可能不一样。字符串和日期要用单引号包起来数字不用。新手常问“为什么我不写 WHERE 也能出结果”。能出但那是把整张表都拿出来几十行数据看着还行几百万行就是灾难。WHERE 的本质是在数据库里先过滤再返回不是把数据全拉到本地再筛这个差别在数据量大的时候非常明显。3.2 LIKE、BETWEEN、ORDER BY模糊、区间和排序精确匹配解决不了“姓李的学生”这种需求这时候用 LIKE配合两个通配符% 代表任意多个字符_ 代表任意一个字符。‘李%’ 就是以李开头的所有名字。-- 姓李的学生只要学号和姓名两列 SELECT sno, sname FROM Stu200905 WHERE sname LIKE 李%;区间查询用 BETWEEN ... AND ...注意它是闭区间两头都包含。查 1999 到 2000 年出生的学生写成 sbirthday BETWEEN ‘1999-01-01’ AND ‘2000-12-31’比写两个比较运算符再用 AND 连起来清爽得多。排序用 ORDER BY默认升序 ASC想降序就加 DESC。实验里有一题是“姓李的学生中按字典顺序取前两个”这时 ORDER BY 和 TOP 要一起用-- TOP 写在 SELECT 后面表示只取排序后的前 N 行 SELECT TOP 2 sno, sname FROM Stu200905 WHERE sname LIKE 李% ORDER BY sname ASC;这里有个顺序问题值得记牢数据库的执行顺序是先 FROM 找到表再 WHERE 过滤再 ORDER BY 排序最后才 SELECT 取列、TOP 截断。所以你写在 SELECT 里的别名在 WHERE 里通常用不了因为 WHERE 执行时别名还没生成。这个“写的顺序不等于执行的顺序”是很多人绕不过去的坎记住一次能省很多调试时间。3.3 聚合函数与 GROUP BY从一行行变成一组组前面查出来的都是一行行明细聚合函数是把多行压成一个数COUNT 数个数SUM 求和AVG 求平均MAX 和 MIN 取最大最小。实验里的“查询选修了两门以上课程的学生学号与课程数”用的就是 COUNT 配合分组。-- 按学号分组每组算一个选课门数 SELECT sno, COUNT(*) AS 选课门数 FROM SC200905 GROUP BY sno HAVING COUNT(*) 2;GROUP BY 的意思是“把学号相同的行归成一组每组只输出一行”。你可以把它想成把一张 Excel 表按学号列做透视同一个学号的多条选课记录被捆成一捆然后对这一捆做统计。分组之后SELECT 里只能出现两类东西——被分组的列sno和套在聚合函数里的列COUNT(*)。你要是写个 sname 出来数据库不知道该取这一组里哪一行的姓名就会报错。这条规则是单表查询里最容易翻车的地方第五章还会再提一次。3.4 HAVING分组之后的“WHERE”WHERE 和 HAVING 长得像作用的位置完全不同。WHERE 在分组之前过滤行HAVING 在分组之后过滤组。所以“选修课程数大于等于 2 的学生”这种带聚合结果的条件只能写在 HAVING 里写进 WHERE 会直接报错因为那时分组还没发生。-- 平均成绩和选课门数一起算按平均成绩降序 SELECT sno, AVG(grade) AS 平均成绩, COUNT(*) AS 选课门数 FROM SC200905 GROUP BY sno HAVING COUNT(*) 2 ORDER BY AVG(grade) DESC;顺带说一个容易忽略的细节AVG 会自动跳过 NULL 值不把它当 0 算。所以如果某个学生有选课记录但分数是空的他这门课不会拉低平均分但 COUNT() 仍然会把这行数进去。想要“有成绩的课程门数”得写 COUNT(grade) 而不是 COUNT()。这个差别在小数据上看不出来数据一多就是两套结果。4. 多表连接与子查询三张表怎么拼回一张大表4.1 内连接把三张表按主外码对接起来单表查询能回答的问题有限。“选修了数据库原理的计算机系学生是谁”这个问题系别在学生表课程名在课程表选课关系在选修表必须三张表一起看。连接的本质就是按主码和外码的对应关系把行拼起来。-- 三表内连接先按学号接上选课再按课程号接上课程 SELECT s.sno, s.sname FROM Stu200905 s JOIN SC200905 sc ON s.sno sc.sno JOIN Cou200905 c ON c.cno sc.cno WHERE s.sdept 计算机系 AND c.cname 数据库原理;s、sc、c 是给表起的别名作用是省字并且让列名不打架——三张表都有 sno 这一列不写前缀数据库就不知道你要哪个。ON 后面写的是连接条件也就是“这两张表靠哪两列对上”。JOIN 不写前缀时默认就是 INNER JOIN只保留两边都能对上的行。这一段值得多敲几遍因为它是整门课的核心动作。你把三张表想成三张名单学生名单、课程名单、选课记录。内连接就是拿选课记录当胶水把学生名单和课程名单粘在一起粘完你就能同时看到“谁”“选了什么”“考了多少”。实验里那道“查询学生的学号、姓名、选修课程的名称和成绩”就是同一个套路换几个列。4.2 外连接为什么有的学生一条也查不出来内连接有个隐藏的副作用对不上的行会被丢掉。假如有个学生一门课都没选他在选修表里没有任何记录内连接一接这个人就消失了。但实验里有一题要求“查询所有学生的信息和他们的选课课程号”一个都不能少这时候就得用左外连接。-- LEFT JOIN左表学生表的行全部保留右表没匹配上就填 NULL SELECT s.sno, s.sname, sc.cno FROM Stu200905 s LEFT JOIN SC200905 sc ON s.sno sc.sno;左外连接的意思是“以左边这张表为准左边的行一行都不能少右边要是没有对应的就用 NULL 补上”。对应的还有 RIGHT JOIN以右表为准和 FULL JOIN两边都保留。实验要求里专门写了要比较内连接和外连接的结果这个比较不是走过场——你把上面这条语句的 LEFT 去掉再跑一次看看少了哪几行那个差集就是内连接丢掉的人。判断该用哪种连接有个简单办法先问自己“哪张表的行必须全留下”。如果答案是学生表就用学生表在左边的 LEFT JOIN如果两边都必须全留就用 FULL JOIN。写完了数一数行数跟原表的行数对一对对不上就说明连接条件写漏了或者写重了。4.3 自连接一张表自己跟自己连找“先行课的先行课”实验里有一道题看着很唬人查每一门课的间接先行课也就是先行课的先行课。奇怪的地方在于课程的先行课信息就存在课程表自己的 cpno 列里没有第二张表可以连。解法是把同一张表当成两张表来用各起一个别名。-- 把 Cou200905 当成两张表a 是当前课b 是它的先行课 SELECT a.cno AS 课程号, b.cpno AS 间接先行课 FROM Cou200905 a JOIN Cou200905 b ON a.cpno b.cno WHERE b.cpno IS NOT NULL;理解这条语句的关键是把 a 和 b 看成两份独立的课程清单。a 负责回答“这门课的先行课是谁”b 负责回答“那个先行课的先行课又是谁”ON 把两者接上。WHERE 那句是在过滤只有当 b 自己也有先行课时才算找到了“间接”的先行课否则就只是直接先行课不符合题目要求。自连接是新手第一次感到抽象的地方我第一次写的时候盯着两个别名看了很久才反应过来。诀窍是别把它当“一张表”就当它真的是两张一模一样的表摆在你面前你只是在做普通的连接。4.4 嵌套子查询先算里层再拿结果喂给外层有些问题一步问不出来得拆成两步。“选修了数据库原理的学生”可以拆成先查出数据库原理的课程号再查选修了这个课程号的学号。这就是嵌套子查询括号里的那层先执行结果交给外层用。-- 里层查出课程号外层用 IN 判断学号在不在这个集合里 SELECT sno, sname FROM Stu200905 WHERE sno IN ( SELECT sno FROM SC200905 WHERE cno IN (SELECT cno FROM Cou200905 WHERE cname 数据库原理) );IN 的意思是“等于这个集合里的任意一个值”适合里层返回一列多行的情况。如果里层返回的是单个值比如某个学生的平均分就可以直接用比较运算符写成WHERE grade (SELECT AVG(grade) FROM SC200905)。EXISTS 是另一种写法它不关心子查询返回了什么值只关心“有没有查到行”。实验里的“查询被 0602001 选修、但没有被 0602002 选修的课程号”用 NOT EXISTS 写就是对每一门课检查 0602002 的选课记录里有没有它没有就留下。-- NOT EXISTS子查询查不到行条件才成立 SELECT cno FROM SC200905 x WHERE x.sno 0602001 AND NOT EXISTS ( SELECT 1 FROM SC200905 y WHERE y.sno 0602002 AND y.cno x.cno );这里的 x 和 y 又是别名x 指外层正在检查的那条记录y 指内层翻找的记录两者通过 x.cno y.cno 关联起来。这种写法叫相关子查询因为里层用到了外层的列。刚开始写容易漏掉那个关联条件漏了之后子查询就变成独立的一次查询结果往往全错。4.5 集合运算UNION、INTERSECT、EXCEPT 怎么选除了嵌套实验还要求用集合运算做同一批题。这三种运算符处理的是“两个查询结果之间”的关系前提是两边的列数一样、类型对得上。运算符含义对应实验题目UNION并集两边结果合起来自动去重被 0602001 或 0602002 选修的课程号INTERSECT交集两边都有的才留下被两人同时选修的课程号EXCEPT差集在左边但不在右边的留下0602001 选了、0602002 没选的课程号-- 并集OR 关系的两种写法 SELECT cno FROM SC200905 WHERE sno 0602001 UNION SELECT cno FROM SC200905 WHERE sno 0602002; -- 差集EXCEPT 版本 SELECT cno FROM SC200905 WHERE sno 0602001 EXCEPT SELECT cno FROM SC200905 WHERE sno 0602002;UNION 默认会去重如果明确知道两边没有重复、想省点开销可以写 UNION ALL。INTERSECT 和 EXCEPT 在 SQL Server 里都支持语法和 UNION 一致只是关键字不同。实验要求你用两种方法做同一道题目的就是让你体会IN 和 UNION 能干的活有重叠EXISTS 和 INTERSECT 能干的活也有重叠选哪个更多是看可读性。我的习惯是条件简单就用 IN条件里要引用外层的列就用 EXISTS。5. 常见问题排查新手最容易踩的 5 个坑5.1 报“对象名无效”表建到别的库里了现象语句明明和实验报告一字不差一执行就红字提示对象名无效说找不到这张表。原因建表时当前数据库不是你以为的那个。SSMS 左上角有个下拉框显示当前库你新建查询时它默认可能是 master 或者上一次用过的库CREATE TABLE 就建到那儿去了。解决每次打开查询窗口第一件事是在下拉框里选对库或者在脚本开头显式写上 USE StuCou200905; 再执行。养成这个动作能省掉一半“莫名其妙的报错”。5.2 中文变成问号字符类型选错了现象插入的中文姓名、课程名显示成一串问号或者查 ‘计算机系’ 查不出任何结果。原因用了 VARCHAR 存中文但排序规则不匹配或者用 CHAR 定了太短的长度把汉字截断了。一个汉字在非 Unicode 类型下通常占两个字节VARCHAR(20) 最多存十个汉字。解决存中文的列优先用 NVARCHAR长度按字符数算不用担心字节问题长度也别卡得太死姓名给 20、课程名给 30 都比较稳妥。改完类型记得把已有数据重新插一遍。5.3 外码加不上被引用的列不是主码现象建选修表时提示“与外码约束冲突”或者插入选课记录时被拒绝。原因有两个常见版本一是被引用的列比如学生表的 sno没有设成主码或唯一约束外码必须指向一个有唯一性的列二是你先插了选修表、后插学生表被引用的那一行还不存在。解决先确认被引用的列有主键或唯一约束再按“学生表 → 课程表 → 选修表”的顺序插入数据。删除时反过来先删选修记录再删学生。这个顺序不是规定是外码在替你把关。5.4 连接查出来少了一半人内连接把空值过滤掉了现象学生表明明有 3 个人三表连接查完只剩 2 个人怎么数都对不上。原因用了内连接而没选课的学生在选修表里没有对应行。内连接只保留两边都能对上的行对不上的直接被丢掉这不是 bug是内连接的定义。解决先数一数原表的行数再数连接后的行数对不上就往连接方式上想。需要保留全部学生的把 JOIN 换成 LEFT JOIN。另外连接条件写错也会导致行数暴涨比如漏了关联条件变成笛卡尔积行数变多同样要回头检查 ON 那几行。5.5 GROUP BY 报错SELECT 里放了没分组的列现象分组查询一跑就提示“列名无效因为该列没有包含在聚合函数或 GROUP BY 子句中”。原因你在 SELECT 里写了 sname 这类既没参与分组、也没套聚合函数的列。分组之后每组只剩一行数据库不知道这一行该取组里哪条记录的姓名。解决要么把这个列加进 GROUP BY要么给它套一个聚合函数比如 MAX(sname)要么就干脆不要这一列。真正需要按姓名分组时把 sname 一起写进 GROUP BY但要想清楚按学号分组和按姓名分组结果可能不一样因为可能重名。6. 下一步学什么把这套实验报告用成一条练习路线如果你已经把上面这些跑过一遍接下来最有效的做法不是去啃更厚的教材而是按“改一个条件、看一个结果”的方式把实验题重做一遍。具体路线可以这样排先把实验一的三张表默写一遍不看报告从建库到插数据一口气写完再把实验二的十道单表查询题不看答案自己写写完对一下重点看 WHERE 的写法差在哪然后攻实验二的多表连接每一题都先想清楚“我要哪几张表、靠哪一列接”再动笔最后做实验三的嵌套和集合运算同一道题强迫自己用两种写法各写一遍比较哪个更顺手。练习题目的选择上我建议按这个优先级走阶段练什么判断自己过关的标准第一周建库、建表、插入能不看资料写全三张表包括主码外码第二周单表查询十道题里有八道能一次写对第三周多表连接能说清内连接和外连接结果差在哪几行第四周子查询与集合运算同一题能用 IN 和 EXISTS 两种写法完成再往后可以补两个实验里没展开但绕不开的点一是索引给经常出现在 WHERE 和连接条件里的列建索引查询会快很多但索引不是越多越好写数据时它也要维护二是事务把几条必须一起成功的语句包在 BEGIN TRANSACTION 和 COMMIT 之间中间出错就 ROLLBACK 撤回。这两块不用一次学透知道有这回事、遇到问题时能想起来就够了。血泪经验是不要只看不敲。这份实验报告的代码量不大但每一题都有坑看着觉得会动手就报错。我第一次练连接查询时自信满满地跳过了 LEFT JOIN觉得内连接够用结果期末做项目时发现统计“所有学生的选课情况”永远少人回头翻实验报告才发现人家早就把对比写在那里了。从那以后我每次写连接都强制先问一句“哪张表的行必须全留”再去选 JOIN 类型这个习惯一直用到现在。如果你想找一份能照着敲的入门材料这份数据库原理实验报告就很合适它覆盖了从建表到复杂查询的完整链条每一题都有明确的目标不会让你漫无目的地敲。拿它当练习册一题一题跑通比读完三本语法书都有用。希望帮到你。本文还有配套的精品资源点击获取
