數據庫系統(tǒng)課件：第3章 關系數據庫標準語言

1、第3章 關系數據庫標準語言本章重要概念（1）SQL語言簡介：SQL發(fā)展歷史與特點、組成結構（2）SQL的數據定義：SQL模式、基本表和索引的創(chuàng)建和撤銷。（3）SQL的數據查詢；SELECT語句的句法，SELECT語句的三種形式及各種限定，基本表的聯(lián)接操作，SQL3中的遞歸查詢。本章重要概念（4）SQL的數據操縱：添加、更新和刪除記錄。（5）SQL數據控制：對數據庫用戶或角色授權、收權（6）視圖管理：創(chuàng)建、修改和刪除視圖，對視圖更新操作的限制。（7）嵌入式SQL：運行環(huán)境，使用規(guī)定，使用技術，卷游標，動態(tài)SQL語句。本章內容概述SQL全稱是“結構化查詢語言(Structured Query La

2、nguage)”，是一種通用的功能極強的關系數據庫標準語言。 SQL語言具有結構簡潔，功能強大，簡單易學，被確定為關系數據庫系統(tǒng)的國際標準。本章詳細介紹SQL的核心部分內容：數據定義、數據查詢、數據更新和嵌入式SQL。 3.1 SQL語言簡介SQL發(fā)展歷史與特點SQL語言特點3.1.1 SQL語言簡介SQL語言是當前最為成功、應用最為廣泛的關系數據庫語言，其發(fā)展主要經歷了以下幾個階段：1) 1974年由CHAMBERLIN和BOYEE提出，當時稱為SEQUEL(STUCTURED ENGLISH QUERY LANGUAGE)；2) IBM公司對其進行了修改，并用于其SYSTEM R關系數據庫

3、系統(tǒng)中；3) 1981年 IBM推出其商用關系關系數據庫SQL/DS，并將其名字改為SQL，由于SQL語言功能強大，簡潔易用，因此得到了廣泛的使用； 4)今天廣泛應用于各種大型數據庫，如SYBASE、Microsoft SQL Server、 ORACLE、DB2等，也用于各種小型數據庫，如FOXPRO、ACCESS。3.1.1 SQL語言簡介SQL語言特點非過程化語言 SQL是一個非過程化的語言，因為它一次處理一個記錄，對數據提供自動導航。SQL允許用戶在高層的數據結構上工作，而不對單個記錄進行操作，可操作記錄集。統(tǒng)一的語法結構 SQL為許多任務提供了命令，包括： 查詢數據 在表中插入、修改

4、和刪除記錄 建立、修改和刪除數據對象 控制對數據和數據對象的存取 保證數據庫一致性和完整性 是所有關系數據庫的公共語言3.1.2 SQL的組成結構 SQL 事實上是操作和檢索關系型數據庫的標準語言，它允許程序員和數據庫管理員做如下的工作：更改數據庫的結構；更改系統(tǒng)的安全設置；增加用戶對數據庫或表的許可權限；在數據庫中檢索需要的信息；對數據庫的信息進行更新；1.數據定義語言(Data Definition Language，簡稱DDL)，DDL用于定義數據庫的邏輯結構，是對關系模式一級的定義，包括基本表、視圖及索引的定義。2. 數據查詢語言(Data Query Language，簡稱DQL)，

5、DQL用于查詢數據。SQL主要定義了以下四類語言：3.1.2 SQL的組成結構3.數據操縱語言(Data Manipulation Language，簡稱DML)，DML用于對關系模式中的具體數據的增、刪、改等操作。4. 數據控制語言(Data Control Language，簡稱DCL)，DCL用于數據訪問權限的控制。 SQL 語言的組成對應的命令動詞建表 SQL的組成結構SQL的各部分對應的命令動詞表SQL組成部分 命令動詞數據定義(DDL)CREATE, DROP, ALTER數據查詢(DQL)SELECT數據操縱(DML)INSERT, UPDATE, DELETE數

6、據控制(DCL)GRANT, REVOKESQL語言的組成對應的命令動詞 表3.13.2 SQL數據定義 SQL支持關系數據庫的外模式、模式、內模式這三級 模式結構，如圖3.1所示。 外模式：由視圖和部分基本表構成。視圖是為滿足用戶和應用程序的數據格式要求而定義的，當基本表不適合于用戶直接查詢的操作要求時，需要定義視圖，以便用戶的查詢操作。 模式：由全體基本表構成?；颈硎潜旧愍毩⒌谋恚琒QL中一個關系對應一個基本表。 內模式：由數據庫的存儲文件和他們的索引文件構成。在SQL中，一個關系對應一個表；一個或多個表對應一個存儲文件；一個表可以帶若干個索引。存儲文件的邏輯結構組成了關系數據庫的內模式

7、。3.2 SQL數據定義SQL基本表b3基本表b4存儲文件f2存儲文件f3視圖v1視圖v2基本表b1基本表b2存儲文件f1外模式模式內模式圖3.1 數據庫的三級模式結構3.2.1 數據庫模式的定義 1.數據庫模式的創(chuàng)建 對于SQL,數據庫的創(chuàng)建用CREATE語句實現(xiàn)，典型的數據庫創(chuàng)建語句如下： CREATE DATABASE 數據庫名 【參數】 例3.1 創(chuàng)建一個名稱為“教務管理系統(tǒng)”的數據庫，參 數保持系統(tǒng)默認。 解： CREATE DATABASE 教務管理系統(tǒng)2. 數據庫的刪除原先所建數據庫進行刪除，此時對應的語句為DROP語句，具體語法如下：DROP DATABASE 數據庫名例3.2

