《MySQL數(shù)據(jù)庫應(yīng)用與管理（第3版）》 課件 魯大林 第5章 數(shù)據(jù)查詢

數(shù)據(jù)查詢

SELECT語句

本節(jié)內(nèi)容1.SELECT語句基本語法2.查詢示例數(shù)據(jù)庫SELECT語句SELECT語句主要用于數(shù)據(jù)的查詢檢索，是SQL語言的核心，也是使用頻率最高的一條語句。SELECT語句可以讓數(shù)據(jù)庫服務(wù)器根據(jù)用戶的要求，從數(shù)據(jù)庫的表中檢索出所需要的數(shù)據(jù)，并按照用戶指定的格式進(jìn)行整理并返回。1.SELECT語句基本語法SELECT語句SELECT語句的語法格式如下：SELECT[ALL|DISTINCT]*|字段列表FROM表名[WHERE查詢條件][GROUPBY分組字段[HAVING分組條件]][ORDERBY排序字段[ASC|DESC]][LIMIT[初始位置,]記錄數(shù)]1.SELECT語句基本語法SELECT語句說明：SELECT子句：用來指定查詢返回的字段。星號（*）表示返回所有字段，并按照表中定義的字段順序顯示查詢結(jié)果集；也可指定字段列表，以逗號隔開，各字段在SELECT子句中的循序決定了它們在查詢結(jié)果集中的順序。使用DISTINCT關(guān)鍵字可以取消重復(fù)的數(shù)據(jù)記錄。FROM子句：用來指定數(shù)據(jù)來源的表。WHERE子句：用來限定返回行的查詢條件。GROUPBY子句：用來指定查詢結(jié)果的分組條件。ORDERBY子句：用來指定結(jié)果集的排序方式。ASC表示升序，可省略；DESC表示降序。LIMIT子句：用來限制SELECT語句返回的記錄數(shù)。1.SELECT語句基本語法SELECT語句以“學(xué)生管理系統(tǒng)”數(shù)據(jù)庫StuInfo作為學(xué)習(xí)本章內(nèi)容的示例數(shù)據(jù)庫，該數(shù)據(jù)庫中的數(shù)據(jù)表如下：學(xué)生表（student） student(id,sNo,sName,sex,birthday,deptName,remark)課程表（course） course(id,cNo,cName,credit,remark)成績表（score） score(id,sid,cid,grade)2.查詢示例數(shù)據(jù)庫謝謝數(shù)據(jù)查詢

簡單查詢

本節(jié)內(nèi)容1.選擇字段進(jìn)行查詢2.使用比較運算符進(jìn)行查詢3.使用邏輯運算符進(jìn)行查詢4.使用LIKE進(jìn)行模糊查詢5.使用BETWEEN…AND進(jìn)行范圍比較查詢本節(jié)內(nèi)容6.使用IN進(jìn)行范圍比對查詢7.使用空值（NULL）的判斷進(jìn)行查詢8.使用ORDERBY子句排序查詢的記錄9.使用LIMIT子句限制返回記錄的行數(shù)10.使用DISTINCT關(guān)鍵字過濾重復(fù)的記錄簡單查詢（1）選擇所有字段在SELECT子句中可以使用星號（*），顯示表中所有的字段。其語法格式如下： SELECT*FROM表名1.選擇字段進(jìn)行查詢操作案例知識點概述示例5-1運行結(jié)果如下：顯示student表中的所有信息。簡單查詢（2）選擇指定字段選擇指定字段的語法格式如下： SELECT字段名1[,字段名2,…,字段名n]FROM表名說明：字段的順序可以與表中定義的字段順序不同，字段與字段之間使用逗號分隔。1.選擇字段進(jìn)行查詢操作案例知識點概述示例5-2運行結(jié)果如下：從student表中查詢出班級名稱（deptName），學(xué)號（sNo）、姓名（sName）、和性別（sex）的學(xué)生信息。說明：在數(shù)據(jù)查詢時，字段的顯示順序由SELECT子句指定，該順序可以和表中定義的字段順序不同，這并不影響數(shù)據(jù)在表中的存儲順序。簡單查詢（3）定義字段別名默認(rèn)情況下返回的查詢結(jié)果以字段名作為列標(biāo)題的，可以為返回的字段指定一個新的列標(biāo)題，也可給通過計算產(chǎn)生的新列指定一個列標(biāo)題。其語法格式如下： SELECT字段名1[AS]列標(biāo)題1[,字段名2[AS]列標(biāo)題2,…]FROM表名說明：AS關(guān)鍵字可以省略。1.選擇字段進(jìn)行查詢操作案例知識點概述示例5-3運行結(jié)果如下：以“學(xué)號姓名性別出生日期”作為列標(biāo)題顯示學(xué)生信息。簡單查詢在實際工作中，大部分查詢并不是針對表中所有數(shù)據(jù)記錄的查詢，而是要找出滿足某些條件的數(shù)據(jù)記錄。此時我們可以在SELECT語句中使用WHERE子句，其語法格式如下： SELECT*|字段列表FROM表名

