第四章關(guān)系數(shù)據(jù)庫(kù)實(shí)現(xiàn)及使用關(guān)系代數(shù)SQL語(yǔ)言本章要點(diǎn)退出第四章關(guān)系數(shù)據(jù)庫(kù)實(shí)現(xiàn)及應(yīng)用4.1關(guān)系代數(shù)4.2關(guān)系演算4.3SQL語(yǔ)言概貌4.4SQL數(shù)據(jù)定義功能4.5SQL數(shù)據(jù)查詢語(yǔ)句4.6SQL查詢語(yǔ)句的加強(qiáng)4.7視圖4.8SQL數(shù)據(jù)更新語(yǔ)句4.9SQL數(shù)據(jù)控制功能4.10嵌入式SQL4.11查詢優(yōu)化關(guān)系代數(shù)是一種抽象的查詢語(yǔ)言，是關(guān)系數(shù)據(jù)操縱語(yǔ)言的一種傳統(tǒng)表達(dá)方式，它是用對(duì)關(guān)系的運(yùn)算來(lái)表達(dá)查詢。按照表達(dá)查詢的方式可分為兩大類。第一類是用對(duì)關(guān)系的運(yùn)算來(lái)表達(dá)查詢的方式，稱為關(guān)系代數(shù)。第二類是用謂詞來(lái)表達(dá)查詢要求的方式稱為關(guān)系演算。關(guān)系代數(shù)一、傳統(tǒng)的集合運(yùn)算包括四種運(yùn)算：并（∪）、交（∩）、差（－）、廣義笛卡爾積（×）。1．并（Union）設(shè)關(guān)系R和關(guān)系S具有相同的目n，且相應(yīng)的屬性取自同一個(gè)域。則關(guān)系R和關(guān)系S的并記為R∪S，其結(jié)果仍為n目關(guān)系，由屬于R或?qū)儆赟的元組組成。關(guān)系代數(shù)如R和S的元組分別用兩個(gè)圓表示,則R∪S的集合如圖4.1所示虛影部分元組。關(guān)系代數(shù)圖4.1集合R∪S集合RS關(guān)系代數(shù)關(guān)系代數(shù)2、交（Intersection）設(shè)關(guān)系R和關(guān)系S具有相同的目n，且相應(yīng)的屬性取自同一個(gè)域。關(guān)系R和關(guān)系S的交記為R∩S，結(jié)果仍為n目關(guān)系，由既屬于R又屬于S的元組組成。如R和S的元組分別用兩個(gè)圓表示，則R∩S集合運(yùn)算結(jié)果可用圖4.2示意，則兩圓相交部分元組表示R與S的交。關(guān)系代數(shù)關(guān)系代數(shù)RS圖4.2集合R∩S關(guān)系代數(shù)關(guān)系代數(shù)3、差(Difference)

設(shè)關(guān)系R和關(guān)系S具有相同的目n，且相應(yīng)的屬性取自同一個(gè)域。定義關(guān)系R和關(guān)系S的差記為R-S，其結(jié)果仍為n目關(guān)系，由屬于R而不屬于S的元組組成。關(guān)系代數(shù)關(guān)系代數(shù)

如R和S的元組分別用兩個(gè)圓表示，則R-S的集合如圖4.3所示。比較圖4.1.2和圖4.1.3，顯然R＝(R∩S)∪(R-S)或R-S＝R-(R∩S)。RS圖4.3集合R-S關(guān)系代數(shù)4．笛卡爾積（ExtendedCartesianProduct）兩個(gè)分別為n，m的關(guān)系R和S的廣義笛卡爾積R×S是一個(gè)（n×m）元組的集合。元組的前n個(gè)分量是R的一個(gè)元組，后m個(gè)分量是S的一個(gè)元組.若R有K1個(gè)元組，S有K2個(gè)元組，則R×S有K1×K2個(gè)元組。記為R×S關(guān)系代數(shù)二、專門的關(guān)系運(yùn)算包括四種運(yùn)算即選擇（σ）、投影（∏）、連接（）和除法（÷），是關(guān)系數(shù)據(jù)庫(kù)數(shù)據(jù)維護(hù)、查詢、統(tǒng)計(jì)等操作的基礎(chǔ)。1．選擇（Selection）設(shè)有關(guān)系R，在關(guān)系R中求取滿足給定條件F的元組組成新的關(guān)系的運(yùn)算稱為選擇。記作:σF（R）關(guān)系代數(shù)

在σF（R）中，f是一個(gè)條件表達(dá)式，其值為“真”或“假”。σ

F

（R）是從關(guān)系R中選取使條件表達(dá)式F為真的元組。這是以行為處理單位進(jìn)行的運(yùn)算。條件表達(dá)式F是由常量、變量及算術(shù)比較符{>，≥，＜，≤，＝，≠}和邏輯運(yùn)算符（∧，∨，）等構(gòu)成。關(guān)系代數(shù)關(guān)系代數(shù)關(guān)系代數(shù)式為：σ單價(jià)≥5(營(yíng)業(yè)庫(kù))，結(jié)果如下表所示：2．投影（Projection）

設(shè)有關(guān)系R(A,B,C,D)，在關(guān)系R中求指定的若干個(gè)屬性列組成新的關(guān)系的運(yùn)算稱作投影，記作∏A（R）。其中A為欲選取的屬性的列表。可以用：列名1，列名2……形式表示，也可以用[1]，[2]，……形式表示。這是以列作為處理單位進(jìn)行的運(yùn)算，示意圖如圖4.4所示的陰影部分，a∈{A},c∈{A},d∈{A}。關(guān)系代數(shù)關(guān)系代數(shù)關(guān)系R(a,c,d)在A上的投影，即∏A(R)，A包括：a,c,d等域。關(guān)系代數(shù)∏[3]，[4]（營(yíng)業(yè)庫(kù)）,結(jié)果：注意：在投影后如出現(xiàn)重復(fù)元組，應(yīng)只保留一個(gè)

關(guān)系代數(shù)【例4.7】求“營(yíng)業(yè)庫(kù)”中所有單價(jià)大于5元的商品品名和單價(jià)，求關(guān)系運(yùn)算式。關(guān)系運(yùn)算式：∏品名，單價(jià)（σ單價(jià)≥5(營(yíng)業(yè)庫(kù))

