Access2003數據庫教程

Access2003數據庫教程第1章數據庫系統概述第2章Access2003數據庫系統的基本概念第3章重要的表操作第4章查詢第5章創建實用窗體第6章報表第7章數據訪問頁的設計第8章宏第9章模塊第10章查詢綜合例題第4章查詢4.1查詢簡介4.2使用向導創建查詢4.３使用查詢設計器創建查詢4.4創建SQL查詢4.5查詢中數據的操作4.1查詢簡介查詢以一個表、多個表或者另一查詢為基礎，創建一個新的數據集作為查詢的結果集。查詢的結果是動態的，其內容是隨著數據源的變化而變化的。可以通過查詢設計器和查詢設計向導來創建查詢。4.1.1Access查詢按功能進行分類1．選擇查詢2．參數查詢3．交叉表查詢4．操作查詢①刪除查詢②更新查詢③追加查詢④生成表查詢5．SQL查詢4.1.2Access查詢按創建查詢方式進行分類（1）使用查詢設計器（2）使用各種查詢向導①簡單查詢向導②交叉表查詢向導③查找重復項查詢向導④查找不匹配項查詢向導返回目錄4.２使用向導創建查詢

4.2.1使用簡單查詢向導創建查詢使用簡單查詢可以在一個或多個表指定字段中檢索數據，還可以對記錄組或全部記錄進行總計、計數、求平均值、最大值和最小值的運算。4.３使用查詢設計器創建查詢

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

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

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

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

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

圖4.3.35表達式生成器

圖4.3.36查詢設置圖4.3.37“計算期評成績”的查詢結果返回目錄4.4創建SQL查詢

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

[*|<字段名1>，<字段名2>,…]FROM數據源

[WHERE<條件表達式>]

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

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

[HAVING<條件表達式>]

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

圖4.4.2關閉顯示表對話框

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

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

AVG(考試成績)

AS

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

平時平均分FROM

選課成績GROUPBY

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

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

學生INNERJOIN選課成績ON學生.學生編號=選課成績.學生編號GROUPBY學生.

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