信息管理系統(tǒng)數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL課件

111語言的特點(diǎn)語言的組成的數(shù)據(jù)定義語句的使用的數(shù)據(jù)查詢語句的使用的數(shù)據(jù)操縱語句的使用的數(shù)據(jù)控制語句的使用本章要點(diǎn)：參考書p892的工作原理請求數(shù)據(jù)數(shù)據(jù)庫數(shù)據(jù)庫管理系統(tǒng)當(dāng)用戶去要檢索數(shù)據(jù)庫的數(shù)據(jù)時通過發(fā)出申請，對請求進(jìn)行處理，將結(jié)果返回用戶。3的特點(diǎn)通用性強(qiáng):集數(shù)據(jù)定義/數(shù)據(jù)操縱/數(shù)據(jù)控制功能于一體兩種使用方式(統(tǒng)一的語法結(jié)構(gòu))方式一、獨(dú)立的用于聯(lián)機(jī)交互使用方式；方式二、嵌入到高級語言使用。面向集合(表)：改變以往操作方式(記錄操作)，建立表間(集合)的數(shù)據(jù)操作。易學(xué)易用:語法非常接近自然語言（英語)便于學(xué)習(xí)理解4支持三級模式視圖：是一個虛表。數(shù)據(jù)庫中僅存視圖定義，不存對應(yīng)的數(shù)據(jù)?；颈恚邯?dú)立存在的表。在中一個關(guān)系對應(yīng)一個表，每個基表對應(yīng)一個存儲文件，一個表可帶若干索引。存儲文件：邏輯結(jié)構(gòu)組成數(shù)據(jù)庫的內(nèi)模式。存儲文件+索引=內(nèi)模式5外模式模式內(nèi)模式Storefile1Storefile2Storefile3Storefile4Basetable1Basetable2Basetable3Basetable4View1View2SQL用戶支持關(guān)系數(shù)據(jù)庫三級模式視圖基本表存儲文件6數(shù)據(jù)查詢語言（）：

數(shù)據(jù)定義語言（）:；；數(shù)據(jù)操作語言（）:;;數(shù)據(jù)控制語言（）:

的基本功能7語言的語句表語言命令實現(xiàn)的功能DDLCREATE創(chuàng)建表DROP刪除表ALTER修改表結(jié)構(gòu)DQLSELECT查詢數(shù)據(jù)庫中滿足指定條件的記錄DMLINSERT向表中插入記錄UPDATE修改表中記錄的數(shù)據(jù)DELETE刪除表中符合條件的記錄DCLGRANT給用戶授權(quán)REVOKE收回用戶的權(quán)限8數(shù)據(jù)定義功能；；操作對象操作方式創(chuàng)建刪除修改表

視圖

索引

用戶創(chuàng)建或重新構(gòu)建數(shù)據(jù)庫的語言9基本表定義：<表名>(<列名><數(shù)據(jù)類型>[<列級完整性約束>],<列名><數(shù)據(jù)類型>[<列級完整性約束>],……,[<表級完整性約束>])[<其它參數(shù)>]?

<表名>給出要創(chuàng)建的基本表的名稱；?

<列名>給出列名或字段名；?

<數(shù)據(jù)類型>?

<列級完整性約束>?

<表級完整性約束>?

<其它參數(shù)>10數(shù)據(jù)類型返回11列級完整性約束用于定義列或字段一級的完整性約束，一般包括：和約束約束約束約束定義約束12表級完整性約束用于定義表一級的完整性約束，一般包括：約束（復(fù)合屬性構(gòu)成的主關(guān)鍵字說明）約束（外部關(guān)鍵字及參照關(guān)系說明）約束（同時涉及到多個屬性的域完整性約束）13其它參數(shù)不是的標(biāo)準(zhǔn)選項，一般用于與物理存儲有關(guān)的說明，不同的數(shù)據(jù)庫管理系統(tǒng)定義的方式肯定不同，另外該項參數(shù)一般也不是必需的。14建立表的例子物資訂購管理數(shù)據(jù)庫的擴(kuò)展圖11115物資訂購管理數(shù)據(jù)庫在中的實現(xiàn)1617例：建立倉庫表倉庫(倉庫號(5),城市(10),面積(面積>0))18例：建立職工表職工(倉庫號(5)倉庫,職工號(5),工資(工資>=1000工資<=5000)1200)19例：建立供應(yīng)商表供應(yīng)商(供應(yīng)商號(5),供應(yīng)商名(20),地址(20))20例：建立訂購單表訂購單(職工號(5)職工,供應(yīng)商號(5)供應(yīng)商,訂購單號(5),訂購日期())21表級約束的例子假設(shè)職工關(guān)系的主關(guān)鍵字是（倉庫號,職工號），則相應(yīng)的命令如下：職工(倉庫號(5)倉庫,職工號(5),工資(工資>=1000工資<=5000)1200,(倉庫號,職工號))創(chuàng)建表23修改表結(jié)構(gòu)命令<表名><列名><數(shù)據(jù)類型>[<列級完整性約束>]<完整性約束名>|<列名>|<列名><數(shù)據(jù)類型>[<列級完整性約束>]24增加新的屬性（字段）往訂購單關(guān)系中增加一個新屬性“完成日期”訂購單完成日期25修改屬性的定義將訂購單關(guān)系的完成日期屬性的數(shù)據(jù)類型修改為訂購單完成日期26刪除完整性約束建立的列級或表級完整性約束可以刪除，但是前提是在定義完整性約束時必須給出約束名稱，否則不能刪除。設(shè)有：職工(倉庫號(5)倉庫,職工號(5),工資(工資>=1000工資<=5000)1200)則可以：職工27刪除屬性刪除訂購單關(guān)系中的“完成日期”屬性：訂購單完成日期注意：有些系統(tǒng)的命令不允許刪除屬性，如果必須要刪除屬性，一般步驟是：先將舊表中的數(shù)據(jù)備份，然后刪除舊表、并建立新表，最后將原來的數(shù)據(jù)恢復(fù)到新表中。28刪除表命令格式：<表名>例：訂購單29索引定義的命令格式[][]<索引名><表名>(<列名>[|][,<列名>[|]…])普通索引唯一（）索引聚集（）索引外模式模式內(nèi)模式Storefile1Storefile2Storefile3Storefile4Basetable1Basetable2Basetable3Basetable4View1View2SQL用戶關(guān)系數(shù)據(jù)庫的三級模式視圖基本表存儲文件文件存儲形式建立并確定數(shù)據(jù)記錄的物理順序和邏輯順序之間的對應(yīng)關(guān)系。邏輯數(shù)據(jù)記錄物理存儲方式順序文件索引文件……順序映射關(guān)系例1順序文件文件中數(shù)據(jù)記錄的物理順序與邏輯順序一致，文件記錄按關(guān)鍵字值的遞增（或遞減）次序排序，形成其邏輯順序。870001張三456870002李四645870003王五587870004孫六676870005錢七565………………12345……文件記錄物理設(shè)備關(guān)鍵字值相對磁道文件存儲具有索引表的文件稱為索引文件。索引文件主文件索引表數(shù)據(jù)記錄區(qū)索引區(qū)記錄原始信息記錄關(guān)鍵字+記錄地址組成映射加工例2索引文件文件存儲記錄地址學(xué)號姓名成績A870005張三456B870002李四645C870001王五587D870004孫六676E870003錢七565主關(guān)鍵字（學(xué)號）記錄地址870001C870002B870003E870004D870005A主文件索引表關(guān)鍵字學(xué)號與學(xué)生記錄地址的對應(yīng)表文件存儲文件存儲聚集文件（） 一個文件可以存儲多個關(guān)系的記錄。不同關(guān)系中有聯(lián)系的記錄存儲在同一塊內(nèi)，可以提高查找速度和I/O速度。例3、聚集文件（）S120MS1C180S120MS221FS1C270S1C180S322MS3C190S1C270S3C285S221FS3C395S322MS3C190S3C285S3C39536索引定義的命令格式[][]<索引名><表名>(<列名>[|][,<列名>[|]…])普通索引唯一（）索引聚集（）索引37普通索引如果沒有指定或等將建立普通索引。在單個字段上建立普通索引：訂購單(供應(yīng)商號)在多個字段上建立普通索引：

訂購單(供應(yīng)商號,職工號)38唯一索引通過指定則為表創(chuàng)建唯一索引（不允許存在索引值相同的兩個元組）。在命令中的約束將隱式創(chuàng)建唯一索引。在倉庫關(guān)系的城市屬性上建立一個唯一索引：倉庫(城市)39聚集索引通過指定建立聚集索引。在命令中的約束將隱式創(chuàng)建聚集索引。一個表只允許建立一個聚集索引。如果在創(chuàng)建表時已經(jīng)指定了主關(guān)鍵字，則不可以再創(chuàng)建聚集索引。40使用索引的原則：不應(yīng)該在一個表上建立太多的索引（一般不超過兩到三個），索引能改善查詢效果，但也耗費(fèi)了磁盤空間，降低了更新操作的性能，因為系統(tǒng)必須花時間來維護(hù)這些索引。除了為數(shù)據(jù)的完整性而建立的唯一索引外，建議在表較大時再建立普通索引，表中的數(shù)據(jù)越多，索引的優(yōu)越性才越明顯。、41索引的刪除命令格式：<索引名>例：倉庫42視圖視圖倉庫號城市面積倉庫號職工號工資倉庫號城市職工號工資倉庫號面積基本表視圖43定義視圖的命令視圖是根據(jù)對基本表的查詢定義的，其命令格式如下：<視圖名><查詢塊>從單個表派生出的視圖從多個表派生出的視圖44從單個表派生出的視圖

職工號，倉庫號職工

倉庫號，面積倉庫基于視圖進(jìn)行查詢：

*45從多個表派生出的視圖

職工號,工資,城市職工,倉庫職工.倉庫號=倉庫.倉庫號;對用戶就好象一個表：46定義視圖的例子：(職工號,月工資,年工資)職工號,工資,工資*12職工查詢視圖：*結(jié)果：E1122014640E3121014520E4125015000E6123014760E712501500047視圖的刪除命令格式是：<視圖名>48幾點(diǎn)注意在執(zhí)行插入、更新和刪除操作時可能會受到關(guān)系完整性的約束，這種約束可以保證數(shù)據(jù)庫中的數(shù)據(jù)是正確的。雖然視圖可以像基本表一樣進(jìn)行各種查詢，但是插入、更新和刪除操作在視圖上卻有一定限制。因為視圖是由基本表導(dǎo)出的，對視圖的任何操作最后都落實在基本表上，這些操作不能違背定義在表上的完整性約束。49數(shù)據(jù)操作功能插入功能：更新功能：刪除功能:50插入操作插入一個元組<表名>[(<列名>[,<列名>…])](<表達(dá)式>[,<表達(dá)式>……])插入一個查詢結(jié)果<表名>[(<列名>[,<列名>…])]<查詢>51插入操作舉例插入一個完整的元組倉庫('5','沈陽',500)插入一個不完整的元組倉庫(倉庫號,面積)('6',500)舉例倉庫表：倉庫('1','北京',370);倉庫('2','上海',500);倉庫('3','廣州',200);倉庫('4','武漢',400);舉例職工表：職工('2','E1',1220);職工('1','E3‘,1210);職工('2','E4‘,1250);職工('3','E6‘,1230);職工('1','E7‘,1250);54更新操作命令格式:<表名><列名>=<表達(dá)式>[,<列名>=<表達(dá)式>…][[<表名>]<邏輯表達(dá)式>]更新滿足“邏輯表達(dá)式”條件的記錄；一次可以更新多個屬性的值；更新的條件可以與其他的表相關(guān)（使用指定）；如果沒有指定更新條件則更新表中的全部記錄。55更新操作舉例給所有職工增加一倍的工資職工工資=工資*2給1倉庫的職工提高10%的工資職工工資=工資*2倉庫號='1'給“上?！眰}庫的職工的工資提高到三倍職工工資=工資*3倉庫倉庫.倉庫號=職工.倉庫號城市=‘上海'56刪除操作

