关系数据库标准语言SQL课件

關係資料庫標準語言SQL

3.1.1語言的發展及標準化在70年代初，E.F.Codd首先提出了關係模型。70年代中期，IBM公司在研製SYSTEMR關係資料庫管理系統中研製了SQL語言，最早的SQL語言（叫SEQUEL2）是在1976年11月的IBMJournalofR&D上公佈的。1979年ORACLE公司首先提供商用的SQL，IBM公司在DB2和SQL/DS資料庫系統中也實現了SQL。1986年10月，美國ANSI採用SQL作為關係資料庫管理系統的標準語言(ANSIX3.135-1986），後為國際標準化組織（ISO）採納為國際標準。

返回本節首頁3.1.1語言的發展及標準化1989年，美國ANSI採納在ANSIX3.135-1989報告中定義的關係資料庫管理系統的SQL標準語言，稱為ANSISQL89。1992年，ISO又推出了SQL92標準，也稱為SQL2。目前SQL99(也稱為SQL3)在起草中，增加了面向對象的功能。結構化查詢語言SQL(StructuredQueryLanguage)是一種介於關係代數與關係演算之間的語言，其功能包括查詢、操縱、定義和控制四個方面，是一個通用的、功能極強的關係資料庫語言。目前已成為關係資料庫的標準語言，廣泛應用於各種資料庫。

返回本節首頁關係資料庫三級模式結構SQL語言支持關係資料庫三級模式結構，如圖3.1所示。其中外模式對應於視圖（View）和部分基本表（BaseTable），模式對應於基本表，內模式對應於存儲檔。3.1.2SQL語言的基本概念返回本節首頁SQL視圖1基本表2視圖2基本表3基本表4基本表1存儲檔1存儲檔2外模式模式內模式圖3.1資料庫三級模式結構返回本節首頁圖3.2關係資料庫三級模式結構示意圖

返回本節首頁基本表是本身獨立存在的表，在SQL中一個關係就對應一個表。一些基本表對應一個存儲檔，一個表可以有若干索引，索引也存放在存儲檔中。視圖是從基本表或其他視圖中導出的表，它本身不獨立存儲在資料庫中，也就是說資料庫中只存放視圖的定義而不存放視圖對應的數據，這些數據仍存放在導出視圖的基本表中，因此視圖是一個虛表。存儲檔的物理結構及存儲方式等組成了關係資料庫的內模式。存儲檔的物理結構及存儲方式等不同資料庫管理系統往往是不同的，一般也是不公開的。3.1.2SQL語言的基本概念返回本節首頁視圖和基本表是SQL語言的主要操作對象，用戶可以用SQL語言對視圖和基本表進行各種操作。在用戶眼中，視圖和基本表都是關係表，而存儲檔對用戶是透明的。

3.1.2SQL語言的基本概念返回本節首頁3.1.3SQL語言的主要特點1、綜合統一2、高度非過程化3、面向集合的操作方式4、以同一種語法結構提供兩種使用方式5、語言簡捷，易學易用

返回本節首頁3.2SQL數據定義

3.2.1字段數據類型

3.2.2創建、修改和刪除數據表

3.2.3設計、創建和維護索引

返回本章首頁3.2.1字段數據類型整數數據類型:bigint,int,smallint,tinyint精確數值類型:numeric,decimal近似浮點數值數據類型:float,real日期時間數據類型:datetime,smalldatetime字串數據類型:char,varchar,textUnicode字串數據類型:nchar,nvarchar,ntext3.2.1字段數據類型二進位數據類型:binary、varbinary、image貨幣數據類型:money,smallmoney標記數據類型:timestamp,uniqueidentifier返回本節首頁3.2.2創建、修改和刪除數據表1．定義基本表2．修改基本表3．刪除基本表返回本節首頁定義基本表CREATETABLE<表名>(<列名><數據類型>[列級完整性約束條件][，<列名><數據類型>[列級完整性約束條件]]…[,<表級完整性約束條件>])

其中<表名>是所要定義的基本表的名字，必須是合法的識別字，最多可有128個字元，但本地臨時表的表名（名稱前有一個編號符#）最多只能包含116個字元。表名不允許重名，一個表可以由一個或多個屬性（列）組成。建表的同時通常還可以定義與該表有關的完整性約束條件，這些完整性約束條件被存入系統的數據字典中，當用戶操作表中數據時由DBMS自動檢查該操作是否違背這些完整性約束條件。如果完整性約束條件涉及到該表的多個屬性列，則必須定義在表級上，否則既可以定義在列級也可以定義在表級。返回本節首頁關係模型的完整性規則(1)實體完整性

①主碼（PRIMARYKEY）

②空值（NULL/NOTNULL）

③惟一值（UNIQUE）(2)參照完整性

FOREIGNKEY約束指定某一個列或一組列作為外部鍵(3)用戶自定義的完整性約束規則返回本節首頁完整性示例下麵我們以一個“學生-課程”資料庫為例來說明，表內容請參下圖。“學生-課程”資料庫中包括三個表：（1）“學生”表S由學號（SNO）、姓名（SN）、性別（SEX）、年齡（AGE）、所在系（DEPT）五個屬性組成，可記為：S（SNO，SN，SEX，AGE，DEPT）；（2）“課程”表C由課程號（CNO）、課程名（CN）、學分（CT）三個屬性組成，可記為：C（CNO，CN，CT）；（3）“學生選課”表SC由學號（SNO）、課程號（CNO）、成績（SCORE）三個屬性組成，可記為：SC（SNO，CNO，SCORE）。返回本節首頁S學號SNO姓名SN性別SEX年齡AGE系別DEPTS1李濤男19資訊S2王林女18電腦S3陳高女21自動化S4張傑男17自動化S5吳小麗女19資訊S6徐敏敏女20電腦返回本節首頁創建命令C課程號CNO課程名CN學分CTC1C語言4C2離散數學2C3操作系統3C5數據結構4C6資料庫4C7組合語言3C8資訊基礎2返回本節首頁SC學號SNO課程號SNO成績SCORES1C190S1C285S2C184S2C294S2C383S3C173S3C768S3C488S3C585S4C265S4C590S4C679S5C289返回本節首頁例1

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

CREATETABLEC(CNOCHAR(5)NOTNULLPRIMARYKEY,CNVARCHAR(20),CTINTCHECK(CT>=1))[例3]建立“選修”關係表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))返回本節首頁修改基本表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]}}返回本節首頁修改基本表說明其中:<表名>指定需要修改的基本表，ADD子句用於增加新列和新的完整性約束條件，DROP子句用於刪除指定的完整性約束條件或原有列，ALTER子句用於修改原有的列定義。{CHECK|NOCHECK}CONSTRAINT

