Access 2016數(shù)據(jù)庫(kù)教程課件

第5章SQL查詢第5章SQL查詢【學(xué)習(xí)目標(biāo)】掌握SELECT語(yǔ)句的格式。掌握使用SELECT語(yǔ)句創(chuàng)建單表查詢和多表查詢。掌握使用SQL實(shí)現(xiàn)數(shù)據(jù)定義、數(shù)據(jù)操作和特定查詢。結(jié)構(gòu)化查詢語(yǔ)言（StructureQueryLanguage，SQL）是操作關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言，SQL具有使用方便、功能強(qiáng)大的特點(diǎn)，因此應(yīng)用廣泛。SQL查詢是使用SQL語(yǔ)句創(chuàng)建的查詢。查詢對(duì)象本質(zhì)上是一條SQL語(yǔ)言編寫的命令。SELECT語(yǔ)句SELECT語(yǔ)句是對(duì)關(guān)系數(shù)據(jù)庫(kù)的表作選擇查詢的一個(gè)命令，可以返回指定的數(shù)據(jù)表中的全部或部分滿足條件的記錄。第5章SQL查詢?cè)O(shè)計(jì)視圖SQL視圖5.1SQL視圖在“SQL視圖”中，用戶可以直接輸入SQL語(yǔ)句或編輯已有的SQL語(yǔ)句，保存后同樣可以得到一個(gè)查詢對(duì)象。SELECT[ALL|DISTINCT|TOPN]*|<字段名稱>[AS別名][,<字段名稱>[AS別名],…]FROM<表名>|<查詢名>[WHERE<條件表達(dá)式>][GROUPBY<字段名稱>[,<字段名稱>,…][HAVING<條件表達(dá)式>]][ORDERBY<字段名稱>[ASC|DESC][,<字段名稱>[ASC|DESC],…]]功能：從FROM子句指定的表中返回一個(gè)滿足WHERE子句指定條件的記錄集，該記錄集中只包含SELECT語(yǔ)句中指定的字段。5.2.1SELECT語(yǔ)句1．SELECT語(yǔ)句的一般格式命令說(shuō)明：（1）ALL：查詢結(jié)果是滿足條件的全部記錄，默認(rèn)值是ALL。（2）字段名稱：字段名稱之間使用“,”分隔。

字段可以來(lái)自單個(gè)表，也可以來(lái)自多個(gè)表。

多個(gè)表的字段格式為：表名.字段名稱（3）FROM：指定查詢的數(shù)據(jù)源。可以是單表，也可是多表。（4）WHERE：指定查詢的條件。（5）GROUPBY：用于對(duì)查詢結(jié)果進(jìn)行分組。（6）ORDERBY：用于對(duì)查詢結(jié)果進(jìn)行排序。ASC表示升序，是默認(rèn)值DESC表示降序5.2.1SELECT語(yǔ)句Select語(yǔ)句的執(zhí)行順序：From

Where

GroupBy

Having

OrderBy

Select5.2.1SELECT語(yǔ)句SQL語(yǔ)句的所有子句既可以寫在同一行中，也可以分成多行書寫。命令中的大寫字母與小寫字母含義相同。【例5-1】查詢學(xué)生表中所有學(xué)生的學(xué)號(hào)、姓名和出生日期。Select學(xué)號(hào)，姓名，出生日期From學(xué)生表SQL命令中的字母大寫與小寫含義相同。5.2.1SELECT語(yǔ)句2．使用SELECT語(yǔ)句創(chuàng)建簡(jiǎn)單查詢【例5-2】查詢“課程表”中全部記錄。Select*From課程表SelectDistinct班級(jí)From學(xué)生表消除查詢結(jié)果中重復(fù)的記錄【例5-3】查詢“學(xué)生表”中所包含的班級(jí)名稱。5.2.1SELECT語(yǔ)句【例5-4】查詢學(xué)生的學(xué)號(hào)、姓名和年齡Select學(xué)號(hào),姓名,Year(date())-year(出生日期)As年齡From學(xué)生表查詢的列可以是計(jì)算表達(dá)式?？梢允褂肁s指定顯示結(jié)果列標(biāo)題名稱。5.2.1SELECT語(yǔ)句【例5-5】查詢學(xué)生表中入學(xué)總分前3名學(xué)生的學(xué)號(hào)、姓名、院系代碼和入學(xué)總分。SelectTop3

