第四章 查詢的創(chuàng)建和使用

1、第4章 查詢的創(chuàng)建和使用4.1 查詢概述4.1.1 查詢的概念u 根據(jù)給定的條件從數(shù)據(jù)庫的一個或多個表中篩選出符合條件的記錄，構成一個數(shù)據(jù)集合，供使用者查看、更改和分析使用。u 查詢從中獲取數(shù)據(jù)的表稱為查詢的數(shù)據(jù)源。u 查詢的結(jié)果是一個動態(tài)的數(shù)據(jù)集合，會隨著數(shù)據(jù)源的變化而變化。查詢的主要功能:1.提取數(shù)據(jù)可以從一個或多個表或查詢中重新組合、檢索出用戶需要的數(shù)據(jù)?？梢詮囊粋€或多個表中選擇部分或全部字段，也可以從一個或多個表中將符合條件的記錄選取出來。2.數(shù)據(jù)更新更新操作包括添加、修改、刪除記錄。查詢的主要功能:3.實現(xiàn)計算在查詢中對相關數(shù)據(jù)進行統(tǒng)計、計算，還可以建立新的字段來保存計算的結(jié)果，這樣

2、可以簡化處理工作，用戶不必每次都在原始數(shù)據(jù)上進行檢索，從而提高了整個數(shù)據(jù)庫的性能。4.產(chǎn)生新的表并可作為其他對象的數(shù)據(jù)源可以建立一個新表來保存查詢的結(jié)果。查詢的結(jié)果還可作為窗體、報表等對象的數(shù)據(jù)源。4.1.2 查詢的類型1.選擇查詢最常用、最基本的一種查詢。它可以根據(jù)用戶提供的條件，從一個或多個表中檢索數(shù)據(jù)，并且在數(shù)據(jù)表中顯示結(jié)果。2.參數(shù)查詢是一種交互式查詢，在建立查詢時為用戶留有動態(tài)參數(shù)接口。在執(zhí)行參數(shù)查詢時會顯示一個對話框，提示用戶根據(jù)需要輸入查詢條件，然后再檢索相應的記錄。參數(shù)查詢具有很強的靈活性。3.交叉表查詢可以計算并重新組織數(shù)據(jù)的結(jié)構，更加方便地分析數(shù)據(jù)。交叉表查詢可以計算數(shù)據(jù)的

3、平均值、總計、最大值、最小值或其他類型的總和。4.1.2 查詢的類型4.操作查詢操作查詢用于添加、更改或刪除數(shù)據(jù)。操作查詢有以下4種類型：u刪除查詢：刪除查詢可以從一個或多個表中刪除一組記錄。u追加查詢：追加查詢可將一組記錄添加到一個或多個表的尾部。運行追加查詢的結(jié)果是向相關表中自動添加記錄，增加表的記錄數(shù)。u更新查詢：更新查詢可根據(jù)指定條件對一個或多個表中的記錄進行更改。u生成表查詢：生成表查詢是利用一個或多個表中的全部或部分數(shù)據(jù)創(chuàng)建新表。4.1.2 查詢的類型5.SQL查詢SQL是一種結(jié)構化查詢語言，是數(shù)據(jù)庫操作的工業(yè)化標準語言，所有的數(shù)據(jù)庫管理系統(tǒng)都支持這種語言。SQL查詢就是使用SQL

4、語句創(chuàng)建的查詢。4.1.3 查詢視圖1.設計視圖設計視圖用于對查詢設計進行編輯，通過設計視圖可以創(chuàng)建SQL之外的各種類型查詢。2.數(shù)據(jù)表視圖數(shù)據(jù)表視圖用于查看查詢運行的結(jié)果，是查詢的數(shù)據(jù)瀏覽器。3.SQL視圖SQL視圖是查看和編輯SQL語句的窗口，可以查看用設計視圖創(chuàng)建的查詢所產(chǎn)生的SQL語句，也可以對SQL語句進行編輯和修改。4.1.3 查詢視圖4.數(shù)據(jù)透視表和數(shù)據(jù)透視圖視圖在數(shù)據(jù)透視表和數(shù)據(jù)透視圖視圖中，可以根據(jù)需要生成數(shù)據(jù)透視表和數(shù)據(jù)透視圖，從而對數(shù)據(jù)進行分析，得到直觀的分析結(jié)果。4.2 選擇查詢的創(chuàng)建選擇查詢的概念：根據(jù)用戶提供的條件，從一個或多個表中檢索數(shù)據(jù)，并且在數(shù)據(jù)表中顯示結(jié)果。

