1-6-4 多表连接查询及使用关系代数的理论查询数据
多表连接查询是用来连接两个表的条件称为连接条件或连接谓词,其一般格式为:
[<表名1>.] <列名1> <比较运算符> [<表名2>.] <列名2>
其中比较运算符主要有:=>< >= <= !=。当连接运算为 = 时,称为等值连接。使用其他运算符称为非等值连接。连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的,但不必是相同的。连接查询可以包括的类型有:比较连接查询,自身连接和复合条件连接等。
1 比较连接查询
连接运算中有两种特殊情况:
1)广义笛卡尔积,是不带连接谓词的连接,两个表的广义笛卡尔积即是两表中元组的交叉乘积,其连接的结果会产生一些没有意义的元组,所以这种运算实际很少使用。
2)自然连接,若在等值连接中把目标列中重复的属性列去掉则为自然连接。
实验:自然连接查询
--1:查询student表和sc表所有数据信息
SELECT Student.*,Sc.* FROM Student,SC WHERE Student.Sno=SC.Sno;     
/*StudentSC中同一学生的元组连接起来,如图1-39所示*/
 

1-6 数据查询(下)——复杂查询 _休闲

1-39 基本表SC
2 自身连接查询
首先我们提出一个问题,对于课程表course而言,先修课的先修课是什么?如图1-40所示,假设数据库这门课程,按照查询的逻辑路线,其先修课的先修课是pascal语言,表现在SQL中的查询思路是,将物理表COURSE表分别复制到不同的内存工作区中,虽然它们的数据内容是完全一致的,但是在概念上市属于不同的虚表,我们将之分别命名为T1T2表,如果T1表的先修课程号码等于T2表的课程号,即T1.cpno=T2.cno,就可以将单表自身连接问题通过SQL语句体现出来,具体的SQL脚本是:select t1.cno,t2.cpno from course t1,course t2 where t1.cpno=t2.cno,具体的逻辑示意图为1-41所示。
 

1-6 数据查询(下)——复杂查询 _数据查询_02

1-40 课程表course中先修课的先修课
 

1-6 数据查询(下)——复杂查询 _数据查询_03

1-41 单表自身连接示意图
因此,单表自连接问题是指单表自身产生逻辑连接关系的一类查询问题。处理这样一类问题的基本思路是:首先将单表在内存中建立多张相同的多个虚表,其次建立多张相同虚表的逻辑关联性。
比较典型的一类问题是家族树问题,假设有数据表person,每行元组代表着一个具体的人,其自身又有父亲的编号(father_id)以及母亲编号(mother_id),而父母的编号由必须出自person表的主键person_id的集合,即父母的编号是该表的外键。具体属性以及主外键的关联可见图1-42所示:
           

1-6 数据查询(下)——复杂查询 _数据查询_04

1-42 家族树问题的逻辑关联结构
实验:通过SQL语句解决家族树的单表自连接问题
--首先建立一个新的数据库,命名为family,在该数据库下新建一张基本表person,代码如下:
create table person
(
person_id int primary key identity, --该主码为自动增长的类型
person_name char(20),
father_id int, --父亲的个人编号
mother_id int, --母亲的个人编号
sex char(2) default '',
marriaged char(1),
date_birth smalldatetime,
date_death smalldatetime,
foreign key (father_id) references person(person_id),
foreign key (mother_id) references person(person_id),
check (sex in ('','')) )
--1:查找李杰的孩子有谁?
select child.person_name from person me,person child where me.person_name=‘李杰’ and me.person_id=child.mother_id
--2:请查询一下每个人的父亲和母亲是谁?
select me.person_name,mother.person_name,father.person_name
from person me,person mother,person father
where mother.person_id=me.mother_id and father.person_id=me.father_id
--3:那些人是双胞胎?
select me.person_name,twins.person_name
from person me,person mother,person twins,person father
where me.mother_id=mother.person_id and twins.mother_id=mother.person_id
and me.father_id=father.person_id and
twins.father_id=father.person_id and twins.date_birth=me.date_birth
--4:每个人的外公和外婆是谁?
select me.person_name as 姓名, me.sex as 性别,grand_father.person_name as 外公姓名, grand_mother.person_name as 外婆姓名
from person me,person mother, person grand_father,person grand_mother
where me.mother_id=mother.person_id
and mother.father_id=grand_father.person_id
and mother.mother_id=grand_mother.person_id
3 嵌套查询
SQL语言中,一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。
实验:嵌套查询实验
--1查询选修了2号课程的学生姓名
SELECT Sname FROM Student WHERE Sno IN
    (SELECT Sno FROM SC WHERE Cno=‘2’) ;
