联表查询
以三种基本查询为基础,可以分为七种查询,本文只讲三种基本查询
inner join 交叉集合,左右的表有匹配的值,才返回
left join 即使右表没有匹配,也会从左表返回值
right join 即使左表没有匹配,也会从右表返回值
多表联接需要一个一个进行联接,把大问题分解为小问题
-- 联表查询
-- 七种查询(基于以下三种)
SELECT * FROM student
SELECT * FROM result
-- 左联
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
LEFT JOIN result AS r
ON s.`studentno` = r.`studentno`
-- 右联
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.`studentno` = r.`studentno`
-- 交叉
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
INNER JOIN result AS r
ON s.`studentno` = r.`studentno`
-- 当需要多张表联查的时候,一步一步联系
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno