前言
续上篇博主MySQL之视图&索引&执行计划这篇给大家讲解MySQL之子查询、连接查询(内&外)以及分页查询
一、SQL脚本
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 80018
Source Host : localhost:3306
Source Schema : mybatis_ssm
Target Server Type : MySQL
Target Server Version : 80018
File Encoding : 65001
Date: 04/07/2023 23:53:33
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_mysql_course
-- ----------------------------
DROP TABLE IF EXISTS `t_mysql_course`;
CREATE TABLE `t_mysql_course` (
`cid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程编号',
`cname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程名称',
`tid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师编号',
PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '课程信息表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_mysql_course
-- ----------------------------
INSERT INTO `t_mysql_course` VALUES ('01', '语文', '02');
INSERT INTO `t_mysql_course` VALUES ('02', '数学', '01');
INSERT INTO `t_mysql_course` VALUES ('03', '英语', '03');
-- ----------------------------
-- Table structure for t_mysql_score
-- ----------------------------
DROP TABLE IF EXISTS `t_mysql_score`;
CREATE TABLE `t_mysql_score` (
`sid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生编号，外键',
`cid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程编号，外键',
`score` float NULL DEFAULT 0 COMMENT '成绩',
INDEX `sid`(`sid`) USING BTREE,
INDEX `cid`(`cid`) USING BTREE,
CONSTRAINT `t_mysql_score_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `t_mysql_student` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `t_mysql_score_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `t_mysql_course` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '成绩信息表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_mysql_score
-- ----------------------------
INSERT INTO `t_mysql_score` VALUES ('01', '01', 80);
INSERT INTO `t_mysql_score` VALUES ('01', '02', 90);
INSERT INTO `t_mysql_score` VALUES ('01', '03', 99);
INSERT INTO `t_mysql_score` VALUES ('02', '01', 70);
INSERT INTO `t_mysql_score` VALUES ('02', '02', 60);
INSERT INTO `t_mysql_score` VALUES ('02', '03', 80);
INSERT INTO `t_mysql_score` VALUES ('03', '01', 80);
INSERT INTO `t_mysql_score` VALUES ('03', '02', 80);
INSERT INTO `t_mysql_score` VALUES ('03', '03', 80);
INSERT INTO `t_mysql_score` VALUES ('04', '01', 50);
INSERT INTO `t_mysql_score` VALUES ('04', '02', 30);
INSERT INTO `t_mysql_score` VALUES ('04', '03', 20);
INSERT INTO `t_mysql_score` VALUES ('05', '01', 76);
INSERT INTO `t_mysql_score` VALUES ('05', '02', 87);
INSERT INTO `t_mysql_score` VALUES ('06', '01', 31);
INSERT INTO `t_mysql_score` VALUES ('06', '03', 34);
INSERT INTO `t_mysql_score` VALUES ('07', '02', 89);
INSERT INTO `t_mysql_score` VALUES ('07', '03', 98);
-- ----------------------------
-- Table structure for t_mysql_student
-- ----------------------------
DROP TABLE IF EXISTS `t_mysql_student`;
CREATE TABLE `t_mysql_student` (
`sid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生编号',
`sname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生名称',
`sage` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生年龄',
`ssex` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生性别',
PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '学生信息表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_mysql_student
-- ----------------------------
INSERT INTO `t_mysql_student` VALUES ('01', '赵雷', '1990-01-01', '男');
INSERT INTO `t_mysql_student` VALUES ('02', '钱电', '1990-12-21', '男');
INSERT INTO `t_mysql_student` VALUES ('03', '孙风', '1990-12-20', '男');
INSERT INTO `t_mysql_student` VALUES ('04', '李云', '1990-12-06', '男');
INSERT INTO `t_mysql_student` VALUES ('05', '周梅', '1991-12-01', '女');
INSERT INTO `t_mysql_student` VALUES ('06', '吴兰', '1992-01-01', '女');
INSERT INTO `t_mysql_student` VALUES ('07', '郑竹', '1989-01-01', '女');
INSERT INTO `t_mysql_student` VALUES ('09', '张三', '2017-12-20', '女');
INSERT INTO `t_mysql_student` VALUES ('10', '李四', '2017-12-25', '女');
INSERT INTO `t_mysql_student` VALUES ('11', '李四', '2012-06-06', '女');
INSERT INTO `t_mysql_student` VALUES ('12', '赵六', '2013-06-13', '女');
INSERT INTO `t_mysql_student` VALUES ('13', '孙七', '2014-06-01', '女');
-- ----------------------------
-- Table structure for t_mysql_teacher
-- ----------------------------
DROP TABLE IF EXISTS `t_mysql_teacher`;
CREATE TABLE `t_mysql_teacher` (
`tid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师编号',
`tname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师名称',
PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '教师信息表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_mysql_teacher
-- ----------------------------
INSERT INTO `t_mysql_teacher` VALUES ('01', '张三');
INSERT INTO `t_mysql_teacher` VALUES ('02', '李四');
INSERT INTO `t_mysql_teacher` VALUES ('03', '王五');
SET FOREIGN_KEY_CHECKS = 1;
二、实操以及实现思路
- 09）查询学过「张三」老师授课的同学的信息
SELECT
s.*,
c.cname,
t.tname,
sc.score
FROM
t_mysql_teacher t,
t_mysql_course c,
t_mysql_student s,
t_mysql_score sc
WHERE
t.tid = c.tid
AND c.cid = sc.cid
AND sc.sid = s.sid
AND t.tname = '张三'
- 10）查询没有学全所有课程的同学的信息
分析：没有学全，学全了有多少门，统计一共有多少门学科
统计每一个学生学了多少门
SELECT
s.sid,
s.sname,
count( sc.score ) n
FROM
t_mysql_student s
LEFT JOIN t_mysql_score sc ON s.sid = sc.sid
GROUP BY
s.sid,
s.sname
HAVING
n <(
SELECT
count( 1 )
FROM
t_mysql_course)
- 11）查询没学过"张三"老师讲授的任一门课程的学生姓名
分析：子查询
张三教了哪些课程
找学过张三的课程有哪些人
SELECT
s.sid,
s.sname
FROM
t_mysql_score sc,
t_mysql_student s
WHERE
s.sid = sc.sid
AND sc.cid NOT IN ( SELECT cid FROM t_mysql_course c, t_mysql_teacher t WHERE c.tid = t.tid AND t.tname = '张三' )
GROUP BY
s.sid,
s.sname
- 12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
分析：聚合函数 内连接 要先筛选再分组
SELECT
s.sid,
s.sname,
avg( sc.score )
FROM
t_mysql_student s,
t_mysql_score sc
WHERE
s.sid = sc.sid
AND sc.score < 60
GROUP BY
s.sid,
s.sname
- 13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT
s.*,
sc.score
FROM
t_mysql_student s,
t_mysql_score sc
WHERE
s.sid = sc.sid
AND sc.cid = '01'
AND sc.score < 60
ORDER BY
sc.score DESC
- 14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
分析：聚合函数 GROUP BY
从高到低 ORDER BY DESC
所有学生的所有课程的成绩
姓名 语文 数学 英语 平均成绩
SELECT
s.sid,
s.sname,
sum((case when sc.cid='01' then sc.score end))语文,
sum((case when sc.cid='02' then sc.score end))数学,
sum((case when sc.cid='03' then sc.score END))英语,
round(avg(sc.score),2) 平均分
FROM
t_mysql_score sc
RIGHT JOIN t_mysql_student s ON sc.sid = s.sid
GROUP BY
s.sid,
s.sname
SELECT
s.sid,
s.sname,
sum(if(sc.cid='01',sc.score,0))语文,
sum(if(sc.cid='02',sc.score,0))数学,
sum(if(sc.cid='03',sc.score,0))英语,
round(avg(sc.score),2) 平均分
FROM
t_mysql_score sc
RIGHT JOIN t_mysql_student s ON sc.sid = s.sid
GROUP BY
s.sid,
s.sname
文章来源：https://www.toymoban.com/news/detail-817766.html
- 15）查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
分析：聚合函数 GROUP BY
大量使用函数
SELECT
c.cid,c.cname,
count(sc.score) 人数,
max(sc.score) 最高分,
min(sc.score) 最低分,
round(avg(sc.score),2) 平均分,
concat(round(sum(if(sc.score>=60,1,0))/(select count(1) from t_mysql_student)*100,2),'%') 及格率,
concat(round(sum(if(sc.score>=70 and sc.score<=80,1,0))/(select count(1) from t_mysql_student)*100,2),'%') 中等率,
concat(round(sum(if(sc.score>80 and sc.score<=90,1,0))/(select count(1) from t_mysql_student)*100,2),'%') 优良率,
concat(round(sum(if(sc.score>=90,1,0))/(select count(1) from t_mysql_student)*100,2),'%') 优秀率
FROM
t_mysql_score sc
LEFT JOIN t_mysql_course c ON sc.cid = c.cid
group by
c.cid,c.cname
文章来源地址https://www.toymoban.com/news/detail-817766.html
到了这里，关于MySQL之子查询、连接查询（内&外）以及分页查询（实操）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
