MySQL數(shù)據(jù)庫(kù)應(yīng)用教程 課件 第5、6章 表記錄的查詢(xún)、索引

第5章表記錄的查詢(xún)《MySQL數(shù)據(jù)庫(kù)應(yīng)用教程》劉瑞新主編配套資源目錄第5章表記錄的查詢(xún)5.1單表記錄查詢(xún)5.2聚合函數(shù)查詢(xún)5.3多表連接查詢(xún)5.4子查詢(xún)5.1.1單表查詢(xún)語(yǔ)句SELECT[ALL|DISTINCT]selection_list1[,selection_list2…]FROMtable_source;5.1單表記錄查詢(xún)【例5-1】在數(shù)據(jù)庫(kù)studentinfo中查詢(xún)學(xué)生表student中的所有記錄。USEstudentinfo;SELECT*FROMstudent;5.1單表記錄查詢(xún)2.查詢(xún)指定的列【例5-2】在數(shù)據(jù)庫(kù)studentinfo中，查詢(xún)學(xué)生表student中的StudentName、Sex、StudentID和ClassID列。SELECTStudentName,Sex,StudentID,ClassIDFROMstudent;5.1單表記錄查詢(xún)5.1單表記錄查詢(xún)3.查詢(xún)計(jì)算的值【例5-3】在數(shù)據(jù)庫(kù)studentinfo中，查詢(xún)學(xué)生表student中的全體學(xué)生，顯示StudentName、Sex列，以及“年齡:”字符串和年齡。SELECTStudentName,Sex,'年齡:',YEAR(NOW())-YEAR(Birthday)FROMstudent;5.1單表記錄查詢(xún)【例5-4】計(jì)算表達(dá)式的值。SELECT101+3*50/7,"abc"="ABC",2>=3;5.1單表記錄查詢(xún)3.為列取別名【例5-5】在數(shù)據(jù)庫(kù)studentinfo中，查詢(xún)student表中的全體學(xué)生的姓名、性別和年齡，要求對(duì)應(yīng)的列名顯示為中文名稱(chēng)。SELECTStudentNameAS'姓名',Sex性別,YEAR(NOW())-YEAR(Birthday)AS年齡

