创建并查询视图

--
--3.5.2节示例
--

--创建并查询视图
USE AdventureWorks
GO
CREATE VIEW vwEmployee --创建视图
AS
SELECT e.*,d.Name AS DepartmentName --联接查询多张表
FROM HumanResources.Department d
INNER JOIN HumanResources.EmployeeDepartmentHistory edh
ON edh.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.Employee e
ON e.EmployeeID = edh.EmployeeID
GO
SELECT * --查询视图中的数据
FROM vwEmployee

--使用其他视图和表创建视图
CREATE VIEW vwEmployeeContract
AS
SELECT e.*,c.FirstName,c.MiddleName,c.LastName,c.EmailAddress,c.Phone
FROM vwEmployee e -- 对视图进行联接查询
INNER JOIN Person.Contact c
ON c.ContactID = e.ContactID
GO
SELECT *
FROM vwEmployeeContract --查询新视图

--
--3.5.4节示例
--

--修改视图
ALTER VIEW [dbo].[vwEmployee]
AS
SELECT e.*,d.Name AS DepartmentName,d.GroupName -- 增加GroupName
FROM HumanResources.Department d
INNER JOIN HumanResources.EmployeeDepartmentHistory edh
ON edh.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.Employee e
ON e.EmployeeID = edh.EmployeeID

--
--3.5.5节示例
--

--删除视图
DROP VIEW vwEmployee, vwEmployeeContract