關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言——SQL課件

1、第五章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL本章教學(xué)目標(biāo)、重點(diǎn)和難點(diǎn) 本章教學(xué)目標(biāo)：使學(xué)生了解SQL的功能和語(yǔ)言特點(diǎn)；熟練掌握用SQL進(jìn)行表的定義、數(shù)據(jù)查詢和數(shù)據(jù)更新，掌握SQL的數(shù)據(jù)控制方法。 2. 教學(xué)重點(diǎn)：SQL的數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)維護(hù)和數(shù)據(jù)控制表示方法 3. 教學(xué)難點(diǎn)：用SQL實(shí)現(xiàn)嵌套查詢、組合查詢、使用分組和SQL函數(shù)查詢。本章內(nèi)容SQL的功能及特點(diǎn)數(shù)據(jù)定義語(yǔ)句數(shù)據(jù)查詢語(yǔ)句數(shù)據(jù)更新語(yǔ)句嵌入式SQL數(shù)據(jù)控制機(jī)制和語(yǔ)句SQL的功能及特點(diǎn)19741975-19791986-198719921999由Boyce和Chamberlin提出IBM公司研制了關(guān)系DBMS原型，并實(shí)現(xiàn)該語(yǔ)言美國(guó)批準(zhǔn)SQL作

2、為美國(guó)標(biāo)準(zhǔn)，隨后ISO也通過(guò)這一標(biāo)準(zhǔn)公布了SQL-92標(biāo)準(zhǔn)公布了SQL-99，也稱作SQL3SQL的功能及特點(diǎn)數(shù)據(jù)定義數(shù)據(jù)控制數(shù)據(jù)操縱SQL語(yǔ)句分類SQL的功能及特點(diǎn)SQL語(yǔ)句分類數(shù)據(jù)定義數(shù)據(jù)操縱數(shù)據(jù)控制定義關(guān)系數(shù)據(jù)庫(kù)的模式、外模式和內(nèi)模式，以實(shí)現(xiàn)對(duì)基本表、視圖以及索引文件的定義、修改和刪除等操作。包括數(shù)據(jù)查詢和數(shù)據(jù)更新兩種數(shù)據(jù)操作語(yǔ)句：數(shù)據(jù)查詢指對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)查詢、統(tǒng)計(jì)、分組、排序操作；數(shù)據(jù)更新指數(shù)據(jù)的插入、刪除、修改等數(shù)據(jù)維護(hù)操作。通過(guò)對(duì)數(shù)據(jù)庫(kù)用戶的授權(quán)和收權(quán)命令來(lái)實(shí)現(xiàn)有關(guān)數(shù)據(jù)的存取控制，以保證數(shù)據(jù)庫(kù)的安全性。SQL的功能及特點(diǎn)SQL的特點(diǎn)1）SQL具有自主式語(yǔ)言和嵌入式語(yǔ)言兩種使用方式

3、；2）SQL具有語(yǔ)言簡(jiǎn)潔、易學(xué)易用的特點(diǎn)；SQL功能命令動(dòng)詞數(shù)據(jù)定義（數(shù)據(jù)模式定義、刪除、修改）Create、Drop、Alter數(shù)據(jù)操縱（數(shù)據(jù)查詢和維護(hù)）Select、Insert、Update、Delete數(shù)據(jù)控制（數(shù)據(jù)存取控制權(quán)和收權(quán)）Grant、RemoveSQL的功能及特點(diǎn)SQL的特點(diǎn)3） SQL支持三級(jí)模式結(jié)構(gòu)存儲(chǔ)文件1存儲(chǔ)文件2基本表1基本表2基本表3基本表4視圖1視圖2SQL外模式模式內(nèi)模式數(shù)據(jù)定義語(yǔ)句操作對(duì)象創(chuàng)建語(yǔ)句刪除語(yǔ)句修改語(yǔ)句基本表Create tableDrop tableAlter table索引Create indexDrop index視圖Create view

4、Drop view數(shù)據(jù)庫(kù)Create databaseDrop databaseAlter database1. 語(yǔ)句格式約定符號(hào)尖括號(hào)中括號(hào)大括號(hào)或分隔符|,n實(shí)際語(yǔ)義任選項(xiàng)必選項(xiàng)，必選其中一項(xiàng)前面的項(xiàng)可重復(fù)多次數(shù)據(jù)定義語(yǔ)句2. 一般語(yǔ)法規(guī)定SQL中的數(shù)據(jù)項(xiàng)（包括列項(xiàng)、表和視圖）分隔符為“,”，其字符串常數(shù)的定界符用單引號(hào)“”表示3. SQL特殊語(yǔ)法規(guī)定SQL的關(guān)鍵詞一般使用大寫字母表示；語(yǔ)句的結(jié)束符為“;”語(yǔ)句一般應(yīng)采用格式化書寫方式數(shù)據(jù)定義語(yǔ)句基本表的定義和維護(hù)1. 基本表的定義定義基本表語(yǔ)句的一般格式為： CREATE TABLE 庫(kù)名表名( 列名數(shù)據(jù)類型列級(jí)完整性約束條件 ，列名數(shù)據(jù)

5、類型列級(jí)完整性約束條件 ，n ，表級(jí)完整性約束條件 ，n )；數(shù)據(jù)定義語(yǔ)句建立基本表： 學(xué)生(學(xué)號(hào)，姓名，年齡，性別，所在系)；課程(課程號(hào)，課程名，先行課)； 選課(學(xué)號(hào)，課程號(hào)，成績(jī)).CREATE TABLE 課程(課程號(hào) CHAR(5) PRIMARY KEY， 課程名 CHAR(20)， 先行課 CHAR(5) )；數(shù)據(jù)定義語(yǔ)句（1）SQL支持的數(shù)據(jù)類型類型表示類型說(shuō)明數(shù)值型數(shù)據(jù)SMALLINT半字長(zhǎng)二進(jìn)制證書，15bit數(shù)據(jù)INTEGER或INT全字長(zhǎng)（4字長(zhǎng)）整數(shù)，31bit數(shù)據(jù)DECIMAL(p,q)十進(jìn)制數(shù)，共p位，其中小數(shù)點(diǎn)后q位，0qp，q=0時(shí)可省略不寫FLOAT雙字長(zhǎng)

