MySQL多表查詢與事務(wù)的操作

MySQL多表查詢與事務(wù)的操作第1節(jié)回顧1.1.2聚合函數(shù)countmaxminsumavg1.1.3分頁查詢1.1.4分組查詢ng1.2約束1.2.1約束的關(guān)鍵字約束名約束關(guān)鍵字primarykeyUniquenotnullDefaultforeignkey1.2.2自增長的關(guān)鍵字：ment1.2.3級聯(lián)操作的語法：onupdatecascadeondeletecascade1.3表與表之間的關(guān)系表與表之間的三種關(guān)系關(guān)系如何維護(hù)表與兩個(gè)表是多對一方加唯一約束2.從表的主鍵同時(shí)又是外鍵1.4數(shù)據(jù)庫的三大范式都直接依賴于主鍵第2節(jié)學(xué)習(xí)目標(biāo)能夠使用左外連接和右外連接進(jìn)行多表查詢用子查詢進(jìn)行多表查詢能夠使用多表進(jìn)行查詢能夠理解事務(wù)的概念說出事務(wù)的特點(diǎn)臟讀、不可重復(fù)讀、幻讀的概念及解決辦法第3節(jié)表連接查詢3.1什么是多表查詢arareeinteptyautoincrementchar)insertintodeptnamevalues'),('財(cái)務(wù)部');createtableemp(yautoincrementrchardreferencesdeptid)insertintoempnamegendersalaryjoindatedeptidvaluesinsertintoempnamegendersalaryjoindatedeptidvalues/30insertintoempnamegendersalaryjoindatedeptidvalues,9000,'2008-insertintoempnamegendersalaryjoindatedeptidvaluesinsertintoempnamegendersalaryjoindatedeptidvalues3.1.1多表查詢的分類：3.2笛卡爾積現(xiàn)象3.2.1什么是笛卡爾積現(xiàn)象3.2.2如何清除笛卡爾積現(xiàn)象的影響ambiguousomempdeptwhereidctfromempdeptwhereempdeptiddeptidselectempnamedeptnamefromempdeptwhereempdeptiddept`id`;30記錄去匹配右邊表的記錄，如果符合條件的則顯示。如：從表.外鍵=主表.主鍵3.3.1隱式內(nèi)連接SELECTSELECT字段名FROM左表,右表WHERE條件ctfromempdeptwhereempdeptiddeptid3.3.2顯式內(nèi)連接SELECT字段名FROM左表[INNER]JOIN右表ON條件內(nèi)連接mpinnerjoindept2)確定表連接條件，員工表.dept_id=部門表.id的數(shù)據(jù)才是有效的edonedeptide4/30詢條件，我們查詢的是唐僧的信息，員工表.name='唐僧'selectfromempeinnerjoindeptdonedeptiddidwheree.`name`='唐僧selecteidenameegenderesalarydnamefromempeinnerjoindeptd現(xiàn)寫表名有點(diǎn)長，可以給表取別名，顯示的字段名也使用別名3.3.3總結(jié)內(nèi)連接查詢步驟：確定表連接的條件確定查詢的條件)確定查詢的字段3.4左外連接SELECT字段名FROM左表LEFT[OUTER]JOIN右表ON條件的基礎(chǔ)上保證左表的數(shù)據(jù)全部顯示(左表是部門，右表員工)tintodeptnamevaluesselectfromdeptdinnerjoinempeondide.`dept_id`;selectfromdeptdleftjoinempeondide.`dept_id`;30303.5右外連接SELECT字段名FROM左表RIGHT[OUTER]JOIN右表ON條件的基礎(chǔ)上保證右表的數(shù)據(jù)全部顯示empondeptidempondeptidemp`dept_id`;empondeptidemp`dept_id`;eptinnerjoineptrightjoin第4節(jié)子查詢4.1什么是子查詢selectfromempwheredeptid1;selectfromempwheredeptidselectidfromdeptwherename);的嵌套，內(nèi)部的查詢稱為子查詢用括號4.2子查詢結(jié)果的三種情況：子查詢的結(jié)果是多行單列的結(jié)果是多行多列4.3子查詢的結(jié)果是一個(gè)值的時(shí)候等SELECT查詢字段FROM表WHERE字段=(子查詢);aryfromemplectfromempwheresalaryselectmaxsalaryfromemparyfromemp息lectfromempwheresalaryselectavgsalaryfromemp4.4子查詢結(jié)果是多行單列的時(shí)候30SELECT查詢字段FROM表WHERE字段IN(子查詢);4.4.1查詢工資大于5000的員工，來自于哪些部門的名字ctdeptidfromempwheresalarywselectnamefromdeptwhereidselectdeptidfromempwheresalary5000);selectnamefromdeptwhereidinselectdeptidfromempwheresalary000);4.4.2查詢開發(fā)部與財(cái)務(wù)部所有的員工信息lectidfromdeptwherenameinselectfromempwheredeptidinselectidfromdeptwherenamein,'財(cái)務(wù)4.5子查詢的結(jié)果是多行多列SELECT查詢字段FROM(子查詢)表別名WHERE條件;別名，否則這張表沒有名稱則無法訪問表中的字段4.5.1查詢出2011年以后入職的員工信息，包括部門名稱selectfromempwherejoindate1-1-1';selectfromdeptdselectfromempwherejoindate1-1-1')ewhered.`id`=e.dept_id;lectfromempinnerjoindeptonempdeptiddeptidwherejoindate-1';selectfromempinnerjoindeptonempdeptiddept`id`andjoindate-1';4.6子查詢小結(jié)30第5節(jié)事務(wù)5.1事務(wù)的應(yīng)用場景說明CREATETABLEaccount(YAUTOINCREMENTNAMEVARCHAR,lanceDOUBLEtNAMEbalanceVALUES('張三',1000),('李四',1000);alancebalancewherenamealancebalancewherenameountountQL5.2手動提交事務(wù)務(wù)2)自動提交事務(wù)2.1手動提交事務(wù)的SQL語句starttransaction;5.2.2手動提交事務(wù)使用過程：30模擬張三給李四轉(zhuǎn)500元錢(成功)目前數(shù)據(jù)庫數(shù)據(jù)如下：模擬張三給李四轉(zhuǎn)500元錢(失敗)目前數(shù)據(jù)庫數(shù)據(jù)如下：2.更新其中某一個(gè)賬戶5.3.2取消自動提交5.4事務(wù)原理事務(wù)開啟之后,所有的操作都會臨時(shí)保存到事務(wù)日志中,事務(wù)日志只有在得到commit命令才會同步到數(shù)據(jù)表ack5.4.1原理圖：5.4.2事務(wù)的步驟：創(chuàng)建此用戶臨時(shí)日志文件開啟事務(wù)以后，所有的操作都會先寫入到臨時(shí)日志文件中查詢操作從表中查詢，但會經(jīng)過日志文件加工后才返回志文件。5.5.1什么是回滾點(diǎn)稱5.5.2回滾點(diǎn)的操作語句回滾點(diǎn)的操作語句語句savepoint名字rollbackto名字5.5.3具體操作：開啟事務(wù)行過程5.6事務(wù)的隔離級別5.6.1事務(wù)的四大特性ACID原子性(Atomicity)一致性(Consistency)事務(wù)在執(zhí)行前數(shù)據(jù)庫的狀態(tài)與執(zhí)行后數(shù)據(jù)庫的狀態(tài)保持一致。如：轉(zhuǎn)賬前2個(gè)人的隔離性(Isolation)事務(wù)與事務(wù)之間不應(yīng)該相互影響，執(zhí)行時(shí)保持隔離的狀態(tài)。持久性(Durability)一旦事務(wù)執(zhí)行成功，對數(shù)據(jù)庫的修改是持久的。就算關(guān)機(jī)，也是保存下來的。5.6.2事務(wù)的隔離級別并發(fā)訪問的問題含義一個(gè)事務(wù)讀取到了另一個(gè)事務(wù)中尚未提交的數(shù)據(jù)據(jù)內(nèi)容不一致，要求的是一個(gè)事務(wù)中多次讀取時(shí)數(shù)據(jù)是一致的，這一個(gè)事務(wù)中兩次讀取的數(shù)據(jù)的數(shù)量不一致，要求在一個(gè)事務(wù)多次讀取的數(shù)據(jù)的數(shù)量是一致5.6.3MySQL數(shù)據(jù)庫有四種隔離級別級別名字隔離級別臟讀不可重復(fù)讀幻讀數(shù)據(jù)庫默認(rèn)隔離級別1是是是2否是是3否否是4lizable否否否級別越高，性能越差，安全性越高。5.6.4MySQL事務(wù)隔離級別相關(guān)的命令查詢隔離級別select@@tx_isolation; 設(shè)置隔離級別setglobaltransactionisolationlevel級別字符串;5.6.5臟讀的演示mysqluroot-prootctionisolationlevelreaduncommittedayupdateaccountsetbalancebalance00whereid=1;updateaccountsetbalancebalance00whereid=2;4.B窗口查詢賬戶5.A窗口回滾6.B窗口查詢賬戶，錢沒了隔離級別進(jìn)行提升UPDATEaccountSETbalance1000;1.在A窗口設(shè)置全局的隔離級別為readcommittedctionisolationlevelreadcommittedAB時(shí)開啟事務(wù)updateaccountsetbalancebalance00whereid=1;updateaccountsetbalancebalance00whereid=2;3.B窗口查詢賬戶A窗口commit提交事務(wù)4.B窗口查看賬戶5.6.6不可重復(fù)讀的演示UPDATEUPDATEaccountSETbalance1000;ctionisolationlevelreadcommittedupdateaccountsetbalancebalance00whereid=1;304.B窗口查詢客解決不可重復(fù)讀的問題：UPDATEaccountSETbalance1000;1.A窗口設(shè)置隔離級別為：repeatablereadansactionisolationlevelrepeatableread303.A窗口更新數(shù)據(jù)updateaccountsetbalancebalance00whereid=1;4.B窗口查詢305.6.7幻讀的演示UPDATEaccountSETbalance1000;olationlevelserializable30accountBinsertintoaccountnamebalancevaluesLaoWang);account308.A窗口就能看到最新的數(shù)據(jù)30leSQL第6節(jié)DCL(DataControlLanguage)管306.1創(chuàng)建用戶6.1.1語法：CREATEUSER'用戶名'@'主機(jī)名'IDENTIFIEDBY'密碼';6.1.2關(guān)鍵字說明：名ost以使用通配符%以為空，如果為空則該用戶可以不需要密碼登陸服務(wù)器6.1.3具體操作：lcreateuseruserlocalhostidentifiedby'123';createuseruseridentifiedby'123';6.2給用戶授權(quán)權(quán)限！需要給用戶授權(quán)6.2.1語法：GRANT權(quán)限1,權(quán)限2...ON數(shù)據(jù)庫名.表名TO'用戶名'@'主機(jī)名';306.2.2關(guān)鍵字說明：O名的哪些表。如果要授予該用戶對所有數(shù)據(jù)庫和表的相應(yīng)操作限則可用*表示，如*.*@'主機(jī)名'6.2.3具體操作：grantcreatealterinsertupdateselectontesttouser'@'localhost';grantallontouser%';6.3撤銷授權(quán)6.3.1語法：REVOKE權(quán)限1,權(quán)限2...ON數(shù)據(jù)庫.表名revokeallontest.*from'user1'@'localhost';'用戶名'@'主機(jī)名';字名些表，如果要取消該用戶對所有數(shù)據(jù)庫和表的操作權(quán)限則可用*表*.*@'主機(jī)名'6.3.2具體操作：keallontestfromuserlocalhost要與創(chuàng)建時(shí)相同，各自要加上單引號6.4查看權(quán)限6.4.1語法：SHOWGRANTSFOR'用戶名'@'主機(jī)名';306.4.2具體操作：注：usa