查询计算机系所有借阅过图书的学生姓名,第七讲 数据库的查询:多表连接查询...

SQL Server 2000

第七讲 数据库的查询:多表连接查询

一、多表连接查询基本知识:p79

1.多表连接查询的概念：若一个查询同时涉及两个或两个以上的表，则称之为多表连接查询。

连接查询可以对两个或两个以上的表进行查询，查询结果通常含有参加连接运算的两个表(或多个表)的指定列。即当用户所需要的数据涉及到两个或两个以上的表，那么就要对两个或两个以上的表先进行连接，然后再查询。

例如：在xsbook库中查找于2006年9月9日借阅了图书的学生姓名,专业名及所借图书的ISBN：

select 姓名,专业名,isbn

from

xs,jy

where

xs.借书证号=jy.借书证号 and 借书时间='2006-09-09'

注意：两个表间是通过其相同的字段(即外码)建立联系的。

外码：如果某字段或字段组合不是本表的主码，却是另一个表的主码，这样的字段或字段组合在本表中被称为外码。

一个表的外码的取值应参照另一个表的主码。

2.连接类型：inner join:内连接,默认的连接类型。又可细分为等值连接和不等值连接。

left join或left outer join：左(外)连接

right join或right outer join：右(外)连接

full join或full outer join： 完全(外)连接

cross join：交叉连接

二、多表连接查询:

1、内连接(inner join):按照指定的连接条件连接两个表，查询结果为满足连接条件的行，是最常用的连接类型。

(1).内连接的语法格式：(这里指等值连接)

格式一：select

字段名列表 from 表名1,表名2[,...n]

where 表名1.列名=表名2.列名 [and ...n]

格式二: select

字段名列表

from 表名1 {[inner] join 表名2

on 表名1.列名=表名2.列名}[...n]

(2).内连接举例：

例1：在xsbook数据库中查找已经借阅了图书的学生姓名，出生时间，专业名，以及他们所借阅图书的ISBN和借书时间。

格式一:

select 姓名,出生时间,专业名,ISBN,借书时间

from

xs,jy

where

xs.借书证号=jy.借书证号

格式二：

select

姓名,出生时间,专业名,ISBN,借书时间 from xs inner join jy

on xs.借书证号=jy.借书证号

以上连接过程说明：首先在表xs中找到第一行,然后从头开始扫描表jy,逐一查找满足连接条件的行,找到后就将表xs中的第一行与该行拼接起来,形成结果表中的一行。待表jy的全部行都扫描完以后,再找到表xs的第2行,然后再从头开始扫描表jy,逐一查找满足连接条件的行,找到后就将表xs的第2行与该行拼接起来,形成结果表中的一行。重复上述操作,直到表xs的全部行都被处理完为止。

若连接查询时不指定字段名列表，则连接后的结果集中默认将含有两个表的全部字段，见下例：

select *

from

xs,jy

where

xs.借书证号=jy.借书证号 在进行连接查询时，应注意以下几点：

A.

在使用两个表所含有的相同字段时，必须在该字段名前加上表名(或表别名)作为前缀，即同名字段用以下形式表示：表名.字段名 或表别名.字段名。若某字段在两个表中是惟一的,则可不加前缀，即两个表的非同名字段可以不加前缀。

如:查找已经借阅了图书的学生的借书证号，姓名以及他们所借阅图书的ISBN，借阅编号和借书时间。

select xs.借书证号,姓名,ISBN,借阅编号,借书时间

from

xs,jy

where

xs.借书证号=jy.借书证号 B.当表名太长时，为方便使用还可以使用表的别名。可在from子句中为表定义一个别名,定义格式为：表名 [as] 别名

例2.查找所借图书的ISBN为“3-6000-02” 的学生的借书证号，姓名及专业名

select a.借书证号,姓名,专业名

from xs as

a,jy as b

where

a.借书证号=b.借书证号 and

ISBN='3-6000-02'

这里：表xs的别名为a，jy的别名为b

注意：表的别名一旦定义，在select语句的各子句中指定字段时只能使用“表别名.列名”格式。

c.可利用内连接进行多表的连接查询:

例3.查找借阅了“网络安全”一书的学生的借书证号、姓名、专业名和借书时间

select xs.借书证号,姓名,专业名,借书时间

from

xs,book,jy

where

xs.借书证号=jy.借书证号 and jy.ISBN=book.ISBN and 书名='网络安全'

或:

select xs.借书证号,姓名,专业名,借书时间

from xs join jy on xs.借书证号=jy.借书证号 join book on jy.isbn=book.isbn

and 书名='网络安全'

例4.查询所有同学的借阅信息，并按借书证号排序，要求查询出:借书证号、姓名、专业名、ISBN、书名和借书时间

select xs.借书证号,姓名,专业名,jy.ISBN,借书时间,书名

from xs,jy,book

where xs.借书证号=jy.借书证号 and jy.ISBN=book.ISBN

order by xs.借书证号

d.内连接时，两个表的先后顺序任意，不影响查询结果。

2.外连接(outer

join):查询结果中不但包含满足连接条件的行，还包括相应表中的不满足条件的行。P82(本讲自以下内容为选学)

注意：外连接只能针对两个表进行。

外连接包括3种：介绍如下：

(1).左外连接(left outer

join)：查询结果中除了包括满足连接条件的行外，还包括左表中不满足条件的行。查询结果集中对于左表不满足条件的行，右表相应字段值为null。

格式： select 字段名列表

from 表名1 left [outer] join 表名2

on 表名1.列名=表名2.列名

其中:outer关键字均可省略。