表4.9從表4.6再作投影品名單價(jià)鋼筆10.00圓珠筆6.00筆記本8.00關(guān)系代數(shù)3．連接（Join）從兩個(gè)分別為n,m元的關(guān)系R和S的廣義笛卡爾積中選取滿足給定條件F的元組組成新的關(guān)系稱為R和S的連接，記作RS（F=AθB）。其中A和B分別為R和S上度數(shù)相等且可比的屬性列，θ是算術(shù)比較符(>，≥，＜，≤，＝，≠)。AθB例如：關(guān)系R和S如表4.10,4.11所式，求RS，連接結(jié)果如圖所示。A=C關(guān)系代數(shù)4．除（Division）給定關(guān)系R（x,y）與S（y）其中x，y為屬性集（也可為單屬性），R中的y和S中的y可以有不同的屬性名，但必須出自相同的域集。在求解R÷S時(shí)，對(duì)R按x的值分組，然后檢查每一組，如某一組中的y包含S中全部的y，則取該組中的x的值作為商關(guān)系中的一個(gè)元組，否則不取。如此求出R÷S的商。關(guān)系代數(shù)關(guān)系代數(shù)第四章關(guān)系數(shù)據(jù)庫(kù)實(shí)現(xiàn)及應(yīng)用4.1關(guān)系代數(shù)4.2關(guān)系演算4.3SQL語(yǔ)言概貌4.4SQL數(shù)據(jù)定義功能4.5SQL數(shù)據(jù)查詢語(yǔ)句4.6SQL查詢語(yǔ)句的加強(qiáng)4.7視圖4.8SQL數(shù)據(jù)更新語(yǔ)句4.9SQL數(shù)據(jù)控制功能4.10嵌入式SQL4.11查詢優(yōu)化關(guān)系演算以數(shù)理邏輯中的謂詞演算為基礎(chǔ)。謂詞（Predicate）指明一個(gè)條件，通過(guò)對(duì)它的求解可得出下列之一的值：“真”、“假”、“未知”。常見(jiàn)的謂詞例如：比較謂詞：>，≥，＜，≤，＝，≠；包含謂詞：IN；存在謂詞：EXISTS關(guān)系演算一、元組關(guān)系演算用表達(dá)式{t|Q(t)}來(lái)表示，其中t為元組變量，Q(t)是由關(guān)系名、元組變量、常量及運(yùn)算符組成的公式。{t|Q(t)}表示使Q(t)為“真”的元組的集合。關(guān)系演算

