第5章數(shù)據(jù)查詢與操作

SQL

Server

2005

數(shù)據(jù)庫管理與開發(fā)教程第5章

數(shù)據(jù)查詢與操作本章主要介紹針對數(shù)據(jù)表記錄的各種查詢以及對記錄的操作，主要包括選擇查詢、數(shù)據(jù)匯總、基于多表的連接查詢、子查詢以及向表中添加、修改和刪除記錄。通過本章的學(xué)習(xí)，讀者可以應(yīng)用各種查詢對數(shù)據(jù)表中的記錄進行訪問，可以使用SQL

Server

Management

Studio或者調(diào)用Transact-SQL語句對數(shù)據(jù)表中的記錄進行操作。創(chuàng)建查詢和測試查詢選擇查詢數(shù)據(jù)匯總基于多表的連接查詢子查詢添加表中的記錄修改表中的記錄刪除表中的記錄5.1

創(chuàng)建查詢和測試查詢5.1.1

編寫SQL語句在SQL

Server2005中，用戶可以在Microsoft

SQL

ServerManager

Studio中編寫SQL語句操作數(shù)據(jù)庫。（1）單擊“開始”/“程序”/“Microsoft

SQL

Server2005”/“SQL

ServerManagement

Studio”命令，打開SQL

ServerManager

Studio窗口。使用“Windows身份驗證”建立連接。單擊“標準”工具欄上的“新建查詢”按鈕。輸入如下SQL語句：

Use

studentSelect

*From

course5.1.2

測試SQL語句在新建的查詢窗口中輸入SQL語句之后，為了查看語句是否有語法錯誤，需要對SQL語句進行測試。單擊工具欄中的

按鈕或直接按<Ctrl>+<F5>組合鍵可以對當前的SQL語句進行測試，如果SQL語句準確無誤，在代碼區(qū)下方會顯示“命令已成功完成”，否則顯示錯誤信息提示。5.1.3

執(zhí)行SQL語句最后要執(zhí)行SQL語句才能實現(xiàn)各種操作。單擊工具欄上的

按鈕或直接按<F5>鍵可以執(zhí)行SQL語句。上面輸入的SQL語句的執(zhí)行結(jié)果如圖5.1所示。圖5.1顯示Course表的所有記錄5.2

選擇查詢5.2.1

簡單的SELECT查詢SELECT語句是從數(shù)據(jù)庫中檢索數(shù)據(jù)并查詢，并將查詢結(jié)果以表格的形式返回。SELECT語句的基本語法如下：SELECT

select_list[

INTO

new_table

]FROM

table_source[

WHERE

search_condition

][

GROUP

BY

group_by_expression

][

HAVING

search_condition

][

ORDER

BY

order_expression

[ASC|

DESC

]]1．選擇所有字段Select語句后的第一個子句，即

Select關(guān)鍵字開頭的子句，用于選擇進行顯示的列。如果要顯示數(shù)據(jù)表中所有列的值時，

Select子句后用星號（*）表示?！纠?-1】查詢包含所有字段的記錄。在Student數(shù)據(jù)庫中，查詢grade表的所有記錄，查詢結(jié)果如圖5.2所示。SQL語句如下：

use

studentselect

*from

grade圖5.2查詢顯示grade表的內(nèi)容2．選擇部分字段在查詢表時，很多時候只顯示所需要的字段。這時在Select子句后分別列出各個字段名稱就可以?！纠?-2】查詢包含部分字段的記錄。圖5.3顯示grade表的部分列SQL語句如下：use

studentselect學(xué)號,課程成績from

grade5.2.2

重新對列排序?qū)τ诒砀癖容^小，不用ORDER

BY子句，查詢結(jié)果會按照在表格中的順序排列的。但對于表格比較大的，則必須使用ORDER

BY子句，方便查看查詢結(jié)果。ORDER