FROMstudent;5.1單表記錄查詢(xún)4.不顯示重復(fù)記錄【例5-6】在數(shù)據(jù)庫(kù)studentinfo中，查詢(xún)student表中的性別。SELECTSexFROMstudent;5.1單表記錄查詢(xún)SELECTDISTINCTSexFROMstudent;5.1單表記錄查詢(xún)SELECTStudentName,SexFROMstudent;5.1單表記錄查詢(xún)5.1.2使用WHERE子句過(guò)濾結(jié)果集SELECT[ALL|DISTINCT]selection_list1[,selection_list2…]FROMtable_source[WHEREsearch_condition];5.1單表記錄查詢(xún)1.使用關(guān)系表達(dá)式和邏輯表達(dá)式的條件查詢(xún)關(guān)系運(yùn)算符有<，<=，=，>，>=，<>，!=，!<，!>，<=>?！?lt;>”表示不等于，等價(jià)“!=”；“!>”表示不大于，等價(jià)“<=”；“!<”表示不小于，等價(jià)“>=”。邏輯運(yùn)算符有ANOT或!，AND或&&，OR或||，XOR。AND和OR連接多個(gè)條件。5.1單表記錄查詢(xún)【例5-7】查詢(xún)selectcourse表中成績(jī)?cè)?0到75之間或者成績(jī)?yōu)?00的學(xué)號(hào)、課程號(hào)。SQL語(yǔ)句和查詢(xún)結(jié)果如下：SELECTStudentID,CourseID,ScoreFROMselectcourseWHEREScore>=60ANDScore<=75ORScore=100;5.1單表記錄查詢(xún)2.使用BETWEEN…AND關(guān)鍵字的范圍查詢(xún)expression[NOT]BETWEENexpressionlANDexpression2“xBETWEEN200AND500”相當(dāng)于表達(dá)式“x>=200ANDx<=500”。5.1單表記錄查詢(xún)【例5-8】對(duì)例5-7改用BETWEEN…AND關(guān)鍵字實(shí)現(xiàn)查詢(xún)。SELECTStudentID,CourseID,ScoreFROMselectcourseWHEREScoreBETWEEN60AND75ORScore=100;5.1單表記錄查詢(xún)【例5-9】查詢(xún)student表中出生日期不在2003-02-01到2003-05-31之間的學(xué)生。SELECTStudentName,Sex,BirthdayFROMstudentWHEREBirthdayNOTBETWEEN'2003-02-01'AND'2003-05-31';5.1單表記錄查詢(xún)3.使用IN關(guān)鍵字的集合查詢(xún)expression[NOT]IN(value1,value2,…)【例5-10】查詢(xún)student表中地址不在北京、上海、廣東、浙江的記錄。SELECT*FROMstudentWHEREAddressNOTIN('北京','上海','廣東','浙江');5.1單表記錄查詢(xún)4.使用ISNULL關(guān)鍵字查詢(xún)空值expressionIS[NOT]NULL【例5-11】在course表中，查詢(xún)填寫(xiě)課程號(hào)的記錄。SELECT*FROMcourseWHEREPreCourseIDISNOTNULL;5.1單表記錄查詢(xún)5.使用LIKE關(guān)鍵字的字符匹配查詢(xún)expression[NOT]LIKE'模式字符串'[ESCAPE'換碼字符']【例5-12】在course表中，查詢(xún)課程名CourseName中含有“基礎(chǔ)”的課程。SELECT*FROMcourseWHERECourseNameLIKE'%基礎(chǔ)%';5.1單表記錄查詢(xún)6.使用正則表達(dá)式的查詢(xún)expression[NOT]REGEXP'正則表達(dá)式'5.1單表記錄查詢(xún)SELECT'teacher'REGEXP'er$';5.1單表記錄查詢(xún)（1）匹配指定的字符串【例5-13】在課程表course中，查詢(xún)課程名稱(chēng)中含有“學(xué)”“基礎(chǔ)”“法”的課程。SELECT*FROMcourseWHERECourseNameREGEXP'學(xué)|基礎(chǔ)|法';5.1單表記錄查詢(xún)（2）查詢(xún)以特定字符或字符串開(kāi)頭的記錄【例5-14】在student表中，查詢(xún)廣東、廣西、湖南或湖北的學(xué)生的信息。SELECT*FROMstudentWHEREAddressREGEXP'^廣|湖';5.1單表記錄查詢(xún)（3）用“.”替代字符串中的任意一個(gè)字符【例5-15】在student表中，查詢(xún)姓名中以“李”開(kāi)頭，以“欣”結(jié)尾，中間包含一個(gè)字的學(xué)生信息。SELECT*FROMstudentWHEREStudentNameREGEXP'^李.欣$';5.1單表記錄查詢(xún)5.1.3對(duì)查詢(xún)結(jié)果集的處理1.使用ORDERBY子句對(duì)查詢(xún)結(jié)果排序ORDERBYexpression1[ASC|DESC][,expression2[ASC|DESC],…]【例5-16】在student表中，按出生日期的先后順序顯示。SELECT*FROMstudentORDERBYBirthdayASC;5.1單表記錄查詢(xún)【例5-17】在selectcourse表中查詢(xún)成績(jī)大于或等于85分的學(xué)生的學(xué)號(hào)、課程號(hào)和成績(jī)，并先按成績(jī)的降序，再按學(xué)號(hào)的升序排列，列名顯示為中文。SELECTStudentIDAS學(xué)號(hào),CourseID課程號(hào),Score成績(jī)FROMselectcourseWHEREScore>=85ORDERBYScoreDESC,StudentIDASC;5.1單表記錄查詢(xún)2.限制查詢(xún)結(jié)果的數(shù)量LIMITlines[OFFSEToffset]【例5-18】在selectcourse表中，查詢(xún)課程號(hào)CourseID為6開(kāi)頭的成績(jī)，按成績(jī)從高到低降序排序，輸出第2至第4條記錄的學(xué)生，該子句改為L(zhǎng)IMIT4OFFSET2。SELECTStudentIDAS學(xué)號(hào),CourseIDAS課程號(hào),ScoreAS成績(jī)

