数据库实验五——数据更新

实验报告

【实验名称】 实验5 数据更新
【实验目的】
（1）熟练掌握使用交互式方式编辑表中的数据。
（2）能够运用T-SQL语句对表中的数据进行插入、修改和删除操作。

【实验原理】
（1）插入数据语句INSERT
（2）修改数据语句UPDATE
（3）删除数据语句DELETE

【实验内容】

实验内容(1)

针对教材中的学生_课程数据库，进行数据更新(有些删除需要分步进行)：
1.将选修了3号课程的学生的成绩加5分 ；

UPDATE SC001
SET Grade=Grade+5
WHERE Cno='3'

2.插入一条记录(‘201215128’， ‘张成’， ‘男’，19，‘MA’)到Student表中 ；

INSERT
INTO S001
VALUES('201215128','张成', '男',19,'MA')

3.插入‘201215128’选修‘1’号课程的选课记录；

INSERT
INTO SC001(Sno,Cno)
VALUES('201215128','1')

4.将计算机系学生的年龄加1岁；

UPDATE S001
SET Sage=Sage+1
WHERE Sdept='CS'

5.修改学号为‘ 201215128’的学生信息，将姓名改为‘王红’，性别改为‘女’；

UPDATE S001
SET Sname='王红',Ssex='女'
WHERE Sno='201215128'

6.统计所有学生选课的平均成绩，保存到一张表中（先建表）；

INSERT
INTO A001
SELECT C001.Cno,Cname,AVG(Grade)
FROM C001,SC001
WHERE SC001.Cno IN
(	SELECT SC001.Cno 
	FROM C001,SC001 
	WHERE C001.Cno=SC001.Cno
) AND C001.Cno = SC001.Cno
GROUP BY C001.Cno,Cname

7.统计所有课程的最高分、最低分和平均分，保存到一张表中（先建表）；

INSERT
INTO G001
SELECT C001.Cno,MAX(Grade),MIN(Grade),AVG(Grade)
FROM C001,SC001
WHERE SC001.Cno IN
(	SELECT SC001.Cno 
	FROM C001,SC001 
	WHERE C001.Cno=SC001.Cno
) AND C001.Cno = SC001.Cno
GROUP BY C001.Cno,Cname

8.删除计算机系与信息系的学生信息（选课信息如何处理？）；

DELETE
FROM SC001
WHERE Sno IN
(
	SELECT Sno
	FROM S001
	WHERE Sdept='CS' OR Sdept='IS'
)

DELETE
FROM S001
WHERE Sdept='CS' OR Sdept='IS'

9.删除选修了“数据库”课程的学生选课信息；

DELETE
FROM SC001
WHERE Cno IN
(
	SELECT Cno
	FROM C001
	WHERE Cname='数据库' 
)

实验内容(2)

针对第二章课后练习6的SPJ数据库，进行各种数据更新（有些删除需要分步进行）。
1.插入一个供应商“S6”的信息，具体参数自己设定；

INSERT
INTO S001 
VALUES('S6','太阳升','40','重庆')

2.插入一个零件“P7”的信息，具体参数自己设定；

INSERT
INTO P001 
VALUES('P7','螺母','绿','25')

3.插入一个项目“J8”的信息，具体参数自己设定；

INSERT
INTO J001 
VALUES('J8','铁塔厂','重庆')

4.插入“S6”供应项目“J8”零件“P7”的信息，供应量为500；

INSERT
INTO SPJ001 
VALUES('S6','P7','J8',500)

5.计算各个供应商供应各种零件的平均供货量，并将结果存放在数据库中（先建表）；

INSERT
INTO A001
SELECT S001.SNO,P001.PNO,AVG(QTY)
FROM S001,P001,SPJ001
WHERE S001.Sno IN
(	SELECT S001.Sno 
	FROM S001,SPJ001 
	WHERE S001.Sno=SPJ001.Sno
) AND S001.Sno=SPJ001.Sno AND SPJ001.PNO=P001.PNO
GROUP BY S001.SNO,P001.PNO

6.将所有供应工程“J1”零件“P2”的供应商改为S1；

UPDATE SPJ001
SET SNO='S1'
WHERE JNO='J1' AND PNO='P2'

7.修改供应量大于100的供应详情，将供应量增加100；

UPDATE SPJ001
SET QTY=QTY+100
WHERE QTY>100

8.将“J1”项目的最大供应量降为原来的一半；

UPDATE SPJ001
SET QTY=QTY/2
WHERE JNO='J1' AND QTY IN
(
	SELECT MAX(QTY)
	FROM SPJ001
	WHERE JNO='J1'
)

9.删除“S6”供应商信息（注意参照完整性）；

DELETE 
FROM SPJ001
WHERE SNO='S6'

DELETE
FROM S001
WHERE SNO='S6'

10.删除项目“J7”的所有供应信息；

DELETE 
FROM SPJ001
WHERE JNO='J7'

DELETE
FROM J001
WHERE JNO='J7'

11.删除 “东方红”供应商的所有供应信息；

DELETE 
FROM SPJ001
WHERE SNO IN
(
	SELECT SPJ001.SNO
	FROM SPJ001,S001
	WHERE SPJ001.SNO=S001.SNO AND SNAME='东方红'
)      

12.删除使用了“北京”供应商供应的“螺丝刀”零件的供应信息和工程项目信息；

DELETE
FROM J001
WHERE JNO IN
(
	SELECT SPJ001.JNO
	FROM SPJ001,J001,S001,P001
	WHERE SPJ001.JNO=J001.JNO AND SPJ001.SNO=S001.SNO AND S001.CITY='北京' AND SPJ001.PNO=P001.PNO AND PNAME='螺丝刀'
)

DELETE 
FROM SPJ001
WHERE SNO IN
(
	SELECT SPJ001.SNO
	FROM SPJ001,S001
	WHERE SPJ001.SNO=S001.SNO AND CITY='北京'
) AND PNO IN
(
	SELECT SPJ001.PNO
	FROM SPJ001,P001
	WHERE SPJ001.PNO=P001.PNO AND PNAME='螺丝刀'
)