BY子句由關(guān)鍵字ORDERBY后跟一個用逗號分開的排序列表組成，語法如下：[

ORDER

BY

{

order_by_expression[

ASC

|

DESC

]

}

[

,...n

]

]1．單級排序排序的關(guān)鍵字是orderby，默認狀態(tài)下是升序，關(guān)鍵字是asc。可以按照某一個字段排序，排序的字段是數(shù)值型，也可以是字符型、日期和時間型。【例5-3】按照某一個字段進行排序。圖5.4grade表按照“課程成績”升序排序SQL語句如下：use

studentselect

*from

gradeorder

by課程成績2．多級排序按照一列進行排序后，如果該列有重復(fù)的記錄值，則重復(fù)記錄值這部分就沒有進行有效的排序，這就需要再附加一個字段，作為第二次排序的標準，對沒有排序的記錄進行再排序。【例5-4】按照多個字段進行排序。圖5.5grade表按照多級字段排序SQL語句如下：use

studentselect

*from

gradeorder

by學(xué)期desc,課程成績5.2.3

使用運算符或函數(shù)進行列計算某些查詢要求在字段上帶表達式進行查詢，關(guān)于表達式中運算符和函數(shù)部分請參考Transact-SQL語法部分。帶表達式的查詢語法如下：SELECT表達式1,表達式2,字段1,字段2,...from數(shù)據(jù)表名【例5-5】使用運算符進行列計算。圖5.6表達式查詢5.2.4

利用WHERE參數(shù)過濾數(shù)據(jù)WHERE子句是用來選取需要檢索的記錄。因為一個表通常會有數(shù)千條記錄，在查詢結(jié)果中，用戶僅需其中的一部分記錄，這時需要使用WHERE子句指定一系列的查詢條件。WHERE子句簡單的語法如下：SELECT<字段列表>FROM<表名>WHERE<條件表達式>比較運算符（如=、<>、<和>）。范圍說明（BETWEEN和NOTBETWEEN）?？蛇x值列表（IN和NOT

IN）。模式匹配（LIKE和NOTLIKE）。上述條件的邏輯組合（AND、OR、NOT）。1．比較查詢條件比較查詢條件由比較運算符連接表達式組成，系統(tǒng)將根據(jù)該查詢條件的真假來決定某一條記錄是否滿足該查詢條件，只有滿足該查詢條件的記錄才會出現(xiàn)在最終的結(jié)果集中。SQL

Server比較運算符如表5.3所示。【例5-6】使用運算符進行比較查詢。圖5.7查詢grade表中課程成績大于90分的信息SQL語句如下：use

studentselect

*from

gradewhere課程成績>902．范圍查詢條件使用范圍條件進行查詢，是當需要返回某一個數(shù)據(jù)值是否位于兩個給定值之間，通常使用BETWEEN…AND和NOT…BETWEEN…AND來指定范圍條件。使用BETWEEN…AND查詢條件時，指定的第1個值必須小于第2個值。因為BETWEEN…AND實質(zhì)是查詢條件“大于等于第1個值，并且小于等于第2個值”的簡寫形式。即BETWEEN…AND要包括兩端的值，等價于比較運算符（>=…<=）。圖5.8顯示grade表中年齡在20～21歲之間的學(xué)生信息SQL語句如下：use

studentselect

*from

studentwhere年齡between

20

and

21【例5-8】使用NOTBETWEEN…AND語句進行范圍查詢。圖5.9顯示grade表中年齡不在20～21歲之間的學(xué)生信息SQL語句如下：use

studentselect

*from

studentwhere年齡not

between

20and

213．列表查詢條件當測試一個數(shù)據(jù)值是否匹配一組目標值中的一個時，通常使用IN關(guān)鍵字來指定列表搜索條件。IN關(guān)鍵字的格式是IN（目標值1，目標值2，目標值3，…），目標值的項目之間必須使用逗號分隔，并且括在括號中。【例5-9】使用IN關(guān)鍵字進行列表查詢。圖5.10查詢“課程編號”是k01，k03，k04的課程信息SQL語句如下：use

studentselect

*from

coursewhere課程代號in

("k01","k03""k04")【例5-10】使用NOT

IN關(guān)鍵字進行列表查詢。圖5.11查詢“課程編號”不是k01，k03，k04的課程信息SQL語句如下：Use

studentselect

*from

coursewhere課程代號not

in("k01","k03","k04")4．模糊LIKE查詢有時用戶對查詢數(shù)據(jù)表中的數(shù)據(jù)了解的不全面，如不能確定所要查詢?nèi)说男彰恢浪绽?、查詢某個人的聯(lián)系方式只知道是以“3451”結(jié)尾等，這時需要使用LIKE進行模糊查詢。LIKE關(guān)鍵字需要使用通配符在字符串內(nèi)查找指定的模式，所以讀者需要了解通配符及其含義。通配符的含義如表5.4所示。（1）“%”通配符【例5-11】使用%通配符進行模糊查詢。圖5.12student表中查詢姓李的同學(xué)信息SQL語句如下：use

studentselect

*from

studentwhere姓名like"李%"（2）“_”通配符【例5-12】使用“_”通配符進行模糊查詢。圖5.13

在student表中查詢姓“劉”并且名字是兩個字的學(xué)生信息SQL語句如下：use

studentselect

*from

studentwhere姓名like"劉_"（3）“[]”通配符【例5-13】使用“[]”通配符進行模糊查詢。圖5.14在student表中查詢聯(lián)系方式以“3451”結(jié)尾的學(xué)生SQL語句如下：use

studentselect

*from

studentwhere聯(lián)系方式5]3451"like"[1-（4）“[^]”通配符【例5-14】使用“[^]”通配符進行模糊查詢。圖5.15

在student表中查詢聯(lián)系方式以“3451”結(jié)尾但不以2開頭的學(xué)生信息SQL語句如下：use

studentselect

*from

studentwhere 聯(lián)系方式

like"[^2]3451"5．復(fù)合查詢條件很多情況下，在where子句中僅僅使用一個條件不能準確地從表中檢索到需要的數(shù)據(jù)，這里就需要邏輯運算符AND、OR和NOT。使用邏輯運算符時，遵循的指導(dǎo)原則如下：使用AND返回滿足所有條件的行；使用OR返回滿足任一條件的行；使用NOT返回不滿足表達式的行?！纠?-15】使用AND和OR結(jié)合進行查詢。圖5.16復(fù)合搜索SQL語句如下：use

studentselect

*from

studentwhere年齡>21

and性別="女"or年齡>=19

and性別="男"5.2.5

消除重復(fù)記錄

DISTINCT關(guān)鍵字主要用來從SELECT語句的結(jié)果集中去掉重復(fù)的記錄。如果用戶沒有指定DISTINCT關(guān)鍵字，那么系統(tǒng)將返回所有符合條件的記錄組成結(jié)果集，其中包括重復(fù)的記錄。【例5-16】使用DISTINCT關(guān)鍵字消除重復(fù)記錄。圖5.17顯示course表中的課程類別SQL語句如下：use

studentselect

distinct課程類別from

course5.3

數(shù)據(jù)匯總5.3.1使用聚合函數(shù)SQL提供一組聚合函數(shù)，它們能夠?qū)φ麄€數(shù)據(jù)集合進行計算，將一組原始數(shù)據(jù)轉(zhuǎn)換為有用的的信息，以便用戶使用。例如求成績表中的總成績、學(xué)生表中平均年齡等。5.3.2

使用GROUP

BY子句在SELECT子句的字段列表中，除了聚集函數(shù)外，其他所出現(xiàn)的字段一定要在GROUP

BY子句中有定義才

行。SELECT子句的字段列表中不一定要有聚集函數(shù)，但至少要用到

GROUP

BY子句列表中的一個項目。在SQL

Server中text、ntext和image數(shù)據(jù)類型的字段不能作為

GROUP

BY子句的分組依據(jù)。GROUP

BY子句不能使用字段別名。1．按單列分組GROUP

