View Query

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 ' ;

 

转载于:https://www.cnblogs.com/FYSUIT/archive/2010/12/11/1902851.html