Access數(shù)據(jù)庫2003教學(xué)課件-CH4

Access2003數(shù)據(jù)庫教程

Access2003數(shù)據(jù)庫教程第1章數(shù)據(jù)庫系統(tǒng)概述第2章Access2003數(shù)據(jù)庫系統(tǒng)的基本概念第3章重要的表操作第4章查詢第5章創(chuàng)建實(shí)用窗體第6章報表第7章數(shù)據(jù)訪問頁的設(shè)計(jì)第8章宏第9章模塊第10章查詢綜合例題第4章查詢4.1查詢簡介4.2使用向?qū)?chuàng)建查詢4.３使用查詢設(shè)計(jì)器創(chuàng)建查詢4.4創(chuàng)建SQL查詢4.5查詢中數(shù)據(jù)的操作4.1查詢簡介查詢以一個表、多個表或者另一查詢?yōu)榛A(chǔ)，創(chuàng)建一個新的數(shù)據(jù)集作為查詢的結(jié)果集。查詢的結(jié)果是動態(tài)的，其內(nèi)容是隨著數(shù)據(jù)源的變化而變化的。可以通過查詢設(shè)計(jì)器和查詢設(shè)計(jì)向?qū)韯?chuàng)建查詢。4.1.1Access查詢按功能進(jìn)行分類1．選擇查詢2．參數(shù)查詢3．交叉表查詢4．操作查詢①刪除查詢②更新查詢③追加查詢④生成表查詢5．SQL查詢4.1.2Access查詢按創(chuàng)建查詢方式進(jìn)行分類（1）使用查詢設(shè)計(jì)器（2）使用各種查詢向?qū)Б俸唵尾樵兿驅(qū)Б诮徊姹聿樵兿驅(qū)Б鄄檎抑貜?fù)項(xiàng)查詢向?qū)Б懿檎也黄ヅ漤?xiàng)查詢向?qū)Х祷啬夸?.２使用向?qū)?chuàng)建查詢

4.2.1使用簡單查詢向?qū)?chuàng)建查詢使用簡單查詢可以在一個或多個表指定字段中檢索數(shù)據(jù)，還可以對記錄組或全部記錄進(jìn)行總計(jì)、計(jì)數(shù)、求平均值、最大值和最小值的運(yùn)算。4.３使用查詢設(shè)計(jì)器創(chuàng)建查詢

