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

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

3.1SQL語言的基本概念與特點(diǎn)1989年，美國ANSI采納在ANSIX3.135-1989報(bào)告中定義的關(guān)系數(shù)據(jù)庫管理系統(tǒng)的SQL標(biāo)準(zhǔn)語言，稱為ANSISQL89。1992年，ISO又推出了SQL92標(biāo)準(zhǔn)，也稱為SQL2。目前SQL99(也稱為SQL3)在起草中，增加了面向?qū)ο蟮墓δ?。結(jié)構(gòu)化查詢語言SQL(StructuredQueryLanguage)是一種介于關(guān)系代數(shù)與關(guān)系演算之間的語言，其功能包括查詢、操縱、定義和控制四個(gè)方面，是一個(gè)通用的、功能極強(qiáng)的關(guān)系數(shù)據(jù)庫語言。目前已成為關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言，廣泛應(yīng)用于各種數(shù)據(jù)庫。

一、關(guān)系數(shù)據(jù)庫三級(jí)模式結(jié)構(gòu)3.1.2SQL語言的基本概念

SQL視圖1基本表2視圖2基本表3基本表4基本表1存儲(chǔ)文件1存儲(chǔ)文件2外模式模式內(nèi)模式基本表是本身獨(dú)立存在的表，在SQL中一個(gè)關(guān)系就對(duì)應(yīng)一個(gè)表。一些基本表對(duì)應(yīng)一個(gè)存儲(chǔ)文件，一個(gè)表可以有若干索引，索引也存放在存儲(chǔ)文件中。視圖是從基本表或其他視圖中導(dǎo)出的表，它本身不獨(dú)立存儲(chǔ)在數(shù)據(jù)庫中，也就是說數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對(duì)應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中，因此視圖是一個(gè)虛表。存儲(chǔ)文件的物理結(jié)構(gòu)及存儲(chǔ)方式等組成了關(guān)系數(shù)據(jù)庫的內(nèi)模式。存儲(chǔ)文件的物理結(jié)構(gòu)及存儲(chǔ)方式等不同數(shù)據(jù)庫管理系統(tǒng)往往是不同的，一般也是不公開的。外模式對(duì)應(yīng)于視圖（View）和部分基本表（BaseTable）;模式對(duì)應(yīng)于基本表;內(nèi)模式對(duì)應(yīng)于存儲(chǔ)文件。視圖和基本表是SQL語言的主要操作對(duì)象，用戶可以用SQL語言對(duì)視圖和基本表進(jìn)行各種操作。在用戶眼中，視圖和基本表都是關(guān)系表，而存儲(chǔ)文件對(duì)用戶是透明的。3.1.3SQL語言的主要特點(diǎn)1、綜合統(tǒng)一

SQL集數(shù)據(jù)定義語言DDL、數(shù)據(jù)操縱語言DML、數(shù)據(jù)控制語言DCL的功能于一體，語言風(fēng)格統(tǒng)一，可以獨(dú)立完成數(shù)據(jù)庫生命周期中的全部活動(dòng)，包括：定義關(guān)系模式，插入數(shù)據(jù)，建立數(shù)據(jù)庫;對(duì)數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行查詢和更新;數(shù)據(jù)庫重構(gòu)和維護(hù);數(shù)據(jù)庫安全性、完整性控制。SQL集數(shù)據(jù)查詢、數(shù)據(jù)操縱、數(shù)據(jù)定義和數(shù)據(jù)控制功能于一體，主要特點(diǎn)包括：2、高度非過程化

非關(guān)系數(shù)據(jù)模型的數(shù)據(jù)操縱語言是“面向過程”的，用“過程化”語言完成某項(xiàng)請(qǐng)求，必須指定存儲(chǔ)路徑。SQL進(jìn)行數(shù)據(jù)操作，只要提出“做什么”，而無須指明“怎么做”，因此無需了解存儲(chǔ)路徑。存儲(chǔ)路徑的選擇以及SQL的操作過程由系統(tǒng)自動(dòng)完成。這樣可以減輕用戶的負(fù)擔(dān)，也提高了數(shù)據(jù)獨(dú)立性。3、面向集合的操作方式

非關(guān)系數(shù)據(jù)模型得采用提面向記錄的操作方式，操作對(duì)象是一條記錄。SQL采用集合操作方式，不僅操作對(duì)象、查找結(jié)果可以是元組的集合，而且一次插入、刪除、更新操作的對(duì)象也可以是元組的集合。4、以同一種語法結(jié)構(gòu)提供多種使用方式SQL既是獨(dú)立的語言，又是嵌入式語言。作為獨(dú)立的語言，它能夠獨(dú)立地用于聯(lián)機(jī)交互的使用方式，用戶可以在終端鍵盤上直接鍵入SQL命令對(duì)數(shù)據(jù)庫進(jìn)行操作;作為嵌入式語言，SQL語句能夠嵌入到高級(jí)語言（如C、C++、Java）程序中，供程序員設(shè)計(jì)程序時(shí)使用。而在兩種不同的使用方式下，SQL的語法結(jié)構(gòu)基本上是一致的。4、以同一種語法結(jié)構(gòu)提供多種使用方式5、語言簡(jiǎn)潔，易學(xué)易用SQL功能極強(qiáng)，完成核心功能只用了9個(gè)動(dòng)詞，接近英語口語，所以容易學(xué)習(xí)，易于使用。數(shù)據(jù)查詢

