多表查询
来源:互联网 发布:php自定义函数 编辑:程序博客网 时间:2024/05/17 22:09
连接查询(多表查询)
在很多情况下,查询要求从两个或更多的表中取得数据。若一个查询同时涉及两个以上的表,则称之为连接查询。连接查询中用来连接两个表的条件称为连接条件或连接谓语。
从概念上讲,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
- 关联查询(多表查询)
- 多表查询,嵌套查询
- 多表查询----连接查询
- 多表查询----合并查询
- 数据库操作,内外联查询,分组查询,嵌套查询,交叉查询,多表查询,语句小结。
- 数据库操作,内外联查询,分组查询,嵌套查询,交叉查询,多表查询,语句小结
- 数据库操作,内外联查询,分组查询,嵌套查询,交叉查询,多表查询,语句小结。
- 数据库操作,内外联查询,分组查询,嵌套查询,交叉查询,多表查询,语句小结。
- 内外联查询,分组查询,嵌套查询,交叉查询,多表查询,语句小结转
- 复杂Oracle查询 限定查询+多表查询+统计查询+子查询
- SQL单表查询、多表查询、嵌套查询
- sql 复杂查询 多表查询 分页查询
- Hibernate数据库查询中DetachedCriteria数据库查询多表查询
- oracle 多表查询,子查询,集合运算,分页查询
- sql 多表查询,3表查询
- MySQL单表查询多表查询
- SQL中的多表查询查询
- SQL查询语句《三》:多表查询
- Wi-Fi简介
- Linux 下 串口收发的常见问题
- pathmunge详解
- Intel Threading Building Blocks 之 并行循环(一)
- tomcat6配置双向认证
- 多表查询
- Visual C++ 文件读写
- vi查找替换
- 家装科普
- In App Purchases: A Full Walkthrough
- 手机电容TP和电阻TP原理简介
- HttpWebRequest 下载网页时 ,处理页面编码和服务器响应流(Gzip压缩)
- 如何在 Web 服务器上设置 SSL
- ARM芯片选型简易指南