视图

实际上是一个查询语句, 如果将子查询保存为视图，

就可以将子查询的结果当作数据表使用从而来简化查询语句

引言
- 例1 查询参加"数据库技术"课程的考试的学生学号、姓名、班级、分数
  - 需要连接学生表, 成绩表和课程表
- 查询航天班各课程的考试信息, 显示学生的学号、姓名、课程名称、分数
  - 也需要连接学生表, 成绩表和课程表
- 由于数据库连接查询语句麻烦,可以先写一个一般的等值连接, 将三个表连接起来

use teachingselect * from Studentselect * from Courseselect * from Study--连接学生表, 成绩表, 课程表Select a.sclass, a.Snumb, a.sname, a.sgender,c.cname, b.score, c.chours, c.creditfrom Student as aINNER JOIN Study as b  on a.snumb = b.snumbINNER JOIN Course as c on b.cnumb= c.cnumb--由于上述查询经常使用, 所以将之保存为视图--CREATE VIEW 视图名 [(字段别名列表)]--AS--查询Create View examinfoASSelect a.sclass, a.Snumb, a.sname, a.sgender,c.cname, b.score, c.chours, c.creditfrom Student as aINNER JOIN Study as b  on a.snumb = b.snumbINNER JOIN Course as c on b.cnumb= c.cnumb--加别名GOCreate View examinfo2(班级,学号,姓名,性别,课程,成绩,学时,学分)ASSelect a.sclass, a.Snumb, a.sname, a.sgender,c.cname, b.score, c.chours, c.creditfrom Student as aINNER JOIN Study as b  on a.snumb = b.snumbINNER JOIN Course as c on b.cnumb= c.cnumb--4.使用视图--查询所有学生的所有课程的考试成绩--显示班级,学号,姓名,性别,课程,成绩,学时,学分select * from examinfo2--查询参加"c++"课程的考试的学生的--班级,学号,姓名,性别,课程,成绩,学时,学分select * from examinfo2where 课程 = 'c++'--查询"航天"班各课程考试的信息,显示学号,姓名,课程名称,分数Select Snumb, Sname, Cname, ScoreFrom examinfowhere Sclass = '航天'--例2 查询平均年龄大于20岁的班级的学生信息Select *From Studentwhere sclass in (select sclass as 班级 from Studentgroup by sclasshaving avg(sage) >= 20)--使用视图--创建视图Create View myClassASselect Sclass as avgage from Studentgroup by Sclasshaving AVG(sage) >= 20--使用视图Select * From StudentWhere sclass in (select * from myClass)--删除视图Drop View Myclass