數(shù)據(jù)定義

數(shù)據(jù)操縱

數(shù)據(jù)控制SELECT

CREATEDROPALTER

INSERTUPDATEDELETE

GRANTREVOKE3.2.1字段數(shù)據(jù)類型整數(shù)數(shù)據(jù)類型:bigint,int,smallint,tinyint

精確數(shù)值類型:numeric,decimal近似浮點(diǎn)數(shù)值數(shù)據(jù)類型:float,real日期時(shí)間數(shù)據(jù)類型:datetime,smalldatetime字符串?dāng)?shù)據(jù)類型:char,varchar,text

Unicode字符串?dāng)?shù)據(jù)類型:nchar,nvarchar,ntext二進(jìn)制數(shù)據(jù)類型:binary、varbinary、image貨幣數(shù)據(jù)類型:money,smallmoney

標(biāo)記數(shù)據(jù)類型:timestamp,uniqueidentifier

具體見書P74－表3.13.2SQL數(shù)據(jù)定義1、定義基本表CREATETABLE<表名>(<列名><數(shù)據(jù)類型>[列級(jí)完整性約束條件]

[,<列名><數(shù)據(jù)類型>[列級(jí)完整性約束條件]]…[,<表級(jí)完整性約束條件>])3.2.2創(chuàng)建、修改和刪除數(shù)據(jù)表建表的同時(shí)通常還可以定義與該表有關(guān)的完整性約束條件，這些完整性約束條件被存入系統(tǒng)的數(shù)據(jù)字典中，當(dāng)用戶操作表中數(shù)據(jù)時(shí)由DBMS自動(dòng)檢查該操作是否違背這些完整性約束條件。如果完整性約束條件涉及到該表的多個(gè)屬性列，則必須定義在表級(jí)上，否則既可以定義在列級(jí)也可以定義在表級(jí)。（1）實(shí)體完整性主碼(PrimaryKey)要求：①一個(gè)基本表中只能定義一個(gè)PrimaryKey約束;

②作為PrimaryKey的任何列不能出現(xiàn)空值;

③定義為表級(jí)約束的語法格式：

[constraint約束名]PrimaryKey(列1[ASC|DESC]［，……n])空值(Null/NotNull)要求：該約束只能用于列級(jí)約束。唯一值(Unique)要求：①表示某一列或多個(gè)列的組合上的取值必須唯一，系統(tǒng)會(huì)自動(dòng)

為其建立唯一索引;

②定義為表級(jí)約束的語法格式：

[constraint約束名]Unique[Clustered|NonClustered]

(列1[ASC|DESC]［，……n])例1、建立Student表(Snochar(9)主碼、Snamechar(20)不為空、

Ssexchar(2)、Sagesmallint、Sdeptchar(20))CREATETABLEStudent

(Snochar(9)PRIMARYKEY,

/*在列級(jí)定義主碼*/

Snamechar(20)NOTNULL,

Ssexchar(2),

Sagesmallint,

Sdeptchar(20)

)CREATETABLEStudent

(Snochar(9),

Snamechar(20)NOTNULL,

Ssexchar(2),

Sagesmallint,

Sdeptchar(20),

/*在表級(jí)定義主碼*/

PRIMARYKEY(Sno)

)例2、建立SC表(Snochar(9)不為空、Cnochar(4)不為空、

GradeSmallint)，將Sno,Cno屬性組定義為主碼。CREATETABLESC

(Snochar(9)NOTNULL,

Cnochar(4)NOTNULL,

Gradesmallint,

PRIMARYKEY(Sno,Cno)/*只能在表級(jí)定義主碼*/

)（2）參照完整性通過FOREIGNKEY短語定義哪些列為外碼，用REFERENCES短語指明這些外碼參照哪些表的主碼。表級(jí)約束的語法格式為：

[constraint約束名]ForeignKey(子表列1,……n)

References主表名(主表列1,……n)

[ONDELETE{CASCADE|NOACTION}]

[ONUPDATE{CASCADE|NOACTION}]

其中：CASCADE是級(jí)聯(lián)操作;NOACTION是拒絕操作。例1、建立SC表(Snochar(9)不為空、Cnochar(4)不為空、

GradeSmallint)，(Sno,Cno)是主碼，Sno和Cno分

