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

本章內(nèi)容3.1SQL語(yǔ)言的基本概念與特點(diǎn)3.2SQL數(shù)據(jù)定義3.3SQL數(shù)據(jù)查詢(xún)3.4SQL數(shù)據(jù)更新3.5視圖3.6SQL數(shù)據(jù)控制3.7嵌入式SQL語(yǔ)言*習(xí)題3.1SQL語(yǔ)言的基本概念與特點(diǎn)

3.1.1語(yǔ)言的發(fā)展及標(biāo)準(zhǔn)化

3.1.2SQL語(yǔ)言的基本概念

3.1.3SQL語(yǔ)言的主要特點(diǎn)

返回本章首頁(yè)3.1.1語(yǔ)言的發(fā)展及標(biāo)準(zhǔn)化在70年代初，E.F.Codd首先提出了關(guān)系模型。70年代中期，IBM公司在研制SYSTEMR關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)中研制了SQL語(yǔ)言，最早的SQL語(yǔ)言（叫SEQUEL2）是在1976年11月的IBMJournalofR&D上公布的。1979年ORACLE公司首先提供商用的SQL，IBM公司在DB2和SQL/DS數(shù)據(jù)庫(kù)系統(tǒng)中也實(shí)現(xiàn)了SQL。1986年10月，美國(guó)ANSI采用SQL作為關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)的標(biāo)準(zhǔn)語(yǔ)言(ANSIX3.135-1986），后為國(guó)際標(biāo)準(zhǔn)化組織（ISO）采納為國(guó)際標(biāo)準(zhǔn)。

返回本節(jié)首頁(yè)3.1.1語(yǔ)言的發(fā)展及標(biāo)準(zhǔn)化1989年，美國(guó)ANSI采納在ANSIX3.135-1989報(bào)告中定義的關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)的SQL標(biāo)準(zhǔn)語(yǔ)言，稱(chēng)為ANSISQL89。1992年，ISO又推出了SQL92標(biāo)準(zhǔn)，也稱(chēng)為SQL2.1999年，推出了SQL：1999，也稱(chēng)SQL3，增加了面向?qū)ο蟮鹊墓δ堋?003年，推出了SQL：2003，增加了XML相關(guān)的特性等新功能。2006年，又推出了SQL：2006，全面加強(qiáng)了對(duì)XML數(shù)據(jù)的處理與操作能力。2008年，推出了SQL：2008正在起草中。

返回本節(jié)首頁(yè)3.1.1語(yǔ)言的發(fā)展及標(biāo)準(zhǔn)化結(jié)構(gòu)化查詢(xún)語(yǔ)言SQL(StructuredQueryLanguage)是一種介于關(guān)系代數(shù)與關(guān)系演算之間的語(yǔ)言，其功能包括查詢(xún)、操縱、定義和控制四個(gè)方面，是一個(gè)通用的、功能極強(qiáng)的關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言。目前已成為關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言，廣泛應(yīng)用于各種數(shù)據(jù)庫(kù)。

返回本節(jié)首頁(yè)關(guān)系數(shù)據(jù)庫(kù)三級(jí)模式結(jié)構(gòu)SQL語(yǔ)言支持關(guān)系數(shù)據(jù)庫(kù)三級(jí)模式結(jié)構(gòu)，如圖3.1所示。其中外模式對(duì)應(yīng)于視圖（View）和部分基本表（BaseTable），模式對(duì)應(yīng)于基本表，內(nèi)模式對(duì)應(yīng)于存儲(chǔ)文件。3.1.2SQL語(yǔ)言的基本概念返回本節(jié)首頁(yè)SQL視圖1基本表2視圖2基本表3基本表4基本表1存儲(chǔ)文件1存儲(chǔ)文件2外模式模式內(nèi)模式圖3.1數(shù)據(jù)庫(kù)三級(jí)模式結(jié)構(gòu)返回本節(jié)首頁(yè)圖3.2關(guān)系數(shù)據(jù)庫(kù)三級(jí)模式結(jié)構(gòu)示意圖

返回本節(jié)首頁(yè)基本表是本身獨(dú)立存在的表，在SQL中一個(gè)關(guān)系就對(duì)應(yīng)一個(gè)表。一些基本表對(duì)應(yīng)一個(gè)存儲(chǔ)文件，一個(gè)表可以有若干索引，索引也存放在存儲(chǔ)文件中。視圖是從基本表或其他視圖中導(dǎo)出的表，它本身不獨(dú)立存儲(chǔ)在數(shù)據(jù)庫(kù)中，也就是說(shuō)數(shù)據(jù)庫(kù)中只存放視圖的定義而不存放視圖對(duì)應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中，因此視圖是一個(gè)虛表。存儲(chǔ)文件的物理結(jié)構(gòu)及存儲(chǔ)方式等組成了關(guān)系數(shù)據(jù)庫(kù)的內(nèi)模式。存儲(chǔ)文件的物理結(jié)構(gòu)及存儲(chǔ)方式等不同數(shù)據(jù)庫(kù)管理系統(tǒng)往往是不同的，一般也是不公開(kāi)的。3.1.2SQL語(yǔ)言的基本概念返回本節(jié)首頁(yè)視圖和基本表是SQL語(yǔ)言的主要操作對(duì)象，用戶(hù)可以用SQL語(yǔ)言對(duì)視圖和基本表進(jìn)行各種操作。在用戶(hù)眼中，視圖和基本表都是關(guān)系表，而存儲(chǔ)文件對(duì)用戶(hù)是透明的。

3.1.2SQL語(yǔ)言的基本概念返回本節(jié)首頁(yè)3.1.3SQL語(yǔ)言的主要特點(diǎn)1、綜合統(tǒng)一2、高度非過(guò)程化3、面向集合的操作方式4、以同一種語(yǔ)法結(jié)構(gòu)提供兩種使用方式5、語(yǔ)言簡(jiǎn)捷，易學(xué)易用

返回本節(jié)首頁(yè)3.2SQL數(shù)據(jù)定義

3.2.1字段數(shù)據(jù)類(lèi)型

3.2.2創(chuàng)建、修改和刪除數(shù)據(jù)表

3.2.3設(shè)計(jì)、創(chuàng)建和維護(hù)索引

返回本章首頁(yè)3.2.1字段數(shù)據(jù)類(lèi)型整數(shù)數(shù)據(jù)類(lèi)型:bigint,int,smallint,tinyint精確數(shù)值類(lèi)型:numeric,decimal近似浮點(diǎn)數(shù)值數(shù)據(jù)類(lèi)型:float,real日期時(shí)間數(shù)據(jù)類(lèi)型:datetime,smalldatetime字符串?dāng)?shù)據(jù)類(lèi)型:char,varchar,textUnicode字符串?dāng)?shù)據(jù)類(lèi)型:nchar,nvarchar,ntext3.2.1字段數(shù)據(jù)類(lèi)型二進(jìn)制數(shù)據(jù)類(lèi)型:binary、varbinary、image貨幣數(shù)據(jù)類(lèi)型:money,smallmoney標(biāo)記數(shù)據(jù)類(lèi)型:timestamp,uniqueidentifier返回本節(jié)首頁(yè)3.2.2創(chuàng)建、修改和刪除數(shù)據(jù)表1．定義基本表2．修改基本表3．刪除基本表返回本節(jié)首頁(yè)定義基本表CREATETABLE<表名>(<列名><數(shù)據(jù)類(lèi)型>[列級(jí)完整性約束條件][，<列名><數(shù)據(jù)類(lèi)型>[列級(jí)完整性約束條件]]…[,<表級(jí)完整性約束條件>])

其中<表名>是所要定義的基本表的名字，必須是合法的標(biāo)識(shí)符，最多可有128個(gè)字符，但本地臨時(shí)表的表名（名稱(chēng)前有一個(gè)編號(hào)符#）最多只能包含116個(gè)字符。表名不允許重名，一個(gè)表可以由一個(gè)或多個(gè)屬性（列）組成。建表的同時(shí)通常還可以定義與該表有關(guān)的完整性約束條件，這些完整性約束條件被存入系統(tǒng)的數(shù)據(jù)字典中，當(dāng)用戶(hù)操作表中數(shù)據(jù)時(shí)由DBMS自動(dòng)檢查該操作是否違背這些完整性約束條件。如果完整性約束條件涉及到該表的多個(gè)屬性列，則必須定義在表級(jí)上，否則既可以定義在列級(jí)也可以定義在表級(jí)。返回本節(jié)首頁(yè)關(guān)系模型的完整性規(guī)則(1)實(shí)體完整性①主碼（PRIMARYKEY）②空值（NULL/NOTNULL）③惟一值（UNIQUE）(2)參照完整性

