连接查询(多表查询)
在很多情况下,查询要求从两个或更多的表中取得数据。若一个查询同时涉及两个以上的表,则称之为连接查询。连接查询中用来连接两个表的条件称为连接条件或连接谓语。
从概念上讲,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