--2:查询选修了高等数学课程的学生姓名和学号
SELECT Sno,Sname                                  ③最后在Student关系中取
FROM Student                                       SnoSname
WHERE Sno IN
    SELECT Sno                                     ②然后在SC关系中找出
    FROM SC                                        选修了2号课程的学生
    WHERE Cno IN                                 学号
        (SELECT Cno                               ①首先在Course关系中找
        FROM Course                               高等数学的课程号,比
        WHERE Cname=‘高等数学’);                 如结果为“2”
--3:查询与刘晨在同一个班的学生姓名、学号和班级
SELECT Sno, Sname, class
FROM    Student
WHERE class =
    (SELECT class
    FROM Student WHERE Sname=‘刘晨’)
注意:子查询一定要跟在比较符(比如“=”)之后。本例的WHERE子句不能用下面的写法:
        WHERE (SELECT class
                FROM Student WHERE Sname=‘刘晨’) = class
4 关系数据库的内外关系连接
SQL sever 2005之中的关系连接方法主要有:笛卡尔集合、交、并、差,除等,而作为标语表之间的关系连接,SQL sever 2005也可以通过多种的连接方法提供多表之间的关联,这其中就包括:内连接、(左、右)外连接、全外连接、非等值连接和交叉连接等,这些连接方法的本质是综合的反映关系代数的基本集合运算理论。
1)内链接
目前我们讲述的SQL设计方法属于ANSI——86标准,早期的连接方法是通过在FROM语句之中指定需要连接的表信息,然后再在WHERE表中将相关的属性进行连接。为了向后兼容,sql sever 2005仍然保持向后兼容的特点。下面我们现在将通过使用ANSI—92标准的设计方法实现表间的互连,基本语法格式如下:
Select 属性项目信息 From table A
 (Inner) join table B         --注意:这里的inner可以省略。
On A.column=B.column
Where 查询条件
实验:内连接查询实验
--1:通过SQL86标准建立查询
--查询计算机导论课程成绩高于90分的同学学号
select score.sno 
from score,course where score.cno=course.cno and
course.cname='计算机导论' and score.degree>90
--或者
select sno from score
where degree>90 and cno=
(select cno  from course  where cname='计算机导论')
--2:下面开始使用内连接的方法进行查询:
select sno from score
inner join course
on score.cno=course.cno where cname='计算机导论' and degree>90
--或者
select sno from score
inner join course
on score.cno=course.cno and degree>90 where cname='计算机导论'
--3:查询成绩低于80分的学生情况
SELECT student.SNAME, student.CLASS, course.CNAME, score.DEGREE
FROM student
INNER JOIN   score
ON student.SNO = score.SNO
INNER JOIN course
ON score.CNO = course.CNO WHERE (score.DEGREE < 80)
总之,对于内连接86标准和92标准在查询成本上面是一样的,在性能上面完全一样,但是数据库专家一般建议使用92标准,这样可以作到更好的兼容性。
2)外链接
在某些情况下,我们查询的集合不仅仅是两张基本表的交集部分(如图1-43所示),而且期望将没有处于交集部分的数据信息也查询出来,这就必须用到外连接。外连接不仅仅会将两个数据集中相互重叠的属性涉及的元组抽出,而且会将重叠部分以外非匹配的那些元组一并纳入到集合中来。如图1-44所示,假设集合A是学生信息集合,集合B是选课信息集合,如果查询每个学生的选课记录,那么按照内连接的方法将AB集合连接查询后的结果仅仅是查询到那些选修了课程的学生记录,但是会将没有选课的学生记录丢失,显然并不符合查询每个学生选课记录的初衷。即使那些没有选课的学生的选课记录为空,我们也应当将他们显示出来。