BY子句可以基于指定某一列的值將數(shù)據(jù)集合劃分為多個分組，同一組內(nèi)所有記錄在分組屬性上具有相同值。【例5-17】使用GROUP

BY子句按單列分組。圖5.18student表按照性別分組SQL語句如下：use

studentselect性別

from

studentgroup

by性別2．按多列分組GROUP

BY子句可以基于指定多列的值將數(shù)據(jù)集合劃分為多個分組?！纠?-18】使用GROUP

BY子句按多列分組。圖5.19student表按多列分組SQL語句如下：use

studentselect性別,年齡

from

studentgroup

by性別,年齡5.3.3

使用HAVING子句分組之前的條件要用where關(guān)鍵字，而分組之后的條件要使用關(guān)鍵字having子句。【例5-19】使用HAVING子句分組查詢。圖5.20student表用having篩選結(jié)果SQL語句如下：use

studentselect

avg(年齡),性別

from

studentgroup

by性別having

avg(年齡)>205.4

基于多表的連接查詢5.4.1

連接謂詞JOIN是一種將兩個表連接在一起的連接謂詞。連接條件可在FROM或WHERE子句中指定，建議在FROM子句中指定連接條件。5.4.2

以JOIN關(guān)鍵字指定的連接使用JOIN關(guān)鍵字可以進行交叉連接、內(nèi)連接和外連接。1．交叉連接交叉連接是兩個表的笛卡兒積的另一個名稱。笛卡兒積就是兩個表的交叉乘積，即兩個表的記錄進行交叉組合，如圖5.21所示。圖5.21兩個表的笛卡兒積示意圖交叉連接的語法如下：SELECT

fieldlistFROM

table1cross

JOIN

table22．內(nèi)連接內(nèi)連接也叫連接，是最早的一種連接，還被稱為普通連接或自然連接。內(nèi)連接是從結(jié)果中刪除其他被連接表中沒有匹配行的所有行，所以內(nèi)連接可能會丟失信息。內(nèi)連接的語法如下：SELECT

fieldlistFROM

table1

[INNER]

JOINtable2ONtable1.column=table2.column3．外連接外連接則擴充了內(nèi)連接的功能，會把內(nèi)連接中刪除原表中的一些保留下來，由于保留下來的行不同，把外連接分為左外連接、右外連接和全外連接3種連接。（1）左外連接左外連接的語法如下：use

studentSELECT

fieldlistFROM

table1

left

JOIN

table2ON

table1.column=table2.column【例5-20】使用LEFTJOIN…ON關(guān)鍵字進行左外連接。圖5.22student表和grade表左外連接SQL語句如下：use

studentselect

*from

studentleft

join

gradeon

student.學(xué)號=grade.學(xué)號（2）右外連接右外連接的語法如下：use

studentSELECT

fieldlistFROM

table1

right

JOINtable2ONtable1.column=table2.column【例5-21】使用RIGHTJOIN…ON關(guān)鍵字進行右外連接。圖5.23grade表和course表右外連接SQL語句如下：

use

studentselect

*from

graderight

join

courseon

course.課程代號=grade.課程代號（3）全外連接全外連接的語法如下：use

studentSELECT

fieldlistFROM

table1

full

JOIN

table2ONtable1.column=table2.column【例5-22】使用JOIN關(guān)鍵字進行全外連接。圖5.24course表和grade表全外連接SQL語句如下：use

studentselect

*

fromgrade

fulljoin

courseon

course.課程代號=grade.課程代號5.5

子

查

詢5.5.1

使用IN或NOT

IN的子查詢（1）使用IN的子查詢。帶in的嵌套查詢語法格式如下：WHERE

查詢表達式

IN（子查詢）【例5-23】使用IN關(guān)鍵字進行子查詢。圖5.25顯示參加考試的同學(xué)信息SQL語句如下：use

studentselect

*from

studentwhere學(xué)號in

(select學(xué)號

from

grade)圖5.26子查詢的結(jié)果（2）使用NOT

IN的子查詢not

