數(shù)據(jù)庫習(xí)題課

1、數(shù)據(jù)庫原理及應(yīng)用A習(xí)題課（一）第一章第四章基本概念基本概念:1.數(shù)據(jù)模型數(shù)據(jù)模型:數(shù)據(jù)結(jié)構(gòu)、數(shù)據(jù)操作、完整性約束數(shù)據(jù)結(jié)構(gòu)、數(shù)據(jù)操作、完整性約束2.完整性約束完整性約束:實(shí)體、參照、用戶定義實(shí)體、參照、用戶定義3.實(shí)體、屬性、碼實(shí)體、屬性、碼4.三級模式結(jié)構(gòu)：外模式、模式、內(nèi)模式三級模式結(jié)構(gòu)：外模式、模式、內(nèi)模式第一章（15題）百貨公司百貨公司連鎖店連鎖店職工職工組成組成1k聘用聘用商品商品銷售銷售nm1n名稱名稱地址地址電話電話名稱名稱地址地址電話電話名稱名稱價格價格生產(chǎn)商生產(chǎn)商姓名姓名年齡年齡性別性別工資工資銷售量銷售量第一章（16題）基本概念基本概念:1.關(guān)系數(shù)據(jù)庫系統(tǒng)關(guān)系數(shù)據(jù)庫系統(tǒng)2.關(guān)

2、系模型中常用的操作關(guān)系模型中常用的操作3.域、笛卡兒積、關(guān)系、主碼、主屬性等域、笛卡兒積、關(guān)系、主碼、主屬性等4.關(guān)系的完整性：實(shí)體完整性、參照完整性、關(guān)系的完整性：實(shí)體完整性、參照完整性、用戶定義完整性用戶定義完整性5.關(guān)系運(yùn)算的定義關(guān)系運(yùn)算的定義關(guān)系代數(shù)運(yùn)算關(guān)系代數(shù)運(yùn)算二元二元二元二元按列按列輔助專門的關(guān)系運(yùn)算輔助專門的關(guān)系運(yùn)算按行按行按行運(yùn)算按行運(yùn)算說明說明不等于不等于小于等于小于等于大于等于大于等于等于等于小于小于或或與與除除 連接連接投影投影 廣義笛卡爾積廣義笛卡爾積交交差差大于大于比較運(yùn)比較運(yùn)算符算符集合運(yùn)集合運(yùn)算符算符并并非非 邏輯運(yùn)邏輯運(yùn)算符算符選擇選擇 專門的專門的關(guān)系運(yùn)關(guān)系

3、運(yùn)算符算符含義含義運(yùn)算符運(yùn)算符傳統(tǒng)的集合運(yùn)算傳統(tǒng)的集合運(yùn)算： 其運(yùn)算是按元組（記錄）進(jìn)行的。其運(yùn)算是按元組（記錄）進(jìn)行的。 設(shè)設(shè)R1R1和和R2R2為兩個度為為兩個度為n n的關(guān)系（表）且相對應(yīng)的屬的關(guān)系（表）且相對應(yīng)的屬性值取自性值取自同一個域同一個域，則可定義三種傳統(tǒng)的集合運(yùn)算：，則可定義三種傳統(tǒng)的集合運(yùn)算：并運(yùn)算：并運(yùn)算：R1R1R2R2交運(yùn)算：交運(yùn)算：R1R1R2R2差運(yùn)算：差運(yùn)算：R1R1R2R2并運(yùn)算并運(yùn)算： R1R2 R1R2，結(jié)果：度為，結(jié)果：度為n n，元組屬于，元組屬于R1R1或或R2R2 交運(yùn)算交運(yùn)算： 交運(yùn)算：交運(yùn)算：R1R2R1R2，結(jié)果：度為，結(jié)果：度為n n 其中

4、元組既屬于其中元組既屬于R1R1又屬于又屬于R2R2差運(yùn)算差運(yùn)算： 差運(yùn)算：差運(yùn)算：R1-R2R1-R2，結(jié)果：度為，結(jié)果：度為n n，元組屬于，元組屬于R1R1ABCa1b1c1R1-R2R1-R2條件連接條件連接： 取兩個關(guān)系的笛卡爾積中屬性符合條件取兩個關(guān)系的笛卡爾積中屬性符合條件A B的元的元組，組， A B 為為R.ZS.T:R SA BXR.YZS.YTx13574x13501x45301R SR.ZS.T等值連接等值連接： 取兩個關(guān)系笛卡爾積中取兩個關(guān)系笛卡爾積中A、B屬性值相等的元組屬性值相等的元組R SA=BXR.YZS.YTx16165x45358R SR.Y=S.Y自然連

