10操作查詢和SQL特定查詢.ppt

數(shù)據(jù)庫應(yīng)用基礎(chǔ) 第10章操作查詢和SQL特定查詢 第10章操作查詢和SQL特定查詢 10 1操作查詢 操作查詢也叫動作查詢 其作用是針對表中的一條記錄或多條記錄和數(shù)據(jù)項進行操作處理 在標準SQL語言中的操作查詢包括 追加 更新 刪除等三種 在Access中的操作查詢 除前三種外還包括生成表查詢 10 1 1生成表查詢 生成表查詢對查詢結(jié)果的處理方式與選擇查詢不同 它將創(chuàng)建一個指定名稱的新表 并把檢索出的結(jié)果數(shù)據(jù)存放在該表中 該表的結(jié)構(gòu)由SELECT后的字段列表所決定 表中的記錄就是查詢結(jié)果集中的那些數(shù)據(jù)行 10 1 1生成表查詢 按班級編號統(tǒng)計各班學(xué)生人數(shù) 并生成表AAA SELECT班級編號 Count AS班級人數(shù)INTOAAAFROM學(xué)生GROUPBY班級編號本例將生成一個名為AAA的新表 其中包含班級編號和班級人數(shù)兩個字段 10 1 2追加查詢 追加查詢由INSERT語句實現(xiàn) 可將一個或多個新記錄添加到表中 INSERT語句的基本格式分為單記錄追加和多記錄追加兩種 單記錄追加查詢 如需將一組已知數(shù)據(jù)追加到某個表中形成一條新記錄 使用單記錄追加查詢 其語法格式為 INSERTINTO 字段1 字段2 VALUES 值1 值2 在按照格式書寫實際語句時 格式中直接出現(xiàn)的單詞是必寫的 由一對尖括號括起的項目要求由用戶填寫 方括號中的項目是可選的 省略號表示該處可以出現(xiàn)更多相應(yīng)內(nèi)容 其他符號表示應(yīng)該按要求填寫對應(yīng)的內(nèi)容 單記錄追加查詢 例2 將編號為 21010504 姓名為 孫琳 的教師數(shù)據(jù)添加到 教師 表中 INSERTINTO教師 教師編號 姓名 VALUES 21010504 孫琳 在表名后的括號中列舉的是將接受數(shù)據(jù)的字段名 這些字段名不要加引號 在VALUES后的括號中列舉的是一組具體值 這些值將依次存入字段列表中對應(yīng)的各字段 這組值的個數(shù)應(yīng)與指定的字段名個數(shù)一致 且類型一致 在VALUES子句中列出的值如果是文本類型數(shù)據(jù)應(yīng)該用雙引號 括起 如果是日期 時間類型數(shù)據(jù)應(yīng)用井號 括起 多記錄追加查詢 如果希望將查詢的結(jié)果追加到某個表中形成一條或多條新記錄時 使用多記錄追加查詢 其語法格式為 INSERTINTO 字段1 字段2 SELECT 源表名 字段1 字段2 FROM與單記錄追加查詢語句格式不同的是 在本語句格式中VALUES部分被一個選擇查詢所取代 我們知道 選擇查詢的結(jié)果集是由若干行 記錄 列 字段 數(shù)據(jù)所組成的 如果把這些數(shù)據(jù)添加到指定表中 結(jié)果集中的行將成為表中的記錄 各列數(shù)據(jù)也將被填入表中相應(yīng)的字段 多記錄追加查詢 例3 從學(xué)生表中查詢出08級學(xué)生數(shù)據(jù) 并將這些數(shù)據(jù)添加到 08級學(xué)生 表中 INSERTINTO08級學(xué)生 學(xué)號 姓名 性別 SELECT學(xué)號 姓名 性別FROM學(xué)生WHERE學(xué)號LIKE 2008 本例使用了一個作為子查詢的選擇查詢 該子查詢從 學(xué)生 表中檢索出數(shù)千行08級學(xué)生數(shù)據(jù) 并將這些數(shù)據(jù)添加到 08級學(xué)生 表中 應(yīng)該注意的是 在被添加的目標表名之后給出了字段列表時 子查詢SELECT后的字段列表也應(yīng)該與之對應(yīng) 字段的名稱可以不相同 各字段值按出現(xiàn)的順序添加到目標表中 多記錄追加查詢 INSERTINTO08級學(xué)生SELECT姓名 性別 學(xué)號FROM學(xué)生WHERE學(xué)號LIKE 2008 如果未指出將數(shù)據(jù)寫入 08級學(xué)生 表中的哪些字段 系統(tǒng)就會自動進行字段匹配處理 按照SELECT后的字段列表中出現(xiàn)的字段名稱 依次檢查 08級學(xué)生 表中是否有同名字段 如果發(fā)現(xiàn) 08級學(xué)生 表中不存在某個字段 則不能執(zhí)行本次追加查詢 如果SELECT后的字段列表中的每個字段都可以在目標表中找到相應(yīng)字段 就可以執(zhí)行追加查詢 并按字段名相同的匹配方式 將子查詢的結(jié)果數(shù)據(jù)記錄添加到目標表中 更新查詢 使用更新查詢可以更改一條或多條現(xiàn)有記錄中的數(shù)據(jù) 更新查詢語句的基本格式如下 UPDATESETWHERE在此格式中 為需要更新數(shù)據(jù)的表 為一組描述如何更新數(shù)據(jù)的表達式 用于篩選將被更新數(shù)據(jù)的記錄 更新查詢 例4 將學(xué)號為 200711043 的學(xué)生的出生日期改為1990年7月15日 并將其民族改為 滿族 UPDATE學(xué)生SET出生日期 1990 7 15 民族 滿族 WHERE學(xué)號 200711043 本例 更新了學(xué)生表中的出生日期和民族字段 當然被更新的只是學(xué)號為 200711043 的記錄 更新查詢 在更新查詢語句中 一次可以更新若干個字段的值 每個字段如何更新用一個賦值表達式來表示 在賦值表達式中等號的左面為要更新的字段的名稱 等號的右面為用來更新的值或表達式 當需更新的字段多于一個時 各賦值表達式間用逗號分隔 更新查詢中的條件直接影響更新的范圍 在描述條件時一定要小心 因為設(shè)置不當時將導(dǎo)致發(fā)生一些不期望的更新 而這些更新是無法撤銷的 在需要更新數(shù)據(jù)前 通常應(yīng)先寫個選擇查詢來驗證條件是否正確 然后將正確的條件用于更新查詢 或者 先將要更新的表做個備份 以便在更新失誤時做恢復(fù)之用 更新查詢 例5 在 學(xué)生 表中有一個 緩考 字段 該字段為 是 否 類型 現(xiàn)根據(jù)本次學(xué)生考試情況更新該項數(shù)據(jù) 未參加任何考試者填True 或 1 參加考試者填False 或0 為了保證數(shù)據(jù)正確 在進行本次更新前 需要把該字段原有的數(shù)據(jù)全部設(shè)置為True 然后再在判斷出參加了考試的情況下 將相應(yīng)值改為False 相應(yīng)的兩個更新語句如下 UPDATE學(xué)生SET緩考 True由于本語句沒有設(shè)置條件 那么全部記錄都將被更新 更新查詢 UPDATE學(xué)生SET緩考 FalseWHEREEXISTS SELECT FROM學(xué)生考試WHERE學(xué)生考試 學(xué)號 學(xué)生 學(xué)號 在本句中使用了子查詢 其目的是檢索當前學(xué)生在 學(xué)生考試 表中是否有相應(yīng)記錄存在 如果有 就表示該學(xué)生參加了考試 于是就將其緩考字段值更新為False 那些沒有參加考試的學(xué)生的緩考字段值沒有被更新 仍然是True 更新查詢 例6 統(tǒng)計并更新各班學(xué)生人數(shù) 當 學(xué)生 表中學(xué)生記錄出現(xiàn)增減時 將引起學(xué)生人數(shù)的變動 現(xiàn)在的要求是更新 班級 表中各班級相應(yīng)的人數(shù) Access支持使用另一個表 查詢中的數(shù)據(jù)來更新當前表的操作 因此 可以先按 班級 對學(xué)生人數(shù)進行分組計數(shù) 生成一個臨時表 然后 用該臨時表來更新班級表 更新查詢 相應(yīng)的兩個語句如下 SELECT班級編號 Count AS班級人數(shù)INTOAAAFROM學(xué)生GROUPBY班級編號本例的新表名為AAA 其中包含班級編號和班級人數(shù)兩個字段 UPDATE班級 AAASET班級 班級人數(shù) AAA 班級人數(shù)WHERE班級 班級編號 AAA 班級編號在本句中兩個相關(guān)表的名稱都放在UPDATE后 在SET部分指出用AAA 班級人數(shù)來更新班級 班級人數(shù) 并且更新是在兩個表中具有相同班級編號值的記錄間進行的 刪除查詢 刪除查詢使用DELETE語句 該語句用于從FROM子句中所列出的一個或多個表中刪除滿足指定條件的記錄行 其一般格式為 DELETEFROMWHERE刪除查詢將刪除整個記錄 并且執(zhí)行刪除后 不能取消該操作 如果希望知道將要刪除哪些記錄 可以先檢查使用相同條件的選擇查詢的結(jié)果 然后再執(zhí)行刪除查詢 刪除查詢 例7 從學(xué)生表中刪除學(xué)號為 200715006 的學(xué)生記錄 DELETEFROM學(xué)生WHERE學(xué)號 200715006 在執(zhí)行時 系統(tǒng)將提示有多少記錄將被刪除 確認后完成刪除操作 千萬注意 如果不使用WHERE子句 或者WHERE中的條件總是成立 就會把表中全部記錄刪除掉 刪除查詢 例8 從 場次 表中刪除那些沒有安排考試的場次 有時為了操作方便 先預(yù)設(shè)了若干場次 以備安排考試時選用 在完成實際考試安排后 有部分場次并沒有被選用 想刪去 此時 應(yīng)該根據(jù) 學(xué)生考試 安排的具體數(shù)據(jù)確定確實沒有安排該場次考試時 才能從 場次 表中刪除 DELETEFROM場次WHERENOTEXISTS SELECT FROM學(xué)生考試WHERE場次 場次 場次 本例使用子查詢來判斷是否有學(xué)生在那個場次考試 如果沒有就把該場次刪除 10 2SQL特定查詢 聯(lián)合查詢可合并兩個或多個表中的數(shù)據(jù) 但具體方式與其他查詢不同 大多數(shù)查詢通過聯(lián)接行來合并數(shù)據(jù) 而聯(lián)合查詢通過追加行來合并數(shù)據(jù) 傳遞查詢不是由Access附帶的數(shù)據(jù)庫引擎處理的 它們被直接傳遞到遠程數(shù)據(jù)庫服務(wù)器并由該服務(wù)器執(zhí)行處理 然后將結(jié)果傳遞回Access 數(shù)據(jù)定義查詢是一種特殊類型的查詢 它不處理數(shù)據(jù) 而是創(chuàng)建 刪除或修改其他數(shù)據(jù)庫對象 子查詢 在查詢的設(shè)計視圖中 無法進行SQL特定查詢設(shè)計 只能在SQL視圖中打開或運行它們 在Access中還提供了交叉表查詢 10 2 1聯(lián)合查詢 聯(lián)合查詢與聯(lián)接查詢在構(gòu)成結(jié)果集時所采用的方式完全不同 聯(lián)接查詢從多個表或查詢中獲取并合并數(shù)據(jù)列 生成查詢結(jié)果集 聯(lián)合查詢則將合并兩個或更多獨立查詢或表的結(jié)果 這時的合并是按行進行的 聯(lián)合查詢 聯(lián)合查詢的語法 TABLE query1UNION ALL TABLE query2 UNION ALL TABLE queryn 其中query1 n表示SELECT語句 或者查詢的名稱 或前面有TABLE關(guān)鍵字的表的名稱 可以在一個UNION操作中以任意組合合并兩個或更多查詢 表和SELECT語句的結(jié)果 默認情況下 使用UNION操作時不會返回重復(fù)的記錄 但可以包括ALL謂詞以確保返回所有記錄 聯(lián)合查詢 例9 假設(shè)已畢業(yè)學(xué)生的信息是保存在結(jié)構(gòu)與 學(xué)生 表相同的 畢業(yè)學(xué)生 表中 現(xiàn)需查詢出商務(wù)學(xué)院歷年來自湖南的學(xué)生名單 SELECT學(xué)生 學(xué)號 學(xué)生 姓名FROM學(xué)生WHERE地區(qū) 湖南 UNIONSELECT學(xué)號 姓名FROM畢業(yè)學(xué)生WHERE地區(qū) 湖南 ORDERBY學(xué)生 學(xué)號本例使用UNION將兩個類似的查詢結(jié)果聯(lián)合在一起 使用這些查詢時 應(yīng)注意所有的查詢必須具有相同數(shù)目的字段 但這些字段的大小或數(shù)據(jù)類型不必相同 聯(lián)合查詢 僅在第一個SELECT語句中使用別名 因為別名在任何其他語句中將被忽略 可以在最后一個query參數(shù)的末尾使用ORDERBY子句以指定的順序顯示返回的數(shù)據(jù) 在ORDERBY子句中 指的是在第一個SELECT語句中調(diào)用別名所依據(jù)的字段 10 2 2數(shù)據(jù)定義查詢 數(shù)據(jù)定義查詢是一種特殊類型的查詢 它不處理數(shù)據(jù) 而是創(chuàng)建 刪除或修改其他數(shù)據(jù)庫對象 下面主要介紹其中的 CREATETABLEALTERTABLEDROPTABLECREATEINDEXDROPINDEX 創(chuàng)建新表 CREATETABLE語句用以創(chuàng)建新表 在Access中 每個表最少應(yīng)該有1個字段 最多可以有1024個字段 在CREATETABLE語句中 應(yīng)該對每個字段按一定的要求分別進行定義 各字段定義間用逗號分隔 創(chuàng)建新表 定義字段的主要成分 字段名 要在新表中創(chuàng)建的字段的名稱 數(shù)據(jù)類型 字段所具有的數(shù)據(jù)類型 在CREATETABLE語句中常用的數(shù)據(jù)類型如下 Char文本類型 用于保存字符 Integer整型 Single單精度實數(shù) Double雙精度實數(shù) Money貨幣型 Datetime日期 時間型 Bit是 否 邏輯 型 OleObjecOLE對象 創(chuàng)建新表 定義字段的主要成分 字段大小 以字符為單位的字段大小 主要用于文本字段 因為其它類型字段的大小基本上是固定的 NULL NOTNULL 可選項目 定義相應(yīng)字段是否允許為空值 PRIMARYKEY 可選項目 定義相應(yīng)字段是否為主鍵 創(chuàng)建新表 例10 創(chuàng)建 教師情況 表 其中包括 教師編號 姓名 性別 出生日期 婚否 學(xué)院編號 職稱 職務(wù) 政治面貌 賬號 基本工資 崗位津貼等字段 相應(yīng)語句如下 CREATETABLE教師情況 教師編號CHAR 8 PRIMARYKEY 姓名CHAR 10 NOTNULL 性別CHAR 1 NOTNULL 出生日期DATETIME 婚否Bit 學(xué)院編號CHAR 2 職稱CHAR 10 職務(wù)CHAR 2 政治面貌CHAR 10 賬號CHAR 16 NOTNULL 基本工資MONEY 崗位津貼MONEY 修改表的結(jié)構(gòu) ALTERTABLE語句針對指定的表可以進行多種與字段相關(guān)的操作 各種操作通過可選子句來指定 這些子句如 ADDCOLUMN向表中增加一個新字段ALTERCOLUMN修改指定字段的類型及大小DROPCOLUMN從表中刪除一個字段ADDCONSTRAINT可添加一個多字段索引DROPCONSTRAINT可刪除多字段索引在使用ALTERTABLE語句時 應(yīng)注意一次只能涉及一個字段或索引 不能一次添加或刪除多個字段或索引 修改表的結(jié)構(gòu) 例11 在 教師情況 表中增加一個 相片 字段 該字段類型為OLE對象 可以用來保存圖像 相應(yīng)的SQL語句如下 ALTERTABLE教師情況ADDCOLUMN相片OleObjec在ALTERTABLE語句使用ADDCOLUMN子句增加新字段時 對新字段的定義要求與CREATETABLE語句相同 例12 將 教師情況 表中的職務(wù)字段的大小改為10 相應(yīng)的SQL語句如下 ALTERTABLE教師情況ALTERCOLUMN職務(wù)CHAR 10 注意 當使用ALTERTABLE修改字段類型或大小時 可能出現(xiàn)由于類型轉(zhuǎn)換而產(chǎn)生的原有數(shù)據(jù)的變化 或者由于大小變化丟失部分數(shù)據(jù)的問題 修改表的結(jié)構(gòu) 例13 刪除 教師情況 表中的崗位津貼字段 相應(yīng)的SQL語句如下 ALTERTABLE教師情況DROPCOLUMN崗位津貼刪除一個字段前應(yīng)該確定該字段中的數(shù)據(jù)確實不需要了 因為刪除字段的操作將使該字段連同相應(yīng)的數(shù)據(jù)都被刪除掉 并且是不可恢復(fù)的 創(chuàng)建索引 建立索引是加快查詢速度的有效手段 用戶可以根據(jù)應(yīng)用的需要 在基本表上建立一個或多個索引 以加快對表中數(shù)據(jù)的查找速度 索引可以在創(chuàng)建新表時在CREATETABLE語句中建立 也可以利用CREATEINDEX語句來建立 創(chuàng)建索引 CREATEINDEX語句的完整格式為 CREATE UNIQUE INDEX索引的名稱ON表的名稱 字段名1 ASC DESC 字段名2 ASC DESC 語句中的 UNIQUE 屬于可選項 如果在INDEX這個詞之前加上保留字 UNIQUE 表示創(chuàng)建的是唯一性索引 即此索引的每一個索引值只對應(yīng)唯一的數(shù)據(jù)記錄 ASC DESC 也是可選項 ASC表示升序 DESC表示降序 當ASC或DESC在某字段名后出現(xiàn)時 表示對該字段按升序或降序創(chuàng)建索引 如果不指明ASC或DESC 則表示默認為ASC 如果需要按多個字段建立索引 可以在字段列表中給出多個字段名 以及排序規(guī)則 創(chuàng)建索引 例14 為 教師情況 表按 賬號 建索引 索引名稱為 教師賬號 CREATEINDEX教師賬號ON教師情況 賬號 本例語句執(zhí)行后 打開 教師情況 表 選擇打開 索引 對話框 在該對話框中 可以看到基于該表所建的兩個索引 一個是按 教師編號 建立的主索引 另一個就是新建的名為 教師賬號 的索引 創(chuàng)建索引 例15 為 學(xué)生考試 表按學(xué)號 升序 和課程編號 降序 建名為 學(xué)號課程 的索引 CREATEINDEX學(xué)號課程ON學(xué)生考