數據庫原理與應用教程-MySQL8.0第6章

數據庫查詢第6章

數據庫查詢所謂查詢，就是檢索數據庫內數據的特定請求。數據庫接受用SQL語言編寫的查詢，使用查詢可以按照不同的方式查看和分析數據。查詢設計是數據庫應用程序開發的重要組成部分，因為在設計好數據庫并用數據進行填充后，最常見地就是通過查詢來使用數據。6.1SELECT查詢語法基本語法格式：SELECT{select_list}FROM{table_list|view_list}[WHERE{search_conditions}][GROUPBY{group_by_list}][HAVING{search_conditions}][ORDERBY{order_list[ASC|DESC]}][LIMIT{n[,m]}];6.2簡單查詢簡單查詢包括投影查詢、選擇查詢及采用聚合函數的簡單計算查詢。6.2.1投影查詢通過SELECT語句的select_list項組成結果表的列。投影查詢格式：SELECT[ALL|DISTINCT]{*|{{column_name|expression}[[AS]column_alias]}[,…n]}6.2.1投影查詢1.選擇一個表中指定的列使用SELECT語句選擇一個表中的某些列，各列名之間要以逗號分隔，每個語句的結尾加分號?！纠?-1】查詢teaching數據庫中學生的姓名、性別和專業。USEteaching;SELECTsname,ssex,majorFROMstudent;6.2.1投影查詢【例6-2】查詢course表的所有記錄。USEteaching;SELECT*FROMcourse;用“*”表示表中所有的列，按用戶創建表時聲明列的順序來顯示所有的列?！纠?-3】查詢student表的專業名稱，濾掉重復行。USEteaching;SELECTDISTINCTmajorFROMstudent;

DISTINCT關鍵字可以過濾掉查詢結果中的重復行。6.2.1投影查詢2.改變查詢結果中的顯示標題SELECT語句中可以根據實際需要對查詢數據的列標題進行改變，或者為沒有標題的列加上臨時的標題。常用的方式：（1）用AS關鍵字來連接列表達式和指定的列名。

（2）在列表達式后面給出列名。6.2.1投影查詢【例6-4】查詢student表中所有學生的學號、姓名，結果中各列的標題分別指定為漢字學號和姓名。

USEteaching;SELECTsnoAS學號,snameAS姓名FROMstudent;或：USEteaching;SELECTsno學號,sname姓名FROMstudent;注意：列標題別名只在定義的語句中有效，即只是顯示標題，對原表中列標題沒有任何影響。6.2.1投影查詢3.計算列值

在進行數據查詢時，經常需要對查詢到的數據進行再次計算處理。“計算列”并不存在于表格所存儲的數據中，所以沒有列名?！纠?-5】查詢sc表，按150分制計算成績。USEteaching;SELECTsno,cno,score*1.50AS成績150分制FROMsc;6.2.2選擇查詢投影查詢是從列的角度進行的查詢，一般對行不進行任何過濾（DISTINCT除外）。但是，一般的查詢都不是針對表中所有行的查詢，只是從表中選出滿足指定條件的內容，這就要用到WHERE子句進行選擇查詢。選擇查詢的基本語法：SELECTselect_listFROMtable_listWHEREsearch_conditions6.2.2選擇查詢查詢條件謂詞比較運算符=,>,<,>=,<=,!=,<>確定范圍BETWEENAND，NOTBETWEENAND確定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重條件AND，OR，NOT6.2.2選擇查詢1.使用關系表達式【例6-6】查詢sc表中成績大于等于60的學生的學號、課程號和成績。USEteaching;SELECT*FROMscWHEREscore>=60;6.2.2選擇查詢2.使用邏輯表達式

【例6-7】查詢student表中計算機科學與技術專業的男生的信息。USEteaching;SELECT*FROMstudentWHEREmajor='計算機科學與技術'ANDssex='男';【例6-8】查詢計算機科學與技術專業或男生信息。USEteaching;SELECT*FROMstudentWHEREmajor='計算機科學與技術'ORssex='男';6.2.2選擇查詢3.使用BETWEEN關鍵字使用BETWEEN關鍵字可以更方便地限制查詢數據的范圍。使用BETWEEN表達式進行查詢的效果完全可以用含有“>=”和“<=”的邏輯表達式來代替，使用NOTBETWEEN進行查詢的效果完全可以用含有“>”和“<”的邏輯表達式來代替。6.2.2選擇查詢【例6-9】查詢sc表中成績在80到90之間的學生的學號、課程號和成績。USEteaching;SELECT*FROMscWHEREscoreBETWEEN80AND90;或：USEteaching;SELECT*FROMscWHEREscore>=80ANDscore<=90;

6.2.2選擇查詢4.使用IN（屬于）關鍵字

同BETWEEN關鍵字一樣，IN的引入也是為了更方便地限制檢索數據的范圍。語法格式為：表達式[NOT]IN(表達式1,表達式2[,…,表達式n])【例6-11】查詢student表中計算機科學與技術和通信工程專業的學生的姓名、學號和專業。USEteaching;SELECTsname,sno,majorFROMstudentWHEREmajorIN('計算機科學與技術','通信工程');6.2.2選擇查詢5.使用LIKE關鍵字使用LIKE關鍵字的查詢又叫模糊查詢，LIKE關鍵字搜索與指定模式匹配的字符串、日期或時間值。通配符“%”：為包含零個或多個字符的任意字符串；通配符“_”：為任意單個字符。【例6-12】通配符的示例

LIKE'AB%'返回以“AB”或“ab”開頭的任意字符串。LIKE'%abc'返回以“ABC”或“abc”結尾的任意字符串。LIKE'%abc%'返回包含“ABC”或“abc”的任意字符串。LIKE'_12'返回以“12”結尾的三個字符的字符串。6.2.2選擇查詢【例6-13】查詢student表中所有姓張的學生的信息。USEteaching;SELECT*FROMstudentWHEREsnamelike'張%';6.2.2選擇查詢6.IS[NOT]NULL(是[否]為空)查詢在WHERE子句中不能使用比較運算符對空值進行判斷，只能使用空值表達式來判斷某個列值是否為空值。語法格式：表達式IS[NOT]NULL【例6-14】查詢sc表中所有成績為空值的學生的學號、課程號和成績。USEteaching;SELECT*FROMscWHEREscoreISNULL;6.2.2選擇查詢7.復合條件查詢在WHERE子句中可以使用邏輯運算符把若干個搜索條件合并起來，組成復雜的復合搜索條件。邏輯運算符優先級從高到低依次是NOT、AND、OR。【例6-15】從student表中查詢所有計算機科學與技術和通信工程專業的女生的信息。USEteaching;SELECT*FROMstudentWHERE(major='計算機科學與技術'ORmajor='通信工程')ANDssex='女';