6、浮點(diǎn)數(shù)字符型數(shù)據(jù)CHARTER(n)或CHAR(n)長(zhǎng)度為n的定長(zhǎng)圖形字符串VARCHAR(n)最大長(zhǎng)度為n的變長(zhǎng)字符串特殊數(shù)據(jù)類型GRAPHIC(n)長(zhǎng)度為n的定長(zhǎng)圖形字符串VARGRAPHIC(n)最大長(zhǎng)度為n的變長(zhǎng)圖形字符串日期時(shí)間型DATE日期型，格式為YYYY-MM-DDTIME時(shí)間型，格式為HH.MM.SSTIMESTAMP日期加時(shí)間數(shù)據(jù)定義語(yǔ)句（2）列級(jí)完整性的約束條件1）NOT NULL或NULL約束NOT NULL約束不允許字段值為空而NULL約束允許字段值為空。2）UNIQUE約束UNIQUE約束是惟一性約束，即不允許列中出現(xiàn)重復(fù)的屬性值。3）DEFAULT約束DEFAUL

7、T為默認(rèn)值約束。DEFAULT約束名默認(rèn)值FOR列名4）CHECK約束CHECK為檢查約束。CONSTRAINT約束名CHECK (約束條件表達(dá)式)數(shù)據(jù)定義語(yǔ)句建立基本表： 學(xué)生(學(xué)號(hào)，姓名，年齡，性別，所在系)；課程(課程號(hào)，課程名，先行課)； 選課(學(xué)號(hào)，課程號(hào)，成績(jī)).CREATE TABLE 學(xué)生(學(xué)號(hào) CHAR(5) NOT NULL UNIQUE， 姓名 CHAR(8) NOT NULL， 年齡 SMALLINT， 性別 CHAR(2)， 所在系 CHAR(20)， DEFAULT C1 20 FOR 年齡， CONSTRAINT C2 CHECK(性別 IN (男，女)；數(shù)據(jù)定義

8、語(yǔ)句（3）表級(jí)完整性的約束條件1）UNIQUE約束UNIQUE約束是惟一性約束，當(dāng)要求列祖的值不能有重復(fù)值時(shí)，使用UNIQUE約束定義。2）PRIMARY KEY約束定義主碼，保證惟一性和非空性。CONTRAINT約束名PRIMARY KEY CLUSTERED (列組)3）FOREIGN KEY約束用于定義參照完整性。 CONTRAINT約束名FOREIGN KEY(外碼) REFERENCES被參照表名(與外碼對(duì)應(yīng)的主碼名)數(shù)據(jù)定義語(yǔ)句建立基本表： 學(xué)生(學(xué)號(hào)，姓名，年齡，性別，所在系)；課程(課程號(hào)，課程名，先行課)； 選課(學(xué)號(hào)，課程號(hào)，成績(jī)).CREATE TABLE 選課(學(xué)號(hào) C

9、HAR(5)， 課程號(hào) CHAR(5)， 成績(jī) SMALLINT， CONSTRAINT C3 CHECK(成績(jī) BETWEEN 0 AND 100)， CONSTRAINT C4 PRIMARY KEY(學(xué)號(hào)，課程號(hào))， CONSTRAINT C5 FOREIGN KEY(學(xué)號(hào)) REFERENCES 學(xué)生(學(xué)號(hào))， CONSTRAINT C6 FOREIGN KEY(課程號(hào)) REFERENCES 課程(課程號(hào))； 數(shù)據(jù)定義語(yǔ)句2.修改基本表ALTER TABLE表名 ADD(新列名數(shù)據(jù)類型完整性約束 ，n) DROP完整性約束名 MODIFY(列名數(shù)據(jù)類型，n)；使用ADD子句增加新列【

10、例5-2】向課程表中增加“學(xué)時(shí)”字段。 ALTER TABLE 課程 ADD 學(xué)時(shí) SMALLINT；使用MODIFY子句修改列的原定義：MODIFY子句主要用于加寬原列的寬度使用DROP子句刪除指定的完整性約束條件【例5-3】刪除學(xué)生表中對(duì)年齡的默認(rèn)值的定義。 ALTER TABLE 學(xué)生 DROP C1；數(shù)據(jù)定義語(yǔ)句3.刪除基本表刪除基本表的一般格式為： DROP TABLE表名；數(shù)據(jù)定義語(yǔ)句索引的定義和維護(hù)1.索引的作用使用索引可以明顯地加快數(shù)據(jù)查詢的速度；先將索引文件讀入內(nèi)存，根據(jù)索引項(xiàng)找到元組的地址，然后再根據(jù)地址將元組數(shù)據(jù)直接讀入計(jì)算機(jī)。由于索引文件中只含有索引項(xiàng)和元組地址，所以文

11、件小，一般可一次讀入內(nèi)存索引文件中的索引項(xiàng)是經(jīng)過(guò)排序的，可以很快地找到索引項(xiàng)值和元組地址數(shù)據(jù)定義語(yǔ)句索引的定義和維護(hù)1.索引的作用使用索引可以明顯地加快數(shù)據(jù)查詢的速度； 使用索引可保證數(shù)據(jù)的惟一性；使用索引可以加快連接速度。2.建立索引的原則索引的建立和維護(hù)由DBA和DBMS完成；大表應(yīng)當(dāng)建索引，小表則不必建索引；對(duì)于一個(gè)基本表，不要建立過(guò)多的索引；根據(jù)查詢要求建索引。數(shù)據(jù)定義語(yǔ)句3.建立索引的格式建立格式為：CREATE UNIQUE CLUSTER INDEX索引名 ON表名(列名次序，列名次序)；是要建索引的基本表的名字；每個(gè)后面還可以用指定索引值的排列次序，次序可選ASC（升序）或DE

12、SC（降序），默認(rèn)值為ASC；UNIQUE表示該索引的每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄；CLUSTER表示要建立的索引是聚簇索引，使基本表中數(shù)據(jù)的物理順序與索引項(xiàng)的排列順序一致。數(shù)據(jù)定義語(yǔ)句3.建立索引的格式 【例5-4】為學(xué)生_課程數(shù)據(jù)庫(kù)中的學(xué)生、課程和選課三個(gè)表建立索引。其中，學(xué)生表按學(xué)號(hào)升序建立索引；課程表按課程號(hào)升序建惟一索引；選課表按學(xué)號(hào)升序和課程號(hào)降序建惟一索引。CREATE UNIQUE INDEX 學(xué)號(hào) ON 學(xué)生(學(xué)號(hào))；CREATE UNIQUE INDEX 課程號(hào) ON 課程(課程號(hào))；CREATE UNIQUE INDEX 選課號(hào) ON 選課(學(xué)號(hào) ASC，課程號(hào) DE

13、SC)；數(shù)據(jù)定義語(yǔ)句4.刪除索引刪除索引格式為： DROP INDEX 索引名；例：刪除學(xué)生表的學(xué)生姓名索引DROP INDEX 學(xué)生姓名數(shù)據(jù)定義語(yǔ)句視圖的定義和維護(hù)1.視圖的優(yōu)點(diǎn)視圖是根據(jù)子模式設(shè)計(jì)的關(guān)系，由一個(gè)或幾個(gè)基本表導(dǎo)出的虛表。視圖能夠簡(jiǎn)化用戶的操作；視圖機(jī)制可以減少用戶對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)結(jié)構(gòu)的調(diào)整操作，使用戶將注意力集中在所關(guān)心的數(shù)據(jù)上。2）視圖機(jī)制可以使用戶以不同的方式看待同一數(shù)據(jù)；當(dāng)多用戶共享同一個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)時(shí)，通過(guò)視圖機(jī)制可以實(shí)現(xiàn)各個(gè)用戶對(duì)數(shù)據(jù)的不同使用要求。3）視圖可以對(duì)機(jī)密的數(shù)據(jù)提供安全保護(hù)數(shù)據(jù)定義語(yǔ)句1.視圖的優(yōu)點(diǎn)4）視圖對(duì)數(shù)據(jù)庫(kù)的重構(gòu)提供了一定程度的邏輯獨(dú)立性；在關(guān)系數(shù)

14、據(jù)庫(kù)中，數(shù)據(jù)庫(kù)的重構(gòu)往往是不可避免的。但由于視圖機(jī)制，將為應(yīng)用程序提供了原來(lái)使用的關(guān)系，保持外模式。原表：學(xué)生（學(xué)號(hào)，姓名，性別，年齡，所在系）分成：SX（學(xué)號(hào)，姓名，年齡） SY（學(xué)號(hào)，性別，所在系）CREATE VIEW 學(xué)生 (學(xué)號(hào)，姓名，性別，年齡，所在系）AS SELECT SX.學(xué)號(hào)，SX.姓名，SX.性別，SY.年齡，SY.所在系FROM SX,SYWHERE SX.學(xué)號(hào)=SY.學(xué)號(hào)；數(shù)據(jù)定義語(yǔ)句2.視圖定義的格式一般格式為： CREATE VIEW視圖名(列名組) AS子查詢 WITH CHECK OPTION；（1）WITH CHECK OPTION選項(xiàng) 表示在對(duì)視圖進(jìn)行UP

15、DATE、INSERT和DELETE操作時(shí)，要保證操作的數(shù)據(jù)滿足視圖定義中的謂詞條件。該謂詞條件是視圖子查詢中的WHERE子句的條件。數(shù)據(jù)定義語(yǔ)句2.視圖定義的格式一般格式為： CREATE VIEW視圖名(列名組) AS子查詢 WITH CHECK OPTION；（2）組成視圖的屬性列名全部省略或者全部指定 若省略了視圖的各個(gè)屬性列名，則由子查詢中的SELECT子句的目標(biāo)列代替 必須明確指定列名的情況某個(gè)目標(biāo)列是集函數(shù)或列表達(dá)式；子查詢中使用多個(gè)表（或視圖），并且目標(biāo)列中含有相同的屬性名；需要在視圖中改用新的、更合適的列名。數(shù)據(jù)定義語(yǔ)句2.視圖定義的格式【例5-6】建立計(jì)算機(jī)系學(xué)生的視圖。

16、CREATE VIEW 計(jì)算機(jī)系學(xué)生 AS SELECT 學(xué)號(hào)，姓名，年齡 FROM 學(xué)生 WHERE 所在系=計(jì)算機(jī)系；數(shù)據(jù)定義語(yǔ)句2.視圖定義的格式【例5-7】由學(xué)生、課程和選課三個(gè)表，定義一個(gè)計(jì)算機(jī)系的學(xué)生成績(jī)視圖，其屬性包括學(xué)號(hào)、姓名、課程名和成績(jī)CREATE VIEW 學(xué)生成績(jī)(學(xué)號(hào)，姓名，課程名，成績(jī)) AS SELECT 學(xué)生.學(xué)號(hào)，學(xué)生.姓名，課程.課程名，選課.成績(jī) FORM 學(xué)生，課程，選課 WHERE 學(xué)生.學(xué)號(hào)=選課.學(xué)號(hào) AND 程.課程名=選課.課程號(hào) AND 學(xué)生.所在系=計(jì)算機(jī)系；數(shù)據(jù)定義語(yǔ)句2.視圖定義的格式【例5-8】將學(xué)生的學(xué)號(hào)、總成績(jī)、平均成績(jī)定義成一個(gè)

17、視圖。 CREATE VIEW 學(xué)生成績(jī)統(tǒng)計(jì)(學(xué)號(hào)，總成績(jī)，平均成績(jī)) AS SELECT 學(xué)號(hào)，SUM（成績(jī)），AVG（成績(jī)） FORM 選課 GROUP BY 學(xué)號(hào)；3.視圖的刪除 DROP VIEW數(shù)據(jù)定義語(yǔ)句4.視圖的查詢和維護(hù)視圖可以和基本表一樣被查詢，其使用方法與基本表相同，但利用視圖進(jìn)行數(shù)據(jù)增、刪、改操作，會(huì)受到一定的限制。一般的數(shù)據(jù)庫(kù)系統(tǒng)不支持對(duì)下列幾種情況的視圖進(jìn)行數(shù)據(jù)更新操作。1）由兩個(gè)以上基本表導(dǎo)出的視圖2）視圖的字段來(lái)自字段表達(dá)式函數(shù)3）視圖中有分組子句或使用了DISTINCT短語(yǔ)4）視圖定義中有嵌套查詢，且內(nèi)層查詢中涉及了與外層一樣的導(dǎo)出該視圖的基本表5）在一個(gè)不允許

18、更新的視圖上定義的視圖數(shù)據(jù)查詢語(yǔ)句數(shù)據(jù)查詢的基本語(yǔ)法1.SELECT語(yǔ)句的語(yǔ)法SELECT目標(biāo)列組 FROM數(shù)據(jù)源 WHERE元組選擇條件 GROUP BY分列組HAVING 組選擇條件 ORDER BY排序列1排序要求1 ，n；數(shù)據(jù)查詢語(yǔ)句1.SELECT語(yǔ)句的語(yǔ)法SELECT子句：指明目標(biāo)列（字段、表達(dá)式、函數(shù)表達(dá)式、常量）?；颈碇邢嗤牧忻硎緸椋罕砻?列名FROM子句：指明數(shù)據(jù)源。表間用“，”分割。數(shù)據(jù)源不在當(dāng)前數(shù)據(jù)庫(kù)中，使用“數(shù)據(jù)庫(kù)名.表名”表示。一表多用，用別名標(biāo)識(shí)。定義表別名：表名.別名WHERE子句：元組選擇條件。數(shù)據(jù)查詢語(yǔ)句1.SELECT語(yǔ)句的語(yǔ)法學(xué)生課程庫(kù)結(jié)構(gòu)為：學(xué)生(

19、學(xué)號(hào)，姓名，年齡，所在系)； 課程(課程號(hào)，課程名，先行課)； 選課(學(xué)號(hào)，課程號(hào)，成績(jī)).【例5-9】求數(shù)學(xué)系學(xué)生的學(xué)號(hào)和姓名。 SELECT 學(xué)號(hào)，姓名 FROM 學(xué)生 WHERE 所在系=數(shù)學(xué)系；【例5-10】求選修了課程的學(xué)生學(xué)號(hào)。 SELECT DISTINCT 學(xué)號(hào) FROM 選課；數(shù)據(jù)查詢語(yǔ)句1.SELECT語(yǔ)句的語(yǔ)法(4) GROUP BY子句：結(jié)果集分組。當(dāng)目標(biāo)列中有統(tǒng)計(jì)函數(shù)，則統(tǒng)計(jì)為分組統(tǒng)計(jì)，否則為對(duì)整個(gè)結(jié)果集統(tǒng)計(jì)。子句后帶上HAVING子句表達(dá)組選擇條件（帶函數(shù)的表達(dá)式）。(5) ORDER BY子句：排序。當(dāng)排序要求為ASC時(shí)升序排序；排序要求為DESC時(shí)降序排列?！纠?/p>

20、5-11】求選修C1課程的學(xué)生學(xué)號(hào)和成績(jī)，并要求對(duì)查詢結(jié)果按成績(jī)的降序排列，如果成績(jī)相同則按學(xué)號(hào)的升序排列。 SELECT 學(xué)號(hào)，成績(jī) FROM 選課 WHERE 課程號(hào)=C1 ORDER BY 成績(jī) DESC，學(xué)號(hào) ASC；數(shù)據(jù)查詢語(yǔ)句2.SELECT語(yǔ)句的操作符(1) 算術(shù)操作符+（加號(hào)）、（減號(hào)）、*（乘號(hào)）和 /（除號(hào)）。(2) 比較操作符=（等于）、（大于）、（小于）、=（大于等于）、!=（不等于）、（小于大于）、!（不大于）和 ! ANY（數(shù)據(jù)表|子查詢）全部(每個(gè))ALL字段比較符ALL （數(shù)據(jù)表|子查詢）例：字段 ALL （數(shù)據(jù)表|子查詢）不存在EXISTSEXISTS (子查

21、詢)在不在范圍NOT BETWEENAND字段NOT BETWEEN 小值 AND 大值是不是空值IS NOT NULL字段 IS NOT NULL模式比較NOT LIKE字段 NOT LIKE 字符常數(shù) 其中， “_” 單字符通配符和 “%” 多字符通配符與運(yùn)算AND條件1AND條件2或運(yùn)算OR條件1OR條件2非運(yùn)算NOTNOT條件數(shù)據(jù)查詢語(yǔ)句(4) 組合查詢操作符SQL的組合查詢操作符是針對(duì)傳統(tǒng)關(guān)系運(yùn)算的操作符，包括UNION（并查詢）、MINUS（差查詢）和INTERSECT（交查詢）其格式為：1）UNION：并查詢，并在結(jié)果集中去掉重復(fù)行。2) MINUS：差查詢操作。3) INTER