5、1.建立單表查詢【例4-1】查詢學生的基本信息，要求顯示學生的學號、姓名、性別、出生日期和院系等信息，所建查詢命名為“學生基本信息查詢”。4.2.1 使用簡單查詢向?qū)?.建立多表查詢當所需要查詢的信息來自兩個或兩個以上的表或查詢時就需要建立多表查詢。建立多表查詢的各個表必須要有關聯(lián)字段，并且事先應通過這些關聯(lián)字段建立了表間關系?！纠?-2】查詢學生的課程成績，要求顯示“學號”、“姓名”、“課程號”、“課程名稱”和“成績” 。4. 2.2 使用查詢設計視圖u 打開查詢設計視圖的方法u 查詢設計視圖的結(jié)構【例4-3】查詢院系為“信息學院”的學生信息，要求顯示“學號”、“姓名”、“出生日期”及“籍貫

6、”?！纠?-4】查詢選修了“大學計算機基礎”課程的學生信息，要求顯示“學號”、“姓名”、“院系”及“課程名稱”和“成績”，并按成績降序顯示。4.2.3 查詢條件u打開表達式生成器的方法u表達式生成器的結(jié)構表達式就是運算符、常量、函數(shù)和字段名稱、控件和屬性的任意組合，計算結(jié)果為單個值。1.常量（1）數(shù)字型常量：如分數(shù)“89”、“90”等。（2）文本型常量：如“信息學院”、“物流學院”等，可使用“*”和“？”通配符。（3）日期型常量：如出生日期#1992-7-1#。（4）是否型常量：Yes、No、True、False。（5）空字段值：“Is Null”表示字段值為空，“Is Not Null”表示

7、字段值不為空。表達式中常用的運算符有算術運算符、關系運算符、邏輯運算符、標識符和特殊操作符。u算術運算符：如+、-、*、/等。u關系運算符：如、=、=、=、。u邏輯運算符：如AND、OR、NOT。u標識符：通常是一個對象的名字，一般指的是字段的名字。引用字段名稱時需要用“ ”將其名稱括起來。2. 表達式中的運算符（1）用邏輯運算符組合條件uAnd表示當兩個操作數(shù)的值都為TRUE時，表達式的值才為TRUE。u Or表示兩個操作數(shù)的值有一個為TRUE時，表達式的值就為TRUE。用Or運算符可將條件進行擴展。u Not則生成操作數(shù)的相反值。（2）用關系運算符組合條件（3）用Between運算符組合條

8、件Between運算符也可用于指定字段的取值范圍，范圍之間用And連接。（4）用like組合條件Like運算符用于查找字段的部分值，格式為： Like 字符模式在所定義的字符模式中，可用“？”表示該位置可匹配任何一個字符；用“*”表示該位置可匹配零個或多個字符；用“#”表示該位置可匹配一個數(shù)字；用“ ”描述一個范圍。（5）用In組合條件In運算符用于指定字段的一系列的值，與like不同，in運算中不允許使用通配符。3.函數(shù)函數(shù)常用的數(shù)值函數(shù)函數(shù) 功能Abs（數(shù)值表達式）返回數(shù)值表達式值的絕對值Int（數(shù)值表達式）返回數(shù)值表達式值的整數(shù)部分Sqr（數(shù)值表達式）返回數(shù)值表達式值的平方根Sgn（數(shù)值

9、表達式）返回數(shù)值表達式值的符號值常用的文本函數(shù)函數(shù) 功能Left（字符串表達式，n）從字符串表達式左側(cè)第1個字符開始截取n個字符Right（字符串表達式，n）從字符串表達式右側(cè)第1個字符開始截取n個字符Len（字符串表達式）返回字符串表達式中字符的個數(shù)Mid（字符 串 表 達 式 ，n1,n2）從字符串表達式左邊n1位置開始，截取連續(xù)n2個字符常用的日期時間函數(shù)常用的日期時間函數(shù)函數(shù) 功能Now（）返回系統(tǒng)當前的日期時間Date（）返回系統(tǒng)當前的日期Time（）返回系統(tǒng)當前的時間Day（日期表達式）返回日期中的日Month（日期表達式）返回日期中的月份Year（日期表達式）返回日期中的年份4.

