數(shù)據(jù)庫原理與應(yīng)用第5章

SQLServer20005.1SQL概述5.2數(shù)據(jù)定義5.3數(shù)據(jù)查詢5.4數(shù)據(jù)更新5.5視圖5.6數(shù)據(jù)控制機(jī)制和語句第五章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言-SQLSQLServer2000一、SQL的發(fā)展

SQL（StructuredQueryLanguage，結(jié)構(gòu)化查詢語言）是關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言

1974年由Boyce和Chamberlin提出

1975年至1979年IBM公司的關(guān)系數(shù)據(jù)庫管理系統(tǒng)System-R實(shí)現(xiàn)了這種語言

1986年美國國家標(biāo)準(zhǔn)局（ANSI）頒布了SQL的美國標(biāo)準(zhǔn)，1987年國際標(biāo)準(zhǔn)化組織（ISO）通過了這一標(biāo)準(zhǔn)，現(xiàn)這兩個標(biāo)準(zhǔn)稱為SQL-861989年ANSI公布SQL-89標(biāo)準(zhǔn)

1992年ANSI公布SQL2標(biāo)準(zhǔn)（即SQL-92標(biāo)準(zhǔn)）

1999年ANSI公布SQL3標(biāo)準(zhǔn)5.1SQL概述SQLServer2000二、SQL優(yōu)點(diǎn)

(1)集多種數(shù)據(jù)語言功能于一體，是一種綜合統(tǒng)一的語言(2)高度非過程化

(3)面向集合的操作方式

(4)以同一種語法結(jié)構(gòu)提供兩種使用方式

(5)語言簡潔、易學(xué)易用

(6)支持三級模式結(jié)構(gòu)5.1SQL概述SQLServer2000注意：

(1)SQL語言是一種關(guān)系數(shù)據(jù)庫語言，

提供數(shù)據(jù)的定義、查詢、更新和控制等功能。

(2)SQL語言不是一個應(yīng)用程序開發(fā)語言，只提供對數(shù)據(jù)庫的操作能力，

不能完成屏幕控制、菜單管理、報表生成等功能，可成為應(yīng)用開發(fā)語言的一部分；

(3)SQL語言不是一個DBMS，它屬于DBMS語言處理程序。5.1SQL概述SQLServer2000

1、數(shù)據(jù)定義語言（ＤＤＬ）

創(chuàng)建、修改或刪除數(shù)據(jù)庫中各種對象，包括表、視圖、索引等。

命令：create、alter、drop

2、查詢語言（ＱＬ）

按照指定的組合、條件表達(dá)式或排序檢索已存在的數(shù)據(jù)庫中數(shù)據(jù)，不改變數(shù)據(jù)庫中數(shù)據(jù)。

命令：select

3、數(shù)據(jù)更新語言

對已經(jīng)存在的數(shù)據(jù)庫進(jìn)行元組的插入、刪除、修改等操作

命令：insert、update、delete

4、數(shù)據(jù)控制語言（ＤＣＬ）

用來授予或收回訪問數(shù)據(jù)庫的某種特權(quán)

命令：grant、revoke三、SQL語句分類5.1SQL概述SQLServer2000操作對象創(chuàng)建語句刪除語句修改語句基本表CREATETABLEDROPTABLEALTERTABLE索引CREATEINDEXDROPINDEX視圖CREATEVIEWDROPVIEW數(shù)據(jù)庫CREATEDATABASEDROPDATABASEALTERDATABASE5.2數(shù)據(jù)定義SQLServer2000一、基本表的定義和維護(hù)1.定義基本表語句基本格式：createtable[<庫名>.]<表名>