學(xué)號(hào),姓名,院系代碼,入學(xué)總分From學(xué)生表OrderBy入學(xué)總分DescASC：升序，是默認(rèn)值DESC：降序5.2.1SELECT語(yǔ)句【例5-6】查詢學(xué)生表中所有女生的學(xué)號(hào)、姓名和性別。Select學(xué)號(hào)，姓名，性別From學(xué)生表

Where性別=“女"指定查詢條件5.2.1SELECT語(yǔ)句Select學(xué)號(hào),姓名,性別,入學(xué)總分

From學(xué)生表

WhereLeft(姓名,1)=“李”And入學(xué)總分>=600【例5-7】查詢學(xué)生表中入學(xué)總分在600（含）以上的姓李的學(xué)生的學(xué)號(hào)、姓名、性別和入學(xué)總分。5.2.1SELECT語(yǔ)句在實(shí)際應(yīng)用中，往往不僅要求將表中的記錄查詢出來(lái)，還需要在原有數(shù)據(jù)的基礎(chǔ)上，通過(guò)計(jì)算來(lái)輸出統(tǒng)計(jì)結(jié)果。函數(shù)名功能Sum()對(duì)數(shù)字型字段求和Avg()對(duì)數(shù)字型字段求均值Count()統(tǒng)計(jì)表中記錄個(gè)數(shù)Max()求最大值Min()求最小值First()返回分組記錄中的第一條記錄中的字段值Last()返回分組記錄中的最后一條記錄中的字段值5.2.2數(shù)據(jù)分組和聚合函數(shù)【例5-8】統(tǒng)計(jì)學(xué)生表中不同性別的學(xué)生人數(shù)。Select性別,Count(學(xué)號(hào))As人數(shù)From學(xué)生表Groupby性別使用GroupBy子句后Select子句中只能有GroupBy中指定的列，其他列必須使用聚合函數(shù)。GroupBy班級(jí)GroupBy院系代碼5.2.2數(shù)據(jù)分組和聚合函數(shù)使用GroupBy子句可以對(duì)表中記錄進(jìn)行分組，然后使用聚合函數(shù)進(jìn)行分組統(tǒng)計(jì)。5.2.2數(shù)據(jù)分組和聚合函數(shù)使用“GroupBy”子句后，“Select”子句中除了“GroupBy”中指定的字段，其他字段必須使用聚合函數(shù)，形式如下。SelectA,聚合函數(shù),