8、 刪除”教務管理系統(tǒng)”數據庫. 解： DROP DATABASE 教務管理系統(tǒng)3.2.1 數據庫模式的定義 1. 創(chuàng)建基本表 SQL語言創(chuàng)建基本表的一般格式為： CREATE TABLE (列級完整性約束, )； 3.2.2基本表的定義完整性約束 列級完整性約束僅作用在該列上，表級完整性約束是作用在整個表的，這些完整性約束條件都保存在系統(tǒng)的數據字典中，并由關系數據庫管理系統(tǒng)自動檢查 。 例3.3 創(chuàng)建一個學生表S，包括如下幾個字段Snum、Sname、Ssex、Sage、Sphone、Dnum，其中Snum是學號，Sname是姓名，Ssex是性別， Sage是年齡，Sphone是電話，Dnum

9、是學生所在的院系編號。Snum是主碼，不能為空值且是唯一 。 解：CREATE TABLE S(Snum CHAR(10) NOT NULL UNIQUE, Sname VARCHAR(8), Ssex CHAR(2), Sage SMALLINT, Sphone VARCHAR(20), Dnum CHAR(4), PRIMARY KEY (Snum),FOREIGN KEY(Dnum) REFERENCES D(Dnum); 這里定義的學生表有六個屬性，其中NOT NULL和UNIQUE是列級完整性約束，作用在Snum上，NOT NULL要求Snum不能為空值，UNIQUE要求Snum取值

10、唯一，而PRIMARY KEY(Snum)是表級完整性約束，F(xiàn)OREIGN KEY(Dnum) REFERENCES D(Dnum)則說明該學生表S的字段Dnum為院系表D的外鍵。 一個課程表C(Cnum，Cname，Cfreq)和一個選修表SC(Snum，Cnum，score)，其中Cnum是課程號，Cname是課程名稱， Cfreq是該門課程的學分，Snum是學號， score是成績。Cnum是C表的主碼，(Snum，Cnum)是SC表的主碼。要求學分Cfreq可以是空值或者是0到10之間的定點數。而SC表中的Cnum和Snum分別是課程表C和學生表S的外碼。 例3.4CREATE TAB

11、LE C(Cnum CHAR(4),Cname VARCHAR(20),Cfreq NUMERIC(2,1),PRIMARY KEY (Cnum),CHECK(Cfreq IS NULL) OR (Cfreq BETWEEN 0 AND 10);解：CREATE TABLE SC(Snum CHAR(10),Cnum CHAR(4),Score SMALLINT,PRIMARY KEY (Snum,Cnum),FOREIGN KEY(Snum) REFERENCES S(Snum),FOREIGN KEY(Cnum) REFERENCES C(Cnum);ALTER TABLE 語句的語法如下

12、: ALTER TABLE ADD COLUMN 完整性約束ADD ,DROP COLUMN ,DROP ,ALTER COLUMN ；2. 修改基本表 12. 修改基本表 2ALTER TABLE 語句可以實現(xiàn)如下三種功能： （1）加入一列到已經存在的表中； （2）修改已經存在的表中的某一列； （3）刪除表中已經存在的一列。 例3.6 在已存在的學生表S中增加一個家庭地址Saddress的新的屬性列： 解：ALTER TABLE S ADD Saddress VARCHAR（50）；要注意的是，新增加的屬性列的值都是空值。(1) 增加一列 例3.7 修改學生表S中Sname的數據類型，變?yōu)镃

13、HAR類型。 解：ALTER TABLE S ALTER COLUMN Sname CHAR(15); （2）修改列的屬性（3）刪除列例3.8 刪除學生表S中的Sage字段。解：ALTER TABLE S DROP COLUMN Sage； 例3.8 在學生表S中限定年齡Sage小于100大于0. 解： ALTER TABLE S ADD CONSTRAINT Chk_Sage CHECK (Sage0 and Sage100);（4）增加完整性約束 (5) 刪除完整性約束 例3.9 刪除學生表S中完整性約束 Uni_Snum 和Chk_Sage. 解： ALTER TABLE S DROP

14、Uni_Snum, Chk_Sage; SQL 提供了一個可以從數據庫徹底地移去某個表的命令DROP TABLE，它可以從數據庫中刪除一個指定的表以及與之相關聯(lián)的索引和視圖，需要注意的是，一旦這個命令發(fā)出以后就沒有辦法可以取消它。SQL語言刪除基本表的一般格式為： DROP TABLE CASCADE|RESTRICT； 例3.11 刪除學生表S。 DROP TABLE S3. 刪除基本表1. 建立索引 SQL語言建立索引的一般格式為： CREATE UNIQUECLUSTER INDEX ON (,)；3.2.3索引的定義建立索引示例例3.12 在學生表S的姓名列Sname上建索引 CREA

15、TE INDEX idx_Sname ON S(Sname)；例3.13 在選修表SC的課程號Cnum和成績Score上建索引，要求課程號為主索引且為升序，課程號相同時成績?yōu)榻敌颉?CREATE INDEX idx_sc_s ON SC(Cnum, Score DESC); SQL語言刪除索引的一般格式為： DROP INDEX ； 例3.13 在學生表S上的索引idx_Sname。 DROP INDEX idx_Sname ON S ;2. 刪除索引3.2.3 索引的定義3.3 SQL數據查詢 SELECT命令的格式與基本使用SELECT語句是SQL語言中功能最強大、用途最廣泛的數據庫操縱語

16、句，也是關系運算理論在SQL語言中的主要體現(xiàn)。一個完整的SELECT語句包括SELECT，F(xiàn)ROM，WHERE，GROUP BY和ORDER BY共五個子句，前兩個必不可少，后面三個可以省略。SELECT語句的完整格式為：SELECT DISTINCTFROM WHERE GROUP BY HAVINGORDER BY ASC|DESC3.3 SQL數據查詢 該SELECT格式的含義是： 根據 WHERE 子句的行條件表達式 , 從 FROM 子句指定的基本表或視圖中找出滿足條件的行 , 再按 SELECT 子句中的目標列表達式選出元組中的屬性值形成結果表。3.3 SQL數據查詢 如果有 GR

17、OUP 子句 ,則將結果按 的值進行分組 , 該屬性列值相等的元組為一個組 , 每個組產生結果表中的一條記錄。通常會在每組中進行聚合操作。如果 GROUP 子句帶 HAVING 短語 , 則只有滿足指定條件的組才予輸出。如果有 ORDER 子句 , 則結果表還要按的值的升序或降序排序。 3.3 SQL數據查詢 我們接下來列出本章所舉的各個例子中要用到的教學數據庫中各個基本表的結構，表中加下劃線的字段為表的主碼，其中表S、T、D、C、SC的示范數據請讀者參看第二章圖2.6，新增加的授課表TC示范數據如下所示：3.3 SQL數據查詢 學生S (學號Snum, 姓名Sname , 性別Ssex ,

18、年齡Sage , 聯(lián)系電話Sphone， 系編號Dnum )教師T (教師號Tnum, 姓名Tname , 性別Tsex , 出生日期Tbirth , 職稱Ttitle , 工資Tsalary ,聯(lián)系電話Tphone， 系編號Dnum )本章數據表用例本章數據表用例院系D（系編號Dnum,系名稱Dname,負責人Ddirector）課程C (課程號Cnum, 課程名Cname , 課時Cfreq )選課SC (學號Snum , 課程號Cnum , 成績Score )授課TC ( 教師號Tnum , 課程號Cnum )例 3.15 查詢學生關系表S中所有年齡大于等于18歲的學生的姓名、性別和年齡

19、。 解： SELECT Sname, Ssex, Sage FROM S WHERE Sage=18;3.3.1 簡單查詢執(zhí)行的結果見表3.3:SnameSsexSage張山男18李波濤男22王勁松男24表3.3例3.15 的運行結果1.SELECT 子句SELECT 子句的基本功能是選擇表中的全部列或部分列 , 類似關系代數中的投影運算 ,其語法格式為: SELECT SELECT-FROM-WHERE的具體功能 例 3.16 查詢課程表D中的全部列。 解：SELECT Dnum, Dname, Director FROM D; 也可以簡單的寫成 : SELECT * FROM D;(1)

20、查詢全部列 執(zhí)行結果：Dnum Dname DirectorD001計算機王森林D002通信 方維偉D003自控張小龍D004外語葉文 也可以是常量 , 此時查詢結果中對應列位置的值就都是該常量值。 例3.17 SELECT 子句目標列為常量示例。 SELECT Tname, 工資：，Tsalary FROM T；(2) 常量執(zhí)行結果見表3.5Tname（無列名）Tsalary梁文博工資：2600王芳工資：3200李剛工資：2200王森林工資：3500余雪梅工資：1880方維偉工資：2680表3.5例3.17 的運行結果SELECT 子句中的 還可以是與屬性列有關的表達式 , 即可以是查詢出來

21、的屬性列經過一定的計算后列出結果。 例 3.18 查詢所有老師的稅后工資情況，為簡單起見，我們假定對不同的工資的個人所得稅征收比率都為6%。 SELECT Tname, Tsalary*(1-0.06) FROM T; (3) 計算列執(zhí)行結果見表3.6Tname（無列名）梁文博2444王芳3008李剛2068王森林3290余雪梅1767.2方維偉2519.2表3.6例3.18 的運行結果SELECT 子句中的第二項為Tsalary*(1-0.06), 這是一個表達式 ,即把查詢結果中每一行的 Tsalary 列的值乘以 (1-0.06), 并將計算的結果值作為查詢的結果。顯然 , 查詢結果中有

22、多少行 , 該表達式就有多少個計算結果 , 所以這種計算有時也被稱為 水平計算 或 按行計算 。 (3) 計算列1) 算術運算符:算術操作符在 SQL 語句中表達數學運算操作 , 參與運算的值都是數值型的。 SQL 的數學運算操作符只有四種 , 它們是 :+( 加 ) 、 -( 減 ) 、 *( 乘 ) 和 /( 除 ) 。SQL Server 中的常用運算符 2) 字符運算符:字符運算符在 SQL 語句中表達字符運算操作 , 參與運算的值必須都是字符串類型的。在 SQL Server 中 , 最常見的字符運算符是 +( 字符串連接 ), 該運算可以把兩個字符串連接成一個字符串。SQL Ser