1-6 数据查询(下)——复杂查询 _数据查询_05 

1-6 数据查询(下)——复杂查询 _职场_06

      
       1-43 关系集合的交互关系图              1-44 左右外连接集合的交互关系图
外连接又分为左外连接(left outer join)和右外连接(right outer join,其基本的语法格式如下所示:
Select 需要查询的属性项 From table1
Left/right (outer) join table2
On table1.column=table2.column
Where ….具体的条件
上面我们书写的是SQL-92标准的格式,在SQL-86标准中,外连接同样也可以被查询出来,仅仅是格式上不同,关键点是通过WHERE语句进行左右外连接*号在那边就将那边表的全部信息查询出来。基本格式为:
q        左外连接:*=
q        右外连接:=*
 注意:
左右外连接的根本区别到底是什么呢?从本质上说,左右外连接没有任何区别!左和右仅仅是人为的感觉。所谓左和右用于指定无论是否具有匹配的行都要保留全部行的那个表称作为外连接表,通常我们将用户定义的第一个表习惯性称为外连接表,也称作是驱动表,所以左外连接就成为经常使用的原因了。这里的左和右指的是表在SQL语句中的位置,而与图形化工具所指定的表的位置毫无关系!
实验:左、右外连接查询实验
--假设新建了一个数据库为产品销售数据库,命名为cpxs,其基本的逻辑关系图如图1-45所示。
 

1-6 数据查询(下)——复杂查询 _休闲_07

1-45 产品销售基本逻辑关系图
--1:显示销售商的基本信息和其购买货物的基本情况
use cpxs
go
select 客户表.*,产品销售表.* from 产品销售表,客户表
where 产品销售表.客户号=客户表.客户号 order by 产品销售表.客户号
--或者
select 客户表.*,产品销售表.* from 产品销售表 inner join 客户表
on 产品销售表.客户号=客户表.客户号 order by 产品销售表.客户号
问题:查询是否正确呢?丢了什么?
很显然这是错误的解答,因为查询的数据仅仅是那些购买了商品的客户信息,而那些没有购买任何商品的客户信息丢失了。这里必须使用的是外连接查询,代码见下:
SELECT 客户表.*, 产品销售表.* FROM 产品销售表
RIGHT OUTER JOIN 客户表 ON 产品销售表.客户号= 客户表.客户号
问题:刚才的查询是右外连接,那么换成左外连接的话,应该如何书写呢?
SELECT 客户表.*, 产品销售表.* FROM 客户表
LEFT OUTER JOIN 产品销售表 ON 产品销售表.客户号= 客户表.客户号
--下面我们再通过SQL86标准将外连接的查询结果写出来:
SELECT 产品销售表.*, 客户表.* FROM 客户表, 产品销售表
Where 客户表.客户号*= 产品销售表.客户号
5.交叉与无限制连接查询
交叉无限制连接用于对两张源表进行纯粹的关系代数乘运算,人们将这种乘积的结果叫做笛卡儿乘积。需要注意的是,这种无因链接而产生的信息查询在实际应用中并无实际的意义,仅限于理论研究层面。交叉与无限制连接的关键词是Cross Join,语法格式见下:
Select Something From Table_A
Cross Join Table_B
实验:交叉无限制连接查询实验
Use school
Go
Select * from student cross join teacher 
--教师表和学生表无任何主外键关联,但是可以查询出来
6.集合并与交的查询
如果两个具有相同属性类型以及值域的集合想完成并或者交的操作,则需要用到关键词UNION(集合并操作符)和intersect(集合交操作符)。
并操作不同于连接操作,并操作是集合的加法,而连接操作是集合的乘法。并操作必须遵循的原则如下,当然这一原则同样也适用于交际和查询:
(1)    列的名字必须由第一个SELECT语句选择列决定;
(2)    每个SELECT语句选择的列数目必须相同;
(3)    默认情况下SELECT语句将去掉重复行;如果希望出现重复的行必须加上SELECT ALL
实验:集合并与交的查询实验
Use school
Go
--1:查询95031班的学生或者年龄小于19岁的学生
SELECT * FROM Student WHERE class=‘95031’
UNION
SELECT * FROM Student WHERE year(getdate())-year(Sage)<=19;
--2:查询101号和107号都选修的课程号码
SELECT cno FROM Score WHERE Sno='101'
intersect
SELECT cno FROM Score WHERE sno='107';
7.关系代数中近似除与关系整除的SQL查询方法
关系除运算是属于关系代数的抽象语义,其在解决现实问题之中非常实用,一个比较典型的案例是在解决集合与子集的包含问题时候的应用。已知有学校数据库school,该数据库之中的基本表以及关系如图1-46所示。
 

1-6 数据查询(下)——复杂查询 _数据查询_08

1-46 学校数据库逻辑关系图
实验:近似除与关系整除的查询实验
如果提出两个问题:第一个问题,谁选修了王萍老师的全部课程,但没有选修其他任何老师的课程呢?第二个问题,谁不仅选修了王萍老师的全部课程,还可能选修了其他任何老师的课程呢?根据设问的逻辑结构,我们可以推定第一个问题属于关系代数除运算中的关系整除问题,而第二个问题属于近似除问题。近似除在上述问题中的关系代数除算法语义是:R÷S = {tr [X] | tr Î R∧πY (S) Í Y},其中R={某学生选修课程的象集},S={王萍老师讲授的全部课程},下面的SQL代码给出解决近似除的查询语义解释,即按照选修了王萍老师课程的学生分组,这些组中的课程数目等于王萍老师代课的数目时候,这些分组的学生满足语义定义,就是所求的集合。但是,这些集合中所求的学生可能即选择了王萍老师代的所有课程,也学修了其他老师的课程,故而这种求解方法为近似除的解决办法。
--1:谁选修了王萍老师的全部课程
select score.sno,sname
from student
join score on score.sno=student.sno
join course on score.cno=course.cno
join teacher on teacher.tno=course.tno
where teacher.name='王萍'
group by score.sno,sname
having count(distinct score.cno)=
   (select count(course.cno)  from course
where course.tno=(select tno from teacher where teacher.name='王萍'))
对于第一个问题,如果仅仅查询选修了王萍老师的全部课程,但没有选修其他任何老师的课程的情形,在关系代数的近似除就不适合了,因为查询结果并没有将不仅选修王萍老师全部课程而且选修其他老师课程的学生集合除去。关系代数的整除就是试图剥离查询结果之中的查询余数集合,得到比较准确的查询结果。继承上面的关系代数算法,改进的关系代数语义表述算法为:如果R÷S = {tr [X] | tr Î R∧πY (S) Í Y},其中R={某学生选修课程的象集},S={王萍老师讲授的全部课程},则设定W ={选修了王萍老师没有讲授课程的学生集合},关系代数整除算法结论就是R÷SW,即利用差运算剥离查询结果之中的查询余数集合,得出最终的正确结果。下面的SQL代码给出解决整除的查询语义解释:
--2:谁不仅选修了王萍老师的全部课程,还可能选修了其他任何老师的课程呢?
select distinct student_wp.sno,sname
from
(select score.sno,sname
from student
join score on score.sno=student.sno
join course on score.cno=course.cno
join teacher on teacher.tno=course.tno
where teacher.name='王萍'
group by score.sno,sname
having count(distinct score.cno)=
   (
   select count(distinct course.cno)
   from course
   where course.tno=(select tno from teacher where teacher.name='王萍')
   )) as student_wp
join score on score.sno=student_wp.sno
join course on score.cno=course.cno
join teacher on teacher.tno=course.tno
where score.cno in
(select cno from course where tno in(select tno from teacher where name<>'王萍'))
8.集合差的SQL查询方法
在图1-43中,如果A-B,则剩余部分为集合A除去AB交集的部分。在SQL查询中一般我们通过not in关键谓词来实现这一集合操作。
实验:集合差的查询实验
--1:查询没有选修王萍老师课程的学生姓名
use school
go
select sname from student where sno not in
(select sno from score where cno in(select cno from course where tno=(select tno from teacher where name='王萍')))
9.全称谓词查询
在日常生活中经常遇到所有的任何每个一切有(些)存在等说法,涉及到个体域的全部元素或部分元素,故有必要引入量词概念对这一全称量词查询进行讨论。一般在SQL中是通过exists实现全称谓词的子查询工作的,exists主要用于在某个集合之中查询全部信息,exists查询的逻辑及效率是:
1)查询逻辑
首先取外层查询中的第一个元组行,根据它与内层查询相关的属性值处理内层查询,若where条件子句返回值为true,则取外层查询中该元组的查询结果放入这个内层查询里面。然后以此类推,直至最内部的查询逻辑被执行为止。
2)查询效率
由于带有exists量词的相关子查询只关心内层查询值是否返回逻辑值,无须查到具体的值,因此效率并不低于不相关的子查询,有时候是高效率的查询方法。
实验:全称谓词的查询实验
--1、查询没有选修3-245号课程的学生姓名
select sname from student where sno not in ( select sno from score where cno='3-245')
--另解:
select sname from student where not exists (select * from score where sno=student.sno and cno='3-245')
--2、查询选修了全部课程的学生姓名
--也就意味着没有一门课程student表中某个(任意个)学生都没有选修过。
select sname from student where not exists
(select * from course where not exists
 (select * from score where sno=student.sno and cno=course.cno))