,聚合函數(shù)From表名GroupByA錯(cuò)誤！【例5-9】在學(xué)生表中按照院系代碼統(tǒng)計(jì)不同性別的學(xué)生人數(shù)。Select性別,政治面貌,Count(學(xué)號(hào))As人數(shù)From學(xué)生表Groupby性別,政治面貌GroupBy子句先按照“性別”字段進(jìn)行分組，具有相同性別的記錄被分配在同一個(gè)組中；然后分別在男組和女組內(nèi)按照“政治面貌”再次分組。Select前兩個(gè)字段與GroupBy指定的兩個(gè)分組字段是一致的，第三個(gè)使用聚合函數(shù)Count()。5.2.2數(shù)據(jù)分組和聚合函數(shù)Select院系代碼,性別,Count(學(xué)號(hào))As人數(shù)From學(xué)生表GroupBy院系代碼【例5-10】在學(xué)生表中統(tǒng)計(jì)男女生入學(xué)總分的最高分、最低分和平均值（保留2位小數(shù)）Select性別,Max(入學(xué)總分)As最高分,Min(入學(xué)總分)As最低分,Round(Avg(入學(xué)總分),2)As平均值From學(xué)生表GroupBy性別四舍五入函數(shù)5.2.2數(shù)據(jù)分組和聚合函數(shù)如果查詢結(jié)果的字段來(lái)自多張不同的表，則需要通過(guò)連接運(yùn)算將多張表進(jìn)行連接。連接運(yùn)算主要分成：內(nèi)連接外連接5.2.3多表連接查詢5.2.3多表連接查詢1．內(nèi)連接內(nèi)連接是應(yīng)用最廣泛的連接運(yùn)算，結(jié)果只包含兩張表中連接字段值相同的記錄行，是等值連接。使用“InnerJoin”就可以將兩張表內(nèi)連接在一起。所以使用SQL創(chuàng)建查詢前，不需要先建立表間的關(guān)聯(lián)關(guān)系。表名.字段名稱對(duì)于多張表中共有的字段，該字段名稱前必須加表名，中間用“.”間隔，格式為“表名.字段名稱”。對(duì)于非共有的字段，可以直接寫字段名稱。【例5-12】按院系名稱統(tǒng)計(jì)學(xué)生表中男學(xué)生的入學(xué)平均分，并按照平均分降序顯示。Select院系名稱,Avg(入學(xué)總分)As平均分From院系代碼表InnerJoin學(xué)生表On院系代碼表.院系代碼=學(xué)生表.院系代碼Where性別="男"GroupBy院系名稱OrderByavg(入學(xué)總分)DESC由于“院系代碼”是學(xué)生表和院系代碼表共有的字段，所以必須加上表名;其他字段均不同名,可以省略表名按照語(yǔ)句的執(zhí)行順序，先執(zhí)行InnerJoin內(nèi)連接操作，將“院系代碼表”和“學(xué)生表”按院系代碼字段進(jìn)行等值連接5.2.3多表連接查詢5.2.3多表連接查詢2．外連接外連接是從一個(gè)表中選擇全部的記錄，從另一個(gè)表中選擇與連接字段匹配的記錄行。外連接的方式有兩種：左連接右連接Select院系名稱,學(xué)號(hào),姓名From院系代碼表

LeftJoin學(xué)生表On院系代碼表.院系代碼=學(xué)生表.院系代碼左外連接中顯示出了所有的院系名稱，包含沒有學(xué)生的“可再生能源學(xué)院”，與其對(duì)應(yīng)的學(xué)生表中的“學(xué)號(hào)”“姓名”字段均為空。5.2.3多表連接查詢Select課程編號(hào),成績(jī),學(xué)生表.學(xué)號(hào),姓名From選課成績(jī)表

