ACCESS實例教程 查詢的操作 第3章 1 建立各種類型的查詢2 建立計算字段3 使用統計函數4 SQL語句與子查詢 本章要點 3 1認識查詢對象 查詢是Access數據庫的一個重要對象 用來查看 處理和分析數據 查詢的數據源可以是一個或多個數據表或已存在的查詢 查詢產生的操作結果形式上看類似于數據表 實際上是一個動態的數據集合 每次打開查詢 都會顯示數據源的最新變化情況 查詢與數據源表是相通的 在查詢中對數據所做的修改可以在數據源表中得到體現 查詢對象有5類 選擇查詢 交叉表查詢 操作查詢 參數查詢 SQL查詢 其中操作查詢又包括4種 生成表查詢 更新查詢 追加查詢 刪除查詢 操作查詢需要用 運行 命令使查詢生效 3 1 1查詢對象的基本類型 3 1 2查詢的設計視圖 常用的查詢視圖有3種 設計視圖 數據表視圖 SQL視圖 查詢的設計視圖窗口分上下兩部分 上半部分是 字段列表 區 放置查詢的數據源 下半部分是 設計網格 區 放置在查詢中顯示的字段和在查詢中做條件的字段 3 2統配符 運算符與常用函數 3 2 1通配符 1 代表任意多個任何字符 例如 李 通配所有第一個字符為 李 的字符串 2 代表任意一個任何字符 例如 李 通配所有第一個字符為 李 且只有2個字符的字符串 3 通配方括號內的任何單個字符 例如 淡 紅綠黃 色 代表的字符串有 淡紅色 淡綠色 淡黃色 4 通配不在方括號內的任何單個字符 例如 淡 紅綠黃 色 代表的字符串有 淡藍色 淡紫色 等 中間的字不能是紅 綠 黃3個字中的任何一個 5 通配指定范圍內的任何字符 該范圍必須是升序 通常針對英文字母 例如 a e g b 代表的字符串有 aeb afb agb 6 代表任意一個數字字符 例如 2 2 代表的字符串有 2112 2802 等 第一個和最后一個必須是2 中間2個可以是任何數字 3 2 2普通運算符 1 算術運算符 乘 除同級 加 減同級 同級運算從左到右 乘 除運算優先于加 減運算 2 關系運算符 Access系統用true或 1表示 真 用false或0表示 假 3 邏輯運算符 not and or 運算結果是邏輯值 4 連接運算符 用于字符連接 號要求兩邊必須是字符型 連接后得到新字符串 號不論兩邊的操作數是字符串還是數字 都按字符串連起來 得到新字符串 3 2 3特殊運算符 1 like 為文本字段設置查詢模式 支持通配符 如 like 李 Like 紅 2 in 指定一個值列表作為查詢的匹配條件 不支持通配符 如 in 張三 李四 王五 3 between 指定數據范圍 用and連接起始數據和終止數據 如 between10and30 相當于 10and 304 isNull 查找為空的數據 5 isnotNull 查找非空的數據 3 2 4字符函數 1 left函數 從字符串左邊取n個字符 得到左子串 格式 left 字符串 n 或left string型變量名 n 2 right函數 從字符串右邊取n個字符 得到右子串 格式 right 字符串 n 或right string型變量名 n 3 mid函數 從字串第n1個字符開始取n2個字符 得到子字串 格式 mid 字符串 n1 n2 或right string型變量名 n1 n2 說明 如果省略n2 則從字串第n1個字符開始一直取到最后 3 2 5日期函數 1 date函數 返回系統當前日期 格式 date 或date2 now函數 返回系統當前日期和時間 格式 now 或now3 year函數 返回日期數據中的年份 格式 year 日期常量 或year 日期 時間型變量名 4 month函數 返回日期數據中的月份 格式 month 日期常量 或month 日期 時間型變量名 5 day函數 返回日期數據中日子的號碼 格式 day 日期常量 或day 日期 時間型變量名 3 2 6統計函數 1 sum函數 對數字型表達式求和 格式 sum 數字型表達式 如 sum 工資 獎金 2 avg函數 對數字型表達式求和 格式 avg 數字型表達式 如 avg 獎金 3 count函數 對表達式統計個數 格式 count 表達式 如 count 編號 4 max函數 求數字型表達式的最大值 格式 max 數字型表達式 如 max 獎金 5 min函數 求數字型表達式的最小值 格式 min 數字型表達式 如 min 年齡 3 3建立簡單的選擇查詢 簡單的選擇查詢包括從一個或多個表中提取字段 按一定條件提取記錄 對查詢結果進行排序等操作 按條件從一個或多個表中提取字段 得到的查詢結果就象組建了新數據表 3 3 1從一個或多個表中提取字段 3 3 2從一個或多個數據源中提取記錄 例 以查詢為源建立簡單查詢1 已知數據表 2 建立查詢1 顯示家長的身份證號與家長的姓名 3 建立查詢2 顯示學生的身份證號 姓名和該學生家長的身份證號 4 建立查詢3 顯示了家長是本校校友的學生 3 3 3用函數和特殊運算符設置查詢條件 例 查找姓李的學生和有繪畫特長的學生 顯示字段為 年級 姓名 愛好 其中年級取學號的前4位 3 4建立帶計算的選擇查詢 3 4 1用表達式建立計算字段 例 計算教師工資 3 4 2用統計函數建立計算字段 例 求最高獎金 最低獎金 獎金最大差額 例 統計教師人數與獎金總額 例 統計男 女講師人數 例 查詢平均分低于班平均分的學生 1 先求班平均分 2 再求人平均分 3 最后求小于班平均的人 3 5建立交叉表查詢 交叉表查詢是一種從水平和垂直兩個方向對數據表進行分組統計的查詢方法 用獨特的概括形式返回表的統計數字 建立交叉表查詢至少要指定3個字段 一個字段用來分組作為行標題 行標題最多可以有3個 一個字段用來分組作為列標題 列標題只能有1個 一個字段放在行與列交叉位置作為統計項 統計項只能有1個 例 查詢學生各門公共課的成績 顯示結果 例 查詢各班男女生成績不及格的人數 顯示結果 3 6建立操作查詢 操作查詢包括 生成表查詢 更新查詢 追加查詢 刪除查詢 3 6 1生成表查詢 生成表查詢能將查詢結果保存成數據表 使查詢結果由動態數據集合轉化為靜態的數據表 新表不繼承數據源表的關鍵字屬性 生成表查詢通常用幾個表中的數據組合起來生成新表 如果僅用一個表的數據生成新表 可以在數據庫窗口用復制 粘貼的方法實現 例 將男學生成績查詢轉換成數據表 1 先建查詢 2 將查詢轉換為生成表查詢 3 為新表起名 4 單擊 運行 按鈕 3 6 2更新查詢 更新查詢能成批修改記錄 用更新查詢更改記錄的數據項以后 無法用 撤消 命令取消操作 更新查詢可以同時更新多個數據源和多個字段的值 在設計網格中 只將要更新的字段和做條件的字段放入即可 例 將職稱為講師的 教師編號 字段前加 講師 字符串 并將講師的獎金增加50 3 6 3追加查詢 追加查詢能將數據源中符合條件的記錄追加到另一個表尾部 數據源可以是表或查詢 追加的去向是一個表 字段列表區只放提供字段的表或查詢 被追加的表不能放入查詢的字段列表區 數據源與被追加表對應的字段之間要類型匹配 例 將籍貫為河北的記錄追加到空表stud中 追加的字段為 班級 姓 名 性別 1 選擇追加去向 2 確定追加字段和條件 3 運行查詢 3 6 4刪除查詢 刪除查詢能將數據表中符合條件的記錄成批的刪除 刪除查詢可以給單個表刪除記錄 也可以給建立了關系的多個表刪除記錄 多個表之間要建立參照完整性 并選擇了 級聯刪除 選項 在刪除查詢的設計網格中 只放入做刪除條件的字段即可 運行刪除查詢后 被刪除的表中記錄不能用 撤消 命令恢復 例 刪除1998年參加工作的記錄 3 7建立參數查詢 參數查詢是按用戶給定的字段值來查找記錄的查詢 參數查詢提供一個對話框 提示用戶輸入一個參數值 然后查找并顯示符合參數值的記錄 參數查詢有單參數查詢和多參數查詢兩種 3 7 1單參數查詢 單參數查詢只需要在字段中指定一個參數 參數通常是完整的字段值 也可以是字段的部分值 還可以是窗體控件中的值 例 以輸入的姓名為參數 顯示該同學的公共課成績 運行時提示 例 用部分字段值做參數 運行時提示 顯示結果 3 7 2多參數查詢 多參數查詢在多個字段中指定參數 執行時提示對話框從左到右依次顯示 用戶則依次輸入多個參數 最后根據多個參數顯示查詢結果 3 8建立SQL查詢與子查詢 SQL StructureQueryLanguage 稱為結構化查詢語言 被所有關系型數據庫支持 它包括了數據定義 DataDefintion 數據查詢 DataQuery 數據操縱 DataManipulation 數據控制 DataControl SQL語句在Access中的應用分為4個方面 聯合查詢 子查詢 數據定義查詢 傳遞查詢 這里我們只介紹前三個查詢 3 8 1創建聯合查詢 聯合查詢將兩個select語句的查詢結果合成一個查詢結果 是集合的并運算 在介紹聯合查詢之前 先看一下select語句的語法 1 select語句的語法select字段列表from表名 where查詢條件 orderby排序項 groupby分組項 例 select from學生where性別 男 例 select姓名 成績from學生 公共課成績where學生 學號 公共課成績 學號 例 select姓名 成績from學生 公共課成績where學生 學號 公共課成績 學號orderby成績desc例 selectleft 學號 4 as班級 姓名from學生 2 聯合查詢的語法 select語句1union all select語句2 例 教師表法律系的教授與teacher表中文系的副教授顯示在一個查詢結果中 1 查詢 菜單 SQL特定查詢