MySQL之子查询、连接查询（内&外）以及分页查询（实操）
发布时间：2024年01月09日
文章目录
前言
续上篇博主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
- 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://blog.csdn.net/2301_77744536/article/details/135469766
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：chenni525@qq.com进行投诉反馈，一经查实，立即删除！
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：chenni525@qq.com进行投诉反馈，一经查实，立即删除！
最新文章
- Python教程
- 深入理解 MySQL 中的 HAVING 关键字和聚合函数
- Qt之QChar编码（1）
- MyBatis入门基础篇
- 用Python脚本实现FFmpeg批量转换
- 【2024系统架构设计】 系统架构设计师第二版-嵌入式系统架构设计理论与实践
- html css网页制作成品——基于HTML网上书城项目的设计与实现
- python.24.1.16函数
- 有效防范网络风险的关键措施
- 十大排序的个人总结之——选择排序
- 积分梳状滤波器CIC原理与实现
- juejin小册git的使用19高级 10：branch 删过了才想起来有用？
- c盘红色满了怎么清理c盘空间？整理了5个方法~
- 产品需求分析——目标用户分析
- 赛921上市，7万的价格到底值不值？