數(shù)據(jù)庫選修課-關(guān)系數(shù)據(jù)庫

Page1計(jì)教中心數(shù)據(jù)庫原理及應(yīng)用§2關(guān)系數(shù)據(jù)庫姚普選Page2內(nèi)容關(guān)系模型

關(guān)系及關(guān)系模式

關(guān)系模型的數(shù)據(jù)操作

數(shù)據(jù)完整性約束

關(guān)系代數(shù)

關(guān)系運(yùn)算

關(guān)系代數(shù)的運(yùn)算種類

三種關(guān)系運(yùn)算SQL語言§2.1關(guān)系模型關(guān)系模型由三部份組成

數(shù)據(jù)結(jié)構(gòu)、數(shù)據(jù)操作、完整性約束1.關(guān)系模型的數(shù)據(jù)結(jié)構(gòu)：數(shù)據(jù)的邏輯結(jié)構(gòu)_二維表學(xué)生選修課程mn概念模型關(guān)系模式表示為

關(guān)系名（屬性名1，屬性名2，…，屬性名n）例：學(xué)生(學(xué)號，姓名，性別，年齡，班級，學(xué)院)注意：關(guān)系模式是型(type)，描述一個(gè)關(guān)系的結(jié)構(gòu)關(guān)系是值，是元組的集合描述某一時(shí)刻關(guān)系模式的內(nèi)容故關(guān)系模式是相對穩(wěn)定的，靜態(tài)的；而關(guān)系是隨時(shí)間變化的，動(dòng)態(tài)的關(guān)系可用來表示實(shí)體（如學(xué)生，課程等）也可用來表示實(shí)體間的聯(lián)系（如選課）關(guān)系的性質(zhì)每列都是不可再分的基本字段（原子屬性）一列中各個(gè)分量具有相同的性質(zhì)和意義；

列名是唯一的（不同不二名）

行的次序可任意交換，不會(huì)改變關(guān)系的意義

列的次序可任意交換，不會(huì)改變關(guān)系的意義元組代表了一個(gè)實(shí)體因此表中不允許出現(xiàn)相同的行故，判別兩個(gè)關(guān)系是否相等，與屬性次序、元組次序、關(guān)系名均無關(guān)若僅這三點(diǎn)有別，其余完全相同，可認(rèn)為兩個(gè)關(guān)系相等2.關(guān)系模型的數(shù)據(jù)操作

數(shù)據(jù)操作——包括對表、行、列的操作主要操作包括查詢、添加（或插入）、刪除、修改例如：查找來自于江蘇的02級學(xué)生的信息

關(guān)系模型的數(shù)據(jù)操作的特點(diǎn)：關(guān)系模型的操作對象是集合操作對象和操作結(jié)果都是關(guān)系（表）用戶只要指明干什么，而不必考慮怎么干細(xì)節(jié)由DBMS處理SQL語言實(shí)現(xiàn)關(guān)系數(shù)據(jù)庫操作

RDBMS支持SQL語言3.關(guān)系模型的數(shù)據(jù)完整性約束完整性約束：規(guī)定了特定數(shù)據(jù)模型中數(shù)據(jù)必須滿足的條件無論對數(shù)據(jù)進(jìn)行什么操作，其結(jié)果都必須滿足完整性約束的條件完整性約束的目的：

?使對數(shù)據(jù)的操作能夠正確進(jìn)行

?使數(shù)據(jù)有意義例如：

學(xué)號既不能為空也不能重復(fù)年齡不大于60歲學(xué)生所屬班級名稱不能寫成根本不存在的班級考試成績是0-100之間的整數(shù)四類完整性約束實(shí)體完整性，引用完整性，值域完整性，用戶定義的完整性實(shí)體完整性（EI，EntityIntegrity）

強(qiáng)制保證表的主碼的完整性

通過主碼實(shí)現(xiàn):主碼的任一屬性都不能取空值“空”（null）指無值（或不知道）0不是null。null≠”null”∵主碼是區(qū)分各個(gè)實(shí)體和聯(lián)系的惟一標(biāo)識為空則意味著可存在不以主碼為標(biāo)識的實(shí)體，與主碼定義矛盾引用(參照)完整性（RI，ReferentialIntegrity）插入、修改或刪除數(shù)據(jù)時(shí)，維護(hù)各表間數(shù)據(jù)一致性主要通過主碼與外碼實(shí)現(xiàn)若二表通過主碼與外碼建立了聯(lián)系，則：外碼所在表（子表）不能引用主碼所在表（主表）中不存在的數(shù)據(jù)主表中數(shù)據(jù)變化時(shí)，子表中相關(guān)數(shù)據(jù)相應(yīng)變化。反之也一樣有時(shí)外碼可取空值。如“學(xué)生”關(guān)系的“宿舍號”例：“學(xué)生”、“專業(yè)”二實(shí)體表示為關(guān)系

學(xué)生（學(xué)號，姓名，性別，專業(yè)號，出生日期）

專業(yè)（專業(yè)號，專業(yè)名）引用完整性值域完整性（DI，DomainIntegrity）限制屬性值的范圍。例如：

性別只能是”男”或”女”，年齡不大于30等