22、SECT：交查詢操作。4) *：取全部字段。格式為： * 或表名.*5) ALL：全部。保留重復(fù)值（有統(tǒng)計(jì)函數(shù)時(shí)要求計(jì)算重復(fù)值）。 格式為： ALL字段或 ALL字段組6) DISTINCT：去掉重復(fù)值。在結(jié)果集中去掉重復(fù)值，或在統(tǒng)計(jì)函數(shù)中不計(jì)重復(fù)值。 格式為： DISTINCT字段或 DISTINCT字段組數(shù)據(jù)查詢語(yǔ)句【例5-12】求選修課程C1且成績(jī)?cè)?090之間的學(xué)生學(xué)號(hào)和成績(jī)，并將成績(jī)乘以系數(shù)0.8輸出。 SELECT 學(xué)號(hào)，成績(jī)*0.8 FROM 選課 WHERE 課程號(hào) = C1 AND 成績(jī) BETWEEN 80 AND 90；【例5-13】求數(shù)學(xué)系或計(jì)算機(jī)系姓張的學(xué)生的信息。

23、SELECT * FROM 學(xué)生 WHERE 所在系 IN ( 數(shù)學(xué)系，計(jì)算機(jī)系 ) AND 姓名 LIKE 張%；1.簡(jiǎn)單查詢實(shí)例數(shù)據(jù)查詢語(yǔ)句【例5-14】求缺少了成績(jī)的學(xué)生的學(xué)號(hào)和課程號(hào)。 SELECT 學(xué)號(hào)，課程號(hào) FROM 選課 WHERE 成績(jī) IS NULL；數(shù)據(jù)查詢語(yǔ)句2.連接查詢實(shí)例連接查詢中的連接條件通過(guò)WHERE子句表達(dá)，連接條件和元組選擇條件之間用AND（與）操作符銜接(1) 等值連接和非等值連接 表名1.列名1比較運(yùn)算符表名2.列名2比較運(yùn)算符：=、=、90；數(shù)據(jù)查詢語(yǔ)句(2) 自身連接操作例如，課程表中的先行課是在上學(xué)期應(yīng)開設(shè)的，先行課的先行課，即間接先行課應(yīng)提前一學(xué)