關(guān)系代數(shù)的運(yùn)算均可用關(guān)系演算表達(dá)式來(lái)表示（反之亦然）表示如下：1）并：R∪S＝{t|R(t)∨S(t)}2）交：R∩S＝{t|R(t)∧S(t)}3）差：R－S＝{t|R(t)∧S(t)}4）投影：∏i1,i2,…ik(R)={tk|(u)(R(u)∧t[1]=u[i1]∧…t[k]=u[ik])}關(guān)系演算5）選擇：其中F’是由F用t[i]代替運(yùn)算對(duì)象i得到的等價(jià)公式。6）連接={t(n+m)|(u(n))(v(m))(R(u)∧s(v)∧t[1]=u[1]∧t[2]=u[2]∧…t[n]=u[n]∧t[n+1]=v[1]∧…t[n+m]=v[m]∧)關(guān)系演算關(guān)系演算【例4.10】根據(jù)表4.2營(yíng)業(yè)庫(kù)顯示所有品名及其單價(jià)。C0l={t(2)|(u)(營(yíng)業(yè)庫(kù)(u))∧t[1]=u[品名]∧t[2]=u[單價(jià)]}二、域關(guān)系演算用表達(dá)式{x1,x2,…xk|(x1,x2,…xk)}來(lái)表示，其中x1,x2,…xk是域變量，是由關(guān)系、域變量、常量及運(yùn)算符組成的式子。{x1,x2,…xk|(x1,x2,…xk)}表示所有使(x1,x2,…xk)為“真”的那些x1,x2,…xk組成的元組的集合。每一個(gè)關(guān)系代數(shù)表達(dá)式有一個(gè)等價(jià)的域演算表達(dá)式，反之亦然。關(guān)系演算關(guān)系演算【例4.11】如果有關(guān)系：學(xué)生（學(xué)號(hào)，姓名，年齡，性別），求用域關(guān)系演算式表示年齡大于20的學(xué)生的學(xué)號(hào)，姓名，年齡。解：{<學(xué)號(hào)，姓名，年齡>|性別（<學(xué)號(hào)，姓名，年齡，性別>學(xué)生∧年齡>20）}第四章關(guān)系數(shù)據(jù)庫(kù)實(shí)現(xiàn)及應(yīng)用4.1關(guān)系代數(shù)4.2關(guān)系演算4.3SQL語(yǔ)言概貌4.4SQL數(shù)據(jù)定義功能4.5SQL數(shù)據(jù)查詢語(yǔ)句4.6SQL查詢語(yǔ)句的加強(qiáng)4.7視圖4.8SQL數(shù)據(jù)更新語(yǔ)句4.9SQL數(shù)據(jù)控制功能4.10嵌入式SQL4.11查詢優(yōu)化SQL（StructuredQueryLanguage）:指結(jié)構(gòu)化查詢語(yǔ)言，但成為關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言之后它已成為一種綜合的、通用的、功能極強(qiáng)的關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言。SQL語(yǔ)言概貌

SQL語(yǔ)言概貌（1）一體化的特點(diǎn)。（2）語(yǔ)言簡(jiǎn)潔，易學(xué)易用。（3）高度非過(guò)程化。（4）極強(qiáng)適應(yīng)性。（5）SQL語(yǔ)言支持關(guān)系數(shù)據(jù)庫(kù)三級(jí)模式的結(jié)構(gòu)。第四章關(guān)系數(shù)據(jù)庫(kù)實(shí)現(xiàn)及應(yīng)用4.1關(guān)系代數(shù)4.2關(guān)系演算4.3SQL語(yǔ)言概貌4.4SQL數(shù)據(jù)定義功能4.5SQL數(shù)據(jù)查詢語(yǔ)句4.6SQL查詢語(yǔ)句的加強(qiáng)4.7視圖4.8SQL數(shù)據(jù)更新語(yǔ)句4.9SQL數(shù)據(jù)控制功能4.10嵌入式SQL4.11查詢優(yōu)化一、基本表的定義和修改

SQL數(shù)據(jù)定義功能包括:定義基本表定義視圖定義索引SQL數(shù)據(jù)定義功能1、定義基本表的語(yǔ)句：CREATETABLE表名（列名1類型）[NOTNULL][,列名2類型][NOTNULL]…）[其他參數(shù)]“其他參數(shù)”指與物理存儲(chǔ)有關(guān)的參數(shù)，隨具體系統(tǒng)不同而不同；“[]”表示可選項(xiàng)；“|”表示多選一項(xiàng)，對(duì)于其兩邊內(nèi)容，用戶只能且必需選一項(xiàng)。SQL數(shù)據(jù)定義功能SQL數(shù)據(jù)定義功能一般的SQL支持的數(shù)據(jù)類型有：INTEGER全字長(zhǎng)（31bits精度）的二進(jìn)制整數(shù)。SMALLINT半字長(zhǎng)（精度為15bits）的二進(jìn)制整數(shù)。DECIMAL([p,q])壓縮十進(jìn)制數(shù)，共p位，小數(shù)點(diǎn)后有q位（15≥P≥q≥0，q＝0時(shí)可省略）。FLOAT雙字長(zhǎng)的浮點(diǎn)數(shù)。CHAR(n)長(zhǎng)度為n的定長(zhǎng)字符串。VARCHAR(n)變長(zhǎng)字符串，最大長(zhǎng)度為n。

SQL數(shù)據(jù)定義功能SQL支持空值（NULL）的概念.空值是不知道的或不確定的值，除了候選關(guān)鍵字外，任何列可以有空值。如不允許空值，則應(yīng)指定該列NOTNULL

2、修改基本表定義語(yǔ)句

ALTERTABLE表名ADD列名類型例如ALTERTABLE學(xué)生ADD班級(jí)CHAR(4)在修改基本表定義的列的語(yǔ)句中不允許NOTNULL。

ALTERTABLE表名RENAMECOLUMN原列名TO新列名用于更改某列名。SQL數(shù)據(jù)定義功能

ALTERTABLE

表名ALTERCOLUMN

列名類型[(寬度)[,小數(shù)位]]修改字段類型、字段或數(shù)值型字段寬度和數(shù)值型字段小數(shù)點(diǎn)后位數(shù)。ALTERTABLE

表名

DROPCOLUMN

列名從表中刪去一列。SQL數(shù)據(jù)定義功能3、刪除基本表的語(yǔ)句

DROPTABLE表例如：DROPTABLE學(xué)生將把一個(gè)基本表的定義連同其中記錄、索引及它導(dǎo)出的所有視圖全部刪除或不可引用。SQL數(shù)據(jù)定義功能二、索引的建立和刪除1、索引的建立

CAEATE[UNIQUE]INDEX索引名ON基本表名（列名[次序][,列名[次序]]…）[其他參數(shù)]

索引可以建立在一列或幾列上，其中次序可取：ASC（升序）或DESC（降序），缺省值為升序。UNIQUE表示每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄。用中括號(hào)[]表示是可選項(xiàng)。例如CREATEINDEX學(xué)生索引ON學(xué)生（班級(jí)DESC，學(xué)生號(hào)）。SQL數(shù)據(jù)定義功能2.刪除索引的語(yǔ)句DROPINDEX索引名例如DROPINDEX學(xué)生索引SQL數(shù)據(jù)定義功能第四章關(guān)系數(shù)據(jù)庫(kù)實(shí)現(xiàn)及應(yīng)用4.1關(guān)系代數(shù)4.2關(guān)系演算4.3SQL語(yǔ)言概貌4.4SQL數(shù)據(jù)定義功能4.5SQL數(shù)據(jù)查詢語(yǔ)句4.6SQL查詢語(yǔ)句的加強(qiáng)4.7視圖4.8SQL數(shù)據(jù)更新語(yǔ)句4.9SQL數(shù)據(jù)控制功能4.10嵌入式SQL4.11查詢優(yōu)化一、語(yǔ)句格式SQL語(yǔ)言數(shù)據(jù)庫(kù)查詢語(yǔ)句一般格式是：SELECT*|<表達(dá)式>FROM基本表|視圖[WHERE條件表達(dá)式1][GROUPBY列名1[HAVLNG條件表達(dá)式2]……][ORDERBY列名2[ASC/DESC]……]注意：這是一條SQL語(yǔ)句SQL數(shù)據(jù)查詢語(yǔ)句SQL數(shù)據(jù)查詢語(yǔ)句

“目標(biāo)列”的格式可以是以下格式：1）列名1，列名2……其中列名1，列名2為FROM子句中所指基本表或視圖中的列名；如果FROM子句中指定多個(gè)表，且列名有相同的時(shí)，則列名應(yīng)寫為“表名.列名”的形式。SQL數(shù)據(jù)查詢語(yǔ)句2）上述每一項(xiàng)可以是表達(dá)式，“目標(biāo)列”允許使用SQL提供的集合函數(shù)形成表達(dá)式。常用的集合函數(shù)如下：COUNT(*)計(jì)算記錄條數(shù)COUNT(列名)計(jì)算一列值的個(gè)數(shù)SUM(列名)計(jì)算某一列值的總和，該列必須為數(shù)值類型AVG(列名)計(jì)算某一列值的平均值，該列必須為數(shù)值類型MAX(列名)計(jì)算某一列值的最大值，該列必須為數(shù)值類型MIN(列名)計(jì)算某一列值的最小值，該列必須為數(shù)值類型如無(wú)HAVING子句，上述函數(shù)完成對(duì)全表統(tǒng)計(jì)，否則作分組統(tǒng)計(jì)。

SQL數(shù)據(jù)查詢語(yǔ)句SQL數(shù)據(jù)查詢語(yǔ)句3）DISTINCT列名1[,列名2…]表示在最終結(jié)果表中，屬性相同的元組只保留一條。在書寫時(shí)，允許使用通配符“＊”、“？”。“＊”表示任意一字符串。“？”表示任意一個(gè)字符。“基本表或視圖”格式如下所示：表名1（或視圖名1）[別名1][,表名2(或視圖名2)[別名2]]…SQL數(shù)據(jù)查詢語(yǔ)句如果{<表達(dá)式>}由列名構(gòu)成，將實(shí)現(xiàn)關(guān)系投影運(yùn)算。如果FROM中指定的是一個(gè)表，WHERE的描述將實(shí)現(xiàn)選擇運(yùn)算。如果FROM中指定的是多個(gè)表，WHERE的描述中將包括連接運(yùn)算。SQL數(shù)據(jù)查詢語(yǔ)句二、對(duì)單一表查詢語(yǔ)句示例下列示例中使用的基本表的結(jié)構(gòu)假設(shè)為：學(xué)生（學(xué)生號(hào)，姓名，性別，出生年份，班級(jí)，寢室號(hào)）課程（課程號(hào)，課程名，先行課程名）成績(jī)（學(xué)生號(hào)，課程號(hào)，分?jǐn)?shù)）其中出生年份和分?jǐn)?shù)為整型，其他為字符類型SQL數(shù)據(jù)查詢語(yǔ)句例4.12求所有學(xué)生表數(shù)據(jù)。SELECT＊FROM學(xué)生例4.13求所有學(xué)生姓名的列表。SELECT姓名FROM學(xué)生例4.14如當(dāng)前系統(tǒng)設(shè)定的年份為YEAR1求顯示所有學(xué)生姓名、年齡。SELECT姓名，YEAR1-出生年份FROM學(xué)生SQL數(shù)據(jù)查詢語(yǔ)句例4.15求顯示學(xué)生人數(shù)和學(xué)生平均年齡。SELECTCOUNT(＊),AVG(YEAR1－出生年份)FROM學(xué)生例4.16求所有學(xué)生班級(jí)的列表。SELECTDISTINCT班級(jí)FROM學(xué)生因?yàn)槊總€(gè)學(xué)生班級(jí)均有許多學(xué)生，為在列表中不出現(xiàn)重復(fù)班級(jí)名，故使用了“DISTINCT”。SQL數(shù)據(jù)查詢語(yǔ)句例4.17顯示一個(gè)學(xué)生表,要求先按班級(jí)，在班級(jí)相同時(shí)按學(xué)生號(hào)排序。SELECT＊FROM學(xué)生ORDERBY班級(jí)，學(xué)生號(hào)例4.18求全體男生信息。SELECT＊FROM學(xué)生WHERE性別＝‘男’例4.19求1980年以前出生的女生姓名、性別、出生年份。SELECT姓名，性別，出生年份FROM學(xué)生WHERE性別＝‘女’AND出生年份<1980SQL數(shù)據(jù)查詢語(yǔ)句例4.20假設(shè)沒(méi)有安排寢室學(xué)生的寢室號(hào)為空值，求沒(méi)有安排寢室學(xué)生姓名。SELECT姓名FROM學(xué)生WHERE寢室號(hào)ISNULL涉及空值的謂詞的一般形式是：列名IS[NOT]NULL。在條件表達(dá)式中還經(jīng)常使用謂詞IN、BETWEEN、LIKE。SQL數(shù)據(jù)查詢語(yǔ)句例4.21求200101班和200103班的學(xué)生表。實(shí)現(xiàn)語(yǔ)句1：SELECT＊FROM學(xué)生