4.3.1查詢設(shè)計(jì)器及其使用1．打開查詢設(shè)計(jì)器，界面如下：3.查詢設(shè)計(jì)器的工具欄按鈕功能和說明視圖每種查詢有三種視圖，一是設(shè)計(jì)視圖；二是數(shù)據(jù)表視圖，用來顯示查詢結(jié)果集；三是SQL視圖，用來通過SQL語句進(jìn)行查詢。本按鈕可以在這三種視圖之間切換查詢類型本按鈕可以在選擇查詢、交叉表查詢、生成表查詢、更新查詢、追加查詢和刪除查詢這六種查詢之間切換執(zhí)行執(zhí)行查詢，將查詢結(jié)果集以工作表的形式顯示出來顯示表彈出“顯示表”對話框，用戶可以從中選擇查詢所要使用的表和查詢?？傆?jì)在查詢設(shè)計(jì)區(qū)中增加“總計(jì)”行，用以進(jìn)行求和、求平均值等統(tǒng)計(jì)計(jì)算。屬性顯示當(dāng)前光標(biāo)所在位置上的對象的屬性：如果光標(biāo)在查詢設(shè)計(jì)窗口內(nèi)的數(shù)據(jù)表/查詢顯示區(qū)內(nèi)，則將顯示查詢的屬性。如果光標(biāo)在下部的查詢設(shè)計(jì)區(qū)內(nèi)，則將顯示字段的屬性。生成器彈出“表達(dá)式生成器”對話框，用于生成準(zhǔn)則表達(dá)式。該按鈕只有在光標(biāo)位于查詢設(shè)計(jì)區(qū)的“準(zhǔn)則”欄內(nèi)才有效。數(shù)據(jù)庫窗口返回到數(shù)據(jù)庫窗口。4.3.3查詢條件和條件表達(dá)式1．條件表達(dá)式（1）簡單條件表達(dá)式所在字段條件表達(dá)式含義說明性別”男”查詢性別為“男”的記錄文本值一般應(yīng)使用雙引號括起來，如果沒有加上雙引號，Access會自動加上雙引號大學(xué)英語83查詢大學(xué)英語為83分的記錄等效為=83，允許省去等號“=”出生年月#84-1-25#查詢1984年1月25日出生者的記錄在條件表達(dá)式中使用日期時應(yīng)該用半角的“#”號括起來。如果沒有加上“#”號，Access會自動加上雙“#”號。（2）含運(yùn)算符的條件表達(dá)式①條件表達(dá)式中含有比較運(yùn)算符比較運(yùn)算符所在字段條件表達(dá)式含義=等于性別=”女”查詢女性的記錄>大于總分>240查詢總分大于240的記錄<>不等于大學(xué)英語<>0查詢大學(xué)英語不為0的記錄>=大于等于略略<小于略略<=小于等于略略4.3.3查詢條件和條件表達(dá)式②條件表達(dá)式中含有字符串運(yùn)算符運(yùn)算符所在字段條件表達(dá)式含義Like姓名Like”李*”Like”李？”查詢姓李的學(xué)生的記錄In姓名In(”李梅”,”馬威”)查詢“姓名”字段值是“李梅”或“馬威”的記錄（用于指定字符串只能是列出來的值）Betweenand出生年月Between#84-1-1#And#84-12-31#查詢出生年月在1984年內(nèi)的記錄（主要用于數(shù)字型、貨幣型和日期型字段）“*”號表示字符串中在該位置可以為任意的若干個字符。“？”號表示字符串中在該位置可以為任意的一個字符。4.3.3查詢條件和條件表達(dá)式③條件表達(dá)式中含有邏輯運(yùn)算符運(yùn)算符所在字段條件表達(dá)式含義and(與)數(shù)學(xué)>=60and<85查詢數(shù)學(xué)成績在60～84之間的記錄or(或)數(shù)學(xué)<60or>85查詢數(shù)學(xué)成績在60以下或者85以上的記錄not性別not"女"查詢性別不是“女”的記錄4.3.3查詢條件和條件表達(dá)式圖4.3.5查詢的“條件”行和“或”行4.3.3查詢條件和條件表達(dá)式不同行是”或”4.3.3查詢條件和條件表達(dá)式同行”或”要用邏輯運(yùn)算符表達(dá)式表達(dá)式可以用在表、查詢、窗體、報表、數(shù)據(jù)頁和宏等六個對象中。

2．表達(dá)式的設(shè)置[選課成績]![平時成績]*0.3+[選課成績]![考試成績]*0.7表名字段名運(yùn)算符常量4.3.3查詢條件和條件表達(dá)式表名必須用一對中括號[]括住外加一感嘆號。例如：[選課成績]!

字段名必須用一對中括號[]括住。例如：[平時成績]字符串必須用一對英文半角單引號或雙引號括住。例如："女"日期時間應(yīng)該用半角的“#”號括起來。例如：#84-1-25#(1)Access表達(dá)式的有關(guān)規(guī)則4.3.3查詢條件和條件表達(dá)式（2）函數(shù)函數(shù)功能說明Year（日期表達(dá)式）返回值是表示年份的四位整數(shù)例如“工作時間”字段的值是1983年1月25日,則Year（[工作時間]）的值是1983Date()返回值是系統(tǒng)當(dāng)天的日期（含年月日）例如當(dāng)前系統(tǒng)日期是2008年11月8日，則Date()的值是2008-11-8Day(Date())返回值是系統(tǒng)當(dāng)天的日期（只是日）例如當(dāng)前系統(tǒng)日期是2008年11月8日，則Day(Date())的值是8Now()返回值是系統(tǒng)當(dāng)天的日期和時間例如當(dāng)前系統(tǒng)日期是2008年11月8日，

