SQLServer第5章SQLServer查詢處理和表數據編輯1第5章SQLServer查詢處理和表數據編輯

5.1查詢數據

5.2表數據編輯5.3大文本和圖像數據處理

25.1查詢數據SQL用SELECT語句進行數據查詢

SELECT語句的格式SELECT[DISTINCT]<目標列表達式>[,…n]FROM<表名或視圖名>[,…n][WHERE<條件表達式>][GROUPBY<列名1>[HAVING<條件表達式>]][ORDERBY<列名2>[ASC|DESC]]SELECT語句的含義

根據WHERE條件，從FROM指定的表中找出滿足條件的元組，按目標列表達式，選出屬性值，形成結果表。

35.1查詢數據5.1.1簡單查詢

5.1.2統計

5.1.3連接查詢

5.1.4子查詢

5.1.5聯合查詢

4【例】查詢學號后兩位是“09”，或者等于學號前兩位或中間兩位的學生學號和姓名。SELECTSno,SnameFROMStudentWHERESUBSTRING(Sno,6,2)IN('09',SUBSTRING(Sno,2,2),SUBSTRING(Sno,4,2))

SUBSTRING(s,p,c):取子串函數,返回字符串s中從第p個字符開始，長度為c的子串。5【例】查詢學號長度不等于7，或者學號后6位含有非數字字符的學生學號和姓名。

SELECTSno,SnameFROMStudent

WHERESnoNOTLIKE'S[0-9][0-9][0-9][0-9][0-9][0-9]'

【例】查詢學號最后一位既不是“1”和“3”，也不是“9”的學生學號和姓名。

SELECTSno,SnameFROMStudentWHERESnoNOTLIKE'%[139]'

6對查詢結果排序若需按SELECT子句中的計算列排序，則ORDERBY子句可用三種方法來表示這個計算列：1）列表達式；2）列順序編號；3）列別名。

【例5-21】查詢選修了開課計劃編號為’010101’的課程的學生學號、成績以及加了10分后的新成績，查詢結果按原成績降序、按新成績升序排列。

SELECTSno,Grade,Grade+10ASNewGradeFROMEnrollmentWHEREOno='010101'ORDERBYGradeDESC,Grade+10上例中的Grade+10也可改寫為：NewGrade或3。也可按SELECT子句中沒有出現的列排序，此時不能用順序編號來表示排序列。

7用給定值和結果集中的元素進行大小比較【例】查詢學生S060101的姓名和平均成績SELECTSname,(SELECTSUM(Grade)FROMEnrollmentWHERESno='S060101')/(SELECTCOUNT(*)FROMEnrollmentWHERESno='S060101')FROMStudentWHERESno='S060101'

85.2表數據編輯表數據編輯又稱數據更新，包括插入數據、修改數據和刪除數據三類命令。本節內容包括：5.2.1插入數據

5.2.2修改數據

5.2.3刪除數據91．插入單個元組:INSERT…VALUES語句,格式為:INSERT[INTO]<表名>[(<列名>[,…n])]VALUES(<表達式>[,…n])注意：(1)未出現在列名列表中的列插入時取空值；(2)表達式數量必須和列名數量相等，表達式的數據類型必須和對應列的數據類型相兼容；(3)關系中的NOTNULL列必須出現在列名列表中；(4)若省略列名列表，則VALUES須指定所有列的值?！纠繉?’S060102’,’010201’)插入Enrollment表。INSERTINTOEnrollment(Sno,Ono)VALUES('S060102','010201')5.2.1插入數據105.2.1插入數據2．插入子查詢的結果:INSERT…SELECT語句,格式為:INSERT[INTO]<表名>[(<列名>[,…n])]SELECT語句注意：(1)未出現在列名列表中的列插入時取空值；(2)SELECT中的目標列須和INSERT中的列數量相等、類型兼容；(3)關系中的NOTNULL列須出現在列名表和SELECT語句中；(4)若省略列名列表，則SELECT語句須指定所有列的值?！纠壳蟾鱾€專業學生的平均累計學分，把結果存入表中。

