查询处理和表数据编辑

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)当目标列是计算列时，必须为它起别名。【例】用SELECT…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