★ FEATURED ARTICLE 简介这份资源是太原理工大学软件工程专业「数据库概论」课程的实验报告文档面向正在学习数据库系统原理、需要完成SQL上机实验的本科生与自学者。报告以Microsoft SQL Server 2016为实验平台围绕交互式SQL语句的使用展开涵盖数据定义、索引创建、视图创建与数据操作四大模块具体包括用CREATE TABLE建立Student、Course、Sc等表用ALTER TABLE修改结构与约束用CREATE INDEX创建唯一索引与聚簇索引用CREATE VIEW定义IS_Student视图以及INSERT、UPDATE等数据更新操作并配有完整SQL代码与执行结果。资源包为1个docx文件大小约1.69MB结构清晰、代码可直接参考。目前已有311人学习下载适合需要对照实验步骤、梳理SQL语法要点或撰写实验报告的学生参考使用。1. 交互式 SQL 的真相为什么你写的约束有时形同虚设很多人以为数据库实验就是敲几条CREATE TABLE、INSERT、SELECT跑通了就算掌握。但真正让开发者翻车的往往不是语法写错而是约束在关键时刻没有生效。比如你明明定义了CHECK(Grade 0 AND Grade 100)插入null却畅通无阻明明设了主键更新学号时却报出莫名其妙的冲突。这些现象背后是 SQL Server 对声明式完整性、执行顺序和空值语义的一整套设计取舍。这份软件工程数据库概论的实验报告恰好把数据定义、数据完整性、用户鉴别与数据控制串成了一条线。搞懂它你就能明白数据库不是“存数据的地方”而是一台用约束和元数据维持一致性的状态机。下面从表结构、约束、查询、视图四个层面把这份实验报告里的机制拆开讲透。2. 数据定义与表结构CREATE TABLE到底在系统表里写了什么2.1 从Student表看列定义与主键的元数据落点实验里第一条语句就是建Student表CREATE TABLE Student( Sno char(8) primary key, Sname char(10), Ssex char(2), Sage int, Sdept char(20) );这条语句执行时SQL Server 并不是简单“记下字段名”。它会在系统目录里做几件事在sys.tables里插入一行记录对象 ID在sys.columns里为每个列插入一行记录列名、类型、长度、是否允许 NULL在sys.indexes里为主键创建一个唯一聚簇索引默认情况下因为主键必须唯一且不能为空。也就是说primary key不是“标记”而是强制创建唯一索引 非空约束的组合动作。常见的实现思路是主键约束在存储引擎层表现为一棵 B 树键值就是Sno。当你插入重复学号时报错不是解析器给的而是存储引擎在索引插入时发现键重复。这也解释了为什么主键列不允许NULL——B 树里没有“空键”的位置。char(8)和varchar(8)的区别也在这里体现char是定长存储时不足 8 位补空格varchar是变长按实际长度存。实验里Sno char(8)固定 8 位是因为学号格式统一定长可以让索引页里每条记录长度一致减少页分裂。这是设计取舍用一点空间换查询稳定性。2.2ALTER TABLE修改列类型时数据发生了什么实验中有一步ALTER TABLE Student ALTER COLUMN Sage smallint;把int改成smallint。这不是改个元数据就完事。SQL Server 需要检查现有数据是否都能放进smallint范围 -32768 到 32767。如果表里已有Sage 100000这条语句会直接失败。检查通过后它会在系统目录里更新列类型并重建受影响的索引和约束。对于大表这是一次昂贵的操作因为要逐行转换。我一般会这么理解ALTER COLUMN本质是一次“影子重建”。数据库先建一个新结构把数据按新类型转换后搬过去再替换旧结构。所以生产环境改列类型通常要选低峰期并且先备份。实验里数据量小感觉不到代价但机制是一样的。另一个动作ALTER TABLE Student ADD Sclass char(4);新增列时如果表里已有数据新列的值默认是NULL除非指定DEFAULT。这不会重写所有行而是通过“列偏移”方式让旧行读出新列时返回默认值。这是 SQL Server 的优化加列是元数据操作不加锁全表。2.3 索引聚簇与非聚簇在页级别怎么分道扬镳实验里创建了多种索引CREATE INDEX iCname ON Course(Cname); CREATE UNIQUE INDEX iSname ON Student(Sname); CREATE CLUSTERED INDEX iSnoCno ON Sc(Sno, Cno DESC); CREATE UNIQUE INDEX iCno ON Course(Cno);聚簇索引决定数据的物理存储顺序。Sc表按Sno, Cno DESC聚簇意味着数据页里记录就是按这个顺序排列的。当执行WHERE Sno 20100001 AND Cno 1时存储引擎可以直接定位到对应页再在页内二分查找。非聚簇索引则是独立的一棵树叶子节点存的是索引键 行定位符聚簇索引键或 RID。查Cname时先走iCname找到键再回表取整行。唯一索引和主键的区别在于唯一索引允许一个NULL因为NULL不等于NULL而主键不允许。实验里iSname是唯一索引如果插入两个Sname为NULL的学生不会冲突。这是 SQL 标准里空值语义的直接体现也是很多人踩坑的地方。3. 数据完整性约束、默认值与触发器的执行顺序3.1CHECK约束为什么拦不住NULL实验三里定义了CREATE TABLE SC( Sno char(8) FOREIGN KEY (Sno) REFERENCES Student(Sno), Cno char(4), Grade smallint CONSTRAINT SC_CHECK CHECK(Grade 0 AND Grade 100), PRIMARY KEY (Sno, Cno), CONSTRAINT FK_SC FOREIGN KEY (Cno) REFERENCES Course(Cno) );然后插入INSERT INTO SC VALUES(20100003,1,null);这条语句能成功。原因是CHECK约束的判定逻辑是如果表达式结果是UNKNOWN则视为通过。Grade为NULL时NULL 0结果是UNKNOWN不是FALSE所以约束不拦。这是 SQL 三值逻辑的经典表现。如果你希望成绩不允许为空必须显式写Grade smallint NOT NULL CHECK(...)。常见的实现思路是CHECK约束在解析阶段被编译成一个谓词每次插入或更新时对行求值。求值结果只有TRUE才放行FALSE和UNKNOWN的处理取决于约束是否声明了NOT NULL。所以“约束没报错”不等于“数据一定合法”这是血泪经验。3.2 外键约束的级联与阻塞插入顺序为什么重要实验里SC表有两个外键分别指向Student和Course。这意味着插入SC记录前对应的Student.Sno和Course.Cno必须已存在。如果先插SC再插Student会报外键冲突。这不是 bug而是引用完整性在存储引擎层的检查每次插入SC行数据库会用外键列的值去被引用表的主键索引里查一次查不到就拒绝。删除时同理。实验里删除Student记录前必须先删SC里对应选课记录否则外键会阻塞。如果定义外键时加了ON DELETE CASCADE删除Student会自动删SC但实验里没加所以是默认的NO ACTION。这是设计取舍级联删除方便但危险可能一次删掉大量关联数据阻塞删除安全但需要应用层维护顺序。3.3 默认值约束与NULL的边界实验里Student表有Stotal smallint DEFAULT 0插入时如果省略Stotal它会自动填 0。但如果你显式写Stotal NULL默认值不生效列值就是NULL。默认值只在“未提供该列”时触发不是“提供 NULL 时替换”。这个区别在批量导入数据时经常导致意外CSV 里空字段被解析成NULL默认值没起作用最后统计时一堆空值。另外DEFAULT约束和NOT NULL是独立的。Stotal允许NULL且默认 0意味着你可以插入NULL也可以省略。如果业务上不允许空应该同时写NOT NULL DEFAULT 0。4. 查询与视图SELECT背后的代数与优化器决策4.1 连接查询的两种写法在执行计划上是否等价实验里写了两种连接-- 写法一 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student, SC WHERE Student.Sno SC.Sno; -- 写法二 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student JOIN SC ON (Student.Sno SC.Sno);在 SQL Server 里这两种写法经过解析器后生成相同的逻辑查询树优化器通常给出相同的执行计划。但JOIN ... ON更清晰因为连接条件和过滤条件分离。写法一里WHERE同时承担连接和过滤一旦漏写连接条件就变成笛卡尔积。实验里多表查询SELECT Student.Sno, Sname, Cname, Grade FROM Student, SC, Course WHERE Student.Sno SC.Sno AND SC.Cno Course.Cno;三个表两两连接优化器会决定先连哪两个。通常它会选结果集最小的先连比如先按SC.Cno Course.Cno过滤再和Student连。这个决策依赖统计信息索引的密度、列的数据分布。如果统计信息过期优化器可能选错顺序查询突然变慢。这是“玄学”性能问题的常见来源。4.2 嵌套查询与EXISTS的语义差异实验里有SELECT Sname FROM Student WHERE EXISTS ( SELECT * FROM SC WHERE Sno Student.Sno AND Cno 1 );EXISTS是相关子查询对外层每一行Student内层执行一次只要找到一条SC记录就返回TRUE。它的执行计划通常是“半连接”semi-join找到第一条匹配就停不关心有多少条。而IN子查询会先算出内层结果集再去重匹配。当内层结果集很大时EXISTS往往更快因为它可以提前终止。但NOT EXISTS和NOT IN在空值上行为不同。如果内层返回NULLNOT IN会得到UNKNOWN导致外层查不出任何行。NOT EXISTS不受影响。这是实验里没写但实际开发中必踩的坑用NOT IN做反连接子查询列有NULL时结果为空。4.3 视图不是表但为什么能更新实验里创建了CREATE VIEW Student_VIEW(学号,姓名,性别,年龄,系,班级) AS SELECT Sno, Sname, Ssex, Sage, Sdept, Sclass FROM Student;视图在系统目录里存的是SELECT语句文本不存数据。查询视图时数据库把视图定义展开和外层查询合并再优化。这叫“视图展开”。如果视图只来自一个表且包含主键通常可以更新因为数据库能反向映射到基表。但如果视图里有JOIN、GROUP BY、聚合函数更新就会受限因为一行视图记录可能对应多行基表数据数据库无法确定改哪一行。实验里的S_G视图CREATE VIEW S_G(Sno, Gavg) AS SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno;这个视图不可更新因为AVG是聚合结果没有明确的基表行对应。查询它可以但UPDATE S_G SET Gavg 90会报错。这是视图的设计边界它简化查询但不承诺可更新性。5. 避坑与排查那些实验报告里没写但一定会遇到的问题5.1 现象插入中文变成问号原因排序规则与列类型不匹配实验里用char和varchar存中文姓名。如果数据库默认排序规则不是中文或者列类型是varchar而非nvarchar插入“李勇”可能变成“??”。原因是varchar按单字节编码存储中文需要多字节。解决方式是建表时用nvarchar或者在连接字符串里指定字符集。SQL Server 里nvarchar是 Unicode能存所有字符。实验报告没提这点但实际部署时这是第一个翻车点。5.2 现象UPDATE学号时报主键冲突原因外键没有级联更新实验里UPDATE Student SET Sno20100025 WHERE Sno20100021;如果SC表里有20100021的选课记录这条更新会失败因为外键约束阻止了被引用键的修改。解决方式是先更新SC里的外键值或者定义外键时加ON UPDATE CASCADE。但级联更新也有风险如果多个表形成环可能触发循环。所以很多团队选择在应用层用事务手动维护而不是依赖数据库级联。5.3 现象DELETE全表后自增列不重置原因身份列计数器独立实验里删除临时表数据用DELETE FROM tmpSC。如果表有IDENTITY列DELETE不会重置种子下次插入继续从上次最大值加一。要重置得用TRUNCATE TABLE它直接释放数据页速度快且重置身份列。但TRUNCATE不能用于有外键引用的表。这是清理测试数据时的常见需求想重跑实验结果 ID 从 100 开始就是因为用了DELETE而不是TRUNCATE。5.4 现象GROUP BY查询报列无效原因SELECT列不在聚合或分组里实验里SELECT cno, COUNT(*), AVG(Grade), MAX(Grade) FROM SC GROUP BY Cno;这是合法的因为cno在GROUP BY里。但如果写成SELECT Sno, Cno, COUNT(*) FROM SC GROUP BY Cno就会报错因为Sno既不在分组里也没被聚合。标准 SQL 要求SELECT列表里的非聚合列必须出现在GROUP BY中。MySQL 某些模式允许但 SQL Server 严格。解决方式是补全分组列或者用窗口函数。5.5 现象LIKE 刘%查不到“刘洋”原因尾随空格与排序规则char类型定长存储“刘洋”时会补空格到 10 位。LIKE 刘%理论上能匹配但如果排序规则是BIN二进制空格参与比较可能影响结果。更常见的是LIKE和在尾随空格上的行为差异abc abc 在 SQL Server 里为TRUE因为char比较忽略尾随空格但LIKE abc不匹配abc 。实验里用LIKE做模糊查询如果数据有尾随空格结果可能少。解决方式是RTRIM后再比较或者统一用varchar。6. 从约束到控制数据完整性之外用户鉴别与权限的取舍实验报告标题里提到“用户鉴别与数据控制”但正文主要落在完整性约束。这两者在数据库里是不同层完整性约束保证数据本身符合业务规则用户鉴别和权限控制保证“谁可以碰哪些数据”。SQL Server 里登录名Login是服务器级数据库用户User是数据库级角色Role是权限集合。常见的做法是为每个应用建一个数据库用户只授予必要的SELECT/INSERT/UPDATE/DELETE不给DROP或ALTER。实验里用sa或高权限账号操作方便但危险。设计取舍在于约束越严格数据越干净但写入路径越容易失败应用层要处理更多异常权限越细安全性越高但管理成本越大。我一般会这么理解完整性约束是“最后一道防线”不是“唯一防线”。应用层校验给用户友好提示数据库约束兜底防止脏数据。两者不是二选一而是分层。验证约束是否生效不能只看“插入成功”。要主动构造边界数据插入NULL看CHECK是否放行插入重复键看主键是否拦截删除被引用行看外键是否阻塞。实验报告里的“检查完整性约束”部分就是干这个的。从那以后我每次设计表都强制走一遍先写约束再插边界数据最后看报错信息是否符合预期。希望帮到你。本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