6.2.3聚合函數查詢MySQL提供了一系列聚合函數，這些函數把數據描述為一個整體而不是一行行孤立的記錄，實現數據集合的匯總或是求平均值等各種運算。函數名功能sum(列名)返回一個數字列的總和avg(列名)對一個數字列計算平均值min(列名)返回一個數字、字符串或日期列的最小值max(列名)返回一個數字、字符串或日期列的最大值count(列名)返回一個列的數據項數count(*)返回找到的行數6.2.3聚合函數查詢【例6-16】查詢sc表中成績的平均值，平均值顯示列標題為平均成績。USEteaching;SELECTavg(score)AS平均成績FROMsc;【例6-17】從student表中查詢專業的種類個數（相同的按一種計算）。

USEteaching;SELECTcount(DISTINCTmajor)AS專業種類數FROMstudent;6.2.3聚合函數查詢【例6-18】在sc表中查詢2021010001號學生的平均成績和最高成績。USEteaching;SELECTavg(score)AS平均成績,max(score)AS最高成績FROMscWHEREsno='2021010001';6.3分組查詢使用聚合函數返回的是所有行數據的統計結果。如果需要按某一列數據的值進行分類，在分類的基礎上再進行查詢，就要使用GROUPBY子句了。GROUPBY子句的語法：[GROUPBY[ALL]group_by_expression[,…n][HAVINGsearch_conditions]]6.3.1簡單分組如果在GROUPBY子句中沒有使用HAVING關鍵字，那么表示這種分組的技術是簡單分組技術。【例6-19】查詢student表中男生和女生的人數。USEteaching;SELECTssex,count(ssex)人數

FROMstudentGROUPBYssex;6.3.1簡單分組注意：使用GROUPBY子句時，選擇（SELECT）列表中任何非聚合表達式內的所有列都應該包含在GROUPBY列表中（不能使用列別名），或者說GROUPBY表達式應該與選擇列表表達式完全匹配，否則查詢結果可能無法理解?！纠?-20】查詢sc表中每個學生的平均成績。USEteaching;SELECTsno,avg(score)平均成績FROMscGROUPBYsno;6.3.2HAVING的應用可以使用HAVING關鍵字來對查詢和統計的結果進行進一步的篩選。【例6-21】在sc表中查詢選修了3門及以上課程的學生學號和選課數。USEteaching;SELECTsno,COUNT(cno)選修課程數FROMscGROUPBYsnoHAVINGCOUNT(cno)>=3;

6.3.2HAVING的應用HAVING與WHERE子句的區別：WHERE子句是對源表中數據篩選滿足條件的行；而HAVING子句是對GROUPBY分組查詢后產生的組再加條件，篩選出滿足條件的組。另外，HAVING中條件一般都直接使用聚合函數，WHERE中條件不能直接使用聚合函數。在MySQL系統中，一般情況下可以在3個地方使用聚合函數，即SELECT子句、HAVING子句和ORDERBY子句中。6.4連接查詢在實際應用中，經常需要同時從兩個表或兩個以上表中檢索數據。實現從兩個或兩個以上表中檢索數據且結果集中出現的列來自于兩個或兩個以上表中的檢索操作被稱為連接技術。在MySQL系統中，這種連接操作又可以細分為交叉連接、內連接、外連接等。6.4.1內連接

內連接把兩個表中的數據連接生成第三個表，第三個表中僅包含那些滿足連接條件的數據行。內連接的語法：SELECTselect_listFROMtable1INNERJOINtable2ONjoin_condition或SELECTselect_listFROMtable1,table2WHEREjoin_condition連接條件格式為：[<table1>.]<column1><compareoperator>[<table2>.]<column2>6.4.1內連接

【例6-22】查詢每個學生的姓名、課程號和成績。

USEteaching;SELECTstudent.sname,o,sc.scoreFROMstudentINNERJOINscONstudent.sno=sc.sno;也可以利用下面的程序來實現：

SELECTstudent.sname,o,sc.scoreFROMstudent,scWHEREstudent.sno=sc.sno;

注意：當從多個表中查詢的列名相同時，列名前必須加表名；列名不同時，列名前可以不加表名，但有時也會加上表名，以增強可讀性。6.4.1內連接

【例6-23】查詢計算機科學與技術專業的學生所選的每門課的平均分。USEteaching;SELECTo,avg(b.score)as平均成績FROMstudentaINNERJOINscbONa.sno=b.snoanda.major='計算機科學與技術'GROUPBYo;為了簡化輸入，可以在SELECT查詢的FROM子句中為表定義一個臨時別名，在查詢中引用，以縮寫表名。6.4.1內連接

【例6-24】查詢成績在75分以上的學生的學號、姓名，選修課的課程號、課程名、成績。USEteaching;SELECTo,ame,a.sno,a.sname,b.scoreFROMstudentASaJOINscASbONa.sno=b.snoANDb.score>75JOINcourseAScono=o;