24、年開設(shè)。如果求查詢某門課的間接先行課或全部課程的間接先行課，就需要對(duì)課程表進(jìn)行自身連接。 課程號(hào)課程名先行課C1計(jì)算機(jī)引論C2PASCAL語(yǔ)言C1C3數(shù)據(jù)結(jié)構(gòu)C2C4數(shù)據(jù)庫(kù)C3C5軟件工程C4課程的先行關(guān)系鏈為：C5C4C3C2C1，課程的間接關(guān)系鏈為：C5C3C1。 數(shù)據(jù)查詢語(yǔ)句(2) 自身連接操作【例5-18】查詢每一門課的間接先行課。 SELECT A.課程號(hào)，A.課程名，B.先行課 FROM 課程 A，課程 B WHERE A.先行課=B.課程號(hào) 課程號(hào)課程名先行課課程號(hào)課程名先行課C1計(jì)算機(jī)引論C1計(jì)算機(jī)引論C2Pascal語(yǔ)言C1C2Pascal語(yǔ)言C1C3數(shù)據(jù)結(jié)構(gòu)C2C3數(shù)據(jù)結(jié)構(gòu)

25、C2C4數(shù)據(jù)庫(kù)C3C4數(shù)據(jù)庫(kù)C3C5軟件工程C4C5軟件工程C4數(shù)據(jù)查詢語(yǔ)句(2) 自身連接操作【例5-18】查詢每一門課的間接先行課。 SELECT A.課程號(hào)，A.課程名，B.先行課 FROM 課程 A，課程 B WHERE A.先行課=B.課程號(hào) A.課程號(hào)A.課程名B.先行課C2Pascal語(yǔ)言C3數(shù)據(jù)結(jié)構(gòu)C1C4數(shù)據(jù)庫(kù)C2C5軟件工程C3數(shù)據(jù)查詢語(yǔ)句(3) 外部連接操作 左外部連接操作是在結(jié)果集中保留連接表達(dá)式左表中的非匹配記錄；右外部連接操作是在結(jié)果集中保留連接表達(dá)式右表中的非匹配記錄。外部連接符號(hào)為“*=”，右外部連接符號(hào)為“=*”。外部連接中不匹配的分量用NULL表示。職工號(hào)姓