WHERE班級(jí)IN(‘200101’,‘200103’)實(shí)現(xiàn)語(yǔ)句2：SELECT＊FROM學(xué)生

WHERE班級(jí)=‘200101’OR班級(jí)=‘200103’謂詞IN實(shí)際是一系列邏輯關(guān)系詞“OR”的縮寫。也可用謂詞NOTIN表示不在某集合之中。SQL數(shù)據(jù)查詢語(yǔ)句例4.22求出生年份在1982至1985年間的學(xué)生姓名和出生年份。SELECT姓名,出生年份FROM學(xué)生WHERE出生年份BETWEEN1982AND1985SQL數(shù)據(jù)查詢語(yǔ)句例4.23找出所有2001級(jí)學(xué)生。SELECT＊FROM學(xué)生WHERE班級(jí)LIKE‘2001%’使用謂詞LIKE時(shí)，列名必須為各種字符串類型。可采用一些通配字符：＿（下橫線）表示任意一單個(gè)字符。％（百分號(hào)）表示任意長(zhǎng)度字符串。SQL數(shù)據(jù)查詢語(yǔ)句例4.23求選修課程超過(guò)了三門的學(xué)生號(hào)SELECT學(xué)生號(hào)FROM成績(jī)GROUPBY學(xué)生號(hào)HAVINGCOUNT(＊)>3以上查詢實(shí)現(xiàn)了關(guān)系運(yùn)算中投影與選擇兩種運(yùn)算。SQL數(shù)據(jù)查詢語(yǔ)句三、對(duì)兩個(gè)以上表的連接查詢例4.25求所有學(xué)生信息，包括他所學(xué)的課程的課程號(hào)和成績(jī)，并按課程號(hào)和成績(jī)排名。SELECT學(xué)生.＊,成績(jī).＊FROM學(xué)生,成績(jī)WHERE學(xué)生.學(xué)生號(hào)=成績(jī).學(xué)生號(hào)ORDERBY課程號(hào),分?jǐn)?shù)DESCSQL數(shù)據(jù)查詢語(yǔ)句例4.26求分?jǐn)?shù)為優(yōu)良（80分?jǐn)?shù)及以上）的所有成績(jī)組成的表，要求顯示內(nèi)容包括姓名、課程名和分?jǐn)?shù)。SELECT姓名,課程名,分?jǐn)?shù)FROM學(xué)生,成績(jī),課程

WHERE學(xué)生.學(xué)號(hào)＝成績(jī).學(xué)號(hào)

