WEB數(shù)據(jù)庫原理與應(yīng)用學(xué)習(xí)情境

任務(wù)5MySＱL5.1數(shù)據(jù)庫及其應(yīng)用掌握MyＳQＬ5．1、安裝、基本操作,及其建表、數(shù)據(jù)庫關(guān)系圖、查詢、視圖、存儲過程、函數(shù)、觸發(fā)器等操作.使用Ｎａｖicaｔ工具依據(jù)網(wǎng)絡(luò)商店數(shù)據(jù)庫結(jié)構(gòu)完成相關(guān)練習(xí)操作?；顒樱笔熳RMyＳQL５．1ＭySQL是一個小型關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，開發(fā)者為瑞典MyＳＱLAB公司。在2００8年１月1６號被Sun公司收購。而２009年,SＵN又被Ｏraｃａl收購.對于Mysql的前途,沒有任何人抱樂觀的態(tài)度.目前ＭySQL被廣泛地應(yīng)用在Ｉｎtｅrnｅt上的中小型網(wǎng)站中。由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點,很多中小型網(wǎng)站為了降低網(wǎng)站總體擁有成本而選擇了MｙSQL作為網(wǎng)站數(shù)據(jù)庫。MySＱL的官方網(wǎng)站的網(wǎng)址是：ＨYPＥRLIＮK＂ｈttp：//ｗwｗ.mｙsql．cｏm＂ｗwｗ.myｓqｌ.coｍMySＱL的特性1．使用C和C++編寫，并使用了多種編譯器進(jìn)行測試，保證源代碼的可移植性2。支持AＩX、FｒｅeBSＤ、HP-UX、Linuｘ、MacＯＳ、ＮoｖｅｌlＮｅtware、ＯpenBSＤ、OS／２Wｒaｐ、Soｌaｒis、Ｗｉｎdｏws等多種操作系統(tǒng)３．為多種編程語言供應(yīng)了API。這些編程語言包括Ｃ、C++、Ｅｉfｆeｌ、Javａ、Perl、ＰHP、Ｐythｏn、Ｒｕby和Ｔcｌ等。４。支持多線程,充分利用ＣPU資源5.優(yōu)化的SＱL查詢算法,有效地提高查詢速度６.既能夠作為一個單獨的應(yīng)用程序應(yīng)用在客戶端服務(wù)器網(wǎng)絡(luò)環(huán)境中,也能夠作為一個庫而嵌入到其他的軟件中供應(yīng)多語言支持,常見的編碼如中文的ＧB２３12、BIG5，日文的Shiｆt_JIＳ等都可以用作數(shù)據(jù)表名和數(shù)據(jù)列名7.供應(yīng)TCP/IP、ＯDBC和ＪDＢC等多種數(shù)據(jù)庫連接途徑８．供應(yīng)用于管理、檢查、優(yōu)化數(shù)據(jù)庫操作的管理工具9．可以處理擁有上千萬條記錄的大型數(shù)據(jù)庫活動２MｙＳＱL5．1安裝打開安裝程序首頁面,如圖３—７２所示：圖3－72MySql安裝首頁面點擊“下一步”,選擇典型安裝,如圖3—7３所示。圖3—7３MｙＳｑl安裝向?qū)Ы缑孢x擇開頭安裝，如圖3-７４所示：圖3-７４MySqｌ安裝信息顯示選擇配置MｙSＱL服務(wù)器，同時不選擇注冊服務(wù)器,如圖３-7５所示：圖3－7５MyＳql安裝注冊界面選擇標(biāo)準(zhǔn)配置,如圖３－76所示：圖3—76ＭｙＳql安裝配置類型選擇一個作為服務(wù)啟動的名稱，如圖３－77所示：圖３—７7MｙSqｌ服務(wù)啟動配置輸入ｒoｏt管理員的密碼：＊*＊*＊＊，同時必須勾選Enabｌerooｔａｃcｅｓsfｒomrｅmotｅｍacｈｉｎes，Enaｂlerｏｏｔaccesｓfromremotemachinｅs的作用是允許用戶遠(yuǎn)程登錄，如圖３-78所示。圖3-78MySｑl用戶管理開頭執(zhí)行配置安裝，如圖３—79所示:圖3-７9MySql安裝執(zhí)行界面完成安裝，如圖3—８0所示:圖3—80MｙSｑｌ安裝完成活動３Ｎａｖiｃaｔ安裝Ｎaｖicat是一個強(qiáng)大的MｙＳQL數(shù)據(jù)庫管理和開發(fā)工具。Navicat為專業(yè)開發(fā)者供應(yīng)了一套強(qiáng)大的足夠尖端的工具，但它對于新用戶仍然是易于學(xué)習(xí)。本書中大部分MySQL應(yīng)用實例都是在這款工具中完成的。Nａvicａt(yī)使用了極好的圖形用戶界面（GUI），可以讓你用一種平安和更為容易的方式快速和容易地創(chuàng)建、組織、存取和共享信息。基于Wｉndoｗs平臺，為MｙSＱL量身訂作，供應(yīng)類似于MｓＳＱL的用管理界面工具.進(jìn)入開頭安裝首頁面,如圖3－81所示:圖３－81Nａviｃat工具安裝首界面選擇安裝路徑,如圖3-8２所示:圖3－８2Ｎaｖicａt(yī)工具安裝路徑選擇開頭安裝，如圖3-8３所示:圖3-8３Nａvｉcat工具安裝安裝完成，如圖3-84所示：圖３—８4Nａｖicat工具安裝完成活動４ＭｙSQL5．1應(yīng)用=1\＊ＧB３①創(chuàng)建數(shù)據(jù)庫依據(jù)學(xué)習(xí)情境2,任務(wù)６數(shù)據(jù)庫的設(shè)計舉例里面的活動2E-R圖的設(shè)計，完成數(shù)據(jù)表的創(chuàng)建工作.數(shù)據(jù)庫名稱:Shｏppｉｎg表名：客戶信息表（bClｉeｎt）字段名稱英文名稱類型長度客戶編號(ＧUＩD)Ｕｓeｒ＿ID整數(shù)客戶姓名Uｓｅr＿Naｍｅ字符串類型３０性別User_Sｅx字符串類型4誕生日期Uｓｅr_Birthdａy日期類型VＩP客戶類型Ｕｓer_Tｙpｅ字符串類型５0地址Uｓeｒ_Ａｄdress字符串類型10０聯(lián)系電話User_Ｔel字符串類型20表名：客戶訂單表(bＯrder）字段名稱英文名稱類型長度訂單編號〈自動加1〉Oｒdｅr_IＤ整數(shù)下單日期Ｏrdｅr_Ｄatｅ日期類型客戶編號User＿IＤ整數(shù)商品編號Goods_ID整數(shù)訂單金額Order_Money雙精度數(shù)值１２，２表名：商品信息表（bＧoｏｄｓ）字段名稱英文名稱類型長度商品編號Gooｄs＿ＩD整數(shù)商品名稱Goｏdｓ_Ｎａme字符串類型5０價格Gｏoｄｓ_Pｒice雙精度數(shù)值12，２通過Naviｃａｔ建立ＭySQL數(shù)據(jù)庫連接,如下圖所示.輸入連接名，主機(jī)名/ＩP地址，端口，用戶名，密碼。建立數(shù)據(jù)庫服務(wù)器的連接.這里連接名用戶自己依據(jù)需要來設(shè)置，主機(jī)名／IＰ地址里面輸入MySQL數(shù)據(jù)庫服務(wù)器的機(jī)器名或者數(shù)據(jù)庫服務(wù)器的IＰ地址，端口為默認(rèn)的330６,用戶名和密碼為安裝時設(shè)置的用戶名和密碼。輸入完成以后，選擇“連接測試”，如果連接成功，會彈出對話框“連接成功”。連接測試完成以后，選擇“確定”按鈕,進(jìn)入數(shù)據(jù)庫管理系統(tǒng),如圖3—8５所示。圖３—8５Ｎavicat數(shù)據(jù)庫連接界面打開連接，在連接建立完成以后，選擇“打開連接"打開MySQL數(shù)據(jù)庫管理系統(tǒng)圖形管理界面,如圖3－86所示。圖３-86Nａvicat工具首界面連接打開以后，單擊右鍵,選擇“創(chuàng)建數(shù)據(jù)庫”。依據(jù)需要創(chuàng)建Ｓhoppinｇ數(shù)據(jù)庫，如下圖所示。數(shù)據(jù)庫名稱部分輸入:Ｓｈoppｉng，為了更好的識別漢字在字符集部分輸入“utf8－－UＴＦ—8Ｕniｃode”，整理部分輸入“uｔｆ８＿ｇｅｎeral_ci"，如圖３—８7所示。圖３—８7Navicａｔ字符選擇數(shù)據(jù)庫創(chuàng)建完成，在Shoppinｇ數(shù)據(jù)庫下面新建數(shù)據(jù)表,單擊右鍵選擇“創(chuàng)建表”,如下圖顯示,如圖3－８8所示.圖3－88Ｎａvicaｔ創(chuàng)建數(shù)據(jù)表＝2\*GＢ3②進(jìn)入創(chuàng)建數(shù)據(jù)表界面，創(chuàng)建客戶信息表(bＣlieｎt)、客戶訂單表(bOrder)和商品信息表(ｂGoods)。ｂCliｅｎt表結(jié)構(gòu)圖如下３-8９所示。圖３—89Navicａｔ數(shù)據(jù)表字段編輯客戶信息表(bClient）、客戶訂單表（bOrｄeｒ)和商品信息表（ｂＧｏodｓ）對應(yīng)的ＳＱL腳本如下所示CRＥAＴETＡBLE`ｂｃlｉｅnｔ`（`User＿ID｀iｎt（11)NOＴNＵLＬAＵTO_IＮCＲＥMENT，`User_Naｍe`ｖａｒcｈａr(５0)DEFAＵLＴＮＵLL,`Uｓｅr_Sｅｘ`vａrｃhar（４)DEFAＵLTNULL,`Ｕser_Biｒｔhday｀daｔeDＥFAULTＮUＬＬ，`Uｓer_Tｙｐe`vａrchaｒ（50）ＤEＦAUＬＴNULＬ，`Ｕsｅr_Aｄｄrｅｓｓ`ｖarchａｒ（1０0）DEFAULＴNULLCOMＭＥNT＇聯(lián)系地址',｀Ｕser_Ｔｅl｀varchaｒ(20)ＤEFAULTNＵＬLCOMＭEＮＴ'聯(lián)系電話＇,PＲＩMARYKＥＹ(`Uｓeｒ_ＩＤ`））EＮGＩNＥ=ＩnｎｏＤBDＥFＡＵLTCHＡＲSET=utf8RＯＷ_FＯRMＡＴ=COMPACTＣＲEＡＴETABLＥ｀bgoｏｄｓ`（`Ｇooｄｓ_ID`int（1１)ＮＯＴＮＵＬL,`Ｇoods_Ｎame｀varcｈaｒ(50）DＥＦＡULＴNULＬ，`Gooｄｓ_Prｉcｅ`deｃimａｌ（15，8)DEＦＡUＬＴNULL，ＰRＩMAＲYKEY（`Gｏｏｄs＿IＤ`））ＥＮＧIＮE=IｎnoDBDＥFＡＵLTCHＡRSET＝utf8ＣRＥATＥTＡBLＥ｀ｂordｅｒ`（`Order_ＩD｀ｉｎt(1１)NＯTＮULLAＵTO＿ＩNCREMENＴ，｀Orｄｅr_Ｄate`ｄateＤEFAULTＮULＬ,`Ｕser_ID`ｉnt（１1)DEFAULTＮULＬ,`Goodｓ＿ID`int（１1）DＥＦAULTNULL，`Orｄeｒ_Ｍoｎeｙ`decｉmal(１5，8）DＥＦAULＴNULL，ＰRIMＡRＹＫEY(`Ｏrder_IＤ`）)ENＧINE=ＩｎnoDBＡUTO_INＣＲEＭEＮT=2DＥＦAUＬTCHＡＲSET=ｕtf8=3\＊GB3③創(chuàng)建視圖例如：創(chuàng)建用戶訂單視圖訂單視圖結(jié)構(gòu)，需要顯示{訂單號碼、下單日期、用戶編號、用戶姓名、用戶電話、用戶地址、產(chǎn)品名稱、訂單金額}SＱＬ腳本如下.ｃreａt(yī)evｉew客戶訂單aｓＳelecｔ`bordeｒ`。｀Orｄｅr_ＩD`AS`訂單號碼`，`ｂoｒdeｒ`．｀Orｄｅr＿Ｄate`AS｀下單日期`，｀bordｅr｀。`Ｕｓer_ID｀AS｀用戶編號｀，`bclieｎt`．`Uｓer_Naｍe`AS`用戶姓名`，｀ｂｃlienｔ`。`Ｕser＿Ｔｅl｀AS`用戶電話｀，｀bclｉeｎt`．`Ｕsｅr_Aｄdｒｅｓs`AS｀用戶地址｀,｀bgooｄs｀。`Gｏods＿Naｍe｀AS`商品名稱｀,`borｄer｀．`Orｄer_Moneｙ｀AＳ`訂單金額`fｒom（（`borｄeｒ`ｌeｆtjｏin`bcｌｉｅｎt`ｏｎ（（`bｃｌient`．`User_ID`=｀bｏｒder`。`Uｓeｒ＿ID｀））)lefｔjｏｉｎ`bgoods｀on（(｀ｂｏrｄeｒ`.｀Ｇoods_ID`=｀bgoｏds`.`Goods_IＤ`））)與SQLＳerver200５類似,Ｎaviｃａt(yī)也設(shè)置了視圖圖形界面，如下圖３-90所示。圖3-90Ｎavｉcat創(chuàng)建視圖＝4＼*ＧＢ３④數(shù)據(jù)表基本操作用戶表插入新用戶ＩnsertｉnｔoｂClｉeｎt(Uｓer_Ｎaｍe，Useｒ＿Seｘ，Uｓｅｒ_Tｙｐe,User_Aｄｄreｓs，User＿Tｅｌ，Uｓｅr_Ｂirtｈday）valuｅs（’張三'，’男'，'ＶIＰ1’,＇蘇州國際教育園北區(qū)學(xué)府路號',＇13９11１111１1＇，'１9７９-12—2５'）商品表增加商品InsｅrｔｉｎtｏbGｏodｓ(Goｏdｓ_ＩＤ，Gooｄs＿Name，Gｏoｄs_Prｉｃe）vaｌｕｅs（1，＇聯(lián)想筆記本＇,２8０0）;InｓｅｒtintobGｏods（Ｇoodｓ_ID,Gｏｏds＿Nａmｅ,Gｏods_Ｐricｅ）vａlｕｅs(2，＇蘋果MP3'，12０）；用戶訂單表增加用戶新的訂單InserｔiｎtobOrｄeｒ（Ｏrder_Dａt(yī)e,Uｓeｒ＿ID,Ｇoods_ID，Oｒder_Moｎey）vaｌuｅs('２010—04-2９’,1,1,16０.6６)修改用戶訂單表里面訂單編號為“１”的商品的價格為180元updatebＯrｄｅｒsetOrder_Ｍｏneｙ＝18０wheｒeOrdｅr_ＩＤ＝１刪除用戶訂單表里面訂單編號為“1"的訂單信息ｄeｌetｅｆromｂOrderｗhereOｒdｅr_IＤ=1查詢視圖“用戶訂單"，里面訂單號碼為“1"的訂單Selｅｃt＊ｆrｏm用戶訂單where訂單號碼=2＝5\＊GB３⑤創(chuàng)建存儲過程一個存儲程序是可以被存儲在服務(wù)器中的一套SQL語句。一旦它被存儲了，客戶端不需要再重新發(fā)布單獨的語句，而是可以引用存儲程序來替代.下面一些情況下存儲程序尤其有用：當(dāng)用不同語言編寫多客戶應(yīng)用程序,或多客戶應(yīng)用程序在不同平臺上運(yùn)行且需要執(zhí)行相同的數(shù)據(jù)庫操作之時。平安極為重要之時。比如,銀行對全部一般操作使用存儲程序。這供應(yīng)一個堅固而平安的環(huán)境，程序可以確保每一個操作都被妥善記入日志。在這樣一個設(shè)置中，應(yīng)用程序和用戶不行能直接訪問數(shù)據(jù)庫表，但是僅可以執(zhí)行指定的存儲程序.修改用戶編號為“1”的姓名為”李四"，同時把該用戶的全部訂單在現(xiàn)在的訂單費(fèi)用的基礎(chǔ)上削減1０%，并顯示訂單視圖中用戶編號是“1”的信息.CREATEprｏcｅｄureｕpdaｔe_Ｆｕｎ(p＿Ｕｓeｒ_ＩDvaｒchａr（50)，P_ＵｓｅrＮamｅｖａrchar（５0))begiｎSＴARTＴRANSACTIＯN;upｄａt(yī)eｂCliｅntsｅtUser_Naｍｅ=P_ＵsｅrNａmeｗheｒeＵsｅｒ_ID＝ｐ_Useｒ_ID；updatｅbＯｒderｓetＯrｄｅr＿Ｍｏney=Order＿M(jìn)ｏney*０．9ｗｈereＵsｅr＿IＤ=p_Ｕser＿IＤ；CＯMMＩＴ;Ｓelect用戶編號，用戶姓名，ｓuｍ（訂單金額）as訂單總金額from用戶訂單wｈerｅ用戶姓名=P_UserNamegrouｐby用戶編號，用戶姓名;eｎd調(diào)用存儲過程操作如下。CＡLＬupdaｔe_Fun(＇１’，'王五’)；=6\＊ＧＢ3⑥創(chuàng)建觸發(fā)器對用戶訂單表（boｒder）建立觸發(fā)操作,監(jiān)視訂單表的新增、刪除和更新操作，并把結(jié)果插入到監(jiān)控表。監(jiān)控表結(jié)構(gòu)如下所示。表名:用戶訂單監(jiān)控表（ｂｗａt(yī)cｈ_Gｏods)字段名稱英文名稱類型長度自動編號AUTO＿ＩDint觸發(fā)時間Op＿dａt(yī)eｄaｔe監(jiān)控內(nèi)容Op_ｃontent字符串１０0表結(jié)構(gòu)SQＬ腳本如下。CＲEＡTETABLE｀ｂwaｔｃh_goods｀（`AＵTO_IＤ｀iｎt(11）ＮOＴＮULＬＡUTO＿INＣREMＥNT，｀Op＿dａt(yī)e`daｔｅtｉmeDEFＡULTNＵLL，`Ｏp_cｏnｔｅnt`varcｈaｒ(1００）ＤEＦＡUＬTＮＵＬＬ,ＰRIMARＹKEY（`AＵTＯ_ID`））EＮＧINE=InnoDBDEFAＵLTＣHＡＲSET＝ｕtf8對用戶訂單表創(chuàng)建新增、更新、刪除觸發(fā)器，監(jiān)視對用戶訂單表的全