WHERE查詢條件說明：查詢條件可以是：比較表達(dá)式、邏輯表達(dá)式、以及其他一些謂詞構(gòu)成的表達(dá)式（字符串模糊匹配LIKE、數(shù)據(jù)范圍BETWEEN、列表數(shù)據(jù)IN、空值判定ISNULL等）。2.使用比較運算符進(jìn)行查詢簡單查詢WHERE子句允許使用的比較運算符如下表所示：2.使用比較運算符進(jìn)行查詢操作案例知識點概述示例5-4運行結(jié)果如下：查詢student表中女學(xué)生的信息。操作案例知識點概述示例5-5運行結(jié)果如下：查詢course表中超過4個學(xué)分（credit）的課程信息。簡單查詢WHERE子句允許使用的邏輯運算符如下表所示：3.使用邏輯運算符進(jìn)行查詢操作案例知識點概述示例5-6運行結(jié)果如下：查詢student表中1995年出生的學(xué)生信息。操作案例知識點概述示例5-7運行結(jié)果如下：查詢student表中“軟件131”班級的女生、以及其他班級的男生。簡單查詢在WHERE子句中，通過LIKE關(guān)鍵字與“%”和“_”兩個通配符的使用，可以對數(shù)據(jù)表中的數(shù)據(jù)進(jìn)行模糊查詢。這兩個通配符的含義如下所示：百分號（%）：表示匹配0個或者任意多個字符。下劃線（_）：表示匹配任意一個字符。說明：如果需要查詢出包含下劃線“_”的數(shù)據(jù)，在進(jìn)行模糊查詢時，需要加一個“\”進(jìn)行轉(zhuǎn)義，表示為“\_”的形式。4.使用LIKE進(jìn)行模糊查詢操作案例知識點概述示例5-8運行結(jié)果如下：從student表中檢索出所有姓“楊”的學(xué)生信息。操作案例知識點概述示例5-9運行結(jié)果如下：從course表中檢索出課程名稱（cName）中包含“設(shè)計”的課程信息。操作案例知識點概述示例5-10運行結(jié)果如下：從student表中檢索出姓名（sName）的第二個字是“偉”和“先”的學(xué)生信息。簡單查詢在WHERE子句中，可以使用BETWEENAND關(guān)鍵字對指定字段的某一范圍內(nèi)的數(shù)據(jù)進(jìn)行比較查詢，其與使用“>=”且“<=”的功能一樣。其語法格式如下：

字段名[NOT]BETWEEN值1AND值2說明：指定字段的值（不）在值1和值2之間。5.使用BETWEEN…AND進(jìn)行范圍比較查詢操作案例知識點概述示例5-11運行結(jié)果如下：查詢student表中1995年出生的學(xué)生信息（使用BETWEENAND關(guān)鍵字）。操作案例知識點概述示例5-12運行結(jié)果如下：從score表中查詢出成績（grade）不在60-89分之間的學(xué)生成績信息。簡單查詢?nèi)绻侄蔚娜≈捣秶皇且粋€連續(xù)的區(qū)間，而是一些離散的值，可以使用IN關(guān)鍵字對指定字段進(jìn)行范圍比對查詢。其語法格式如下：

