实验三 数据的更新

一、实验目的
1．掌握基本表的Insert，Update，Delete操作
2．掌握视图的Insert，Update，Delete操作

二、实验环境（实验的软件、硬件环境）
硬件：PC机 软件：SQL2000

三、实验说明
请复习相关的SQL语句insert,update,delete语法知识点并完成如下内容。

四、实验内容
1．表中数据的基本操作
（1）给实验二中的5张表添加记录，记录数据见数据文件data.txt

insert into employee(employeeNo, employeeName, sex, birthday, address, telephone, hiredate, department, headship, salary)
values(‘E2005001’, ‘喻自强’, ‘M’, ‘1965-4-15’, ‘南京市’, ‘13817605008’, ‘1990-2-6’, ‘财务科’, ‘科长’, 5800.80)

insert into employee(employeeNo, employeeName, sex, birthday, address, telephone, hiredate, department, headship, salary)
values(‘E2005002’, ‘张小梅’, ‘F’, ‘1973-11-1’, ‘上海市’, ‘13817605008’, ‘1991-3-28’, ‘业务科’, ‘职员’, 2400.00)

insert into employee(employeeNo, employeeName, sex, birthday, address, telephone, hiredate, department, headship, salary)
values(‘E2005003’, ‘张小娟’, ‘F’, ‘1973-3-6’, ‘上海市’, ‘13817605008’, ‘1992-3-28’, ‘业务科’, ‘职员’, 2600.00)

insert into employee(employeeNo, employeeName, sex, birthday, address, telephone, hiredate, department, headship, salary)
values(‘E2005004’, ‘张露’, ‘F’, ‘1967-1-5’, ‘南昌市’, ‘13817605008’, ‘1990-3-28’, ‘业务科’, ‘科长’, 4100.00)

insert into employee(employeeNo, employeeName, sex, birthday, address, telephone, hiredate, department, headship, salary)
values(‘E2005005’, ‘张小东’, ‘M’, ‘1973-9-3’, ‘南昌市’, ‘13817605008’, ‘1992-3-28’, ‘业务科’, ‘职员’, 1800.00)

insert into employee(employeeNo, employeeName, sex, birthday, address, telephone, hiredate, department, headship, salary)
values(‘E2006001’, ‘陈辉’, ‘M’, ‘1965-11-1’, ‘南昌市’, ‘13817605008’, ‘1990-3-28’, ‘办公室’, ‘主任’, 4000.00)

insert into employee(employeeNo, employeeName, sex, birthday, address, telephone, hiredate, department, headship, salary)
values(‘E2006002’, ‘韩梅’, ‘F’, ‘1973-12-11’, ‘上海市’, ‘13817605008’, ‘1990-11-28’, ‘业务科’, ‘职员’, 2600.00)

insert into employee(employeeNo, employeeName, sex, birthday, address, telephone, hiredate, department, headship, salary)
values(‘E2006003’, ‘刘风’, ‘F’, ‘1973-5-21’, ‘南昌市’, ‘13817605008’, ‘1991-2-28’, ‘业务科’, ‘职员’, 2500.00)

insert into employee(employeeNo, employeeName, sex, birthday, address, telephone, hiredate, department, headship, salary)
values(‘E2007001’, ‘吴浮萍’, ‘M’, ‘1973-9-1’, ‘南京市’, ‘13817605008’, ‘1990-6-28’, ‘业务科’, ‘职员’, 2500.00)

insert into employee(employeeNo, employeeName, sex, birthday, address, telephone, hiredate, department, headship, salary)
values(‘E2007002’, ‘高代鹏’, ‘M’, ‘1973-1-2’, ‘南京市’, ‘13817605008’, ‘1991-11-28’, ‘办公室’, ‘文员’, 2000.00)

insert into employee(employeeNo, employeeName, sex, birthday, address, telephone, hiredate, department, headship, salary)
values(‘E2008001’, ‘陈诗杰’, ‘M’, ‘1968-1-6’, ‘南京市’, ‘13817605008’, ‘1990-12-6’, ‘财务科’, ‘出纳’, 3200.00)

