SQL多表查询

表与表关系

​ 一对一，一对多，多对多关系

笛卡尔积连接

-- 想查询学生信息时候显示班级名称
select student.id as id,student.name as name,class.name as class 
from student,class 
where student.class_id=class.id;

--查询单个学生选修的课程
select student.name as name,lesson.name as lesson 
from stu_lesson,student,lesson 
where stu_lesson.student_id=student.id and stu_lesson.lesson_id=lesson.id and student.name='zjy';

内连接

-- 想查询学生信息时候显示班级名称(一对多)
select student.id as id,student.name as name,class.name as class 
from student 
inner join class on student.class_id=class.id;

----查询单个学生选修的课程
select student.name as name,lesson.name as lesson 
from stu_lesson
inner join student on stu_lesson.student_id=student.id
inner join lesson on stu_lesson.lesson_id=lesson.id
where student.name='zjy';

左外连接

select * from lesson left join stu_lesson on lesson.id=stu_lesson.lesson_id and lesson.id=1;

解释：左表lesson全部打印出来，图中可见lesson_id为null，也打印出来了

右外连接

select * from lesson right join stu_lesson on lesson.id=stu_lesson.lesson_id and lesson.id=1;