RightJoin學(xué)生表On選課成績(jī)表.學(xué)號(hào)=學(xué)生表.學(xué)號(hào)右外連接表示包含“學(xué)生表”的全部記錄，以及“選課成績(jī)表”中具有相同“學(xué)號(hào)”的記錄。使用該方法可以查詢出沒有選課的學(xué)生?？梢钥闯?，有“張虎”等5位學(xué)生沒有選課?！纠?-13】查詢每門課程的平均分（保留1位小數(shù)）、最高分和最低分。Select課程名稱,Round(Avg(成績(jī)),1)As平均分,Max(成績(jī))As最高分,Min(成績(jī))As最低分From課程表InnerJoin選課成績(jī)表On課程表.課程編號(hào)=選課成績(jī)表.課程編號(hào)GroupBy課程名稱涉及到“選課成績(jī)表”和“課程表”，先執(zhí)行InnerJoin內(nèi)連接操作將兩張表進(jìn)行等值連接然后使用GroupBy子句按照課程名稱進(jìn)行分組再計(jì)算每個(gè)組內(nèi)包含成績(jī)的平均值、最大值和最小值。5.2.3SELECT多表連接查詢【例5-14】查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)（保留2位小數(shù)），查詢結(jié)果按平均成績(jī)降序排序。Select學(xué)生表.學(xué)號(hào),First(學(xué)生表.姓名)As姓名,Round(Avg(成績(jī)),2)As平均成績(jī)From學(xué)生表InnerJoin選課成績(jī)表On學(xué)生表.學(xué)號(hào)=選課成績(jī)表.學(xué)號(hào)GroupBy學(xué)生表.學(xué)號(hào)OrderByRound(Avg(成績(jī)),2)DescSelect中的字段第一個(gè)與GroupBy子句的分組字段相同，其后必須使用聚合函數(shù)?！癎roupBy學(xué)生表.學(xué)號(hào)”指定按照“學(xué)號(hào)”分組，相同“學(xué)號(hào)”中的“姓名”是一致的，所以使用聚合函數(shù)First(學(xué)生表.姓名)求出第一個(gè)姓名顯示（或Last(學(xué)生表.姓名)）5.2.3SELECT多表連接查詢【例5-15】查詢學(xué)生選修課程成績(jī)，要求顯示學(xué)號(hào)、姓名、課程名稱和成績(jī)。Select學(xué)生表.學(xué)號(hào),姓名,課程名稱,成績(jī)From課程表InnerJoin(學(xué)生表InnerJoin選課成績(jī)表On學(xué)生表.學(xué)號(hào)=選課成績(jī)表.學(xué)號(hào))On課程表.課程編號(hào)=選課成績(jī)表.課程編號(hào)涉及到“學(xué)生表”“課程表”和“選課成績(jī)表”3張表1、先執(zhí)行：學(xué)生表InnerJoin選課成績(jī)表On學(xué)生表.學(xué)號(hào)=選課成績(jī)表.學(xué)號(hào)，產(chǎn)生中間結(jié)果2、再執(zhí)行：InnerJoin(中間結(jié)果)On課程表.課程編號(hào)=選課成績(jī)表.課程編號(hào)，完成3張表內(nèi)連接5.2.3SELECT多表連接查詢每位學(xué)生的每門課程都會(huì)產(chǎn)生一條結(jié)果數(shù)據(jù)，導(dǎo)致查詢結(jié)果數(shù)據(jù)較多【例5-16】查詢通過(guò)了四門課程的學(xué)生，要求顯示學(xué)號(hào)、姓名和通過(guò)課程數(shù)。5.2.3SELECT多表連接查詢Select學(xué)生表.學(xué)號(hào),First(學(xué)生表.姓名)As姓名,Count(成績(jī))As通過(guò)課程數(shù)From學(xué)生表InnerJoin選課成績(jī)表On學(xué)生表.學(xué)號(hào)=選課成績(jī)表.學(xué)號(hào)Where成績(jī)>=60GroupBy學(xué)生表.學(xué)號(hào)HavingCount(成績(jī))>=4;

注意“Having”子句與“Where”子句的不同之處如下。（1）“Where”子句在“GroupBy”分組之前起作用，“Having”子句在“GroupBy”分組之后起作用。（2）“Where”子句作用于表，從表中選擇滿足條件的記錄；“Having”子句作用于“GroupBy”分組，從分組中選擇滿足條件的組。篩選出及格的記錄“Having”子句必須和“GroupBy”配合使用，用于指定約束條件。數(shù)據(jù)定義查詢可以創(chuàng)建表刪除表修改表5.3SQL數(shù)據(jù)定義創(chuàng)建表CREATETABLECREATETABLE<表名>(<字段名稱1><數(shù)據(jù)類型>,<字段名稱2><數(shù)據(jù)類型>[,…])【例5-17】創(chuàng)建一個(gè)名為“學(xué)生家庭情況”表。字段名稱數(shù)據(jù)類型大小說(shuō)

明學(xué)號(hào)短文本(Text)10主鍵(PrimaryKey)家庭住址短文本(Text)50

家庭年收入數(shù)字(Numeric)

