叶磊数据库文档格式.docx

1、-（订单号、成交时间、订单总金额、订单状态及所有订单的平均金额），并成交时间降序排列。create procedure proc1user_id varchar（20）asselect oid,ordertime,payment,orderstate,avgpayment,sumpaymentfrom order,orderstate, （select avg（payment） avgpayment from where user = user_id） a, （select sum（payment） sumpayment = user_id） bwhere .state = orderstat

2、e.osid and = user_idorder by ordertime descgoexec proc1 102-新建一存储过程proc2,通过输入订单号、书目ID及数量，-实现给该订单增加商品、更新订单总金额，并返回当前订单包含的商品总数（商品类别数量）。create procedure proc2（order_id varchar（20）,book_d varchar（20）,book_s varchar（20）update set payment = payment + pricefrom book,where oid in（ select orderid from orderbo

3、ok where bookid = book_d） and bid = book_dupdate orderbookset quantity = quantity + book_swhere orderid = order_idselect bookidfrom orderbookexec proc2 2014001,1001,4-新建一存储过程proc3，通过修改指定订单、指定商品的价格，并返回该订单的新总金额及优惠金额。create procedure proc3book_id varchar（20）,s_price varchar（20）set payment = payment + （

4、s_price - price） * quantity） from book,orderbookwhere bookid = book_id and（ bid = book_id and orderid = order_id）select *where oid = order_idexec proc3 2014001,1001,20-新建一存储过程proc4，统计各订单总金额的分布情况。- 0-50元、-100元、-150元、-200元和元以上。create procedure proc4begin declare zerotofifty int, fiftytofundred int, hu

5、ndredtohunf int, hunftoTwoh int, moretwoh int; select zerotofifty = COUNT（case when payment = 0 AND payment = 51 AND payment = 100 then 2 hundredtohunf = COUNT（case= 101 AND payment = 150 then 3 hunftoTwoh = COUNT（case= 151 AND payment = 200 then 4 moretwoh = COUNT（case= 201 then 5 end） from print z

6、erotofifty; print fiftytofundred; print hundredtohunf; print hunftoTwoh; print moretwohendexec proc4-新建一存储过程proc5，实现查询购买过指定书目ID的用户还同时-购买过的购买次数最多的前名书目名称及购买次数，并按照购买次数排序。create procedure proc5book_id varchar（20） declare user_id int; select user_id = uid from orderbook, where bookid = book_id and orderi

7、d = oid and . = uid select uid,name,oid,quantity,a.bookid,a.title,orderbook,（select top 3 sum（quantity） sumquantity,bookid,title from orderbook,book where bookid = bid group by bookid,title order by sumquantity desc） a where uid = user_id and uid = and oid = orderid and orderbook.bookid = a.bookid o

8、rder by a.sumquantity descexec proc5 1003-新建一存储过程proc6，实现查询与指定用户ID购买过-相同商品的用户及购买过相同商品的数量，并按照相同商品数量降序排列。create procedure proc6 select a.uid,a.name,a.bookid,a.quantity,（select uid,name,bookid,quantity from orderbook, where orderid = oid and = uid） a where .uid = user_id = .uid .oid = orderbook.orderid

9、 and a.uid != .uid and a.bookid = orderbook.bookid group by a.uid,a.name,a.bookid,a.quantity order by quantity descexec proc6 102-创建一触发器tg1，当往orderbook表中增加记录时，-实现商品库存相应减少；create trigger tg1on orderbook for insert,update declare order_id int, book_id int, quantity int; select book_id = bookid,quantit

10、y = quantity from inserted; update book set book.stock = book.stock - quantity where book_id = book.bid; print insert succeed; declare book_idf int, quantityf int, bookido int, quantityo int, lst int; select bookido = bookid,quantityo = quantity from deleted; select book_idf = bookid,quantityf = qua

11、ntity set book.stock = book.stock - quantityf + quantityo where book_idf = book.bid;update succeeddrop trigger tg1-创建一触发器tg2，当往orderbook中增加记录时，-如果商品库存量少于，则不允许购买该商品，并给出相应提示。create trigger tg2on orderbook after insertas declare stock int,bookid int; select bookid = s.bookid from inserted s; select sto

12、ck = b.stock from book b where b.bid = bookid; if（stock 10） raiserror（库存少于，不允许购买,16,1）; rollback tran;drop trigger tg2-新建图书订购情况统计表bookstas（包含图书编号、图书名称、图书类别、图书价格和订购册数，数据类型自定），-并根据数据库的订单情况将社科类图书的订购情况插入表中。创建一触发器tg3，-当往orderbook表中增加记录时，自动更新bookstas表相应图书的统计信息create table bookstas（ id int primary key, tit

13、le varchar（20） not null, category varchar（20） not null, price float not null, quantity int）;create trigger tg3for insert declare bookid int, select bookid = bookid,quantity = quantity update bookstas set quantity = quantity + quantity where id = bookiddrop trigger tg35 执行过程截屏6 总结 存储过程Procedure是一组为了完

14、成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。1:触发器是一种特殊的存储过程它不能被显式地调用而是在往表中插入记录更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约束。2:SQL Server为每个触发器都创建了两个专用表Inserted表和Deleted表。这两个表由系统来维护它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行 完成后与该触发器相关的这两个表也被删除。