实验七视图及SQL数据更新语句

实验七视图及SQL数据更新语句

一、实验目的

1.掌握视图的概念；

2.熟练掌握视图的生成及操作语句；

3.熟练掌握SQL数据更新语句；

二、实验内容

给定一个练习数据库和相应的练习题，要求上机完成，并验证结果

实验基础知识提要

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。

创建视图的基本语句为：

CREATE VIEW view_name [ ( column [ ,...n ] ) ]

AS

select_statement

[ WITH CHECK OPTION ]

view_name

是视图的名称。视图名称必须符合标识符规则。可以选择是否指定视图所有者名称。

column

是视图中的列名。只有在下列情况下，才必须命名CREATE VIEW 中的列：当列是从算术表达式、函数或常量派生的，两个或更多的列可能会具有相同的名称（通常是因为联接），视图中的某列被赋予了不同于派生来源列的名称。还可以在SELECT 语句中指派列名。

如果未指定column，则视图列将获得与SELECT 语句中的列相同的名称。

select_statement

是定义视图的SELECT 语句。该语句可以使用多个表或其它视图。若要从创建视图的SELECT 子句所引用的对象中选择，必须具有适当的权限。

WITH CHECK OPTION

强制视图上执行的所有数据修改语句都必须符合由select_statement 设置的准则。通过视图修改行时，WITH CHECK OPTION 可确保提交修改后，仍可通过视图看到修改的数据。

三、实验步骤

构建数据表作为实验数据内容（数据表同实验六，参考实验六实验步骤)

运行SQL SERVER服务管理器，确认数据库服务器开始运行。

打开查询分析器，选择刚才恢复的数据库exampleDB，输入SQL指令，获得运行结果。

任务：完成以下SQL数据更新语句

(1)创建视图V_SupplyCount,显示供应商编号，以及该供应商供应的产品的品种数

(非CategoryID)，该视图包含两个字段：SupplierID、ProductCount。

create view V_SupplyCount(SupplierID,ProductCount)

as

select SupplierID,count(ProductID)

from products

group by SupplierID

(2)创建视图V_OrderCount，显示顾客编号，顾客所下订单的产品总金额(金额=单

价×数量×折扣)，该视图包含两个字段：CustomerID，TotalFee。

create view V_OrderCount(CustomerID,TotalFee)

as

select CustomerID,sum(UnitPrice*Quantity*Discount)

from orders,orderdetails

where orders.OrderID=orderdetails.OrderID

group by CustomerID

(3)通过视图V_SupplyCount，查出供应产品的品种数最少的供应商编号和供应商

名称。

select SupplierID,CompanyName

from Suppliers

where SupplierID in

(select SupplierID

from V_SupplyCount

where ProductCount=

(select min(ProductCount)

from V_SupplyCount))

(4)通过视图V_OrderCount，查出所下订单的产品总金额最多的顾客编号和地址。select CustomerID,Address

from Customers

where CustomerID in

(select CustomerID

from V_OrderCount

where TotalFee=

(select max(TotalFee)

from V_OrderCount))

(5)再Region表中，添加一个新的地区：地区编号为5，地区描述为Central insert into Region values(5,'Central')

(6)将Suppliers表中的所有国家为法国的供应商信息内容添加到OldSuppliers中create table OldSuppliers(

SupplierID int NOT NULL primary key,

CompanyName varchar(50) ,

ContactName varchar(50) ,

ContactTitle varchar(50) ,

Address varchar(60) ,

City varchar(50) ,

Region varchar(50) ,

PostalCode varchar(50) ,

Country varchar(50) ,

Phone varchar(50) ,

Fax varchar(50) ,

HomePage varchar(50)

)

insert into OldSuppliers

select * from Suppliers where Country='France'

(7)将所有由职员Fuller(LastName)签订的订单运费降低10%

update Orders

set Freight=Freight*0.9

where EmployeeID=

(select EmployeeID

from Employees

where LastName='Fuller')