北京郵電大學(xué)軟件工程研究生的課程數(shù)據(jù)庫設(shè)計(jì)開發(fā).ppt

北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3SQL 自從20世紀(jì)80年代以來 SQL就是一個(gè)通用的 功能極強(qiáng)的關(guān)系數(shù)據(jù)庫語言 現(xiàn)在 SQL語言正從關(guān)系形式 ANSISQL 92標(biāo)準(zhǔn) 轉(zhuǎn)向?qū)ο?關(guān)系形式 ANSISQL 99標(biāo)準(zhǔn) 1999年頒布 SQL語言是1974年由Boyce和Chamberlin提出 1986年10月美國國家標(biāo)準(zhǔn)局 ANSl 批準(zhǔn)了SQL作為關(guān)系數(shù)據(jù)庫語言的美國標(biāo)準(zhǔn) 同年公布了SQL標(biāo)準(zhǔn)文本 簡稱SQL 86 1987年6月國際標(biāo)準(zhǔn)化組織 ISO 也采納了此標(biāo)準(zhǔn) 1989年 美國國家標(biāo)準(zhǔn)局 ANSI 采納了新的規(guī)范SQL 89標(biāo)準(zhǔn) 取代SQL 86 同時(shí)SQL 89標(biāo)準(zhǔn)也被國際標(biāo)準(zhǔn)化組織 ISO 采納 1992年 ANSI ISO頒布了SQL2版本 標(biāo)準(zhǔn)的名稱為SQL 92 SQL 92分稱幾個(gè)順序級別 從代表SQL 89最小擴(kuò)展集的 Entry 到 Intermediate 和 Full 完成于己于1999年的SQL 99具有更加高級的特征 包括對象 關(guān)系特性 亦稱SQL3 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3SQL 主要的幾個(gè)數(shù)據(jù)庫生產(chǎn)廠商并不可能完全遵守SQL 99 以及更老的SQL 92 我們一般更加關(guān)注在產(chǎn)品中已經(jīng)實(shí)現(xiàn)的SQL 99特征 1 3 1SQL特點(diǎn)1 3 2數(shù)據(jù)定義1 3 2 1SQL模式的創(chuàng)建和刪除1 3 2 2SQL提供的基本數(shù)據(jù)類型1 3 2 3定義 刪除與修改基本表1 3 2 4建立與刪除索引1 3 2 5視圖的創(chuàng)建和刪除1 3 3數(shù)據(jù)查詢1 3 3 1簡單查詢1 3 3 2子查詢1 3 3 3UNION運(yùn)算和FORALL條件 1 3 3 4高級SQL語法1 3 3 5集合函數(shù)1 3 3 6行分組1 3 4數(shù)據(jù)更新1 3 4 1插入數(shù)據(jù)1 3 4 2修改數(shù)據(jù)1 3 4 3刪除數(shù)據(jù) 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 1SQL特點(diǎn) 1 綜合統(tǒng)一2 高度非過程化3 面向集合的操作方式4 以同一種語法結(jié)構(gòu)提供兩種使用方式5 語言簡捷 易學(xué)易用和關(guān)系代數(shù)相比 就查詢能力而言 SQL并沒有根本的改進(jìn) 在關(guān)系代數(shù)查詢方面的經(jīng)驗(yàn)可以成為用SQL來實(shí)現(xiàn)查詢的良好借鑒 在構(gòu)造查詢時(shí)SQL的select語句比關(guān)系代數(shù)要靈活 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 2數(shù)據(jù)定義 SQL的數(shù)據(jù)定義功能包括對模式 Schema 表 關(guān)系 Table 視圖 View 和索引 Index 的創(chuàng)建 刪除和修改操作 如下表所示 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 2 1SQL模式的創(chuàng)建和刪除 在SQL 99中 模式是表 索引及其他數(shù)據(jù)庫對象的集合 模式名通常是一個(gè)用戶名 在CoreSQL 99和當(dāng)前大多數(shù)產(chǎn)品中 當(dāng)用戶的數(shù)據(jù)庫帳戶建立時(shí) 其模式在用戶名之后給出 他們不能再建立其他模式 SQL 99的擴(kuò)展特性允許用戶建立附加模式 一個(gè)SQL模式由模式名和模式擁有者的用戶名或賬號來確定 SQL模式的創(chuàng)建可用CREATE語句實(shí)現(xiàn) 其句法如下 CREATESCHEMAAUTHORIZATION目前只有DB2UDB允許用戶建立附加模式 其它產(chǎn)品中模式用用戶名替代 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 2 1SQL模式的創(chuàng)建和刪除 在SQL中還有一個(gè) 目錄 概念 目錄是SQL環(huán)境中所有模式的集合 包含數(shù)據(jù)庫中定義的對象的信息的表 由系統(tǒng)維護(hù) ORACLE叫數(shù)據(jù)字典 DB2UDB叫目錄表 INFORMIX叫系統(tǒng)目錄 目錄表在建立數(shù)據(jù)庫時(shí)建立 用戶不能更新 但DBA可以用select獲取這些信息 當(dāng)一個(gè)SQL模式及其所屬的基本表 視圖等元素都不需要時(shí) 可以用DROP語句撤消這個(gè)SQL模式 DROP語句的句法如下 DROPSCHEMA CASCADE RESTRICT 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 2 2SQL提供的基本數(shù)據(jù)類型 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 2 3定義 刪除與修改基本表 建立數(shù)據(jù)庫最重要的一步就是定義一些基本表 SQL語言使用CREATETABLE語句定義基本表 一般格式如下 CREATETABLE 列級完整性約束條件 列級完整性約束條件 例 CREATETABLEStudent SnoCHAR 5 PRIMARYKEY SnameCHAR 8 NOTNULL SageSMALLINTCHECK SageBETWEEN17AND22 SsexCHAR 2 CHECK SsexIN 男 女 SdeptCHAR 20 DEFAULT 軟件學(xué)院 創(chuàng)建學(xué)生表 Sno為主鍵 非空唯一 Sname非空 Sage在17到20之間取值 Ssex只能取 男 或 女 Sdept默認(rèn)值為 軟件學(xué)院 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 2 3定義 刪除與修改基本表 實(shí)際使用時(shí)要有用戶ID和密碼 進(jìn)入交互式環(huán)境 才能完成數(shù)據(jù)庫操作 修改基本表ALTERTABLE ADD 完整性約束 DROP MODIFY 其中是要修改的基本表 ADD子句用于增加新列和新的完整性約束條件 DROP子句用于刪除指定的完整性約束條件 MODIPY子句用于修改原有的列定義 包括修改列名和數(shù)據(jù)類型 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 2 3定義 刪除與修改基本表 刪除基本表DROPTABLE基本表一旦刪除 表中的數(shù)據(jù) 此表上建立的索引和視圖都將自動(dòng)被刪除掉 因此執(zhí)行刪除基本表的操作一定要格外小心 注意 有的系統(tǒng) 如Oracle 刪除基本表后建立在此表上的視圖定義仍然保留在數(shù)據(jù)字典中 但是 當(dāng)用戶引用時(shí)就報(bào)錯(cuò) 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 2 4建立與刪除索引 索引的功能表現(xiàn)在以下3方面 1 使用索引可以明顯地加快數(shù)據(jù)查詢的速度 2 使用索引可保證數(shù)據(jù)的唯一性 3 使用索引可以加快連接速度建立索引的原則 1 索引的建立和維護(hù)由DBA和DBMS完成 2 大表應(yīng)當(dāng)建索引 小表則不必建索引 3 對于一個(gè)基本表 不要建立過多的索引 4 根據(jù)查詢要求建索引 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 2 4建立與刪除索引 CREATE UNIQUE CLUSTER INDEXON 如果數(shù)據(jù)增加刪改頻繁 系統(tǒng)會(huì)花費(fèi)許多時(shí)間來維護(hù)索引 這時(shí) 可以刪除一些不必要的索引 DROPINDEX 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 2 5視圖的創(chuàng)建和刪除 視圖是關(guān)系數(shù)據(jù)庫系統(tǒng)提供給用戶以多種角度觀察數(shù)據(jù)庫中數(shù)據(jù)的重要機(jī)制 視圖一經(jīng)定義 就可以和基本表一樣被查詢 被刪除 我們也可以在一個(gè)視圖之上再定義新的視圖 但對視圖的更新 增加 刪除 修改 操作則有一定的限制 CREATVIEW 列名 AS WITHCHECKOPTION 其中子查詢可以是任意復(fù)雜的SELECT語句 但通常不允許含有ORDERBY子句和DISTINCT短語 WITHCHECKOPTION表示對視圖進(jìn)行UPDATE INSERT和DELETE操作時(shí)要保證更新 插入或刪除的行滿足視圖定義中的謂詞條件 即子查詢中的條件表達(dá)式 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 2 5視圖的創(chuàng)建和刪除 DROPVIEW 視圖刪除后視圖的定義將從數(shù)據(jù)字典中刪除 但是由該視圖導(dǎo)出的其他視圖定義仍在數(shù)據(jù)字典中 不過該視圖已失效 用戶使用時(shí)會(huì)出錯(cuò) 要用DROPVIEW語句將它們一一刪除 DBMS執(zhí)行CREATEVIEW語句的結(jié)果只把視圖的定義存入數(shù)據(jù)字典 并不執(zhí)行其中的SELECT 在關(guān)系數(shù)據(jù)庫中 并不是所有的視圖都是可更新的 因?yàn)橛行┮晥D的更新不能的有意義的轉(zhuǎn)換成對基本表的更新 行列子集視圖是可更新的 各個(gè)DBMS對視圖的更新有自己的規(guī)定 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3數(shù)據(jù)查詢 數(shù)據(jù)查詢是數(shù)據(jù)庫的核心操作 SQL語言的數(shù)據(jù)查詢只有一條SELECT語句 SELECT ALL DISTINCT FROM WHERE GROUPBY HAVING ORDERBY ASC DESC ASC DESC 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3數(shù)據(jù)查詢 Select語句的執(zhí)行過程可以理解為 首先 對FROM子句中的所有表做關(guān)系乘積接著 刪除不滿足WHERE子句的行根據(jù)GROUPBY子句對剩余的行進(jìn)行分組然后刪除不滿足HAVING子句的組求出SELECT子句選擇列表的表達(dá)式的值若有關(guān)鍵詞DISTINCT存在 則刪除重復(fù)的行Select中的標(biāo)識(shí)符 一般的SQL標(biāo)識(shí)符是大小寫無關(guān)的 實(shí)際上SQL在解釋以前會(huì)把它們轉(zhuǎn)化為大寫形式 一個(gè)標(biāo)識(shí)符必須以一個(gè)字母打頭 EntrySQL 92和CoreSQL 99將一個(gè)標(biāo)識(shí)符字節(jié)數(shù)限制在18個(gè)以內(nèi) 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3數(shù)據(jù)查詢 表達(dá)式可以是數(shù)值表達(dá)式 字符串表達(dá)式和日期表達(dá)式等 數(shù)值表達(dá)式由常數(shù) 表屬性 算術(shù)運(yùn)算符 算術(shù)函數(shù)所組成 字符串表達(dá)式由常數(shù) 表屬性 字符串運(yùn)算符 字符串函數(shù)所組成 日期表達(dá)式由常數(shù) 表屬性 日期運(yùn)算符 日期函數(shù)所組成WHERE中使用謂詞來表示條件 一般情況謂詞運(yùn)算結(jié)果為TRUE或FALSE 但如果遇到空值時(shí) 可能為UNKNOWN SQL查詢的一個(gè)爭議點(diǎn)即 對于同一個(gè)查詢會(huì)存在眾多不同的構(gòu)造方法 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3ORACLE INFORMIX DB2UDB中的一些數(shù)學(xué)函數(shù) 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3一些標(biāo)準(zhǔn)的和特定產(chǎn)品的串處理函數(shù) 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3SQL中的標(biāo)準(zhǔn)謂詞 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 1簡單查詢 例 檢索定貨記錄中所有pid值 selectpidfromorders 結(jié)果中有重復(fù)的pidselectdistinctpidfromorders 結(jié)果中pid唯一select缺省為all distinct沒有出現(xiàn)時(shí)允許重復(fù)行 缺省情況不遵守行唯一性規(guī)則 例 在orders表上生成每筆業(yè)務(wù)的利潤profit 收入減去60 的成本 顧客的折扣以及代理商的酬金 selectordno x cid x aid x pid 40 x qty p price 01 c discnt a percent x qty p price asprofitfromordersasx customerasc agentsasa productsaspwherec cid x cidanda aid x aidandp pid x pid 乘積 投影 選擇 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 1簡單查詢 注 1 SQL 99規(guī)定FROM子句中執(zhí)行連接運(yùn)算 但大多數(shù)產(chǎn)品通過笛卡爾積運(yùn)算并且在WHERE子句中包含表示參與連接的列值相等的條件來模擬連接運(yùn)算 具體實(shí)現(xiàn)方法 執(zhí)行計(jì)劃 查詢優(yōu)化 各不相同 2 FROM中AS被省略 SQL仍能識(shí)別表別名 ORACLE和INFORMIX使用別名或表別名 DB2UDB使用相關(guān)名 3 列表達(dá)式的列名可以通過AS指明 ORACLE中稱列別名 INFORMIX中稱顯示標(biāo)簽 DB2UDB中簡單稱為列名 如果沒有AS ORACLE中將完整表達(dá)式文本作為列名 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 2子查詢 每個(gè)Select查詢都會(huì)生成一張表 但我們不能像關(guān)系代數(shù)表達(dá)式那樣任意將一個(gè)Select語句嵌入另一個(gè)Select語句 這是SQL與關(guān)系代數(shù)的一個(gè)很重要的不同點(diǎn) 例如 from子句中不能出現(xiàn)select SQL 99標(biāo)準(zhǔn)已去掉該限制 但數(shù)據(jù)庫產(chǎn)品中并未完全實(shí)現(xiàn) where子句中顯然可以出現(xiàn)select 出現(xiàn)在另一個(gè)select語句之內(nèi)的select語句形式稱為子查詢 一個(gè)子查詢能以許多種方式出現(xiàn)在另一個(gè)select語句的WHERE子句條件中 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 2子查詢 1 IN謂詞 NOTIN 例 求通過住在北京或上海的代理商訂貨的顧客的姓名和折扣 selectcname discntfromcustomerswherecidin selectcidfromorderswhereaidin selectaidfromagentswherecityin 北京 上海 例 求由住在北京的顧客和住在北京的代理商組成的所有訂貨ordno selectordnofromorderswhere cid aid in selectcid aidfromcustomersc agentsawherec city 北京 anda city 北京 以上兩例為不相關(guān)查詢 內(nèi)層子查詢獨(dú)立于外層的select 例 找出訂購了產(chǎn)品p05的顧客的名字 selectdistinctcnamefromcustomerswhere p05 in selectpidfromorderswherecid customers cid 本例為相關(guān)查詢 子查詢使用外層select語句提供的數(shù)據(jù) SQL 99允許 有些系統(tǒng)不允許 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 2子查詢 2 量化比較謂詞 expr SOME ANY ALL subquery 為比較運(yùn)算符 SOME和ANY含義相同 SOME是最新版本的推崇形式 例 找出傭金百分率最小的代理商aid selectaidfromagentswherepercent all selectpercentfromagents 例 求出滿足以下條件的顧客cid 該顧客的discnt小于任一住在北京的顧客的discnt 錯(cuò)誤 selectcidfromcustomerwherediscnt any selectdiscntfromcustomerswherecity 北京 正確 selectcidfromcustomerwherediscnt all selectdiscntfromcustomerswherecity 北京 注意any不是任意 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 2子查詢 3 EXISTS謂詞 EXISTS Subquery 為真當(dāng)且僅當(dāng)子查詢返回一個(gè)非空集合 NOTEXISTS Subquery 為真當(dāng)且僅當(dāng)返回集合為空 例 求出既訂購了產(chǎn)品p01有訂購了產(chǎn)品p07的顧客cid 關(guān)系代數(shù) cid pid p01 O cid pid p07 O selectdistinctcidfromordersxwherepid p01 andexists select fromorderswherecid x cidandpid p07 或selectdistinctx cidfromordersx ordersywherex pid p01 andx cid y cidandy cid p07 EXISTS的查詢一般能找到等價(jià)的其他查詢形式 使用和不使用exists 交運(yùn)算 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 2子查詢 NOTEXISTS確實(shí)為我們帶來了一些新的功能 notexists能被用來實(shí)現(xiàn)關(guān)系代數(shù)的MINUS運(yùn)算 如果R和S是兩個(gè)兼容表 屬性相同A1 An R S用SQL計(jì)算 selectA1 AnfromRwherenotexists select fromSwhereS A1 R A1and andS An R An 例 找出沒有通過代理商a03訂貨的顧客cid 關(guān)系代數(shù) cid O cid aid a03 O selectdistinctcidfromordersxwherenotexists select fromorderswherecid x cidandaid a03 差運(yùn)算 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 3UNION運(yùn)算和FORALL條件 為了提供關(guān)系代數(shù)的 運(yùn)算 SQL使用UNION subqueryUNION ALL subquery例 包含了顧客所在的或代理商所在或兩者皆在的城市名單 selectcityfromcustomersunionselectcityfromagents 或selectcityfromcustomersunionallselectcityfromagents 行不重復(fù) 行重復(fù) 運(yùn)算 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 3UNION運(yùn)算和FORALL條件 SQL中沒有等價(jià)的 運(yùn)算 如果面臨的查詢 要求被檢索的對象集合必須符合 所有 這類關(guān)鍵詞的條件 FORALL 時(shí) 關(guān)系代數(shù)要用到除運(yùn)算 SQL中可以 1 表述要檢索的候選對象的一個(gè)反例 至少一個(gè)對象不符合條件 并建立select語句 選出所有反例 2 建立表示這類反例不存在的條件 notexists 3 建立最終select 例 求通過住在北京的所有代理商訂了貨的顧客cid 1 反例 住在北京但沒有為所求顧客c cid訂貨的代理商 select fromagentswherea city 北京 andnotexists select fromordersxwherex cid c cidandx aid a aid 2 反例不存在 notexists 反例 3 最終 selectc cidfromcustomerswherenotexists select fromagentswherea city 北京 andnotexists select fromordersxwherex cid c cidandx aid a aid 運(yùn)算 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 3UNION運(yùn)算和FORALL條件 例 找出訂購了所有被顧客c006訂購的商品的顧客的cid 反例 被c006訂購但沒有被c cid訂購的商品 selectp pidfromproductspwherep pidin selectpidfromordersxwherex cid c006 andnotexists select fromordersywherey pid p pidandy cid c cid 反例不存在 notexists selectp pidfromproductspwherep pidin selectpidfromordersxwherex cid c006 andnotexists select fromordersywherey pid p pidandy cid c cid 最終 selectcidfromcustomerswherenotexists selectp pidfromproductspwherep pidin selectpidfromordersxwherex cid c006 andnotexists select fromordersywherey pid p pidandy cid c cid 運(yùn)算 被c006訂購 沒有被c cid訂購 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 4高級SQL語法 以下介紹的高級SQL運(yùn)算符不是EntrySQL 92的部分 但幾乎都屬于SQL 99 目前產(chǎn)品中不一定支持 但可能出現(xiàn)在未來的數(shù)據(jù)庫產(chǎn)品中 1 INTERSECT 和EXCEPT 運(yùn)算符Subquery UNION ALL INTERSECT ALL EXCEPT ALL Subquery 注 CoreSQL 99只有EXCEPT而沒有EXCEPTALL 兩個(gè)子查詢從左到右的列類型是兼容的 即可以并 交 差 如類型char 5 和類型char 10 的列運(yùn)算 結(jié)果為char 10 ORACLE提供UNION UNIONALL INTERSECT MINUS EXCEPT 但不支持INTERSECTALL或MINUSALL DB2UDB都支持 ALL考慮重復(fù)行及數(shù)目 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 4高級SQL語法 例 QUNIONALLQUNIONALLQ INTERSECTALL QUNIONALLQ 結(jié)果是 QUNIONALLQ 包含兩個(gè)重復(fù)行 QUNIONALLQUNIONALLQ INTERSECT QUNIONALLQ 結(jié)果是 Q 沒有包含重復(fù)行 QUNIONALLQUNIONALLQ EXCEPTALL QUNIONALLQ 結(jié)果是 Q 沒有包含重復(fù)行 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 4高級SQL語法 2 連接形式通用形式 FROMtablename AS corr name SQL 99 FROM后面還可以是 一般形式 tablename AS corr name colname colname 子查詢 subquery AS corr name colname colname 顯式連接 table1 INNER LEFT RIGHT FULL OUTER JOINtable2ONcondition顯然SQL 99中允許子查詢出現(xiàn)在FROM子句中 以上三種形式在產(chǎn)品中并沒有完全實(shí)現(xiàn) 使用時(shí)可以試用或幫助 可以是視圖名 ORACLE不能有AS 允許為表的列重新命名 允許是子查詢 此時(shí)別名必須 內(nèi)連 左連 右連 外連 連接條件 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 4高級SQL語法 例 檢索至少訂購了一件價(jià)格低于0 5的商品的顧客姓名 selectdistinctcnamefrom ordersojoinproductspono pid p pid joincustomerscono cid c cidwherep price 0 5 注 ORACLE僅提供左連和右連 而且語法與標(biāo)準(zhǔn)SQL也不同 SELECT FROMT1 T2WHERE T1 c1 T2 c2 T1 c1 T2 c2 ANDcondition T2保留所有行 與T1連不上的用NULL 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 5SQL中的集合函數(shù) SQL中稱集合函數(shù) setfunction ORACLE稱組函數(shù) groupfunction DB2UDB稱列函數(shù) columnfunction INFORMIX稱聚集函數(shù) aggregatefunction 集合函數(shù)語法 SET FUNCTION ALL DISTINCT col COUNT 注 ALL時(shí)包括重復(fù)行 DISTINCT不包括重復(fù)行 WHERE子句比較操作中不能使用集合函數(shù) 如discnt max 集合函數(shù)忽略了所有空值 集合函數(shù)不允許嵌套使用 如AVG selectMAX dollars 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 6SQL中行的分組 SQL報(bào)表功能 根據(jù)某些列值的共性把一個(gè)表所包含的全部行分成若干個(gè)子集 然后對每個(gè)子集執(zhí)行集合函數(shù) 例 打印每個(gè)代理商為顧客c002和c003訂購產(chǎn)品及產(chǎn)品總數(shù)量 selecta aid aname p pid pname sum qty fromordersx productsp agentsawherex pid p pidandx aid a aidandx cidin c002 c003 groupbya aid a aname p pid p pname GROUPBY對象的列上的空值會(huì)被分在同一組里 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 6SQL中行的分組 如果要去掉分組后的某些行 不能用where 只能用HAVING子句 例 求被至少兩個(gè)顧客訂購的產(chǎn)品pid selectpidfromordersgroupbypidhavingcount distinctcid 2 如果沒有GROUPBY只有HAVING 則整個(gè)結(jié)果為一組 基本SQLselect語句的通用形式不允許集合函數(shù)的嵌套 但可以有變通的GROUPBY形式 例 求所有代理商的最大銷售額的平均值 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 3 6SQL中行的分組 例 求所有代理商的最大銷售額的平均值 錯(cuò) selectavg selectmax dollars fromordersgroupbyaid 對 SQL高級形式 ORACLE中可行 selectavg t x from selectaid max dollars asxfromordersgroupbyaid t 常用作法 creatviewtas selectaid max dollars asxfromordersgroupbyaid selectavg t x fromt 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 4SQL數(shù)據(jù)更新 SQL中數(shù)據(jù)更新包括插入數(shù)據(jù) Insert 修改數(shù)據(jù) Update 和刪除數(shù)據(jù) Delete 三條語句 1 3 4 1插入數(shù)據(jù)INSERTINTO VALUES expr1 NULL expr2 NULL Subquery 將新元組插入指定表中 其中新記錄屬性列1的值為expr1 屬性列2的值為expr2 INTO子句中沒有出現(xiàn)的屬性列 新記錄在這些列上將取空值 還可通過子查詢批量插入數(shù)據(jù) 在表定義時(shí)說明了NOTNULL的屬性列不能取空值 否則會(huì)出錯(cuò) 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 4SQL數(shù)據(jù)更新 如果INTO子句中沒有指明任何列名 則新插入的記錄必須在每個(gè)屬性列上均有值 給定值對應(yīng)于表定義的字段順序 子查詢不需要用括號括起來 insertintomy cselect fromcustomerswherecity 北京 insertintomy c select fromcustomerswherecity 北京 1 3 4 1修改數(shù)據(jù)UPDATESET WHERE 與customers表結(jié)構(gòu)一樣 錯(cuò)誤 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 4SQL數(shù)據(jù)更新 其功能是修改指定表中滿足WHERE子句條件的元組 其中SET子句給出expr的值用于取代相應(yīng)的屬性列值 如果省略WHERE子句 則表示要修改表中的所有元組 只有一個(gè)表可以作為UPDATE的對象 有些系統(tǒng)中不允許在SET子句中使用限定屬性名 updateagentssetagents percent 1 1 agents percentEntrySQL 92中SET子句中不能用子查詢 但SQL 99及ORACLE DB2UDB等都支持 用customers表中最新discnt更新my c中discntupdatemy csetdiscnt selectdiscntfromcustomerswherecid my c cid 錯(cuò)誤 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 1 3 4SQL數(shù)據(jù)更新 刪除語句的一般格式為 DELETEFROM WHERE 從指定表中刪除滿足WHERE子句條件的所有元組 如果省略WHERE子句 表示刪除表中全部元組 但表的定義仍在字典中 DELETE語句刪除的是表中的數(shù)據(jù) 而不是關(guān)于表的定義 刪除條件中可以有子查詢刪除總訂貨金額小于600的代理商 deletefromagentswhereaidin selectaidfromordersgroupbyaidhavingsum dollars 600 增刪改操作只能對一個(gè)表操作 因此在執(zhí)行增刪改操作時(shí) 要注意數(shù)據(jù)庫中數(shù)據(jù)的一致性 北京郵電大學(xué)軟件學(xué)院郭文明2003 06 數(shù)據(jù)庫設(shè)計(jì)與開發(fā) 講義 作業(yè) 1 檢索傭金百分率大于最小百分率的代理商aid 2 求通過住在北京或上海的代理商訂貨的顧客cid 使用子查詢和不使用子查詢兩種 3 R S和T具有相同的屬性列A1 An 不用高級SQL來實(shí)現(xiàn)關(guān)系表達(dá)式 RUNIONS MINUST 4 求出沒有為任何住在北京的顧客訂購任何在天津生產(chǎn)的產(chǎn)品的代理商的aid 5 沒有一個(gè)包含GROUPBY子句的查詢會(huì)返回重復(fù)行 這一命題為真嗎 如果為真 解釋原因 否則 給出反例 6 說出你所用到DBMS和其中的SQL語句 他們與講課時(shí)的不一樣或者不能用