Access中的“有效性規(guī)則”：“男”or“女”

<=30

SQL語句的Where子句中寫：性別=“男”or“女”年齡<=30屬性值可為空，例如，學(xué)生管理信息系統(tǒng)中，新生報(bào)到后要記錄其住宿房號，若該生不在學(xué)校住宿，則宿舍號可為空用戶定義的完整性實(shí)現(xiàn)應(yīng)用系統(tǒng)的業(yè)務(wù)規(guī)則，由用戶自行定義如：項(xiàng)目書的名稱必須以“XM”開頭折扣額不能大于銷售額，等等DBMS提供了用戶自定義完整性的手段：約束（constraint）標(biāo)識列（identitycolumn）默認(rèn)值（default）規(guī)則（rule）觸發(fā)器（trigger）數(shù)據(jù)類型驗(yàn)證（datatype）索引（index）存儲(chǔ)過程（storedprocedure）Page13§2.2關(guān)系代數(shù)1.關(guān)系代數(shù)簡介關(guān)系模型：提供一組完備的關(guān)系運(yùn)算，支持關(guān)系數(shù)據(jù)庫檢索和修改（插入、更新、刪除）操作關(guān)系運(yùn)算方法分為兩類：關(guān)系代數(shù)：關(guān)系數(shù)據(jù)操縱的傳統(tǒng)表達(dá)方式，

以集合代數(shù)運(yùn)算方法對關(guān)系進(jìn)行數(shù)據(jù)操作以若干個(gè)關(guān)系為運(yùn)算對象，運(yùn)算結(jié)果產(chǎn)生新關(guān)系關(guān)系演算：以謂詞表達(dá)式描述關(guān)系操作的條件和要求

Page14關(guān)系代數(shù)運(yùn)算的種類：二元二元按列輔助專門的關(guān)系運(yùn)算按行按行運(yùn)算說明不等于≠小于等于≤大于等于≥等于＝小于＜或∨與∧除連接投影廣義笛卡爾積×交∩差－大于＞比較運(yùn)算符∪傳統(tǒng)的集合運(yùn)算符并非邏輯運(yùn)算符選擇專門的關(guān)系運(yùn)算符含義運(yùn)算符

Page152.關(guān)系運(yùn)算并運(yùn)算：R∪S

元組屬于R或S差運(yùn)算：R-S

元組屬于R交運(yùn)算：R∩S

元組既屬R

又屬于S⑴集合運(yùn)算(按元組)設(shè)關(guān)系R、S度(屬性個(gè)數(shù))為n，相應(yīng)屬性值取自同一個(gè)域

Page16⑵三種關(guān)系運(yùn)算：選擇、投影、連接例7-4：“學(xué)生”關(guān)系

Access數(shù)據(jù)庫中的“學(xué)生”表

Page17選擇運(yùn)算：

按條件從一個(gè)或多個(gè)關(guān)系中

抽取若干個(gè)元組組成新關(guān)系，

新關(guān)系是原關(guān)系的子集。

例如，條件：性別=“女”

Page18投影運(yùn)算：從一個(gè)或多個(gè)關(guān)系中

選擇一些屬性組成新關(guān)系，

新關(guān)系是原關(guān)系子集例：學(xué)生關(guān)系在學(xué)號、姓名、

性別、班級四個(gè)屬性上投影

Page19連接運(yùn)算：連接運(yùn)算：將兩個(gè)關(guān)系中滿足條件的元組連接起來組成新關(guān)系等連接：取兩關(guān)系笛卡爾積中A、B屬性值相等的元組R.C=S.TRSPage20

§2.3SQL語言介紹SQL（StructuredQueryLanguage，結(jié)構(gòu)化查詢語言）

ISO命名為國際標(biāo)準(zhǔn)數(shù)據(jù)庫語言提供了數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)操縱和數(shù)據(jù)控制語句，是一種綜合性的數(shù)據(jù)庫語言，可獨(dú)立完成數(shù)據(jù)庫生命周期中的全部活動(dòng)用戶可直接鍵入SQL命令來操縱數(shù)據(jù)庫，也可將其嵌入高級語言（C、Pascal、Java等）程序中使用各種RDBMS一般都支持SQL或提供SQL接口。其影響已超出數(shù)據(jù)庫領(lǐng)域，擴(kuò)展到了其他領(lǐng)域Page211.SQL對關(guān)系數(shù)據(jù)庫的支持SQL視圖1視圖2基表1基表2基表3基表4存儲(chǔ)文件1存儲(chǔ)文件2外模式模式內(nèi)模式從DBMS的角度看，數(shù)據(jù)庫系統(tǒng)有一個(gè)嚴(yán)謹(jǐn)?shù)捏w系結(jié)構(gòu)，從而保證其功能得以實(shí)現(xiàn)SQL語言支持三級模式結(jié)構(gòu)

