先展示两个表
学生表:
老师表:
年级班级表
普通连表查询
查询年级的管理老师和班级的管理老师
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 方式的使用比普通连表查询对比:
- 查询效率 inner join 要快很多 (因为使用普通查询where 的方式查询的时候所全不匹配一遍,然而inner join on 的方式on只匹配了匹配的内容)
- 普通查询的方式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