第一章学生课程管理项目构建

多表连接查询环境准备
下载上述sql脚本，在远程工具连接后执行建库建表操作

mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cno   | int(11)     | NO   | PRI | NULL    |       |
| cname | varchar(20) | NO   |     | NULL    |       |
| tno   | int(11)     | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc sc;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sno   | int(11) | NO   |     | NULL    |       |
| cno   | int(11) | NO   |     | NULL    |       |
| score | int(11) | NO   |     | 0       |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc student;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| sno   | int(11)             | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20)         | NO   |     | NULL    |                |
| sage  | tinyint(3) unsigned | NO   |     | NULL    |                |
| ssex  | enum('f','m')       | NO   |     | m       |                |
+-------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc teacher;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| tno   | int(11)     | NO   | PRI | NULL    |       |
| tname | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

第二章多表查询

多表查询就是相当于讲多个表的查询结果当成一个表，再来过滤。
语法：select a.x,b.y from a join b on a.z=b.z where group by having order by limit

1.笛卡尔乘积

笛卡尔乘积在上产上不会应用，会有部分数据是无用的，仅用于学习。类似于Shell里面的嵌套循环，即Simple-Next-Loop 嵌套循环。

mysql> select * from teacher,course;
-- 等价于，生产上不要用这种，大表的结果集非常大，影响性能！！！
mysql> select * from teacher join course;
+-----+--------+------+--------+-----+
| tno | tname  | cno  | cname  | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux  | 101 |
| 101 | oldboy | 1002 | python | 102 |
| 101 | oldboy | 1003 | mysql  | 103 |
| 101 | oldboy | 1004 | k8s    | 108 |
| 102 | hesw   | 1001 | linux  | 101 |
| 102 | hesw   | 1002 | python | 102 |
| 102 | hesw   | 1003 | mysql  | 103 |
| 102 | hesw   | 1004 | k8s    | 108 |
| 103 | oldguo | 1001 | linux  | 101 |
| 103 | oldguo | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql  | 103 |
| 103 | oldguo | 1004 | k8s    | 108 |
| 104 | oldx   | 1001 | linux  | 101 |
| 104 | oldx   | 1002 | python | 102 |
| 104 | oldx   | 1003 | mysql  | 103 |
| 104 | oldx   | 1004 | k8s    | 108 |
| 105 | oldw   | 1001 | linux  | 101 |
| 105 | oldw   | 1002 | python | 102 |
| 105 | oldw   | 1003 | mysql  | 103 |
| 105 | oldw   | 1004 | k8s    | 108 |
+-----+--------+------+--------+-----+
20 rows in set (0.01 sec)

从上面的案例不难看出来，有很多数据是重复而且是无效的，这里就引入了多表查询。

2.内连接查询

内连接即inner join：取多表交集,可以省略inner,其中on表示查询条件，可以使用where替换，生产上推荐链接的表数量不要超过3张，最多四张表。
上述查询可以用以下方式实现：

mysql> select * from teacher 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 |
+-----+--------+------+--------+-----+
3 rows in set (0.00 sec)

3.外连接查询

--- 外连接：左外连接left join on;右外连接right join on,记一个就完事了！
---- join左边的就是左表，右边的就是右表。左外连接就是把左表的内容加上右表符合on条件的记录一起显示出来。
---- 这种嵌套循环匹配查询性能差，这是以后优化的重点。
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)

--- 导出查询结果(需要在命令行操作，5.7之后需要在配置文件设置安全路径secure-file-priv=/tmp)
select * from teacher join course on teacher.tno=course.tno into outfile '/tmp/bb.txt';

4.案例

-- 案例
--- 查询老师教授的课程名称
select teacher.tname,course.cname from teacher join course on teacher.tno=course.tno;

--- 统计每个学员学习几门课
select concat(student.sname,'_',student.sno) as '学生姓名',count(sc.cno) as '课程总数' 
from student join sc on student.sno=sc.sno group by student.sno,student.sname;

--- 统计每个学员，学习课程的门数和课程名
select student.sname as '学员',count(sc.cno) as '课程总数',group_concat(course.cname) as '课程名' 
from student join sc on student.sno=sc.sno join course on sc.cno=course.cno 
group by student.sno,student.sname;

--- 每位老师教的学生数量和学生名列表
SELECT teacher.tname,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; 

--- 每位老师教所教课程的平均分
select teacher.tname as '教师',course.cname as '课程名称',avg(sc.score) as '平均分'
from teacher 
join course on teacher.tno=course.tno 
join sc on course.cno=sc.cno group by teacher.tno,course.cno;

--- 练习4 ：查找学习了hesw但没学习oldguo课程的学生名。
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;

--- 练习5 ：查询出只选修了一门课程的全部学生的学号和姓名
select student.sname as '姓名',student.sno as '学号',count(sc.cno) as '课程总数' 
from student join sc on student.sno=sc.sno group by student.sno,student.sname
having count(sc.cno)  = 1;

--- 练习6 ：查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select course.cno ,MAX(sc.score),MIN(sc.score)
from course
join sc 
on course.cno=sc.cno 
group by course.cno;

--- 练习7 ：查询平均成绩大于85的所有学生的学号、姓名和平均成绩  
select student.sno as '学号',student.sname as '姓名',avg(sc.score)
from student join sc on student.sno=sc.sno group by student.sno,student.sname
having avg(sc.score)>85;

--- 练习8 ：统计各位老师,所教课程的及格率
select 
teacher.tname, 
CONCAT(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;

--- 练习9 ：统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
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

第一章 学生课程管理项目构建

第二章 多表查询