sql视图基本操作(创建、更新、删除)

  • 创建视图基本语句
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 
(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