別參照引用Student表的主碼和Course表的主碼。CREATETABLESC

(Snochar(9)NOTNULL,

Cnochar(4)NOTNULL,

Gradesmallint,

PRIMARYKEY(Sno,Cno)

/*在表級(jí)上定義實(shí)體完整性*/

FOREIGNKEY(Sno)REFERENCESStudent(Sno),

/*在表級(jí)上定義參照完整性*/

FOREIGNKEY(Cno)REFERENCESCourse(Cno)

)例2、顯示說明參照完整性的違約處理示例。CREATETABLESC

(Snochar(9)NOTNULL,

Cnochar(4)NOTNULL,

Gradesmallint,

PRIMARYKEY(Sno,Cno),

ForeignKey(Sno)ReferencesStudent(Sno)

ONDELETECASCADE

ONUPDATECASCADE,

ForeignKey(Cno)ReferencesCourse(Cno)

ONDELETENOACTION

/*當(dāng)刪除Course表中的元組造成了與SC表不一致時(shí)拒絕刪除*/

ONUPDATECASCADE

/*當(dāng)更新Course表中的Cno時(shí)，級(jí)連更新SC表中相應(yīng)的元組*/

)（3）用戶定義完整性Check可用于定義用戶自定義的完整性約束規(guī)則。語法格式為：

[Constraint約束名]CHECK(條件)例1、建立Student表，要求Ssex列只允許取“男”或“女”。CREATETABLEStudent

(Snochar(9)PRIMARYKEY,

/*在列級(jí)定義主碼*/

Snamechar(20)NOTNULL,

Ssexchar(2)CHECK(SsexIN(‘男’,’女’)),

Sagesmallint,

Sdeptchar(20)

)例2、建立SC表，要求Grade的值應(yīng)該在0和100之間。CREATETABLESC

(Snochar(9)NOTNULL,

Cnochar(4)NOTNULL,

GradesmallintCHECK(Grade>=0andGrade<=100),

PRIMARYKEY(Sno,Cno)/*只能在表級(jí)定義主碼*/

)例3、建立Student表，要求當(dāng)學(xué)生的性別是男時(shí)，其名字不能以Ms.打頭。CREATETABLEStudent

(Snochar(9),

Snamechar(20)NOTNULL,

Ssexchar(2),

Sagesmallint,

Sdeptchar(20)，

PRIMARYKEY(Sno),

CHECK(Ssex=‘女’ORSnameNOTLIKE‘Ms.%’)

)（4）完整性約束的命名SQL在CREATETABLE語句中提供了完全性約束命名子句Constraint，用來對(duì)完整性約束條件命名。從而可以靈活地增加、刪除一個(gè)完整性約束條件。例、建立學(xué)生登記表Student，要求學(xué)號(hào)在90000～99999之間，姓名不能取空值，年齡小于30,性別只能是“男”或“女”，主碼為Sno。CREATETABLEStudent

(Snonumeric(6)

CONSTRAINTC1Check(SnoBETWEEN90000AND99999),

Snamechar(20)NOTNULL,

Ssexchar(2)

CONSTRAINTC2Check(SsexIN(‘男’,’女’))

Sagesmallint

CONSTRAINTC3Check(Sage<30),

Sdeptchar(20)，

ConstraintStuentKeyPRIMARYKEY(Sno)

)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ī)完整性示例“學(xué)生-課程”數(shù)據(jù)庫中包括三個(gè)表：C課程號(hào)CNO課程名CN學(xué)分CTC1C語言4C2離散數(shù)學(xué)2C3操作系統(tǒng)3C5數(shù)據(jù)結(jié)構(gòu)4C6數(shù)據(jù)庫4C7匯編語言3C8信息基礎(chǔ)2SC學(xué)號(hào)SNO課程號(hào)SNO成績(jī)SCORES1C190S1C285S2C184S2C294S2C383S3C173S3C768S3C488S3C585S4C265S4C590S4C679S5C289例1、建立一個(gè)“學(xué)生”表S，它由學(xué)號(hào)SNO、姓名SN、性別SEX、年齡AGE、所在系DEPT五個(gè)屬性組成，其中學(xué)號(hào)屬性為主鍵，姓名、年齡與性別不為空，假設(shè)姓名沒有唯一并建立惟一索引，并且性別只能在“男”與“女”中選一個(gè)，年齡不能小于0。返回本節(jié)首頁

CREATETABLES(SNOCHAR(5)PRIMARYKEY,SNVARCHAR(8)NOTNULL,SEXCHAR(2)NOTNULLCHECK(SEXIN('男','女')),AGEINTNOTNULLCHECK(AGE>0),DEPTVARCHAR(20),

CONSTRAINTSN_UUNIQUE(SN)

)[例2]建立“課程”表C，它由課程號(hào)（CNO）、課程名（CN）、學(xué)分（CT）三個(gè)屬性組成。CNO為該表主鍵，學(xué)分大于等于1。