(<列名><數(shù)據(jù)類型>[<列級完整性約束條件>][,<列名><數(shù)據(jù)類型>[<列級完整性約束條件>][，…n][,<表級完整性約束條件>][，…n])；

5.2數(shù)據(jù)定義類型表示類型說明數(shù)值型數(shù)據(jù)SMALLINT半字長二進(jìn)制整數(shù)。15bits數(shù)據(jù)INTEGER或INT全字長（四字長）整數(shù)。31bits數(shù)據(jù)DECIMAL(p[，q])十進(jìn)制數(shù)，共p位，其中小數(shù)點(diǎn)后q位。0≤q≤p，q=0時可省略不寫FLOAT雙字長浮點(diǎn)數(shù)字符型數(shù)據(jù)CHARTER(n)或CHAR(n)長度為n的定長字符串VARCHAR(n)最大長度為n的變長字符串特殊數(shù)據(jù)類型GRAPHIC(n)長度為n的定長圖形字符串VARGRAPHIC(n)最大長度為n的變長圖形字符串日期時間型DATE日期型，格式為YYYY-MM-DDTIME時間型，格式為HH.MM.SSTIMESTAMP日期加時間

SQL支持的數(shù)據(jù)類型5.2數(shù)據(jù)定義SQLServer2000

列級完整性約束條件——針對屬性值的設(shè)置的限制條件，只涉及到一個列的數(shù)據(jù)，有以下5種：notnull約束不允許為空。unique約束不允許該列出現(xiàn)重復(fù)的屬性值。default約束定義該列的缺省值。default約束的格式為：default<約束名><默認(rèn)值>for<列名>

如：defaults1男for性別primarykey約束定義該列為主碼check約束定義屬性值的檢查條件。不能直接跟在列后定義，而通過約束條件表達(dá)式來設(shè)置，check約束的格式為：

constraint<約束名>check(<約束條件>)如：constraintn1check(年齡between19and30)5.2數(shù)據(jù)定義SQLServer2000表級完整性約束條件——涉及到一個關(guān)系中多個列的限制條件，有以下3種：unique約束當(dāng)要求列組的值不能有重復(fù)值時，就需要使用表級唯一性約束。unique約束約束的格式為：

constraint<約束名>unique(屬性組)如：constraintu1unique（學(xué)號，姓名）primarykey約束

如果一個表的主鍵內(nèi)有兩個或兩個以上的列，則必須使用表約束將這兩列加入主鍵內(nèi)。不能直接跟在列后定義，而通過約束條件表達(dá)式來設(shè)置。primarykey約束的格式為：

constraint<約束名>primarykey[clustered](<屬性組>)foreignkey

約束

用于定義外碼和被參照表。外鍵的數(shù)據(jù)類型必須和被參照表中的主碼嚴(yán)格匹配。foreignkey約束的格式為：

constraint<約束名>foreignkey(<外碼>)references<被參照表名>(<與外碼對應(yīng)的主碼名>)5.2數(shù)據(jù)定義SQLServer2000例1：用SQL在學(xué)生數(shù)據(jù)庫中建立如下基本表：學(xué)生（學(xué)號，姓名，年齡，性別，所在系）要求：學(xué)生表中以學(xué)號為主碼，姓名不能為空，性別只能輸“男”或“女”，年齡的缺省值為205.2數(shù)據(jù)定義createtable學(xué)生(學(xué)號char(5)primarykey,

姓名char(8)notnull,

年齡smallintdefault20,

性別char(2),

所在系char(20),constraintC1check(性別in(‘男’,‘女’)));SQLServer2000例2：用SQL在學(xué)生數(shù)據(jù)庫中建立如下基本表：課程（課程號，課程名，先行課）要求：課程表中以課程號為主碼createtable課程(課程號char(5)primarykey,

課程名char(20),

先行課char(5));5.2數(shù)據(jù)定義SQLServer2000例3：用SQL在學(xué)生數(shù)據(jù)庫中建立如下基本表：選課（學(xué)號，課程號，成績）要求：選課表中以學(xué)號和課程號為主碼，成績限定在0—100，并且還要求學(xué)號與學(xué)生表中的學(xué)號建立參照關(guān)系，課程號與課程表中的課程號建立參照關(guān)系。createtable選課(學(xué)號char(5),

課程號char(5),

成績int,constraintC2check(成績between0and100),constraintC3primarykey(學(xué)號,課程號),constraintC4foreignkey(學(xué)號)references學(xué)生(學(xué)號),constraintC5foreignkey(課程號)references課程(課程號));5.2數(shù)據(jù)定義SQLServer2000二、修改基本表

1、增加屬性語句基本格式：

altertable<表名>add(<新列名><數(shù)據(jù)類型>[<列級完整性約束條件>][，…n])例4：向?qū)W生表中增加“家庭地址”和“電話”altertable學(xué)生add(家庭地址char(30),電話char(12))5.2數(shù)據(jù)定義SQLServer20002、刪除屬性語句基本格式：altertable<表名>dropcolumn<列名>例5：在學(xué)生表中刪除“家庭地址”和“電話”altertable學(xué)生dropcolumn家庭地址,電話5.2數(shù)據(jù)定義SQLServer2000例6不能執(zhí)行，因?yàn)樵凇靶詣e”屬性上已定義了check約束條件例7可以執(zhí)行，因?yàn)樵凇靶彰睂傩陨现欢x了notnull約束例6：altertable學(xué)生dropcolumn性別例7：altertable學(xué)生dropcolumn姓名5.2數(shù)據(jù)定義注：不允許刪除已定義列級完整性約束或表級完整性約束的屬性，NOTNULL約束除外，要刪除這些屬性必須先刪除該屬性上的約束條件SQLServer20003、修改屬性語句基本格式：

altertable<表名>modify(<列名><數(shù)據(jù)類型>[，…，n])注：只能改變寬度，增加notnull約束對于已有數(shù)據(jù)的表，只能將屬性的寬度改為已有數(shù)據(jù)的寬度

例8：改變學(xué)生表中“所在系”的寬度altertable學(xué)生modify(所在系char(30))5.2數(shù)據(jù)定義SQLServer20004、刪除完整性約束條件語句基本格式：

altertable<表名>drop<約束名>

例9：向?qū)W生表中刪除“性別”屬性上的約束C1，然后刪除“性別”屬性altertable學(xué)生dropC1altertable學(xué)生dropcolumn性別5.2數(shù)據(jù)定義5.2數(shù)據(jù)定義5、增加完整性約束條件語句基本格式：

ALTERTABLE<表名>ADDCONSTRAINT<新完整性約束名><完整性約束條件>例10：向課程表中增加完整性約束條件：每門課的學(xué)時小于8學(xué)時ALTERTABLE課程ADDCONSTRAINTX1CHECK（學(xué)時<8）

SQLServer2000三、刪除基本表語句基本格式：

droptable

<表名>注：基本表一旦被刪除，表中的數(shù)據(jù)及在此表基礎(chǔ)上建立的索引，視圖將自動地全部被刪除，所以要特別小心

例11：droptable學(xué)生

不能執(zhí)行，因?yàn)閷W(xué)生表已被選課表定義為它的參照表不能刪除已被定義為其它表的參照表的表5.2數(shù)據(jù)定義SQLServer2000四、索引

1、索引的作用5.2數(shù)據(jù)定義通過創(chuàng)建唯一索引，可以保證數(shù)據(jù)記錄的唯一性?？梢源蟠蠹涌鞌?shù)據(jù)檢索速度。可以加速表與表之間的連接，這一點(diǎn)在實(shí)現(xiàn)數(shù)據(jù)的參照完整性方面有特別的意義。在使用ORDERBY和GROUPBY子句中進(jìn)行檢索數(shù)據(jù)時，可以顯著減少查詢中分組和排序的時間。使用索引可以在檢索數(shù)據(jù)的過程中使用優(yōu)化隱藏器，提高系統(tǒng)性能。SQLServer20002.建立索引的原則

1)索引的建立和維護(hù)由DBA和DBMS完成。

2)大表應(yīng)當(dāng)建索引，小表則不必建索引。

3)對于一個基本表，不要建立過多的索引。

4)根據(jù)查詢要求建索引。5.2數(shù)據(jù)定義SQLServer20003、建立索引語句基本格式：

create[unique][cluster]index<索引名>

