视图的操作

1.创建视图v_1包含姓名(latname+firstname)/职称(title)/入职时间(hiredate)


use northwind
go
create view v_1
as
select LastName+'  '+FirstName as [name],Title,HireDate
from employees
go



2.查看视图源代码


exec sp_helptext v_1


3.修改视图v_1对其加密

alter view v_1 with encryption
as
select LastName+'  '+FirstName as [name],Title,HireDate
from employees
go




4.查询视图v_1里的信息


select * from v_1




exec sp_helptext v_1





5.创建视图v_2只显示14号供应商供应产品名,单价.并且防止用户更改供应商号


use northwind
go
create view v_2
as
select productname,UnitPrice
from Products
where SupplierID=14
with check option
go


select * from v_2

6.创建一个视图v_3包含产品名，供应商公司名，产品单价，库存

use northwind
go
create view v_3
as
select p.productname,s.CompanyName,p.UnitPrice,p.UnitsInStock
from products p
join suppliers s
on p.SupplierID=s.SupplierID
go


select * from v_3



7.修改视图v_3 ，还要包含该产品所属的种类名.

use northwind
go
alter view v_3
as
select p.productname,s.CompanyName,p.UnitPrice,p.UnitsInStock,c.CategoryName
from products p
join suppliers s
on p.SupplierID=s.SupplierID
join dbo.Categories c
on c.CategoryID=p.CategoryID
go


select * from v_3