欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-04 23:33:46
创建视图的前提必须有表...
  • 创建视图基本语句
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