数据库：数据更新和视图

1 实验目的

（1）掌握SQL语言的数据更新功能：

update 语句用于对表进行更新

delete 语句用于对表进行删除

       insert 语句用于对表进行插入

（2）掌握对视图的操作：

视图的定义

视图的更新

       基于视图的查询

2 实验任务

1.将一个新学生元组（学号：95007；姓名：张娜丽；性别：女；所在系：IS；年龄：18岁）插入到Student表中。

2.插入一条选课记录，插入一条选课记录( '95007'，'1 ')。

3.对每一个系，求学生的平均成绩，并把结果存入数据库。

4.将全体学生的年龄增加一岁。

5.将信息系全体学生的成绩置零。

6.删除95001学生的记录。

7.删除信息系所有学生的选课记录。

8.建立计算机学生的视图CS_S。（视图中包括学号，姓名，年龄，系），并要求进行修改和插入操作时仍需保证该视图只有计算机系的学生。（视图中包含的属性名称自拟）

9.向CS_S视图中插入分别插入两条信息,如果不能插入说明原因。

10.利用CS_S视图完成以下查询：

查询计算机学生年龄小于20岁的学生（学号、年龄）

查询计算机系选修了2号课程的学生(学号、姓名)。

拓展练习：

利用实验二建立的BSXT数据库，完成以下操作。

基于BSXT数据库，并完成以下查询：

1）向选题表中插入一条记录（‘H06’,‘学生成绩管理系统’，‘已选’，‘T03’,’0706034107’）

2）将教师李霞的学历改为“博士”。

3）删除“1组”所有学生的选题情况。

4）建立选题学生选题情况的视图（包括学号，姓名，题目名称，指导教师姓名，指导老师联系电话）

5）利用4）建立的视图查询所有杨坚老师所带学生的学号，姓名和题目名称。

3 相关知识

       数据更新操作有三种：向表中添加若干行数据、修改表中的数据和删除表中的若干行数据。

       修改操作又称为更新操作，其语句一般格式为：

       UPDATE <表名>

       SET<列名>=<表达式>[,<列名>=<表达式>]……

       [WHERE<条件>];

其功能是修改指定表中满足WHERE子句条件的元组。其中SET子句给出<表达式>的值用于取代相应的属性列值；如果省略WHERE子句，则表示要修改表中所有元组。

       删除语句一般格式为：DELETE FROM <表名> [WHERE<条件>];DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组；如果省略WHERE子句，则表示要删除表中所有元组，但表的定义仍在表中。也就是说DELETE语句删除的是表中的数据，而不是关于表的定义。

       视图是从一个或几个基本表中导出的表，它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中，所有一旦基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图，但对新的视图的更新操作有一定的限制。

4 实验内容与实验结果

INSERT INTO Student
VALUES('95007','张娜丽','女','18','IS');
INSERT INTO SC(Sno,Cno)
VALUES('95007','1');
CREATE TABLE Dept_age
       (Sdept CHAR(15),
	    Avg_age SMALLINT);
INSERT INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,Avg(CAST(Sage as int))
FROM Student
GROUP BY Sdept;
UPDATE Student
SET Sage=Sage+1;
UPDATE SC
SET Grade=0
WHERE 'CS'=(SELECT Sdept
            FROM Student
			WHERE Student.Sno = SC.Sno);
INSERT INTO Student
VALUES('95001','沈彤彤','女','20','IS');
DELETE
FROM Student
WHERE Sno = '95001';
DELETE
FROM SC
WHERE  'CS'=(SELECT Sdept
             FROM Student
             WHERE Student.Sno=SC.Sno);
CREATE VIEW CS_S
AS
SELECT Sno,	Sname,Sage,Sdept
FROM Student
WHERE Sdept='CS'
WITH CHECK OPTION;
INSERT INTO CS_S
VALUES('95002','李华华','18','IS');
INSERT INTO CS_S
VALUES('95003','王冬冬','20','CS');
SELECT Sno,Sage
FROM CS_S
WHERE Sage<=20
SELECT CS_S.Sno,Sname
FROM CS_S,SC
WHERE Cno='1';

不能插入的原因：因为定义视图时增加了WITH CHECK OPTION；更新视图不满足视图定义的谓词条件。

数据库BSXT：

INSERT INTO Topic(Hno,Hname,Hstatus,Tno,Sno)
VALUES('H06','学生成绩管理系统','已选','T03','0706034107');

UPDATE Teacher
SET Tdegree = '博士'
WHERE Tname = '李霞';

DELETE
FROM Topic
WHERE Topic.Sno in(SELECT Student.Sno
                   FROM Student
				   WHERE Sgroup = '1');

CREATE VIEW Condition
AS
SELECT Student.Sno,Sname,Tname,Hname,Ttel
FROM Student,Topic,Teacher
WHERE Topic.Sno = Student.Sno AND Topic.Tno = Teacher.Tno
WITH CHECK OPTION;

SELECT Sno,Sname,Hname
FROM Condition
WHERE Tname = '杨坚';