10、2.4 編輯編輯查詢查詢1.增加字段2.刪除字段3.修改字段4.移動字段5.調(diào)整設計網(wǎng)格的列寬4.3在查詢中進行在查詢中進行計算計算4.3.1 查詢中的計算功能u 預定義計算u 自定義計算查詢中的常用計算查詢中的常用計算計算名 功能合計 計算一組記錄中某個字段值的總和平均值 計算一組記錄中某個字段值的平均值最大值 計算一組記錄中某個字段值的最大值最小值 計算一組記錄中某個字段值的最小值計數(shù) 計算一組記錄中記錄的個數(shù)First 一組記錄中某個字段的第一個值Last 一組記錄中某個字段的最后一個值Expression 創(chuàng)建一個由表達式產(chǎn)生的計算字段Where 設定分組條件以便選擇記錄4.3.2 總

11、計總計查詢查詢總計查詢是通過對查詢設計視圖窗口中的總計行進行設置實現(xiàn)的，用于對查詢中的全部記錄進行總和、平均值、最大值、最小值的計算?！纠?-5】查詢?nèi)W生的入學成績情況：平均入學成績、最高成績和最低成績，并定義查詢字段依次為平均分、最高分、最低分。4.3.3 分組總計查詢分組是指在查詢設計窗口中指定某一字段為分組字段，將該字段值相同的所有記錄組合在一起，并對分組進行統(tǒng)計計算。【例4-6】查詢?nèi)８髟合祵W生的入學成績情況：即查詢各院系的平均入學成績、最高成績和最低成績，定義查詢字段依次為平均分、最高分、最低分，并要求平均分保留小數(shù)點后1位。4.3.4 添加計算字段當需要統(tǒng)計的數(shù)據(jù)在表中沒有相

12、應的字段，或者用于計算的數(shù)據(jù)值來源于多個字段時，應在設計網(wǎng)格中添加一個計算字段，計算字段是指根據(jù)一個或多個表中的一個或多個字段并使用表達式建立的新字段?！纠?-7】計算所有學生的年齡，要求顯示每個學生的“學號”、“姓名”和“年齡”?！纠?-8】 查詢每個學生的獎學金級別，要求顯示的字段有：“學號”、“姓名”、“院系”、“入學成績”和“獎學金等級”。獎學金等級的劃分標準是：入學成績=650 的為“一級” ，620= 入學成績650 的為“二級”，590=入學成績620的為“三級” ，否則沒有。iif( )函數(shù)的格式如下：iif( 條件表達式1，表達式2，表達式3）功能：首先計算條件表達式1，若條

13、件表達式1的值為真返回表達式2的值否則返回表達式3的值。4.4 其他查詢的設計4.4.1 交叉表查詢交叉表查詢以一種緊湊的、類似于電子表格的形式顯示查詢字段，并且可以計算該字段的合計或平均值等。使用交叉表查詢計算和重構數(shù)據(jù)，可以簡化數(shù)據(jù)分析。1.交叉表查詢向?qū)А纠?-9】建立學生與課程成績交叉表查詢，統(tǒng)計每名學生選修課程的成績。2.設計視圖中的交叉表查詢【例4-10】利用交叉表查詢，統(tǒng)計每名學生選修課程情況。要求顯示學生“姓名”、選課數(shù)量、平均成績以及所學課程的成績。4. 4.2 參數(shù)查詢創(chuàng)建這種參數(shù)的查詢只要在查詢設計窗口中，在要定義為參數(shù)的字段的條件行中輸入用“”括起來的提示信息，在運行查

