MySQL——连接查询

1.内连接

内连接inner join只返回两个表中，连接字段 相等的记录。
组合两张表的记录，返回两张表中关联字段相等的记录，即返回两张表的交集部分。
例如：
用户信息表 user_profile，其中device_id指终端编号（认为每个用户有唯一的一个终端），gender指性别，age指年龄，university指用户所在的学校，gpa是该用户平均学分绩点，active_days_within_30是30天内的活跃天数。

答题情况明细表 question_practice_detail，其中question_id是题目编号，result是答题结果。

题目：想要了解每个学校答过题的用户平均答题数量情况？
限定条件：无
每个学校：按学校分组（group by university）
平均答题数量：在每个学校的分组内，用总答题数量除以总人数即可得到平均答题数量count(question_id) / count(distinct device_id)
表连接：学校和答题信息在不同的表，需要做连接

select university,count(question_id)/count(distinct user_profile.device_id) avg_answer_cnt
from user_profile
join question_practice_detail
using(device_id)
group by university

查询结果：

2.左连接

左连接 left join 返回包括左表中的所有记录和右表中连接字段相等的记录。右表中没有则以null显示。
例如：
用户信息表：user_profile

题库练习明细表：question_practice_detail

表：question_detail

题目：想要计算一些参加了答题的不同学校、不同难度的用户平均答题量？

限定条件：无；
每个学校：按学校分组group by university
不同难度：按难度分组group by difficult_level
平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id)
来自上面信息三个表，需要联表，up与qpd用device_id连接，qd与qpd用question_id连接。

select   university,  difficult_level,   
			round(count(qpd.question_id) / count(distinct qpd.device_id), 4) as avg_answer_cnt
from question_practice_detail as qpd

left join user_profile as up
on up.device_id=qpd.device_id

left join question_detail as qd
on qd.question_id=qpd.question_id

group by university, difficult_level

查询结果：

3.右连接

右连接 right join 返回包括右表中的所有记录和左表中连接字段相等的记录。表中没有则以null显示。
例如：
用户信息表：user_profile

题目：找到每个学校gpa最低的同学，取出每个学校的最低gpa。

select a.device_id, a.university, a.gpa
from user_profile a
right join
(
    select university, min(gpa) as gpa
    from user_profile
    group by university
) as b
on a.university=b.university and a.gpa=b.gpa
order by a.university

查询结果：

4.全连接/联合查询

全连接 union all 返回两站表的所有记录，union相同记录会去重。（取出的列数必须相同）。
例如：
用户信息表：user_profile：

题库练习明细表：question_practice_detail

表：question_detail

题目：分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，结果不去重。
限定条件：学校为山东大学或者性别为男性的用户：university=‘山东大学’, gender=‘male’；
分别查看&结果不去重：所以直接使用两个条件的or是不行的，直接用union也不行，要用union all，分别去查满足条件1的和满足条件2的，然后合在一起不去重（union 去重）

select  device_id, gender, age, gpa
from user_profile
where university='山东大学'

union all

select device_id, gender, age, gpa
from user_profile
where gender='male'

查询结果：