insert into employee(employeeNo, employeeName, sex, birthday, address, telephone, hiredate, department, headship, salary)
values(‘E2008002’, ‘张良’, ‘M’, ‘1972-2-16’, ‘上海市’, ‘13817605008’, ‘1992-2-28’, ‘业务科’, ‘职员’, 2700.00)

insert into employee(employeeNo, employeeName, sex, birthday, address, telephone, hiredate, department, headship, salary)
values(‘E2008003’, ‘黄梅莹’, ‘F’, ‘1972-5-15’, ‘上海市’, ‘13817605008’, ‘1991-2-28’, ‘业务科’, ‘职员’, 3100.00)

insert into employee(employeeNo, employeeName, sex, birthday, address, telephone, hiredate, department, headship, salary)
values(‘E2008004’, ‘李虹冰’, ‘F’, ‘1972-10-13’, ‘南京市’, ‘13817605008’, ‘1990-5-28’, ‘业务科’, ‘职员’, 3400.00)

insert into employee(employeeNo, employeeName, sex, birthday, address, telephone, hiredate, department, headship, salary)
values(‘E2008005’, ‘张小梅’, ‘F’, ‘1970-11-6’, ‘深圳市’, ‘13817605008’, ‘1990-11-18’, ‘财务科’, ‘会计’, 5000.00)

insert into customer(CustomerNo, customerName, Address, telephone, zip)
values(‘c2005001’, ‘统一股份有限公司’, ‘022-3566021’, ‘天津市’, ‘220012’)

insert into customer(CustomerNo, customerName, Address, telephone, zip)
values(‘c2005002’, ‘兴隆股份有限公司’, ‘022-3566021’, ‘天津市’, ‘220301’)

insert into customer(CustomerNo, customerName, Address, telephone, zip)
values(‘c2005003’, ‘上海生物研究室’, ‘022-3566021’, ‘北京市’, ‘108001’)

insert into customer(CustomerNo, customerName, Address, telephone, zip)
values(‘c2005004’, ‘五一商厦’, ‘022-3566021’, ‘上海市’, ‘210100’)

insert into customer(CustomerNo, customerName, Address, telephone, zip)
values(‘c2006001’, ‘大地商城’, ‘022-3566021’, ‘北京市’, ‘100803’)

insert into customer(CustomerNo, customerName, Address, telephone, zip)
values(‘c2006002’, ‘联合股份有限公司’, ‘022-3566021’, ‘上海市’, ‘210100’)

insert into customer(CustomerNo, customerName, Address, telephone, zip)
values(‘c2007001’, ‘南昌电脑研制公司’, ‘022-3566021’, ‘南昌市’, ‘330046’)

insert into customer(CustomerNo, customerName, Address, telephone, zip)
values(‘c2007002’, ‘世界技术开发公司’, ‘022-3566021’, ‘上海市’, ‘210230’)

insert into customer(CustomerNo, customerName, Address, telephone, zip)
values(‘c2007003’, ‘万事达股份有限公司’, ‘022-3566021’, ‘天津市’, ‘220400’)

insert into customer(CustomerNo, customerName, Address, telephone, zip)
values(‘c2008001’, ‘红度股份有限公司’, ‘022-3566021’, ‘北京市’, ‘100800’)

insert into product(productNo, productName, productClass, productPrice)
values(‘p2005001’, ‘32M DRAM’, ‘内存’, 80.70)

insert into product(productNo, productName, productClass, productPrice)
values(‘p2005002’, ‘17寸显示器’, ‘显示器’, 700.00)

insert into product(productNo, productName, productClass, productPrice)
values(‘p2005003’, ‘120GB硬盘’, ‘存储器’, 300.00)

insert into product(productNo, productName, productClass, productPrice)
values(‘p2005004’, ‘3.5寸软驱’, ‘设备’, 35.00)

insert into product(productNo, productName, productClass, productPrice)
values(‘p2005005’, ‘键盘’, ‘设备’, 100.60)

insert into product(productNo, productName, productClass, productPrice)
values(‘p2006001’, ‘VGA显示卡’, ‘显示器’, 1200.60)