FOREIGNKEY約束指定某一個(gè)列或一組列作為外部鍵(3)用戶(hù)自定義的完整性約束規(guī)則返回本節(jié)首頁(yè)完整性示例下面我們以一個(gè)“學(xué)生-課程”數(shù)據(jù)庫(kù)為例來(lái)說(shuō)明，表內(nèi)容請(qǐng)參圖3.3。“學(xué)生-課程”數(shù)據(jù)庫(kù)中包括三個(gè)表：（1）“學(xué)生”表S由學(xué)號(hào)（SNO）、姓名（SN）、性別（SEX）、年齡（AGE）、所在系（DEPT）五個(gè)屬性組成，可記為：S（SNO，SN，SEX，AGE，DEPT）；（2）“課程”表C由課程號(hào)（CNO）、課程名（CN）、學(xué)分（CT）三個(gè)屬性組成，可記為：C（CNO，CN，CT）；（3）“學(xué)生選課”表SC由學(xué)號(hào)（SNO）、課程號(hào)（CNO）、成績(jī)（SCORE）三個(gè)屬性組成，可記為：SC（SNO，CNO，SCORE）。返回本節(jié)首頁(yè)S學(xué)號(hào)SNO姓名SN性別SEX年齡AGE系別DEPTS1李濤男19信息S2王林女18計(jì)算機(jī)S3陳高女21自動(dòng)化S4張杰男17自動(dòng)化S5吳小麗女19信息S6徐敏敏女20計(jì)算機(jī)返回本節(jié)首頁(yè)創(chuàng)建命令圖3.3C課程號(hào)CNO課程名CN學(xué)分CTC1C語(yǔ)言4C2離散數(shù)學(xué)2C3操作系統(tǒng)3C5數(shù)據(jù)結(jié)構(gòu)4C6數(shù)據(jù)庫(kù)4C7匯編語(yǔ)言3C8信息基礎(chǔ)2返回本節(jié)首頁(yè)圖3.3SC學(xué)號(hào)SNO課程號(hào)SNO成績(jī)SCORES1C190S1C285S2C184S2C294S2C383S3C173S3C768S3C488S3C585S4C265S4C590S4C679S5C289返回本節(jié)首頁(yè)圖3.3例1

建立一個(gè)“學(xué)生”表S，它由學(xué)號(hào)SNO、姓名SN、性別SEX、年齡AGE、所在系DEPT五個(gè)屬性組成，其中學(xué)號(hào)屬性為主鍵，姓名、年齡與性別不為空，假設(shè)姓名沒(méi)有唯一并建立惟一索引，并且性別只能在“男”與“女”中選一個(gè)，年齡不能小于0。CREATETABLES(SNOCHAR(5)PRIMARYKEY, SNVARCHAR(8)NOTNULL, SEXCHAR(2)NOTNULLCHECK(SEXIN('男','女')), AGEINTNOTNULLCHECK(AGE>0), DEPTVARCHAR(20), CONSTRAINTSN_UUNIQUE(SN) )返回本節(jié)首頁(yè)[例2]建立“課程”表C，它由課程號(hào)（CNO）、課程名（CN）、學(xué)分（CT）三個(gè)屬性組成。CNO為該表主鍵，學(xué)分大于等于1。

CREATETABLEC(CNOCHAR(5)NOTNULLPRIMARYKEY,CNVARCHAR(20),CTINTCHECK(CT>=1))[例3]建立“選修”關(guān)系表SC，定義SNO,CNO為SC的外部鍵，(SNO,CNO)為該表的主鍵。

CREATETABLESC(SNOCHAR(5)NOTNULLCONSTRAINTS_FFOREIGNKEYREFERENCESS(SNO),CNOCHAR(5)NOTNULL,SCORENUMERIC(3),CONSTRAINTS_C_PPRIMARYKEY(SNO,CNO),CONSTRAINTC_FFOREIGNKEY(CNO)REFERENCESC(CNO))返回本節(jié)首頁(yè)修改基本表ALTERTABLEtable{[ALTERCOLUMNcolumn_name{new_data_type[(precision[,scale])][COLLATE<collation_name>][NULL|NOTNULL]|{ADD|DROP}ROWGUIDCOL}]|ADD{[<column_definition>]|column_nameAScomputed_column_expression}[,...n]|[WITHCHECK|WITHNOCHECK]ADD{<table_constraint>}[,...n]|DROP{[CONSTRAINT]constraint_name|COLUMNcolumn}[,...n]|{CHECK|NOCHECK}CONSTRAINT{ALL|constraint_name[,...n]}|{ENABLE|DISABLE}TRIGGER{ALL|trigger_name[,...n]}}返回本節(jié)首頁(yè)修改基本表說(shuō)明其中:<表名>指定需要修改的基本表，ADD子句用于增加新列和新的完整性約束條件，DROP子句用于刪除指定的完整性約束條件或原有列，ALTER子句用于修改原有的列定義。{CHECK|NOCHECK}CONSTRAINT

指定啟用或禁用constraint_name。如果禁用，將來(lái)插入或更新該列時(shí)將不用該約束條件進(jìn)行驗(yàn)證。此選項(xiàng)只能與FOREIGNKEY和CHECK約束一起使用。{ENABLE|DISABLE}TRIGGER指定啟用或禁用trigger_name。當(dāng)一個(gè)觸發(fā)器被禁用時(shí)，它對(duì)表的定義依然存在；然而，當(dāng)在表上執(zhí)行INSERT、UPDATE或DELETE語(yǔ)句時(shí)，觸發(fā)器中的操作將不執(zhí)行，除非重新啟用該觸發(fā)器。返回本節(jié)首頁(yè)修改表示例[例4]

向S表增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類(lèi)型為日期型。

ALTERTABLESADDSCOMEDATETIME不論基本表中原來(lái)是否已有數(shù)據(jù)，新增加的列一律為空值。[例5]將年齡的數(shù)據(jù)類(lèi)型改為半字長(zhǎng)整數(shù)。

ALTERTABLESALTERCOLUMNAGESMALLINT修改原有的列定義，會(huì)使列中數(shù)據(jù)作新舊類(lèi)型的自動(dòng)轉(zhuǎn)化，有可能會(huì)破壞已有數(shù)據(jù)。[例6]

刪除例4增加的“入學(xué)時(shí)間”列。

ALTERTABLESDROPCOLUMNSCOME[例7]

禁止SC中的參照完整性C_F。

ALTERTABLESNOCHECKCONSTRAINTC_F返回本節(jié)首頁(yè)刪除基本表DROPTABLE<表名>

[例8]

刪除S表。

DROPTABLES返回本節(jié)首頁(yè)3.2.3設(shè)計(jì)、創(chuàng)建和維護(hù)索引1．索引的概念2．創(chuàng)建索引3．刪除索引

返回本節(jié)首頁(yè)索引的概念數(shù)據(jù)庫(kù)中的索引是為了加速對(duì)表中元組（或記錄）的檢索而創(chuàng)建的一種分散存儲(chǔ)結(jié)構(gòu)(如B+樹(shù)數(shù)據(jù)結(jié)構(gòu))，它實(shí)際上是記錄的關(guān)鍵字與其相應(yīng)地址的對(duì)應(yīng)表。索引是對(duì)表或視圖而建立的，由索引頁(yè)面組成。改變表中的數(shù)據(jù)(如增加或刪除記錄)時(shí)，索引將自動(dòng)更新。索引建立后，在查詢(xún)使用該列時(shí)，系統(tǒng)將自動(dòng)使用索引進(jìn)行查詢(xún)。索引是把雙刃劍，由于要建立索引頁(yè)面，索引也會(huì)減慢更新的速度。索引數(shù)目無(wú)限制，但索引越多，更新數(shù)據(jù)的速度越慢。對(duì)于僅用于查詢(xún)的表可多建索引，對(duì)于數(shù)據(jù)更新頻繁的表則應(yīng)少建索引。返回本節(jié)首頁(yè)索引的概念按照索引記錄的存放位置可分為聚集索引(ClusteredIndex)與非聚集索引(Non-ClusteredIndex)兩類(lèi)。聚集索引是指索引項(xiàng)的順序與表中記錄的物理順序一致的索引組織；非聚集索引按照索引的字段排列記錄，但是排列的結(jié)果并不會(huì)存儲(chǔ)在表中，而是另外存儲(chǔ)。在檢索記錄時(shí)，聚集索引會(huì)比非聚集索引速度快，一個(gè)表中只能有一個(gè)聚集索引，而非聚集索引可以有多個(gè)。返回本節(jié)首頁(yè)創(chuàng)建索引CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX<索引名>ON{<表名>|<視圖名>}(<列名>[ASC|DESC][,...n])[WITH<索引選項(xiàng)>[,...n]][ON文件組名][例9]

