乘风原创程序

  • sql视图基本操作(创建、更新、删除)
  • 2020/8/11 11:35:05
    • 创建视图基本语句
    CREATE VIEW view_name AS
    SELECT column_name(s)
    FROM table_name
    WHERE condition 

    下面举个例子:

    --判断Table_View视图是否存在,不存在就创建,存在就先删除在新建
    IF EXISTS(select 1 FROM sys.views WHERE name='Table_View') 
    DROP view Table_View
    go
    create view Table_View as
    select  a.SBVID as '发票编号',dDate as '发票日期',cBusType as '业务类型',a.cDepCode as '部门编码',cDepName as '部门名称',a.cPersonCode as '业务员编码',p.cPersonName as '业务员名称',cCusCode as '客户编码',cCusName as '客户名称',sum(SaleBillVouchs.iNatMoney) as '金额',[sTate] as '提交状态',[cReatDate] as '提交日期'
        from SaleBillVouch a
        inner join SaleBillVouchs  on a.SBVID = SaleBillVouchs.SBVID
        inner join cCusHeadView  on a.cCusCode = cCusHeadView.cCusHeadCode
        inner join Person p on a.cPersonCode = p.cPersonCode
        inner join Department  on a.cDepCode = Department.cDepCode
    	where a.SBVID=SaleBillVouchs.SBVID group by  a.SBVID ,dDate,cBusType,a.cDepCode,a.cPersonCode,cDepName,p.cPersonName,cCusCode,cCusName ,[sTate],[cReatDate], SaleCost
    GO 
    • 查询视图(可以增加查询条件,和查询表完全一样)
    select *from Table_View 
    • SQL 更新视图
    SQL CREATE OR REPLACE VIEW Syntax
    CREATE OR REPLACE VIEW view_name AS
    SELECT column_name(s)
    FROM table_name
    WHERE condition 

    举个例子(往Table_View视图添加Category列)

    CREATE VIEW  Table_View AS
    SELECT ProductID,ProductName,Category
    FROM Products
    WHERE Discontinued=No 
    • SQL 撤销视图 (和删除表一样)
    SQL DROP VIEW Syntax
    DROP VIEW view_name