insert into product(productNo, productName, productClass, productPrice)
values(‘p2006002’, ‘网卡’, ‘设备’, 66.00)

insert into product(productNo, productName, productClass, productPrice)
values(‘p2006003’, ‘Pentium100CPU’, ‘处理器’, 200.00)

insert into product(productNo, productName, productClass, productPrice)
values(‘p2007001’, ‘1G DDR’, ‘内存’, 256.00)

insert into product(productNo, productName, productClass, productPrice)
values(‘p2007002’, ‘52倍速光驱’, ‘设备’, 200.00)

insert into product(productNo, productName, productClass, productPrice)
values(‘p2007003’, ‘计算机字典’, ‘图书’, 100.00)

insert into product(productNo, productName, productClass, productPrice)
values(‘p2007004’, ‘9600bits/s调制解调’, ‘设备’, 320.00)

insert into product(productNo, productName, productClass, productPrice)
values(‘p2008001’, ‘Pentium主板’, ‘主板’, 890.00)

insert into product(productNo, productName, productClass, productPrice)
values(‘p2008002’, ‘索泰克主板’, ‘主板’, 1100.00)

insert into product(productNo, productName, productClass, productPrice)
values(‘p2008003’, ‘纯平显示器’, ‘显示器’, 900.00)

insert into OrderMaster(OrderNo, CustomerNo, saleNo, OrderDate, OrderSum, InvoiceNo)
values(‘200801090001’, ‘c2005001’, ‘E2005002’, ‘2008-1-9’, 0.00, ‘I000000001’)

insert into OrderMaster(OrderNo, CustomerNo, saleNo, OrderDate, OrderSum, InvoiceNo)
values(‘200801090002’, ‘c2005004’, ‘E2005003’, ‘2008-1-9’, 0.00, ‘I000000002’)

insert into OrderMaster(OrderNo, CustomerNo, saleNo, OrderDate, OrderSum, InvoiceNo)
values(‘200801090003’, ‘c2008001’, ‘E2005002’, ‘2008-1-9’, 0.00, ‘I000000003’)

insert into OrderMaster(OrderNo, CustomerNo, saleNo, OrderDate, OrderSum, InvoiceNo)
values(‘200802090001’, ‘c2005001’, ‘E2005003’, ‘2008-2-19’, 0.00, ‘I000000004’)

insert into OrderMaster(OrderNo, CustomerNo, saleNo, OrderDate, OrderSum, InvoiceNo)
values(‘200802090002’, ‘c2007002’, ‘E2008002’, ‘2008-2-19’, 0.00, ‘I000000005’)

insert into OrderMaster(OrderNo, CustomerNo, saleNo, OrderDate, OrderSum, InvoiceNo)
values(‘200803010001’, ‘c2007002’, ‘E2008001’, ‘2008-3-1’, 0.00, ‘I000000006’)

insert into OrderMaster(OrderNo, CustomerNo, saleNo, OrderDate, OrderSum, InvoiceNo)
values(‘200803020001’, ‘c2005004’, ‘E2008003’, ‘2008-3-2’, 0.00, ‘I000000007’)

insert into OrderMaster(OrderNo, CustomerNo, saleNo, OrderDate, OrderSum, InvoiceNo)
values(‘200803090001’, ‘c2007003’, ‘E2008004’, ‘2008-3-9’, 0.00, ‘I000000008’)

insert into OrderMaster(OrderNo, CustomerNo, saleNo, OrderDate, OrderSum, InvoiceNo)
values(‘200805090001’, ‘c2006002’, ‘E2008002’, ‘2008-5-9’, 0.00, ‘I000000009’)