為學(xué)生-課程數(shù)據(jù)庫(kù)中的S、C、SC三個(gè)表建立索引。其中S表按學(xué)號(hào)升序建惟一索引，C表按課程號(hào)降序建立聚簇索引，SC表按學(xué)號(hào)升序和課程號(hào)降序建非聚簇索引。CREATEUNIQUEINDEXS_SNOONS(SNO)CREATECLUSTEREDINDEXC_CNOONC(CNODESC)CREATENONCLUSTEREDINDEXSC_SNO_CNOONSC(SNOASC,CNODESC)返回本節(jié)首頁(yè)刪除索引返回本節(jié)首頁(yè)刪除索引的命令語(yǔ)法：DROPINDEX表名.<索引名>|

視圖名.<索引名>[,...n][例10]

刪除S表的S_SNO索引。DROPINDEXS.S_SNO3.3SQL數(shù)據(jù)查詢(xún)3.3.1SELECT命令的格式及其含義3.3.2SELECT子句的基本使用3.3.3WHERE子句的基本使用3.3.4常用庫(kù)函數(shù)及統(tǒng)計(jì)匯總查詢(xún)3.3.5分組查詢(xún)3.3.6查詢(xún)的排序3.3.7連接查詢(xún)3.3.8合并查詢(xún)3.3.9嵌套查詢(xún)3.3.10子查詢(xún)別名表達(dá)式的使用*

3.3.11存儲(chǔ)查詢(xún)結(jié)果到表中

返回本章首頁(yè)3.3.1SELECT命令的格式之一SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[,<目標(biāo)列表達(dá)式>]...[INTO<新表名>]FROM<表名或視圖名>[,<表名或視圖名>]...[WHERE<條件表達(dá)式>][GROUPBY<列名1>...[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]]...返回本節(jié)首頁(yè)3.3.1SELECT命令的格式2SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式1>[[AS]列別名1][,<目標(biāo)列表達(dá)式2>[[AS]列別名2]]...[INTO<新表名>]FROM<表名1或視圖名1>[[AS]表別名1][,<表名2或視圖名2>[[AS]表別名2]]...[WHERE<元組或記錄篩選條件表達(dá)式>][GROUPBY<列名11>[,<列名12>]...[HAVING<分組篩選條件表達(dá)式>]][ORDERBY<列名21>[ASC|DESC][,<列名22>[ASC|DESC]]...]返回本節(jié)首頁(yè)3.3.1SELECT命令的含義整個(gè)SELECT語(yǔ)句的含義是，根據(jù)WHERE子句的條件表達(dá)式，從FROM子句指定的基本表或視圖中找出滿(mǎn)足條件的元組，再按SELECT子句中的目標(biāo)列表達(dá)式，選出元組中的屬性值形成結(jié)果表。如果有GROUP子句，則將結(jié)果按<列名11>的值進(jìn)行分組（假設(shè)只有一列分組列），該屬性列值相等的元組為一個(gè)組，每個(gè)組將產(chǎn)生結(jié)果表中的一條記錄，通常會(huì)在每組中作用集函數(shù)。如果GROUP子句帶HAVING短語(yǔ)，則只有滿(mǎn)足指定條件的組才給予輸出。如果有ORDER子句，則結(jié)果表還要按<列名22>的值的升序或降序排序后（假設(shè)只有一列排序列）再輸出。返回本節(jié)首頁(yè)3.3.1SELECT命令的含義SELECT語(yǔ)句組成成分的說(shuō)明：1、目標(biāo)列表達(dá)式的可選格式：（1）[<表名>.]屬性列名|各種普通函數(shù)|常量|...（2）[<表名>.]*（3）COUNT([ALL|DISTINCT]<屬性列名>|*)等集函數(shù)（4）算術(shù)運(yùn)算（+、-、*、/）為主的表達(dá)式。其中參數(shù)可以是屬性列名、集函數(shù)、常量、普通函數(shù)、表達(dá)式等形式。返回本節(jié)首頁(yè)3.3.1SELECT命令的含義SELECT語(yǔ)句組成成分的說(shuō)明：2、集函數(shù)的可選格式：

COUNT([ALL|DISTINCT]<屬性列名>|*)；

SUM|AVG|MAX|MIN([ALL|DISTINCT]<屬性列名>)返回本節(jié)首頁(yè)3.3.1SELECT命令的含義SELECT語(yǔ)句組成成分的說(shuō)明：3、WHERE子句的元組或記錄篩選條件表達(dá)式有以下可選格式：（1）<屬性列名>θ{<屬性列名>|<常量>|[ANY|ALL](SELECT語(yǔ)句)}其中θ為6種關(guān)系比較運(yùn)算符之一。（2）<屬性列名>NOTBETWEEN{<屬性列名>|<常量>|(SELECT語(yǔ)句)}AND{<屬性列名>|<常量>|(SELECT語(yǔ)句)}（3）<屬性列名>[NOT]IN{(值1[,值2]…)|(SELECT語(yǔ)句)}（4）<屬性列名>[NOT]LIKE<匹配串>（5）<屬性列名>IS[NOT]NULL（6）[NOT]EXISTS(SELECT語(yǔ)句)（7）[NOT]<條件表達(dá)式>{AND|OR}[NOT]<條件表達(dá)式>[{AND|OR}([NOT]<條件表達(dá)式>)]…返回本節(jié)首頁(yè)3.3.1SELECT命令的含義SELECT語(yǔ)句組成成分的說(shuō)明：4、HAVING子句的分組篩選條件表達(dá)式有以下可選格式：HAVING子句的分組篩選條件表達(dá)式格式基本同WHERE子句的可選格式。不同的是HAVING子句的條件表達(dá)式中出現(xiàn)的屬性列名應(yīng)為GROUPBY子句中的分組列名。HAVING子句的條件表達(dá)式中一般要使用到集函數(shù)COUNT、SUM、AVG、MAX或MIN等，因?yàn)橹挥羞@樣才能表達(dá)出篩選分組的要求。返回本節(jié)首頁(yè)3.3.2SELECT子句的基本使用1.查詢(xún)指定列2.查詢(xún)?nèi)苛?.查詢(xún)經(jīng)過(guò)計(jì)算的值返回本節(jié)首頁(yè)查詢(xún)指定列[例11]

查詢(xún)?nèi)w學(xué)生的學(xué)號(hào)與姓名。

SELECTSNO,SNFROMS<目標(biāo)列表達(dá)式>中各個(gè)列的先后順序可以與表中的順序不一致。也就是說(shuō)，用戶(hù)在查詢(xún)時(shí)可以根據(jù)應(yīng)用的需要改變列的顯示順序。[例12]

查詢(xún)?nèi)w學(xué)生的姓名、學(xué)號(hào)、所在系。

SELECTSN,SNO,DEPTFROMS

這時(shí)結(jié)果表中的列的順序與基表中不同，是按查詢(xún)要求，先列出姓名屬性，然后再列出學(xué)號(hào)和所在系屬性。返回本節(jié)首頁(yè)查詢(xún)?nèi)苛衃例13]

查詢(xún)?nèi)w學(xué)生的詳細(xì)記錄。

SELECT*FROMS該SELECT語(yǔ)句實(shí)際上是無(wú)條件地把S表的全部信息都查詢(xún)出來(lái)，所以也稱(chēng)為全表查詢(xún)，這是最簡(jiǎn)單的一種查詢(xún)命令形式。它等價(jià)于如下命令：

SELECTSNO,SN,SEX,AGE,DEPTFROMS返回本節(jié)首頁(yè)查詢(xún)經(jīng)過(guò)計(jì)算的值SELECT子句的<目標(biāo)列表達(dá)式>不僅可以是表中的屬性列，也可以是有關(guān)表達(dá)式，即可以將查詢(xún)出來(lái)的屬性列經(jīng)過(guò)一定的計(jì)算后列出結(jié)果。[例14]

查全體學(xué)生的姓名及其出生年份。

SELECTSN,2005-AGEFROMS

本例中，<目標(biāo)列表達(dá)式>中第二項(xiàng)不是通常的列名，而是一個(gè)計(jì)算表達(dá)式，是用當(dāng)前的年份（假設(shè)為2005年）減去學(xué)生的年齡，這樣，所得的即是學(xué)生的出生年份。輸出的結(jié)果為：

SN-----------------

李濤1986

王林1987

陳高1984

張杰1988

吳小麗1986

徐敏敏1985返回本節(jié)首頁(yè)3.3.3WHERE子句的基本使用1.消除取值重復(fù)的行2.指定WHERE查詢(xún)條件返回本節(jié)首頁(yè)消除取值重復(fù)的行[例16]

查所有選修過(guò)課的學(xué)生的學(xué)號(hào)。SELECTSNOFROMSC結(jié)果為：

SNO----S1 S1S2S2S2S3S3S3S3S4S4S4S5該查詢(xún)結(jié)果里包含了許多重復(fù)的行。如果想去掉結(jié)果表中的重復(fù)行，必須指定DISTINCT短語(yǔ)：SELECTDISTINCTSNOFROMSC執(zhí)行結(jié)果為：

SNO----S1S2S3S4S5返回本節(jié)首頁(yè)表3.2常用的查詢(xún)條件查詢(xún)條件謂詞比較運(yùn)算符=,>,<.>=,<=,!=,<>,!>,!<；Not(上述比較運(yùn)算符構(gòu)成的比較關(guān)系表達(dá)式)確定范圍BETWEENAND，NOTBETWEENAND確定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重條件AND，OR，NOT返回本節(jié)首頁(yè)比較運(yùn)算符[例17]

查計(jì)算機(jī)系全體學(xué)生的名單。

SELECTSNFROMSWHEREDEPT='計(jì)算機(jī)'[例18]

查所有年齡在20歲以下的學(xué)生姓名及其年齡。

SELECTSN,AGEFROMSWHEREAGE<20或

SELECTSN,AGEFROMSWHERENOTAGE>=20返回本節(jié)首頁(yè)確定范圍[例20]

查詢(xún)年齡在20至23歲之間的學(xué)生的姓名、系別和年齡。

SELECTSN,DEPT,AGEFROMSWHEREAGEBETWEEN20AND23與“BETWEEN…AND…”相對(duì)的謂詞是“NOTBETWEEN…AND…”。[例21]

查詢(xún)年齡不在20至23歲之間的學(xué)生姓名、系別和年齡。

SELECTSN,DEPT,AGEFROMSWHEREAGENOTBETWEEN20AND23返回本節(jié)首頁(yè)確定集合[例22]

查詢(xún)信息系、自動(dòng)化系和計(jì)算機(jī)系的學(xué)生的姓名和性別。SELECTSN,SEXFROMSWHEREDEPTIN('信息','自動(dòng)化','計(jì)算機(jī)')與IN相對(duì)的謂詞是NOTIN，用于查找屬性值不屬于指定集合的元組。[例23]

查既不是信息系、數(shù)學(xué)系，也不是計(jì)算機(jī)科學(xué)系的學(xué)生的姓名和性別。SELECTSN,SEXFROMSWHEREDEPTNOTIN('信息','自動(dòng)化','計(jì)算機(jī)')返回本節(jié)首頁(yè)3.3通配符及其含義通配符描述%（百分號(hào)）代表零個(gè)或更多字符的任意字符串。_（下劃線）代表任何單個(gè)字符（長(zhǎng)度可以為0）。[]（中擴(kuò)號(hào)）指定范圍([a-f])或集合([abcdef])中的任何單個(gè)字符。[^]不屬于指定范圍([^a-f])或集合([^abcdef])的任何單個(gè)字符。返回本節(jié)首頁(yè)字符匹配示例1[例24]

查所有姓劉的學(xué)生的姓名、學(xué)號(hào)和性別。

SELECTSN,SNO,SEXFROMSWHERESNLIKE'劉%‘[例25]

查姓“歐陽(yáng)”且全名為三個(gè)漢字的學(xué)生的姓名。

SELECTSNFROMSWHERESNLIKE'歐陽(yáng)_'返回本節(jié)首頁(yè)字符匹配示例2[例26]

查名字中第二字為“陽(yáng)”字的學(xué)生的姓名和學(xué)號(hào)。

SELECTSN,SNOFROMSWHERESNLIKE'_陽(yáng)%'[例27]

查所有不姓劉的學(xué)生姓名。

SELECTSN,SNO,SEXFROMSWHERESNNOTLIKE'劉%'返回本節(jié)首頁(yè)字符匹配示例3[例28]

查DB_Design課程的課程號(hào)和學(xué)分。

SELECTCNO,CTFROMCWHERECNLIKE'DB\_Design'ESCAPE'\'ESCAPE'\'短語(yǔ)表示\為換碼字符，這樣匹配串中緊跟在\后面的字符'_'不再具有通配符的含義，而是取其本身含義，被轉(zhuǎn)義為普通的'_'字符。返回本節(jié)首頁(yè)涉及空值的查詢(xún)[例29]某些學(xué)生選修某門(mén)課程后沒(méi)有參加考試，所以有選課記錄，但沒(méi)有考試成績(jī)，下面我們來(lái)查一下缺少成績(jī)的學(xué)生的學(xué)號(hào)和相應(yīng)的課程號(hào)。

SELECTSNO,CNOFROMSCWHERESCOREISNULL注意這里的'IS'不能用等號(hào)（'='）代替[例30]查所有有成績(jī)的記錄的學(xué)生學(xué)號(hào)和課程號(hào)。

SELECTSNO,CNOFROMSCWHERESCOREISNOTNULL返回本節(jié)首頁(yè)多重條件查詢(xún)1邏輯運(yùn)算符AND、OR和NOT可用來(lái)聯(lián)結(jié)多個(gè)查詢(xún)條件。他們的優(yōu)先級(jí)NOT最高，接著是AND，OR優(yōu)先級(jí)最低，但用戶(hù)可以用括號(hào)改變運(yùn)算的優(yōu)先順序。[例31]

查計(jì)算機(jī)系年齡在20歲以下的學(xué)生姓名。

SELECTSN FROMS WHEREDEPT='計(jì)算機(jī)'ANDAGE<20返回本節(jié)首頁(yè)多重條件查詢(xún)2[例32]IN謂詞實(shí)際上是多個(gè)OR運(yùn)算符的縮寫(xiě)，因此“查詢(xún)信息系、自動(dòng)化系和計(jì)算機(jī)系的學(xué)生的姓名和性別”一題，也可以用OR運(yùn)算符寫(xiě)成如下等價(jià)形式：

SELECTSN,SEXFROMSWHEREDEPT='計(jì)算機(jī)'ORDEPT='信息'ORDEPT='自動(dòng)化'或

SELECTSN,SEXFROMSWHERENOT(DEPT<>'計(jì)算機(jī)'ANDDEPT<>'信息'ANDDEPT<>'自動(dòng)化')返回本節(jié)首頁(yè)3.3.4常用集函數(shù)及統(tǒng)計(jì)匯總查詢(xún)COUNT返回組中項(xiàng)目的數(shù)量。SUM返回表達(dá)式中所有值的和，或只返回DISTINCT值的和。AVG返回組中值的平均值。MAX返回組中值的最大值。MIN返回組中值的最小值。返回本節(jié)首頁(yè)表3.4常用集函數(shù)[例33]

查詢(xún)學(xué)生總?cè)藬?shù)。

SELECTCOUNT(*) FROMS[例34]查詢(xún)選修了課程的學(xué)生人數(shù)。

SELECTCOUNT(DISTINCTSNO) FROMSC

學(xué)生每選修一門(mén)課，在SC中都有一條相應(yīng)的記錄，而一個(gè)學(xué)生一般都要選修多門(mén)課程，為避免重復(fù)計(jì)算學(xué)生人數(shù)，必須在COUNT函數(shù)中用DISTINCT短語(yǔ)。[例35]計(jì)算C1課程的學(xué)生人數(shù)、最高成績(jī)、最低成績(jī)及平均成績(jī)。

