数据库实验四数据更新与视图.docx

1、数据库实验四数据更新与视图实验四 数据更新与视图北京师范大学珠海分校信息技术学院12计算机1班一、数据更新使用相应SQL语句，完成如下操作：（1） 根据订单信息更新order表中的payment（订单总金额）字段的值。update orderset order.payment = a.totalfrom (select orderid,sum(price*quantity)as total from book,orderbook where bookid=bid group by orderid )awhere order.oid=a.orderidselect oid,paymentfrom

2、 order（2） 新建图书订购情况统计表bookstas（包含图书编号、图书名称、图书类别、图书价格和订购册数，数据类型自定），并根据数据库的订单情况将社科类图书的订购情况插入表中。图书订购情况统计表bookstas表bsidbtitlebcategorybpriceordersell图书编号图书名称图书类别图书价格订购册数intvarchar(50)intfloatintPrimary keycreate table bookstas( bsid int primary key, btitle varchar(50) , bcategory int , bprice float, orde

3、rsell int );insertinto bookstas(bsid,btitle,bcategory,bprice,ordersell)select bid,title,category,price,sum(quantity)from orderbook,bookwhere category=2and bookid=bidgroup by bid,title,category,price;select bookstas.*from bookstas;（3） 新建用户订购统计表userstas(包含用户编号、用户名、订单数量、总金额，数据类型自定)，并根据数据库的订单情况将活跃用户（正常使

4、用、锁定）的情况插入表中。uidunameordernumpricesum用户编号用户名订单数量总金额intvarchar(20)intfloatPrimary keycreate table userstas( uid int primary key, uname varchar(20) not null , ordernum int , pricesum float,);insertinto userstas(uid,uname,ordernum,pricesum)select distinct uid,name,count(user),sum(payment)from order,use

5、r,userstatewhere (usid=1 or usid=2) and user.state=usid and user.uid=order.usergroup by uid,name;select userstas.*from userstas;（4） 给用户表增加一字段level（等级），初始值为0，根据各用户订单总金额来给予评级（1:100200元，2：200300元，3:300元以上）。ALTER TABLE user ADD level int default 0;update userset level= (select (case when pricesum100 and

6、 pricesum200 and pricesum300 then 3 end) level from userstas where user.uid=userstas.uid)select user.*from user;（5） 用户102通过提交了一订单购买1001号图书1本，请问如何在数据库中完成上述操作？（假设订单号编号连续，要求完成订单信息、库存更新、订单总额及用户等级信息）。insert order values(2014008,102,4,0,2014-01-10 13:50:33);insert orderbook values(2014008,1001,1);update o

7、rderset payment=sumTotalfrom order a, (select orderid,sum(quantity*price) sumTotal from book,orderbook where bookid=bid group by orderid) bwhere a.oid=b.orderidselect order.*from orderupdate bookset stock=sumstockfrom book a,(select bid,title,category.category,price,(stock -sum(quantity) sumstockfro

8、m book,category,orderbookwhere book.category=category.cid and bookid=bid group by bid,title,category.category,price,stock) bwhere a.bid=b.bidselect book.*from bookupdate userset level= (select (case when pricesum100 and pricesum200 and pricesum300 then 3 end) level from userstas where user.uid=users

9、tas.uid)select user.*from user（6） 给数据中库存在100本以上的图书增加库存，其中社科类图书分别增加200本、理工类图书分别增加300本。update bookset stock=stock+200where stock100 and category =(select cid from category where cid=2)update bookset stock=stock+300where stock100 and category =(select cid from category where cid=1)select book.*from boo

10、k（7） 将数据库中处于锁定状态的用户的状态更改为正常使用，同时重新设定其密码为0000。update userset state=1,password=0000where state = (select usid from userstate where userstate=锁定)select user.*from user（8） 将数据库中订单状态为“未提交”的订单状态修改为“已提交”，同时给予该订单九折优惠。update orderset state=2,payment=payment*0.9where state =(select osid from orderstate where

11、orderstate=未提交)select order.*from order（9） 将数据库已经停用的用户及订单信息全部删除。delete from orderbookwhere orderid in (select orderid from orderbook,order,user,userstate where userstate=停用 and user.state=usid and uid=user and oid=orderid)select orderbook.*from orderbookdelete from orderwhere user in (select order.u

12、ser from order,user,userstate where userstate=停用 and user.state=usid and uid=user)select order.*from orderdelete from userwhere state in (select state from user,userstate where userstate=停用 and user.state=usid)select user.*from user二、视图使用相应SQL语句，完成如下操作：（1）创建视图View1，查看各订单的订单号、下单用户、订单总金额和订单状态。create v

13、iew view1asselect oid,user,payment,statefrom order（2）创建视图View2，查看各图书的编号、作者、名称、出版社、价格、订购册数。create view view2asselect bid,title,author,press,price,bookstas.ordersellfrom bookstas,bookwhere bsid=bid（3）创建视图View4，查看社科类图书的基本情况，并保证以后对该视图的修改都要满足专业为“社科类图书”这个条件。create view view4as select bid,title,author,pres

14、s,price,book.category,stockfrom book,categorywhere category.category=社会科学类 and cid=book.categorywith check option（4）通过View1，查看订单总额在100元以上（含100元）且状态为已完成的订单号、下单用户、订单总金额。select oid,user,paymentfrom view1,orderstatewhere payment=100 and state=osid and orderstate=已完成（5）通过视图View2，查看订购册数在平均值以上的图书编号、作者、名称、出版社、价格、订购册数，并按订购册数降序排列。select bid,title,author,press,price,ordersellfrom view2 where ordersell (select AVG(ordersell)from view2)（6）通过视图View4，将库存在200本以上的图书的库存分别减少50本。update view4set stock=stock-50where stock200（7）删除视图View1.drop view view1