SQL Server 视图（仅代码及练习）

SQL Server 视图（仅代码及练习）

use electric

go

--建立供电局1#仓库所存放物资的视图。



create view s1_stock

as

select * from stock where warehouse='供电局1#仓库';

select * from s1_stock

--建立供电局1#仓库所存放物资的视图，并要求进行修改和插入 *** 作时仍需保证该视图只有供电局1#仓库所存放的物资。



create view s2_stock

as

select * from stock where warehouse='供电局1#仓库' with check option

select * from s2_stock

--建立由抢修工程项目名称（prj_name）、出库物资名称（mat_name）、规格（speci）及领取数量（amount）的视图。



create view s3_stock

as

select Salvaging.prj_name,Stock.mat_name,speci,stock.amount from stock,Salvaging,Out_Stock where Salvaging.prj_no=Out_stock.prj_no and Out_Stock.mat_no=stock.mat_no

select * from Salvaging

--建立供电局1#仓库所存放物资库存数量不少于50的视图。



create view s4_stock

as

select * from stock where amount>=

select * from s4_stock

--建立一个体现抢修工程项目实际抢修天数的视图。



create view s5_stock

as

select prj_no,prj_name,datediff(day,start_date,end_date) 抢修天数 from Salvaging

select * from s5_stock

--将仓库名称与其仓库内所存放物资的种类定义为一个视图。



create view s6_stock

as

select mat_name,amount from Stock

select * from s6_stock

--视图加密

CREATE VIEW s7_stock(warehouse,counts)

WITH ENCRYPTION --加密

AS

SELECT warehouse, COUNT(mat_no)

FROM stock

GROUP BY warehouse;

--被加密后无法查看

sp_helptext s7_stock

--视图删除

DROP VIEW S1_Stock;

--更新视图原表也一定会更新