例5.查询所有学生的相关信息,及他们所借图书的借阅编号和ISBN,若学生未借阅任何图书,也要包括其相关情况。

select xs.*,借阅编号,ISBN

from xs left outer join jy

on xs.借书证号=jy.借书证号

(2).右外连接(right outer

join)：查询结果中除了包括满足连接条件的行外，还包括右表中不满足条件的行。查询结果集中对于右表不满足条件的行，左表相应字段值为null。

格式： select 字段名列表

from 表名1 right [outer] join 表名2

on 表名1.列名=表名2.列名

例6.上例若使用右连接则为：

select xs.*,借阅编号,ISBN

from jy

right outer join xs

on

xs.借书证号=jy.借书证号

练习:查找所有图书的书名，ISBN，及借书者的借书证号和借书时间。若某图书未被借阅,也要包括其情况。

要求：分别用左连接和右连接完成，并练习使用表别名。

(3).完全连接(full outer

join)：查询结果中除了包括满足连接条件的行外，还包括两个表的不满足条件行。查询结果集中对于两表不满足条件的行，各表相应字段值为null。。

格式： select 字段名列表

from 表名1 full [outer] join 表名2

on 表名1.列名=表名2.列名

3、交叉连接(cross join)：实际是将两个表进行拼接，结果表是由第一个表的每行与第二个表的每一行拼接后形成的表,因此查询结果的行数等于两个表行数之积。

格式一： select 字段名列表

from 表名1 cross join 表名2

格式二：select 字段名列表

from 表名1,表名2

例7.列出学生所有可能的借书情况

select 借书证号,姓名,ISBN,书名

from xs cross join book

或

select 借书证号,姓名,ISBN,书名

from xs,book

4.自连接:将一个表与它自身进行连接,称为自连接,若要在一个表中查找某字段值相同的行,则可以使用自连接。使用自连接时需为表指定两个

别名,且所有列的引用均要用别名限定。

例8.查询一人同时借阅了多种图书的学生的借书证号,ISBN和借书时间。(即查询借阅了2本及2本以上图书的学生的借书证号、ISBN和借书时间)

select distinct a.借书证号,a.isbn,a.借书时间

from jy a join jy b

on a.借书证号=b.借书证号 and

a.isbn!=b.isbn

order by a.借书证号

再如:查找在同一天借阅了不同图书的学生的借书证号、ISBN和借书时间.

select distinct a.借书证号,a.isbn,a.借书时间

from jy a join jy b

on a.借书证号=b.借书证号 and a.借书时间=b.借书时间 and

a.isbn!=b.isbn

综述：

与单表查询完全相同，对连接查询的列和行可以进行诸如指定输出标题、使用聚合函数、消除重复行、分组、排序等处理，同样连接查询

的条件中也可以包含确定范围(between...and)、确定集合(In)、字符匹配(like)等。

如:查找已借阅了书名中含有"计算机"字样的图书的学生的借书证号、姓名、专业名、所借图书的ISBN、书名、借阅编号和借书时间

select a.借书证号,姓名,专业名,b.isbn,书名,借阅编号,借书时间

from xs a,book b,jy c

where b.isbn=c.isbn and a.借书证号=c.借书证号 and 书名 like '%计算机%'

order by a.借书证号

再如：查询借阅了“网络安全”一书的学生人数。

select count(*) as 人数

from

book,jy

where

book.isbn=jy.isbn and 书名='网络安全'

经验总结：单表查询：要查询的信息全部来自同一个表。

多表连接查询：要查询的信息来自不同的表。

内连接：在两个表中查询满足条件的记录，不满足条件的记录不显示。

左连接：在两个表中查询满足条件的记录，并且显示左表中不满足条件的记录。

右连接：在两个表中查询满足条件的记录，并且显示右表中不满足条件的记录。

全连接：在两个表中查询满足条件的记录，并且同时显示两个表中不满足条件的记录。

自连接：在一个表中查找具有相同字段值的行(有多个记录某字段的取值相同)。并且可显示具体的记录数据,但不能显示统计信息。

课堂综合练习一：以下查询均在xsbook库中进行。其中6-8为选做。

1.查找在2006年9月借阅了图书的学生姓名及专业名。

2.查找每个学生的基本情况，以及其所借阅图书的情况，要求显示：借书证号,姓名,ISBN,借阅编号,借书时间(练习使用表别名)。

3.查找"数据库原理"一书的ISBN,出版社，价格及被借出时间。

4.查找借阅了"操作系统"一书的学生的借书证号、姓名、专业名和借书时间(练习使用表别名)。

5.查找计算机系的同学所借阅图书的ISBN、书名、姓名及借书数。

6.查找所有学生的情况,及他们借阅图书的借书时间和ISBN,若学生未借阅任何图书,也要包括其情况。(请分别用左连接和右连接完成，并练习

使用表别名。)

7.查找同一人借阅了不同图书的学生的借书证号、ISBN和借书时间。(借阅了2本及2本以上图书的学生的借书证号、ISBN和借书时间)

8.查找学生人数在2人及2人以上的专业的专业名，姓名，性别。

课堂综合练习二：以下查询均在教学库中进行。

1.查询计算机系老师开设的所有课程的课程号和课程名。

2.查询所有女同学所选课程的课程号。

3.查询选修“微机原理”的所有学生的姓名和成绩。

4.查询选修了高等数学或普通物理的学生姓名,学号。

5.查询选修了洪文源老师所讲授课程的学生的学号和成绩。

6.查询选修了计算机课系老师所讲授课程的学生姓名和成绩。

7.查询选修高等数学课程的人数。

8.查询高等数学课程的平均分。