insert into OrderMaster(OrderNo, CustomerNo, saleNo, OrderDate, OrderSum, InvoiceNo)
values(‘200806120001’, ‘c2005001’, ‘E2005002’, ‘2008-6-12’, 0.00, ‘I000000010’)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200801090001’, ‘p20050001’, 5, 500)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200801090001’, ‘p20050002’, 3, 500)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200801090001’, ‘p20050003’, 2, 300)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200801090002’, ‘p20060002’, 5, 250)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200801090002’, ‘p20080001’, 5, 280)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200801090002’, ‘p20080002’, 4, 270)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200801090002’, ‘p20080003’, 2, 158)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200801090003’, ‘p20050001’, 5, 130)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200801090003’, ‘p20060001’, 3, 350)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200802190001’, ‘p20060003’, 4, 270)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200802190001’, ‘p20070001’, 2, 158)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200802190001’, ‘p20070002’, 5, 250)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200802190001’, ‘p20070003’, 3, 350)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200802190001’, ‘p20070004’, 2, 330)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200802190001’, ‘p20080001’, 2, 160)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200802190001’, ‘p20080002’, 3, 260)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200802190001’, ‘p20080003’, 1, 330)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200802190002’, ‘p20050003’, 2, 160)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200802190002’, ‘p20050005’, 3, 150)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200802190002’, ‘p20070001’, 3, 500)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200803010001’, ‘p20050001’, 8, 150)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200803010001’, ‘p20070001’, 4, 150)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200803020001’, ‘p20050001’, 2, 100)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200803020001’, ‘p20050002’, 1, 200)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200803020001’, ‘p20070003’, 3, 200)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200803090001’, ‘p20050003’, 4, 200)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200803090001’, ‘p20050004’, 5, 250)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200803090001’, ‘p20070001’, 2, 158)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200803090001’, ‘p20070002’, 5, 380)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200803090001’, ‘p20070004’, 3, 350)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200805090001’, ‘p20060003’, 8, 300)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200805090001’, ‘p20070001’, 4, 500)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200805090001’, ‘p20070002’, 2, 600)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200805090001’, ‘p20070003’, 5, 300)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200806120001’, ‘p20050004’, 2, 600)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200806120001’, ‘p20050005’, 3, 600)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200806120001’, ‘p20060001’, 1, 300)

insert into Orderdetail(OrderNo, productNo, Qty, price)
values(‘200806120001’, ‘p20060002’, 2, 280)

（2）将作废订单（发票号5197791779）由订单明细表中删除

delete from orderdetail where OrderNo = (select OrderNo from orderMaster where InvoiceNo ='5197791779')

（3）将上海的客户住址全都改为深圳

update customer set Address = '深圳市'
where Address = '上海市'

（4）将工作满2周年的员工薪水上调5%，工作满5周年的员工薪水上调8%

    update Employee set Salary = 1.05 * Salary
where Hiredate <= DATEADD(YEAR,-2,GETDATE()) and Hiredate > dateadd(year,-5,getdate())


update Employee set Salary = 1.08 * Salary
where Hiredate <= DATEADD(YEAR,-5,GETDATE())

update Employee set Salary = 1.08 * Salary
where Hiredate <= DATEADD(YEAR,-5,GETDATE())

（5）将客户c20090001在2009年1月购买的所有商品单价打9折

update product set ProductPrice = 0.9 * ProductPrice
from orderMaster a, orderDetail b, product c
where a.customerNo = 'c20090001' and 
CONVERT(varchar(120),a.Orderdate) like '2009-1%' 
and a.orderNo = b.OrderNo
and c.ProductNo = b.ProductNo

（6）根据订单明细表，修改订单主表的订单金额信息。

update orderMaster set ordersum =
(select SUM(Qty * price) from orderDetail 
 where orderDetail.OrderNo = orderMaster.OrderNo)

2．视图中数据的基本操作
（1）在实验二创建的视图上，对视图添加一条记录数据（注意：分别查看customer表和该视图的结果）
（2）删除视图中所有姓“王”的客户数据
（3）通过视图修改表内某一个客户的姓名
（4）对员工表和订单主表创建一个视图，该视图包含相同业务员的编号、姓名、订单号、订单金额。
（5）将上述视图中订单号为200808080808的记录的订单金额改为60000
（6）给上述视图添加一条记录数据
（7）删除上述视图
五、实验步骤
请完成实验内容，并写出具体的实验步骤

六、思考题：
1．请注意区别Create，alter，drop和insert，update，delete的异同和使用情况？
2．请思考基本表数据的更新和视图数据的更新的联系和区别？

七、总结（实验过程的体会、心得和实验教与学之间还需改进的内容）