第八章資料查詢 許明宗
大綱 查詢物件的功能及種類 運算子 函數及運算式建立幫手 選取查詢 動作查詢
查詢物件 利用設計檢視 ( 以範例查詢 Query By Example, QBE) 或精靈的方式來建立 執行查詢, 並且將查詢儲存為 查詢 物件方便重複使用 也可以使用 SQL 語言來建立查詢物件 功能篩選資料欄位重組及排序新增記算欄位新增記錄 資料表維護資料其它資料庫物件的資料來源 說明 設定準則來篩選 過濾資料 選取一 ( 多 ) 個資料表中要使用的欄位, 對特定欄位進行排序 建立使用計算公式產生的欄位 將查詢的結果新增至資料表 ( 新增資料查詢 ) 或是轉成新的資料表 ( 產生資料表查詢 ) 更新 刪除資料 ( 更新查詢 刪除查詢 ) 表單 報表或其它查詢的資料來源
查詢的種類 - 選取查詢 最常見的查詢 依照特定的準則在資料表中選取記錄 準則 可以是固定不變的, 也可以依照使用者所輸入的資料 ( 參數 ) 來選取記錄 ( 又稱為參數查詢 ) 可以對欄位進行縮減 運算或是合併等操作 統稱為 動作查詢
查詢的種類 - 交叉資料表查詢 交叉資料表為利用不同欄位分別做為欄 列的分組依據, 再配合其它相關的欄位 ; 來進行資料的交叉分析 例如 : 要知道各部門中不同年資員工薪水的平均水準 ( 需要部門 年資 薪水等欄位 ) 樞紐分析表也是一種交叉資料表 樞紐分析表和交叉資料表查詢主要差別為 : 交叉資料表無法自由的交換要分析的欄位 在設計及使用上交叉分析表也較樞紐分析表困難
查詢的種類 - 動作查詢 依照準則選取記錄後, 執行更新 刪除 新增記錄或產生資料表 更新查詢 找出所有已婚員工, 將其薪資調高 5% 產生資料表查詢 過濾一年以前的交易記錄, 轉存到另一新資料表 刪除查詢 過濾一年以前的交易記錄, 並且刪除之 新增查詢 過濾分公司的資料增加到總公司的資料表中
查詢常用的運算子 運算子 + - / * < > = <> And Or Not Between A And B In (A,B,C, ) Like 字串 # 日期 / 時間 # 意義加 減 除 乘等四則運算小於 大於 等於 不等於和 或 非等邏輯運算介於 A 和 B 之間的值等於列表中的其中一個值是否符合設定的字串日期時間的標示符號 範例 10+20 <= 200 Not Like " 台??" Between A And g In ( 台北, 台中 ) Like ( 新竹 * ) #2006/02/02# 英文字母不區分大小寫 * 代表任意個數的任意字元( 可以沒有 )? 代表單一個數的任意字元( 不可以沒有 )
查詢常用的函數 -SQL 聚合函 數 函數名稱 Avg Count First Last Max Min StDev StDevP Sum Var VarP 說明計算欄位的平均值 ( 不含 NULL 值 ) 計算記錄的筆數 ( 不含 NULL 值 ) 資料表中的第一筆記錄資料表中的最後一筆記錄欄位中最大值 ( 不含 NULL 值 ) 欄位中最小值 ( 不含 NULL 值 ) 計算欄位的統計標準差計算欄位母群體的標準差計算欄位值的總合計算欄位的統計變異數計算欄位母群體的統計變異數 聚合函數為用來計算各類合計性質的函數 ( Access 查詢格線中的 合計 列 )
查詢常用的函數 - 字串函數 函數名稱 Format LCase Left Len LTrim Mid Right RTrim Space UCase 說明設定日期 文字 數字等顯示樣式將字串轉成小寫由字串左邊取出部分字串計算字串的長度去掉字串左邊的空白由字串中取出指定位置的字串由字串右邊取出部分字串去掉字串右邊的空白加入空白將字串轉成大寫
查詢常用的函數 - 日期 / 時間函 數 函數名稱 Date DateDiff Day Hour Minute Month Now Second Time Weekday Year 說明取得目前的系統日期傳回兩指定日期值的相差值取出日期 / 時間字串中的 日 取出日期 / 時間字串中的 小時 取出日期 / 時間字串中的 分 取出日期 / 時間字串中的 月份 取得目前的系統日期及時間取出日期 / 時間字串中的 月份 取得目前的系統時間取出日期 / 時間字串中的 星期幾 取出日期 / 時間字串中的 年
查詢常用的函數 - 其它函數 函數名稱 IIf ( 條件式, 真, 否 ) 說明 判斷條件式是否正確 ; 正確的話傳回 真 的值, 不正確的話傳回 否 的值 IIf ([ 薪金 > 5000, 0.05, 0.02])
運算式建立幫手 運算式方塊 運算子按鈕 運算式元件 資料表 查詢 表單及報表, 內建和使用者定義函數, 常數 運算子以及共用運算式 左邊選項的特定的元件或元件的屬性類別 值或是函數
運算式建立幫手 - 函數
運算式建立幫手 - 欄位清單
選取查詢 單一資料表的查詢 設計檢視 修改查詢準則 建立計算欄位 限定查詢的記錄個數 參數查詢 多資料表查詢 查詢與關聯 設計檢視 暫時性關聯 連接類型
單一資料表查詢 - 設計檢視 查詢格線 ( 以前稱為 QBE 格線 ) 是否執行排序及排序的方式 可以利用 顯示資料表 按鈕來新增查詢要使用的資料表 / 查詢 是否顯示欄位 ( 是否只是用來放置準則 ) 篩選資料的準則 要查詢的欄位
單一資料表查詢 - 查詢準則 準則的 交集 和 聯集 運算以及可使用的運算子 函數都和篩選視窗相同 訂書日期在 2006/2/14 之後, 並以訂書日期遞增排序
常用的準則範例 - 文字 Like 林 * Left([ 欄位 ],5) = ABCDE In ( 台北市, 台中市, 高雄市 ) Between A And Z
常用的準則範例 - 數字 > 5000 Or <= -200 Between 200 And 500 (>= 200 And <= 500)
常用的準則範例 - 日期 / 時間 > #2006/03/25/# <= Date() Year([ 欄位 ])=1989
常用的準則範例 - 是 / 否 OLE 超連結 是 / 否 :Yes( 或 No) OLE:IsNotNull 超連結 :Right([ 欄位 ],4) =.tw# 針對 網址 內容過濾 超連結欄位的資料後面都會自動加上一個 #
單一資料表查詢 - 計算欄位 計算欄位是由 : 欄位名稱 加上 : 再加上 計算公式 所組成
常見的新增計算欄位範例 多欄位的四則運算 總價 : [ 單價 ]*[ 數量 ] 計算日期間的差異 DateDiff 函數會算出年份差異,Format 函數會計算現在的月日是否小於出生的月日, 若小於 (True) 表示生日還沒有到, DateDiff 得到的值要減 1 作業日期 : [ 出貨日期 ] - [ 訂貨日期 ] 計算年齡 ( 年資 ) 年齡 : DateDiff("yyyy",[ 出生日期 ],Now()) + Int(Format(Now(),"mmdd") < Format([ 出生日期 ],"mmdd")) 在 Access 中 True 的值為 -1, False 的值為 0
單一資料表查詢 - 記錄個數 選取要顯示的記錄個數 ( 使用 SQL 中的 Top 關鍵字 )
單一資料表查詢 - 參數查詢 1 建立參數 : 查詢 / 參數, 可以檢查輸入的資料是否符合欄位的資料類型 2 更改查詢準則為 :[ 客戶名稱 ] 3 執行查詢時的對話框
多個參數值的參數查詢 1 建立參數 : 查詢 / 參數 2 更改查詢準則為 : Between [ 開始日期 ] And [ 結束日期 ]
多資料表查詢 - 設計檢視 資料表間的關聯 : 自動設定 : 資料庫關聯中的關聯 ( 永久性 ), 或是相同的欄位名稱 ( 暫時性 ) 手動設定 : 拖曳資料表的一個欄位到另一個欄位 ( 暫時性 )
多資料表查詢 - 永久性關聯 資料表間具有永久性關聯時, 查詢會自動套用
多資料表查詢 - 暫時性關聯 資料表間未設定永久性關聯, 且欄位名稱不同時, 需要自行建立資料表的暫時性關聯 資料表間未設定永久性關聯, 但欄位名稱相同時, 會自動建立資料表間的暫時性關聯
多資料表查詢 - 未設定關聯 未設定關聯時, 查詢會產生兩個資料表的乘積
多資料表查詢 - 連接類型 2:Left Join 3:Right Join
Left Join 的使用範例 查詢不同書籍的訂購數量 部份書籍沒有訂購資料, 但是還是要顯示書籍資料, 才能知道該書沒有被訂購過
查詢格線 -SQL 聚合函數 1 在 合計 列選取要分組 ( 群組 ) 的欄位 2 在 合計 列選取要計算 ( 可使用總計 平均等 SQL 聚合函數 ) 資料的欄位 選取 合計, 在查詢格線上出現 合計 列
動作查詢 刪除查詢 更新查詢 新增查詢 產生資料表查詢
刪除查詢 1 開啟查詢的設計檢視窗, 選取要刪除資料的資料表 2 設定要刪除資料的準則 3 選擇 查詢 / 刪除查詢 或工具列上的 刪除查詢 按鈕
刪除查詢 - 準則 檢視 執行 4 資料工作表檢視 按鈕, 只會檢視查詢結果 ( 不會有刪除的動作 ) 5 按下 執行 按鈕, 刪除資料 要刪除資料的準則 執行前要先執行檢視, 確定準則是否設定正確
更新查詢 1 開啟查詢的設計檢視窗, 選取要更新資料的資料表 2 選擇 查詢 / 更新查詢 或工具列上的 更新查詢 按鈕
更新查詢 - 準則 檢視 執行 執行前要先執行檢視, 確定準則是否設定正確 3 設定要更新資料的準則及新的資料內容
新增查詢 1 開啟查詢的設計檢視窗, 選取要來源資料表 2 選擇 查詢 / 新增查詢 或工具列上的 新增查詢 按鈕
新增查詢 - 選取資料表 3 選擇要新增資料到那一個資料表中 ( 查詢格線中的欄位要符合該資料表的設計 )
新增查詢 - 準則 檢視 執行 4 要將那些欄位的資料增加到其它資料表 5 目的資料表對應的欄位 6 要新增資料的準則 執行前要先執行檢視, 確定準則是否設定正確
新增查詢 - 範例 要重設資料表中有跳號的 自動編號 欄位, 首先要複製資料表的結構 複製要重設 自動編號 欄位資料表的結構
重設 自動編號 欄位 - 新增 查詢 1 新增一個 新增查詢, 選取原來的資料表中除了自動編號欄位之外的所有欄位 2 執行 查詢 / 新增查詢, 新增資料到新的資料表中
產生資料表查詢 1 開啟查詢的設計檢視窗, 選取要來源資料表 2 選擇 查詢 / 產生資料表查詢 或工具列上的 產生資料表查詢 按鈕
產生資料表查詢 - 資料表名稱 3 設定要新增的資料表的名稱
產生資料表查詢 - 準則 檢 視 執行 4 要將那些欄位的資料增加到新的資料表中 5 要新增資料的準則 執行前要先執行檢視, 確定準則是否設定正確