Page22說明：由基表構(gòu)成關(guān)系數(shù)據(jù)庫的“模式”基表是實(shí)際的表，一個(gè)基對應(yīng)一個(gè)關(guān)系；一個(gè)或多個(gè)表對應(yīng)一個(gè)存儲(chǔ)文件；存儲(chǔ)文件的邏輯結(jié)構(gòu)組成關(guān)系數(shù)據(jù)庫內(nèi)模式；視圖是從一個(gè)或多個(gè)表中導(dǎo)出的表，為虛表，數(shù)據(jù)庫只存放其定義而數(shù)據(jù)仍在原表中視圖和基表都是關(guān)系用戶使用SQL語言對基表和視圖進(jìn)行查詢、更新等各種操作

SQL語言功能概要四類功能：數(shù)據(jù)定義：CREATE、DROP、ALTER創(chuàng)建表、索引、查詢數(shù)據(jù)操縱：INSERT、UPDATE、DELETE

插入、更新、刪除數(shù)據(jù)查詢：SELECT

查詢數(shù)據(jù)控制：GRANT、REVOTE

授予、收回權(quán)限閱讀：SQL語言的發(fā)展SQL：StructuredQueryLanguage1974年IBM公司Boyce和Chamberlin提出1975-1979年IBM在SystemR關(guān)系數(shù)據(jù)庫系統(tǒng)原型中實(shí)現(xiàn)1986年10月成為ANSI標(biāo)準(zhǔn)：SQL-861987年6月接納為ISO標(biāo)準(zhǔn)1989年4月ISO較為完整的SQL-89標(biāo)準(zhǔn)1992年8月ISO推出更完善的SQL-92標(biāo)準(zhǔn)1999年ISO推出新的SQL-99標(biāo)準(zhǔn)閱讀：SQL語言的特點(diǎn)一體化集數(shù)據(jù)定義語言DDL、數(shù)據(jù)操縱語言DML和數(shù)據(jù)控制語言DCL于一體，綜合管理功能于一身高度非過程化用戶只需提出“做什么”，而不需指明“怎么做”面向集合的操作方式操作的數(shù)據(jù)不是一條記錄，而是記錄的集合同樣語法提供兩種使用方式用戶可直接鍵盤交互方式使用SQL命令，也可將SQL語句嵌入其他高級語言（VB、Delphi、VC、Java等）語言簡潔，容易掌握命令（語句）只有9條，語法接近于自然語言2.SQL語言的數(shù)據(jù)類型不同數(shù)據(jù)庫產(chǎn)品所支持的數(shù)據(jù)類型稍有差別（以SQLServer2000為例）兩大類：?

系統(tǒng)數(shù)據(jù)類型系統(tǒng)內(nèi)含的?用戶自定義數(shù)據(jù)類型

在系統(tǒng)數(shù)據(jù)類型的基礎(chǔ)上建立使用系統(tǒng)存儲(chǔ)過程sp_addtype定義SQLServer2000的系統(tǒng)數(shù)據(jù)類型如下：1）數(shù)值型

數(shù)據(jù)類型說明精確整數(shù)型Bigint8字節(jié)，-263～263-1Int4字節(jié)，-231～231-1Smallint2字節(jié)，-215～215-1Tinyint1字節(jié)，0～255精確數(shù)值型Decimal(p,q)或Numeric(p,q)-10^38+1～1038-1p為總位數(shù)，q為小數(shù)位數(shù)位型Bit存儲(chǔ)二進(jìn)制0或1近似浮點(diǎn)型Float-1.79E+308～1.79E+308Real-3.4E+38～3.4E+382）字符型

數(shù)據(jù)類型說明定長字符串Char(n)n＝1～8000變長字符串Varchar(n)n＝1～8000文本Text最多231－1個(gè)字符定長UnicodeNchar(n)n＝1～4000變長UnicodeNvarchar(n)n＝1～4000Unicode文本Ntext最多230－1個(gè)字符定長二進(jìn)制字符Binary(n)n＝1～8000變長二進(jìn)制字符Varbinary(n)n＝1～8000任意二進(jìn)制數(shù)據(jù)Image最多231－1個(gè)字節(jié)，約2GB3）日期時(shí)間型數(shù)據(jù)類型說明Datetime8字節(jié)，1753年1月1日～9999年12月31日精度百分之三秒Smalldatetime4字節(jié)，1900年1月1日～2079年6月6日，精度1分鐘日期：英文數(shù)字Oct252005數(shù)字加分隔符2005-10-25或2005/12/25純數(shù)字20051025時(shí)間：24小時(shí)制15:28:5612小時(shí)制3:28:56PM4）貨幣型數(shù)據(jù)類型說明Money8字節(jié)，-263～263-1，精確度為4位小數(shù)，最多19位數(shù)字Smallmoney4字節(jié)，-214748.3648～214748.3647，精確度為4位小數(shù)SQL語言的數(shù)據(jù)定義語句操作對象創(chuàng)建刪除修改

表

CREATETABLE

DROPTABLE

ALTERTABLE

視圖

CREATEVIEWDROPVIEW

索引

CREATEINDEXDROPINDEX

觸發(fā)器