指定啟用或禁用constraint_name。如果禁用，將來插入或更新該列時將不用該約束條件進行驗證。此選項只能與FOREIGNKEY和CHECK約束一起使用。{ENABLE|DISABLE}TRIGGER指定啟用或禁用trigger_name。當一個觸發器被禁用時，它對表的定義依然存在；然而，當在表上執行INSERT、UPDATE或DELETE語句時，觸發器中的操作將不執行，除非重新啟用該觸發器。返回本節首頁修改表示例[例4]

向S表增加“入學時間”列，其數據類型為日期型。

ALTERTABLESADDSCOMEDATETIME不論基本表中原來是否已有數據，新增加的列一律為空值。[例5]將年齡的數據類型改為半字長整數。

ALTERTABLESALTERCOLUMNAGESMALLINT修改原有的列定義，會使列中數據作新舊類型的自動轉化，有可能會破壞已有數據。[例6]

刪除例4增加的“入學時間”列。

ALTERTABLESDROPCOLUMNSCOME[例7]

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

ALTERTABLESNOCHECKCONSTRAINTC_F返回本節首頁刪除基本表DROPTABLE<表名>

[例8]

刪除S表。

DROPTABLES返回本節首頁3.2.3設計、創建和維護索引1．索引的概念2．創建索引3．刪除索引

返回本節首頁索引的概念資料庫中的索引是為了加速對表中元組（或記錄）的檢索而創建的一種分散存儲結構(如B+樹數據結構)，它實際上是記錄的關鍵字與其相應地址的對應表。索引是對表或視圖而建立的，由索引頁面組成。改變表中的數據(如增加或刪除記錄)時，索引將自動更新。索引建立後，在查詢使用該列時，系統將自動使用索引進行查詢。索引是把雙刃劍，由於要建立索引頁面，索引也會減慢更新的速度。索引數目無限制，但索引越多，更新數據的速度越慢。對於僅用於查詢的表可多建索引，對於數據更新頻繁的表則應少建索引。返回本節首頁索引的概念按照索引記錄的存放位置可分為聚集索引(ClusteredIndex)與非聚集索引(Non-ClusteredIndex)兩類。聚集索引是指索引項的順序與表中記錄的物理順序一致的索引組織；非聚集索引按照索引的字段排列記錄，但是排列的結果並不會存儲在表中，而是另外存儲。在檢索記錄時，聚集索引會比非聚集索引速度快，一個表中只能有一個聚集索引，而非聚集索引可以有多個。返回本節首頁創建索引CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX<索引名>ON{<表名>|<視圖名>}(<列名>[ASC|DESC][,...n])[WITH<索引選項>[,...n]][ON檔組名][例9]

為學生-課程資料庫中的S、C、SC三個表建立索引。其中S表按學號昇冪建惟一索引，C表按課程號降序建立聚簇索引，SC表按學號昇冪和課程號降序建非聚簇索引。CREATEUNIQUEINDEXS_SNOONS(SNO)CREATECLUSTEREDINDEXC_CNOONC(CNODESC)CREATENONCLUSTEREDINDEXSC_SNO_CNOONSC(SNOASC,CNODESC)返回本節首頁刪除索引返回本節首頁刪除索引的命令語法：DROPINDEX表名.<索引名>|

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

刪除S表的S_SNO索引。DROPINDEXS.S_SNO3.3SQL數據查詢3.3.1SELECT命令的格式及其含義3.3.2SELECT子句的基本使用3.3.3WHERE子句的基本使用3.3.4常用庫函數及統計匯總查詢3.3.5分組查詢3.3.6查詢的排序3.3.7連接查詢3.3.8合併查詢3.3.9嵌套查詢3.3.10子查詢別名運算式的使用*

