sql中视图的简单应用实例



---创建视图(单表)

 select   from  work_view
 
 CREATE VIEW work_view
 as  select  from Table_V1
 
 select   from  work_view2
 
 create view work_view2(ID,name,address)
 as
 select ID,name,address  from dbo.ST_User
 ---创建视图(多表查询)
select   from  work_view3
 
CREATE  VIEW work_view3(ID,NAME,address)
AS
SELECT a.id,a.name ,b.id as expr1
FROM ST_User as a,dbo.ST_User2 as b
WHERE a.id=b.id

select  from dbo.work_view3
 --删除视图
 
 Drop view dbo.work_view3
 --修改视图
alter view ST_User2 as

select  from ST_User
select  from ST_User2
--内连接
select  a.ID,a.name, a.address,a.worker,b.ID,b.worker,b.address  from   ST_User as a   inner   join   ST_User2  as b   
  on    a.address=b.address         
--左连接
select  a.ID,a.name, a.address,a.worker,b.ID,b.worker,b.address from   ST_User as a   left   join   ST_User2  as b  
on   a.address=b.address    
--右连接
select   a.ID,a.name, a.address,a.worker,b.ID,b.worker,b.address from   ST_User as a   right      join   ST_User2  as b  
on   a.address=b.address  
--完全连接
 select  a.ID,a.name, a.address,a.worker,b.ID,b.worker,b.address   from  ST_User as a  full   join   ST_User2  as b   
  on   a.address=b.address  
--左外连接
 SELECT a.ID,a.name, a.address,a.worker,b.ID,b.worker,b.address
 FROM ST_User as a LEFT OUTER JOIN ST_User2 as b ON a.address=b.address

select  from dbo.V_WF_V1
select  from dbo.work_view2

----视图
SELECT     a.ID, a.COMPANYID, a.COMPANYNAME, a.STARTTIME, a.ENDTIME, a.IDNUMBER, a.PAYMENTAMOUNT, b.PAYMENTAMOUNT AS Expr1, b.ID AS Expr2, c.ID AS Expr3,
                      c.PAYMENTAMOUNT AS Expr4, b.COMPANYNAME AS Expr5, c.COMPANYNAME AS Expr6
FROM         dbo.Table_V1 AS a inner Join
                      dbo.Table_V2 AS b ON a.PAYMENTAMOUNT = b.PAYMENTAMOUNT AND a.ID = b.ID inner Join
                      dbo.Table_V3 AS c ON b.PAYMENTAMOUNT = c.PAYMENTAMOUNT
----视图
select a.ID, a.COMPANYID, a.COMPANYNAME, a.STARTTIME, a.ENDTIME, a.IDNUMBER, a.PAYMENTAMOUNT,b.ID as Expr1,b.COMPANYID as expr2
FROM Table_V1 as a INNER JOIN
                   Table_V2 as b ON a.COMPANYID=b.COMPANYID and a.ID=b.ID inner join
                   Table_V3 as c oN  b.Id=c.Id