MySQL數(shù)據(jù)庫技術教案

教案2011～2012學年第1學期課程名稱MySQL數(shù)據(jù)庫系(院、部)教研室(實驗室)授課班級主講教師職稱使用教材MySQL實用教程二○一一年八月

教案課程名稱MYSQL數(shù)據(jù)庫應用授課教師授課班級授課時數(shù)1授課方式理論課授課重點、難點1.mysql關系數(shù)據(jù)庫簡介2.mysql安裝授課內容、教具與時間分配1.MySQL數(shù)據(jù)庫是從多關系型數(shù)據(jù)庫產品中的一個，相比其他數(shù)據(jù)庫而言，MySQL數(shù)據(jù)庫可以稱為目前運行速度最快的SQL語言。除此外還具有許多其他數(shù)據(jù)庫所不具有的功能和選擇。MySQL數(shù)據(jù)庫是一種完全免費的產品，用戶可以從網(wǎng)上直接下載，可以是個人，也可是商業(yè)用戶，是不用支付任何費用的一個數(shù)據(jù)庫。MySQL數(shù)據(jù)庫具有以下特點：?沒有限制同時訪問數(shù)據(jù)庫的用戶數(shù)量。?可以保存記錄超過50,000,000條。?數(shù)據(jù)庫系統(tǒng)的運行速度是目前所有產品中最快的。?可以簡單、有效地進行用戶權限的設置。?從MySQL數(shù)據(jù)庫具有的以上特點可以看出，MySQL數(shù)據(jù)庫是一個運行速度快、多線程、多用戶和強大的SQL數(shù)據(jù)庫服務器。MySQL可以運行在多種平臺上，包括WindowsNT、UNIX和Linux系統(tǒng)。MySQL是建立數(shù)據(jù)庫驅動和動態(tài)網(wǎng)站的最佳數(shù)據(jù)庫。PHP、MySQL和Apache是Linux平臺網(wǎng)站的最佳組合。目前，國際上知名公司也開始把MySQL作為其數(shù)據(jù)庫管理系統(tǒng)，這也充分的證明了MySQL數(shù)據(jù)庫的優(yōu)越性能和廣闊的發(fā)展前景。2.安裝數(shù)據(jù)庫系統(tǒng)?數(shù)據(jù)庫概述1.1數(shù)據(jù)庫理論基礎1.1.1數(shù)據(jù)存儲方式1.1.2數(shù)據(jù)庫泛型1.1.3數(shù)據(jù)庫在開發(fā)中作用小結復習思考題參考書本次課程主要內容為mysql關系數(shù)據(jù)庫簡介Mysql安裝配置《mysql入門很簡單》清華大學出版社Mysql數(shù)據(jù)庫應用課程名稱MYSQL數(shù)據(jù)庫應用授課教師授課班級授課時數(shù)4授課方式理論課授課重點、難點1.Mysql數(shù)據(jù)類型授課內容、教具與時間分配1.MySQL的數(shù)據(jù)類型在MySQL中有如下幾種數(shù)據(jù)類型：（1）數(shù)值型MySQL提供了五種整型：TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT，三種浮點類型：FLOAT、DOUBLE和DECIMAL。數(shù)值是諸如32或153.4這樣的值。MySQL支持科學表示法，科學表示法由整數(shù)或浮點數(shù)后跟“e”或“E”、一個符號（“+”或“-”）和一個整數(shù)指數(shù)來表示。1.24E+12和23.47e-1都是合法的科學表示法表示的數(shù)。而1.24E12不是合法的，因為指數(shù)前的符號未給出。浮點數(shù)由整數(shù)部分、一個小數(shù)點和小數(shù)部分組成。整數(shù)部分和小數(shù)部分可以分別為空，但不能同時為空。數(shù)值前可放一個負號“-”以表示負值。（2）字符（串）型類型名說明CHAR定長字符串VARCHAR可變長字符串TINYBLOB非常小的BLOB（二進制大對象）BLOB小BLOBMEDIUMBLOB中等的BLOBLONGBLOB大BLOBTINYTEXT非常小的文本串TEXT小文本串MEDIUMTEXT中等文本串LONGTEXT大文本串ENUM枚舉；列可賦予某個枚舉成員SET集合；列可賦予多個集合成員（3）日期和時間型類型名說明DATE“YYYY-MM-DD”格式表示的日期值TIME“hh:mm:ss”格式表示的時間值DATETIME“YYYY-MM-DDhh:mm:ss”格式TIMESTAMP“YYYYMMDDhhmmss”格式表示的時間戳值YEAR“YYYY”格式的年份值（4）NULL值NULL表示未知值。比如填寫表格中通訊地址不清楚留空不填寫，這就是NULL值。小結復習思考題參考書本次課程主要內容為Mysql數(shù)據(jù)類型《mysql入門很簡單》清華大學出版社Mysql數(shù)據(jù)庫應用課程名稱數(shù)據(jù)庫操作授課教師授課班級授課時數(shù)2授課方式理論課授課重點、難點1創(chuàng)建數(shù)據(jù)庫2刪除數(shù)據(jù)庫3數(shù)據(jù)庫存儲引擎3.1MySQL存儲引擎簡介3.2InnoDB存儲引擎3.3MyISAM存儲引擎3.4MEMORY存儲引擎.3.5存儲引擎的選擇授課內容、教具與時間分配創(chuàng)建數(shù)據(jù)庫（當然，數(shù)據(jù)庫名\"asb\"不能是已經存在的）mysql>createdatabaseasb;選用數(shù)據(jù)庫，使其成為當前操作的數(shù)據(jù)庫mysql>useasb;成功選中后會有如下顯示：Databasechanged甚至可以在窗口命令登陸mysql數(shù)據(jù)庫的時候直接選中要操作的數(shù)據(jù)庫（當然前提是asb數(shù)據(jù)庫存在）bin>mysqlasb-uuesername-pEnterpassword:******查看當前所操作的數(shù)據(jù)庫名稱mysql>selectdatabase();運行后結果如下：+------------+|database()|+------------+|asb|+------------+1rowinset(0.00sec)數(shù)據(jù)庫存儲引擎：ISAMISAM是一個定義明確且歷經時間考驗的數(shù)據(jù)表格管理方法，它在設計之時就考慮到數(shù)據(jù)庫被查詢的次數(shù)要遠大于更新的次數(shù)。因此，ISAM執(zhí)行讀取操作的速度很快，而且不占用大量的內存和存儲資源。ISAM的兩個主要不足之處在于，它不支持事務處理，也不能夠容錯：如果你的硬盤崩潰了，那么數(shù)據(jù)文件就無法恢復了。如果你正在把ISAM用在關鍵任務應用程序里，那就必須經常備份你所有的實時數(shù)據(jù)，通過其復制特性，MySQL能夠支持這樣的備份應用程序。MyISAMMyISAM是MySQL的ISAM擴展格式和缺省的數(shù)據(jù)庫引擎。除了提供ISAM里所沒有的索引和字段管理的大量功能，MyISAM還使用一種表格鎖定的機制，來優(yōu)化多個并發(fā)的讀寫操作。其代價是你需要經常運行OPTIMIZETABLE命令，來恢復被更新機制所浪費的空間。MyISAM還有一些有用的擴展，例如用來修復數(shù)據(jù)庫文件的MyISAMChk工具和用來恢復浪費空間的MyISAMPack工具。MyISAM強調了快速讀取操作，這可能就是為什么MySQL受到了Web開發(fā)如此青睞的主要原因：在Web開發(fā)中你所進行的大量數(shù)據(jù)操作都是讀取操作。所以，大多數(shù)虛擬主機提供商和Internet平臺提供商(InternetPresenceProvider，IPP)只允許使用MyISAM格式。HEAPHEAP允許只駐留在內存里的臨時表格。駐留在內存里讓HEAP要比ISAM和MyISAM都快，但是它所管理的數(shù)據(jù)是不穩(wěn)定的，而且如果在關機之前沒有進行保存，那么所有的數(shù)據(jù)都會丟失。在數(shù)據(jù)行被刪除的時候，HEAP也不會浪費大量的空間。HEAP表格在你需要使用SELECT表達式來選擇和操控數(shù)據(jù)的時候非常有用。要記住，在用完表格之后就刪除表格。讓我再重復一遍：在你用完表格之后，不要忘記刪除表格。InnoDB和BerkleyDBInnoDB和BerkleyDB(BDB)數(shù)據(jù)庫引擎都是造就MySQL靈活性的技術的直接產品，這項技術就是MySQL++API。在使用MySQL的時候，你所面對的每一個挑戰(zhàn)幾乎都源于ISAM和MyISAM數(shù)據(jù)庫引擎不支持事務處理也不支持外來鍵。盡管要比ISAM和MyISAM引擎慢很多，但是InnoDB和BDB包括了對事務處理和外來鍵的支持，這兩點都是前兩個引擎所沒有的。如前所述，如果你的設計需要這些特性中的一者或者兩者，那你就要被迫使用后兩個引擎中的一個了。小結復習思考題參考書本次課程主要內容為Mysql數(shù)據(jù)庫操作數(shù)據(jù)庫存儲引擎復習題目：1.什么是存儲引擎？如何選擇？2.mysql數(shù)據(jù)庫操作方法《mysql入門很簡單》清華大學出版社Mysql數(shù)據(jù)庫應用課程名稱數(shù)據(jù)庫表操作授課教師授課班級授課時數(shù)4授課方式理論課授課重點、難點1.創(chuàng)建表2.修改表3.刪除表授課內容、教具與時間分配用CREATETABLE語句創(chuàng)建數(shù)據(jù)表用CREATETABLE語句創(chuàng)建表。此語句的完整語法是相當復雜的，因為存在那么多的可選子句，但在實際中此語句的應用相當簡單。如我們在第1章中使用的所有CREATETABLE語句都不那么復雜。有意思的是，大多數(shù)復雜東西都是一些子句，這些子句MySQL在分析后扔掉。參閱附錄1可以看到這些復雜的東西。1、CREATETABLE語句的基本語法CREATETABLEtbl_name(create_definition,...)[TYPE=table_type]create_definition:col_nametype[NOTNULL|NULL][DEFAULTdefault_value][AUTO_INCREMENT][PRIMARYKEY]用ALTERTABLE語句修改表的結構有時你可能需要改變一下現(xiàn)有表的結構，那么AlterTable語句將是你的合適選擇。增加列altertabletbl_nameaddcol_nametype例如,給表增加一列weightmysql>altertablepetaddweightint;刪除列altertabletbl_namedropcol_name例如，刪除列weight：mysql>altertablepetdropweight;改變列altertabletbl_namemodifycol_nametype例如，改變weight的類型：mysql>altertablepetmodifyweightsamllint;另一種方法是：altertabletbl_namechangeold_col_namecol_nametype例如：mysql>altertablepetchangeweightweightsamllint;給列更名mysql>altertablepetchangeweightwei;給表更名altertabletbl_namerenamenew_tbl例如，把pet表更名為animalmysql>altertablepetrenameanimal;改變表的類型另外，可以為列增加或刪除索引等屬性。用DROPTABLE語句刪除數(shù)據(jù)表DROPTABLE[IFEXISTS]tbl_name[,tbl_name,...]DROPTABLE刪除一個或多個數(shù)據(jù)庫表。所有表中的數(shù)據(jù)和表定義均被刪除，故小心使用這個命令！在MySQL3.22或以后版本，你可以使用關鍵詞IFEXISTS類避免不存在表的一個錯誤發(fā)生。例如：mysql>USEmytest;mysql>DROPTABLEguestbook;或者，也可以同時指定數(shù)據(jù)庫和表：mysql>DROPTABLEmytest.guestbook;小結復習思考題參考書本節(jié)講述了有關表的大部分操作，現(xiàn)在將所述內容總結如下：MySQL的表的三種類型如何創(chuàng)建表、刪除表如何改變表的結構、名字復習題目：1.數(shù)據(jù)表操作方法？教材：《mysql實用教程》《mysql入門很簡單》清華大學出版社Mysql數(shù)據(jù)庫應用課程名稱Mysql索引授課教師授課班級授課時數(shù)2授課方式理論課授課重點、難點Mysql索引授課內容、教具與時間分配索引是快速搜索的關鍵。MySQL索引的建立對于MySQL的高效運行是很重要的。下面介紹幾種常見的MySQL索引類型。MySQL索引類型包括：（1）普通索引這是最基本的索引，它沒有任何限制。它有以下幾種創(chuàng)建方式：◆創(chuàng)建索引CREATEINDEXindexNameONmytable(username(length));如果是CHAR，VARCHAR類型，length可以小于字段實際長度；如果是BLOB和TEXT類型，必須指定length，下同?！粜薷谋斫Y構ALTERmytableADDINDEX[indexName]ON(username(length))◆創(chuàng)建表的時候直接指定CREATETABLEmytable(IDINTNOTNULL,usernameVARCHAR(16)NOTNULL,INDEX[indexName](username(length)));刪除索引的語法：DROPINDEX[indexName]ONmytable;（2）唯一索引它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創(chuàng)建方式：◆創(chuàng)建索引CREATEUNIQUEINDEXindexNameONmytable(username(length))◆修改表結構ALTERmytableADDUNIQUE[indexName]ON(username(length))◆創(chuàng)建表的時候直接指定CREATETABLEmytable(IDINTNOTNULL,usernameVARCHAR(16)NOTNULL,UNIQUE[indexName](username(length)));（3）主鍵索引它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創(chuàng)建主鍵索引：CREATETABLEmytable(IDINTNOTNULL,usernameVARCHAR(16)NOTNULL,PRIMARYKEY(ID));當然也可以用ALTER命令。記?。阂粋€表只能有一個主鍵。（4）組合索引為了形象地對比單列索引和組合索引，為表添加多個字段：CREATETABLEmytable(IDINTNOTNULL,usernameVARCHAR(16)NOTNULL,cityVARCHAR(50)NOTNULL,ageINTNOTNULL);為了進一步榨取MySQL的效率，就要考慮建立組合索引。就是將name,city,age建到一個索引里：ALTERTABLEmytableADDINDEXname_city_age(name(10),city,age);建表時，usernname長度為16，這里用10。這是因為一般情況下名字的長度不會超過10，這樣會加速索引查詢速度，還會減少索引文件的大小，提高INSERT的更新速度。如果分別在usernname，city，age上建立單列索引，讓該表有3個單列索引，查詢時和上述的組合索引效率也會大不一樣，遠遠低于我們的組合索引。雖然此時有了三個索引，但MySQL只能用到其中的那個它認為似乎是最有效率的單列索引。建立這樣的組合索引，其實是相當于分別建立了下面三組組合索引：usernname,city,ageusernname,cityusernname為什么沒有city，age這樣的組合索引呢？這是因為MySQL組合索引“最左前綴”的結果。簡單的理解就是只從最左面的開始組合。并不是只要包含這三列的查詢都會用到該組合索引，下面的幾個SQL就會用到這個組合索引：SELECT*FROMmytableWHREEusername="admin"ANDcity="鄭州"SELECT*FROMmytableWHREEusername="admin"而下面幾個則不會用到：SELECT*FROMmytableWHREEage=20ANDcity="鄭州"SELECT*FROMmytableWHREEcity="鄭州"（5）建立索引的時機到這里我們已經學會了建立索引，那么我們需要在什么情況下建立索引呢？一般來說，在WHERE和JOIN中出現(xiàn)的列需要建立索引，但也不完全如此，因為MySQL只對<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE才會使用索引。例如：SELECTt.NameFROMmytabletLEFTJOINmytablemONt.Name=m.usernameWHEREm.age=20ANDm.city='鄭州'此時就需要對city和age建立索引，由于mytable表的userame也出現(xiàn)在了JOIN子句中，也有對它建立索引的必要。剛才提到只有某些時候的LIKE才需建立索引。因為在以通配符%和_開頭作查詢時，MySQL不會使用索引。例如下句會使用索引：SELECT*FROMmytableWHEREusernamelike'admin%'而下句就不會使用：SELECT*FROMmytableWHEREtNamelike'%admin'因此，在使用LIKE時應注意以上的區(qū)別。（6）索引的不足之處上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：◆雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件?！艚⑺饕龝加么疟P空間的索引文件。一般情況這個問題不太嚴重，但如果你在一個大表上創(chuàng)建了多種組合索引，索引文件的會膨脹很快。索引只是提高效率的一個因素，如果你的MySQL有大數(shù)據(jù)量的表，就需要花時間研究建立最優(yōu)秀的索引，或優(yōu)化查詢語句。（7）使用索引的注意事項使用索引時，有以下一些技巧和注意事項：◆索引不會包含有NULL值的列只要列中包含有NULL值都將不會被包含在索引中，復合索引中只要有一列含有NULL值，那么這一列對于此復合索引就是無效的。所以我們在數(shù)據(jù)庫設計時不要讓字段的默認值為NULL?！羰褂枚趟饕龑Υ羞M行索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字符內，多數(shù)值是惟一的，那么就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作?！羲饕信判騇ySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那么orderby中的列是不會使用索引的。因此數(shù)據(jù)庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創(chuàng)建復合索引?！鬺ike語句操作一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like“%aaa%”不會使用索引而like“aaa%”可以使用索引?！舨灰诹猩线M行運算select*fromuserswhereYEAR(adddate)<2007;將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成select*fromuserswhereadddate<‘2007-01-01’;◆不使用NOTIN和<>操作以上，就對其中MySQL索引類型進行了介紹。小結復習思考題參考書教材：《mysql實用教程》《mysql入門很簡單》清華大學出版社Mysql數(shù)據(jù)庫應用課程名稱視圖授課教師授課班級授課時數(shù)4授課方式理論課授課重點、難點創(chuàng)建視圖、修改視圖和刪除視圖授課內容、教具與時間分配一．視圖概述視圖是一個虛擬表，其內容由查詢定義。同真實的表一樣，視圖包含一系列帶有名稱的列和行數(shù)據(jù)。但是，視圖并不在數(shù)據(jù)庫中以存儲的數(shù)據(jù)值集形式存在。行和列數(shù)據(jù)來自由定義視圖的查詢所引用的表，并且在引用視圖時動態(tài)生成。二．創(chuàng)建視圖——CREATEVIEW1.語法CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW[db_name.]view_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]通過該語句可以創(chuàng)建視圖，若給定了[ORREPLACE]，則表示當已具有同名的視圖時，將覆蓋原視圖。select_statement是一個查詢語句，這個查詢語句可從表或其它的視圖中查詢。視圖屬于數(shù)據(jù)庫，因此需要指定數(shù)據(jù)庫的名稱，若未指定時，表示在當前的數(shù)據(jù)庫創(chuàng)建新視圖。表和數(shù)據(jù)庫共享數(shù)據(jù)庫中相同的名稱空間，因此，數(shù)據(jù)庫不能包含相同名稱的表和視圖，并且，視圖的列名也不能重復。3.注意事項創(chuàng)建視圖存在如下注意事項：（1）運行創(chuàng)建視圖的語句需要用戶具有創(chuàng)建視圖（CRATEVIEW）的權限，若加了[ORREPLACE]時，還需要用戶具有刪除視圖（DROPVIEW）的權限；（2）SELECT語句不能包含F(xiàn)ROM子句中的子查詢；（3）SELECT語句不能引用系統(tǒng)或用戶變量；（4）SELECT語句不能引用預處理語句參數(shù)；（5）在存儲子程序內，定義不能引用子程序參數(shù)或局部變量；（6）在定義中引用的表或視圖必須存在。但是，創(chuàng)建了視圖后，能夠舍棄定義引用的表或視圖。要想檢查視圖定義是否存在這類問題，可使用CHECKTABLE語句；（7）在定義中不能引用TEMPORARY表，不能創(chuàng)建TEMPORARY視圖；（8）在視圖定義中命名的表必須已存在；（9）不能將觸發(fā)程序與視圖關聯(lián)在一起；（10）在視圖定義中允許使用ORDERBY，但是，如果從特定視圖進行了選擇，而該視圖使用了具有自己ORDERBY的語句，它將被忽略。三．修改視圖——ALTERVIEW1.語法ALTER[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEWview_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]該語句用于更改已有視圖的定義。其語法與CREATEVIEW類似。四．刪除視圖——DROPVIEW1.語法DROPVIEW[IFEXISTS]view_name[,view_name]...[RESTRICT|CASCADE]該語句用戶刪除視圖，可一次刪除多個視圖。[IFEXISTS]選項確保語句正確運行。若沒有該子句，當指定的視圖不存在時，將發(fā)生錯誤。小結復習思考題參考書本文講述MySql5.x中視圖的使用，包括創(chuàng)建視圖、修改視圖和刪除視圖的相關知識，包括命令的語法、使用舉例以及注意事項。復習題目：1.視圖與表的區(qū)別和聯(lián)系？教材：《mysql實用教程》《mysql入門很簡單》清華大學出版社Mysql數(shù)據(jù)庫應用課程名稱觸發(fā)器使用授課教師授課班級授課時數(shù)2授課方式理論課授課重點、難點創(chuàng)建觸發(fā)器觸發(fā)器的刪除授課內容、教具與時間分配為什么要用觸發(fā)器我們在MySQL5.0中包含對觸發(fā)器的支持是由于以下原因:MySQL早期版本的用戶長期有需要觸發(fā)器的要求。我們曾經許諾支持所有ANSI標準的特性。您可以使用它來檢查或預防壞的數(shù)據(jù)進入數(shù)據(jù)庫。您可以改變或者取消INSERT,UPDATE以及DELETE語句。您可以在一個會話中監(jiān)視數(shù)據(jù)改變的動作。語法1.語法：命名規(guī)則CREATETRIGGER<觸發(fā)器名稱><--{BEFORE|AFTER}{INSERT|UPDATE|DELETE}ON<表名稱>FOREACHROW<觸發(fā)器SQL語句>觸發(fā)器必須有名字，最多64個字符，可能后面會附有分隔符.它和MySQL中其他對象的命名方式基本相象.■trigger_name：標識觸發(fā)器名稱，用戶自行指定；■trigger_time：標識觸發(fā)時機，用before和after替換；■trigger_event：標識觸發(fā)事件，用insert，update和delete替換；■tbl_name：標識建立觸發(fā)器的表名，即在哪張表上建立觸發(fā)器；■trigger_stmt：觸發(fā)器程序體。觸發(fā)器程序可以使用begin和end作為開始和結束，中間包含多條語句。②查看觸發(fā)器查看觸發(fā)器語法如下，如果知道觸發(fā)器所在數(shù)據(jù)庫，以及觸發(fā)器名稱等具體信息：SHOW?TRIGGERS?from?SF_User?like?"usermaps%";?/*?查看SF_User庫上名稱和usermaps%匹配的觸發(fā)器?*/?如果不了解觸發(fā)器的具體的信息，或者需要查看數(shù)據(jù)庫上所有觸發(fā)器，如下：SHOWTRIGGERS;//查看所有觸發(fā)器用上述方式查看觸發(fā)器可以看到數(shù)據(jù)庫的所有觸發(fā)器，不過如果一個庫上的觸發(fā)器太多，由于會刷屏，可能沒有辦法查看所有觸發(fā)器程序。③刪除觸發(fā)器刪除觸發(fā)器語法如下：DROP?TRIGGER?[schema_name.]trigger_name?小結復習思考題參考書本節(jié)講述了觸發(fā)器的使用：復習題目：1.如何創(chuàng)建觸發(fā)器？教材：《mysql實用教程》《mysql入門很簡單》清華大學出版社Mysql數(shù)據(jù)庫應用課程名稱SQL查詢語句授課教師授課班級授課時數(shù)8授課方式理論課授課重點、難點基本查詢語句使用集合函數(shù)查詢連接查詢授課內容、教具與時間分配SELECT被用來檢索從一個或多個表中精選的行。select_expression指出你想要檢索的列。SELECT也可以用來檢索不引用任何表的計算行1、select*fromtable1;2、selectfield1,field2fromtable1;3、selectcount(*)fromtable2;4、selecttable1.filed1,table2.filed2fromtable1,table2wheretable1.field1='valed1';5、select*fromtable1wherefield1='valed1'andfiels2='valed2'ordedbyfield1;6、select*fromtable1wherefiled1='valed1'orderbyfiled1desclinitn;7、select*fromtable2wherenamelike'%$lin%';(模糊)8、sub_selects(高級)select*fromtable1whereidIn(selectidfromtable2......);9、otable...(高級)(可用ooutfile...;insert...selkect...替代)10、SELECTMAX(field1)ASfield1FROMtable1列的最大值其中：表：table\字段：field集合函數(shù)查詢對一個表中的記錄進行數(shù)據(jù)統(tǒng)計。例如，如果你想統(tǒng)計存儲在表中的一次民意測驗的投票結果?；蛘吣阆胫酪粋€訪問者在你的站點上平均花費了多少時間。要對表中的任何類型的數(shù)據(jù)進行統(tǒng)計，都需要使用集合函數(shù)。你可以統(tǒng)計記錄數(shù)目，平均值，最小值，最大值，或者求和。當你使用一個集合函數(shù)時，它只返回一個數(shù)，該數(shù)值代表這幾個統(tǒng)計值之一。這些函數(shù)的最大特點就是經常和GROUPBY語句配合使用，需要注意的是集合函數(shù)不能和非分組的列混合使用。行列計數(shù)計算查詢語句返回的記錄行數(shù)直接計算函數(shù)COUNT(*)的值計算字段的平均值需要計算這些值的平均值。使用函數(shù)AVG()，你可以返回一個字段中所有值的平均值。SELECTAVG(vote)FROMopinion計算字段值的和SELECTSUM(purchase_amount)FROMorders計算字段值的極值求字段的極值，涉及兩個函數(shù)MAX()和MIN()。SELECTname,MIN(birth)FROMpet;mysql多表連接查詢innerjoin,fullouterjoin,leftjoin,rightjion內部連接innerjoin兩表都滿足的組合fullouter全連兩表相同的組合在一起，A表有，B表沒有的數(shù)據(jù)（顯示為null）,同樣B表有A表沒有的顯示為(null)A表leftjoinB表左連,以A表為基礎，A表的全部數(shù)據(jù)，B表有的組合。沒有的為nullA表rightjoinB表右連,以B表為基礎，B表的全部數(shù)據(jù)，A表的有的組合。沒有的為null一、外連接1.概念：包括左向外聯(lián)接、右向外聯(lián)接或完整外部聯(lián)接2.左連接：leftjoin或leftouterjoin(1)左向外聯(lián)接的結果集包括LEFTOUTER子句中指定的左表的所有行，而不僅僅是聯(lián)接列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關聯(lián)的結果集行中右表的所有選擇列表列均為空值(null)。(2)sql語句select*fromtable1leftjointable2ontable1.id=table2.id3.右連接：rightjoin或rightouterjoin(1)右向外聯(lián)接是左向外聯(lián)接的反向聯(lián)接。將返回右表的所有行。如果右表的某行在左表中沒有匹配行，則將為左表返回空值。(2)sql語句select*fromtable1rightjointable2ontable1.id=table2.id4.完整外部聯(lián)接:fulljoin或fullouterjoin(1)完整外部聯(lián)接返回左表和右表中的所有行。當某行在另一個表中沒有匹配行時，則另一個表的選擇列表列包含空值。如果表之間有匹配行，則整個結果集行包含基表的數(shù)據(jù)值。(2)sql語句select*fromtable1fulljointable2ontable1.id=table2.id二、內連接1.概念：內聯(lián)接是用比較運算符比較要聯(lián)接列的值的聯(lián)接2.內連接：join或innerjoin3.sql語句select*fromtable1jointable2ontable1.id=table2.id三、交叉連接(完全)1.概念：沒有WHERE子句的交叉聯(lián)接將產生聯(lián)接所涉及的表的笛卡爾積。第一個表的行數(shù)乘以第二個表的行數(shù)等于笛卡爾積結果集的大小。（table1和table2交叉連接產生3*3=9條記錄）2.交叉連接：crossjoin(不帶條件where...)3.sql語句select*fromtable1crossjointable2小結復習思考題參考書本節(jié)講述了部分查詢操作：復習題目：1.練習表的查詢？教材：《mysql實用教程》《mysql入門很簡單》清華大學出版社Mysql數(shù)據(jù)庫應用課程名稱Mysql插入更新刪除授課教師授課班級授課時數(shù)4授課方式理論課授課重點、難點Mysql的插入更新刪除方法授課內容、教具與時間分配1插入、更新和刪除記錄1．1插入記錄Insertintotable_name(field_name1,field_name2,…)values(value1,value2,…);Insertintovalues(value1,value2,…);//這種形式只能應用于值的順序與字段順序匹配的情況（可以通過調用describe命令來決定字段順序）。包含引用標志的值需要再引號’前面加上反斜線\ //轉義字符注意：into關鍵字是可選的也可以同時插入多個值，用逗號分開Eg：inserttable_namevalues(value1,value2,…),(value3,value4,…)；還可以不使用insert…values格式，而使用類似update語句，它使用set子句分別為每一列設置值。Insertintotable_namesetfield_name1=value1,field_name2=value2,…；使用default值Createtabletable_name(field_name1typedefaultdef_valuenotnull,…);Inserttable_namevalues(default,…);//default關鍵字只在4.0.3以及更高版本才支持使用AUTOINCREMENT字段自動產生下一個序號，該字段必須被設置為主鍵使用UNIQUE字段使用IGNORE關鍵字或者ONDUPLICATEKEYUPDATE子句跳過INSERT、中斷操作或者更新舊記錄為新值。INSERTIGNOREINTOTABLE_NAME(UNIQUE_FIELD,…)VALUES(REPEAT,…);如果重復記錄，則不會報告錯誤，也不會更新記錄，數(shù)據(jù)庫中的數(shù)據(jù)保持不變。ONDUPLICATEKEYUPDATE和REPLACE區(qū)別：前者只把已命名的字段更新為新值，后者刪除了舊記錄，然后用新值完全替換。Eg：在這種情況下，如果MySQL發(fā)現(xiàn)表中已經包含具有相同唯一鍵的記錄，則會自動更新舊的記錄為ONDUPLICATEKEYUPDATE從句中指定的新值。當有很多insert語句需要被順序執(zhí)行時，IGNORE關鍵字就使操作變得很方便。這可以保證不管拿一個INSERT包含了重復值，MySQL都會跳過而不放棄全部操作。1．2更新記錄UPDATETABLE_NAMESETFIELD_NAME=VALUE,SETFIELD_ANOTHER=VALUE_ANTOHER,…WHERE…如果省略了where子句，表中的每行都會收到update的影響而改變精細操作：當執(zhí)行INSERT或者UPDATE操作時，可以通過使用LOW_PRIORITY和DELAYED關鍵字使MySQL客戶執(zhí)行等待，知道成功完成操作或者排隊等候服務器進行處理。LOW_PRIORITY關鍵字阻止語句執(zhí)行知道沒有其他線程使用這個表，從而強制客戶等待，知道語句完全被執(zhí)行。在應用中可靠性是最終要的，添加這個關鍵字可以幫助提高SQL事務處理以及在它上面的業(yè)務邏輯的完整性。DELAYED關鍵字和LOW_PRIORITY關鍵字相反，把要執(zhí)行的語句放在一個對壘中（在server的存儲器內）就立即讓客戶退出。當處理繁忙的數(shù)據(jù)庫服務時，這一點在提高應用程序的性能方面尤其重要，意味它可以提高客戶相應時間（但是比較冒險―――如果服務器崩潰，在服務器隊列中的所有請求都會丟失）。這兩個關鍵字不適用與InnoDB表。1．3刪除記錄MySQL允許使用delete和truncate語句刪除數(shù)據(jù)。DELETE語句可以允許從表中刪除記錄，支持whereDELETEFROMTABLE_NAMEWHERECONDITIONAL;如果沒有where子句，則刪除表中所有數(shù)據(jù)。該操作不可逆轉（除非使用的是InnoDB表且關掉了AUTOCOMMIT？？？？）使用truncate命令也可一刪除表的所有記錄TRUNCATETABLETABLE_NAME；該命令比delete執(zhí)行速度要快，因為TRUNCATE刪除了表，然后重建它。注意：delete刪除的是記錄，并沒有嘗試去修改表。這也是為什么當向一個使用delete清空的表插入記錄時，MySQL會小結復習思考題參考書本節(jié)講述了有關表插入更新和刪除：復習題目：1.表的更新插入刪除方法？教材：《mysql實用教程》《mysql入門很簡單》清華大學出版社Mysql數(shù)據(jù)庫應用課程名稱Mysql運算符授課教師授課班級授課時數(shù)4授課方式理論課授課重點、難點算術運算符、比較運算符、邏輯運算符、位運算符、表達式授課內容、教具與時間分配運算符優(yōu)先級順序最高優(yōu)先級:=1||,or,xor2&&,and3between,case,when,then,else4=,<=>,>=,>,<=,<,<>,!=,is,like,regexp,in5|6&7<<,>>8-,+9*,/,div,%,mod10^11-(unaryminus),~(unarybitinversion)12!,not最低優(yōu)先級binary,collate算數(shù)運算符加mysql>select1+2;減mysql>select2-1;乘mysql>select2*3;除mysql>select5/3;商mysql>select5div2;模mysql>select5%2,mod(5,2);邏輯運算符非mysql>selectnot0,not1,notnull;mysql>select!0,!1,!null;與mysql>select(1and1),(0and1),(3and1),(1andnull);mysql>select(1&&1),(0&&1),(3&&1),(1&&null);或mysql>select(1or0),(0or0),(1ornull),(1or1),(nullornull);mysql>select(1||0),(0||0),(1||null),(1||1),(null||null);異或mysql>select(1xor1),(0xor0),(1xor0),(0xor1),(nullxor1);mysql>select(1^1),(0^0),(1^0),(0^1),(null^1);比較運算符等于mysql>select1=0,1=1,null=null;不等于mysql>select1<>0,1<>1,null<>null;安全等于mysql>select1<=>1,2<=>0,0<=>0,null<=>null;小于mysql>select'a'<'b','a'<'a','a'<'c',1<2;小于等于mysql>select'bdf'<='b','b'<='b',0<1;大于mysql>select'a'>'b','abc'>'a',1>0;大于等于mysql>select'a'>='b','abc'>='a',1>=0,1>=1;betweenmysql>select10between10and20,9between10and20;inmysql>select1in(1,2,3),'t'in('t','a','b','l','e'),0in(1,2);isnullmysql>select0isnull,nullisnull;isnotnullmysql>select0isnotnull,nullisnotnull;likemysql>select123456like'123%',123456like'%123%',123456like'%321%';regexpmysql>select'abcdef'regexp'ab','abcdefg'regexp'k';位運算符位與mysql>select2&3;mysql>select2&3&4;位或mysql>select2|3;位異或mysql>select2^3;位取反位右移mysql>select100>>3;位左移mysql>select100<<3;小結復習思考題參考書本節(jié)講述了mysql運算符：1.熟悉各種運算符及其使用方法教材：《mysql實用教程》《mysql入門很簡單》清華大學出版社Mysql數(shù)據(jù)庫應用課程名稱系統(tǒng)內置函數(shù)授課教師授課班級授課時數(shù)2授課方式理論課授課重點、難點Mysql函數(shù)使用授課內容、教具與時間分配算數(shù)運算子+,-,*,/除于0會等于NULL。比較運算子任何東西跟NULL比較會等于NULL。比較字符串通常不需要注意大小寫，如果要比較大小寫，用BINARY關鍵詞。邏輯運算子傳回的值有1(true),0(false,或NULL。任何不是0或不是NULL的值都是true。字符串函式concat(s1,s2,...)-連接字符串s1和s2...conv(n,original_base,new_base)-轉換數(shù)字n從本來的base到新的baselength(s)-找字符串的長度load_file(filename)-依照字符串傳回檔案的內容locate(needle,haystack,position)-傳回needle字符串的開始位置，在haystack字符串里，從position開始lower(s)andupper(s)-轉換s到小寫或大寫quote(s)-跳脫s字符串，讓它可以安全輸入數(shù)據(jù)庫replace(target,find,replace)-傳回target字符串，將find字符串覆蓋成replace字符串soundex(s)-傳回跟s類似的soundex字符串。soundex是字符串的發(fā)音substring(s,position,length)-傳回字符串里的字符串，s是本來的字符串，position是開始的位置，length是傳回的字數(shù)trim(s)-移除開頭跟字尾的空格符。也可以用rtrim()或ltrim()字符串比較的函式LIKE-利用通配字符來進行比較RLIKE-利用正規(guī)表示法來進行比較STRCMP-比較字符串，類似C里面的strcmp()MATCH-進行full-text比較數(shù)字函式abs(n)傳回n的正數(shù)ceiling(n)nroundeduptothenearestintegerfloor(n)nroundeddowntothenearestintegermod(n,m)和div這兩個函式會將n除于m。div傳回商數(shù)，mod()會傳回剩余數(shù)。power(n,m)ntothepowerofmrand(n)傳回0到1的隨意數(shù)。n可以不提供，如果提供會用來產生隨意數(shù)。相同的n會產生相同的隨意數(shù)。round(n[,d])nroundedtothenearestinteger.ifsupplyd,nwillberoundedtoddecimalplaces.sqrt(n)傳回n的平方mod()可以是mod(9,2)或9mod2或9%2。div只能用9div2。日期和時間函式adddate(date,INTERVALntype)和subdate(date,INTERVALntype)cast函式cast可以用來改變類型，用cast()和convert()函式。兩個函式相同，但是不同的語法構造。cast(expressionAStype)convert(expression,type)cast()是依照ANSI的規(guī)格，convert()是依照ODBC的規(guī)格。類型可以是BINARY,CHAR,DATE,DATETIME,SIGNED(INTEGER),和UNSIGNED(INTEGER)。通常MySQL會自動幫你改變類型。其它函式benchmark(count,expression)這個函式是用來測試查詢的速度，只會傳回0值。encrypt(s[,salt])將s加密用Unix的加密系統(tǒng)。salt字符串是兩個字符的字符串，可以不用。如果沒有crypt功能，像在Windows系統(tǒng)，這個函式會傳回NULL。found_rows()傳回字段的數(shù)量，如果沒有用LIMIT。只有在SELECT里使用SQL_CALC_FOUND_ROWS才能用這個函式。last_insert_id()傳回最后產生的AUTO_INCREMENT值。md5(s)傳回128bitMD5的加密字符串。password(s)計算s的密碼，不建議用這個函式來儲存密碼。用于GROUPBY的函式這些還是專門為了GROUPBY而寫的。avg(column)-傳回字段的平均值count(column)-傳回字段的數(shù)量min(column)-傳回字段的最小值max(column)-傳回字段的最大值std(column)-傳回字段的標準差sum(column)-傳回字段的總數(shù)小結復習思考題參考書本節(jié)講述了有關函數(shù)的使用：復習題目：1.熟悉各種函數(shù)及其使用。教材：《mysql實用教程》《mysql入門很簡單》清華大學出版社Mysql數(shù)據(jù)庫應用課程名稱過程式數(shù)據(jù)庫對象授課教師授課班級授課時數(shù)2授課方式理論課授課重點、難點創(chuàng)建存儲過程調用存儲過程查看存儲過程授課內容、教具與時間分配我們大家都知道MySQL存儲過程是從MySQL5.0開始逐漸增加新的功能。存儲過程在實際應用中也是優(yōu)點大于缺點。不過最主要的還是執(zhí)行效率和SQL代碼封裝。特別是SQL代碼封裝功能，如果沒有存儲過程。在外部程序訪問數(shù)據(jù)庫時（例如PHP），要組織很多SQL語句。特別是業(yè)務邏輯復雜的時候，一大堆的SQL和條件夾雜在PHP代碼中，讓人不寒而栗?，F(xiàn)在有了MySQL存儲過程，業(yè)務邏輯可以封裝存儲過程中，這樣不僅容易維護，而且執(zhí)行效率也高。一、MySQL創(chuàng)建存儲過程“pr_add”是個簡單的MySQL存儲過程，這個MySQL存儲過程有兩個int類型的輸入?yún)?shù)“a”、“b”，返回這兩個參數(shù)的和。dropprocedureifexistspr_add二、調用MySQL存儲過程callpr_add(10,20);執(zhí)行MySQL存儲過程，存儲過程參數(shù)為MySQL用戶變量。set@a=10;set@b=20;callpr_add(@a,@b);三、MySQL存儲過程特點創(chuàng)建MySQL存儲過程的簡單語法為：createprocedure存儲過程名字()([in|out|inout]參數(shù)datatype)beginMySQL語句;end;MySQL存儲過程參數(shù)如果不顯式指定“in”、“out”、“inout”，則默認為“in”。習慣上，對于是“in”的參數(shù)，我們都不會顯式指定。1.MySQL存儲過程名字后面的“()”是必須的，即使沒有一個參數(shù)，也需要“()”2.MySQL存儲過程參數(shù)，不能在參數(shù)名稱前加“@”，如：“@aint”。下面的創(chuàng)建存儲過程語法在MySQL中是錯誤的（在SQLServer中是正確的）。MySQL存儲過程中的變量，不需要在變量名字前加“@”，雖然MySQL客戶端用戶變量要加個“@”。3.MySQL存儲過程的參數(shù)不能指定默認值。4.MySQL存儲過程不需要在procedurebody前面加“as”。而SQLServer存儲過程必須加“as”關鍵字。5.如果MySQL存儲過程中包含多條MySQL語句，則需要beginend關鍵字。6.MySQL存儲過程中的每條語句的末尾，都要加上分號“;”7.MySQL存儲過程中的注釋。8.不能在MySQL存儲過程中使用“return”關鍵字。9.調用MySQL存儲過程時候，需要在過程名字后面加“()”，即使沒有一個參數(shù)，也需要“()”callpr_no_param();10.因為MySQL存儲過程參數(shù)沒有默認值，所以在調用MySQL存儲過程時候，不能省略參數(shù)。可以用null來替代。小結復習思考題參考書本節(jié)講述了有關表的大部分操作，現(xiàn)在將所述內容總結如下：MySQL的表的三種類型如何創(chuàng)建表、刪除表如何改變表的結構、名字復習題目：1.數(shù)據(jù)表操作方法？教材：《mysql實用教程》《mysql入門很簡單》清華大學出版社Mysql數(shù)據(jù)庫應用課程名稱備份與恢復授課教師授課班級授課時數(shù)4授課方式理論課授課重點、難點授課內容、教具與時間分配目前MySQL支持的免費備份工具有：mysqldump、mysqlhotcopy，還可以用SQL語法進行備份：BACKUPTABLE或者SELECTINTOOUTFILE，又或者備份二進制日志（binlog），還可以是直接拷貝數(shù)據(jù)文件和相關的配置文件。MyISAM表是保存成文件的形式，因此相對比較容易備份，上面提到的幾種方法都可以使用。Innodb所有的表都保存在同一個數(shù)據(jù)文件ibdata1中（也可能是多個文件，或者是獨立的表空間文件），相對來說比較不好備份，免費的方案可以是拷貝數(shù)據(jù)文件、備份binlog，或者用mysqldump。1、mysqldump1.1備份mysqldump是采用SQL級別的備份機制，它將數(shù)據(jù)表導成SQL腳本文件，在不同的MySQL版本之間升級時相對比較合適，這也是最常用的備份方法。1.2還原用mysqldump備份出來的文件是一個可以直接倒入的SQL腳本，有兩種方法可以將數(shù)據(jù)導入。直接用mysql客戶端例如： /usr/local/mysql/bin/mysql-uyejr-pyejrdb_name<db_name.sql用SOURCE語法其實這不是標準的SQL語法，而是mysql客戶端提供的功能，例如： SOURCE/tmp/db_name.sql;這里需要指定文件的絕對路徑，并且必須是mysqld運行用戶(例如nobody)有權限讀取的文件。2、mysqlhotcopy2.1備份mysqlhotcopy是一個PERL程序，最初由TimBunce編寫。它使用LOCKTABLES、FLUSHTABLES和cp或scp來快速備份數(shù)據(jù)庫。它是備份數(shù)據(jù)庫或單個表的最快的途徑，但它只能運行在數(shù)據(jù)庫文件（包括數(shù)據(jù)表定義文件、數(shù)據(jù)文件、索引文件）所在的機器上。mysqlhotcopy只能用于備份MyISAM，并且只能運行在類Unix和NetWare系統(tǒng)上。mysqlhotcopy支持一次性拷貝多個數(shù)據(jù)庫，同時還支持正則表達。2.2還原mysqlhotcopy備份出來的是整個數(shù)據(jù)庫目錄，使用時可以直接拷貝到mysqld指定的datadir(在這里是/usr/local/mysql/data/)目錄下即可，同時要注意權限的問題，如下例：root#cp-rfdb_name/usr/local/mysql/data/root#chown-Rnobody:nobody/usr/local/mysql/data/(將db_name目錄的屬主改成mysqld運行用戶)3、SQL語法備份3.1備份BACKUPTABLE語法其實和mysqlhotcopy的工作原理差不多，都是鎖表，然后拷貝數(shù)據(jù)文件。它能實現(xiàn)在線備份，但是效果不理想，因此不推薦使用。它只拷貝表結構文件和數(shù)據(jù)文件，不同時拷貝索引文件，因此恢復時比較慢。例子：BACKTABLEtbl_nameTO'/tmp/db_name/';注意，必須要有FILE權限才能執(zhí)行本SQL，并且目錄/tmp/db_name/必須能被mysqld用戶可寫，導出的文件不能覆蓋已經存在的文件，以避免安全問題。SELECTINTOOUTFILE則是把數(shù)據(jù)導出來成為普通的文本文件，可以自定義字段間隔的方式，方便處理這些數(shù)據(jù)。例子：SELECT*INTOOUTFILE'/tmp/db_name/tbl_name.txt'FROMtbl_name;注意，必須要有FILE權限才能執(zhí)行本SQL，并且文件/tmp/db_name/tbl_name.txt必須能被mysqld用戶可寫，導出的文件不能覆蓋已經存在的文件，以避免安全問題。3.2恢復用BACKUPTABLE方法備份出來的文件，可以運行RESTORETABLE語句來恢復數(shù)據(jù)表。例子：RESTORETABLEFROM'/tmp/db_na