数据库多表链接查询的方式

多表联查可以通过连接运算实现，即将多张表通过主外键关系关联在一起进行查询。

分为非等值查询和等值查询：

需要用到的表： 

学生表：
create table student(
	sid int(11),
	sname varchar(10),
	birthday datetime,
	ssex varchar(10),
	classid int(11)
)

成绩表：
create table sc(
	sid int(11),
	cid int(11),
	score decimal(18,1)
)

课程表:
create table course(
	cid int(11),
	cname varchar(10),
	tid varchar(10)
)

班级表:
create table class(
	classid int(11),
	classname varchar(20)
)

老师表:
create table teacher(
	tid int(11)primary key,
	tname varchar(10),
	tsex tinyint(4),
	tbirthday date,
	taddress varchar(255),
	temail varchar(255),
	tmoney decimal(20,2)
)

 

 1.非等值查询(不属于内联)：

语法：

select * from 表1,表2

举例（学生表的数据*班级表的数据*成绩表的数据的数量）：

select count(*) from stuednt,class,sc

如上实例，此查询方式的实质是笛卡尔积的应用，若学生表有5行，班级表有2行，成绩表有10行，上例的查询结果便是5*2*10=100行数据。

不推荐使用非等值查询方式，因为当数据量庞大时，使用此方式便会造成大量的数据，从而降低效率。故此方式适用于数据量较小的情况。

 2.等值联查（内联查询）： 

语法：

select * from 表1,表2 where 表1.字段1 = 表2.字段2......

举例：

select * from student,class where student.classid = class.classid

通过where后面的判断条件来获取需要的数据，主要分为以下三种（内连接、左连接、右连接）：

①内联：inner join 

侧重于两个表之间的共性，它的作用是使用链接比较两个或多个表之间的共有数据，然后进行返回。

语法：

select * from 表1
inner join 表2
on 表1.字段名 = 表2.字段名

举例（比如要查询学生的成绩，涉及到两张表：学生表和成绩表，使用内连接查询的数据就是下图红色部分）：

select * from student
inner join sc on student.sid=sc.sid

延伸举例（五表联查）：

select *from student
inner join sc on student.sid=sc.sid
inner join course on sc.cid=course.cid
inner join teacher on course.tid=teacher.tid
inner join class on student.classid=class.classid

②左外联：left join 

以left join 左边的表（表1）为主表，从左表中返回所有的记录，即便在右表（表2）中没有匹配的行。

on的后面，根据条件进行过滤筛选，再生成临时查询结果。

where的后面，从临时查询结果中再根据条件进行筛选，生成最终结果。

语法：

select * from 表1
left join 表2
on 表1.字段1=表2.字段1

举例（使用左连接查询的数据就是下图红色部分）：  

 

select * from student 
left join class 
on student.classid=class.classid

 如果是三张表，可以通过两个 left join 来连接，即把前面两张表先 left join 之后当成一张虚拟表，然后再跟第三张表 left join。

③右外联：right join 

以left join 右边的表（表2）为主表，从右表中返回所有的记录，即便在左表（表1）中没有匹配的行。

语法：

select * from 表1
right join 表2
on 表1.字段1=表2.字段1

举例（使用右连接查询的数据就是下图红色部分）：  

 

select * from student 
right join class 
on student.classid=class.classid

3.举例一些考试的题：

* 根据上面的表结构找到语文分数相同的学生的个数

select score,count(*) from sc 
where course=‘语文’
group by score 
having count(*) >= 2

* 查询平均成绩大于70的学生姓名

select sname from sc 
group by sname 
having avg(score) > 70

 * 查询出每门课都大于80分的学生的姓名

select sname from student 
where sid not in
 (select  sid  from sc where score < 80)

 * 查询数学成绩最好的学生姓名

select sname from student,sc 
where student.sid = sc.sid and 
 cid = (select cid from course where cname = '数学')
 order by score desc limit 1

* 查询各科成绩平均分，显示课程名称和平均分并按照平均分降序排序 

select cname, avg(score) 平均分 from course,sc 
where course.cid = sc.cid 
group by course.cid 
order by 平均分 desc

* 查询考试超过2门不及格学生的平均成绩 

select avg(score) from sc where sid in
 (select sid from sc where score < 60 group by sid having count(*) >=2)

4.拓展发散：

因为举例中有用到子查询，所以简单写一下：子查询和关联查询的区别

子查询就是查询中又嵌套的查询,表连接都可以用子查询，但不是所有子查询都能用表连接替换，子查询比较灵活，方便，形式多样，适合用于作为查询的筛选条件，而表连接更适合与查看多表的数据。
子查询不一定需要两个表有关联字段，而连接查询必须有字段关联（所谓的主外键关系）
①表关联的效率要高于子查询，因为子查询走的是笛卡尔积
②表关联可能有多条记录，子查询只有一条记录，如果需要唯一的列，最好走子查询

对于数据量多的肯定是用连接查询快些，因为子查询会多次遍历所有的数据（视你的子查询的层次而定），而连接查询只会遍历一次。

但是数据量少的话也就无所谓是连接查询还是子查询，视自己的习惯而定。一般情况下还是用子查询来的好，容易控制。

预祝大家早日成为代码小能手！！！