Access查詢的設(shè)計與創(chuàng)建-1039課件(PPT 44頁)

1、Access查詢的設(shè)計與創(chuàng)建第1頁，共44頁。第10講 項目四 Access查詢的設(shè)計與創(chuàng)建SQL查詢的創(chuàng)建內(nèi)容簡介教學(xué)目標(biāo)4.1 認(rèn)識查詢 4.2 創(chuàng)建選擇查詢 4.3 創(chuàng)建參數(shù)查詢 4.4 創(chuàng)建交叉表查詢4.5 創(chuàng)建操作查詢4.6 SQL查詢本講小結(jié) 習(xí)題四 Access數(shù)據(jù)庫程序設(shè)計 第2頁，共44頁。3內(nèi)容簡介查詢是Access數(shù)據(jù)庫的主要組件之一，也是Access數(shù)據(jù)庫中最強(qiáng)的功能之一。它是Access處理和分析數(shù)據(jù)的工具，能夠把多個表中的數(shù)據(jù)抽取出來，供用戶查看、更改和分析使用。本章主要介紹查詢的五種類型及根據(jù)具體使用目的選擇查詢的類別，學(xué)會創(chuàng)建查詢的方法及設(shè)計準(zhǔn)則。第3頁，共44

2、頁。4教學(xué)目標(biāo)了解查詢的概念和類型熟練掌握創(chuàng)建查詢的多種方法熟練掌握操作各類已建查詢的技巧第4頁，共44頁。54.1 認(rèn)識查詢 4.1.1 查詢的功能4.1.2 查詢的類型4.1.3 查詢的3種視圖 4.1.4 查詢的準(zhǔn)則第5頁，共44頁。64.1.1 查詢的功能1選擇字段2選擇記錄3分級和排序記錄4實現(xiàn)計算5創(chuàng)建新表6建立基于查詢的報表和窗體第6頁，共44頁。74.1.2 查詢的類型1選擇查詢2交叉表查詢3操作查詢4參數(shù)查詢5SQL查詢第7頁，共44頁。84.1.3 查詢的三種視圖 Access 2000中查詢具有三種視圖，分別是：“設(shè)計視圖”、“數(shù)據(jù)表視圖”和“SQL視圖”。 第8頁，共4

3、4頁。94.1.4 查詢的準(zhǔn)則1準(zhǔn)則中的運(yùn)算符運(yùn)算符是組成準(zhǔn)則的基本元素。Access中提供了算術(shù)運(yùn)算符、關(guān)系運(yùn)算符、邏輯運(yùn)算符和特殊運(yùn)算符等。（1）算術(shù)運(yùn)算符算術(shù)運(yùn)算符可進(jìn)行常見的算術(shù)運(yùn)算，按運(yùn)算的優(yōu)先級次序排列為：（乘方）、-（負(fù)號）、*（乘）、/（除）、MOD（求余）、+（加）、-（減）。（2）關(guān)系運(yùn)算符關(guān)系運(yùn)算符用來比較兩個值或者兩個表達(dá)式之間的關(guān)系，包括：（大于）、=（大于等于）、=（小于等于）、（不等于）、=（等于）。關(guān)系運(yùn)算的值為TRUE或FALSE。（3）邏輯運(yùn)算符邏輯運(yùn)算符用來實現(xiàn)邏輯運(yùn)算，按優(yōu)先級次序排序：NOT（非）、AND（并且）、OR（或）。邏輯運(yùn)算符通常與關(guān)系運(yùn)算符