6.4.2自連接連接操作不僅可以在不同的表上進行，而且在同一張表內可以進行自身連接，即將同一個表的不同行連接起來。自連接可以看作一張表的兩個副本之間的連接。必須為表指定兩個別名，使之在邏輯上成為兩張表。【例6-25】查詢同名學生的信息。USEteaching;SELECT*FROMstudentaINNERJOINstudentbONa.sname=b.snameANDa.sno<>b.sno;6.4.3外連接外連接一般只限制其中一個表的數據行，而不限制另外一個表中的數據。這種連接形式在許多情況下是非常有用的，例如在連鎖超市統計報表時，不僅要統計那些有銷售量的超市和商品，而且還要統計那些沒有銷售量的超市和商品。在MySQL系統中，可以使用的兩種外連接關鍵字，即LEFTOUTERJOIN和RIGHTOUTERJOIN。①左外連接是對連接條件中左邊的表不加限制。②右外連接是對連接條件中右邊的表不加限制。6.4.3外連接【例6-26】查詢每個學生及其選修課程的成績情況（含未選課的學生信息）。USEteaching;SELECTstudent.*,o,sc.scoreFROMstudentLEFTJOINscONstudent.sno=sc.sno;【例6-27】查詢學生選修課程的情況(含未被選修的課程信息)。USEteaching;SELECTsc.sno,sc.score,course.*FROMscRIGHTJOINcourseONo=o;如果想查詢成績最高的學生的學號和姓名。SELECTstudent.sno,sname,scoreFROMstudent,scWHEREscore=MAX(score)Andstudent.sno=sc.sno注意：where條件當中不能直接使用聚合函數。所以，以上語句是錯誤的，而這樣的查詢需要用子查詢來完成。SELECT語句可以嵌套在其他許多語句中，這些語句包括SELECT、INSERT、UPDATE及DELETE等，這些嵌套的SELECT語句就被稱為子查詢。6.5子查詢6.5子查詢當一個查詢依賴于另外一個查詢結果時，那么可以使用子查詢（一般為查詢條件不已知）。在某些查詢中，查詢語句比較復雜不容易理解，因此為了把這些復雜的查詢語句分解成多個比較簡單的查詢語句形式時也常使用子查詢方式。使用子查詢方式完成查詢操作的技術是子查詢技術。子查詢可以分為無關子查詢（嵌套子查詢）和相關子查詢。6.5.1無關子查詢無關子查詢的執行不依賴于外部查詢。無關子查詢在外部查詢之前執行，然后返回數據供外部查詢使用，無關子查詢中不包含對于外部查詢的任何引用。1.比較子查詢使用子查詢進行比較測試時，通過等于（=）、不等于（<>）、小于（<）、大于（>）、小于或等于（<=）以及大于或等于（>=）等比較運算符，將一個表達式的值與子查詢返回的單值進行比較。6.5.1無關子查詢【例6-29】查詢與2021010001號學生在同一個專業學習的學生的學號、姓名和專業。USEteaching;SELECTsno,sname,majorFROMstudentWHEREmajor=(SELECTmajorFROMstudentWHEREsno='2021010001');6.5.1無關子查詢【例6-30】查詢C001號課考試成績比劉玲玲高的學生的學號和姓名。USEteaching;SELECTstudent.sno,snameFROMstudent,scWHEREstudent.sno=sc.snoandcno='C001'andscore>(SELECTscoreFROMscWHEREcno='C001'andsno=(SELECTsnoFROMstudent

WHEREsname='劉玲玲'));6.5.1無關子查詢2.SOME、ANY、ALL和IN子查詢ALL和ANY操作符的常見用法是結合一個比較運算符對一個數據列子查詢的結果進行測試，測試比較值是否與子查詢所返回的全部或一部分值匹配。【例6-31】查詢計算機科學與技術專業年齡最大的學生的學號和姓名。SELECTsno,snameFROMstudentWHEREsbirthday<=ALL(SELECTsbirthdayFROMstudentWHEREmajor='計算機科學與技術')ANDmajor='計算機科學與技術';6.5.1無關子查詢【例6-32】查詢與任何一個通信工程專業學生同齡的學生的信息。USEteaching;SELECT*FROMstudentWHEREyear(birthday)=ANY(SELECTyear(birthday)FROMstudentWHEREspecialty='通信工程');6.5.1無關子查詢【例6-33】查詢選修了C001號課程的學生姓名和所在專業。SELECTsname,majorFROMstudentWHEREsnoIN(SELECTsnoFROMscWHEREcno='C001');6.5.1無關子查詢3.子查詢結果作為主查詢的查詢對象【例6-34】查詢有2個以上學生平均成績超過80分的班級（用年級和專業表示）。USEteaching;SELECTgrade,majorFROMstudents,(SELECTsnoFROMSCGROUPBYsnoHAVINGAVG(score)>=80)ssWHEREs.sno=ss.snoGROUPBYgrade,majorHAVINGCOUNT(*)>=2;6.5.1無關子查詢USEteaching;SELECTgrade,majorFROMstudentWHEREsnoIN(SELECTsnoFROMSCGROUPBYsnoHAVINGAVG(score)>=80)GROUPBYgrade,majorHAVINGCOUNT(*)>=2;6.5.2相關子查詢在相關子查詢中，子查詢的執行依賴于外部查詢，多數情況下是子查詢的WHERE子句中引用了外部查詢的表。無關子查詢中子查詢只執行一次，而相關子查詢中的子查詢需要重復地執行。

相關子查詢的執行過程：(1)子查詢為外部查詢的每一行執行一次，外部查詢將子查詢引用的列的值傳給子查詢。(2)如果子查詢的任何行與其匹配，外部查詢就返回結果行。(3)再回到第一步，直到處理完外部表的每一行。6.5.2相關子查詢1.比較子查詢【例6-35】查詢成績比該課的平均成績低的學生的學號、課程號、成績。USEteaching;SELECTsno,cno,scoreFROMscaWHEREscore<(SELECTavg(score)FROMscbWHEREo=o);6.5.2相關子查詢2.帶有EXISTS的子查詢（存在性測試）使用子查詢進行存在性測試時，通過邏輯運算符EXISTS或NOTEXISTS，檢查子查詢所返回的結果集是否有行存在。使用邏輯運算符EXISTS時，如果在子查詢的結果集內包含有一行或多行，則存在性測試返回TRUE；否則返回FALSE。在EXISTS前加上NOT時，將對存在性測試結果取反。6.5.2相關子查詢【例6-37】查詢所有選修了C004號課程的學生的學號和姓名。USEteaching;SELECTsno,snameFROMstudentWHEREEXISTS(SELECT*FROMscWHEREsno=student.snoANDcno='C004');