CREATETRIGGER存儲(chǔ)過程CREATEPROCEDURE§2.3SQL語言數(shù)據(jù)定義功能1.表的定義與刪除1)建立表結(jié)構(gòu)_基本語法：列名——表中列的名字類型——該列數(shù)據(jù)類型約束——該列完整性約束條件：NOTNULL列值不能為空（不能用于表級約束）DEFAULT<常量>指定列的默認(rèn)值（不能用于表級約束）UNIQUE列值不能重復(fù)CHECK<表達(dá)式>限定列的取值范圍PRIMARYKEY指定本列為主碼FOREIGNKEY本列為引用其他表的外碼，用法如下

FOREIGNKEY[列名]REFERENCES外表名(外表列名)

CREATETABLE表名

(列名類型約束，…)，[表級約束]例：創(chuàng)建student表CREATETABLEstudent(Snochar(7)PRIMARYKEY,Snamechar(10)NOTNULL,Ssexchar(2)CHECK(Ssex='男'ORSsex='女'),SagetinyintCHECK(Sage>=15ANDSage<=45),Sdeptchar(20)DEFAULT'計(jì)算機(jī)系')默認(rèn)為‘計(jì)算機(jī)系’字符串，長度為20所在系Sdept15～45整數(shù)年齡Sage‘男’或‘女’字符串，長度為2性別Ssex非空字符串，長度為10姓名Sname主碼字符串，長度為7學(xué)號Sno約束數(shù)據(jù)類型說明列名例：創(chuàng)建course表CREATETABLEcourse(Cnochar(10)NOTNULL,Cnamechar(20)NOTNULL,CcredittinyintCHECK(Ccredit>0),SemestertinyintCHECK(Semester>0),PeriodintCHECK(Period>0),PRIMARYKEY(Cno))取值大于0整數(shù)學(xué)時(shí)Period取值大于0整數(shù)學(xué)期Cemester取值大于0整數(shù)學(xué)分Ccredit非空字符串，長度為20課程名Cname主碼字符串，長度為10課程號Cno約束數(shù)據(jù)類型說明列名例：創(chuàng)建SC表CREATETABLESC(Snochar(7)NOTNULL,Cnochar(10)NOTNULL,GradetinyintCHECK(Grade>=0ANDGrade<=100),PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESstudent(Sno),FOREIGNKEY(Cno)REFERENCEScourse(Cno))取值0～100整數(shù)成績Grade主碼，引用course的外碼字符串，長度為10課程號Cno主碼，引用student的外碼字符串，長度為7學(xué)號Sno約束數(shù)據(jù)類型說明列名說明：刪除表時(shí)會(huì)將表結(jié)構(gòu)和表中的數(shù)據(jù)一起刪除例：刪除test表

DROPTABLEtest2)刪除表（DROPTABLE）基本語法：DROPTABLE表名3)修改表結(jié)構(gòu)基本語法：例1：為SC表添加“選課類別”列：XKLBchar(4)

ALTERTABLESCADDXKLBchar(4)例2：將新添加的XKLB列的類型改為char(6)

ALTERTABLESCALTERCOLUMNXKLBchar(6)ALTERTABLE表名

[ALTERCOLUMN列名

新類型]修改列定義|[ADD[COLUMN]列名類型約束]增加新列|[DROPCOLUMN列名]刪除列|[ADDPRIMARYKEY(列名列表)]增加主碼約束|[ADDFOREIGNKEY(列名)REFERENCES

外表名

(列名)]增加外碼約束更新：插入、刪除、修改表中插入一行數(shù)據(jù)：

insertintostudent(…)VALUES(…)修改（更新）表中數(shù)據(jù)：

UPDATEstudentSETSage=Sage+1刪除數(shù)據(jù)：

DELETESCWHEREGrade<60清空整個(gè)表：

TRUNCATETABLESC§2.4SQL語言數(shù)據(jù)定義功能1.插入數(shù)據(jù)基本語法：列名表——表中的列名的列表，列名之間用逗號分隔未指定列名表時(shí)，值列表中值的順序須與表中定義列的順序一致，且每列都要有值（或?yàn)榭眨┲盗斜怼鄳?yīng)列的值，值之間用逗號分隔值列表中的值要與列名表中的列按位置順序?qū)?yīng)，且應(yīng)滿足約束條件INSERT[INTO]表名

[(列名表)]VALUES(值列表)例：學(xué)生記錄（9512101,李勇,男,19,計(jì)算機(jī)系）插入student表insertintostudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('9512101','李勇','男',19,'計(jì)算機(jī)系')2.更新數(shù)據(jù)基本語法：例1：所有學(xué)生年齡加1

UPDATEstudentSETSage=Sage+1

例2：‘9512101’學(xué)生年齡加1UPDATEstudentSETSage=Sage+1WHERESno='9512101'SET子句——指定要改的列及改后的值，可一次修改多列WHERE子句——指定要修改表中哪些記錄，如果省略，則所有記錄的指定列都被修改UPDATE表名

SET列名=表達(dá)式

[,…][WHERE條件

]3.刪除數(shù)據(jù)基本語法：例1：刪除所有學(xué)生的選課記錄

DELETEFROMSC

例2：刪除所有不及格學(xué)生的選課記錄

DELETESCWHEREGrade<60WHERE子句——指定要?jiǎng)h除表中哪些記錄，如果省略，則所有記錄都被刪除，僅保留表的結(jié)構(gòu)DELETE[FROM]表名