CREATETABLEMajorgpa(SmajorCHAR(20),AvgpaINT)INSERTINTOMajorgpa(Smajor,Avgpa)SELECTSmajor,AVG(Sgpa)FROMStudentGROUPBYSmajor115.2.1插入數據3．使用SELECT…INTO語句進行數據插入,格式為:SELECT<目標列>[,…n]INTO<新表名>[SELECT語句的其他子句]注意：(1)系統會自動創建一個新表，新表的結構由目標列表達式定義，然后將SELECT語句的結果集插入這個新表；(2)當目標列是計算列時，必須為它起別名?！纠坑肧ELECT…INTO語句改寫上例。

SELECTSmajor,AVG(Sgpa)ASAvgpaINTOMajorgpa

FROMStudentGROUPBYSmajor125.3大文本和圖像數據處理處理大文本和圖像數據的數據類型：TEXT：長達2GB的ASCII字符串NTEXT：長達1GB的UNICODE字符串IMAGE：長達2GB的二進制數據流

本節內容：5.3.1大文本和圖像數據列的定義和使用5.3.2用于大文本和圖像數據處理的函數和語句135.3.1大文本和圖像數據列的定義和使用TEXT、NTEXT和IMAGE列的定義和使用方法與普通列基本相同，但有如下限制：(1)不能在這種列上定義主鍵、外鍵和索引；(2)不能按這種列進行排序或分組；(3)不能在WHERE子句中直接引用這種列，只能作為函數參數，或參與IS[NOT]NULL和LIKE運算而出現在WHERE子句中。(4)不能對TEXT和NTEXT數據實施字符串連接操作(5)向IMAGE列插人數據時，應在數據前加0x前綴表示插入的是16進制數據。145.3.1大文本和圖像數據列的定義和使用若這種列值的長度不超過8000字節，就可在SELECT、UPDATE和INSERT語句中引用它們?！纠渴紫葎摻ū恚渲泻粋€TEXT列，然后插入一些元組，最后顯示內容。

CREATETABLETextdemo(ItemINTPRIMARYKEY,

TextstringTEXT)INSERTINTOTextdemoVALUES(1,NULL)INSERTINTOTextdemoVALUES(2,'Educationismoreall-inclusivethanschooling')INSERTINTOTextdemoVALUES(3,REPLICATE('Educationisall-inclusive',4))SELECT*FROMTextdemo若長度超過8000字節，處理時會發生數據截尾。155.3.2用于大文本和圖像數據處理的函數和語句1．獲取大文本和圖像列的指針和數據長度TEXTPTR(列名):獲取大文本和圖像列的數據指針DATALENGTH(列名):獲取數據長度?！纠匡@示Textdemo表中Textstring列的數據指針和長度。SELECTItem,TEXTPTR(Textstring)AS指針,DATALENGTH(Textstring)AS長度FROMTextdemo執行結果為：

Item 指針 長度x

1 NULL NULL20xFFFFA83000 4630xFFFF3.2用于大文本和圖像數據處理的函數和語句2．檢測大文本和圖像列指針的有效性

格式：TEXTVALID('<表名>.<列名>',<數據指針>)。返回值：如果數據指針有效，則返回1，否則返回0。

【例】顯示上例中數據指針有效的列值及其長度。

SELECTDATALENGTH(Textstring)AS長度,TextstringFROMTextdemoWHERETEXTVALID('Textdemo.Textstring',TEXTPTR(Textstring))=1執行結果為：

長度

Textstring46 Educationismoreall-inclusivethanschooling104 Educationisall-inclusiveEducationis...175.3.2用于大文本和圖像數據處理的函數和語句3．通過指針讀大文本和圖像列

格式：READTEXT<表名>.<列名><數據指針>offset

size[HOLDLOCK]含義:在<數據指針>所指數據中返回從offset開始的size個字符。如果使用了HOLDLOCK關鍵字，則讀取時對數據進行加鎖。注意offset是從0開始的。

【例】顯示上例中Item為2的元組的列值前20個字符。

