简介本资源是太原理工大学软件工程专业《数据库概论》课程配套的完整实验报告面向高校计算机类专业学生及数据库初学者聚焦SQL Server 2016环境下数据库核心操作的实践训练。报告覆盖数据定义CREATE/ALTER/DROP TABLE、索引创建唯一索引、聚簇索引、视图构建如IS_Student系别筛选视图及数据增删改查全流程含详细语句示例与关键注意事项助力夯实数据库管理与SQL编程基础。资源为单个Word文档.docx大小1.69MB内容结构清晰含实验目的、平台配置、分步代码、执行结果与总结反思便于直接复现与对照学习。目前已有311人下载学习适合课程复习、实验预习、SQL语法速查及数据库原理实操巩固。1. 这不是一份普通实验报告它是太原理工大学软件工程专业数据库课的「SQL 实战手账」覆盖 SQL Server 2016 全流程操作、数据完整性验证与可复现脚本如果你正被《数据库概论》课程卡在“建表报错”“外键插不进”“视图查不出数据”“成绩更新总漏行”这些具体问题上——别再翻 PDF 教材或对着 SSMS 界面发呆了。这份来自太原理工大学软件工程专业2017 级的真实实验报告不是模板不是示例而是张利云同学在软件学院实验室 A1 用 SQL Server 2016 亲手敲出来、跑通、截图、写进 Word 的完整过程。它包含 3 大核心模块交互式 SQL 全语法实操含 47 条可直接粘贴执行的语句、数据完整性约束的 7 类边界验证主键冲突、空值插入、CHECK 范围越界、外键级联失效等、视图索引连接查询的生产级组合用法含 LEFT JOIN 与 EXISTS 子查询对比。它不讲“什么是范式”只告诉你“ALTER TABLE Student ALTER COLUMN Sage smallint执行后为什么UPDATE Student SET Sage22 WHERE Sno20100001突然报错”它不罗列“SQL Server 有哪些版本”只给出CREATE UNIQUE INDEX iSname ON Student(Sname)在实际建索引时必须加的IGNORE_DUP_KEY OFF隐含参数。适合两类人一是刚装好 SQL Server 2016 却连SELECT * FROM Student都报“对象名无效”的新手二是做课程设计时需要快速搭出带约束、带视图、带多表关联的最小可行数据库的软件工程本科生。你不需要理解所有原理但只要照着第 2 章的顺序一条条执行就能在 45 分钟内跑通全部 38 个操作点。2. 数据定义与结构操作从 CREATE TABLE 到 DROP TABLE每一步都带参数解释和执行逻辑2.1 基本表创建字段类型、主键、外键的选型依据与常见误配在 SQL Server 中表结构定义不是“能跑就行”而是直接影响后续 DML 操作的稳定性。张利云同学实验中创建的四张表Student、Course、Sc、Employee其字段类型选择有明确工程依据Sno char(8)学号为固定 8 位数字字符串用char(8)而非varchar(8)避免因长度变化引发索引碎片但注意char会补空格若后续需WHERE Sno 20100001匹配必须确保插入时未带尾部空格。Sage int→ 后续改为smallint原始定义用int4 字节足够但实验中通过ALTER TABLE Student ALTER COLUMN Sage smallint改为smallint2 字节理由是学生年龄范围为 16–25smallint可存 -32768 至 32767既节省存储又提升索引效率。这是软件工程中典型的“类型最小化”实践。Score int成绩为整数但实验三中约束改为Grade SMALLINT CONSTRAINT SC_CHECK CHECK(Grade 0 AND Grade100)强制范围校验。此处SMALLINT比INT更合理且CHECK约束比应用层校验更可靠。外键定义必须严格匹配引用列create table Sc ( Sno char(8), Cno char(4), Score int, primary key(Sno,Cno), foreign key(Sno) references Student(Sno), -- ✅ 正确Sno 类型、长度、是否允许 NULL 必须与 Student.Sno 完全一致 foreign key(Cno) references Course(Cno) -- ✅ 正确Course.Cno 是 char(4) 主键 );提示若Course.Cno定义为varchar(4)而Sc.Cno为char(4)SQL Server 会报错Column Course.Cno is not the same data type as referencing column Sc.Cno in foreign key FK__Sc__Cno__...。类型必须一字不差。2.2 表结构修改ALTER TABLE 的三种典型场景与不可逆风险ALTER TABLE是高频操作但不同修改类型风险差异极大。实验中展示了三类操作需按安全等级排序执行1添加新字段低风险alter table Student ADD Sclass char(4);逻辑在表末尾追加一列不影响现有数据。注意Sclass无NOT NULL约束因此对已有 7 行Student记录自动填充NULL。若需非空必须先ADD再UPDATE填值最后ALTER COLUMN Sclass char(4) NOT NULL。2修改字段类型中风险alter table Student ALTER COLUMN Sage smallint;逻辑将int列转为smallintSQL Server 需逐行转换数据。风险若某行Sage值 32767如误存为99999执行失败并回滚。实验中所有年龄 ≤23故安全。关键检查执行前务必SELECT MAX(Sage), MIN(Sage) FROM Student确认值域。3删除表高风险不可逆drop table Employee;逻辑物理删除表结构及全部数据回收空间。血泪经验在真实项目中DROP TABLE必须前置IF EXISTS并备份。实验中Employee仅用于演示无业务依赖故直接删除。但你在课程设计中若删Sc表会导致所有选课记录永久丢失永远不要在未备份时执行DROP TABLE。2.3 索引创建聚簇索引、唯一索引与查询性能的量化关系索引不是“越多越好”而是针对高频查询路径优化。实验中创建的 4 个索引各有明确目的索引语句类型作用场景性能影响create index iCname ON Course(Cname)非聚簇WHERE Cname 数据库系统原理将全表扫描O(n)降为索引查找O(log n)create unique index iSname ON Student(Sname)唯一非聚簇WHERE Sname 刘晨 防重名除加速查询外强制Sname值唯一避免同名学生插入create clustered index iSnoCno on Sc(Sno,Cno desc)聚簇ORDER BY Sno, Cno DESC查询改变物理存储顺序使(Sno,Cno)组合查询极快但每个表仅能有一个聚簇索引create unique index iCno ON Course (Cno)唯一非聚簇WHERE Cno 1因Cno已是主键此索引冗余SQL Server 会忽略主键自动建唯一聚簇索引注意create clustered index iSnoCno on Sc(Sno,Cno desc)中desc仅影响ORDER BY排序方向不改变索引树结构。聚簇索引的物理排序由Sno主序决定Cno为次序。2.4 视图创建虚拟表的本质与三层嵌套查询的简化逻辑视图是“保存的 SELECT 语句”不存数据只存定义。实验中三个视图解决不同抽象层级问题IS_Student数据过滤抽象create view IS_Student as select Sno,Sname,Sage from Student where SdeptIS;本质将WHERE SdeptIS条件封装业务代码只需SELECT * FROM IS_Student无需关心系别字段名或值。优势若系别编码从IS改为INFO只需改视图定义所有调用方无感。S_G聚合计算抽象create view S_G(Sno,Gavg) as select Sno,avg(Grade) from SC group by Sno;本质将GROUP BYAVG()封装隐藏分组细节。关键点S_G中Gavg是decimal(18,6)类型SQL Server 默认若需保留 2 位小数应显式CAST(AVG(Grade) AS DECIMAL(5,2))。XK_VIEW多表连接抽象create view XK_VIEW as select Student.*,Course.*,Grade from Student,SC,Course where Student.Sno SC.Sno and SC.Cno Course.Cno;本质将三表JOIN逻辑固化调用方SELECT * FROM XK_VIEW WHERE Cname数据库系统原理直接获得学生课程成绩。风险Student.*和Course.*可能有同名列如都含id导致SELECT *报错。生产环境应显式列出字段如Student.Sno, Student.Sname, Course.Cno, Course.Cname, SC.Grade。3. 数据操作全流程INSERT/UPDATE/DELETE 的 12 个关键参数与事务控制3.1 插入数据单行、多行、子查询插入的语法差异与 NULL 处理INSERT是最易出错的操作核心在于字段列表、值列表、NULL 显式性三者严格对应。1标准单行插入字段全列insert into Student Values (20100001,李勇,男,20,CS,1001);字段顺序必须与CREATE TABLE Student(...)中定义顺序一致。Values中值数量、类型、顺序必须完全匹配。Sage为int传20字符串会隐式转换但Sno为char(8)传20100001数字会报错必须加单引号。2指定字段插入推荐防结构变更insert into Student (Sno,Sname,Ssex,Sage,Sdept,Sclass) Values (20100002,刘晨,女,19,CS,1001);显式声明字段即使表增加列此语句仍有效。允许跳过NOT NULL字段否Sclass在实验二中无NOT NULL可省略但在实验三中Sclass CHAR(4) NOT NULL则必须提供值。3子查询插入批量导入create table cs_Student (学号 char(8), 姓名 char(8), 年龄 smallint); insert into cs_Student select Sno,Sname,Sage from Student where SdeptCS;SELECT返回列数、类型、顺序必须与cs_Student定义完全一致。WHERE SdeptCS中CS是字符串大小写敏感SQL Server 默认区分大小写。4NULL 值处理显式 vs 隐式insert into Student Values (20100003,刘洋,女,null,null,1001); -- ✅ 显式 NULL insert into Student (Sno,Sname,Ssex,Sclass) VALUES(20100004,张伟,男,1002); -- ✅ 隐式 NULLSage,Sdept 未提供若字段允许NULL如Sage无NOT NULL可省略或写NULL。若字段NOT NULL如实验三中Sclass CHAR(4) NOT NULL省略即报错Cannot insert the value NULL into column Sclass。3.2 更新数据WHERE 条件的精确性与子查询关联的执行顺序UPDATE的最大陷阱是WHERE 条件不精确导致误更新。实验中 5 个UPDATE操作覆盖典型场景1单条件精确更新update Student Set Sage22 where Sno20100001; -- ✅ 安全Sno 主键唯一匹配2无 WHERE 全表更新危险update Student Set SageSage1; -- ⚠️ 警告无 WHERE所有行 Sage 1实验中用于演示生产禁用。3子查询关联更新关键相关子查询update Sc Set ScoreScore5 where CS (select Sdept from Student where Student.SnoSc.Sno);执行逻辑对Sc表每一行执行子查询(select Sdept from Student where Student.SnoSc.Sno)获取该生所在系。若子查询返回多行如Sno不唯一报错Subquery returned more than 1 value。若子查询返回NULL如Sc.Sno在Student中不存在CS NULL结果为UNKNOWN该行不更新。4多条件组合更新update Sc Set Score85 where Sno20100010 And Cno3; -- ✅ 精确到学生课程组合3.3 删除数据物理删除、逻辑删除与临时表隔离策略DELETE的风险高于UPDATE因数据不可恢复。实验中采用三层防护1单行物理删除最低风险delete from Student where Sno20100022; -- ✅ 主键定位精准删除2临时表隔离推荐可回滚select * into tmpSC from Sc; -- 创建临时表复制全量数据 delete from tmpSC where Sno20100001 and Cno1; -- 在 tmpSC 中操作不影响原表 -- 验证无误后再 delete from Sc ...优势tmpSC是独立表删除操作可随时SELECT * FROM tmpSC验证原Sc表毫发无损。注意select * into仅复制数据不复制索引、约束、触发器。3全表清空最高风险delete from tmpSC; -- ✅ 清空临时表安全 -- 但 delete from Sc; 是灾难性操作实验中未执行仅用于教学警示。避坑 / 常见问题 / 排查 / 注意现象 1执行insert into Student Values (20100001,李勇,男,20,CS,1001)报错Violation of PRIMARY KEY constraint原因Sno20100001已存在前面已插入主键冲突。解决先SELECT * FROM Student WHERE Sno20100001确认是否存在或用MERGE语句实现“存在则更新不存在则插入”。现象 2update Student Set Sage22 where Sno20100001执行后SELECT * FROM Student查不到变化原因未提交事务。SQL Server 默认autocommit关闭需手动COMMIT或在 SSMS 中勾选Tools → Options → Query Execution → SQL Server → ANSI → SET IMPLICIT_TRANSACTIONS。解决执行UPDATE后立即SELECT验证或开启IMPLICIT_TRANSACTIONS。现象 3delete from Sc where Cno1 and Sno20100001删除失败SELECT * FROM Sc仍显示该记录原因Sc表主键为(Sno,Cno)但Cno字段在Course表中为char(4)而插入时用了1长度1SQL Server 自动补空格至char(4)实际存储为1 。WHERE Cno1匹配失败。解决WHERE Cno1 或改用RTRIM(Cno)1但最佳实践是统一用varchar避免空格问题。现象 4insert into Course Values(2,高等数学,null,2)成功但insert into Course Values(8,JAVA,7,3)报错The INSERT statement conflicted with the FOREIGN KEY constraint原因Cpno是外键引用Course.Cno而7在Course表中不存在实验中Cno最大为7但7对应C 语言Cpno为NULL。解决先确认Cpno值存在于Course.Cno中或设Cpno为NULL。现象 5select * from Student order by Sdept,Sage Desc结果中Sdept为空的行排在最前原因NULL在ORDER BY中默认排在最前SQL Server 行为Sdeptnull的行如刘洋被优先显示。解决ORDER BY ISNULL(Sdept,ZZZZ), Sage DESC将NULL映射为ZZZZ排到最后。4. 数据查询深度解析单表、分组、连接、嵌套、集合五大查询模式的执行计划对照4.1 单表查询BETWEEN、IN、LIKE、IS NULL 的底层执行差异单表查询看似简单但不同谓词影响执行计划WHERE Sage BETWEEN 20 AND 23等价于WHERE Sage 20 AND Sage 23可利用Sage上的索引若存在。WHERE Sdept IN(IS,MA,CS)若Sdept有索引SQL Server 可能转为多个OR条件索引查找。WHERE Sname LIKE刘%前导通配符刘%可用索引Sname有iSname唯一索引但%刘或%刘%无法用索引必全表扫描。WHERE Score IS NULLNULL不参与 B-Tree 索引比较除非索引包含INCLUDE (Score)或使用WHERE Score IS NULL的筛选索引。实验中SELECT Sname,2004-Sage YearofBirth,Lower(Sdept) FROM Student展示了表达式计算2004-Sage常量减法无性能问题。Lower(Sdept)函数调用若Sdept无函数索引此列无法走索引。4.2 分组统计GROUP BY 与 HAVING 的执行顺序与聚合函数限制GROUP BY是 SQL 最易误解的语法之一。执行顺序为FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY。SELECT Sno,Count(Cno),AVG(Score),MAX(Score) FROM Sc GROUP BY Sno按学生分组计算每人选课数、均分、最高分。HAVING AVG(Score) 90在分组后过滤不能写WHERE AVG(Score) 90WHERE在分组前AVG未计算。关键限制SELECT中非聚合字段必须在GROUP BY中出现。错误示例SELECT Sno,Sname,AVG(Score) FROM Sc GROUP BY Sno——Sname未在GROUP BY中报错。正确写法SELECT Sc.Sno,Sname,AVG(Score) FROM Sc JOIN Student ON Sc.SnoStudent.Sno GROUP BY Sc.Sno,Sname。4.3 连接查询ON 与 WHERE 的语义区别及 LEFT JOIN 的 NULL 处理连接查询的核心是理解连接条件ON与过滤条件WHERE的执行时机。1WHERE 中指定连接旧式不推荐SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SC WHERE Student.Sno SC.Sno; -- ✅ 连接条件逻辑笛卡尔积后过滤效率低易写错。2FROM 中指定连接ANSI 标准推荐SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student JOIN SC ON (Student.SnoSC.Sno); -- ✅ 显式连接3LEFT OUTER JOIN保留左表全部行SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student LEFT OUTER JOIN SC ON (Student.SnoSC.Sno);结果Student表所有行都出现若某生未选课Cno、Grade为NULL。关键点WHERE过滤会破坏LEFT JOIN效果。错误... LEFT JOIN SC ON ... WHERE SC.Cno1—— 将NULL行过滤掉等效于INNER JOIN。正确... LEFT JOIN SC ON (Student.SnoSC.Sno AND SC.Cno1)—— 连接时就限定课程。4.4 嵌套查询EXISTS 与 IN 的性能分水岭及相关子查询原理嵌套查询中EXISTS通常优于IN尤其当子查询结果集大时。EXISTS (SELECT * FROM SC WHERE SnoStudent.Sno AND Cno 1)对Student每行检查SC中是否存在匹配记录找到即停不遍历全表。Sno IN (SELECT Sno FROM SC WHERE Cno1)先执行子查询生成Sno列表再对外表Sno匹配若子查询返回 10 万行内存压力大。实验中SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE SnoStudent.Sno AND Cno 1)是典型“存在性检查”EXISTS是最优解。4.5 集合查询UNION/INTERSECT/EXCEPT 的去重规则与 NULL 处理集合操作要求列数、类型、顺序完全一致。UNION合并结果集自动去重UNION ALL不去重性能更高。INTERSECT取交集A INTERSECT B等价于A WHERE EXISTS (SELECT * FROM B WHERE B.colA.col)。EXCEPT取差集A EXCEPT B返回在 A 中但不在 B 中的行。NULL 处理UNION/INTERSECT/EXCEPT将两个NULL视为相等因此SELECT NULL UNION SELECT NULL返回一行NULL。5. 数据完整性验证约束、触发器与 7 类完整性失效场景的实测复现5.1 约束类型详解PRIMARY KEY、FOREIGN KEY、CHECK、UNIQUE、DEFAULT 的作用域数据完整性是数据库的基石实验三通过 7 类操作验证约束有效性约束类型定义位置作用实验中验证点PRIMARY KEYCREATE TABLE或ALTER TABLE唯一标识行不允许NULL插入重复Sno失败FOREIGN KEYCREATE TABLE或ALTER TABLE引用其他表主键保证参照完整性插入Sc中不存在的Cno失败CHECKCREATE TABLE或ALTER TABLE字段值满足布尔表达式Grade插入101或-1失败UNIQUECREATE TABLE或ALTER TABLE字段值唯一允许NULL插入同名Cname失败DEFAULTCREATE TABLE字段无值时的默认值INSERT INTO Student(Sno,Sname,...) VALUES(...)省略Stotal自动为0DEFAULT 0在Student表中定义Stotal smallint DEFAULT 0。插入时若不提供Stotal自动填0若提供NULL则存NULL因无NOT NULL约束。5.2 主键与外键约束失效场景INSERT/UPDATE 的 4 种拒绝模式约束在INSERT和UPDATE时实时生效实验中复现了 4 种典型拒绝1主键重复Primary Key ViolationINSERT INTO Student VALUES(20100001,李斌,男,20,CS,1001,0); -- ✅ 成功Sno 唯一 INSERT INTO Student VALUES(20100001,李斌,男,20,CS,1001,0); -- ❌ 失败主键重复2外键缺失Foreign Key ViolationINSERT INTO SC VALUES(20100001,9999,78); -- ❌ 失败Cno9999 不在 Course.Cno 中3CHECK 范围越界Check Constraint ViolationINSERT INTO SC VALUES(20100001,1,101); -- ❌ 失败Grade100 违反 CHECK(Grade0 AND Grade100)4UPDATE 主键冲突Update PK ConflictUPDATE Student SET Sno20100021 WHERE Sname 张立; -- ❌ 失败Sno20100021 已被 王敏 占用5.3 触发器机制初探实验虽未编码但为课程设计埋下伏笔实验三提到“了解触发器的机制和使用”虽未给出代码但为后续课程设计指明方向。例如可创建AFTER INSERT触发器当向Sc插入成绩时自动更新Student.Stotal总分CREATE TRIGGER trg_UpdateStotal ON SC AFTER INSERT AS BEGIN UPDATE Student SET Stotal Stotal (SELECT Score FROM inserted WHERE inserted.Sno Student.Sno) WHERE Sno IN (SELECT Sno FROM inserted); END;触发器在INSERT后执行inserted是内存表存新插入行。此触发器确保Stotal实时准确避免应用层多次UPDATE。6. 实战技巧与验证方法如何用 3 个命令验证你的数据库是否“真正跑通”6.1 一键验证表结构sp_help 与 sys.columns 的组合技光看CREATE TABLE语句不够必须确认 SQL Server 中实际结构。两个命令直击本质1sp_help快速查看表元数据EXEC sp_help Student;输出字段名、类型、长度、是否允许NULL、默认值、索引、约束。关键信息Sage是否为smallintSclass是否NOT NULLSno是否主键一目了然。2sys.columns编程式查询字段属性SELECT c.name AS column_name, t.name AS data_type, c.max_length, c.is_nullable, dc.definition AS default_value FROM sys.columns c JOIN sys.types t ON c.user_type_id t.user_type_id LEFT JOIN sys.default_constraints dc ON c.default_object_id dc.object_id WHERE c.object_id OBJECT_ID(Student) ORDER BY c.column_id;优势可写入脚本批量检查多张表is_nullable明确显示是否允许NULL。6.2 约束有效性验证DBCC CHECKCONSTRAINTS 与自定义测试用例约束是否生效不能只靠“没报错”要主动验证1DBCC CHECKCONSTRAINTS检查所有约束状态DBCC CHECKCONSTRAINTS(SC); -- 检查 SC 表所有约束 DBCC CHECKCONSTRAINTS; -- 检查整个数据库所有约束输出constraint_name,table_name,statusNO CHECK表示禁用CHECK表示启用。若status为NO CHECK约束形同虚设需ALTER TABLE SC CHECK CONSTRAINT SC_CHECK启用。2编写 3 行测试用例覆盖边界值为SC表CHECK(Grade0 AND Grade100)编写最小验证集-- 测试下界 INSERT INTO SC VALUES(20100001,1,0); -- ✅ 应成功 -- 测试上界 INSERT INTO SC VALUES(20100001,1,100); -- ✅ 应成功 -- 测试越界 INSERT INTO SC VALUES(20100001,1,101); -- ❌ 应失败这 3 行比任何文档都可靠。每次修改约束后必须运行此测试集。6.3 查询性能基线测试SET STATISTICS IO ON 与执行计划解读“查询慢”是假问题“为什么慢”才是真问题。用两行命令定位瓶颈1SET STATISTICS IO ON看物理读取SET STATISTICS IO ON; SELECT * FROM Student WHERE SdeptCS; SET STATISTICS IO OFF;输出Table Student. Scan count 1, logical reads 1, physical reads 0。logical reads 1表示从内存读取 1 页8KB极快若logical reads数千说明未走索引。2查看执行计划CtrlL识别红色警告在 SSMS 中执行查询按CtrlL显示图形化执行计划。关注是否有Table Scan全表扫描应为Index Seek索引查找。若WHERE SdeptCS出现Table Scan说明Sdept无索引需CREATE INDEX ix_Sdept ON Student(Sdept)。从那以后我每次写完CREATE TABLE都强制走一遍sp_help确认字段类型每次加CHECK约束必写三行测试用例边界值、正常值、越界值每次写SELECT必开SET STATISTICS IO ON看logical reads。这三步花不了 2 分钟却能避开 80% 的“数据库跑不通”问题。希望帮到你。本文还有配套的精品资源点击获取