字段名[NOT]IN(值1[,值2,值3,…])說明：指定字段的值（不）在括號中列出的值之中。6.使用IN進(jìn)行范圍比對查詢操作案例知識點概述示例5-13運行結(jié)果如下：查詢student表中學(xué)號（sNo）為1308013101、1309122503、1312054904的學(xué)生信息。簡單查詢空值（NULL）是一個特殊的值，它僅僅是一個符號，不等于空字符串，也不等于0?？罩蹬袛嗟恼Z法格式如下：

字段名IS[NOT]NULL7.使用空值（NULL）的判斷進(jìn)行查詢操作案例知識點概述示例5-14運行結(jié)果如下：檢索course表中備注（remark）為空的課程記錄。簡單查詢在通常情況下，數(shù)據(jù)庫中的數(shù)據(jù)記錄行在顯示時是無序的，它按照數(shù)據(jù)記錄插入數(shù)據(jù)庫時的順序排列，因此用SELECT語句查詢的結(jié)果也是無序的。使用ORDERBY子句可以將查詢結(jié)果進(jìn)行排序顯示。其語法格式如下：

SELECT*|字段列表FROM表名 [WHERE查詢條件]

ORDERBY字段名1[ASC|DESC][,字段名2[ASC|DESC]][,…]8.使用ORDERBY子句排序查詢的記錄簡單查詢說明：在默認(rèn)情況下，ORDERBY子句按升序進(jìn)行排序，即默認(rèn)使用的是ASC關(guān)鍵字，如果特別要求按降序進(jìn)行排列，必須使用DESC關(guān)鍵字。當(dāng)ORDERBY子句指定了多個排序字段時，系統(tǒng)先按照ORDERBY子句中第一個字段的順序排列，當(dāng)該字段出現(xiàn)相同的值時，再按照第二個字段的順序排列，依次類推。8.使用ORDERBY子句排序查詢的記錄操作案例知識點概述示例5-15運行結(jié)果如下：查詢student表中的男生信息，按照出生日期（birthday）的降序排列。操作案例知識點概述示例5-16運行結(jié)果如下：查詢student表中的學(xué)生信息，按照姓名（sName）的升序排列。說明：從以上排序后的查詢結(jié)果中可以看出，并沒有按照姓名（sName）的中文拼音的順序進(jìn)行排序。這是因為，當(dāng)數(shù)據(jù)表采用的是utf8字符集時，對于中文字符串字段的排序，不會按照中文拼音的順序進(jìn)行排序。其解決方法是把ORDERBY子句的語法格式更改如下：ORDERBYCONVERT(字段名usinggbk|gb2312)[ASC|DESC]。操作案例知識點概述示例5-17運行結(jié)果如下：查詢student表中的學(xué)生信息，按照姓名（sName）的中文拼音的升序排列。操作案例知識點概述示例5-18運行結(jié)果如下：查詢student表中的數(shù)據(jù)，先按班級（deptName）的升序排列，相同的班級再按照出生日期（birthday）的降序排列。簡單查詢在對數(shù)據(jù)進(jìn)行查詢時，如果返回的記錄數(shù)很多，那么不僅檢索的速度慢，也不便于用戶閱讀。使用LIMIT子句，可以限制SELECT語句返回的記錄數(shù)。LIMIT子句通常位于SELECT語句的最后面，其語法格式如下：

SELECT*|字段列表FROM表名 [WHERE查詢條件] [ORDERBY排序字段[ASC|DESC]]

[LIMIT[初始位置,]記錄數(shù)]9.使用LIMIT子句限制返回記錄的行數(shù)簡單查詢說明：初始位置指定從查詢結(jié)果集中的哪一條記錄開始返回，如果省略，則表示從第1條記錄開始返回，第1條記錄的位置為0。記錄數(shù)指定返回的記錄條數(shù)。9.使用LIMIT子句限制返回記錄的行數(shù)操作案例知識點概述示例5-19運行結(jié)果如下：返回年齡最小的5位同學(xué)的信息。操作案例知識點概述示例5-20運行結(jié)果如下：返回課程ID（cId）為“1”課程的第2~4名學(xué)生的成績。簡單查詢在對數(shù)據(jù)進(jìn)行查詢時，如果返回的查詢結(jié)果中包含重復(fù)的記錄，可以使用DISTINCT關(guān)鍵字取消重復(fù)的數(shù)據(jù)，只返回其中的一條。其語法格式如下：

