“數據庫系統原理及應用”課程第三章關系數據庫語言SQL主講人：李俊山教授聯系方式：743419辦公地點：訓練樓516房間SQL語句的分類：

數據定義語句：用于定義數據庫的邏輯結構，包括定義基本表、定義視圖和定義索引。

數據查詢語句：用于按不同查詢條件實現對數據庫中數據的檢索查詢。

數據操縱語句：用于更改和操作表中的數據，包括數據插入、數據修改、數據刪除及數據查詢。

數據控制語句：用于實現用戶授權、基本表和視圖授權、事務控制、完整性和安全性控制等。

3.1.1表的定義、修改與撤消

1.表的定義

表的定義語句格式為：

CREATETABLE<表名>（<列名1><數據類型>[<列1的完整性約束>][，<列名2><數據類型>[<列2的完整性約束>]，……，<列名n><數據類型>[<列n的完整性約束>]，[<表的完整性約束>]]）；

其中：

●典型的<數據類型>

（a）CHAR(m)：長度為m的字符(串)型數據，長度不夠時用空白字符補充，不超過240。（b）VARCHAR(m)：長度小于等于m的字符(串)型數據，長度不夠時不補充其它字符。（c）INT/INTEGER：長整型數據。（d）DATE：日期型數據，形式為YYYY-MM-DD，分別表示年、月、日。其中：

●典型的<列的完整性約束>（a）NULL：指出該列可以為空值。（b）NOTNULL：指出該列不能為空值。每一個表中至少應有一個列的可選項為NOTNULL。（c）PRIMARYKEY：指出該列名為表的主鍵。（d）DEFAULT：給所在的列設置一個缺省值。（e）CHECK：指出該列的值只能取CHECK約束條件范圍的值。例3.2創建圖1.30所示的教學管理數據庫系統中的學生關系表S，可用如下的表定義語句定義：CREATETABLES（S#CHAR(9)PRIMARYKEY，SNAMECHAR(10)NOTNULL，SSEXCHAR(2)CHECK(SSEXIN(′男′，′女′))，SBIRTHINDATENOTNULL，PLACEOFBCHAR(16)，SCODECHAR(5)NOTNULL，CLASSCHAR(5)NOTNULL）；其中：

●

<表的完整性約束>

(a)表的主鍵約束

格式為：PRIMARYKEY(<列名表>)當該表的主鍵由2個或2個以上屬性組成時，表的主鍵必須由表的完整性約束給出。

例3.3創建教學管理數據庫系統中的學習關系SC，可用如下的表定義語句定義：CREATETABLESC（S#CHAR(9),C#CHAR(7)，GRADEINTDEFAULT(0),PRIMARYKEY(S#，C#)）；

●

<表的完整性約束>

(b)表的外鍵約束

格式為：FOREIGNKEY(<列名1>)REFERENCE<表名>（<列名2>）

本子句定義了一個列名為“<列名1>”的外鍵，它與表“<表名>”中的“<列名2>”相對應，且“<列名2>”在表“<表名>”中是主鍵。

例3.4教學管理數據庫系統中的學習關系SC，可重新用如下的表定義語句定義如下：CREATETABLESC（S#CHAR(9),C#CHAR(7)，GRADEINTDEFAULT(0),PRIMARYKEY(S#，C#)，FOREIGNKEY(C#)REFERENCESC(C#))；

●

<表的完整性約束>

(c)表檢驗CHECK約束

格式為：CHECK(<值的約束條件>)

表檢驗約束CHECK子句的含義和格式與列檢驗約束相同，所不同的是，表檢驗約束CHECK子句是一個獨立的子句而不是子句中的一部分。表檢驗約束CHECK子句中的<值的約束條件>不僅可以是一個條件表達式，而且還可以是一個包含SELECT語句的SQL語句。例3.5教學管理數據庫系統中的學習關系SC，還可用如下的表定義語句定義如下：CREATETABLESC（S#CHAR(9),C#CHAR(7)，GRADEINTDEFAULT(0),PRIMARYKEY(S#，C#)，FOREIGNKEY(C#)REFERENCESC（C#）,CHECK(GRADEBETWEEN0AND100))；2.表的修改

(1)改變表名

修改表名的語句格式為：RENAME<原表名>TO<新表名>；

2.表的修改

(2)增加列

在表的最后一列后面增加新的一列，但不允許將一個列插入到原表的中間。

增加列語句的格式為：ALTERTABLE<表名>ADD<增加的列名><數據類型>；例3.7給專業表SS增加一個新屬性NOUSE_COLUMN，設其數據類型為NUMERIC(8，1)。

語句應為：ALTERTABLESSADDNOUSE_COLUMNNUMERIC(8，1)；2.表的修改

(3)刪除列

刪除表中不再需要的列，語句格式為：ALTERTABLE<表名>DROP<刪除的列名>[CASCADE|RESTRICT]；其中，可選項“[CASCADE|RESTRICT]”是刪除方式。當選擇CASCADE時，表示在刪除名為“<表名>”的表中的列“<刪除的列名>”時，所有引用到該列的視圖或有關約束也一起被刪除；當選擇RESTRICT時，表示當沒有視圖或有關約束引用列“<刪除的列名>”時，該列才能被刪除，否則拒絕該刪除操作。例3.8刪除專業表SS中增加的屬性NOUSE_COLUMN的兩種刪除語句分別為：ALTERTABLESSDROPNOUSE_COLUMNCASCADE；ALTERTABLESSDROPNOUSE_COLUMNRESTRICT；2.表的修改

(4)修改列的定義

修改屬性列的定義語句只用于修改列的類型和長度，列的名稱不能改變。當表中已有數據時，不能縮短列的長度，但可以增加列的長度。

