普通高等教育十一五規(guī)劃教材數(shù)據(jù)庫(kù)原理教程8/30/2019數(shù)據(jù)庫(kù)原理教程1范明、葉陽(yáng)東、邱保志、職為梅編著科學(xué)出版社，2008第4章

關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL8/30/2019數(shù)據(jù)庫(kù)原理教程28/30/2019數(shù)據(jù)庫(kù)原理教程3第4章

關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL4.1引言4.2數(shù)據(jù)定義4.3數(shù)據(jù)查詢4.4數(shù)據(jù)更新4.5視圖4.6嵌入式SQL8/30/2019數(shù)據(jù)庫(kù)原理教程44.1引言引言8/30/2019數(shù)據(jù)庫(kù)原理教程5SQL是Structured

Query

Language的縮寫(xiě)，意為結(jié)構(gòu)化查詢語(yǔ)言，是關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言SQL是一個(gè)通用的、功能極強(qiáng)的關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言背景SQL語(yǔ)言最

稱為Sequel，是Boyce和Chamberlin1974年提出的。Sequel不斷發(fā)展，并更名為SQL由于SQL1974年在IBM公司的System

R上實(shí)現(xiàn)1986年被美國(guó)國(guó)家標(biāo)準(zhǔn)局（ANSI）批準(zhǔn)為關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言的美國(guó)標(biāo)準(zhǔn)。1987年國(guó)際標(biāo)準(zhǔn)化組織（ISO）通過(guò)這一標(biāo)準(zhǔn)

自SQL-92以來(lái)，SQL標(biāo)準(zhǔn)的規(guī)模開(kāi)始變大（SQL-89標(biāo)準(zhǔn)大約120頁(yè)，

SQL-92標(biāo)準(zhǔn)超過(guò)620頁(yè)，而SQL-99標(biāo)準(zhǔn)多達(dá)1700頁(yè)）

目前，大多數(shù)商品化DBMS支持SQL-92主要部分（初級(jí)標(biāo)準(zhǔn)和部分中、高級(jí)的標(biāo)準(zhǔn)），并在其他方面有一些擴(kuò)展