in和in查詢過程類似。notin的嵌套查詢語法格式如下：WHERE

查詢表達式

NOT

IN（子查詢）【例5-24】使用NOT

IN進行子查詢。圖5.27查詢學(xué)生參加考試的課程信息SQL語句如下：use

studentselect

*from

coursewhere課程代號not

in(select課程代號from

grade)圖5.28查詢沒有學(xué)生參加考試的課程信息5.5.2

使用比較運算符的子查詢嵌套內(nèi)層子查詢通常作為搜索條件的一部分呈現(xiàn)在WHERE或HAVING子句中。例如，把一個表達式的值和由子查詢生成的值相比較。這個測試類似于簡單比較測試。子查詢比較測試用到的運算符是：=、<>、<、>、<=、>=。子查詢比較測試把一個表達式的值和由子查詢產(chǎn)生值進行比較，這時子查詢只能返回一個值，否則錯誤。最后返回比較結(jié)果為TRUE的記錄?！纠?-25】使用比較運算符進行子查詢。圖5.29顯示成績大于98分的學(xué)生信息SQL語句如下：use

studentselect

*from

studentwhere學(xué)號=(select學(xué)號

from

grade

where課程成績>98)5.5.3

使用EXISTS的子查詢exists謂詞只注重子查詢是否返回行。如果子查詢返回一個或多個行，謂詞評價為真，否則為假。exists搜索條件并不真正地使用子查詢的結(jié)果，它僅僅測試子查詢是否產(chǎn)生任何結(jié)果?！纠?-26】使用EXISTS進行子查詢。圖5.30查詢參加考試的學(xué)生信息SQL語句如下：

use

studentselect

*from

studentwhereexists(select學(xué)號from

grade

wherestudent.學(xué)號=grade.學(xué)號)【例5-27】使用NOT

EXISTS進行子查詢。SQL語句如下：

use

studentselect

*from

studentwherenot

exists(select*

from

grade

wherestudent.學(xué)號=grade.學(xué)號)圖5.31查詢沒參加考試的學(xué)生信息5.5.4

使用UNION運算符組合多個結(jié)果表的合并操作是指將兩個表的行合并到了單個表中，且不需要對這些行作任何更改。在構(gòu)造合并查詢時必須遵循以下幾條規(guī)則。所有查詢中的列數(shù)和列的順序必須相同。數(shù)據(jù)類型必須兼容。作為對所有select語句的合并操作結(jié)果進行排序的order

by子句，必須放到最后一個select后面，但它所使用的排序列名必須是第1個select選擇列表中的列名?！纠?-28】使用UNION運算符合并查詢結(jié)果。圖5.32兩個表的簡單合并查詢SQL語句如下：use

studentselect

學(xué)號,姓名

,性別

fromstudent where

年齡<22union

allselect學(xué)號,姓名,性別fromstudent

where性別="男"5.6

添加表中的記錄5.6.1

使用管理控制器添加記錄圖5.33打開數(shù)據(jù)表圖5.34編輯窗口在新增記錄內(nèi)容時有以下幾點需要注意。①設(shè)置為標識規(guī)范的字段不能輸入字段內(nèi)容。②被設(shè)置為主鍵的字段不允許與其他行的主鍵值相同。③輸入字段內(nèi)容的數(shù)據(jù)類型和字段定義的數(shù)據(jù)類型一致，包括數(shù)據(jù)類型、長度和精度等。④不允許NULL的字段必須輸入與字段類型的數(shù)據(jù)。⑤作為外鍵的字段，輸入的內(nèi)容一定要符合外鍵要求。⑥如果字段存在其他約束，輸入的內(nèi)容必須滿足約束要求。⑦如果字段被設(shè)置默認值，當不在字段內(nèi)輸入任何數(shù)據(jù)時會字段填入默認值。5.6.2

使用INSERT語句添加記錄1．INSERT語句的語法Transact-SQL中INSERT語句的基本語法如下：INSERT

INTO表名[(列名1，列名2，列名3...)