6.5.2相關子查詢由EXISTS引出的子查詢，其目標屬性列表達式一般用*表示，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義。使用子查詢時要注意以下幾點：(1)子查詢需要用括號()括起來。(2)子查詢可以嵌套。(3)子查詢返回的結果的數據類型必須匹配外圍查詢WHERE語句的數據類型。(4)子查詢中不能使用ORDERBY子句。6.6其它查詢6.6.1限制查詢的行數使用LIMIT子句可以選擇只顯示一個表中連續的某些行數據。語法格式：select_statementLIMITn[,m];【例6-38】查詢student表，只顯示前5行數據。USEteaching;SELECT*FROMstudentLIMIT5;6.6.1限制查詢的行數【例6-39】查詢student表，只顯示第6到8行數據。USEteaching;SELECT*FROMstudentLIMIT5,3;注意：初始記錄行的偏移量是0而不是1。6.6.2聯合查詢聯合查詢即集合的并，是指將兩個或兩個以上的SELECT語句通過UNION運算符連接起來的查詢，聯合查詢可以將兩個或更多查詢的結果組合為單個結果集，該結果集包含聯合查詢中所有查詢的全部行。使用UNION組合兩個查詢的結果集的兩個基本規則：

所有查詢中的列數和列的順序必須相同；數據類型必須兼容。6.6.2聯合查詢【例6-40】查詢選修了課程C001和課程C004的學生的課程號和姓名。USEteaching;SELECTcno,snameFROMsc,studentWHEREcno='C001'andsc.sno=student.snoUNIONSELECTcno,snameFROMsc,studentWHEREcno='C004'andsc.sno=student.sno;6.6.3對查詢結果排序在使用SELECT語句時，排序是一種常見的操作。排序是指按照指定的列或其他表達式對結果集進行排列順序的方式。SELECT語句中的ORDERBY子句負責完成排序操作?！纠?-41】查詢teaching庫中女生的學號、姓名和專業，并按學號升序排列。USEteaching;SELECTsno,sname,majorFROMstudentWHEREssex='女'ORDERBYsnoASC;

6.6.3對查詢結果排序【例6-42】查詢sc表中學生的學號和成績，并按成績降序排列。USEteaching;SELECTsno,scoreFROMscORDERBYscoreDESC;【例6-43】查詢sc中學生的學號和成績，并按成績降序排列，若成績相同按學號升序排列。USEteaching;SELECTsno,scoreFROMscORDERBYscoreDESC,snoASC;6.6.3對查詢結果排序【例6-44】使用LIMIT關鍵字查詢平均成績最高的前三名。USEteaching;

SELECTsno,AVG(score)平均成績FROMscGROUPBYsnoORDERBYAVG(score)DESCLIMIT3;

6.7數據操作中使用SELECT子句6.7.1INSERT語句中使用SELECT子句在INSERT語句中使用SELECT子句可以將一個或多個表或視圖中的值添加到另一個表中。USEteaching;CREATETABLE成績表(學號char(10),

課程號char(4),

成績int);INSERTINTO成績表(學號,課程號,成績)SELECT*FROMscWHEREscore>=80;6.7.1INSERT語句中使用SELECT子句注意：①不要把SELECT子句寫在圓括號中。②INSERT語句中的列名列表應當放在圓括號中，而且不使用VALUES關鍵字。如果來源表與目標表結構完全相同，則可以省略INSERT語句中的列名列表。③SELECT子句中的列列表必須與INSERT語句中的列列表相匹配。如果沒有在INSERT語句中給出列列表，SELECT子句中的列列表必須與目標表中的列相匹配。6.7.2UPDATE語句中使用SELECT子句【例6-46】將2022010001號學生選修的計算機應用基礎課的成績改為86分。UPDATEscSETscore=86WHEREsno='2022010001'ANDcno=(SELECTcnoFROMcourseWHEREcname='計算機應用基礎');6.7.3DELETE語句中使用SELECT子句【例6-49】將2022級軟件工程專業郭小偉選修的C001號課刪除。DELETEFROMscWHEREcno='C001'ANDsno=(SELECTsnoFROMstudentWHEREsname='郭小偉'ANDgrade='2022級'ANDmajor='軟件工程');數據庫原理與應用教程-MySQL8.0第7章

視圖和索引7.1視圖數據庫的基本表是由數據庫設計人員根據所有用戶的需求、按照規范化設計方法設計的，并不一定符合用戶的應用需求。MySQL可以根據各個用戶的應用需求重新定義表的數據結構，這種數據結構就是視圖。7.1.1視圖概述視圖可以視為另一種形式的表，是從一個或多個表中使用SELECT語句導出的虛表，那些用來導出視圖的表稱為基本表。數據庫中只存儲視圖的定義，而不存儲視圖對應的數據，這些數據仍存儲在導出視圖的基本表中，視圖實際上是一個查詢結果。當基本表中的數據發生變化時，視圖中的數據也隨之改變。7.1.1視圖概述視圖的優點如下：(1)為用戶集中數據，簡化用戶的數據查詢和處理。(2)保證數據的邏輯獨立性。(3)重新定制數據，使得數據便于共享。(4)提高了數據的安全性。7.1.2創建視圖必須遵循的原則：(1)只能在當前數據庫中創建視圖。

(2)視圖名稱必須遵循標識符的規則，且對每個用戶必須唯一。(3)用戶可以在其它視圖之上建立視圖。(4)如果視圖中的某一列是一個算術表達式、內置函數或常量派生而來，或者視圖中兩個或者更多的不同列擁有一個相同的名字，此時，需要為視圖的這些列指定特定的名稱。7.1.2創建視圖創建視圖的SQL語句語法：CREATEVIEW[database_name.]view_name