修改列定義語句格式為：ALTERTABLE<表名>MODIFY<列名><新的數據類型及其長度>；例3.8將專業表SS中的專業名稱SSNAME(30)修改為SSNAME(40)，即長度增加10。ALTERTABLESSMODIFYSSNAMEVARCHAR(40)；3.表的撤銷

表的撤消就是將不再需要的表或定義有錯誤的表刪除掉。當一個表被撤消時，該表中的數據也一同被撤消（刪除）。

撤消表的語句格式為：DROPTABLE<表名>[CASCADE|RESTRICT]；其中,CASCADE表示在撤消表“<表名>”時，所有引用這個表的視圖或有關約束也一起被撤消；RESTRICT表示在沒有視圖或有關約束引用該表的屬性列時，表“<表名>”才能被撤消，否則拒絕該撤消操作。3.1.2數據的插入、修改、刪除、提交與撤消1.數據的插入

向表中插入一行數據的單元組值插入方式的INSERT語句格式為：INSERTINTO<表名>[（<列名表>）]

VALUES（<值表>）；其中：(1)如果選擇可選項“[（<列名表>）]”，表示在插入一個新元組時，只向由<列名i>指出的列中插入數據，其他沒有列出的列不插入數據(為空值)，且“<列名表>”中必須至少包括表中那些列約束為“NOTNULL”的列和主鍵列。如果不選擇該可選項，則默認表中所有的列都要插入數據。

(2)<值表>指出要插入列的具體值。如果選擇了可選項[<列名表>]，則<值表>中的屬性列值必須與<列名表>中的屬性列名一一對應；如果沒有選擇可選項[<列名表>]，則<值表>中的屬性列值必須與<列名表>中的屬性列名一一對應。

例3.10給學習關系SC中插入王麗麗同學（學號為200401003）學習計算機網絡課（課程號為C403001）的成績（89分）。INSERTINTOSC（S#，C#，GRADE）VALUES（’200401003’，’C403001’，89）；

或：INSERTINTOSC

VALUES（’200401003’，’C403001’，89）；3.1.2數據的插入、修改、刪除、提交與撤消2.數據的修改

語句格式為：UPDATE<表名>SET<列名1>＝<表達式1>[，<列名2>＝<表達式2>,…,<列名n>＝<表達式n>][WHERE<條件>]其中，“<列名i>＝<表達式i>”指出將列“<列名i>”的值修改成<表達式i>。可選項“[WHERE<條件>]”中的<條件>指定修改有關列的數據時所應滿足的條件。當不選擇該選項時，表示修改表中全部元組中相應列的數據。

例3.13將學生關系S中的學生名字“王麗麗”改為“王黎麗”。UPDATESSETSNAME＝’王黎麗’WHERES#＝’200401003’；例3.14將所有女同學的專業改為S0404。UPDATESSETSCODE＝’S0404’WHERESSEX＝’女’；

3.1.2數據的插入、修改、刪除、提交與撤消3.數據的刪除

語句格式為：DELETEFROM<表名>[WHERE<條件>]其中，可選項“[WHERE<條件>]”中的<條件>指定所刪元組應滿足的條件。當不選該可選項時，表示刪除表中全部數據。例3.15在學生關系S中刪除學號為200403001的學生信息。DELETEFROMSWHERES#=‘200403001’；例3.16刪除專業關系中的全部信息。DELETEFROMSS；3.1.2數據的插入、修改、刪除、提交與撤消4.數據的提交

工作區概念：數據提交的概念：就是把用戶對數據庫中數據的更新結果永久地保存到數據庫中。

顯式提交:

隱式提交：3.1.2數據的插入、修改、刪除、提交與撤消5.數據的撤銷

命令格式:

3.2.1簡單查詢1.SELECT查詢語句

SQL查詢語句的基本格式為：SELECT<列名表>FROM<表名表>[WHERE<條件>]3.2.1簡單查詢2.無條件查詢

例3.17查詢教學管理數據庫中全部學生的基本信息。SELECT*FROMS；

例3.19查詢課程關系C中的記錄數，也即開課的總門數。SELECTCOUNT(*)FROMC；SQL語言中常用的聚合函數主要有：

（1）COUNT(*)計算元組的個數；

（2）COUNT(列名)計算某一列中數據的個數；

（3）COUNTDISTINCT(列名)計算某一列中不同值的個數；

（4）SUM(列名)計算某一數據列中值的總和；

（5）AVG(列名)計算某一數據列中值的平均值；

（6）MIN(列名)求(字符、日期、屬性列)的最小值；（7）MAX(列名)求(字符、日期、屬性列)的最大值；例3.20計算所有學生所學課程的最高分數、最低分數和平均分數。

SELECTMAX(GRADE)，MIN(GRADE)，AVG(GRADE)FROMSC；3.2.1簡單查詢3.單條件查詢

例3.21查詢所有學習計算機網絡課(課程號為C403001)的學生的學號和成績。

SELECTS#，GRADEFROMSCWHEREC#=‘C403001’；表3.1條件表達式中的關系比較符運算符含義=!=或<>>>=<<=ISNULLISNOTNULL等于不等于大于大于等于小于小于等于是空值不是空值3.2.1簡單查詢4.多條件查詢

例3.22查詢選修了計算機網絡課(課程號為C403001)或信息安全技術課(課程號為C403002)的學生的學號。SELECT#SFROMSCWHEREC#=‘C403001’ORC#=‘C403002’；(關系表見下頁)學生關系模式：S（S#，SNAME，SSEX，SBIRTHIN，PLACEOFB，SCODE，CLASS）專業關系模式：SS（SCODE#，SSNAME）課程關系模式：C（C#，CNAME，CLASSH）設置關系模式：CS（SCODE#，C#）學習關系模式：SC（S#，C#，GRADE）教師關系模式：T（T#，TNAME，TSEX，TBIRTHIN，TITLEOF，TRSECTION，TEL）講授關系模式：TEACH（T#，C#）圖1.30圖1.9的教學管理數據庫系統的概念模式

