用实验课速通SQLServer期末考点二

一、实验内容

SQL Server 2017 环境下教学信息管理系统核心表数据的插入、更新与删除操作。

二、实验目的

1. 掌握 SQL Server 中INSERT语句的用法，能向表中插入单条或多条合法数据；

2. 掌握UPDATE语句的用法，能根据条件修改表中的数据，避免全表更新；

3. 掌握DELETE语句的用法，能根据条件删除表中的数据，理解外键约束对删除操作的影响；
   结合教学信息管理系统业务场景，确保数据操作后表间数据的一致性。

三、实验任务

向核心表插入合法数据

• 向Teacher表插入至少 3 条教师数据（需包含不同职称，如教授、副教授、讲师）；

• 向Student表插入至少 5 条学生数据（需包含不同院系、性别）；

• 向Course表插入至少 4 条课程数据（需关联Teacher表中已存在的Tno，确保外键合法）。

DELETE FROM SC;
DELETE FROM Teacher;
DELETE FROM Student;
DELETE FROM Course;
-- 切换到TeachingSysDB数据库
USE TeachingSysDB;
GO
-- 1. 向Teacher表插入教师数据（先插入Teacher表，避免Course表外键约束）
INSERT INTO Teacher(Tno, Tname, Tsex, Ttitle, Tdept)
VALUES 
('T2023001', '王教授', '男', '教授', '计算机学院'),
('T2023002', '李老师', '女', '讲师', '数学学院'),
('T2023003', '张副教授', '男', '副教授', '文学院'),
('T2023004', '刘老师', '女', '讲师', '计算机学院');
GO
-- 2. 向Student表插入学生数据
INSERT INTO Student(Sno, Sname, Ssex, Sage, Sdept)
VALUES 
('2023000101', '张三', '男', 20, '计算机学院'),
('2023000102', '李四', '女', 19, '数学学院'),
('2023000103', '王五', '男', 18, '文学院'),
('2023000104', '赵六', '女', 21, '计算机学院'),
('2023000105', '孙七', '男', 19, '数学学院'),
('2023000106', '周八', '女', 17, '文学院');  -- 年龄17岁，后续用于删除测试
GO
-- 3. 向Course表插入课程数据（关联Teacher表已存在的Tno）
INSERT INTO Course(Cno, Cname, Ccredit, Tno)
VALUES 
('CS101', '数据库原理', 4, 'T2023001'),  -- 关联王教授（T2023001）
('MA101', '高等数学', 5, 'T2023002'),    -- 关联李老师（T2023002）
('CH101', '大学语文', 2, 'T2023003'),    -- 关联张副教授（T2023003）
('CS102', 'Python编程', 3, 'T2023004');  -- 关联刘老师（T2023004）
GO
-- 验证插入结果：查询各表数据
SELECT * FROM Teacher;
SELECT * FROM Student;
SELECT * FROM Course;
GO

根据业务需求更新数据

• 将 “计算机学院” 的所有学生年龄增加 1 岁；

• 将教师编号为 “T2023001” 的教师职称从 “讲师” 更新为 “副教授”；

• 将课程名称为 “数据库原理” 的课程学分调整为 3 分。

-- 1. 将“计算机学院”的所有学生年龄增加1岁（带WHERE条件，避免全表更新）
UPDATE Student
SET Sage = Sage + 1
WHERE Sdept = '计算机学院';
GO
-- 2. 将教师编号“T2023001”的职称更新为“副教授”（按主键更新，精准定位）
UPDATE Teacher
SET Ttitle = '副教授'
WHERE Tno = 'T2023001';
GO
-- 3. 将课程名称“数据库原理”的学分调整为3分（按唯一字段更新）
UPDATE Course
SET Ccredit = 3
WHERE Cname = '数据库原理';
GO
-- 验证更新结果：查询修改后的数据
SELECT Sno, Sname, Sage, Sdept FROM Student WHERE Sdept = '计算机学院';  -- 查看年龄是否+1
SELECT Tno, Tname, Ttitle FROM Teacher WHERE Tno = 'T2023001';          -- 查看职称是否更新
SELECT Cname, Ccredit FROM Course WHERE Cname = '数据库原理';            -- 查看学分是否调整
GO