AND成績(jī).課程號(hào)＝課程.課程號(hào)AND分?jǐn)?shù)>=80SQL數(shù)據(jù)查詢語(yǔ)句___自連接例4.27求每門課程的先行課的先行課。SELECT表1.課程名，表2.先行課程名FROM課程表1，課程表2WHERE表1.課程號(hào)＝表2.課程號(hào)別名例4.28求既選修了課程號(hào)為C1，又選修了課程號(hào)為C2這兩門課的學(xué)生的學(xué)生號(hào)。SELECT表1.學(xué)生號(hào)FROM成績(jī)表1，成績(jī)表2WHERE表1.學(xué)生號(hào)＝表2.學(xué)生號(hào)AND表1.課號(hào)＝‘C1’AND表2.課號(hào)＝‘C2’SQL數(shù)據(jù)查詢語(yǔ)句___自連接SQL數(shù)據(jù)查詢語(yǔ)句__嵌套查詢

嵌套查詢亦稱為子查詢，它是指一個(gè)SELECT－FROM－WHERE查詢塊可以嵌入到另一個(gè)查詢塊之中的查詢。例4.29求選修了課程號(hào)為‘C1’的學(xué)生姓名。SELECT姓名FROM學(xué)生WHERE學(xué)號(hào)IN(SELECT學(xué)號(hào)FROM成績(jī)

WHERE課程號(hào)＝‘C1’)SQL數(shù)據(jù)查詢語(yǔ)句例4.30求選修了課程名為‘C語(yǔ)言’的學(xué)生姓名。SELECT姓名FROM學(xué)生WHERE學(xué)號(hào)IN

(

SELECT學(xué)號(hào)FROM成績(jī)

WHERE課程號(hào)IN(SELECT課程號(hào)FROM課程

WHERE課程名＝‘C語(yǔ)言’))SQL數(shù)據(jù)查詢語(yǔ)句例4.31由表4.3所示關(guān)系，求表成績(jī)1和成績(jī)2的兩個(gè)關(guān)系的交。SELECT＊FROM成績(jī)1WHERE學(xué)生號(hào)IN(SELECT學(xué)生號(hào)FROM成績(jī)2WHERE成績(jī)1.課程號(hào)=成績(jī)2.課程號(hào)

AND成績(jī)1.分?jǐn)?shù)=成績(jī)2.分?jǐn)?shù))SQL數(shù)據(jù)查詢語(yǔ)句例4.32由表4.3所示關(guān)系，求成績(jī)1－成績(jī)2兩個(gè)關(guān)系的差。SELECT＊FROM成績(jī)1WHERE學(xué)生號(hào)NOTIN(SELECT學(xué)生號(hào)FROM成績(jī)2WHERE成績(jī)1.課程號(hào)=成績(jī)2.課程號(hào)

AND成績(jī)1.分?jǐn)?shù)=成績(jī)2.分?jǐn)?shù)）SQL數(shù)據(jù)查詢語(yǔ)句兩個(gè)SELECT-FROM-WHERE查詢塊如果目標(biāo)列相同，可利用謂詞UNION構(gòu)成一個(gè)查詢，實(shí)現(xiàn)關(guān)系并運(yùn)算，其格式如下：SELECT目標(biāo)列1FROM表1WHERE條件表達(dá)式1UNIONSELECT目標(biāo)列2FROM表2WHERE條件表達(dá)式2SQL數(shù)據(jù)查詢語(yǔ)句目標(biāo)列1與目標(biāo)列

2名字不要求一樣，但列數(shù)對(duì)應(yīng)列類型和寬度必須一樣，結(jié)果中的列名按第一個(gè)查詢塊中列名查詢時(shí)將去掉重復(fù)元組。例4.33求營(yíng)業(yè)庫(kù)1和營(yíng)業(yè)庫(kù)2兩個(gè)關(guān)系的并。SELECT＊FROM營(yíng)業(yè)庫(kù)1UNIONSELECT＊FROM營(yíng)業(yè)庫(kù)2SQL數(shù)據(jù)查詢語(yǔ)句__關(guān)系除法

一般用于實(shí)現(xiàn)關(guān)系除法，需利用存在量詞EXISTS和NOTEXISTS。這類查詢稱為相關(guān)子查詢（CorrelatedSubquery）。相關(guān)子查詢中查詢條件依賴于外層查詢中的某個(gè)值，因此不能只處理一次，而要內(nèi)外交錯(cuò)反復(fù)求值。應(yīng)用EXISTS查詢語(yǔ)句定義見(jiàn)下例。例4.35求選修了課程號(hào)為‘C1’的學(xué)生姓名SELECT姓名FROM學(xué)生WHEREEXISTS(SELECT＊FROM成績(jī)

WHERE成績(jī).學(xué)號(hào)=學(xué)生.學(xué)號(hào)AND

課程號(hào)=‘C1’)SQL數(shù)據(jù)查詢語(yǔ)句__關(guān)系除法SQL數(shù)據(jù)查詢語(yǔ)句__關(guān)系除法在SQL中沒(méi)有全稱量詞（Forall），但是帶全稱量詞的謂詞都可轉(zhuǎn)換成等價(jià)的帶存在量詞的謂詞：

例4.36求選修了全部課程的學(xué)生姓名。SELECT姓名FROM學(xué)生WHERENOTEX1STS(SELECT＊FROM課程WHERENOTEX1STS(SELECT＊FROM成績(jī)

WHERE成績(jī).學(xué)號(hào)=學(xué)生.學(xué)號(hào)AND

成績(jī).課程號(hào)=課程.課程號(hào)))SQL數(shù)據(jù)查詢語(yǔ)句__關(guān)系除法更一般的關(guān)系除法問(wèn)題如下例。例4.37求至少選修了學(xué)生號(hào)為S1的學(xué)生所選修的全部課程的學(xué)生的學(xué)生號(hào)。本問(wèn)題含義是對(duì)成績(jī)表按學(xué)生號(hào)分組，如每組課程號(hào)包含了學(xué)生號(hào)為S1，所選修的全部課程的課程號(hào)則取該組學(xué)生號(hào)列入結(jié)果表。SQL數(shù)據(jù)查詢語(yǔ)句SQL沒(méi)有蘊(yùn)函(Implication)邏輯運(yùn)算。但是蘊(yùn)函邏輯運(yùn)算可作如下變換，