表3.2邏輯運算符

運算符含義NOTANDOR邏輯非邏輯與邏輯或例3.23若有學生關系S1（S#，SNAME，SSEX，SAGE，PLACEOFB，SCODE，CLASS），即該學生關系中給出的不是出生年月，而是年齡。要求查詢年齡在21歲至25歲之間學生的基本信息。SELECT*FROMS1WHEREAGE>=18ANDAGE<=25；

上例中：這種把某數值型列的值限定在某個數值區間的比較查詢條件（<、<=、>、>=）可以用比較運算符“BETWEEN…AND”來表示，格式為：<數值型列名>BETWEEN<數值區間下限值>AND<數值區間上限值>例3.23的查詢可以改寫為：

SELECT*FROMS1WHEREAGEBETWEEN18AND25；5.分組查詢

在SQL語言中，把元組按某個或某些列上相同的值分組，然后再對各組進行相應操作的查詢方式稱為分組查詢。

分組查詢的語句格式為：SELECT<列名表>FROM<表名表>[WHERE<條件>][GROUPBY<列名表>[HAVING<分組條件>]]；

（1）GROUPBY子句

GROUPBY子句用來將列的值分成若干組，從而控制查詢的結果排序。

例3.24計算各個同學的平均分數。SELECT#S，AVG(GRADE)FROMSCGROUPBY#S；

例3.25計算每個專業的男、女生分別有多少人數。SELECTSCODE，SSEX，COUNT(*)FROMSGROUPBYSCODE，SSEX；

（2）HAVING子句

在數據查詢中，有時只希望選擇滿足一定條件的分組。一般是利用GROUPBY子句進行分組，利用HAVING子句判斷分組應滿足的條件。例3.26查詢學生總數超過300人的專業及其具體的總人數。

SELECTSCODE，COUNT(*)FROMSGROUPBYSCODEHAVINGCOUNT(*)>300；6.結果排序

通常，SELECT的查詢結果是按元組在數據庫中的存儲順序給出的。但有時用戶希望按照某種約定的順序給出查詢結果，ORDERBY子句可以實現查詢結果的排序顯示功能，其語句格式為：SELECT<列名表>FROM<表名表>[WHERE<條件>]

ORDERBY<列名>[ASC/DESC][,<列名>[，ASC/DESC]]；例3.27按學號遞增的順序顯示學生的基本信息。SELECT*FROMSORDERBYS#ASC；例3.28按學號遞增、課程成績遞減的順序顯示學生的課程成績。

SELECTS#，C#，GRADEFROMSCORDERBYS#ASC，GRADEDESC；7.字符串的匹配

在WHERE子句的條件表達式中，實現兩個字符串的部分字符的相等比較。比較運算符的格式為：

<列名>LIKE‘[字符串1]通配符[字符串2]’其中：（1）下劃線_：在字符串比較時，如果有一個字符可以任意，則在該字符位置上用下劃線表示。（2）百分號%：在字符串比較時，如果有一個長度大于等于0子字符串可以任意，則在該子字符串位置上用百分號表示。例3.29查詢學生關系S中姓李的學生的學號和姓名。SELECTS#，SNAMEFROMSWHERESNAMELIKE‘李%’；8.比較完整的SELECT查詢語句

SELECT<列名或列表達式序列>FROM<表名表>[WHERE<條件>][GROUPBY<列名表>][HAVING<分組條件>]][ORDERBY<列名>[ASC/DESC][,<列名>[，ASC/DESC]]]；3.2.2SQL語言中的常用函數及其使用方式1.日期型函數

日期是SQL語言中的標準數據類型（DATE）。

函數

功能sysdatelast_daynext_day(d，w_day)add_months(d，n)months_between(d1,d2)to_char(d，new_d)

取當前日期和時間取本月最后一天計算日期d之后第一周指定星期幾(由w_day指定)的日期取在日期d的基礎上后推n個月的日期計算日期d1和d2之間相差的月數將日期d的缺省格式“DD-MON-YYYY”表示成指定的新格式new_d表3.3常用的日期函數

例3.30查詢出生日期在1982年1月1日到1982年12月31日之間的所有學生的學號、姓名和出生日期，并按日期的遞增順序排列。SELECTS#，SNAME，SBIRTHINFROMSWHERESBIRTHINBETWEEN‘1-JUN-1982’AND’31-DEC-1982’ORDERBYSBIRTHIN；表3.4常用的日期顯示格式

日期顯示格式（模式）示例缺省格式MM/DD/YYYYYY/MM/DDDYDDMONYYDayMonDD

23-FEB-9902/23/991999/02/23WED12JAN88WednesdayJan12

例3.31在上例中，出生日期的缺省顯示方式不符合我國的日期表示習慣，可用日期轉換函數函數to_char將其轉換成“年/月/日”的表示形式。SELECTS#，SNAME，to_char(SBIRTHIN，’YYYY”/”MM”/”DD’)FROMSWHERESBIRTHINBETWEEN‘1-JUN-1982’AND’31-DEC-1982’ORDERBYSBIRTHIN；2.字符串函數表3.5常用的字符串函數

函數

功能length(str)initcap(str)lower(str)upper(str)replace(str，str1，str2)substr(str，m，n)itrim(str)ascii(str)

求字符串的長度將字符串str中的第一個字符轉換成大寫字符將字符串str中的大寫字母轉換成小寫字母將字符串str中的小寫字母轉換成大寫字符將字符串str中的所有str1換成str2從字符串str的第m個字符開始取出n個字符截掉字符串str尾部的空格取字符串str的ASCII碼值

3.2.2SQL語言中的常用函數及其使用方式3.算術函數表3.6常用的算術函數

