实验三 数据的更新 一、实验目的 1．掌握基本表的 Insert，Update，Delete 操作 2．掌握视图的 Insert，Update，Delete 操作 二、实验环境（实验的软件、硬件环境）硬件：PC 机 软件：SQL2000 三、实验说明 请复习相关的 SQL 语句 insert,update,delete。
语法知识点并完成如下内容。
四、实验内容 1．表中数据的基本操作（1）给实验二中的 5 张表添加记录，记录数据见数据文件 data.txt（2）将作废订单（发票号 5197791779）由订单明细表中删除（3）将上海的客户住址全都改为深圳（4）将工作满 2 周年的员工薪水上调 5%，工作满 5 周年的员工薪水上调 8%（5）将客户 c20090001 在 2009 年 1 月购买的所有商品单价打 9 折（6）根据订单明细表，修改订单主表的订单金额信息。
2．视图中数据的基本操作（1）在实验二创建的视图上，对视图添加一条记录数据（注意：分别查看customer 表和该视图的结果）（2）删除视图中所有姓“王”的客户数据（3）通过视图修改表内某一个客户的姓名（4）对员工表和订单主表创建一个视图，该视图包含相同业务员的编号、姓名、订单号、订单金额。
（5）将上述视图中订单号为 200808080808 的记录的订单金额改为 60000（6）给上述视图添加一条记录数据
（7）删除上述视图 五、实验步骤 一.(1).insert into employee(employeeno,employeeName,sex,birthday,addr,telephone,hiredate,department,headship,salary)values("E2005001","喻自强","M","1965-4-15" ,"南京市","***","1990-2-6" ,"财务科","科长","5800.80"),("E2005002","张小梅","F","1973-11-1" ,"上海市","***","1991-3-28" ,"业务科","职员","2400.00"),("E2005003","张小娟","F","1973-3-6" ,"上海市","***","1992-3-28" ,"业务科","职员","2600.00"),("E2005004","张 露","F","1967-1-5" ,"南昌市","***","1990-3-28" ,"业务科","科长","4100.00"),("E2005005","张小东","M","1973-9-3" ,"南昌市","***","1992-3-28" ,"业务科","职员","1800.00"),("E2006001","陈 辉","M","1965-11-1" ,"南昌市","***","1990-3-28" ,"办公室","主任","4000.00"),("E2006002","韩 梅","F","1973-12-11","上海市","***","1990-11-28","业务科","职员","2600.00"),("E2006003","刘 风","F","1973-5-21" ,"南昌市","***","1991-2-28" ,"业务科","职员","2500.00"),("E2007001","吴浮萍","M","1973-9-12" ,"南京市","***","1990-6-28" ,"业务科","职员","2500.00"),("E2007002","高代鹏","M","1973-1-2" ,"南京市","***","1991-11-28","办公室","文员","2000.00"),("E2008001","陈诗杰","M","1968-1-6" ,"南京市","***","1990-12-6" ,"财务科","出纳","3200.00"),("E2008002","张 良","M","1972-2-16" ,"上海市","***","1992-2-28" ,"业务科","职员","2700.00"),("E2008003","黄梅莹","F","1972-5-15" ,"上海市","***","1991-2-28" ,"业务科","职员","3100.00"),("E2008004","李虹冰","F","1972-10-13","南京市","***","1990-5-28" ,"业务科","职员","3400.00"),("E2008005","张小梅","F","1970-11-6" ,"深圳市","***","1990-11-18","财务科","会计","5000.00")insert into customer(customerNo,customerName,telephone,addr,zip)
values("c2005001","统一股份有限公司" ,"022-3566021","天津市","220012"),("c2005002","兴隆股份有限公司" ,"022-3566021","天津市","220301"),("c2005003","上海生物研究室" ,"022-3566021","北京市","108001"),("c2005004","五一商厦" ,"022-3566021","上海市","210100"),("c2006001","大地商城" ,"022-3566021","北京市","100803"),("c2006002","联合股份有限公司" ,"022-3566021","上海市","210100"),("c2007001","南昌电脑研制公司" ,"022-3566021","南昌市","330046"),("c2007002","世界技术开发公司" ,"022-3566021","上海市","210230"),("c2007003","万事达股份有限公司","022-3566021","天津市","220400"),("c2008001","红度股份有限公司" ,"022-3566021","北京市","100800")insert into product(productNo,productName,productClass,productPrice)values("p2005001","32M DRAM" ,"内 存","80.70"),("p2005002","17寸显示器" ,"显示器","700.00"),("p2005003","120GB硬盘" ,"存储器","300.00"),("p2005004","3.5寸软驱" ,"设 备","35.00"),("p2005005","键盘" ,"设 备","100.60"),("p2006001","VGA显示卡" ,"显示器","1200.60"),("p2006002","网卡" ,"设 备","66.00"),("p2006003","Pentium100CPU" ,"处理器","200.00"),("p2007001","1G DDR" ,"内 存","256.00"),("p2007002","52倍速光驱" ,"设 备","200.00"),("p2007003","计算机字典" ,"图 书","100.00"),("p2007004","9600bits/s调制解调","设 备","320.00"),("p2008001","Pentium主板" ,"主 板","890.00"),("p2008002","索泰克主板" ,"主 板","1100.00"),("p2008003","纯平显示器" ,"显示器","900.00")insert into ordermaster(orderNo,customerNo,saleNo,orderdate,ordersum,invoiceNo)values("200801090001","c2005001","E2005002","2008-1-9" ,"0.00","I000000001"),("200801090002","c2005004","E2005003","2008-1-9" ,"0.00","I000000002"),("200801090003","c2008001","E2005002","2008-1-9" ,"0.00","I000000003"),("200802090001","c2005001","E2005003","2008-2-19","0.00","I000000004"),("200802090002","c2007002","E2008002","2008-2-19","0.00","I000000005"),("200803010001","c2007002","E2008001","2008-3-1" ,"0.00","I000000006"),("200803020001","c2005004","E2008003","2008-3-2" ,"0.00","I000000007"),("200803090001","c2007003","E2008004","2008-3-9" ,"0.00","I000000008"),("200805090001","c2006002","E2008002","2008-5-9" ,"0.00","I000000009"),("200806120001","c2005001","E2005002","2008-6-12","0.00","I000000010")insert into orderdetail(orderNo,productno,qty ,price)values("200801090001","p2005001","5","500"),("200801090001","p2005002","3","500"),("200801090001","p2005003","2","300"),("200801090002","p2006002","5","250"),("200801090002","p2008001","5","280"),("200801090002","p2008002","4","270"),("200801090002","p2008003","2","158"),("200801090003","p2005001","5","130"),("200801090003","p2006001","3","350"),("200802190001","p2006003","4","270"),("200802190001","p2007001","2","158"),("200802190001","p2007002","5","250"),("200802190001","p2007003","3","350"),("200802190001","p2007004","2","330"),("200802190001","p2008001","2","160"),("200802190001","p2008002","3","260"),("200802190001","p2008003","1","330"),("200802190002","p2005003","2","160"),("200802190002","p2005005","3","150"),("200802190002","p2007001","3","500"),("200803010001","p2005001","8","150")，("200803010001","p2007001","4","150"),("200803020001","p2005001","2","100"),("200803020001","p2005002","1","200"),("200803020001","p2007003","3","200"),("200803090001","p2005003","4","200"),("200803090001","p2005004","5","250"),("200803090001","p2007001","2","158"),("200803090001","p2007002","5","380"),("200803090001","p2007004","3","350"),("200805090001","p2006003","8","300"),("200805090001","p2007001","4","500"),("200805090001","p2007002","2","600"),("200805090001","p2007003","5","300"),("200806120001","p2005004","2","600"),("200806120001","p2005005","3","600"),("200806120001","p2006001","1","300"),("200806120001","p2006002","2","280")（2）.delete from orderdetail where orderno="5197791779"(3).update customer set addr="深圳市" where addr="上海市"(4).update employee set salary =salary *1.05 where(GETDATE()-hiredate)>2and(GETDATE()-hiredate)<5 update employee set salary =salary *1.08 where(GETDATE()-hiredate)>5 update employee set salary =(case when GETDATE()-hiredate>5 then salary *1.08 when(GETDATE()-hiredate)>2and(GETDATE()-hiredate)<5 then salary *1.05 end)(5).update ordermaster set ordersum =ordersum *0.9 where YEAR(orderdate)=2009 and MONTH(orderdate)=1
(6).update ordermaster set ordersum =ordersum2 from ordermaster ,(select orderno,sum(qty*price)as "ordersum2"from orderdetail group by orderno)as b where ordermaster.orderno =b.orderno 二.(1).alter table customer alter column telephone varchar(40)null insert into look values(1,1,1,1)(2).delete look where customername like "%王%"(3).update look set customername="1111" where customername="五一商厦"(4).create view iv_employee as(select employeeno,employeename,orderno,ordersum from ordermaster,employee where ordermaster.saleno =employee.employeeno)(5).update iv_employee set ordersum =60000 where orderno="200808080808"(6).insert into iv_employee values(1,1,1,1)视图或函数"iv_employee" 不可更新，因为修改会影响多个基表。
(7).drop view iv_employee 六、思考题：
1．请注意区别 Create，alter，drop 和 insert，update，delete 的异同和使用情况？ Create:用于创建各种框架，例如数据库，表，视图 Alter：在改变大框架时要加上，例如数据库，表，视图 Drop：删除大框架例如数据库，基本叫，视图 Insert：在表或者视图中插入小的数据时使用 Update：在跟新表或者视图中小的数据时使用 Delete：在删除表或者视图中小的数据时使用 2．请思考基本表数据的更新和视图数据的更新的联系和区别？ 如上面的题目 在对多张关联起来的表操作时会出错，也就是只能在表上操作，并且对于单表视图只能修改可以看见的内容，如果单表，并且可以看见全部内容那么视图与表对于操作和可视都是相同。
七、总结（实验过程的体会、心得和实验教与学之间还需改进的内容）在上面的插入数据中，orderdetail 的 orderno 插不进去，有多数据在 ordermast中没有，所以必须删除约束才能插入，对于视图操作中要跟新 customer 的视图，但是视图没有 telephone 属性，而在 customer 表中 telephone 是非空的，所以customer 视图不能插入操作，必须修改 telephone 可为空。总的来说这次实验对
我帮助很大，学会了如何多个关联表进行跟新操作，知道了对视图的修改，表也会同时修改，同时发现外键约束真的在插入数据时很麻烦。
