第6章 SQL高级应用.docx
《第6章 SQL高级应用.docx》由会员分享，可在线阅读，更多相关《第6章 SQL高级应用.docx（30页珍藏版）》请在冰豆网上搜索。
第6章SQL高级应用
第6章SQL高级应用
关键字：
高级查询 管理检索数据 管理修改数据 事务处理 锁定 游标
学习要求：
本章主要阐述了Transact-SQL的高级查询语句，事务处理、游标和数据锁定的概念。
并且全面地、系统地介绍了数据汇总、联接查询、子查询；处理长ntext、text和image数据的方法；事务的概念、事务的特性、事务的类型；数据锁定的方法；游标的概念、游标的使用。
重点分析了Transact-SQL的高级查询语句，事务处理、游标和数据锁定的概念。
学习和掌握本章，是对SQL Server 2000数据库的灵活运用。
6.1SELECT高级查询
6.1.1数据汇总
１、COMPUTE子句
COMPUTE子句用于生成统计结果，该结果出现在查询结果的最后。
格式：
COMPUTE {{AVG|MAX|MIN|STDEV|VAR|SUM}（expression）}[，...n] [BY expression [，...n] ]
●AVG|MAX|MIN|STDEV|VAR|SUM：
指定要执行的聚合函数。
AVG：
计算一个数值型列的平均值。
MAX：
计算指定列中的最大值。
MIN：
计算指定列中的最小值。
STDEV：
计算统计标准偏差。
VAR：
统计方差。
SUM：
计算指定列中的数值总和。
●expression：
指定需要执行计算的列名。
●BY expression：
在结果内生成控制中断和分类汇总。
SELECT sno，cno，degree
FROM score
WHERE sno IN（‘103’，’105’）
ORDER BY sno -- 结果按sno升序排序输出
COMPUTE sum（degree）
●功能：
查询学号为103和105学生选修的所有课程号和成绩，按学号升序排序输出，最后统计满足条件记录的成绩总和。
执行结果如下：
sno cno degree
----- ---------- --------------------
103 3-105 92
103 3-245 86
105 3-105 88
105 3-245 75
sum
========================================
341
SELECT sno，cno，degree
FROM score
WHERE sno IN（‘103’，’105’）
ORDER BY sno
COMPUTE sum（degree） BY sno
●功能：
按学号分组汇总成绩。
执行结果如下：
sno cno degree
----- ---------- -------------------
103 3-105 92
103 3-245 86
sum
=======================================
178
sno cno degree
----- ---------- -------------------
105 3-105 88
105 3-245 75
sum
=======================================
163
２、GROUP BY 子句
将查询结果分组。
格式：
[GROUP BY [ALL] group_by_eapression [,...n] [WITH {CUBE|ROLLUP}] ]
●ALL：
包含所有的组和结果。
●group_by_eapression：
执行分组的表达式。
●CUBE：
在查询结果内不仅包含由GROUP BY提供的正常行，还包含汇总行。
●ROLLUP：
在查询结果内不仅包含由GROUP BY提供的正常行，还包含汇总行。
按层次结构顺序，从组内的最低级别到最高级别汇总组。
USE school
GO
SELECT student.class AS ‘班号’，ame AS ‘课程名’，AVG（score.degree） AS ‘平均分’
FROM student，course，score
WHERE student.sno=score.sno AND o=o
GROUP BY student.class，ame
功能：
统计各班各课程的平均分。
执行结果如下：
班号 课程 平均分
----- ---------------- ---------------
95031 操作系统 76.333333
95031 计算机导论 85.333333
95033 计算机导论 77.666666
95033 数字电路 81.666666
USE school
GO
SELECT student.class as ‘班号’，ame AS ‘课程’，AVG（score.degree） AS ‘平均分’
FROM student，course，score
WHERE student.sno=score.sno AND o=o
GROUP BY student.class，ame WITH CUBE
●功能：
统计各班各课程的平均分。
●注意：
查询的结果还包含汇总行。
执行结果如下：
班号 课程 平均分
----- ---------------- -------------------
95031 操作系统 76.333333
95031 计算机导论 85.333333
95031 NULL 80.833333
95033 计算机导论 77.666666
95033 数字电路 81.666666
95033 NULL 79.666666
NULL NULL 80.250000
NULL 操作系统 76.333333
NULL 计算机导论 81.500000
NULL 数字电路 81.666666
USE school
GO
SELECT student.class AS ‘班号’， AVG（score.degree） AS ‘平均分’
FROM student，course，score
WHERE student.sno=score.sno
GROUP BY student.class WITH ROLLUP
●功能：
统计各班平均分。
●WITH ROLLUP：
按GROUP BY后的第一个字段做汇总。
执行结果如下：
班号 平均分
----- ----------------
95031 80.833333
95033 79.666666
NULL 80.250000
6.1.2联接查询
通过联接，可从两个或多个表中检索数据。
SQL Server 2000可以在FROM子句中指定联接条件，这有助于将这些联接条件与WHERE子句中可能指定的其他条件分开。
格式：
FROM first_table join_type second_table
[ON （join_condition）]
例：
使用联接查询各课程的任课教师姓名。
USE school
GO
SELECT ame,teacher.tname
FROM course JOIN teacher ON （course.tno=teacher.tno）
GO
１、内联接（INNER JOIN）
返回参与联接的数据表中所有匹配的行。
例：
查询各课程的任课教师姓名。
USE school
GO
SELECT ame,teacher.tname
FROM course INNER JOIN teacher ON（course.tno=teacher.tno）
２、外联接
（1）左向外联接（左联接）（LEFT [OUTER]JOIN）
返回参与联接的数据表中所有匹配的行和所有来自左表的不符合指定条件的行，在新增的属性上填NULL值。
例：
USE school
GO
INSERT course VALUES（‘8-166’，’高等数学’，’888’）
GO
SELECT ame,teacher.tname
FROM course LEFT JOIN teacher ON（course.tno=teacher.tno）
DELETE course WHERE cno=‘8-166’
GO
功能：
查询各课程的任课教师姓名以及没有任课教师的课程。
左向外联接操作执行结果：
cname tname
---------------- --------
计算机导论 王萍
操作系统 李诚
数字电路 张旭
高等数学 NULL
（2）右向外联接（右联接）（RIGHT [OUTER]JOIN）
返回参与联接的数据表中所有匹配的行和所有来自右表的不符合指定条件的行，在新增的属性上填NULL值。
例：
USE school
GO
INSERT course VALUES（‘8-166’，’高等数学’，’888’）
GO
SELECT ame，teacher.tname
FROM course RIGHT JOIN teacher ON（course.tno=teacher.tno）
DELETE course WHERE cno=‘8-166’
GO
功能：
查询各课程的任课教师姓名以及没有承担课程的教师。
右向外联接操作执行结果：
cname tname
---------------- --------
操作系统 李诚
计算机导论 王萍
NULL 刘冰
数字电路 张旭
（3）完全外部联接（FULL [OUTER]JOIN）
返回参与联接的数据表中所有匹配的行和所有来自左、右表的不符合指定条件的行，在新增的属性上填NULL值。
例：
USE school
GO
INSERT course VALUES（‘8-166’，’高等数学’，’888’）
GO
SELECT ame，teacher.tname
FROM course FULL JOIN teacher ON（course.tno=teacher.tno）
DELETE course WHERE cno=‘8-166’
GO
功能：
查询各课程的任课教师姓名以及没有任课教师的课程和没有承担课程的教师。
完全外联接操作执行结果：
cname tname
---------------- --------
操作系统 李诚
计算机导论 王萍
NULL 刘冰
数字电路 张旭
高等数学 NULL
３、交叉联接（CROSS JOIN）
即进行两个表的笛卡尔积运算。
例：
USE school
GO
INSERT course VALUES（‘8-166’，’高等数学’，’888’）
GO
SELECT ame,teacher.tname
FROM course CROSS JOIN teacher
DELETE course WHERE cno=‘8-166’
GO
交叉联接操作执行结果：
cname tname
---------------- --------
计算机导论 李诚
操作系统 李诚
数字电路 李诚
高等数学 李诚
计算机导论 王萍
操作系统 王萍
数字电路 王萍
高等数学 王萍
计算机导论 刘冰
操作系统 刘冰
数字电路 刘冰
高等数学 刘冰
计算机导论 张旭
操作系统 张旭
数字电路 张旭
高等数学 张旭
6.1.3子查询
子查询是一个SELECT查询，它可以嵌套在SELECT、INSERT、UPDATE、DELETE语句的WHERE或HAVING子句内，或嵌套在其他子查询中。
最多可嵌套32层。
子查询的执行过程是：
首先执行内部查询，它查询出来的数据并不被显示出来，而是传递给外层语句，并作为外层语句的查询条件来使用。
子查询的SELECT查询总是用圆括号括起来。
例：
查询“操作系统”课程的任课教师。
方法一：
使用子查询实现。
USE school
GO
SELECT tname
FROM teacher
WHERE tno=
（ SELECT tno
FROM course
WHERE cname=‘操作系统’）
GO
方法二：
使用联接查询实现。
USE school
GO
SELECT tercher.tname
FROM teacher JOIN course ON （teacher.tno=course.tno）
WHERE ame=’操作系统’
GO
注意：
方法一的效率更高。
１、使用 IN 或 NOT IN 实现的子查询
例：
查询选修“6-166”课程号的学生名单。
USE school
GO
SELECT sno，sname
FROM student
WHERE sno IN
（ SELECT sno
FROM score
WHERE cno=‘6-166’）
２、UPDATE、DELETE和INSERT语句中的子查询
例：
删除没有被选修的课程。
USE school
GO
INSERT course VALUES（‘8-166’，’’，’888’）
GO
DELETE course
WHERE cno NOT IN
（ SELECT cno
FROM score ）
GO
３、比较运算符的子查询
例：
查询成绩高于平均分的成绩记录。
USE school
GO
SELECT sno，cno，degree
FROM score
WHERE degree >
（SELECT AVG（degree）
FROM score）
４、带ANY、ALL谓词的子查询
ANY：
与子查询结果中的某个值满足关系
ALL：
与子查询结果中的所有值满足关系
注意：
ANY、ALL必须与关系比较符同时使用。
例1：
查询其他班比95031班某学生年龄小的学生名、班级。
SELECT sname，class
FROM student
WHERE year（getdate（））-year（sbirthday） （ SELECT year（getdate（））-year（sbirthday）
FROM student
WHERE class=‘95031’）
AND class<>‘95031’
例2：
查平均成绩最高的学生号。
５、多层嵌套
例：
查询最高分的学生姓名。
USE school
GO
SELECT sname
FROM student
WHERE sno=
（SELECT sno
FROM score
WHERE degree=
（SELECT MAX（degree）
FROM score）
）
GO
6.1.4在查询的基础上创建新表
在SELECT子句中使用INTO关键字可以创建新表并将结果行从查询插入新表中。
例：
USE school
GO
SELECT student.sno，student.sname，ame，score.degree
INTO score1
FROM student，course，score
WHERE student.sno=score.sno AND o=o
GO
SELECT * FROM score1
GO
功能：
将查询得到学生的学号、姓名、课程名和分数插入到新建的表score1中，再显示该新表的记录。
6.2管理ntext、text和image数据
引言
如果ntext、text和image数据值不超过Unicode串、字符串或二进制串的长度（分别为4000个字符、8000个字符和8000个字节），就可以在SELECT、UPDATE和INSERT语句中引用它们，其引用方式与较小的数据类型相同。
如果ntext、text和image数据值较大，则必须逐块处理。
在SQL Server 2000中，用户可以在表上启用text in row选项，以使该表能够在其数据行中存储ntext、text或image数据。
可以使用sp_tableoption存储过程来启用。
例：
在test数据库中创建一个table2表，其中c2字段的数据类型为text，并插入一笔记录：
USE test
GO
CREATE TABLE table2（c1 int，c2 text）
EXEC sp_tableoption ’table2’，’text in row’，’on’
INSERT table2 VALUES（1，’This is a text’）
GO
可以通过下面的方式来检索ntext、text或image值：
6.2.1检索ntext、texta或image值
１、在SELECT语句中引用该列
原理：
将该列绑定到一个程序变量上，然后使用特殊的API函数或方法逐块检索数据。
注意：
如果数据的长度大于SET TEXTSIZE中指定的长度，则必须增大TEXTSIZE或使用其他方法。
在全局变量@@TEXTSIZE中存储了当前的TEXTSIZE设置。
SET TEXTSIZE：
可修改TEXTSIZE 设置。
例1：
SET TEXTSIZE 64512 --将TEXTSIZE设置为64512字节
例2：
SET TEXTSIZE 0 --设为默认值4096字节（4KB）
２、使用TEXTPTR函数获得传递给READTEXT语句的文本指针
格式：
READTEXT {table.column text_ptr offset size} [HOLDLOCK]
其中各参数含义为：
●table.column。
是从中读取的表和列的名称。
●text_ptr。
有效文本指针。
●offset。
开始读取text、image或ntext数据之前跳过的字节数。
●size。
是要读取数据的字节数（使用text或image数据类型时）或字符数（使用ntext数据类型时）。
●HOLDLOCK。
使文本值一直锁定到事务结束。
●功能：
读取ntext、text或image数据块。
例：
读取table2表的c2字段的第1到第7个字符。
--对text数据类型的对象使用指针前，应关闭text in row选项
EXEC sp_tableoption ‘table2’，‘text in row’，‘off’
DECLARE @ptrval varbinary（16）
--获得指向文本的指针
SELECT @ptrval=TEXTPTR（c2） FROM table2
--从指向文本的指针处读取table2表的c2列的7个字节
READTEXT table2.c2 @ptrval 0 7
３、使用SUBSTRING函数检索从列开头特定偏移位置开始的数据块
格式：
SUBSTRING（expression，start，length）
其中各参数含义如下：
express
升级会员 