[WHERE條件

]4.清空整個(gè)表基本語法：TRUNCATETABLE表名說明：表中所有記錄都被刪除，僅保留表的結(jié)構(gòu)。要?jiǎng)h除所有數(shù)據(jù)，TRUNCATE比DELETE速度快例：刪除所有學(xué)生的選課記錄

TRUNCATETABLESC查詢表中記錄:SELECT語句,可完成單表查詢、多表聯(lián)合查詢、視圖查詢查詢結(jié)果可0行、1行或多行，是一個(gè)記錄集§2.5SQL語言數(shù)據(jù)查詢功能基本語法：SELECT[ALL/DISTINCT]

目標(biāo)列名列表FROM表名列表WHERE條件GROUPBY列名列表HAVING組的篩選條件ORDERBY列名列表SELECT語句的說明參數(shù)描述ALL滿足條件的所有記錄都將被返回，包括重復(fù)的記錄（默認(rèn)，可以省略)DISTINCT返回記錄中不出現(xiàn)重復(fù)的記錄。這個(gè)子句在每個(gè)SELECT子句中只使用一次FROM指定數(shù)據(jù)從哪幾個(gè)表中獲取WHERE從表中選取記錄的條件GROUPBY返回的記錄將按照某一個(gè)或幾個(gè)列的值分組HAVING結(jié)果集中每個(gè)組必須滿足的條件。該子句只和GROUPBY子句聯(lián)合使用ORDERBY查詢結(jié)果按某列或幾列的值升序（ASC）或降序（DESC）排列2.5.1簡單查詢簡單查詢：單表查詢SELECT子句中指定要查詢的列:例1：查詢?nèi)w學(xué)生的學(xué)號與姓名

SELECTSno,Sname

FROMstudent例2：查詢?nèi)w學(xué)生的姓名、學(xué)號與所在系

SELECTSname,Sno,Sdept

FROMstudent1.查詢指定的列查詢?nèi)苛蟹?：目標(biāo)列名列表中列出所有的列例：查詢?nèi)w學(xué)生的記錄

SELECTSno,Sname,Ssex,Sage,SdeptFROMstudent法2：星號‘＊’代替目標(biāo)列名列表同上例：

SELECT*FROMstudent查詢經(jīng)過計(jì)算的列目標(biāo)列名列表中可包含表達(dá)式、常量或函數(shù)例1：查詢?nèi)w學(xué)生的出生年份

SELECTSname,2006-SageFROMstudent

例2：列出每個(gè)學(xué)生的學(xué)號、所選課程號、課程綜合成績：綜合成績＝(成績×0.8)+20

SELECTSno,Cno,Grade*0.8+20FROMSC例3：查詢?nèi)w學(xué)生出生年份，Sname列標(biāo)題顯示為“姓名”，出生年份列標(biāo)題顯示為“出生年份”

SELECTSname

AS'姓名',2006-Sage'出生年份'FROMstudent

避免查詢結(jié)果中的重復(fù)行使用DISTINCT子句可避免重復(fù)行例：查詢student表中的學(xué)生都屬于哪些系不使用DISTINCT:

SELECTSdeptFROMstudent使用DISTINCT:

SELECTDISTINCTSdeptFROMstudent查詢結(jié)果按列的值排序使用ORDERBY子句，可將查詢結(jié)果按某列或某幾列的值升序（ASC）或降序（DESC）排列例：查詢學(xué)生學(xué)號、姓名、年齡，按年齡升序排列

SELECTSno學(xué)號,Sname姓名,Sage年齡

FROMstudent

ORDERBYSageASC注：默認(rèn)情況下為升序，故ASC可以省略

降序排序時(shí)DESC不能省略2.選擇表中若干元組（行）查詢條件表達(dá)式謂詞比較=、>、>=、<、<=、<>(或!=)、NOT<比較運(yùn)算符>確定范圍BETWEEN…AND、NOTBETWEEN…AND確定集合IN、NOTIN相似字符串匹配LIKE、NOTLIKE空值ISNULL、ISNOTNULL多重條件AND、OR查詢過程中，可以使用篩選條件，使得查詢結(jié)果中只包含滿足條件的元組（記錄行）查詢滿足條件的元組是通過WHERE子句實(shí)現(xiàn)的。WHERE子句中常用的查詢條件如下：帶條件的查詢－比較大小例1：查詢計(jì)算機(jī)系全體學(xué)生的姓名

SELECTSnameFROMstudent

WHERESdept='計(jì)算機(jī)系'

例2：查詢所有不小于20歲的學(xué)生的姓名及年齡

SELECTSname,SageFROMstudent

WHERESage>=20

帶條件的查詢－確定范圍條件：列名|表達(dá)式[NOT]BETWEEN下限AND上限例：查詢年齡20～25歲間的學(xué)生的姓名及年齡

SELECTSname,SageFROMstudent

WHERESageBETWEEN20AND25等價(jià)于：

SELECTSname,SageFROMstudent

WHERESage>=20ANDSage>=25帶條件的查詢－確定集合用于查找列值屬于指定集合的元組條件格式：

列名[NOT]IN(常量1,常量2,…,常量n)例：查詢信息系和計(jì)算機(jī)系學(xué)生的姓名和性別