--备注:SQL中是没有全称谓词的,因此多转化成为否定之否定的逻辑语义。
--3、查询至少选修了103号学生选修的全部课程的学生号码
select distinct sno from score sc1 where not exists
 (select * from score sc2 where sc2.sno='103' and not exists
   (select * from score sc3 where sc3.sno=sc1.sno and sc3.cno=sc2.cno))
--注意:这种查询的逻辑与嵌套查询的差异是:嵌套查询首先将子查询的结果集求出后,为上级查询提供条件逻辑集合,并逐级上溯,直至查询完结。
--4、求没有选修张旭老师和李诚老师课程的学生学号
--逻辑语义可以转化为:不存在这样的课程y,学生a选修了y,而学生b没有选修
select distinct sno from score sc1 where not exists
 (select * from
     (select cno from teacher,course where teacher.tno=course.tno and teacher.name='张旭'
       union
     select cno from teacher,course where teacher.tno=course.tno and teacher.name='李诚') sc2
    where not exists
    (select * from score sc3 where sc3.sno=sc1.sno and sc3.cno=sc2.cno))
--5、求陆君同学和曾华同学都没有选修的课程和任课教师名单
select teacher.name,cname from
(select cno from course where cno not in
 (select course.cno from student,score,course
   where student.sno=score.sno and course.cno=score.cno and sname='陆君'
   intersect
   select course.cno from student,score,course
   where student.sno=score.sno and course.cno=score.cno and sname='曾华')) as cno1,course,teacher where cno1.cno=course.cno and course.tno=teacher.tno