这篇文章讲述了多表之间连接,包括内连接、外连接,如有错误或者不妥之处,还请各位大佬批评指正。
连表
SQL中操作多个表,以便可以查询到所需数据,其中包括内连接、外链接、等值连接、非等值连接、左连接、右连接。
- 两种写法:
1. 使用逗号隔开两表再用where判断。
2. 使用join语法,注意条件之间不加逗号。
数据说明
有五个表:学生表,老师表,课程表,学院表,课程成绩表。
表结构
初始数据
- 学生表
- 老师表
- 课程表
- 学院表
- 课程成绩表
内连接
内连接查询操作列出与连接条件匹配的数据行,结果集中不包括一个表与另一个表不匹配的行。
等值连接
- 查询学生’叶清逸’名称,学院名称,老师名称,课程姓名
select s_id , s_name , c_name , t_name , d_name
from t_student , t_teacher , t_department ,t_course
where s_name = '叶清逸' ;
--join写法:
select s_id , s_name , d_name
from t_student cross join t_department
where s_name = '叶清逸'
查询结果:
由结果可见,将t_student , t_teacher , t_department ,t_course这几个表做笛卡尔积之后选出名字叫‘叶清逸’的结果显然不是我们希望得到的结果。
- 加入判断条件改进:
select s_id , s_name , t_name , d_name, c_name ,score
from t_student , t_teacher , t_department ,t_course ,t_grade
where t_grade.student_id = t_student.s_id
and t_grade.course_id = t_course.c_id
and t_student.department_id = t_department.d_id
and t_student.teacher_id = t_teacher.t_id
and s_name = '叶清逸' ;
--join写法
select s_id , s_name ,t_name , d_name ,c_name , score
from t_grade natural join t_student
t_grade natural join t_course
t_student natural join t_department
t_student natural join t_teacher
where s_name = '叶清逸'
查询结果:
注:连接n个表,至少需要n-1个条件,否则会出现笛卡尔积错误的情况。
非等值连接
- 查询成绩在60-90间学生的学号、姓名、课程名、成绩
select s_id , s_name , c_name , score
from t_student s , t_course c , t_grade g
where g.student_id = s.s_id
and g.course_id = c.c_id
and score between 60 and 90 ;
--join写法
select s_id , s_name ,t_name , d_name ,c_name , score
from t_grade natural join t_student
t_grade natural join t_course
t_student natural join t_department
t_student natural join t_teacher
where score between 60 and 90 ;
查询结果:
外连接
两个表在查询时,不仅包括与条件相满足的行,还包括左或右与条件不满足的结果,称之为左连接或者右连接。
添加数据
为了演示的方便,在t_student中添加一个没有分配学院的学生‘刘八’,在t_department中添加一个没有学生的学院‘马克思主义学院’
左连接
- 查询所有学生所在学院,显示未分配学院的学生(左连接)
select s_id , s_name , d_name
from t_student s , t_department d
where s.department_id = d.d_id(+)
--join写法
select s_id , s_name , d_name
from t_student left outer join t_department
using (d_id)
--或者(on写法更为灵活)
select s_id , s_name , d_name
from t_student s left outer join t_department d
on s.d_id = d.d_id
查询结果:
- 查询所有学生所在学院,显示未分配学生的学院(右连接)
select s_id , s_name , d_name
from t_student s , t_department d
where s.department_id(+) = d.d_id
--join写法
select s_id , s_name , d_name
from t_student s right outer join t_department d
on s.d_id = d.d_id
--或 using (d_id)
查询结果:
左右连接(全连接)
- 查询所有学生所在学院,显示未分配学院的学生和未分配学生的学院(左右连接)
select s_id , s_name , d_name
from t_student s full outer join t_department d
on s.d_id = d.d_id
--或 using (d_id)
查询结果: