SQL实验 数据的插入、修改和删除操作

一、实验目的

1．掌握Management Studio的使用。

2．掌握SQL中INSERT、UPDATE、DELETE命令的使用。

二、实验内容及要求

用SQL语句完成下列功能。使用数据库为SCHOOL数据库。

1、新开设一门课程，名叫网络安全与防火墙，学时40，编号为“0118”，主要介绍网络的安全与主要的防火墙软件。    

--新开设一门课程，名叫网络安全与防火墙，学时40，编号为“0118”，主要介绍网络的安全与主要的防火墙软件。
 
INSERT 
 
INTO Course(Course_id,Course_name,Course_hour,Introduce)
 
VALUES('0118','网络安全与防火墙',40,'介绍网络的安全与主要的防火墙软件')

2、更新所有职称为“助教”的教师职称为“助理教师”。

--更新所有职称为“助教”的教师职称为“助理教师”。
 
UPDATE Teacher
 
SET TechPost='助理教师'
 
WHERE TechPost='助教'

3、学号为“130101005”的学生的“数据结构”课程成绩改为80分。

UPDATE StudentGrade
 
SET Grade=80
 
WHERE Stu_id=130101005
 
AND Course_id IN 
 
(SELECT Course_id
 
From Course
 
WHERE Course_name='数据结构')

4、删除“利旭勉”的选课记录。

--删除“利旭勉”的选课记录
 
DELETE
 
FROM StudentGrade
 
WHERE Stu_id IN
 
(SELECT Stu_id
 
FROM Student
 
WHERE Stu_name='利旭勉')

5、先建立monitor表，其结构与student表大致一样．包含student表的学号、姓名、性别和班级编号，然后把各班班长的个人信息插入到monitor表中。

--先建立monitor表，其结构与student表大致一样．包含student表的学号、姓名、性别和班级编号，然后把各班班长的个人信息插入到monitor表中。
 
--建立monitor表
 
CREATE TABLE Monitor(
 
Stu_id varchar(10) NOT NULL,
 
Stu_name varchar(8) NOT NULL,
 
Stu_sex varchar(2) DEFAULT '男'  NOT NULL,
 
Age  AS (datepart(year,getdate())-datepart(year,Birthday)),  --计算列
 
Birthday smalldatetime NOT NULL,
 
Phone varchar(8) NULL,
 
Address varchar(100) NULL,
 
Class_id varchar(4) NULL,
 
 CONSTRAINT PK_Monitor PRIMARY KEY CLUSTERED (Stu_id ASC) --主键，聚集索引（clustered）
 
 ) 
 
 --筛选班长
 
 INSERT
 
 INTO Monitor(Stu_id,Stu_name,Stu_sex,Birthday,Phone,Address,Class_id)
 
 SELECT Stu_id,Stu_name,Stu_sex,Birthday,Phone,Address,Student.Class_id
 
 FROM Student JOIN Class ON Student.Stu_id=Class.Monitor
 
 WHERE Class.Monitor IS NOT NULl

6、删除studentgrade表中学号以“2017”开头的学生选修课程的记录。

 --删除studentgrade表中学号以“2017”开头的学生选修课程的记录。
 
 --材料未提供学号以“2017”开头的学号，因此将其换为“14050”
 
 DELETE
 
 FROM StudentGrade
 
 WHERE Stu_id LIKE '14050%'

7、删除课程名为“C语言”的课程信息和所有这门课的选课信息。（不用级联删除，因级联需在创建表时定义。考虑各个表删除的先后顺序）

 --删除课程名为“C语言”的课程信息和所有这门课的选课信息。（不用级联删除，因级联需在创建表时定义。考虑各个表删除的先后顺序）
 
 --材料未提供“C语言”，因此将其换位“数据结构”
 
 -- 删除与 Course 表相关的成绩记录
 
 --未能删除Course表对应数据与 REFERENCE 约束"FK_CourseTeacher_Course"冲突
 
 --解决方法：先删除CourseTeacher表中对应数据，再删除Course表中对应的数据（tip：不能合并表，因为合并表后要考虑删除的先后顺序）
 
DELETE 
 
FROM StudentGrade
 
WHERE Course_id IN (
 
    SELECT Course_id
 
    FROM Course
 
    WHERE Course_name = '数据结构'
 
)
 
 
 
-- 删除 CourseTeacher 表中的对应记录
 
DELETE 
 
FROM CourseTeacher
 
WHERE Course_id IN (
 
SELECT Course_id
 
FROM Course
 
WHERE Course_name = '数据结构'
 
)
 
---- 删除 Course 表中的对应记录
 
DELETE 
 
FROM Course
 
WHERE  Course_name = '数据结构'

8、把数据库中学生表学号为“130101005”的学生的学号改为“130101006”，请关注是否能修改成功。为什么？

--把数据库中学生表学号为“130101005”的学生的学号改为“130101006”，请关注是否能修改成功。为什么？
 
UPDATE Student
 
SET Stu_id='130101006'
 
WHERE Stu_id='130101005'
不能修改成功，不能在对象“dbo.Student”中插入重复键。重复键值，因为违反了 PRIMARY KEY 约束