5、接自然連接： A、B為相同屬性組的等值連接為相同屬性組的等值連接R SXYZTx1615x4538R S象集象集： 某屬性組在其他屬性上對應(yīng)分量的集合某屬性組在其他屬性上對應(yīng)分量的集合關(guān)系關(guān)系R上屬性上屬性A的象集：的象集：a1的象集：的象集：(b1,c2),(b2,c4)a2的象集：的象集：(b3,c1)a3的象集：的象集：(b1,c2),(b2,c4)a4的象集：的象集：(b1,c2)R(X,Y)R(X,Y)S(Y,Z)=R(X,Y)S(Y,Z)=R(X,Y)Y Y(S(Y,Z)=T(X)(S(Y,Z)=T(X)除法除法：X在在R上的象集包含上的象集包含Y在在S上的投影的元素上的投影的元素

6、a1的象集：的象集：(b1,c2),(b2,c3),(b2,c1)a2的象集：的象集：(b3,c7),(b2,c3)a3的象集：的象集：(b4,c6)c1b2a1c3b2a2c3b2a1c6b4a3c7b3a2c2b1a1CBAd2c3b2d1c1b2d1c2b1DCBR Sa1ARS(1) 1 1()SNOJNOJPNOPSPJ 1()SNOJNOJSPJ 1()SNOJNOJCOLORSPJP紅( )-()JNOJNOCOLORCITYJSPJS紅天津, 1()()PNO JNOPNOSNOSSPJSPJP(2)(3)(4)(5)第二章（10題）第二章（11題）#,( )CCNAMETEA

7、CHERLIUC#,22( )SSNAMEAGEsexS 男#, 3()CTEACHERSNOsSC C#( )()CCSNAMEWANGCS SC()SNAMESEXTEACHERLIUS SC C女(1)(2)(3)(4)(5)#,#,#()( )SCCNAMESC SCS#,#( )( )SCCTEACHERLIUCC(6)(7)第二章（11題）SQL中連接查詢的主要類型：中連接查詢的主要類型：l 廣義笛卡兒積廣義笛卡兒積l 等值連接（含自然連接）等值連接（含自然連接）l 非等值連接非等值連接l 自身連接查詢自身連接查詢l 外連接查詢外連接查詢l 復(fù)合條件連接查詢復(fù)合條件連接查詢l 嵌套

8、查詢嵌套查詢l 廣義笛卡兒積：廣義笛卡兒積： select Student.* , SC.* from Student,SC;注：這樣不帶連接謂詞的連接很少使用注：這樣不帶連接謂詞的連接很少使用,無意義無意義l等值連接（含自然連接）等值連接（含自然連接）:. = . 任何子句中引用表任何子句中引用表1和表和表2中同名屬性時，都中同名屬性時，都必須加表名前綴。引用唯一屬性名時可以加也必須加表名前綴。引用唯一屬性名時可以加也可以省略表名前綴?？梢允÷员砻熬Y。 例：查詢每個學(xué)生及其選修課的情況例：查詢每個學(xué)生及其選修課的情況 (表見表見P59)。 Select Student.*, SC.* fr

9、om Student,SC Where Student.sno=SC.sno;l 自身連接查詢自身連接查詢 一個表與其自己進(jìn)行連接，需要給表起別名以一個表與其自己進(jìn)行連接，需要給表起別名以示區(qū)別，由于所有屬性都是同名屬性，因此必須示區(qū)別，由于所有屬性都是同名屬性，因此必須使用別名前綴。使用別名前綴。例：查詢每一門課的間接先修課。例：查詢每一門課的間接先修課。 Select Fo, First.cpno, So, Second.cpno From Course First, Course Second Where First.cpno=So;l外連接查詢外連接查詢:外連接與普通連接的區(qū)別：外連接

10、與普通連接的區(qū)別： 普通連接只輸出滿足連接條件的元組普通連接只輸出滿足連接條件的元組 外連接以指定表為連接主體，將主體表不滿足外連接以指定表為連接主體，將主體表不滿足連接條件的元組一并輸出連接條件的元組一并輸出例：查詢每個學(xué)生及其選修課程的情況包括沒有選例：查詢每個學(xué)生及其選修課程的情況包括沒有選修課程的學(xué)生。修課程的學(xué)生。Select Student.sno,sname,Ssex,Sage,Sdept,cno,gradefrom Student, SCwhere Student.sno=SC.sno;from Studentleft join SC on (student.sno=sc.sn

11、o);l 復(fù)合條件連接查詢復(fù)合條件連接查詢 where子句中可以有多個連接條件。子句中可以有多個連接條件。例：查詢選修例：查詢選修2號課程且成績在號課程且成績在90分以上的分以上的所有學(xué)生學(xué)號和姓名。所有學(xué)生學(xué)號和姓名。 Select Student.sno, sname from Student, SC where Student.sno=SC.sno and SC.cno=2 and SC.grade90;l 嵌套查詢嵌套查詢 將一個查詢塊嵌套在另一個查詢塊的將一個查詢塊嵌套在另一個查詢塊的where子子句或句或having短語的條件中的查詢短語的條件中的查詢例：查詢與例：查詢與“劉晨劉晨

12、”同一個系學(xué)習(xí)的學(xué)生信息同一個系學(xué)習(xí)的學(xué)生信息 select sno,sname,sdept from Student where sdept in (select sdept from Student where sname=劉晨劉晨);(1)求供應(yīng)工程求供應(yīng)工程J1零件的供應(yīng)商號碼零件的供應(yīng)商號碼SNO: select sno from SPJ where jno=J1;(2)求供應(yīng)工程求供應(yīng)工程J1零件零件P1的供應(yīng)商號碼的供應(yīng)商號碼SNO: select sno from SPJ where jno=J1 and pno=P1;(3)求供應(yīng)工程求供應(yīng)工程J1零件為零件為紅色紅色的供應(yīng)商

13、號碼的供應(yīng)商號碼SNO: select sno from SPJ where jno=J1 and pno in (select pno from P where color=紅紅); 或或: select sno from SPJ,P where SPJ.pno=P.pno and SPJ.jno=J1 and P.color=紅紅;(4)求沒有使用求沒有使用天津天津供應(yīng)商生產(chǎn)的供應(yīng)商生產(chǎn)的紅色紅色零件的工程號零件的工程號JNO： 方法一方法一(相關(guān)子查詢相關(guān)子查詢): select jno from J where jno not in (select jno from SPJ,S,P w

14、here SPJ.sno=S.sno and SPJ.pno=P.pno and city=天津天津 and color=紅紅)；(4)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號JNO： 方法二方法二(不相關(guān)子查詢不相關(guān)子查詢): select jno from J where not exists (select * from SPJ where SPJ.jno=J.jno and sno in(select sno from S where city=天津天津) and pno in(select pno from P where color=紅紅

15、);(4)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號JNO： 方法三方法三(不相關(guān)子查詢不相關(guān)子查詢): select jno from J where not exists (select * from SPJ,S,P where SPJ.jno=J.jno and SPJ.sno=S.sno and SPJ.pno=P.pno and city=天津天津 and color=紅紅);(5)求至少使用了供應(yīng)商求至少使用了供應(yīng)商S1所供應(yīng)的全部零件的工程所供應(yīng)的全部零件的工程號號JNO： select distinct jno from SPJ A w

16、here not exists (select * from SPJ B where sno=S1 and not exists (select * from SPJ C where C.pno=B.pno and C.jno=A.jno);(1)找出所有供應(yīng)商的姓名和所在城市：找出所有供應(yīng)商的姓名和所在城市： select sname,city from S;(2)找出所有零件的名稱找出所有零件的名稱,顏色顏色,重量重量: select pname,color,weight from P;(3)找出使用供應(yīng)商找出使用供應(yīng)商S1所供應(yīng)零件的工程號碼所供應(yīng)零件的工程號碼: select jno

17、from SPJ where sno=S1;(4)找出工程項(xiàng)目找出工程項(xiàng)目J2使用的各種零件的名稱和數(shù)量使用的各種零件的名稱和數(shù)量: select pname,sum(qty) from spj,p where jno=j2 and spj.pno=p.pno group by pname;供應(yīng)商沒確定供應(yīng)商沒確定(5)找出上海廠商供應(yīng)的所有零件號碼找出上海廠商供應(yīng)的所有零件號碼: select distinct pno from SPJ where sno in(select sno from S where city=上海上海);或或: select pno from SPJ,S wher

18、e SPJ.sno=S.sno and city=上海上海;(6)找出使用上海產(chǎn)的零件的工程名稱找出使用上海產(chǎn)的零件的工程名稱: select jname from J, S, SPJ where J.jno=SPJ.jno and S.sno=SPJ.sno and city=上海上海;或或: select jname from J where jno in(select jno from SPJ where sno in(select sno from S where city=上海上海);(7)找出沒有使用天津產(chǎn)的零件的工程號碼找出沒有使用天津產(chǎn)的零件的工程號碼:select jno f

19、rom J where jno not in(select jno from SPJ,S where SPJ.sno=S.sno and city=天津);或: select J.jno from J,S,SPJ where SPJ.jno=J.jno and SPJ.sno=S.sno and city!=天津;或: select jno from J where not exists (select * from SPJ where SPJ.jno=J.jno and sno in (select sno from S where city=天津);(8)把全部紅色零件的顏色改成藍(lán)色把全部

20、紅色零件的顏色改成藍(lán)色: update P set color=藍(lán)色藍(lán)色 where color=紅色紅色;(9)由由S5供給供給J4的零件的零件P6改為由改為由S3供應(yīng)供應(yīng): update SPJ set sno=S3 where sno=S5 and pno=P6 and jno=J4;(10)從供應(yīng)商關(guān)系中刪除從供應(yīng)商關(guān)系中刪除S2的記錄的記錄,并從供應(yīng)情況關(guān)并從供應(yīng)情況關(guān)系中刪除相應(yīng)的記錄系中刪除相應(yīng)的記錄: delete from SPJ where sno=S2; delete from S where sno=S2;或或: delete from S where sno=S2 d

21、escade; (11)請將請將(S2,J6,P4,200)插入供應(yīng)情況關(guān)系插入供應(yīng)情況關(guān)系: insert into SPJ values(S2,J6,P4,200);200 ?第三章（第6題）(1) Select * from Customers where city=shanghai;(2) Select Gname,Stocks,Price,Price*0.75 Discount from Goods order by Price(3) Select GoodId,Gname,Price from Goods where Gname like %computer%(4) Select

22、Stocks,Gname,Price from Goods where Stocks between 100 and 500第三章（第6題）(5) Select Cname,Gname,Price,Quantity,Ordersum from Customers,Goods,Orders where Customers. CustomeID=Orders. CustomeID and Goods. GoodID=Orders. GoodID and Date between 2007-7-1 and 2007-12-31 and Ordersum30000(6) Select CustomeI

23、D, GoodID, OrderID from Orders where Quantity200 Group by Date(7) Select * from Orders where CustomeID in (Select CustomeID from Customers where City=beijing )第三章（第6題）(8) Select * from Orders where CustomeID not in (Select CustomeID from Customers where City=tianjing )(9) Select Customers. CustomeID

24、,Cname,City,OrderID,GoodID, Quantity,Ordersum,Date from Orders ,Customers where Customers. CustomeID= Orders . CustomeID and City=xian第三章（第6題）(10) Upade Goods Set Price=Price*0.85 where Stocks500(11) Delete from Goods where Stocks=0(12) Insert into Goods values(T10056,計(jì)算機(jī),5300,清華同方,20,0)視圖視圖:create

25、view V_sanjian as select pno,sno,qty from SPJ,J where SPJ.jno=J.jno and jname=三建三建;(1)找出三建工程項(xiàng)目使用的各種零件代碼及數(shù)量找出三建工程項(xiàng)目使用的各種零件代碼及數(shù)量: select pno,sum(QTY) from V_sanjian group by pno;(2)找出供應(yīng)商找出供應(yīng)商S1的供應(yīng)情況的供應(yīng)情況: select * from V_sanjian where sno=S1;(1)把對表把對表S的的insert權(quán)限授予用戶張勇權(quán)限授予用戶張勇,并允許他再將并允許他再將此權(quán)限授予其他用戶此權(quán)限授

26、予其他用戶:grant insert on table S to 張勇張勇 with grant option;(2)把查詢把查詢SPJ表和修改表和修改QTY屬性的權(quán)限授予用戶李天屬性的權(quán)限授予用戶李天明明:grant select, update(QTY) on table SPJ to 李天明李天明;注：用戶名不要加單引號注：用戶名不要加單引號第三章（第11題）(1) create view Bj_orders(OrderID,Gname,Quantity,CustomerID,Cname) As select OrderID,Gname,Quantity, Orders.Customer

27、ID,Cname from Orders,Customers,Goods where Customers. CustomeID=Orders. CustomeID and Goods. GoodID=Orders. GoodID and City=beijing(2)Select * from Bj_orders where Cname=張三張三第四章（第5題）(1) Create procedure sp_shanghai As select * from Customers where City=shanghai(2) Create procedure sp_Goods Goodname

28、Varchar(10) As select * from Goods where Gname= Goodname第四章（第5題）(3) Create procedure sp_goodsum cnum Varchar(10) snum int output As select snum =sum(Ordersum) from Orders Group by CustomerID having CustomerID= cnum (4) Create procedure sp_insertGoods Goodnum char(10), Goodname char(10),Gprice int,Gprovider char(10),Gstocks i