05MySQL-SQL语句-多表查询

05MySQL-SQL语句-多表查询

Deng YongJie's blog 191 2022-02-26

第1章 多表查询

1.多表查询类型

1.1 笛卡尔乘积

select * from teacher,course;
或者：
select * from teacher join course;
拿着 teacher每⾏数据和course逐⾏进⾏组合，显示两层for循环的实现逻辑。Simple-Nextloop （嵌套循环⽅式）得出的结果，会有部分数据是⽆意义的

1.2 内连接 join 取交集

mysql> select * from teacher join course on teacher.tno=course.tno ;
+-----+--------+------+--------+-----+
| tno | tname | cno | cname | tno |
+-----+--------+------+--------+-----+
| 101 | oldjie | 1001 | linux | 101 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql | 103 |
+-----+--------+------+--------+-----+
3 rows in set (0.00 sec)
mysql> select * from teacher,course where teacher.tno=course.tno;
+-----+--------+------+--------+-----+
| tno | tname | cno | cname | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql | 103 |
+-----+--------+------+--------+-----+
3 rows in set (0.00 sec)

1.3 外连接 left join , right join

mysql> select * from teacher left join course on
teacher.tno=course.tno;
+-----+--------+------+--------+------+
| tno | tname | cno | cname | tno |
+-----+--------+------+--------+------+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql | 103 |
| 104 | oldx | NULL | NULL | NULL |
| 105 | oldw | NULL | NULL | NULL |
+-----+--------+------+--------+------+
5 rows in set (0.00 sec)
mysql> select * from teacher right join course on
teacher.tno=course.tno;
+------+--------+------+--------+-----+
| tno | tname | cno | cname | tno |
+------+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql | 103 |
| NULL | NULL | 1004 | k8s | 108 |
+------+--------+------+--------+-----+
4 rows in set (0.00 sec)

2.多表连接语法

2.1 a表 和 b表 有直接的关联关系

select a.x,b.y from a join b on a.z=b.z where group by having
order by limit;
select a.x,b.y #查找的内容
from a
join b #a关联b
on a.z=b.z #关联条件
where #其他条件
group by #分组依据
having #分组后判断
order by #排序规则
limit; #显示条⽬

2.2 a表 和 b表 没有直接的关联关系

假如：a和c 有关，b和c有关
a join c on a.i = c.j join b on c.x=b.y
a join c
on a.i = c.j
join b
on c.x=b.y

2.3 套路

1.根据题意将所有涉及到的表找出来 a b
2.找到a和b直接或者间接的关联条件
3.⽤join on 语句把所有表连接到⼀起
4.罗列其他查询条件

3.⼤量练习

image-20210625093738813

3.1 导⼊数据

source /root/school.sql

3.2 每位⽼师所教课程名称

select
teacher.tname,course.cname
from teacher
join course
on teacher.tno=course.tno;

3.3 统计每个学员，学习课程的⻔数

select student.sname'学⽣姓名',COUNT(*)'学习⻔数'
from student
join sc
on student.sno=sc.sno
GROUP BY student.sno;

3.4 统计每个学员，学习课程的⻔数和课程名列表

student ----> sc ----> course ---> teacher

语句：

select
CONCAT(student.sname,"_",student.sno),COUNT(*),GROUP_CONCAT(course.cname
)
from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
group by student.sno

3.5 每位⽼师教的学⽣数量和学⽣名列表

student ----> sc ----> course ---> teacher

语句：

select CONCAT(teacher.tname,"_",teacher.tno),COUNT(*),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.tno

3.6 每位⽼师教所教课程的平均分

select
CONCAT(teacher.tname,"_",teacher.tno,"_",course.cno),AVG(sc.score)
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
group by teacher.tno , course.cno

3.7 查找学习了hesw但没学习oldguo课程的学⽣名

case⽤法：

select case when 1=1 then "true" end
USE mysql;
SELECT
case
WHEN USER='root' THEN HOST END,
WHEN USER !='root' THEN 2 END
FROM mysql.user;
USE mysql;
SELECT
CASE
WHEN USER='root' THEN HOST
WHEN USER !='root' THEN 2 END
FROM mysql.user;

⽅法1:

select a.sname from
a
left join
b
on a.sname=b.sname
where b.sname is null;
select a.sname from
(select 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 teacher.tname = 'hesw') as a
left join
(select 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 teacher.tname = 'oldguo') as b
on a.sname=b.sname
where b.sname is null