CREATETABLEC(CNOCHAR(5)NOTNULLPRIMARYKEY,CNVARCHAR(20),CTINTCHECK(CT>=1))CREATETABLESC(SNOCHAR(5)NOTNULL

CONSTRAINTS_FFOREIGNKEYREFERENCESS(SNO),CNOCHAR(5)NOTNULL,SCORENUMERIC(3),

CONSTRAINTS_C_PPRIMARYKEY(SNO,CNO),

CONSTRAINTC_FFOREIGNKEY(CNO)REFERENCESC(CNO))[例3]建立“選修”關(guān)系表SC，定義SNO,CNO為SC的外部鍵，(SNO,CNO)為該表的主鍵。練習(xí)：建立如下表

Student(Sno,Sname,Ssex,Sage,Sdept)

要求：Sno為主碼，Sage在15～45間(包括15和45)，

Ssex只能取男或女，默認(rèn)值為男。

Course(Cno,Cname,Cpno,Ccredit)

要求：Cno為主碼。

Sc(Sno,Cno,Grade)

要求：Sno和Cno為主碼，利用Sno建立與表Student

的關(guān)聯(lián)、Cno建立與表Course的關(guān)聯(lián)。

Grade只能接收空值或0～100(包括0和100)的值。CREATETABLEStudent

(SnoCHAR(5)NOTNULLPRIMARYKEY,

SnameVARCHAR(8),

SsexCHAR(2)DEFAULT‘男’CHECK(SsexIN(‘男’,‘女’)),

SageSMALLINTCHECK(Sage>=15ANDSage<=45),

SdeptCHAR(2)

)CREATETABLECourse

(CnoCHAR(2)NOTNULLPRIMARYKEY,

CnameVARCHAR(20),

CpnoCHAR(2),

CcreditSMALLINT)CREATETABLESc

(SnoCHAR(5)NOTNULL

FOREIGNKEYREFERENCESStudent(Sno),

CnoCHAR(2)NOTNULL,

GradeSMALLINTCHECK((GradeISNULL)

OR(GradeBETWEEN0AND100)),

PRIMARYKEY(Sno,Cno),

FOREIGNKEY(Cno)REFERENCESCourse(Cno))ALTERTABLE表名

ALTERCOLUMN列名新類型[(長(zhǎng)度[,小數(shù)位])][NULL|NOTNULL]]

ADD

新列名AS表達(dá)式或別名[,...n][WITHCHECK|WITHNOCHECK]ADD完整性約束

DROP[CONSTRAINT]約束名|COLUMN列名}[,...n][CHECK|NOCHECK]CONSTRAINT{約束名[,...n]}2、修改基本表其中:<表名>指定需要修改的基本表，ADD子句用于增加新列和新的完整性約束條件，DROP子句用于刪除指定的完整性約束條件或原有列，ALTER子句用于修改原有的列定義。{CHECK|NOCHECK}CONSTRAINT

指定啟用或禁用constraint_name。如果禁用，將來插入或更新該列時(shí)將不用該約束條件進(jìn)行驗(yàn)證。此選項(xiàng)只能與FOREIGNKEY和CHECK約束一起使用。[例4]

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

ALTERTABLESADDSCOMEDATETIME[例5]將年齡的數(shù)據(jù)類型改為半字長(zhǎng)整數(shù)。

ALTERTABLESALTER

COLUMNAGESMALLINT[例6]

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

ALTERTABLESDROP

COLUMNSCOME[例7]

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

ALTERTABLESNOCHECKCONSTRAINTC_F對(duì)學(xué)生表進(jìn)行如下操作：將姓名列的長(zhǎng)度先改為6，不允許有空值。增加兩列，性別(char(2))電話(varchar(11))；再將新建的兩列刪除。練習(xí)：altertable

學(xué)生

altercolumn姓名char(6)notnull

goaltertable

學(xué)生

add性別char(2),電話varchar(11)

goaltertable

學(xué)生

dropcolumn性別,電話

go例:1)為xs表添加主鍵約束,該主鍵約束由學(xué)號(hào)單列組成,約束

名為pk_xh.

2)修改xs表的主鍵約束,該主鍵約束由學(xué)號(hào)和姓名兩列

組成,約束名為pk_xh_xm.1))ALTERTABLE

xs

ADDCONSTRAINT

pk_xh

PRIMARYKEY(學(xué)號(hào))

2)ALTERTABLE

xs

DROPCONSTRAINT

pk_xh

GO

ALTERTABLE

xs

ADDCONSTRAINT

pk_xh_xm

PRIMARYKEY(學(xué)號(hào),姓名)例:為選課表添加外鍵約束,約束名為FK_kcbh,使之與課程表建

