數(shù)據(jù)庫(kù)系統(tǒng)原理第4章.ppt

1,第4章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL,主要內(nèi)容 SQL概述 SQL的數(shù)據(jù)定義 SQL的數(shù)據(jù)查詢 數(shù)據(jù)更新,2,本章主要概念,(1) SQL的數(shù)據(jù)定義：SQL模式、基本表和索引的創(chuàng)建和撤銷。 (2) SQL的數(shù)據(jù)更新：插入、刪除和修改語(yǔ)句。 (3) SQL的數(shù)據(jù)查詢；SELECT語(yǔ)句的句法，SELECT語(yǔ)句的三種形式及各種限定，基本表的聯(lián)接操作。,3,結(jié)構(gòu)化查詢語(yǔ)言SQL（Structured Query Language）是關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言，對(duì)關(guān)系模型的發(fā)展和商用DBMS的研制起著重要的作用。SQL語(yǔ)言是介乎于關(guān)系代數(shù)和元組演算之間的一種語(yǔ)言。本章詳細(xì)介紹SQL的核心部分內(nèi)容： 數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)更新。,4,4.1 SQL概述 p66,主要內(nèi)容 SQL的產(chǎn)生與發(fā)展 SQL的組成及特點(diǎn),5,1、SQL的產(chǎn)生與發(fā)展,1970年，美國(guó)IBM研究中心的E.F.Codd連續(xù)發(fā)表多篇論文，提出關(guān)系模型。1972年，IBM公司開(kāi)始研制實(shí)驗(yàn)型關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)SYSTEM R，配制的查詢語(yǔ)言稱為SQUARE (Specifying Queries As Relational Expression )語(yǔ)言，在語(yǔ)言中使用了較多的數(shù)學(xué)符號(hào)。1974年，Boyce和Chamberlin把SQUARE修改為SEQUEL (Structured English QUEry Language )語(yǔ)言。后來(lái)SEQUEL簡(jiǎn)稱為SQL(Structured Query Language )，即“結(jié)構(gòu)式查詢語(yǔ)言”，SQL的發(fā)音仍為“sequel”?，F(xiàn)在SQL已經(jīng)成為一個(gè)標(biāo)準(zhǔn) 。 SQL有兩個(gè)標(biāo)準(zhǔn)：ANSI SQL和1992年通過(guò)的修改版本SQL-92（簡(jiǎn)稱SQL2）。還有一個(gè)新的標(biāo)準(zhǔn)SQL3，它擴(kuò)充了SQL2，引入了遞歸、觸發(fā)器和對(duì)象等概念和機(jī)制。,6,2、SQL的組成及特點(diǎn),SQL語(yǔ)言從功能上可以分為四部分：數(shù)據(jù)查詢（Data Query）、數(shù)據(jù)操縱（Data Manipulation）、數(shù)據(jù)定義（Data Definition）和數(shù)據(jù)控制（Data Control）。 核心SQL主要有四個(gè)部分: (1) 數(shù)據(jù)定義語(yǔ)言，即SQL DDL，用于定義SQL模式、基本表、視圖、索引等結(jié)構(gòu)。 (2) 數(shù)據(jù)操縱語(yǔ)言，即SQL DML。數(shù)據(jù)操縱分成數(shù)據(jù)查詢和數(shù)據(jù)更新兩類。其中數(shù)據(jù)更新又分成插入、刪除和修改三種操作。 (3) 數(shù)據(jù)控制語(yǔ)言，即SQL DCL，這一部分包括對(duì)基本表和視圖的授 權(quán)、完整性規(guī)則的描述、事務(wù)控制等內(nèi)容。 (4) 嵌入式SQL語(yǔ)言的使用規(guī)定。這一部分內(nèi)容涉及到SQL語(yǔ)句嵌入在宿主語(yǔ)言程序中的規(guī)則。,7,3、SQL的核心動(dòng)詞（不區(qū)分大小寫）, SQL有兩種使用方式：一是聯(lián)機(jī)交互使用，另一種是嵌入到某種高級(jí)語(yǔ)言程序設(shè)計(jì)語(yǔ)言中去使用。 SQL是一種第四代語(yǔ)言（4GL），用戶只需提出“干什么”，無(wú)需具體指明“怎么干”，像存取路徑選擇和具體處理操作等，均有系統(tǒng)自動(dòng)完成。,8,4.2 SQL的數(shù)據(jù)定義 P68,主要內(nèi)容 數(shù)據(jù)庫(kù)的創(chuàng)建與撤銷 定義、修改與刪除基本表,9,4.2.1 SQL數(shù)據(jù)庫(kù)的體系結(jié)構(gòu),SQL語(yǔ)言可以對(duì)兩種基本數(shù)據(jù)結(jié)構(gòu)進(jìn)行操作：“表”和“視圖” 視圖是從一個(gè)或幾個(gè)基本表導(dǎo)出的表。它本身不獨(dú)立存儲(chǔ)在數(shù)據(jù)庫(kù)中，是一個(gè)虛表。 基本表是本身獨(dú)立存在的表，一個(gè)（或多個(gè)）基本表對(duì)應(yīng)一個(gè)存儲(chǔ)文件，一個(gè)表可以帶若干索引，索引也存放在存儲(chǔ)文件中。,10,SQL的數(shù)據(jù)定義功能包括數(shù)據(jù)庫(kù)定義、表定義、視圖和索引的定義。,11,例： 定義一個(gè)教務(wù)管理數(shù)據(jù)庫(kù)JWGL，它包含3個(gè)表： 學(xué)生表：S(SNO,SNAME,SEX,AGE,SDEPT) 課程表：C(CNO,CNAME,CPNO,CCREDIT) 學(xué)生選課表：SC(SNO,CNO,GRADE),學(xué)生表實(shí)例,學(xué)生表選課表 實(shí)例,12,課程表實(shí)例,13,4.2.2 數(shù)據(jù)庫(kù)的創(chuàng)建與撤銷（增加）,1、數(shù)據(jù)庫(kù)的創(chuàng)建 在SQL中，一個(gè)數(shù)據(jù)庫(kù)模式被定義為基本表的集合。 對(duì)數(shù)據(jù)庫(kù)的創(chuàng)建可用CREATE語(yǔ)句實(shí)現(xiàn)，其句法如下： CREATE DATABASE AUTHORIZATION 其中AUTHORIZATION 是指該用戶獲得授予創(chuàng)建和使用數(shù)據(jù)庫(kù)的權(quán)限。 例1，下面語(yǔ)句定義了教務(wù)管理數(shù)據(jù)庫(kù)的模式： CREATE DATABASE JWGL AUTHORIZATION 張銘 創(chuàng)建的數(shù)據(jù)庫(kù)名為JWGL，擁有者為張銘。,14,2、數(shù)據(jù)庫(kù)的撤消 當(dāng)一個(gè)數(shù)據(jù)庫(kù)及其所屬的基本表、視圖等元素都不需要時(shí)，可以用DROP語(yǔ)句撤消這個(gè)數(shù)據(jù)庫(kù)。DROP語(yǔ)句的句法如下： DROP DATABASE CASCADE | RESTRICT 撤消的方式有以下兩種: CASCADE（連鎖式）方式：執(zhí)行DROP語(yǔ)句時(shí)，把數(shù)據(jù)庫(kù)及其下屬的基本表、視圖、索引等所有元素全部撤消。 RESTRICT（約束式）方式：執(zhí)行DROP語(yǔ)句時(shí)，只有當(dāng)數(shù)據(jù)庫(kù)中沒(méi)有任何下屬元素時(shí)，才能撤消數(shù)據(jù)庫(kù)，否則拒絕執(zhí)行DROP語(yǔ)句。 例2，要撤消數(shù)據(jù)庫(kù)JWGL及其下屬所有的元素，可用下列語(yǔ)句實(shí)現(xiàn)： DROP DATABASE JWGL CASCADE,15,4.2.3 基本表的定義與刪改P68,數(shù)據(jù)類型 一個(gè)屬性選用哪種數(shù)據(jù)類型要根據(jù)實(shí)際情況來(lái)決定，一般要從兩個(gè)方面來(lái)考慮： 一是取值范圍， 二是要做哪些運(yùn)算。,16,1、定義基本表 定義基本表，就是創(chuàng)建基本表的結(jié)構(gòu)。其一般格式為： CREATE TABLE （ 列級(jí)完整性約束條件 ， 列級(jí)完整性約束條件 ，）； 建表的同時(shí)通常還可以定義與該表有關(guān)的完整性約束條件，這些完整性約束條件被存入系統(tǒng)的數(shù)據(jù)字典中，當(dāng)用戶操作表中數(shù)據(jù)時(shí)由RDBMS自動(dòng)檢查該操作是否違背這些完整性約束條件。 注: 可選項(xiàng), 可選項(xiàng), |-在多個(gè)短語(yǔ)中選一個(gè)。,17,其中： 列級(jí)完整性約束條件： NOT NULL:限制列取值非空 DEFAULT:給定列的默認(rèn)值 UNIQUE:限制列取值不重復(fù) CHECK:限制列的取值范圍 PRIMARY KEY:指定本列為主碼 FOREIGN KEY:定義本列為引用其他表的外碼，形式如下 FOREIGN KEY () REFERENCES（） 表級(jí)完整性約束性條件：除NOT NULL和DEFAULT不能定義外，其他都可定義,18,例3 建立一個(gè)“學(xué)生”表S。 CREATE TABLE S (SNO CHAR(9) PRIMARY KEY, /* 列級(jí)完整性約束條件，SNO 是主碼 */ SNAME CHAR(20) UNIQUE, /* SNAME取唯一值 */ SEX CHAR(1), AGE SMALLINT, SDEPT CHAR(20) ) 系統(tǒng)執(zhí)行上面的CREATE TABLE語(yǔ)句后，就在數(shù)據(jù)庫(kù)中建立一個(gè)新的空的“學(xué)生”表S，并將有關(guān)“學(xué)生”表的定義及有關(guān)約束條件存放在數(shù)據(jù)字典中。,19,例4 建立 “課程”表C。 CREATE TABLE C (CNO CHAR(2) PRIMARY KEY, /* 列級(jí)完整性約束條件，SNO 是主碼 */ CNAME CHAR(20) UNIQUE, /* CNAME取唯一值 */ CPNO CHAR(2), CCREDIT SMALLINT ),20,例5 建立學(xué)生選課表SC。 CREATE TABLE SC (SNO CHAR(9), CNO CHAR(2), GRADE SMALLINT, PRIMARY KEY(SNO,CNO), /* 主碼由兩個(gè)屬性構(gòu)成，必須作 為表級(jí)完整性進(jìn)行定義 */ FOREIGN KEY(SNO) REFERENCES S(SNO), /* 表級(jí)完整性約束條件，SNO是外碼，被參照表是S */ FOREIGN KEY(CNO) REFERENCES C(CNO) /* 表級(jí)完整性約束條件，CNO是外碼，被參照表是C */ ),21,2、 修改基本表 SQL語(yǔ)言用ALTER TABLE語(yǔ)句修改基本結(jié)構(gòu)表，其一般格式為： ALTER TABLE ADD COLUMN 完整性約束 | DROP COLUMN | ALTER COLUMN ； | ADD PRIMARY KEY（列名，n） | ADD FOREIGN KEY（列名）REFERENCES 表名列名 其中是要修改的基本表。 ADD子句用于增加新列和新的完整性約束條件； DROP子句用于刪除指定的完整性約束條件； ALTER COLUMN子句用于修改原有的列定義。包括修改列名和數(shù)據(jù)類型。,22,例6 向S表增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類型為日期型。 ALTER TABLE S ADD S_ENTRANCE DATE; 不論基本表中原來(lái)是否已有數(shù)據(jù)，新增加的列一律為空值。,23,3、刪除基本表 用DROP TABLE語(yǔ)句刪除基本表。其一般格式為： DROP TABLE ， RESTRICT | CASCADE； 若選擇RESTRICT：則該表的刪除是有限制條件的。 欲刪除的基本表不能被其他表的約束所引用（如CHECK，F(xiàn)OREIGN KEY等約束），不能有視圖，不能有觸發(fā)器（trigger），不能有存儲(chǔ)過(guò)程或函數(shù)等。如果存在這些依賴該表的對(duì)象，則此表不能被刪除。 若選擇CASCADE：則該表的刪除沒(méi)有限制條件。在刪除基本表的同時(shí)，相關(guān)的依賴對(duì)象，例如視圖，都將被一起刪除。 缺省情況是RESTRICT。,24,例 刪除S表。 DROP TABLE S CASCADE,25,4.3 SQL的數(shù)據(jù)查詢 P71,主要內(nèi)容 SELECT語(yǔ)句格式 單表查詢 聚集函數(shù) 數(shù)據(jù)分組 多表查詢 集合操作,26,SELECT語(yǔ)句格式,SELECTFROMWHERE句型 在關(guān)系代數(shù)中最常用的式子是下列表達(dá)式： A1,An(F(R1Rm) 這里R1、Rm為關(guān)系，F(xiàn)是公式，A1、An為屬性。,27,針對(duì)上述表達(dá)式，SQL為此設(shè)計(jì)了SELECTFROMWHERE句型： SELECT A1，An FROM R1，Rm WHERE F 這個(gè)句型是從關(guān)系代數(shù)表達(dá)式演變來(lái)的，但WHERE子句中的條件表達(dá)式F要比關(guān)系代數(shù)中公式更靈活。,28,SELECT語(yǔ)句的一般格式： SELECT INTO FROM WHERE GROUP BY HAVING ORDER BY ASC|DESC ， ,主語(yǔ)句SELECT-FROM-WHERE的含義是： 根據(jù)WHERE子句的條件表達(dá)式，從FROM子句指定的基本表或視圖中找出滿足條件的元組，再按SELECT子句中的目標(biāo)列表達(dá)式，選出元組中的屬性值形成結(jié)果表。 SELECT子句和FROM子句是必需的。,29,整個(gè)語(yǔ)句的執(zhí)行過(guò)程如下： (1) 讀取FROM子句中基本表、視圖的數(shù)據(jù)，執(zhí)行笛卡爾積操作。 (2) 選取滿足WHERE子句中給出的條件表達(dá)式的元組。 (3) 按GROUP子句中指定列的值分組，同時(shí)提取滿足HAVING子句中組條件表達(dá)式的那些組。 (4) 按SELECT子句中給出的列名或列表達(dá)式求值輸出。 (5) ORDER子句對(duì)輸出的目標(biāo)表進(jìn)行排序，按附加說(shuō)明ASC升序排列，或按DESC降序排列。,30,4.3.1 簡(jiǎn)單查詢 P71,查詢表中的若干列 查詢指定列 在很多情況下，用戶只對(duì)表中的一部分屬性列感興趣，這時(shí)可以通過(guò)在SELECT子句的中指定要查詢的屬性列。 例7： 查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名。 SELECT SNO,SNAME FROM S 該語(yǔ)句的執(zhí)行過(guò)程可以是這樣的：從S表中取出一個(gè)元組，再取出該元組在屬性SNO和SNAME上的值，形成一個(gè)新的元組作為輸出。對(duì)S表中的所有元組做相同的處理，最后形成一個(gè)結(jié)果關(guān)系作為輸出。顯示如第四章編輯圖,31, 查詢?nèi)苛?將表中的所有屬性列都選出來(lái)，可以有兩種方法。一種方法就是在SELECT關(guān)鍵字后面列出所有列名；另一種方法是如果列的顯示順序與其在基表中的順序相同，也可以簡(jiǎn)單地將指定為“*”。 例8： 查詢?nèi)w學(xué)生的詳細(xì)記錄。 SELECT * FROM S 等價(jià)于： SELECT SNO,SNAME,SEX,AGE,SDEPT FROM S,32,選擇表中的若干元組 消除取值重復(fù)的行 兩個(gè)本來(lái)并不完全相同的元組，投影到指定的某些列上后，可能變成相同的行了，可以用DISTINCT取消它們。 例9： 查詢選修了課程的學(xué)生學(xué)號(hào)。 SELECT SNO FROM SC 該查詢結(jié)果里包含了許多重復(fù)的行。如果想去掉結(jié)果表中的重復(fù)行，必須指定DISTINCT關(guān)鍵詞： SELECT DISTINCT SNO FROM SC,33, 查詢滿足條件的元組 查詢滿足指定條件的元組可以通過(guò)WHERE子句實(shí)現(xiàn)。,例10： 查詢計(jì)算機(jī)科學(xué)系全體學(xué)生的名單。 SELECT SNAME FROM S WHERE SDEPT=CS,34,范圍查詢 謂詞BETWEENAND和NOT BETWEENAND可以用來(lái)查找屬性值在（或不在）指定范圍內(nèi)的元組，其中BETWEEN后是范圍的下限（即低值），AND后是范圍的上限（即高值）。 例11： 查詢年齡在2023歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。 SELECT SNAME,SDEPT,AGE FROM S WHERE AGE BETWEEN 20 AND 23 與BETWEENAND相對(duì)的謂詞是NOT BETWEENAND。 例12： 查詢年齡不在2023歲之間的學(xué)生姓名、系別和年齡。 SELECT SNAME,SDEPT,AGE FROM S WHERE AGE NOT BETWEEN 20 AND 23,35,確定集合 謂詞IN可以用來(lái)查找屬性值屬于指定集合的元組。 例13： 查詢計(jì)算機(jī)科學(xué)系（CS）、數(shù)學(xué)系（MA）和信息系（IS）學(xué)生的姓名和性別。 SELECT SNAME,SEX FROM S WHERE SDEPT IN(CS,MA,IS) 與IN相對(duì)的謂詞是NOT IN，用于查找屬性值不屬于指定集合的元組。 例14： 查詢既不是計(jì)算機(jī)科學(xué)系、數(shù)學(xué)系，也不是信息系的學(xué)生的姓名和性別。 SELECT SNAME,SEX FROM S WHERE SDEPT NOT IN(CS,MA,IS),36,字符匹配謂詞 LIKE可以用來(lái)進(jìn)行字符串的匹配。其一般語(yǔ)法格式如下： NOT LIKE ESCAPE 其含義是查找指定的屬性列值與相匹配的元組。 可以是一個(gè)完整的字符串，也可以含有通配符%和_。其中： %（百分號(hào)）代表任意長(zhǎng)度（長(zhǎng)度可以為0）的字符串。例如a%b表示以a開(kāi)頭，以b結(jié)尾的任意長(zhǎng)度的字符串。如acb，addgb，ab等都滿足該匹配串。 _（下劃線）代表任意單個(gè)字符。例如a_b表示以a開(kāi)頭，以b結(jié)尾的長(zhǎng)度為3的任意字符串。如acb，afb等都滿足該匹配串。,37,例15： 查詢學(xué)號(hào)為200915121的學(xué)生的詳細(xì)情況。 SELECT * FROM S WHERE SNO LIKE 200915121 等價(jià)于： SELECT * FROM S WHERE SNO=200915121 如果LIKE后面的匹配串中不含通配符，則可以用=（等于）運(yùn)算符取代LIKE謂詞，用!=或（不等于）運(yùn)算符取代NOT LIKE謂詞。 例16： 查詢所有姓劉的學(xué)生的姓名、學(xué)號(hào)和性別。 SELECT SNAME,SNO,SEX FROM S WHERE SNAME LIKE 劉%,38,涉及空值的查詢例 例17： 某些學(xué)生選修課程后沒(méi)有參加考試，所以有選課記錄，但沒(méi)有考試成績(jī)。查詢?nèi)鄙俪煽?jī)的學(xué)生的學(xué)號(hào)和相應(yīng)的課程號(hào)。 SELECT SNO,CNO FROM SC WHERE GRADE IS NULL /* 分?jǐn)?shù)GRADE是空值 */,39,多重條件查詢 可用邏輯運(yùn)算符AND和OR來(lái)聯(lián)結(jié)多個(gè)查詢條件。AND的優(yōu)先級(jí)高于OR，但可以用括號(hào)改變優(yōu)先級(jí)。 例18：查詢計(jì)算機(jī)科學(xué)系年齡在20歲以下的學(xué)生姓名。 SELECT SNAME FROM S WHERE SDEPT=CS AND AGE20,40,ORDER BY子句 用戶可以用ORDER BY子句對(duì)查詢結(jié)果按照一個(gè)或多個(gè)屬性列的升序（ASC）或降序（DESC）排列，缺省值為升序。 例19 查詢選修了3號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢結(jié)果按分?jǐn)?shù)的降序排列。 SELECT SNO,GRADE FROM SC WHERE CNO=3 ORDER BY GRADE DESC 對(duì)于空值，若按升序排列，含空值的元組將在最后顯示。若按降序排列，空值的元組將在最前面顯示。,41,以上查詢針對(duì)的是一個(gè)表的查詢，若一個(gè)查詢涉及到兩個(gè)或兩個(gè)以上的表，則稱之為連接查詢。 連接查詢包括內(nèi)連接、外連接和交叉查詢。 在多表查詢中，如果要引用不同關(guān)系中的同名屬性，則需要在屬性名前加關(guān)系名，即用“關(guān)系名.屬性名”的形式表示，以便區(qū)分。,4.3.2 連接查詢 P75,42,連接查詢 內(nèi)連接查詢 若兩個(gè)表的相關(guān)字段滿足連接條件，則從這兩個(gè)表中提取數(shù)據(jù)并組合成新的記錄。 基本格式： FORM 表1 INNER JOIN 表2 ON ,43,連接查詢的JOIN子句中用來(lái)連接兩個(gè)表的條件稱為連接條件或連接謂詞，其一般格式為： . . 其中比較運(yùn)算符主要有：=、=、）等。 當(dāng)比較符為等號(hào)（=）時(shí)，稱為等值連接，使用其他運(yùn)算符的連接稱為非等值連接。,44,例20： 查詢每個(gè)學(xué)生及其選修課程的情況。 學(xué)生情況存放在S表中，學(xué)生選課情況存放在SC表中，所以本查詢實(shí)際上涉及S與SC兩個(gè)表。這兩個(gè)表之間的聯(lián)系是通過(guò)公共屬性SNO實(shí)現(xiàn)的。 SELECT S.*,SC.* FROM S JOIN SC ON S.SNO=SC.SNO /* 將S與SC中同一學(xué)生的元組連接起來(lái) */,45,執(zhí)行步驟：首先在表S中找到第一個(gè)元組，然后從頭開(kāi)始掃描SC表，逐一查找與S第一個(gè)元組的SNO相等的SC元組，找到后就將S中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。SC全部查找完后，再找S中第二個(gè)元組，然后再?gòu)念^開(kāi)始掃描SC，逐一查找滿足連接條件的元組，找到后就將S中的第二個(gè)元組與該元組拼接起來(lái)，再形成結(jié)果表中一個(gè)元組。重復(fù)上述操作，直到S中的全部元組都處理完畢為止。這就是循環(huán)嵌套算法的基本思想 。,46,(2) 自身連接 連接操作不僅可以在兩個(gè)表之間進(jìn)行，也可以是一個(gè)表與其自己進(jìn)行連接，稱為表的自身連接。,47,例21： 查詢每一門課的間接先修課（即先修課的先修課）。 在C表中，只有每門課的直接先修課信息，而沒(méi)有先修課的先修課。要得到這個(gè)信息，必須先對(duì)一門課找到其先修課，再按此先修課的課程號(hào)，查找它的先修課程。這就將C表與其自身連接。為此，要為C表取兩個(gè)別名，一個(gè)是FIRST，另一個(gè)是SECOND。完成該查詢的SQL語(yǔ)句為 SELECT FIRST.CNO,SECOND.CPNO FROM C FIRST,C SECOND WHERE FIRST.CPNO=SECOND.CNO,48,(3) 外連接 如果想輸出一些不滿足連接條件的元組的信息，只能使用外連接。 外連接是只限制一張表中的數(shù)據(jù)必須滿足連接條件，而另一張表中的數(shù)據(jù)可以不滿足連接條件。 一般格式： FROM 表1 LEFT|RIGHT OUTER JOIN 表2 ON 其中： LEFT OUTER稱為左外連接，其含義是：限制表2中的數(shù)據(jù)必須滿足連接條件，而不管表1中的數(shù)據(jù)是否滿足連接條件，均輸出表1中的內(nèi)容； RIGHT OUTER 稱為右外連接，其含義是：限制表1中的數(shù)據(jù)必須滿足連接條件，而不管表2中的數(shù)據(jù)是否滿足連接條件，均輸出表2中的內(nèi)容；,49,有時(shí)想以S表為主體列出每個(gè)學(xué)生的基本情況及其選課情況。若某個(gè)學(xué)生沒(méi)有選課，仍把舍棄的表S元組保存在結(jié)果關(guān)系中，而在SC表的屬性上填空值（NULL），這時(shí)就需要使用外連接。 例22： 查詢每個(gè)學(xué)生及其選修課程的情況。 SELECT S.SNO,SNAME,SEX,AGE,SDEPT,CNO,GRAGE FROM S LEFT OUTER JOIN SC ON S.SNO=SC.SNO /* 也可以使用USING來(lái)去掉結(jié)果中的重復(fù)值： FROM S LEFT OUT JOIN SC USING(SNO) */,50,4.3.3 子查詢P78,在SQL語(yǔ)言中，一個(gè)SELECTFROMWHERE語(yǔ)句稱為一個(gè)查詢塊。將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語(yǔ)的條件中的查詢稱為嵌套查詢（nested query），也稱為子查詢。 并允許多層嵌套。可以出現(xiàn)在任何能夠使用表達(dá)式的地方。 但ORDER不能出現(xiàn)在子查詢中。,51, 使用子查詢進(jìn)行基于集合的測(cè)試 使用運(yùn)算符IN或NOT IN，將一個(gè)表達(dá)式的值與子查詢返回的結(jié)果進(jìn)行比較，若該表達(dá)式的值與集合中的某個(gè)值相等，則測(cè)試結(jié)果為TRUE；若均不相等，則返回FALSE。,52,例23： 查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生。 先分步來(lái)完成此查詢，然后再構(gòu)造嵌套查詢。, 確定“劉晨”所在系名 SELECT SDEPT FROM S WHERE SNAME=劉晨 結(jié)果為：CS, 查找所有在CS系學(xué)習(xí)的學(xué)生。 SELECT SNO,SNAME,SDEPT FROM S WHERE SDEPT=CS,53,將第一步查詢嵌人到第二步查詢的條件中，構(gòu)造嵌套查詢?nèi)缦拢?SELECT SNO,SNAME,SDEPT FROM S WHERE SDEPT IN (SELECT SDEPT FROM S WHERE SNAME=劉晨 ) 本例中，子查詢的查詢條件不依賴于父查詢，稱為不相關(guān)子查詢。 一種求解方法是由里向外處理，即先執(zhí)行子查詢，子查詢的結(jié)果用于建立其父查詢的查找條件。,54,例24： 查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號(hào)和姓名。 本查詢涉及學(xué)號(hào)、姓名和課程名3個(gè)屬性。學(xué)號(hào)和姓名存放在S表中，課程名存放在C表中，但S與C兩個(gè)表之間沒(méi)有直接聯(lián)系，必須通過(guò)SC表建立它們二者之間的聯(lián)系。所以本查詢實(shí)際上涉及3個(gè)關(guān)系。 SELECT SNO,SNAME /* 外層在S關(guān)系中取出SNO和SNAME */ FROM S WHERE SNO IN (SELECT SNO /* 在SC關(guān)系中找出選修了3號(hào)課程的學(xué)生學(xué)號(hào) */ FROM SC WHERE CNO IN (SELECT CNO /* 在C關(guān)系中找出“信息系統(tǒng)”的課 FROM C 程號(hào)，結(jié)果為3號(hào) */ WHERE CNAME=信息系統(tǒng) ) ),55,本查詢同樣可以用連接查詢實(shí)現(xiàn)： SELECT S.SNO,SNAME FROM S,SC,C WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND C.CNAME=信息系統(tǒng) 說(shuō)明: 有些嵌套查詢可以用連接運(yùn)算替代，有些是不能替代的。,56,2、使用子查詢進(jìn)行比較測(cè)試 如果子查詢的查詢條件依賴于父查詢，這類子查詢稱為相關(guān)子查詢（Correlated Subquery）。 通過(guò)比較運(yùn)算符（=、=、=(SELECT AVG(GRADE) /* 某學(xué)生的平均成績(jī) */ FROM SC Y WHERE Y.SNO=X.SNO),57,例26： 查詢其他系中比計(jì)算機(jī)科學(xué)系某一學(xué)生年齡小的學(xué)生的姓名和年齡。 SELECT SNAME,AGE FROM S WHERE AGECS,注意這是父查詢塊中的條件,RDBMS執(zhí)行此查詢時(shí)，首先處理子查詢，找出CS系中所有學(xué)生的年齡，構(gòu)成一個(gè)集合（20，19）。然后處理父查詢，找所有不是CS系且年齡小于20或l9的學(xué)生。,58,3、使用子查詢進(jìn)行存在性測(cè)試 使用EXISTS謂詞，當(dāng)子查詢中有滿足條件的數(shù)據(jù)時(shí)，EXISTS返回的結(jié)果為真值，否則為假值。 例27： 找出學(xué)習(xí)課程為“數(shù)據(jù)庫(kù)”的學(xué)生姓名。 SELECT SNAME FROM S WHERE EXISTS （ SELECT * FROM SC,C WHERE S.SNO=SC.SNO AND C.CNAME=數(shù)據(jù)庫(kù) ） 注意：先執(zhí)行外層查詢，再執(zhí)行內(nèi)層查詢 在子查詢中的目標(biāo)列名序列通常都用“*”,59,還可使用NOT EXISTS謂詞，當(dāng)子查詢中至少有一個(gè)滿足條件的數(shù)據(jù)時(shí)，返回的結(jié)果為假值，都不滿足時(shí)為真值。 例28： 找出學(xué)習(xí)課程沒(méi)有選修“數(shù)據(jù)庫(kù)”這門課的學(xué)生姓名。 SELECT SNAME FROM S WHERE NOT EXISTS（ SELECT * FROM SC,C WHERE S.SNO=SC.SNO AND C.CNAME=數(shù)據(jù)庫(kù)）,60,子查詢的結(jié)果是一個(gè)關(guān)系，可用于這個(gè)關(guān)系的SQL運(yùn)算符有：IN，ALL，ANY。,61,4.3.4 使用庫(kù)函數(shù)查詢P81,SQL 提供的常用統(tǒng)計(jì)函數(shù)稱為庫(kù)函數(shù)。 SQL還提供了聚集函數(shù)用來(lái)增強(qiáng)檢索功能： 聚合函數(shù)是涉及整個(gè)關(guān)系的另一類運(yùn)算操作。通過(guò)聚合函數(shù)，可以把某一列中的值形成單個(gè)值。SQL不僅允許聚合屬性上的值，而且可以按照某個(gè)準(zhǔn)則將關(guān)系中的元組分組。,62,COUNT（*） 計(jì)算元組的個(gè)數(shù)。 COUNT（列名） 對(duì)一列中的值計(jì)算個(gè)數(shù)。 SUM（列名） 求某一列值的總和（此列的值必須是數(shù)值型）。 AVG（列名） 求某一列值的平均值（此列的值必須是數(shù)值型）。 MAX（列名） 求某一列值的最大值。 MIN（列名） 求某一列值的最小值。,如果指定在列名前面指明DISTINCT短語(yǔ)，則表示在計(jì)算時(shí)要取消指定列中的重復(fù)值。如果不指定DISTINCT短語(yǔ)或指定ALL短語(yǔ)（ALL為缺省值），則表示不取消重復(fù)值。,63,例29： 查詢選修了課程的學(xué)生人數(shù)。 SELECT COUNT(DISTINCT SNO) FROM SC 學(xué)生每選修一門課，在SC中都有一條相應(yīng)的記錄。一個(gè)學(xué)生要選修多門課程，為避免重復(fù)計(jì)算學(xué)生人數(shù)，必須在COUNT函數(shù)中用DISTINCT短語(yǔ)。,64,例30： 計(jì)算選修1號(hào)課程的學(xué)生平均成績(jī)。 SELECT AVG(GRADE) FROM SC WHERE CNO=1 例31： 查詢選修1號(hào)課程的學(xué)生最高分?jǐn)?shù)。 SELECT MAX(GRADE) FROM SC WHER CNO=1,65,關(guān)系是元組的集合，可以進(jìn)行集合運(yùn)算。 集合運(yùn)算包括：并UNION、差MINUS、交INTERSECTION。 SELECT語(yǔ)句的查詢結(jié)果是元組的集合，所以多個(gè)SELECT語(yǔ)句的結(jié)果可進(jìn)行集合操作。,4.3.5 集合運(yùn)算查詢P84,66,例32： 查詢計(jì)算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。 SELECT * FROM S WHERE SDEPT=CS UNION SELECT * FROM S WHERE AGE=19 本查詢實(shí)際上是求計(jì)算機(jī)科學(xué)系的所有學(xué)生與年齡不大于19歲的學(xué)生的并集。使UNION將多個(gè)查詢結(jié)果合并起來(lái)時(shí)，系統(tǒng)會(huì)自動(dòng)去掉重復(fù)元組。如果要保留重復(fù)元組則需要換為UNION ALL操作符。,67,例33： 查詢既選修了1號(hào)課程又選修了2號(hào)課程的全體學(xué)生。就是查詢選修1號(hào)課程的學(xué)生集合與選修2號(hào)課程的學(xué)生集合的交集。 SELECT SNO FROM SC WHERE CNO=1 INTERSECTION SELECT SNO FROM SC WHERE CNO=2； 本例也可以表示為 SELECT SNO FROM SC WHERE CNO=1 AND SNO IN (SELECT SNO FROM SC WHERE CNO=2),68,4.4數(shù)據(jù)更新 P85,主要內(nèi)容 數(shù)據(jù)插入 數(shù)據(jù)刪除 數(shù)據(jù)修改,69,前面介紹使用SELECT 語(yǔ)句查詢數(shù)據(jù)庫(kù)中的數(shù)據(jù)，但不會(huì)使數(shù)據(jù)庫(kù)中的數(shù)據(jù)發(fā)生變化。 若要對(duì)數(shù)據(jù)進(jìn)行各種更新的操作，如添加、修改、刪除，則需使用數(shù)據(jù)修改語(yǔ)句INSERT、UPDATE、DELETE來(lái)完成。,70,插入數(shù)據(jù)有兩種格式： 一種向具體元組插入常量數(shù)據(jù)；（一行） 另一種是把從子查詢的結(jié)果輸入到另一個(gè)關(guān)系中去。（多行）,4.4.1 數(shù)據(jù)插入,71,1、插入元組 插入元組的INSERT語(yǔ)句的格式為 INSERT INTO (，() VALUES( ，) 其功能是將新元組插入指定表中，其中新元組的屬性列1的值為常量1，屬性列2的值為常量2，。INTO子句中沒(méi)有出現(xiàn)的屬性列，新元組按表中原順序逐一賦給對(duì)應(yīng)列。 但必須注意的是，在表定義時(shí)說(shuō)明了NOT NULL的屬性列不能取空值，否則會(huì)出錯(cuò)。 如果INTO子句中沒(méi)有指明任何屬性列名，則新插入的元組必須在每個(gè)屬性列上均有值。,72,例34： 將一個(gè)新學(xué)生元組（學(xué)號(hào)：200915128；姓名：陳冬；性別：M；所在系：IS；年齡：l8）插入到S表中。 INSERT INTO S(SNO,SNAME,SEX,SDEPT,AGE) VALUES(200915128,陳冬,M,IS,l8) 在INTO子句中指出了表名S，指出了新增加的元組在哪些屬性上要賦值，屬性的順序可以與CREATE TABLE中的順序不一樣，VALUES子句對(duì)新元組的各屬性賦值，字符串常數(shù)要用單引號(hào)（英文符號(hào)）括起來(lái)。 例35： 將學(xué)生張成民的信息插入到S表中。 INSERT INTO S VALUES(200915126,張成民,M,18,CS),73,例36： 插入一條選課記錄（200915128,1）。 INSERT INTO SC(SNO,CNO) VALUES(200915128,1) RDBMS將在新插入記錄的GRADE列上自動(dòng)地賦空值。 或者： INSERT INTO SC VALUES(20091