SELECT COUNT(*),MAX(SCORE),MIN(SCORE),AVG(SCORE) FROMSC WHERECNO='C1'返回本節(jié)首頁(yè)3.3.5分組查詢(xún)GROUPBY子句可以將查詢(xún)結(jié)果表的各行按一列或多列取值相等的原則進(jìn)行分組。對(duì)查詢(xún)結(jié)果分組的目的是為了細(xì)化集函數(shù)的作用對(duì)象。如果未對(duì)查詢(xún)結(jié)果分組，集函數(shù)將作用于整個(gè)查詢(xún)結(jié)果，即整個(gè)查詢(xún)結(jié)果為一組對(duì)應(yīng)統(tǒng)計(jì)產(chǎn)生一個(gè)函數(shù)值。否則，集函數(shù)將作用于每一個(gè)組，即每一組分別統(tǒng)計(jì)，分別產(chǎn)生一個(gè)函數(shù)值。返回本節(jié)首頁(yè)[例36]查詢(xún)各個(gè)課程號(hào)與相應(yīng)的選課人數(shù)。

SELECTCNO,COUNT(SNO)FROMSC

GROUPBYCNO該SELECT語(yǔ)句對(duì)SC表按CNO的取值進(jìn)行分組，所有具有相同CNO值的元組為一組，然后對(duì)每一組作用集函數(shù)COUNT以求得該組的學(xué)生人數(shù)，執(zhí)行結(jié)果為：

CNO---------C13C24C31C41C52C61C71返回本節(jié)首頁(yè)[例37]

查詢(xún)有3人以上學(xué)生（包括3人）選修的課程的課程號(hào)及選修人數(shù)。

SELECTCNO,COUNT(SNO)FROMSCGROUPBYCNOHAVINGCOUNT(*)>=3結(jié)果為：

CNO----------C13C24返回本節(jié)首頁(yè)如果分組后還要求按一定的條件對(duì)這些組進(jìn)行篩選，最終只輸出滿(mǎn)足指定條件組的統(tǒng)計(jì)值，則可以使用HAVING短語(yǔ)指定篩選條件。3.3.6查詢(xún)的排序

如果沒(méi)有指定查詢(xún)結(jié)果的顯示順序，DBMS將按其最方便的順序（通常是元組在表中的先后順序）輸出查詢(xún)結(jié)果。用戶(hù)也可以用ORDERBY子句指定按照一個(gè)或多個(gè)屬性列的升序（ASC）或降序（DESC）重新排列查詢(xún)結(jié)果，其中升序ASC為缺省值。返回本節(jié)首頁(yè)查詢(xún)的排序示例1[例38]

查詢(xún)選修了3號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢(xún)結(jié)果按分?jǐn)?shù)的降序排列。

SELECTSNO,SCOREFROMSCWHERECNO='C3'

ORDERBYSCOREDESC

前面已經(jīng)提到，可能有些學(xué)生選修了C3號(hào)課程后沒(méi)有參加考試，即成績(jī)列為空值。用ORDERBY子句對(duì)查詢(xún)結(jié)果按成績(jī)排序時(shí)，在SQLSERVER2000中空值（NULL）被認(rèn)為是最小值。返回本節(jié)首頁(yè)查詢(xún)的排序示例2[例39]

查詢(xún)?nèi)w學(xué)生情況，查詢(xún)結(jié)果按所在系升序排列，對(duì)同一系中的學(xué)生按年齡降序排列。SELECT*FROMSORDERBYDEPT,AGEDESC返回本節(jié)首頁(yè)3.3.7連接查詢(xún)1、等值與非等值連接查詢(xún)當(dāng)連接運(yùn)算符為=時(shí)，稱(chēng)為等值連接。使用其它運(yùn)算符稱(chēng)為非等值連接。2、自身連接連接操作不僅可以在兩個(gè)表之間進(jìn)行，也可以是一個(gè)表與其自己進(jìn)行連接，這種連接稱(chēng)為表的自身連接。 3、外連接

返回本節(jié)首頁(yè)等值與非等值連接查詢(xún)

從概念上講DBMS執(zhí)行連接操作的過(guò)程是，首先在表1中找到第一個(gè)元組，然后從頭開(kāi)始順序掃描或按索引掃描表2，查找滿(mǎn)足連接條件的元組，每找到一個(gè)元組,就將表1中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。表2全部掃描完畢后，再到表1中找第二個(gè)元組，然后再?gòu)念^開(kāi)始順序掃描或按索引掃描表2，查找滿(mǎn)足連接條件的元組，每找到一個(gè)元組,就將表1中的第二個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。重復(fù)上述操作，直到表1全部元組都處理完畢為止。返回本節(jié)首頁(yè)等值與非等值連接查詢(xún)示例[例40]

查詢(xún)每個(gè)學(xué)生及其選修課程的情況。學(xué)生情況存放在S表中，學(xué)生選課情況存放在SC表中，所以本查詢(xún)實(shí)際上同時(shí)涉及S與SC兩個(gè)表中的數(shù)據(jù)。這兩個(gè)表之間的聯(lián)系是通過(guò)兩個(gè)表都具有的屬性SNO實(shí)現(xiàn)的。要查詢(xún)學(xué)生及其選修課程的情況，就必須將這兩個(gè)表中學(xué)號(hào)相同的元組連接起來(lái)。這是一個(gè)等值連接。完成本查詢(xún)的SQL語(yǔ)句為：

SELECT* FROMS,SC WHERES.SNO=SC.SNO返回本節(jié)首頁(yè)兩種連接運(yùn)算連接運(yùn)算中有兩種特殊情況，一種稱(chēng)為廣義笛卡爾積連接，另一種稱(chēng)為自然連接。廣義笛卡爾積連接是不帶連接謂詞的連接。兩個(gè)表的廣義笛卡爾積連接即是兩表中元組的交叉乘積，也即其中一表中的每一元組都要與另一表中的每一元組作拼接，因此結(jié)果表往往很大。如果是按照兩個(gè)表中的相同屬性進(jìn)行等值連接，且目標(biāo)列中去掉了重復(fù)的屬性列，但保留了所有不重復(fù)的屬性列，則稱(chēng)之為自然連接。返回本節(jié)首頁(yè)[例41]自然連接S和SC表。

SELECTS.SNO,SN,SEX,AGE,DEPT, CNO,SCORE FROMS,SC WHERES.SNO=SC.SNO

在本查詢(xún)中，由于SN、SEX、AGE、DEPT、CNO和SCORE屬性列在S與SC表中是唯一的，因此引用時(shí)可以去掉表名前綴。而SNO在兩個(gè)表都出現(xiàn)了，因此引用時(shí)必須加上表名前綴。該查詢(xún)的執(zhí)行結(jié)果不再出現(xiàn)SC.SNO列。返回本節(jié)首頁(yè)例3.42

查詢(xún)比李濤年齡大的學(xué)生的姓名、年齡和李濤的年齡。要查詢(xún)的內(nèi)容均在同一表S中，可以將表S分別取兩個(gè)別名，一個(gè)是X，一個(gè)是Y。將X，Y中滿(mǎn)足比李濤年齡大的行連接起來(lái)。這實(shí)際上是同一表S的大于連接。完成該查詢(xún)的SQL語(yǔ)句為：

SELECTX.SNAS姓名,X.AGEAS年齡,Y.AGEAS李濤的年齡

FROMSASX,SASYWHEREX.AGE>Y.AGEANDY.SN='李濤'結(jié)果為：姓名年齡李濤的年齡

------------------------

陳高2119

徐敏敏2019自身連接示例返回本節(jié)首頁(yè)外連接1

在通常的連接操作中，只有滿(mǎn)足連接條件的元組才能作為結(jié)果輸出，如在例40和例41的結(jié)果表中沒(méi)有關(guān)于學(xué)生S6的信息，原因在于她沒(méi)有選課，在SC表中沒(méi)有相應(yīng)的元組。但是有時(shí)我們想以S表為主體列出每個(gè)學(xué)生的基本情況及其選課情況，若某個(gè)學(xué)生沒(méi)有選課，則只輸出其基本情況信息，其選課信息為空值即可，這時(shí)就需要使用外連接（OuterJoin）。外連接的運(yùn)算符通常為*,有的關(guān)系數(shù)據(jù)庫(kù)中也用+。這樣，我們就可以如下改寫(xiě)例41了：返回本節(jié)首頁(yè)外連接2SELECTS.SNO,SN,SEX,AGE,DEPT,CNO,SCOREFROMSLEFTJOINSCONS.SNO=SC.SNO

