实验七视图及SQL数据更新语句
实验七视图及SQL数据更新语句
实验七视图及SQL数据更新语句一、实验目的1.掌握视图的概念；2.熟练掌握视图的生成及操作语句；3.熟练掌握SQL数据更新语句；二、实验步骤构建数据表作为实验数据内容（数据表同实验六，参考实验六实验步骤)运行SQL SERVER服务管理器，确认数据库服务器开始运行。
打开查询分析器，选择刚才恢复的数据库exampleDB，输入SQL指令，获得运行结果。
任务：完成以下SQL数据更新语句(1)创建视图V_SupplyCount,显示供应商编号，以及该供应商供应的产品的品种数(非CategoryID)，该视图包含两个字段：SupplierID、ProductCount。
(2)创建视图V_OrderCount，显示顾客编号，顾客所下订单的产品总金额(金额=单价×数量×折扣)，该视图包含两个字段：CustomerID，TotalFee。
(3)通过视图V_SupplyCount，查出供应产品的品种数最少的供应商编号和供应商名称。
(4)通过视图V_OrderCount，查出所下订单的产品总金额最多的顾客编号和地址。
(5)再Region表中，添加一个新的地区：地区编号为5，地区描述为Central(6)将Suppliers表中的所有国家为法国的供应商信息内容添加到OldSuppliers中(7)将所有由职员Fuller(LastName)签订的订单运费降低10%(8)将所有美国顾客购买的订单单价调高20%(9)将订货数量最多的产品的单价上调5元(10)删除订单个数最少的职员的信息(11)删除所有没有下订单的顾客信息三. 实验结果与分析（上交实验报告）创建视图V_SupplyCount,显示供应商编号，以及该供应商供应的产品的品种数(非CategoryID)，该视图包含两个字段：SupplierID、ProductCount。
create view V_SupplyCount(SupplierID,ProductCount)as select SupplierID,count(ProductID) from products group by SupplierID●创建视图V_OrderCount，显示顾客编号，顾客所下订单的产品总金额(金额=单价×数量×折扣)，该视图包含两个字段：CustomerID，TotalFee。
实验七 数据库的更新及视图的定义与维护
实验七数据的更新及视图的定义与维护一．实验目的1.掌握使用SQL语言向数据库中插入数据、修改数据和删除数据等更新操作2.掌握视图的定义与维护操作3. 加深对视图在关系数据库中的作用的理解二．实验准备1．装有SQL Server 2005的PC机。
2. 成功创建各数据库、数据库表、并已插入各种数据。
三．实验内容11.打开“SQL Server Management Studio”窗口2.创建学生选课数据库“S_T”3.在学生选课数据库中完成以下插入数据的操作：（1）将一个新学生元组（学号：200215128；姓名：陈东；性别：男；年龄：18；所在系：IS）插入学生表中；（2）插入一条选课记录(‘200215128’,1)；（3）对每一个系，求学生的平均年龄，并把结果存入数据库。
提示：首先在学生-选课数据库中建立一个新表，其中一列存放系名，另一列存放相应的学生平均年龄；然后对student表按系分组求平均年龄，再把系名和平均年龄存入新表中。
4.在学生选课数据库中完成以下修改数据的操作：（4）将学生200215121的年龄改为22岁；（5）将所有学生的年龄增加一岁；（6）将计算机科学系全体学生的成绩置零；5.在学生选课数据库中完成以下删除数据的操作：（7）删除学号为200215122的学生记录；（8）删除所有的学生选课记录；（9）删除计算机科学系所有学生的选课记录四．实验内容21. 在学生选课数据库中完成以下创建视图的操作：（1）建立信息系学生的视图，并要求进行修改和插入操作时仍须保证该视图只有信息系的学生；（2）建立信息系选修了“1”号课程的学生视图；（3）建立信息系选修了“1”号课程且成绩在90分以上的学生视图；（4）定义一个反映学生出生年份的视图；（5）将学生的学号及他的平均成绩定义为一个视图；（6）将Student表中所有女生记录定义为一个视图；2. 在学生选课数据库中完成以下删除视图的操作：（7）选择一个您所建的视图进行删除；3. 在学生选课数据库中完成以下查询视图的操作：（8）在信息系学生的视图中找出年龄小于20岁的学生；（9）查询选修了1号课程的信息系学生；4. 在学生选课数据库中完成以下更新视图的操作：（10）将信息系学生视图中学号为200215122的学生姓名改为“刘辰”；（11）向信息系学生视图中插入一个新的学生记录，其中学号为200215129，姓名为赵新，年龄为20岁。
使用SQL语句创建视图和更新视图数据
视图的概念
视图不是真实存在的基础表（基表）， 而是一张虚表。
通过视图看到的数据只是存放在基表 中的数据 对视图的操作与对表的操作一样 可以对其进行查询、修改（有一定的 限制）和删除； 可以在视图的基础上继续创建视图。
2.视图的用途
1
简化查询操作
用户可以将多个表 中感兴趣的数据集 中在一个视图中， 通过查询视图查看 多个表中的数据。
通过视图执行的所有数据修改语句 必须符合视图定义中设置的条件, 即商品类型='水果'。
如果‘水果类商品信息’视图定义时没有With check option ，能添加成功，只是加在商品表上。 ‘水果类商品信息’视图显示的是水果类，所以不显示伊 利红枣酸牛奶的记录
3．往‘商品销售详细信息’视图中添加一条记录，销售号： '101-106-45'，销售时间：'2010-4-18'，是否会员：0，支付 方式：’现金’ ，条形码：'2000000341316'，销售数量：5 。
思考：是否能添加成功？？如果出错，为什么？
消息550，级别16，状态1，第1 行 试图进行的插入或更新已失败，原因是目标视图或者目标 视图所跨越的某一视图指定了WITH CHECK OPTION， 而该操作的一个或多个结果行又不符合CHECK OPTION 约束。
语句已终止。
--不能添加成功，
因为创建视图时使用With check option,
思考：能否添加成功？如果出错，为什么？
insert 商品销售详细信息(销售号,销售时间,是否会员,支付方式,条形码,销售数量) values('101-106-45','2010-4-18',0,'现金','2000000341316',5)
SQL：数据库更新语句操作实例
SQL：数据库更新语句操作实例⼀、SQL Server数据库更新操作（create,update,delete,insert,drop,alter）背景知识：⼀、插⼊：对表进⾏插⼊操作，insert into table_name（属性列）values（插⼊值），⼀定要注意属性列和插⼊值的顺序要⼀样⼆、语法记忆：update、delete、insert都不改变表的结构，所以只需要加上table_name就可以，⽽drop、create,alter都改变了表的结构，所以需要加上table，例如drop table table_name名称解释（不区分⼤⼩写）student学⽣表：包含属性列：sno学号、sname学⽣姓名、age年龄、sex性别、dno学院编号、birthday⽣⽇sc选课信息表：包含属性列：sno学号、cno课程号、grade成绩dept学院信息表：包含属性列：dno学院编号、dname学院名称、dean学院负责⼈course课程信息表：包含属性列：cno课程号、cname课程名称、tname⽼师名称、credit学分、room教室下⾯语句可以直接复制到SQL Server运⾏运⾏⽅法，选中执⾏语句⽅法不唯⼀，有问题欢迎留⾔讨论！代码块见⽂章末尾⼆、代码select * from scselect * from studentselect * from course–插⼊新的学⽣李⼀和李⼆insert into student(sno,sname,sex,dno,BIRTHDAY)values(‘20069011’,‘李⼀’,‘男’,‘0001’,‘1985-01-02’)select * from studentwhere sname = ‘李⼀’insert into student(sno,sname,sex,dno,BIRTHDAY)values(‘20069012’,‘李⼆’,‘⼥’,‘0002’,‘1986-01-02’)select * from studentwhere sname = ‘李⼆’–创建新表CREATE TABLE chengjiao(SNO char (8) not null unique,SNAME char(10),SEX char(2),DNO char(8),AGE smallint,BIRTHDAY datetime )–插⼊新学⽣张三、王⼆、张三INSERT INTO student(SNO,SNAME,AGE,DNO) VALUES (‘20067027’,‘张三’,20,‘0002’) INSERT INTO chengjiao(SNO,SNAME,AGE,DNO) VALUES (‘20067011’,‘王⼆’,23,‘0003’) INSERT INTO chengjiao(SNO,SNAME,AGE,DNO) VALUES (‘20067021’,‘张三’,19,‘0003’) select * from studentwhere sno = ‘20067027’unionselect * from studentwhere sno = ‘20067011’unionselect * from studentwhere sno = ‘20067021’–将成教表 chengjiao 中的所有学⽣⼀次性添加到学⽣表 student 中。
使用SQL语句创建视图和更新视图数据.
相关限制 在一个语句中，一次不能修改一个以上的视图基表； 对视图中所有列的修改必须遵守视图基表中所定义 的各种数据约束条件； 不允许对视图中的计算列进行修改，也不允许对 视图定义中包含有统计函数或GROUP BY子句的 视图进行插入或修改操作。
2．往‘水果类商品信息’视图中添加一条记录：条形码 为：’907992101279’，商品名称为：’伊利红枣酸牛奶 ’，库存数量为：0，商品类型为：’牛奶’。 思考：是否能添加成功？？如果出错，为什么？
SELECT
FROM
列名或表达式
表名或视图名
[WHERE 行条件表达式] [GROUP BY 列名] [HAVING 组条件表达式] [ORDER BY 列名 [ASC|DESC]]
1．重新创建视图‘目前商品价格2’，查询各商品目前的零 售价和会员价，结果包括条形码、商品名称、零售价、会员 价。 提示：通过视图‘目前商品价格1’和商品表创建。 将目前商品价格1当成一张表来使用， 要查找条形码、商品名称、零售价、会员价 将目前商品价格1’和商品表连接起来 select dbo.商品.条形码,商品名称,零售价,会员价
任务5：创建视图和更新视图数据
使用management studio创建视图；
使用CREATE VIEW语句创建视图；
视图的管理：查看视图信息、修改视图、删除视图、重命 名视图等； 使用视图对表数据操作：查询、添加、修改、删除等。
第一部分
Байду номын сангаас
创建视图
1.视图概述
视图是一种常用的数据库对象，是从 一个或多个基表中导出的表，其结构 和数据是对数据表进行查询的结果。 视图被定义后便存储在数据库中，通 过视图看到的数据只是存放在基表中 的数据。
数据库试验---SQL数据更新语句
实验SQL数据更新语句一、实验目的本次实验了解SQL Server 2005的启动，熟悉如何使用SSMS和5@1.建立数据库和表，并加深对于完整性的理解。
一.二、背景知识在使用数据库的过程中，接触最多的就是数据库中的表。
表是数据存储的地方，是数据库中最重要的部分，管理好表也就管理好了数据库。
表是由行和列组成的。
创建表的过程主要就是定义表的列的过程。
表的列名在同一个表中具有唯一性，同一列的数据属于同一种数据类型。
除了用列名和数据类型来指定列的属性外，还可以定义其它属性：是否为空、默认值、标识符列、全局唯一标识符列等。
约束是SQL Server提供的自动保持数据库完整性的一种方法，定义了可输入表或表的单个列中的数据的限制条件。
在SQL Server中有5种约束：主关键字约束(Primary Key Constraint)、外关键字约束(Foreign Key Constraint)、惟一性约束(Unique Constraint)x 检查约束(Check Constraint)和默认约束(Default Constraint) 0...三、实验目的加深对SQL数据更新(插入、修改及删除)语句的基本语法格式的掌握掌握单个元组及多个元组的插入、修改及删除操作的实现过程加深对更新操作时数据库中数据一致性问题的了解加深对约束条件在数据更新操作执行中的作用问题的了解。
实验报告要求1、写出与上述任务相对应的SQL更新语句(实验报告上写出3、5、10的SQL语句)2、并记录在实验过程中遇到的问题、解决办法及心得体会。
五、实验内容方法一：在SSMS中插入、删除及修改数据，方法二：在查询编辑器中写SQL插入、删除及修改数据设有一个SPJ数据库，包括S, P, J, SPJ四个关系模式：S (SNO, SNAME, STATUS, CITY)dlim-S dbo. F dbo. JP (PNO, PNAME, COLOR, WEIGHT)PNAM;vardnar(2D)08L8Cbi3l(4)0WEIGm-int H□J (JNO, JNAME, CITY)列名数据类型允许空供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商（STATUS）、供应商所在城市（CITY）组成；零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）组成；工程项目表1由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成；供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某供应商供应某种零件给某工程项目的数量为QTY。
基于SQL的关系数据库更新和视图
2. UPDATE (修改数据 修改数据) 修改数据
格式: 格式
UPDATE <表名 表名> 表名 SET <列名 = <表达式 列名> 表达式>|<子查询 子查询> 列名 表达式 子查询 [, <列名 = <表达式 列名> 表达式>|<子查询 子查询>……..] 列名 表达式 子查询 [WHERE <条件表达式 条件表达式>]; 条件表达式 说明:修改时,对满足条件表达式的行, 说明:修改时,对满足条件表达式的行,将用表达式的 值或子查询的结果(唯一值)替换相应列的值. 值或子查询的结果(唯一值)替换相应列的值.
例:将学生05001的年龄改为22岁。 将学生05001的年龄改为22岁 05001的年龄改为22 UPDATE Student SET Sage=22 WHERE Sno=' 05001 ';
例:将信息系所有学生的年龄增加1岁。 将信息系所有学生的年龄增加1 UPDATE Student SET Sage= Sage+1 WHERE Sdept='IS';
• 对于有NOT NULL约束的属性列是否提供了非空值 对于有NOT NULL约束的属性列是否提供了非空值 • 对于有UNIQUE约束的属性列是否提供了非重复值 对于有UNIQUE约束的属性列是否提供了非重复值 UNIQUE • 对于有值域约束的属性列所提供的属性值是否在值 域范围内
SQL视图 视图
例:将学生张立转入学生李勇所在的系 UPDATE Student SET sdept=
(SELECT sdept FROM Student WHERE SNAME = '李勇 李勇') 李勇
SQL数据更新语句
SQL操作功能插入记录INSERT INTO dbf-name [frame1，frame2] V ALUE（）INSERT INTO dbf-name 说明向指定表插入记录[frame1，frame2] 当插入不完整记录时，用frame1,frame2 指定记录V ALUE（）给出具体值INSERT TABLE表名FROM ARRAY数组名SQL数据更新记录语句UPDA TE 表名SET <列名1> =表达式[,列名2=表达式2]WHＥＲＥ条件表达式UPDA TA雇员SETＥＭＡＩＬ＝ＡＬＬＴＲＩＭ（部门号）＋ＡＬＬＴＲＩＭ（雇员号）＋“＠ＸＸＸＸ．＠．ｃｏｍ．ｃｎＵＰＤＡＴＥＡＬＬ日期＝ＤＡＴＡ（）一般用ＷＨＥＲＥ子句指定条件，以更新满足条件的一些记录的字段值，并且一次可以更新多个字段，若不使用ＷＨＥＲＥ子句，则更新全部记录俩表的连接条件在ＷＨＥＲＥ子句中指定，另外对记录进行记录的筛选的条件通过ＡＮＤ短语与表的连接条件一起凡在ｗｈｅｒｅ子句之后SELECT 字段名from 表名WHERE 连接条件GROUPE BYＣＯＵＮＴＨＡＶＩＮＧＯＲＤＥＲBYＡＳＣＤＥＳＣ排序字段有多个，每个字段之间用英语逗号分开，默认排序方式是升序ＴａｂＩｎｄｅｘ属性可以用来控制光标在控件之间移动次序，光标按照１、２、３的顺序依次移动DELET删除记录DELECT FROM 表名WHERE 条件若不使用WHERE则删除所有记录ＳｅｔＦｏｃｕｓ可以让控件获得焦点，使其成为活动对象。
如果一个控件的Ｅｎａｂｌｅｄ属性值和Ｖｉｓｉｂｌｅ属性值.F.,将不能获得焦点，如果一个控件获得焦点，则Enabled 属性值和Visible属性值.T.ALRET TABLE 表名ＡＬＲＥＴＣＲＥＡＴＥVIEW视图名ａｓＳＱＬ语句表单类、方法、事件类只是实例对象的抽象，类并不进行任何行为操作。
类是对相似对象的性质描述，对象具有相同的性质：相同种类的属性和方法继承是基于现有的类创建新类，新类继承现有类的方法和属性，病创建新的属性和方法子类继承父类所有属性和方法事件是由系统预先定义而由用户或系统发出的动作，事件作用于对象，对象识别事件并作出相应的反应，事件集是固定的，用户不可以定义新事件表单的事件、属性、方法运行时事件load事件Init事件在对象建立时引发。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验七视图及SQL数据更新语句一、实验目的1.掌握视图的概念；2.熟练掌握视图的生成及操作语句；3.熟练掌握SQL数据更新语句；二、实验内容给定一个练习数据库和相应的练习题，要求上机完成，并验证结果实验基础知识提要视图是一个虚拟表，其内容由查询定义。
同真实的表一样，视图包含一系列带有名称的列和行数据。
但是，视图并不在数据库中以存储的数据值集形式存在。
行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
对其中所引用的基础表来说，视图的作用类似于筛选。
定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。
创建视图的基本语句为：CREATE VIEW view_name [ ( column [ ,...n ] ) ]ASselect_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)asselect SupplierID,count(ProductID)from productsgroup by SupplierID(2)创建视图V_OrderCount，显示顾客编号，顾客所下订单的产品总金额(金额=单价×数量×折扣)，该视图包含两个字段：CustomerID，TotalFee。
create view V_OrderCount(CustomerID,TotalFee)asselect CustomerID,sum(UnitPrice*Quantity*Discount)from orders,orderdetailswhere orders.OrderID=orderdetails.OrderIDgroup by CustomerID(3)通过视图V_SupplyCount，查出供应产品的品种数最少的供应商编号和供应商名称。
select SupplierID,CompanyNamefrom Supplierswhere SupplierID in(select SupplierIDfrom V_SupplyCountwhere ProductCount=(select min(ProductCount)from V_SupplyCount))(4)通过视图V_OrderCount，查出所下订单的产品总金额最多的顾客编号和地址。
select CustomerID,Addressfrom Customerswhere CustomerID in(select CustomerIDfrom V_OrderCountwhere 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 OldSuppliersselect * from Suppliers where Country='France'(7)将所有由职员Fuller(LastName)签订的订单运费降低10%update Ordersset Freight=Freight*0.9where EmployeeID=(select EmployeeIDfrom Employeeswhere LastName='Fuller')(8)将所有美国顾客购买的订单单价调高20%update Orderdetailsset UnitPrice=UnitPrice*1.2where OrderID in(select distinct Orderdetails.OrderIDfrom Orderdetails,Orders,Customerswhere Orderdetails.OrderID=Orders.OrderID andOrders.CustomerID=Customers.CustomerID andCustomers.Country='USA')(9)将订货数量最多的产品的单价上调5元update Productsset UnitPrice=UnitPrice+5where ProductID in(select ProductIDfrom Orderdetailsgroup by ProductIDhaving sum(Quantity)>=ALL(select sum(Quantity)from Orderdetailsgroup by ProductID))(10)删除订单个数最少的职员的信息delete from Employeeswhere EmployeeID in(select EmployeeIDfrom Ordersgroup by EmployeeIDhaving count(OrderID)<=ALL(select count(OrderID)from Ordersgroup by EmployeeID))(11)删除所有没有下订单的顾客信息delete from Customerswhere not exists(select *from Orderswhere Customers.CustomerID=Orders.CustomerID)四. 实验结果与分析（上交实验报告）分析思考问题：1. 分析在什么情况下需要使用视图，并简述其目的；筛选用户表中需要的数据，但不对原用户表进行插入，更新，删除等修改操作时，建立视图可以使查找工作更方便，更有针对性，也不易受无关数据项干扰，用户表数据量越大以及需要查找数据量较大的情况下，使用视图就更方便，且是查找结果输出表格化，便于观察。
2. 分析是否可以使用视图进行数据更新删除，和对基础表进行更新删除有哪些异同点；您可以通过视图修改基础基表的数据，修改方式与通过 UPDATE、INSERT 和 DELETE 语句修改表中数据的方式一样。
但是，以下限制应用于更新视图，但不应用于表：任何修改（包括 UPDA TE、INSERT 和 DELETE 语句）都只能引用一个基表的列。
视图中被修改的列必须直接引用表列中的基础数据。
它们不能通过其他方式派生，例如通过：聚合函数（A VG、COUNT、SUM、MIN、MAX、GROUPING、STDEV、STDEVP、V AR 和 V ARP）。
计算，不能通过表达式并使用列计算出其他列。
使用集合运算符（UNION、UNION ALL、CROSSJOIN、EXCEPT 和 INTERSECT）形成的列得出的计算结果不可更新。
正在修改的列不受 GROUP BY、HA VING 或 DISTINCT 子句的影响。
五．讨论、心得。