Oracle觸發(fā)器,過程等的實驗

1、實驗四： PL/SQL 程序設(shè)計、實驗?zāi)康恼莆?PL/SQL 程序設(shè)計基本技巧，包括基本數(shù)據(jù)類型、表類型、數(shù)組類型、匿名程 序塊、控制語句、 PL/SQL 中使用 SQL 語句、游標(biāo)、錯誤處理等。熟悉和掌握 PL/SQL 中關(guān)于存儲過程、函數(shù)、包和觸發(fā)器程序設(shè)計技術(shù)。二、實驗內(nèi)容某餐飲系統(tǒng)數(shù)據(jù)庫， 請創(chuàng)建如下各數(shù)據(jù)表，并實現(xiàn)如下存儲過程、 函數(shù)、包和觸發(fā)器等功能設(shè)計，將程序腳本保存到文本文件 Source.sql 中：1)菜肴類別表 MK (菜肴類別編號 MKid ，菜肴類別名稱MkName ),菜肴類別名稱：魚類、蔬菜類、涼菜類、肉類、主食類和酒水類等。菜單信息表 MList (菜肴編號 M

2、id ，菜肴名稱 Mname，菜肴類別 MKid ，菜肴單價Mprice ，菜肴成本單價 Mcost，更新日期 Mdate) 。餐臺類別表 DK (餐臺類別編號DKid ，餐臺類別名稱DkName )，餐臺類別：包間和散臺等。餐臺信息表 Dinfo ( 餐臺編號Did ，餐臺名稱 Dname ，餐臺類別 DKid ，座位數(shù)Dseats ，更新日期 Ddate) 。5)消費單主表 C (消費單號 Cid，餐臺編號 Did ，消費開始時間 StartTime ，結(jié)賬時間EndTime，消費金額合計 Smoney，盈利金額合計 SPsum），其中，消費金額合計即 SUM (消=消費單明細(xì)表CList

3、中該消費單號的所有消費記錄的消費金額的合計,費金額）或SUM （菜肴單價 X消費數(shù)量），盈利金額合計=消費單明細(xì)表 CList中該消費單號的所有消費記錄的盈利合計，即SUM （菜肴單價-菜肴成本單價）X消 費數(shù)量）。（6）消費單明細(xì)表 CList （消費單號Cid，序號Sid ,菜肴編號Mid，菜肴名稱Mname ,消費數(shù)量Cqty，菜肴單價Mprice，菜肴成本單價 Mcost ,消費金額Cmoney），消費金額=消費數(shù)量X菜肴單價；消費數(shù)量為 正數(shù)是正常點菜，消費數(shù)量為 負(fù)數(shù)是退菜,三、實驗步驟及相關(guān)程序截圖3.1 創(chuàng)建表空間 RESTAURANT,創(chuàng)建用戶DINER 3.1.1創(chuàng)建表空間

4、 RESTAURANT，大小10M。3.1.2創(chuàng)建用戶 DINER，口令 XXX，默認(rèn)表空間 RESTAURANT，給該用戶授予角色權(quán)限CONNECT 、 RESOURCE。F:oradataorclrestautant_1.dbf size 10M;Create tables pace restautant datafileCreate user diner identified by zwl2011 default tables pace restaurant ;Grant connect ,resource to diner;3.2創(chuàng)建餐飲系統(tǒng)數(shù)據(jù)庫的所有表，并向各表插入演示數(shù)據(jù)。3.2

5、.1創(chuàng)建實驗內(nèi)容中的餐飲系統(tǒng)數(shù)據(jù)庫的所有表（菜肴類別表MK、菜單信息表 MList、餐臺類別表DK、餐臺信息表Dinfo、消費單主表C、消費單明細(xì)表CList） Ovarchar2(20);Create table MK (MKid number(2) primary key,MKnameCreate table DK(DKid number(2) primary key,DKname varchar2(20);Create table MList(Mid number(2) primary key,Mname varchar2(20),MKid number(2) referencesMK(

6、MKid),M price number(7,2),Mcost number(7,2),Mdate date);Create table Dinfo(Did number(2) p rimary key,Dname varchar2(20), DKid number(2) referencesDK(DKid),Dseats number(4),Ddate date);Create table CList(Cid number(2),Sid number(2),primary key(Cid,Sid),Mid number(2) referencesMList(Mid),Mname varcha

7、r2(20),Cqty number(4),M price number(7,2),Mcost number(7,2),Cmoney number(7,2);date,EndTime date,Smoney number(7,2), Sp sum number(7,2);ELf !斤世ptfET-sr V7. Prm*- v4rcLo-(Sn PKL J -nizrhy i2frirm: U M: Jl,iml卄時曰尺：/T TfnCla JtLwbfeT 住pccrir ki-r. lt.n-i- vk*uJiktZ 空J(rèn). IKd e.lab-iT (SO-H1C+X D(XXailJ D

8、iAfetB nLnb-iE-(4)_ld.fel-i dhl:CLlgHEbUJit燈 列pif迂r Lrp-. Sid unhir匚F_Ls.tLUhd.Jhi-w bucL址2口DJ.巾號_1*丁, U尹也斗訊白畫5心血皿tiibdjaC (TlI iiai-44e- GJ |AiJ iK i*?, Cid i.aht 上 qelcee. IjhIj P.dJ,3t-tlLririii 42 In訂】rw JjiHiihuUF ziiMibut (|, SJ“ JsujT XI /rr*+3.2.2依次向菜肴類別表MK、菜單信息表 MList、餐臺類別表DK、餐臺信息表 Dinfo插入足

9、夠多的演示數(shù)據(jù)。將日期的表示形式改變:Alter session set nis date format=yyyy-mm-dd(1 )菜肴類別表MKInsert into MK values(01,魚類);Insert into MK values(02.蔬菜類);Insert into MK values(03,涼菜類);Insert into MK values(04,肉類);Insert into MK values(05,主食類);Insert into MK values(06,酒水類);片 J亠 B.U； L EI U nr- V J IL 3 J,Eg / 1kg J-EhC4r

10、9L!VL Tustrt 1 Tf 0 VK vaCii尸弓 COS-rnw 1 n-r lri工 4X:七 LTllO WK kCu工 EWj沖菜；;r);rcw inzrld此,丄山匕出i. iiLlo VK V丄uub IJJ|幻弟*丿.rev ijL=r ld膽】Insert in VK vua COG士主供#-):9L TBiPT t iTVTi WK vain* OlFi-1 VW 111*91 1 wL(2 )菜單信息表MListInsert into MList values(01,，鱈魚，,01,30,24, 2012-05-17)Insert into MList valu

11、es(02,，鯉魚，,01,42,28, 2012-05-17)Insert into MList values(03,，帶魚，,01,25,20, 2012-05-20)Insert into MList values(04,黃花魚,01,34,28, 2012-05-20?Insert into MList values(05,，鲅魚，,01,18,15, 2012-05-30);Insert into MList values(06,芹菜 ,02,15,12, 2012-06-01 )Insert into MList values(07,韭菜 ,02,10,8, 2012-06-10

12、);Insert into MList values(08,白菜豆腐 ,02,13,10, 2012-06-15Insert into MList values(09,菠菜 ,02,8,5, 2013-04-05 );Insert into MList values(10,花生米 ,03,12,10, 2013-04-05 Insert into MList values(11,皮蛋豆腐 ,03,13,10, 2013-05-06Insert into MList values(12,海帶 ,03,7,4, 2013-05-06 );Insert into MList values(13,辣白

13、菜 ,03,8,5, 2013-06-01 );Insert into MList values(14,豬肉 ,04,34,25, 2013-06-01 )Insert into MList values(15,牛排 ,04,48,40, 2013-12-01 )Insert into MList values(16,羊排 ,04,45,36, 2013-12-01 )Insert into MList values(17,燉雞 ,04,42,38, 2013-12-31 )Insert into MList values(18,豬頭肉 ,04,28,24, 2013-12-31 Insert

14、 into MList values(19,豬蹄 ,04,36,30, 2014-01-01 )Insert into MList values(20,宮保雞丁 ,04,20,18, 2014-01-10Insert into MList values(21,米飯 ,05,3,2.5, 2014-02-01 );Insert into MList values(22,饅頭 ,05,2,1.5, 2014-02-01 );Insert into MList values(23,面條 ,05,4,2.6, 2014-03-01 );Insert into MList values(24,青島啤酒

15、,06,5,3.5, 2014-03-01Insert into MList values(25,散白酒 ,06,3,1.5, 2014-03-01 Insert into MList values(26,雪花啤酒 ,06,6,4, 2014-04-01 Insert into MList values(27,哈爾濱啤酒 ,06,5,4, 2014-04-12Insert into MList values(28,燕京啤酒 ,06,5,4, 2014-04-12 Insert into MList values(29,老村長 ,06,15,12, 2014-04-12 Insert into

16、MList values(30,林海雪原 ,06,18,14, 2014-04-12);););););););););););); IP J JB _亠 kNLAC LnluULIlLLZTda博；JL 廠耳？ 1 -S =in-n?-n|-)Jr DIV 3 Xk-LBT- J LiA ItLh t h L tit ULlkt*筑P!bl；er ,00,?. 3 S/Q 4 -D3-OJ ) jTO卑l(fā)td L、Tiivxt L. Anr.iift3八 -i-.i-ra-or ?.r D4 f i -I _A 11LX r t L nl fTLiiti-ni 口(3 )餐臺類別表DKIns

17、ert into DK values(01.大包);Insert into DK values(02,中包);Insert into DK values(03,小包);Insert into DK values(04,散臺);CLi*Lo IK 皚iurat?人rnv ilZRrtpJUA Lr.ztrt 1B.W IJK vilu也2 牛乜丿；iTiTirl ig HI rraliiPH(03/小也riV TTRartdJ口 Lr.=.erL BE value a (4)餐臺信息表Di nfoInsert into Dinfo values(01,，大包一,01,50,2012-01-01)

18、；Insert into Dinfo values(02,大包二,01,45,2012-01-01);Insert into Dinfo values(03,大包三,01,40,2012-01-01);Insert into Dinfo values(04,，中包一,02,30,2013-04-05);Insert into Dinfo values(05,中包二,02,25,2013-04-05);Insert into Dinfo values(06,中包三,02,20,2013-07-01);Insert into Dinfo values(07,中包四,02,20,2013-08-01

19、);Insert into Dinfo values(08,，小包一,03,15,2014-01-01);Insert into Dinfo values(09,小包二,03,15,2014-01-01);Insert into Dinfo values(10,小包三,03,10,2014-01-01);Insert into Dinfo values(11,小包四,03,10,2014-04-01);Insert into Dinfo values(12,，散臺一,04,8,2014-04-01);Insert into Dinfo values(13,散臺二,04,8,2014-04-01

20、);Insert into Dinfo values(14,散臺三,04,6,2014-04-01);Insert into Dinfo values(15,散臺四,04,6,2014-04-10);Insert into Dinfo values(16.Insert into Dinfo values(17,Insert into Dinfo values(18,Insert into Dinfo values(19,Insert into Dinfo values(20,散臺五,04,4,2014-04-10散臺六,04,4,2014-04-15散臺七,04,4,2014-04-15散臺八

21、,04,2,2014-04-20散臺九,04,2,2014-04-2003. 10. SOK-fX-Or J：););););); VW- 1.MUBLSee*414s4l L.trov instrttJ具I. I Al u Di aI u vHm. 4 4 E, MVStInwTt in+o Dinfo valoes f4.Tierih 合二m3Cl 4 Etnq04. 5/ 20L*fH-or 01-01 fftrzrw-t=f Jr Dv 1 na r tdIii.-u L J ACu LaniV vuiuC lbt uv iJkVvrLv JTn-r* iniI?3.3完成【實驗內(nèi)容】

22、中的觸發(fā)器、存儲過程、函數(shù)和程序包等功能設(shè)計 3.3.1完成【2.1觸發(fā)器設(shè)計】（1 ）為消費單明細(xì)表 CList定義一個觸發(fā)器，每插入（INSERT） 條消費單明細(xì)記錄（消費單號，序號,菜肴編號，消費數(shù)量），自動根據(jù)菜肴編號從菜單信息表MList中讀取菜肴名稱Mname、菜肴單價Mprice、菜肴成本單價 Mcost ,然后計算其消費金額（消費數(shù)量X菜肴單價）、以及消費單主表C的消費金額合計、盈利金額合計。編寫相應(yīng)的插入語句（INSERT ）和查詢語句（SELECT）測試該觸發(fā)器效果。create or rep lace trigger In sertClist before in sert

23、 on Clist for each row declare v_Smoney C.Sm on ey%ty pe;v_SP sum C.S Psum%t ype;SelectBeg inMn ame, Mp rice,Mcost,: new.Cqty* Mp riceinto :new.Mname,:new.Mprice,:new.Mcost,:new.CmoneyfromMlistintowhere Mlist.Mid=:new.Mid;Select nvl(sum(Cqty*Mprice),0),nvl(sum(Cqty*(Mprice-Mcost),0)setv_Smoney,v_SPs

24、um from Clist where Clist.Cid=:new.Cid;updateSmoney=v_Smoney+:new.Cmoney,SPsum=v_SPsum+(:new.Cqty*(:new.Mprice-:new.Mcost) where C.Cid=:new.Cid;end InsertClist;測試觸發(fā)器：insert into CList(Cid,Sid,Mid,Cqty) values(1,1,1,1);insertintoCList(Cid,Sid,Mid,Cqty) values(1,2,6,3);insert into CList(Cid,Sid,Mid,Cq

25、ty) values(1,3,14,2);insertintoCList(Cid,Sid,Mid,Cqty) values(1,4,22,10);insert into CList(Cid,Sid,Mid,Cqty)values(1,5,28,6);insertintoCList(Cid,Sid,Mid,Cqty) values(1,6,29,2);insert into CList(Cid,Sid,Mid,Cqty)values(2,1,2,2);insertintoCList(Cid,Sid,Mid,Cqty) values(2,2,3,1);insert into CList(Cid,S

26、id,Mid,Cqty)values(2,3,9,2);insertintoCList(Cid,Sid,Mid,Cqty) values(2,4,21,5);insert into CList(Cid,Sid,Mid,Cqty)values(2,5,28,6);insertintoCList(Cid,Sid,Mid,Cqty) values(2,6,14,2);insert into CList(Cid,Sid,Mid,Cqty)values(2,7,20,1);insertintoCList(Cid,Sid,Mid,Cqty) values(3,1,4,2);insert into CLis

27、t(Cid,Sid,Mid,Cqty) values(3,2,9,3);insertintoCList(Cid,Sid,Mid,Cqty) values(3,3,23,5);insert into CList(Cid,Sid,Mid,Cqty)values(3,4,15,2);insertintoCList(Cid,Sid,Mid,Cqty) values(4,1,4,2);insert into CList(Cid,Sid,Mid,Cqty)values(4,2,16,3);insertintoCList(Cid,Sid,Mid,Cqty) values(4,3,13,2);insert i

28、nto CList(Cid,Sid,Mid,Cqty)values(4,4,22,6);nsertintoCList(Cid,Sid,Mid,Cqty) values(4,5,28,2);insert into CList(Cid,Sid,Mid,Cqty)values(5,1,4,2);insertintoCList(Cid,Sid,Mid,Cqty) values(5,2,8,3);insert into CList(Cid,Sid,Mid,Cqty)values(5,3,20,2);insertintoCList(Cid,Sid,Mid,Cqty) values(5,4,22,5);in

29、sert into CList(Cid,Sid,Mid,Cqty)values(6,1,2,1);insertintoCList(Cid,Sid,Mid,Cqty) values(6,2,19,2);insert into CList(Cid,Sid,Mid,Cqty)values(6,3,16,3);insertintoCList(Cid,Sid,Mid,Cqty) values(6,4,23,6);改 變 時 間格 式 ： Alter session set nls_date_format=yyyy-mm-ddhh24:mi:ss;向 C 表中插入其他信息：values(1,2,2013-

30、3-15insert into C(Cid,Did,StartTime,EndTime)18:30:00,2013-3-15 20:10:15);insert into C(Cid,Did,StartTime,E ndTime)values(2,15,2014-4-112:30:00,2014-4-1 14:20:00);in sertintoC(Cid,Did,StartTime,E ndTime)values(3,20,2014-4-1217:30:02,2014-4-12 19:20:06);insert into C(Cid,Did,StartTime,E ndTime)values(

31、4,9,2014-4-1812:10:05,2014-4-18 13:30:20);in sertintoC(Cid,Did,StartTime,E ndTime)values(5,14,2014-4-2017:20:10,2014-4-20 18:40:10);in sertintoC(Cid,Did,StartTime,E ndTime)values(6,13,2014-4-2612:05:40,2014-4-26 13:30:20);select cl.*,Cqty*(Mprice-Mcost)as 單項盈利金額from Clist cl order byCid,Sid;CTDSTDLI

32、TEnWAHEKQTYiMPEICEiWCOSTCWOHZT1單頂蠱利金額 1111鱗魚 - 130. 004 0030.00G212&芹菜-1315 00 0045.00331314豬肉“ 234. 002S.OO63.001841422慢頭1 102. 001.5020. 00551523燕京嗟厝 &5. 004.0030.006&1629老封長1 15 0012 0030.006Stleetclist Sftltci clistSelect C畬Cid,sum(Cm on ey),sum(Cqty* Mp rice),sum(Cqty*( Mp rice-Mcost)selectfrom

33、 Clist group by Cid order by Cid;二二 =CII|3UrCH0MEYj,SUM C9T7WEICE), SUM (CeTT* OimCE-MCOST)|11223SO56awST. 53320S2CS4444241241505515715725ee1ET373ei.4select* from C order by Cid;S elect clist Sftlfict cIl S t S elec t cSelect c3.l s t I S elect cLi zt Eele c t ccmDID ISMTTIHElEmTILIfiSHONElSFSW 1 11

34、 O13/3jZ15 ie：30:002013/3/150110:15 -23. OCSO. 00215 014/4/1 l;3C:O0- 201W1-ES.OCST. 50二 33|12a 12014/4/12 17:3a；02 - 20119:20:06 -303. OC44.00=44g 201WL3 12:10:05 - soivvie13；30：20 -紉.QC50.00一 55|1hIzOIWEO 17:20:10 201V4/201.&-40JO -157 OC2T.50.&e13 01W& IZU亍 40 F 01Vl/e13-30:20 73 OCei.10（2）為消費單明細(xì)

35、表CList定義一個觸發(fā)器，每更新 UP DATE 條消費單明細(xì)表記錄，自動修改其消費金額、以及消費單主表 C的消費金額合計、盈利金額合計。編寫相應(yīng)的更新語句（UP DATE）和查詢語句（SELECT）測試該觸發(fā)器效果。create or rep lace trigger Up dateClist before up date on Clist for each row beg in select Mn ame, Mp rice,Mcost into :n ew. Mn ame,: new. Mp rice,: new.Mcostfrom Mlist where Mlist.Mid=: new

36、.Mid;:n ew.Cm on ey:=nvl(: new.Cqty,:old.Cqty)* nvl(: new. Mp rice,:old. Mp rice);if :n ew.Cid is not null the nsetsetup dateSmon ey=Sm on ey-:old.Cm on ey,S Psum=S Psum-:old.Cqty*(:old. Mprice-:old.Mcost) where C.Cid=:old.Cid;up dateSmon ey=Sm on ey+: new.Cm on ey,S Psum=S Psum+: new.Cqty*(: new. M

37、price-: new.Mcost) where C.Cid=: new.Cid;elseUp dateCsetSmon ey=Sm on ey-:old.Cm on ey+:n ew.Cm on ey,S Psum=S Psum-:old.Cqty*(:old.M price-:old.Mcost)+: new.Cqty*(: new.M price-: new.Mcost)where C.Cid=:old.Cid;end if;end Up dateClsit;測試觸發(fā)器: 消費單號改變：select cl.*,Smoney,SPsum from Clist cl,C where cl.C

38、id=1 andandcl.Sid=3C.Cid=1;CID I SID niB riME cen IriFBICE FKOeT CM021ET ISM031EY 13PSLM | ZB 1 1314 堵肉34. OO 25. OO63.00223. OO 50. OOup date Clist set Cid=4 where Cid=1 and Sid=6;select cl.*,Sm on ey,S Psum from Clist cl,C where cl.Cid=1 and cl.Sid=3 andC.Cid=1;BIECID I SID Mill HNAWE CtjTT 上L丄I13

39、 M豬肉-3I 同W BB HPMCEHCOST CWOHEY SHOHEI |PSUW珂 OQ 5.0019340 啊,M消費單號未改變:select cl.*,Sm on ey,S Psum from Clist cl,C where cl.Cid=3 and cl.Sid=4 andC.Cid=3;CID SID IHD mt/lE區(qū)TYIlflPBI匚EHBST | CMOMEY |別0昶1|3415 牛排 2 4fi.OO 40.0095.00203. OO 4A. 00up date Clist set Cqty=4 where Cid= 3 and Sid= 4 ;select

40、cl.*,Sm on ey,S Psum from Clist cl,C where cl.Cid=3 and cl.Sid=4 andC.Cid=3;lUI-CID I 口D |WID I MME| 同TY) HF ME|HCOST |CnOHJT SMOHEY | SPSUW |34 衍牛排440.004C.C0192.0O504. OO 60.00（3）為消費單明細(xì)表CList定義一個觸發(fā)器，每刪除DELETE一條消費單明細(xì)表記錄自動修改其消費單主表 C的消費金額合計、盈利金額合計。編寫相應(yīng)的刪除語句（DELETE）和查詢語句（SELECT測試該觸發(fā)器效果。create or rep l

41、ace trigger DeleteClistbefore delete on Clist for each rowbeg inup datesetSmon ey=Sm on ey-:old.Cm on ey,S Psum=S Psum-:old.Cqty*(:old. Mprice-:old.Mcost) where C.Cid=:old.Cid;end DeleteClist;測試觸發(fā)器:CID 1 DIDSTXKTTLWEliEHDTIMEIsMomSPSUNI 111 1215201474/1 12:30:00 2014/1/1 14 垃 ADO250.006T.50select *

42、from C where Cid=2;delete from Clist where Cid=2 and Sid=4;select * from C where Cid=2;I 帥口肛 ti 5FSUM i215 2C14/4/1 12:30:00 2OL4/4/1 14.20:10 243.0065.00CID Imp ISTARTTIlflE（4）將【2.1.1】、【2.1.2】、【2.1.3】三個觸發(fā)器禁用disable，重新編寫一個觸發(fā)器實現(xiàn)這三個觸發(fā)器的全部功能。編寫相應(yīng)的插入語句（INSERT）、更新語句（UPDATE）、刪除語句（DELETE）和查詢語句（SELECT）測試該觸發(fā)

43、器效果。禁用觸發(fā)器： Alter table Clist disable all triggers;創(chuàng)建觸發(fā)器：create or replace trigger DML_Clistbefore insert or update or delete on Clist for each rowdeclarev_Smoney C.Smoney%type;v_SPsum C.SPsum%type;beginselectif inserting thenMname,Mprice,Mcost,:new.Cqty*Mpriceinto :new.Mname,:new.Mprice,:new.Mcost,:

44、new.CmoneyfromMlistintowhere Mlist.Mid=:new.Mid;select nvl(sum(Cqty*Mprice),0),nvl(sum(Cqty*(Mprice-Mcost),0)setv_Smoney,v_SPsum from Clist where Clist.Cid=:new.Cid;updateSmoney=v_Smoney+:new.Cmoney,SPsum=v_SPsum+(:new.Cqty*(:new.Mprice-:new.Mcost) where C.Cid=:new.Cid;elsif updating then select Mna

45、me,Mprice,Mcost into :new.Mname,:new.Mprice,:new.Mcostfrom Mlist where Mlist.Mid=:new.Mid;:new.Cmoney:=nvl(:new.Cqty,:old.Cqty)*nvl(:new.Mprice,:old.Mprice);if :new.Cid is not null thenupdatesetSmoney=Smoney-:old.Cmoney,SPsum=SPsum-:old.Cqty*(:old.Mprice-:old.Mcost) where C.Cid=:old.Cid;updatesetSmo

46、ney=Smoney+:new.Cmoney,SPsum=SPsum+:new.Cqty*(:new.Mprice-:new.Mcost) where C.Cid=:new.Cid;elseupdatesetSmoney=Smoney-:old.Cmoney+:new.Cmoney,SPsum=SPsum-:old.Cqty*(:old.Mprice-:old.Mcost)+:new.Cqty*(:new.Mprice-:new.Mcost)where C.Cid=:old.Cid;end if;elseupdatesetSmoney=Smoney-:old.Cmoney,SPsum=SPsu

47、m-:old.Cqty*(:old.Mprice-:old.Mcost) where C.Cid=:old.Cid;end if;end DML_Clist;測試觸發(fā)器：Insert 測試 ;select Smoney,SPsum from C whereC.cid=6;5IBCIMET ISFEUIW口2T3.00Bl. 4Cinsert into clist (cid,sid,mid,cqty)values(6,5,7,1);select cl.*,Smoney,SPsum fromclist cl,C where cl.cid=6 and sid=5 andC.cid=6;CID I S

48、IB I MID |we lean1 s 57 韭菜nrucE 1 MCOST IcMcirer swoket Isrsuw I10 00 a 0010.00263.0063,40Up date 測試:select cl.*,Smoney,SPsum fromclist cl,C where cl.cid=4and sid=3 andC.cid=4;CIE ISID MID MME CQTY riFKCCJ MCOST CMOmE jJ 4513 辣e菜-23.00 E.OO 15.00ISH0HE7 srsvw241.00 so. coup date clist set mid=6,cqty

49、=5 where cid=4 and sid=3;select cl.*,Smoney,SPsum from clist cl,C wherecl.cid=4 and sid=3 andC.cid=4;. CID 3ID llflID IllNAME ICTT |PflmCE nOOST1 I 43 S 芹菜 -515. aO 12.00ICIOMETISMOHETSfEUW T5.OO 300 00 59.CODelete 測試:1CIDbinSTARrriMElEHITIME1 SMOKEYH 1S13ZC14/4/26 1:05:40F 2014/4/25 13：3D:O263.00select* from c where cid=6;EFEUM I63. 40delete from clist where cid=6 and sid=5;匚IDBin 1 siAKmnE1EHBTIHEIsiflOMETISFSUHI 1| 6L3 2C1W26 1E：O5-4OF 201 WSe 13:30:2073. 006L. Wselect* from c where