第八章資料查詢及結構化查詢 語言 許明宗
大綱 尋找 篩選 與查詢 查詢物件的功能及種類 常見的查詢運算子 函數及運算式建立幫手 選取查詢 交叉資料表查詢 動作查詢 結構化查詢語言
尋找 篩選 與查詢 找尋特定的資料, 可使用尋找 篩選 查詢等方式 尋找 : 找尋單一資料表 ( 或查詢 ) 中的資料 用於簡單的資料尋找 ( 配合排序 ) 或資料修改 ( 取代 ) 每次只能找到一筆符合條件的記錄, 無法跨資料表找尋 篩選 : 依照使用者設定的篩選準則, 過濾出資料表中符合該準則的資料 可一次找出所有符合準則的資料 是一種簡易的查詢 ( 可另存成查詢物件 ) 無法跨資料表找尋
尋找 篩選 與查詢 (cont d) 查詢 : 除了進行資料 ( 多資料表 ) 篩選的動作外, 還可以執行資料更新 新增資料表等動作 基本 ( 選取 ) 查詢為使用 結構化查詢語言 (SQL) 的 資料操作語言 來過濾資料 可對過濾的資料進行排序 計算或統計, 將結果以資料表呈現 可縮減 合併資料表的欄位, 提供適量 有效的內容 查詢是資料庫最常用的操作, 因此 Access 提供了 查詢物件, 讓使用者可以將 查詢 儲存起來, 方便重複使用 單純的查詢, 可以使用篩選替代 動作查詢 ( 資料更新 新增資料表 ) 則是屬於查詢的進階應用
查詢物件 利用設計檢視 ( 以範例查詢 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 值 ) 計算欄位的統計標準差計算欄位母群體的標準差計算欄位值的總合計算欄位的統計變異數計算欄位母群體的統計變異數 聚合函數為用來計算各類合計性質的函數
查詢常用的函數 - 字串函數 函數名稱 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])
運算式建立幫手 運算式方塊 運算子按鈕 運算式元件 資料表 查詢 表單及報表, 內建和使用者定義函數, 常數 運算子以及共用運算式 左邊選項的特定的元件或元件的屬性類別 值或是函數
運算式建立幫手 -SQL 聚合函 數
運算式建立幫手 - 欄位清單
選取查詢 單一資料表的查詢 簡單查詢精靈 設計檢視 修改查詢準則 建立計算欄位 限定查詢的記錄個數 參數查詢 多資料表查詢 簡單查詢精靈 設計檢視 暫時性關聯 連接類型
單一資料表查詢 - 精靈 查詢訂單資料表上的記錄的訂單號碼 客戶名稱及訂書日期 選擇要使用的資料表 選擇要使用的欄位 命名查詢物件
單一資料表查詢 - 設計檢視 查詢格線 ( 以前稱為 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 [ 結束日期 ]
多資料表查詢 - 精靈 要使用棈靈建立多資料表查詢前, 一定要先建立資料表的關聯 先選取 訂單 及相關的欄位 再選取 訂購數量表 及 數量 欄位
簡單查閱精靈
簡單查閱精靈 (cont d)
多資料表查詢 - 查詢結果 訂單號碼 客戶名稱 業務代表 訂書日期 / 月 數量之總計 數量之平均 數量之最小值 數量之最大值 訂購數量表之筆數 1 北大書城 趙得勝 February 2006 55 18.333 15 20 3 2 大鳥書局 李華山 March 2006 90 30 10 50 3
多資料表查詢 - 設計檢視
多資料表查詢 - 暫時性關聯 欄位名稱不同時, 需要自行建立資料表的暫時性關聯 欄位名稱相同時, 會自動建立資料表間的暫時性關聯
多資料表查詢 - 連接類型 2:Left Join 3:Right Join
Left Join 的使用範例 查詢不同書籍的訂購數量 部份書籍沒有訂購資料, 但是還是要顯示書籍資料, 才能知道該書沒有被訂購過
交叉分析表查詢精靈
交叉分析表查詢精靈 - 選取資 料來源
交叉分析表查詢精靈 - 列
交叉分析表查詢精靈 - 欄
交叉分析表查詢精靈 - 分組 季和月的分組設定會有問題!
交叉分析表查詢精靈 - 函數
交叉分析表查詢精靈 - 命名
交叉分析表查詢精靈 - 結果
樞紐分析表
動作查詢 刪除查詢 更新查詢 新增查詢 產生資料表查詢
刪除查詢 1 開啟查詢的設計檢視窗, 選取要刪除資料的資料表 2 設定要刪除資料的準則 3 選擇 查詢 / 刪除查詢 或工具列上的 刪除查詢 按鈕
刪除查詢 - 準則 檢視 執行 4 資料工作表檢視 按鈕, 只會檢視查詢結果 ( 不會有刪除的動作 ) 5 按下 執行 按鈕, 刪除資料 要刪除資料的準則 執行前要先執行檢視, 確定準則是否設定正確
更新查詢 1 開啟查詢的設計檢視窗, 選取要更新資料的資料表 2 選擇 查詢 / 更新查詢 或工具列上的 更新查詢 按鈕
更新查詢 - 準則 檢視 執行 執行前要先執行檢視, 確定準則是否設定正確 3 設定要更新資料的準則及新的資料內容
新增查詢 1 開啟查詢的設計檢視窗, 選取要來源資料表 2 選擇 查詢 / 新增查詢 或工具列上的 新增查詢 按鈕
新增查詢 - 選取資料表 3 選擇要新增資料到那一個資料表中 ( 查詢格線中的欄位要符合該資料表的設計 )
新增查詢 - 準則 檢視 執行 4 要將那些欄位的資料增加到其它資料表 5 目的資料表對應的欄位 6 要新增資料的準則 執行前要先執行檢視, 確定準則是否設定正確
新增查詢 - 範例 要重設資料表中有跳號的 自動編號 欄位, 首先要複製資料表的結構 複製要重設 自動編號 欄位資料表的結構
重設 自動編號 欄位 - 新增 查詢 1 新增一個 新增查詢, 選取原來的資料表中除了自動編號欄位之外的所有欄位 2 執行 查詢 / 新增查詢, 新增資料到新的資料表中
產生資料表查詢 1 開啟查詢的設計檢視窗, 選取要來源資料表 2 選擇 查詢 / 產生資料表查詢 或工具列上的 產生資料表查詢 按鈕
產生資料表查詢 - 資料表名稱 3 設定要新增的資料表的名稱
產生資料表查詢 - 準則 檢 視 執行 4 要將那些欄位的資料增加到新的資料表中 5 要新增資料的準則 執行前要先執行檢視, 確定準則是否設定正確
結構化查詢語言 (SQL) Structured Query Language(SQL) 是資料庫管理系統用來定義 操作 控制資料庫的語言 專門用來處理關聯式資料庫的標準程式語言 SQL 語言主要可分為三個部份 資料定義語言 (Data Definition Language,DDL) CREATE ALTER DROP 資料操作語言 (Data Manipulation Language,DML) SELECT INSERT UPDATE DELETE 資料控制語言 (Data Control Language,DCL) COMMIT ROLLBACK GRANT REVOKE
SQL (cont d) 念法 : S-Q-L 或 see-kwul 標準化作業由 ANSI( 美國國家標準學會 ) 與 ISO( 國際標準組織 )2 個標準化組織推動 最初的標準化規格, 是在 1986 年由 ANSI 制定 1992 年時 ANSI 與 ISO 分別制定新規格, 稱為 SQL-92 規格, 通稱為 SQL2 現在的 SQL 規格便是上述的 SQL-92 ANSI 與 ISO 正持續地在研討新一代的 SQL-99 規格, 通稱 SQL3
新增 SQL 敘述句 以設計檢視開啟一個新的選取查詢設計視窗, 按下工具列上的 SQL 按鈕 在這裡輸入 SQL 敘述句 會依據 SQL 敘述句相關的關鍵字來判斷是何種查詢
資料操作語言 提供使用者在資料庫中新增 刪除 更新 與查詢資料的操作指令 SELECT( 查詢 ) INSERT( 新增 ) UPDATE( 修改 ) DELETE( 刪除 )
資料操作語言 - 簡單查詢 SELECT 訂單號碼, 客戶名稱, 訂書日期 FROM 訂單完整資料 WHERE ( 訂書日期 >#1/1/2006#) ORDER BY 訂書日期 ; 選取要使用的欄位選擇要使用的資料表選取準則排序準則 SELECT * FROM 訂單資料 WHERE ( 客戶名稱 Like 北大 * ) ORDER BY 訂書日期 ; 一個完整的 SQL 敘述會以 ; 結束
資料操作語言 - 多資料表查詢 SELECT FROM 書籍資料表.* INNER JOIN 訂購數量表 ON 書籍資料表. 書籍名稱 = 訂購數量表. 書籍名稱 ; SELECT FROM 書籍資料表.* LEFT JOIN 訂購數量表 ON 書籍資料表. 書籍名稱 = 訂購數量表. 書籍名稱 ;
資料操作語言 - 新增 INSERT INTO 訂書資料 ( 訂單號碼, 客戶名稱, 業務代表, 書籍名稱, 訂價, 數量, 訂書日期 ) VALUES (003, 大鳥書城, 趙得勝, 開發史, 250, 30, 2006/03/05 );
資料操作語言 - 修改 UPDATE 訂單資料 SET 訂單資料. 業務代表 = " 陳華山 " WHERE ((( 訂單資料. 業務代表 )=" 李華山 "));
資料操作語言 - 刪除 DELETE 訂單資料. 客戶名稱 FROM 訂單資料 WHERE (( 訂單資料. 客戶名稱 ) Like " 大鳥 *"); 一般不需要欄位名稱 (Access 特別要求 )
資料定義語言 定義資料庫的資料結構 建立 (CREATE) 修改 (ALTER) 刪除 (DROP)
Microsoft Jet SQL 資料型別 英文名稱 BIT BYTE MONEY DATETIME REAL FLOAT SMALLINT INTEGER DECIMAL MEMO IMAGE CHAR TEXT 中文名稱是 / 否位元組貨幣日期 / 時間單精準數雙精準數整數長整數小數點備忘 OLE 物件文字
資料定義語言 - 新增資料表 CREATE TABLE 員工資料表 ( 員工編號 INT NOT NULL PRIMARY KEY, 姓名 CHAR(10) NOT NULL UNIQUE, 住址 CHAR(30), 性別 BIT, 電話 CHAR(10) );
資料定義語言 - 修改資料表 ALTER TABLE 員工資料表 ADD COLUMN 分機 CHAR(5); ALTER TABLE 員工資料表 ALTER COLUMN 電話 CHAR(12); ALTER TABLE 員工資料表 DROP COLUMN 性別 ;
資料定義語言 - 刪除資料表 DROP TABLE 員工資料表 ;
資料控制語言 控制資料庫內部的交易處理以及維護系統效能的指令 使用者授權 GRANT( 授予使用權 ) REVOKE( 撤銷使用權 ) 交易處理 (Access 不提供 ) COMMIT ROLLBACK