5.3SQL數(shù)據(jù)定義CreateTable家庭情況表(學(xué)號(hào)Text(10),家庭住址Text(50),家庭年收入Numeric,PrimaryKey(學(xué)號(hào)))“學(xué)生家庭情況”表修改表ALTERTABLEALTERTABLE<表名>[ADDCOLUMN<新字段名稱><數(shù)據(jù)類型>][DROP<字段名稱>][ALTERCOLUMN<字段名稱><數(shù)據(jù)類型>]5.3SQL數(shù)據(jù)定義【例5-18】為“家庭情況表”添加一個(gè)新字段，字段名稱為“父親工作單位”，短文本型，字段大小為40。AlterTable家庭情況表Add父親工作單位Text(40)5.3SQL數(shù)據(jù)定義【例5-19】刪除“學(xué)生家庭情況表”。DropTable學(xué)生家庭情況表表一旦被刪除，其結(jié)構(gòu)和記錄都被刪除，并且不可恢復(fù)刪除表DROPTABLE

DROPTABLE<表名>5.3SQL數(shù)據(jù)定義1.插入記錄INSERTINTO<表名>[(字段名稱1[,字段名稱2[,…]])]VALUES(值1[,值2[,….]])VALUES：指定表中新插入字段的具體值。其中各常量的數(shù)據(jù)類型及個(gè)數(shù)必須與對(duì)應(yīng)字段的數(shù)據(jù)類型和個(gè)數(shù)一致。5.4SQL數(shù)據(jù)操作InsertInto課程表(課程編號(hào),課程名稱,開課狀態(tài))Values("10600201","大學(xué)計(jì)算機(jī)",True)【例5-20】在選課成績(jī)表中添加一條記錄。5.4SQL數(shù)據(jù)操作語(yǔ)法格式：UPDATE<表名>SET<字段名稱1=值1[,字段名稱2=值2,…]>WHERE<條件表達(dá)式>2.更新記錄對(duì)指定表中滿足<條件表達(dá)式>的記錄進(jìn)行修改。如果沒有WHERE子句，則對(duì)指定表的全部記錄進(jìn)行修改。5.4SQL數(shù)據(jù)操作【例5-21】將“學(xué)生表副本”中“學(xué)號(hào)”字段4～6位是“104”的記錄對(duì)應(yīng)位修改為“999”，其他位保持不變。Update學(xué)生表副本Set學(xué)號(hào)=left(學(xué)號(hào),3)+"999"+right(學(xué)號(hào),4)Wheremid(學(xué)號(hào),4,3)="104"UPDATE語(yǔ)句Left(學(xué)號(hào),3)取前3位Right(學(xué)號(hào),4)取最后4位3.刪除語(yǔ)句語(yǔ)法格式：DELETEFROM<表名>WHERE<條件表達(dá)式>刪除指定表中滿足<條件表達(dá)式>的所有記錄如果沒有WHERE子句，則刪除指定表的所有記錄5.4SQL數(shù)據(jù)操作【例5-22】刪除“女學(xué)生表”中全部的男生的記錄。DeleteFrom女學(xué)生表

Where性別="男"5.4SQL數(shù)據(jù)操作命令格式：SELECT<字段列表>FROM<表名1>[，<表名2>]…[WHERE<條件表達(dá)式1>]UNIONSELECT<字段列表>FROM<表名A>[，<表名B>]…[WHERE<條件表達(dá)式2>]1．聯(lián)合查詢聯(lián)合查詢將兩個(gè)以上的表或查詢中的字段合并到一個(gè)集合中查看。使用聯(lián)合查詢可以合并多個(gè)表中的數(shù)據(jù)。5.5SQL特定查詢命令說(shuō)明（1）“UNION”運(yùn)算符可以將前后兩個(gè)SELECT語(yǔ)句的查詢結(jié)果進(jìn)行合并，生成一個(gè)數(shù)據(jù)集