時間是16:05:16，則Now()的值是2008-11-8:16:05:16表6.4.1部分常用日期函數(shù)4.3.3查詢條件和條件表達(dá)式（3）運(yùn)算符運(yùn)算符說明+加例如計(jì)算[平時成績]+[考試成績]-減例如計(jì)算[總工資]-[扣款]*乘例如計(jì)算[平時成績]*0.3/除例如計(jì)算[總分]/[人數(shù)]^乘冪例如計(jì)算[訂購量]^24.3.3查詢條件和條件表達(dá)式4.3.4創(chuàng)建參數(shù)查詢參數(shù)查詢在運(yùn)行時會提示用戶輸入?yún)?shù)值（查詢條件），并根據(jù)用戶的輸入給出查詢結(jié)果，從而可以實(shí)現(xiàn)交互式查詢。參數(shù)查詢實(shí)質(zhì)上是把選擇查詢的“條件”設(shè)置成一個帶有參數(shù)的“可變條件”。例4.3.2創(chuàng)建一個名為“按照學(xué)生姓名查詢”的查詢，當(dāng)輸入不同的學(xué)生姓名時，顯示相應(yīng)學(xué)生的記錄數(shù)據(jù)。4.3.5創(chuàng)建生成表查詢生成表查詢是在表或者查詢的基礎(chǔ)上創(chuàng)建的，在使用時生成表查詢，就能夠生成一個新“表”。例4.3.3根據(jù)“學(xué)生”表，創(chuàng)建一個名為“創(chuàng)建男學(xué)生表”的生成表查詢，并使用該查詢生成一個包含“學(xué)生編號”、“姓名”、“性別”和“年齡”的名為“男學(xué)生”的表。如下圖所示。4.3.6創(chuàng)建追加查詢?nèi)绻枰獙⒁粋€或多個表中所有記錄或符合一定條件的記錄添加到另一個表的尾部，可以使用追加查詢快速完成。在此，將接受添加記錄的表稱為目的表，將用來添加記錄的表稱為源表，追加查詢只能追加匹配的字段。例4.3.4創(chuàng)建一個名為“添加記錄”的追加查詢，并使用該查詢，將“學(xué)生”表中“家庭住址”為“廣西來賓”的記錄添加到“男學(xué)生”的表中。步驟如下圖所示。4.3.7創(chuàng)建更新查詢?nèi)绻枰笈啃薷臄?shù)據(jù)，或者是輸入有規(guī)律的數(shù)據(jù)，可以借助更新查詢來完成。例4.3.5創(chuàng)建一個名為“年齡加1”的更新查詢，并使用該查詢，將“學(xué)生”表中每個學(xué)生的年齡加1。步驟如下圖所示。4.3.8創(chuàng)建刪除查詢使用刪除查詢，可以將數(shù)據(jù)庫中一些無用的數(shù)據(jù)批量刪除。例4.3.6創(chuàng)建一個名為“刪除不及格”的刪除查詢，并使用該查詢，將“選課成績”表中考試成績不及格的學(xué)生的紀(jì)錄刪除。步驟如下圖所示。4.3.9創(chuàng)建多表查詢創(chuàng)建查詢時，如果查詢的數(shù)據(jù)源不在同一個表中。則需要創(chuàng)建多表查詢。這是這幾個表必須先建立有相應(yīng)的關(guān)系。例4.3.7建立一個名為“學(xué)生成績”的查詢，包括“學(xué)生編號”、學(xué)生的“姓名”、“課程名稱”、“平時成績”和“考試成績”字段，并分別計(jì)算“平時成績”和“考試成績”的平均值。

4.3.10在查詢中計(jì)算在實(shí)際應(yīng)用中，經(jīng)常需要對查詢的結(jié)果進(jìn)行計(jì)算，例如求和、計(jì)數(shù)、求最大最小值、求平均值等。1．總計(jì)計(jì)算例4.3.8創(chuàng)建一個名為“計(jì)算”的查詢，計(jì)算“選課成績”中“平時成績”的最小值，“考試成績”的平均分。步驟如下圖所示。2．分組總計(jì)計(jì)算例4.3.9在例4.3.8的基礎(chǔ)上，創(chuàng)建按學(xué)期進(jìn)行計(jì)算，命名為“按學(xué)期計(jì)算”的查詢。步驟如下圖所示。4．自定義計(jì)算字段可以在查詢中添加一個自定義的用于計(jì)算的新字段。計(jì)算字段是指根據(jù)一個或多個表中的一個或多個字段并使用表達(dá)式建立的新字段。例4.3.10創(chuàng)建一個名為“計(jì)算期評成績”的查詢，包含“學(xué)生編號”、“姓名”、“平時成績”和“考試成績”字段，新建一個“期評成績”字段，而且期評成績=平時成績×0.3+考試成績×0.7并將期評成績?yōu)?0分以上的記錄顯示出來。步驟如下圖所示。圖4.3.34查詢的設(shè)計(jì)視圖

