數(shù)據(jù)庫與程序設(shè)計(jì)-04 -第3章查詢

回顧數(shù)據(jù)查詢的基本概念查詢的分類查詢準(zhǔn)則選擇查詢與計(jì)算查詢復(fù)習(xí)題1、在Access查詢的條件表達(dá)式中要表示任意個(gè)字符，應(yīng)使用通配符

。2、在Access查詢的條件表達(dá)式中要表示任意單個(gè)字符，應(yīng)使用通配符

。3、在查詢?cè)O(shè)計(jì)視圖中，為“成績”表的“成績”字段設(shè)置的條件為：Between80And90，則下列表達(dá)式中與所設(shè)條件功能相同的是（

）。A.成績.成績>=80AND成績.成績<=90B.成績.成績>=80AND成績.成績<90C.80<=成績.成績<=90D.80<成績.成績<90復(fù)習(xí)題4、條件“Not工資額>3000”的含義是（

）。A.選擇工資額大于3000的記錄B.選擇工資額小于3000的記錄C.選擇除了工資額大于3000之外的記錄D.選擇除了字段工資額之外的字段，且大于3000的記錄5、在建立查詢時(shí)，若要篩選出圖書編號(hào)是“01”或“02”的記錄，可以在查詢?cè)O(shè)計(jì)視圖準(zhǔn)則行中輸入（

）。A.“01”O(jiān)R“02”B.“01”AND“02”C.In(“01”AND“02”)D.NotIn(“01”AND“02”)復(fù)習(xí)題6、在學(xué)生表中建立查詢，“姓名”字段的查詢條件設(shè)置為“IsNull”，運(yùn)行該查詢后，顯示的記錄是（

）。A.姓名字段中包含空格的記錄B.姓名字段為空的記錄C.姓名字體中不包含空格的記錄D.姓名字體不為空的記錄第三章

數(shù)據(jù)查詢SQL查詢Lesson06操作查詢Lesson05交叉表查詢與參數(shù)查詢Lesson043.4交叉表查詢與參數(shù)查詢3.4.1認(rèn)識(shí)交叉表查詢?cè)趧?chuàng)建交叉表查詢時(shí)，要設(shè)置行標(biāo)題、列標(biāo)題和值。行標(biāo)題把某一字段或相關(guān)數(shù)據(jù)放入指定的行中，行標(biāo)題最多可以選擇3個(gè)字段；列標(biāo)題是對(duì)每一列指定的字段進(jìn)行統(tǒng)計(jì)，并將統(tǒng)計(jì)結(jié)果放入該列，列標(biāo)題只能選擇1個(gè)字段；值是行與列交叉處上的字段，該字段要指定一個(gè)總計(jì)項(xiàng)，比如平均值、計(jì)數(shù)等，交叉表查詢中只能指定一個(gè)總計(jì)類型的字段。例9創(chuàng)建交叉表查詢，統(tǒng)計(jì)各系中的男女教師人數(shù)。分析：這個(gè)查詢的數(shù)據(jù)源是一個(gè)表，可以使用查詢向?qū)韯?chuàng)建。行標(biāo)題是“系別”字段，列標(biāo)題是“性別”字段，“行”與“列”交叉處的值，可以對(duì)“職工號(hào)”計(jì)數(shù)得到。3.4.2創(chuàng)建交叉表查詢3.4.2創(chuàng)建交叉表查詢例10創(chuàng)建交叉表查詢，統(tǒng)計(jì)各門課程每個(gè)系學(xué)生的最高成績數(shù)據(jù)源是？字段是？行列值設(shè)置3.4.3參數(shù)查詢例11創(chuàng)建查詢，當(dāng)用戶從鍵盤上輸入職工號(hào)或姓名時(shí)，顯示相應(yīng)的教師任課情況，包括“職工號(hào)”、“姓名”、“職稱”、“課程名稱”和“學(xué)時(shí)”5個(gè)字段。數(shù)據(jù)源是？字段是？參數(shù)類型？查詢準(zhǔn)則？3.4.3參數(shù)查詢參數(shù)查詢運(yùn)行結(jié)果3.4.3參數(shù)查詢例12：創(chuàng)建查詢，當(dāng)用戶從鍵盤上輸入學(xué)生的出生月份時(shí)，顯示學(xué)生相應(yīng)的信息，包括“學(xué)號(hào)”、“姓名”和“出生日期”。這是一個(gè)單參數(shù)查詢，與上例不同的是參數(shù)值“月份”需要對(duì)數(shù)據(jù)源表的“出生日期”字段計(jì)算得到，月份值=Month([出生日期])。3.4.3參數(shù)查詢方法一方法二例13：查詢工作日期在某個(gè)日期范圍內(nèi)的教師，當(dāng)用戶輸入開始日期和結(jié)束日期時(shí)，顯示工作日期在這個(gè)范圍內(nèi)的教師信息，包括“職工號(hào)”、“姓名”、“職稱”和“工作日期”4個(gè)字段。這是一個(gè)多參數(shù)查詢，參數(shù)值是兩個(gè)日期。3.4.3參數(shù)查詢3.4.3參數(shù)查詢3.5操作查詢操作查詢是Access查詢對(duì)象中的重要組成部分，它用于對(duì)數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行管理操作，能夠通過一次操作完成多條記錄的修改。操作查詢共有4種類型：生成表查詢更新查詢追加查詢刪除查詢操作查詢的創(chuàng)建過程十分簡單，只需先打開一個(gè)查詢?cè)O(shè)計(jì)視圖，Access的菜單欄隨即增加“查詢”菜單，選中“查詢”菜單的效果如圖所示，選擇相應(yīng)的菜單項(xiàng)即創(chuàng)建相應(yīng)的查詢。動(dòng)手練一練1、創(chuàng)建交叉表查詢，統(tǒng)計(jì)每門課程各系不及格的學(xué)生人數(shù)。2、創(chuàng)建單參數(shù)查詢，查詢運(yùn)行時(shí)，通過輸入課程號(hào)或課程名稱查詢?cè)撻T課程的學(xué)生成績，查詢結(jié)果顯示“課程名稱”、“學(xué)號(hào)”、“姓名”和“成績”4個(gè)字段。3、創(chuàng)建多參數(shù)查詢，查詢運(yùn)行時(shí)，通過輸入系別和職稱，顯示該系具有相應(yīng)職稱的教師情況，查詢結(jié)果顯示“系別”、“職稱”、“姓名”、“學(xué)位”和“聯(lián)系電話”5個(gè)字段。3.5.1 生成表查詢生成表查詢利用一個(gè)或多個(gè)表中的全部或部分?jǐn)?shù)據(jù)創(chuàng)建新表。生成表查詢的應(yīng)用范圍十分廣泛，例如可用于制作表的備份副本、創(chuàng)建用于保存舊記錄的歷史表等。創(chuàng)建的新表中數(shù)據(jù)是當(dāng)前數(shù)據(jù)庫中數(shù)據(jù)的子集，之后數(shù)據(jù)庫中源數(shù)據(jù)記錄的變更，則不能在生成的表中體現(xiàn)。【例3-14】將考試成績不及格的學(xué)生的數(shù)據(jù)信息存放到新表中，新表命名為“補(bǔ)考學(xué)生名單”。該例題完成后，數(shù)據(jù)庫的表對(duì)象中應(yīng)增加一張表（如圖），表中數(shù)據(jù)為考試成績?cè)?0分以下的學(xué)生信息。3.5.2刪除查詢刪除查詢是指刪除符合設(shè)定條件的記錄的查詢。在數(shù)據(jù)庫的使用過程中，有些數(shù)據(jù)不再有意義，可以將其刪除，可通過創(chuàng)建并執(zhí)行刪除查詢的方法完成刪除操作?！纠?-15】將例題3-14生成的“補(bǔ)考學(xué)生名單”表中，課程名稱為“高等數(shù)學(xué)”的記錄刪除。本例中需要添加到查詢中的表只有一個(gè)，意在刪除數(shù)據(jù)表中符合條件的記錄（包括該記錄對(duì)應(yīng)的所有字段值），因此，選擇刪除的字段應(yīng)是所有字段，即“補(bǔ)考學(xué)生名單.*”。注：1）刪除查詢不能撤銷。2）比較第二章介紹的數(shù)據(jù)表中一條記錄的刪除方法，如果要?jiǎng)h除符合設(shè)定條件的一組記錄，就可以使用刪除查詢操作完成。3.5.3更新查詢維護(hù)數(shù)據(jù)庫時(shí)，常常需要對(duì)符合一定條件的記錄作統(tǒng)一修改，這些操作可通過更新查詢完成。例如，將特定出版商的所有書籍的價(jià)格上調(diào)10%的比例，教師的實(shí)發(fā)工資分等級(jí)扣除所得稅，課程的總評(píng)成績由平時(shí)成績和期末考試成績的平均構(gòu)成等等，均可由更新查詢來實(shí)現(xiàn)數(shù)據(jù)維護(hù)。更新查詢的一次運(yùn)行可以更改多行的內(nèi)容，和刪除查詢一樣，更新查詢的操作也是無法撤銷執(zhí)行的操作，如有需要，在執(zhí)行該查詢前，可先備份數(shù)據(jù)表。【例3-16】學(xué)生課程信息表中，把課程類型是必修課且學(xué)時(shí)數(shù)在60以上的課程，均上調(diào)5個(gè)學(xué)時(shí)。創(chuàng)建更新查詢時(shí)，需要注意的細(xì)節(jié)是：定義要更新行的過濾條件；用什么樣的值或表達(dá)式替代原來的字段值。