14、詢時這些提示信息出現(xiàn)在“輸入?yún)?shù)值”輸入框的上方，然后保存查詢文件。【例4-11】創(chuàng)建一個帶有參數(shù)的查詢，要求先輸入學生的學號，之后顯示該學生的“學號”、“姓名”、“性別”、“課程名稱”及“成績”。4.4.3 操作查詢Access2010的操作查詢包括以下幾種查詢：u 生成表查詢。利用從一個或多個表獲得的數(shù)據(jù)創(chuàng)建一個新的表。 u 更新查詢。對一個或多個表中的多個記錄的某些字段的值進行修改。u 追加查詢。為一個或多個表添加從一個或多個表中獲得的一組記錄。u 刪除查詢。對一個或多個表中滿足條件的一組記錄進行刪除操作。1.生成表查詢【例4-12】在“新教務管理數(shù)據(jù)庫”中創(chuàng)建一個“不及格學生名單”的表

15、。要求該表中要有：“學號”、“姓名”、“性別”、“院系”、“課程名稱”和“成績”等字段。【例4-13】將“學生”表中院系為“信息學院”的學生記錄保存到當前數(shù)據(jù)庫名為“信息學院學生名單”的新表中。2. 更新查詢【例4-14】在“不及格學生名單”表中添加一個“補考日期”字段，并將“2012/10/10” 輸入到“補考日期”字段中。3.追加查詢【例4-15】將“學生”表中院系為物流學院的所有學生追加到“信息學院學生名單”表中。4.刪除查詢【例4-16】將“信息學院學生名單”表中院系字段為“物流學院”的所有學生刪除。4.4.4 重復項、不匹配項查詢1.查找重復項查詢根據(jù)“重復項查詢向?qū)А彼鶆?chuàng)建的查詢結(jié)

16、果可以確定在表中是否有重復的記錄，或確定記錄在表中是否共享相同的值?！纠?-17】使用重復項查詢向?qū)В凇皩W生”表中查詢同名學生，要求顯示學生“學號”、“姓名”、“性別”和“院系”等字段。2.查找不匹配項查詢在關系數(shù)據(jù)庫中，當建立了一對多的關系后，通常在“一方”表中的每一條記錄，與“多方”表中的多條記錄相匹配。但是也有可能存在“多方”表中沒有記錄與之匹配。因此要執(zhí)行查找不匹配查詢至少需要兩個表，并且這兩個表要在同一個數(shù)據(jù)庫中。【例4-18】利用不匹配項查詢，查找出沒有考試成績的學生名單，要求顯示學生“學號”、“姓名”、“性別”和“院系”等字段。 4.5 SQL查詢4.5.1 查詢與SQL視圖A

17、ccess 2010中的查詢是以SQL語句為基礎來實現(xiàn)查詢功能的，因此，Access 2010中所有的查詢都可認為是SQL查詢。各種查詢操作，系統(tǒng)自動地將操作命令轉(zhuǎn)換為SQL語句。只要單擊“SQL視圖”就可以看到系統(tǒng)所生成的SQL代碼。4.5.2 SQL簡介SQL語言是1974年由Boyce 和Chamberlin公司提出，并首先在IBM公司研制的關系數(shù)據(jù)庫原型系統(tǒng)System R上實現(xiàn)。由于它具有功能豐富、使用靈活、語言簡捷易學等特點，被業(yè)界廣泛采用。經(jīng)過不斷修改、擴充和完善，1986年被美國國家標準局批準成為關系型數(shù)據(jù)庫語言的標準。SQL語言的功能包括以下4個方面：u數(shù)據(jù)定義語言DDL：定

18、義數(shù)據(jù)庫的邏輯結(jié)構，包括定義表、視圖和索引等。u數(shù)據(jù)操縱語言DML：包括數(shù)據(jù)檢索和數(shù)據(jù)更新兩大操作，其中數(shù)據(jù)更新包括插入、刪除和修改操作。u數(shù)據(jù)控制語言：包括基本表和視圖等的授權、完整性描述以及事務開始和結(jié)束等控制語句等。u嵌入式SQL語言：SQL語言既可以作為獨立的語言供終端用戶聯(lián)機使用，也可以作為宿主型語言嵌入到某種高級程序設計語言中使用。規(guī)定了SQL語言在宿主語言程序中的使用規(guī)則。 SQL語言的應用有以下幾個方面：u在查詢“設計”視圖中創(chuàng)建查詢時，Access將在后臺構造等效的SQL語句。u某些查詢不能在設計網(wǎng)格中創(chuàng)建。對于傳遞查詢、數(shù)據(jù)定義查詢和聯(lián)合查詢，必須直接在 SQL 視圖中創(chuàng)建

