数据库day-08
一、实验名称和性质
删除修改数据
验证 设计
二、实验目的
1．掌握数据操作-- 删除、修改；
三、实验的软硬件环境要求
硬件环境要求：
PC机（单机）
使用的软件名称、版本号以及模块：
Windows 10，SQL Server 2019
四、知识准备
1．数据删除的语句格式：
DELETE [FROM] 表名 [WHERE 字句]
3．据修改格式：
UPDATE 表名 SET 字段名=表达式 [,…] [WHERE 字句]
五、实验内容
1．数据的修改操作；
2．数据的删除操作；
六、验证性实验
（1）生成实验数据 （表名自己加学号后三位）
SELECT * INTO STUDENTAXXX FROM STUDENTXXX
SELECT * INTO SCOREAXXX FROM SCOREXXX WHERE DEGREE <0
INSERT SCOREAXXX SELECT * FROM SCOREXXX
--注：下面修改和删除数据，在STUDENTAXXX，SCOREAXXX中操作
（2）数据的修改删除
1. 把所有人的出生日期中的月份减1
UPDATE STUDENTAXXX set Sbirthday=DATEADD(MONTH, -1,Sbirthday)
2. 把陈文文 的出生日期中年加1，班级改为95033
UPDATE STUDENTAXXX set Sbirthday=DATEADD(year,1,Sbirthday),Class='95033' where Sname='陈文文'
3. 把95033班所有男生的出生日期中的日减1，女生的出生日期中的日加1
UPDATE STUDENTAXXX set Sbirthday=case ssex when '男' THEN DATEADD(DAY,-1,Sbirthday)
WHEN '女' THEN DATEADD(DAY,1,Sbirthday) END
where Class='95033'
4. 把课程号3-105的所有成绩减10
UPDATE SCOREAXXX SET Degree-=10 WHERE CNO='3-105'
5. 把课程计算机导论的所有成绩加10
UPDATE SCOREAXXX SET Degree+=10 WHERE CNO=(select cno from Course where Cname='计算机导论') --子查询
6. 把李君帅的所有成绩减10
UPDATE SCOREAXXX SET Degree=Degree- 10 FROM STUDENTAXXX S JOIN SCOREAXXX SC ON SC.Sno=S.Sno WHERE Sname='李君帅' --连接
7. 把李君帅的计算机导论成绩加10
UPDATE SCOREAXXX SET Degree=Degree+ 10 FROM STUDENTAXXX S JOIN SCOREAXXX SC ON SC.Sno=S.Sno
join Course C ON SC.CNO=C.CNO WHERE Sname='李君帅' AND Cname='计算机导论'
8. 删除学号109课程 3-245的成绩
delete from SCOREAXXX WHERE SNO='109' AND CNO='3-245'
9. 删除曾华庆的 数字电路 成绩
delete SCOREAXXX WHERE SNO=(select sno from STUDENTAXXX where sname='曾华庆' ) AND CNO=(select cno from Course where Cname='数字电路')
10.删除STUDENTAXXX的所有数据
TRUNCATE TABLE STUDENTAXXX
11.删除SCOREAXXX的所有数据
delete SCOREAXXX
12.其他，运行以下语句，观察结果，分别得到什么结论？
--1. SET ANSI_NULLS {ON | OFF}
SET ANSI_NULLS ON --默认
select * FROM SCOREXXX WHERE DEGREE <>NULL
select * FROM SCOREXXX WHERE DEGREE =NULL
select * FROM SCOREXXX WHERE DEGREE is not null
select * FROM SCOREXXX WHERE DEGREE is null
SET ANSI_NULLS OFF
select * FROM SCOREXXX WHERE DEGREE <>NULL
select * FROM SCOREXXX WHERE DEGREE =NULL
select * FROM SCOREXXX WHERE DEGREE is not null
select * FROM SCOREXXX WHERE DEGREE is null
SET ANSI_NULLS ON
--2.SET QUOTED_IDENTIFIER { ON | OFF }
SET QUOTED_IDENTIFIER ON --默认
SELECT SNO,'CNO',DEGREE FROM SCOREXXX
SELECT SNO,"CNO",DEGREE FROM SCOREXXX
SELECT SNO,[CNO],DEGREE FROM SCOREXXX
SET QUOTED_IDENTIFIER Off
SELECT SNO,'CNO',DEGREE FROM SCOREXXX
SELECT SNO,"CNO",DEGREE FROM SCOREXXX
SELECT SNO,[CNO],DEGREE FROM SCOREXXX
SET QUOTED_IDENTIFIER On
--3. top ... WITH TIES
select top 3 * from SCOREXXX
select top 3 WITH TIES * from SCOREXXX
select top 3 WITH TIES * from SCOREXXX order by sno
select top 30 percent * from SCOREXXX order by sno
select top 30 percent WITH TIES * from SCOREXXX order by sno
select top 20 percent WITH TIES * from SCOREXXX order by sno
--4. ANY, SOME, ALL
SEELCT * FROM SCOREXXX where Degree >= all(select degree from SCOREXXX )
SEELCT * FROM SCOREXXX where Degree >= all(select degree from SCOREXXX where Degree is not null )
SEELCT * FROM SCOREXXX where Degree < any(select degree from SCOREXXX where sno =’101’) and sno =’101’
SEELCT * FROM SCOREXXX where Degree < some(select degree from SCOREXXX where sno =’103’) and sno =’103’
七、设计性实验
1．实验要求
（1）重新生成实验数据（STUDENTAXXX，SCOREAXXX）
（2）删除修改数据(STUDENTAXXX，SCOREAXXX中操作，每题一个语句完成)
1. 把陈文文改名为陈文且出生日期中年月日都加1
UPDATE STUDENT023
SET Sname = '陈文',
Sbirthday = DATEADD(DAY, 1, DATEADD(MONTH, 1, DATEADD(YEAR, 1, Sbirthday)))
WHERE Sname = '陈文文'
2.把学号101所有成绩加10，最大不超过100
UPDATE SCORE023
SET Degree = CASE WHEN Degree + 10 > 100 THEN 100 ELSE Degree + 10 END
WHERE SNO = '101'
3. 把男生所有成绩加10%，女生所有成绩加10，最大不超过100
UPDATE SCORE023
SET Degree = CASE
WHEN Ssex = '男' THEN
CASE WHEN Degree * 1.1 > 100 THEN 100 ELSE ROUND(Degree * 1.1, 0) END
ELSE
CASE WHEN Degree + 10 > 100 THEN 100 ELSE Degree + 10 END
END
FROM SCORE023 SC
JOIN STUDENT023 ST ON SC.SNO = ST.SNO
4.把张旭 老师任课所有及格成绩减10，最低不小于60
UPDATE SCORE023
SET Degree = CASE WHEN Degree - 10 < 60 THEN 60 ELSE Degree - 10 END
WHERE CNO IN (SELECT CNO FROM teacher023 WHERE Tname = '张旭')
AND Degree >= 60
5. 把曾华庆同学 的张旭 老师任课所有及格成绩加10%，最大不超过100
UPDATE SCORE023
SET Degree = CASE WHEN Degree * 1.1 > 100 THEN 100 ELSE ROUND(Degree * 1.1, 0) END
WHERE SNO = (SELECT SNO FROM STUDENT023 WHERE Sname = '曾华庆')
AND CNO IN (SELECT CNO FROM teacher023 WHERE Tname = '张旭')
AND Degree >= 60
6. 删除成为null的成绩
DELETE FROM SCORE023 WHERE Degree IS NULL
7. 删除王芳芳 的操作系统成绩
DELETE FROM SCORE023
WHERE SNO = (SELECT SNO FROM STUDENT023 WHERE Sname = '王芳芳')
AND CNO = (SELECT CNO FROM COURSE023 WHERE Cname = '操作系统')
8.删除曾华庆同学 的张旭 老师任课所有成绩
DELETE FROM SCORE023
WHERE SNO = (SELECT SNO FROM STUDENT023 WHERE Sname = '曾华庆')
AND CNO IN (SELECT CNO FROM teacher023 WHERE Tname = '张旭')
9. 删除女生的计算机导论成绩
DELETE SCORE023
FROM SCORE023 SC
JOIN STUDENT023 ST ON SC.SNO = ST.SNO
JOIN COURSE023 C ON SC.CNO = C.CNO
WHERE ST.Ssex = '女' AND C.Cname = '计算机导论'
10.删除本月过生日的男同学信息
DELETE FROM STUDENT023
WHERE Ssex = '男'
AND MONTH(Sbirthday) = MONTH(GETDATE())
2.实验报告要求
通过本实验，我掌握了：
- 使用UPDATE语句修改表中的数据
- 使用DELETE语句删除表中的数据
- 复杂条件更新和删除的实现方法
- 多表连接在更新和删除中的应用
- 子查询在更新和删除中的应用
遇到的问题：
- 日期计算时需要注意边界情况
- 百分比计算时需要考虑四舍五入
- 多表连接更新时需要注意表之间的关系