MYSQL經(jīng)典基礎(chǔ)教程課件

SQL入門IT資訊交流網(wǎng)WWW.IT315.ORGSQL入門IT資訊交流網(wǎng)WWW.IT315.ORG1MySQL簡(jiǎn)介數(shù)據(jù)庫(kù)簡(jiǎn)介SQLServer、Oracle、MySQL、DB2、SyBaseMySQL數(shù)據(jù)庫(kù)的安裝和配置使用命令行窗口連接MYSQL數(shù)據(jù)庫(kù)mysql–u用戶名–p密碼MySQL簡(jiǎn)介數(shù)據(jù)庫(kù)簡(jiǎn)介2數(shù)據(jù)庫(kù)服務(wù)器、數(shù)據(jù)庫(kù)和表的關(guān)系MySQLDBDB表表表Client所謂安裝數(shù)據(jù)庫(kù)服務(wù)器，只是在機(jī)器上裝了一個(gè)數(shù)據(jù)庫(kù)管理程序，這個(gè)管理程序可以管理多個(gè)數(shù)據(jù)庫(kù)，一般開發(fā)人員會(huì)針對(duì)每一個(gè)應(yīng)用創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)。為保存應(yīng)用中實(shí)體的數(shù)據(jù)，一般會(huì)在數(shù)據(jù)庫(kù)創(chuàng)建多個(gè)表，以保存程序中實(shí)體的數(shù)據(jù)。數(shù)據(jù)庫(kù)服務(wù)器、數(shù)據(jù)庫(kù)和表的關(guān)系如圖所示：IT資訊交流網(wǎng)WWW.IT315.ORG數(shù)據(jù)庫(kù)服務(wù)器、數(shù)據(jù)庫(kù)和表的關(guān)系MySQLDBDB表表表Cli3數(shù)據(jù)在數(shù)據(jù)庫(kù)中的存儲(chǔ)方式id=1name=“l(fā)isi”age=23User對(duì)象idnameage1lisi232wang24User表id=2name=“wang”age=24行(row)列(column)表的一行稱之為一條記錄表中一條記錄對(duì)應(yīng)一個(gè)java對(duì)象的數(shù)據(jù)User對(duì)象IT資訊交流網(wǎng)WWW.IT315.ORG數(shù)據(jù)在數(shù)據(jù)庫(kù)中的存儲(chǔ)方式id=1User對(duì)象idnameag4創(chuàng)建數(shù)據(jù)庫(kù)CREATEDATABASE[IFNOTEXISTS]db_name

[create_specification[,create_specification]...]

create_specification:

[DEFAULT]CHARACTERSETcharset_name

|[DEFAULT]COLLATEcollation_name

