insertinto bookdetail values('kp','10010',' ') insertinto bookdetail values('sw','10011',' ') insertinto bookdetail values('kp','10012',' ')
insertinto orders values('0001','0001','张宇','北京','山那边',15)
insertinto orderdetail values( 6501001,'0001',2015/12/30)
altertable orderdetail withnocheck
addconstraint FK_OD_O FOREIGNKEY(orderdetailno)references orders(orderdetailno) ondeletecascade
--如何确定order里的名字必须在userinfo中存在 altertable orders withnocheck
addconstraint FK_u_o_update FOREIGNKEY(userno)references userinfo(userno) onupdatecascade
33
--外键
altertable bookdetail withnocheck addconstraint book(bookno) ondeletecascade --用户订单级联删除触发器 createtrigger tr_u_o on userinfo fordelete asdeclare @uname_del char(20) select @uname_del= uname from deleted deletefrom orders where uname=@uname_del go
--订单和订单详情的级联删除触发器 createtrigger tr_order_detail on orders fordelete as
declare @orderno_del char(10)
select @orderno_del= orderno from deleted deletefrom orders where orderno=@orderno_del go
--表值函数
34
FK_BD_B FOREIGNKEY(bookno)references
createfunction U_Orders_fun(@uname_in char(20)) returnstable as
return (select userinfo.userno 编号,userinfo.uname 姓名,
userinfo.address 地址,bname 书名,price 价格,orderno 订单号
from userinfo,orders,orderdetail where userinfo.uname=orders.uname and
orders.orderdetailno=orderdetail.orderdetailno and userinfo.uname=@uname_in) go
select*from U_Orders_fun('王宇')
--购书情况表值函数
createfunction B_Orders_fun(@bname_in varchar(20)) returnstable as
return (select book.bname 书名,book.price 价格,orderno 订单号
from book,orders
35
where book.bname=orders.bname and book.bname=@bname_in) go
select*from B_Orders_fun('羊皮卷')
--用户买书的详细情况视图
createview userifro_o(uname,address,orderno,bname) as
select userinfo.uname, userinfo.address,orderno,bname from userinfo,orders
where orders.uname=userinfo.uname and orderno isnotnull
--书籍情况的视图
createview book_ca( bname,pdate,exist ,remark) as
selectdistinct bname,pdate,exist,remark from book,catagory,bookdetail
where book.bookno=bookdetail.bookno and bookdetail.crono=catagory.crono
--数据库备份
36