数据库关联查询

定义

联合多张表查询数据，查询结果集中的数据来自于多张表

关联关系

表与表中的记录在设计时会产生一种对应关系，它称为关联关系

关联关系的分类

。一对一 :A 表的一条记录仅对应 B 表的一条记录 , 称为 A 与 B 表存在一对一关系

。一对多 :A 表中的一条记录可以对应 B 表中的多条记录 , 称为 A 与 B 表存在一对多关系

。多对多 : 当 A 与 B 表双向都存在一对多关系时就称为多对多关系

连接条件

在关联关系中，非常重要的一点就是指定连接条件，作用是让数据库清楚两张表记录与记录之间的对应关系。

如果指定的连接条件无效或没有指定连接条件时，会产生笛卡尔积，这通常是一个无意义的结果集，要尽量避免

语法

SELECT   各表中的字段
FROM     A表,B表[,C表...]
WHERE    连接条件      用来让数据库清楚表与表之间记录的对应关系
AND      过滤条件      用来筛选数据的.
         注:连接条件要与过滤条件同时满足!!!

列

 。查看每个老师及其负责课程科目名

select teacher.name,teacher.age,subject.name
from teacher,subject
where teacher.subject_id=subject.id

为了区分字段属于那张表，我们需要在字段前添加表名，格式为：表名.字段名

当表名过长时，这样写过于臃肿，对此我们可以为表取别名，此时我们可以用：表别名.字段名

select t.name,t.age,s.name
from teacher t,subject s
where t.subject_id=s.id

 

数据库在进行关联查询时 , 会遍历 A 表每一条记录并与 B 表每条记录连接一次 , 但是只有满足连接条件

的记录才会将它们中需要查询的字段提取出来形成结果集中的一条记录 .

不指定连接条件时 , 会产生 笛卡尔积

select t.naem,t.age,s.naem
from teacher t,subject s

笛卡尔积的产生

当不指定连接条件时 , 数据库在进行关联查询时 , 仍然会用 A 表一条记录与 B 表每条记录连接一次 , 并产

生结果集中的一条记录 . 此时的数据量为 A 表记录数与 B 表记录数的乘积 .

当表中数据量大时 , 这样的结果集开销巨大 , 甚至可能导致服务器宕机 . 因此要尽量避免 .

查看班级的名称和对应的班主任 ( 老师 ) 是谁 ?

---

1: 确定数据来自哪些表 , 确定 FROM 子句

需要查询班级名称 , 班级名称来自 class 表中的字段

班主任名字 ( 老师名字 ), 老师名字来自 teacher 表中的字段

确定 :FROM class c,teacher t

2: 当表明确了 , 就要确定表与表中记录的对应关系 , 确定连接条件

班级表中有一个字段 teacher_id 记录了该班级班主任 ( 老师 ) 的 id

确定连接条件 :c.teacher_id=t.id

select c.name,t.name
from class c,teacher t
where c.teacher_id=t.id

---

查看每个学生的名字，年龄，以及其所在的班级名称和所在楼层

1 : 确定数据来自哪些表 , 确定 FROM 子句 

student s,class c

2 : 当表明确了 , 就要确定表与表中记录的对应关系 , 确定连接条件

s.class_id=c.id

select s.name,sage,c.floor
from student s,class c
where s.class_id=c.id

 

连接条件要与过滤条件同时满足

例

王克晶是哪个班的班主任 ? 列出 : 班级名称，楼层，老师名称，工资

1:数据来自哪些表?
teacher t,class c
2:连接条件?
c.teacher_id=t.id
3:过滤条件?
t.naem='王克晶'

sleect c.naem,c.floor,t.name,t.salary
from teacher t,class c
where c.teacher_id=t.id                  连接条件
and t.name='王克晶'                       过滤条件

查看三年级的班级班主任都是谁 ? 要列出班级名称，所在楼层，班主任名字和工资

select c.naem,c.floor,t.anem,t.salary
from class c,teacher t
where c.teacher_id=t.id
and c.name like '3年级%'

查看来自南京的学生都有谁 ? 要列出城市名字，学生名字，年龄，性别

select l.anem,sanem,s.age,s.gender
from student s,location l
where s.location_id=l.id
and l.naem='南京'

查看 5 年级的中队长都有谁 ? 要列出学生名字，年龄，性别，职位和所在班级的名字以及楼层

select s.name,s.age,s.job,c.floor
from student s,class c
where s.class_id=c.id
and c.name like '5年级%'
and s.job='中队长'

 