FROMselectcourseWHERESUBSTR(CourseID,1,1)='6'ORDERBYScoreDESCLIMIT3OFFSET2;5.1單表記錄查詢(xún)5.2.1聚合函數(shù)5.2聚合函數(shù)查詢(xún)1.COUND()函數(shù)COUNT(*)：返回?cái)?shù)據(jù)表中記錄的行數(shù)，包含NULL值的行。COUNT([DISTINCT|ALL]<列名>)返回指定列中的所有非空值的記錄行數(shù)。5.2聚合函數(shù)查詢(xún)【例5-19】在course表中，查詢(xún)PreCourseID列的行數(shù)，分別查詢(xún)包含NULL行和不包含NULL的行的行數(shù)。SELECTCOUNT(*)AS'行數(shù)',COUNT(PreCourseID)FROMcourse;5.2聚合函數(shù)查詢(xún)【例5-20】查詢(xún)student表中2022級(jí)計(jì)算機(jī)學(xué)院的學(xué)生的總數(shù)。SELECTCOUNT(*)FROMstudentWHERESUBSTRING(StudentID,1,6)='202263';5.2聚合函數(shù)查詢(xún)【例5-21】查詢(xún)selectcourse表中選修了課程的學(xué)生總?cè)藬?shù)。一名學(xué)生可以選修多門(mén)課程，在selectcourse表中對(duì)應(yīng)多條記錄，為避免重復(fù)計(jì)算學(xué)生人數(shù)，在COUNT()函數(shù)中使用DISTINCT關(guān)鍵字。SELECTCOUNT(DISTINCTStudentID)FROMselectcourse;5.2聚合函數(shù)查詢(xún)2.SUM()函數(shù)和AVG()函數(shù)SUM([DISTINCT|ALL]<列名>)返回指定列中的所有非空值的和。AVG([DISTINCT|ALL]<列名>)返回指定列中的所有非空值的平均值。5.2聚合函數(shù)查詢(xún)【例5-22】在selectcourse表中，查詢(xún)學(xué)生的總成績(jī)和平均成績(jī)。SELECTSUM(Score)總分,AVG(Score)平均分,SUM(Score)/COUNT(*)平均分?jǐn)?shù)

FROMselectcourse;5.2聚合函數(shù)查詢(xún)3.MAX()函數(shù)和MIN()函數(shù)MAX([DISTINCT|ALL]<列名>) MIN([DISTINCT|ALL]<列名>)【例5-23】查詢(xún)selectcourse表中，求課程編號(hào)CourseID為630575的學(xué)生最高分、最低分、最高分與最高分之差、平均分。SELECTMAX(Score)最高分,MIN(Score)最低分,MAX(Score)-MIN(Score)分?jǐn)?shù)差,AVG(Score)平均分FROMselectcourseWHERECourseID='630575';5.2聚合函數(shù)查詢(xún)5.2.2分組聚合查詢(xún)[GROUPBY分組表達(dá)式1,分組表達(dá)式2,…][HAVING條件表達(dá)式][WITHROLLUP]（1）按單列分組【例5-24】在學(xué)生表student中，按照Sex單列分組，查詢(xún)學(xué)生信息。SELECTSex性別,COUNT(*)人數(shù)FROMstudentGROUPBYSex;5.2聚合函數(shù)查詢(xún)【例5-25】在成績(jī)表selectcourse中，統(tǒng)計(jì)每位學(xué)生的選課門(mén)數(shù)、最高分、最低分和平均分。SELECTstudentID,count(*)選課門(mén)數(shù),MAX(Score)最高分,MIN(Score)最低分,avg(Score)平均分

FROMselectcourseGROUPBYStudentID;5.2聚合函數(shù)查詢(xún)【例5-26】在selectcourse表中，先按照CourseID分組，再按照StudentID分組。SELECTCourseID,StudentID,COUNT(CourseID),AVG(Score)FROMselectcourseGROUPBYCourseID,StudentID;5.2聚合函數(shù)查詢(xún)2.HAVING子句【例5-27】在selectcourse表中，查詢(xún)平均分80以上的每位學(xué)生的選課門(mén)數(shù)、最高分、最低分和平均分。SELECTStudentID學(xué)號(hào),COUNT(*)選課門(mén)數(shù),MAX(Score)最高分,MIN(Score)最低分,AVG(Score)平均分

