MYSQL數(shù)據(jù)庫練習.doc

學習資料收集于網(wǎng)絡，僅供參考附錄A 學生成績管理系統(tǒng)數(shù)據(jù)庫 學生成績管理系統(tǒng)數(shù)據(jù)庫是比較簡單的一個數(shù)據(jù)庫。基于讀者都對學校的學生管理方式十分了解，所以這個數(shù)據(jù)庫作為上課時的實際演示例子。A.1 學生表(Tbl_StudentInfo)倫理名物理名屬性主鍵外鍵學號StunoChar(5) 非空是姓名stunameVarchar(20) 非空生日StubirthDate性別Stusexchar(1)家庭住址StuAddrVarchar(200) 手機StuTelVarchar(11)圖 A.1 學生表的表結(jié)構(gòu)學號姓名生日性別家庭住址手機05001張三1988-12-120江蘇南京1234505002李四1987-06-051上海1234605003王五1987-12-010北京1234705004趙六1986-02-231廣東深圳1234805005張三1988-04-010重慶1234905006孫七1988-07-031湖北武漢圖A.2 學生表的表數(shù)據(jù)A.2 課程表(Tbl_ClassInfo)倫理名物理名屬性主鍵外鍵課程號classnoChar(3) not null是課程名ClassnameVarchar(10) not null圖 A.3 課程表的表結(jié)構(gòu)課程號課程名001計算機002日語003英語圖A.4 課程表的表數(shù)據(jù)A.3 成績表(Tbl_ScoreInfo)倫理名物理名屬性主鍵外鍵學號StunoChar(5) not null是參照學生表課程號ClassnoVarchar(3) not null是參照課程表考試成績scoreNumeric(3,1)圖 A.5 成績表的表結(jié)構(gòu)學號課程號成績0500100195050010029005001003880500200191050020029305002003880500300195050030027305003003580500400147050040036105005002590500500347圖A.6 成績表的表數(shù)據(jù)附錄B 網(wǎng)上書店管理系統(tǒng)數(shù)據(jù)庫網(wǎng)上書店管理系統(tǒng)數(shù)據(jù)庫是比較復雜的一個數(shù)據(jù)庫?；诰W(wǎng)上銷售圖書的方式，作為課后習題。B.1 客戶表(Tbl_Customer)倫理名物理名屬性主鍵外鍵顧客編號CustomerInt(4) 非空是顧客姓名Customer_NameVarchar(10)顧客地址AddressVarchar(20)所在城市CityVarchar(20)所在省StateVarchar(20)郵政編碼ZipVarchar(6)推薦會員ReferredInt(4)圖 B.1客戶表的表結(jié)構(gòu)顧客編號顧客姓名顧客地址所在城市所在省郵政編碼推薦會員1001張三白下區(qū)南京江蘇2100001002李四徐匯區(qū)上海上海1100001003王五朝陽區(qū)北京北京1100001004趙六北大街無錫江蘇1100001005李三平江路蘇州江蘇1100001006陳三升州路南京江蘇1100001007朱三三牌樓合肥安徽11000010031008梁三徐匯區(qū)上海上海1100001009宮三徐匯區(qū)上海上海11000010031010馬三海淀區(qū)北京北京1100001011劉三崇文區(qū)北京北京1100001012齊三宣武區(qū)北京北京1100001013陸三鼓樓區(qū)南京江蘇11000010061014陳三雨花區(qū)南京江蘇1100001015錢三新橋村無錫江蘇1100001016高三利農(nóng)村無錫江蘇11000010101017郝三楊浦區(qū)上海上海1100001018黃三三家莊合肥安徽1100001019黃四常青鎮(zhèn)合肥安徽11000010031020梁四余老莊合肥安徽110000圖B.2 客戶表的表數(shù)據(jù)B.2 圖書表(Tbl_Books)倫理名物理名屬性主鍵外鍵圖書編號IsbnVarchar (10) 非空是書名TitleVarchar(300)出版日期PubdateDate出版社IDPubidInt(2)成本CostNumeric(5,2)售價RetailNumeric (5,2)種別CategoryVarchar (12)圖 B.3圖書表的表結(jié)構(gòu)圖書編號書名出版日期出版社ID成本售價種別1059831198一天中10分鐘的身體成分2001-01-21418.7530.95健康0401140733米奇的復仇2001-12-12114.222家庭生活4981341710用牙簽造車2002-08-18237.859兒童8843172113數(shù)據(jù)庫的實現(xiàn)1999-06-0433155計算機3437212490用蘑菇烹飪2000-02-28412.519.95烹飪3957136468數(shù)據(jù)庫法寶2001-11-3034775計算機1915762492電腦手冊2001-01-2132125計算機9959789321電子商務的道路2002-03-0223754計算機2491748320教育孩子_輕松方法2000-07-1754889.95家庭生活0299282519烹飪之路2000-09-1141928烹飪8117949391大熊和小寶貝2001-11-0855.328.95兒童0132149871如何最快的制作比薩2002-11-11417.8529.95自助9247381001如何成為經(jīng)理人1999-05-09115.431.95商務2147428890最短小精悍的詩2001-05-01521.8539.95文學圖B.4 圖書表的表數(shù)據(jù)B.3 訂單表(Tbl_Orders)倫理名物理名屬性主鍵外鍵訂單號OrderInt (4) 非空是顧客號CustomerInt(4)參照客戶表訂單日期OrderdateDate發(fā)貨日期ShipdateDate發(fā)貨地點ShipstreetVarchar (18)發(fā)貨城市ShipcityVarchar (15)發(fā)貨省ShipstateVarchar (2)郵編ShipzipVarchar (6)圖B.5 訂單表的表結(jié)構(gòu)訂單號顧客號訂單日期發(fā)貨日期發(fā)貨地點發(fā)貨城市發(fā)貨省郵編100010052003-03-312003-04-02奧體大街南京江蘇210019100110102003-03-312003-04-01和會街南京江蘇210001100210112003-03-312003-04-01南京路上海上海201100100310012003-04-012003-04-01南京路上海上海201100100410202003-04-012003-04-05歐風街無錫江蘇214002100510182003-04-012003-04-02南大街常州江蘇213000100610032003-04-012003-04-02洋人街重慶重慶400015100710072003-04-012003-04-04洋人街重慶重慶400015100810042003-04-022003-04-03歐風街無錫江蘇214002100910052004-04-032004-04-05南大街常州江蘇213000101010192004-04-032004-04-04和平街北京北京100000101110102003-04-032003-04-05長安街北京北京100000101210172005-04-03北京東路南京江蘇210001101310142005-04-032005-04-04德化街鄭州河南450007101410072003-04-032003-04-05和平街北京北京100000101510202005-03-31歐風街無錫江蘇214002101610032003-03-31長陽街蘇州江蘇215500101710152005-03-312005-04-05太合路合肥安徽230001101810012003-03-31江蘇路南京江蘇210001101910182003-03-31青陽路合肥安徽230001102010082003-03-31梅山新村合肥安徽230001圖B.6 訂單表的表數(shù)據(jù)B.4 訂單詳細表(Tbl_Orderitems)倫理名物理名屬性主鍵外鍵訂單號OrderInt (4) 非空是參照訂單表明細編號ItemInt (2) 非空是圖書編號IsbnVarchar(10)數(shù)量QuantityInt (3)圖B.7 訂單詳細表的表結(jié)構(gòu)訂單號明細編號圖書編號數(shù)量10001343721249011001192473810011100122491748320110021884317211321003188431721131100321059831198110033343721249011004124917483202100512147428890110061995978932111007139571364683100729959789321110073811794939111007488431721131100813437212490210091343721249011009204011407331101018843172113110111249174832011012181179493911101221915762492210123249174832011012404011407331101318843172113110141040114073321015134372124901101612491748320110171811794939111018134372124902101828843172113110191040114073311020134372124901圖B.8 訂單詳細表的表數(shù)據(jù)B.5 作者表(Tbl_Author)倫理名物理名屬性主鍵外鍵作者編號AuthorIdVarchar (4) 非空是姓名NameVarchar (20)圖B.9 作者表的表結(jié)構(gòu)作者編號姓名S100薛明J100陳洪A100張二M100劉虎K100陳康P100王立A105吳明B100洪海P105李強W100宮立W105龔佩R100張青F100鐘漢W110韓青圖B.10 作者表的表數(shù)據(jù)B.6 圖書作者表(Tbl_BookAuthor)倫理名物理名屬性主鍵外鍵圖書編號IsbnVarchar (10) 非空是作者編號AuthorIdVarchar (4) 非空是圖B.11 圖書作者表的表結(jié)構(gòu)圖書編號作者編號1059831198S1001059831198P1000401140733J1004981341710K1008843172113P1058843172113A1008843172113A1053437212490B1003957136468A1001915762492W1001915762492W1059959789321J1002491748320R1002491748320F1002491748320B1000299282519S1008117949391R1000132149871S1009247381001W1002147428890W105圖B.12 圖書作者表的表數(shù)據(jù)B.7 出版社表(Tbl_Publisher)倫理名物理名屬性主鍵外鍵出版社編號PubidInt (10) 非空是出版社名NameVarchar (23)聯(lián)系人ContactVarchar (15)聯(lián)系電話PhoneVarchar (12)圖B.13 出版社表的表結(jié)構(gòu)出版社編號出版社名聯(lián)系人聯(lián)系電話1新華出版社陳明000-714-83212揚子出版社張譯010-410-00103人民教育出版社元力800-555-12114北京大學出版社鄭爽800-555-97435機械工業(yè)出版社陸華800-555-8284圖B.14 出版社表的表數(shù)據(jù)B.8 促銷表(Tbl_Promotion)倫理名物理名屬性主鍵外鍵禮物GiftVarchar (10) 最低售價MinretailNumeric (5,2) 最高售價MaxretailNumeric(5,2) 圖B.15促銷表的表結(jié)構(gòu)禮物最低售價最高售價書簽012書題標簽12.0125圖書封面25.0156免費購物券56.01999.99圖B.16促銷表的表數(shù)據(jù)第一章 數(shù)據(jù)庫概念 1. 理解附錄B網(wǎng)上書店訂單系統(tǒng)的數(shù)據(jù)庫，并回答如下問題。 1.1 可以訪問哪個表和字段在當月訂單中確定客戶已經(jīng)購買了哪些書？1.2 通過哪些表可以確定哪些訂單沒有發(fā)貨。1.3 通過哪些表及字段確定每月訂單產(chǎn)生的總利潤。1.4 訂單表和明細表的訂單編號是什么樣的關(guān)系。2. 參照教材附錄C有關(guān)數(shù)據(jù)庫的安裝過程，把數(shù)據(jù)庫安裝到本機，從中學習安裝數(shù)據(jù)庫。 （考察：數(shù)據(jù)庫安裝 預估：50分鐘）第二章 表格及數(shù)據(jù)約束 1. 熟悉附錄B中各個表及各字段含義，并且能說明此字段屬性的設(shè)置原因。 （考察：表格和字段的理解 預估：30分鐘） 2. 創(chuàng)建附錄B所示的客戶表，圖書表，訂單表，訂單詳細表，作者表，圖書作者表，出版社表及促銷表。3. 為客戶表中增加客戶電話字段(字段名：TEL_NO,屬性：Varchar(15)。 4. 為客戶表中將客戶地址修改為可以存儲50個中文漢字的屬性。 5. 為客戶表，圖書表，訂單表，訂單詳細表，作者表，圖書作者表創(chuàng)建主鍵約束。 6. 為訂單表的客戶編號項目創(chuàng)建外鍵約束，為訂單詳細表的訂單編號和圖書編號創(chuàng)建外鍵約束；以及為教材附錄A表的考試成績表的課程號創(chuàng)建外鍵約束。7. 為促銷表的最低售價和最高售價指定檢查約束，最高售價必須大于最低售價。 為訂單詳細表的數(shù)量指定檢查約束，數(shù)量0。 為圖書表的成本和售價指定檢查約束，售價必須大于成本。 （考察：檢查約束 預估：40分鐘） 8. 為客戶表的客戶電話項目設(shè)置唯一約束。第三章 數(shù)據(jù)操作及事務處理 1. 參照教材附錄B表插入數(shù)據(jù)。（保留住插入的sql文） 2. 參照教材附錄B，修改朱三的客戶信息，地址變?yōu)樾靺R區(qū)，城市變?yōu)樯虾?，省變?yōu)樯虾＃]編變?yōu)?10000，推薦會員變?yōu)殛惾?. 參照附錄B，刪除訂單明細表中書名為用蘑菇烹飪的信息。 4. 修改訂單1016 的發(fā)貨地點為江蘇路，城市南京，省江蘇，郵編210001。 5. 修改訂單1006的圖書為電子商務的道路，數(shù)量5。 6. 修改書簽禮物的最高售價為18，書題標簽的最低售價改為18.01。 7. 修改人民教育出版社的聯(lián)系電話為800-555-1398。 （考察：更新數(shù)據(jù) 預估：5分鐘）第四章 基本SQL 參看教材附錄A表，完成以下習題： 1. 查詢所有沒電話的學生信息。 2. 查詢所有成績優(yōu)秀(大于90)和成績不及格(低于60)的學生學號,和課程號。 3. 查詢所有學生信息,按照生日從大到小排序。 4. 查詢所有姓孫的學生信息。 參看教材附錄B表，完成以下習題： 1. 查詢兒童和烹飪種類的所有圖書（兩種方式查詢）。 （考察：IN和OR查詢的使用 預估：10分鐘） 2. 查詢所有圖書信息,按照出版社ID從小到大，出版日期從大到小排序。 （考察：排序的復雜使用 預估：10分鐘） 3. 查詢由其他客戶向其推薦書店的所有客戶。 （考察：NULL的使用 預估：10分鐘） 4. 查詢售價在20到50元之間的圖書信息（用2種方式實現(xiàn)）。 （考察：BETWEEN和運算符的使用 預估：15分鐘） 5. 查詢不屬于計算機的圖書信息（用2種方式實現(xiàn)）。 （考察：NOT IN和運算符的使用 預估：10分鐘） 6. 查詢居住在上海和南京的顧客信息，并按照姓名升序排列（用2種方式實現(xiàn)）。 （考察：IN和OR以及排序的使用 預估：15分鐘） 7. 查詢發(fā)貨城市在北京和常州的訂單信息，并按照發(fā)貨日期升序排列（用2種方式實現(xiàn)）。 （考察：IN和OR以及排序的使用 預估：15分鐘） 8. 查詢發(fā)貨地點以“南”開頭的的訂單信息，并按照發(fā)貨日期升序排列。 （考察：模糊查詢以及排序的使用 預估：15分鐘） 9. 查詢書名中含有_的圖書信息，并按照出版日期升序排列。 （考察：模糊查詢以及排序的使用 預估：15分鐘）第五章 內(nèi)置函數(shù) 1. 查詢所有課程的英文名稱（分別用CASE和DECODE方法）。 （考察：CASE和DECODE方法 預估：15分鐘） 2. 查詢所有學生的成績（用等級表示：低于60表示不及格；6070表示及格；7080表示中等；80100表示優(yōu)秀）（用CASE方法）。 （考察：CASE方法 預估：15分鐘） 參照教材附錄B表完成下面習題： 3. 根據(jù)所在城市查詢顧客所在的?。ǚ謩e用CASE和DECODE方法）。 （考察：CASE和DECODE方法 預估：15分鐘） 4. 創(chuàng)建所有客戶的列表，如果一個客戶不是由其他客戶介紹來的，則顯示字符NO REFERRED。 （考察：CASE方法 預估：10分鐘） 5. 顯示所有圖書的毛利（百分數(shù)）的列表，毛利應該顯示為一個沒有小數(shù)位的整數(shù)（就是乘以100），后面帶有百分號（例如：0.2793=28%）。 （考察：ROUND方法 預估：15分鐘） 6. 顯示所有圖書的成本的最小整數(shù)。 （考察：CEIL方法 預估：5分鐘） 7. 顯示所有圖書的成本和售價均截斷小數(shù)點后的數(shù)值。 （考察：TRUNC方法 預估：5分鐘） 8. 求出所有圖書的成本和售價的余數(shù)。 （考察：MOD方法 預估：5分鐘） 9. 顯示所有圖書的種別和書名，中間用-連接。 （考察：CONCAT方法 預估：5分鐘） 10. 顯示所有顧客的姓名，地址，城市，中間用/連接，顯示長度到80。 （考察：CONCAT和VARCHAR方法 預估：10分鐘） 11. 顯示出版社名的開頭4位以及聯(lián)系人的姓。 （考察：SUBSTR方法以及字節(jié)數(shù) 預估：10分鐘） 12. 顯示把顧客所在城市是上海的替換成英文。 （考察：REPLACE方法 預估：10分鐘） 13. 顯示所有顧客的姓名，對于是直轄市的，需顯示空白，否則顯示城市名稱。 （考察：NULLIF方法 預估：10分鐘） 14. 重復顯示發(fā)貨城市4遍。 （考察：REPEAT方法 預估：5分鐘） 15. 顯示訂單訂單日期和發(fā)貨日期的年。 （考察：YEAR方法 預估：5分鐘） 16. 顯示訂單日期和發(fā)貨日期。 （考察：DATE方法 預估：5分鐘） 17. 顯示當前系統(tǒng)時間。 （考察：TIME方法 預估：5分鐘） 18. 顯示當前系統(tǒng)時間的秒。 （考察：SECOND方法 預估：5分鐘） 19. 顯示訂單日期和發(fā)貨日期的月份。 （考察：MONTH方法 預估：5分鐘） 20. 顯示當前系統(tǒng)時間的分。 （考察：MINUTE方法 預估：5分鐘） 21. 顯示當前系統(tǒng)時間的小時。 （考察：HOUR方法 預估：5分鐘） 22. 顯示書名從第2個漢字的書名以及圖書編號的后四位。 （考察：SUBSTR方法 預估：5分鐘） 23. 顯示書名中子出現(xiàn)的位置。 （考察：POSSTR方法 預估：5分鐘）第六章 多表查詢參照附錄B完成練習 1. 創(chuàng)建一個列表，顯示每本書的書名及出版社辦公室中你再次訂購每本書時需要聯(lián)系的人的姓名和電話。（傳統(tǒng)和JOIN方法）。 （考察：一般關(guān)聯(lián)查詢 預估：30分鐘） 2. 確定哪些訂單還沒發(fā)貨以及下達這些訂單的客戶的姓名，將結(jié)果按下達訂單的日期排序（傳統(tǒng)和JOIN方法）。 （考察：一般關(guān)聯(lián)查詢 預估：30分鐘） 3. 列出已經(jīng)購買了計算機種類的所有人的客戶號和姓名以及書名和訂單號。 3.1 查詢計算機種類的圖書編號。 3.2 查詢訂單詳細表中是圖書編號是3.1得到編號的訂單號。 3.3 查詢訂單中訂單號是3.2得到編號的顧客編號。 3.4 根據(jù)3.3的顧客編號得到顧客的姓名。 （傳統(tǒng)和JOIN方法） （考察：一般關(guān)聯(lián)查詢 預估：40分鐘） 4. 確定李三已經(jīng)購買了哪些書。 4.1 查詢李三的顧客編號。 4.2 查詢訂單表中李三編號對應的訂單號。 4.3 查詢訂單詳細表中4.2的訂單號對應的圖書編號。 4.4 根據(jù)圖書編號取得書名。 （傳統(tǒng)方法） （考察：一般關(guān)聯(lián)查詢 預估：30分鐘） 5. 確定銷售給王五的每一本書的利潤。將結(jié)果按訂單日期排序。如果訂購了多本書，那么將結(jié)果按利潤的降序排列。（傳統(tǒng)和JOIN方法） （考察：一般關(guān)聯(lián)查詢 預估：30分鐘） 6. 哪一本書是由叫洪海的作者編寫的？（傳統(tǒng)和JOIN方法） （考察：一般關(guān)聯(lián)查詢 預估：30分鐘） 7. 得到居住在南京并且訂購了計算機圖書的所有客戶的列表。（傳統(tǒng)和JOIN方法） （考察：一般關(guān)聯(lián)查詢 預估：30分鐘） 8. 查詢所有訂單購買的圖書書名。（JOIN方法） （考察：外關(guān)聯(lián)查詢 預估：30分鐘） 9. 查詢所有圖書列表，及被哪些顧客購買以及該顧客的信息。（JOIN方法） （考察：外關(guān)聯(lián)查詢 預估：30分鐘） 10. 查詢所有作者編寫了哪些圖書。（JOIN方法） （考察：外關(guān)聯(lián)查詢 預估：30分鐘）11. 查詢所有作者編寫了哪些圖書以及購買該圖書的顧客信息。（JOIN方法） （考察：外關(guān)聯(lián)查詢 預估：30分鐘） 12. 得到所有居住在南京的顧客以及他訂購圖書的信息。（JOIN方法） （考察：外關(guān)聯(lián)查詢 預估：30分鐘） 13. 得到訂單年在2003和2004年的訂單信息以及顧客姓名。 （UNION，UNION ALL，傳統(tǒng)和與JOIN方法） （考察：UNION查詢 預估：40分鐘）第七章 分組查詢 1. 查詢單科成績最高的分數(shù)和課程名，按照課程名排序（多表關(guān)聯(lián),GroupBy子句） （傳統(tǒng)和JOIN方法）。 1.1 查詢單科成績最高的課程編號和成績。 1.2 用1.1得到的課程號得出課程名。 （考察：多表關(guān)聯(lián)查詢 預估：40分鐘） 2. 查詢單科成績最低分數(shù)和課程名,按照課程名排序（多表關(guān)聯(lián),GroupBy子句）。（傳統(tǒng)和JOIN方法） （考察：多表關(guān)聯(lián)查詢 預估：10分鐘） 3. 查詢平均分大于80分的學生的姓名，按照姓名排序 。（多表關(guān)聯(lián) ,having 子句） （傳統(tǒng)和JOIN方法） （考察：多表關(guān)聯(lián)查詢 預估：40分鐘） 4. 查詢科目中,80分以上 或者 不及格的人的人數(shù)。按課程分組。 （考察：多表關(guān)聯(lián)查詢 預估：20分鐘） 5. 英語課的平均分數(shù)，最低，最高分數(shù)。 （考察：多表關(guān)聯(lián)查詢 預估：20分鐘） 6. 最低分比最高分低40分的課程名。 （考察：多表關(guān)聯(lián)查詢 預估：20分鐘） 7. 查詢每門課最高分的人的學號和科目，成績。 （考察：GroupBy查詢 預估：10分鐘） 8. 日語課不及格的人數(shù)。 （考察：COUNT查詢 預估：10分鐘） 9. 查詢李四的考試總分數(shù)。 （考察：SUM查詢 預估：10分鐘）10，所有考試有過不及格的學生人數(shù) （考察：COUNT查詢 預估：10分鐘） 11. 所有的學生姓名，及他們的平均分數(shù)，總分數(shù)。 （考察：AVG，SUM,GROUP BY查詢 預估：10分鐘） 12. 所有的圖書書名，及平均成本，平均售價，最低成本，最高成本最低售價，最高售價，總成本，總售價，書的總數(shù)。 （考察：AVG，SUM,GROUP BY查詢 預估：20分鐘） 13. 訂單中所有的圖書書名，及其銷售的數(shù)量。 （考察：SUM,GROUP BY查詢 預估：20分鐘） 14. 查詢每個圖書種類數(shù)量。 （考察：COUNT,GROUP BY查詢 預估：10分鐘） 15. 查詢每個出版社出版的圖書數(shù)量。 （考察：COUNT,GROUP BY查詢 預估：10分鐘）第八章 子查詢 1. 查詢沒參加過考試的學生的姓名,性別。（兩種方式） （考察：IN，EXISTS條件子查詢 預估：30分鐘） 參看教程附錄B表，完成下列習題： 2. 確定哪些書的零售價低于銷售所有圖書的平均零售價。 （考察：條件子查詢 預估：10分鐘） 3. 確定哪些書的成