3.3.11存儲查詢結果到表中

返回本章首頁3.3.1SELECT命令的格式及其含義SELECT[ALL|DISTINCT]<目標列表達式>[,<目標列表達式>]...[INTO<新表名>]FROM<表名或視圖名>[,<表名或視圖名>]...[WHERE<條件運算式>][GROUPBY<列名1>...[HAVING<條件運算式>]][ORDERBY<列名2>[ASC|DESC]]...返回本節首頁3.3.2SELECT子句的基本使用1.查詢指定列2.查詢全部列3.查詢經過計算的值返回本節首頁查詢指定列[例11]

查詢全體學生的學號與姓名。

SELECTSNO,SNFROMS<目標列表達式>中各個列的先後順序可以與表中的順序不一致。也就是說，用戶在查詢時可以根據應用的需要改變列的顯示順序。[例12]

查詢全體學生的姓名、學號、所在系。

SELECTSN,SNO,DEPTFROMS

這時結果表中的列的順序與基表中不同，是按查詢要求，先列出姓名屬性，然後再列出學號和所在系屬性。返回本節首頁查詢全部列[例13]

查詢全體學生的詳細記錄。

SELECT*FROMS該SELECT語句實際上是無條件地把S表的全部資訊都查詢出來，所以也稱為全表查詢，這是最簡單的一種查詢命令形式。它等價於如下命令：

SELECTSNO,SN,SEX,AGE,DEPTFROMS返回本節首頁查詢經過計算的值SELECT子句的<目標列表達式>不僅可以是表中的屬性列，也可以是有關運算式，即可以將查詢出來的屬性列經過一定的計算後列出結果。[例14]

查全體學生的姓名及其出生年份。

SELECTSN,2005-AGEFROMS

本例中，<目標列表達式>中第二項不是通常的列名，而是一個計算運算式，是用當前的年份（假設為2005年）減去學生的年齡，這樣，所得的即是學生的出生年份。輸出的結果為：

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

李濤1986

王林1987

陳高1984

張傑1988

吳小麗1986

徐敏敏1985返回本節首頁3.3.3WHERE子句的基本使用1.消除取值重複的行2.指定WHERE查詢條件返回本節首頁消除取值重複的行[例16]

查所有選修過課的學生的學號。SELECTSNOFROMSC結果為：

SNO----S1 S1S2S2S2S3S3S3S3S4S4S4S5該查詢結果裏包含了許多重複的行。如果想去掉結果表中的重複行，必須指定DISTINCT短語：SELECTDISTINCTSNOFROMSC執行結果為：

SNO----S1S2S3S4S5返回本節首頁常用的查詢條件查詢條件謂詞比較運算符=,>,<.>=,<=,!=,<>,!>,!<；Not(上述比較運算符構成的比較關係運算式)確定範圍BETWEENAND，NOTBETWEENAND確定集合IN，NOTIN字元匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重條件AND，OR，NOT返回本節首頁比較運算符[例17]

查電腦系全體學生的名單。

SELECTSNFROMSWHEREDEPT='電腦'[例18]

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

SELECTSN,AGEFROMSWHEREAGE<20或

SELECTSN,AGEFROMSWHERENOTAGE>=20返回本節首頁確定範圍[例20]

查詢年齡在20至23歲之間的學生的姓名、系別和年齡。

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

查詢年齡不在20至23歲之間的學生姓名、系別和年齡。

SELECTSN,DEPT,AGEFROMSWHEREAGENOTBETWEEN20AND23返回本節首頁確定集合[例22]

查詢資訊系、自動化系和電腦系的學生的姓名和性別。SELECTSN,SEXFROMSWHEREDEPTIN('資訊','自動化','電腦')與IN相對的謂詞是NOTIN，用於查找屬性值不屬於指定集合的元組。[例23]

查既不是資訊系、數學系，也不是電腦科學系的學生的姓名和性別。SELECTSN,SEXFROMSWHEREDEPTNOTIN('資訊','自動化','電腦')返回本節首頁字元匹配通配符描述%（百分號）代表零個或更多字元的任意字串。_（下劃線）代表任何單個字元（長度可以為0）。[]（中擴號）指定範圍([a-f])或集合([abcdef])中的任何單個字元。[^]不屬於指定範圍([a-f])或集合([abcdef])的任何單個字元。返回本節首頁字元匹配示例1[例24]

查所有姓劉的學生的姓名、學號和性別。

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

查姓“歐陽”且全名為三個漢字的學生的姓名。

SELECTSNFROMSWHERESNLIKE'歐陽_'返回本節首頁字元匹配示例2[例26]

查名字中第二字為“陽”字的學生的姓名和學號。

SELECTSN,SNOFROMSWHERESNLIKE'_陽%'[例27]

查所有不姓劉的學生姓名。

SELECTSN,SNO,SEXFROMSWHERESNNOTLIKE'劉%'返回本節首頁字元匹配示例3[例28]

查DB_Design課程的課程號和學分。

SELECTCNO,CTFROMCWHERECNLIKE'DB\_Design'ESCAPE'\'ESCAPE'\'短語表示\為換碼字元，這樣匹配串中緊跟在\後面的字元'_'不再具有通配符的含義，而是取其本身含義，被轉義為普通的'_'字元。返回本節首頁涉及空值的查詢[例29]某些學生選修某門課程後沒有參加考試，所以有選課記錄，但沒有考試成績，下麵我們來查一下缺少成績的學生的學號和相應的課程號。