on<表名>(<列名１>[asc|desc][，…，n]）

unique：用于指定為表創(chuàng)建唯一索引，即不允許存在索引值相同的兩行。

cluster：用于指定創(chuàng)建的索引為聚簇索引。

asc升序，desc降序，默認(rèn)為asc例12：為學(xué)生表建立按學(xué)號升序索引createindex學(xué)生_學(xué)號on學(xué)生(學(xué)號)例13：為選課表按學(xué)號升序和課程號降序建唯一索引createuniqueindex選課_學(xué)號on選課(學(xué)號asc,課程號desc)5.2數(shù)據(jù)定義SQLServer20004、刪除索引語句基本格式：

dropindex<索引名>

例14：dropindex學(xué)生_學(xué)號5.2數(shù)據(jù)定義SQLServer2000語句格式：

select[ALL|DISTINCT]<目標(biāo)列組>

from<數(shù)據(jù)源>[where<元組選擇條件>][groupby<分組列名>[having<組選擇條件>]][orderby<排序列1>asc|desc[，…]]5.3數(shù)據(jù)查詢SQLServer2000select子句：用于指明查詢結(jié)果集的目標(biāo)列。目標(biāo)列可以是直接從數(shù)據(jù)源中投影得到的屬性以及與屬性相關(guān)的表達(dá)式或數(shù)據(jù)統(tǒng)計的函數(shù)表達(dá)式，目標(biāo)列甚至還可以是常數(shù)。from子句：用于指明查詢的數(shù)據(jù)源，數(shù)據(jù)源可以是基本表或視圖。where子句：描述選擇條件。groupby子句：將查詢結(jié)果的各行按一列取值相等的原則進(jìn)行分組，如果有having短語，則查詢結(jié)果只是滿足指定條件的組orderby子句：查詢結(jié)果按一定順序排序。5.3數(shù)據(jù)查詢SQLServer2000SQL的查詢實(shí)例：1、單表查詢（簡單查詢）（1）選擇表中的若干列①選擇表中的指定列語法格式：select<目標(biāo)列表達(dá)式>[，….n]

from<數(shù)據(jù)源>

其中列的先后順序可與表中不一致，可以根據(jù)需要改變列的顯示順序。5.3數(shù)據(jù)查詢例1查詢?nèi)w學(xué)生的學(xué)號與姓名SELECT學(xué)號，姓名FROM學(xué)生例2查詢?nèi)w學(xué)生的姓名，學(xué)號，所在系SELECT姓名，學(xué)號，所在系FROM學(xué)生5.3數(shù)據(jù)查詢5.3數(shù)據(jù)查詢②查詢?nèi)苛杏袃煞N方式：在SELECT后列出所有列名，可以改變列的排列順序?qū)⒛繕?biāo)列表達(dá)式指定為*例3查詢?nèi)w學(xué)生的詳細(xì)記錄SELECT*FROM學(xué)生5.3數(shù)據(jù)查詢

③查詢經(jīng)過計算的值<目標(biāo)列表達(dá)式>是與屬性列有關(guān)的表達(dá)式例4查詢?nèi)w學(xué)生的姓名及其出生年份SELECT姓名，2011-年齡FROM學(xué)生④<目標(biāo)列表達(dá)式>為常量或函數(shù)例5查詢?nèi)w學(xué)生的姓名、出生年份和所在系，出生年份前增加屬性列“YearofBirth”，并用小寫字母表示所在系名。SELECT姓名，‘YearofBirth’，2011-年齡，ISLOWER（所在系）FROM學(xué)生ASNAMEBIRTH出生年份5.3數(shù)據(jù)查詢(2)選擇表中的若干元組①消除取值重復(fù)的行例6查詢選修了課程的學(xué)生學(xué)號SELECT學(xué)號FROM選修D(zhuǎn)ISTINCT5.3數(shù)據(jù)查詢②查詢滿足條件的元組ⅰ)比較大小用于比較大小的運(yùn)算符一般包括：=,>,<,>=,<=,!=或<>邏輯運(yùn)算符NOT可以與比較運(yùn)算符同用，表示對條件求非。例7查詢CS系全體學(xué)生的名單SELECT姓名FROM學(xué)生WHERE所在系=‘CS’;5.3數(shù)據(jù)查詢例8查詢所有年齡在20歲以下的學(xué)生姓名及其年齡SELECT姓名，年齡FROM學(xué)生WHERE年齡<20（或WHERENOT年齡>=20）;例9查詢考試成績有不及格的學(xué)生的學(xué)號SELECTDISTINCT學(xué)號FROM選修WHERE成績<60；5.3數(shù)據(jù)查詢ⅱ)確定范圍BETWEEN…AND…

和NOTBETWEEN…AND…例10查詢年齡在20到23歲之間的學(xué)生的姓名、系別和年齡SELECT姓名，所在系，年齡FROM學(xué)生WHERE年齡BETWEEN20AND23;5.3數(shù)據(jù)查詢ⅲ)確定集合謂詞IN可以用來查找屬性值屬于指定集合的元組。在SQL中，集合的表示法使用圓括號括起來的一組元素值，元素值之間用逗號隔開。例11查詢信息系（IS）、數(shù)學(xué)系（MA）和計算機(jī)系（CS）學(xué)生的姓名和性別。SELECT姓名，性別FROM學(xué)生WHERE所在系IN（‘IS’，‘MA’，‘CS’）；5.3數(shù)據(jù)查詢ⅳ）字符匹配謂詞LIKE可以用來進(jìn)行字符串的匹配。語法格式：[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]

其含義是查找指定的屬性列值與<匹配串>相匹配的元組。<匹配串>可以是一個完整的字符串，也可以含有通配符“%”和“_”。其中：%：代表任意長度的字符串。例如：a%b表示以a開頭，以b結(jié)尾的任意長度的字符串，如：acb，addgb，ab都滿足該匹配串。

