多表查询

本章用到三张表：teacher，course，student

创建表teacher

create table teacher
(
tno int not null,
tname char(10) not null,
cno int not null,
sal int,
dname char(10) not null,<br>tsex char(10) not null,
age int not null
);

 插入数据

insert into teacher values(1,'王军',4,800,'数学','男',32);
insert into teacher values(2,'李彤',5,1200,'生物','女',54);
insert into teacher values(3,'王永军',1,900,'计算机','男',40);
insert into teacher values(4,'刘小静',2,1200,'计算机','女',46);
insert into teacher values(5,'高伟',8,2100,'电子工程','男',39);
insert into teacher values(6,'李伟',7,1200,'机械工程','男',29);
insert into teacher values(7,'刘辉',3,900,'生物','女',46);
insert into teacher values(8,'李伟',9,null,'计算机','女',43);
insert into teacher values(9,'刘静',12,1300,'经济管理','女',28);
insert into teacher values(10,'刘一凯',13,null,'计算机','女',33);

 创建表course

create table course
(
cno int not null,
cname char(30) not null,
ctime int not null,
scount int not null,
ctest datetime not null
)

 插入数据

insert into course values (4,'应用数学基础',48,120,'2006-7-10');
insert into course values (5,'生物工程概论',32,80,'2006-7-8');
insert into course values (1,'计算机软件基础',32,70,'2006-7-8');
insert into course values (2,'计算机硬件基础',24,80,'2006-6-28');
insert into course values (8,'模拟电路设计',28,90,'2006-7-10');
insert into course values (7,'机械设计实践',48,68,'2006-7-14');
insert into course values (3,'生物化学',32,40,'2006-7-2');
insert into course values (9,'数据库设计',16,80,'2006-7-1');
insert into course values (6,'设计理论',28,45,'2006-6-30');
insert into course values (10,'计算机入门',24,150,'2006-6-29');
insert into course values (11,'数字电路设计基础',30,125,'2006-6-20');

 创建表student

create  table student
(
sno char(4) not null,
sname char(10) not null,
dname char(10) not null,
ssex char(2),
cno int,
mark decimal(3,1),
type char(4)
)

 插入数据

insert into student values('9701','刘建国','管理工程','男',4,82.5,'必修');
insert into student values('9701','刘建国','管理工程','男',10,70,'选修');
insert into student values('9701','刘建国','管理工程','男',1,78.5,'选修');
insert into student values('9702','李春','环境工程','女',5,63,'必修');
insert into student values('9702','李春','环境工程','女',10,58,'选修');
insert into student values('9703','王天','生物','男',5,48.5,'必修');
insert into student values('9703','王天','生物','男',2,86,'选修');
insert into student values('9704','李华','计算机','女',4,76,'必修');
insert into student values('9704','李华','计算机','女',1,92,'必修');
insert into student values('9704','李华','计算机','女',2,89,'必修');
insert into student values('9704','李华','计算机','女',9,80,'必修');
insert into student values('9704','李华','计算机','女',8,70,'选修');
insert into student values('9705','孙庆','电子工程','男',8,79,'必修');
insert into student values('9705','孙庆','电子工程','男',1,59,'必修');
insert into student values('9705','孙庆','电子工程','男',11,52,'必修');
insert into student values('9705','孙庆','电子工程','男',6,68,'必修');
insert into student values('9706','高伟','机械工程','男',13,93,'必修');
insert into student values('9706','高伟','机械工程','男',12,88.5,'必修');
insert into student values('9706','高伟','机械工程','男',1,78,'选修');
insert into student values('9706','高伟','机械工程','男',10,76,'选修');

 显示数据

 

 

 

表的基本连接

 简单的二表连接

在teacher和course两张表查找课程编号相等的记录

select tname,dname,cname,ctest

from teacher,course

where teacher.cno=course.cno;

两个表的查找时，系统会首先执行from子句，这里from子句有两个表，系统会首先计算这两个表的迪卡尔积，列出这两个表的所有组合，放到一个中间表，然后执行where子句。

为了验证以上流程，执行以下语句：

select * from teacher,course;

结果部分如下：

多表连接

查询学生的姓名、所在专业、所学课程、考试时间、课程成绩和授课老师

select sname,student.dname,cname,ctest,mark,tname

from teacher,course,student

where student.cno=course.cno and teacher.cno = student.cno order by sname;

由于teacher和student表都有dname字段，因此在select子句中查询dname时，一定要指明表名。

使用表别名

上面的查询语句可以通过设置表别名：

select s.sname,s.dname,c.cname,c.ctest,s.mark,t.tname

from teacher as t,course as c,student as s

where s.cno=c.cno and t.cno = s.cno order by s.sname;

采用join关键字建立连接

语法如下：

select column

from join_table

join_type join_table

on (join_condition)

1）join_table指出参与连接的表名

2）join_type为连接类型，有4种：自然连接、内连接、外连接和交叉连接

自然连接：natural join

内连接：inner join

外连接：分左连接left join，右连接right join，全连接full join

交叉连接：cross join

各种连接类型，下一节详细介绍。

表的连接类型

自连接

指表与其自身进行连接，这就需要表的别名。

查询存在不及格课程的学生的学号

select distinct s1.sname,s1.dname,s1.cno,s1.mark

from student as s1,student as s2

where s1.sno=s2.sno and s2.mark<60 order by s1.sname;

