2021-08-04 Mysql联表查询

-- ==================联表查询join=======================

-- 查询参加了考试的同学（学号 姓名 科目编号 分数）
SELECT * FROM `student`
SELECT * FROM `result`

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 r
ON s.studentno = r.studentno

-- left join 

SELECT s.studentno,studentname,subjectno,studentresult
FROM `student` s
LEFT JOIN result r
ON s.studentno = r.studentno

-- 查询缺考的同学

SELECT s.studentno,studentname,subjectno,studentresult
FROM `student` s
LEFT JOIN result r
ON s.studentno = r.studentno
WHERE studentresult IS NULL

-- 查询参加了考试的同学： 学号 姓名 科目名称 分数

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