其意義是說(shuō)如p包含在q中，則一定不存在：“包含在p內(nèi)的元素”卻“不在q之中”的情況。上例也就可如下表述：求這樣一些學(xué)生學(xué)號(hào)，不存在這樣的情況，即S1選修了的課程CY，他卻沒(méi)有選修。SQL數(shù)據(jù)查詢語(yǔ)句如果用p表示謂詞“學(xué)生s1選修課程cy”，用q表示謂詞“學(xué)生Sx選修課程Cy”，則上述查詢可表示為：SELECTDISTINCT學(xué)生號(hào)FROM成績(jī)表1WHERENOTEXISTS(SELECT*FROM成績(jī)表2WHERE表2.學(xué)生號(hào)＝‘S1’ANDNOTEXISTS(SELECT*FROM成績(jī)表3WHERE表3.學(xué)生號(hào)＝表1.學(xué)生號(hào)AND

表3.課程號(hào)＝表2.課程號(hào)))許多數(shù)據(jù)庫(kù)產(chǎn)品對(duì)SQL查詢語(yǔ)句進(jìn)一步擴(kuò)展。以下結(jié)合SQLServer摘要介紹：1．輸出列更名運(yùn)算SELECT[DISTINCT][<別名1>]<表達(dá)式1>[AS<新列名1>][,<別名2>]<表達(dá)式2>[AS<新列名2>]…]FROM……“AS新列名”指定查詢結(jié)果中列的重新定義的標(biāo)題。SQL查詢語(yǔ)句的加強(qiáng)

2．輸出定向有許多應(yīng)用要求根據(jù)查詢語(yǔ)句產(chǎn)生一個(gè)新表并將查詢結(jié)果送到新表中等，其方法是在語(yǔ)句中增加子句：INTO<表名>SQL查詢語(yǔ)句的加強(qiáng)

3．外部連接實(shí)現(xiàn)外連接的方法是在FROM子句中增加有關(guān)連接的語(yǔ)句成分，格式為：<表名1><連接類別>OUTERJOIN<表名2>根據(jù)連接類別不同具體分為：SQL查詢語(yǔ)句的加強(qiáng)左外連接，格式為：<表名1>LEFTOUTERJOIN<表名2>，意義為可供選作輸出內(nèi)容的記錄除了滿足連接條件的記錄之外，還要加上那些左表（表名1）中不滿足連接條件的記錄。右外連接，格式為：<表名1>RIGHTOUTERJOIN<表名2>，意義為可供選作輸出內(nèi)容的記錄除了滿足連接條件的記錄之外，還要加上那些右表（表名2）中不滿足連接條件的記錄。全外連接，格式為：<表名1>OUTEROUTERJOIN<表名2>，意義為可供選作輸出內(nèi)容的記錄除了滿足連接條件的記錄之外，還要同時(shí)加上那些左表（表名1）中不滿足連接條件的記錄以及右表（表名2）中不滿足連接條件的記錄。SQL查詢語(yǔ)句的加強(qiáng)例4.38求根據(jù)表4.2生成一個(gè)新表：統(tǒng)計(jì)表，包括所有單價(jià)大于5元的記錄，并要求增加二列：金額、說(shuō)明，其中金額＝數(shù)量×單價(jià)，說(shuō)明要求10個(gè)字符寬，以空格填充。SELECT＊,數(shù)量＊單價(jià)AS金額,“”AS說(shuō)明

FROM營(yíng)業(yè)庫(kù)WHERE單價(jià)>5INTO統(tǒng)計(jì)表SQL查詢語(yǔ)句的加強(qiáng)例4.39求生成按課程成績(jī)的分析統(tǒng)計(jì)表，包括每門課平均分、最高分、最低分，存放在表TMP中。SELECT課程號(hào),AVG(分?jǐn)?shù))AS平均分,MAX(分?jǐn)?shù))AS最高分,MIN(分?jǐn)?shù))AS最低分

FROM成績(jī)

GROUPBY課程號(hào)

INTOTMPSQL查詢語(yǔ)句的加強(qiáng)

例4.40對(duì)于表4.2營(yíng)業(yè)庫(kù)，求生成統(tǒng)計(jì)表，要求按商品代碼分組，顯示每筆記錄，對(duì)每組記錄顯示之后求該組“數(shù)量”數(shù)據(jù)之和，并在其中品名欄內(nèi)標(biāo)注“小計(jì)”字樣。SQL查詢語(yǔ)句的加強(qiáng)

例4.41求生成按課程成績(jī)的分析表，包括所有學(xué)生姓名，班級(jí)，課程號(hào)，分?jǐn)?shù)。要求沒(méi)有成績(jī)的學(xué)生情況也能顯示。SELECT學(xué)生.姓名,學(xué)生.班級(jí),成績(jī).課程號(hào),成績(jī).分?jǐn)?shù)FROM學(xué)生LEFTOUTERJOIN成績(jī)第四章關(guān)系數(shù)據(jù)庫(kù)實(shí)現(xiàn)及應(yīng)用4.1關(guān)系代數(shù)4.2關(guān)系演算4.3SQL語(yǔ)言概貌4.4SQL數(shù)據(jù)定義功能4.5SQL數(shù)據(jù)查詢語(yǔ)句4.6SQL查詢語(yǔ)句的加強(qiáng)4.7視圖4.8SQL數(shù)據(jù)更新語(yǔ)句4.9SQL數(shù)據(jù)控制功能4.10嵌入式SQL4.11查詢優(yōu)化視圖和表不同之處在于，它并沒(méi)有真正地存儲(chǔ)數(shù)據(jù)，它所存取的數(shù)據(jù)必須依附于所關(guān)聯(lián)的數(shù)據(jù)表，它是一種虛的映射關(guān)系。

SQL建立視圖的語(yǔ)句格式：