SELECTSname,SsexFROMstudentWHERESdeptIN('信息系','計(jì)算機(jī)系')帶條件的查詢－相似字符串匹配用于查詢指定列中與匹配模板相似的元組條件：

列名[NOT]LIKE匹配字符串模板模板中可包含如下四種通配符：_

代表任意單個(gè)字%

代表任意長度的字符串（包括0個(gè)）[]

匹配[]中的任意一個(gè)字符

如[xyz]表示匹配x、y、z中的任意一個(gè)[^]

不匹配[]中任意一個(gè)字符相似字符串匹配（Cont.）例1：查詢姓“李”的學(xué)生的信息

SELECT*FROMstudentWHERESnameLIKE'李%'例2：查詢姓“王”、姓“賈”的學(xué)生的信息

SELECT*FROMstudentWHERESnameLIKE'[王賈]%'例3：查詢學(xué)號第2、3位為52的學(xué)生的信息

SELECT*FROMstudentWHERESnoLIKE'_52%'帶條件的查詢－空值空值（NULL）：不確定的值比較一個(gè)值是否為空，不能用普通比較運(yùn)算符

必須使用“IS[NOT]NULL”來判斷例：查詢無考試成績的學(xué)生的學(xué)號和相應(yīng)課程號

SELECTSno,CnoFROMSCWHEREGradeISNULL

帶條件的查詢－多重條件多條件查詢：使用邏輯運(yùn)算符AND、OR構(gòu)成條件表達(dá)式例：查詢計(jì)算機(jī)系年齡在20歲以下的

學(xué)生的學(xué)號、姓名和性別

SELECTSno,Sname,SsexFROMstudentWHERESdept='計(jì)算機(jī)系'ANDSage<20

3.使用計(jì)算函數(shù)匯總數(shù)據(jù)計(jì)算函數(shù)：也稱為聚合函數(shù)或聚集函數(shù)

可對一組值進(jìn)行計(jì)算并返回計(jì)算值COUNT(*)統(tǒng)計(jì)表中元組個(gè)數(shù)COUNT(列名)統(tǒng)計(jì)本列列值個(gè)數(shù)SUM(列名)計(jì)算列值總和（列須數(shù)值型AVG(列名)計(jì)算列值平均值（列須數(shù)值型MAX(列名)求列值中最大值MIN(列名)求列值中最小值注：除COUNT(*)外，其他函數(shù)忽略NULL值使用計(jì)算函數(shù)匯總數(shù)據(jù)例1：統(tǒng)計(jì)學(xué)生表中學(xué)生的人數(shù)

SELECTCOUNT(*)FROMstudent例2：統(tǒng)計(jì)選修了課程的學(xué)生的人數(shù)

SELECTCOUNT(DISTINCTSno)FROMSC例3：計(jì)算C01課程的學(xué)生平均成績

SELECTAVG(Grade)FROMSCWHERECno='C01'例4：查詢選修C01課程的學(xué)生的最高分和最低分

SELECTMIN(Grade),MAX(Grade)FROMSCWHERECno='C01'4.對查詢結(jié)果進(jìn)行分組計(jì)算GROUP子句:依據(jù)某幾列的值對結(jié)果分組注意：若用GROUP子句，則目標(biāo)列名列表中每個(gè)列為分組依據(jù)列或計(jì)算函數(shù)。即非聚集字段全部出現(xiàn)在GROUP子句中，否則將出錯(cuò)格式：GROUPBY分組依據(jù)列名列表[HAVING組篩選條件]例1：統(tǒng)計(jì)每門課程選課人數(shù)，列出課程號和人數(shù)

SELECTCnoAS課程號,COUNT(Sno)AS選課人數(shù)

FROMSC

GROUPBYCno

例2：查詢每名學(xué)生的選課門數(shù)和平均成績

SELECTSnoAS學(xué)號,COUNT(*)AS選課數(shù),AVG(Grade)AS平均成績FROMSCGROUPBYSno例3：要求同例2，以下語句錯(cuò)誤!SELECTSno學(xué)號,Cno課程號,COUNT(*)選課數(shù),AVG(Grade)平均成績

FROMSC

GROUPBYSno因：每組只有一條記錄，

該記錄中Cno值不確定（有多個(gè)值）結(jié)果分組（Cont.）用HAVING子句可以對組的結(jié)果進(jìn)行篩選例：查詢選修了3門以上課程的學(xué)生的學(xué)號

SELECTSnoAS學(xué)號

FROMSC

GROUPBYSnoHAVINGCOUNT(*)>32.5.2多表連接查詢多表連接查詢:多個(gè)相關(guān)聯(lián)表中獲取信息實(shí)際上是計(jì)算多個(gè)表的笛卡爾積（一種關(guān)系運(yùn)算）連接查詢主要包括內(nèi)連接、自連接和外連接1.內(nèi)連接使用內(nèi)連接:兩個(gè)表的相關(guān)字段滿足連接條件，則從兩個(gè)表中提取數(shù)據(jù)并拼接成新記錄內(nèi)連接格式：FROM表1[INNER]JOIN表2

