mysql 多表查询 （多表联合查询）

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.列;

说明：多表查询的最终目的是将多张表的信息整合为一张大表显示，并将显示的结果信息可以做相应单表的操作处理；

数据准备

mysql 测试数据 

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;

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';

# 根据需求所需的表信息
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;

进阶：

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;

目前只能进行到  得到及格的数量。

欢迎来撩 ： 汇总all