26、名性別年齡所在部門部門號(hào)部門名稱電話1010李勇男201111生產(chǎn)科5661011劉晨女1912計(jì)劃科5781012王敏女221213一車間4671014張立男211314科研所數(shù)據(jù)查詢語(yǔ)句(3) 外部連接操作職工號(hào)姓名性別年齡所在部門部門名稱電話1010李勇男2011生產(chǎn)科5661012王敏女2212計(jì)劃科5781014張立男2113一車間467內(nèi)連接的結(jié)果集數(shù)據(jù)查詢語(yǔ)句(3) 外部連接操作左連接的結(jié)果集職工號(hào)姓名性別年齡所在部門部門名稱電話1010李勇男2011生產(chǎn)科5661011劉晨女191012王敏女2212計(jì)劃科5781014張立男2113一車間467數(shù)據(jù)查詢語(yǔ)句(3) 外部連接操作

27、右連接的結(jié)果集職工號(hào)姓名性別年齡所在部門部門名稱電話1010李勇男2011生產(chǎn)科5661012王敏女2212計(jì)劃科5781014張立男2113一車間467科研所數(shù)據(jù)查詢語(yǔ)句(3) 外部連接操作【例5-19】用SQL表達(dá)職工和部門之間的內(nèi)連接、左外部連接和右外部連接的語(yǔ)句內(nèi)連接： SELECT 職工.*，部門名稱，電話 FROM 職工，部門 WHERE 職工.所在部門= 部門.部門號(hào)；左外部連接： SELECT 職工.*，部門名稱，電話 FROM 職工，部門 WHERE 職工.所在部門*= 部門.部門號(hào)；右外部連接： SELECT 職工.*，部門名稱，電話 FROM 職工，部門 WHERE 職工

28、.所在部門 =*部門.部門號(hào)； 數(shù)據(jù)查詢語(yǔ)句3.嵌套查詢?cè)赟QL語(yǔ)言中，一個(gè)SELECTFROMWHERE語(yǔ)句稱為一個(gè)查詢塊。將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語(yǔ)的條件中的查詢稱為嵌套查詢。(1) 使用IN操作符的嵌套查詢【例5-20】求選修了高等數(shù)學(xué)的學(xué)生學(xué)號(hào)和姓名。 SELECT 學(xué)號(hào)，姓名 FROM 學(xué)生 WHERE 學(xué)號(hào) IN ( SELECT 學(xué)號(hào) FROM 選課 WHERE 課程號(hào) IN ( SELECT 課程號(hào) FROM 課程 WHERE 課程名=高等數(shù)學(xué) )；數(shù)據(jù)查詢語(yǔ)句3.嵌套查詢?cè)赟QL語(yǔ)言中，一個(gè)SELECTFROMWHERE語(yǔ)句稱為一個(gè)查詢塊