（2）聯(lián)合查詢中的兩個(gè)SELECT語(yǔ)句必須具有相同的字段列數(shù)，各列具有相同的數(shù)據(jù)類型。Select姓名,課程名稱,成績(jī)From不及格學(xué)生信息UnionSelect姓名,課程名稱,成績(jī)From例5-15查詢Where成績(jī)>=90OrderBy成績(jī)Desc【例5-23】要求顯示“不及格學(xué)生信息”表以及“例5-15查詢”中所有成績(jī)90（含）分以上的學(xué)生的姓名、課程名稱和成績(jī)，并按成績(jī)的降序排列。5.5SQL特定查詢Select姓名,課程名稱,成績(jī)From不及格學(xué)生信息UnionSelect姓名,課程名稱,成績(jī)From成績(jī)查詢Where成績(jī)>=90OrderBy成績(jī)Desc因?yàn)椴樵兠Q中有符號(hào)“-”，執(zhí)行后系統(tǒng)會(huì)提示錯(cuò)誤，所以先將“例5-15查詢”復(fù)制后粘貼為名為“成績(jī)查詢”，再執(zhí)行聯(lián)合查詢2．子查詢子查詢由另一個(gè)選擇查詢或操作查詢的SELECT語(yǔ)句組成。在子查詢中還可創(chuàng)建子查詢，稱為嵌套子查詢。在SELECT語(yǔ)句中使用的子查詢，是指嵌套于SELECT語(yǔ)句的WHERE子句中的SELECT語(yǔ)句。5.5SQL特定查詢【例5-24】查詢“學(xué)生表”中入學(xué)總分高于平均值的學(xué)生的學(xué)號(hào)、姓名和入學(xué)總分。Select學(xué)號(hào),姓名,入學(xué)總分From學(xué)生表Where入學(xué)總分>=(SelectAvg(入學(xué)總分)From學(xué)生表)先執(zhí)行5.5SQL特定查詢子查詢結(jié)果只有一條記錄時(shí)，可用“=”、“>”、“<”、“<=”、“>=”等比較運(yùn)算符子查詢結(jié)果有多條記錄時(shí)，用In運(yùn)算符5.6課堂案例：學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)的SQL查詢1.單表SQL查詢可以完成單表的條件查詢，也可以實(shí)現(xiàn)統(tǒng)計(jì)計(jì)算功能的查詢。【課堂案例5-1】查詢學(xué)生表中“英語(yǔ)2001”“機(jī)械2001”“財(cái)務(wù)2001”這三個(gè)班學(xué)生的學(xué)號(hào)、姓名和班級(jí)。Select學(xué)號(hào),姓名,班級(jí)From學(xué)生表Where班級(jí)In("英語(yǔ)2001","機(jī)械2001","財(cái)務(wù)2001")5.6課堂案例：學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)的SQL查詢【課堂案例5-2】查詢學(xué)生表中“英語(yǔ)2001”“機(jī)械2001”“財(cái)務(wù)2001”這三個(gè)班的學(xué)生人數(shù)。Select班級(jí),Count(學(xué)號(hào))As人數(shù)From學(xué)生表Where班級(jí)In("英語(yǔ)2001","機(jī)械2001","財(cái)務(wù)2001")GroupBy班級(jí)5.6課堂案例：學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)的SQL查詢【課堂案例5-3】查詢課程表中全部課程的課程編號(hào)、課程名稱、學(xué)分和學(xué)時(shí)（假設(shè)1學(xué)分對(duì)應(yīng)16學(xué)時(shí)），并按照學(xué)時(shí)的降序排列。Select課程編號(hào),課程名稱,學(xué)分,學(xué)分*16As學(xué)時(shí)From課程表OrderBy4Desc;課程表的設(shè)計(jì)為了滿足3NF的要求，只保留了學(xué)分，學(xué)時(shí)可以通過(guò)表達(dá)式計(jì)算出來(lái)。因?yàn)橛?jì)算表達(dá)式“學(xué)分*16As學(xué)時(shí)”位于第4列，所以可以使用“OrderBy4Desc”來(lái)表示按照計(jì)算出的學(xué)時(shí)的降序排列。但是不能寫成“OrderBy學(xué)時(shí)Desc”，因?yàn)镺rderBy子句先于Select執(zhí)行。【課堂案例5-4】查詢選課成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)、姓名、課程名稱和成績(jī)，并按成績(jī)降序排列。Having子句必須和GroupBy配合使用，用于指定約束條件。5.6課堂案例：學(xué)