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

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