CHARACTERSET：指定數(shù)據(jù)庫(kù)采用的字符集COLLATE：指定數(shù)據(jù)庫(kù)字符集的比較方式練習(xí)：創(chuàng)建一個(gè)名稱為mydb1的數(shù)據(jù)庫(kù)。創(chuàng)建一個(gè)使用utf-8字符集的mydb2數(shù)據(jù)庫(kù)。創(chuàng)建一個(gè)使用utf-8字符集，并帶校對(duì)規(guī)則的mydb3數(shù)據(jù)庫(kù)。創(chuàng)建數(shù)據(jù)庫(kù)CREATEDATABASE[IFNOT5查看、刪除數(shù)據(jù)庫(kù)顯示數(shù)據(jù)庫(kù)語(yǔ)句：SHOWDATABASES顯示數(shù)據(jù)庫(kù)創(chuàng)建語(yǔ)句：SHOWCREATEDATABASEdb_name

數(shù)據(jù)庫(kù)刪除語(yǔ)句：DROPDATABASE[IFEXISTS]db_name

練習(xí)：查看當(dāng)前數(shù)據(jù)庫(kù)服務(wù)器中的所有數(shù)據(jù)庫(kù)查看前面創(chuàng)建的mydb2數(shù)據(jù)庫(kù)的定義信息刪除前面創(chuàng)建的mydb1數(shù)據(jù)庫(kù)查看、刪除數(shù)據(jù)庫(kù)顯示數(shù)據(jù)庫(kù)語(yǔ)句：練習(xí)：6修改、備份、恢復(fù)數(shù)據(jù)庫(kù)ALTERDATABASE[IFNOTEXISTS]db_name

[alter_specification[,alter_specification]...]

alter_specification:

[DEFAULT]CHARACTERSETcharset_name

|[DEFAULT]COLLATEcollation_name備份數(shù)據(jù)庫(kù)表中的數(shù)據(jù) mysqldump-u用戶名-p數(shù)據(jù)庫(kù)名>文件名.sql恢復(fù)數(shù)據(jù)庫(kù) Source文件名.sql練習(xí)查看服務(wù)器中的數(shù)據(jù)庫(kù)，并把其中某一個(gè)庫(kù)的字符集修改為utf8;備份test庫(kù)中的數(shù)據(jù)，并恢復(fù)修改、備份、恢復(fù)數(shù)據(jù)庫(kù)ALTERDATABASE[I7創(chuàng)建表(基本語(yǔ)句)CREATETABLEtable_name(

field1datatype, field2datatype, field3datatype,)characterset字符集collate校對(duì)規(guī)則field：指定列名datatype：指定列類型注意：創(chuàng)建表時(shí)，要根據(jù)需保存的數(shù)據(jù)創(chuàng)建相應(yīng)的列，并根據(jù)數(shù)據(jù)的類型定義相應(yīng)的列類型。例：user對(duì)象 idint namestring passwordstring birthdaydateIdNamePasswordbirthday注意：創(chuàng)建表前，要先使用usedb語(yǔ)句使用庫(kù)。創(chuàng)建表(基本語(yǔ)句)CREATETABLEtable_na8MySQL常用數(shù)據(jù)類型VARCHAR、BLOB和TEXT類是變長(zhǎng)類型。每個(gè)類型的存儲(chǔ)需求取決于列值的實(shí)際長(zhǎng)度。

分類數(shù)據(jù)類型說明數(shù)值類型BIT(M)TINYINT[UNSIGNED][ZEROFILL]BOOL，BOOLEANSMALLINT[UNSIGNED][ZEROFILL]INT[UNSIGNED][ZEROFILL]BIGINT[UNSIGNED][ZEROFILL]FLOAT[(M,D)][UNSIGNED][ZEROFILL]DOUBLE[(M,D)][UNSIGNED][ZEROFILL]位類型。M指定位數(shù)，默認(rèn)值1，范圍1-64帶符號(hào)的范圍是-128到127。無符號(hào)0到255。使用0或1表示真或假2的16次方2的32次方2的64次方M指定顯示長(zhǎng)度，d指定小數(shù)位數(shù)表示比float精度更大的小數(shù)文本、二進(jìn)制類型CHAR(size)char(20)VARCHAR(size)varchar(20)BLOBLONGBLOBTEXT(clob)LONGTEXT(longclob)固定長(zhǎng)度字符串可變長(zhǎng)度字符串二進(jìn)制數(shù)據(jù)大文本時(shí)間日期DATE/DATETIME/TimeStamp日期類型(YYYY-MM-DD)(YYYY-MM-DDHH:MM:SS)，TimeStamp表示時(shí)間戳，它可用于自動(dòng)記錄insert、update操作的時(shí)間MySQL常用數(shù)據(jù)類型VARCHAR、BLOB和TEXT類是9創(chuàng)建表練習(xí)創(chuàng)建一個(gè)員工表字段屬性Id整形name字符型sex字符型或bit型brithday日期型Entry_date日期型job字符型Salary小數(shù)型resume大文本型創(chuàng)建表練習(xí)創(chuàng)建一個(gè)員工表字段屬性Id整形name字符型sex10修改表使用ALTERTABLE語(yǔ)句追加,修改,或刪除列的語(yǔ)法.ALTERTABLEtableADD

(columndatatype[DEFAULTexpr] [,columndatatype]...);ALTERTABLEtableMODIFY

(columndatatype[DEFAULTexpr] [,columndatatype]...);ALTERTABLEtableDROP

(column);修改表的名稱：Renametable表名to新表名修改表的字符集：altertablestudentcharactersetutf8;修改表使用ALTERTABLE語(yǔ)句追加,修改,或刪11修改表練習(xí)在上面員工表的基本上增加一個(gè)image列。修改job列，使其長(zhǎng)度為60。刪除sex列。表名改為user。修改表的字符集為utf-8列名name修改為usernamealtertableuserchangecolumnnameusernamevarchar(20);IT資訊交流網(wǎng)WWW.IT315.ORG修改表練習(xí)IT資訊交流網(wǎng)WWW.IT315.ORG12IT資訊交流網(wǎng)WWW.IT315.ORGIT資訊交流網(wǎng)WWW.IT315.ORG13數(shù)據(jù)庫(kù)CRUD語(yǔ)句Insert語(yǔ)句(增加數(shù)據(jù))Update語(yǔ)句(更新數(shù)據(jù))Delete語(yǔ)句(刪除數(shù)據(jù))Select語(yǔ)句(查找數(shù)據(jù))IT資訊交流網(wǎng)WWW.IT315.ORG數(shù)據(jù)庫(kù)CRUD語(yǔ)句Insert語(yǔ)句(增加數(shù)據(jù))IT資14Insert語(yǔ)句INSERTINTO table[(column[,column...])]VALUES (value[,value...]);使用INSERT語(yǔ)句向表中插入數(shù)據(jù)。插入的數(shù)據(jù)應(yīng)與字段的數(shù)據(jù)類型相同。數(shù)據(jù)的大小應(yīng)在列的規(guī)定范圍內(nèi)，例如：不能將一個(gè)長(zhǎng)度為80的字符串加入到長(zhǎng)度為40的列中。在values中列出的數(shù)據(jù)位置必須與被加入的列的排列位置相對(duì)應(yīng)。字符和日期型數(shù)據(jù)應(yīng)包含在單引號(hào)中。插入空值，不指定或insertintotablevalue(null)IT資訊交流網(wǎng)WWW.IT315.ORGInsert語(yǔ)句INSERTINTO table[(co15Insert語(yǔ)句練習(xí)練習(xí)：使用insert語(yǔ)句向表中插入三個(gè)員工的信息。注意：字符和日期要包含在單引號(hào)中。showvariableslike'character%';setcharacter_set_results=gbk;IT資訊交流網(wǎng)WWW.IT315.ORGInsert語(yǔ)句練習(xí)練習(xí)：使用insert語(yǔ)句向表中插入三個(gè)16Update語(yǔ)句UPDATE tbl_name

SETcol_name1=expr1[,col_name2=expr2...]

[WHEREwhere_definition]

使用update語(yǔ)句修改表中數(shù)據(jù)。UPDATE語(yǔ)法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要給予哪些值。WHERE子句指定應(yīng)更新哪些行。如沒有WHERE子句，則更新所有的行。IT資訊交流網(wǎng)WWW.IT315.ORGUpdate語(yǔ)句UPDATE tbl_name

使用17Update語(yǔ)句練習(xí)練習(xí)：在上面創(chuàng)建的employee表中修改表中的紀(jì)錄。要求將所有員工薪水修改為5000元。將姓名為’zs’的員工薪水修改為3000元。將姓名為’aaa’的員工薪水修改為4000元,job改為ccc。將wu的薪水在原有基礎(chǔ)上增加1000元。 IT資訊交流網(wǎng)WWW.IT315.ORGUpdate語(yǔ)句練習(xí)練習(xí)：在上面創(chuàng)建的employee表中修18Delete語(yǔ)句deletefromtbl_name

[WHEREwhere_definition]

使用delete語(yǔ)句刪除表中數(shù)據(jù)。如果不使用where子句，將刪除表中所有數(shù)據(jù)。Delete語(yǔ)句不能刪除某一列的值（可使用update）使用delete語(yǔ)句僅刪除記錄，不刪除表本身。如要?jiǎng)h除表，使用droptable語(yǔ)句。同insert和update一樣，從一個(gè)表中刪除記錄將引起其它表的參照完整性問題，在修改數(shù)據(jù)庫(kù)數(shù)據(jù)時(shí)，頭腦中應(yīng)該始終不要忘記這個(gè)潛在的問題。刪除表中數(shù)據(jù)也可使用TRUNCATETABLE語(yǔ)句，它和delete有所不同，參看mysql文檔。IT資訊交流網(wǎng)WWW.IT315.ORGDelete語(yǔ)句deletefromtbl_name

19Delete語(yǔ)句練習(xí)刪除表中名稱為’zs’的記錄。刪除表中所有記錄。使用truncate刪除表中記錄。IT資訊交流網(wǎng)WWW.IT315.ORGDelete語(yǔ)句練習(xí)刪除表中名稱為’zs’的記錄。IT資訊交20Select語(yǔ)句(1)SELECT[DISTINCT]*|{column1,column2.column3..} FROM table;Select指定查詢哪些列的數(shù)據(jù)。column指定列名。*號(hào)代表查詢所有列。From指定查詢哪張表。DISTINCT可選，指顯示結(jié)果時(shí)，是否剔除重復(fù)數(shù)據(jù)基本select語(yǔ)句IT資訊交流網(wǎng)WWW.IT315.ORGSelect語(yǔ)句(1)SELECT[DISTINCT]*21Select語(yǔ)句(1)練習(xí)：查詢表中所有學(xué)生的信息。查詢表中所有學(xué)生的姓名和對(duì)應(yīng)的英語(yǔ)成績(jī)。過濾表中重復(fù)數(shù)據(jù)。IT資訊交流網(wǎng)WWW.IT315.ORGSelect語(yǔ)句(1)練習(xí)：IT資訊交流網(wǎng)WWW.IT3122Select語(yǔ)句(２)SELECT*|{column1｜expression,column2｜expression，..} FROM table;在select語(yǔ)句中可使用表達(dá)式對(duì)查詢的列進(jìn)行運(yùn)算在select語(yǔ)句中可使用as語(yǔ)句SELECTcolumnas別名from表名;IT資訊交流網(wǎng)WWW.IT315.ORGSelect語(yǔ)句(２)SELECT*|{column1｜e23Select語(yǔ)句(２)練習(xí)在所有學(xué)生分?jǐn)?shù)上加10分特長(zhǎng)分。統(tǒng)計(jì)每個(gè)學(xué)生的總分。使用別名表示學(xué)生分?jǐn)?shù)。IT資訊交流網(wǎng)WWW.IT315.ORGSelect語(yǔ)句(２)練習(xí)IT資訊交流網(wǎng)WWW.IT31524Select語(yǔ)句(3)使用where子句，進(jìn)行過濾查詢。練習(xí)：查詢姓名為wu的學(xué)生成績(jī)查詢英語(yǔ)成績(jī)大于90分的同學(xué)查詢總分大于200分的所有同學(xué)IT資訊交流網(wǎng)WWW.IT315.ORGSelect語(yǔ)句(3)使用where子句，進(jìn)行過濾查詢。練習(xí)25Select語(yǔ)句(４)在where子句中經(jīng)常使用的運(yùn)算符比較運(yùn)算符><<=>==<>大于、小于、大于(小于)等于、不等于BETWEEN...AND...顯示在某一區(qū)間的值IN(set)顯示在in列表中的值，例：in(100,200)LIKE‘張pattern’模糊查詢ISNULL判斷是否為空邏輯運(yùn)算符and多個(gè)條件同時(shí)成立or多個(gè)條件任一成立not不成立，例：wherenot(salary>100);Like語(yǔ)句中，%代表零個(gè)或多個(gè)任意字符，_代表一個(gè)字符，例first_namelike‘_a%’;IT資訊交流網(wǎng)WWW.IT315.ORGSelect語(yǔ)句(４)在where子句中經(jīng)常使用的運(yùn)算符>26Select語(yǔ)句(4)查詢英語(yǔ)分?jǐn)?shù)在80－90之間的同學(xué)。查詢數(shù)學(xué)分?jǐn)?shù)為89,90,91的同學(xué)。查詢所有姓李的學(xué)生成績(jī)。查詢數(shù)學(xué)分>80，語(yǔ)文分>80的同學(xué)。IT資訊交流網(wǎng)WWW.IT315.ORGSelect語(yǔ)句(4)查詢英語(yǔ)分?jǐn)?shù)在80－90之間的同學(xué)。27Select語(yǔ)句(５)SELECTcolumn1,column2.column3.. FROM table; orderbycolumnasc|descOrderby指定排序的列，排序的列即可是表中的列名，也可以是select語(yǔ)句后指定的列名。Asc升序、Desc降序ORDERBY子句應(yīng)位于SELECT語(yǔ)句的結(jié)尾。練習(xí)：對(duì)數(shù)學(xué)成績(jī)排序后輸出。對(duì)總分排序后輸出，然后再按從高到低的順序輸出對(duì)姓李的學(xué)生成績(jī)排序輸出使用orderby子句排序查詢結(jié)果。IT資訊交流網(wǎng)WWW.IT315.ORGSelect語(yǔ)句(５)SELECTcolumn1,col28合計(jì)函數(shù)－countSelectcount(*)|count(列名)fromtablename [WHEREwhere_definition]