_:代表任意單個字符。例如：a_b表示以a開頭，以b結(jié)尾的長度為3的任意字符串。如：acb，afb等。5.3數(shù)據(jù)查詢例12查詢學(xué)號為95001的學(xué)生的詳細(xì)情況。SELECT*FROM學(xué)生WHERE學(xué)號LIKE‘95001’；例13查詢所有姓“劉”的學(xué)生的姓名、學(xué)號和性別。SELECT姓名，學(xué)號，性別FROM學(xué)生WHERE姓名LIKE‘劉%’；5.3數(shù)據(jù)查詢例14

查詢姓“歐陽”且全名為三個漢字的學(xué)生的姓名。SELECT姓名FROM學(xué)生WHERE姓名LIKE‘歐陽__’；例15查詢名字中第二個字為“陽”字的學(xué)生的姓名和學(xué)號SELECT姓名，學(xué)號FROM學(xué)生WHERE姓名LIKE‘__陽%’；5.3數(shù)據(jù)查詢例16查詢所有不姓劉的學(xué)生姓名。SELECT姓名FROM學(xué)生WHERE姓名NOTLIKE‘劉%’；

如果用戶要查詢的字符串本身就含有“%”或

“_”，這時就要使用ESCAPE‘<換碼字符>’短語對通配符進(jìn)行轉(zhuǎn)義了。例17查詢DB_Design課程的課程號和學(xué)分。SELECT課程號，學(xué)分FROM課程WHERE課程名LIKE‘DB\_Design’ESCAPE‘\’；SQLServer20005.3數(shù)據(jù)查詢例18查詢以‘DB_’開頭且倒數(shù)第3個字符為i的課程的詳細(xì)情況。SELECT*FROM課程WHERE課程名LIKE‘DB\_%i__’ESCAPE‘\’SQLServer2000SQLServer20005.3數(shù)據(jù)查詢ⅴ)涉及空值的查詢

ISNULL或ISNOTNULL例19查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號。SELECT學(xué)號，課程號FROM選課WHERE成績ISNULL；注：這里的IS不能用=代替。SQLServer20005.3數(shù)據(jù)查詢ⅵ）多重條件查詢

邏輯運(yùn)算符AND

和OR可用來聯(lián)結(jié)多個查詢條件，AND的優(yōu)先級高于OR，但用戶可以用括號改變優(yōu)先級。例20查詢計算機(jī)系年齡在20歲以下的學(xué)生的姓名。SELECT姓名FROM學(xué)生WHERE所在系=‘CS’AND年齡<20;SQLServer20005.3數(shù)據(jù)查詢對查詢結(jié)果排序

使用ORDERBY子句來指定按照一個或多個屬性列的升序(ASC)或降序(DESC)重新排列查詢結(jié)果中的行，其中升序(ASC)為默認(rèn)值。例21查詢選修了3號課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按分?jǐn)?shù)的降序排列。SELECT學(xué)號，成績FROM選課WHERE課程號=‘3’ORDERBY成績DESC；SQLServer2000注：對于空值，若按升序排，含空值的元組將最后顯示；若按降序排，空值的元組將最先顯示。例22查詢?nèi)w學(xué)生的情況，查詢結(jié)果按所在系的系號升序排列，同一系中的學(xué)生按年齡降序排列。SELECT*FROM學(xué)生ORDERBY所在系，年齡DESC;5.3數(shù)據(jù)查詢SQLServer20005.3數(shù)據(jù)查詢使用集函數(shù)

SQL提供的集函數(shù)主要有：COUNT([DISTINCT|ALL]*)統(tǒng)計元組個數(shù)COUNT([DISTINCT|ALL]<列名>)統(tǒng)計一列中值的個數(shù)SUM([DISTINCT|ALL]<列名>)計算一列值的總和（此列必須是數(shù)值型的）

AVG([DISTINCT|ALL]<列名>)計算一列值的平均值（此列必須是數(shù)值型的）MAX([DISTINCT|ALL]<列名>)求

一列值中的最大值MIN([DISTINCT|ALL]<列名>)求

一列值中的最小值SQLServer20005.3數(shù)據(jù)查詢例23查詢學(xué)生總?cè)藬?shù)。SELECTCOUNT(*)FROM學(xué)生；例24查詢選修了課程的學(xué)生人數(shù)SELECTCOUNT(DISTINCT學(xué)號)FROM選課；學(xué)生人數(shù)SQLServer2000例25計算選修了1號課程的學(xué)生平均成績。SELECTAVG(成績)FROM選課WHERE課程號=‘1’；例26查詢選修1號課程的學(xué)生最高分?jǐn)?shù)。SELECTMAX(成績)FROM選課WHERE課程號=‘1’；注：除count(*)外，列中的空值先去掉再計算5.3數(shù)據(jù)查詢SQLServer2000例27：求選修了課程號為“c1”且成績在80-90之間的學(xué)生學(xué)號和成績，并將成績乘以系數(shù)0.8輸出SELECT學(xué)號，成績*0.8FROM選課WHERE課程號=‘c1’AND成績BETWEEN80AND90例28：檢索數(shù)學(xué)系或計算機(jī)系姓“陳”的學(xué)生的信息

SELECT*FROM學(xué)生WHERE所在系IN(‘?dāng)?shù)學(xué)’，‘計算機(jī)’)AND姓

名LIKE‘陳%’SQLServer20005.3數(shù)據(jù)查詢（5）對查詢結(jié)果分組GROUPBY

子句和HAVING子句來實(shí)現(xiàn)分組統(tǒng)計。例29求各個課程號及相應(yīng)的選課人數(shù)。SELECT課程號，COUNT(學(xué)號)FROM選課GROUPBY課程號；例30查詢選修了3門以上課程的學(xué)生學(xué)號。SELECT學(xué)號FROM選課GROUPBY學(xué)號HAVINGCOUNT(*)>3SQLServer20005.3數(shù)據(jù)查詢WHERE子句與HAVING短語的區(qū)別：WHERE子句與HAVING短語都用來做選擇，其區(qū)別在于作用對象不同。WHERE子句作用于基本表或視圖，從中選擇滿足條件的元組，HAVING短語作用于組，從中選擇滿足條件的組。當(dāng)WHERE子句與HAVING短語同時出現(xiàn)時，先運(yùn)行WHERE子句選擇元組，再進(jìn)行分組統(tǒng)計。例如上例不能寫成：

