简介南华大学《数据库原理》实验报告面向大数据技术等专业学生及数据库初学者围绕 SQL Server 环境下的建库、建表与查询操作展开可用于课程实验对照、上机练习和期末复习。内容以“学生选课”数据库为主线涵盖认识 DBMS、用管理工具和 SQL 语句创建数据库及学生、课程、选修三张表定义主码、外码、默认值与表间关系并系统整理单表查询、多表连接查询、高级 SQL 查询和数据更新等实验涉及条件筛选、排序、分组、JOIN、间接先行课查询及计算机系选课统计等典型题目附有实验题目、要求、代码记录和实验总结便于理解实体完整性与参照完整性。资源包共 1 个 doc 文件大小约 5.38MB内容集中、目录清晰适合按实验模块查阅语句写法和操作流程。目前已有 377 人学习或浏览可作为数据库原理课程实验的参考材料。1. 数据库原理实验的知识地图从建库建表到嵌套查询的一条线数据库原理这门课有个反差课上讲关系代数、范式分解、事务调度实验里真正动手的却只有建库、建表、写 SELECT。《数据库原理实验报告》把四组实验串成一条线——先认识 DBMS 并用两种方式建出「学生选课」库再用单表查询把条件筛选练熟接着用多表连接把三张表拼起来最后用嵌套查询和组合查询收口中间还夹一次数据更新。这条线对应一条完整的能力梯度会定义结构、会取单表数据、会跨表取数、会用子查询表达「不存在」和「全部」这类量词。复习最怕把它当成四个互不相关的练习其实实验三的每道题都能在实验二的 JOIN 里找到影子。下面按这条线把重点、易错点和自测方式重新铺一遍。2. 建库建表三张表、四种约束与一条插入顺序实验一看着最简单实际上是整份报告里信息密度最高的一节。它要求用两种方式做同一件事先用管理工具点出来再用 SQL 语句写出来。两种方式的结果必须一致这个「一致」本身就是考点——图形界面点错了没人提醒你语句写错了编译器会当场报错。更重要的是三张表的字段类型、主码、默认值、可空性一旦定歪后面实验二、实验三的所有查询都会跟着歪属于典型的「地基问题」。2.1 同一个库的两种建法管理工具与 CREATE DATABASE管理工具那条路考的是对对象树的熟悉度数据库节点右键新建填库名确定然后在该库下建表、拉关系图。这条路的好处是直观能看见表和表之间那根连线坏处是不可复现换台机器就得重来一遍。SQL 那条路考的是语句结构建库、切库、建表三步走每一步都能贴进脚本里重复执行。-- 建库语句只负责创建不会自动切换上下文 CREATE DATABASE StuCou200905; GO -- USE 之后的所有语句才落在这个库里 USE StuCou200905; GOGO不是 T-SQL 语句是客户端工具的批处理分隔符作用是让前面的CREATE DATABASE先提交后面的USE才能找到这个库。新手常犯的错是把CREATE DATABASE和USE写在同一个批次里结果USE执行时报「库不存在」。库名后面带学号后缀报告里是200905是为了多人在同一台服务器上练习时不撞名这个习惯在做课程设计和多人共库时非常有用。注意管理工具建库时会按模板给一堆默认参数初始大小、增长比例、文件组课程实验不用管但要知道这些参数的存在不然换到真实项目里看到日志文件疯长会一头雾水。2.2 三张表的字段定义类型、主码、默认值与可空性报告的字段设计不算复杂但每个字段都在考一个决策点用什么类型、能不能为空、要不要默认值。把三张表摊开看判断依据其实很清楚。表字段建议类型约束与理由学生表学号CHAR(8)主码定长用整型会丢前导零学生表姓名VARCHAR(20)非空变长省空间学生表性别CHAR(2)默认值「男」非空学生表出生日期DATE允许为空只存日期不存时间学生表院系名称VARCHAR(30)允许为空查询时作为高频筛选列课程表课程号CHAR(4)主码课程表课程名VARCHAR(30)非空课程表先行课CHAR(4)外码引用本表课程号可为空课程表学分DECIMAL(3,1)默认值 2.0非空选修表学号 课程号CHAR(8) CHAR(4)复合主码选修表分数DECIMAL(5,1)可空加 CHECK 限 0 到 100表设计里最容易被忽略的是「学号为什么不用整型」。学号是编号不是数量它不参与加减乘除却经常要保留前导零、保持定长对齐所以CHAR(8)比INT更合适。学分用DECIMAL(3,1)而不是FLOAT是因为学分要参与求和求平均浮点数的舍入误差会让「总学分 15.000000001」这种结果出现在报表里。CREATE TABLE Stu200905 ( 学号 CHAR(8) NOT NULL PRIMARY KEY, -- 实体完整性主码非空且唯一 姓名 VARCHAR(20) NOT NULL, 性别 CHAR(2) NOT NULL DEFAULT 男, -- 默认值只在插入时省略该列才生效 出生日期 DATE NULL, 院系名称 VARCHAR(30) NULL, 备注 VARCHAR(100) NULL );DEFAULT的触发条件值得单独强调它只在INSERT语句里没写这一列、或者写了DEFAULT关键字时才生效。如果插入时显式写了NULL默认值不会兜底该列就是NULL——除非这一列同时被声明为NOT NULL那才会直接报错。2.3 外码与表间关系参照完整性到底卡住了什么三张表的关系是一张标准的多对多学生和课程本身没有直接联系靠选修表搭桥。选修表里的学号指向学生表主码课程号指向课程表主码两个外码加上复合主码一起构成了这张联系表的全部骨架。外码约束做的事情只有一件不许引用不存在的实体。往选修表插一条(0602099,C01,90)如果学生表里没有0602099语句直接被拒。这条规则在查询阶段看不出威力在数据更新阶段就是硬约束——它决定了插入顺序和删除顺序。课程表里还有一个自引用外码先行课引用本表的课程号。这个设计表达的是「课程之间的先修关系」是后面自连接查询的基础。自引用外码允许为空因为总有一门课没有先行课这个「为空」正是实验二第 3 小题的筛选条件。CREATE TABLE Cou200905 ( 课程号 CHAR(4) NOT NULL PRIMARY KEY, 课程名 VARCHAR(30) NOT NULL, 先行课 CHAR(4) NULL, 学分 DECIMAL(3,1) NOT NULL DEFAULT 2.0, CONSTRAINT FK_Cou_Pre FOREIGN KEY (先行课) REFERENCES Cou200905(课程号) ); CREATE TABLE SC200905 ( 学号 CHAR(8) NOT NULL, 课程号 CHAR(4) NOT NULL, 分数 DECIMAL(5,1) NULL CHECK (分数 BETWEEN 0 AND 100), CONSTRAINT PK_SC PRIMARY KEY (学号, 课程号), -- 一个学生对一门课只能选一次 CONSTRAINT FK_SC_Stu FOREIGN KEY (学号) REFERENCES Stu200905(学号), CONSTRAINT FK_SC_Cou FOREIGN KEY (课程号) REFERENCES Cou200905(课程号) );给约束起名字FK_SC_Stu这类看起来是多余动作实际是排错时的救命绳报错信息里出现的是约束名而不是表名有名字才知道是哪条关系断了。不写名字的话系统会给一串随机串翻半天才能定位。注意外码默认行为是「拒绝删除被引用的行」也就是NO ACTION。实验里没要求级联所以删学生之前必须先删他的选课记录顺序反了会直接失败。2.4 灌数据与更新插入的先后与删除的方向数据更新这一步顺序比语句本身重要。父表先插、子表后插自引用的行要先插被引用的那门课。删除则完全反过来先删子表记录再删父表记录。-- 先插学生和课程父表 INSERT INTO Stu200905(学号, 姓名, 性别, 出生日期, 院系名称) VALUES (0602001, 李一, DEFAULT, 1981-03-04, 计算机系); -- 性别走默认值 -- 先行课为空的课先插后面才能引用它 INSERT INTO Cou200905(课程号, 课程名, 先行课, 学分) VALUES (C01, 数据库原理, NULL, 4.0); INSERT INTO Cou200905(课程号, 课程名, 先行课, 学分) VALUES (C02, 数据结构, C01, 3.5); -- 最后插联系表两个外码都要能对上 INSERT INTO SC200905(学号, 课程号, 分数) VALUES (0602001, C01, 88);INSERT省略列名时VALUES必须严格按建表顺序给全所有列一旦表结构改过加了一列这种写法立刻失效。养成写全列名的习惯代价是多敲几个字收益是改表结构时不用回来改一堆语句。UPDATE和DELETE的风险点则在于WHERE漏写WHERE就是全表更新或全表清空这种事在实验里顶多重灌数据在工作环境里就是事故。3. 单表查询十道题背后的四类筛选逻辑实验二的十道单表题看着花样多拆开其实只有四类动作按条件筛行、拼计算列、排序取前几行、分组做聚合。这四类动作各自对应一组语法混在一起就容易写成一锅粥。复习时按类别归位比按题号顺序背要牢得多。3.1 十道题归成四类一张对照表看清考点类别对应题号核心语法常见失分点条件筛选1、2、3、4、5WHERE 比较 / LIKE / IS NULL / IN用 NULL判空计算列6、7日期函数、表达式别名年龄算错一年、区间端点漏排序截断8ORDER BY TOPTOP 没配 ORDER BY分组聚合9、10GROUP BY HAVING 聚合函数非聚合列没进 GROUP BY这张表的价值在于拿到新题先判断属于哪一类再调用对应的语法骨架比从零想快得多。第 6 题「查姓名及年龄」表面是查询实质是计算列第 10 题看着像筛选实质是「先分组再筛组」用WHERE写不出来。3.2 WHERE 的四种写法与 NULL 的特殊性条件筛选的写法可以归为四种比较运算、逻辑组合、范围与集合、模式匹配。前两种是基础后两种是效率工具。-- 1) 等值比较字符串常量用单引号中文列名不加引号 SELECT 学号, 姓名, 性别, 出生日期, 院系名称, 备注 FROM Stu200905 WHERE 院系名称 计算机系; -- 2) 模式匹配% 代表任意长度_ 代表单个字符 SELECT 学号, 姓名 FROM Stu200905 WHERE 姓名 LIKE 李%; -- 3) 集合成员IN 等价于一串 OR但可读性高得多 SELECT 学号, 分数 FROM SC200905 WHERE 课程号 IN (C01, C02); -- 4) 空值判断必须用 IS NULL不能用 NULL SELECT 课程名 FROM Cou200905 WHERE 先行课 IS NULL; NULL这个写法不会报错只会永远返回空结果——因为NULL表示「未知」任何值和未知比较的结果还是未知WHERE只保留结果为真的行。同理分数 60会漏掉所有分数为NULL的行这在「查不及格学生」那题里会造成误判缺考的学生不是不及格是没成绩业务含义完全不同。3.3 计算列与日期年龄到底怎么算才不翻车第 6、7 两题都卡在日期上。年龄不是数据库里的字段是算出来的算法有两种常见写法精度差一岁。-- 写法一只比年份生日没到的人会被多算一岁 SELECT 姓名, YEAR(GETDATE()) - YEAR(出生日期) AS 年龄 FROM Stu200905 WHERE 院系名称 计算机系; -- 写法二用 DATEDIFF 按整年差算跨过生日才加一岁更贴近真实年龄 SELECT 姓名, DATEDIFF(YEAR, 出生日期, GETDATE()) AS 年龄 FROM Stu200905 WHERE 院系名称 计算机系;YEAR()相减是「年份差」DATEDIFF(YEAR, ...)是「跨过的年份边界数」两者在生日未满时会差 1。课程实验对精度要求不严但写论文或做报表时差一岁会被追问。第 7 题的区间筛选也有个坑-- 正确用日期字面量端点写全年月日 SELECT 姓名 FROM Stu200905 WHERE 院系名称 计算机系 AND 出生日期 BETWEEN 1999-01-01 AND 2000-12-31;如果写成BETWEEN 1999 AND 2000字符串会被隐式转换2000转成的日期是 2000 年 1 月 1 日零点2000 年出生的学生全被排除这个错很隐蔽结果集少几行根本看不出来。3.4 聚合与分组COUNT 的三种形态与 HAVING 的位置第 9、10 两题是分组聚合的主场。这一块的语法结构固定WHERE筛行GROUP BY分组HAVING筛组ORDER BY排序四个子句的执行顺序和书写顺序不一致理解顺序才能理解为什么WHERE里不能写聚合函数。-- 选修两门以上先按学号分组再筛掉门数不够的组 SELECT 学号, COUNT(*) AS 课程数 FROM SC200905 GROUP BY 学号 HAVING COUNT(*) 2; -- 门数 2 的学号、平均分、门数按平均分降序 SELECT 学号, AVG(分数) AS 平均成绩, COUNT(*) AS 选课门数 FROM SC200905 GROUP BY 学号 HAVING COUNT(*) 2 ORDER BY AVG(分数) DESC;COUNT有三种形态结果完全不同COUNT(*)数行数包含全NULL的行COUNT(列名)只数该列非空的行COUNT(DISTINCT 列名)数去重后的非空值。「统计选修某课的人数」用COUNT(*)是对的因为选修表主码是学号课程号一个学生对一门课只会有一条记录换成COUNT(分数)就会把缺考的人漏掉。ORDER BY后面既能写列名也能写聚合函数还能写SELECT里定义的别名。SQL Server 允许别名排序但标准写法是重复聚合表达式移植到别的库时不会翻车。4. 多表连接与嵌套组合从拼表到表达「不存在」实验二下半段和实验三难度跳了一级。前面所有查询只涉及一张表从多表连接开始问题变成「数据分散在几张表里怎么拼回来」到了嵌套查询问题又变成「怎么表达『没有』『全部』这类否定和全称的含义」。这两个台阶是数据库实验里最容易掉队的地方。4.1 内连接三张表串起来的最小骨架连接的本质是把两张表按某个条件配对配对成功的行留下。三张表连接的写法就是两个JOIN串起来条件写在各自的ON后面。-- 查选修了《数据库原理》的计算机系学生 SELECT s.学号, s.姓名 FROM Stu200905 AS s JOIN SC200905 AS sc ON s.学号 sc.学号 JOIN Cou200905 AS c ON sc.课程号 c.课程号 WHERE s.院系名称 计算机系 AND c.课程名 数据库原理;这段代码的信息量在于「筛选条件放在哪」。院系名称在学生表课程名在课程表两个条件都写在WHERE里连接条件写在ON里。内连接时两者等价外连接时完全不同——ON里的条件只决定怎么配对WHERE里的条件决定配对完之后留哪些行。表别名s、sc、c在这里不是可选项而是必需品三张表都有「学号」或「课程号」不写别名编译器会报「列名不明确」。写别名的另一个好处是省字长表名在复杂查询里能把一行撑到看不清。4.2 外连接哪张表的数据不能丢内连接只保留配对成功的行这意味着一件事没选课的学生会消失没人选的课也会消失。实验二里有两道题专门考这个-- 所有学生的信息 他所选的课程号没选课的也要出现 SELECT s.学号, s.姓名, s.性别, s.出生日期, s.院系名称, s.备注, sc.课程号 FROM Stu200905 AS s LEFT JOIN SC200905 AS sc ON s.学号 sc.学号; -- 所有课程名 被选修情况没人选的课也要出现 SELECT c.课程名, sc.学号, sc.课程号, sc.分数 FROM Cou200905 AS c LEFT JOIN SC200905 AS sc ON c.课程号 sc.课程号;判断用哪种连接问一句「哪张表的数据一行都不能少」。学生表一行不能少就用学生表在左边的LEFT JOIN课程表一行不能少就把课程表放左边。放反了结果集会少行而且不报错。外连接里没配上的那部分右表的列全是NULL。这个NULL是「没有匹配」的意思不是「数据缺失」两者在结果里长得一样但含义不同。要筛出「没选课的学生」就在WHERE里加sc.学号 IS NULL这是外连接的经典用法。注意RIGHT JOIN和LEFT JOIN能互相改写把两张表换个位置就行。实验报告里统一用左连接是为了读代码时「主表在左」这个直觉保持一致。4.3 自连接间接先行课与笛卡尔积的边界第 2 题「查每门课的间接先行课」是全场唯一必须自连接的题。一门课的先行课的先行课需要把课程表当成两张表来看一张扮演「课程」一张扮演「先行课」。-- a 是当前课程b 是 a 的先行课a 的间接先行课 b 的先行课 SELECT a.课程号 AS 课程编号, b.先行课 AS 间接先行课 FROM Cou200905 AS a JOIN Cou200905 AS b ON a.先行课 b.课程号 WHERE b.先行课 IS NOT NULL;拆解这段a.先行课 b.课程号让 b 表里那一行正好是 a 的直接先行课接着取b.先行课就往上跳了一层拿到间接先行课。最后的WHERE b.先行课 IS NOT NULL过滤掉「先行课本身没有先行课」的情况也就是报告里那句「有间接先行课才算」。少了这个条件结果里会多出一批NULL行。自连接不写ON条件就退化成笛卡尔积课程表有 m 行自己乘自己就是 m² 行再被WHERE筛一遍性能差且容易出重复。第 7 题「列出学生所有可能的选修情况」反过来故意要的就是笛卡尔积-- 学生 x 课程 的全组合用 CROSS JOIN 明确表达意图 SELECT s.学号, s.姓名, c.课程号, c.课程名 FROM Stu200905 AS s CROSS JOIN Cou200905 AS c;用CROSS JOIN而不是省略条件的逗号写法是为了让读代码的人一眼看出来「这里就是要全组合」而不是「忘写连接条件了」。4.4 子查询相关与非相关IN 与 EXISTS 的取舍嵌套查询分两种。非相关子查询可以独立跑出结果外层再拿这个结果去比相关子查询引用外层的列外层每换一行它就要重算一次。-- 非相关子查询先算出课程号外层只做一次 IN 判断 SELECT COUNT(*) AS 人数 FROM SC200905 WHERE 课程号 IN (SELECT 课程号 FROM Cou200905 WHERE 课程名 数据库原理); -- 相关外层每行都要回到子查询里确认一次 SELECT DISTINCT 课程号 FROM SC200905 AS a WHERE 学号 0602001 AND EXISTS (SELECT 1 FROM SC200905 AS b WHERE b.学号 0602002 AND b.课程号 a.课程号);EXISTS只关心子查询有没有行不关心查出什么所以SELECT 1就够了写SELECT *也不会更慢只是显得没想清楚。IN和EXISTS多数情况下可以互换选哪个看语义顺不顺判断「某集合里有没有」用IN判断「是否存在满足条件的记录」用EXISTS。「比计算机系学生年龄都小」这题用的是全称量词两种写法-- 写法一比最大的出生日期还晚就是比所有人都小 SELECT * FROM Stu200905 WHERE 院系名称 计算机系 AND 出生日期 (SELECT MAX(出生日期) FROM Stu200905 WHERE 院系名称 计算机系); -- 写法二逐个比用 ALL 表达「比每一个都小」 SELECT * FROM Stu200905 WHERE 院系名称 计算机系 AND 出生日期 ALL (SELECT 出生日期 FROM Stu200905 WHERE 院系名称 计算机系);「年龄小」等价于「出生日期晚」这个转换是解题关键转错了方向整道题就反了。ALL的语义是「和子查询结果里的每一个值比较都为真」子查询返回空集时ALL恒为真这是它和MAX写法的细微差别平时不影响边界数据下会出问题。4.5 集合运算UNION、INTERSECT、EXCEPT 与它们的子查询等价写法实验三最后三道题考的是同一件事的两种表达用集合运算或者用嵌套子查询。两条路都要会因为不同数据库对集合运算的支持程度不一样。-- 或选修了 C01 或 C02 的课程号UNION 自动去重 SELECT 课程号 FROM SC200905 WHERE 学号 0602001 UNION SELECT 课程号 FROM SC200905 WHERE 学号 0602002; -- 与两人同时选修的课程号 SELECT 课程号 FROM SC200905 WHERE 学号 0602001 INTERSECT SELECT 课程号 FROM SC200905 WHERE 学号 0602002; -- 差0602001 选了但 0602002 没选的课程号 SELECT 课程号 FROM SC200905 WHERE 学号 0602001 EXCEPT SELECT 课程号 FROM SC200905 WHERE 学号 0602002;三个运算符的优先级和结合性在标准里没定义混用时必须加括号不然同一个表达式在不同数据库里结果可能不同。UNION默认去重要保留重复得写UNION ALLINTERSECT和EXCEPT也默认去重这点在「查两人共同选修的课程」时正好省事因为选修表本身就是一人一课一条记录。三道题各自的子查询写法核心是把集合运算翻译成存在性判断-- 交集用 EXISTS0602001 选的课里存在 0602002 也选了的 SELECT DISTINCT 课程号 FROM SC200905 AS a WHERE 学号 0602001 AND EXISTS (SELECT 1 FROM SC200905 AS b WHERE b.学号 0602002 AND b.课程号 a.课程号); -- 差集用 NOT EXISTS0602001 选的课里不存在 0602002 也选的 SELECT DISTINCT 课程号 FROM SC200905 AS a WHERE 学号 0602001 AND NOT EXISTS (SELECT 1 FROM SC200905 AS b WHERE b.学号 0602002 AND b.课程号 a.课程号);「或」那题的子查询写法是WHERE 学号 IN (0602001,0602002)或者用OR连两个条件。三种写法结果相同差别在可读性和执行计划数据量小时随便写数据量大时IN列表和EXISTS往往比多个OR更容易走索引。5. 易错点排查五个让结果悄悄变空的地方实验做完能跑出结果不等于写对了。下面这五处全都是「不报错但结果是错的」属于最需要警惕的一类问题——编译器不帮你兜底只能靠自查。5.1 NOT IN 碰上 NULL一行脏数据让整个结果变空易漏点用NOT IN写「没有选修某课的学生」返回零行。为什么漏NOT IN展开后是一串比较只要子查询结果里出现一个NULLx NULL就是未知整个AND链条变成未知WHERE一行都不留。选修表里只要有一个学生的课程号是NULL或者子查询本身可能返回NULL结果就全灭。怎么补优先写NOT EXISTS它的语义是「不存在这样的行」天然不受NULL影响非要用NOT IN就在子查询里加WHERE 列名 IS NOT NULL兜底。-- 稳的写法NOT EXISTS SELECT s.* FROM Stu200905 AS s WHERE NOT EXISTS ( SELECT 1 FROM SC200905 AS sc JOIN Cou200905 AS c ON sc.课程号 c.课程号 WHERE sc.学号 s.学号 AND c.课程名 数据库原理 );5.2 该用外连接却写了内连接少掉的行没人提醒易漏点「查所有学生的选课情况」「查所有课程被选情况」两题用JOIN写结果里没选课的学生、没人选的课凭空消失。为什么漏内连接的语义就是「只留配对成功的」没配对的行被静默丢弃不报错、不警告。加上这两题的样例数据里恰好大多数人都有选课记录少掉一两行不容易被发现。怎么补拿到题先问「哪张表一行都不能少」把它放左边写LEFT JOIN写完再拿学生表总行数和结果行数对一遍数字对不上就是连接方式选错了。5.3 GROUP BY 漏列与 HAVING 写错位置易漏点SELECT列表里放了没参与分组的列或者把聚合条件写进WHERE。为什么漏SQL Server 对前者会直接报「列无效因为未包含在聚合函数或 GROUP BY 子句中」但很多人看到报错就随手把列删了而不是想清楚「这一列到底该不该分组」后者会报「聚合函数不能出现在 WHERE 子句中」改的时候又容易把整段条件搬错地方。怎么补WHERE筛行、HAVING筛组这个分工记死。写分组查询时先列出「按什么分组」再把SELECT里的每个非聚合列逐个对照确认它要么在GROUP BY里要么被聚合函数包住。-- 正确学号和姓名都进 GROUP BYSQL Server 不认主码函数依赖 SELECT s.学号, s.姓名, AVG(sc.分数) AS 平均成绩, COUNT(*) AS 门数 FROM Stu200905 AS s JOIN SC200905 AS sc ON s.学号 sc.学号 WHERE s.院系名称 计算机系 GROUP BY s.学号, s.姓名 HAVING COUNT(*) 2 ORDER BY 平均成绩 DESC;5.4 年龄与日期区间差一岁、差一天都看不出来易漏点年龄用两个年份相减区间用BETWEEN 1999 AND 2000。为什么漏两种写法都能跑结果只差一行两行肉眼对不出来而且课程实验的数据量小错了也很难被老师发现。怎么补年龄统一用DATEDIFF(YEAR, 出生日期, GETDATE())日期区间两端写全年月日BETWEEN 1999-01-01 AND 2000-12-31。写完顺手拿一个生日在 12 月 31 日的学生验算一遍边界一过就露馅。5.5 集合运算的去重与排序位置易漏点以为UNION保留重复或者把ORDER BY写在某个子查询里。为什么漏UNION的默认行为是去重这在「查两人共同选修」时恰好帮了忙但在「查两人各自选了哪些课、重复也要显示」时就把数据吃掉了ORDER BY只能出现在整个集合运算的最后写在中间会直接语法错误。怎么补凡是需要保留重复就写UNION ALLORDER BY一律放到最后一个SELECT之后作用于整个结果集。三个以上集合运算混用时加括号明确结合顺序。6. 查漏补缺一张自测清单和一套三问法复习到这一步剩下的不是再看一遍语法而是把「看到题能不能立刻判断该用什么」练成条件反射。我用一套三问法过完了整份报告比按题号顺序重做一遍省一半时间。第一问涉及几张表一张表就在WHERE和聚合里做文章两张以上先画连接骨架确定主表是谁、谁的数据不能丢再决定JOIN还是LEFT JOIN。第二问结果是一行还是一个组出现「每」「各」「平均」「人数」「门数」这类词就是要分组GROUP BY 聚合函数 HAVING三件套直接上。第三问题目里有没有「没有」「所有」「同时」「或者」「没有」优先想NOT EXISTS「所有」优先想ALL或MAX/MIN子查询「同时」想INTERSECT或EXISTS「或者」想UNION或IN。配合下面这张自测表逐项打勾勾不上的回去补对应章节。自测项判断标准对应模块能默写三张表的建表语句主码、外码、默认值、可空性一个不漏第 2 章能说清 NULL为什么错能讲出三值逻辑第 3 章能区分COUNT(*)与COUNT(列)能举出缺考的例子第 3 章能判断该用内连接还是左连接说得清哪张表不能丢行第 4 章能手写间接先行课的自连接知道为什么要加IS NOT NULL第 4 章能用两种写法做交、差、并集合运算与EXISTS各一遍第 4 章能解释NOT IN的 NULL 陷阱说得出为什么结果是空集第 5 章还有一个小技巧把每道题的本文还有配套的精品资源点击获取
简介南华大学《数据库原理》实验报告面向大数据技术等专业学生及数据库初学者围绕 SQL Server 环境下的建库、建表与查询操作展开可用于课程实验对照、上机练习和期末复习。内容以“学生选课”数据库为主线涵盖认识 DBMS、用管理工具和 SQL 语句创建数据库及学生、课程、选修三张表定义主码、外码、默认值与表间关系并系统整理单表查询、多表连接查询、高级 SQL 查询和数据更新等实验涉及条件筛选、排序、分组、JOIN、间接先行课查询及计算机系选课统计等典型题目附有实验题目、要求、代码记录和实验总结便于理解实体完整性与参照完整性。资源包共 1 个 doc 文件大小约 5.38MB内容集中、目录清晰适合按实验模块查阅语句写法和操作流程。目前已有 377 人学习或浏览可作为数据库原理课程实验的参考材料。1. 数据库原理实验的知识地图从建库建表到嵌套查询的一条线数据库原理这门课有个反差课上讲关系代数、范式分解、事务调度实验里真正动手的却只有建库、建表、写 SELECT。《数据库原理实验报告》把四组实验串成一条线——先认识 DBMS 并用两种方式建出「学生选课」库再用单表查询把条件筛选练熟接着用多表连接把三张表拼起来最后用嵌套查询和组合查询收口中间还夹一次数据更新。这条线对应一条完整的能力梯度会定义结构、会取单表数据、会跨表取数、会用子查询表达「不存在」和「全部」这类量词。复习最怕把它当成四个互不相关的练习其实实验三的每道题都能在实验二的 JOIN 里找到影子。下面按这条线把重点、易错点和自测方式重新铺一遍。2. 建库建表三张表、四种约束与一条插入顺序实验一看着最简单实际上是整份报告里信息密度最高的一节。它要求用两种方式做同一件事先用管理工具点出来再用 SQL 语句写出来。两种方式的结果必须一致这个「一致」本身就是考点——图形界面点错了没人提醒你语句写错了编译器会当场报错。更重要的是三张表的字段类型、主码、默认值、可空性一旦定歪后面实验二、实验三的所有查询都会跟着歪属于典型的「地基问题」。2.1 同一个库的两种建法管理工具与 CREATE DATABASE管理工具那条路考的是对对象树的熟悉度数据库节点右键新建填库名确定然后在该库下建表、拉关系图。这条路的好处是直观能看见表和表之间那根连线坏处是不可复现换台机器就得重来一遍。SQL 那条路考的是语句结构建库、切库、建表三步走每一步都能贴进脚本里重复执行。-- 建库语句只负责创建不会自动切换上下文 CREATE DATABASE StuCou200905; GO -- USE 之后的所有语句才落在这个库里 USE StuCou200905; GOGO不是 T-SQL 语句是客户端工具的批处理分隔符作用是让前面的CREATE DATABASE先提交后面的USE才能找到这个库。新手常犯的错是把CREATE DATABASE和USE写在同一个批次里结果USE执行时报「库不存在」。库名后面带学号后缀报告里是200905是为了多人在同一台服务器上练习时不撞名这个习惯在做课程设计和多人共库时非常有用。注意管理工具建库时会按模板给一堆默认参数初始大小、增长比例、文件组课程实验不用管但要知道这些参数的存在不然换到真实项目里看到日志文件疯长会一头雾水。2.2 三张表的字段定义类型、主码、默认值与可空性报告的字段设计不算复杂但每个字段都在考一个决策点用什么类型、能不能为空、要不要默认值。把三张表摊开看判断依据其实很清楚。表字段建议类型约束与理由学生表学号CHAR(8)主码定长用整型会丢前导零学生表姓名VARCHAR(20)非空变长省空间学生表性别CHAR(2)默认值「男」非空学生表出生日期DATE允许为空只存日期不存时间学生表院系名称VARCHAR(30)允许为空查询时作为高频筛选列课程表课程号CHAR(4)主码课程表课程名VARCHAR(30)非空课程表先行课CHAR(4)外码引用本表课程号可为空课程表学分DECIMAL(3,1)默认值 2.0非空选修表学号 课程号CHAR(8) CHAR(4)复合主码选修表分数DECIMAL(5,1)可空加 CHECK 限 0 到 100表设计里最容易被忽略的是「学号为什么不用整型」。学号是编号不是数量它不参与加减乘除却经常要保留前导零、保持定长对齐所以CHAR(8)比INT更合适。学分用DECIMAL(3,1)而不是FLOAT是因为学分要参与求和求平均浮点数的舍入误差会让「总学分 15.000000001」这种结果出现在报表里。CREATE TABLE Stu200905 ( 学号 CHAR(8) NOT NULL PRIMARY KEY, -- 实体完整性主码非空且唯一 姓名 VARCHAR(20) NOT NULL, 性别 CHAR(2) NOT NULL DEFAULT 男, -- 默认值只在插入时省略该列才生效 出生日期 DATE NULL, 院系名称 VARCHAR(30) NULL, 备注 VARCHAR(100) NULL );DEFAULT的触发条件值得单独强调它只在INSERT语句里没写这一列、或者写了DEFAULT关键字时才生效。如果插入时显式写了NULL默认值不会兜底该列就是NULL——除非这一列同时被声明为NOT NULL那才会直接报错。2.3 外码与表间关系参照完整性到底卡住了什么三张表的关系是一张标准的多对多学生和课程本身没有直接联系靠选修表搭桥。选修表里的学号指向学生表主码课程号指向课程表主码两个外码加上复合主码一起构成了这张联系表的全部骨架。外码约束做的事情只有一件不许引用不存在的实体。往选修表插一条(0602099,C01,90)如果学生表里没有0602099语句直接被拒。这条规则在查询阶段看不出威力在数据更新阶段就是硬约束——它决定了插入顺序和删除顺序。课程表里还有一个自引用外码先行课引用本表的课程号。这个设计表达的是「课程之间的先修关系」是后面自连接查询的基础。自引用外码允许为空因为总有一门课没有先行课这个「为空」正是实验二第 3 小题的筛选条件。CREATE TABLE Cou200905 ( 课程号 CHAR(4) NOT NULL PRIMARY KEY, 课程名 VARCHAR(30) NOT NULL, 先行课 CHAR(4) NULL, 学分 DECIMAL(3,1) NOT NULL DEFAULT 2.0, CONSTRAINT FK_Cou_Pre FOREIGN KEY (先行课) REFERENCES Cou200905(课程号) ); CREATE TABLE SC200905 ( 学号 CHAR(8) NOT NULL, 课程号 CHAR(4) NOT NULL, 分数 DECIMAL(5,1) NULL CHECK (分数 BETWEEN 0 AND 100), CONSTRAINT PK_SC PRIMARY KEY (学号, 课程号), -- 一个学生对一门课只能选一次 CONSTRAINT FK_SC_Stu FOREIGN KEY (学号) REFERENCES Stu200905(学号), CONSTRAINT FK_SC_Cou FOREIGN KEY (课程号) REFERENCES Cou200905(课程号) );给约束起名字FK_SC_Stu这类看起来是多余动作实际是排错时的救命绳报错信息里出现的是约束名而不是表名有名字才知道是哪条关系断了。不写名字的话系统会给一串随机串翻半天才能定位。注意外码默认行为是「拒绝删除被引用的行」也就是NO ACTION。实验里没要求级联所以删学生之前必须先删他的选课记录顺序反了会直接失败。2.4 灌数据与更新插入的先后与删除的方向数据更新这一步顺序比语句本身重要。父表先插、子表后插自引用的行要先插被引用的那门课。删除则完全反过来先删子表记录再删父表记录。-- 先插学生和课程父表 INSERT INTO Stu200905(学号, 姓名, 性别, 出生日期, 院系名称) VALUES (0602001, 李一, DEFAULT, 1981-03-04, 计算机系); -- 性别走默认值 -- 先行课为空的课先插后面才能引用它 INSERT INTO Cou200905(课程号, 课程名, 先行课, 学分) VALUES (C01, 数据库原理, NULL, 4.0); INSERT INTO Cou200905(课程号, 课程名, 先行课, 学分) VALUES (C02, 数据结构, C01, 3.5); -- 最后插联系表两个外码都要能对上 INSERT INTO SC200905(学号, 课程号, 分数) VALUES (0602001, C01, 88);INSERT省略列名时VALUES必须严格按建表顺序给全所有列一旦表结构改过加了一列这种写法立刻失效。养成写全列名的习惯代价是多敲几个字收益是改表结构时不用回来改一堆语句。UPDATE和DELETE的风险点则在于WHERE漏写WHERE就是全表更新或全表清空这种事在实验里顶多重灌数据在工作环境里就是事故。3. 单表查询十道题背后的四类筛选逻辑实验二的十道单表题看着花样多拆开其实只有四类动作按条件筛行、拼计算列、排序取前几行、分组做聚合。这四类动作各自对应一组语法混在一起就容易写成一锅粥。复习时按类别归位比按题号顺序背要牢得多。3.1 十道题归成四类一张对照表看清考点类别对应题号核心语法常见失分点条件筛选1、2、3、4、5WHERE 比较 / LIKE / IS NULL / IN用 NULL判空计算列6、7日期函数、表达式别名年龄算错一年、区间端点漏排序截断8ORDER BY TOPTOP 没配 ORDER BY分组聚合9、10GROUP BY HAVING 聚合函数非聚合列没进 GROUP BY这张表的价值在于拿到新题先判断属于哪一类再调用对应的语法骨架比从零想快得多。第 6 题「查姓名及年龄」表面是查询实质是计算列第 10 题看着像筛选实质是「先分组再筛组」用WHERE写不出来。3.2 WHERE 的四种写法与 NULL 的特殊性条件筛选的写法可以归为四种比较运算、逻辑组合、范围与集合、模式匹配。前两种是基础后两种是效率工具。-- 1) 等值比较字符串常量用单引号中文列名不加引号 SELECT 学号, 姓名, 性别, 出生日期, 院系名称, 备注 FROM Stu200905 WHERE 院系名称 计算机系; -- 2) 模式匹配% 代表任意长度_ 代表单个字符 SELECT 学号, 姓名 FROM Stu200905 WHERE 姓名 LIKE 李%; -- 3) 集合成员IN 等价于一串 OR但可读性高得多 SELECT 学号, 分数 FROM SC200905 WHERE 课程号 IN (C01, C02); -- 4) 空值判断必须用 IS NULL不能用 NULL SELECT 课程名 FROM Cou200905 WHERE 先行课 IS NULL; NULL这个写法不会报错只会永远返回空结果——因为NULL表示「未知」任何值和未知比较的结果还是未知WHERE只保留结果为真的行。同理分数 60会漏掉所有分数为NULL的行这在「查不及格学生」那题里会造成误判缺考的学生不是不及格是没成绩业务含义完全不同。3.3 计算列与日期年龄到底怎么算才不翻车第 6、7 两题都卡在日期上。年龄不是数据库里的字段是算出来的算法有两种常见写法精度差一岁。-- 写法一只比年份生日没到的人会被多算一岁 SELECT 姓名, YEAR(GETDATE()) - YEAR(出生日期) AS 年龄 FROM Stu200905 WHERE 院系名称 计算机系; -- 写法二用 DATEDIFF 按整年差算跨过生日才加一岁更贴近真实年龄 SELECT 姓名, DATEDIFF(YEAR, 出生日期, GETDATE()) AS 年龄 FROM Stu200905 WHERE 院系名称 计算机系;YEAR()相减是「年份差」DATEDIFF(YEAR, ...)是「跨过的年份边界数」两者在生日未满时会差 1。课程实验对精度要求不严但写论文或做报表时差一岁会被追问。第 7 题的区间筛选也有个坑-- 正确用日期字面量端点写全年月日 SELECT 姓名 FROM Stu200905 WHERE 院系名称 计算机系 AND 出生日期 BETWEEN 1999-01-01 AND 2000-12-31;如果写成BETWEEN 1999 AND 2000字符串会被隐式转换2000转成的日期是 2000 年 1 月 1 日零点2000 年出生的学生全被排除这个错很隐蔽结果集少几行根本看不出来。3.4 聚合与分组COUNT 的三种形态与 HAVING 的位置第 9、10 两题是分组聚合的主场。这一块的语法结构固定WHERE筛行GROUP BY分组HAVING筛组ORDER BY排序四个子句的执行顺序和书写顺序不一致理解顺序才能理解为什么WHERE里不能写聚合函数。-- 选修两门以上先按学号分组再筛掉门数不够的组 SELECT 学号, COUNT(*) AS 课程数 FROM SC200905 GROUP BY 学号 HAVING COUNT(*) 2; -- 门数 2 的学号、平均分、门数按平均分降序 SELECT 学号, AVG(分数) AS 平均成绩, COUNT(*) AS 选课门数 FROM SC200905 GROUP BY 学号 HAVING COUNT(*) 2 ORDER BY AVG(分数) DESC;COUNT有三种形态结果完全不同COUNT(*)数行数包含全NULL的行COUNT(列名)只数该列非空的行COUNT(DISTINCT 列名)数去重后的非空值。「统计选修某课的人数」用COUNT(*)是对的因为选修表主码是学号课程号一个学生对一门课只会有一条记录换成COUNT(分数)就会把缺考的人漏掉。ORDER BY后面既能写列名也能写聚合函数还能写SELECT里定义的别名。SQL Server 允许别名排序但标准写法是重复聚合表达式移植到别的库时不会翻车。4. 多表连接与嵌套组合从拼表到表达「不存在」实验二下半段和实验三难度跳了一级。前面所有查询只涉及一张表从多表连接开始问题变成「数据分散在几张表里怎么拼回来」到了嵌套查询问题又变成「怎么表达『没有』『全部』这类否定和全称的含义」。这两个台阶是数据库实验里最容易掉队的地方。4.1 内连接三张表串起来的最小骨架连接的本质是把两张表按某个条件配对配对成功的行留下。三张表连接的写法就是两个JOIN串起来条件写在各自的ON后面。-- 查选修了《数据库原理》的计算机系学生 SELECT s.学号, s.姓名 FROM Stu200905 AS s JOIN SC200905 AS sc ON s.学号 sc.学号 JOIN Cou200905 AS c ON sc.课程号 c.课程号 WHERE s.院系名称 计算机系 AND c.课程名 数据库原理;这段代码的信息量在于「筛选条件放在哪」。院系名称在学生表课程名在课程表两个条件都写在WHERE里连接条件写在ON里。内连接时两者等价外连接时完全不同——ON里的条件只决定怎么配对WHERE里的条件决定配对完之后留哪些行。表别名s、sc、c在这里不是可选项而是必需品三张表都有「学号」或「课程号」不写别名编译器会报「列名不明确」。写别名的另一个好处是省字长表名在复杂查询里能把一行撑到看不清。4.2 外连接哪张表的数据不能丢内连接只保留配对成功的行这意味着一件事没选课的学生会消失没人选的课也会消失。实验二里有两道题专门考这个-- 所有学生的信息 他所选的课程号没选课的也要出现 SELECT s.学号, s.姓名, s.性别, s.出生日期, s.院系名称, s.备注, sc.课程号 FROM Stu200905 AS s LEFT JOIN SC200905 AS sc ON s.学号 sc.学号; -- 所有课程名 被选修情况没人选的课也要出现 SELECT c.课程名, sc.学号, sc.课程号, sc.分数 FROM Cou200905 AS c LEFT JOIN SC200905 AS sc ON c.课程号 sc.课程号;判断用哪种连接问一句「哪张表的数据一行都不能少」。学生表一行不能少就用学生表在左边的LEFT JOIN课程表一行不能少就把课程表放左边。放反了结果集会少行而且不报错。外连接里没配上的那部分右表的列全是NULL。这个NULL是「没有匹配」的意思不是「数据缺失」两者在结果里长得一样但含义不同。要筛出「没选课的学生」就在WHERE里加sc.学号 IS NULL这是外连接的经典用法。注意RIGHT JOIN和LEFT JOIN能互相改写把两张表换个位置就行。实验报告里统一用左连接是为了读代码时「主表在左」这个直觉保持一致。4.3 自连接间接先行课与笛卡尔积的边界第 2 题「查每门课的间接先行课」是全场唯一必须自连接的题。一门课的先行课的先行课需要把课程表当成两张表来看一张扮演「课程」一张扮演「先行课」。-- a 是当前课程b 是 a 的先行课a 的间接先行课 b 的先行课 SELECT a.课程号 AS 课程编号, b.先行课 AS 间接先行课 FROM Cou200905 AS a JOIN Cou200905 AS b ON a.先行课 b.课程号 WHERE b.先行课 IS NOT NULL;拆解这段a.先行课 b.课程号让 b 表里那一行正好是 a 的直接先行课接着取b.先行课就往上跳了一层拿到间接先行课。最后的WHERE b.先行课 IS NOT NULL过滤掉「先行课本身没有先行课」的情况也就是报告里那句「有间接先行课才算」。少了这个条件结果里会多出一批NULL行。自连接不写ON条件就退化成笛卡尔积课程表有 m 行自己乘自己就是 m² 行再被WHERE筛一遍性能差且容易出重复。第 7 题「列出学生所有可能的选修情况」反过来故意要的就是笛卡尔积-- 学生 x 课程 的全组合用 CROSS JOIN 明确表达意图 SELECT s.学号, s.姓名, c.课程号, c.课程名 FROM Stu200905 AS s CROSS JOIN Cou200905 AS c;用CROSS JOIN而不是省略条件的逗号写法是为了让读代码的人一眼看出来「这里就是要全组合」而不是「忘写连接条件了」。4.4 子查询相关与非相关IN 与 EXISTS 的取舍嵌套查询分两种。非相关子查询可以独立跑出结果外层再拿这个结果去比相关子查询引用外层的列外层每换一行它就要重算一次。-- 非相关子查询先算出课程号外层只做一次 IN 判断 SELECT COUNT(*) AS 人数 FROM SC200905 WHERE 课程号 IN (SELECT 课程号 FROM Cou200905 WHERE 课程名 数据库原理); -- 相关外层每行都要回到子查询里确认一次 SELECT DISTINCT 课程号 FROM SC200905 AS a WHERE 学号 0602001 AND EXISTS (SELECT 1 FROM SC200905 AS b WHERE b.学号 0602002 AND b.课程号 a.课程号);EXISTS只关心子查询有没有行不关心查出什么所以SELECT 1就够了写SELECT *也不会更慢只是显得没想清楚。IN和EXISTS多数情况下可以互换选哪个看语义顺不顺判断「某集合里有没有」用IN判断「是否存在满足条件的记录」用EXISTS。「比计算机系学生年龄都小」这题用的是全称量词两种写法-- 写法一比最大的出生日期还晚就是比所有人都小 SELECT * FROM Stu200905 WHERE 院系名称 计算机系 AND 出生日期 (SELECT MAX(出生日期) FROM Stu200905 WHERE 院系名称 计算机系); -- 写法二逐个比用 ALL 表达「比每一个都小」 SELECT * FROM Stu200905 WHERE 院系名称 计算机系 AND 出生日期 ALL (SELECT 出生日期 FROM Stu200905 WHERE 院系名称 计算机系);「年龄小」等价于「出生日期晚」这个转换是解题关键转错了方向整道题就反了。ALL的语义是「和子查询结果里的每一个值比较都为真」子查询返回空集时ALL恒为真这是它和MAX写法的细微差别平时不影响边界数据下会出问题。4.5 集合运算UNION、INTERSECT、EXCEPT 与它们的子查询等价写法实验三最后三道题考的是同一件事的两种表达用集合运算或者用嵌套子查询。两条路都要会因为不同数据库对集合运算的支持程度不一样。-- 或选修了 C01 或 C02 的课程号UNION 自动去重 SELECT 课程号 FROM SC200905 WHERE 学号 0602001 UNION SELECT 课程号 FROM SC200905 WHERE 学号 0602002; -- 与两人同时选修的课程号 SELECT 课程号 FROM SC200905 WHERE 学号 0602001 INTERSECT SELECT 课程号 FROM SC200905 WHERE 学号 0602002; -- 差0602001 选了但 0602002 没选的课程号 SELECT 课程号 FROM SC200905 WHERE 学号 0602001 EXCEPT SELECT 课程号 FROM SC200905 WHERE 学号 0602002;三个运算符的优先级和结合性在标准里没定义混用时必须加括号不然同一个表达式在不同数据库里结果可能不同。UNION默认去重要保留重复得写UNION ALLINTERSECT和EXCEPT也默认去重这点在「查两人共同选修的课程」时正好省事因为选修表本身就是一人一课一条记录。三道题各自的子查询写法核心是把集合运算翻译成存在性判断-- 交集用 EXISTS0602001 选的课里存在 0602002 也选了的 SELECT DISTINCT 课程号 FROM SC200905 AS a WHERE 学号 0602001 AND EXISTS (SELECT 1 FROM SC200905 AS b WHERE b.学号 0602002 AND b.课程号 a.课程号); -- 差集用 NOT EXISTS0602001 选的课里不存在 0602002 也选的 SELECT DISTINCT 课程号 FROM SC200905 AS a WHERE 学号 0602001 AND NOT EXISTS (SELECT 1 FROM SC200905 AS b WHERE b.学号 0602002 AND b.课程号 a.课程号);「或」那题的子查询写法是WHERE 学号 IN (0602001,0602002)或者用OR连两个条件。三种写法结果相同差别在可读性和执行计划数据量小时随便写数据量大时IN列表和EXISTS往往比多个OR更容易走索引。5. 易错点排查五个让结果悄悄变空的地方实验做完能跑出结果不等于写对了。下面这五处全都是「不报错但结果是错的」属于最需要警惕的一类问题——编译器不帮你兜底只能靠自查。5.1 NOT IN 碰上 NULL一行脏数据让整个结果变空易漏点用NOT IN写「没有选修某课的学生」返回零行。为什么漏NOT IN展开后是一串比较只要子查询结果里出现一个NULLx NULL就是未知整个AND链条变成未知WHERE一行都不留。选修表里只要有一个学生的课程号是NULL或者子查询本身可能返回NULL结果就全灭。怎么补优先写NOT EXISTS它的语义是「不存在这样的行」天然不受NULL影响非要用NOT IN就在子查询里加WHERE 列名 IS NOT NULL兜底。-- 稳的写法NOT EXISTS SELECT s.* FROM Stu200905 AS s WHERE NOT EXISTS ( SELECT 1 FROM SC200905 AS sc JOIN Cou200905 AS c ON sc.课程号 c.课程号 WHERE sc.学号 s.学号 AND c.课程名 数据库原理 );5.2 该用外连接却写了内连接少掉的行没人提醒易漏点「查所有学生的选课情况」「查所有课程被选情况」两题用JOIN写结果里没选课的学生、没人选的课凭空消失。为什么漏内连接的语义就是「只留配对成功的」没配对的行被静默丢弃不报错、不警告。加上这两题的样例数据里恰好大多数人都有选课记录少掉一两行不容易被发现。怎么补拿到题先问「哪张表一行都不能少」把它放左边写LEFT JOIN写完再拿学生表总行数和结果行数对一遍数字对不上就是连接方式选错了。5.3 GROUP BY 漏列与 HAVING 写错位置易漏点SELECT列表里放了没参与分组的列或者把聚合条件写进WHERE。为什么漏SQL Server 对前者会直接报「列无效因为未包含在聚合函数或 GROUP BY 子句中」但很多人看到报错就随手把列删了而不是想清楚「这一列到底该不该分组」后者会报「聚合函数不能出现在 WHERE 子句中」改的时候又容易把整段条件搬错地方。怎么补WHERE筛行、HAVING筛组这个分工记死。写分组查询时先列出「按什么分组」再把SELECT里的每个非聚合列逐个对照确认它要么在GROUP BY里要么被聚合函数包住。-- 正确学号和姓名都进 GROUP BYSQL Server 不认主码函数依赖 SELECT s.学号, s.姓名, AVG(sc.分数) AS 平均成绩, COUNT(*) AS 门数 FROM Stu200905 AS s JOIN SC200905 AS sc ON s.学号 sc.学号 WHERE s.院系名称 计算机系 GROUP BY s.学号, s.姓名 HAVING COUNT(*) 2 ORDER BY 平均成绩 DESC;5.4 年龄与日期区间差一岁、差一天都看不出来易漏点年龄用两个年份相减区间用BETWEEN 1999 AND 2000。为什么漏两种写法都能跑结果只差一行两行肉眼对不出来而且课程实验的数据量小错了也很难被老师发现。怎么补年龄统一用DATEDIFF(YEAR, 出生日期, GETDATE())日期区间两端写全年月日BETWEEN 1999-01-01 AND 2000-12-31。写完顺手拿一个生日在 12 月 31 日的学生验算一遍边界一过就露馅。5.5 集合运算的去重与排序位置易漏点以为UNION保留重复或者把ORDER BY写在某个子查询里。为什么漏UNION的默认行为是去重这在「查两人共同选修」时恰好帮了忙但在「查两人各自选了哪些课、重复也要显示」时就把数据吃掉了ORDER BY只能出现在整个集合运算的最后写在中间会直接语法错误。怎么补凡是需要保留重复就写UNION ALLORDER BY一律放到最后一个SELECT之后作用于整个结果集。三个以上集合运算混用时加括号明确结合顺序。6. 查漏补缺一张自测清单和一套三问法复习到这一步剩下的不是再看一遍语法而是把「看到题能不能立刻判断该用什么」练成条件反射。我用一套三问法过完了整份报告比按题号顺序重做一遍省一半时间。第一问涉及几张表一张表就在WHERE和聚合里做文章两张以上先画连接骨架确定主表是谁、谁的数据不能丢再决定JOIN还是LEFT JOIN。第二问结果是一行还是一个组出现「每」「各」「平均」「人数」「门数」这类词就是要分组GROUP BY 聚合函数 HAVING三件套直接上。第三问题目里有没有「没有」「所有」「同时」「或者」「没有」优先想NOT EXISTS「所有」优先想ALL或MAX/MIN子查询「同时」想INTERSECT或EXISTS「或者」想UNION或IN。配合下面这张自测表逐项打勾勾不上的回去补对应章节。自测项判断标准对应模块能默写三张表的建表语句主码、外码、默认值、可空性一个不漏第 2 章能说清 NULL为什么错能讲出三值逻辑第 3 章能区分COUNT(*)与COUNT(列)能举出缺考的例子第 3 章能判断该用内连接还是左连接说得清哪张表不能丢行第 4 章能手写间接先行课的自连接知道为什么要加IS NOT NULL第 4 章能用两种写法做交、差、并集合运算与EXISTS各一遍第 4 章能解释NOT IN的 NULL 陷阱说得出为什么结果是空集第 5 章还有一个小技巧把每道题的本文还有配套的精品资源点击获取
