多表查询

连接查询（多表查询）

在很多情况下，查询要求从两个或更多的表中取得数据。若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询中用来连接两个表的条件称为连接条件或连接谓语。

从概念上讲，DBMS 执行连接操作的过程是：首先在表1 找到第一条记录，然后从头开始扫描表2 ，逐一查找满足条件的记录，找到后就将表1 中的第一条记录和该记录拼接起来，形成结果表中的第一条记录，之后再接着查找和拼接，直到表2 全部查找完成。再找表1 中的第2 条记录，然后再从头开始扫描表2 ，找到满足条件的记录后进行拼接。重复上述的操作，直到表1 中的全部记录都处理完毕为止。

stu_info( 学生基本信息表) 、result_info( 学生成绩表) 、course_info( 课程信息表)  

1 、查询每个学生的基本情况和每门课程的成绩。

select stu_info.* , result_info.* from stu_info , result_info where  stu_info.stu_id = result_info.stu_id

        学生情况放在stu_info 表中，学生成绩存放在result_info 表中，所以本例中的查找实际上涉及两个表。这两个表之间的联系是通过公共字段stu_id （学号）实现的。

2 、查询每个学生每门课程的成绩，结果表中要显示course_info( 课程信息表) 中的课程名称等内容。

   select stu_info.* ,course_info.* ,  result_info.* from stu_info , course_info, result_info where stu_info.stu_id = result_info.stu_id and result_info.course_no = course_info.course_no

   本例涉及三个数据表的内容，其中stu_info 表与result_info 表通过stu_id( 学号) 字段进行连接，result_info 表和course_info 表通过course_no （课程编号）字段进行连接。

自然连接

在上面的两个实例中，显示了多个表中的所有字段，包括重复的字段。实际使用中，有些字段是无须显示的，而有些字段只需要显示一次。

在等值连接中去除结果表中相同的字段名称为自然连接。在自然连接的各种表达式中，往往是把各表的主键和外键进行等值连接。

例如：查询每个学生每门课程的成绩，结果表那个只显示学号、姓名、学期编号、课程编号和成绩几个字段。

select stu_info.stu_id,name,ter_id,name,term_no,course_no,result from stu_info,result_info where stu_info.stu_id = result_info.stu_id         

例如：查询每个学生每门课程的成绩，结果表那个只显示学号、姓名、课程名和成绩几个字段。

select stu_info.stu_id as 学号,name  as x 姓名,course_name as 课程名称result as 成绩 from stu_info,result_info where stu_info.stu_id = result_info.stu_id  and result_info.course = course_info.course_no

自身连接查询

有时在查询中需要对相同的表进行连接，即同一个表与其自身进行连接，成为自身连接。在自身连接中，为了区别相同的表，需要对一个表适用两个表名。

例如，在result_info （学生成绩表）中进行查询，结果表中显示与学号为2007001 的学生修读同一门课程的学生的学号、课程编号以及成绩。

Select  r2.stu_id ,r2.course_no, r2.result from result_info as r1, result_info as r2 where r1.stu_id = ’2007001 ’ and r1.course_no = r2.course_no and r2.stu_id<> ’2007001 ’

在where 子句中，共有三个条件，第一个条件是指表r1 中查找学号为2007001 的记录，第二个条件是指表r1 和表r2 的课程编号应当相同，第三个条件是指表r2 中排除学号为2007001 的记录。

外连接查询

在一般的连接操作中，只有满足连接条件的记录才能作为结果输出。如果希望以stu_info 表为主体列出每个学生的基本情况及其选课请情况，若某个学生没有选课，则只显示其基本情况信息，其选课信息为空值，这是就需要外连接。

利用外连接，可以使结果表中不仅包含满足条件的记录，还包含指定表中的所有记录。外连接共有三种形式。

（1 ）            左向外连接（left outer join ）：即结果表中除了满足连接条件的记录，还包括jion 子句左边表中的所有行。

（2 ）            右向外连接（right outer join ）：即结果表中除了满足连接条件的记录，还包括jion 子句右边表中的所有行。

（3 ）            完整外连接（full outer join ）：即结果表中除了满足连接条件的记录，还包括两个表中的所有行。

外连接的一般格式为：

select < 字段列表> from < 左表> <join 子句> < 右表> on < 外连接条件>

例如：查询显示每个学生的学号、姓名、性别等基本信息及其选修课程的课程编号和成绩，如果某个学生没有选修课程，则只显示其基本信息。

Select stu_info.stu_id , name , sex , course_no , result from stu_info left outer  join  result_info  on  stu_info.stu_id = result_info.stu_id

嵌套查询