SELECTDISTINCT字段列表FROM表名說明：DISTINCT關(guān)鍵字作用的范圍是整個查詢的字段列表，而不是僅僅單獨一列。10.使用DISTINCT關(guān)鍵字過濾重復(fù)的記錄操作案例知識點概述示例5-21運行結(jié)果如下：查詢student中的班級，如果有多個相同的班級，只需顯示一個即可。操作案例知識點概述示例5-22運行結(jié)果如下：查詢score表，顯示選修了課程的學(xué)生ID，如果有多個相同的學(xué)生ID，只需顯示一個即可。謝謝數(shù)據(jù)查詢

高級查詢

本節(jié)內(nèi)容1.使用內(nèi)連接（INNERJOIN）進(jìn)行多表查詢2.使用外連接（OUTERJOIN）進(jìn)行多表查詢3.使用統(tǒng)計函數(shù)對數(shù)據(jù)進(jìn)行統(tǒng)計匯總4.使用GROUPBY子句對數(shù)據(jù)進(jìn)行分組匯總本節(jié)內(nèi)容5.使用HAVING子句對分組匯總結(jié)果進(jìn)行篩選6.子查詢的返回值為單列單值的嵌套查詢7.子查詢的返回值為單列多值的嵌套查詢8.使用EXISTS關(guān)鍵字創(chuàng)建子查詢高級查詢關(guān)系數(shù)據(jù)庫在進(jìn)行數(shù)據(jù)表設(shè)計時，為了減少冗余，確保數(shù)據(jù)一致性、完整性，要求數(shù)據(jù)表的設(shè)計符合規(guī)范（比如3NF），為了遵循這些規(guī)范，往往需要將數(shù)據(jù)分離到多張表中。然而在實際應(yīng)用中，又往往需要將多張表的相關(guān)數(shù)據(jù)提取、聚合后一起提供給用戶，即需要多表查詢。多表查詢的本質(zhì)是多張表通過關(guān)聯(lián)的列的連接，所以多表查詢也稱為連接查詢。1.使用內(nèi)連接（INNERJOIN）進(jìn)行多表查詢高級查詢多表（連接）查詢的語法格式有如下兩種：1.使用內(nèi)連接（INNERJOIN）進(jìn)行多表查詢第一種語法格式： SELECT*|字段列表

FROM表名1 [連接類型]JOIN表名2ON連接條件 [[連接類型]JOIN表名3ON連接條件][…] WHERE查詢條件;第二種語法格式： SELECT*|字段列表 FROM表名1,表名2[,表名3,…表名n] WHERE連接條件