立級(jí)聯(lián)刪除和級(jí)聯(lián)更新操作.ALTERTABLE

選課表ADDCONSTRAINT

FK_kcbh

FOREIGNKEY(課程編號(hào))REFERENCES

課程表(課程編號(hào))

ONDELETECASCADE

ONUPDATECASCADEUPDATE

課程表SET

課程編號(hào)='1111'WHERE

課程編號(hào)='0001'DELETE

課程表WHERE

課程編號(hào)='1111'ALTERTABLE

選課表DROPCONSTRAINTFK_kcbh例:為選課表添加外鍵約束,約束名為FK_xh,使之與學(xué)生表建

立級(jí)聯(lián)刪除和級(jí)聯(lián)更新操作.ALTERTABLE

選課表ADDCONSTRAINT

FK_xh

FOREIGNKEY(學(xué)號(hào))REFERENCES

學(xué)生表(學(xué)號(hào))

ONDELETECASCADE

ONUPDATECASCADEALTERTABLE

選課表DROPCONSTRAINTFK_xh例：1）建立XS表，包括學(xué)號(hào)char(2)姓名char(6)，同時(shí)以學(xué)

號(hào)建立唯一性約束，約束名為UK_xh.

2）為XS表以姓名建立唯一性約束，約束名為UK_xm。

3）刪除XS表中的UK_xh的唯一性約束。CREATETABLEXS(學(xué)號(hào)char(2)CONSTRAINT

UK_xh

UNIQUE,

姓名char(6))ALTERTABLEXSADD

CONSTRAINT

UK_xm

UNIQUE(姓名)ALTERTABLEXS

DROPCONSTRAINT

UK_xhCREATETABLEscore(學(xué)號(hào)char(4),

課號(hào)char(1),

分?jǐn)?shù)decimal(5,2)CONSTRAINT

CK_cj

CHECK(分?jǐn)?shù)>=0and分?jǐn)?shù)<=100))INSERTscoreVALUES('0001','1',120)ALTERTABLEscoreDROPCONSTRAINT

CK_cjALTERTABLEscoreADDCONSTRAINT

CK_fs

CHECK(分?jǐn)?shù)>=0and分?jǐn)?shù)<=150)例：建立score表，包括學(xué)號(hào)char(4)課號(hào)char(1)分?jǐn)?shù)decimal(5,2)，為分?jǐn)?shù)建立檢查約束，約束名為CK_cj，要求分?jǐn)?shù)只能在0~100間8.4.5默認(rèn)約束例

員工表employee的sex列添加默認(rèn)約束，默認(rèn)值是“男”。ALTERTABLEemployeeADDCONSTRAINT

sex_default

DEFAULT‘男’

FORsex

更改表employee為hire_date列定義默認(rèn)約束。ALTERTABLEemployeeADDCONSTRAINThire_date_df

DEFAULTgetdate()FORhire_dateDROPTABLE<表名>

[例8]

刪除S表。

DROPTABLES3、刪除基本表建立索引是加快查詢速度的有效手段?？稍诨颈砩辖⒁粋€(gè)或多個(gè)索引，以提供多種存取路徑，加快查詢速度。系統(tǒng)在存取數(shù)據(jù)時(shí)會(huì)自動(dòng)選擇合適的索引作為存取路徑，用戶不必也不能顯式地選擇索引。按照索引記錄的存放位置不同，可分為聚集索引(ClusteredIndex)與非聚集索引(Non-ClusteredIndex)兩類。聚集索引是指索引項(xiàng)的順序與表中記錄的物理順序一致的索引組織；非聚集索引按照索引的字段排列記錄，但是排列的結(jié)果并不會(huì)存儲(chǔ)在表中，而是另外存儲(chǔ)。在檢索記錄時(shí)，聚集索引會(huì)比非聚集索引速度快，一個(gè)表中只能有一個(gè)聚集索引，而非聚集索引可以有多個(gè)。3.2.2設(shè)計(jì)、創(chuàng)建和維護(hù)索引1、索引的概念

CREATE

[UNIQUE][CLUSTERED|NONCLUSTERED]

INDEX<索引名>

ON<表名>|<視圖名>(<列名>[ASC|DESC][,...n])2、創(chuàng)建索引[例9]為學(xué)生-課程數(shù)據(jù)庫中的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_CNO

ONSC(SNOASC,CNODESC)1、按employee表的employee_name列建立非聚集索引。

CREATENONCLUSTEREDINDEXname_idx

ONemployee(employee_name)2、在學(xué)生表上創(chuàng)建一個(gè)名為學(xué)號(hào)_index的唯一性聚簇索

引，索引關(guān)鍵字為學(xué)號(hào)，降序。CREATEUNIQUECLUSTEREDINDEX