例3-16更新查詢執(zhí)行前后數(shù)據(jù)表中數(shù)據(jù)對(duì)照提示對(duì)話框3.5.4追加查詢?cè)谶M(jìn)行數(shù)據(jù)庫維護(hù)時(shí)，常常需要將某個(gè)表中符合一定條件的記錄添加到另一張表中。創(chuàng)建追加查詢時(shí)，首先必須明確：要追加的記錄從哪張或哪幾張表（源表）中來；這些記錄將送往哪個(gè)數(shù)據(jù)庫表（目標(biāo)表）中去。若源表與目標(biāo)表相同，則在表內(nèi)進(jìn)行復(fù)制。追加查詢類似于生成表查詢，但該查詢將記錄復(fù)制到已存在的表中。【例3-17】將學(xué)生成績表中考試成績?cè)?0至65分的學(xué)生添加到例題3-14生成的“補(bǔ)考學(xué)生名單”表。

創(chuàng)建追加查詢的另一個(gè)關(guān)鍵點(diǎn)在于：復(fù)制源表中選定的若干列，所選定的記錄數(shù)據(jù)必須要與目標(biāo)表中的對(duì)應(yīng)各列數(shù)據(jù)兼容，即源列與目標(biāo)列的數(shù)據(jù)類型盡量保持一致。本例中的學(xué)號(hào)字段（選自學(xué)生基本信息表）、姓名字段（選自學(xué)生基本信息表）、課程名稱字段（選自學(xué)生課程信息表）以及成績字段（選自學(xué)生成績表）均與“補(bǔ)考學(xué)生名單”表中各字段類型保持一致。動(dòng)手練一練1、生成表查詢。將“學(xué)生基本信息表”中政治面貌為“黨員”的學(xué)生記錄存入“學(xué)生黨員信息表”。2、追加查詢。將“學(xué)生基本信息表”中政治面貌為“團(tuán)員”的若干學(xué)生記錄追加到“學(xué)生黨員信息表”中。3、更新查詢。在“學(xué)生黨員信息表”中，更新政治面貌為“團(tuán)員”的學(xué)生記錄，將“團(tuán)員”修改為“黨員”。4、刪除查詢。“學(xué)生基本信息表”中的學(xué)生記錄中，若該記錄已經(jīng)存放在“學(xué)生黨員信息表”中，則在“學(xué)生基本信息表”中將其刪除。3.6SQL查詢SQL（StructureQueryLanguage）是“結(jié)構(gòu)化查詢語言”的英文縮寫，它是用于訪問和處理數(shù)據(jù)庫的標(biāo)準(zhǔn)計(jì)算機(jī)語言，亦作為數(shù)據(jù)庫領(lǐng)域中最為廣泛的數(shù)據(jù)庫查詢語言。SQL是用于訪問和處理數(shù)據(jù)庫的標(biāo)準(zhǔn)計(jì)算機(jī)語言，亦作為數(shù)據(jù)庫領(lǐng)域中最為廣泛的數(shù)據(jù)庫查詢語言，應(yīng)用于各類數(shù)據(jù)庫軟件。MSAccess中對(duì)數(shù)據(jù)庫進(jìn)行的操作，例如，數(shù)據(jù)表的創(chuàng)建、表字段的修改、插入新記錄、刪除數(shù)據(jù)、執(zhí)行數(shù)據(jù)查詢等，均能夠通過SQL來實(shí)現(xiàn)。SQL還能實(shí)現(xiàn)聯(lián)合查詢、傳遞查詢、數(shù)據(jù)定義查詢和子查詢等其他4種查詢操作。一個(gè)Access查詢對(duì)象實(shí)質(zhì)上是一條SQL語句，而Access提供的查詢?cè)O(shè)計(jì)視圖實(shí)質(zhì)上是為我們提供了一個(gè)編寫相應(yīng)SQL語句的可視化工具。3.6.1查看查詢中的SQL語句在Access提供的查詢?cè)O(shè)計(jì)視圖上，通過直觀的操作，可以迅速地建立所需要的Access查詢對(duì)象，實(shí)質(zhì)上也就是編寫一條SQL語句。例3-18 從學(xué)生基本信息表中查找出中文系學(xué)生的姓名、性別及政治面貌。創(chuàng)建選擇查詢來完成該項(xiàng)任務(wù)，并在其設(shè)計(jì)視圖打開的前提下，單擊主菜單欄“視圖”菜單下的“SQL視圖”選項(xiàng)（或在快捷菜單中選擇“SQL視圖”選項(xiàng)）。切換至的SQL視圖中生成了該選擇查詢的SQL語句：主要介紹SQL語法的兩個(gè)部分：數(shù)據(jù)操作語句DML：

