SQL Server创建视图的语法:
CREATE VIEW
[ < database_name > .] [ < owner > .]
view_name [ ( column [ ,...n ] ) ]
[ WITH < view_attribute > [ ,...n ] ]
AS
select_statement
[ WITH CHECK OPTION ]
< view_attribute > ::=
{ ENCRYPTION | SCHEMABINDING |
VIEW_METADATA }
例1:建立显示年龄大于20岁的学生学号、姓名、性别等信息的S_view1
create view S_view1
as
select sno,sname,sex from s where age>20
例2:创建v_score1,要求基本表来源:S,C,SC;选择的字段为:S表中的sno、sname;C表中的cname及SC表中score;要求查询的数据为学号为20030001的学生的考试成绩。
Use s
create view v_score1
As
Select s.sno,s.sname,c.cname,sc.score
From s,c,sc
Where s.sno=sc.sno and c.cno=sc.cno and sno=“20030001”
在查询分析器中执行上面的程序,会生成视图v_score1。为了查看视图中的数据,在查询分析器中输入语句:
select * from v_score1。