ON連接條連接條件的一般格式：

表名1.列名1<比較運(yùn)算符>表名2.列名2注意：參與比較的兩個(gè)列在語義上必須相同

多表連接查詢－內(nèi)連接（Cont.）例如，兩表根據(jù)條件number=scale進(jìn)行內(nèi)連接numbername1張山2李斯scaleAddress1北京1上海2西安2成都結(jié)果numbernamescaleAddress1張山1北京1張山1上海2李斯2西安2李斯2成都例1：查詢每名學(xué)生的情況及其選課情況。

SELECT*FROMstudentJOINSCONstudent.Sno=SC.Sno注意:結(jié)果中有重復(fù)的Sno列例2：查詢計(jì)算機(jī)系學(xué)生選課情況，

列出學(xué)生的姓名、課程號和成績SELECTSname,Cno,GradeFROMstudentJOINSCONstudent.Sno=SC.SnoWHERESdept='計(jì)算機(jī)系'可為表指定別名，以簡化書寫。但一旦指定別名，用到該表名的地方都必須使用別名例3：查詢信息系選修VB的課程的學(xué)生的成績列出學(xué)號、姓名、課程名和成績

SELECTa.Sno,a.Sname,c.Cname,b.GradeFROMstudentaJOINSCbONa.Sno=b.SnoJOINcoursecONc.Cno=b.CnoWHEREa.Sdept='信息系'ANDc.Cname='VB編程語言'2.多表連接查詢－自連接自連接:特殊的內(nèi)連接，把一個(gè)表自己與自己做內(nèi)連接

即物理上一個(gè)表，邏輯上是兩個(gè)表使用自連接時(shí)必須為自連接的表起兩個(gè)別名，使之在邏輯上成為兩個(gè)表自連接的格式：

SELECT<別名1|別名2>.列名1,<別名1|別名2>.列名2FROM表名[AS]別名1JOIN表名[AS]別名2ON連接條件

…(其它子句)2.多表連接查詢－自連接（Cont.）例：查詢與李勇在同一個(gè)系的學(xué)生的姓名和系名

SELECTS2.Sname,S2.Sdept

FROMstudentS1JOINstudentS2ONS1.Sdept=S2.SdeptWHERES1.Sname='李勇'ANDS2.Sname!='李勇'3.多表連接查詢－外連接在內(nèi)連接中，只有滿足連接條件的元組才在結(jié)果中。結(jié)果中需要不滿足連接條件的元組時(shí)：外連接外連接只限制一個(gè)表的數(shù)據(jù)必須滿足連接條件，而另一個(gè)表中的數(shù)據(jù)可以不滿足連接條件外連接格式：

FROM表1LEFT|RIGHT[OUTER]JOIN表2ON連接條件LEFT[OUTER]JOIN稱為左外連接，它限制表2中的數(shù)據(jù)必須滿足連接條件，表1不限RIGHT[OUTER]JOIN稱為右外連接，它限制表1中的數(shù)據(jù)必須滿足連接條件，表2不限多表連接查詢－外連接例：查詢學(xué)生選課情況，包括選課及未選課的學(xué)生

SELECTa.Sno,Sname,Cno,Grade

FROMstudentaLEFTJOINSCONa.Sno=SC.Sno可知：LEFTJOIN：左表(表1)數(shù)據(jù)全在結(jié)果中RIGHTJOIN：右表(表2)數(shù)據(jù)全在結(jié)果中不滿足條件的那個(gè)表的列在結(jié)果中的值均為NULL2.5.4子查詢子查詢：嵌入某個(gè)SELECT、INSERT、UPDATE或DELETE語句中的SELECT語句子查詢可以嵌套多層子查詢允許出現(xiàn)在任何能夠使用表達(dá)式的地方，

一般位于外層語句的WHERE子句或HAVING子句中，與比較運(yùn)算符或邏輯運(yùn)算符一起構(gòu)成查詢條件嵌套查詢的一般處理方法：

先處理子查詢，其結(jié)果作為父查詢的條件涉及多表的查詢，可連接查詢，也可嵌套查詢?nèi)舨樵兘Y(jié)果來自一個(gè)關(guān)系，適用于嵌套查詢1.使用子查詢進(jìn)行基于集合的測試

子查詢返回的結(jié)果是一個(gè)集合用IN（或NOTIN）操作符比較集合中的數(shù)據(jù)例1：查詢與宋江在同一個(gè)系的學(xué)生

SELECTSno,Sname,SdeptFROMstudentWHERESdept

