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