[(column_name[,...n])]AS{select_statement}[WITHCHECKOPTION]7.1.2創建視圖【例7-1】創建名為v_male的視圖，包括所有男生的基本信息。USEteaching;CREATEVIEWv_maleASSELECTsno,sname,ssex,sbirthday,major,gradeFROMstudentWHEREssex='男';7.1.2創建視圖【例7-2】創建名為v_computer的視圖，包括計算機科學與技術專業的學生的學號、姓名，和他們選修的課程號、課程名和成績。USEteaching;CREATEVIEWv_computerASSELECTstudent.sno,sname,o,cname,scoreFROMstudent,sc,courseWHEREstudent.sno=sc.snoANDo=oANDmajor='計算機科學與技術';7.1.2創建視圖【例7-3】在bankcard數據庫創建名為acc_count的賬戶統計視圖，求每個儲戶的賬戶個數，要求包括身份證號和姓名。USEbankcard;CREATEVIEWacc_countASSELECTdepositor.IDNO,Dname,COUNT(*)ASNumberFROMdepositor,accountWHEREdepositor.IDNO=account.IDNOGROUPBYdepositor.IDNO,Dname;7.1.3修改視圖修改視圖，語法格式如下：ALTERVIEW[database_name.]view_name[(column_name[,...n])]AS{select_statement}[WITHCHECKOPTION]注：語句中的參數與CREATEVIEW語句中的參數相同。7.1.3修改視圖【例7-4】修改acc_count視圖，求每個儲戶的賬戶個數和總存款余額，要求包括身份證號和姓名。USEbankcard;ALTERVIEWacc_countASSELECTdepositor.IDNO,Dname,COUNT(*)ASNumber,SUM(Balance)SumBalanceFROMdepositor,accountWHEREdepositor.IDNO=account.IDNOGROUPBYdepositor.IDNO,Dname;7.1.3修改視圖【例7-5】在視圖上創建視圖：創建Few_Balance余額統計視圖，求總存款余額少于5000的儲戶信息，包括身份證號和姓名和手機號。USEbankcard;CREATEVIEWFew_BalanceASSELECTdepositor.IDNO,depositor.Dname,TelephoneFROMacc_count,depositorWHEREacc_count.IDNO=depositor.IDNOandSumBalance<5000;7.1.4使用視圖可以如同查詢基本表一樣通過視圖查詢所需要的數據，也可以通過視圖更新基本表中的數據。1．使用視圖進行數據查詢查詢各儲戶的統計信息：SELECT*FROMacc_count;

【例7-6】查詢v_computer視圖，統計計算機應用基礎課程的總分和平均分。SELECTSUM(score)sumscore,AVG(score)avgscoreFROMv_computerWHEREcname='計算機應用基礎';

7.1.4使用視圖2．使用視圖更新基本表中數據更新視圖的數據，其實就是對基本表中數據進行更新，因為真正存儲數據的地方是基本表，同樣使用INSERT、UPDATE、DELETE語句來完成。但并不是所有的視圖都可以進行數據更新，只有滿足以下可更新條件的視圖才能進行數據更新。(1)任何通過視圖的數據更新都只能引用一個基本表的列。①如果視圖中數據為一個表的行、列子集，則此視圖可更新；但如果視圖中沒有包含表中某個不允許取空值又沒有默認值約束的列，則不能插入數據。7.1.4使用視圖②如果視圖所依賴的基本表有多個時，完全不能向該視圖添加（INSERT）數據。③如果視圖所依賴的基本表有多個時，那么一次修改只能修改（UPDATE）一個基本表中的數據。④如果視圖所依賴的基本表有多個時，那么不能通過視圖刪除（DELETE）數據。(2)視圖中被修改的列必須直接引用表中基礎數據，不能是通過任何其他方式派生而來的，比如通過聚合函數、計算（如表達式計算）、集合運算等。(3)被修改的列不應是在創建視圖時受GROUPBY、HAVING、DISTINCT子句影響的。7.1.4使用視圖根據以上可更新條件分析例7-1~例7-3中各視圖是否為可更新視圖，即能否通過此視圖更新基本表中數據？很明顯，例7-1的視圖完全可更新例7-2的視圖只可以修改數據，不能插入和刪除例7-3的視圖完全不可更新7.1.4使用視圖通常有可能插入并不滿足視圖查詢的WHERE子句條件中的一行。為了限制此操作，可以在創建視圖時使用WITHCHECKOPTION選項。【例7-8】通過v_male視圖向student表中插入一個男生。INSERTINTOv_maleVALUES('2021010006','張三','男','2003-8-1','電子信息','2021級');7.1.4使用視圖通過v_male視圖向student表中插入一個女生，也可以完成。如果不希望用戶通過v_male視圖插入女生，在創建v_male視圖時應該使用WITHCHECKOPTION選項。CREATEVIEWv_maleASSELECTsno,sname,ssex,sbirthday,major,gradeFROMstudentWHEREssex='男'

WITHCHECKOPTION;7.1.5刪除視圖在不需要該視圖或想清除視圖定義及與之相關聯的權限時，可以刪除該視圖。視圖的刪除不會影響所依附的基本表的數據。1．在Navicat中選菜單界面刪除視圖2．使用SQL語句刪除視圖語法：DROPVIEWview_name【例7-9】刪除例7-2創建的v_computer視圖。USEteaching;DROPVIEWv_computer;7.2索引索引(Index)是對數據庫表中一個或多個列的值進行排序的結構，其主要目的是提高MySQL系統的性能，加快數據的查詢速度和減少系統的響應時間。7.2.1索引簡介數據庫的索引就類似于書籍的目錄，如果想快速查找而不是逐頁查找指定的內容，可以通過目錄中章節的頁號快速找到其對應的內容。在表中建立索引，然后在索引中找到符合查詢條件的索引值，最后通過保存在索引中的ROWID(行號，相當于頁碼)快速找到表中對應的記錄。7.2.1索引簡介索引的優點：（1）大大加快數據的檢索速度，這是創建索引的最主要的原因。（2）創建唯一性索引，可以保證表中每一行數據的唯一性。（3）可以加速表和表之間的連接。

（4）在使用分組和排序子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間。

（5）查詢優化器可以提高系統的性能，但它是依靠索引起作用的。

