1 ---001建立信息系学生视图-----------------------------------
2 USE Student_course
3 CREATE VIEW IS_student
4 AS
5 SELECT Sno,Sname,Sage
6 FROM Student
7 WHERE Sdept='IS';
8 SELECT * FROM Student;
9 SELECT * FROM IS_student;
10 ---002建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系学生----
11 CREATE VIEW IS_student01
12 AS
13 SELECT Sno,Sname,Sage
14 FROM Student
15 WHERE Sdept='IS'
16 WITH CHECK OPTION ;----
17 ---003建立信息选修了一号课程的学生视图
18 INSERT INTO SC VALUES ('200215125','1',85);
19 UPDATE Student
20 SET Sdept='IS'
21 WHERE Sno='200215125';
22
23 CREATE VIEW IS_S1(Sno,Sname,Grade)
24 AS
25 SELECT Student.Sno,Sname,Grade
26 FROM Student , SC
27 WHERE Sdept='IS' AND Cno='1'AND Student.Sno=SC.Sno;
28 DROP VIEW IS_S1;
29 SELECT * FROM IS_S1;
30 SELECT * FROM Student;
31 SELECT * FROM SC;
32
33 DELETE
34 FROM SC
35 WHERE Sno='200215125' AND Cno='1';
36
37 ---004 建立信息系选修了1号课程且成绩在90分以上的学生的视图。
38 UPDATE SC
39 SET Grade='98'
40 WHERE Sno='200215125';
41
42 CREATE VIEW IS_Grade90
43 AS
44 SELECT Student.Sno,Sname,Grade
45 FROM Student,SC
46 WHERE Sdept='IS' AND Cno='1' AND Grade>=90 AND Student.Sno=SC.Sno;
47 DROP VIEW IS_Grade90;
48 SELECT * FROM IS_Grade90;
49 ---005定义一个反映学生出生年份的视图-------------------------
50 CREATE VIEW BT_S1(Sno,Sname,S_birth)
51 AS
52 SELECT Sno,Sname,2010-Sage
53 FROM Student;
54 SELECT * FROM BT_S1;
55 SELECT * FROM Student;
56 ---006将学生的学号及平均成绩定义一个视图-----------------
57 CREATE VIEW Sno_AvgGrade(Sno,AvgGrade)
58 AS
59 SELECT Sno,AVG(Grade)
60 FROM SC
61 GROUP BY SC.Sno;
62 DROP VIEW Sno_AvgGrade;---删除视图
63 SELECT * FROM Sno_AvgGrade;
64 SELECT * FROM SC;
65 ---007将学生表中所有女生定义为一个视图
66 CREATE VIEW Student_SsexF
67 AS
68 SELECT Sno,Sname,Ssex
69 FROM Student
70 WHERE Ssex='女';
71 DROP VIEW Student_SsexF;
72 SELECT * FROM Student_SsexF;
73
74 CREATE VIEW F_Student(F_Sno,name,sex,age,dept)
75 AS
76 SELECT Sno,Sname,Ssex,Sage,Sdept--可以用*表示所有
77 FROM Student
78 WHERE Ssex='女';
79 SELECT * FROM F_Student;
80
81 DELETE
82 FROM F_Student
83 WHERE sex='女';---从视图中删除数据，原始表中的数据也会被删除
84 DELETE FROM Student WHERE sno='200215129';
85 INSERT INTO F_Student(F_Sno,name,sex,age,dept) VALUES('200215129','李刚','女',22,'IS') ;
86 /*向视图插入一个值（记录），如此记录元组条目与视图元组条目不相符，
87 则此记录被插到原始数据中，而视图中却没有此记录.反之，如果元组相同，
88 则视图中更新了此记录，原始数据也被更新*/
89 SELECT * FROM Student;
90 ---在视图中更新数据，原始数据也被更新
91 UPDATE F_Student
92 SET age='25'
93 WHERE F_Sno='200215129';
94 ---在原始数据中更新了数据，相应视图数据也被更新
95 UPDATE Student
96 SET Sage='17'
97 WHERE Sno='200215123';
