页数：40页文件大小：712.2 KB来源： SQLServer2005数据库技术及应用ppt课件
本章深入T-SQL高级查询，先分析多表查询中的笛卡儿乘积问题与连接条件数量的规定，介绍表别名与UNION联合运算，随后讲解等值连接、自然连接、自连接、交叉连接与外连接，以及统计函数、GROUP BY与HAVING、COMPUTE和COMPUTE BY的用法，最后通过带IN、EXISTS、NOT IN的子查询示例说明嵌套查询。
第11章T-SQL高级查询
第11章T-SQL高级查询
11.1多表查询1多表查询和笛卡儿乘积前面讨论的查询，大多还局限在一张表内。但在更多的情况下,需要对多张表中的数据同时进行查询。多表查询就是在FROM子句中出现多张表的情形。下面的SQL语句实现同时对两张表进行的查询:
USE JWGL GO SELECT name,grade FROM student,result这个查询的目的是查询每个学生的成绩,查询结果如下:11.1多表查询
11.1多表查询
11.1多表查询【例11.1】还是利用上述同一个例子,查询每个学生的成绩，为了实现查询目标,应该使用WHERE子句来给出查询连接条件，实现查询。SELECT name,course_id,gradeFROM student,resultWHERE student.student_id= result.student_id这时,返回了26条记录，查询结果是正确的。一般说来，为了有效避免笛卡儿乘积的出现,对所定义的查询连接条件的数目是有规定的:如果有N个表出现在FROM的后面,那么,所定义的连接条件不得少于N-1个。11.1多表查询2使用表的别名
如11.1的例子可以改写为:SELECT name,course_id,gradeFROM student s,result rWHERE s.student_id=.student_id 也可以按照ANSI92标准使用AS关键字来使用别名,如上面的查询可以更改为：SELECT name,course_id,gradeFROM student as s,result as rWHERE s.student_id=.student_id 使用表的别名时，首先应在SELECT查询语句的FROM子句中指定表的别名。11.1多表查询3使用联合运算UNION子句
11.1多表查询3使用联合运算UNION子句【例11.2】使用UNION语句联合对学生表（Student）和教师表（Teacher）的查询,列出学生和教师的姓名和出生日期。SELECT name,birthdayFROM studentUNIONSELECT name,birthdayFROM teacher联合查询的结果列标题为第一个查询的列标题，如果要排序，也必须使用第一个查询语句中的列名。要对UNION操作的数据集结果进行排序,必须把ORDER BY子句写在最后的SELECT查询语句后面。11.2连接SQL Server中的连接主要是为了实现多表查询。这是SQL Server作为一种关系数据库的一个体现。在关系数据库中，常把一个实体的所有信息存储到一个表中，当需要检索信息的时候，可以通过操作查询出存放在不同表中的不同实体信息。最后得出比较详细的信息。连接的主要类型有：
11.2连接1等值连接和自然连接等值连接和自然连接，在WHERE子句中使用比较运算符。它们的区别主要是等值连接的查询结果将列出所连接表中的所有列（包括重复列）。自然连接的选择列表中删除被连接表的重复列。【例11.3】在查询学生成绩采用等值连接时，查询结果列出所连接表中的所有列。SELECT *FROM student,resultWHERE student.student_id= result.student_id11.2连接【例11.4】在查询学生成绩采用自然连接时，查询结果列出选择列表中删除被连接表的重复列。SELECT *FROM student,result.*WHERE student.student_id= result.student_id11.2连接2不等值连接与自连接
11.2连接3 JOIN关键字连接也可以使用JOIN关键字，例如，要查看学生成绩，同时显示学生姓名和课程名，可以使用下面的语句：SELECT s.name，c.name，r.gradeFROM student sJOIN result r ON s.student_id=r. student_idJOIN course c ON c.course_id=r. course_id11.2连接【例11.5】一个交叉连接的例子。如果没有WHERE子句，交叉连接将产生两个表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。如果添加一个WHERE子句，则交叉连接的作用将同内联接一样。
SELECT s.name，r.gradeFROM student s cross JOIN result r SELECT s.name，r.gradeFROM student s cross JOIN result rWhere s.student_id=r. student_i11.2连接内连接是保证两个表中所有的行都要满足连接条件，而外连接会列出FROM子句中提到的至少一个表的所有行，只要这些行符合任何WHERE或HAVING搜索条件。外连接包括以下三种形式：
11.2连接【例11.6】“教师选课”查询是一个左外连接的例子。该查询用到三张表，分别是Teacher、teaching、Course表，Teacher是teaching的左表，Course是teaching的右表，表间两两左外连接。SELECT teacher.teacher_id教师编号, teacher.name教师姓名,course.name课程名称, course.class_time课时FROM teaching LEFT OUTER JOINCourse ON teaching.course_id=course..course_id LEFT OUTER JOINTeacher ON teaching. teacher_id= teacher. teacher_id常用的统计函数
| 函数名 | 函数功能 |
|---|---|
| SUM | 返回数值列中所有值的总和 |
| AVG | 返回数值列中所有值的平均值 |
| MIN | 返回数值、字符、日期列中的最小值 |
| MAX | 返回数值、字符、日期列中的最大值 |
| COUNT | 返回列中满足条件的行数 |
| COUNT(*) | 返回表中的行数 |
11.3使用统计函数为了使用这些统计函数,首先执行一条SELECT语句,其中含有按统计规则能够计算的列或表达式。下面的例子将返回教师的平均工资:USE JWGLGOSELECT avg(salary)FROM teacher11.3使用统计函数【例11.7】查询出teacher表中年龄最大和最小的教师的记录。查询程序如下：SELECT YEAR(GETDATE())-YEAR(MIN(birthday)) AS最大年龄FROM teacherSELECT YEAR(GETDATE())-YEAR(MAX(birthday)) AS最小年龄FROM teacher（以上两个查询可合并为一个查询）11.3使用统计函数2与统计函数一起使用WHERE子句通过结合WHERE子句来使用统计函数,可以指定统计操作中应该包括哪些行。【例11.8】在student表中,查询出生日期为“1982-1-1”以后的学生数。SELECT COUNT(*)FROM studentWHERE birthday>’1982-1-1’程序中count(*)是一个非常特殊但又非常有用的函数。COUNT(*)函数不需要参数，因为该函数不使用有关任何特定列的信息。该函数计算符合查询限制条件的总行数。11.3使用统计函数3与统计函数一起使用DISTINCT关键字在T-SQL中,允许与统计函数一起使用DISTINCT关键字,来处理列或表达式中不同的值。【例11.9】求出teaher表教师的职称种类数、平均工资和工资总和。SELECT AVG(salary)平均工资,SUM (salary)总工资,COUNT(DISTINCT profession)职称种类数FROM teacher11.4使用GROUP BY子句进行分类汇总1 GROUP BY使用方法一般情况下使用统计函数,返回的是所有行数据的统计结果。如果需要按某一列数据的值进行分类,在分类的基础上再进行查询,就要用到GROUPBY子句了。可以使用GROUP BY子句和统计函数对查询结果进行统计。也可以同时使用GROUP BY子句和统计函数对数据库进行分组统计，在查询的结果集中，对每一组统计出一个结果。【例11.10】求出teaher表各种职称的教师人数、平均工资和工资总和。SELECT profession职称, AVG(salary)平均工资,SUM (salary)总工资,COUNT(profession)人数FROM teacherGROUP BY profession11.4使用GROUP BY子句进行分类汇总在GROUPBY子句中, SELECT后面每一列数据除了出现在统计函数中的列以外,都必须在GROUP BY子句中应用。【例11.11】一个错误的查询例子。SELECT后面每一列数据除了出现在统计函数中的列以外，还有两个列：sex,profession。而GROUP BY后只出现sex。SELECT profession职称, AVG(salary)平均工资,SUM (salary)总工资,COUNT(profession)人数FROM teacherGROUP BY sex11.4使用GROUP BY子句进行分类汇总在GROUPBY子句中, SELECT后面每一列数据除了出现在统计函数中的列以外,都必须在GROUP BY子句中应用。【例11.11】一个错误的查询例子。SELECT后面每一列数据除了出现在统计函数中的列以外，还有两个列：sex,profession。而GROUP BY后只出现sex。SELECT profession职称, AVG(salary)平均工资,SUM (salary)总工资,COUNT(profession)人数FROM teacherGROUP BY sex11.4使用GROUP BY子句进行分类汇总要修改上面的例子,需要在GROUP BY子句中加入所有的查询数据列。这时,统计函数按照这些列的惟一组合来进行统计计算。在下面这个修改过的例子中,服务器会针对性别（sex）和职称（profession）的每一种组合,返回一个性别、职称、平均工资、总工资和人数。SELECT profession职称, AVG(salary)平均工资,SUM (salary)总工资,COUNT(profession)人数FROM teacherGROUP BY sex, profession11.4使用GROUP BY子句进行分类汇总2使用HAVING关键字来筛选结果
11.4使用GROUP BY子句进行分类汇总【例11.12】先从teacher表按类统计不同职称教师的平均工资，在此基础上进一步筛选,找出所有平均工资超过3000元的职称。
SELECT profession职称, AVG (salary)平均工资FROM teacherGROUP BY profession SELECT profession职称, AVG (salary)平均工资FROM teacherGROUP BY professionHAVING AVG (salary)>3000ORDER BY AVG (salary)11.4使用GROUP BY子句进行分类汇总6使用COMPUTE和COMPUTE BY子句COMPUTE子句可以和统计函数一起实现对数据库的统计。这种统计不仅会显示统计结果，还会显示统计数据的详细细节。通过使用COMPUTE子句既可以计算数据分类后的和,也可以计算所有数据的总和。【例11.13】分别用COMPUTE和COMPUTE BY子句从teacher表按类统计不同职称教师的平均工资和总工资。
SELECT profession职称, salary工资FROM teacherCOMPUTE COMPUTE(salary) SUM(salary) SELECT profession职称, salary工资FROM teacherCOMPUTE COMPUTE(salary) SUM(salary)BYprofession11.4使用GROUP BY子句进行分类汇总这个例子中先用COMPUTE子句从teacher表统计所有职称教师的平均工资和总工资，没有BY关键字,返回的结果是在列出所有细节后，增添了一行表示所有教师的平均工资和总工资。使用了BY关键字以后，按类统计不同职称教师的平均工资和总工资，数据分组列出，每1组既有细节又有统计结果。COMPUTE子句和GROUP BY子句非常相似。但是,两者之间存在着较大的区别,具体体现在:
COMPUTE生成多个结果集。一类结果集包含每个组的明细行，其中包含选择列表中的表达式。另一类结果集包含组的子汇总统计，或SELECT语句的总的汇总统计。选择列表可包含除分组依据列或统计函数之外的其它表达式。统计函数在COMPUTE子句中指定，而不是在选择列表中。11.5使用嵌套的子查询1嵌套子查询的使用方法
11.5使用嵌套的子查询【例11.14】用带“IN”的查询语句列出任课教师的名单。SELECT name,professionFROM teacherWHERE teacher_id IN(SELECT teacher_id FROM teaching )本例查询进行的顺序是:首先服务器执行小括号中的子查询,返回的结果是所有选课的教师的编号（teacher_id）；然后服务器才开始执行外围查询,返回正确的结果。11.5使用嵌套的子查询当嵌套查询返回的结果只有一行结果时，WHERE子句中可用“=”来连接嵌套查询。但若嵌套查询返回的结果不止一行结果时，就不能用“=”来连接。否则系统出错。上面的例子如写为:SELECT name,professionFROM teacherWHERE teacher_id =(SELECT teacher_id FROM teaching)则系统会提示出错。除非非常明确嵌套查询只会返回一行结果，否则，在嵌套查询时用“IN”关键字。11.5使用嵌套的子查询2在嵌套子查询中使用EXISTS关键字使用EXISTS关键字同样可以实现例
11.14使用IN关键字实现的查询。【例
11.15】使用带有EXISTS的查询语句列出任课教师的名单。SELECT name,professionFROM teacherWHERE EXISTS(SELECT * FROM teachingWHERE teaching.teacher_id = teacher. teacher_id)11.5使用嵌套的子查询使用NOT EXISTS和NOT似关键字,可以很方便地解决非成员关系型和非存在关系型查询难题。【例11.16】用带“NOT IN”的查询语句列出未任课教师的名单。SELECT name,professionFROM teacherWHERE teacher_id NOT IN (SELECT teacher_id FROM teaching ) 本例查找出所有没有任课任务的教师名单。这个例子,如果使用NOT EXISTS关键字应该写为:SELECT name,professionFROM teacherWHERE NOT EXISTS (SELECT * FROM teachingWHERE teaching.teacher_id = teacher.teacher_id)11.5使用嵌套的子查询3在SELECT语句的列清单中使用子查询【例11.17】查询学生各门功课的成绩。在查询编辑器窗口输入如下语句：SELECT学生姓名=(SELECT name from student sWhere s.student id=r.student id),课程名称=( SELECT name from course cWhere c.course id=r.course_id),成绩=gradeFROM result rORDER BY课程名称11.5使用嵌套的子查询4用嵌套子查询更新、添加或删除表中的记录
子查询可以嵌套在外部SELECT、INSERT、UPDATE或DELETE语句的WHERE或HAVING子句内，或者其他子查询中。11.5使用嵌套的子查询【例11.18】请给教数据库的老师的工资提高20%。这里涉及三张表：teacher、teaching、course。用三层嵌套查询实现。程序如下：USE JWGLUPDATE teacherSET salary = salary *1.2WHERE teacher_id IN(SELECT teacher_idFROM teachingWHERE course_id =(select course_id FROM courseWHERE name= '数据库'))GO11.5使用嵌套的子查询【例11.19】将班级编号为“200708”的新生数据从新生数据库表student1添加到student表中。用嵌套查询实现。程序如下：INSERT INTO studentSELECT *FROM student1WHERE student_id IN(SELECT student_idFROM student1WHERE class_id = '200708')11.5使用嵌套的子查询【例11.20】将学生“李凡”的成绩从result表中删除。用嵌套查询实现，通过子查询查出李凡的学号，再通过主查询将含有李凡学号的记录全部删除。实现程序如下：DELETE resultWHERE student_id IN(SELECT student_idFROM studentWHERE name= '李凡')下载地址：{{downurl}}
{{{a}}}
{{#b}}{{{b}}}
{{/b}} {{/title}} {{#nolog}}登陆注册
{{/nolog}} {{#djsx}}点我刷新
{{/djsx}} {{#gocz}} {{/gocz}} {{#ffqr}} {{/ffqr}}