MySQl 专栏持续更新 不说晦涩难懂的东西 尽量输出容易理解 和 使用的SQL技巧 和 初中级开发不是很常用的但很有用的知识

欢迎查看👉🏻👉🏻👉🏻SQL 专栏 查漏补缺 指教一二

本文正在参加「技术专题19期 漫谈数据库技术」活动

前言

通过思路解析 分析SQL书写 拆分逻辑 简单易懂 跟着学习 等系列更新完 SQL编写能力 和 SQL思维都会有提升 欢迎关注专栏 如果有更简单的接替方法 可以发在评论区会补充完善

创建表

如果已经创建过表的请跳过

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `c_id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `t_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '语文', 2);
INSERT INTO `course` VALUES (2, '数学', 1);
INSERT INTO `course` VALUES (3, '英语', 3);

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `c_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `s_score` int(3) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', 80);
INSERT INTO `score` VALUES ('1', '2', 90);
INSERT INTO `score` VALUES ('1', '3', 99);
INSERT INTO `score` VALUES ('2', '1', 70);
INSERT INTO `score` VALUES ('2', '3', 60);
INSERT INTO `score` VALUES ('2', '4', 80);
INSERT INTO `score` VALUES ('3', '1', 80);
INSERT INTO `score` VALUES ('3', '1', 80);
INSERT INTO `score` VALUES ('3', '3', 80);
INSERT INTO `score` VALUES ('4', '1', 50);
INSERT INTO `score` VALUES ('4', '2', 30);
INSERT INTO `score` VALUES ('4', '3', 20);
INSERT INTO `score` VALUES ('5', '1', 76);
INSERT INTO `score` VALUES ('5', '2', 87);
INSERT INTO `score` VALUES ('6', '1', 31);
INSERT INTO `score` VALUES ('6', '3', 34);
INSERT INTO `score` VALUES ('7', '2', 89);
INSERT INTO `score` VALUES ('7', '3', 98);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `s_code` int(100) NULL DEFAULT NULL,
  `s_sex` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `s_birth` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `普通索引`(`s_sex`) USING BTREE,
  INDEX `联合索引`(`s_name`, `s_code`, `s_birth`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '学生1', 1, '男', '2022-11-20');
INSERT INTO `student` VALUES (2, '学生2', 2, '男', '2022-11-20');
INSERT INTO `student` VALUES (3, '变成派大星', 3, '男', '2022-11-20');
INSERT INTO `student` VALUES (4, '学生4', 4, '男', '2022-11-20');
INSERT INTO `student` VALUES (5, '学生5', 5, '女', '2022-11-20');
INSERT INTO `student` VALUES (6, '学生6', 6, '女', '2022-11-20');
INSERT INTO `student` VALUES (7, '学生7', 7, '女', '2022-11-20');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `t_id` int(11) NOT NULL AUTO_INCREMENT,
  `t_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '泡芙老师');
INSERT INTO `teacher` VALUES (2, '蟹老板');
INSERT INTO `teacher` VALUES (3, '章鱼哥');

SET FOREIGN_KEY_CHECKS = 1;

表对应关系：

中等

难度：

问题

查询没学过泡芙老师老师授课的同学的信息

分析

首先我们可以从上面的图中明白 四张表之间的关系 我们提取关键词

• 学生信息 student
• 老师 teacher
• 课程 course
• 判断是否学过 这门课程 score
• 找到未学习

这是一个四张表都能用到的问题 但是思维逻辑比较简单 如果没有思路 可能是经验少

首先是 进行表连接我们需要查看的是泡芙老师 所以我们可以直接进行范围的缩小

select * from teacher te inner join course co on te.t_id = co.t_id and te.t_id= 1

我这边直接使用的内连接 下一张表就是score 这里面包含课程和学生信息

我们要寻找未学习的 我们都是到表连接的几方式 其中右连接 我们非常适合使用在这里

• 我们是以右表为主 当这个学生没有选择 老师的课程那么字段就会为空 score 表中我们最需要的是s_id

select * from teacher te inner join course co on te.t_id = co.t_id and te.t_id= 1 right join score sc on sc.c_id = co.c_id

我们可以看到 右和没有之间是有差异的 我们可以先查出学习的 然后通过not in 进行数据排除

完整SQL：

select * from student st where st.id not in (select sc.s_id from teacher te inner join course co on te.t_id = co.t_id and te.t_id= 1 right join score sc on sc.c_id = co.c_id where !ISNULL(co.t_id ))

知识点

• 内连接
• 右连接
• 子查询

中等

难度：

问题

查询学过编号为"1"并且也学过编号为2"的课程的同学的信息

示例

分析

• 学生信息 student
• 学过的课程 score

这个就是两张表之间的连接 乍一看可能不难的但是去写的话还是有点弯弯的

首先我们先来写一些基本的SQL 我们知道 是课程 1 和 课程 2 所以能直接当做固定条件 但是怎么去判断 是不是同一个Id 呢 我们可以将这个表进行自连接比较 然后判断s_id 相同的数据

查询出数据 我们这里可以使用子查询 或这是表连接

我们就使用连接吧 如果能不使用子查询就不使用子查询

这里的弯弯只有你自己去写才会明白思路会出现偏差

知识点

• 自连接

中等

难度：

问题

查询没有学全所有课程的同学的信息

示例

分析

这个思路的比较简单

• 三个课程全部学过 也就是score 里面至少有三个s_id 相同的字段 但是 每个s_id 对应的 c_id 不相同
• 怎么去判断上面的条件 那就是自连接 进行三张表连接
• 找出三张表中的s_id 相同的字段 同时 c_id 相同

最简单的写法

select * from  score sc ,score sc1,score sc2 where sc.s_id = sc1.s_id and sc1.s_id = sc2.s_id and sc.c_id = 1 and sc1.c_id = 2 and sc2.c_id = 3

然后再连接student 表进行最终数据查询

结果是正确了 但是总是感觉哪里不合适 因为我们的可能如果不是三个 那就会麻烦 后续要再思考一下其他写法

知识点

• 内连接
• 自连接

经过一段时间的练习 感觉自连接还是很有意思的 之前工作中很少使用 但是再处理一张表的时候自联结真的 YYDS 当一张表中的数据需要进行比较的时候 可以考虑一下自联结 先尝试一下看看能不能走通

有时间再思考一下最优解

本文正在参加「技术专题19期 漫谈数据库技术」活动