第一章 学生课程管理项目构建
多表连接查询环境准备
下载上述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