9.MySQL

4.4联表查询

-- ======================联表查询========================
/*思路
1.分析需求，分析查询的字段来自哪些表（连接查询）
2.确定使用哪种连接查询？7种
确定交叉点（on或where：这两个表中哪个数据或字段是相同的）
判断条件：学生表中 studentno = 成绩表中 studentno
*/

-- join on(判断的条件)连接查询
-- where 等值查询

-- 两张表所有列都呈现
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno

-- Right Join 以右表为基准
SELECT s.studentno ,studentname,subjectno,studentresult
FROM student s
RIGHT JOIN result AS r
ON s.studentno = r.studentno

-- Left Join 以左表为基准
SELECT s.studentno ,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result AS r
ON s.studentno = r.studentno

-- 查询缺考的同学
SELECT s.studentno ,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result AS r
ON s.studentno = r.studentno
WHERE studentresult IS NULL