《網(wǎng)絡(luò)數(shù)據(jù)庫應(yīng)用教程》全套PPT電子課件教案-第4章　查詢及其應(yīng)用.ppt

第4章 查詢及其應(yīng)用,4.1 認(rèn) 識(shí) 查 詢 4.2 查詢的基本操作 4.3 sql查詢,4.1 認(rèn) 識(shí) 查 詢,4.1.1 查詢的目的 查詢是按照一定的條件或要求對(duì)數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行檢索。,4.1.2 查詢的種類 1選擇查詢 選擇查詢是最常見的查詢類型，它可以從當(dāng)前數(shù)據(jù)庫中的一個(gè)或多個(gè)表中按照一定的條件檢索數(shù)據(jù)，也可以使用選擇查詢對(duì)記錄進(jìn)行分組，并對(duì)記錄作總計(jì)、計(jì)數(shù)、平均值以及其他類型的統(tǒng)計(jì)計(jì)算。,2參數(shù)查詢 參數(shù)查詢?cè)趫?zhí)行時(shí)顯示對(duì)話框以提示用戶輸入相關(guān)信息，然后按用戶輸入內(nèi)容執(zhí)行相應(yīng)的查詢操作。 3交叉表查詢 交叉表查詢把一個(gè)表或查詢作為數(shù)據(jù)源，將表或查詢中的某個(gè)字段的統(tǒng)計(jì)值（合計(jì)、計(jì)算以及平均）作為查詢結(jié)果，并將它們分組，一組列在數(shù)據(jù)表的左側(cè)，一組列在數(shù)據(jù)表的上部。,4操作查詢 操作查詢是在一個(gè)查詢中更改多條記錄的查詢，共有4種類型： （1）刪除查詢 刪除查詢是從一個(gè)或多個(gè)表中刪除滿足條件的一組記錄。 （2）更新查詢 更新查詢是對(duì)一個(gè)或多個(gè)表中的一組記錄做全局的更改。,（3）追加查詢 追加查詢是從一個(gè)或多個(gè)表中將滿足條件的一組記錄追加到一個(gè)表的尾部。 （4）生成表查詢 生成表查詢是使用一個(gè)或多個(gè)表中的全部或部分?jǐn)?shù)據(jù)新建表。,5sql查詢 sql 查詢是用戶使用sql語句創(chuàng)建的查詢。除使用符合sql語法規(guī)定的sql命令外，還支持聯(lián)合查詢、傳遞查詢、數(shù)據(jù)定義查詢和子查詢等特殊查詢。 （1）聯(lián)合查詢 聯(lián)合查詢是將多個(gè)查詢結(jié)果合并起來，系統(tǒng)會(huì)自動(dòng)去掉重復(fù)的記錄。,（2）傳遞查詢 傳遞查詢可直接將查詢命令發(fā)送到odbc數(shù)據(jù)庫服務(wù)器。 （3）數(shù)據(jù)定義查詢 數(shù)據(jù)定義查詢用于創(chuàng)建、刪除、更改表或創(chuàng)建數(shù)據(jù)庫中的索引。 （4）子查詢 子查詢是包含了另一個(gè)選擇查詢或操作查詢中的sql select語句的查詢。,4.2 查詢的基本操作,access 2000為查詢對(duì)象提供了3種視圖：設(shè)計(jì)視圖、sql視圖和數(shù)據(jù)表視圖。查詢的設(shè)計(jì)視圖主要用于創(chuàng)建和修改查詢，sql視圖用于查看和修改sql語句，數(shù)據(jù)表視圖以行列方式查看查詢結(jié)果中的數(shù)據(jù)。從查詢的設(shè)計(jì)視圖切換到另外兩種視圖的方法是，單擊“查詢?cè)O(shè)計(jì)”工具欄上“視圖”按鈕，打開“視圖”列表，選擇“sql視圖”或“數(shù)據(jù)表視圖”。,4.2.1 建立查詢的方式 創(chuàng)建查詢有兩種方式，一種是使用向?qū)?chuàng)建查詢，另一種是使用查詢的設(shè)計(jì)視圖創(chuàng)建查詢。,1使用向?qū)?chuàng)建查詢 access 2000提供了4種查詢向?qū)В汉?jiǎn)單查詢向?qū)?、交叉表查詢向?qū)?、查找重?fù)項(xiàng)查詢向?qū)Ш筒檎也黄ヅ漤?xiàng)查詢向?qū)?。啟?dòng)查詢向?qū)У姆椒ㄊ牵涸跀?shù)據(jù)庫窗口的“對(duì)象”欄中單擊“查詢”，然后單擊數(shù)據(jù)庫窗口上的“新建”按鈕或者選擇access 主窗口的菜單欄中的“插入”“查詢”命令，可打開圖4-2所示的“新建查詢”對(duì)話框。,2使用查詢的設(shè)計(jì)視圖創(chuàng)建查詢 利用查詢的設(shè)計(jì)視圖創(chuàng)建查詢的方法如下。 （1）在數(shù)據(jù)庫窗口中打開 “新建查詢”對(duì)話框。 （2）在“新建查詢”對(duì)話框中選擇“設(shè)計(jì)視圖”選項(xiàng)，然后單擊“確定”按鈕，出現(xiàn) “顯示表”對(duì)話框。,（3）在“顯示表”對(duì)話框中，選擇查詢所需要的表名或查詢名。 （4）在查詢的設(shè)計(jì)視圖窗口中，設(shè)置查詢的條件。 “字段”設(shè)置查詢結(jié)果中要顯示的字段?！芭判颉敝付ㄔ诓樵兘Y(jié)果中記錄按哪個(gè)字段中的數(shù)據(jù)進(jìn)行排序，它有兩種類型：升序和降序?！皽?zhǔn)則”中的每一列準(zhǔn)則指定了篩選記錄的限制條件。,“顯示”決定了選定的字段是否顯示在查詢結(jié)果中。 （5）保存查詢的定義 。 （6）如果要查看查詢結(jié)果，單擊access主窗口工具欄上的“運(yùn)行”按鈕。,4.2.2 創(chuàng)建選擇查詢 例4.1 在“課程管理”數(shù)據(jù)庫中，創(chuàng)建一個(gè)查詢“高數(shù)成績(jī)查詢”，查詢結(jié)果中包含student表中的snumber和sname字段，scores表中的grade字段，schedule表中的ctitle字段，并且要求只查詢高等數(shù)學(xué)課程的成績(jī)信息。,4.2.3 創(chuàng)建交叉表查詢 在使用交叉表查詢向?qū)?chuàng)建交叉表時(shí)，查詢所依據(jù)的字段只能出自同一個(gè)表或查詢。如果從多個(gè)表中創(chuàng)建交叉表查詢，需要先創(chuàng)建一個(gè)查詢，該查詢中包含交叉表查詢中所需的所有字段，然后再使用交叉表查詢向?qū)А?例4.2 在“課程管理”數(shù)據(jù)庫創(chuàng)建一個(gè)交叉表查詢，用來查找每個(gè)學(xué)生各門課程的成績(jī)。 （1）打開查詢的設(shè)計(jì)視圖，將student表、schedule表和scores表添加到查詢的設(shè)計(jì)視圖窗口中。 （2）在菜單欄上選擇“查詢”“交叉表查詢”命令，查詢的設(shè)計(jì)視圖窗口中的查詢?cè)O(shè)計(jì)網(wǎng)格部分增添了“總計(jì)”行和“交叉表”行。,（3）首先指定一個(gè)或多個(gè)行標(biāo)題。然后指定一個(gè)列標(biāo)題。最后指定需要進(jìn)行統(tǒng)計(jì)的字段。 （4）保存。 （5）運(yùn)行 。,4.2.4 創(chuàng)建參數(shù)查詢 例4.3 在“課程管理”數(shù)據(jù)庫中的student表上創(chuàng)建一個(gè)參數(shù)查詢，根據(jù)輸入的學(xué)生政治面目，將所有與輸入政治面目相同的學(xué)生的snumber、sname、ssex字段的信息顯示出來。,4.2.5 創(chuàng)建操作查詢 1生成表查詢 例4.4 從“課程管理”數(shù)據(jù)庫中的schedule表中選取courseid、ctitle和coursehour字段，從textbook表中選取tbtitle字段，創(chuàng)建一個(gè)新表“課程簡(jiǎn)要信息”。,2刪除查詢 例4.5 從“課程管理”數(shù)據(jù)庫的student表中刪除年齡大于35歲的學(xué)生。,3追加查詢 創(chuàng)建追加查詢的步驟如下。 （1）在數(shù)據(jù)庫窗口中，打開查詢的設(shè)計(jì)視圖 。 （2）單擊工具欄上的“查詢類型”按鈕，打開“查詢類型”列表，選擇其中的“追加查詢”選項(xiàng)，打開“追加”對(duì)話框。在“追加”對(duì)話框中的“表名稱”下拉列表框中輸入表名 。,（3）在查詢的設(shè)計(jì)視圖窗口中，將所需的字段拖動(dòng)到查詢?cè)O(shè)計(jì)網(wǎng)格的“字段”行中。 （4）access 2000會(huì)自動(dòng)在“追加到”行中填上與“字段”行中相同的字段名，表示將查詢到的記錄追加到相同的字段中。 （5）單擊access主窗口工具欄上的“執(zhí)行”按鈕，這時(shí)出現(xiàn)一個(gè)確認(rèn)對(duì)話框，確認(rèn)追加操作后，access就將滿足條件的記錄追加到指定的表中。,4更新查詢 例4.6 在“課程管理”數(shù)據(jù)庫中，將schedule表中coursehour為64的課程改為48。,在使用操作查詢時(shí)需要注意如下內(nèi)容。 操作查詢執(zhí)行之后不能撤消，因此在使用刪除查詢和更新查詢之前，應(yīng)先備份表中的數(shù)據(jù)，這樣當(dāng)刪除或更新操作出現(xiàn)錯(cuò)誤后，可以從備份數(shù)據(jù)中恢復(fù)被刪除或更改的數(shù)據(jù)。 如果被刪除數(shù)據(jù)的表與其他表之間建立的關(guān)系，并且在設(shè)定表的關(guān)系時(shí)選定了“級(jí)聯(lián)刪除相關(guān)記錄”，執(zhí)行刪除查詢時(shí)會(huì)同時(shí)刪除相關(guān)表中的記錄。,4.3 sql查詢,4.3.1 sql的數(shù)據(jù)定義功能 sql的數(shù)據(jù)定義功能包括定義基本表和定義索引。,1定義基本表 定義基本表的語句是create，語法格式為： create table （列級(jí)完整性約束條件 ，列級(jí)完整性約束條件 ，）,2修改基本表 修改基本表的語句是alter，語法格式為： alter tableadd列級(jí)完整性約束條件； 其中，add子句用于增加新列和新的完整性約束條件。,例4.8 向student表中增加szip（郵編），其數(shù)據(jù)類型為文本類型，且字段大小為6。 alter table student add szip char（6）,3刪除表 刪除一個(gè)表是將表結(jié)構(gòu)和表中記錄一起刪除，如果這個(gè)表上建有索引，則將索引一起刪除。 刪除基本表的語句是drop，語法格式為： drop table 例4.9 刪除student表。 drop table student,4建立索引 建立索引是加快查詢速度的有效手段。用戶可根據(jù)需要在基本表上建立一個(gè)或多個(gè)索引（23個(gè)索引）。 格式為：create uniqueclusterindex on （ ，）；,例4.10 為student表建立索引，按snumber升序建立惟一索引。 create unique index snumber on student（snumber） 5刪除索引 格式為： drop index on； 例4.11 刪除snumber索引。 drop index snumber on student,4.3.2 sql的數(shù)據(jù)查詢功能 select命令的語法如下。 select all|distinct , from ， where group byhaving| order byasc|desc；,1select子句 select子句指出所要查找的列。其中，all表示檢索符合條件的所有記錄，為缺省值；distinct表示去掉重復(fù)記錄；目標(biāo)列表達(dá)式指定要查詢的列，可以是列名、表達(dá)式或函數(shù)。select后如果沒有指定目標(biāo)列表達(dá)式，而用“*”表示，則表示要指定表中的所有列。,2from子句 from子句指定要查詢的數(shù)據(jù)出自哪張表，可以是一個(gè)表，也可以是多個(gè)表。 例4.12 查詢?nèi)w學(xué)生的詳細(xì)信息。 select * from student,若查詢涉及兩個(gè)以上的表，則稱為連接查詢。 例4.15 查找學(xué)生信息以及所選修課程的courseid及grade select * from student, scores where student.snumber= scores.snumber,3where子句 where子句指定查詢所需的條件。如果沒有where子句，表示選擇全部記錄。,例4.17 在scores表中查找courseid號(hào)為1101且成績(jī)?cè)?0分到90分之間的學(xué)生的學(xué)號(hào)。 select snumber, courseid, grade from scores where （courseid=“1101“） and （grade between 80 and 90）,例4.20 查找沒有指定教材的課程名稱。 select ctitle from schedule where tbookid is null 測(cè)試字段的值是否為空值的一般形式是：列名 is not null。 需要注意的是，不能寫成：列名=null。,例4.21 求選修高等數(shù)學(xué)的學(xué)生的學(xué)號(hào)和姓名。 select snumber, sname from student where snumber in ( select snumber from scores where courseid = ( select courseid from schedule where ctitle=高等數(shù)學(xué) ),本例中描述的查詢是一個(gè)嵌套查詢，嵌套查詢又稱為子查詢。嵌套查詢是指一個(gè)select-from-where查詢塊中可以嵌入另一個(gè)select-from-where查詢塊。sql中允許多層嵌套。嵌套是由內(nèi)向外處理的，外層查詢可以利用內(nèi)層查詢的結(jié)果。,4group by子句 group by子句用于對(duì)查詢結(jié)果按指定的列進(jìn)行分組，并且可以利用函數(shù)進(jìn)行統(tǒng)計(jì)，如求平均值、最大值、最小值和計(jì)數(shù)等。,例4.23 求選修courseid號(hào)為1101的學(xué)生的平均分?jǐn)?shù) select avg(grade) from scores where courseid=1101,例4.25 求每門課程的平均成績(jī)。 select courseid,avg(grade) from scores group by courseid 5having子句 having必須跟隨group by使用，它用來限定分組必須滿足的條件。,例4.26 查找選修課程超過三門課程的學(xué)生學(xué)號(hào)。 select snumber from scores group by snumber having count(*)3,6order by子句 order by用來對(duì)查詢結(jié)果按指定的列進(jìn)行排序。,例4.27 求選修courseid為1101的學(xué)生學(xué)號(hào)和成績(jī)，且按成績(jī)降序排列。 select snumber, grade from scores where courseid=1101 order by grade desc,例4.28 查找選修courseid為1102課程的學(xué)生學(xué)號(hào)及成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)。 select * from scores where courseid=1102 union select * from scores where grade80,該查詢是一個(gè)聯(lián)合查詢。聯(lián)合查詢使用union將多個(gè)查詢結(jié)果合并起來，系統(tǒng)會(huì)自動(dòng)去掉重復(fù)的記錄。,4.3.3 sql數(shù)據(jù)更新 sql的數(shù)據(jù)更新包括對(duì)表中記錄的添加、刪除和修改。 1插入數(shù)據(jù) 插入數(shù)據(jù)分為兩種格式，一種是插入單個(gè)記錄，另一種是插入一個(gè)子查詢的結(jié)果。,（1）插入單個(gè)記錄 語法格式： insert into (, ) values(,)；,例4.29 將一個(gè)新學(xué)生記錄（學(xué)號(hào)：99080220；姓名：王小兵；性別：男；政治面目：團(tuán)員；出生日期：1982年2月5日；住址：德外125號(hào)；郵政編碼：100083）插入到學(xué)生表中。 insert into student values（99080220,王小兵,男,團(tuán)員, #1982-2-5# , 德外125號(hào) , 100083）,（2）插入子查詢結(jié)果 語法格式： insert into (,) 子查詢；,例4.30 求每一門課程的平均成績(jī)，并把結(jié)果存入數(shù)據(jù)庫。 首先在數(shù)據(jù)庫中建立一個(gè)新表，其中一列存放courseid，另一列存放相