先看个问题

  • 问:查询每个学生每个科目的分数
  • 分析:学生姓名来源于 students 表,科目名称来源于 subjects,分数来源于 scores 表,怎么将 3 个表放到一起查询,并将结果显示在同一个结果集中呢?
  • 答:当查询结果来源于多张表时,需要使用连接查询
  • 关键:找到表间的关系,当前的关系是
    • students 表的 id—scores 表的 stuid
    • subjects 表的 id—scores 表的 subid
  • 则上面问题的答案是:
1
2
3
4
select students.sname,subjects.stitle,scores.score
from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;
  • 结论:当需要对有关系的多张表进行查询时,需要使用连接 join

连接查询

  • 连接查询分类如下:
    • 表 A inner join 表 B:表 A 与表 B 匹配的行会出现在结果中
    • 表 A left join 表 B:表 A 与表 B 匹配的行会出现在结果中,外加表 A 中独有的数据,未对应的数据使用 null 填充
    • 表 A right join 表 B:表 A 与表 B 匹配的行会出现在结果中,外加表 B 中独有的数据,未对应的数据使用 null 填充
  • 在查询或条件中推荐使用“表名.列名”的语法
  • 如果多个表中列名不重复可以省略“表名.”部分
  • 如果表的名称太长,可以在表名后面使用’ as 简写名’或’ 简写名’,为表起个临时的简写名称

练习

  • 查询学生的姓名、平均分
1
2
3
4
select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
group by students.sname;
  • 查询男生的姓名、总分
1
2
3
4
5
select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
where students.gender=1
group by students.sname;
  • 查询科目的名称、平均分
1
2
3
4
select subjects.stitle,avg(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
group by subjects.stitle;
  • 查询未删除科目的名称、最高分、平均分
1
2
3
4
5
select subjects.stitle,avg(scores.score),max(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
where subjects.isdelete=0
group by subjects.stitle;