数据库实验四数据更新与视图.docx
《数据库实验四数据更新与视图.docx》由会员分享，可在线阅读，更多相关《数据库实验四数据更新与视图.docx（14页珍藏版）》请在冰点文库上搜索。
数据库实验四数据更新与视图
实验四数据更新与视图
北京师范大学珠海分校信息技术学院
12计算机1班
一、数据更新
使用相应SQL语句，完成如下操作：
（1）根据订单信息更新order表中的payment（订单总金额）字段的值。
update"order"
set"order".payment=a.total
from（selectorderid,sum（price*quantity）astotal
frombook,orderbook
wherebookid=bid
groupbyorderid
）a
where"order".oid=a.orderid
selectoid,payment
from"order"
（2）新建图书订购情况统计表bookstas（包含图书编号、图书名称、图书类别、图书价格和订购册数，数据类型自定），并根据数据库的订单情况将社科类图书的订购情况插入表中。
图书订购情况统计表bookstas表
bsid
btitle
bcategory
bprice
ordersell
图书编号
图书名称
图书类别
图书价格
订购册数
int
varchar（50）
int
float
int
Primarykey
createtablebookstas
（
bsidintprimarykey,
btitlevarchar（50）,
bcategoryint,
bpricefloat,
ordersellint
）;
insert
intobookstas（bsid,btitle,bcategory,bprice,ordersell）
selectbid,title,category,price,sum（quantity）
fromorderbook,book
wherecategory='2'andbookid=bid
groupbybid,title,category,price;
selectbookstas.*
frombookstas;
（3）新建用户订购统计表userstas（包含用户编号、用户名、订单数量、总金额，数据类型自定），并根据数据库的订单情况将活跃用户（正常使用、锁定）的情况插入表中。
uid
uname
ordernum
pricesum
用户编号
用户名
订单数量
总金额
int
varchar（20）
int
float
Primarykey
createtableuserstas
（
uidintprimarykey,
unamevarchar（20）notnull,
ordernumint,
pricesumfloat,
）;
insert
intouserstas（uid,uname,ordernum,pricesum）
selectdistinctuid,name,count（"user"）,sum（payment）
from"order","user",userstate
where（usid=1orusid=2）and"user".state=usidand"user".uid="order"."user"
groupbyuid,name;
selectuserstas.*
fromuserstas;
（4）给用户表增加一字段level（等级），初始值为0，根据各用户订单总金额来给予评级（1:
100~200元，2：
200~300元，3:
300元以上）。
ALTERTABLE"user"ADDlevelintdefault0;
update"user"
setlevel=
（select（casewhenpricesum>100andpricesum<=200then1
whenpricesum>200andpricesum<=300then2
whenpricesum>300then3end）level
fromuserstas
where"user".uid=userstas.uid）
select"user".*
from"user";
（5）用户102通过提交了一订单购买1001号图书1本，请问如何在数据库中完成上述操作？
（假设订单号编号连续，要求完成订单信息、库存更新、订单总额及用户等级信息）。
insert"order"values（'2014008','102','4','0','2014-01-1013:
50:
33'）;
insertorderbookvalues（'2014008','1001','1'）;
update"order"
setpayment=sumTotal
from"order"a,
（selectorderid,sum（quantity*price）sumTotal
frombook,orderbookwherebookid=bidgroupbyorderid）b
wherea.oid=b.orderid
select"order".*
from"order"
updatebook
setstock=sumstock
frombooka,（
selectbid,title,category.category,price,（stock-sum（quantity））sumstock
frombook,category,orderbook
wherebook.category=category.cidandbookid=bid
groupbybid,title,category.category,price,stock）b
wherea.bid=b.bid
selectbook.*
frombook
update"user"
setlevel=
（select（casewhenpricesum>100andpricesum<=200then1
whenpricesum>200andpricesum<=300then2
whenpricesum>300then3end）level
fromuserstas
where"user".uid=userstas.uid）
select"user".*
from"user"
（6）给数据中库存在100本以上的图书增加库存，其中社科类图书分别增加200本、理工类图书分别增加300本。
updatebook
setstock=stock+200
wherestock>100andcategory=（selectcidfromcategorywherecid=2）
updatebook
setstock=stock+300
wherestock>100andcategory=（selectcidfromcategorywherecid=1）
selectbook.*
frombook
（7）将数据库中处于锁定状态的用户的状态更改为正常使用，同时重新设定其密码为’0000’。
update"user"
setstate=1,password='0000'
wherestate=（selectusidfromuserstatewhereuserstate='锁定'）
select"user".*
from"user"
（8）将数据库中订单状态为“未提交”的订单状态修改为“已提交”，同时给予该订单九折优惠。
update"order"
setstate=2,payment=payment*0.9
wherestate=（selectosidfromorderstatewhereorderstate='未提交'）
select"order".*
from"order"
（9）将数据库已经停用的用户及订单信息全部删除。
deletefromorderbook
whereorderidin（selectorderidfromorderbook,"order","user",userstate
whereuserstate='停用'and"user".state=usidanduid="user"andoid=orderid）
selectorderbook.*
fromorderbook
deletefrom"order"
where"user"in（select"order"."user"from"order","user",userstate
whereuserstate='停用'and"user".state=usidanduid="user"）
select"order".*
from"order"
deletefrom"user"
wherestatein（selectstatefrom"user",userstate
whereuserstate='停用'and"user".state=usid）
select"user".*
from"user"
二、视图
使用相应SQL语句，完成如下操作：
（1）创建视图View1，查看各订单的订单号、下单用户、订单总金额和订单状态。
createviewview1
as
selectoid,"user",payment,state
from"order"
（2）创建视图View2，查看各图书的编号、作者、名称、出版社、价格、订购册数。
createviewview2
as
selectbid,title,author,press,price,bookstas.ordersell
frombookstas,book
wherebsid=bid
（3）创建视图View4，查看社科类图书的基本情况，并保证以后对该视图的修改都要满足专业为“社科类图书”这个条件。
createviewview4
as
selectbid,title,author,press,price,book.category,stock
frombook,category
wherecategory.category='社会科学类'andcid=book.category
withcheckoption
（4）通过View1，查看订单总额在100元以上（含100元）且状态为已完成的订单号、下单用户、订单总金额。
selectoid,"user",payment
fromview1,orderstate
wherepayment>=100andstate=osidandorderstate='已完成'
（5）通过视图View2，查看订购册数在平均值以上的图书编号、作者、名称、出版社、价格、订购册数，并按订购册数降序排列。
selectbid,title,author,press,price,ordersell
fromview2
whereordersell>
（selectAVG（ordersell）
fromview2）
（6）通过视图View4，将库存在200本以上的图书的库存分别减少50本。
updateview4
setstock=stock-50
wherestock>200
（7）删除视图View1.
dropviewview1
升级会员 