圖4.3.35表達(dá)式生成器

圖4.3.36查詢設(shè)置圖4.3.37“計(jì)算期評成績”的查詢結(jié)果返回目錄4.4創(chuàng)建SQL查詢

4.4.1SQL簡介SQL(StructuredQueryLanguage)結(jié)構(gòu)化查詢語言，是訪問關(guān)系型數(shù)據(jù)庫的標(biāo)準(zhǔn)語言。JetSQL語句有兩種類型：（1）數(shù)據(jù)操作語言（DML）語句：主要用來檢索、添加、修改和刪除數(shù)據(jù)庫中的數(shù)據(jù)。（2）數(shù)據(jù)定義語言（DDL）語句：主要用來管理數(shù)據(jù)庫中的對象。常用的SQL語句有：（1）SELECT：用于創(chuàng)建查詢，檢索符合條件的數(shù)據(jù)。（2）INSERT：在現(xiàn)有表中添加一條或多條記錄。（3）UPDATE：修改表中一條或多條記錄數(shù)據(jù)。（4）DELETE：刪除表中一條或多條記錄。（5）SELECTINTO：將現(xiàn)有表的結(jié)構(gòu)和數(shù)據(jù)復(fù)制到另一個新表中。4.4.2SELECT語句簡介SELECT語句是最常用的SQL語句之一，主要用于創(chuàng)建選擇查詢、檢索數(shù)據(jù)，SELECT包含多個子句，其語句格式為：SELECT[ALL|DISTINCT|TOPn|TOPnpercent]

[*|<字段名1>，<字段名2>,…]FROM數(shù)據(jù)源

[WHERE<條件表達(dá)式>]