29、。將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語(yǔ)的條件中的查詢稱為嵌套查詢。(1) 使用IN操作符的嵌套查詢【例5-20】求選修了高等數(shù)學(xué)的學(xué)生學(xué)號(hào)和姓名。 該題也可以使用下面的連接查詢表達(dá)。 SELECT 學(xué)生.學(xué)號(hào)，姓名 FROM 學(xué)生，課程，選課 WHERE 學(xué)生.學(xué)號(hào)=課程.學(xué)號(hào) AND 課程.課程號(hào)=選課.課程號(hào) AND 課程.課程名=高等數(shù)學(xué)；數(shù)據(jù)查詢語(yǔ)句3.嵌套查詢(2) 使用比較符的嵌套查詢【例5-21】求C1課程的成績(jī)高于張三的學(xué)生學(xué)號(hào)和成績(jī)。 SELECT 學(xué)號(hào)，成績(jī) FROM 選課 WHERE 課程號(hào)=C1 AND 成績(jī) ( SELEC 成績(jī) FROM

30、選課 WHERE 課程號(hào)=C1AND 學(xué)號(hào)= (SELECT 學(xué)號(hào) FROM 學(xué)生 WHERE 姓名=張三)；數(shù)據(jù)查詢語(yǔ)句(3) 使用ANY或ALL操作符的嵌套查詢其格式為：ANY|ALL 操作符語(yǔ)意ANY大于子查詢結(jié)果中的某個(gè)值，即表示大于查詢結(jié)果中最小值A(chǔ)LL大于子查詢結(jié)果中的所有值，即表示大于查詢結(jié)果中最大值A(chǔ)NY小于子查詢結(jié)果中的某個(gè)值，即表示小于查詢結(jié)果中最大值=ANY大于等于子查詢結(jié)果中的某個(gè)值，即表示大于等于結(jié)果集中最小值=ALL大于等于子查詢結(jié)果中的所有值，即表示大于等于結(jié)果集中最大值數(shù)據(jù)查詢語(yǔ)句(3) 使用ANY或ALL操作符的嵌套查詢其格式為：ANY|ALL 操作符語(yǔ)意=A

31、NY小于等于子查詢結(jié)果中的某個(gè)值，即表示小于等于結(jié)果集中最大值=ALL小于等于子查詢結(jié)果中的所有值，即表示小于等于結(jié)果集中最小值=ANY等于子查詢結(jié)果中的某個(gè)值，即相當(dāng)于IN=ALL等于子查詢結(jié)果中的所有值(通常沒有實(shí)際意義)!=(或)ANY不等于子查詢結(jié)果中的某個(gè)值，!=(或)ALL不等于子查詢結(jié)果中的任何一個(gè)值，即相當(dāng)于NOT IN數(shù)據(jù)查詢語(yǔ)句(3) 使用ANY或ALL操作符的嵌套查詢其格式為：ANY|ALL 【例5-22】求其他系中比計(jì)算機(jī)系某一學(xué)生年齡小的學(xué)生。 SELECT * FROM 學(xué)生 WHERE 年齡 ANY (SELECT 年齡 FROM 學(xué)生 WHERE 所在系=計(jì)算機(jī)

32、系 ) AND 所在系計(jì)算機(jī)系；數(shù)據(jù)查詢語(yǔ)句(3) 使用ANY或ALL操作符的嵌套查詢其格式為：ANY|ALL 【例5-23】求其他系中比計(jì)算機(jī)系學(xué)生年齡都小的學(xué)生。 SELECT * FROM 學(xué)生 WHERE 年齡 ALL (SELECT 年齡 FROM 學(xué)生 WHERE 所在系=計(jì)算機(jī)系) AND 所在系 計(jì)算機(jī)系；數(shù)據(jù)查詢語(yǔ)句(4) 使用EXISTS操作符的嵌套查詢【例5-24】求選修了C2課程的學(xué)生姓名。 SELECT 姓名 FROM 學(xué)生 WHERE EXISTS (SELECT * FROM 選課 WHERE 學(xué)生.學(xué)號(hào)=學(xué)號(hào) AND 課程號(hào)=C2)；Select 姓名From

33、學(xué)生，選課Where 學(xué)生.學(xué)號(hào)=選課.學(xué)號(hào) and 課程號(hào)=c2數(shù)據(jù)查詢語(yǔ)句(4) 使用EXISTS操作符的嵌套查詢【例5-25】求沒有選修C2課程的學(xué)生姓名。 SELECT 姓名 FROM 學(xué)生 WHERE NOT EXISTS (SELECT * FROM 選課 WHERE 學(xué)生.學(xué)號(hào)=學(xué)號(hào) AND 課程號(hào)=C2)；不能寫成以下：Select 姓名From 學(xué)生，選課Where 學(xué)生.學(xué)號(hào)=選課.學(xué)號(hào) and 課程號(hào)c2數(shù)據(jù)查詢語(yǔ)句(4) 使用EXISTS操作符的嵌套查詢【例5-26】查詢選修了全部課程的學(xué)生的姓名。 SELECT 姓名 FROM 學(xué)生 WHERE NOT EXISTS

34、(SELECT * FROM 課程 WHERE NOT EXISTS (SELECT * FROM 選課 WHERE 學(xué)生.學(xué)號(hào)=學(xué)號(hào) AND 課程.課程號(hào)=課程號(hào))；由于SQL沒有全稱量詞的操作符，該題轉(zhuǎn)換為查詢這樣的學(xué)生：沒有一門課他不選修。數(shù)據(jù)查詢語(yǔ)句(4) 使用EXISTS操作符的嵌套查詢【例5-27】求至少選修了學(xué)號(hào)為“S2”的學(xué)生所選修的全部課程的學(xué)生學(xué)號(hào)和姓名。 SELECT 學(xué)號(hào)，姓名 FROM 學(xué)生 WHERE NOT EXISTS (SELECT * FROM 選課 選課1 WHERE 選課1.學(xué)號(hào)=S2 AND NOT EXISTS (SELECT * FROM 選課 選

35、課2 WHERE 學(xué)生.學(xué)號(hào)=選課2.學(xué)號(hào) AND 選課2.課程號(hào)=選課1.課程號(hào))；數(shù)據(jù)查詢語(yǔ)句4.組合查詢Union并操作，Intersect交操作，MINUS差操作【例5-28】求選修了C1課程或選修了C2課程的學(xué)生學(xué)號(hào)。 SELECT 學(xué)號(hào) FROM 選課 WHERE 課程號(hào)=C1 UNION SELECT 學(xué)號(hào) FROM 選課 WHERE 課程號(hào)=C2；Select 學(xué)號(hào)From選課Where 課程號(hào)=C1 or 課程號(hào)=C2數(shù)據(jù)查詢語(yǔ)句4.組合查詢【例5-29】求選修C1課程，并且也選修C2課程的學(xué)生學(xué)號(hào)。 SELECT 學(xué)號(hào) FROM 選課 WHERE 課程號(hào)=C1 INTERS

36、ECT SELECT 學(xué)號(hào) FROM 選課 WHERE 課程號(hào)=C2；數(shù)據(jù)查詢語(yǔ)句4.組合查詢【例5-30】求選修了C1課程但沒有選修C2課程的學(xué)生學(xué)號(hào)。SELECT 學(xué)號(hào) FROM 選課 WHERE 課程號(hào)=C1 MINUS SELECT 學(xué)號(hào) FROM 選課 WHERE 課程號(hào)=C2；數(shù)據(jù)查詢語(yǔ)句4.組合查詢【例5-30】求選修了C1課程但沒有選修C2課程的學(xué)生學(xué)號(hào)。 本例也可以用下面的EXISTS嵌套查詢表示。 SELECT 學(xué)號(hào) FROM 選課 選課1 WHERE 課程號(hào)=C1 AND NOT EXISTS (SELECT 學(xué)號(hào) FROM 選課 選課2 WHERE 選課1.學(xué)號(hào)=選課2

37、.學(xué)號(hào) AND 選課2.課程號(hào)=C2)；數(shù)據(jù)查詢語(yǔ)句5.使用分組和SQL函數(shù)查詢函數(shù)功能AVG(數(shù)值表達(dá)式)求與字段相關(guān)的數(shù)值表達(dá)式的平均值SUM(數(shù)值表達(dá)式)求與字段相關(guān)的數(shù)值表達(dá)式的和值MIN(字段表達(dá)式)求字段表達(dá)式的最小值MAX(字段表達(dá)式)求字段表達(dá)式的最大值COUNT(*|字段)求記錄行數(shù)(*)，或求不是NULL的字段的行數(shù)數(shù)據(jù)查詢語(yǔ)句5.使用分組和SQL函數(shù)查詢【例5-31】求學(xué)生的總?cè)藬?shù)。 SELECT COUNT (*) FROM 學(xué)生；【例5-32】求選修了課程的學(xué)生人數(shù)。 SELECT COUNT(DISTINCT 學(xué)號(hào)) FROM 選課；數(shù)據(jù)查詢語(yǔ)句5.使用分組和SQL

38、函數(shù)查詢【例5-33】求課程和選修該課程的人數(shù)。 SELECT 課程號(hào)，COUNT(學(xué)號(hào)) FROM 選課 GROUP BY 課程號(hào)；【例5-34】求選修課超過(guò)3門課的學(xué)生學(xué)號(hào)。 SELECT 學(xué)號(hào) FROM 選課 GROUP BY 學(xué)號(hào) HAVING COUNT(*)3；數(shù)據(jù)更新語(yǔ)句SQL的數(shù)據(jù)插入功能1. 使用常量插入單個(gè)元組格式為： INSERT INTO表名(屬性列1，屬性列2) VALUES (常量1，常量2)；數(shù)據(jù)更新語(yǔ)句SQL的數(shù)據(jù)插入功能1. 使用常量插入單個(gè)元組【例5-35】將一個(gè)新學(xué)生記錄(學(xué)號(hào)：98010，姓名：張三，年齡：20，所在系：計(jì)算機(jī)系 )插入到學(xué)生表中。INS

39、ERTINTO 學(xué)生VALUES (98010，張三，20，計(jì)算機(jī)系)；【例5-36】插入一條選課記錄(學(xué)號(hào)：98011，課程號(hào)：C10，成績(jī)不詳)。 INSERT INTO 選課 (學(xué)號(hào)，課程號(hào)) VALUES (98011，C10)；數(shù)據(jù)更新語(yǔ)句2.在表中插入子查詢的結(jié)果集INSERT INTO表名(屬性列1，屬性列2) 子查詢；【例5-37】求每個(gè)系學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫(kù)中。 CREATE TABLE 系平均年齡 (系名稱CHAR(20)， 平均年齡SMALLINT)； INSERT INTO 系平均年齡 SELECT 所在系，AVG(ALL年齡) FROM 學(xué)生 GROUP

40、 BY 所在系；數(shù)據(jù)更新語(yǔ)句3.SQL的數(shù)據(jù)修改功能UPDATE表名 SET列名=表達(dá)式，列名=表達(dá)式，n WHERE條件；【例5-38】將學(xué)生表中全部學(xué)生的年齡加上2歲。 UPDATE 學(xué)生 SET 年齡=年齡+2；【例5-39】將選課表中的數(shù)據(jù)庫(kù)課程的成績(jī)乘以1.2。 UPDATE 選課 SET 成績(jī)= 成績(jī)*1.2 WHERE 課程號(hào)= (SELECT 課程號(hào) FROM 課程 WHERE 課程名= 數(shù)據(jù)庫(kù) )；數(shù)據(jù)更新語(yǔ)句3.SQL的數(shù)據(jù)刪除功能DELETE FROM表名 WHERE條件；【例5-40】刪除藝術(shù)系的學(xué)生記錄及選課記錄。 DELETE FROM 選課 WHERE 學(xué)號(hào) IN

41、 (SELECT 學(xué)號(hào) FROM 學(xué)生 WHERE 所在系=藝術(shù)系)； DELETE FROM 學(xué)生 WHERE 所在系=藝術(shù)系；嵌入式SQL嵌入式SQL的特點(diǎn)1. 嵌入式SQL應(yīng)注意的問(wèn)題 1) SQL和主語(yǔ)言的配合問(wèn)題。 2) 合理選擇主語(yǔ)言。2. SQL嵌入主語(yǔ)言時(shí)必須解決的三個(gè)問(wèn)題 (1) 如何區(qū)別SQL和主語(yǔ)言 在所有的SQL語(yǔ)句前面加上前綴EXEC SQL；SQL語(yǔ)句的結(jié)束標(biāo)志隨主語(yǔ)言的不同而不同嵌入式SQL嵌入式SQL的特點(diǎn)2. SQL嵌入主語(yǔ)言時(shí)必須解決的三個(gè)問(wèn)題 (2) 使數(shù)據(jù)庫(kù)的工作單元與程序工作單元之間能夠通信 1) 主語(yǔ)言通過(guò)主變量向SQL語(yǔ)句提供參數(shù)。 2) SQL語(yǔ)

