51工具盒子mysql 多表查询
查询获取服务数据信息（多表查询）
在对数据库中数据信息查询时，有些需求情况要获取的数据信息，是通过多个表的数据信息整合获取到的，就称为多表查询；
查询命令语法格式：
# 笛卡尔乘积连接多表：
select * from t1,t2;
# 内连接查询多表：
select * from t1,t2 where t1.列=t2.列;
select * from t1 [inner] join t2 on t1.列=t2.列;
# 外连接查询多表：左外连接
select * from t1 left join t2 on t1.列=t2.列;
# 外连接查询多表：右外连接
select * from t1 right join t2 on t1.列=t2.列;
说明：多表查询的最终目的是将多张表的信息整合为一张大表显示，并将显示的结果信息可以做相应单表的操作处理；
数据准备
1多表查询方式类型：笛卡尔乘积 （平时用不到）只作为理解用
实现局域teacher表与course表进行多表关联;
2多表查询方式类型：内连接（取交集）
可以基于笛卡尔乘积方式的结果集，将有意义的信息进行展示，并且是基于两张表里的相同含义字段，进行比较后输出相等的结果信息；
内连接查询的简单描述：两个表中有关联条件的行显示出来；
# 比较传统的SQL 92的内连接标准方式
select * from teacher,course where teacher.tno=course.tno;
# 比较新颖的SQL 99的内连接使用方式
select * from teacher join course on teacher.tno=course.tno;
3多表查询方式类型：外连接（应用更广泛）
利用外连接查询时，是可以进行性能优化处理的，因为内连接在底层查询时，是逐行进行比较后输出，整体数据查询检索的效率较低；
- 左外连接-left join on
左外连接表示查询数据结构包含：左表所有数据行+右表满足关联条件的行；
# 左连接查询语法
a left join b on a.x = b.x
-- a表示左表，b表示右表，基于左表a建立关联
# 实际操作演示过程
mysql> select * from teacher left join course on teacher.tno=course.tno;
-- 包含了左表的所有数据行信息（teacher），包含了右表的关联数据行信息（course）
外连接方式左连接与右连接区别举例：
# 会将左表作为驱动表，进行外层循环
for each row in a
for each row in b
if a.x=b.y print row
else print a.x b.null
右连接查询语法
右外连接表示查询数据结构包含：右表所有数据行+左表满足关联条件的行；
# 右连接查询语法
a right join b on a.x = b.x
-- a表示左表，b表示右表，基于右表b建立关联
实际操作演示过程
select * from teacher right join course on teacher.tno=course.tno;
-- 包含了右表的所有数据行信息（course），包含了左表的关联数据行信息（teacher）
多表连接查询的步骤思路：
进行需求分析，根据查询需求找寻所有需要的表信息；
找寻表的关联，根据多张表字段信息获取关联的字段；（也可以查询间接关系）
组合后的需求，根据多张表组合后定义查询条件信息；
多表查询信息练习题目分析：
01 统计zhang3，学习了几门课？
思路，先看看表结构信息
思路， 如果统计学生学了几门课程， 需要 让 学生表 和 成绩表关联起来。
我们做一个大表
select * from student join sc on student.sno=sc.sno;
然后我们在 这个基础上 做一下 查询 zhang3 学习了几门课。
按照 学生姓名分组;
select student.sname,count(*) from student join sc on student.sno=sc.sno group by student.sname;
这样结果就出来了。
select student.sname,count(*) from student join sc on student.sno=sc.sno group by student.sname having student.sname='zhang3';
02 查询zhang3，学习的课程名称有哪些？
# 根据需求所需的表信息
student course sc
-- 需要先将student与sc合成一张表，才能在和course建立关联；
# 建立表之间关联
select * from student join sc on student.sno=sc.sno join course on sc.cno=course.cno;
-- 将三张表建立关联后，形成一张大表
再进行 查询操作：
select student.sname, course.cname from student join sc on student.sno=sc.sno join course on sc.cno=course.cno where student.sname='zhang3';
或者是 先分组再统计
select student.sname, group_concat(course.cname) from student join sc on student.sno=sc.sno joinin course on sc.cno=course.cno group by student.sname having student.sname='zhang3';
03 查询xiaoA老师教的学生名？
思路： 需要用到的表 teacher， course, student sc
先把 表关联一下
select * from teacher join course on teacher.tno=course.tno;
select * from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno;
select teacher.tname,group_concat(student.sname) from teacher join course on teacher.tno=course.tno
e.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno group by teacher.tname;
最后过滤 xiaoA
select teacher.tname,group_concat(student.sname) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno group by teacher.tname having teacher.tname='xiaoA';
04 查询xiaoA老师教课程的平均分数？
接上一步的联合大表：
select * from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno;
在这里 统计 平均分数。
select teacher.tname , avg(sc.score) from teacher join course on teacher.tno=course.tno join scon course.cno=sc.cno join student on sc.sno=student.sno group by teacher.tname;
select teacher.tname , avg(sc.score) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno group by teacher.tname having teacher.tname='xiaoA';
正确做法:
mysql> select teacher.tname,course.cname,concat(floor(count(case when sc.score>=60 then 1 end)/count(*)*100),"%") from teacher
join course on teacher.tno=course.tno
join sc on course.cno=sc.cno
group by teacher.tno,course.cno;
+--------+--------+--------------------------------------------------------------------------+
| tname | cname | concat(floor(count(case when sc.score>=60 then 1 end)/count(*)*100),"%") |
+--------+--------+--------------------------------------------------------------------------+
| oldboy | linux | 100% |
| xiaoQ | python | 66% |
| xiaoA | mysql | 75% |
+--------+--------+--------------------------------------------------------------------------+
3 rows in set (0.00 sec)
05 每位老师所教课程的平均分，并按平均分排序？
结合上步的思路：
select teacher.tname , avg(sc.score) from teacher join course on teacher.tno=course.tno join sc
on course.cno=sc.cno join student on sc.sno=student.sno group by teacher.tname order by avg(sc.score);
06 查询xiaoA老师教的不及格的学生姓名？
select teacher.tname,student.sname from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno where sc.score < 60 and teacher.tname='xiaoA';
select teacher.tname, group_concat(student.sname) from teacher join course on teacher.tno=course.tno.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno where sc.score < 60 and teacher.tnaame='xiaoA' group by teacher.tname;
07 查询所有老师所教学生不及格的信息？
select teacher.tname, group_concat(student.sname) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno where sc.score < 60 group by teacher.tname;
{#more-11730}
进阶：
01 查询平均成绩大于60分的同学的学号和平均成绩
02 查询所有同学的学号，姓名。选课数，总成绩
03 查询各科成绩最高和最低的分，以如下形式显示：课程ID，最高分，最低分
04 统计各位老师，所教课程的及格率
05 查询每门课程被选修的学生数
06 查询出只选修了一门课程的全部学生的学号和姓名
07 查询选修课程门数超过1门的学生信息
08 统计每门课程：优秀（85分以上），良好（70-85），一般（60-70），不及格（小于60）的学生列表
09 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
答案： 参考：https://www.cnblogs.com/oldboy-heqing/articles/16868031.html
01 查询平均成绩大于60分的同学的学号和平均成绩
用到2 张表 student, sc
select student.sname,group_concat(student.sno),avg(sc.score) from student join sc on student.sno=sc.sno=sc.sno group by student.sname;
02 查询所有同学的学号，姓名。选课数，总成绩
select student.sname, count(course.cname), sum(sc.score), group_concat(student.sno) from student
join sc on student.sno=sc.sno join course on sc.cno=course.cno group by student.sname;
03 查询各科成绩最高和最低的分，以如下形式显示：课程ID，最高分，最低分
select group_concat(course.cno), course.cname, max(sc.score),min(sc.score) from course join sc onn course.cno=sc.cno group by course.cname;
04 统计各位老师，所教课程的及格率
select teacher.tname, count(sc.score) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno where sc.score > 60 group by teacher.tname;
目前只能进行到 得到及格的数量。
