09-联表查询

联表查询

join

-- 查询的字段来自哪些表 （连接查询）
-- inner join
select a.studentNo,a.studentName,b.studentScore
from 表1 as a
inner join 表2 as b
where a.studentNo=b.studentNo




-- right join
select a.studentNo,a.studentName,b.studentScore
from 表1 as a
right join 表2 as b
on a.studentNo=b.studentNo


-- left join
select a.studentNo,a.studentName,b.studentScore
from 表1 as a
left join 表2 as b
on a.studentNo=b.studentNo

操作	描述
inner join	如果表中至少有一个匹配，就返回
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

练习：三表查询

select s.studentNo,studentName,SubjectName,`StudentResult`
from student s
right join result r
on r.studentNo=s.studentNo
inner join `subject` sub
on r.SubjectNo=sub.SubjectNo

自连接

pid	id	subjectname
1	2	java
2	1	js
3	1	jQuery
4	3	Ajax

-- 把一张表分为两张表

select a.subjectname as '父' , b.subjectname as '子'
from 表 as a , 表 as b
where a.pid=b.id