AND查詢條件;高級查詢說明：查詢時所有的字段都必須要明確，為了區(qū)分多張表中出現(xiàn)的重復(fù)字段名，可以在字段列表中使用“表名.字段名”的形式；星號（*）表示的是多張表中的所有字段，如果要指定某一張表中是所有字段，可以使用“表名.*”的形式。連接類型主要包括：內(nèi)連接（INNER）、左外連接（LEFTOUTER）、右外連接（RIGHTOUTER）等。為了增加可讀性，可以對數(shù)據(jù)表使用別名進(jìn)行引用。表的別名的使用方法是在表名的后面直接加上一個別名，原名與別名之間用空格隔開；一旦使用了別名代替某個表，則在連接時必須用表的別名，不能再用表的原名。1.使用內(nèi)連接（INNERJOIN）進(jìn)行多表查詢高級查詢內(nèi)連接（INNERJOIN）查詢是最常用的多表查詢形式。內(nèi)連接（INNERJOIN）是指多個表通過連接條件中共享列的值進(jìn)行的比較連接，INNER關(guān)鍵字可以省略，當(dāng)未指明連接類型時，默認(rèn)為內(nèi)連接。內(nèi)連接值顯示兩個表中所有匹配數(shù)據(jù)的行。1.使用內(nèi)連接（INNERJOIN）進(jìn)行多表查詢操作案例知識點概述示例5-23運行結(jié)果如下：使用兩種語法格式查詢所有女生的學(xué)號、姓名、性別、課程ID和成績。操作案例知識點概述示例5-24運行結(jié)果如下：使用兩種語法格式查詢學(xué)號（sNo）為“1308013101”學(xué)生的學(xué)號、姓名、性別、班級、課程名稱和成績。高級查詢外連接顯示包含來自一個表中所有行和來自另一個表中匹配行的結(jié)果集。2.使用外連接（OUTERJOIN）進(jìn)行多表查詢高級查詢外連接主要又分為左外連接和右外連接，說明如下：左外連接（LEFTOUTERJOIN）返回LEFTOUTERJOIN關(guān)鍵字左側(cè)指定的表（左表）的所有行和右側(cè)指定的表（右表）的匹配的行。對于來自左表中的行，在右表中沒有發(fā)現(xiàn)匹配的行，那么在來自右表中獲得數(shù)據(jù)的列中將顯示NULL值。OUTER關(guān)鍵字可以省略。右外連接（RIGHTOUTERJOIN）即在連接兩表時，結(jié)果集包含RIGHTOUTERJOIN關(guān)鍵字右側(cè)指定的表（右表）的所有行以及左表匹配的行；對于來自右表的行，如果左表無匹配，則左表的數(shù)據(jù)列將顯示NULL。OUTER關(guān)鍵字可以省略。2.使用外連接（OUTERJOIN）進(jìn)行多表查詢操作案例知識點概述示例5-25運行結(jié)果如下：使用兩種方式查詢“網(wǎng)絡(luò)131”班學(xué)生的學(xué)號、姓名、性別、班級、課程ID和成績（包括沒有選修課程的學(xué)生信息）。高級查詢MySQL不僅可以查詢返回滿足條件的記錄，還可以對數(shù)據(jù)進(jìn)行統(tǒng)計匯總。3.使用統(tǒng)計函數(shù)對數(shù)據(jù)進(jìn)行統(tǒng)計匯總操作案例知識點概述示例5-26運行結(jié)果如下：統(tǒng)計student表中的男生人數(shù)。操作案例知識點概述示例5-27運行結(jié)果如下：統(tǒng)計course表中的課程總數(shù)、以及備注（remark）不為空的課程總數(shù)。操作案例知識點概述示例5-28運行結(jié)果如下：通過score表統(tǒng)計已選修課程的學(xué)生人數(shù)。操作案例知識點概述示例5-29運行結(jié)果如下：統(tǒng)計學(xué)號（sNo）為“1308013101”的學(xué)生選修課程的門數(shù)、最高分、最低分、平均分和總分。高級查詢使用GROUPBY子句，可以顯示分組的匯總數(shù)據(jù)。該子句的功能是按照指定的字段，先將數(shù)據(jù)分成多個組（相同字段的值為一組），然后對每個組匯總出一個數(shù)據(jù)。結(jié)果集中每個組都有一行匯總數(shù)據(jù)。其語法格式為： SELECT字段名1[,字段名2,…],統(tǒng)計函數(shù) FROM表名 [WHERE查詢條件]