SELECTSNO,CNOFROMSCWHERESCOREISNULL注意這裏的'IS'不能用等號（'='）代替[例30]查所有有成績的記錄的學生學號和課程號。

SELECTSNO,CNOFROMSCWHERESCOREISNOTNULL返回本節首頁多重條件查詢1邏輯運算符AND、OR和NOT可用來聯結多個查詢條件。他們的優先順序NOT最高，接著是AND，OR優先順序最低，但用戶可以用括弧改變運算的優先順序。[例31]

查電腦系年齡在20歲以下的學生姓名。

SELECTSN FROMS WHEREDEPT='電腦'ANDAGE<20返回本節首頁多重條件查詢2[例32]IN謂詞實際上是多個OR運算符的縮寫，因此“查詢資訊系、自動化系和電腦系的學生的姓名和性別”一題，也可以用OR運算符寫成如下等價形式：

SELECTSN,SEXFROMSWHEREDEPT='電腦'ORDEPT='資訊'ORDEPT='自動化'或

SELECTSN,SEXFROMSWHERENOT(DEPT<>'電腦'ANDDEPT<>'資訊'ANDDEPT<>'自動化')返回本節首頁3.3.4常用庫函數及統計匯總查詢COUNT返回組中專案的數量。SUM返回運算式中所有值的和，或只返回DISTINCT值的和。AVG返回組中值的平均值。MAX返回組中值的最大值。MIN返回組中值的最小值。返回本節首頁[例33]

查詢學生總人數。

SELECTCOUNT(*) FROMS[例34]查詢選修了課程的學生人數。

SELECTCOUNT(DISTINCTSNO) FROMSC

學生每選修一門課，在SC中都有一條相應的記錄，而一個學生一般都要選修多門課程，為避免重複計算學生人數，必須在COUNT函數中用DISTINCT短語。[例35]計算C1課程的學生人數、最高成績、最低成績及平均成績。

SELECT COUNT(*),MAX(SCORE),MIN(SCORE),AVG(SCORE) FROMSC WHERECNO='C1'返回本節首頁3.3.5分組查詢GROUPBY子句可以將查詢結果表的各行按一列或多列取值相等的原則進行分組。對查詢結果分組的目的是為了細化集函數的作用對象。如果未對查詢結果分組，集函數將作用於整個查詢結果，即整個查詢結果為一組對應統計產生一個函數值。否則，集函數將作用於每一個組，即每一組分別統計，分別產生一個函數值。返回本節首頁[例36]查詢各個課程號與相應的選課人數。

SELECTCNO,COUNT(SNO)FROMSC

GROUPBYCNO該SELECT語句對SC表按CNO的取值進行分組，所有具有相同CNO值的元組為一組，然後對每一組作用集函數COUNT以求得該組的學生人數，執行結果為：

CNO---------C13C24C31C41C52C61C71返回本節首頁[例37]

查詢有3人以上學生（包括3人）選修的課程的課程號及選修人數。

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

CNO----------C13C24返回本節首頁如果分組後還要求按一定的條件對這些組進行篩選，最終只輸出滿足指定條件組的統計值，則可以使用HAVING短語指定篩選條件。3.3.6查詢的排序

如果沒有指定查詢結果的顯示順序，DBMS將按其最方便的順序（通常是元組在表中的先後順序）輸出查詢結果。用戶也可以用ORDERBY子句指定按照一個或多個屬性列的昇冪（ASC）或降序（DESC）重新排列查詢結果，其中昇冪ASC為缺省值。返回本節首頁查詢的排序示例1[例38]

查詢選修了3號課程的學生的學號及其成績，查詢結果按分數的降序排列。

SELECTSNO,SCOREFROMSCWHERECNO='C3'

ORDERBYSCOREDESC

前面已經提到，可能有些學生選修了C3號課程後沒有參加考試，即成績列為空值。用ORDERBY子句對查詢結果按成績排序時，在SQLSERVER2000中空值（NULL）被認為是最小值。返回本節首頁查詢的排序示例2[例39]

查詢全體學生情況，查詢結果按所在系昇冪排列，對同一系中的學生按年齡降序排列。SELECT*FROMSORDERBYDEPT,AGEDESC返回本節首頁3.3.7連接查詢1、等值與非等值連接查詢當連接運算符為=時，稱為等值連接。使用其他運算符稱為非等值連接。2、自身連接連接操作不僅可以在兩個表之間進行，也可以是一個表與其自己進行連接，這種連接稱為表的自身連接。 3、外連接

返回本節首頁等值與非等值連接查詢

從概念上講DBMS執行連接操作的過程是，首先在表1中找到第一個元組，然後從頭開始順序掃描或按索引掃描表2，查找滿足連接條件的元組，每找到一個元組,就將表1中的第一個元組與該元組拼接起來，形成結果表中一個元組。表2全部掃描完畢後，再到表1中找第二個元組，然後再從頭開始順序掃描或按索引掃描表2，查找滿足連接條件的元組，每找到一個元組,就將表1中的第二個元組與該元組拼接起來，形成結果表中一個元組。重複上述操作，直到表1全部元組都處理完畢為止。返回本節首頁等值與非等值連接查詢示例[例40]

