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

sql视图基本操作（创建、更新、删除）

相关推荐