學(xué)號(hào)_indexON學(xué)生表(學(xué)號(hào)

DESC)

3、在選課表上創(chuàng)建一個(gè)名為學(xué)號(hào)_課號(hào)_index的非聚簇復(fù)合索引，索引關(guān)鍵字為學(xué)號(hào),課號(hào)，升序CREATENONCLUSTERED

INDEX

學(xué)號(hào)_課號(hào)_indexON

學(xué)生表(學(xué)號(hào)ASC,課號(hào)ASC)刪除索引的命令語法:DROPINDEX<索引名>[例10]

刪除S表的S_SNO索引。DROPINDEXS_SNO3、刪除索引SELECT[ALL|DISTINCT]目標(biāo)列表達(dá)式[,目標(biāo)列表達(dá)式]...[INTO新表名]FROM表名或視圖名[,表名或視圖名]...[WHERE條件表達(dá)式][GROUPBY列名1[HAVING條件表達(dá)式]][ORDERBY列名2[ASC|DESC]]...3.3SQL數(shù)據(jù)查詢3.3.1SELECT命令的格式及其含義1、查詢指定列

[例11]

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

SELECTSNO,SNFROMS

[例12]

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

SELECTSN,SNO,DEPTFROMS

3.3.2單表查詢?cè)赟elect子句中給出包含所選字段的一個(gè)列表，各個(gè)字段之間用逗號(hào)分隔，字段的次序可以任意指定。如在From子句中指定了兩個(gè)表，而這兩個(gè)表中又有同名的字段，使用這些字段時(shí)就應(yīng)在其字段名前冠以表名。

USEJWGLSelect

student.student_id,student_name,monitor

Fromstudent,class

Whereclass.class_id=student.class_id

[例13]

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

SELECT*FROMS等價(jià)于：

SELECTSNO,SN,SEX,AGE,DEPTFROMS2、查詢?nèi)苛?/p>

SELECT子句的<目標(biāo)列表達(dá)式>不僅可以是表中的屬性列，也可以是有關(guān)表達(dá)式。[例14]

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

SELECTSN,2005-AGEFROMS輸出的結(jié)果為：

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

李濤1986

王林1987

陳高1984

張杰1988

吳小麗1986

徐敏敏19853、查詢經(jīng)過計(jì)算的列

[例15]

查全體學(xué)生的姓名、出生年份和所在系，要求用小寫字母表示所有系名。

SELECTSN,‘出生年份:’,2005-AGE,lower(DEPT)FROMS輸出的結(jié)果為：

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

李濤出生年份:1986信息

王林出生年份:1987計(jì)算機(jī)

陳高出生年份:1984自動(dòng)化

張杰出生年份:1988自動(dòng)化

吳小麗出生年份:1986信息

徐敏敏出生年份:1985計(jì)算機(jī)設(shè)置字段別名：顯示選擇查詢的結(jié)果時(shí)，第一行（即表頭）中顯示的是各個(gè)輸出字段的名稱。為了便于閱讀，也可指定更容易理解的字段名來取代原來的字段名。設(shè)置別名的方法：原字段名字段別名原字段名AS字段別名字段別名=原字段名用戶可以通過別名來改變查詢結(jié)構(gòu)的列標(biāo)題，這對(duì)于含自述表達(dá)式、函數(shù)名的目標(biāo)列表達(dá)式尤為重要。上例可以定義如下：SELECTSN,‘出生年份:’BIRTH,BIRTHDAY=2005-AGE,

lower(DEPT)ASDEPARTMENTFROMS輸出的結(jié)果為：

SNBIRTHBIRTHDAYDEPARTMENT----------------------------------------------------------

李濤出生年份:1986信息王林出生年份:1987計(jì)算機(jī)陳高出生年份:1984自動(dòng)化張杰出生年份:1988自動(dòng)化吳小麗出生年份:1986信息徐敏敏出生年份:1985計(jì)算機(jī)3.3.3WHERE子句的基本使用

1.消除取值重復(fù)的行2.指定WHERE查詢條件返回本節(jié)首頁消除取值重復(fù)的行[例16]

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

SNO----S1 S1S2S2S2S3S3S3S3S4S4S4S5該查詢結(jié)果里包含了許多重復(fù)的行。如果想去掉結(jié)果表中的重復(fù)行，必須指定DISTINCT短語：SELECT

DISTINCTSNOFROMSC執(zhí)行結(jié)果為：

SNO----S1S2S3S4S5例

顯示employee表中全部員工的姓名和年齡，

去掉重名。

(注：表中只有出生日期字段birth_date)

SELECT

DISTINCT

employee_name

AS

姓名，

YEAR(GETDATE())-YEAR(birth_date)

AS