查詢每個學生及其選修課程的情況。學生情況存放在S表中，學生選課情況存放在SC表中，所以本查詢實際上同時涉及S與SC兩個表中的數據。這兩個表之間的聯繫是通過兩個表都具有的屬性SNO實現的。要查詢學生及其選修課程的情況，就必須將這兩個表中學號相同的元組連接起來。這是一個等值連接。完成本查詢的SQL語句為：

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

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

在本查詢中，由於SN、SEX、AGE、DEPT、CNO和SCORE屬性列在S與SC表中是唯一的，因此引用時可以去掉表名首碼。而SNO在兩個表都出現了，因此引用時必須加上表名首碼。該查詢的執行結果不再出現SC.SNO列。返回本節首頁自身連接示例[例42]

查詢比張梅工資高的教師的姓名、工資和張梅的工資。要查詢的內容均在同一表T(教師表)中，可以為表T分別取兩個別名，一個是X，一個是Y。將X，Y中滿足比張梅工資高的行連接起來。這實際上是同一表T的大於連接。完成該查詢的SQL語句為：

SELECTX.TN,X.SALASSAL_B,Y.SALAS張梅的工資

FROMTASX,TASYWHEREX.SAL>Y.SALANDY.TN='張梅'結果為：

TNSAL_B張梅的工資

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

劉義1500.00001300.0000

李剛1500.00001300.0000返回本節首頁外連接1

在通常的連接操作中，只有滿足連接條件的元組才能作為結果輸出，如在例40和例41的結果表中沒有關於學生S6的資訊，原因在於她沒有選課，在SC表中沒有相應的元組。但是有時我們想以S表為主體列出每個學生的基本情況及其選課情況，若某個學生沒有選課，則只輸出其基本情況資訊，其選課資訊為空值即可，這時就需要使用外連接（OuterJoin）。外連接的運算符通常為*,有的關係資料庫中也用+。這樣，我們就可以如下改寫例41了：返回本節首頁外連接2SELECTS.SNO,SN,SEX,AGE,DEPT,CNO,SCOREFROMSLEFTJOINSCONS.SNO=SC.SNO

結果為：

SNOSNSEXAGEDEPTCNOSCORE-------------------------------------------------S1李濤男19資訊C190S1李濤男19資訊C285

…………S5吳小麗女19資訊C289

S6徐敏敏女20電腦NULLNULL

從查詢結果可以看到，學號為S6的學生沒選課，但S6的資訊也出現在查詢結果中，上例中外連接符LEFT[OUTER]JOIN稱其為左外連接。相應地，如果RIGHT[OUTER]JOIN則稱為右外連接，FULL[OUTER]JOIN稱為全外連接。返回本節首頁3.3.8合併查詢

合併查詢結果就是使用UNION操作符將來自不同查詢的數據組合起來，形成一個具有綜合資訊的查詢結果。UNION操作會自動將重複的數據行剔除。必須注意的是，參加合併查詢結果的各子查詢的使用的表結構應該相同，即各子查詢的數據數目相同，對應的數據類型要相融。返回本節首頁合併查詢示例

[例43]從SC數據表中查詢出學號為“S1”的同學的學號和總分，再從SC數據表中查詢出學號為“S5”的同學的學號和總分，然後將兩個查詢結果合併成一個結果集。SELECTSNOAS學號，SUM(SCORE)AS總分FROMSCWHERE(SNO='S1')GROUPBYSNOUNIONSELECTSNOAS學號,SUM(SCORE)AS總分FROMSCWHERE(SNO='S5')GROUPBYSNO返回本節首頁3.3.9嵌套查詢1、帶有IN謂詞的子查詢2、帶有比較運算符的子查詢3、帶有ANY或ALL謂詞的子查詢4、帶有EXISTS謂詞的子查詢返回本節首頁帶有IN謂詞的子查詢[例44]查詢與“王林”在同一個系學習的學生的學號、姓名和所在系。查詢與“王林”在同一個系學習的學生，可以首先確定“王林”所在系名，然後再查找所有在該系學習的學生。所以可以分步來完成此查詢：①確定“劉晨”所在系名

SELECTDEPTFROMSWHERESN='王林'結果為：

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

電腦②查找所有在電腦系學習的學生。

SELECTSNO,SN,DEPTFROMSWHEREDEPT='電腦'返回本節首頁結果為：

SNOSNDEPT-------------------S2王林電腦

S6徐敏敏電腦分步寫查詢畢竟比較麻煩，上述查詢實際上可以用子查詢來實現，即將第一步查詢嵌入到第二步查詢中，用以構造第二步查詢的條件。SQL語句如下：SELECTSNO,SN,DEPTFROMSWHEREDEPTIN(SELECTDEPTFROMSWHERESN='王林')返回本節首頁本例中的查詢也可以用我們前面學過的表的自身連接查詢來完成：

SELECTS1.SNO,S1.SN,S1.DEPTFROMSS1,SS2WHERES1.DEPT=S2.DEPTANDS2.SN='王林'返回本節首頁帶有比較運算符的子查詢示例