19、 SQL 語句。u在Access中，某些情況下，Access會幫助用戶填入SQL語句。SQL語言的功能SQL功能 動詞數(shù)據(jù)定義 CREATE，DROP，ALTER數(shù)據(jù)查詢 SELECT數(shù)據(jù)操縱 INSERT，UPDATE，DELETE數(shù)據(jù)控制 GRANT，REVOTE1.CREATE 語句創(chuàng)建表的一般格式為： CREATE TABLE ( () NOT NULL PRIMARY KEY | UNIQUE , () NOT NULL PRIMARY KEY | UNIQUE , )；說明：其中的是所要定義的基本表的名字，它可以由一個或若干個字段組成。各個字段的數(shù)據(jù)類型必須用字符表示，如字符型用C

20、HAR或TEXT，日期型用DATE，貨幣型用MONEY，備注型用MEMO，整型用INTEGER，單精度用SINGLE表示等等。 定義單個字段為主鍵時，可以直接在該字段名后加上PRIMARY KEY或UNIQUE關鍵字。如果要將多個字段定義為主鍵或唯一鍵，應使用PRIMARY KEY或UNIQUE子句?！纠?-19】使用SQL語句創(chuàng)建一個名為STUDENT的表，其表結(jié)構為：學號（文本型，10字符，主鍵）、姓名（文本型，8字符，不允許為空值）、性別（文本型，1字符）、出生日期（日期/時間型）、貸款否（是/否型）、簡歷（備注型）、照片（OLE對象型）。 CREATE TABLE STUDENT (學

21、號TEXT(10) PRIMARY KEY,姓名 TEXT(8) NOT NULL,性別TEXT(1),出生日期 DATE, 貸款否 LOGICAL,簡歷 MEMO, 照片 OLEOBJECT )；【例4-20】使用SQL語句創(chuàng)建一個名為COURSE表，其表結(jié)構為：課程號（文本型，3字符，主鍵）、課程名稱（文本型，50字符）、學分（單精度型）、授課院系（文本型，50字符）。 CREATE TABLE COURSE (課程號TEXT(3) PRIMARY KEY,課程名稱 TEXT(50),學分 SINGLE,授課院系TEXT(50)；【例4-21】使用SQL語句創(chuàng)建一個名為SC表，其表結(jié)構為：

22、學號（文本型，10字符）、課程號（文本型，3字符）、成績（單精度型），主鍵由學號和課程號組成。同時使SC表與STUDENT表之間通過“學號”建立關系、SC表與COURSE表之間通過“課程號”字段建立關系。 CREATE TABLE SC (學號 TEXT(10) REFERENCES STUDENT(學號)，課程號 TEXT(3) REFERENCES COURSE(課程號)，成績 SINGLE，PRIMARY KEY(學號，課程號) )；2.ALTER（1）在表中添加字段，其一般格式為： ALTER TABLE ADD ()【例4-22】 在STUDENT表中增加一個“電話號碼”字段，類型為

23、“長整型”。 ALTER TABLE STUDENT ADD 電話號碼 INTEGER；（2）修改字字段的數(shù)據(jù)類型，其一般格式為： ALTER TABLE ALTER () 注意：使用該命令只能修改字段的數(shù)據(jù)類型而不能修改字段名?！纠?-23】 將STUDENT表中新增加的“電話號碼”字段修改為文本型、大小為8字符。ALTER TABLE STUDENT ALTER 電話號碼 TEXT(8)；（3）刪除字段，其一般格式為： ALTER TABLE DROP 【例4-24】 在STUDENT表中將新增加的“電話號碼”字段刪除。 ALTER TABLE STUDENT DROP 電話號碼；3.DR

24、OP（1）刪除索引，其一般格式為： DROP INDEX ON ;（2）刪除表，其一般格式為： DROP TABLE ; 刪除表后，在表上定義的索引也一起被刪除。4.INSERT其一般格式為：INSERT INTO (,，) VALUES(,)；說明：該語句的功能是將新記錄插入到指定表中，新記錄的字段名1的值為常量1、字段名2的值為常量2，如果INTO后缺省字段名，則必須為新記錄中的每個字段都賦值，且插入的數(shù)據(jù)類型和順序要與表中定義的字段一一對應?！纠?-25】 向STUDENT表中插入2個學生記錄。第一條記錄“學號”為“2010011123”，“姓名”為“李榮輝”，“性別”為“男”。第二條記

25、錄“學號”為“2010010148”，“姓名”為“黃娟”，“性別”為“女”，“出生日期”為“1990-5-26”，“貸款否”為“是”，其他字段為空值。INSERT INTO STUDENT(學號，姓名，性別) VALUES(“2010011123”，“李榮輝”，“男”)；INSERT INTO STUDENT VALUES(“2010010148”，“黃娟”，“女”，#1990-5-26#，YES，NULL，NULL)；5.UPDATE其一般格式為：UPDATE SET = ,=，WHERE ；說明：如果不帶WHERE子句，則更新表中所有的記錄。如果帶WHERE子句，則只更新表中滿足條件的記錄

26、?！纠?-26】將STUDENT表中姓名為“李榮輝”的出生日期改為“1991-5-20”。UPDATE STUDENT SET 出生日期=#1991/5/20# where姓名=“李榮輝”；【例4-27】將STUDENT表中所有女生的“貸款否”字段改為“否”。UPDATE STUDENT SET 貸款否=NO WHERE 性別=“女”；6.DELETE其一般格式為：DELETE FROM WHERE ； 說明：如果不帶WHERE子句，則刪除表中所有的記錄，但表對象仍保留在數(shù)據(jù)庫中。如果帶WHERE子句，則只刪除表中滿足條件的記錄?！纠?-28】 刪除STUDENT表中學號為“201001112

27、3”的學生記錄。 DELETE FROM STUDENT WHERE 學號=“2010011123”；4.5.3 SQL查詢的創(chuàng)建SELECT語句的一般格式：SELECT ALL|DISTINCT ， FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC 說明：根據(jù)WHERE子句的條件表達式，從FROM子句指定的基本表或查詢中找出滿足條件的記錄，再按SELECT子句中的目標列找出元組中的屬性值形成結(jié)果表。ALL為默認值，表示所有滿足條件的記錄，DISTINCT用于忽略重復數(shù)據(jù)的記錄，即在基本表中重復記錄只出現(xiàn)一次。GROUP子句則表示將結(jié)果按表中的某一字

28、段名（分組項）的值進行分組，該屬性列值相同的元組為一個組，每個組產(chǎn)生結(jié)果表中的一條記錄。GROUP 子句還可以帶HAVING短語，表示只有滿足指定條件的組才輸出。ORDER子句將結(jié)果按表中的某一字段名（排序項）的值升序或降序排列。1. 單表查詢（1）選擇查詢【例4-29】 從“學生”中查詢信息學院的所有學生的“學號”、“姓名”、“性別”和年齡。 SELECT 學號，姓名，性別，YEAR(DATE()-YEAR(出生日期) AS 年齡 FROM 學生WHERE 院系=“信息學院”；（2）排序查詢ORDER BY子句的格式為： ORDER BY ASC|DESC【例4-30】在“成績”表中查詢80

29、90分的記錄，同一門課程按成績降序排。 SELECT * FROM 成績 WHERE 成績 BETWEEN 80 AND 90 ORDER BY 課程號,成績 DESC;TOP短語的格式為： TOP 或 TOP PERCENT【例4-31】 查詢“成績”表中成績排在前5名的記錄。 SELECT TOP 5 * FROM 成績 ORDER BY 成績 DESC；（3）分組查詢常用統(tǒng)計函數(shù)有：COUNT、AVG、SUM、MIN、MAX。【例4-32】 統(tǒng)計“學生”表中各院系的學生人數(shù)。 SELECT 院系，COUNT(*) AS 各院系人數(shù) FROM 學生 GROUP BY 院系；【例4-33】 查詢選修了3門以上課程的學生學號。 SELEC