select學(xué)號from選課

groupby學(xué)號

wherecount(*)>3SQLServer2000例31：求各門課的課程號，最高分，最低分，平均分SELECT

課程號,MAX(成績)AS最高分,MIN(成績)AS最低分,AVG(成績)AS平均分FROM選課GROUPBY課程號例32：求每個學(xué)生的學(xué)號、姓名及平均分SELECT

學(xué)生.學(xué)號,姓名,AVG(成績)AS平均分FROM選課,學(xué)生WHERE學(xué)生.學(xué)號=選課.學(xué)號GROUPBY學(xué)生.學(xué)號,姓名5.3數(shù)據(jù)查詢SQLServer20002、連接查詢連接查詢的數(shù)據(jù)源為多個表，連接條件通過where子句表達(dá)，連接條件和元組選擇條件之間用and銜接。1)等值和非等值連接其一般格式為：[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>

比較運(yùn)算符：=、>、<、>=、<=和!=；列名稱為連接字段。

此外，連接條件還可以使用下面形式：[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

當(dāng)連接運(yùn)算符為“=”時，稱為等值連接，其它運(yùn)算符稱為非等值連接。連接條件中的各連接字段類型必須是可比的，但不必是相同的。5.3數(shù)據(jù)查詢SQLServer20005.3數(shù)據(jù)查詢例33：查詢每個學(xué)生及其選修課程的情況

SELECT學(xué)生.*,選課.*FROM學(xué)生,選課

WHERE學(xué)生.學(xué)號=選課.學(xué)號

連接運(yùn)算有兩種特殊的情況，一種為自然連接，另一種為廣義笛卡爾積連接。SQLServer2000如果使用了一個以上的表，但沒有where子句，則結(jié)果為廣義笛卡爾積，其連接結(jié)果會產(chǎn)生一些沒有意義的元組。例如：

SELECT學(xué)生.學(xué)號,課程號,成績FROM學(xué)生,選課WHERE學(xué)生.學(xué)號=選課.學(xué)號

結(jié)果為學(xué)生表與選課表中對應(yīng)的記錄

SELECT學(xué)生.學(xué)號,課程號,成績FROM學(xué)生,選課

結(jié)果為笛卡爾積5.3數(shù)據(jù)查詢SQLServer20005.3數(shù)據(jù)查詢

若在等值連接中把目標(biāo)列中重復(fù)的屬性列去掉，則為自然連接。如：上例用自然連接完成：SELECT學(xué)生.學(xué)號，姓名，性別，年齡，所在系，課程號，

成績FROM學(xué)生，選課WHERE學(xué)生.學(xué)號=選課.學(xué)號SQLServer2000例34：查詢學(xué)生的學(xué)號，姓名及所選修的課程名及成績

SELECT學(xué)生.學(xué)號,姓名,課程名,成績FROM學(xué)生,課程,選課

WHERE學(xué)生.學(xué)號=選課.學(xué)號and課程.課程號=選課.課程號例35：查詢考試成績有不及格的學(xué)生的學(xué)號、姓名SELECTDISTINCT學(xué)生.學(xué)號,姓名FROM學(xué)生,選課WHERE成績<60AND學(xué)生.學(xué)號=選課.學(xué)號5.3數(shù)據(jù)查詢SQLServer2000例36：查詢每一門課的間接先行課（即先行課的先行課）SELECTA.課程號,A.課程名,B.先行課FROM課程A,課程BWHEREA.先行課=B.課程號2)自身連接

連接操作不只在兩個表之間進(jìn)行，一個表內(nèi)也可以進(jìn)行自身連接5.3數(shù)據(jù)查詢SQLServer2000課程號課程名先行課c1 計算機(jī)導(dǎo)論NULLc2 C語言 c1c3 數(shù)據(jù)結(jié)構(gòu) c2c4 數(shù)據(jù)庫原理 c3c5 軟件工程 c4c6 高等數(shù)學(xué)NULLc7 DB_Design c3A課程號課程名先行課c1 計算機(jī)導(dǎo)論NULLc2 C語言 c1c3 數(shù)據(jù)結(jié)構(gòu) c2c4 數(shù)據(jù)庫原理 c3c5 軟件工程 c4c6 高等數(shù)學(xué)NULLc7 DB_Design c3B5.3數(shù)據(jù)查詢SQLServer20003)外連接內(nèi)連接：結(jié)果集中只保留了符合連接條件的元組，而排除了兩個表中沒有匹配的元組情況，前面所舉的例子均屬內(nèi)連接

如果要求查詢結(jié)果集中保留非匹配的元組，就要執(zhí)行外部連接操作。左外部連接：結(jié)果集中保留連接表達(dá)式左表中的非匹配記錄。左外部連接符號為“＊=”右外部連接：結(jié)果集中保留連接表達(dá)式右表中的非匹配記錄。右外部連接符號為“=＊”外部連接中不匹配的分量用NULL表示。5.3數(shù)據(jù)查詢SQLServer2000職工號姓名性別年齡所在部門

部門號部門名稱電話1010李勇男201111生產(chǎn)科5661011劉晨女19

12計劃科5781012王敏女221213一車間4671014張立男211314科研所

5.3數(shù)據(jù)查詢內(nèi)連接：

SELECT職工.*，部門名稱，電話

FROM職工，部門

WHERE職工.所在部門=部門.部門號；SQLServer2000左外部連接：

SELECT職工.*，部門名稱，電話

FROM職工，部門

WHERE職工.所在部門*=部門.部門號；右外部連接：

SELECT職工.*，部門名稱，電話

FROM職工，部門

WHERE職工.所在部門=*部門.部門號；5.3數(shù)據(jù)查詢SQLServer2000例37：所有學(xué)生的選課情況（包括沒有選課的學(xué)生）

SELECT學(xué)生.學(xué)號,課程號,成績FROM學(xué)生,選課