結(jié)果為：

SNOSNSEXAGEDEPTCNOSCORE-------------------------------------------------S1李濤男19信息C190S1李濤男19信息C285…………S5吳小麗女19信息C289

S6徐敏敏女20計(jì)算機(jī)NULLNULL

從查詢(xún)結(jié)果可以看到，學(xué)號(hào)為S6的學(xué)生沒(méi)選課，但S6的信息也出現(xiàn)在查詢(xún)結(jié)果中，上例中外連接符LEFT[OUTER]JOIN稱(chēng)其為左外連接。相應(yīng)地，如果RIGHT[OUTER]JOIN則稱(chēng)為右外連接，F(xiàn)ULL[OUTER]JOIN稱(chēng)為全外連接。返回本節(jié)首頁(yè)3.3.8合并查詢(xún)

合并查詢(xún)結(jié)果就是使用UNION操作符將來(lái)自不同查詢(xún)的數(shù)據(jù)組合起來(lái)，形成一個(gè)具有綜合信息的查詢(xún)結(jié)果。UNION操作會(huì)自動(dòng)將重復(fù)的數(shù)據(jù)行剔除。必須注意的是，參加合并查詢(xún)結(jié)果的各子查詢(xún)的使用的表結(jié)構(gòu)應(yīng)該相同，即各子查詢(xún)的數(shù)據(jù)數(shù)目相同，對(duì)應(yīng)的數(shù)據(jù)類(lèi)型要相融。返回本節(jié)首頁(yè)合并查詢(xún)示例

[例43]從SC數(shù)據(jù)表中查詢(xún)出學(xué)號(hào)為“S1”的同學(xué)的學(xué)號(hào)和總分，再?gòu)腟C數(shù)據(jù)表中查詢(xún)出學(xué)號(hào)為“S5”的同學(xué)的學(xué)號(hào)和總分，然后將兩個(gè)查詢(xún)結(jié)果合并成一個(gè)結(jié)果集。SELECTSNOAS學(xué)號(hào)，SUM(SCORE)AS總分FROMSCWHERE(SNO='S1')GROUPBYSNOUNIONSELECTSNOAS學(xué)號(hào),SUM(SCORE)AS總分FROMSCWHERE(SNO='S5')GROUPBYSNO返回本節(jié)首頁(yè)3.3.9嵌套查詢(xún)1、帶有IN謂詞的子查詢(xún)2、帶有比較運(yùn)算符的子查詢(xún)3、帶有ANY或ALL謂詞的子查詢(xún)4、帶有EXISTS謂詞的子查詢(xún)返回本節(jié)首頁(yè)帶有IN謂詞的子查詢(xún)[例44]查詢(xún)與“王林”在同一個(gè)系學(xué)習(xí)的學(xué)生的學(xué)號(hào)、姓名和所在系。查詢(xún)與“王林”在同一個(gè)系學(xué)習(xí)的學(xué)生，可以首先確定“王林”所在系名，然后再查找所有在該系學(xué)習(xí)的學(xué)生。所以可以分步來(lái)完成此查詢(xún)：①確定“劉晨”所在系名

SELECTDEPTFROMSWHERESN='王林'結(jié)果為：

DEPT--------------------

計(jì)算機(jī)②查找所有在計(jì)算機(jī)系學(xué)習(xí)的學(xué)生。

SELECTSNO,SN,DEPTFROMSWHEREDEPT='計(jì)算機(jī)'返回本節(jié)首頁(yè)結(jié)果為：

SNOSNDEPT-------------------S2王林計(jì)算機(jī)

S6徐敏敏計(jì)算機(jī)分步寫(xiě)查詢(xún)畢竟比較麻煩，上述查詢(xún)實(shí)際上可以用子查詢(xún)來(lái)實(shí)現(xiàn)，即將第一步查詢(xún)嵌入到第二步查詢(xún)中，用以構(gòu)造第二步查詢(xún)的條件。SQL語(yǔ)句如下：SELECTSNO,SN,DEPTFROMSWHEREDEPTIN(SELECTDEPTFROMSWHERESN='王林')返回本節(jié)首頁(yè)本例中的查詢(xún)也可以用我們前面學(xué)過(guò)的表的自身連接查詢(xún)來(lái)完成：

SELECTS1.SNO,S1.SN,S1.DEPTFROMSS1,SS2WHERES1.DEPT=S2.DEPTANDS2.SN='王林'返回本節(jié)首頁(yè)帶有比較運(yùn)算符的子查詢(xún)示例

例如：在例44中，由于一個(gè)學(xué)生只可能在一個(gè)系學(xué)習(xí)，也就是說(shuō)內(nèi)查詢(xún)王林所在系的結(jié)果是一個(gè)唯一值，因此該查詢(xún)也可以用比較運(yùn)算符來(lái)實(shí)現(xiàn)，其SQL語(yǔ)句如下：

SELECTSNO,SN,DEPT FROMS WHEREDEPT=(SELECTDEPTFROMS WHERESN='王林‘)返回本節(jié)首頁(yè)帶有ANY或ALL謂詞的子查詢(xún)示例1[例46]

查詢(xún)其他系中比信息系任一學(xué)生年齡小的學(xué)生名單。SELECTSN,AGEFROMSWHEREAGE<ALL(SELECTAGEFROMSWHEREDEPT='信息')ANDDEPT<>'信息'ORDERBYAGEDESC返回本節(jié)首頁(yè)帶有ANY或ALL謂詞的子查詢(xún)示例2

本查詢(xún)實(shí)際上也可以用集函數(shù)（請(qǐng)參閱表3.6）實(shí)現(xiàn)。SELECTSN,AGEFROMSWHEREAGE<(SELECTMIN(AGE)FROMSWHEREDEPT='信息')ANDDEPT<>'信息'ORDERBYAGEDESC

事實(shí)上，用集函數(shù)實(shí)現(xiàn)子查詢(xún)通常比直接用ANY或ALL查詢(xún)效率要高。返回本節(jié)首頁(yè)帶有EXISTS謂詞的子查詢(xún)示例1[例47]

查詢(xún)所有選修了C1號(hào)課程的學(xué)生姓名。查詢(xún)所有選修了C1號(hào)課程的學(xué)生姓名涉及S關(guān)系和SC關(guān)系，我們可以在S關(guān)系中依次取每個(gè)元組的SNO值，用此S.SNO值去檢查SC關(guān)系，若SC中存在這樣的元組，其SC.SNO值等于用來(lái)檢查的S.SNO值，并且其SC.CNO='C1'，則取此S.SN送入結(jié)果關(guān)系。將此想法寫(xiě)成SQL語(yǔ)句就是：SELECTSNFROMSWHEREEXISTS

(SELECT*FROMSCWHERESNO=S.SNOANDCNO='C1')返回本節(jié)首頁(yè)帶有EXISTS謂詞的子查詢(xún)示例2[例49]

查詢(xún)選修了全部課程的學(xué)生姓名。由于沒(méi)有全稱(chēng)量詞，我們將題目的意思轉(zhuǎn)換成等價(jià)的存在量詞的形式：查詢(xún)這樣的學(xué)生姓名，沒(méi)有一門(mén)課程是他不選的。該查詢(xún)涉及三個(gè)關(guān)系，存放學(xué)生姓名的S表，存放所有課程信息的C表，存放學(xué)生選課信息的SC表。其SQL語(yǔ)句為：

SELECTSNFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMSCWHERESNO=S.SNOANDCNO=C.CNO))返回本節(jié)首頁(yè)3.3.10子查詢(xún)別名表達(dá)式的使用*

在查詢(xún)語(yǔ)句中，直接使用子查詢(xún)別名的表達(dá)形式不失為一種簡(jiǎn)捷的查詢(xún)表達(dá)方法。以下舉例說(shuō)明。例3.50

在選修C2課程成績(jī)大于該課平均成績(jī)的學(xué)生中，查詢(xún)還選C1課程的學(xué)生學(xué)號(hào)、姓名與C1課程成績(jī)。SELECTS.SNO,S.SN,SCOREFROMSC,S,(SELECTSNOFROMSCWHERECNO='C2'ANDSCORE>(SELECTAVG(SCORE)FROMSCWHERECNO='C2'))AST1(sno)WHERESC.SNO=T1.SNOANDS.SNO=T1.SNOANDCNO='C1'