N 张表关联查询

N 张表关联查询就要有至少 N-1 个连接条件 , 并且这些 连接条件要同时满足

例

查看 " 范传奇 " 所带班级的学生都有谁 ? 要列出 : 学生名字，年龄，班级名称，老师名字

1 : 数据来自哪些表

student s,class c,teacher t

2 : 关联关系 - 3 张表关联就要有 2 个连接条件

学生表与班级表的关联关系 :s .class_id = c .id

班级表与老师表的关联关系 :c .teacher_id = t .id

3 : 过滤条件

老师的名字 :t .name = ' 范传奇 '

select s.naem,s.age,c.naem,t.naem
from student s,class c,teacher t
where s.class_id=c.id
and c.teacher_id=t.id
and t,name='范传奇'

查看 1 年级 1 班的同学的名字和来自的城市

1 : 数据来自哪张表以及过滤条件来自哪张表 ?

student s,location l,class c

2 : 确定关联关系

学生与班级的关系 :s .class_id = c .id

学生与城市的关系 :s .location_id = l .id

3 : 过滤条件 ?

班级名字为 :c .name = '1 年级 1 班 '

select s.name,l.naem,c.name
from student s,location l,class c
where s.class_id=c.id
and s.location_id=l.id
and c.name='1年级1班'

 

1. 查看来自北京的学生都是谁 ?

2. 教 " 英语 " 的老师都是谁 ?

3. 刘苍松所带班级的学生都有谁 ?

4. 教语文的老师所带的班级有哪些 ?

5. 王克晶所带的班级学生都来自哪些城市 ( 去重 ) ?

6.3 年级的几个班主任都教哪些课程 ?

7. 工资高于 10000 的老师所带班里的大队长都是谁 ?

8. " 李费水 " 的班主任教哪门课 ?

9. 所在 4 楼的班里的大队长和中队长以及班主任都是谁 ?

10. 全校最小的同学的班主任是谁 ?

答案

1.查看来自北京的学生都是谁?
select s.name,l.name
from student s,location l
where s.location_id=l.id
and l.name='北京'
2.教"英语"的老师都是谁?
select t.naem,subject su
where t.subject_id=su.id
and su.naem='英语'
3.刘苍松所带班级的学生都有谁?
select t.naem,s.naem,c.name
from student s,class c,teacher t
where s.class_id=c.id
and c.teacher_id=t.id
and t.name='刘苍松'
4.教语文的老师所带的班级有哪些?
select c.name,t.naem,su.naem
from subject su,teacher t,class c
where su.id=t.subject_id
and t.id=c.teacher_id
and su.name='语文'
5.王克晶所带的班级学生都来自哪些城市(去重)?
select distinct l.name
from teacher t,class c,student s,location l
where t.id=c.teacher_id
and s.class_id=c.id
and s.location_id=l.id
and t.name='王克晶'
6.3年级的几个班主任都教哪些课程?
select c.naem,t.naem,su.name
from class c,teacher t,subject su
where c.teacher_id=t.id
and t.subject_id=su.id
and c.name like '3年级%'
7.工资高于10000的老师所带班里的大队长都是谁?
selects.name,s.job,c.anem,t.anem,t.salary
from teacher t,class c,student s
where t.id=c.teacher_id
and s.class_id=c.id
and t.salary>1000
and s.job='大队长'
8."李费水"的班主任教哪门课?
select s.name,c.naem,t.name,su.anme
from student s,class c,teacher t,subject su
where s.class_id=c.id
and c.teacher_id=t.id
and t.subject_id=su.id
and s.naem='李费水'
9.所在4楼的班里的大队长和中队长以及班主任都是谁?
select s.name，s.job,c.name,c.floor,t.name
from student s,class c,teacher t
where s.class c,teacher t
and c.teacher_id=t.id
and s.job in('大队长'，'中队长')
#不使用in,改为使用or
select s.anem,s.job,c.floor,t.name
from student s,class c,teacher t
where s.class_id=c.id
and c.teacher_id=t.id
and c.floor=4
and (s.job='大队长' or job='中队长')#提高or的优先级
10.全校最小的同学的班主任是谁?
未知条件:全校最小的生日是哪天?
子查询:
select max(birth)from student

select distinct t.naem
from student s,class c,teacher t
where s.class_id=c.id
and c.teahcer_id=t.id
and s.birth=(select max(birth)from student)#最小生日有两个学生