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

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