一、创建表与插入数据
create table PilotSkills
(pilot char(15) not null,
plane char(15) not null,
primary key (pilot, plane))
insert into PilotSkills
values ('Celko', 'Piper
Cub'),
('Higgins', 'B-52
Bomber'),
('Higgins', 'F-14
Fighter'),
('Higgins', 'Piper
Cub'),
('Jones', 'B-52
Bomber'),
('Jones', 'F-14
Fighter'),
('Smith', 'B-1
Bomber'),
('Smith', 'B-52
Bomber'),
('Smith', 'F-14
Fighter'),
('Wilson', 'B-1
Bomber'),
('Wilson', 'B-52
Bomber'),
('Wilson', 'F-14
Fighter'),
('Wilson', 'F-17
Fighter')
create table Hangar
(plane char(15) primary key)
insert into Hangar
values ('B-1
Bomber'),
('B-52
Bomber'),
('F-14
Fighter')
二、查询数据
1、解惑一
select pilot
from PilotSkills as p1
where not exists (select *
from Hangar
where not exists (select *
from PilotSkills as p2
where (p1.pilot = p2.pilot)
and (p2.plane = Hangar.plane)))
(1)from PilotSkills as p1语句
选择表PilotSkills的数据,语句中主要选择匹配的是pilot
(2)from Hangar语句
选择表Hangar的数据,语句中主要起作用的是plane
(3)from PilotSkills as p2语句
选择表PilotSkills的数据,语句主要是选择数据与前面的p1和hangar的飞行员和飞机进行匹配
(4)where (p1.pilot = p2.pilot) and (p2.plane = Hangar.plane)语句
将表PilotSkills的数据与前面的p1和hangar的飞行员和飞机进行匹配,按飞行员+现有Hangar表中的飞机进行分配,匹配PilotSkills表中是否存在这样的数据,如果有则select *
(5)select * from Hangar where not exists (...)语句
按飞行员+现有Hangar表中的飞机进行分配,并与PilotSkills表中匹配。如果存在任一数据esists中则返回True,同时又由于not关键字则转换为False,即代表在Hangar表中不选择该飞行员不会驾驶的飞机。如果不存在符合的数据esists中则返回False,同时又由于not关键字则转换为True,即代表在Hangar表中选择到了该飞行员不会驾驶的飞机。
(6)select * from PilotSkills where not exists (...)语句
如果前面该飞行员在Hangar表中所有飞机都会驾驶,那么exists中则会返回False,又由于关键字not,则变成了True,则代表会选中表PilotSkills中的该名飞行员的这行记录。
如果前面该飞行员在Hangar表中存在某架飞机不会驾驶,那么exists中则会返回True,又由于关键字not,则变成了False,则查询语句的时候,该名飞行员在表PilotSkills中会由于这架不会驾驶的飞机全部都返回False,导致在表PilotSkills中的该名飞行员的所有记录都不会被选到。、
以下是过程:
(1)Higgins与Plane表匹配,然后其中的B-1 Bomber在PilotSkills表中没有找到数据,对于所有的PilotSkills表中的Higgins返回的结果都是一样的。
(2)Wilson与Plane表匹配后,所有的都能找到数据,因此在exists中则不会返回True。
2、解惑二
(1)将PilotSkills表与hangar表的飞机进行匹配,再按pilot飞行员字段分组,只有满足分组内的数量等于hangar表的飞机数则代表该飞行员能否驾驶该飞机表中的所有飞机。
select pilot
from Pilotskills as p1,hangar as h1
where p1.plane = h1.plane
group by p1.pilot
having COUNT(p1.plane) = (select COUNT(*) from Hangar)
3、解惑三
(1)精确匹配
书中的这个语句将leftjoin前的数据与leftjoin后的数据统计的数量来跟Hangar表进行匹配。
select p1.pilot
from PilotSkills as p1
left outer join Hangar as h1
on p1.plane = h1.plane
group by p1.pilot
having COUNT(p1.plane) = (select COUNT(plane) from Hangar)
and COUNT(h1.plane) = (select COUNT(plane) from Hangar)
(2)模糊匹配
只要飞行员能够驾驶的飞机满足飞机表的飞机即可筛选到。
select p1.pilot
from PilotSkills as p1
left outer join Hangar as h1
on p1.plane = h1.plane
group by p1.pilot
having COUNT(h1.plane) = (select COUNT(plane) from Hangar)