SELECT數(shù)據(jù)查詢語句 UPDATE數(shù)據(jù)更新語句 INSERTINTO數(shù)據(jù)插入語句 DELETE數(shù)據(jù)刪除語句數(shù)據(jù)定義語句DDL：

CREATETABLE創(chuàng)建數(shù)據(jù)庫中的表 ALTERTABLE在已有的表中添加、刪除或修改字段 DROPTABLE刪除表3.6.2SQL基本語法（1）SELECT語句查詢語句SELECT用于查詢數(shù)據(jù)庫并檢索出符合指定條件的數(shù)據(jù)，該語句的完整語法格式如下：SELECT[predicate]{*|talbe.*|[table.]field1[AS

alias1][,[table.]field2[AS

alias2][,…]]}

FROMtableexpression[,...][INexternaldatabase][WHERE...][GROUPBY...][HAVING...][ORDERBY...[ASC|DESC]];表3.3SELECT語句關(guān)鍵字匯總參量取值及其含義說明PredicateALL|DISTINCT|DISTINCTROW|TOP謂詞，用于限定返回記錄數(shù)目及特征。默認(rèn)為ALL*全部字段從指定的表中選擇該表的全部字段Table表的名稱數(shù)據(jù)的來源Field1字段名稱包含在表中的字段Alias1別名給某列數(shù)據(jù)取別名，用來作列表頭。Tableexpression表的名稱要查找的數(shù)據(jù)包含在這些表中Externaldatabase數(shù)據(jù)庫的名稱Tableexpression指定的表包含在Externaldatabase指定的數(shù)據(jù)庫中WHERE條件表達(dá)式過濾出滿足條件的記錄GROUPBY字段名列表根據(jù)所列字段名分組ORDERBY字段名列表根據(jù)所列字段名排序HAVING條件表達(dá)式分組后，過濾出滿足條件的記錄SELECT語句例題匯總Select子句，Predicate參量【例3-19】從學(xué)生課程信息表中查找出第1至5條記錄。SELECTTOP5*FROM學(xué)生課程信息表;【例3-20】請(qǐng)用一個(gè)查詢語句找出：教學(xué)數(shù)據(jù)庫中總共有多少個(gè)系別以及具體系名。SELECTdistinct系別FROM學(xué)生基本信息表;SELECTdistinct系別AS系名稱FROM學(xué)生基本信息表；SELECT語句例題匯總Where子句，orderby子句【例3-21】從教師信息表中取出姓名、性別、職稱、學(xué)位字段，要求性別為男，并按職稱排序，職稱相同則按學(xué)位排序。SELECT姓名,性別,職稱,學(xué)位FROM教師信息表WHERE性別='男'ORDERBY職稱,學(xué)位;【例3-22】查詢教師姓名，參加工作日期，且按工作時(shí)間降序排列。SELECT姓名,工作日期FROM教師信息表ORDERBY工作日期DESCSELECT語句例題匯總在SELECT語句中，GROUPBY子句將查詢的結(jié)果做分組統(tǒng)計(jì)，分組的依據(jù)是GROUPBY后的字段。簡單舉例：SELECT系別,count(學(xué)號(hào))FROM學(xué)生基本信息表GROUPBY系別;SELECT系別,count(學(xué)號(hào))FROM學(xué)生基本信息表GROUPBY系別HAVING系別="法律"or系別="數(shù)學(xué)";SELECTAvg(學(xué)時(shí))as平均學(xué)時(shí)FROM學(xué)生課程信息表;函數(shù)說明MIN返回一個(gè)指定列中最小的數(shù)值MAX返回一個(gè)指定列中最大的數(shù)值SUM返回一個(gè)指定列中所有數(shù)值的總和AVG返回一個(gè)指定列中所有數(shù)值的平均值COUNT返回一個(gè)指定列中所有數(shù)值數(shù)目(NULL不計(jì)入)COUNT（*）返回一個(gè)表中記錄的行數(shù)表3.4SQL統(tǒng)計(jì)函數(shù)（2）INSERTINTO語句INSERTINTO語句用于向數(shù)據(jù)表中插入或者增加一行數(shù)據(jù)，其語法格式為：INSERTINTOtablename(first_column,…last_column)VALUES(first_value,…,last_value);該語句首先指定數(shù)據(jù)庫表的名稱，將要插入新數(shù)據(jù)的各個(gè)字段名寫在緊跟表名的小括號(hào)中，各個(gè)字段值放入VALUES語句中。【例3-23】將本課程“Access數(shù)據(jù)庫程序設(shè)計(jì)”的相關(guān)信息添加到“學(xué)生課程信息表”中。課程相關(guān)信息有：課程號(hào)為134，是必修課，64學(xué)時(shí)。INSERTINTO學(xué)生課程信息表(課程號(hào),課程名稱,課程類別,學(xué)時(shí))VALUES("134","Access數(shù)據(jù)庫程序設(shè)計(jì)","必修課",64);（3）DELETE語句DELETE語句用來從數(shù)據(jù)表中刪除記錄，其語法格式為：DELETEFROMtablename[WHERE…]如同SELECT語句，WHERE后跟條件表達(dá)式，DELECT語句從“tablename”指定的表中刪除滿足條件的記錄。例如：將例題3-23中新增加的記錄編號(hào)為134的記錄刪除。DELETEFROM學(xué)生課程信息表WHERE課程號(hào)="134";慎用類似如下的語句：