⽅法2:

SELECT student.`sname`,GROUP_CONCAT(teacher.`tname`)
FROM course
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
JOIN teacher
ON course.tno=teacher.tno
GROUP BY student.sname
HAVING GROUP_CONCAT(teacher.`tname`) LIKE '%hesw%' AND
GROUP_CONCAT(teacher.`tname`) NOT LIKE '%oldguo%';

3.8 查询出只选修了⼀⻔课程的全部学⽣的学号和姓名

SELECT student.sname,student.sno,COUNT(sc.cno)
FROM sc
JOIN student
ON sc.sno=student.sno
GROUP BY sc.sno
HAVING COUNT(sc.cno)=1;

3.9 查询各科成绩最⾼和最低的分：以如下形式显示：课程名称，最⾼分，最低分

SELECT course.cname'课程名称',MAX(sc.`score`)'最分',MIN(sc.`score`)'最低分'
FROM sc
JOIN course
ON sc.cno=course.cno
GROUP BY course.cname;

3.10 查询平均成绩⼤于85的所有学⽣的学号、姓名和平均成绩

select sc.sno,student.sname,AVG(sc.score)
from sc
join student
on sc.sno=student.sno
group by sc.sno
having AVG(sc.score)>85;

3.11 统计每⻔课程:优秀(85分以上),良好(70-85),⼀般(60-70),不及格(⼩于60)的 学⽣列表

select
course.cname ,
GROUP_CONCAT(case when sc.score>=85 then student.sname end),
GROUP_CONCAT(case when sc.score>=70 and sc.score<85 then student.sname
end),
GROUP_CONCAT(case when sc.score>=60 and sc.score<70 then student.sname
end),
GROUP_CONCAT(case when sc.score<60 then student.sname end)
from course
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
group by course.cno
SELECT course.cname,
GROUP_CONCAT(CASE WHEN sc.score>=85 THEN
CONCAT(student.sname,":",sc.score) END)'优秀',
GROUP_CONCAT(CASE WHEN sc.score>=75 AND sc.`score`<85 THEN
CONCAT(student.sname,":",sc.score) END)'良好',
GROUP_CONCAT(CASE WHEN sc.score>=60 AND sc.`score`<75 THEN
CONCAT(student.sname,":",sc.score) END)'⼀般'
FROM course
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
GROUP BY course.cname;

3.12 表别名使⽤

select
a.cname ,
GROUP_CONCAT(case when b.score>=85 then c.sname end),
GROUP_CONCAT(case when b.score>=70 and b.score<85 then c.sname end),
GROUP_CONCAT(case when b.score>=60 and b.score<70 then c.sname end),
GROUP_CONCAT(case when b.score<60 then c.sname end)
from course as a
join sc as b
on a.cno=b.cno
join student as c
on b.sno=c.sno
group by a.cno

3.13 列别名

select
a.cname as "课程名称" ,
GROUP_CONCAT(case when b.score>=85 then c.sname end) as "优秀学员",
GROUP_CONCAT(case when b.score>=70 and b.score<85 then c.sname end) as
"良好学员",
GROUP_CONCAT(case when b.score>=60 and b.score<70 then c.sname end) as
"⼀般学员",
GROUP_CONCAT(case when b.score<60 then c.sname end) as "不及格学员"
from course as a
join sc as b
on a.cno=b.cno
join student as c
on b.sno=c.sno
group by a.cno

第2章 元数据获取

1.常⽤show语句

help show;
show databases; # 查询所有库名
show tables; # 查询当前库的所有表名
show tables from world; # 查询world库下的所有表名
show create database world; # 查询world建库语句
show create table city; # 当前库下的city表建表语句
show create table world.city; # world库下的建表语句
show privileges; # 数据库中所有权限
show engines; # 数据库中⽀持的存储引擎
show grants for root@'localhost' # 查询某个⽤户权限
show charset; # 查询数据库字符集⽀持
show collation; # 查询所有校对规则的⽀持
show variables like '%trx%' # 查询数据库参数
show status like 'com_%' # 查询数据库的状态
show processlist; # 查询所有会话信息
show engine innodb status # 查询innodb引擎相关的状态
show binary logs # 查询⼆进制⽇志⽂件信息
show binlog events in 'xxx' # 查看⼆进制⽇志事件
show master status ; # 当前正在使⽤的⼆进制⽇志信息
show slave status\G # 查看主从状态相关信息
show slave hosts; # 查看从库主机信息
MySQL