IN(SELECTSdeptFROMstudentWHERESname='宋江‘)ANDSname!='宋江'例2：查詢成績小于60分的學(xué)生及其課程

SELECTa.Sno,Sname,Sdept,b.Cname,GradeFROMstudentaJOINSCONa.Sno=SC.SnoJOINcoursebONb.Cno=SC.CnoWHEREa.Sno

IN

(SELECTSnoFROMSCWHEREGrade<60)ANDGrade<602.使用子查詢進(jìn)行比較測試要求子查詢返回的結(jié)果是單一值，不能是集合用比較運(yùn)算符與子查詢返回的值進(jìn)行比較例：查詢選修了c02課程且成績大于此課程平均成績的學(xué)生，列出學(xué)號和成績。

SELECTSno,GradeFROMSCWHERECno='c02'ANDGrade>

(SELECTAVG(Grade)FROMSCWHERECno='c02‘)3.使用子查詢進(jìn)行存在性測試

先執(zhí)行外層查詢，再執(zhí)行子查詢

子查詢返回的是一個(gè)邏輯值（真或假）

用EXISTS（或NOTEXISTS）確定外層查詢的數(shù)據(jù)是否是滿足要求的結(jié)果

子查詢不需指定列名，一般用星號（*）代替。例：查詢選擇了c01課程的學(xué)生姓名

SELECTSnameFROMstudentaWHEREEXISTS(SELECT*FROMSCWHERESno=a.SnoANDCno='c01‘)

小結(jié)SQL語言的發(fā)展常用數(shù)據(jù)類型數(shù)據(jù)定義語句：表的建立、修改、刪除數(shù)據(jù)的增、刪、改操作數(shù)據(jù)查詢操作單表查詢無條件、條件、分組、排序、聚集函數(shù)計(jì)算多表連接查詢內(nèi)連接、自連接、左外連接、右外連接子查詢練習(xí)題1.利用本章提供的三個(gè)表（Student、Course和SC）實(shí)現(xiàn)如下操作，寫出相應(yīng)的SQL語句，并上機(jī)驗(yàn)證結(jié)果a)查詢SC表中的全部記錄b)查詢計(jì)算機(jī)系學(xué)生的姓名、年齡c)查詢成績小于60分的學(xué)生的學(xué)號、姓名、課程名和分?jǐn)?shù)d)統(tǒng)計(jì)每門課程的選課人數(shù)和考試最高分e)查詢哪些課程沒有學(xué)生選修，列出課程號和課程名練習(xí)題2.寫出創(chuàng)建下表的SQL語句，表名為test，

表結(jié)構(gòu)如下：COL1：字符型，主碼COL2：整型，允許空值COL3：字符型，長度為10，不允許空值3.用ALTER語句修改上題創(chuàng)建的表結(jié)構(gòu)：a)增加一個(gè)新列COL4，貨幣類型，允許空值b)將COL3的長度改為203.2視圖

內(nèi)容視圖概念

定義視圖

刪除視圖

§2.6視圖視圖:數(shù)據(jù)庫基本表中取出的數(shù)據(jù)組成的邏輯窗口，其它表中導(dǎo)出的虛表數(shù)據(jù)庫中只存放視圖的定義，不存放其包含的數(shù)據(jù)，這些數(shù)據(jù)仍存放在原來的基本表中視圖對應(yīng)于數(shù)據(jù)庫結(jié)構(gòu)中的外模式視圖可以建立在基本表上，也可以建立在其他的視圖上，即可以在一個(gè)視圖之上再定義視圖一個(gè)基本表上可以建立多個(gè)視圖。一個(gè)視圖也可以建立在多個(gè)基本表上從視圖中查詢與從表中查詢的語句基本相同視圖的示意圖基本表1基本表2視圖視圖有很多優(yōu)點(diǎn)：簡化操作多表間查詢時(shí)，通過視圖實(shí)現(xiàn)，將復(fù)雜查詢封裝在視圖內(nèi)部，用戶不必學(xué)習(xí)復(fù)雜的數(shù)據(jù)庫查詢技術(shù)增加了數(shù)據(jù)安全性授權(quán)用戶才能使用視圖；而且只能查詢授權(quán)的列分割數(shù)據(jù)通過列名表縱向分割，通過WHERE橫向分割提供邏輯獨(dú)立性對視圖進(jìn)行修改時(shí)，不必修改使用視圖的應(yīng)用程序定義視圖的CREATEVIEW語句的一般格式：

CREATEVIEW視圖名[(視圖列名表)]AS

子查詢語句其中子查詢可是任意SELECT語句，但要注意：子查詢中通常不含ORDERBY和DISTINCT子句定義視圖時(shí)要么指定全部視圖列，要么全部省略。若省略視圖的屬性列名，則其列名同子查詢列名如下三種情況須明確指定組成視圖的所有列名：?某個(gè)目標(biāo)列是計(jì)算函數(shù)或列表達(dá)式；?多表連接時(shí)選出了幾個(gè)同名列作為視圖的字段；?需要在視圖中為某個(gè)列選用新的更合適的列名。1.定義單源表視圖單源表視圖：視圖來自一個(gè)基本表的部分行、列可查詢和修改數(shù)據(jù)操作例：建立信息系學(xué)生的視圖

CREATEVIEWV_IS_StudentASSELECTSno,Sname,Sage FROMStudentWHERESdept='信息系‘CREATEVIEW語句的結(jié)果僅保存視圖定義對視圖查詢時(shí)，才按其定義從相應(yīng)基本表中查詢數(shù)如：SELECT*FROMIS_Student2.定義多源表視圖多源表視圖：視圖來自于多個(gè)表的數(shù)據(jù)。多源表視圖一般只用于查詢，不用于修改數(shù)據(jù)例：建立信息系選了‘c01’號課程的學(xué)