4、一起使用，構(gòu)成復(fù)雜的用于判斷比較的表達(dá)式，運(yùn)算的值為TRUE或FALSE。（4）特殊運(yùn)算符（5）統(tǒng)計函數(shù)統(tǒng)計函數(shù)常用于對數(shù)據(jù)的統(tǒng)計分析。 第9頁，共44頁。104.1.4 查詢的三種視圖2準(zhǔn)則中的函數(shù)Access提供了大量的標(biāo)準(zhǔn)函數(shù)，如：數(shù)值函數(shù)、字符函數(shù)、日期時間函數(shù)、統(tǒng)計函數(shù)等。這些函數(shù)為用戶更好地構(gòu)造查詢準(zhǔn)則提供了極大的便利，也為用戶更準(zhǔn)確地進(jìn)行統(tǒng)計計算、實現(xiàn)數(shù)據(jù)處理提供了有效的方法。（1）數(shù)值函數(shù)數(shù)值函數(shù)用于數(shù)值的計算，常用的數(shù)值函數(shù)。（2）字符函數(shù)字符函數(shù)又稱文本處理函數(shù)，用于處理字符串。（3）日期和時間日期和時間函數(shù)常用于處理字段中的日期/時間值，可以通過日期和時間函數(shù)抽取日期的一

5、部分及時間的一部分。第10頁，共44頁。114.1.4 查詢的三種視圖3使用文本作為準(zhǔn)則在Access中建立查詢，經(jīng)常使用文本值作為查詢準(zhǔn)則。使用文本值作為查詢的準(zhǔn)則可以方便地限定查詢范圍和查詢的條件，實現(xiàn)一些相對簡單的查詢。4使用處理日期作為準(zhǔn)則在Access中建立查詢，經(jīng)常使用以計算或處理日期作為查詢準(zhǔn)則。使用計算或處理日期作為查詢的準(zhǔn)則可以方便地限定查詢時間范圍5使用字段的部分值作為準(zhǔn)則在Access中建立查詢，有時需要使用字段的部分值作為查詢的準(zhǔn)則。使用字段的部分值作為查詢的準(zhǔn)則可以方便地限定查詢范圍。6使用空字段值作為準(zhǔn)則空值是使用Null或空白來表示字段的值；空字符串是用雙引號括起

6、來的字符串，且雙引號中間沒有空格。第11頁，共44頁。124.2 創(chuàng)建選擇查詢4.2.1 簡單查詢向?qū)Р樵兿驅(qū)б话阌脕韯?chuàng)建相對比較簡單的查詢，或者用來初建基本查詢，以后再用設(shè)計視圖進(jìn)行修改。使用“簡單查詢向?qū)А笨梢詣?chuàng)建一個簡單的選擇查詢。第12頁，共44頁。13在“學(xué)生成績管理系統(tǒng)”數(shù)據(jù)庫中，創(chuàng)建一個“學(xué)生基本情況查詢”，查找學(xué)生的姓名、性別、專業(yè)和班級。 演示具體操作：【例4.1】第13頁，共44頁。14【例4.2】創(chuàng)建“學(xué)生成績查詢”，查找學(xué)生的姓名，課程名稱和成績。 演示具體操作：注意：在創(chuàng)建多表間的查詢時，前提條件是多個表間設(shè)置了“關(guān)系”，否則會提示報錯。第14頁，共44頁。154.2

7、.2 在設(shè)計視圖中創(chuàng)建條件查詢在日常工作中，用戶的查詢并非只是簡單的查詢，往往是帶有一定的條件。這就需要通過“設(shè)計”視圖來建立查詢。1認(rèn)識設(shè)計視圖2單個條件查詢【例4.3】創(chuàng)建“成績在80-90間的學(xué)生信息” 查詢，查找成績在80-90之間的學(xué)生姓名，課程名稱和成績。第15頁，共44頁。163多條件查詢【例4.4】創(chuàng)建“95年前工作的副教授信息” 查詢，查找工作時間在1995年之前（不包括1995年）且職稱為副教授的教師信息。4.2.2 在設(shè)計視圖中創(chuàng)建條件查詢第16頁，共44頁。174.2.3 在設(shè)計視圖中創(chuàng)建總計查詢 1在查詢中創(chuàng)建計算字段Access數(shù)據(jù)庫為經(jīng)常用到的數(shù)值匯總提供了豐富的