例如：在例44中，由於一個學生只可能在一個系學習，也就是說內查詢王林所在系的結果是一個唯一值，因此該查詢也可以用比較運算符來實現，其SQL語句如下：

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

查詢其他系中比資訊系任一學生年齡小的學生名單。SELECTSN,AGEFROMSWHEREAGE<ALL(SELECTAGEFROMSWHEREDEPT='資訊')ANDDEPT<>'資訊'ORDERBYAGEDESC返回本節首頁帶有ANY或ALL謂詞的子查詢示例2

本查詢實際上也可以用集函數（請參閱表3.6）實現。SELECTSN,AGEFROMSWHEREAGE<(SELECTMIN(AGE)FROMSWHEREDEPT='資訊')ANDDEPT<>'資訊'ORDERBYAGEDESC

事實上，用集函數實現子查詢通常比直接用ANY或ALL查詢效率要高。返回本節首頁帶有EXISTS謂詞的子查詢示例1[例47]

查詢所有選修了C1號課程的學生姓名。查詢所有選修了C1號課程的學生姓名涉及S關係和SC關係，我們可以在S關係中依次取每個元組的SNO值，用此S.SNO值去檢查SC關係，若SC中存在這樣的元組，其SC.SNO值等於用來檢查的S.SNO值，並且其SC.CNO='C1'，則取此S.SN送入結果關係。將此想法寫成SQL語句就是：SELECTSNFROMSWHEREEXISTS

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

查詢選修了全部課程的學生姓名。由於沒有全稱量詞，我們將題目的意思轉換成等價的存在量詞的形式：查詢這樣的學生姓名，沒有一門課程是他不選的。該查詢涉及三個關係，存放學生姓名的S表，存放所有課程資訊的C表，存放學生選課資訊的SC表。其SQL語句為：

SELECTSNFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMSCWHERESNO=S.SNOANDCNO=C.CNO))返回本節首頁3.3.10子查詢別名運算式的使用*

在查詢語句中，直接使用子查詢別名的表達形式不失為一種簡捷的查詢表達方法。以下舉例說明。例3.50

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

注意：通過AS關鍵字給子查詢命名的表達方式，別名後的括弧中可對應給子查詢列指定列名。一旦命名，別名運算式可作為一般表一樣的使用。返回本節首頁3.3.10子查詢別名運算式的使用*返回本節首頁[例3.51]

查詢選課門數唯一的學生的學號（例：若只有S1學號的學生選2門，則S1應為結果之一）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子查詢別名運算式的使用*返回本節首頁例3.52

查詢學習編號為“C2”課成績為第3名的學生的學號（設選C2課的學生人數>=3）。SELECTSC.SNOFROM(SELECTMIN(SCORE)FROM(SELECTDISTINCTTOP3SCOREFROMSCWHERECNO='C2'ORDERBYSCOREDESC)ASt1(SCORE))ASt2(SCORE)INNERJOINSCONt2.SCORE=SC.SCOREWHERECNO='C2'思考：讀者可試試若不用子查詢別名運算式的表示方法，這些查詢該如何表達？3.3.11存儲查詢結果到表中

使用SELECT…INTO語句可以將查詢到的結果存儲到一個新建的資料庫表或臨時表中。

[例53]從SC數據表中查詢出所有同學的學號和總分，並將查詢結果存放到一個新的數據表Cal_Table中。SELECTSNOAS學號,SUM(SCORE)AS總分

INTOCal_TableFROMSCGROUPBYSNO返回本節首頁3.4SQL數據更新3.4.1插入數據3.4.2修改數據3.4.3刪除數據

返回本章首頁3.4.1插入數據1、插入單個元組插入單個元組的INSERT語句的格式為：

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

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

子查詢

返回本節首頁插入單個元組[例51]將一個新學生記錄（學號：S7；姓名：陳冬；性別：男；年齡：18歲；所在系：資訊）插入S表中。

INSERTINTOSVALUES('S7','陳冬','男','18','資訊')[例52]

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

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

新插入的記錄在SCORE列上取空值。返回本節首頁插入子查詢結果[例53]

對每一個系，求學生的平均年齡，並把結果存入資料庫。對於這道題，首先要在資料庫中建立一個有兩個屬性列的新表，其中一列存放系名，另一列存放相應系的學生平均年齡。

CREATETABLEDEPTAGE(DEPTCHAR(15),AVGAGETINYINT)

然後對數據庫的S表按系分組求平均年齡，再把系名和平均年齡存入新表中。

INSERTINTODEPTAGE(DEPT,AVGAGE)SELECTDEPT,AVG(AGE)FROMSGROUPBYDEPT返回本節首頁3.4.2修改數據UPDATE<表名>SET<列名>=<運算式>[,<列名>=<運算式>]...[WHERE<條件>]

其功能是修改指定表中滿足WHERE子句條件的元組。其中SET子句用於指定修改方法，即用<運算式>的值取代相應的屬性列值。如果省略WHERE子句，則表示要修改表中的所有元組。返回本節首頁修改某一個元組的值[例54]將學生S3(為學號)的年齡改為20歲。UPDATESSETAGE=20WHERESNO='S3'返回本節首頁修改多個元組的值[例55]將所有學生的年齡增加1歲。UPDATESSETAGE=AGE+1

