SQL Server 实验

10软件一班22号周超

实验九

一、实验内容

2-3人一组，使用SQL完成下列工作（具体要求由其他组员给出）

1、建立视图

2、操作视图（包括查询视图和更新视图）

3、删除视图

二、实验过程

A 建立视图

1、建立使用了零件P3的工程项目情况的视图P3_SPJ

SQL：CREATE VIEW P3_SPJ

AS

SELECT SNO,JNO,QTY

FROM SPJ

WHERE PNO='P3'

2、建立颜色为红色的零件视图RED_P

SQL：CREATE VIEW RED_P

AS

SELECT*

FROM P

WHERE COLOR='红'

WITH CHECK OPTION

3、建立一个供应商情况和供应情况的视图S_SPJ_SPJ

SQL：CREA TE VIEW S_SPJ_SPJ

AS

SELECT S.SNO,SNAME,STA TUS,CITY,PNO,JNO,QTY

FROM S,SPJ

WHERE S.SNO=SPJ.SNO

B 操作视图

a 查询视图

1、要求：在使用了零件P3的视图中查询供应数量大于300工程项目

SQL：SELECT JNO

FROM P3_SPJ

WHERE QTY>300

2、要求：查询使用了P3零件且供应商所在地为北京工程项目信息

SQL：SELECT DISTINCT J.JNO,JNAME,J.CITY

FROM S,J,P3_SPJ

WHERE S.SNO=P3_SPJ.SNO AND J.JNO=P3_SPJ.JNO AND J.CITY='北京'

b 更新视图

（1）修改

查询要求：将颜色为红色的零件视图RED_P中名字为齿轮的零件重量改为35

SQL：UPDATE RED_P

SET WEIGHT='35'

WHERE PNAME='齿轮'

（2）插入

查询要求：向颜色为红色的零件视图RED_P中插入一个新的记录，其中零件代码为P7，零件名为链条，重量为50

SQL：INSERT

INTO RED_P

VALUES('P7','链条','红','30')

（3）删除

查询要求：删除视图P3_SPJ、RED_P、S_SPJ_SPJ

SQL：DROP VIEW P3_SPJ,RED_P,S_SPJ_SPJ

附加题：举一例不可更新视图

向S_SPJ_SPJ视图中插入以下一条数据:

（'S6'，'东风',20,'广西','P4','J3',600)

SQL : INSERT

INTO S_SPJ_SPJ

V ALUES('S6','东风',20,'广西','P4','J3',600)

消息4405，级别16，状态1，第2 行

视图或函数'S_SPJ_SPJ' 不可更新，因为修改会影响多个基表。

一、遇到的问题

在进行往视图中插入新数据时输入SQL

INSERT

INTO RED_P

VALUES('P7','链条','30')

出现提示错误：

消息213，级别16，状态1，第1 行

插入错误: 列名或所提供值的数目与表定义不匹配。

二、原因及解决方法

原因：在建立视图RED_P时选择了P表的所有列，所以即便是建立视图加上了WITH CHECK OPTION也不能使系统为VALUES子句自动加上红色这个限定条件。

解决方法：讲插入信息补全

INSERT

INTO RED_P

VALUES('P7','链条','红色','30')