CREATEVIEW視圖名[(字段名[,字段名]…]AS子查詢[WITHCHECKOPTION]視圖所用字段名可以與基本表中字段名不一致。視圖當(dāng)字段名不一致或子查詢中目標(biāo)列列名是函數(shù)、一般表達(dá)式或子查詢中目標(biāo)列有相同列名時(shí)，在視圖定義中必須指出視圖的各個(gè)字段名，也可以不列出，默認(rèn)與子查詢結(jié)果相同。視圖在子查詢中一般不能包括DISTINCT,INTO,ORDER等，不能涉及臨時(shí)表。WITHCHECKOPTION選項(xiàng)表示在通過(guò)視圖對(duì)基本表進(jìn)行插入和更新操作時(shí)必須滿足子查詢中WHERE語(yǔ)句中規(guī)定的條件。視圖視圖例4.42將學(xué)生表、成績(jī)表、課程表連接后取部分字段建立視圖。CREATEVIEW學(xué)生成績(jī)ASSELECT姓名,課名,分?jǐn)?shù)FROM學(xué)生,成績(jī),課程WHERE學(xué)生.學(xué)生號(hào)=成績(jī).學(xué)生號(hào)AND

成績(jī).課程號(hào)=課程.課程號(hào)視圖例4.43例4.39我們可改為視圖方式。CREATEVIEW成績(jī)統(tǒng)計(jì)(課程號(hào),平均分,最高分,最低分)ASSELECT課程號(hào),AVG(分?jǐn)?shù)),MAX(分?jǐn)?shù)),MIN(分?jǐn)?shù))FROM成績(jī)

GROUPBY課程號(hào)再查看成績(jī)統(tǒng)計(jì)表。使用如下語(yǔ)句：SELECT*FROM成績(jī)統(tǒng)計(jì)

刪除視圖語(yǔ)句格式為：

DROPVIEW視圖名一個(gè)基本表的刪除，由它導(dǎo)出的視圖將自動(dòng)刪除。當(dāng)一個(gè)視圖被刪去后，由它導(dǎo)出的其他視圖也將自動(dòng)刪除或變?yōu)椴豢梢谩Ｒ晥D不僅可用于查詢，還可借助視圖實(shí)現(xiàn)對(duì)基本表的插入、修改和刪除操作。視圖在關(guān)系優(yōu)化過(guò)程中，我們將一個(gè)實(shí)際數(shù)據(jù)改為多個(gè)表存儲(chǔ)，使用戶面對(duì)的是復(fù)雜的數(shù)據(jù)結(jié)構(gòu)，而視圖可不改變數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)同時(shí)又使用戶面對(duì)的是簡(jiǎn)單的數(shù)據(jù)結(jié)構(gòu)，視圖將其中連接操作對(duì)用戶隱藏起來(lái)，就使用戶對(duì)數(shù)據(jù)使用大大簡(jiǎn)化，更容易操作。視圖在一些數(shù)據(jù)庫(kù)系統(tǒng)中，利用視圖可建立兩個(gè)不同數(shù)據(jù)庫(kù)系統(tǒng)的聯(lián)系和通信。例如VFP中，可十分容易地建立ORACLE、ACCESS、SQLSERVER等系統(tǒng)中的表的視圖，稱之為“遠(yuǎn)程視圖”。VFP的程序可對(duì)這些視圖如同基本表一樣作查詢、錄入、修改、刪除等操作，并借之實(shí)現(xiàn)對(duì)相關(guān)數(shù)據(jù)庫(kù)系統(tǒng)中表的操作，使得程序設(shè)計(jì)大大簡(jiǎn)化。視圖視圖視圖的作用：（1）視圖對(duì)于數(shù)據(jù)庫(kù)的重構(gòu)提供了一定程度的邏輯獨(dú)立性（2）簡(jiǎn)化用戶觀點(diǎn)，隱藏了表之間的連接（3）方便用戶，使用戶從不同角度看待數(shù)據(jù)（4）提供數(shù)據(jù)安全功能（5）在一些數(shù)據(jù)庫(kù)系統(tǒng)中，利用視圖可建立兩個(gè)不同數(shù)據(jù)庫(kù)系統(tǒng)的聯(lián)系和通信第四章關(guān)系數(shù)據(jù)庫(kù)實(shí)現(xiàn)及應(yīng)用4.1關(guān)系代數(shù)4.2關(guān)系演算4.3SQL語(yǔ)言概貌4.4SQL數(shù)據(jù)定義功能4.5SQL數(shù)據(jù)查詢語(yǔ)句4.6SQL查詢語(yǔ)句的加強(qiáng)4.7視圖4.8SQL數(shù)據(jù)更新語(yǔ)句4.9SQL數(shù)據(jù)控制功能4.10嵌入式SQL4.11查詢優(yōu)化一、修改(UPDATE)語(yǔ)句格式：UPDATE表名SET字段=表達(dá)式

[,字段=表達(dá)式]…[WHERE條件表達(dá)式]例4.44將營(yíng)業(yè)庫(kù)中所有單價(jià)高于5元的商品單價(jià)減少10%。UPDATE營(yíng)業(yè)庫(kù)SET單價(jià)=單價(jià)＊0.9WHERE單價(jià)>5SQL數(shù)據(jù)更新語(yǔ)句SQL數(shù)據(jù)更新語(yǔ)句二、刪除（DELETE）語(yǔ)句格式：DELETEFROM表名

[WHERE條件表達(dá)式]

例4.45清空成績(jī)表。

DELETEFROM成績(jī)例4.46清空89級(jí)學(xué)生成績(jī)記錄。DELETEFROM成績(jī)WHERE學(xué)生號(hào)IN(SELECT學(xué)生號(hào)FROM學(xué)生WHERE班級(jí)LIKE“1989%”)SQL數(shù)據(jù)更新語(yǔ)句三、插入(INSERT)語(yǔ)句格式1：INSERTINTO表名[(字段名[,字段名)…)]VALUES(表達(dá)式[,表達(dá)式]…)格式2：INSERTINTO表名[(字段名[,字段名]…)]