以上查询使用了两张表（只不过是相同的表），表s2用来查询不及格学生的学号sno，根据查到的sno去表s1中过滤不符合的学号sno，剩下的记录就是不及格学生的信息。但有可能在s2中，有一个学生的两条记录都满足不及格条件，就会查到两个相同的sno，用这两个相同的sno去表s1查数据时，会查到多条相同的记录（如下），所以select后面使用distinct来避免重复记录。

自然连接

一种特殊的等价连接，它将表中具有相同名称的列自动进行记录匹配。自然连接不必指定任何同等连接条件。自然连接会自动判断相同名称的列，而后形成匹配。缺点是不能人为指定哪些列被匹配。

比如以下语句：

select sname,dname,cno,tname from student natural join teacher;

表student和表teacher具有相同名称的列为cno和dname，因此只有两个表中cno和dname的值都相等的行才会连接起来作为结果表的一行。

实际上，自然连接不如使用where灵活，比如上面的查询，同时匹配cno和dname并没有太大意义。我们可以使用where语句来查询学生的信息以及所学课程的授课老师，如下：

select sname,s.dname,s.cno,tname from student as s,teacher as t where s.cno = t.cno order by sname;

内连接（inner join）

内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。

返回的结果集是两个表中所有匹配的数据，舍弃不匹配的数据。这种查询中，只返回来自源表的相关行，即查询的结果表包含的两个源表行必须满足on子句中的搜索条件。

比如查询学生的姓名、专业、课程和授课老师：

select sname,s.dname,s.cno,tname

from student as s

inner join teacher as t

on s.cno=t.cno order by sname;

以上查询语句等同于

select s.sname,s.dname,s.cno,t.tname

from student as s,teacher as t

where s.cno=t.cno

order by sname;

外连接（outer join）

内连接和带where的多表查询，都组合多个表，并生成结果表。也就是，如果任何一个源表的行在另一个源表中没有匹配，DBMS将不把该行放在最后的结果表中。

而外连接，不仅包含符合条件的行，而且还包含左表（左外连接）、右表（右外连接）、连个边接表（全外连接）中的所有数据行。

左外连接

结果表除了匹配行外，还包括join左边的不匹配行。

左外连接=内连接+左表失配的元组

比如：查询学生姓名、专业以及课程名称等信息

select s.sno,sname,s.cno,cname,ctest,mark

from student as s

left join course as c

on s.cno=c.cno

order by sname;

左表中的不匹配行缺少右边表对应的行，因此相关列用NULL表示。

右外连接

结果表除了匹配行外，还包括join右边的不匹配行。

右外连接=内连接+右表失配的元组

比如：查询学生姓名、专业以及课程名称等信息

select s.sno,sname,s.cno,cname,ctest,mark

from student as s

right join course as c

on s.cno=c.cno

order by sname;

全外连接

结果表除了匹配行外，还包括join左边和右边的不匹配行。

右外连接=内连接+右表失配的元组+左表失配的元组

注：mysql没有full join这个东西

要想实现全连接，可以使用union如下：

select  s.sno,sname,s.cno,cname,ctest,mark from student as s right join course as c on s.cno=c.cno union select s.sno,sname,s.cno,cname,ctest,mark from student as s left join course as c on s.cno=c.cno order by sname;

注：union会对结果集去重，因此查询结果不会重复，而union all则不会去重。使用union all会直接把左侧的结果集和右边的结果集加在一起。

交叉连接（cross join）

省略。

union与union join

关系的集合运算

R并S：R或S或两者中元素的集合，即R和S集合的合集，但去除重复的

R交S：R和S都存在的元素的集合

R差S：在R中存在而S中不存在的元素

union运算符

union运算用于执行集合并运算。

举例：

select sno,sname,dname from student where cno=1

union

select sno,sname,dname from student where cno=10;

注：union左右的结果集执行合并运算，然后去除重复，如果要保留重复行，使用union all

不同表的不同字段也可以使用union进行并操作，但要求参与的两个集合必须选择同样数列的列，并且相应的列必须具有相同的类型。

例如：

select sname as name,dname,cno udent where cno=1 or cno=10 union select tname as name,dname,cno from teacher where cno=1 or cno=10;

union join连接表

union join和之前提到的关于join连接表不同，union join并不对表的数据进行任何匹配处理，只是把几个表的每一行联合起来，生成的结果表包括第一个表的所有行和列以及另一个表的所有行和列，缺少的属性用null表示。

表连接的其他应用

连接表进行聚合运算

在内连接中使用聚合函数

比如：查询老师授课中，每个课程学生选修的数量

select t.cno,count(s.cno) as num_s from teacher as t

inner join student as s on t.cno=s.cno

group by t.cno

order by num_s;

外连接中使用聚合函数

比如：查询老师授课中，每个课程学生选修的数量

select t.cno,count(s.cno) as num_s from teacher as t

left join student as s on t.cno=s.cno

group by t.cno

order by num_s;

多表连接的综合应用

从teacher表、student表和course表查询所有教师的姓名、专业、开设的课程以及选修这门课的学生姓名及成绩，要求显示的教师信息必须是所开课程在student表中有同学选修。

分析：教师姓名和专业在teacher表中存储，而教师开设的课程名称在course表存储，这里没有要求开设的课程名不能为null，因此student和course采用左连接；而学生姓名和课程信息在course表存储，这里要求教师开设的课程在student表有通学选修，即学生姓名和课程信息不能为null，所以与student表的连接采用内连接。
select tname,t.dname,cname,sname,mark from teacher as t

left join course as c on t.cno=c.cno

inner join student as s on t.cno=s.cno

order by tname;