8、“總計”選項 ，即對查詢中的記錄組或全部數(shù)據(jù)進(jìn)行的計算，它包括總和、平均值、計數(shù)、最大值、最小值、標(biāo)準(zhǔn)偏差或方差等。這12個選項分為4類：分組（Group By）、聚合函數(shù)（Sum, Avg, Min, Max, Count, SrDev, Var, First, Last）、表達(dá)式（Expression）和限制條件（Where）使用時，只需要選擇或輸入這12個選項，即可完成相應(yīng)功能。2總計查詢建立查詢時，可能我們更關(guān)心的是記錄的統(tǒng)計結(jié)果，而不是表中的記錄。因此為了獲取這些數(shù)據(jù)，就需要使用Access提供的總計查詢功能，完成一定的計算查詢。第17頁，共44頁。18例題【例4.5】創(chuàng)建“各職稱教

9、師人數(shù)”查詢，統(tǒng)計各類職稱教師人數(shù)。演示具體操作：【例4.6】創(chuàng)建“學(xué)生平均成績”查詢，統(tǒng)計每個學(xué)生的各門課程的平均成績，并將結(jié)果按平均成績的降序排列。演示具體操作：第18頁，共44頁。194.3 創(chuàng)建參數(shù)查詢4.3.1 單參數(shù)查詢創(chuàng)建單參數(shù)查詢，就是在字段中指定一個參數(shù)，在執(zhí)行參數(shù)查詢時，由用戶輸入一個參數(shù)值。當(dāng)需要統(tǒng)計的數(shù)據(jù)在表中沒有相應(yīng)的字段，或者用于計算數(shù)據(jù)值來源于多個字段時，需要在“設(shè)計網(wǎng)格”中添加一個計算字段。計算字段是指根據(jù)一個或多個表中的一個或多個字段并使用表達(dá)式建立的新字段，通常計算字段都包含計算公式或函數(shù)條件。第19頁，共44頁。20例題【例4.7】在“學(xué)生成績管理系統(tǒng)”數(shù)

10、據(jù)庫中，創(chuàng)建一個“教師基本信息查詢”，按姓名查詢某教師的全部信息。演示具體操作：【例4.8】創(chuàng)建一個“某專業(yè)年齡小于24歲學(xué)生信息”，按用戶輸入的所屬專業(yè)查詢年齡小于24歲學(xué)生的姓名、性別、年齡和所屬專業(yè)。演示具體操作：第20頁，共44頁。214.3.2 多參數(shù)查詢用戶不僅可以建立單個參數(shù)查詢，如果需要也可以創(chuàng)建多參數(shù)查詢。在執(zhí)行多參數(shù)查詢時，用戶依次輸入多個參數(shù)值。【例4.9】創(chuàng)建一個“某課程成績區(qū)間學(xué)生信息”，按課程名稱和成績區(qū)間查詢學(xué)生成績信息。演示具體操作：第21頁，共44頁。224.4 創(chuàng)建交叉表查詢使用交叉表查詢計算和重構(gòu)數(shù)據(jù)，可以簡化數(shù)據(jù)分析。交叉表查詢計算數(shù)據(jù)的總和、平均值、計

11、數(shù)或其他類型的總計值，而這種數(shù)據(jù)又分為兩部分信息：一部分在數(shù)據(jù)表左側(cè)向下，另一部分在數(shù)據(jù)表頂端。第22頁，共44頁。23在創(chuàng)建交叉表查詢時，需要指定三類數(shù)據(jù)：一是在數(shù)據(jù)表左端的行標(biāo)題，它是把某一個字段或相關(guān)的數(shù)據(jù)放入指定的一行中；一是在數(shù)據(jù)表最上面的列標(biāo)題，它是把每一列指定的字段或表進(jìn)行統(tǒng)計，并將統(tǒng)計結(jié)果放入列中；三是放在數(shù)據(jù)表行與列交叉處的字段，用戶需要指定總計項。對于交叉表查詢，用戶只能指定一個總計類型的字段。4.4.1 認(rèn)識交叉表查詢 第23頁，共44頁。244.4.2 創(chuàng)建交叉表查詢創(chuàng)建交叉表查詢有兩種方法：“交叉表查詢向?qū)А焙筒樵儭霸O(shè)計”視圖。1使用“交叉表查詢向?qū)А?【例4.10】

