mysql连表查询,多表查询

先展示两个表

学生表：

老师表：

年级班级表

普通连表查询

查询年级的管理老师和班级的管理老师
select gradeclass.name as gradeName,teacher.name as teacherName from gradeclass,teacher where gradeclass.leaderid = teacher.id

查询多个表使用 select 字段名,字段名… from 表名1,表名2 的方式查询两张表

内连接查询 inner join

inner 可以省略
查询年级的管理老师和班级的管理老师
select gradeclass.name as gradeName,teacher.name as teacherName from gradeclass inner join teacher on gradeclass.leaderid = teacher.id

查询年级的年级主任老师
select gradeclass.name as gradeName,teacher.name as teacherName from gradeclass inner join teacher on gradeclass.leaderid = teacher.id where gradeclass.pid = 0

inner join也可以本表连接本表(这样需要起别名)
查询每个年级的班级，显示年级和班级名称
select g1.name as grade ,g2.name as class from gradeclass g1 join gradeclass g2 on g1.id=g2.pid

注：inner join 方式的使用比普通连表查询对比：

1. 查询效率 inner join 要快很多 (因为使用普通查询where 的方式查询的时候所全不匹配一遍，然而inner join on 的方式on只匹配了匹配的内容)
2. 普通查询的方式where后面就没有了，而inner join on 后面还可以跟where再次过滤数据

外连接 left join on 和 right join on

left join 就是把左面的表认为成主表
right join 就是把右面的表认为成主表

查询年级和班级的负责老师
select gradeclass.name as gradeName,teacher.name as teacherName from gradeclass right join teacher on gradeclass.leaderid = teacher.id

因为right join是右连接所以后面的表为主 宋老师因为没有管理班级或年级所以年级班级位置为null

上面的查询代码直接换成left join就会以左面的表认为成主表显示
select gradeclass.name as gradeName,teacher.name as teacherName from gradeclass left join teacher on gradeclass.leaderid = teacher.id

上面的sql语句就是改了一下left join左连接，因为左连接是左面的表为主表 宋老师的名字直接不显示

多表连查

查询年级下班级的班主任，显示成几年级几班，老师只显示班主任
select concat(g1.name,g2.name) gradeclassname,t.name from gradeclass g1 join gradeclass g2 on g1.id=g2.pid left join teacher t on g2.leaderid = t.id

查询年级下班级的班主任和学生，显示成几年级几班，老师只显示班主任
select concat(g1.name,g2.name) gradeclassname,t.name teachername,s.name studentname from gradeclass g1 join gradeclass g2 on g1.id=g2.pid left join teacher t on g2.leaderid = t.id left join student s on s.grade=g2.id