7.2.1索引簡介缺點：創建索引和維護索引都會消耗時間，當對表中的數據進行增加、刪除和修改操作時，索引就要進行維護，否則索引的作用就會下降。另外，每個索引都會占用一定的物理空間，如果占用的物理空間過多，就會影響到整個MySQL系統的性能。7.2.2索引分類1.兩大索引方法MySQL主要有HASH和B+樹索引兩大索引方法。（1）HASHHASH索引可以一次定位，不需要像樹形索引那樣逐層查找，具有極高的效率。但是，這種高效是有條件的，即只在“=”和“in”條件下高效，對于范圍查詢、排序及組合索引仍然效率不高。（2）B+樹B+樹索引就是一個中序遍歷有序的樹形結構。這是MySQL里默認和最常用的索引類型，實例中只按默認創建B+樹索引。7.2.2索引分類2.索引類別MySQL的索引類別包括普通索引、主鍵索引、唯一索引、全文索引和空間索引。另外，還有一種前綴索引。如果索引是根據單列創建的，這樣的索引稱為單列索引，根據多列組合創建的索引則稱為組合索引。7.2.3創建索引哪些列適合創建索引，哪些列不適合創建索引，需要進行詳細的考察。1．創建索引時應考慮的問題（1）對一個表中建大量的索引，應進行權衡。對于SELECT查詢，大量索引可以提高性能，可以從中選擇最快的查詢方法；但是，會影響INSERT、UPDATE和DELETE語句的性能。因為對表中的數據進行修改時，索引也要動態的維護，維護索引耗費的時間會隨著數據量的增加而增加，所以應避免對經常更新的表建立過多的索引，而對更新少而且數據量大的表創建多個索引，可以大大提高查詢性能。7.2.3創建索引（2）對于小型表（行數較少）進行索引可能不會產生優化效果。（3）對于主鍵和外鍵列應考慮建索引，因為經常通過主鍵查詢數據，而外鍵用于表間的連接。（4）很少在查詢中使用的列以及值很少的列（比如“性別”列）不應考慮建索引。（5）如果char或varchar類型的列字符數很多，可視情況選擇前n個字符值進行索引，即創建前綴索引。7.2.3創建索引2．在Navicat中通過菜單界面創建索引3．利用SQL語句創建索引（1）CREATETABLE語句創建索引語法格式：CREATETABLE

表名(屬性名數據類型[完整性約束條件],

……[UNIQUE|FULLTEXT|SPATIAL]

INDEX|KEY

[索引名]

(屬性名1[(長度)]

[ASC|DESC][,…n])

);7.2.3創建索引【例7-10】在teaching1庫中創建student1表，包含sno、sname、ssex和idno列，含義分別為學號、姓名、性別、身份證號，并按姓名列創建一個名為index_sname的普通索引。USEteaching1;CREATETABLEstudent1(snochar(10)PRIMARYKEY,snamevarchar(10)NOTNULL,ssexchar(1),idnochar(18),INDEXindex_sname(sname));7.2.3創建索引（2）ALTERTABLE語句創建索引ALTER

TABLE

表名

ADD

[

UNIQUE

|

FULLTEXT

|

SPATIAL

]

INDEX|KEY

[索引名](屬性名1

[

(長度)

]

[

ASC

|

DESC][,…n]);

【例7-11】修改teaching庫中student表，按姓名列創建一個名為index_sname的普通索引。USEteaching;ALTER

TABLE

studentADDINDEXindex_sname(sname);

7.2.3創建索引（3）CREATEINDEX語句創建索引語法格式如下：CREATE

[

UNIQUE

|

FULLTEXT

|

SPATIAL

]

INDEX|KEY

索引名ON表名(屬性名1

[

(長度)

]

[

ASC

|

DESC][,…n]);

【例7-12】在teaching1庫中student1表上，按身份證號列創建一個名為index_idno的唯一性索引。USEteaching1;CREATEUNIQUEINDEXindex_idnoONstudent1(idno);

7.2.3創建索引【例7-13】根據teaching庫中student表的專業、年級創建一個名為major_grade的組合索引，其中專業稱為升序，年級為降序。USEteaching;CREATEINDEXmajor_gradeONstudent(major,gradeDESC);7.2.3創建索引4．間接創建索引在定義表結構或修改表結構時，如果定義了主鍵約束（PRAMARYKEY）或者唯一性約束（UNIQUE），可以間接創建索引?！纠?-15】在teaching1庫中創建一個teacher教師表，并定義主鍵約束和唯一性約束。USEteaching1;CREATETABLEteacher(tnochar(6)PRAMARYKEY,tidchar(18)UNIQUE,tnamevarchar(10));7.2.3創建索引4．間接創建索引在定義表結構或修改表結構時，如果定義了主鍵約束（PRAMARYKEY）或者唯一性約束（UNIQUE），可以間接創建索引。【例7-15】在teaching1庫中創建一個teacher教師表，并定義主鍵約束和唯一性約束。USEteaching1;CREATETABLEteacher(tnochar(6)PRAMARYKEY,tidchar(18)UNIQUE,tnamevarchar(10));7.2.3創建索引索引一經創建，就完全由系統自動選擇和維護，不需要用戶指定使用索引，也不需要用戶執行打開索引或更新索引等操作，所有的工作都由MySQL數據庫管理系統自動完成。但對于讀者來講，應該明白為什么要創建這些索引，即這些索引可能在什么情況下被選擇使用。例如，student表中按姓名列創建的index_sname索引，下面的SQL語句在執行時系統就可以利用此索引來加快查詢速度。SELECTsno,specialtyFROMstudentWHEREsname='張平';7.2.5刪除索引當一個索引不再需要時，可以將其從數據庫中刪除，以釋放當前占用的存儲空間，這些釋放的空間可以由數據庫中的任何對象使用。1．在Navicat中菜單界面刪除索引2．使用SQL語句刪除索引（1）刪除主鍵索引對于主鍵索引，語法格式為：ALTERTABLE表名DROPINDEXPRIMARYKEY;或者DROPINDEX`PRIMARY`ON表名;注意：PRIMARY為系統關鍵字，要用反引號括起來。7.2.5刪除索引（2）刪除其他索引語法格式為：ALTERTABLE表名DROPINDEX索引名;或DROPINDEX索引名ON表名;【例7-18】在teaching1庫中刪除teacher表中的兩個索引。USEteaching1;DROPINDEX`PRIMARY`ONteacher;DROPINDEXtidONteacher;數據庫原理與應用教程-MySQL8.0第8章MySQL編程第8章MySQL編程與程序設計語言相比，SQL語言具有高度非過程化的特點，而為了完成較復雜的操作，就需要進行編程，比如使用常量、變量、表達式、關鍵字等編寫程序。MySQL提供稱為流程控制的特殊關鍵字，用于控制SQL語句、語句塊和存儲過程的執行流。在數據庫開發過程中，函數和存儲過程起著很重要的作用，函數和存儲過程都是由一個或多個SQL語句組成的子程序，可用于封裝代碼以便重復使用。8.1MySQL編程基礎8.1.1標識符標識符是用來標識事物的符號，其作用類似于給事物取的名稱。標識符分為合法標識符和引用標識符。1.合法標識符(1)可以由大小寫形式的字母a-z、數字0-9、美元符號、下劃線、以及范圍在U+0080到U+FFFF之間的Unicode擴展字符構成。(2)可以是以上任何字符開頭，但不允許全部是數字。(3)不能是MySQL關鍵字，關鍵字不區分大小寫。8.1.1標識符2.引用標識符不符合合法標識符格式規則的標識符必須使用引用標識符，比如使用空格等特殊符號，使用MySQL關鍵字以及全部是數字。引用標識符括在反引號``中。8.1.2變量變量可以分為兩大類：系統變量和自定義變量。1.系統變量

