MySQL多表连接项目案例实践

MySQL多表连接项目案例实践 老男孩Linux运维MySQL课程
（1）准备工作（表名和表结构）

use school
student ：学生表
sno：  学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表
tno：     教师编号
tname：   教师名字

course ：课程表
cno：  课程编号
cname：课程名字
tno：  教师编号

score  ：成绩表
sno：  学号
cno：  课程编号
score：成绩

（2）各个表关联关系

（3）生成表格及插入数据

/* oldboyedu.com by oldboy 
Date: 05/12/2021 10:32:02 */

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
create database school CHARSET=utf8mb4;
use school;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cno` int(11) NOT NULL COMMENT '课程编号',
  `cname` varchar(20) NOT NULL COMMENT '课程名字',
  `tno` int(11) NOT NULL COMMENT '教师编号',
  PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of course
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES (1001, 'linux', 101);
INSERT INTO `course` VALUES (1002, 'python', 102);
INSERT INTO `course` VALUES (1003, 'mysql', 103);
INSERT INTO `course` VALUES (1004, 'k8s', 108);
COMMIT;
-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `sno` int(11) NOT NULL COMMENT '学号',
  `cno` int(11) NOT NULL COMMENT '课程编号',
  `score` int(11) NOT NULL DEFAULT '0' COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of sc
-- ----------------------------
BEGIN;
INSERT INTO `sc` VALUES (1, 1001, 80);
INSERT INTO `sc` VALUES (1, 1002, 59);
INSERT INTO `sc` VALUES (2, 1002, 90);
INSERT INTO `sc` VALUES (2, 1003, 100);
INSERT INTO `sc` VALUES (3, 1001, 99);
INSERT INTO `sc` VALUES (3, 1003, 40);
INSERT INTO `sc` VALUES (4, 1001, 79);
INSERT INTO `sc` VALUES (4, 1002, 61);
INSERT INTO `sc` VALUES (4, 1003, 99);
INSERT INTO `sc` VALUES (5, 1003, 40);
INSERT INTO `sc` VALUES (6, 1001, 89);
INSERT INTO `sc` VALUES (6, 1003, 77);
INSERT INTO `sc` VALUES (7, 1001, 67);
INSERT INTO `sc` VALUES (7, 1003, 82);
INSERT INTO `sc` VALUES (8, 1001, 70);
INSERT INTO `sc` VALUES (9, 1003, 80);
INSERT INTO `sc` VALUES (10, 1003, 96);
COMMIT;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sno` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `sname` varchar(20) NOT NULL COMMENT '姓名',
  `sage` tinyint(3) unsigned NOT NULL COMMENT '年龄',
  `ssex` enum('f','m') NOT NULL DEFAULT 'm' COMMENT '性别',
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES (1, 'zhangs', 18, 'm');
INSERT INTO `student` VALUES (2, 'lisi88', 18, 'm');
INSERT INTO `student` VALUES (3, 'wangwu', 22, 'm');
INSERT INTO `student` VALUES (4, 'laoliu', 19, 'f');
INSERT INTO `student` VALUES (5, 'zhao4m', 18, 'm');
INSERT INTO `student` VALUES (6, 'liuxia', 18, 'm');
INSERT INTO `student` VALUES (7, 'matian', 19, 'f');
INSERT INTO `student` VALUES (8, 'oldboy', 20, 'm');
INSERT INTO `student` VALUES (9, 'oldgirl', 20, 'f');
INSERT INTO `student` VALUES (10, 'oldpod', 25, 'm');
COMMIT;
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tno` int(11) NOT NULL COMMENT '教师编号',
  `tname` varchar(20) NOT NULL COMMENT '教师名字',
  PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of teacher
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES (101, 'oldboy');
INSERT INTO `teacher` VALUES (102, 'ran');
INSERT INTO `teacher` VALUES (103, 'lisir');
INSERT INTO `teacher` VALUES (104, 'Tim');
INSERT INTO `teacher` VALUES (105, 'Jack');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;

项目构建：
source /root/school.sql

（4）多表连接查询数据实践

#实践练习 
#1.统计下每个学生的平均成绩 
解答：
#a.分析题意,找出所有相关表student、sc
#b.找到以上表的直接或间接关联条件，student.sno，sc.sno
#c.列举要查询的列条件，student.sname，sc.score 
#d.组合最终结果：
mysql> SELECT student.sname,AVG(sc.score) FROM student JOIN sc ON student.sno=sc.sno GROUP BY student.sno;
+---------+---------------+
| sname   | AVG(sc.score) |
+---------+---------------+
| zhangs  |       69.5000 |
| lisi88  |       95.0000 |
...省略若干行...
| oldgirl |       80.0000 |
| oldpod  |       96.0000 |
+---------+---------------+

#2.统计每位学生学习的课程门数
mysql> SELECT student.sname,COUNT(sc.score) FROM student JOIN sc  ON student.sno=sc.sno GROUP BY student.sno;
+---------+-----------------+
| sname   | COUNT(sc.score) |
+---------+-----------------+
| zhangs  |               2 |
| lisi88  |               2 |
...省略若干行...
| oldgirl |               1 |
| oldpod  |               1 |
+---------+-----------------+

#3.统计每位老师所教的课程门数
mysql> SELECT teacher.tname ,COUNT(course.cname) FROM teacher JOIN course ON teacher.tno=course.tno GROUP BY teacher.tno;
+--------+---------------------+
| tname  | COUNT(course.cname) |
+--------+---------------------+
| oldboy |                   1 |
| ran    |                   1 |
| lisir  |                   1 |
+--------+---------------------+
#4.每位老师所教的课程门数和名称
mysql> SELECT teacher.tname ,COUNT(course.cname),GROUP_CONCAT(course.cname)
    -> FROM teacher JOIN course ON teacher.tno=course.tno
    -> GROUP BY teacher.tno;
+--------+---------------------+----------------------------+
| tname  | COUNT(course.cname) | GROUP_CONCAT(course.cname) |
+--------+---------------------+----------------------------+
| oldboy |                   1 | linux                      |
| ran    |                   1 | python                     |
| lisir  |                   1 | mysql                      |
+--------+---------------------+----------------------------+

#5.每位学生学习的课程门数和名称
mysql> SELECT student.sname,COUNT(sc.score),GROUP_CONCAT(course.cname)
    -> FROM student
    -> JOIN sc 
    -> ON student.sno=sc.sno
    -> JOIN course
    -> ON sc.cno=course.cno
    -> GROUP BY student.sno;
+---------+-----------------+----------------------------+
| sname   | COUNT(sc.score) | GROUP_CONCAT(course.cname) |
+---------+-----------------+----------------------------+
| zhangs  |               2 | python,linux               |
...省略若干行...
| oldboy  |               1 | linux                      |
| oldgirl |               1 | mysql                      |
| oldpod  |               1 | mysql                      |
+---------+-----------------+----------------------------+

（5）多表SQL考试题

1. 统计zhangs,学习了几门课

2. 查询zhangs,学习的课程名称有哪些?

3. 查询oldboy老师教的学生名.

4. 查询oldboy所教课程的平均分数

5. 每位老师所教课程的平均分,并按平均分排序

6. 查询ran所教的不及格的学生姓名

7. 查询所有老师所教学生不及格的信息

8. 查询平均成绩大于60分的同学的学号和平均成绩；

9. 查询所有同学的学号、姓名、选课数、总成绩；

10.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

11.查询每门课程被选修的学生数

12.查询出只选修了一门课程的全部学生的学号和姓名

13.查询选修课程门数超过1门的学生信息

14.查询平均成绩大于85的所有学生的学号、姓名和平均成绩

15.统计各位老师,所教课程的及格率

16.统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表

 《本内容来自老男孩教育MySQL部分教学》，交流数据库技术可加右上角联系方式