函數功能abs(n)ceil(n)floor(n)mod(m,n)power(m,n)round(n,m)sign(n)sqrt(n)求n的絕對值求大于等于數值n的最小整數求小于等于數值n的最大整數取余數。若n=0，則取m求m的n次方四舍五入，保留m位n=0，返回0；n>n，返回1；n<0，返回-1求n的平方

4.空值顯示與空值函數例3.33查詢學習關系SC中分數GRADE為空值的課程號。SELECTC#FROMSCWHEREGRADEISNULL；3.2.3SQL高級查詢技術1.多表聯接查詢（二元查詢/多元查詢）SELECTS.S#，SNAMEFROMS，SCWHERES.S#=SC.S#ANDC#=‘C401001’；例3.37查詢所有學習了數據結構課（課程號為C401001）的學生的學號和姓名。例3.38查詢選修了“信息安全技術”課程的學生的學號與姓名。SELECTS.S#，SNAMEFROMS，SC，CWHERES.S#=SC.S#ANDSC.C#=C.C#ANDCNAME=‘信息安全技術’；3.2.3SQL高級查詢技術2.外聯接查詢

學號姓名性別出生年月籍貫專業代碼班級200401001張華男14-dec-82北京S0401200401200401002李建平男20-aug-82上海S0401200401200401003王麗麗女02-feb-83上海S0401200401200402001楊秋紅女09-may-83西安S0402200402200402002吳志偉男30-jun-82南京S0402200402200402003李濤男25-jun-83西安S0402200402200403001趙曉艷女11-mar-82長沙S0403200403專業代碼專業名稱S0401計算機科學S0402指揮自動化S0403網絡工程S0404信息研究學生關系S：專業關系SS注意：雖然存在信息研究專業，但學生關系的當前值中沒有信息研究專業的學生。

對于查詢：

SELECTSS.SCODE#，SS.SSNAME，S#，SNAMEFROMSS，SWHERESS.SCODE#=S.SCODEORDERBYSCODE#；

有查詢結果：SCODESSNAMES#SNAME

S0401計算機科學200401001張華S0401計算機科學200401002李建平S0401計算機科學200401003王麗麗S0402指揮自動化200402001楊秋紅S0402指揮自動化200402002吳志偉S0402指揮自動化200402003李濤S0403網絡工程200403001趙曉艷

由于信息研究專業沒有學生，所以查詢結果丟失了信息研究的專業信息，為此，SQL引入了外聯接查詢。

外聯接查詢包括：左外聯接和右外聯接。

(1)左外聯接(Left-outerJoin)

RS=(RS)∪(R1(?…?))其中：

R1=R-πR（RS）(2)右外聯接(Right-outerJoin)

RS=(RS)∪((?…?)S1)其中：S1=S-πS（RS）

外聯接查詢包括：左外聯接和右外聯接。

設“R.A=S.B”是WHERE子句中的等值條件表達式，則：（1）左外聯接的表示形式為R.A(+)=S.B，其含義是假設在等值條件右邊的關系S中有一個“全空”的元組，且該元組可與等值條件左邊的關系R中任何不滿足等值聯接條件的元組匹配構成一個新的元組；（2）右外聯接的表示形式為R.A=S.B(+)，其含義是假設在等值條件左邊的關系R中有一個“全空”的元組，且該元組可與等值條件右邊的關系S中任何不滿足等值聯接條件的元組匹配構成一個新的元組。這時，上述的查詢語句：

SELECTSS.SCODE#，SS.SSNAME，S#，SNAMEFROMSS，SWHERESS.SCODE#=S.SCODEORDERBYSCODE#；

就可以改寫成：

SELECTSS.SCODE#，SS.SSNAME，S#，SNAMEFROMSS，SWHERESS.SCODE#(+)=S.SCODE#ORDERBYSCODE#；

查詢出的結果就包含了：S0404信息研究nullnull盡管信息研究專業沒有學生，但專業仍然存在！3.嵌套查詢