42、句的當(dāng)前工作狀態(tài)和運(yùn)行環(huán)境數(shù)據(jù)要返饋給應(yīng)用程序。 (3) 使用游標(biāo)解決SQL一次一集合的操作與主語(yǔ)言一次一記錄操作的矛盾 游標(biāo)是系統(tǒng)為用戶開設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)，存放SQL語(yǔ)句的執(zhí)行結(jié)果。嵌入式SQL不用游標(biāo)的SQL語(yǔ)句1. 幾種不需要使用游標(biāo)的SQL語(yǔ)句(1) 用于說(shuō)明主變量的說(shuō)明性語(yǔ)句 SQL的說(shuō)明性語(yǔ)句主要有兩條： EXEC SQL BEGIN DECLARE SECTION； EXEC SQL END DECLARE SECTION；(2) 數(shù)據(jù)定義和數(shù)據(jù)控制語(yǔ)句(3) 查詢結(jié)果為單記錄的查詢語(yǔ)句(4) 數(shù)據(jù)的插入語(yǔ)句和某些數(shù)據(jù)刪除、修改語(yǔ)句 獨(dú)立的數(shù)據(jù)刪除和修改語(yǔ)句不需要使用游標(biāo)；與查

43、詢語(yǔ)句配合，刪除或修改查詢到的當(dāng)前記錄的操作，與游標(biāo)有關(guān)。 嵌入式SQL2. 不用游標(biāo)的查詢語(yǔ)句EXEC SQL SELECT ALL|DISTINCT目標(biāo)列表達(dá)式，n INTO主變量指示變量，n FROM表名或視圖名，n WHERE條件表達(dá)式；1) 在語(yǔ)句開始前要加EXEC SQL前綴。2) 該查詢語(yǔ)句中又?jǐn)U充了INTO子句。3) 在WHERE子句的條件表達(dá)式中可以使用主變量。嵌入式SQL2. 不用游標(biāo)的查詢語(yǔ)句EXEC SQL SELECT ALL|DISTINCT目標(biāo)列表達(dá)式，n INTO主變量指示變量，n FROM表名或視圖名，n WHERE條件表達(dá)式；4) 由于查詢的結(jié)果集中只有一條