注意：通過(guò)AS關(guān)鍵字給子查詢(xún)命名的表達(dá)方式，別名后的括號(hào)中可對(duì)應(yīng)給子查詢(xún)列指定列名。一旦命名，別名表達(dá)式可作為一般表一樣的使用。返回本節(jié)首頁(yè)3.3.10子查詢(xún)別名表達(dá)式的使用*返回本節(jié)首頁(yè)[例3.51]

查詢(xún)選課門(mén)數(shù)唯一的學(xué)生的學(xué)號(hào)（例：若只有S1學(xué)號(hào)的學(xué)生選2門(mén)，則S1應(yīng)為結(jié)果之一）SELECTt3.SNOFROM(SELECTCTFROM(SELECTSNO,COUNT(SNO)ASCTFROMSCGROUPBYSNO)AST1(sno,ct)GROUPBYCTHAVINGCOUNT(*)=1)AST2(ct),(SELECTSNO,COUNT(SNO)ASCTFROMSCGROUPBYSNO)AST3(sno,ct)WHERET2.CT=T3.CT3.3.10子查詢(xún)別名表達(dá)式的使用*返回本節(jié)首頁(yè)例3.52

查詢(xún)學(xué)習(xí)編號(hào)為“C2”課成績(jī)?yōu)榈?名的學(xué)生的學(xué)號(hào)（設(shè)選C2課的學(xué)生人數(shù)>=3）。SELECTSC.SNOFROM(SELECTMIN(SCORE)FROM(SELECTDISTINCTTOP3SCOREFROMSCWHERECNO='C2'ORDERBYSCOREDESC)ASt1(SCORE))ASt2(SCORE)INNERJOINSCONt2.SCORE=SC.SCOREWHERECNO='C2'思考：讀者可試試若不用子查詢(xún)別名表達(dá)式的表示方法，這些查詢(xún)?cè)撊绾伪磉_(dá)？3.3.11存儲(chǔ)查詢(xún)結(jié)果到表中

使用SELECT…INTO語(yǔ)句可以將查詢(xún)到的結(jié)果存儲(chǔ)到一個(gè)新建的數(shù)據(jù)庫(kù)表或臨時(shí)表中。

[例53]從SC數(shù)據(jù)表中查詢(xún)出所有同學(xué)的學(xué)號(hào)和總分，并將查詢(xún)結(jié)果存放到一個(gè)新的數(shù)據(jù)表Cal_Table中。SELECTSNOAS學(xué)號(hào),SUM(SCORE)AS總分

INTOCal_TableFROMSCGROUPBYSNO返回本節(jié)首頁(yè)3.4SQL數(shù)據(jù)更新3.4.1插入數(shù)據(jù)3.4.2修改數(shù)據(jù)3.4.3刪除數(shù)據(jù)

返回本章首頁(yè)3.4.1插入數(shù)據(jù)1、插入單個(gè)元組插入單個(gè)元組的INSERT語(yǔ)句的格式為：

INSERT[INTO]<表名>[(<屬性列1>[,<屬性列2>]...)]VALUES(<常量1>[,<常量2>]...)2、插入子查詢(xún)結(jié)果 插入子查詢(xún)結(jié)果的INSERT語(yǔ)句的格式為：

INSERTINTO<表名>[(<屬性列1>[,<屬性列2>]...)]

子查詢(xún)

返回本節(jié)首頁(yè)插入單個(gè)元組[例51]將一個(gè)新學(xué)生記錄（學(xué)號(hào)：S7；姓名：陳冬；性別：男；年齡：18歲；所在系：信息）插入S表中。

INSERTINTOSVALUES('S7','陳冬','男','18','信息')[例52]

插入一條選課記錄（'S7'，'C1'）。

INSERTINTOSC(SNO,CNO)VALUES('S7','C1')

新插入的記錄在SCORE列上取空值。返回本節(jié)首頁(yè)插入子查詢(xún)結(jié)果[例53]

對(duì)每一個(gè)系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫(kù)。對(duì)于這道題，首先要在數(shù)據(jù)庫(kù)中建立一個(gè)有兩個(gè)屬性列的新表，其中一列存放系名，另一列存放相應(yīng)系的學(xué)生平均年齡。

CREATETABLEDEPTAGE(DEPTCHAR(15),AVGAGETINYINT)

然后對(duì)數(shù)據(jù)庫(kù)的S表按系分組求平均年齡，再把系名和平均年齡存入新表中。

INSERTINTODEPTAGE(DEPT,AVGAGE)SELECTDEPT,AVG(AGE)FROMSGROUPBYDEPT返回本節(jié)首頁(yè)3.4.2修改數(shù)據(jù)UPDATE<表名>SET<列名>=<表達(dá)式>[,<列名>=<表達(dá)式>]...[WHERE<條件>]

其功能是修改指定表中滿(mǎn)足WHERE子句條件的元組。其中SET子句用于指定修改方法，即用<表達(dá)式>的值取代相應(yīng)的屬性列值。如果省略WHERE子句，則表示要修改表中的所有元組。返回本節(jié)首頁(yè)修改某一個(gè)元組的值[例54]將學(xué)生S3(為學(xué)號(hào))的年齡改為20歲。UPDATESSETAGE=20WHERESNO='S3'返回本節(jié)首頁(yè)修改多個(gè)元組的值[例55]將所有學(xué)生的年齡增加1歲。UPDATESSETAGE=AGE+1

返回本節(jié)首頁(yè)帶子查詢(xún)的修改語(yǔ)句[例56]將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績(jī)置零。

UPDATESCSETSCORE=0WHERE'計(jì)算機(jī)'=(SELECTDEPTFROMSWHERESC.SNO=S.SNO)或

UPDATESCSETSCORE=0WHERESNOIN(SELECTSNOFROMSWHEREDEPT='計(jì)算機(jī)')返回本節(jié)首頁(yè)3.4.3刪除數(shù)據(jù)DELETE[FROM]<表名>[WHERE<條件>]

DELETE語(yǔ)句的功能是從指定表中刪除滿(mǎn)足WHERE子句條件的所有元組。如果省略WHERE子句，表示刪除表中全部元組，但表的定義仍在字典中。也就是說(shuō)，DELETE語(yǔ)句刪除的只是表中的數(shù)據(jù)，而不包括表的結(jié)構(gòu)定義。返回本節(jié)首頁(yè)1、刪除某一個(gè)元組的值[例57]

刪除學(xué)號(hào)為S7的學(xué)生記錄。

DELETEFROMSWHERESNO='S7'2、刪除多個(gè)元組的值[例58]刪除所有的學(xué)生選課記錄。

DELETEFROMSC3、帶子查詢(xún)的刪除語(yǔ)句子查詢(xún)同樣也可以嵌套在DELETE語(yǔ)句中[例59]刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。

DELETEFROMSCWHERE'計(jì)算機(jī)'=(SELECTDEPTFROMSWHERES.SNO=SC.SNO)返回本節(jié)首頁(yè)更新操作舉例

更新操作練習(xí)題

返回本節(jié)首頁(yè)3.5視圖3.5.1定義和刪除視圖3.5.2查詢(xún)視圖3.5.3更新視圖

返回本章首頁(yè)視圖的相關(guān)概念關(guān)于視圖在關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)中，視圖為用戶(hù)提供了多種看待數(shù)據(jù)庫(kù)數(shù)據(jù)的方法與途徑，是關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)中的一種重要對(duì)象。視圖是從一個(gè)或幾個(gè)基本表（或視圖）導(dǎo)出的表，它與基本表不同，是一個(gè)虛表。通過(guò)視圖能操作數(shù)據(jù)，基本表數(shù)據(jù)的變化也能在刷新的視圖中反映出來(lái)。從這個(gè)意義上講，視圖像一個(gè)窗口或望遠(yuǎn)鏡，透過(guò)它可以看到數(shù)據(jù)庫(kù)中自己感興趣的數(shù)據(jù)及其變化。視圖在概念上與基本表等同，一經(jīng)定義，就可以和基本表一樣被查詢(xún)、被刪除，我們也可以在一個(gè)視圖上再定義新的視圖，但對(duì)視圖的更新（插入、刪除、修改）操作則有一定的限制。返回本節(jié)首頁(yè)3.5.1創(chuàng)建和刪除視圖1、創(chuàng)建視圖CREATEVIEW<視圖名>[(<列名>[,<列名>]...)]AS<子查詢(xún)>