FROMselectcourseWHEREScore>=60GROUPBYStudentIDHAVINGAVG(Score)>=80;5.2聚合函數(shù)查詢(xún)3.GROUPBY子句與WITHROLLUP【例5-28】在selectcourse表中，查詢(xún)每一門(mén)課的平均分?jǐn)?shù)和所有課的平均分?jǐn)?shù)。SELECTCourseID課程號(hào),AVG(Score)每門(mén)課程的平均分FROMselectcourseGROUPBYCourseIDWITHROLLUP;5.2聚合函數(shù)查詢(xún)交叉連接內(nèi)連接外連接連接子句的語(yǔ)法格式如下：FROMtb_name1連接類(lèi)型tb_name2[連接類(lèi)型tb_name3[…]][ON連接條件]5.3多表連接查詢(xún)5.3.1交叉連接SELECT*FROMtb_name1CROSSJOINtb_name2;或SELECT*FROMtb_name1,tb_name2;【例5-29】將班級(jí)表class和院系表department進(jìn)行交叉連接。SELECT*FROMclassCROSSJOINdepartment;5.3多表連接查詢(xún)5.3多表連接查詢(xún)5.3.2內(nèi)連接SELECTselection_list1,selection_list2,…,selection_listnFROMtb_name1INNERJOINtb_name2ON連接條件