根据条件删除数据

• 删除年龄小于 18 岁的学生记录（若存在）；

• 删除 “文学院” 未关联课程的教师记录（需先确认无课程关联，避免外键约束报错）；

• 尝试删除已关联课程的教师记录，观察外键约束的影响。

-- 1. 删除年龄小于18岁的学生记录（先查询符合条件的记录，再删除）
SELECT * FROM Student WHERE Sage < 18;  -- 先确认待删除数据（周八，Sno=2023000106）
DELETE FROM Student
WHERE Sage < 18;
GO
-- 2. 删除“文学院”未关联课程的教师记录（先判断是否有关联，再删除）
-- 步骤1：查询“文学院”教师关联的课程数量
SELECT t.Tno, t.Tname, COUNT(c.Cno) AS 关联课程数
FROM Teacher t
LEFT JOIN Course c ON t.Tno = c.Tno
WHERE t.Tdept = '文学院'
GROUP BY t.Tno, t.Tname;
-- 步骤2：若关联课程数为0，删除该教师（假设张副教授无关联课程，实际需根据数据判断）
DELETE FROM Teacher
WHERE Tdept = '文学院' 
  AND Tno NOT IN (SELECT DISTINCT Tno FROM Course);  -- 排除已关联课程的教师
GO
-- 3. 尝试删除已关联课程的教师记录（测试外键约束）
-- 教师T2023002关联“高等数学”课程，删除会触发外键约束报错
DELETE FROM Teacher
WHERE Tno = 'T2023002';  -- 报错：DELETE 语句与 REFERENCE 约束"FK__Course__Tno__..."冲突
GO
-- 验证删除结果：查询剩余数据
SELECT * FROM Student;  -- 确认年龄<18的学生已删除
SELECT * FROM Teacher WHERE Tdept = '文学院';  -- 确认未关联课程的教师已删除
GO

验证数据操作结果

使用SELECT语句查询各表数据，确认插入、更新、删除操作是否生效；对比操作前后的数据变化，确保无数据不一致问题。

四、实验结果与分析

1. 数据插入结果：通过SELECT查询确认，Teacher表插入 4 条教师数据、Student表插入 6 条学生数据、Course表插入 4 条课程数据，所有数据均符合表的约束（如外键Tno存在于Teacher表），无插入失败；

2. 数据更新结果：“计算机学院” 学生年龄均增加 1 岁（如张三从 20 岁变为 21 岁），教师 “T2023001” 职称从 “教授” 更新为 “副教授”，“数据库原理” 课程学分从 4 分调整为 3 分，更新操作仅影响目标数据，无全表更新风险；
   数据删除结果：年龄小于 18 岁的学生（周八）成功删除，“文学院” 未关联课程的教师（若存在）成功删除；尝试删除已关联课程的教师（T2023002）时，SQL Server 抛出 “外键约束冲突” 错误，证明外键约束有效，避免了表间数据不一致。

五、实验总结

1. 本次实验掌握了 SQL Server 中INSERT（单条 / 多条插入）、UPDATE（带条件精准更新）、DELETE（带条件安全删除）语句的用法，理解了 “先查询后操作” 的重要性（避免误操作）；

2. 认识到外键约束对删除操作的限制：当父表（Teacher）记录被子表（Course）关联时，无法直接删除父表记录，需先删除子表关联记录或修改外键级联策略，这是保障数据一致性的关键；

3. 遇到的问题：更新数据时忘记加WHERE条件，导致全表数据被修改，通过 “事务回滚”（后续实验会学习）或重新插入数据解决；删除数据时未判断关联关系，触发外键报错，通过LEFT JOIN查询关联数量后解决。