由系統定義，非用戶定義，屬于服務器層面。分為全局變量和會話變量。(1)全局變量全局變量由系統提供且預先聲明，名稱前加兩個“@”符號。用戶不能定義但可以使用全局變量，具有super權限的用戶還可以對它們進行修改。(2)會話變量客戶機連接MySQL服務器后，都會產生與之對應的會話。會話期間，MySQL服務實例會生成與該會話對應的會話變量。用戶可修改，連接斷開失效。8.1.2變量2.自定義變量(1)用戶變量用戶變量與數據庫連接有關，如果數據庫實例連接斷開，變量就會消失。用戶變量以”@”開始，形式為”@變量名”，只對當前用戶使用的客戶端生效。用戶變量在定義和使用時要在其名稱前加上標志“@”，使用SET或SELECT語句定義并賦值。8.1.2變量【例8-1】定義一個用戶變量@CurrentDateTime，賦值為CURDATE()函數的值，最后輸出變量@CurrentDateTime的值。SET@CurrentDateTime=CURDATE();SELECT@CurrentDateTimeAS'當前的日期和時間';8.1.2變量(2)局部變量局部變量是根據需要在局部生效的變量，訪問之前，需要DECLARE聲明。可用作存儲過程或函數內的局部變量和輸入參數局部變量的作用范圍是聲明它的BEGIN...END語句塊內。為了與用戶變量進行區分，局部變量不以”@”開頭，它采用普通的標識符名稱。8.1.2變量(2)局部變量局部變量是根據需要在局部生效的變量，訪問之前，需要DECLARE聲明。可用作存儲過程或函數內的局部變量和輸入參數局部變量的作用范圍是聲明它的BEGIN...END語句塊內。為了與用戶變量進行區分，局部變量不以”@”開頭，它采用普通的標識符名稱。8.1.3運算符運算符是一種符號，用來指定要在一個或多個表達式中執行的操作。在MySQL系統中，可以使用的運算符可以分為算術運算符、邏輯運算符、賦值運算符、字符串串聯運算符、按位運算符、一元運算符及比較運算符等。8.1.4注釋兩類注釋符：(1)注釋符“#”用于單行注釋；(2)多行注釋符“/*……*/”。USEteaching;#查詢學生的學號和姓名SELECTsno,snameFROMstudent;/*查詢所有男同學的學號、姓名和專業*/SELECTsno,sname,majorFROMstudentWHEREssex=’男’;8.2流程控制語句與所有的計算機編程語言一樣，MySQL也提供了用于編寫過程性代碼的語法結構，可用來進行順序、分支、循環、函數、存儲過程等程序設計，編寫結構化的模塊代碼，從而提高編程語言的處理能力。8.2.1SET語句一個SET語句可以定義并初始化多個變量。SET語句是順序執行的，將一個表達式賦值給聲明的變量，變量的類型就是表達式的數據類型。8.2.2BEGIN…END語句BEGIN…END語句能夠將多個SQL語句組合成一個語句塊，并將它們視為一個單元處理。語法格式：BEGIN

{sql_statement

|statement_block

}END其中，參數{sql_statement|statement_block}為任何有效的SQL語句或語句塊。8.2.3DELIMITER語句在MySQL命令行客戶端中，如果有一行命令以分號結束，回車后，MySQL將會執行該命令。但有時候，一段命令可能輸入較多的語句，且語句中包含有分號，默認情況下，不可能等到用戶把這些語句全部輸入完之后，再執行整段語句，因為MySQL一遇到分號，就要自動執行。可以使用DELIMITER語句，把DELIMITER后面換成其它符號，如//、$$、;;等等，限制整段程序遇到其換成的符號時才執行。此語句多用在定義子程序、觸發器等MySQL自己內嵌小程序中。8.2.4IF…ELSE語句語法格式：IFBoolean_expression/*條件表達式，可含有SELECT語句*/THEN{sql_statement}/*條件表達式為真時執行的語句*/[ELSE{sql_statement}]/*條件表達式為假時執行的語句*/ENDIF;IF…ELSE語句只能應用于MySQL子程序中，所以在后面的函數、存儲過程、觸發器等中再進行舉例。8.2.5CASE語句使用CASE語句可以進行多個分支的選擇。簡單CASE格式：將某個表達式與一組簡單表達式進行比較（等于），以確定結果。CASEinput_expressionWHENwhen_expression

THENresult_expression[...n

][

ELSEelse_result_expression]

END8.2.5CASE語句【例8-6】以簡單CASE格式查詢所有學生的專業情況，包括學號、姓名以及專業的英文名。USEteaching;SELECTsno,sname,CASEmajorWHEN'計算機科學與技術'THEN'ComputerScienceandTechnology'WHEN'信息安全'THEN'InformationSecurity'WHEN'通信工程'THEN'CommunicationEngineering'WHEN'軟件工程'THEN'SoftwareEngineering'ELSE'ElectronicCommerce'ENDASmajorFROMstudent;8.2.5CASE語句搜索CASE格式：計算一組布爾表達式，以確定結果。CASE