[WHERE過(guò)濾條件];[表名1.]列名1比較運(yùn)算符[表名2.]列名2表名[AS]表別名5.3多表連接查詢(xún)1.等值連接SELECTselection_list1,selection_list2,…,selection_listnFROMtb_name1INNERJOINtb_name2ONtb_name1.column_name1=tb_name2.column_name2[WHERE過(guò)濾條件];5.3多表連接查詢(xún)【例5-30】查詢(xún)選修課程號(hào)為630572的學(xué)生的學(xué)號(hào)、姓名和成績(jī)。本例中要求查詢(xún)的列分別在student表和selectcourse表中，通過(guò)StudentID列使用內(nèi)連接方式連接兩個(gè)表，找出選修課程號(hào)為630572的行。SELECTstudent.StudentID,StudentName,ScoreFROMstudentINNERJOINselectcourseONstudent.StudentID=selectcourse.StudentIDWHEREselectcourse.CourseID='630572';5.3多表連接查詢(xún)【例5-31】查詢(xún)選修了課程名稱(chēng)為“數(shù)據(jù)結(jié)構(gòu)”的學(xué)號(hào)、姓名和課程號(hào)、課程名、成績(jī)的學(xué)生。SELECTstudent.StudentID,StudentName,course.CourseID,CourseName,ScoreFROMstudentINNERJOINcourseINNERJOINselectcourseONstudent.StudentID=selectcourse.StudentIDANDcourse.CourseID=selectcourse.CourseIDWHERECourseName='數(shù)據(jù)結(jié)構(gòu)';5.3多表連接查詢(xún)2.不等值連接【例5-32】對(duì)student表和selectcourse表做不等值連接，返回的結(jié)果集限制在5行以?xún)?nèi)。SELECT*FROMstudentINNERJOINselectcourseONstudent.StudentID!=selectcourse.StudentID;5.3多表連接查詢(xún)3.自然連接（NATURALJOIN）SELECTselection_list1,selection_list2,…,selection_listnFROMtb_name1NATURALJOINtb_name2;【例5-33】對(duì)student表和selectcourse表做自然連接。SELECT*FROMstudentNATURALJOINselectcourse;5.3多表連接查詢(xún)SELECTStudentID,StudentName,CourseID,ScoreFROMstudentNATURALJOINselectcourse;5.3多表連接查詢(xún)SELECTStudentID,StudentName,CourseID,CourseName,ScoreFROMstudentNATURALJOINselectcourseNATURALJOINcourse;5.3多表連接查詢(xún)4.自連接【例5-34】查詢(xún)與“算法”這門(mén)課學(xué)分相同的課程信息。SELECTc1.*FROMcourseASc1JOINcourseASc2ONc1.Credit=c2.CreditWHEREc2.CourseName='算法';5.3多表連接查詢(xún)SELECTc1.*FROMcourseASc1,courseASc2WHEREc1.Credit=c2.CreditANDc2.CourseName='算法';SELECTc1.*FROMcourseASc1JOINcourseASc2ONc1.Credit=c2.CreditWHEREc2.CourseName='算法'ANDc1.CourseName!='算法';5.3多表連接查詢(xún)5.3.3外連接SELECTselection_list1,selection_list2,…,selection_listnFROMtb_name1LEFT|RIGHTJOINtb_name2ONtb_name1.column_name1=tb_name2.column_name25.3多表連接查詢(xún)1.左連接（LEFTJOIN）【例5-35】查詢(xún)所有學(xué)生及其選修課程的情況，包括沒(méi)有選修課程的學(xué)生，要求顯示學(xué)號(hào)、姓名、班號(hào)、選修的課程號(hào)和成績(jī)，使用左外連接。INSERTINTOstudent(StudentID,StudentName,Sex,Birthday,Address,ClassID)VALUES('202260010321','于得水','男','2003-10-22','山西','2022600103');SELECTa.StudentID,StudentName,ClassID,CourseID,ScoreFROMstudentASaLEFTJOINselectcourseASbONa.StudentID=b.StudentID;5.3多表連接查詢(xún)5.3多表連接查詢(xún)2.右連接（RIGHTJOIN）【例5-36】查詢(xún)所有學(xué)生及其選修課程的情況，包括沒(méi)有選修課程的學(xué)生，要求顯示學(xué)號(hào)、姓名、班號(hào)、選修的課程號(hào)和成績(jī)，使用右外連接。SELECTa.StudentID,StudentName,ClassID,CourseID,ScoreFROMstudentASaRIGHTJOINselectcourseASbONa.StudentID=b.StudentID;5.3多表連接查詢(xún)5.4.1子查詢(xún)的執(zhí)行過(guò)程和類(lèi)型1.子查詢(xún)的執(zhí)行過(guò)程2.子查詢(xún)的類(lèi)型3.子查詢(xún)中的常用運(yùn)算4.使用子查詢(xún)時(shí)的注意事項(xiàng)5.4子查詢(xún)5.4.2選擇列表中的子查詢(xún)【例5-37】利用子查詢(xún)顯示學(xué)號(hào)為202263050133的學(xué)生的平均成績(jī)、考試次數(shù)，同時(shí)顯示學(xué)號(hào)、姓名等信息。SELECTStudentID,StudentName,(SELECTAVG(Score)FROMselectcourseWHEREStudentID='202263050133')平均成績(jī),(SELECTCOUNT(StudentID)FROMselectcourseWHEREStudentID='202263050133')考試次數(shù)