<表名>[[<表名>]<邏輯表達(dá)式>]命令從指定的表中刪除滿足“邏輯表達(dá)式”條件的元組；如果沒有指定刪除條件則刪除表中的全部元組，所以在使用該命令時要格外小心；刪除的條件可以與其他的表相關(guān)（使用可選的指定）；命令只刪除元組，它不刪除表或表結(jié)構(gòu)。57刪除操作舉例刪除倉庫關(guān)系中倉庫號值是5的元組倉庫倉庫號='5'刪除所在城市是上海的倉庫的所有職工元組職工倉庫倉庫.倉庫號=職工.倉庫號城市='上海'58的數(shù)據(jù)查詢功能基本格式：………………59查詢[]{*|<目標(biāo)列表達(dá)式>,…,<目標(biāo)列表達(dá)式>}<表名或視圖名>[,<表名視圖名>…][<條件表達(dá)式>][<列名>[,<列名>…][<表達(dá)式>]][<列名>[][,<列名>[]..][…]60說明:要查詢的數(shù)據(jù)，說明不去掉重復(fù)元組，說明要去掉重復(fù)元組，<目標(biāo)列表達(dá)式>一般是查詢表中的列名，如果要查詢表中的所有列可以使用“*”表示[]

{*|<目標(biāo)列表達(dá)式>,…,<目標(biāo)列表達(dá)式>}61<表名或視圖名>[,<表名或視圖名>…]說明要查詢的數(shù)據(jù)來自哪個（些）表(或視圖)，可以基于單個表或多個表進(jìn)行查詢62[<條件表達(dá)式>]說明查詢條件，即選擇元組的條件，可以用于查詢條件的運(yùn)算符也非常豐富，下表列出了常用的運(yùn)算符63<列名>[,<列名>…][<表達(dá)式>]短語用于對查詢結(jié)果進(jìn)行分組，可以利用它進(jìn)行分組匯總；短語必須跟隨使用，它用來限定分組必須滿足的條件。64<列名>[]

[,<列名>]…用來對查詢的結(jié)果進(jìn)行排序。65[…]支持的短語，可以進(jìn)行帶明細(xì)的分組匯總。66查詢的分類簡單查詢排序連接查詢嵌套查詢分組及計算查詢67例：從職工關(guān)系中檢索所有工資值解1：工資職工結(jié)果1是：12201210125012301250解2：工資職工結(jié)果2是：1220121012501230簡單查詢68檢索倉庫關(guān)系中的所有元組*倉庫倉庫號,城市,面積倉庫69檢索工資多于1230元的職工號職工號職工工資>1230結(jié)果是：E4E770檢索哪些倉庫有工資多于1210元的職工倉庫號職工工資>1210結(jié)果是：23171給出在倉庫1或2工作，并且工資

少于1250元的職工號職工號職工工資<1250(倉庫號='1'倉庫號='2')結(jié)果是：E1E372檢索出工資在1220元到1240元

范圍內(nèi)的職工信息*職工工資12201240結(jié)果是：2E112203E61230表達(dá)式“工資12201240“等價于(工資>=1220)(工資<=1240)73從供應(yīng)商關(guān)系中檢索出全部公司的信息*供應(yīng)商供應(yīng)商名'%公司'結(jié)果是：S4華通電子公司北京這里的是字符串匹配運(yùn)算符，通配符“%”表示0個或多個字符，另外還有一個通配符“_”（下劃線）表示一個字符。74找出不在北京的全部供應(yīng)商信息*供應(yīng)商地址'北京'或*供應(yīng)商(地址='北京')結(jié)果是：S3振華電子廠西安S6607廠鄭州的應(yīng)用范圍很廣，比如，可以有、等。75找出尚未確定供應(yīng)商的訂購單*訂購單供應(yīng)商號結(jié)果是：E677 E180 E390 注意：查詢空值時要使用，而是無效的，因為空值不是一個確定的值，所以不能用“=”這樣的運(yùn)算符進(jìn)行比較。76列出已經(jīng)確定了供應(yīng)商的訂購單信息*訂購單供應(yīng)商號結(jié)果是：E3 S3 91 2002-07-1300:00:00.000E7 S4 76 2002-05-2500:00:00.000E3 S4 79 2002-06-1300:00:00.000E1 S4 73 2002-07-2800:00:00.000E3 S7 67 2002-06-2300:00:00.00077排序

<列名>[][,<列名>[]..可以對查詢的結(jié)果進(jìn)行排序，可以是升序或降序，可以按多列排序。按職工的工資值升序檢索出全部職工信息先按倉庫號排序，再按工資排序并輸出全部職工信息78按職工的工資值升序檢索出全部職工信息*職工工資結(jié)果是：1E312102E112203E612302E412501E7125079先按倉庫號排序再按工資排序*職工倉庫號,工資結(jié)果是：1E312101E712502E112202E412503E6123080連接查詢當(dāng)查詢的結(jié)果出自多個表時，需要通過表之間的連接操作來完成。一般連接外連接81一般連接（）常規(guī)的兩個表或多個表之間的連接。找出工資多于1230元的職工號和他們所在的城市找出工作在面積大于400的倉庫的職工號以及這些職工工作所在的城市82找出工資多于1230元的職工號

和他們所在的城市職工號,城市職工,倉庫(工資>1230)(職工.倉庫號=倉庫.倉庫號)職工號,城市職工倉庫職工.倉庫號=倉庫.倉庫號工資>1230或結(jié)果是：E4上海E7北京三個表連接？83找出工作在面積大于400的倉庫的職工號以及

這些職工工作所在的城市職工號,城市倉庫,職工(面積>400)(職工.倉庫號=倉庫.倉庫號)職工號,城市職工倉庫職工.倉庫號=倉庫.倉庫號面積>400或結(jié)果是：E1上海E4上海84外連接外連接與前面所介紹的等值連接不同。原來的連接是只有滿足連接條件，相應(yīng)的結(jié)果才會出現(xiàn)在結(jié)果表中；而外連接可以使不滿足連接條件的元組也出現(xiàn)在結(jié)果表中。按連接方式外連接又可以分為左連接（）、右連接（）和全連接（）三種。85外連接（）運(yùn)算的一般格式左連接在結(jié)果表中包含第一個表中滿足條件的所有記錄；如果是在連接條件上匹配的元組，則第二個表返回相應(yīng)值，否則第二個表返回空值。右連接在結(jié)果表中包含第二個表中滿足條件的所有記錄；如果是在連接條件上匹配的元組，則第一個表返回相應(yīng)值，否則第一個表返回空值。全連接在結(jié)果表中包含兩個表中滿足條件的所有記錄；如果是在連接條件上匹配的元組，則另一個表返回相應(yīng)值，否則另一個表返回空值。<屬性或表達(dá)式列表><表名>||[]<表名><連接條件>[<限定條件>]86舉例：等值或自然連接

倉庫.倉庫號,城市,面積,職工號,工資倉庫職工倉庫.倉庫號=職工.倉庫號結(jié)果是：2 上海 500 E1 12201 北京 370 E3 12102 上海 500 E4 12503 廣州 200 E6 12301 北京 370 E7 125087例：倉庫、職工

左連接倉庫.倉庫號,城市,面積,職工號,工資倉庫職工倉庫.倉庫號=職工.倉庫號結(jié)果是：1 北京 370 E3 12101 北京 370 E7 12502 上海 500 E1 12202 上海 500 E4 12503 廣州 200 E6 12304 武漢 400 右連接？88嵌套查詢普通嵌套查詢使用量詞的嵌套查詢使用的嵌套查詢89普通嵌套查詢當(dāng)檢索關(guān)系X中的元組時，它的條件依賴于相關(guān)的關(guān)系Y中的元組的屬性值，這時使用普通的嵌套查詢將非常方便。哪些城市至少有一個倉庫的職工的工資為1250元？找出和職工E4工資相同的所有職工。

90哪些城市至少有一個倉庫的

職工的工資為1250元？城市倉庫倉庫號(倉庫號職工工資=1250)結(jié)果是：北京上海91找出和職工E4工資相同的所有職工。職工號職工工資=(工資職工職工號=“E4”)結(jié)果是：E4E792使用量詞的嵌套查詢在嵌套查詢中可以使用、、等量詞，它們的形式是：<表達(dá)式><比較運(yùn)算符>[](子查詢)其中和是同義詞，在進(jìn)行比較運(yùn)算時只要子查詢中有一行能使結(jié)果為真，則結(jié)果就為真；而則要求子查詢中的所有行都使結(jié)果為真時，結(jié)果才為真。93檢索有職工的工資大于或等于1倉庫中

任何一名職工的倉庫號倉庫號職工工資>=(工資職工倉庫號='1')倉庫號職工工資>=((工資)職工倉庫號='1')等價于：結(jié)果是：12394檢索有職工的工資大于或等于1倉庫中

所有職工的工資的倉庫號倉庫號職工工資>=(工資職工倉庫號='1')倉庫號職工工資>=((工資)職工倉庫號='1')等價于：結(jié)果是：1295使用的嵌套查詢在嵌套查詢中還可以使用[]，具體形式是：[](子查詢)或是用來檢查在子查詢中是否有結(jié)果返回（即存在元組或不存在元組）。96檢索哪些倉庫中還沒有職工的倉庫的信息。*倉庫(*職工倉庫號=倉庫.倉庫號)*倉庫倉庫號(倉庫號職工)等價于結(jié)果是：4武漢40097檢索那些倉庫中至少已經(jīng)有

一個職工的倉庫的信息*倉庫(*職工倉庫號=倉庫.倉庫號)等價于*倉庫倉庫號(倉庫號職工)結(jié)果是：1北京3702上海5003廣州20098注意：[]只是判斷子查詢中是否有或沒有結(jié)果返回，它本身并沒有任何運(yùn)算或比較。[]實際是一種內(nèi)、外層互相關(guān)的嵌套查詢，只有在內(nèi)層引用了外層的值，這種查詢才有意義。集合運(yùn)算2005/2008支持以下的集合運(yùn)算并（）交（）差（）99集合并運(yùn)算查詢工資大于1500和工資小于1250的職工的倉庫號、職工號、和工資信息（用集合并運(yùn)算完成），結(jié)果按工資升序排序。語句倉庫號,職工號,工資職工工資>1500倉庫號,職工號,工資職工工資<1250工資注意：短語是對最終結(jié)果進(jìn)行排序，該短語不能用在中間結(jié)果上。100集合交運(yùn)算查詢2倉庫有經(jīng)手2011年6月訂購單的職工號信息（用集合交運(yùn)算完成）。語句職工號職工倉庫號='2'職工號訂購單訂購日期‘2011/06/01’‘2011/06/30’101集合差運(yùn)算查詢目前沒有經(jīng)手訂購單的職工號（用集合差運(yùn)算完成）。語句職