返回本節首頁帶子查詢的修改語句[例56]將電腦科學系全體學生的成績置零。

UPDATESCSETSCORE=0WHERE'電腦'=(SELECTDEPTFROMSWHERESC.SNO=S.SNO)或

UPDATESCSETSCORE=0WHERESNOIN(SELECTSNOFROMSWHEREDEPT='電腦')返回本節首頁3.4.3刪除數據DELETE[FROM]<表名>[WHERE<條件>]

DELETE語句的功能是從指定表中刪除滿足WHERE子句條件的所有元組。如果省略WHERE子句，表示刪除表中全部元組，但表的定義仍在字典中。也就是說，DELETE語句刪除的只是表中的數據，而不包括表的結構定義。返回本節首頁1、刪除某一個元組的值[例57]

刪除學號為S7的學生記錄。

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

DELETEFROMSC3、帶子查詢的刪除語句子查詢同樣也可以嵌套在DELETE語句中[例59]刪除電腦科學系所有學生的選課記錄。

DELETEFROMSCWHERE'電腦'=(SELECTDEPTFROMSWHERES.SNO=SC.SNO)返回本節首頁更新操作舉例

更新操作練習題

返回本節首頁3.5視圖3.5.1定義和刪除視圖3.5.2查詢視圖3.5.3更新視圖

返回本章首頁視圖的相關概念關於視圖在關係資料庫系統中，視圖為用戶提供了多種看待資料庫數據的方法與途徑，是關係資料庫系統中的一種重要對象。視圖是從一個或幾個基本表（或視圖）導出的表，它與基本表不同，是一個虛表。通過視圖能運算元據，基本表數據的變化也能在刷新的視圖中反映出來。從這個意義上講，視圖像一個窗口或望遠鏡，透過它可以看到資料庫中自己感興趣的數據及其變化。視圖在概念上與基本表等同，一經定義，就可以和基本表一樣被查詢、被刪除，我們也可以在一個視圖上再定義新的視圖，但對視圖的更新（插入、刪除、修改）操作則有一定的限制。返回本節首頁3.5.1創建和刪除視圖1、創建視圖CREATEVIEW<視圖名>[(<列名>[,<列名>]...)]AS<子查詢>

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

[例62]刪除視圖IS_S1。

DROPVIEWIS_S1返回本節首頁創建視圖示例[例61]

建立資訊系選修了C1號課程的學生的視圖。CREATEVIEWIS_S1(SNO,SN,SCORE)ASSELECTS.SNO,SN,SCOREFROMS,SCWHEREDEPT='資訊'ANDS.SNO=SC.SNOANDSC.CNO='C1'返回本節首頁查詢視圖示例[例63]

在資訊系學生的視圖中找出年齡小於20歲的學生。

SELECTSNO,AGEFROMIS_SWHERESage<20

視圖是定義在基本表上的虛表，它可以和其他基本表一起使用，實現連接查詢或嵌套查詢。這也就是說，在關係資料庫的三級模式結構中，外模式不僅包括視圖，而且還可以包括一些基本表。返回本節首頁更新視圖示例[例64]將資訊系學生視圖IS_S中學號為S3的學生姓名改為“劉辰”。

UPDATEIS_SSETSN='劉辰'WHERESNO='S3'返回本節首頁不同的資料庫對視圖的更新有不同的規定，如下是IBM的DB2資料庫中視圖不允許更新的規定：1.若視圖是由兩個以上基本表導出的，則此視圖不允許更新。2.若視圖的字段來自字段運算式或常數，則不允許對此視圖執行INSERT和UPDATE操作，但允許執行DELETE操作。3.若視圖的字段來自集函數，則此視圖不允許更新。4.若視圖定義中含有GROUPBY子句，則此視圖不允許更新。5.若視圖定義中含有DISTINCT短語，則此視圖不允許更新。6.若視圖定義中有嵌套查詢，並且內層查詢的FROM子句中涉及的表也是導出該視圖的基本表，則此視圖不允許更新。7.一個不允許更新的視圖上定義的視圖也不允許更新。

返回本節首頁3.6SQL數據控制3.6.1許可權與角色3.6.2系統許可權與角色的授予與收回3.6.3對象許可權與角色的授予與收回

返回本章首頁3.6.1許可權與角色1、許可權

SQL系統安全機制：視圖機制、許可權機制 資料庫中許可權：系統許可權、對象許可權2、系統許可權 系統許可權是指數據庫用戶能夠對數據庫系統進行某種特定的操作的權力。它由資料庫管理員授予其他用戶。如創建一個基本表（CREATETABLE）的權力。3、對象許可權 對象許可權是指數據庫用戶在指定的資料庫對象上進行某種特定的操作的權力。返回本節首頁3.6.1許可權與角色1、角色

角色是多種許可權的集合，可以把角色授予用戶或角色。當要為某一用戶同時授予或收回多項許可權時，則可以把這些許可權定義為一個角色，對此角色進行操作。這樣就避免了許多重複性的工作，簡化了管理資料庫用戶許可權的工作。返回本節首頁許可權機制