在SQL 语言中，一个select from where 语句称为一个查询块。将一个查询块嵌套在另一个查询块的where 子句或having 子句的条件中的查询称为嵌套查询。

嵌套查询一般执行的是由里向外的，即每个子查询在其上级查询处理之前执行，子查询的结果用于建立其子查询的查询条件。嵌套查询使用多个简单的查询组合成复杂的查询，从而增强SQL 的查询能力。

1 、带有in 的子查询

In 子查询用于在子查询的结果集合中查找是否存在指定值

例如，查询选修了课程编号为001 的课程的学生的基本信息

select * from stu_info where stu_id  in ( select stu_id from result_info where course_no = ‘001 ’)

也可以用等值连接

Select stu_info.* from stu_info , result­_info where stu_info.stu_id = result_info.stu_id and course_no = ‘001 ’

例如 查询选修了课程名称为“高等数学”的学生的学号、姓名和班级编号。

select stu_id as 学号 , name as 姓名, class_id as 班级编号 from stu_info  where  stu_id in (select stu_id from result_info where course_no in ( select course_no from course_info where course_name = ‘高等数学’))

2 、带有比较运算符的子查询

例如、查询与“张三”同班的学生信息

Select * from stu_info where class_id = ( select class_id from stu_info where name = ‘张三’)

Select * from stu_info where ( select class_id from stu_info where name = ‘张三’) = class_id （错误的）

3 、带有any 或all 的子查询

All 是指表达式要与子查询的结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回true 。

Any 是指表达式只要与子查询的结果集中的某个值满足比较的关系时，就返回true

例如，查询比学号2007001 的学生的所有成绩都低的学生成绩信息。

Select * from result_info where result < all (select result from result_info where stu_id = ‘2007001 ’)

例如，查询比学号为2007001 的学生的某一科成绩低的学生成绩信息。

Select * from result_info where result < any (select result from result_info where stu_id = ‘2007001 ’)

4 、带有exists 的子查询

使用exists 测试子查询的结果是否为空表，若不为空，则返回true

例如，查询选修了课程编号为001 的学生的学号、姓名和班级编号

Select  stu_id , name , class_id from stu_info where exists ( slelect * from result_info where stu_id = stu_info.stu_id and course_no = ‘001 ’)

5 、带有union 的集合查询

使用union 运算符可以将两个或多个select 语句的结果组合成一个结果集。使用union 将多个查询结果合并起来时，系统会自动去掉重复记录。需要注意的是参加union 操作的各结果表的列数必须相同。对应项的数据类型也必须相同。

查询选修了课程编号为001 和002 的学生的成绩信息。

Select  stu_id ,course_no,result from resule_info where course_no = ‘001 ’        union 

Select  stu_id ,course_no,result from resule_info where course_no = ‘001 ’        union                                                               Select  stu_id ,course_no,result from resule_info where course_no = ‘002 ’

6 、在查询的基础上创建新表

在SQL Server 中，提供了into 子句，可以将结果表中的数据插入到一个新的数据表中，新表的字段名、字段的顺序、字段的数据类型都有select 的列表确定。

例：在三个数据表进行查询，得到的学生学号、姓名、课程名称、课程成绩四个字段和相应的记录，将结果保存到新表stu_result 中。

Select  stu_info.stu_id, name, course_name, result into into stu_result from stu_info, result_info, course_info where stu_info.stu_id = result_info.stu_id and result_info.course_no = course_info.course_no

利用 into 子句，可以为原表创建一个空的副表，只保留原表的结构，而不保留原表的任何记录。

例：为stu_info 表创建一个空的副本。

Select * into stu_backup from stu_info where 0

利用into 子句，还可以创建一个临时表，临时表的使用方法与基本表完全相同，不过，临时表种的内容部在数据库中并没有保存，退出SQL server 后，其自动消失。

例：为stu_info 表创建一个临时表。

Select * into #temp from stu_info

查询中的统计计算

例1 、统计stu_info （学生基本信息表）中学生的总人数。

Select count （* ） as 总人数 from stu_info

例2 、统计result_info( 学生成绩表) 中有成绩的学生人数。

Select count （distinct stu_id ） as 有成绩的人数 from result_info

例3 、计算课程编号为001 的课程学生的平均成绩

Select  avg  ( result ) as 课程001 的平均成绩 from result_info where course_no = ’001 ’

例4 、查询姓名为“张三”的学生在第01 学期的最高分

Select Max （result ） as 张三在第01 学期的最高分 from result_info, stu_info where name = ’张三‘ and term_no = ’01 ’ and result_info.stu_id = stu_info.stu_id

转载地址 http://blog.sina.com.cn/s/blog_5c52ae1b0100nlgn.html