WHERE學(xué)生.學(xué)號*=選課.學(xué)號

例38：查詢?nèi)窟x課情況（包括學(xué)生表中沒有的學(xué)生的選課信息）SELECT學(xué)生.學(xué)號,課程號,成績FROM學(xué)生,選課WHERE學(xué)生.學(xué)號=*選課.學(xué)號

5.3數(shù)據(jù)查詢SQLServer2000一個SELECT…FROM…WHERE語句稱為一個查詢塊，將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢叫嵌套查詢。

SQL語言允許多層嵌套查詢，即：一個子查詢中還可以嵌套其它子查詢。注意：子查詢的SELECT語句中不能使用ORDERBY子句，ORDERBY子句只能對最終查詢結(jié)果排序。3、嵌套查詢5.3數(shù)據(jù)查詢有以下4種子查詢：

帶IN謂詞的子查詢帶比較運(yùn)算符的子查詢帶ANY或ALL的子查詢帶EXISTS謂詞的子查詢5.3數(shù)據(jù)查詢5.3數(shù)據(jù)查詢1）帶IN謂詞的子查詢

使用IN運(yùn)算符的子查詢用于判斷某個屬性列的值是否在子查詢的結(jié)果中。例39查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生信息。SELECT*FROM學(xué)生WHERE所在系IN(SELECT所在系

FROM學(xué)生

WHERE姓名=‘劉晨’）也可以用自身連接來完成：SELECTS1.*FROM學(xué)生S1,學(xué)生S2WHERES1.所在系=S2.所在系A(chǔ)NDS2.姓名=‘劉晨’；5.3數(shù)據(jù)查詢例40求選修了高等數(shù)學(xué)的學(xué)生學(xué)號和姓名。SELECT學(xué)號，姓名FROM學(xué)生WHERE學(xué)號IN(SELECT學(xué)號FROM選課

WHERE課程號IN(SELECT課程號

FROM課程

WHERE課程名=‘高等數(shù)學(xué)'))；SQLServer2000也可以使用連接查詢：SELECT學(xué)生.學(xué)號，姓名FROM學(xué)生，課程，選課WHERE學(xué)生.學(xué)號=課程.學(xué)號AND課程.課程號=選課.課程號AND課程.課程名='高等數(shù)學(xué)'；SQLServer2000例41：求選修了“計算機(jī)導(dǎo)論”的學(xué)生學(xué)號

SELECT學(xué)號FROM選課WHERE課程號IN(SELECT課程號FROM課程WHERE課程名='計算機(jī)導(dǎo)論')此例也可用連接查詢：SELECT學(xué)號

FROM選課,課程WHERE選課.課程號=課程.課程號and課程名='計算機(jī)導(dǎo)論'5.3數(shù)據(jù)查詢SQLServer2000例42：求沒有選修“計算機(jī)導(dǎo)論”的學(xué)生學(xué)號SELECT學(xué)號FROM學(xué)生WHERE學(xué)號NOTIN(SELECT學(xué)號FROM選課WHERE課程號IN(SELECT課程號FROM課程WHERE課程名='計算機(jī)導(dǎo)論'))5.3數(shù)據(jù)查詢SQLServer2000如下語句是否正確:SELECT學(xué)號FROM選課WHERE課程號NOTIN(SELECT課程號FROM課程WHERE課程名='計算機(jī)導(dǎo)論')(2)SELECT學(xué)號FROM選課,課程WHERE選課.課程號=課程.課程號AND課程名<>'計算機(jī)導(dǎo)論'5.3數(shù)據(jù)查詢SQLServer20005.3數(shù)據(jù)查詢2)使用比較運(yùn)算符的子查詢例39查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生信息。SELECT*FROM學(xué)生WHERE所在系IN(SELECT所在系

FROM學(xué)生

WHERE姓名=‘劉晨’）可以用“=”代替INSQLServer20005.3數(shù)據(jù)查詢例43求c1課程成績高于“王紅”的學(xué)生學(xué)號，成績SELECT學(xué)號,成績FROM選課WHERE課程號=‘c1’AND成績

>

(SELECT成績FROM選課WHERE課程號='c1'AND學(xué)號=(SELECT學(xué)號FROM學(xué)生WHERE姓名='王紅'))SQLServer20005.3數(shù)據(jù)查詢3)使用ANY或ALL的嵌套查詢

使用ANY或ALL操作符時，必須與比較符配合使用，其格式為：<字段><比較符>[ANY|ALL]<子查詢>操作符語意>ANY大于子查詢結(jié)果中的某個值，即表示大于查詢結(jié)果中最小值>ALL大于子查詢結(jié)果中的所有值，即表示大于查詢結(jié)果中最大值<ANY小于子查詢結(jié)果中的某個值，即表示小于查詢結(jié)果中最大值<ALL小于子查詢結(jié)果中的所有值，即表示小于查詢結(jié)果中最小值>=ANY大于等于子查詢結(jié)果中的某個值，即表示大于等于結(jié)果集中最小值>=ALL大于等于子查詢結(jié)果中的所有值，即表示大于等于結(jié)果集中最大值<=ANY小于等于子查詢結(jié)果中的某個值，即表示小于等于結(jié)果集中最大值<=ALL小于等于子查詢結(jié)果中的所有值，即表示小于等于結(jié)果集中最小值=ANY等于子查詢結(jié)果中的某個值，即相當(dāng)于IN=ALL等于子查詢結(jié)果中的所有值(通常沒有實(shí)際意義)!=(或<>)ANY不等于子查詢結(jié)果中的某個值，!=(或<>)ALL不等于子查詢結(jié)果中的任何一個值，即相當(dāng)于NOTINSQLServer2000例44：求其他系中比計算機(jī)系某一學(xué)生年齡小的學(xué)生的信息。（即求年齡小于計算機(jī)系年齡最大者的學(xué)生）SELECT*FROM學(xué)生WHERE所在系<>’計算機(jī)’AND年齡<ANY(SELECT年齡FROM學(xué)生WHERE所在系='計算機(jī)')

此例也可用max()：SELECT

*FROM學(xué)生WHERE所在系<>'計算機(jī)'AND年齡<(SELECTMAX(年齡)FROM學(xué)生WHERE所在系=‘計算機(jī)’)

5.3數(shù)據(jù)查詢SQLServer2000例45：檢索所有課程成績都在80分及80分以上的學(xué)生學(xué)號、姓名如下語句是否正確:SELECT學(xué)號,姓名FROM學(xué)生WHERE

學(xué)號IN(SELECT學(xué)號FROM選課WHERE成績>80)(2)SELECT學(xué)號,姓名FROM學(xué)生

WHERE

學(xué)號NOTIN(SELECT學(xué)號FROM選課WHERE成績<80)5.3數(shù)據(jù)查詢正確語句:SELECTDISTINCT學(xué)生.學(xué)號,姓名FROM學(xué)生,選課

WHERE學(xué)生.學(xué)號=選課.學(xué)號AND學(xué)號NOTIN(SELECT學(xué)號FROM選課WHERE成績<80OR成績ISNULL)

SQLServer2000例46：求其他系中比計算機(jī)系學(xué)生年齡都小的學(xué)生的信息。（即求年齡小于計算機(jī)系年齡最小者的學(xué)生）SELECT*FROM學(xué)生WHERE年齡<ALL(SELECT年齡FROM學(xué)生WHERE所在系='計算機(jī)')AND所在系<>’計算機(jī)’也可以用集函數(shù)表示：SELECT*FROM學(xué)生WHERE年齡<(SELECTMIN(年齡)

FROM學(xué)生WHERE所在系='計算機(jī)')AND所在系<>’計算機(jī)’5.3數(shù)據(jù)查詢SQLServer2000例47：求選課門數(shù)最多的學(xué)生的學(xué)號SELECT學(xué)號FROM選課GROUPBY學(xué)號HAVINGCOUNT(課程號)>=ALL(SELECTCOUNT(課程號)FROM選課GROUPBY學(xué)號)5.3數(shù)據(jù)查詢SQLServer2000例48：求選課門數(shù)最多的學(xué)生的學(xué)號、姓名SELECT學(xué)號,姓名FROM學(xué)生WHERE學(xué)號IN(SELECT學(xué)號FROM選課

GROUPBY學(xué)號HAVINGCOUNT(課程號)>=ALL(SELECTCOUNT(課程號)FROM選課GROUPBY學(xué)號))5.3數(shù)據(jù)查詢SQLServer2000或：SELECT學(xué)生.學(xué)號,姓名FROM學(xué)生,選課

WHERE學(xué)生.學(xué)號=選課.學(xué)號GROUPBY學(xué)生.學(xué)號,姓名

HAVINGCOUNT(課程號)>=ALL(SELECTCOUNT(課程號)FROM選課GROUPBY學(xué)號)5.3數(shù)據(jù)查詢SQLServer20005.3數(shù)據(jù)查詢4）使用EXISTS操作符的嵌套查詢

EXISTS代表存在量詞。EXISTS操作符后子查詢的結(jié)果集中如果不為空，則產(chǎn)生邏輯真值“true”，否則產(chǎn)生假值“false”。例49求選修了C2課程的學(xué)生姓名。

也可以用連接查詢實(shí)現(xiàn)：SELECT姓名FROM學(xué)生，選課WHERE學(xué)生.學(xué)號=選課.學(xué)號AND課程號=‘C2’;SELECT姓名FROM學(xué)生

WHEREEXISTS(SELECT*FROM選課

WHERE學(xué)生.學(xué)號=學(xué)號AND課程號='C2')；SQLServer20005.3數(shù)據(jù)查詢例50求沒有選修C2課程的學(xué)生姓名。SELECT姓名

FROM學(xué)生

WHERENOTEXISTS(SELECT*FROM選課

WHERE學(xué)生.學(xué)號=學(xué)號AND課程號='C2')；查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生信息SELECT*FROM學(xué)生S1WHEREEXISTS(SELECT*FROM學(xué)生S2WHERES1.所在系=所在系A(chǔ)ND姓名=‘劉晨’）5.3數(shù)據(jù)查詢5.3數(shù)據(jù)查詢

SQL語言沒有全稱量詞，但是總可以把帶有全稱量詞的謂詞轉(zhuǎn)換為帶有存在量詞的謂詞：例51查詢選修了全部課程的學(xué)生的姓名SELECT姓名FROM學(xué)生

WHERENOTEXISTS(SELECT*FROM課程

WHERENOTEXISTS(SELECT*

FROM選課

WHERE學(xué)生.學(xué)號=學(xué)號AND課程.課程號=課程號))；5.3數(shù)據(jù)查詢例52求至少選修了學(xué)號為“S2”的學(xué)生所選修的全部課程的學(xué)生學(xué)號和姓名。

SELECT學(xué)號，姓名

FROM學(xué)生

WHERENOTEXISTS(SELECT*FROM選課選課1WHERE選課1.學(xué)號='S2'ANDNOTEXISTS(SELECT*FROM選課選課2WHERE學(xué)生.學(xué)號=選課2.學(xué)號AND選課2.課程號=選課1.課程號)；SQLServer2000例53：求選修了C1課程或選修了C2課程的學(xué)生的學(xué)號SELECT學(xué)號FROM選課WHERE課程號='C1'OR課程號='C2'4、組合查詢或：SELECT學(xué)號FROM選課WHERE課程號='C1'

UNIONSELECT學(xué)號FROM選課WHERE課程號='C2'5.3數(shù)據(jù)查詢5.3數(shù)據(jù)查詢例54：求選修了C1課程又選修了C2課程的學(xué)生的學(xué)號如下語句是否正確：SELECT學(xué)號FROM選課WHERE課程號='C1'AND課程號='C2'應(yīng)該為SELECT學(xué)號FROM選課WHERE課程號=‘C1’AND學(xué)號IN(SELECT學(xué)號FROM選課WHERE課程號='C2')SQLServer20001、插入數(shù)據(jù)

(1)插入單個元組語句格式：

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

VALUES

(<常量1>[,<常量2>]…)注：a、在INTO子句中若沒有指明任何列名，則在VALUES子句中必須在每個列上均有值，并且要與表中屬性的邏輯順序?qū)?yīng)例：

INSERTINTO選課VALUES(‘98008’,‘c5’,70)INSERTINTO選課VALUES(‘98010’,‘c5’)INSERTINTO選課VALUES(‘c2’,‘98008’,70)

5.4數(shù)據(jù)更新對錯錯SQLServer2000b、如果某些列在INTO子句中沒有出現(xiàn)，則新插入的記錄在這些列上取空值，但如果這些列在表定義是定義為NOTNULL，則不能在INTO子句中省略例：

INSERTINTO選課(學(xué)號,課程號)VALUES(‘98008’,‘c4’)

INSERTINTO選課(學(xué)號,成績)VALUES(‘98008’,70)c、INTO子句中列名與VALUES子句中的常量要求邏輯順序一致例：INSERTINTO選課(學(xué)號,課程號)VALUES(‘98008’,‘c4’)

INSERTINTO選課(學(xué)號,課程號)VALUES(‘c1’,’98008’)5.4數(shù)據(jù)更新對錯對錯SQLServer2000例55將一個新學(xué)生記錄(學(xué)號：‘98010’，姓名：‘張三’，年齡：20，所在系：‘計算機(jī)系’)插入到學(xué)生表中。INSERT

INTO學(xué)生

VALUES(‘98010’，‘張三’，20，‘計算機(jī)系’)；5.4數(shù)據(jù)更新SQLServer2000(2)插入子查詢的結(jié)果集語句格式：INSERTINTO<表名>[(<屬性列1>[,<屬性列2>…)]<子查詢>;例56：求每個系學(xué)生的平均年齡，并要求將結(jié)果存入數(shù)據(jù)庫中。此例要求先建立系平均年齡表，再將各系的平均年齡寫入此表中CREATETABLE系平均年齡(系名稱CHAR(10),

平均年齡SMALLINT);INSERTINTO系平均年齡

SELECT所在系,AVG(年齡)FROM學(xué)生GROUPBY所在系5.4數(shù)據(jù)更新SQLServer2000

2、修改數(shù)據(jù)

語句格式：

UPDATE<表名>

SET<列名1>=<表達(dá)式1>[,<列名2>=<表達(dá)式2>][,…n][WHERE<條件>]注：

a、如果無WHERE子句，則表示修改表中全部元組例57：將學(xué)生表中每個學(xué)生年齡加1UPDATE學(xué)生SET年齡=年齡+15.4數(shù)據(jù)更新SQLServer2000b、可以通過WHERE子句中的條件表達(dá)式修改某一個元組的值例57將學(xué)生95001的年齡改為22歲。UPDATE學(xué)生SET年齡=22WHERE學(xué)號=‘95001’;c、WHERE子句中可以嵌入子查詢

例58：將“高等數(shù)學(xué)”的成績加5分

UPDATE選課SET成績=成績+5WHERE課程號IN(SELECT課程號FROM課程WHERE課程名='高等數(shù)學(xué)')5.4數(shù)據(jù)更新SQLServer20003、刪除數(shù)據(jù)

語句格式：

DELETEFROM<表名>[WHERE<條件>]注：

a、如果無WHERE子句，則表示刪除表中全部元組例59刪除所有學(xué)生選課記錄DELETEFROM選課5.4數(shù)據(jù)更新SQLServer2000b、可以通過WHERE子句中的條件表達(dá)式刪除一個元組的值例60刪除學(xué)號為95019的學(xué)生記錄DELETEFROM學(xué)生WHERE學(xué)號=‘95019’;c、WHERE子句中可以嵌入子查詢5.4數(shù)據(jù)更新SQLServer2000DELETEFROM學(xué)生WHERE所在系=‘?dāng)?shù)學(xué)’5.4數(shù)據(jù)更新d、一個DELETE語句只能刪除一個表中的元組，即FROM子句中只能有一個表名，不允許有多個表名。例61：刪除“數(shù)學(xué)系”的學(xué)生記錄及該系學(xué)生的所有選課記錄

DELETEFROM選課WHERE學(xué)號IN(SELECT學(xué)號

FROM學(xué)生WHERE所在系=‘?dāng)?shù)學(xué)’)