年齡FROMemployee常用的查詢條件查詢條件謂詞比較運(yùn)算符=,>,<.>=,<=,!=,<>,!>,!<；Not(上述比較運(yùn)算符構(gòu)成的比較關(guān)系表達(dá)式)確定范圍BETWEENAND，NOTBETWEENAND確定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重條件AND，OR，NOT查詢滿足條件的元組(1)比較運(yùn)算符[例17]

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

SELECTSN

FROMS

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

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

SELECTSN,AGE

FROMS

WHEREAGE<20或

SELECTSN,AGE

FROMS

WHERENOTAGE>=20例

對(duì)employee表，求出男員工的平均工資。SELECT

AVG(wages)AS平均工資FROMemployee

WHEREsex='男‘例、在student中檢索男生且年齡大于21歲的學(xué)生記錄。(只有birth字段)例、在“student”中檢索姓張或姓李的學(xué)生。Select*FROMstudentWheresex=‘男’ANDDatediff(yy,birth,Getdate())

Select*FROMstudent

Where

Substring(姓名,1,1)=‘張’ORSubstring(姓名,1,1)=‘李’(2)確定范圍[例20]

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

SELECTSN,DEPT,AGE

FROMS

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

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

SELECTSN,DEPT,AGE

FROMS

WHEREAGENOTBETWEEN20AND23返回本節(jié)首頁例對(duì)employee表，列出月工資在2000到3000之間的員

工名單。

SELECT

*

FROMemployee

WHEREwagesBETWEEN2000AND3000

語句中的WHERE子句還有等價(jià)的形式：

WHEREwages>=2000ANDwages<=3000(3)確定集合[例22]

查詢信息系、自動(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é)首頁例：在“student”中檢索姓李、姓張、姓林、姓許的學(xué)生記錄。Select*FROMstudent

Where

Substring(姓名,1,1)IN(‘李’,‘張’,‘王’,‘劉’)

等價(jià)于：

Select*FROMstudent

WhereSubstring(姓名,1,1)＝‘李’ORSubstring(姓名,1,1)＝‘張’ORSubstring(姓名,1,1)＝‘王’ORSubstring(姓名,1,1)＝‘劉’

(4)字符匹配通配符描述%（百分號(hào)）代表零個(gè)或更多字符的任意字符串。_（下劃線）代表任何單個(gè)字符（長(zhǎng)度可以為0）。[]（中擴(kuò)號(hào)）指定范圍([a-f])或集合([abcdef])中的任何單個(gè)字符。[^]不屬于指定范圍([a-f])或集合([abcdef])的任何單個(gè)字符。字符匹配示例1[例24]

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

SELECTSN,SNO,SEX

FROMS

WHERESNLIKE'劉%‘[例25]

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

SELECTSN

FROMS

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

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

SELECTSN,SNO

FROMS

WHERESNLIKE'_陽%'[例27]

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

SELECTSN,SNO,SEX

FROMS

WHERESNNOTLIKE'劉%'返回本節(jié)首頁字符匹配示例3[例28]

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

SELECTCNO,CT

FROMC

WHERECNLIKE'DB\_Design'ESCAPE'\'ESCAPE'\'短語表示\為換碼字符，這樣匹配串中緊跟在\后面的字符'_'不再具有通配符的含義，而是取其本身含義，被轉(zhuǎn)義為普通的'_'字符。返回本節(jié)首頁例

對(duì)employee表，列出所有的姓“張”的員工名單。

SELECT

*

FROMemployee

WHERE

employee_nameLIKE‘張%'

語句中的WHERE子句還有等價(jià)的形式：

WHERE

LEFT(employee_name,1)=‘張’例、從“STUDENT”中查找姓王、姓李和姓林的學(xué)生。

Select*FROMSTUDENT

Where

姓名LIKE‘[王李林]%'例、從“STUDENT”中檢索學(xué)號(hào)末位數(shù)字不在2~6范圍內(nèi)的

學(xué)生。

Select*FROMSTUDENT

Where

student_idLIKE'%[^2-6]'(5)涉及空值的查詢[例29]某些學(xué)生選修某門課程后沒有參加考試，所以有選課記錄，但沒有考試成績(jī)，下面我們來查一下缺少成績(jī)的學(xué)生的學(xué)號(hào)和相應(yīng)的課程號(hào)。

SELECTSNO,CNO

FROMSC

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

SELECTSNO,CNO

FROMSCWHERESCOREISNOTNULL返回本節(jié)首頁例

對(duì)employee表，列出所有工資為空值的員工編號(hào)和姓名。

SELECTemployee_id,employee_name

FROMemployee

WHEREwagesISNULL例：刪除STUDENT表中姓名值為空的記錄。

DELETEstudentWHERE

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

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

SELECTSN

FROMS

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

SELECTSN,SEX

FROMS

WHEREDEPT='計(jì)算機(jī)'ORDEPT='信息'OR

DEPT='自動(dòng)化'或