GROUPBY字段名1[,字段名2,…]說明：GROUPBY子句用來指定分組的字段，這些字段還必須要全部包含在SELECT子句中。4.使用GROUPBY子句對數(shù)據(jù)進(jìn)行分組匯總操作案例知識點概述示例5-30運行結(jié)果如下：分組統(tǒng)計男、女學(xué)生的人數(shù)。高級查詢使用HAVING子句，可以用來指定結(jié)果集的組需要滿足的條件，即對結(jié)果集的組進(jìn)行篩選，僅顯示滿足條件的分組統(tǒng)計結(jié)果。其語法格式為： SELECT字段名1[,字段名2,…],統(tǒng)計函數(shù) FROM表名 [WHERE查詢條件] GROUPBY字段名1[,字段名2,…] [HAVING分組條件]5.使用HAVING子句對分組匯總結(jié)果進(jìn)行篩選高級查詢說明：如果同時具有WHERE子句、GROUPBY子句、HAVING子句時，其執(zhí)行順序是：首先執(zhí)行WHERE子句，然后執(zhí)行GROUPBY子句，最后再執(zhí)行HAVING子句。即先使用WHERE查詢出滿足條件的記錄；然后使用GROUPBY對這些滿足條件的數(shù)據(jù)按照指定的字段分組匯總；最后再使用HAVING子句篩選出符合條件的組。5.使用HAVING子句對分組匯總結(jié)果進(jìn)行篩選操作案例知識點概述示例5-31運行結(jié)果如下：分組統(tǒng)計被選修過1次以上的課程編號、選修次數(shù)和平均分。說明：語句中的“HAVING選修次數(shù)>1”也可以更改為“HAVINGCOUNT(*)>1”。操作案例知識點概述示例5-32運行結(jié)果如下：分組統(tǒng)計“軟件131”班級中選修超過2門課程、且平均成績高于60分的學(xué)生學(xué)號、姓名、選修門數(shù)和平均分，并按照平均分降序排列。高級查詢在關(guān)系型數(shù)據(jù)庫的應(yīng)用中，也經(jīng)常會涉及到嵌套查詢的使用。嵌套查詢是指一個SELECT語句的WHERW子句中還包含另外一個SELECT語句，外層的SELECT語句稱為外部查詢或父查詢，內(nèi)層的SELECT語句稱為內(nèi)部查詢或子查詢，子查詢需要使用圓括號“（）”括起來。SQL語言允許多層嵌套查詢，即一個子查詢中還可以有其他子查詢。嵌套查詢的求解方法是由里向外處理，即每個子查詢都是在上一級查詢之前求解，子查詢的結(jié)果用于建立其父查詢的查詢條件。如果子查詢的返回值為單列單值，可以通過使用“=”、“!=”、“>”、“<”等比較運算符直接與父查詢的字段值進(jìn)行比較。6.子查詢的返回值為單列單值的嵌套查詢操作案例知識點概述示例5-33運行結(jié)果如下：查詢與學(xué)號（sNo）為“1308013101”的同學(xué)在同一個班級的學(xué)生名單。操作案例知識點概述示例5-34運行結(jié)果如下：查詢選修課程編號（cNo）為“01001”的課程、且成績超過該課程平均分的學(xué)生的學(xué)號、姓名、班級、課程名稱和成績。高級查詢?nèi)绻硬樵兊姆祷刂禐閱瘟卸嘀?，可以使用IN或NOTIN關(guān)鍵字，即表示在或者不在子查詢的結(jié)果集中。7.子查詢的返回值為單列多值的嵌套查詢操作案例知識點概述示例5-35運行結(jié)果如下：查詢選修課程編號（cNo）為“01001”的課程的學(xué)生名單。操作案例知識點概述示例5-36運行結(jié)果如下：查詢學(xué)號為“1308013101”的學(xué)生選修的課程信息。操作案例知識點概述示例5-37運行結(jié)果如下：查詢沒有選修課程的女生名單。高級查詢?nèi)绻硬樵兊姆祷刂禐槎嗔袛?shù)據(jù)，可以使用EXISTS或NOTEXISTS關(guān)鍵字。在WHERE子句中使用EXISTS關(guān)鍵字，表示判斷子查詢的結(jié)果集是否為空，如果子查詢至少返回一行時，WHERE子句的條件為真，返回TRUE；否則條件為假，返回FALSE。加上關(guān)鍵字NOT，則剛好相反。8.使用EXISTS關(guān)鍵字創(chuàng)建子查詢操作案例知識點概述示例5-38運行結(jié)果如下：查詢選修課程的女生名單，使用關(guān)鍵字EXISTS。說明：EXISTS關(guān)鍵字的前面沒有字段名或其他表達(dá)式。由EXISTS引出的子查詢，其選擇字段表達(dá)式通常都使用星號（*），這是因為，帶EXISTS的子查詢只是測試是否存在符合子查詢中指定條件的行，所以不必列出字段名。謝謝數(shù)據(jù)查詢

帶子查詢的數(shù)據(jù)更新

本節(jié)內(nèi)容1.拷貝表結(jié)構(gòu)及數(shù)據(jù)到新表2.向表中插入子查詢結(jié)果集3.帶子查詢的修改語句4.帶子查詢的刪除語句帶子查詢的數(shù)據(jù)更新拷貝表結(jié)構(gòu)及數(shù)據(jù)到新表使用的是