WHENBoolean_expressionTHENresult_expression

[...n]

[ELSEelse_result_expression]

END8.2.5CASE語句【例8-7】以搜索CASE格式查詢所有學生的考試等級，包括學號、課程號以及成績級別(a、b、c、d、e)。USEteaching;SELECTsno,cno,

CASEWHENscore>=90then'a'WHENscore>=80then'b'WHENscore>=70then'c'WHENscore>=60then'd'WHENscore<60then'e'ENDASscore_levelFROMsc;8.2.6WHILE語句如果需要重復執行程序中的一部分語句，可使用WHILE循環語句實現。可使用BREAK和CONTINUE關鍵字在循環內部控制循環中語句的執行。語法格式為：WHILEBoolean_expressionessionDO/*條件表達式*/sql_statement|statement_block/*SQL語句或語句塊構成的循環體*/ENDWHILE;8.3函數函數是由一個或多個SQL語句組成的子程序，可用于封裝代碼以便重復使用。SQL語言提供了豐富的數據操作函數，用以完成各種數據管理工作。MySQL允許用戶創建自己的用戶定義函數。8.3.1系統內置函數MySQL系統內置函數按函數的功能分類可分為系統信息函數、聚合函數、數學函數、字符串函數、日期和時間函數等類型。8.3.1系統內置函數1.數學函數可以滿足數據庫維護人員日常的數值計算需要【例8-8】求下列語句的執行結果。SELECTFLOOR(10.9),FLOOR(-10.9);執行結果為：10,-112.日期和時間函數用于進行時間方面的處理工作【例8-9】計算現在是幾月。SELECTMONTH(CURDATE());8.3.1系統內置函數3．聚合函數聚合函數在結果集中通過對被選列值的收集處理并返回一個數值型的計算結果。4．字符串函數主要用來處理二進制類型的數據和文本類型的數據?！纠?-10】將字符串“Iamastudent”以大寫字母顯示。SELECTUPPER('Iamastudent');8.3.1系統內置函數5.IF函數用于條件判斷的函數，包括IF()和IFNULL()函數。（1）IF()函數IF(condition_expression,result_expression1,result_expression2)【例8-11】查詢teaching數據庫中的sc表，如果成績小于60，則輸出“不及格”，否則輸出“成績合格或為空”。USEteaching;SELECTsno,cno,score,IF(score<60,'不及格','成績合格或為空')ASlevelFROMsc;8.3.1系統內置函數（2）IFNULL()函數IFNULL(expression1,experssion2)【例8-12】查詢teaching數據庫中的sc表，如果成績不為為空，則顯示成績值，否則顯示“缺考”。USEteaching;SELECTsno,cno,IFNULL(score,'缺考')AS成績FROMsc;8.3.2用戶定義函數用戶定義函數可以針對特定應用程序問題提供解決方案。用戶定義函數都是經過封裝的SQL子程序，其他SQL代碼可以調用這些子程序來返回單一的值。1.函數的創建CREATEFUNCTIONfunction_name/*函數名部分*/

([{parameter_namepar_data_type}[,...n]])/*形參定義部分*/RETURNSreturn_data_type/*返回參數的類型*/BEGINfunction_body;/*函數體部分*/

RETURNexpression;/*返回語句*/END8.3.2用戶定義函數【例8-13】求sc表中某門課的平均成績。USEteaching;DELIMITER//CREATEFUNCTIONaverage(cnchar(4))RETURNSfloatBEGINDECLAREaverfloat;SETaver=(SELECTavg(score)FROMscWHEREcno=cn);RETURNaver;END//8.3.2用戶定義函數2.查看函數可以通過可視化管理工具的菜單界面、也可以通過SQL語句查看用戶自定義函數。3.調用函數在其它程序模塊中可以調用函數，語法格式：SELECTfunction_name(parameter_expression1…parameter_expressionn);【例8-15】求C001號課的平均成績。USEteaching;SELECTaverage('C001')AS'C001號課程的平均成績';8.3.2用戶定義函數【例8-16】創建函數，顯示兩門課中平均成績高的那門課的課程號。USEteaching;DELIMITER//CREATEFUNCTIONaverage1(cn1char(4),cn2char(4))RETURNSchar(4)BEGINDECLAREaver1,aver2float;SETaver1=(SELECTavg(score)FROMscWHEREcno=cn1);SETaver2=(SELECTavg(score)FROMscWHEREcno=cn2);8.3.2用戶定義函數IFaver1>=aver2/*IF…ELSE語句的應用*/THENRETURNcn1;ELSERETURNcn2;ENDIF;END//【例8-17】判斷C001和C002號課哪個平均成績高。USEteaching;SELECTaverage1('C001','C002')AS'平均成績高的課程';8.3.2用戶定義函數4.刪除函數可以通過可視化管理工具的菜單界面、也可以通過SQL語句刪除用戶自定義函數?！纠?-18】通過DROPFUNCTION語句刪除average函數。USEteaching;DROPFUNCTIONaverage;8.4存儲過程存儲過程就是一個具有獨立功能的子程序，以特定的名稱存儲在數據庫中。8.4.1存儲過程概述存儲過程是SQL語句和可選流程控制語句的預編譯集合，它以一個名稱存儲并作為一個單元處理，能夠提高系統的應用效率和執行速度。存儲過程是一種獨立存儲在數據庫內的對象，可以接受輸入參數、輸出參數，可以返回單個值或多個結果集，也可以完成數據庫中數據更新，由應用程序通過調用執行。8.4.1存儲過程概述存儲過程的優點：(1)模塊化程序設計。只需創建一次，可在程序中調用任意次。可由在數據庫編程方面有專長的人員創建，并可獨立于程序源代碼而單獨修改。

(2)加快SQL語句的執行速度。創建存儲過程時對其進行分析和優化并預先編譯好放在數據庫內，減少編譯語句所花的時間。(3)減少網絡流量。一條執行過程代碼的單獨語句。(4)更高地安全性?？梢允谟栌脩魣绦写鎯^程的權限，而不授予其直接訪問數據庫對象（表、視圖等）的權限。8.4.2