子查詢SQL數(shù)據(jù)更新語(yǔ)句例4.47錄入一個(gè)學(xué)生記錄到學(xué)生表中，假設(shè)已將其學(xué)號(hào)、姓名、性別、出生年份和班級(jí)數(shù)據(jù)分別賦值給變量xh1、xm1、xbl、csnf1、bj1。INSERTINTO學(xué)生(學(xué)號(hào)，姓名，性別，出生年份，班級(jí))VALUES(xh1,xm1,xbl,csnf1,bj1)SQL數(shù)據(jù)更新語(yǔ)句例4.48欲由表4.1的營(yíng)業(yè)庫(kù)1和營(yíng)業(yè)庫(kù)2生成表4.2的營(yíng)業(yè)庫(kù)表且增加金額一欄，可先基于營(yíng)業(yè)庫(kù)1生成一個(gè)新表，在新表中增加“金額”字段，再將營(yíng)業(yè)庫(kù)2的數(shù)據(jù)添加到生成的新表中。用如下兩條語(yǔ)句完成。SELECT＊，數(shù)量＊單價(jià)AS金額

FROM營(yíng)業(yè)庫(kù)1INTO營(yíng)業(yè)庫(kù)INSERTINTO營(yíng)業(yè)庫(kù)SELECT＊，數(shù)量＊單價(jià)FROM營(yíng)業(yè)庫(kù)2SQL數(shù)據(jù)更新語(yǔ)句四、視圖的更新若一個(gè)視圖是從單個(gè)基本表導(dǎo)出，且只是去掉了基本表的某些行和某些列（不包括候選關(guān)鍵字），我們稱這類視圖為行列子集視圖。例如：建立女學(xué)生視圖S_V1。CREATEVIEWS_V1ASSELECT學(xué)生號(hào)，性別FROM學(xué)生WHERE性別=‘女’SQL數(shù)據(jù)更新語(yǔ)句對(duì)于行列子集視圖允許通過(guò)它更新基本表。對(duì)視圖的更新，實(shí)質(zhì)是通過(guò)視圖實(shí)現(xiàn)對(duì)基本表的更新。但如果視圖中某些字段對(duì)應(yīng)的是對(duì)基本表按某種表達(dá)式運(yùn)算的結(jié)果，或者視圖是基于多表建立的，將不能通過(guò)這樣的視圖實(shí)現(xiàn)對(duì)基本表的更新。SQL數(shù)據(jù)更新語(yǔ)句例如刪除女學(xué)生視圖s_v1中學(xué)號(hào)為200101的學(xué)生。DELETEFROMs_v1WHERE學(xué)號(hào)=‘200101’；轉(zhuǎn)換為對(duì)基本表的更新：DELETEFROM學(xué)生WHERE學(xué)生號(hào)=‘200101’AND性別='女'；SQL數(shù)據(jù)更新語(yǔ)句視圖更新的限制：目前各DBMS一般都只允許對(duì)行列子集視圖進(jìn)行更新，并有以下限制：（1）若視圖是由兩個(gè)以上基本表導(dǎo)出的，則此視圖不允許更新。（2）若視圖的字段來(lái)自表達(dá)式或常數(shù)，則不允許對(duì)此視圖執(zhí)行INSERT和UPDATE操作，但允許執(zhí)行DELETE操作。（3）若視圖的字段來(lái)自集函數(shù)，則此視圖不允許更新。（4）若視圖定義中含有GROUPBY子句，則此視圖不允許更新。（5）若視圖定義中含有DISTINCT任選項(xiàng)，則此視圖不允許更新。（6）若視圖定義中有嵌套查詢，并且內(nèi)層查詢的FROM子句中的表是相同的，則此視圖不允許更新。（7）一個(gè)不允許更新的視圖所導(dǎo)出的視圖不允許更新。第四章關(guān)系數(shù)據(jù)庫(kù)實(shí)現(xiàn)及應(yīng)用4.1關(guān)系代數(shù)4.2關(guān)系演算4.3SQL語(yǔ)言概貌4.4SQL數(shù)據(jù)定義功能4.5SQL數(shù)據(jù)查詢語(yǔ)句4.6SQL查詢語(yǔ)句的加強(qiáng)4.7視圖4.8SQL數(shù)據(jù)更新語(yǔ)句4.9SQL數(shù)據(jù)控制功能

4.10嵌入式SQL4.11查詢優(yōu)化SQL數(shù)據(jù)控制功能SQL具有授權(quán)語(yǔ)句，實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)使用的控制。其語(yǔ)句格式為：GRANT權(quán)力[,權(quán)力]…[ON對(duì)象類型對(duì)象名]TO用戶[,用戶]…[WITHGRANTOPTION][IDENTIFIEDBY口令]SQL數(shù)據(jù)控制功能其中，所授的權(quán)力可分為兩類：1．進(jìn)入數(shù)據(jù)庫(kù)系統(tǒng)權(quán)力2．使用他人數(shù)據(jù)的權(quán)力。1．進(jìn)入數(shù)據(jù)庫(kù)系統(tǒng)權(quán)力，建立自己的數(shù)據(jù)權(quán)力。這些權(quán)力由系統(tǒng)管理員授予。包括：

CONNECT連接到數(shù)據(jù)庫(kù)的權(quán)力

CREATETAB建立數(shù)據(jù)庫(kù)的權(quán)力或在數(shù)據(jù)庫(kù)中建立表的權(quán)力。DBA，系統(tǒng)管理員的權(quán)力。SQL數(shù)據(jù)控制功能例4.49授予用戶USER1進(jìn)入數(shù)據(jù)庫(kù)系統(tǒng)權(quán)力。GRANTCONNECTTOUSER1例4.50將數(shù)據(jù)庫(kù)學(xué)生系統(tǒng)中建立表的權(quán)力授予用戶USER1。GRANTCREATETABONDATABASE學(xué)生系統(tǒng)TOUSER1SQL數(shù)據(jù)控制功能2．使用他人數(shù)據(jù)的權(quán)力。一般由數(shù)據(jù)表的擁有者授予，這些權(quán)力包括：

SELECT 查詢權(quán)限

INSERT 插入新記錄權(quán)限

DELETE 刪除記錄權(quán)限

UPDATE(屬性名[,屬性名]…) 對(duì)有關(guān)列修改權(quán)限

ALTER 修改表結(jié)構(gòu)權(quán)限

INDEX 建立索引權(quán)限

ALL 以上所有權(quán)限SQL數(shù)據(jù)控制功能SQL數(shù)據(jù)控制功能例4.51設(shè)成績(jī)表由USER1所建，則USER1可發(fā)出命令將對(duì)成績(jī)表查詢、修改的權(quán)力授予USER2。GRANT