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

SQL（StructuredQueryLanguage，結(jié)構化查詢語言）是關系數(shù)據(jù)庫的標準語言

1974年由Boyce和Chamberlin提出

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

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

1992年ANSI公布SQL2標準（即SQL-92標準）

1999年ANSI公布SQL3標準5.1SQL概述SQLServer2000二、SQL優(yōu)點

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

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

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

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

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

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

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

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

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

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

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

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

命令：create、alter、drop

2、查詢語言（ＱＬ）

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

命令：select

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

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

命令：insert、update、delete

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

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

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

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

5.2數(shù)據(jù)定義類型表示類型說明數(shù)值型數(shù)據(jù)SMALLINT半字長二進制整數(shù)。15bits數(shù)據(jù)INTEGER或INT全字長（四字長）整數(shù)。31bits數(shù)據(jù)DECIMAL(p[，q])十進制數(shù)，共p位，其中小數(shù)點后q位。0≤q≤p，q=0時可省略不寫FLOAT雙字長浮點數(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ù)據(jù)，有以下5種：notnull約束不允許為空。unique約束不允許該列出現(xiàn)重復的屬性值。default約束定義該列的缺省值。default約束的格式為：default<約束名><默認值>for<列名>

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

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

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

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

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

約束

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

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

姓名char(8)notnull,

年齡smallintdefault20,

性別char(2),

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

課程名char(20),

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

課程號char(5),

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

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

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

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

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

altertable<表名>drop<約束名>

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

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

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

droptable

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

例11：droptable學生

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

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

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

2)大表應當建索引，小表則不必建索引。

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

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

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

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

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

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

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

dropindex<索引名>

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

select[ALL|DISTINCT]<目標列組>

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

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

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

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

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

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

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

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

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

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

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

邏輯運算符AND

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

使用ORDERBY子句來指定按照一個或多個屬性列的升序(ASC)或降序(DESC)重新排列查詢結(jié)果中的行，其中升序(ASC)為默認值。例21查詢選修了3號課程的學生的學號及其成績，查詢結(jié)果按分數(shù)的降序排列。SELECT學號，成績FROM選課WHERE課程號=‘3’ORDERBY成績DESC；SQLServer2000注：對于空值，若按升序排，含空值的元組將最后顯示；若按降序排，空值的元組將最先顯示。例22查詢?nèi)w學生的情況，查詢結(jié)果按所在系的系號升序排列，同一系中的學生按年齡降序排列。SELECT*FROM學生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查詢學生總?cè)藬?shù)。SELECTCOUNT(*)FROM學生；例24查詢選修了課程的學生人數(shù)SELECTCOUNT(DISTINCT學號)FROM選課；學生人數(shù)SQLServer2000例25計算選修了1號課程的學生平均成績。SELECTAVG(成績)FROM選課WHERE課程號=‘1’；例26查詢選修1號課程的學生最高分數(shù)。SELECTMAX(成績)FROM選課WHERE課程號=‘1’；注：除count(*)外，列中的空值先去掉再計算5.3數(shù)據(jù)查詢SQLServer2000例27：求選修了課程號為“c1”且成績在80-90之間的學生學號和成績，并將成績乘以系數(shù)0.8輸出SELECT學號，成績*0.8FROM選課WHERE課程號=‘c1’AND成績BETWEEN80AND90例28：檢索數(shù)學系或計算機系姓“陳”的學生的信息

SELECT*FROM學生WHERE所在系IN(‘數(shù)學’，‘計算機’)AND姓

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

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

select學號from選課

groupby學號

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

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

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

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

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

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

SELECT學生.*,選課.*FROM學生,選課

WHERE學生.學號=選課.學號

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

SELECT學生.學號,課程號,成績FROM學生,選課WHERE學生.學號=選課.學號

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

SELECT學生.學號,課程號,成績FROM學生,選課

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

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

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

SELECT學生.學號,姓名,課程名,成績FROM學生,課程,選課

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

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

如果要求查詢結(jié)果集中保留非匹配的元組，就要執(zhí)行外部連接操作。左外部連接：結(jié)果集中保留連接表達式左表中的非匹配記錄。左外部連接符號為“＊=”右外部連接：結(jié)果集中保留連接表達式右表中的非匹配記錄。右外部連接符號為“=＊”外部連接中不匹配的分量用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：所有學生的選課情況（包括沒有選課的學生）

SELECT學生.學號,課程號,成績FROM學生,選課

WHERE學生.學號*=選課.學號

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

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

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

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

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

FROM學生

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

WHERE課程號IN(SELECT課程號

FROM課程

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

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

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

FROM學生

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

>

(SELECT成績FROM選課WHERE課程號='c1'AND學號=(SELECT學號FROM學生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é)果中的某個值，即相當于IN=ALL等于子查詢結(jié)果中的所有值(通常沒有實際意義)!=(或<>)ANY不等于子查詢結(jié)果中的某個值，!=(或<>)ALL不等于子查詢結(jié)果中的任何一個值，即相當于NOTINSQLServer2000例44：求其他系中比計算機系某一學生年齡小的學生的信息。（即求年齡小于計算機系年齡最大者的學生）SELECT*FROM學生WHERE所在系<>’計算機’AND年齡<ANY(SELECT年齡FROM學生WHERE所在系='計算機')

此例也可用max()：SELECT

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

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

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

WHERE

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

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

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

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

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

WHERE學生.學號=選課.學號GROUPBY學生.學號,姓名

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

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

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

WHEREEXISTS(SELECT*FROM選課

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

FROM學生

WHERENOTEXISTS(SELECT*FROM選課

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

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

WHERENOTEXISTS(SELECT*FROM課程

WHERENOTEXISTS(SELECT*

FROM選課

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

SELECT學號，姓名

FROM學生

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

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

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

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

VALUES

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

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選課(學號,課程號)VALUES(‘98008’,‘c4’)

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

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

INTO學生

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

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

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

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

語句格式：

UPDATE<表名>

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

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

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

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

語句格式：

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

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

DELETEFROM選課WHERE學號IN(SELECT學號

FROM學生WHERE所在系=‘數(shù)學’)

SQLServer2000

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

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

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