23、ver 中的常用運算符 例319 選出老師工資情況，但對每位老師都帶上稱呼：老師。SELECT Tname老師, Tsalary FROM T;執(zhí)行結果見表3.17Tname（無列名）梁文博老師2600王芳老師3200李剛老師2200王森林老師3500余雪梅老師1880方維偉老師2680例3.19 的運行結果表3.17SQL Server 中的常用運算符 日期運算符在SQL 語句中表示日期運算操作。常用的日期運算符是 +, 該運算允許一個日期值 d和一個 n 相加 , 運算結果為一個日期 d 后 第 n天的日期值 , 也就是說 , 若 x 的值為日期值2009-1-15, 則 x+10 的值為

24、2009-1-25, 類 似的 ,x-10 的值為2009-1-5。(3) 日期運算符 除了運算符外 , 還可以在目標列表達式中使用函數以支持更多的功能 ,常見的函數包括數學函數、字符函數、日期函數等，這部分函數往往依賴于具體的關系數據庫管理系統(tǒng)，讀者可以依據自己所采用的系統(tǒng)，查詢相關的技術手冊來使用。 用戶可以通過指定別名來改變查詢結果中的列標題 ,這樣我們往往可以將表中英文列名轉換成相應的中文名稱來顯示，同時這對于含計算列、常量的目標列表達式尤為有用。 別名的定義格式為 : AS 其中AS可以省略(4) 列的別名 例3.20 在前面的例3., 可以看到其查詢結果中第二列( 計算列 Tsal

25、ary *(1-0.0) 的列標題為 “ 無列名 ” ，現(xiàn)希望將其改為：稅后工資，同時將Tname列改為：教師姓名。 SELECT Tname AS 教師姓名, Tsalary*(1-0.0) AS 稅后工資 FROM T； 或 SELECTTname 教師姓名，Tsalary*（1-0.06）AS 稅后工資 FROMT T;(4) 列的別名執(zhí)行結果為見下表教師姓名稅后工資梁文博2444王芳3008李剛2068王森林3290余雪梅1767.2方維偉2519.2例3.20 的運行結果 按照關系的一般理論，重復的元組是不會出現(xiàn)在關系 中的。但是，在實踐中重復元組的刪除是相當費時的，所以SQL允許在

26、關系和查詢結果中出現(xiàn)重復。在SELECT語句中缺省或ALL是保留重復元組的，因此一般ALL不寫。如果想要強迫刪除重復，則必須使用DISTINCT短語。重復元組的產生多數是在投影的情況下。(5) 取消重復行例3.21 列出已經選課了的同學的學號。如果用語句 : SELECT Snum FROM SC;則執(zhí)行結果為 :Snum 0903330001 0903330001 0903330001 09033300020903330004 0903330005 0903330005 (5) 取消重復行 顯然，這不是我們希望得到的 結果，比如學號0903330001出現(xiàn)了3次，我們只是關心那位同學已經選課

27、，而不是關心選了幾次課，所以，我們不應該讓它重復出現(xiàn)。 于是代碼改為： SELECT DISTINCT Snum FROM SC; 這時執(zhí)行結果為：Snum0903330001090333000209033300040903330005(5) 取消重復行 DISTINCT 關鍵字的作用就是取消重復的行。 其語法格式為 : SELECT DISTINCT|ALL與DISTINCT相對應的關鍵字是ALL, 表示在查詢結果中保留重復的行 , 如果 SELECT子句中沒有指明是DISTINCT 還是 ALL, 則默認為 ALL。(5) 取消重復行FROM 子句的基本功能用于指明查詢所涉及的表 , 其語

28、法結為 : FROM , 如果FROM關鍵字后只有一個基本表名 , 這種查詢稱為單表查詢 , 即僅涉及一個基本表的查詢。2. FROM 選擇表如果 FROM 關鍵字后有多個基本表名 , 這種查詢往往稱為多表查詢 , 查詢結果中包括了多張基本表中的列 , 這幾張基本表之間還要進行連接運算 , 而連接條件則要寫在WHERE 子句中。2. FROM 選擇表WHERE 子句的基本功能用于指明查詢的條件以選擇行 , 只有滿足查詢條件的行才會出現(xiàn)在查詢結果中。如前所述 ,WHERE 子句的功能類似于關系運算中的選擇運算。在例3.14中 , 使用的SQL 語句為 : SELECT Tname, Ttitle

29、, Tsalary FROM T WHERE Tsalary=2500; 3.WHERE 選擇行行選擇條件中常用的運算符如下表：作用運算符比較=,=,=,!=范圍BETWEEN AND, NOT BETWEEN AND集合IN,NOT IN模式匹配LIKE,NOT LIKE空值IS NULL,IS NOT NULL邏輯運算NOT,AND,ORSQL提供的比較運算符主要有：=、!=、 、=，以及 BETWEEN AND。注意： 和！=都表示不等于。SQL允許使用比較運算符比較算術表達式和字符串以及特殊類型，如日期等。 例3.22 查詢所有男學生的信息。 解： SELECT * FROM S WH

30、ERE Ssex= 男;(1) 比較運算SnumSnameSsexSageSphoneDnum0903330001張山男1845673434D0020903330002李波濤男2289457321D0030903330005王勁松男2413098765892D003表 3.10執(zhí)行結果見表3.10例3.22 的運行結果(1) 比較運算 例3.23 查詢成績大于80分的學生學號,課程號,成績。 解： SELECT Snum, Cnum, Score FROM SC WHERE Score80; 執(zhí)行結果見表3.11SnumCnumScore0903330001C004890903330004C0

31、01850903330005C00192表 3.11例3.23 的運行結果(1) 比較運算 例3.24 列出工資在20002600之間的老師姓名和工資情況。 解： SELECT Tname, Tsalary FROM T WHERE Tsalary BETWEEN 2000 AND 2600； 執(zhí)行結果見表3.12：TnameTsalary梁文博2600李剛2200表3.12例3.24 的運行結果(1) 比較運算SQL 中的邏輯運算符有 :NOT非 ( 取反)AND與 ( 并且 )OR 或 ( 或者 )如果兩個邏輯運算符同時出現(xiàn)在同一個WHERE子句中 , 則 NOT 的優(yōu)先級最高 ,AND的

32、優(yōu)先級高于 OR, 當然也可以通過括號改變優(yōu)先級。(2) 邏輯運算 例3.25 查出年齡不大于18歲的同學的學號、姓名、年齡。 解：SELECT Snum, Sname,SageFROM SWHERE NOT Sage18;執(zhí)行結果見表3.13SnumSnameSage0903330003岳嵐170903330004王燕170903330006趙玉蘭16表3.13例3.25 的運行結果(2) 邏輯運算 例3.26 查詢工資在1960年1月1日以前和1970年12月30 日以后出生的教師信息。 解： SELECT * FROM T WHERE Tbirth1970-12-31; 執(zhí)行結果見表3.

33、14：TnumTnameTsexTbirthTtitleTsalaryTphoneDnumT003李剛男1979-05-23講師220013809097865D003T004王森林男1950-11-03教授350056784325D001T005余雪梅女1979-03-23助教188013387650643D001表3.14例3.26 的運行結果(2) 邏輯運算 空值 NULL 是一個特殊的數據 , 在基本表中 ,如果某個列中沒有輸入數據 , 則它的值就為空。如果要判斷某一列的值是否為空 , 不能使用比較運算符 = 和 , 而應該使用 IS NULL 或 IS NOT NULL 運算符。 (3

34、) 空值條件NULL的意義 再次說明，空值NULL是一個特殊值，它看起來和任何其它值一樣，但它與一般意義上的值又不同： NULL的值是未知的，應該有某個值屬于它， 但不知道。 NULL的值是無意義的，只是表示一種空缺。 NULL不是常量。盡管NULL值可以出現(xiàn)在元組 中，但不能顯式地將NULL作為操作數使用。 NULL值與其它任何值(包括另一個NULL值) 進行算術運算，其結果仍為NULL。 NULL值與其它任何值(包括另一個NULL值) 進行比較運算，其結果為FALSE。 即使兩個同名分量的值都是NULL值，也不 能認為它們相等。例如，當我們連接兩個 關系時，并不認為兩個NULL分量彼此相等

35、。NULL的意義SnumSname0903330003岳嵐0903330006趙玉蘭 例3.27 列出所有沒有登記聯(lián)系電話的同學的姓名和學號。 解： SELECT Snum, Sname FROM S WHRER Sphone IS NULL; 執(zhí)行結果見表3.15表3.15例3.27 的運行結果NULL的意義運算符 IN 可以用來表示數據是否屬于指定集合 ,其語法格式為 : NOT IN其中 的格式為 ( 元素值 , 元素值 ), 也就是說在SQL中 , 集合的表示法是用圓括號括起來的一組元素值 , 元素值之間使用逗號隔開。與 IN 相對的運算符是 NOT IN, 用于表示數據不屬于指定集合

36、。 (4) 集合運算例3.28 查詢 張山和李波濤兩位學生的年齡情況。SELECT Sname, Sage FROM SWHERE Sname IN (張山, 李波濤);等價于SELECT Sname, Sage FROM SWHERE Sname= 張山 OR Sname= 李波濤;執(zhí)行結果見表3.16。SnameSage張山18李波濤22表3.16例3.28 的運行結果(4) 集合運算運算符 LIKE 可以用來進行字符串的模式匹配 ,其語法格式為 : NOT LIKE 其含義是查找指定的屬性列值與 相匹配的行 , 可以是一個普通的字符串 ( 此時的 LIKE 運算就等同于 = 運算 , 也

37、可以含有通配符 % 和_。含義分別為：% ( 百分號)：代表任意長度的字符串。_ (下劃線)： 代表任意的單個字符。(5) 字符串的模式匹配例3.29 列出所有姓王并且姓名為兩個字的學生的學號、姓名和年齡。解：SELECT Snum, Sname, Sage FROM SWHERE Sname LIKE 王_;執(zhí)行結果見表3.17SnumSnameSage0903330004王燕17表3.17例3.29 的運行結果(5) 字符串的模式匹配例3.30 查詢聯(lián)系電話不是133開頭的教師的姓名和電話。解：SELECT Tname, Tphone FROM TWHERE Tphone NOT LIKE

38、 133%;執(zhí)行結果見表3.18TnameTphone梁文博8765078王森林56784325表3.18例3.30 的運行結果(5) 字符串的模式匹配 使用 ORDER BY 子句來指定按照一個或多個屬性列的升序(ASC) 或降序 (DESC) 重新排列查詢結果中的行 ,其中升序(ASC)為默認值。格式如下： ORDER BY 列名1ASC|DESC, 列名2ASC|DESC, 首先排列按列名1的值，若列名1的值相等則按列名2的值排列，依次類推。 (6) ORDER BY排序例3.31 按照年齡從小到大列出所有學生的學號、姓名、年齡。SEL

39、ECT Snum,Sname, Sage FROM S ORDER BY Sage;執(zhí)行結果見表3.19Snum Sname Sage 0903330006趙玉蘭16 0903330003 岳嵐170903330004 王燕170903330001張山180903330002李波濤220903330005王勁松24表3.19例3.31 的運行結果(6) ORDER BY排序例3.32 查詢教師的姓名、職稱和工資。查詢結果按照職稱排列，職稱相同的按照工資從多到少排列。SELECT Tname, Ttitle, Tsalary FROM TORDER BY Ttitle, Tsalary DESC

40、;執(zhí)行結果見表3.20TnameTtitleTsalary方維偉副教授2680梁文博副教授2600李剛講師2200王森林教授3500王芳教授3200余雪梅助教1880表3.20例3.32 的運行結果(6) ORDER BY排序一個數據庫中的多個表之間一般都存在某種內在聯(lián)系 , 它們共同提供有用的信息。前面介紹的簡單查詢都是針對一個表進行的, 而在實際使用中 , 許多有用的查詢要求從兩個或更多的表中取得數據 , 這種查詢就被稱為多表查詢。 3.3.2 多表查詢 連接查詢是指兩個或兩個以上的基本表或視圖的連接操作來實現(xiàn)的查詢。SQL提供了一種簡單的方法把幾個關系連到一個查詢中，即在FROM子句中列

41、出每個關系，然后在SELECT子句和WHERE子句中引用FROM子句中的關系的屬性，而WHERE子句中用來連接兩個關系的條件稱為連接條件。 FROM子句中多個表可以用逗號或者CROSSJOIN分隔。還有一種連接方式采用JOIN ON進行條件連接。 1. 連接查詢 當一個查詢請求涉及到數據庫中的多個表時 , 必須按照一定的條件把這些表連接在一起 , 以便能夠共同提供用戶需要的信息。用來連接兩個表的條件稱為連接條件 , 其一般格式為：. 其中的比較運算符主要有： = 、 、 = 、 = 、 。 1. 連接查詢例3.33 查詢各個系中所有老師的姓名和職稱。解：系的情況存放在院系表D中 , 而教師的情

42、況存放在表T中 , 所以本查詢實際上同時涉及 D 表和 T表。這兩張表的聯(lián)系是通過兩張表具有的屬性列Dnum和Dnum實現(xiàn)的。要查詢教師及其所在系的情況 , 就必須將這兩個表中Dnum和Dnum相同的行連接起來。 這是一個標準的等值連接 , 連接條件是D.Dnum=T.Dnum,在標準 SQL 中 , 連接條件在WHERE 子句中給出 , 所以完成本查詢的 SQL 語句為: 1. 連接查詢SELECT D.Dname, T.Tname,T. TtitleFROM D, TWHERE D.Dnum=T.Dnum;執(zhí)行結果見表3.21DnameTnameTtitle計算機梁文博副教授自控王芳教授自

43、控李剛講師計算機王森林教授計算機余雪梅助教通信方維偉副教授表3.21 例3.33 的運行結果 1. 連接查詢例3.33 中的T表和D表是通過“=”連接的，這種連接運算符為“=”的連接稱為等值連接，若用其它比較運算符(如、=、=、)連接，則稱為非等值連接。除了連接條件外，WHERE子句中還可以有其它限制條件，如例3.34。 1. 連接查詢例3.34 查詢自控系所有老師的工資情況。解： SELECT T.Tname, T. Tsalary FROM T, D WHERE T.Dnum=D.Dnum AND D.Dname=自控; 1. 連接查詢執(zhí)行結果見表3.22TnameTsalary王芳320

44、0李剛2200表3.22例3.34 運行結果 1. 連接查詢例3.35 無條件連接院系表D 和教師表T。SELECT D.*, Tnum,Tname,Tsex, Tbirth,TtitleTsalary,TphoneFROM D ,T;執(zhí)行結果 1. 連接查詢DnumDnameDdirectorTnumTnameTsexTbirthTtitleTsalaryTphoneD001計算機王森林T001梁文博男1970-04-09副教授1600.087659078D001計算機王森林T002王芳女1967-08-05教授2200.013556789056D001計算機王森林T003李剛男1979-0

45、5-23講師1200.013809097865D001計算機王森林T004王森林男1950-11-03教授2500.056784325D001計算機王森林T005余雪梅女1979-03-23助教880.013387650643D001計算機王森林T006方維偉男1965-05-03副教授1680.013345678932D002通信方維偉T001梁文博男1970-04-09副教授1600.087659078D002通信方維偉T002王芳女1967-08-05教授2200.013556789056D002通信方維偉T003李剛男1979-05-23講師1200.013809097865D002通

46、信方維偉T004王森林男1950-11-03教授2500.056784325D002通信方維偉T005余雪梅女1979-03-23助教880.013387650643D002通信方維偉T006方維偉男1965-05-03副教授1680.013345678932D003自控王芳T001梁文博男1970-04-09副教授2200.087659078D003自控張小龍T002王芳女1967-08-05教授1200.013556789056D003自控張小龍T003李剛男1979-05-23講師2500.013809097865D003自控張小龍T004王森林男1950-11-03教授880.0567

47、84325D003自控張小龍T005余雪梅女1979-03-23助教1680.013387650643D0033自控張小龍T006方維偉男1965-05-03副教授2200.013345678932例3.36 從學生表S、課程表C和選修表SC三個表中，查詢學生姓名、所修課程及成績。解：SELECT Sname, Cname, scoreFROM S, C, SCWHERE S.Snum=SC.Snum AND SC.Cnum=C.Cnum;二個以上表的連接 執(zhí)行結果見表3.24 SnumSname Cname Score 0903330001張山 C程序設計 530903330001張山 自動

48、控制原理 890903330001張山 數據結構 650903330002李波濤 自動控制原理 75 0903330004王燕 數據庫系統(tǒng)原理 85 0903330005王勁松數據庫系統(tǒng)原理92 0903330005王勁松計算機體系結構76 表3.24例3.36 的運行結果二個以上表的連接 例3.37 查詢所有年齡相同的學生的姓名和年齡。解：SELECT S1.Sname,S2.Sname,S1.SageFROM S S1，S S2WHERE S1. Sage=S2. SageAND S1.SnumS2.Snum;自身連接執(zhí)行結果見表3.25SnameSnameSage岳嵐王燕17表 3.25

49、例3.37 的運行結果自身連接在前面的連接示例中 , 結果集中只保留了符合連接條件的元組 , 而排除了兩個表中沒有對應的或匹配的元組情況 , 這種連接稱為內連接。如果要求查詢結果集中保留非匹配的元組 , 就要執(zhí)行外部連接操作。外連接外連接分類 SQL 的外部連接分左外部連接和右外部連接和全外連接三種 : 左外部連接操作是在結果集中保留連接表達式左表中的非匹配記錄 ；右外部連接操作是在結果集中保留連接表達式右表中的非匹配記錄。外部連接中不匹配的列用NULl表示。SQL 中左外部連接符號為 *=,右外部連接符號為 =* 。外部連接中不匹 配的列用 NULL 表示。SQL-2006標準所定義的FRO

50、M子句的連接語法格式為：FROM join_table1 join_type join_table2 ON (join_condition)。其中join_table1和join_table2指出參與連接操作的表名，連接可以對同一個表操作，也可以對多表操作，對同一個表操作的連接就是自身連接。join_type 指出連接類型是內連接還是外連接。 內連接用INNER JOIN或者JOIN.外連接分類外連接分為左外連接(LEFT OUTER JOIN或LEFT JOIN)、右外連接(RIGHT OUTER JOIN或RIGHT JOIN)和全外連接(FULL OUTER JOIN或FULL JOIN

51、)三種。 連接操作中的ON (join_condition) 子句指出連接條件，它由被連接表中的列和比較運算符、邏輯運算符等構成。 外連接分類例3.38 查詢各個系中所有老師的系名、系編號、姓名、教師號和職稱，用左連接完成。解:SELECT D.Dname,T.Tnum, T.Tname,T. TtitleFROM DLEFT OUTER JOIN ON TON D.Dnum=T.Dnum;外連接分類執(zhí)行結果見表3.25DnameTnumTnameTtitle計算機T001梁文博副教授計算機T004王森林教授計算機T005余雪梅助教通信T006方維偉副教授自控T002王芳教授自控T003李剛講

52、師外語NULLNULLNULL表3.25例3.38 的運行結果外連接分類1. 聚合函數聚合(Aggregation)是指把關系中的某一列的一系列的值形成單一值的運算。它是對關系整體的一種運算。SQL提供了五個聚合函數，可以用于列信息的統(tǒng)計或匯總。 COUNT(DISTINCT|ALL * ) 統(tǒng)計一列中的值的個數 AVG(DISTINCT |ALL) 求某一列值的平均值 ( 此列必須是數值型 ) 3.3.3 聚合和分組 SUM(DISTINCT |ALL)求某一列值的總和 ( 此列必須是數值型 ) MIN(DISTINCT |ALL)求某一列值的最小值 MAX(DISTINCT |ALL)求某

53、一列值的最大值如果指定 DISTINCT, 則表示在計算時忽略指定列的重復值。如果不指定 DISTINCT或指定了 ALL (ALL 為默認值 ), 則不忽略重復值。1. 聚合函數（無列名）6 例3.39 查詢學生總數。解： SELECT COUNT(distinct snum) FROM Sc; 執(zhí)行結果： 這里的星號(*)表示整個元組。這是COUNT()函數特有的，表示統(tǒng)計元組的個數，其它聚合函數的作用超過一列則是沒有意義的。注意星號（*）和DISTINCT|ALL 不能同時出現(xiàn)。 1. 聚合函數例3.40 對教師的工資差異進行比較，找到最高工資和最低工資。解： SELECT MAX(Ts

54、alary) AS 最高工資, MIN(Tsalary) AS 最低工資 FROM T;執(zhí)行結果如下:最高工資最低工資 350018801. 聚合函數前面的幾個例子完成的是對表中所有選出的元組進行統(tǒng)計運算,其計算結果中只有一行,但在實際應用中,經常需要將查詢結果進行分組,然后再對每個分組進行統(tǒng)計,如:按系統(tǒng)計學生人數,按工廠統(tǒng)計工人平均工資等。這些查詢的結果中一般都會有多行。SQL語言提供了GROUP BY子句和HAVING子句來實現(xiàn)分組統(tǒng)計。2.分組統(tǒng)計例3.41計算不同職稱的老師的工資情況。SELECT Ttitle AS 職稱, AVG(Tsalary) AS 平均工資,count(tn

55、um) as FROM TGROUP BY Ttitle;執(zhí)行結果見表3.27人數職稱平均工資2副教授26401講師22002教授33501助教1880表3.27例3.41 的運行結果2.分組統(tǒng)計例3.42 按照性別統(tǒng)計學生人數。解： SELECT Ssex, COUNT(*) FROM S GROUP BY Ssex;執(zhí)行結果見表3.28Ssex(無列名)男3女3表3.28例3.42 的運行結果2.分組統(tǒng)計例3.42 每個學生可能選了多門課程，查詢每個學生的學號、姓名和相應的課程平均成績。SELECT S.Snum, Sname,AVG(score)FROM S,SCWHERE S.Snum

56、=SC.SnumGROUP BY S.Snum,sname;執(zhí)行結果:2.分組統(tǒng)計Snum Sname (無列名) S001 張山 69.0 S002李新 75.0 S004 王燕 85.0 S005 王力 84.0 2.分組統(tǒng)計例3.43 統(tǒng)計各系的學生人數 , 顯示各系的名稱和學生人數。解: 本例需要使用多表查詢 ,SQL 語句為:SELECT D.Dname AS 系,COUNT(*) AS 學生人數FROM D,SWHERE D.Dnum=S.DnumGROUP BY D.Dname在分組查詢中使用多表連接 , 進行統(tǒng)計2.分組統(tǒng)計執(zhí)行結果 系 學生人數 計算機 3通信 1自控 22.

57、分組統(tǒng)計有時我們希望對所分的組按某一條件進行選擇，可以用HAVING子句實現(xiàn)。例3.44 查詢平均成績低于80分的學生學號。 SELECT Snum FROM SC GROUP BY Snum HAVING AVG(score) 、 = 、 = 、 等.2. 使用比較運算符的子查詢例3.49 列出工資低于教師平均工資的老師的編號、姓名、工資情況。SELECT Tnum,Tname,Tsalary FROM T WHERE TsalaryANY R為真，否則為假；若s比R中至少一個值小，則sALL R為真，否則為假；若s比R中每個值小，則sALL R為真，否則為假。同理其它比較運算符可與ALL搭

58、配。如sANY R和s NOT IN R是相同的，可以互相替換，但一般多采用NOT IN，因為容易理解。2. 使用比較運算符的子查詢例350 查詢教師中工資比自控系任一教師低的教師編號、姓名和工資。SELECT Tnum,Tname, Tsalary FROM T WHERE Tsalary ANY (SELECT Tsalary FROM T WHERE Dnum IN (SELECT Dnum FROM D WHERE Dname=自控 )2. 使用比較運算符的子查詢執(zhí)行結果：TnumTnameTsalaryT001梁文博2600.0T003李剛2200.0T005余雪梅1880.0T00

59、6方維偉2680.02. 使用比較運算符的子查詢例 3.51查詢年齡最低的學生的學號，姓名和年齡 SELECT Snum,Sname,SageFROM SWHERE Sage 19) 5 組合查詢SnumSname0903330001張山0903330002李波濤0903330004王燕0903330005王勁松從上面的例子可以看出，UNION 對查詢塊 1 和查詢塊 2 的結果集合并為一個結果集 , 要求這兩個結果集的結構相同。所謂結構相同就是指集合中列的個數、類型和順序必須一樣。 5 組合查詢 (2) 交操作交操作(INTERSECT)是把同時出現(xiàn)在這些查詢結果中的元組取出。INTERSE

60、CT操作也會自動去掉重復元組，如果要保留所有重復，必須用INTERSECT ALL代替INTERSECT。因為交操作可以在WHERE子句中加一邏輯運算符AND簡單實現(xiàn)，所以實際上很少直接用到交操作。 5 組合查詢 (3) 差操作差操作(MINUS)是把出現(xiàn)在第一個查詢結果中，但不出現(xiàn)在第二個查詢結果中的元組取出。因為差也可以在WHERE子句中加一邏輯運算符AND簡單實現(xiàn)，只是將某個邏輯條件取反即可，所以實際上也很少直接用到交操作。 5 組合查詢遞歸查詢的概念是新標準SQL3中引入的，SQL3 實現(xiàn)遞歸查詢的方法是以關系邏輯為基礎的。在SQL3 中 , 可以通過關鍵字WITH 引導的語句來實現(xiàn)關