FROMstudentWHEREStudentID='202263050133';5.4子查詢(xún)5.4.3子查詢(xún)生成派生表【例5-38】查詢(xún)課程號(hào)為600131，成績(jī)高于60分的學(xué)生的學(xué)號(hào)、課程號(hào)和成績(jī)。SELECTT.StudentID,T.CourseID,T.ScoreFROM(SELECT*FROMselectcourseWHEREScore>60)AST WHERECourseID='600131';5.4子查詢(xún)5.4.4WHERE子句中的子查詢(xún)1.子查詢(xún)與比較運(yùn)算符組成條件【例5-39】在成績(jī)表selectcourse中，查詢(xún)低于平均分的學(xué)生。SELECTStudentID,CourseID,ScoreFROMselectcourseWHEREScore<(SELECTAVG(Score)FROMselectcourse);SELECTAVG(Score)FROMselectcourse;SELECTStudentID,CourseID,ScoreFROMselectcourseWHEREScore<75.57143;5.4子查詢(xún)2.使用帶IN關(guān)鍵字的子查詢(xún)【例5-40】查詢(xún)考試成績(jī)低于60分的學(xué)生的學(xué)號(hào)、姓名等信息。SELECT*FROMstudentWHEREStudentIDIN(SELECTStudentIDFROMselectcourseWHEREScore<60);SELECTStudentIDFROMselectcourseWHEREScore<60;5.4子查詢(xún)3.使用帶EXISTS關(guān)鍵字的子查詢(xún)【例5-31】如果存在班號(hào)“2022600103”的班，則查詢(xún)?cè)摪嗟乃袑W(xué)生。SELECT*FROMstudentWHEREEXISTS(SELECT*FROMclassWHEREClassID='2022600103')ANDClassID='2022600103';5.4子查詢(xún)4.使用ANY、SOME或ALL的子查詢(xún)（1）使用ANY（或SOME）的子查詢(xún)＜表達(dá)式＞{=|<>|!=|>|>=|<|<=|!>|!<}ANY(子查詢(xún))【例5-42】查詢(xún)男生中比某個(gè)女生出生年份晚的學(xué)生姓名和出生年份。SELECTDISTINCTYEAR(Birthday)FROMstudentWHERESex='女';5.4子查詢(xún)SELECTStudentName,YEAR(Birthday)FROMstudentWHERESex='男'ANDYEAR(Birthday)>ANY(SELECTDISTINCTYEAR(Birthday)FROMstudentWHERESex='女');5.4子查詢(xún)（2）使用ALL的子查詢(xún)【例5-43】查詢(xún)男生中比所有女生出生年份晚的學(xué)生姓名和出生年份。SELECTStudentName,YEAR(Birthday)FROMstudentWHERESex='男'ANDYEAR(Birthday)>ALL(SELECTDISTINCTYEAR(Birthday)FROMstudentWHERESex='女');5.4子查詢(xún)（3）ANY或ALL與聚合函數(shù)的對(duì)應(yīng)關(guān)系5.4子查詢(xún)【例5-44】查詢(xún)男生中比某個(gè)女生出生年份晚的學(xué)生姓名和出生年份。SELECTStudentName,YEAR(Birthday)FROMstudentWHERESex='男'ANDYEAR(Birthday)>(SELECTMIN(YEAR(Birthday))FROMstudentWHERESex='女');5.4子查詢(xún)5.4.5用子查詢(xún)插入、修改或刪除記錄1.用子查詢(xún)插入記錄INSERTINTO表名[(列名列表1)](SELECT列名列表2FROM表名);【例5-45】把student表中2002年出生的學(xué)生記錄添加到student2002表中。INSERTINTOstudent2002(StudentID,StudentName,Sex,Birthday,Address,ClassID)(SELECT*FROMstudentWHEREYEAR(Birthday)='2002');SELECT*FROMstudent2002;5.4子查詢(xún)2.用子查詢(xún)修改記錄【例5-46】學(xué)號(hào)為202240010215的學(xué)生轉(zhuǎn)班為“數(shù)學(xué)2022-3班”，在student2002表中，修改該生的班級(jí)號(hào)。SELECTDISTINCTClassIDFROMclassWHEREClassName='數(shù)學(xué)2022-3班'LIMIT1;UPDATEstudent2002SETClassID=(SELECTDISTINCTClassIDFROMclassWHEREClassName='數(shù)學(xué)2022-3班'LIMIT1)WHEREStudentID='202240010215';5.4子查詢(xún)3.用子查詢(xún)刪除記錄【例5-47】在student2002表中，刪除“數(shù)學(xué)2022-3班”的所有記錄。DELETEFROMstudent2002WHEREClassID=(SELECTDISTINCTClassIDFROMclassWHEREClassName='數(shù)學(xué)2022-3班'LIMIT1);5.4子查詢(xún)祝賀你完成了一章《MySQL數(shù)據(jù)庫(kù)應(yīng)用教程》劉瑞新主編配套資源第6章索引《MySQL數(shù)據(jù)庫(kù)應(yīng)用教程》劉瑞新主編配套資源目錄第6章索引6.1索引概述6.2查看索引6.3創(chuàng)建索引6.4使用索引6.5刪除索引6.1.1表中數(shù)據(jù)的搜索方式1.全表搜索方式2.索引搜索方式6.1索引概述6.1.2索引的概念6.1.3索引的類(lèi)型1.按數(shù)據(jù)結(jié)構(gòu)分類(lèi)（1）B樹(shù)索引（2）Hash索引6.1索引概述2.按邏輯區(qū)分類(lèi)（1）普通索引（INDEX）（2）唯一（UNIQUE）索引（3）主鍵（PRIMARYKEY）索引（4）全文（FULLTEXT）索引（5）空間（SPATIAL）索引6.1索引概述3.按使用時(shí)分類(lèi)（1）單列索引（2）多列索引6.1索引概述4.聚簇索引和非聚簇索引（1）聚簇索引（2）非聚簇索引6.1索引概述使用SHOWINDEX語(yǔ)句查看表上建立的索引名、索引類(lèi)型及相關(guān)參數(shù)，其語(yǔ)法格式為：SHOWINDEXFROMtb_name[FROMdb_name];SHOWINDEXFROM[db_name.]tb_name;6.2查看索引【例6-1】在studentinfo數(shù)據(jù)庫(kù)中，查看selectcourse表上建立的索引。USEstudentinfo;SHOWINDEXFROMselectcourse;6.2查看索引6.3.1創(chuàng)建索引的語(yǔ)句1.使用CREATETABLE語(yǔ)句創(chuàng)建索引CREATE[TEMPORARY]TABLE[db_name.]tb_name(column_namedata_type[列級(jí)完整性約束條件,][…,][表級(jí)完整性約束條件,][CONSTRAINTindex_name][UNIQUE|FULLTEXT][INDEX][index_name](index_column));6.3創(chuàng)建索引index_column索引列的語(yǔ)法格式如下：index_column_name[(length)][ASC|DESC]6.3創(chuàng)建索引2.使用CREATEINDEX語(yǔ)句創(chuàng)建索引CREATE[UNIQUE|FULLTEXT][INDEX]index_nameONtb_name(index_column_name[(length)][ASC|DESC]);6.3創(chuàng)建索引3.使用ALTERTABLE語(yǔ)句創(chuàng)建索引ALTERTABLEtb_nameADD[UNIQUE|FULLTEXT][INDEX][index_name](index_column_name[(length)][ASC|DESC]);4.自動(dòng)創(chuàng)建索引6.3創(chuàng)建索引6.3.2創(chuàng)建索引實(shí)例1.沒(méi)有索引【例6-2】在studentinfo數(shù)據(jù)庫(kù)中，重新創(chuàng)建系表department，列定義有系編號(hào)DepartmentID、CHAR(2)；系名稱(chēng)DepartmentName、VARCHAR(10)；系電話Telephone、VARCHAR(20)。DROPTABLEIFEXISTSdepartment;CREATETABLEdepartment(DepartmentIDCHAR(2),DepartmentNameVARCHAR(10),TelephoneCHAR(20));SHOWINDEXFROMdepartment;6.3創(chuàng)建索引2.普通索引【例6-3】重新創(chuàng)建department表，在定義表的同時(shí)為DepartmentID列定義普通索引，并按升序排列；創(chuàng)建表后為該表的DepartmentName列添加普通索引。DROPTABLEIFEXISTSdepartment;CREATETABLEdepartment(DepartmentIDCHAR(2),DepartmentNameVARCHAR(10),TelephoneCHAR(20),INDEXindex_id(IDASC));6.3創(chuàng)建索引CREATEINDEXindex_nameONdepartment(DepartmentName);SHOWINDEXFROMdepartment;6.3創(chuàng)建索引3.列值前綴普通索引【例6-4】在department表中，為DepartmentName列添加前3個(gè)字符的列值前綴普通索引；為T(mén)elephone列添加前14個(gè)字符的列值前綴普通索引。CREATEINDEXindex_name3ONdepartment(DepartmentName(3)DESC);ALTERTABLEdepartmentADDINDEXindex_telephone14(Telephone(14));SHOWINDEXFROMdepartment;6.3創(chuàng)建索引4.唯一索引【例6-5】重新創(chuàng)建department表，定義表的Telephone列是唯一鍵約束，在表的DepartmentName列上創(chuàng)建名為name的唯一索引。DROPTABLEIFEXISTSdepartment;CREATETABLEdepartment(DepartmentIDCHAR(2),DepartmentNameVARCHAR(10),TelephoneCHAR(20)UNIQUE,UNIQUEINDEXname(DepartmentName));6.3創(chuàng)建索引SHOWINDEXFROMdepartment;6.3創(chuàng)建索引【例6-6】使用CREATEINDEX在表department的DepartmentID列上創(chuàng)建唯一索引。CREATEUNIQUEINDEXdepartmentIDONdepartment(DepartmentID);6.3創(chuàng)建索引【例6-7】使用ALTERTABLE語(yǔ)句在表department的電話列Telephone上創(chuàng)建唯一索引。ALTERTABLEdepartmentADDUNIQUEINDEX(TelephoneDESC);6.3創(chuàng)建索引5.多列索引【例6-8】創(chuàng)建teacher表，列有TeacherID、CHAR(6)；TeacherName、CHAR(20)；Age、INT；DepartmentName、VARCHAR(10)，DepartmentID、CHAR(2)。CREATETABLEteacher(TeacherIDCHAR(6),TeacherNameCHAR(20),AgeINT,DepartmentNameVARCHAR(10),DepartmentIDCHAR(2),INDEXname(DepartmentNameASC,TeacherNameDESC));6.3創(chuàng)建索引SHOWINDEXFROMteacher;CREATEINDEXname2ONteacher(DepartmentName,TeacherName);6.3創(chuàng)建索引6.全文索引【例6-9】在teacher表中添加簡(jiǎn)歷列Note、VARCHAR(50)，并指定Note列為全文索引。ALTERTABLEteacherADDCOLUMNNoteVARCHAR(50),ADDFULLTEXTINDEXindex_note(Note);6.3創(chuàng)建索引7.主鍵索引和外鍵索引【例6-10】重新創(chuàng)建department表，在定義列時(shí)對(duì)DepartmentID列設(shè)置主鍵約束，對(duì)Telephone列設(shè)置唯一鍵，對(duì)DepartmentName列創(chuàng)建唯一索引。DROPTABLEIFEXISTSdepartment;CREATETABLEdepartment(DepartmentIDCHAR(2)PRIMARYKEY,#設(shè)置主鍵約束

DepartmentNameVARCHAR(10),TelephoneCHAR(20)UNIQUE,#設(shè)置唯一約束

UNIQUEINDEXname(DepartmentName)#創(chuàng)建唯一索引);6.3創(chuàng)建索引SHOWINDEXdepartment;6.3創(chuàng)建索引【例6-11】在teacher表中，給TeacherID列添加主鍵約束。ALTERTABLEteacherADDPRIMARYKEY(TeacherID);6.3創(chuàng)建索引【例6-12】重新創(chuàng)建teacher表，設(shè)置TeachertID列為主鍵約束，設(shè)置外鍵約束FK_teacher，通過(guò)外鍵DepartmentID列與department表建立外鍵關(guān)系。DROPTABLEIFEXISTSteacher;CREATETABLEteacher(TeacherIDCHAR(6),TeacherNameCHAR(20),AgeINT,DepartmentIDCHAR(2),CONSTRAINTPK_teacherPRIMARYKEY(TeacherID),

CONSTRAINTFK_teacherFOREIGNKEY(DepartmentID)REFERENCESdepartment(DepartmentID));6.3創(chuàng)建索引SHOWINDEXFROMteacher;6.3創(chuàng)建索引6.4.1EXPLAIN語(yǔ)句的使用1.EXPLAIN語(yǔ)句的語(yǔ)法EXPLAINSQL_statement;【例6-13】在studentinfo數(shù)據(jù)庫(kù)中，顯示student表的索引，然后用EXPLAIN語(yǔ)句執(zhí)行查詢(xún)，了解EXPLAIN運(yùn)行的結(jié)果。SHOWINDEXFROMstudent;6.4使用索引6.4使用索引6.4使用索引6.4使用索引6.4使用索引2.