mysql之 关联表（left join | right join | inner join | union）

一.首先给出两张表
user表：用户基本信息表

score表：分数表（学生在哪一天，哪一科目，所考分数）

二.分别解释 left join ,right join ,inner join,union

1.left join 

原理 user left join score = 以user表为准，去查询所有user表成员的分数

select *
from user
left join score on user.name = score.student_name

 2.right join 

原理 user rightjoin score = 以score表为准，去查询所有score表成员的基本信息

select *
from user
right join score on user.name = score.student_name

 3.inner join

原理：user inner join score = 交集，只有user和score都存在的成员，才会被查询出来

select *
from user
inner join score on user.name = score.student_name

 4.union(重点)

原理：要查询的列，都一样，才可以union

示例：我要查询（20241011，20241012，20241013）三天内考试，英语科目，考试总人数，以及及格人数

 

select *
from (
select 
"20241011" as date,
count(*) as all_people_num,
sum(case when score>=60 then 1 else 0 end) as jige_people_num
from score where date_time="20241011" and subject_name="英语"
)as t1 union(
select 
"20241012" as date,
count(*) as all_people_num,
sum(case when score>=60 then 1 else 0 end) as jige_people_num
from score where date_time="20241012" and subject_name="英语"
)union(
select 
"20241013" as date,
count(*) as all_people_num,
sum(case when score>=60 then 1 else 0 end) as jige_people_num
from score where date_time="20241013" and subject_name="英语"
)