SELECTSN,SEX

FROMS

WHERE

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

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

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

SELECTCOUNT(DISTINCTSNO) FROMSC

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

SELECT COUNT(*),MAX(SCORE),MIN(SCORE),AVG(SCORE) FROMSC WHERECNO='C1'返回本節(jié)首頁3.3.5分組查詢

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

SELECTCNO,COUNT(SNO)

FROMSC

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

CNO---------C13C24C31C41C52C61C71返回本節(jié)首頁例對(duì)employee表，分別統(tǒng)計(jì)男女員工人數(shù)。

SELECTsex,COUNT(sex)AS人數(shù)

FROM

employeeGROUPBYsex例對(duì)employee表，分別統(tǒng)計(jì)各部門男女員工的人數(shù)。

SELECTdepartment_id,sex,COUNT(*)AS

人數(shù)

FROMemployee

GROUPBY

department_id,sex例

用GROUPBY句匯總出student_course表中學(xué)生的學(xué)號(hào)及

總成績(jī)。USE

jwglGOSELECT'學(xué)號(hào)‘=student_id,'總成績(jī)‘=sum(grade)

FROM

student_course

GROUPBY

student_id[例37]查詢有3人以上學(xué)生（包括3人）選修的課程的課程號(hào)及選修人數(shù)。

SELECTCNO,COUNT(SNO)

FROMSC

GROUPBYCNOHAVING

COUNT(*)>=3結(jié)果為：

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

對(duì)employee表，列出部門平均工資大于2000的部門編號(hào)。

SELECTdepartment_id,AVG(wages)AS平均工資

FROMemployee

GROUPBYdepartment_id

HAVINGAVG(wages)>=2000例

用GROUPBY句匯總出student_course表中總分大于450分

的學(xué)生的學(xué)號(hào)及總成績(jī)。USE

jwglGOSELECT'學(xué)號(hào)‘=student_id,'總成績(jī)‘=sum(grade)

FROM

student_course

GROUPBY

student_id

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

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

SELECTSNO,SCORE

FROMSC

WHERECNO='C3'

ORDERBYSCOREDESC

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

查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系升序排列，對(duì)同一系中的學(xué)生按年齡降序排列。SELECT*FROMSORDERBY

DEPT,AGEDESC返回本節(jié)首頁3.3.7連接查詢1、等值與非等值連接查詢當(dāng)連接運(yùn)算符為=時(shí)，稱為等值連接。使用其它運(yùn)算符稱為非等值連接。2、自身連接連接操作不僅可以在兩個(gè)表之間進(jìn)行，也可以是一個(gè)表與其自己進(jìn)行連接，這種連接稱為表的自身連接。 3、外連接

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

[例40]

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

SELECT*

FROMS,SC

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

[例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表中是唯一的，因此引用時(shí)可以去掉表名前綴。而SNO在兩個(gè)表都出現(xiàn)了，因此引用時(shí)必須加上表名前綴。該查詢的執(zhí)行結(jié)果不再出現(xiàn)SC.SNO列。返回本節(jié)首頁【例】從student及student_course兩個(gè)表中檢索學(xué)生的學(xué)號(hào)、姓名、學(xué)習(xí)課程號(hào)及課程成績(jī)。USE

jwglGOselect

student.student_id,student.student_name,

student_course.course_id,

student_course.grade

from

student,student_courseWHERE

student.student_id=student_course.student_id

【例】從student、course及student_course三個(gè)表中檢索學(xué)生的學(xué)號(hào)、姓名、學(xué)習(xí)課程號(hào)、學(xué)習(xí)課程名及課程成績(jī)。USE

jwglGOSELECT

student.student_id,student.student_name,student_course.course_id,

course.course_name,student_course.grade

fromstudent,student_course,courseWHERE

student.student_id=student_course.student_idAND

course.course_id=student_course.course_id

例

Sales數(shù)據(jù)庫中部門表department和員工表employee的自然連接。SELECTa.department_name,b.*FROMdepartmentaINNERJOINemployeeb

ON

b.department_id=a.department_id例

對(duì)Sales數(shù)據(jù)庫，列出銷售“G00001”產(chǎn)品的員工中，銷售數(shù)量大于編號(hào)為“E001”的員工銷售該類產(chǎn)品銷售數(shù)量的那些員工的編號(hào)和銷售數(shù)量。SELECT

a.employee_id,a.order_numFROMsell_ordera

INNERJOIN

sell_orderb

ON

a.order_num>b.order_num

AND

a.goods_id=b.goods_id

WHERE

b.goods_id='G00001'ANDb.employee_id='E001'自身連接示例[例42]

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

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

FROM

TASX,TASY

WHEREX.SAL>Y.SALANDY.TN='張梅'結(jié)果為：

TNSAL_B張梅的工資

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

劉義