资源描述
, , , , , ,*,SQL Server 2005,第,11,章,T-SQL,高级查询,第,11,章,T-SQL,高级查询,11.1,多表查询,11.2,连接,11.3,使用统计函数,11.4,使用,GROUP BY,子句进行分类汇总,11.5,使用,COMPUTE,和,COMPUTE BY,子句,11.6,使用嵌套的子查询,11.1,多表查询,1,多表查询和笛卡儿乘积,前面讨论的查询，大多还局限在一张表内。但在更多的情况下,需要对多张表中的数据同时进行查询。多表查询就是在,FROM,子句中出现多张表的情形。,下面的,SQL,语句实现同时对两张表进行的查询,:,USE JWGL,GO,SELECT,name,grade,FROM,student,result,这个查询的目的是查询每个学生的成绩,查询结果如下,:,陈红,85,陈红,75,陈红,65,陈红,45,11.1,多表查询,李凡,60,李凡,75,李凡,99,(,所影响的行数为,234,行,),这个查询一共返回了,234,个结果,显然,这是不正确的。问题出在对表连接条件的限制上。在上面的例子中,没有对表连接条件作任何限制,所以,SQL Server,在,student,表中每取出一条数据,就从,result,表中取出一条数据与前者组合成一条记录。在,student,中有,9,条记录,在,result,表中有,26,条记录,所以,一共返回了,234,条记录。,11.1,多表查询,这种情况在数据库理论中被称为笛卡儿乘积。,笛卡儿乘积返回的结果在大多数情况下是冗余、无用的,所以应该采取措施避免笛卡儿乘积的出现。,没有,WHERE,子句的连接将产生连接所涉及的表的笛卡尔积。,第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。,通过包含一条,WHERE,子句来给出查询连接条件,就可以有效避免出现笛卡儿乘积。,11.1,多表查询,【,例,11.1】,还是利用上述同一个例子,查询每个学生的成绩，为了实现查询目标,应该使用,WHERE,子句来给出查询连接条件，实现查询。,SELECT,name,course_id,grade,FROM,student,result,WHERE,student.student_id,=,result.student_id,这时,返回了,26,条记录，查询结果是正确的。,一般说来，为了有效避免笛卡儿乘积的出现,对所定义的查询连接条件的数目是有规定的,:,如果有,N,个表出现在,FROM,的后面,那么,所定义的连接条件不得少于,N-1,个。,11.1,多表查询,2,使用表的别名,在,SQL,语言中,允许对表使用别名,所以,可以用更简便明了的方法来书写上面的查询语句。,如,11.1,的例子可以改写为,:,SELECT,name,course_id,grade,FROM student,s,result,r,WHERE,s.student_id,=.,student_id,也可以按照,ANSI92,标准使用,AS,关键字来使用别名,如上面的查询可以更改为：,SELECT,name,course_id,grade,FROM student as,s,result,as r,WHERE,s.student_id,=.,student_id,使用表的别名时，首先应在,SELECT,查询语句的,FROM,子句中指定表的别名。,11.1,多表查询,3,使用联合运算,UNION,子句,如果有多个不同的查询结果数据集,但又希望将它们连接在一起,组成一组数据。,这组数据是这多个结果集的逻辑联合。在这种情况下,可以使用,UNION,子句。,它将两个或更多查询的结果组合为单个结果集，该结果集包含联合查询中的所有查询的全部行。,使用,UNION,组合两个查询的结果集的两个基本规则是：所有查询中的列数和列的顺序必须相同，并且数据类型必须兼容。,所谓兼容就是各列的数据类型不必精确地进行匹配,服务器会自动地实现不同长度字符串的转换,也可以在不同类型的数值之间实现相互转换。但是,服务器不会把数值转换为字符串,也不会把日期类型转换为数值类型。,11.1,多表查询,使用联合运算,UNION,子句,【,例,11.2】,使用,UNION,语句联合对学生表（,Student,）和教师表（,Teacher,）的查询,列出学生和教师的姓名和出生日期。,SELECT,name,birthday,FROM student,UNION,SELECT,name,birthday,FROM teacher,联合查询的结果列标题为第一个查询的列标题，如果要排序，也必须使用第一个查询语句中的列名。,要对,UNION,操作的数据集结果进行排序,必须把,ORDER BY,子句写在最后的,SELECT,查询语句后面。,11.2,连接,SQL Server,中的连接主要是为了实现多表查询。这是,SQL Server,作为一种关系数据库的一个体现。在关系数据库中，常把一个实体的所有信息存储到一个表中，当需要检索信息的时候，可以通过操作查询出存放在不同表中的不同实体信息。最后得出比较详细的信息。,连接的主要类型有：,等值连接（,EQUI JOINS,）,自然连接（,NATURAL JOINS,）,自连接（,SELF JOINS,）,外部连接（,OUTER JOINS,）,11.2,连接,1,等值连接和自然连接,等值连接和自然连接，在,WHERE,子句中使用比较运算符。它们的区别主要是等值连接的查询结果将列出所连接表中的所有列（包括重复列）。自然连接的选择列表中删除被连接表的重复列。,【,例,11.3】,在查询学生成绩采用等值连接时，查询结果列出所连接表中的所有列。,SELECT *,FROM,student,result,WHERE,student.student_id,=,result.student_id,11.2,连接,【,例,11.4】,在查询学生成绩采用自然连接时，查询结果列出选择列表中删除被连接表的重复列。,SELECT *,FROM,student,result,.*,WHERE,student.student_id,=,result.student_id,11.2,连接,2,不等值连接与自连接,不等值连接使用除了等于（,=,）运算符之外还包括其他比较运算符。这些运算符包括,=,!,等。使用方法类似于等值连接与自然连接。,自连接，指的是同一个表的相同列进行比较。,11.2,连接,3 JOIN,关键字,连接也可以使用,JOIN,关键字，例如，要查看学生成绩，同时显示学生姓名和课程名，可以使用下面的语句：,SELECT,s.name,，,c.name,，,r.grade,FROM student s,JOIN result r ON,s.student_id,=r.,student_id,JOIN course c ON,c.course_id,=r.,course_id,在这组语句中用到了,JOIN,和,ON,关键字。,JOIN,用于连接两个不同的表；,ON,用于给出这两个表之间的连接条件。,在进行表之间的连接时，可以使用三种连接关键字：,INNER JOIN,关键字，表示内连接，内连接可直接简写为,JOIN,关键字。结果集中仅包含满足条件的行。,CROSS JOIN,关键字，表示交叉连接，结果集中包含两个表中所有行的组合。,OUTER JOIN,关键字，表示外连接，结果集中既包含那些满足条件的行，还包含某些不满足条件的数据。,11.2,连接,【,例,11.5】,一个交叉连接的例子。如果没有,WHERE,子句，交叉连接将产生两个表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。如果添加一个,WHERE,子句，则交叉连接的作用将同内联接一样。,SELECT,s.name,，,r.grade,FROM student s cross JOIN result r,SELECT,s.name,，,r.grade,FROM student s cross JOIN result r,Where,s.student_id,=r.,student_i,11.2,连接,内连接是保证两个表中所有的行都要满足连接条件，而外连接会列出,FROM,子句中提到的至少一个表的所有行，只要这些行符合任何,WHERE,或,HAVING,搜索条件。外连接包括以下三种形式：,LEFT OUTER JOIN,， 左外连接，包括左表中的全部行。,RIGHT OUTER JOIN,，右外连接，包括右表中的全部行。,FULL OUTER JOIN,，完整外连接，包括左表和右表中所有不满足条件的行。,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 JOIN,Course ON,teaching.course_id,=,course.course_id,LEFT OUTER JOIN,Teacher ON teaching.,teacher_id,= teacher.,teacher_id,11.3,使用统计函数,常用统计函数,最常见的统计函数,函数名函数功能,SUM,（） 对一个数值列或计算列求和,AVG,（）对一个数值列或计算列求平均值,MIN,（）返回一个数值列或一个数值表达式的最小,MAX,（）返回一个数值列或一个数值表达式的最大值,COUNT,（）返回满足,SELECT,语句中指定的条件的记录数,COUNT,（*）返回指定表中行的总数，不带任何特定列的信,息,11.3,使用统计函数,为了使用这些统计函数,首先执行一条,SELECT,语句,其中含有按统计规则能够计算的列或表达式。,下面的例子将返回教师的平均工资,:,USE JWGL,GO,SELECT,avg(salary,),FROM teacher,11.3,使用统计函数,【,例,11.7】,查询出,teacher,表中年龄最大和最小的教师的记录。查询程序如下：,SELECT YEAR(GETDATE() -,YEAR(MIN(birthday,) AS,最大年龄,FROM teacher,SELECT YEAR(GETDATE() -,YEAR(MAX(birthday,) AS,最小年龄,FROM teacher,（以上两个查询可合并为一个查询）,11.3,使用统计函数,2,与统计函数一起使用,WHERE,子句,通过结合,WHERE,子句来使用统计函数,可以指定统计操作中应该包括哪些行。,【,例,11.8】,在,student,表中,查询出生日期为“,1982-1-1”,以后的学生数。,SELECT COUNT(*),FROM student,WHERE birthday1982-1-1,程序中,count(*),是一个非常特殊但又非常有用的函数。,COUNT(*),函数不需要参数，因为该函数不使用有关任何特定列的信息。该函数计算符合查询限制条件的总行数。,11.3,使用统计函数,3,与统计函数一起使用,DISTINCT,关键字,在,T-SQL,中,允许与统计函数一起使用,DISTINCT,关键字,来处理列或表达式中不同的值。,【,例,11.9】,求出,teaher,表教师的职称种类数、平均工资和工资总和。,SELECT,AVG(salary,),平均工资,SUM (salary),总工资,COUNT(DISTINCT profession),职称种类数,FROM teacher,11.4,使用,GROUP BY,子句进行分类汇总,1 GROUP BY,使用方法,一般情况下使用统计函数,返回的是所有行数据的统计结果。如果需要按某一列数据的值进行分类,在分类的基础上再进行查询,就要用到,GROUPBY,子句了。可以使用,GROUP BY,子句和统计函数对查询结果进行统计。也可以同时使用,GROUP BY,子句和统计函数对数据库进行分组统计，在查询的结果集中，对每一组统计出一个结果。,【,例,11.10】,求出,teaher,表各种职称的教师人数、平均工资和工资总和。,SELECT profession,职称,AVG(salary,),平均工资,SUM (salary),总工资,COUNT(profession,),人数,FROM teacher,GROUP BY profession,11.4,使用,GROUP BY,子句进行分类汇总,在,GROUPBY,子句中, SELECT,后面每一列数据除了出现在统计函数中的列以外,都必须在,GROUP BY,子句中应用。,【,例,11.11】,一个错误的查询例子。,SELECT,后面每一列数据除了出现在统计函数中的列以外，还有两个列：,sex,profession,。而,GROUP BY,后只出现,sex,。,SELECT profession,职称,AVG(salary,),平均工资,SUM (salary),总工资,COUNT(profession,),人数,FROM teacher,GROUP BY sex,11.4,使用,GROUP BY,子句进行分类汇总,在,GROUPBY,子句中, SELECT,后面每一列数据除了出现在统计函数中的列以外,都必须在,GROUP BY,子句中应用。,【,例,11.11】,一个错误的查询例子。,SELECT,后面每一列数据除了出现在统计函数中的列以外，还有两个列：,sex,profession,。而,GROUP BY,后只出现,sex,。,SELECT profession,职称,AVG(salary,),平均工资,SUM (salary),总工资,COUNT(profession,),人数,FROM teacher,GROUP BY sex,11.4,使用,GROUP BY,子句进行分类汇总,要修改上面的例子,需要在,GROUP BY,子句中加入所有的查询数据列。这时,统计函数按照这些列的惟一组合来进行统计计算。在下面这个修改过的例子中,服务器会针对性别（,sex,）和职称（,profession,）的每一种组合,返回一个性别、职称、平均工资、总工资和人数。,SELECT profession,职称,AVG(salary,),平均工资,SUM (salary),总工资,COUNT(profession,),人数,FROM teacher,GROUP BY sex, profession,11.4,使用,GROUP BY,子句进行分类汇总,2,使用,HAVING,关键字来筛选结果,当完成数据结果的查询和统计后,可以使用,HAVING,关键字来对查询和计算的结果作进一步的筛选。,WHERE,子句搜索条件在进行分组操作之前应用；而,HAVING,搜索条件在进行分组操作之后应用。,HAVING,语法与,WHERE,语法类似，但,HAVING,可以包含统计函数。,HAVING,子句可以引用选择列表中出现的任意项。,11.4,使用,GROUP BY,子句进行分类汇总,【,例,11.12】,先从,teacher,表按类统计不同职称教师的平均工资，在此基础上进一步筛选,找出所有平均工资超过,3000,元的职称。,SELECT profession,职称, AVG (salary),平均工资,FROM teacher,GROUP BY profession,SELECT profession,职称, AVG (salary),平均工资,FROM teacher,GROUP BY profession,HAVING AVG (salary)3000,ORDER BY AVG (salary),11.4,使用,GROUP BY,子句进行分类汇总,6,使用,COMPUTE,和,COMPUTE BY,子句,COMPUTE,子句可以和统计函数一起实现对数据库的统计。这种统计不仅会显示统计结果，还会显示统计数据的详细细节。通过使用,COMPUTE,子句既可以计算数据分类后的和,也可以计算所有数据的总和。,【,例,11.13】,分别用,COMPUTE,和,COMPUTE BY,子句从,teacher,表按类统计不同职称教师的平均工资和总工资。,SELECT profession,职称, salary,工资,FROM teacher,COMPUTE,COMPUTE(salary,),SUM(salary,),SELECT profession,职称, salary,工资,FROM teacher,COMPUTE,COMPUTE(salary,),SUM(salary,),BY,profession,11.4,使用,GROUP BY,子句进行分类汇总,这个例子中先用,COMPUTE,子句从,teacher,表统计所有职称教师的平均工资和总工资，没有,BY,关键字,返回的结果是在列出所有细节后，增添了一行表示所有教师的平均工资和总工资。使用了,BY,关键字以后，按类统计不同职称教师的平均工资和总工资，数据分组列出，每,1,组既有细节又有统计结果。,COMPUTE,子句和,GROUP BY,子句非常相似。但是,两者之间存在着较大的区别,具体体现在,:,GROUP BY,生成单个结果集。每个组都有一个只包含分组依据列和显示该组统计汇总行。选择列表只能包含分组依据列和统计函数进行统计计算的列。,COMPUTE,生成多个结果集。一类结果集包含每个组的明细行，其中包含选择列表中的表达式。另一类结果集包含组的子汇总统计，或,SELECT,语句的总的汇总统计。选择列表可包含除分组依据列或统计函数之外的其它表达式。统计函数在,COMPUTE,子句中指定，而不是在选择列表中。,11.5,使用嵌套的子查询,1,嵌套子查询的使用方法,在实际运用中,经常要应用到嵌套查询。,嵌套查询要求服务器在处理最终查询工作之前先生成一个结果,然后,根据当前的查询结果再进继续下面的查询工作。,子查询自身可以包括一个或多个子查询。,一个语句中可以嵌套任意数量的子查询。,嵌套查询只能放在小括号中。,11.5,使用嵌套的子查询,【,例,11.14】,用带“,IN”,的查询语句列出任课教师的名单。,SELECT,name,profession,FROM teacher,WHERE,teacher_id,IN,(SELECT,teacher_id,FROM teaching ),本例查询进行的顺序是,:,首先服务器执行小括号中的子查询,返回的结果是所有选课的教师的编号（,teacher_id,）；然后服务器才开始执行外围查询,返回正确的结果。,11.5,使用嵌套的子查询,当嵌套查询返回的结果只有一行结果时，,WHERE,子句中可用“,=”,来连接嵌套查询。但若嵌套查询返回的结果不止一行结果时，就不能用“,=”,来连接。否则系统出错。上面的例子如写为,:,SELECT,name,profession,FROM teacher,WHERE,teacher_id,=,(SELECT,teacher_id,FROM teaching),则系统会提示出错。除非非常明确嵌套查询只会返回一行结果，否则，在嵌套查询时用“,IN”,关键字。,11.5,使用嵌套的子查询,2,在嵌套子查询中使用,EXISTS,关键字使用,EXISTS,关键字同样可以实现例,11.14,使用,IN,关键字实现的查询。,【,例,11.15】,使用带有,EXISTS,的查询语句列出任课教师的名单。,SELECT,name,profession,FROM teacher,WHERE EXISTS,(SELECT * FROM teaching,WHERE,teaching.teacher_id,= teacher.,teacher_id,),11.5,使用嵌套的子查询,使用,NOT EXISTS,和,NOT,似关键字,可以很方便地解决非成员关系型和非存在关系型查询难题。,【,例,11.16】,用带“,NOT IN”,的查询语句列出未任课教师的名单。,SELECT,name,profession,FROM teacher,WHERE,teacher_id,NOT IN (,SELECT,teacher_id,FROM teaching ),本例查找出所有没有任课任务的教师名单。这个例子,如果使用,NOT EXISTS,关键字应该写为,:,SELECT,name,profession,FROM teacher,WHERE NOT EXISTS (,SELECT * FROM teaching,WHERE,teaching.teacher_id,=,teacher.teacher_id,),11.5,使用嵌套的子查询,在,SELECT,语句的列清单中使用子查询,【,例,11.17】,查询学生各门功课的成绩。,在查询编辑器窗口输入如下语句：,SELECT,学生姓名,=(SELECT name from student s,Where,s.student,id=,r.student,id),课程名称,=( SELECT name from course c,Where,c.course,id=,r.course_id,),成绩,=grade,FROM result r,ORDER BY,课程名称,11.5,使用嵌套的子查询,4,用嵌套子查询更新、添加或删除表中的记录,子查询可以嵌套在外部,SELECT,、,INSERT,、,UPDATE,或,DELETE,语句的,WHERE,或,HAVING,子句内，或者其他子查询中。,UPDATE,语句用来更改数据库表中的现有数据。,而,INSERT,语句可给表添加一个或多个新行。,DELETE,语句从表中删除行。,11.5,使用嵌套的子查询,【,例,11.18】,请给教数据库的老师的工资提高,20%,。这里涉及三张表：,teacher,、,teaching,、,course,。用三层嵌套查询实现。程序如下：,USE JWGL,UPDATE teacher,SET salary =,salary,*1.2,WHERE,teacher_id,IN,(SELECT,teacher_id,FROM teaching,WHERE,course_id,=(select,course_id,FROM course,WHERE name= ,数据库,),GO,11.5,使用嵌套的子查询,【,例,11.19】,将班级编号为“,200708”,的新生数据从新生数据库表,student1,添加到,student,表中。用嵌套查询实现。程序如下：,INSERT INTO student,SELECT *,FROM student1,WHERE,student_id,IN,(SELECT,student_id,FROM student1,WHERE,class_id,= 200708),11.5,使用嵌套的子查询,【,例,11.20】,将学生“李凡”的成绩从,result,表中删除。用嵌套查询实现，通过子查询查出李凡的学号，再通过主查询将含有李凡学号的记录全部删除。实现程序如下：,DELETE result,WHERE,student_id,IN,(SELECT,student_id,FROM student,WHERE name= ,李凡,),
展开阅读全文