DECLARE@ptrVARCHAR(16)SELECT@ptr=TEXTPTR(Textstring)FROMTextdemoWHEREItem=2READTEXTTextdemo.Textstring@ptr020HOLDLOCK執行結果為：

TextstringEducationismoreal185.3.2用于大文本和圖像數據處理的函數和語句4．通過指針寫大文本和圖像列

格式：WRITETEXT<表名>.<列名><數據指針>[WITHLOG]data

含義:將數據data寫入<數據指針>所指的列，覆蓋原有數據。如果使用了WITHLOG關鍵字，則要記錄日志。

【例】向Item為1的元組的列寫入數據。

UPDATETextdemoSETTextstring='TRY'WHEREItem=1DECLARE@ptrVARCHAR(16)SELECT@ptr=TEXTPTR(Textstring)FROMTextdemoWHEREItem=1WRITETEXTTextdemo.Textstring@ptr'Ithinkso.‘SELECT*FROMTextdemoWHEREItem=1執行結果為：

ItemTextstring1 Ithinkso.195.3.2用于大文本和圖像數據處理的函數和語句5．通過指針修改大文本和圖像列,格式：

UPDATETEXT<目標表名>.<目標列名><目標數據指針><位置><長度>[WITHLOG][<要插入的數據>

|<源表名>.<源列名><源數據指針>]功能:(1)若不給出<要插入的數據>和<源表名>.<源列名><源數據指針>，則從<位置>處開始刪除<目標表名>.<目標列名><目標數據指針>所指數據的<長度>個字符。(2)若給出了<要插入的數據>或<源表名>.<源列名><源數據指針>，則將數據源插入到<目標數據指針>所指數據的第<位置>個字符位置處。插入的數據可直接給出，也可通過<源表名>.<源列名><源數據指針>給出。此時<位置>和<長度>各種組合的含義見下表。表5-10UPDATETEXT語句中<位置>和<長度>各種組合的含義

位置

長度

含義

NULLNULL將源數據追加到目標數據值的尾部

非NULLNULL用源數據替換目標數據中從<位置>開始到末尾的所有數據

非NULL0將源數據插入到目標數據的<位置>處

非NULL非NULL且非0用源數據替換目標數據中從<位置>開始的<長度>個字符

205.3.2用于大文本和圖像數據處理的函數和語句【例】在Textdemo表中刪除Item為2的元組的Textstring列的數據片斷,這個片斷從位置2開始,長度為10個字符。DECLARE@ptrVARCHAR(16)SELECT@ptr=TEXTPTR(Textstring)FROMTextdemoWHEREItem=2UPDATETEXTTextdemo.Textstring@ptr210SELECT*FROMTextdemoWHEREItem=2

執行結果為：

ItemTextstring2 Edmoreall-inclusivethanschooling215.3.2用于大文本和圖像數據處理的函數和語句【例】在Textdemo表中，將Item為2的元組的Textstring列插入Item為3的元組的Textstring列首部。

DECLARE@ptrsVARCHAR(16),@ptrdVARCHAR(16)SELECT@ptrs=TEXTPTR(Textstring)FROMTextdemoWHEREItem=2SELECT@ptrd=TEXTPTR(Textstring)FROMTextdemoWHEREItem=3UPDATETEXTTextdemo.Textstring@ptrd00Textdemo.Textstring@ptrsSELECT*FROMTextdemoWHEREItem=2

ORItem=3執行結果為：

ItemTextstring2Edmoreall-inclusivethanschooling3Edmoreall-inclusivethanschoolingEducationis…225.3.2用于大文本和圖像數據處理的函數和語句準備工作，在庫Im_Test中建立一張表Im_Info，此表中有兩個字段，分別為Pr_Id（INT），Pr_Info(IMAGE)，用來存儲圖形編號及圖形信息。其語法如下：

CREATETABLEIm_Info(Pr_IdINTNULL,Pr_InfoIMAGENULL)

第一步:往表中插入一條記錄，并初始化PR_INFO字段。其語法如下：

INSERTINTOI