44、記錄，該語(yǔ)句中不必有排序和分組子句。5) INTO子句中的主變量后面跟有指示變量時(shí) 如果查詢結(jié)果列值為NULL，指示變量為負(fù)值，結(jié)果列不向該主變量賦值； 如果傳遞正常，指示變量的值為0；如果主變量寬度不夠，則指示變量的值為數(shù)據(jù)截?cái)嗲暗膶挾取?) 如果查詢結(jié)果并不是單條記錄，則程序出錯(cuò)。嵌入式SQL2. 不用游標(biāo)的查詢語(yǔ)句EXEC SQL SELECT ALL|DISTINCT目標(biāo)列表達(dá)式，n INTO主變量指示變量，n FROM表名或視圖名，n WHERE條件表達(dá)式；【例5-41】查詢學(xué)號(hào)為主變量givesno、課號(hào)為主變量givecno的值的學(xué)生選課記錄。 EXEC SQL SELECT 學(xué)

45、號(hào)，課程號(hào)，成績(jī) INTO :Sno，:Cno，:grade :gradeid FROM 選課 WHERE 學(xué)號(hào)= :givesno AND 課程號(hào)= :givecno；嵌入式SQL2. 不用游標(biāo)的數(shù)據(jù)維護(hù)語(yǔ)句 (1) 不用游標(biāo)的數(shù)據(jù)刪除語(yǔ)句【例5-42】刪除學(xué)號(hào)由主變量Sno決定的學(xué)生記錄。 EXEC SQL DELETE FROM 學(xué)生 WHERE 學(xué)號(hào)= :Sno；嵌入式SQL2. 不用游標(biāo)的數(shù)據(jù)維護(hù)語(yǔ)句 (2) 不用游標(biāo)的數(shù)據(jù)維護(hù)語(yǔ)句 【例5-43】將計(jì)算機(jī)系所有學(xué)生的年齡都加上主變量Raise。 EXEC SQL UPDATE 學(xué)生 SET 年齡=年齡+:Raise WHERE 所在系=計(jì)算機(jī)系； 【例5-44】將計(jì)算機(jī)系學(xué)生的年齡置空。 Raiseid=-1 EXEC SQL UPDATE 學(xué)生 SET 年齡=年齡+:Raise :Raiseid；嵌入式SQL2. 不用游標(biāo)的數(shù)據(jù)維護(hù)語(yǔ)句 (3) 不用游標(biāo)的數(shù)據(jù)插入語(yǔ)句 【例5-45】將學(xué)號(hào)為主變量Sno、課程號(hào)為Cno的選課記錄，插到庫(kù)中。 EXEC SQL INSERT INTO 選課 VALUES (:Sno， :Cno)；嵌入式SQL使用游標(biāo)的SQL 游標(biāo)機(jī)制用于解決SQL查詢結(jié)果為集合而主語(yǔ)言處理方式為記錄方式的矛盾。 在處理中，必須使用游標(biāo)的SQL語(yǔ)句有兩種