12、在“學(xué)生成績管理系統(tǒng)”數(shù)據(jù)庫中，創(chuàng)建一個“統(tǒng)計各班男女生人數(shù)查詢”，顯示每個班級的男女生人數(shù)。2使用“設(shè)計”視圖【例4.11】創(chuàng)建一個“每位學(xué)生每門課成績查詢”，顯示學(xué)生姓名、課程名稱和成績。第24頁，共44頁。254.5 創(chuàng)建操作查詢4.5.1 認(rèn)識操作查詢操作查詢是指僅在一個操作中更改或移動記錄的查詢，操作查詢共有4種類型：生成表查詢、刪除查詢、更新查詢和追加查詢。操作查詢與選擇查詢、參數(shù)查詢及交叉表查詢有所不同。選擇查詢、參數(shù)查詢和交叉表查詢只是根據(jù)要求選擇數(shù)據(jù)，并不對表中的數(shù)據(jù)進(jìn)行修改，而操作查詢除了從表中選擇數(shù)據(jù)外，還對表中數(shù)據(jù)進(jìn)行修改。第25頁，共44頁。264.5.2 生成表查詢

13、生成表查詢就是將查詢的結(jié)果存在一個新表中，這樣就可以使用已有的一個或多個表中的數(shù)據(jù)創(chuàng)建表。【例4.12】創(chuàng)建一名為“生成60分以下學(xué)生信息查詢”，將成績小于60分的學(xué)生的“姓名”、“性別”、“所屬專業(yè)”、“課程名”和“成績”存儲到一個新表中，新表名為“成績60以下學(xué)生信息”。演示具體操作：第26頁，共44頁。274.5.3 追加查詢追加查詢就是將一組記錄追加到一個或多個表原有記錄的后面。追加查詢的結(jié)果是向有關(guān)表中自動添加記錄?！纠?.13】創(chuàng)建一名為“追加電商成績60-70間學(xué)生信息”，將電子商務(wù)專業(yè)的成績在60-70之間的學(xué)生的“姓名”、“性別”、“所屬專業(yè)”、“課程名”和“成績”追加到“成

14、績60以下學(xué)生信息”表中。演示具體操作：第27頁，共44頁。28更新查詢就是對一個或多個表中的一組記錄作全局的更改。使用更新查詢，可以更改已有表中的數(shù)據(jù)。【例4.14】創(chuàng)建一名為“成績加5分”更新查詢，將“成績60以下學(xué)生信息”表中的高等數(shù)學(xué)課程成績都增加5分。演示具體操作：4.5.4 更新查詢第28頁，共44頁。29刪除查詢就是從已有的一個或多個表中刪除滿足查詢條件的記錄?！纠?.15】創(chuàng)建一個名為“刪除60分以下學(xué)生信息”查詢，該查詢將“成績60以下學(xué)生信息”表中成績低于或等于60分的記錄刪除。演示具體操作：注意：刪除查詢運(yùn)行一定要慎重，記錄刪除后不能撤消所做的更改。4.5.5 刪除查詢第

15、29頁，共44頁。30SQL查詢是用戶使用SQL語句自定義創(chuàng)建的查詢，它是一個用于顯示當(dāng)前查詢的SQL語句窗口，在這個窗口里用戶可以查看和改變SQL語句，從而達(dá)到查詢的目的。SQL是Structured Query Language（結(jié)構(gòu)化查詢語言）的縮寫。SQL是專為數(shù)據(jù)庫而建立的操作命令集，是一種功能齊全的數(shù)據(jù)庫語言。SQL意為結(jié)構(gòu)化查詢語言，可用于定義、查詢、更新、管理關(guān)系型數(shù)據(jù)庫系統(tǒng)。SQL是一種非過程語言，易學(xué)易用，語句由近似自然語言的英語單詞組成4.6 SQL查詢第30頁，共44頁。31SQL語句按其功能的不同可以分為以下三大類：1數(shù)據(jù)定義語句（DDL，Data-Definitio