2、刪除視圖DROPVIEW<視圖名>

[例62]刪除視圖IS_S1。

DROPVIEWIS_S1返回本節(jié)首頁(yè)創(chuàng)建視圖示例[例61]

建立信息系選修了C1號(hào)課程的學(xué)生的視圖。CREATEVIEWIS_S1(SNO,SN,SCORE)ASSELECTS.SNO,SN,SCOREFROMS,SCWHEREDEPT='信息'ANDS.SNO=SC.SNOANDSC.CNO='C1'返回本節(jié)首頁(yè)查詢(xún)視圖示例[例63]

在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生。

SELECTSNO,AGEFROMIS_SWHERESage<20

視圖是定義在基本表上的虛表，它可以和其他基本表一起使用，實(shí)現(xiàn)連接查詢(xún)或嵌套查詢(xún)。這也就是說(shuō)，在關(guān)系數(shù)據(jù)庫(kù)的三級(jí)模式結(jié)構(gòu)中，外模式不僅包括視圖，而且還可以包括一些基本表。返回本節(jié)首頁(yè)更新視圖示例[例64]將信息系學(xué)生視圖IS_S中學(xué)號(hào)為S3的學(xué)生姓名改為“劉辰”。

UPDATEIS_SSETSN='劉辰'WHERESNO='S3'返回本節(jié)首頁(yè)不同的數(shù)據(jù)庫(kù)對(duì)視圖的更新有不同的規(guī)定，如下是IBM的DB2數(shù)據(jù)庫(kù)中視圖不允許更新的規(guī)定：1.若視圖是由兩個(gè)以上基本表導(dǎo)出的，則此視圖不允許更新。2.若視圖的字段來(lái)自字段表達(dá)式或常數(shù)，則不允許對(duì)此視圖執(zhí)行INSERT和UPDATE操作，但允許執(zhí)行DELETE操作。3.若視圖的字段來(lái)自集函數(shù)，則此視圖不允許更新。4.若視圖定義中含有GROUPBY子句，則此視圖不允許更新。5.若視圖定義中含有DISTINCT短語(yǔ)，則此視圖不允許更新。6.若視圖定義中有嵌套查詢(xún)，并且內(nèi)層查詢(xún)的FROM子句中涉及的表也是導(dǎo)出該視圖的基本表，則此視圖不允許更新。7.一個(gè)不允許更新的視圖上定義的視圖也不允許更新。

返回本節(jié)首頁(yè)3.6SQL數(shù)據(jù)控制3.6.1權(quán)限與角色3.6.2系統(tǒng)權(quán)限與角色的授予與收回3.6.3對(duì)象權(quán)限與角色的授予與收回

返回本章首頁(yè)3.6.1權(quán)限與角色1、權(quán)限

SQL系統(tǒng)安全機(jī)制：視圖機(jī)制、權(quán)限機(jī)制 數(shù)據(jù)庫(kù)中權(quán)限：系統(tǒng)權(quán)限、對(duì)象權(quán)限2、系統(tǒng)權(quán)限 系統(tǒng)權(quán)限是指數(shù)據(jù)庫(kù)用戶(hù)能夠?qū)?shù)據(jù)庫(kù)系統(tǒng)進(jìn)行某種特定的操作的權(quán)力。它由數(shù)據(jù)庫(kù)管理員授予其他用戶(hù)。如創(chuàng)建一個(gè)基本表（CREATETABLE）的權(quán)力。3、對(duì)象權(quán)限 對(duì)象權(quán)限是指數(shù)據(jù)庫(kù)用戶(hù)在指定的數(shù)據(jù)庫(kù)對(duì)象上進(jìn)行某種特定的操作的權(quán)力。返回本節(jié)首頁(yè)3.6.1權(quán)限與角色1、角色

角色是多種權(quán)限的集合，可以把角色授予用戶(hù)或角色。當(dāng)要為某一用戶(hù)同時(shí)授予或收回多項(xiàng)權(quán)限時(shí)，則可以把這些權(quán)限定義為一個(gè)角色，對(duì)此角色進(jìn)行操作。這樣就避免了許多重復(fù)性的工作，簡(jiǎn)化了管理數(shù)據(jù)庫(kù)用戶(hù)權(quán)限的工作。返回本節(jié)首頁(yè)權(quán)限機(jī)制

在SQL系統(tǒng)中，有兩個(gè)安全機(jī)制，一種是視圖機(jī)制，當(dāng)用戶(hù)通過(guò)視圖訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，他不能訪問(wèn)此視圖外的數(shù)據(jù)，它提供了一定的安全性。而主要的安全機(jī)制是權(quán)限機(jī)制。權(quán)限機(jī)制的基本思想是給用戶(hù)授予不同類(lèi)型的權(quán)限，在必要時(shí)，可以收回授權(quán)，使用戶(hù)能夠進(jìn)行的數(shù)據(jù)庫(kù)操作以及所操作的數(shù)據(jù)限定在指定范圍內(nèi)，禁止用戶(hù)超越權(quán)限對(duì)數(shù)據(jù)庫(kù)進(jìn)行非法的操作，從而保證數(shù)據(jù)庫(kù)的安全性。返回本節(jié)首頁(yè)3.6.2系統(tǒng)權(quán)限與角色的授予與收回1、系統(tǒng)權(quán)限與角色的授予GRANT<系統(tǒng)權(quán)限>[,<系統(tǒng)權(quán)限>]...TO<用戶(hù)>|角色[,<用戶(hù)>|角色]...[WITHGRANTOPTION]2、系統(tǒng)權(quán)限與角色的收回REVOKE<系統(tǒng)權(quán)限>[,<系統(tǒng)權(quán)限>]…FROM<用戶(hù)名>|<角色>|PUBLIC[,<用戶(hù)名>|<角色>]…返回本節(jié)首頁(yè)系統(tǒng)權(quán)限與角色的授予與收回示例[例65]把創(chuàng)建表的權(quán)限授給用戶(hù)U1。

GRANTCREATETABLETOU1[例66]

把U1所擁有的創(chuàng)建表權(quán)限收回。

REVOKECREATETABLEFROMU1返回本節(jié)首頁(yè)3.6.3對(duì)象權(quán)限與角色的授予與收回1、對(duì)象權(quán)限與角色的授予

GRANTALL|<對(duì)象權(quán)限>[（列名[，列名]…）][,<對(duì)象權(quán)限>]...ON<對(duì)象名>TO<用戶(hù)>|<角色>|PUBLIC[,<用戶(hù)>|<角色>]...[WITHGRANTOPTION]2、對(duì)象權(quán)限與角色的收回

REVOKE<對(duì)象權(quán)限>|<角色>[,<對(duì)象權(quán)限>|<角色>]…FROM<用戶(hù)名>|<角色>|PUBLIC[,<用戶(hù)名>|<角色>]…返回本節(jié)首頁(yè)權(quán)限命令對(duì)象權(quán)限與角色的授予與收回示例[例67]把查詢(xún)S表權(quán)限授給用戶(hù)U1。

GRANTSELECTONSTOU1[例68]

收回用戶(hù)U1對(duì)S表的查詢(xún)權(quán)限。

REVOKESELECTONSFROMU1

返回本節(jié)首頁(yè)3.7嵌入式SQL語(yǔ)言*3.7.1嵌入式SQL簡(jiǎn)介3.7.2嵌入式SQL要解決的三個(gè)問(wèn)題3.7.3第四代數(shù)據(jù)庫(kù)應(yīng)用開(kāi)發(fā)工具或高級(jí)語(yǔ)言中SQL的使用

返回本章首頁(yè)嵌入式SQL的預(yù)編譯、編譯、連接與運(yùn)行處理過(guò)程

源程序（主語(yǔ)言+SQL）預(yù)編譯系統(tǒng)主語(yǔ)言源程序主語(yǔ)言編譯系統(tǒng)目標(biāo)代碼連接與運(yùn)行返回本節(jié)首頁(yè)3.7.2嵌入式SQL要解決的三個(gè)問(wèn)題1、區(qū)分SQL語(yǔ)句與主語(yǔ)言語(yǔ)句2、數(shù)據(jù)庫(kù)工作單元和程序工作單元之間的通信3、協(xié)調(diào)SQL集合式操作與高級(jí)語(yǔ)言記錄式處理之間的關(guān)系4、舉例返回本節(jié)首頁(yè)voi