在SQL系統中，有兩個安全機制，一種是視圖機制，當用戶通過視圖訪問資料庫時，他不能訪問此視圖外的數據，它提供了一定的安全性。而主要的安全機制是許可權機制。許可權機制的基本思想是給用戶授予不同類型的許可權，在必要時，可以收回授權，使用戶能夠進行的資料庫操作以及所操作的數據限定在指定範圍內，禁止用戶超越許可權對數據庫進行非法的操作，從而保證資料庫的安全性。返回本節首頁3.6.2系統許可權與角色的授予與收回1、系統許可權與角色的授予GRANT<系統許可權>[,<系統許可權>]...TO<用戶>|角色[,<用戶>|角色]...[WITHGRANTOPTION]2、系統許可權與角色的收回REVOKE<系統許可權>[,<系統許可權>]…FROM<用戶名>|<角色>|PUBLIC[,<用戶名>|<角色>]…

返回本節首頁系統許可權與角色的授予與收回示例[例65]把創建表的許可權授給用戶U1。

GRANTCREATETABLETOU1[例66]

把U1所擁有的創建表許可權收回。

REVOKECREATETABLEFROMU1返回本節首頁3.6.3對象許可權與角色的授予與收回1、對象許可權與角色的授予

GRANTALL|<對象許可權>[（列名[，列名]…）][,<對象許可權>]...ON<對象名>TO<用戶>|<角色>|PUBLIC[,<用戶>|<角色>]...[WITHGRANTOPTION]2、對象許可權與角色的收回

REVOKE<對象許可權>|<角色>[,<對象許可權>|<角色>]…FROM<用戶名>|<角色>|PUBLIC[,<用戶名>|<角色>]…返回本節首頁許可權命令對象許可權與角色的授予與收回示例[例67]把查詢S表許可權授給用戶U1。

GRANTSELECTONSTOU1[例68]

收回用戶U1對S表的查詢許可權。

REVOKESELECTONSFROMU1

返回本節首頁3.7嵌入式SQL語言*3.7.1嵌入式SQL簡介3.7.2嵌入式SQL要解決的三個問題3.7.3第四代數據庫應用開發工具或高級語言中SQL的使用

返回本章首頁嵌入式SQL的預編譯、編譯、連接與運行處理過程

根源程式（主語言+SQL）

預編譯系統

主語言根源程式

主語言編譯系統

目標代碼

連接與運行返回本節首頁3.7.2嵌入式SQL要解決的三個問題1、區分SQL語句與主語言語句2、資料庫工作單元和程式工作單元之間的通信3、協調SQL集合式操作與高級語言記錄式處理之間的關係4、舉例返回本節首頁voidErrorHandler(void);#include<stddef.h>//standardCrun-timeheader#include<stdio.h>//standardCrun-timeheader#include"gcutil.h"//utilityheaderintmain(intargc,char**argv,char**envp){intnRet;//forreturnvaluescharyn[2];

EXECSQLBEGINDECLARESECTION;//①先說明主變數

charszServerDatabase[(SQLID_MAX*2)+2]="";//放資料庫伺服器名與資料庫名。

charszLoginPassword[(SQLID_MAX*2)+2]="";//放登錄用戶名與口令。

chartname[21]="xxxxxxxxxxx";//放表名變數

charcscustid[8];charcsname[31];doublecsdiscount;doublenewdisc;intcsdiscnull=0;

EXECSQLENDDECLARESECTION;返回本節首頁//②接著是錯誤處理設置與連接的相關選項設置。EXECSQLWHENEVERSQLERRORCALLErrorHandler();EXECSQLSETOPTIONLOGINTIME10;EXECSQLSETOPTIONQUERYTIME100;printf("SampleEmbeddedSQLforCapplication\n");//displaylogo//若不使用“GetConnectToInfo()”，則也可直接指定“伺服器名.資料庫名”與//“用戶名.口令名”來連接，如EXECSQLCONNECTTOqh.qxzUSERsa.sa;//這裏“qh”為伺服器名，“qxz”為數據庫名，“sa”為用戶名，“sa”為口令。//GetConnectToInfo()實現連接資訊的獲取，一般在“gcutil.c”C根源程式中的。nRet=GetConnectToInfo(argc,argv,szServerDatabase,szLoginPassword);if(!nRet){return(1);}

返回本節首頁//下麵CONNECTTO命令真正實現與SQLServer的連接EXECSQLCONNECTTO:szServerDatabaseUSER:szLoginPassword;if(SQLCODE==0){printf("ConnectiontoSQLServerestablished\n");}else{//problemconnectingtoSQLServerprintf("ERROR:ConnectiontoSQLServerfailed\n");return(1);}//檢測資料庫是否有customer表？

EXECSQLSELECTnameinto:tnameFROMsysobjects//③SELECTINTO語句

WHERE(xtype='U'andname='customer');if(SQLCODE==0||strcmp(tname,"customer")==0){printf("客戶表已經存在。\n");}else{//若不存在customer表，則創建表並插入若干條記錄。EXECSQLcreatetablecustomer//④

創建customer表

(CustIDDec(7,0)notnull,NameChar(30)notnull,ShipCityChar(30)NULL,DiscountDec(5,3)NULL,primarykey(CustID));返回本節首頁if(SQLCODE==0){printf("createsuccess!%d\n",SQLCODE);}else{printf("ERROR:create%d\n",SQLCO