SQLServer2000

視圖是從一個或者多個表或視圖中導(dǎo)出的表，和真實(shí)的表一樣，視圖也包括幾個被定義的數(shù)據(jù)列和多個數(shù)據(jù)行，但視圖是一個虛擬表，所以數(shù)據(jù)庫中只存放視圖的定義，而不存放視圖的數(shù)據(jù)，這些數(shù)據(jù)來源于基本表。

使用視圖的優(yōu)點(diǎn)和作用

①可以使視圖集中數(shù)據(jù)、簡化和定制不同用戶對數(shù)據(jù)庫的不同數(shù)據(jù)要求。②使用視圖可以屏蔽數(shù)據(jù)的復(fù)雜性，用戶不必了解數(shù)據(jù)庫的結(jié)構(gòu)，就可以方便地使用和管理數(shù)據(jù)，簡化數(shù)據(jù)權(quán)限管理和重新組織數(shù)據(jù)以便輸出到其他應(yīng)用程序中。5.5視圖SQLServer2000③視圖可以使用戶只關(guān)心他感興趣的某些特定數(shù)據(jù)和他們所負(fù)責(zé)的特定任務(wù)，而那些不需要的或者無用的數(shù)據(jù)則不在視圖中顯示。④視圖大大地簡化了用戶對數(shù)據(jù)的操作。⑤視圖可以讓不同的用戶以不同的方式看到不同或者相同的數(shù)據(jù)集。⑥在某些情況下，由于表中數(shù)據(jù)量太大，因此在表的設(shè)計時常將表進(jìn)行水平或者垂直分割，但表的結(jié)構(gòu)的變化不會對應(yīng)用程序產(chǎn)生不良的影