05第05章查询处理和表数据编辑.ppt

SQLServer 第5章SQLServer查询处理和表数据编辑 第5章SQLServer查询处理和表数据编辑 5 1查询数据5 2表数据编辑5 3大文本和图像数据处理 5 1查询数据 SQL用SELECT语句进行数据查询SELECT语句的格式SELECT DISTINCT n FROM n WHERE GROUPBY HAVING ORDERBY ASC DESC SELECT语句的含义根据WHERE条件 从FROM指定的表中找出满足条件的元组 按目标列表达式 选出属性值 形成结果表 5 1查询数据 5 1 1简单查询5 1 2统计5 1 3连接查询5 1 4子查询5 1 5联合查询 例 查询学号后两位是 09 或者等于学号前两位或中间两位的学生学号和姓名 SELECTSno SnameFROMStudentWHERESUBSTRING Sno 6 2 IN 09 SUBSTRING Sno 2 2 SUBSTRING Sno 4 2 SUBSTRING s p c 取子串函数 返回字符串s中从第p个字符开始 长度为c的子串 例 查询学号长度不等于7 或者学号后6位含有非数字字符的学生学号和姓名 SELECTSno SnameFROMStudentWHERESnoNOTLIKE S 0 9 0 9 0 9 0 9 0 9 0 9 例 查询学号最后一位既不是 1 和 3 也不是 9 的学生学号和姓名 SELECTSno SnameFROMStudentWHERESnoNOTLIKE 139 对查询结果排序 若需按SELECT子句中的计算列排序 则ORDERBY子句可用三种方法来表示这个计算列 1 列表达式 2 列顺序编号 3 列别名 例5 21 查询选修了开课计划编号为 010101 的课程的学生学号 成绩以及加了10分后的新成绩 查询结果按原成绩降序 按新成绩升序排列 SELECTSno Grade Grade 10ASNewGradeFROMEnrollmentWHEREOno 010101 ORDERBYGradeDESC Grade 10上例中的Grade 10也可改写为 NewGrade或3 也可按SELECT子句中没有出现的列排序 此时不能用顺序编号来表示排序列 用给定值和结果集中的元素进行大小比较 例 查询学生S060101的姓名和平均成绩SELECTSname SELECTSUM Grade FROMEnrollmentWHERESno S060101 SELECTCOUNT FROMEnrollmentWHERESno S060101 FROMStudentWHERESno S060101 5 2表数据编辑 表数据编辑又称数据更新 包括插入数据 修改数据和删除数据三类命令 本节内容包括 5 2 1插入数据5 2 2修改数据5 2 3删除数据 1 插入单个元组 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插入数据 5 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 FROMStudentGROUPBYSmajor 5 2 1插入数据 3 使用SELECT INTO语句进行数据插入 格式为 SELECT n INTO SELECT语句的其他子句 注意 1 系统会自动创建一个新表 新表的结构由目标列表达式定义 然后将SELECT语句的结果集插入这个新表 2 当目标列是计算列时 必须为它起别名 例 用SELECT INTO语句改写上例 SELECTSmajor AVG Sgpa ASAvgpaINTOMajorgpaFROMStudentGROUPBYSmajor 5 3大文本和图像数据处理 处理大文本和图像数据的数据类型 TEXT 长达2GB的ASCII字符串NTEXT 长达1GB的UNICODE字符串IMAGE 长达2GB的二进制数据流本节内容 5 3 1大文本和图像数据列的定义和使用5 3 2用于大文本和图像数据处理的函数和语句 5 3 1大文本和图像数据列的定义和使用 TEXT NTEXT和IMAGE列的定义和使用方法与普通列基本相同 但有如下限制 1 不能在这种列上定义主键 外键和索引 2 不能按这种列进行排序或分组 3 不能在WHERE子句中直接引用这种列 只能作为函数参数 或参与IS NOT NULL和LIKE运算而出现在WHERE子句中 4 不能对TEXT和NTEXT数据实施字符串连接操作 5 向IMAGE列插人数据时 应在数据前加0 x前缀表示插入的是16进制数据 5 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字节 处理时会发生数据截尾 5 3 2用于大文本和图像数据处理的函数和语句 1 获取大文本和图像列的指针和数据长度TEXTPTR 列名 获取大文本和图像列的数据指针DATALENGTH 列名 获取数据长度 例 显示Textdemo表中Textstring列的数据指针和长度 SELECTItem TEXTPTR Textstring AS指针 DATALENGTH Textstring AS长度FROMTextdemo执行结果为 Item指针长度x1NULLNULL20 xFFFFA0010000000083000000010000004630 xFFFFA101000000008300000001000200104 5 3 2用于大文本和图像数据处理的函数和语句 2 检测大文本和图像列指针的有效性格式 TEXTVALID 返回值 如果数据指针有效 则返回1 否则返回0 例 显示上例中数据指针有效的列值及其长度 SELECTDATALENGTH Textstring AS长度 TextstringFROMTextdemoWHERETEXTVALID Textdemo Textstring TEXTPTR Textstring 1执行结果为 长度Textstring46Educationismoreall inclusivethanschooling104Educationisall inclusiveEducationis 5 3 2用于大文本和图像数据处理的函数和语句 3 通过指针读大文本和图像列格式 READTEXT offsetsize HOLDLOCK 含义 在所指数据中返回从offset开始的size个字符 如果使用了HOLDLOCK关键字 则读取时对数据进行加锁 注意offset是从0开始的 例 显示上例中Item为2的元组的列值前20个字符 DECLARE ptrVARCHAR 16 SELECT ptr TEXTPTR Textstring FROMTextdemoWHEREItem 2READTEXTTextdemo Textstring ptr020HOLDLOCK执行结果为 TextstringEducationismoreal 5 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执行结果为 ItemTextstring1Ithinkso 5 3 2用于大文本和图像数据处理的函数和语句 5 通过指针修改大文本和图像列 格式 UPDATETEXT WITHLOG 功能 1 若不给出和 则从处开始删除 所指数据的个字符 2 若给出了或 则将数据源插入到所指数据的第个字符位置处 插入的数据可直接给出 也可通过 给出 此时和各种组合的含义见下表 5 3 2用于大文本和图像数据处理的函数和语句 例 在Textdemo表中删除Item为2的元组的Textstring列的数据片断 这个片断从位置2开始 长度为10个字符 DECLARE ptrVARCHAR 16 SELECT ptr TEXTPTR Textstring FROMTextdemoWHEREItem 2UPDATETEXTTextdemo Textstring ptr210SELECT FROMTextdemoWHEREItem 2执行结果为 ItemTextstring2Edmoreall inclusivethanschooling 5 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 2ORItem 3执行结果为 ItemTextstring2Edmoreall inclusivethanschooling3Edmoreall inclusivethanschoolingEducationis 5 3 2用于大文本和图像数据处理的函数和语句 准备工作 在库Im Test中建立一张表Im Info 此表中有两个字段 分别为Pr Id INT Pr Info IMAGE 用来存储图形编号及图形信息 其语法如下 CREATETABLEIm Info Pr IdINTNULL Pr InfoIMAGENULL 第一步 往表中插入一条记录 并初始化PR INFO字段 其语法如下 INSERTINTOIm InfoVALUES 1 0 xFFFFFFFF 第二步往表中写入图形信息 其语法如下 DECLARE ptrvalvarbinary 16 SELECT ptrval TEXTPTR Pr Info FROMIm InfoWHEREPr Id 1WRITETEXTIm Info pr info ptrval0 x624fd543fd其中0 x624fd543fd 为图形的十六进制数据 可以通过C Java等工具获得 注意在写入图形信息前必须先将此数据库的 selectinto bulkcopy 属性设置为True 其语法如下 usemasterexecsp dboptionIm Test selectinto bulkcopy True protectedvoidButton1 Click objectsender EventArgse 将图片转化为字节流byte content FileToByte FileUpload1 PostedFile FileName 保存字节数组到数据