数据库表更新、视图和索引——沐雨先生

沐雨先生

已于 2024-03-06 10:22:19 修改

阅读量346

点赞数 4

分类专栏： LNU数据库原理实验文章标签：数据库 sql

于 2024-02-24 22:01:05 首次发布

本文链接：https://blog.csdn.net/qq_55376781/article/details/136276457

版权

LNU数据库原理实验专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、实验目的及原理

1. 掌握SQL中与表更新有关的命令

熟悉Insert命令
熟悉Update命令
熟悉Delete命令

2.掌握SQL中与视图有关命令

3.掌握创建索引命令

二、实验用品及设备

1. PC机

2. 操作系统Windows xp, Windows 7, Windows 10等

3. 要求有一DBMS软件,例如SQL Sever2008,MySQL等

i.实验内容及步骤

Retail Database Table Structure
零售数据库表结构

Customer( CustNO, FName, LName, Street, Suburb, Postcode, Balance)
顾客（顾客号，名，姓，街道，城市，邮政编码，余额）
Department( DepartNO, DepartName)
部门（部门号，部门名）
SalesRep( SalesRepNO, SalesRepLastName, SalesRepFName, DepartNOfk)
销售代表（销售代表号，姓，名，部门号）
Order( OrderNO, CustNOfk, OrdDate, SalesRepNOfk, OrdStatus)
订单（订单号，顾客号，订单日期，销售代表号，订单状态）
OrderLine( OrderNO fk, ProductNOfk, QtyPurchased, PurchasePrice)
订单明细（订单号，商品号，购买数量，销售价格）
Product( ProductNO, ProdDescription,QtyOnHand, UnitPrice)
商品（商品号，商品名，库存量，单价）
numofOrders(CustNO, Fname, Lname, numberofOrders)
顾客订单数（顾客号，名字，姓，订单数）
totalSale(SalesRepNO, Fname, Lname, total)
销售总量（销售代表编号，名字，姓，销售总额）
题目：

新增一个部门，部门信息为部门编号07，部门名称为TECH
insert into department values('07','TECH');
新来一名顾客，顾客号为2001，名字为Robert(名) Bill（姓），余额为1000
insert into customer(custno,fname,lname,balance) values ('2001','Robert','Bill',1000);
将每个顾客的订单数，保存到numofOrders表里。
insert into numoforders select c.custno,fname,lname,COUNT(*) from orders o,Customer c group by c.custno,fname,lname;
将每个销售代表的总销售金额保存到totalSale表里。
insert into totalSale select s.SalesRepNo ,SalesRepFName ,SalesRepLastName ,COUNT(QtyPurchased *PurchasePrice ) from SalesRep s,Orders o,Orderline od where s.SalesRepNo =o.SalesRepNo and o.OrderNO =od.OrderNo group by s.SalesRepNo ,SalesRepFName ,SalesRepLastName;
将每个商品单价上调5%。
update Product set UnitPrice =1.05*UnitPrice ;
把购买laser Printer的顾客的余额加100元
update Customer set Balance =Balance +100 where CustNo in (select c.CustNo from Customer c,Orders o,Orderline od,Product p where c.CustNo =o.CustNO and o.OrderNO =od.OrderNo and od.ProductNo =p.ProductNo and p.ProdDescription ='laser Printer');
订单数超过2的商品的单价上调5%
update Product set UnitPrice =UnitPrice *1.05 where ProductNo in (select p.ProductNo from Customer c,Orders o,Orderline od,Product p,numofOrders n where c.CustNo =o.CustNO and o.OrderNO =od.OrderNo and od.ProductNo =p.ProductNo and n.CustNo =c.CustNo and n.numberofOrders >2);
根据下图,对相关表进行相应处理.
insert into Orders (OrderNO ,CustNO ,OrdDate) values('12900','2567','8/9/2015');
insert into Orderline (OrderNo ,ProductNo ,QtyPurchased ,PurchasePrice) values('12900','AX12','5','20');
insert into Orderline (OrderNo ,ProductNo ,QtyPurchased ,PurchasePrice) values('12900','BT04','9','129');
update numofOrders set numberofOrders =numberofOrders +1 where CustNo ='2567';
创建视图V1，显示顾客和他们购买商品的信息。(要求有个顾客姓名,购买商品日期,商品名,数量,单价,小计(数量*单价)
create view V1 as select fname,lname,orddate,proddescription,QtyPurchased ,PurchasePrice ,QtyPurchased *PurchasePrice xiaoji from Customer c,Orders o,Orderline od,Product p where c.CustNo =o.CustNO and o.OrderNO =od.OrderNo and od.ProductNo =p.ProductNo;
创建视图V2，显示销售代表（名）及其所在的部门名
create view V2 as select SalesRepLastName ,DepartName from SalesRep s,Department d where s.DepartNo =d.DepartNo ;
创建视图V3，显示销售代表(姓名)，部门名和他们的业绩（有多少订单）
create view V3 as select SalesRepFName ,SalesRepLastName ,DepartName,COUNT(*) yeji from SalesRep s,Department d,Orders o where s.DepartNo =d.DepartNo and s.SalesRepNo =o.SalesRepNo group by SalesRepFName ,SalesRepLastName ,DepartName;
在V1视图上，查询每名顾客一共消费的了多少钱，显示顾客名，总消费金额
select fname,lname,SUM(xiaoji) sum from V1 group by fname,lname;
在V3视图上，查询订单数超过3个的销售代表姓名，部门，订单数量
select SalesRepFName ,SalesRepLastName ,DepartName ,yeji from V3 where yeji>3;
删除Tech部门
delete from Department where DepartName ='Tech';
把订单数为零的销售代表消息全部删除
delete from SalesRep where SalesRepNo in (select SalesRepNo from totalSale where total =0);
在商品表上按商品名升序建立索引
create index prode on product(proddescription);
在销售代表表上按部门号升序建立索引，如果部门号相同，按姓名降序建立索引。
create index sales on salesrep(departno asc,salesreplastname desc,salesrepfname desc);