河南大学数据库实验6

1．根据EDUC数据库，按如下要求设计视图：

1）基于单个表按投影操作定义视图。

举例：定义一个视图用以查看所有学生的学号、姓名和年龄。

2）基于单个表按选择操作定义视图。

举例：定义一个满足性别=‘男’的学生的所有信息的视图。

3）基于多个表根据连接操作定义视图。

举例：定义一个视图用以查看所有学生的学号、姓名、课名、成绩。

4）基于多个表根据嵌套查询定义视图。

举例1：定义一个比所有‘CS’系的学生年龄都小的学生的信息的视图

举例2：定义一个视图用以查看年龄大于该系平均年龄的学生的学号、姓名。

5）定义含有虚字段（即基本表中原本不存在的字段）的视图。

举例：定义一个视图用以查看所有学生的学号、姓名、出生年份。

2．根据EDUC数据库，分析如下问题：

1）举例说明，在定义的视图上进行查询、插入、更新和删除操作，分情况（查询、更新）讨论哪些操作可以成功完成，哪些不能成功完成，并分析原因。

2）举例说明，WITH CHECK OPTION语句的作用。

分别给出对应题目的代码及运行结果截图：

1、

1．根据EDUC数据库，按如下要求设计视图：

1）基于单个表按投影操作定义视图。

举例：定义一个视图用以查看所有学生的学号、姓名和年龄。

CREATE view student_1

SELECT Sno,Sname,Sage

FROM student;

2）基于单个表按选择操作定义视图。

举例：定义一个满足性别=‘男’的学生的所有信息的视图。

CREATE VIEW student_2

SELECT *

FROM student

WHERE Ssex='男';

3）基于多个表根据连接操作定义视图。

举例：定义一个视图用以查看所有学生的学号、姓名、课名、成绩。

CREATE VIEW student_3

SELECT student.Sno,Sname,Cname,Grade

FROM sc,student,course

WHERE sc.Sno=student.Sno AND sc.Cno=course.Cno;

4）基于多个表根据嵌套查询定义视图。

举例1：定义一个比所有‘CS’系的学生年龄都小的学生的信息的视图

CREATE VIEW student_4

SELECT *

FROM student

WHERE Sage<ALL

(SELECT Sage

FROM student

WHERE Sdept='CS');

举例2：定义一个视图用以查看年龄大于该系平均年龄的学生的学号、姓名。

CREATE VIEW student_5(Sno,Sname)

SELECT Sno,Sname

FROM student s1

WHERE Sage>

(SELECT AVG(Sage)

FROM student s2

WHERE s1.Sdept=s2.Sdept);

5）定义含有虚字段（即基本表中原本不存在的字段）的视图。

举例：定义一个视图用以查看所有学生的学号、姓名、出生年份。

CREATE VIEW student_6(Sno,Sname,createdate)

SELECT Sno,Sname,YEAR(NOW())-Sage

FROM student;

2、

2．根据EDUC数据库，分析如下问题：

一．1号视图

1.在1号视图中找出年龄大于19岁的学生。

SELECT *

FROM student_1

WHERE Sage>19;

2.在1号视图中插入一名学号‘01’，姓名‘真烦’，年龄‘100’的学生。

INSERT

INTO student_1

VALUES('01','真烦','100');

3.在1号视图中将‘01’号学生的姓名改为‘真麻烦’。

UPDATE student_1

SET Sname='真麻烦'

WHERE Sno='01';

4.在1号视图中删除学号为‘01’的学生。

DELETE

FROM student_1

WHERE Sno='01';

二．2号视图

1.在2号视图中查询‘CS’系的学生。

SELECT *

FROM student_2

WHERE Sdept='CS';

2.在2号视图中插入两名学生记录，信息分别为：‘01’，‘麻烦’,’男’,’10’,’IS’。和‘02’，‘坑人’，‘女’，‘111’，‘AS’。

INSERT

INTO student_2

VALUES('01','麻烦','男','10','IS'),

('02','坑人','女','111','AS');

3.在2号视图中，将01号学生的系名改为‘QW’。

UPDATE student_2

SET Sdept='QW'

WHERE Sno=01;

4.在2号视图中，删除01号学生记录。

DELETE

FROM student_2

WHERE Sno='01';

三．3号视图。

1.在3号视图中查询成绩大于85的学生的信息。

SELECT *

FROM student_3

WHERE Grade>85;

2.在3号视图中，插入一名学号为‘011’，姓名为‘累死了’，课名为‘数据库’，成绩为‘100’的学生。

INSERT

INTO student_3(Sno,Sname,Cname,Grade)

VALUES('011','累死了','数据库',100);

3.在3号视图中，将李勇的数学成绩改为90。

UPDATE student_3

SET Grade='90'

WHERE Sname='李勇' AND Cname='数学';

4．在3号视图中，删除李勇的信息系统成绩。

DELETE

FROM student_3

WHERE Sname='李勇' AND Cname='信息系统';

四．4号视图。

1.在4号视图中，查询所有男生的信息。

SELECT *

FROM student_4

WHERE Ssex='男';

2.在4号视图中，插入一名学号为‘007’，姓名为‘要死人了’，年龄为‘1’的学生记录。

INSERT

INTO student_4(Sno,Sname,Sage)

VALUES('007','要死人了',1);

3.在4号视图中，将王敏的系名改为‘QQ’。

UPDATE student_4

SET Sdept='QQ'

WHERE Sname='王敏';

4.在4号视图中，删除年龄小于10岁的学生的记录。

DELETE

FROM student_4

WHERE Sage<10;

五．5号视图。

1.查询所有学生的记录。

SELECT *

FROM student_5;

2.插入‘007’‘王者’的学生记录。

INSERT

INTO student_5

VALUES('07','王者');

3.5号视图中，将所有学生的名字改为‘啊啊啊啊啊’。

UPDATE student_5

SET Sname='啊啊啊啊啊';

4.删除男生的记录。

DELETE

FROM student_5

WHERE Sname='李勇';

六．6号视图。

1.查询出生年份在2005年及之前的学生记录。

SELECT *

FROM student_6

WHERE createdate<='2005';

2.插入一个‘08’，‘终于写完了’，‘1945’的学生记录。

INSERT

INTO student_6

VALUES('08','终于写完了',1945);

3.将王者同学的出生日期改为1945。

UPDATE student_6

SET createdate='1945'

WHERE Sname='王者';

4.删除王者同学的纪录。

DELETE

FROM student_6

WHERE Sname='王者';

分情况讨论：

查询时：只有满足建立视图时的子查询的条件的记录才能被查询到。

更新时：基于单个表的投影、选择操作建立的视图可以更新。基于多个表的连接操作建立的视图无法插入和删除，可以修改。原因：插入和删除会同时涉及多个表，所以不行。基于多个表的嵌套查询定义的视图不能插入和删除，部分可插入。原因：涉及到多个表。定义含有虚字段的视图不能插入和更新，可以删除。原因：视图中含有不存在于表中的虚字段。

2）举例说明，WITH CHECK OPTION语句的作用。

定义一个和2号视图相同但带有with check option的视图。

CREATE VIEW student_22

SELECT *

FROM student

WHERE Ssex='男'

WITH CHECK OPTION;