即：在SQL語言中，如果在一個SELECT語句的WHERE子句中嵌入了另一個SELECT語句，則稱為嵌套查詢。WHERE子句中的SELECT語句稱為子查詢。例3.39查詢張華同學（學號為200401001）的那個班的女同學的基本信息。SELECT*FROMSWHERECLASS=(SELECTCLASSFROMSWHERES#=‘200401001’)ANDSSEX=‘女’；4.謂詞演算查詢表3.7常用的謂詞操作符操作符說明betweenAandBnotbetweenAandBlikeinnotinanysomeallexistsnotexists

某列的數值區間是[A，B]某列的數值區間在[A，B]外兩個字符串的部分字符相等，其余可以任意某列的某個值屬于集合成員中的一個成員某列的值不屬于集合成員中的任何一個成員某列的值滿足一個條件即可滿足集合中的某些值某列的值滿足子查詢中所有值的記錄總存在一個值滿足條件不存在任何值滿足條件

4.謂詞演算查詢

（1）IN和NOTIN謂詞

條件表達式格式為：<集合1>IN<集合2><集合1>NOTIN<集合2>

前者的含義：如果集合1中的數據是集合2中的成員，那么邏輯值為true，否則為false。

后者的含義：如果集合1中的數據不是集合2中的成員，那么邏輯值為true，否則為false。例3.40查詢所有學習了數據結構課（課程號為C401001）的學生的學號和姓名。SELECTS.S#，SNAMEFROMSWHERES#IN(SELECTS#FROMSCWHEREC#=‘C401001’)；例3.41利用嵌套查詢實現例3.38，也即查詢選修了“信息安全技術”課程的學生的學號與姓名。SELECTS.S#，SNAMEFROMSWHERES#IN(SELECTS#FROMSCWHEREC#IN(SELECTC#FROMCWHERECNAME=‘信息安全技術’));思路：選修了“信息安全技術”課的課程號；選修了該課程（號）的學生的學號4.謂詞演算查詢

（2）ANY和SOME謂詞

條件表達式格式為：<列數據>θANY<集合><列數據>θSOME<集合>其中，θ是算術比較運算符<、<=、>、>=、=、!=。其含義是：比較運算符θ左邊的數據與右邊集合中的某個或某些元素是否滿足θ運算，滿足則為真，不滿足則為假。

在SQL語言中，ANY和SOME具有相同的含義，早期的版本用的是ANY，新的版本都改為SOME，有些商用數據庫版本的SQL語言中，同時保存了ANY和SOME兩個謂詞。

例3.42查詢所有學習了數據結構課（課程號為C401001）的學生的學號和姓名。SELECTS.S#，SNAMEFROMSWHERES#=ANY(SELECTS#FROMSCWHEREC#=‘C401001’)；其中，“=ANY”的作用相當于IN。4.謂詞演算查詢

（3）ALL謂詞

條件表達式格式為：<列數據>θALL<集合>含義是：比較運算符θ左邊的數據與右邊集合中的所有元素是否滿足θ運算，滿足其邏輯值為true，不滿足其邏輯值為false。

。

例3.43查詢考試成績大于網絡工程專業（專業代碼為S0403）所有學生的課程成績的學生的基本信息。SELECTS.S#，SNAME，SSEX，SBIRTHIN，PLACEOFB，SCODE，CLASSFROMS，SCWHERES.S#=SC.S#ANDGRADE>ALL(SELECTGRADEFROMS，SCWHERES.S#=SC.S#ANDSCODE=’S0403’；

4.謂詞演算查詢

（4）EXISTS和NOTEXISTS謂詞

條件表達式格式為：

EXISTS（<集合>）

NOTEXISTS（<集合>）

前者的含義是：當集合中至少存在一個元素（非空）時，其邏輯值為true，否則為false；

后者的含義是：當集合中不存在任何元素（為空）時，其邏輯值為true，否則為false。

通常用于測試子查詢是否有返回結果。例3.44查詢所有學習了數據結構課（課程號為C401001）的學生的學號和姓名。SELECTS.S#，SNAMEFROMSWHEREEXISTS(SELECT*FROMSCWHERESC.S#=S.S#ANDC#=‘C401001’)；

直觀的意義為：查詢的是那些，在學習關系中存在所學課程為數據結構的學生的學號和姓名。5.查詢結果的并、交、差操作

（1）并操作

即，指將兩個或多個SELECT語句的查詢結果組合在一起作為總的查詢結果輸出。語句格式為：

SELECT<列名表>FROM<表名表>[WHERE<條件>][UNION[ALL]{SELECT語句}…]；

其中，如果不選擇可選項ALL，則在輸出總查詢結果時重復的行會自動被取掉。如果選擇可選項ALL，則表示將全部行合并輸出，也即不取掉重復行。

例3.46合并學生關系和專業關系中的專業代碼。SELECTSCODE#FROMSUNIONSELECTSCODE#FROMSS；

5.查詢結果的并、交、差操作

（2）交操作

查詢結果的交操作是指將同時屬于兩個或多個SELECT語句的查詢結果作為總的查詢結果輸出。查詢結果交操作的基本數據單位是行。其語句格式為：SELECT<列名表>FROM<表名表>[WHERE<條件>][INTERSECT{SELECT語句}…]；例3.47查詢有成績的學生的學號。SELECTS#FROMSINTERSECTSELECTS#FROMSC[WHEREGRADEISNOTNULL];5.查詢結果的并、交、差操作

（3）差操作

是指從第一個SELECT語句的查詢結果中去掉屬于第二個SELECT語句查詢結果的行作為總的查詢結果輸出。其語句格式為：SELECT<列名表>FROM<表名表>[WHERE<條件>]

[MINUS{SELECT語句}]；

例3.48查詢沒有成績的學生的學號。SELECTS#FROMSMINUSSELECTS#FROMSC[WHEREGRADEISNOTNULL];

課堂綜合練習：

1、查詢全部學生的學號、姓名、所學課程號和成績。

2、查詢沒有選修任何課程的學生的學號和姓名。

3、找出選修了全部課程的學生的學號和姓名。

堂綜合練習答案：

1、查詢全部學生的學號、姓名、所學課程號和成績。SELECTS.S#，SNAME，C#，GRADEFROMS，SCWHEREC#IN(SELECTC#FROMSCWHERES.S#=SC.S#)；

堂綜合練習答案：

2、查詢沒有選修任何課程的學生的學號和姓名。SELECTS#，SNAMEFROMSWHERENOTEXISTS(SELECT*FROMSCWHERESC.S#=S.S#)；

堂綜合練習答案：

3、找出選修了全部課程的學生的學號和姓名（沒有一們可他不選）。

SELECTS#，SNAMEFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMSCWHERESC.S#=S.S#ANDSC.C#=C.C#)；

第三章第三次課3.3.1視圖的概念

視圖是由數據庫中滿足一定條件約束的數據組成的“虛表”，它可以由某個表中滿足一定條件的行組成，也可以由某個（或某些）表的某些列組成，還可以由若干個表經過一定的運算而形成。圖3.1視圖與基本表的映射關系視圖3視圖4視圖1視圖2表1表2表3表43.3.2視圖的定義

視圖的定義語句格式為：

CREATEVIEW<視圖名>[(<視圖列名表>)]AS<SELECT語句>[WITHREADONLY|WITHCHECKOPTION]；其中，當選擇[WITHREADONLY]可選項時，表示該視圖被定義成一個只讀，不能進行插入、刪除和修改操作。當選擇[WITHCHECKOPTION]可選項時，用戶必須保證每當向該視圖中插入或更新數據時，所插入或更新的數據能夠從該視圖查詢出來。

例3.49教學安排視圖：TA(C#，CNAME，CLASSH，TNAME，TRSECTION）的創建語句為：

CREATEVIEWTAASSELECTC#，CNAME，CLASSH，TNAME，TRSECTIONFROMC，TEACH，TWHEREC.C#=TEACH.C#ANDTEACH.T#=T.T#；其中，只有視圖名TA，沒有<視圖列名表>選項說明視圖TA的列名表與SELECT語句中的列名表相同。例3.49（續）平均成績視圖A_GRADE(S#，SNAME，AVG_GRADE)的創建語句為：CREATEVIEWA_GRADE（S#，SNAME，AVG_GRADE）ASSELECTS.S#，SNAME，AVG(GRADE)FROMS，SCWHERES.S#=SC.S#ANDGRADEISNOTNULL；GROUPBYS.S#ORDERBYS.S#；

3.3.3在視圖上進行查詢操作

例3.50利用視圖A_GRADE查詢學生所學課程的平均成績。SELECT*FROMA_GRADE；

如果用查詢語句實現查詢學生所學課程的平均成績，其查詢語句應為：SELECTS.S#，SNAME，AVG(GRADE)FROMS，SCWHERES.S#=SC.S#ANDGRADEISNOTNULL；GROUPBYS.S#ORDERBYS.S#；3.3.4在視圖上進行數據更新

在視圖上進行數據更新操作(INSERT、DELETE、UPDATE)時應注意以下幾點：（1）如果某視圖是建立在多個表的基礎上，那么不允許對該視圖進行更新操作。（2）如果在某視圖的定義中使用了GROUPBY子句和聚合函數，那么不允許對該視圖進行更新操作。（3）如果視圖是建立在單個表的基礎上，且視圖的屬性列個數與表的屬性列個數相同，或少于表的屬性列個數時，且所少的列是表中可為空值(NULL)的列，則可在該視圖上進行更新操作。3.3.6用戶視圖對數據庫設計和系統性能帶來的影響

（1）用戶視圖給數據庫應用系統的設計提供了極大的優越性

用戶視圖的建立，會使在應用程序中用到并反復出現的含有復雜關系聯接和投影的查詢語句被簡單的視圖查詢語句所代替。這樣不僅簡化了用戶接口，使應用程序中的SQL語句變得簡單明了，清晰可讀；而且可以使應用程序員把編寫應用程序的主要精力集中在對數據的處理、分析和用戶界面的實現上，方便應用程序的設計。3.3.6用戶視圖對數據庫設計和系統性能帶來的影響

（2）實現了數據庫的邏輯數據獨立性

由于不同的用戶具有不同用戶視圖，這樣就使一個用戶視圖的變化不會影響另一些用戶視圖。當數據庫的邏輯結構（概念模型）或存儲結構（存儲模型）發生變化時，并且這些變化與某一或某些用戶的視圖無關時，就不必改變該用戶的應用程序；當這些變化與某一或某些用戶的視圖有關時，可通過改變基本表到用戶視圖之間的映射關系，也即通過重新定義用戶視圖而使用戶視圖保持不變或稍有變化，從而不必修改應用程序或少量修改應用程序。這樣就實現了數據庫的邏輯數據獨立性。3.3.6用戶視圖對數據庫設計和系統性能帶來的影響

（3）可起到了對數據庫中數據進行安全保護的作用

由于不同用戶具有不同的用戶視圖，這樣就使得在各個用戶視圖中只出現他自己關心的那部分數據，其它數據對他來說是不能使用的。而且系統給用戶使用的是視圖而不是在數據庫中存儲有數據的表。這樣就起到了對數據庫中數據進行安全保護的作用。3.4.1數據插入操作（數據的導入）把從某個或某些表中查詢出的數據插入到另一個表中去。語句格式為：INSERTINTO<表名>[（<列名表>）]）<子查詢>；

其中，<子查詢>是一個合法的SELECT查詢語句。例3.51設在教學管理數據庫系統中，要建立某些臨時表來輔助有關管理過程。若其中有一個臨時表用于暫存選修了“信息安全技術”課程的學生的學號、姓名、專業名稱和班級，表的其格式為：S_C（S#，SNAME，SSNAME，CLASS）例3.51（續）則將從有關表中查詢出的有關數據組成的記錄插入該表的插入語句為：INSERTINTOS_C(S#，SNAME，SSNAME，CLASS)SELECTS.S#，SNAME，SSNAME，CLASSFROMS，SSWHERESS.SCODE#=S.SCODEANDS#IN(SELECTS#FROMSCWHEREC#IN(SELECTC#FROMCWHERECNAME=‘信息安全技術’))；例3.52設在教學管理數據庫系統中，有一個臨時表用于暫存平均成績大于等于80分的女同學的學號和平均成績。臨時表的格式為：S_AVG(S#,AVG_GRADE)則將從有關表中查詢出的有關數據組成的記錄插入該表的插入語句為：INSERTINTOS_AVG(S#,AVG_GRAGE)SELECTS#,AVG(GRADE)FROMSCWHERES#IN(SELECTS#FROMSWHERESSEX=‘女’）GROUPBYS#HAVINGAVG(GRADE)>=80；3.4.2數據更新操作用子查詢表示條件的數據更新操作。語句格式為：

UPDATE<表名>SET<列名1>＝<表達式1>[，<列名2>＝<表達式2>,…,<列名n>＝<表達式n>]WHERE<條件>；

其中，WHERE條件中包含有SELECT查詢子句。例3.53為了加強對高水平尖子人才的重點培養，學校擬將計算機應用技術專業(專業代碼為：S0401)中，各門課程均在85分以上的學生單獨編為200400班，所以需要修改學生基本情況數據庫中的“班級”屬性。其實現語句為：UPDATESSETCLASS=‘200400’WHERESCODE=‘S0401’ANDS#IN(SELECTS#FROMSCWHEREGRADE>85)；例3.54由于試題難度原因，需要將“計算機網絡”課的成績提高5%。其實現語句為：UPDATESCSETGRADE=GRADE*1.05WHEREC#IN(SELECTC#FROMCWHERECNAME=‘計算機網絡’）；

3.5.1嵌入式SQL的概念

交互式SQL：是非過程語言，大多數語句的執行都是獨立的，與上下文無關的；無法滿足絕大多數應用所需的過程性要求。

嵌入式SQL：引入過程性結構和把SQL語言嵌入到C、PASCAL等高級語言的機制。

宿主語言：在SQL嵌入形式中用到高級語言。

應用程序：含有嵌入SQL語句的高級語言應用程序稱為宿主應用程序，簡稱為宿主程序或應用程序。3.5.2嵌入式SQL遇到的問題

（1）當SQL語言語句嵌入到C語言后，宿主程序中就包含了不屬于高級語言的一部分成份。換句話說，宿主程序不再是普通的高級語言源程序了，顯然也不能用通常的編譯、連接方式對其進行處理。如何實現宿主程序的編譯是要解決的問題之一。

3.5.2嵌入式SQL遇到的問題

（2）SQL語言是非過程性語言，大多數SQL語句的執行是獨立的，與上下文無關的。而C語言是過程性語言，程序的執行機制是順序、分支和循環，當前語句的執行與前面語句的執行結果有關，各語句的執行是上下文有關的。那么，當把SQL語句嵌入到C語言后，如何來確定SQL語句的執行順序是要解決的問題之一。

3.5.2嵌入式SQL遇到的問題

（3）SQL語言語句中的屬性名是表示關系的屬性及其當前值的集合型“參變量”。而C語言語句中的變量既有單值變量，又有數組變量。如何建立兩種語言變量之間聯系是要解決的問題之一。（4）SQL語句是面向集合的，一次查詢的結果往往有許多行。而C語言的數據處理方式原則上是一次處理一個記錄的順序方式。如何使它們在對數據的保持和處理上相一致是要解決的問題之一。3.5.3嵌入式SQL的實現

兩種實現途徑：

（1）建立全新的編譯程序，使之可直接編譯SQL語句；

（2）采用預處理方式，把宿主程序中的SQL語句轉換成可為高級語言調用的函數形式：

后一種途徑的實現需要：

提供預編譯器；

提供實現所有SQL語句的函數庫。3.5.3嵌入式SQL的實現（續）

基于預處理方式的數據庫應用程序從編寫到執行的過程為：

（1）編寫包含有嵌入式SQL語句的宿主語言應用程序；（2）利用預編譯器對應用程序進行預編譯，產生含有高級語言函數調用的“普通”高級語言源程序；（3）利用高級語言編譯器編譯由(2)生成的高級語言源程序，生成相應的目標文件；（4）對生成的目標文件和所需的函數庫進行連接，生成可執行文件；（5）運行可執行文件形式的應用程序。3.5.4SQL語句的嵌入形式

3.5.5宿主程序的組成與結構

預編譯單元：在嵌入式SQL中，將宿主應用程序中的一個源程序文件稱為一個預編譯單元。宿主程序的組成：具有與C語言一樣的組成結構，一個宿主應用程序可以分成若干個源程序文件，每個源程序文件可以是一個獨立的預編譯單元，可以將這些經過獨立預編譯的程序再分別獨立編譯，并生成各自的目標文件，最后將這些目標文件連接成一個完整的執行文件。

3.5.5宿主程序的組成與結構

預編譯單元的組成結構：

（1）說明部分（DECLARE部分）（2）通信區定義部分（3）系統連接部分

（4）程序體程序首部程序體1.說明部分（DECLARE部分）

（1）主語言變量、宿主變量、宿主變量的使用規則：主語言變量：宿主變量：在SQL語句中使用的主語言的變量稱為宿主變量。

例3.55根據由宿主變量S1給出的學生學號，查詢、學生的姓名、性別和出生年月。

EXECSQLSELECTSNAME，SSEX，SBIRTHINFROMS

INTO:SNAME1，:SSEX1，:SBIRTHWHERES#=:S1；由本例說明：宿主變量是主語言和SQL語句都可以對其賦值和引用其值的變量。

宿主變量的使用規則：

①宿主變量必須在DECLARE部分定義。②在SQL語句中使用宿主變量時，必須在僅接宿主變量的前面寫一個冒號“：”；在純C語言語句中使用宿主變量時，與非宿主變量一樣不加任何標記。③宿主變量不能是SQL語言中的保留字。④一個宿主變量在一條SQL語句中只能使用一次。⑤宿主變量可以帶指示變量。1.說明部分（DECLARE部分）（續）（2）指示變量

是一個用來指示所指宿主變量的值或條件的整型變量。宿主變量利用指示變量賦空值或檢測是否是空值。

指示變量的使用規則為：

①指示變量必須在DECLARE部分定義。②在SQL語句中使用指示變量時，必須在僅接指示變量的前面寫一個冒號“：”；在純C語言語句（也即，主語言）中使用指示變量時，不用加任何標記。③指示變量不能是SQL語言中的保留字。④指示變量必須定義成2字節的整型變量，如short，int類型。⑤在SQL語句中，指示變量必須緊跟在所指宿主變量之后，中間不能有逗號或空格。例3.56宿主變量帶有指示變量的例子。根據由宿主變量S1給出的學號查詢學生姓名、性別和出生年月。

EXECSQLSELECTSNAME，SSEX，SBIRTHINFROMSINTO:SNAME1:SNAME2，:SSEX1:SSEX2，

:SBIRTH:SBIRTH2WHERES#=:S1；1.說明部分（DECLARE部分）（續）（3）DECLARE的格式

用DECLARE定義宿主變量和指示變量的描述格式為：EXECSQLBEGINDECLARESECTION；<定義宿主變量和指示變量語句> EXECSQLENDDECLARESECTION；1.說明部分（DECLARE部分）（續）（4）作為宿主變量的指針和C語言一樣，宿主變量的指針是由星號“*”和變量名來說明的。比如：EXECSQLBEGINDECLARESECTION；intGRADE1，GRADE2，*intptr；EXECSQLENDDECLARESECTION；但在SQL語句中，指針變量前不寫星號（星號是隱含的），而要在變量名前加一個冒號“：”。比如：SELECTINT_PTRINTO:intptrFROM…1.說明部分（DECLARE部分）（續）（5）VARCHAR偽類型VARCHAR偽類型用于說明稱為偽類型變量的變長字符串。偽類型變量在DECLARE部分說明后，就可以在宿主程序中被引用了。比如：EXECSQLBEGINDECLARESECTION；VARCHARuid[20]；EXECSQLENDDECLARESECTION；相當于定義了一個結構體變量：struct{unsignedintlen；unsignedchararr[20]；}uid;在C語言中對該結構體變量的引用方式為：uid.len是無符號整數類型，用于表示（存放）偽類型變量定義的字符串的長度。uid.arr用于表示（存放）偽類型變量定義的字符串。例3.57給出例3.56的語句中的主變量說明。EXECSQLBEGINDECLARESECTION；VARCHARSNAME1[10]，SSEX1[2]，S1[9]；dateSBIRTH；intSNAME2，SSEX2，SBIRTH2；EXECSQLENDDECLARESECTION；

（以上是例3.57的正確形式，注意改錯！）

2.SQL通信區及其定義

（1）問題的提出

在基于預處理的宿主語言中，宿主程序中的一條SQL語句的執行實質上是執行一次函數調用，通過相應的函數調用實現該SQL語句的功能。函數在執行過程中可能出現各種情況，所以就引入了SQL通信區SQLCA，利用SQL通信區來返回函數調用的執行狀態信息，在返回后根據返回信息，決定下一步的執行流程或對錯誤的處理。

2.SQL通信區及其定義

（2）SQL通信區的定義

在宿主程序的相應部分用加上語句：EXECSQLINCLUDESQLCA；

2.SQL通信區及其定義

（3）SQLCA返回的信息類別

①任務完成，一切正常；②任務完成，但有例外，也即有警告信息；③有某種錯誤，任務沒有完成。

2.SQL通信區及其定義

（4）對SQLCA返回信息的判別方式

WHENEVER語句：

WHENEVER語句格式：

EXECSQLWHENEVER[SQLERROR|SQLWARING|NOTFOUND][STOP|CONTINUE|GOTO<語句標號>]；3.系統連接（數據庫登錄）CONNECT語句：CONNECT語句格式：

EXECSQLCONNECT：<用戶名>IDENTIFIEDBY：<口令>；或

EXECSQLCONNECT：<用戶名>/<口令>；4.程序體

在程序首部（DECLARE部分、SQL通信區、系統連接部分）之后，就是程序體。程序體是主語言語句和由“EXECSQL”為開始標識的SQL語句（DDL語句、DML語句、DCL語句）組成。例3.58宿主應用程序系統連接（注冊登錄）示例。

#include<stdio.h>#include<string,h>

EXECSQLBEGINDECLARESECTION；/*說明SQL變量*/VARCHARuid[10]；/*uid中存放用戶名*/VARCHARpwd[8]；/*pwd中存放口令*/

EXECSQLENDDECLARESECTION；

EXECSQLINCLUDESQLCA；/*說明SQL通信區*/

Main(){strcpy(uid.arr,”scott”)；/*初始化用戶名:scott*/uid.len=strlen(uid.arr)/*初始化uid的長度值*/strcpy(pwd.arr,”tigger”)；/*初始化口令:tigger*/pwd.len=strlen(pwd.arr)；/*初始化pwd的長度值*/

EXECSQLCONNECT:uidIDENTIFIEDBY:pwd；

/*注冊登錄數據庫*/Printf(“SuccessfullyconnectedtoSQL\n”)；

EXECSQLCOMMITWORKRELEASE;/*提交并釋放數據庫*/exit(0)；}3.5.6數據更新及其嵌入式SQL應用程序設計

（1）數據的插入例3.59：向數據庫中已存在的課程關系表中插入一個記錄的應用程序。

#include<stdio.h>#include<string.h>EXECSQLBEGINDECLARESECTION；/*說明SQL變量*/VARCHARuid[10]；/*uid中存放用戶名*/VARCHARpwd[8]；/*pwd中存放口令*/VARCHARcnum[7]；/*定義課程號CNUM為偽類型*/VARCHARcname[30]；/*定義課程名CNAME為偽類型*/intclassh；/*定義學時數CLASSH為整型數*/EXECSQLENDDECLARESECTION；EXECSQLINCLUDESQLCA；/*說明SQL通信區*/Main()Main(){strcpy(uid.arr，”scott”)；/*初始化用戶名：scott*/uid.len=strlen(uid.arr)；/*初始化uid的長度值*/strcpy(pwd.arr，”tigger”)；/*初始化口令：tigger*/pwd.len=strlen(pwd.arr)；/*初始化pwd的長度值*/

EXECSQLCONNECT:uidIDENTIFIEDBY:pwd；/*注冊登錄數據庫*/printf(“用戶‘%s’已成功地連接到數據庫！\n”，uid.arr)；While(1){prinft(“\n\n輸入課程號：”)；scanf(”%s”，&cnum)；cnum.len=strlen(cnum.arr)；/*設置課程號CNUM的長度*/prinft(“\n\n輸入課程名稱：”)；scanf(”%s”，&cname)；cname.len=strlen(cname.arr)；/*設置課程名CNAME的長度*/prinft(“\n\n輸入課程的學時數：”)；scanf(”%d”，&classh)；

EXECSQLINSERTINTOC(C#，CNAME，CLASSH)VALUES(:cnum，:cname，:classh)；EXECSQ