DELETEFROM學(xué)生課程信息表（4）UPDATE語句UPDATE語句用于更新或者改變符合指定條件的記錄，它是通過構(gòu)造WHERE子句來限定條件，其語句格式如下：UPDATETableNameSETcolumn1=newvalue1[,column2=newvalue2…][WHERE…]【例3-24】把王雪麗（學(xué)號(hào)900156）、付辰（學(xué)號(hào)900483）、夏毅（學(xué)號(hào)910131）、張凡（學(xué)號(hào)900617）四名同學(xué)的政治面貌由群眾修改為團(tuán)員。UPDATE學(xué)生基本信息表SET政治面貌="團(tuán)員"WHERE學(xué)號(hào)In("900156","900483","910131","900617");(1)CREATETABLE語句CREATETABLE語句用于創(chuàng)建數(shù)據(jù)庫中的表，其語法為CREATETABLETableName(ColumnName1DataType,ColumnName2DataType,ColumnName3DataType,

……);ColumnName指字段名，各字段的數(shù)據(jù)類型（DataType）寫在字段名的后部，中間以空格隔開。CREATETABLE教材信息表(書名char(40),作者char(10),出版社char(15),出版日期date,價(jià)格money,ISBNchar(17));(2)ALTERTABLEALTERTABLE語句用于在已有的表中添加、刪除或修改字段。例如：在教材信息表增加一個(gè)字段“影印本”，類型為是/否型，表結(jié)構(gòu)修改語句為：ALTERTABLE教材信息表ADD影印本bit;修改影印本字段的類型為char，寬度為2，表結(jié)構(gòu)修改語句為：ALTERTABLE教材信息表ALTER影印本char(2);刪除添加的“影印本”字段，表結(jié)構(gòu)修改語句為：ALTERTABLE教材信息表DROPCOLUMN影印本;(3)DROPTABLE語句DROPTABLE語句用于刪除表,其結(jié)構(gòu)、屬性及索引也會(huì)被刪除。例如：DROPTABLE教材信息表;語句執(zhí)行后，教材信息表連同表中數(shù)據(jù)一并被刪除。3.6.3聯(lián)合查詢聯(lián)合查詢的UNION運(yùn)算符可以將兩個(gè)或兩個(gè)以上SELECT語句的查詢結(jié)果集合合并，使之作為一個(gè)結(jié)果集合顯示，即執(zhí)行聯(lián)合查詢。UNION的語法格式為：Select_statementUNION[ALL]SelectStatement[UNION[ALL]SelectStatement][…]其中，select_statement為待聯(lián)合的SELECT查詢語句。ALL選項(xiàng)表示將所有行合并到結(jié)果中，不指定該項(xiàng)時(shí)，被聯(lián)合查詢結(jié)果中的重復(fù)行將只保留一行。需注意兩點(diǎn)：（1）聯(lián)合查詢要求各SELECT語句的列必須匹配，即列與數(shù)據(jù)類型匹配。（2）聯(lián)合查詢結(jié)果的列標(biāo)題為第一個(gè)查詢語句的列標(biāo)題。3.6.3聯(lián)合查詢【例3-25】物理系的男同學(xué)由理學(xué)系的江小洋老師帶隊(duì)，參加周末的植樹活動(dòng)，請(qǐng)列出參加活動(dòng)的學(xué)生名單及老師的電話號(hào)碼。SELECT姓名,系別FROM學(xué)生基本信息表WHERE系別="物理"AND性別="男"UNIONSELECT姓名,聯(lián)系電話FROM教師信息表WH