[ORDERBY<字段1>[ASC|DESC][，<字段2>[ASC|DESC][，…]]

[GROUPBY<字段1>[，<字段2>,…]]

[HAVING<條件表達(dá)式>]

語句格式說明：其中：ALL：表示相關(guān)檢索或操作應(yīng)用于數(shù)據(jù)源中所有的行，ALL是所列四項(xiàng)中的默認(rèn)項(xiàng)。DISTINCT：表示對指定的字段，將返回不包含重復(fù)項(xiàng)的一條記錄。TOPn：表示將返回滿足檢索條件的前n條記錄。TOPnpercent：表示將返回滿足檢索條件的前n%條記錄。*：表示返回的記錄包含所有字段的值。FROM子句：用于指定創(chuàng)建查詢所用的數(shù)據(jù)源，可以是表或已有查詢，此項(xiàng)為必選項(xiàng)。WHERE子句：用于設(shè)置檢索滿足<條件表達(dá)式>的記錄集，此<條件表達(dá)式>可以是用And和Or運(yùn)算符連接起來的復(fù)雜的條件。ORDERBY子句：用于指定查詢結(jié)果所依據(jù)的排序字段，其中ASC表示按指定字段升序排序，DESC表示按指定字段降序排序，不寫ASC或DESC時，默認(rèn)是ASC。GROUPBY子句：用于設(shè)置查詢結(jié)果是否按指定字段進(jìn)行分組。HAVING子句：用于限制GROUPBY子句返回的結(jié)果，只有上面使用了GROUPBY子句才能夠使用HAVING子句，但是使用了GROUPBY子句不一定要使用HAVING子句。SELECTFROMGROUPBYORDERBYWHERESQL語句與查詢設(shè)計(jì)器的比較4.4.3使用SELECT語句創(chuàng)建單表選擇查詢例4.4.1使用“教學(xué)管理系統(tǒng)”數(shù)據(jù)庫中“教師”表的數(shù)據(jù)創(chuàng)建一個SQL選擇查詢，要求按照工作時間先后順序檢索經(jīng)濟(jì)系和計(jì)算機(jī)系的女教師，查詢結(jié)果僅顯示教師姓名、性別、學(xué)歷、職稱、系別和工作時間。步驟如下圖所示。圖4.4.1新建查詢對話框

圖4.4.2關(guān)閉顯示表對話框

圖4.4.3選擇“查詢／ＳＱＬ特定查詢／聯(lián)合”打開聯(lián)合查詢窗口，錄入查詢語句圖4.4.4另存為對話框圖4.4.5例4401-SQL查詢結(jié)果例4.4.2使用“教學(xué)管理系統(tǒng)”數(shù)據(jù)庫中“學(xué)生”表的數(shù)據(jù)創(chuàng)建一個SQL選擇查詢，要求檢索來自山東的學(xué)生，查詢結(jié)果要求先按年齡降序排序，再按學(xué)生編號升序排序，查詢結(jié)果中包含“學(xué)生編號”、“姓名”、“性別”、“年齡”和“簡歷”字段內(nèi)容。SQL如圖所示。4.4.4使用SELECT語句創(chuàng)建多表選擇查詢使用SELECT語句創(chuàng)建多表選擇查詢時，由于數(shù)據(jù)源來自多個表，因此要對FROM子句的數(shù)據(jù)源部分作相應(yīng)的修改，主要是指明多個表的連接關(guān)系。SELECT語句修改成以下格式：SELECT[ALL|DISTINCT|TOPn|TOPnpercent][*|<字段名1>，<字段名2>,…]FROM數(shù)據(jù)表或查詢[INNERJOIN數(shù)據(jù)表或查詢ON<條件表達(dá)式>][WHERE<條件表達(dá)式>][ORDERBY字段1[ASC|DESC][，字段2[ASC|DESC][，…]]4.4.4使用SELECT語句創(chuàng)建多表選擇查詢例4.4.4使用“教學(xué)管理系統(tǒng)”數(shù)據(jù)庫中“課程”表和“選課成績”表創(chuàng)建一個SQL選擇查詢，要求統(tǒng)計(jì)各門課程期末考試的平均成績，并以各門課程期末考試的平均成績降序排序。SQL語句如下圖所示：4.4.4使用SELECT語句創(chuàng)建多表選擇查詢說明：（1）本例在SELECT語句后面的字段列表中用到了：AVG(選課成績.考試成績)AS平均成績，表示以“平均成績”作為AVG(選課成績.考試成績)字段的別名，別名只要符合字段的命名規(guī)則即可，在查詢結(jié)果的字段名稱行中會顯示別名。AVG()是統(tǒng)計(jì)平均值的函數(shù)。（2）本例在統(tǒng)計(jì)各門課程的平均成績時用到了分組子句：GROUPBY課程.課程名稱，其中“課程名稱”是分組字段。（3）Round函數(shù)可為字段限制小數(shù)位數(shù)，如：Round(AVG(選課成績.考試成績),2)圖4.4.11各門課程平均成績查詢結(jié)果例4.4.3使用“教學(xué)管理系統(tǒng)”數(shù)據(jù)庫中“學(xué)生”表、“課程”表、“選課成績”表和“教師”表的數(shù)據(jù)創(chuàng)建一個SQL選擇查詢，要求顯示“學(xué)生”表的“學(xué)生編號”、“姓名”、“性別”字段，“課程”表的“課程名稱”字段，“選課成績”表的“考試成績”字段，“教師”表的“姓名”字段。查詢結(jié)果要求先按課程名稱降序排序，再按學(xué)生成績降序排序。SQL語句如下圖所示：SELECT學(xué)生.學(xué)生編號,學(xué)生.姓名,學(xué)生.性別,課程.課程名稱,選課成績.考試成績,教師.姓名FROM學(xué)生INNERJOIN((教師INNERJOIN課程ON教師.教師編號=課程.任課教師編號)

INNERJOIN選課成績ON課程.課程編號=選課成績.課程編號)ON學(xué)生.學(xué)生編號=選課成績.學(xué)生編號ORDERBY課程.課程名稱DESC,選課成績.考試成績DESC;SELECTFROMGROUPBYORDERBYWHERESQL語句與查詢設(shè)計(jì)器的比較INNERJOIN…on……3．函數(shù)函數(shù)被用來完成一些特殊的運(yùn)算，函數(shù)由函數(shù)名、括號和參數(shù)構(gòu)成，某些函數(shù)的括號中可以無參數(shù)。如Sum([平時成績]),在這里Sum是函數(shù)名,[平時成績]是參數(shù)，函數(shù)的返回值即函數(shù)值是平時成績之和?？梢灾苯邮褂煤瘮?shù)的返回值，也可以將函數(shù)的返回值作為條件的比較對象。在SQL語句中常用的統(tǒng)計(jì)函數(shù)有：（1）MAX(字段名)：返回指定字段數(shù)據(jù)的最大值。（2）MIN(字段名)：返回指定字段數(shù)據(jù)的最小值。（3）COUNT(字段名)：返回指定字段數(shù)據(jù)中非空數(shù)據(jù)的個數(shù)。（4）COUNT(*)：返回查詢結(jié)果中總的行數(shù)。（5）AVG(字段名)：返回指定字段數(shù)據(jù)的平均值。（6）SUM(字段名)：返回指定字段數(shù)據(jù)的總和。表4.3.2總計(jì)項(xiàng)函數(shù)的功能分類中文名英文名功能匯總函數(shù)合計(jì)Sum為每一組中指定的字段進(jìn)行求和運(yùn)算平均值A(chǔ)vg為每一組中指定的字段進(jìn)行求平均值運(yùn)算最小值Min為每一組中指定的字段進(jìn)行求最小值最大值Max為每一組中指定的字段進(jìn)行求最大值計(jì)數(shù)Count根據(jù)指定的字段計(jì)算每一組中記錄的個數(shù)標(biāo)準(zhǔn)差StDev根據(jù)指定的字段計(jì)算每一組的統(tǒng)計(jì)標(biāo)準(zhǔn)差方差Var根據(jù)指定的字段計(jì)算每一組的統(tǒng)計(jì)方差其他總計(jì)項(xiàng)第一條記錄First找出每一組中首條記錄指定字段的值最后一條記錄Last找出每一組中最后一條記錄指定字段的值表達(dá)式Expression用來在設(shè)計(jì)網(wǎng)格的“字段”行中建立計(jì)算字段條件Where限定表中的哪些記錄可以參加分組匯總，即指定查詢條件分組GroupBy用來指定分組匯總字段在SQL中使用統(tǒng)計(jì)函數(shù)例4.4.5統(tǒng)計(jì)“學(xué)生”表中的學(xué)生人數(shù)。SELECTCOUNT（*）AS學(xué)生表人數(shù)FROM學(xué)生例4.4.6統(tǒng)計(jì)“選課成績”表中的考試成績的最高分。SELECTMAX（考試成績）AS最高分FROM選課成績例4.4.7統(tǒng)計(jì)“選課成績”表中的考試成績的最高分、最低分。SELECTMAX（考試成績）AS最高分,MIN（考試成績）AS最低分FROM選課成績例4.4.84408-按學(xué)期統(tǒng)計(jì)“選課成績”表中的考試成績、平時成績的平均分SELECT

AVG(考試成績)

AS

考試平均分,AVG(平時成績)AS

平時平均分FROM

選課成績GROUPBY

學(xué)期;在SQL中使用統(tǒng)計(jì)函數(shù)例4.4.9統(tǒng)計(jì)“學(xué)生”表中男女生的平均年齡SELECT性別,AVG(年齡)AS平均年齡FROM學(xué)生GROUPBY性別;例4.4.10統(tǒng)計(jì)“學(xué)生”表中男女生的平均成績SELECT

學(xué)生.性別,AVG(選課成績.考試成績)AS考試平均成績FROM

學(xué)生INNERJOIN選課成績ON學(xué)生.學(xué)生編號=選課成績.學(xué)生編號GROUPBY學(xué)生.

性別;在SQL中使用統(tǒng)計(jì)函數(shù)例4.4.11(實(shí)驗(yàn)6分組合計(jì)1)：設(shè)有數(shù)據(jù)表學(xué)生（學(xué)號，姓名，性別，班級），用SQL語句建立一個查詢，統(tǒng)計(jì)并顯示出學(xué)生表中姓氏在2人以上的姓氏及其人數(shù)，假設(shè)姓氏都是一個字。在SQL中使用統(tǒng)計(jì)函數(shù)Selectleft(姓名,1)AS姓氏,count(姓名)