16、n Language）定義數(shù)據(jù)庫的邏輯結(jié)構(gòu)，包括定義數(shù)據(jù)庫、基本表、視圖和索引4部分。2數(shù)據(jù)操作語句（DML，Data-Manipulation Language）包括數(shù)據(jù)查詢和數(shù)據(jù)更新兩大類操作，其中數(shù)據(jù)更新又包括插入、刪除和更新三種操作。3數(shù)據(jù)控制語句（DCL，Data-Control Language） 對用戶訪問數(shù)據(jù)的控制有基本表和視圖的授權(quán)、完整性規(guī)則的描述，事務(wù)控制語句等。4.6.1 SQL基礎(chǔ)知識第31頁，共44頁。32使用SQL語句，可以直接在SQL視圖中修改已建查詢中的準(zhǔn)則。1數(shù)據(jù)查詢的一般格式SELECT AS 別名FROM WHERE 條件表達(dá)式GROUP BY 列名集合

17、 HAVING 組條件表達(dá)式ORDER BY 列名ASC|DESC在SELECT語法格式中，大寫字母為SQL保留字，尖括號所括部分為必須選的內(nèi)容，方括號所括部分為可選擇的內(nèi)容。4.6.2 使用SQL修改查詢中的準(zhǔn)則第32頁，共44頁。332常用數(shù)據(jù)查詢【例4.16】用SQL修改“95年前工作的副教授信息”查詢，使查詢的結(jié)果顯示為：“95年前工作的教授信息”。演示具體操作：4.6.2 使用SQL修改查詢中的準(zhǔn)則第33頁，共44頁。34SQL查詢分為聯(lián)合查詢、傳遞查詢、數(shù)據(jù)定義和子查詢四種。1創(chuàng)建聯(lián)合查詢 聯(lián)合查詢功能是由UNION子句實現(xiàn)的，其含義是將兩個SELECT命令的查詢結(jié)果合并成一個查詢

18、結(jié)果。子句格式：UNIONALL其中ALL表示結(jié)果全部合并。若沒有ALL，則重復(fù)的記錄將被自動取掉。合并的規(guī)則是：（1）不能合并子查詢的結(jié)果。（2）兩個SELECT命令必須輸出同樣的列數(shù)。（3）兩個表各相應(yīng)列出的數(shù)據(jù)類型必須相同，數(shù)字和字符不能合并。（4）僅最后一個SELECT命令中可以用ORDER BY子句，且排序選項必須用數(shù)字說明。4.6.2 創(chuàng)建SQL查詢 第34頁，共44頁。35創(chuàng)建名為“合并學(xué)生信息”的查詢，查詢“成績60以下學(xué)生信息”表中學(xué)生信息和“學(xué)生”表中營銷與策劃專業(yè)學(xué)生信息，顯示學(xué)生的姓名、性別和所屬專業(yè)字段。演示具體操作：【例4.17】第35頁，共44頁。362創(chuàng)建傳遞查

19、詢 傳遞查詢是SQL特定查詢之一，Access傳遞查詢是自己并不執(zhí)行而傳遞該另一個數(shù)據(jù)庫來執(zhí)行的查詢。傳遞查詢可直接將命令發(fā)送到ODBC數(shù)據(jù)庫服務(wù)器中。使用傳遞查詢時，不必與服務(wù)器上的表鏈接，就可以直接使用相應(yīng)的表。3建立數(shù)據(jù)定義查詢 數(shù)據(jù)定義查詢與其他查詢不同，利用它可以直接創(chuàng)建、刪除或更改表，或者在當(dāng)數(shù)據(jù)庫中創(chuàng)建索引。在數(shù)據(jù)定義查詢中要輸入SQL語句，每個數(shù)據(jù)定義查詢只能由一個數(shù)據(jù)定義語句組成。4.6.2 創(chuàng)建SQL查詢第36頁，共44頁。37（1）表的定義create table （3404.6.4 刪除記錄語句第41頁，共44頁。42INSERT INTO 表名（字段名，字段名）val