SQL-99擴(kuò)充太快，過(guò)于龐大，DBMS開(kāi)發(fā)商對(duì)實(shí)現(xiàn)SQL-99似乎不太積極本書(shū)關(guān)于SQL的介紹主要基于SQL-92，也介紹SQL-99的部分功能8/30/2019數(shù)據(jù)庫(kù)原理教程6背景8/30/2019數(shù)據(jù)庫(kù)原理教程7標(biāo)準(zhǔn)大致頁(yè)數(shù)發(fā)布日期SQL/861986.10SQL/89(FIPS

127-1)120頁(yè)1989年SQL/92622頁(yè)1992年SQL991700頁(yè)1999年SQL2003360頁(yè)2003年SQL概述8/30/2019數(shù)據(jù)庫(kù)原理教程8SQL的功能SQL的使用方式SQL的表的類型SQL概述(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程9SQL的功能

SQL的數(shù)據(jù)定義語(yǔ)言（DDL）提供了模式定義、修改和刪除，基本表定義、修改和刪除、域定義修改和刪除

SQL的數(shù)據(jù)操縱語(yǔ)言（DML）提供了數(shù)據(jù)查詢子語(yǔ)言。SQL的數(shù)據(jù)

查詢子語(yǔ)言是關(guān)系完備的，并且具有關(guān)系代數(shù)和關(guān)系演算的雙重特征

SQL

DML不僅包括數(shù)據(jù)查詢，而且包括數(shù)據(jù)更新（數(shù)據(jù)插入、刪除和修改）語(yǔ)句，允許用戶更新數(shù)據(jù)庫(kù)

SQL

DDL還允許用戶定義視圖，并且SQL

DML允許用戶對(duì)視圖進(jìn)行查詢和受限的更新操作SQL概述(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程10SQL的功能(續(xù))

SQL

DDL允許用戶定義各種完整性約束條件，并在數(shù)據(jù)庫(kù)訪問(wèn)時(shí)自動(dòng)檢查，確保數(shù)據(jù)庫(kù)操作不會(huì)破壞完整性約束條件

SQL

DDL還包括授權(quán)定義，用來(lái)定義用戶對(duì)數(shù)據(jù)庫(kù)對(duì)象（基本表、視圖等）的訪問(wèn)權(quán)限，防止非法訪問(wèn)，確保數(shù)據(jù)庫(kù)的安全性SQL還支持事務(wù)，提供了定義事務(wù)開(kāi)始和結(jié)束的語(yǔ)句SQL的使用方式獨(dú)立使用嵌入到通用程序設(shè)計(jì)語(yǔ)言中SQL概述(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程11SQL的表的類型基本表（base

table）■■持久基本表（persistent

basetable）全局臨時(shí)表（global

temporarytable）局部臨時(shí)表（local

temporary

table）導(dǎo)出表SQL的特點(diǎn)8/30/2019數(shù)據(jù)庫(kù)原理教程12SQL的特點(diǎn)集多種數(shù)據(jù)庫(kù)語(yǔ)言于一體高度非過(guò)程化面向集合的操作方式一種語(yǔ)法兩種使用方式功能強(qiáng)大、語(yǔ)言簡(jiǎn)潔SQL的特點(diǎn)(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程131.集多種數(shù)據(jù)庫(kù)語(yǔ)言于一體（綜合統(tǒng)一）非關(guān)系模型的數(shù)據(jù)語(yǔ)言一般分為：模式數(shù)據(jù)定義語(yǔ)言（模式DDL）外模式數(shù)據(jù)定義語(yǔ)言（外模式DDL，子模式DDL）數(shù)據(jù)存儲(chǔ)有關(guān)的描述語(yǔ)言（DSDL）數(shù)據(jù)操縱語(yǔ)言（DML）

當(dāng)用戶數(shù)據(jù)庫(kù)投入運(yùn)行后，如果要修改模式，必須停止運(yùn)行，轉(zhuǎn)儲(chǔ)數(shù)據(jù)，修改模式并編譯后再重裝數(shù)據(jù)庫(kù)SQL的特點(diǎn)(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程141.集多種數(shù)據(jù)庫(kù)語(yǔ)言于一體(續(xù))

SQL語(yǔ)言集數(shù)據(jù)定義語(yǔ)言（DDL），數(shù)據(jù)操縱語(yǔ)言（DML），數(shù)據(jù)控制語(yǔ)言（DCL）功能于一體可以獨(dú)立完成數(shù)據(jù)庫(kù)生命周期中的全部活動(dòng)定義關(guān)系模式，插入數(shù)據(jù)，建立數(shù)據(jù)庫(kù)對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行查詢和更新數(shù)據(jù)庫(kù)重構(gòu)和維護(hù)數(shù)據(jù)庫(kù)安全性、完整性控制等

用戶數(shù)據(jù)庫(kù)投入運(yùn)行后，可根據(jù)需要隨時(shí)逐步修改模式，不影響數(shù)據(jù)的運(yùn)行SQL的特點(diǎn)(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程152.高度非過(guò)程化

非關(guān)系數(shù)據(jù)模型的數(shù)據(jù)操縱語(yǔ)言“面向過(guò)程”，必須使用類似于指針的機(jī)制，指定存取路徑SQL只要提出“做什么”，無(wú)須了解存取路徑。例如，使用SQL語(yǔ)言表達(dá)查詢時(shí)，用戶只需要正確地表達(dá)需要哪些信息這些信息在哪些關(guān)系中結(jié)果元組應(yīng)當(dāng)滿足什么條件系統(tǒng)將考察多種執(zhí)行方案，選擇并運(yùn)行一個(gè)最優(yōu)的執(zhí)行方案大大減輕了用戶負(fù)擔(dān)有利于提高數(shù)據(jù)的獨(dú)立性SQL的特點(diǎn)(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程163.面向集合的操作方式非關(guān)系數(shù)據(jù)模型采用面向記錄的操作方式，操作對(duì)象是一條記錄SQL采用集合操作方式操作對(duì)象、查找結(jié)果可以是元組的集合一次插入、刪除、更新操作的對(duì)象可以是元組的集合SQL的特點(diǎn)(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程174.一種語(yǔ)法兩種使用方式SQL是獨(dú)立的語(yǔ)言能夠獨(dú)立地用于聯(lián)機(jī)交互的使用方式SQL又是嵌入式語(yǔ)言

SQL能夠嵌入到高級(jí)語(yǔ)言（例如C，C++，Java）程序中，供程序員設(shè)計(jì)程序時(shí)使用SQL的特點(diǎn)(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程185.功能強(qiáng)大,語(yǔ)言簡(jiǎn)潔

SQL是一種完整地?cái)?shù)據(jù)庫(kù)語(yǔ)言，其功能涵蓋數(shù)據(jù)定義、數(shù)據(jù)操縱、數(shù)據(jù)控制等數(shù)據(jù)管理的主要需求但SQL語(yǔ)言相對(duì)比較簡(jiǎn)潔，其核心動(dòng)詞只有9個(gè)SQL語(yǔ)言的語(yǔ)法簡(jiǎn)單，與英語(yǔ)口語(yǔ)的風(fēng)格類似，易學(xué)易用8/30/2019數(shù)據(jù)庫(kù)原理教程194.2數(shù)據(jù)定義4.2數(shù)據(jù)定義8/30/2019數(shù)據(jù)庫(kù)原理教程20SQL的數(shù)據(jù)定義語(yǔ)言DDL包括定義模式域（第5章）關(guān)系（SQL稱之為基本表）視圖（4.5節(jié)）索引斷言（第5章）授權(quán)（第5章）SQL的數(shù)據(jù)類型8/30/2019數(shù)據(jù)庫(kù)原理教程21SQL支持許多內(nèi)置的數(shù)據(jù)類型，并允許用戶定義新的域（數(shù)據(jù)）類型SQL支持的數(shù)據(jù)類型CHARACTERCHARACTER

VARYINGBITBIT

VARYINGINTEGERSMALLINTNUMERICSQL的數(shù)據(jù)類型(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程22SQL支持的數(shù)據(jù)類型DECIMALFLOATREALDOUBLE

PRECISIONDATETIMETIMESTAMPINTERVALSQL的數(shù)據(jù)類型(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程23

CHAR(n)：定長(zhǎng)字符串，長(zhǎng)度n由用戶指定。省略(n)時(shí)，長(zhǎng)度為1。CHAR的全稱是CHARACTER。

VARCHAR(n)：變長(zhǎng)字符串，最大長(zhǎng)度n由用戶指定。VARCHAR的全稱是CHARACTER

VARYING

定長(zhǎng)和變長(zhǎng)字符串的差別主要表現(xiàn)在前者需要固定長(zhǎng)度的空間，而后者占用的空間在最大長(zhǎng)度范圍內(nèi)是可改變的。BIT(n)：定長(zhǎng)二進(jìn)位串，長(zhǎng)度n由用戶指定。省略(n)時(shí)，長(zhǎng)度為1。BIT

VARYING(n)：變長(zhǎng)二進(jìn)位串，最大長(zhǎng)度n由用戶指定。INT：整數(shù)，其值域依賴于具體實(shí)現(xiàn)。INT的全稱是INTEGER。SMALLINT：小整數(shù)，其值域依賴于具體實(shí)現(xiàn)，但小于INT的值域SQL的數(shù)據(jù)類型(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程24NUMERIC(p,d)：p位有效數(shù)字的定點(diǎn)數(shù)，其中小數(shù)點(diǎn)右邊占d位

DEC(p,d)：p位有效數(shù)字的定點(diǎn)數(shù)，其中小數(shù)點(diǎn)右邊占d位。DEC的全稱是DECIMALFLOAT(n)：精度至少為n位數(shù)字的浮點(diǎn)數(shù)，其值域依賴于實(shí)現(xiàn)REAL：實(shí)數(shù)，精度依賴于實(shí)現(xiàn)

DOUBLE

PRECISION：雙精度實(shí)數(shù)，精度依賴于實(shí)現(xiàn)，但精度比

REAL高DATE：日期，包括年、月、日，格式為YYYY-MM-DD

TIME：時(shí)間，包括時(shí)、分、秒，格式為HH:MM:SS。TIME(n)可以表示比秒更小的單位，秒后取n位TIMESTAMP：時(shí)間戳，是DATE和TIME的結(jié)合

INTERVAL：時(shí)間間隔。SQL允許對(duì)DATE、TIME和INTERVAL類型的值進(jìn)行計(jì)算SQL的數(shù)據(jù)類型(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程25

SQL提供ETRACT(field

FROM

Var)，從DATE、TIME和TIMESTAMP類型變量Var從提取字段field對(duì)于DATE類型的變量，field可以是YEAR、MONTH和DAY；對(duì)于TIME類型的變量，field可以是HOUR、MINUTE和SECOND；

而對(duì)于TIMESTAMP類型field可以是YEAR、MONTH、DAY、HOUR、MINUTE和SECOND例如，如果d是DATE類型，則ETRACT(YEAR

FROM

d)返回d中的年份定義、修改和刪除基本表8/30/2019數(shù)據(jù)庫(kù)原理教程26說(shuō)明：

本章，我們將“表”和“關(guān)系”視為同義詞（盡管它們實(shí)際上有差別）將“屬性”、“屬性列”和“列”視為同義詞使用“元組”或“記錄”表示表的行

術(shù)語(yǔ)“基本表”主要用于表示持久基本表，而“表”泛指基本表和導(dǎo)出表（包括視圖）定義、修改和刪除基本表(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程27符號(hào)約定<X>表示X是需要進(jìn)一步定義或說(shuō)明語(yǔ)言成分[X]表示X可以缺省或出現(xiàn)一次{X}表示X可以出現(xiàn)一次X

|

Y表示或者X出現(xiàn)，或者Y出現(xiàn)，但二者不能同時(shí)出現(xiàn)SQL語(yǔ)言的保留字（如CREATE）不區(qū)分大小寫(xiě)為醒目起見(jiàn)，對(duì)于SQL語(yǔ)句中的SQL的保留字，我們使用大寫(xiě)SQL語(yǔ)句用分號(hào)結(jié)束

一個(gè)SQL語(yǔ)句可以寫(xiě)在一行或多行中，各種空白符號(hào)用于分隔不同的詞良好的語(yǔ)句的書(shū)寫(xiě)風(fēng)格使得程序賞心悅目、易于閱讀定義基本表8/30/2019數(shù)據(jù)庫(kù)原理教程28

創(chuàng)建一個(gè)基本表要對(duì)基本表命名，定義表的每個(gè)列，并定義表的完整性約束條件。SQL語(yǔ)言使用CREATE

TABLE語(yǔ)句創(chuàng)建基本表，其基本格式如下：

CREATE

TABLE

<表名>(<列定義>,…,<列定義>[,<表約束定義>,…,<表約束定義>])；<表名>是標(biāo)識(shí)符，對(duì)定義的基本表命名

圓括號(hào)中包括一個(gè)或多個(gè)<列定義>，零個(gè)或多個(gè)<表約束定義>，中間用逗號(hào)隔開(kāi)定義基本表(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程29

<列定義>定義每個(gè)屬性（列）的名稱、類型、缺省值和列上的約束條件，格式如下：<列名><類型>[DEFAULT

<缺省值>][<列約束定義>,…,<列約束定義>]其中，<列名>是標(biāo)識(shí)符，對(duì)定義的列命名

<類型>定義列的取值類型，它可以是4.2.1節(jié)介紹的任意類型，也可以是用戶定義的域類型（見(jiàn)5.3節(jié)）可選短語(yǔ)“DEFAULT

<缺省值>”定義列上的缺省值，<缺省值>是<類型>中的一個(gè)特定值或NULL（空值）每個(gè)列上可以定義零個(gè)或或多個(gè)約束條件，約束列的取值定義基本表(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程30列約束定義格式如下[CONSTRAINT

<約束名>]<列約束>其中可選短語(yǔ)“CONSTRAINT

<約束名>”為列約束命名常用的列約束包括：NOT

NULL：不允許該列取空值不加NOT

NULL限制時(shí)，該列可以取空值PRIMARY

KEY：指明該列是主碼，其值非空、唯一UNIQUE：該列上的值必須唯一相當(dāng)于說(shuō)明該列為候選碼

CHECK

(<條件>)：指明該列的值必須滿足的條件，其中<條件>是一個(gè)涉及該列的布爾表達(dá)式定義基本表(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程31

一個(gè)表可以包含零個(gè)或多個(gè)<表約束定義>，用于定義主碼、其他候選碼、外碼和表上的其它約束表約束定義定義形式如下：[CONSTRAINT<約束名>]<表約束>其中可選短語(yǔ)“CONSTRAINT<約束名>”為表約束命名PRIMARY

KEY

(A1

,…,Ak

)：屬性列A1

,…,Ak

構(gòu)成該關(guān)系的主碼當(dāng)主碼只包含一個(gè)屬性時(shí)，也可以用列約束定義主碼。UNIQUE

(A1

,…,Ak

)：屬性列A1

,…,Ak

上的值必須唯一相當(dāng)于說(shuō)明A1

,…,Ak

構(gòu)成該關(guān)系的候選碼當(dāng)候選碼只包含一個(gè)屬性時(shí)，也可以用列約束定義候選碼CHECK

(<條件>)：說(shuō)明該表上的一個(gè)完整性約束條件通常，<條件>是一個(gè)涉及該表一個(gè)或多個(gè)列的布爾表達(dá)式定義基本表(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程32外碼比較復(fù)雜，它具有下形式：FOREIGN

KEY

(A1

,…,Ak

)REFERENCES

<外表名>(<外表主碼>)[<參照觸發(fā)動(dòng)作>]屬性A1

,…,Ak

是關(guān)系（表）的外碼<外表名>給出被參照關(guān)系的表名<外表主碼>給出被參照關(guān)系的主碼<參照觸發(fā)動(dòng)作>說(shuō)明違反參照完整性時(shí)需要采取的措施第5章詳細(xì)討論參照觸發(fā)動(dòng)作定義基本表(續(xù))例4.1下面的語(yǔ)句創(chuàng)建教師表TeachersCREATE

TABLE

Teachers(TnoTnameSexCHAR

(7)

PRIMARY

KEY,CHAR

(10)

NOT

NULL,CHAR(2)CHECK

(Sex=‘男’OR

Sex=‘女’),Birthday

DATE,TitleDnoCHAR

(6),CHAR

(4),FOREIGN

KEY

(Dno)

REFERENCES

Departments

(Dno));長(zhǎng)度為6的字符串長(zhǎng)度為10的字符串，非空長(zhǎng)度為2的字符串，取值“男”或“女”長(zhǎng)度為8的字符串，主碼日期型長(zhǎng)度為4的字符串，外碼，參照Departments的主碼Dno8/30/2019數(shù)據(jù)庫(kù)原理教程33定義基本表(續(xù))創(chuàng)建選課表SC用如下語(yǔ)句：CREATE

TABLE

SC(SnoCnoGradeCHAR

(9),CHAR

(5),SMALLINT

CHECK

(Grade>=0

AND

Grade<=100),PRIMARY

KEY

(Sno,Cno),FOREIGN

KEY

(Sno)

REFERENCES

Students

(Sno),FOREIGN

KEY

(Cno)

REFERENCES

Courses

(Cno));長(zhǎng)度為9的字符串，外碼，參照Students的主碼Sno主碼長(zhǎng)度為5的字符串，外碼，參照Courses的主碼Cno小整數(shù)，在

0

~

100之間取值8/30/2019數(shù)據(jù)庫(kù)原理教程34定義基本表(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程35CREATE

TABLE

Departments(DnoDnameCHAR(4)

PRIMARY

KEY,CHAR

(10),Dheadno

CHAR

(7),FOREIGN

KEY

(Dheadno)

REFERENCES

Teachers(Tno));CREATE

TABLE

Students(SnoSnameSexBirthdayCHAR(9)

PRIMARY

KEY,CHAR(10)

NOT

NULL,CHAR(2)CHECK

(Sex=’男’OR

Sex=’女’),DATE,Enrollyear

CHAR(4),Speciality

CHAR(20),Dno

CHAR

(3),FOREIGN

KEY

(Dno)

REFERENCES

Department

(Dno));定義基本表(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程36CREATE

TABLE

Courses(CnoCnamePeriodCreditCHAR(5)

PRIMARY

KEY,CHAR(20)

NOT

NULL,SMALLINT,SMALLINT);CREATE

TABLE

Teaches(TnoCnoCHAR

(7),CHAR

(5),TCscore

SMALLINT,PRIMARY

KEY

(Tno,Cno),FOREIGN

KEY

(Tno)

REFERENCES

Teachers

(Tno),FOREIGN

KEY

(Cno)

REFERENCES

Courses

(Cno));修改基本表8/30/2019數(shù)據(jù)庫(kù)原理教程37基本表創(chuàng)建好以后，在某些情況下需要修改它的結(jié)構(gòu)。SQL允許添加列定義修改或刪除列的缺省值刪除列添加表約束刪除表約束使用ALTER

TABLE語(yǔ)句修改基本表修改基本表(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程381.向基本表添加新的列ALTER

TABLE

<表名>ADD

[COLUMN]<列定義>COLUMN可以省略（下同）

<列定義>和創(chuàng)建基本表相同，但是新添加的列一般不允許用NOTNULL說(shuō)明

2.對(duì)于已經(jīng)存在的列，SQL-92只允許修改或刪除列的缺省值，語(yǔ)句形式為：ALTER

TABLE

<表名>ALTER

[COLUMN]<列名>{SET

DEFAULT

<缺省值>|

DROP

DEFAULT}SET

DEFAULT<缺省值>以新的缺省值替換原來(lái)的缺省值DROP

DEFAULT刪除缺省值修改基本表(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程393.刪除已存在的列ALTER

TABLE

<表名>DROP

[COLUMN

]<列名>{CASCADE

|

RESTRICT}CASCADE表示級(jí)聯(lián)，刪除將成功，并且依賴于該列的數(shù)據(jù)庫(kù)對(duì)象（如涉及該列的視圖）也一并刪除

RESTRICT表示受限，僅當(dāng)沒(méi)有依賴于該列的數(shù)據(jù)庫(kù)對(duì)象時(shí)刪除才能成功4.添加表約束ALTER

TABLE

<表名>ADD

<表約束定義>其中<表約束定義>與創(chuàng)建基本表相同修改基本表(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程405.刪除表約束ALTER

TABLE

<表名>DROP

CONSTRAINT

<約束名>{CASCADE

|

RESTRICT}其中被刪除的約束一定是命名的約束，給出約束名CASCADE導(dǎo)致刪除約束并且同時(shí)刪除依賴于該約束的數(shù)據(jù)庫(kù)對(duì)象RESTRICT僅當(dāng)不存在依賴于該約束的數(shù)據(jù)庫(kù)對(duì)象時(shí)才刪除該約束修改基本表(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程41

例4.2在Courses中增加一個(gè)新列Pno，表示課程的先行課的課程號(hào)，可以用ALTER

TABLE

Courses

ADD

Pno

CHAR

(5);

在Students的Sex列設(shè)置缺省值“女”可以減少大約一半學(xué)生性別的輸入?？梢杂萌缦抡Z(yǔ)句來(lái)設(shè)置缺省值：ALTER

TABLE

Students

ALTER

Sex

SET

DEFAULT

‘女’;而刪除Sex上的缺省值可以用ALTER

TABLE

Students

ALTER

Sex

DROP

DEFAULT;刪除Courses中的Pno列可以用

ALTER

TABLE

Courses

DROP

Pno;刪除基本表8/30/2019數(shù)據(jù)庫(kù)原理教程42

當(dāng)不需要某個(gè)基本表時(shí)，可以使用DROP

TABLE語(yǔ)句將它刪除。語(yǔ)句格式為：DROP

TABLE

<表名>{CASCADE∣RESTRICT}CASCADE表示及聯(lián)刪除依賴于表的數(shù)據(jù)對(duì)象（最常見(jiàn)的是視圖）也將一同被刪除RESTRICT表示受限刪除

如果基于該表定義有視圖，或者有其他表引用該表（如CHECK、FOREIGN

KEY等約束），或者該表有觸發(fā)器、存儲(chǔ)過(guò)程或函數(shù)等，則不能刪除刪除基本表導(dǎo)致存放在表中的數(shù)據(jù)和表定義都將被徹底刪除刪除基本表(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程43例4.3如果用如下語(yǔ)句刪除SC表 DROP

TABLE

SC

RESTRICT;則僅當(dāng)沒(méi)有依賴于SC的任何數(shù)據(jù)庫(kù)對(duì)象刪除才能成功如果用DROP

TABLE

SC

CASCADE;則表SC和依賴它的數(shù)據(jù)庫(kù)對(duì)象都被徹底刪除

注意：基本表一旦被刪除，這種刪除是永久的，不可恢復(fù)的?；颈碇械臄?shù)據(jù)及在該表上建立的視圖、索引將全部被刪除掉執(zhí)行刪除基本表的操作時(shí)要格外小心創(chuàng)建和刪除索引8/30/2019數(shù)據(jù)庫(kù)原理教程44索引類似于書(shū)的目錄索引可以加快表之間的連接速度，加快表的排序和分組工作索引屬于物理存儲(chǔ)的路徑概念，而不是邏輯的概念

索引由DBA或表的屬主負(fù)責(zé)建立和刪除，其他用戶不能隨意建立和刪除索引索引由DBMS自動(dòng)選擇使用和維護(hù)索引通常分為唯一性索引（每一個(gè)索引值對(duì)應(yīng)一個(gè)數(shù)據(jù)行）和非唯一性索引聚族索引和非聚族索引創(chuàng)建索引8/30/2019數(shù)據(jù)庫(kù)原理教程45通常，DBMS自動(dòng)為主碼建立索引其他索引需要用CREATE

INDEX語(yǔ)句創(chuàng)建創(chuàng)建索引的語(yǔ)句格式為：CREATE

[UNIQUE][CLUSTER]INDEX

<索引名>ON

<表名>(<列名>[<次序>]{,<列名>[<次序>]})<索引名>為建立的索引命名<表名>是要建立索引的基本表的名字

索引可以建在該表的一列或多列上，各列名間用逗號(hào)分隔；每個(gè)<列名>后可以用<次序>指定索引值的排列次序次序可以是ASC（升序）和DESC（降序），缺省值為ASC創(chuàng)建索引(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程46

UNIQUE表示該索引為唯一性索引。UNIQUE缺省時(shí)，創(chuàng)建的索引為非唯一性索引CLUSTER表示建立的索引是聚簇索引，缺省時(shí)為非聚簇索引創(chuàng)建索引不僅創(chuàng)建索引結(jié)構(gòu)，而且將索引的定義存儲(chǔ)在數(shù)據(jù)字典中創(chuàng)建索引(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程47例4.4在Students的Dno上創(chuàng)建一個(gè)名為Student_Dept的索引

CREATE

INDEX

Student_Dept

ON

Students(Dno);在Teachers上的Dno創(chuàng)建一個(gè)名為T(mén)eacher_Dept的聚簇索引

CREATE

CLUSTER

INDEX

Teacher_Dept

ON

Teachers(Dno);

注意：學(xué)生流動(dòng)性比較大，Students更新頻繁，不適合創(chuàng)建聚簇索引；而教師相對(duì)穩(wěn)定，可以考慮按所在院系在Teachers上創(chuàng)建聚簇索引刪除索引8/30/2019數(shù)據(jù)庫(kù)原理教程48

索引—旦建立，就由系統(tǒng)來(lái)選擇和維護(hù)，無(wú)需用戶干預(yù)，但當(dāng)刪除一些不必要的索引時(shí)，可用下列語(yǔ)句來(lái)實(shí)現(xiàn)：DROP

INDEX

<索引名>

刪除索引時(shí)，系統(tǒng)將刪除索引結(jié)構(gòu)，并同時(shí)從數(shù)據(jù)字典中刪去有關(guān)該索引的定義例4.5刪除索引Student_Dept DROP

INDEX

Student_Dept；模式的定義和刪除8/30/2019數(shù)據(jù)庫(kù)原理教程49支持SQL的DBMS提供了一個(gè)SQL環(huán)境（SQL-environment）。SQL環(huán)境包括零個(gè)或多個(gè)目錄零個(gè)或多個(gè)用戶標(biāo)識(shí)符（稱作授權(quán)標(biāo)識(shí)符）零個(gè)或多個(gè)模塊和目錄中的模式描述的SQL數(shù)據(jù)DBMS為關(guān)系的命名提供了一個(gè)三級(jí)層次結(jié)構(gòu)頂層由目錄（catalog）組成每個(gè)目錄中包含一些模式（schema）而SQL對(duì)象（關(guān)系、視圖等）都包含在模式內(nèi)模式的定義和刪除(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程50注意：SQL環(huán)境中的目錄不能重名同一目錄下的模式不能重名同一模式下的關(guān)系不能重名一個(gè)關(guān)系由目錄名、模式名和關(guān)系名唯一確定，例如

Catalog2.Supply-schema.Suppliers確定Catalog2目錄下Supply-schema模式中的Suppliers關(guān)系

如果關(guān)系在默認(rèn)目錄的默認(rèn)模式中，則可以省略目錄名和模式名前綴模式的定義和刪除(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程51為了進(jìn)行數(shù)據(jù)庫(kù)操作，首先必須連接到數(shù)據(jù)庫(kù)

當(dāng)用戶（程序）連接到數(shù)據(jù)庫(kù)時(shí)，系統(tǒng)為該連接建立一個(gè)默認(rèn)的目錄和模式目錄的創(chuàng)建、設(shè)置和刪除依賴于具體實(shí)現(xiàn)，不包含在SQL標(biāo)準(zhǔn)中可以用CREATE

SCHEMA和DROP

SCHEMA創(chuàng)建和刪除模式創(chuàng)建模式8/30/2019數(shù)據(jù)庫(kù)原理教程52誰(shuí)有權(quán)創(chuàng)建模式依賴于實(shí)現(xiàn)

通常，DBMS規(guī)定只有DBA和或經(jīng)DBA授權(quán)創(chuàng)建模式的用戶才能創(chuàng)建模式創(chuàng)建模式的語(yǔ)句有兩種第一種格式CREATE

SCHEMA

<模式名>[<模式元素>…]

創(chuàng)建一個(gè)以<模式名>命名的模式，并可以在創(chuàng)建模式的同時(shí)為該模式創(chuàng)建或不創(chuàng)建模式元素<模式元素>可以是表定義、視圖定義、斷言定義、授權(quán)定義等

這種格式?jīng)]有授權(quán)其他用戶訪問(wèn)創(chuàng)建的模式，以后可以用授權(quán)語(yǔ)句授權(quán)創(chuàng)建模式(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程53第二種格式CREATE

SCHEMA

[<模式名>]AUTHORIZATION

<用戶名>[<模式元素>…]與第一種的區(qū)別在于它將創(chuàng)建的模式授權(quán)予<用戶名>指定的用戶當(dāng)<模式名>缺省時(shí)，用<用戶名>作為模式名實(shí)際上，創(chuàng)建一個(gè)模式就相當(dāng)于創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)微軟的SQL

Server用CREATE

DATABSE創(chuàng)建模式(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程54例4.6為WangQiang創(chuàng)建一個(gè)名為Supply_schema的模式：CREATE

SCHEMA

Supply_schema

AUTHORIZATION

WangQiang;所創(chuàng)建的模式在當(dāng)前目錄下，并為WangQiang所擁有語(yǔ)句CREATE

SCHEMA

Supply_schema;創(chuàng)建一個(gè)名為Supply_schema的模式，但未向任何用戶授權(quán)語(yǔ)句CREATE

SCHEMA

AUTHORIZATION

WangQiang;為WangQiang創(chuàng)建一個(gè)模式，并用WangQiang命名創(chuàng)建模式(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程55還可以在創(chuàng)建模式的同時(shí)創(chuàng)建該模式中的對(duì)象例如CREATE

SCHEMA

Supply_schemaCREATE

TABLE

Suppliers(SnoSnameStatusCHAR(5)

PRIMERY

KEY,CHAR(20)

NOT

NULL,SMALLINT,Address

CHAR(30),Phone

CHAR(10));

在創(chuàng)建模式Supply_schema的同時(shí)還在該模式中定義了一個(gè)基本表

Suppliers刪除模式8/30/2019數(shù)據(jù)庫(kù)原理教程56DBA和模式的擁有者可以用DROP

SCHEMA刪除模式刪除模式的語(yǔ)句格式為：DROP

SCHEMA

<模式名>CASCADE∣RESTRICT其中CASCADE和RESTRICT兩者必須選擇其一

CASCADE：刪除<模式名>指定模式得同時(shí)并刪除該模式中的所有數(shù)據(jù)庫(kù)對(duì)象（基本表、視圖、斷言等）

RESTRICT：僅當(dāng)<模式名>指定的模式不包含任何數(shù)據(jù)庫(kù)對(duì)象時(shí)才刪除指定的模式，否則拒絕刪除刪除模式(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程57例4.7語(yǔ)句DROP

SCHEMA

Supply_schema

RESTRICT

僅當(dāng)模式Supply_schema中不包含任何數(shù)據(jù)庫(kù)對(duì)象時(shí)，才刪除模式

Supply_schema，否則什么也不做語(yǔ)句DROP

SCHEMA

Supply_schema

CASCADE

將直接刪除模式Supply_schema，并同時(shí)刪除該模式中所有的數(shù)據(jù)庫(kù)對(duì)象8/30/2019數(shù)據(jù)庫(kù)原理教程584.3數(shù)據(jù)查詢數(shù)據(jù)查詢8/30/2019數(shù)據(jù)庫(kù)原理教程59查詢是數(shù)據(jù)庫(kù)的最重要的操作在SQL中，所有查詢都用SELECT語(yǔ)句實(shí)現(xiàn)查詢?cè)谝粋€(gè)或多個(gè)關(guān)系（基本表或視圖）上進(jìn)行，其結(jié)果是一個(gè)關(guān)系數(shù)據(jù)查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程60SELECT語(yǔ)句的一般形式不帶WHERE的簡(jiǎn)單查詢帶WHERE子句的查詢排序和分組連接查詢嵌套查詢子查詢導(dǎo)出的表集合運(yùn)算SELECT語(yǔ)句的一般形式8/30/2019數(shù)據(jù)庫(kù)原理教程61SELECT語(yǔ)句的一般形式如下：SELECT

[ALL︱DISTINCT]<選擇序列>FROM

<表引用>,…,<表引用>[WHERE

<查詢條件>][GROUP

BY

<分組列>{,<分組列>}[HAVING

<分組選擇條件>]][ORDER

BY

<排序列>[ASC︱DESC]{,<排序列>[ASC︱DESC]}]

其中最基本的結(jié)構(gòu)是SELECT-FROM-WHERE，并且SELECT子句和FROM子句是必須的，其他子句都是可選的

我們先介紹SELECT語(yǔ)句的基本結(jié)構(gòu)，稍后再詳細(xì)介紹GROUP

BY子句和ORDER

BY子句SELECT語(yǔ)句8/30/2019數(shù)據(jù)庫(kù)原理教程621.SELECT子句

相當(dāng)于關(guān)系代數(shù)的投影運(yùn)算（更準(zhǔn)確地說(shuō)，相當(dāng)于廣義投影），用來(lái)列出查詢結(jié)果表的諸列SELECT后可以使用集合量詞ALL或DISTINCT，缺省時(shí)為ALLALL不刪除結(jié)果的重復(fù)行DISTINCT將刪除結(jié)果中的重復(fù)行ALL或DISTINCT作用于所有列而不是一個(gè)列<選擇序列>有兩種形式列舉查詢結(jié)果的每個(gè)列*SELECT語(yǔ)句(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程631.SELECT子句<選擇序列>列舉查詢結(jié)果的每個(gè)列

結(jié)果列的次序可以任意指定，列之間用逗號(hào)隔開(kāi)。每個(gè)結(jié)果列具有如下形式<值表達(dá)式>[[AS]<列名>]<值表達(dá)式>最常見(jiàn)形式為

[<表名>.]<列名><列名>必須出現(xiàn)在FROM子句指定的表中

在不會(huì)引起混淆（即<列名>只出現(xiàn)在一個(gè)表中）時(shí)，<列名>前的可選前綴“<表名>.”可以缺省<表名>是<列名>所在表的名字或別名<選擇序列>可以是“*”SELECT語(yǔ)句(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程642.FROM子句

相當(dāng)于關(guān)系代數(shù)的笛卡爾積運(yùn)算，用來(lái)列出查詢需要掃描的基本表或?qū)С霰鞦ROM子句中可以有一個(gè)或多個(gè)<表引用>，中間用逗號(hào)隔開(kāi)<表引用>的最常見(jiàn)形式是：<表名>[[AS]<表別名>]其中，<表名>是合法的表名；

可選短語(yǔ)“AS<表別名>”（可省略AS）用來(lái)對(duì)表起別名（在SELECT-FROM-WHERE結(jié)構(gòu)中有效）

當(dāng)同一個(gè)表在SELECT-FROM-WHERE結(jié)構(gòu)中重復(fù)出現(xiàn)時(shí)，使用別名可以很好地區(qū)分它們的不同出現(xiàn)

當(dāng)列名前需要帶表名前綴時(shí)，使用較短的別名也可以簡(jiǎn)化語(yǔ)句書(shū)寫(xiě)SELECT語(yǔ)句(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程653.WHERE子句

WHERE子句相當(dāng)于關(guān)系代數(shù)中的選擇運(yùn)算，<查詢條件>是作用于

FROM子句中的表和視圖的選擇謂詞WHERE子句缺省時(shí)等價(jià)于WHERE

TRUE（即查詢條件為恒真條件）<查詢條件>可以非常復(fù)雜通過(guò)大量例子介紹基本SELECT語(yǔ)句的執(zhí)行相當(dāng)于首先，求FROM子句指定的基本表或?qū)С霰淼牡芽柗e然后，根據(jù)WHERE子句的查詢條件從中選擇滿足查詢條件的元組最后，投影到SELECT子句的結(jié)果列上，得到查詢的回答不帶WHERE的簡(jiǎn)單查詢8/30/2019數(shù)據(jù)庫(kù)原理教程66最簡(jiǎn)單的SELECT語(yǔ)句是只包括SELECT和FROM子句這種語(yǔ)句只能完成對(duì)單個(gè)表的投影運(yùn)算。例4.8查詢所有課程的信息可以用：

SELECT

Cno,Cname,Period,Credit FROM

Courses;或簡(jiǎn)單地用：SELECT

*FROM

Courses;不帶WHERE的簡(jiǎn)單查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程67

用“*”來(lái)表示所有的屬性列時(shí)，得到的查詢表的屬性次序?qū)⒑捅矶x的屬性次序一致

如果用第一種形式，屬性的次序不必與表屬性的定義次序一致，并且允許只顯示我們感興趣的某些屬性例如，下面的語(yǔ)句將顯示每門(mén)課程的課程號(hào)和學(xué)分：SELECT

Cno,

CreditFROM

Courses;不帶WHERE的簡(jiǎn)單查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程68SELECT子句中的列可以是表達(dá)式

例4.9假定當(dāng)前年份為2007，并且假設(shè)我們定義了一個(gè)函數(shù)year(d)，它返回DATE類型的參數(shù)d中的年份。下面的語(yǔ)句將顯示每位學(xué)生的年齡：SELECT

2007﹣year(Birthday)

AS

AgeFROM

Students;

AS

Age（可以省略AS）用Age對(duì)表達(dá)式2007﹣year(Birthday)重新命名，導(dǎo)致結(jié)果為單個(gè)屬性Age的表

如果去掉AS

Age，則查詢結(jié)果的列名為2007﹣year(Birthday)。該查詢將顯示每位學(xué)生的年齡

由于學(xué)生的人數(shù)眾多，這可能是一個(gè)很長(zhǎng)的年齡列表，其中幾乎每個(gè)值都重復(fù)出現(xiàn)很多次不帶WHERE的簡(jiǎn)單查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程69

SQL允許我們用DISTINCT短語(yǔ)強(qiáng)制刪除重復(fù)元組。下面的語(yǔ)句將所有顯示所有學(xué)生的不同年齡：SELECT

DISTINCT

2007﹣year(Birthday)

AgeFROM

Students;帶WHERE的簡(jiǎn)單查詢8/30/2019數(shù)據(jù)庫(kù)原理教程70稍微復(fù)雜一點(diǎn)的查詢都需要WHERE子句，用來(lái)說(shuō)明一個(gè)查詢條件一般地，查詢條件是一個(gè)布爾表達(dá)式

布爾表達(dá)式是由基本布爾表達(dá)式用圓括號(hào)和邏輯運(yùn)算符（NOT、AND和OR）構(gòu)成的表達(dá)式基本布爾表達(dá)式可以是邏輯常量（TRUE和FALSE）很少用比較表達(dá)式BETWEEN表達(dá)式IN表達(dá)式LIKE表達(dá)式NULL表達(dá)式量化比較表達(dá)式存在表達(dá)式唯一表達(dá)式匹配表達(dá)式帶WHERE的簡(jiǎn)單查詢(續(xù))1.比較表達(dá)式比較表達(dá)式的常見(jiàn)形式<值表達(dá)式1>

<值表達(dá)式2>其中

是比較運(yùn)算符（<、<=、>、>=、=、<>或!=）

<值表達(dá)式1>和<值表達(dá)式2>都是可求值的表達(dá)式，并且它們的值可以進(jìn)行比較通常，這些值表達(dá)式是常量、屬性和函數(shù)例4.10(1)查詢職稱（Title）為講師的全體教師的姓名和性別SELECT

Tname,

SexFROM

TeachersWHERE

Title=‘講師’;(2)查詢考試成績(jī)不及格的學(xué)生的學(xué)號(hào)SELECT

DISTIINCT

SnoFROM

SCWHERE

Grade<60;8/30/2019數(shù)據(jù)庫(kù)原理教程71帶WHERE的簡(jiǎn)單查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程722.BETWEEN表達(dá)式判定一個(gè)給定的值是否在給定的閉區(qū)間，其最常見(jiàn)形式是：<值表達(dá)式>[NOT]BETWEEN

<下界>AND

<上界>

其中<值表達(dá)式>、<下界>和<上界>都是可求值的表達(dá)式，其值是序數(shù)類型<下界>的值小于或等于<上界>

當(dāng)且僅當(dāng)<值表達(dá)式>的值在<下界>和<上界>確定的閉區(qū)間時(shí)，<值表達(dá)式>BETWEEN<下界>AND<上界>為真，而<值表達(dá)式>NOT

BETWEEN<下界>AND<上界>為假例4.11(1)查詢查詢出生年份在1987～1990年之間的學(xué)生的姓名和專業(yè)SELECT

Sname,

SpecialityFROM

StudentsWHERE

year(Birthday)

BETWEEN

1987

AND

1990;帶WHERE的簡(jiǎn)單查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程73(2)查詢查詢出生年份不在1987～1990年之間的學(xué)生的姓名和專業(yè)。SELECT

Sname,

SpecialityFROM

StudentsWHERE

year(Birthday)

NOT

BETWEEN

1987

AND

1990;

BETWEEN-AND和NOT

BETWEEN-AND表示的條件一般都可以用

AND或OR連接的多重比較表示

例如，查詢(1)可以用：

SELECT

Sname,SpecialityFROM

StudentsWHERE

year(Birthday)>=1987

AND

year(Birthday)<=1990;而查詢(2)可以用：SELECT

Sname,

SpecialityFROM

StudentsWHERE

year(Birthday)<1987

OR

year(Birthday)>1990;帶WHERE的簡(jiǎn)單查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程743.IN表達(dá)式判定一個(gè)給定的元素是否在給定的集合中IN表達(dá)式有兩種形式：<值表達(dá)式>[NOT]IN

(<值表達(dá)式列表>)<元組>|[NOT]IN

<子查詢>在第一種形式中<值表達(dá)式>是可求值的表達(dá)式（通常是屬性）

<值表達(dá)式列表>包括一個(gè)或多個(gè)可求值的表達(dá)式（通常是字面值，如45，‘教授’等），中間用逗號(hào)隔開(kāi)

當(dāng)且僅當(dāng)<值表達(dá)式>的值出現(xiàn)在<值表達(dá)式列表>中,<值表達(dá)式>IN

(<值表達(dá)式列表>)為真,而<值表達(dá)式>NOT

IN(<值表達(dá)式列表>)為假帶WHERE的簡(jiǎn)單查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程75例4.12(1)查詢計(jì)算機(jī)科學(xué)與技術(shù)和軟件工程專業(yè)的學(xué)生的學(xué)號(hào)和姓名SELECT

Sno,

SnameFROM

StudentsWHERE

Speciality

IN

(‘計(jì)算機(jī)科學(xué)與技術(shù)’,‘軟件工程’)；

(2)查詢既不是計(jì)算機(jī)科學(xué)與技術(shù)，也不是軟件工程專業(yè)的學(xué)生的學(xué)號(hào)和姓名SELECT

Sno,

SnameFROM

StudentsWHERE

Speciality

NOT

IN

(‘計(jì)算機(jī)科學(xué)與技術(shù)’,‘軟件工程’);

當(dāng)<值表達(dá)式列表>很小時(shí)，IN和NOT

IN表示的查詢條件都容易用多重比較表示。然而，當(dāng)<值表達(dá)式列表>較大時(shí)，使用IN表達(dá)式更簡(jiǎn)潔帶WHERE的簡(jiǎn)單查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程764.LIKE表達(dá)式使用比較運(yùn)算符，兩個(gè)字符串可以在字典序下進(jìn)行比較這種比較是精確比較

不能解決諸如“查找課程名的前兩個(gè)漢字是‘?dāng)?shù)據(jù)’的課程”這類模糊查詢。LIKE表達(dá)式允許我們表示這類查詢。LIKE表達(dá)式的一般形式為：<匹配值>[NOT]LIKE<模式>[ESCAPE‘<換碼字符>’]其中<匹配值>和<模式>都是字符串表達(dá)式，它們的值是可比較的通常，<匹配值>是屬性，<模式>是給定的字符串常量<模式>中允許使用通配符帶WHERE的簡(jiǎn)單查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程77兩種通配符“_”（下橫線）可以與任意單個(gè)字符匹配

例:a_b表示以a開(kāi)頭,以b結(jié)尾的長(zhǎng)度為3的任意字符串.例如,

acb,afb等而“%”可以與零個(gè)或多個(gè)任意字符匹配

例:a%b表示以a開(kāi)頭,以b結(jié)尾的任意長(zhǎng)度的字符串.例如

acb,addgb,ab等ESCAPE‘<換碼字符>’通常的形式是：ESCAPE‘\’它定義“\”為轉(zhuǎn)義字符，將緊隨其后的一個(gè)字符轉(zhuǎn)義

例如，如果<模式>中的_或%緊跟在\之后，則這個(gè)_或%就失去了通配符的意義，而取其字面意義

當(dāng)且僅當(dāng)<匹配值>與<模式>匹配時(shí)，<匹配值>LIKE<模式>為真，而<匹配值>NOT

LIKE<模式>為假帶WHERE的簡(jiǎn)單查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程78例4.13(1)查詢所有以“數(shù)據(jù)”開(kāi)頭的課程名。SELECT

CnameFROM

CoursesWHERE

Cname

LIKE

‘?dāng)?shù)據(jù)%’;(2)查詢姓李并且姓名只有兩個(gè)漢字的學(xué)生的學(xué)號(hào)和姓名。SELECT

Sno,

SnameFROM

StudentsWHERE

Sname

LIKE

‘李_

_’;注意：一個(gè)漢字占兩個(gè)字符位置帶WHERE的簡(jiǎn)單查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程79(3)查詢以C_打頭的課程的詳細(xì)信息。由于通配符“_”出現(xiàn)在模式中，我們需要使用轉(zhuǎn)義字符將它轉(zhuǎn)義。該查詢可以用如下語(yǔ)句實(shí)現(xiàn)：SELECT

*FROM

CoursesWHERE

Cname

LIKE

‘C\_%’

ESCAPE

‘\’;

其中，ESCAPE短語(yǔ)定義“\”為轉(zhuǎn)義字符，模式‘C\_%’中的

“_”被轉(zhuǎn)義，不再取通配符含義，而是取字面意義

注意：‘C\_%’中的“%”仍然是通配符，因?yàn)檗D(zhuǎn)義字符只對(duì)緊隨其后的一個(gè)字符轉(zhuǎn)義帶WHERE的簡(jiǎn)單查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程805.NULL表達(dá)式SQL允許元組的某些屬性上取空值（NULL）空值代表未知的值，不能與其他值進(jìn)行比較

NULL表達(dá)式允許我們判定給定的值是否為空值。NULL表達(dá)式的常見(jiàn)形式如下<值表達(dá)式>|<子查詢>IS

[NOT

]NULL

NULL表達(dá)式可以判定一個(gè)特定的值或子查詢結(jié)果是否為空值。通常，<值表達(dá)式>是屬性例如,如果A是屬性,則A

IS

NULL為真當(dāng)且僅當(dāng)屬性A上取空值例4.14查詢成績(jī)?yōu)榭盏膶W(xué)生的學(xué)號(hào)和課程號(hào)。SELECT

Sno,

CnoFROM

SCWHERE

Grade

IS

NULL;排序和分組8/30/2019數(shù)據(jù)庫(kù)原理教程811.將查詢結(jié)果排序通常，查詢結(jié)果的顯示次序是任意的查詢的結(jié)果按一定的次序顯示更便于觀察

ORDER

BY子句可以將查詢的結(jié)果按一定次序顯示。并可以按多個(gè)結(jié)果列將查詢結(jié)果排序，其一般形式如：ORDER

BY

<排序列>[ASC︱DESC],…,<排序列>[ASC︱DESC]其中，<排序列>是屬性名或?qū)傩缘膭e名，必須出現(xiàn)在SELECT子句中ORDER

BY后可以有一個(gè)或多個(gè)<排序列>，中間用逗號(hào)隔開(kāi)

每個(gè)<排序列>都可以獨(dú)立指定按升序（ASC）還是按降序（DESC）排序，缺省時(shí)為升序

如果指定多個(gè)<排序列>，則查詢結(jié)果按指定的次序，首先按第一個(gè)<排序列>的值排序，第一個(gè)<排序列>值相同的結(jié)果元組按第二個(gè)<排序列>的值排序，如此下去排序和分組(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程82

例4.15(1)查詢每位學(xué)生CS202課程的成績(jī)，并將查詢結(jié)果按成績(jī)降序排序SELECT

*FROM

SCWHERE

Cno=’CS202’ORDER

BY

Grade

DESC;這導(dǎo)致CS202課程成績(jī)由高分到低分顯示

(2)查詢每位學(xué)生的每門(mén)課程的成績(jī)，并將查詢結(jié)果按課程號(hào)升序、成績(jī)降序排序SELECT

*FROM

SCORDER

BY

Cno,Grade

DESC;這導(dǎo)致同一門(mén)課程的成績(jī)相繼顯示，并且高分在前排序和分組(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程832.聚集函數(shù)在實(shí)際應(yīng)用中，常常需要計(jì)算一些統(tǒng)計(jì)量例如，統(tǒng)計(jì)學(xué)生的總?cè)藬?shù)、女生的人數(shù)、學(xué)生的平均成績(jī)等等

SQL語(yǔ)言提供了一些聚集函數(shù)，使用這些聚集函數(shù)可以方便的進(jìn)行各種統(tǒng)計(jì)查詢。SQL的聚集函數(shù)可以單獨(dú)使用聚集函數(shù)作用于整個(gè)查詢結(jié)果SQL的聚集函數(shù)也可以配合GROUP

BY（分組）子句使用聚集函數(shù)作用于查詢結(jié)果的每個(gè)分組聚集函數(shù)單獨(dú)使用時(shí)，可以認(rèn)為整個(gè)查詢結(jié)果形成一個(gè)分組排序和分組(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程84SQL的聚集函數(shù)具有如下形式：

COUNT

([ALL

|

DISTINCT]*)或<聚集函數(shù)>([ALL

|

DISTINCT]<值表達(dá)式>)第一種情況只用于COUNTCOUNT

(*)或COUNT

(ALL

*)返回每個(gè)分組中的元組個(gè)數(shù)COUNT

(DISTINCT

*)返回每個(gè)分組中不同元組的個(gè)數(shù)排序和分組(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程85對(duì)于第二種情況，<聚集函數(shù)>可以是COUNT（計(jì)數(shù)）SUM（和）AVG（平均值）MAX（最大值）MIN（最小值）<值表達(dá)式>是可求值的表達(dá)式，通常是屬性短語(yǔ)ALL或DISTINCT是可選的，缺省時(shí)為ALL排序和分組(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程86設(shè)f是聚集函數(shù)，e是值表達(dá)式f(ALL

e)或f(e)

對(duì)每個(gè)分組，首先對(duì)該分組中每個(gè)元組計(jì)算e，得到e值的多重集；然后，將f作用于該多重集得到聚集函數(shù)值f(DISTINCT

e)

與f

(e)的唯一不同是，f

(DISTINCTe)在得到函數(shù)值之前要?jiǎng)h除多重集中的重復(fù)元素例如，設(shè)A是屬性

SUM(DISTINCT

A)將對(duì)每個(gè)分組中的元組，在屬性A的不同值上求和SUM(A)將簡(jiǎn)單地對(duì)每個(gè)分組中的元組，在屬性A上求和排序和分組(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程87例4.16(1)查詢選修了CS302課程的學(xué)生的人數(shù)。SELECT

COUNT

(*)FROM

SCWHERE

Cno

=

‘CS302’;(2)查詢CS302課程成績(jī)最低分、平均分和最高分。

SELECT

MIN

(Grade),AVG

(Grade),MAX

(Grade) FROM

SCWHERE

Cno

=

‘CS302’;排序和分組(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程883.分組SQL語(yǔ)言提供了GROUP

BY子句用于分組，其一般形式如下：

GROUP

BY

<分組列>,…,<分組列>[HAVING

<分組選擇條件>]

其中，<分組列>是屬性（可以帶表名前綴），它所在的表出現(xiàn)在FROM子句中

可選的HAVING子句用來(lái)過(guò)濾掉不滿足<分組選擇條件>的分組，缺省時(shí)等價(jià)于HAVING

TRUE

<分組選擇條件>類似于WHERE子句的查詢條件，但其中允許出現(xiàn)聚集函數(shù)

對(duì)于帶GROUP

BY子句的SELECT語(yǔ)句，SELECT子句中的結(jié)果列必須是

GROUP

BY子句中的<分組列>或聚集函數(shù)為什么？排序和分組(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程89帶GROUP

BY子句的SELECT語(yǔ)句的執(zhí)行效果相當(dāng)于：首先對(duì)FROM子句中的表計(jì)算笛卡爾積

再根據(jù)WHERE子句的查詢條件從中選擇滿足查詢條件的元組，得到查詢的中間結(jié)果

然后，按照GROUP

BY子句指定的一個(gè)或多個(gè)列對(duì)中間結(jié)果分組，在這些列上的值相等的元組分為一組

計(jì)算聚集函數(shù)（如果SELECT子句或HAVING短語(yǔ)包含聚集函數(shù)的話），并按照HAVING短語(yǔ)中的分組選擇條件過(guò)濾掉不滿足條件的分組最后，投影到SELECT子句的結(jié)果列上，得到查詢的回答排序和分組(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程90例4.17查詢每個(gè)學(xué)生的平均成績(jī)，輸出學(xué)生的學(xué)號(hào)和平均成績(jī)SELECT

Sno,

AVG

(Grade)FROM

SCGROUP

BY

Sno;這導(dǎo)致SC的元組按學(xué)號(hào)分組并對(duì)每組求平均成績(jī)

例4.18查詢每個(gè)學(xué)生的平均成績(jī)，并輸出平均成績(jī)大于85的學(xué)生學(xué)號(hào)和平均成績(jī)。SELECT

Sno,

AVG

(Grade)FROM

SCGROUP

BY

Sno

HAVING

AVG

(Grade)>85;

這導(dǎo)致SC的元組按學(xué)號(hào)分組，對(duì)每組求平均成績(jī)，并刪除平均成績(jī)不大于85的分組連接查詢8/30/2019數(shù)據(jù)庫(kù)原理教程91SQL支持多表查詢，允許FROM子句中包括多個(gè)表當(dāng)FROM子句中包含多個(gè)表時(shí)，相當(dāng)于求這些表的笛卡爾積

一般地，將來(lái)自不同表的任意元組串接在一起所形成的元組并沒(méi)有實(shí)際意義,我們需要的是自然連接和其他連接SQL允許FROM子句中包含各種連接的表

例如，T1

NATURAL

JOIN

T2產(chǎn)生表T1和T2的自然連接，可以作為一個(gè)表引用出現(xiàn)在FROM子句中許多商品化的DBMS并不能很好地支持這些功能

可以在WHERE子句中說(shuō)明連接條件，并通過(guò)SELECT子句選取所需要的屬性來(lái)實(shí)現(xiàn)各種連接在這種意義下，涉及多個(gè)表的查詢通常稱為連接查詢連接查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程92

例4.19查詢學(xué)號(hào)為200605098的學(xué)生的各科成績(jī)，對(duì)每門(mén)課程顯示課程名和成績(jī)。SELECT

Cname,

GradeFROM

SC,CoursesWHERE

SC.Cno=Courses.Cno

AND

Sno

=

‘200605098’;

其中SC.Cno=Courses.Cno是連接條件，相當(dāng)于求SC和Courses的自然連接而Sno=‘200605098’是該查詢的選擇條件注意

Cno既是表SC的屬性，也是Courses的屬性。為了避免二義性，我們必須在Cno前加前綴“SC.”或“Courses.”

其實(shí)，任何屬性前都可以加前綴。但是，當(dāng)A只是FROM子句中一個(gè)表的屬性時(shí)，前綴可以省略連接查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程93

例4.20查詢選修CS202課程，并且成績(jī)?cè)?0分以上的所有學(xué)生的學(xué)號(hào)、姓名和成績(jī)SELECT

Students.Sno,

Sname,

GradeFROM

Students,

SCWHERE

Students.Sno

=

SC.Sno

AND

Cno=

‘CS202’

ANDGrade>90；

例4.21查詢每個(gè)學(xué)生選修的每門(mén)課程的成績(jī)，要求列出的學(xué)號(hào)、姓名、課程名和成績(jī)SELECT

Student.Sno,

Sname,

Cname,

GradeFROM

Students,SC,

CoursesWHERE

Students.Sno

=SC.

Sno

AND

SC.Cno

=

Course.

Cno;連接查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程94

例4.22查詢每個(gè)學(xué)生的平均成績(jī)，并輸出平均成績(jī)大于85的學(xué)生學(xué)號(hào)、姓名和平均成績(jī)。SELECT

Student.Sno,

Sname,

AVG

(Grade)FROM

SC,StudentsWHERE

Students.Sno

=

SC.

SnoGROUP

BY

Students.Sno,

SnameHAVING

AVG

(Grade)>85;

注意：對(duì)于帶GROUP

BY子句的查詢，SELECT子句中的結(jié)果列只能是分組屬性和聚集函數(shù)

由于查詢要求顯示學(xué)生的姓名，因此在該查詢中，我們按學(xué)生的學(xué)號(hào)和姓名分組按學(xué)生的學(xué)號(hào)和姓名分組與按學(xué)號(hào)分組的效果是一樣的（為什么？）連接查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程95自身連接是一個(gè)表和它自己進(jìn)行連接通常情況下使用的不多對(duì)于一些特定的查詢，自身連接查詢非常有效例4.23查詢和王麗麗出生年月相同的學(xué)生的姓名。SELECT

S2.SnameFROM

Students

S1,

Students

S2

WHERE

S1.Birthday=S2.Birthday

ANDS1.Sname=’王麗麗’AND

S2.Sname<>’王麗麗’;連接查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程96

表Students在FROM子句中出現(xiàn)兩次，分別對(duì)它們使用別名S1和S2。這種別名可以看作元組變量該查詢的執(zhí)行相當(dāng)于

對(duì)于Students的每個(gè)元組S1，考察Students的每個(gè)元組S2，如果它們滿足WHERE子句中的條件，則顯示元組S2的Sname屬性值條件S2.Sname<>’王麗麗’使得顯示的結(jié)果不包括王麗麗本人

實(shí)際上，不僅可以把表別名看作元組變量，而且也可以將表名看作該表的元組變量例如，例4.19可以解釋為

對(duì)于SC的每個(gè)元組SC，考慮Courses的每個(gè)元組Courses，如果它們滿足條件SC.Cno=Courses.Cno

AND

Sno=‘200605098’，則顯示元組Courses的Cname屬性和元組SC的Grade屬性的值嵌套查詢8/30/2019數(shù)據(jù)庫(kù)原理教程97

SQL是一種結(jié)構(gòu)化查詢語(yǔ)言，它允許將一個(gè)查詢作為子查詢嵌套在另一個(gè)SELECT語(yǔ)句中最常見(jiàn)的嵌套是將子查詢嵌套在WHERE子句或HAVING短語(yǔ)的條件中

我們稱將一個(gè)查詢嵌套在另一個(gè)查詢中的查詢稱為嵌套查詢，并稱前者為子查詢（內(nèi)層查詢），后者為父查詢（外層查詢）子查詢中不能使用ORDER

BY子句嵌套查詢可以分兩類不相關(guān)子查詢的子查詢的條件不依賴于父查詢相關(guān)子查詢的子查詢的查詢條件依賴于父查詢使用子查詢可以對(duì)集合的成員資格、集合比較和集合基數(shù)進(jìn)行檢查

可以引進(jìn)子查詢的表達(dá)式:IN表達(dá)式、存在表達(dá)式、NULL表達(dá)式和唯一表達(dá)式等嵌套查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程981.IN引出的子查詢2.集合的比較引出的子查詢3.存在量詞引出的子查詢4.檢測(cè)子查詢結(jié)果中的重復(fù)元組嵌套查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程991.IN引出的子查詢IN表達(dá)式的第一種形式，用于判定一個(gè)給定的值是否在給定的集合中（前面已經(jīng)見(jiàn)過(guò)）IN表達(dá)式的第二種形式可以更一般地判定集合成員資格，其形式如下：<元組>[NOT]IN

<子查詢>

其中<元組>形如(<值表達(dá)式>,…,<值表達(dá)式>)，并且當(dāng)元組只有一個(gè)分量時(shí)，可以省略圓括號(hào)當(dāng)<元組>出現(xiàn)在<子查詢>的結(jié)果中，<元組>IN<子查詢>為真，而<元組>NOT

IN<子查詢>為假嵌套查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程100例4.24查詢和王麗麗在同一個(gè)專業(yè)學(xué)習(xí)的女同學(xué)的學(xué)號(hào)和姓名。下面的查詢得到王麗麗的專業(yè)SELECT

SpecialityFROM

StudentsWHERE

Sname=‘王麗麗’;將它作為子查詢，我們得到該查詢的SQL語(yǔ)句：SELECT

Sno,

SnameFROM

StudentsWHERE

Sex=‘女’AND

Speciality

IN(SELECT

SpecialityFROM

StudentsWHERE

Sname=‘王麗麗’);嵌套查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程101上面的例子中，表Students出現(xiàn)在父查詢和子查詢中

子查詢的FROM子句中的表（包括它的屬性，下同）僅在子查詢中存在并起作用

父查詢FROM子句中的表在父查詢和子查詢都存在，但是當(dāng)子查詢的FROM子句包含相同的表時(shí)，其作用域不包含子查詢這類似于程序設(shè)計(jì)語(yǔ)言中的變量的存在域和作用域

然而，我們可以對(duì)父查詢中的表起別名，并在子查詢中引用它的屬性。當(dāng)子查詢中還包含子查詢時(shí)，解釋類似嵌套查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程1022.集合的比較引出的子查詢

SQL允許將一個(gè)元素與子查詢的結(jié)果集進(jìn)行比較。這種量化比較表達(dá)式的常用形式是：<值表達(dá)式>

ALL

|

SOME

|

ANY

<子查詢>其中<值表達(dá)式>通常是屬性是比較運(yùn)算符（=、<>、!＝、<、>、>=、<=）SOME和ANY的含義相同

早期只有ANY，但容易與英語(yǔ)中的any一詞在語(yǔ)言上混淆，現(xiàn)在更多地使用SOME當(dāng)<子查詢>的結(jié)果為單個(gè)值時(shí)，ALL、SOME和ANY可以省略嵌套查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程103

設(shè)v是<值表達(dá)式>的值，S是<子查詢>的查詢結(jié)果，它是元組（值）的集合v

ALL

S為真，當(dāng)且僅當(dāng)v與S中的每個(gè)值都滿足比較關(guān)系

v

SOME

S（或v

ANYS）為真，當(dāng)且僅當(dāng)v與S中的某個(gè)值滿足比較關(guān)系

例如，當(dāng)v大于S中每個(gè)值時(shí)，v>ALLS為真；而當(dāng)v不等于S中的某個(gè)值時(shí)，v

<>SOME

S為真注意：=SOME等價(jià)于IN，但是<>SOME并不等價(jià)于NOT

IN嵌套查詢(續(xù))8/30/2019數(shù)據(jù)庫(kù)原理教程104

例4.25查詢比軟件工程專業(yè)所有學(xué)生都小其他專業(yè)的學(xué)生的學(xué)號(hào)、姓名、專業(yè)和出生日期下面的語(yǔ)句將得到軟件工程專業(yè)所有學(xué)生的出生日期：SELECT

BirthdayFROM

StudentsWHERE

Speciality=‘軟件工程’;將它作為子查詢，我們得到該查詢的SQL語(yǔ)句:SELECT

Sno,

Sname,

Speciality,

BirthdayFROM

StudentsWHERE

Speciality<>‘軟件工程’AND

Birthday

<ALL(SELECT

BirthdayFROM

StudentsWHERE

Speciality