教学思路SQL之入门习题《学生成绩》 四、多表连接关系查询
表与表之间的连接关系主要分为五种：内连接inner join、左外连接left outer join、右外连接reight outer join、全外连接full outer join、交叉连接cross join。
如果查询时用到了表与表的连接关系，应使用on子句作为连接两个表的关联条件，即搜索到符合条件的联合的行的结果集，使用where子句作为过滤表的搜索条件，本节课主要是教授如何使用这四种连接。
内连接：使用inner join将实现查询的结果集中只包含满足on子句表与表的连接条件的行结果集，如果任意个源表中的行在另一个表中没有对应的（符合on子句）的行，结果集将被排除掉，不会包含。
内连接
on的用法比如：
select a.供应商全称，b.供应商品名称 from 供应商信息表 as a inner join 商品信息表 as b on a.供应商编号=b.供应商编号
查询的结果为：所有商品信息表中现有商品的供应商全称及其所供应的商品的名称 。
这道题还有另外的一种
多表查询的写法：
select a.供应商全称，b.供应商品名称 from 供应商信息表 as a ，商品信息表 as b where a.供应商编号=b.供应商编号
其实内连接等价于多表查询,这种多表查询的用法我已经在上节课用到过，要求同学们记住的是，如果你使用了[inner] join 关键字，查询的联合条件一定要使用on关键字。
内连接中
using的用法：使用using（字段a，字段b）来作为连接表与表字段值相等的搜索条件，相当于on（表1.字段a=表2.字段a and 表1.字段b=表2.字段b）
例题如果改用using关键字的写法为：
select a.供应商全称，b.供应商品名称 from 供应商信息表 as a inner join 商品信息表 as b using（供应商编号）
左连接：使用left outer join将得到放到left join左边的表的所有行，然后通过on子句将满足联合条件的右边的表的查询字段匹配到结果行中，当左侧的表在右表中没有向匹配的拼接值时，用null来填充。
比如：select a.供应商全称，b.供应商品名称 from 供应商信息表 as a left outer join 商品信息表 as b on a.供应商编号=b.供应商编号
查询结果为：所有供应商及其所供应的商品名称，其中如果供应商全称"唐僧事业有限公司"原供应的"长生不老口服液"因虚假夸大疗效而不再进货，商品信息表中应不会再保留本货品，而唐僧事业有限公司暂无其他供应商品，此时查询结果中，因采用了左查询，即left join左边的表中的行都会出现，在唐僧事业有限公司所供应的商品信息的值将为null。
右连接：使用right outer join用法与左连接一致，只是将显示出所有right outer join右侧表的所有行，然后用左边的表的列的值去匹配右侧表，如果找不到向匹配的左侧表的值，将用null来填充结果集中无匹配的右侧表的行的值。
使用右连接实现左连接的效果语句为：
select a.供应商全称，b.供应商品名称 from 信息信息表 as b right outer join 供应商信息表 as a on a.供应商编号=b.供应商编号
区别就是将两个表在关系关键字的位置换一下就可以了。
全连接：使用full outer join，将查询出所有与左表匹配的行和与左表不匹配的行以及与左表在右表中不匹配的行，类似与是左外连接加上右连接中不匹配的行。
下面请同学们按照如下代码执行sql脚本，然后执行这四种连接关系语句，体会查询的结果集。
两表的关系字段为col
1
create
table a (col
int,coll
int)
2 insert into a values(1,10)
3 insert into a values(2,20)
4 insert into a values(3,30)
5 insert into a values(4,40)
6 insert into a values(5,50)
7 go
8 create table b (col int,coll int)
9 insert into b values(1,10)
10 insert into b values(6,20)
11 insert into b values(7,30)
12 insert into b values(8,40)
13 insert into b values(2,50)
14
2 insert into a values(1,10)
3 insert into a values(2,20)
4 insert into a values(3,30)
5 insert into a values(4,40)
6 insert into a values(5,50)
7 go
8 create table b (col int,coll int)
9 insert into b values(1,10)
10 insert into b values(6,20)
11 insert into b values(7,30)
12 insert into b values(8,40)
13 insert into b values(2,50)
14
交叉连接：如果a表有5条记录、6个字段，b表有3条记录、4个字段，使用cross join连接两表查询所有字段得到的结果集为5*3=15条记录，6+4=10个字段。
同学们可以用上面的表完成交叉连接，体会不同。在表与表的连接关系中，用的较多的还是内连接和左连接，请同学们对于这两种语法掌握清楚。
下面我们来继续对“学生成绩”库进行习题练习，练习使用表的连接关系：
1.查询出所有所代课的教师的姓名及其科目名称。
2.查询出所有教师的姓名及其所带科目的名称。
3.查询出考过试的所有男学生的姓名和平均成绩
4.查询出所有学生的姓名及平均成绩，如果该生没有参加考试，平均成绩用0分代替。
答案
1.select a.teachername,b.kemu from teacherinfo as a inner join score as b on a.teacherid=b.teacherid
2.select a.teachername,b.kemu from teacherinfo as a left join score as b
on a.teacherid=b.teacherid
3.select a.name,avg(b.score) as 平均成绩 from student a join chengji b on a.number=b.number where a.sex='男' group byb.number ,a.name
4.select a.name,isnull(avg(b.score),0) as 平均成绩 from student a left join chengji b on a.number=b.number group by b.number ,a.name
2.select a.teachername,b.kemu from teacherinfo as a left join score as b
on a.teacherid=b.teacherid
3.select a.name,avg(b.score) as 平均成绩 from student a join chengji b on a.number=b.number where a.sex='男' group byb.number ,a.name
4.select a.name,isnull(avg(b.score),0) as 平均成绩 from student a left join chengji b on a.number=b.number group by b.number ,a.name
本文转自叶子文文博客51CTO博客，原文链接http://blog.51cto.com/leafwf/185778如需转载请自行联系原作者
叶子文文