練習(xí)：統(tǒng)計(jì)一個(gè)班級(jí)共有多少學(xué)生？統(tǒng)計(jì)數(shù)學(xué)成績(jī)大于90的學(xué)生有多少個(gè)？統(tǒng)計(jì)總分大于250的人數(shù)有多少？Count(列名)返回某一列，行的總數(shù)IT資訊交流網(wǎng)WWW.IT315.ORG合計(jì)函數(shù)－countSelectcount(*)|coun29合計(jì)函數(shù)－SUMSelectsum(列名)｛,sum(列名)…｝fromtablename [WHEREwhere_definition]

練習(xí)：統(tǒng)計(jì)一個(gè)班級(jí)數(shù)學(xué)總成績(jī)？統(tǒng)計(jì)一個(gè)班級(jí)語(yǔ)文、英語(yǔ)、數(shù)學(xué)各科的總成績(jī)統(tǒng)計(jì)一個(gè)班級(jí)語(yǔ)文、英語(yǔ)、數(shù)學(xué)的成績(jī)總和統(tǒng)計(jì)一個(gè)班級(jí)語(yǔ)文成績(jī)平均分注意：sum僅對(duì)數(shù)值起作用，否則會(huì)報(bào)錯(cuò)。注意：對(duì)多列求和，“，”號(hào)不能少。Sum函數(shù)返回滿足where條件的行的和IT資訊交流網(wǎng)WWW.IT315.ORG合計(jì)函數(shù)－SUMSelectsum(列名)｛,sum(列名30合計(jì)函數(shù)－AVGSelectsum(列名)｛,sum(列名)…｝fromtablename [WHEREwhere_definition]

練習(xí)：求一個(gè)班級(jí)數(shù)學(xué)平均分？求一個(gè)班級(jí)總分平均分AVG函數(shù)返回滿足where條件的一列的平均值IT資訊交流網(wǎng)WWW.IT315.ORG合計(jì)函數(shù)－AVGSelectsum(列名)｛,sum(列名31合計(jì)函數(shù)－MAX/MINSelectmax(列名)fromtablename [WHEREwhere_definition]

練習(xí)：求班級(jí)最高分和最低分（數(shù)值范圍在統(tǒng)計(jì)中特別有用）Max/min函數(shù)返回滿足where條件的一列的最大/最小值IT資訊交流網(wǎng)WWW.IT315.ORG合計(jì)函數(shù)－MAX/MINSelectmax(列名)fro32Select語(yǔ)句(6)SELECTcolumn1,column2.column3..FROM table; groupbycolumn練習(xí)：對(duì)訂單表中商品歸類后，顯示每一類商品的總價(jià)使用groupby子句對(duì)列進(jìn)行分組SELECTcolumn1,column2.column3.. FROM table; groupbycolumnhaving...使用having子句過濾練習(xí)：查詢購(gòu)買了幾類商品，并且每類總價(jià)大于100的商品Having和where均可實(shí)現(xiàn)過濾，但在having可以使用合計(jì)函數(shù),having通常跟在groupby后，它作用于組。IT資訊交流網(wǎng)WWW.IT315.ORGSelect語(yǔ)句(6)SELECTcolumn1,col33時(shí)間日期相關(guān)函數(shù)ADDTIME(date2,time_interval)將time_interval加到date2CURRENT_DATE(

)當(dāng)前日期CURRENT_TIME(

)當(dāng)前時(shí)間CURRENT_TIMESTAMP()當(dāng)前時(shí)間戳DATE(datetime)返回datetime的日期部分DATE_ADD(date2,INTERVALd_valued_type)在date2中加上日期或時(shí)間DATE_SUB(date2,INTERVALd_valued_type)在date2上減去一個(gè)時(shí)間DATEDIFF(date1,date2)兩個(gè)日期差NOW(

)當(dāng)前時(shí)間YEAR|Month|DATE(datetime)年月日示例：selectaddtime(‘02:30:30’,‘01:01:01’);注意：字符串、時(shí)間日期的引號(hào)問題 selectdate_add(entry_date,INTERVAL2year)fromstudent;／／增加兩年selectaddtime(time,‘11-110:09:09’)fromstudent;／／時(shí)間戳上增加，注意年后沒有-IT資訊交流網(wǎng)WWW.IT315.ORG時(shí)間日期相關(guān)函數(shù)ADDTIME(date2,time_i34字符串相關(guān)函數(shù)CHARSET(str)返回字串字符集CONCAT(string2

[,...])連接字串INSTR(string,substring)返回substring在string中出現(xiàn)的位置,沒有返回0UCASE(string2)轉(zhuǎn)換成大寫LCASE(string2)轉(zhuǎn)換成小寫LEFT(string2,length)從string2中的左邊起取length個(gè)字符LENGTH(string)string長(zhǎng)度REPLACE(str,search_str,replace_str)在str中用replace_str替換search_strSTRCMP(string1,string2)逐字符比較兩字串大小,SUBSTRING(str,position

[,length])從str的position開始,取length個(gè)字符LTRIM(string2)RTRIM(string2)trim去除前端空格或后端空格IT資訊交流網(wǎng)WWW.IT315.ORG字符串相關(guān)函數(shù)CHARSET(str)返回