SQL思维训练三 思路拆分解析

糊涂码
3,054 阅读7分钟

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

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

src=http __img2.biaoqingjia.com_biaoqing_201810_2c3993f64eec252da6d674f9d80fc4e9.gif&refer=http __img2.biaoqingjia.gif

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

前言

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

简单

难度：

src=http%20__qq.yh31.com_tp_zjbq_201203060912442848.gif&refe.gif

创建表

DROP TABLE IF EXISTS `world`;
CREATE TABLE `world`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `continent` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `area` int(11) NULL DEFAULT NULL,
  `population` int(11) NULL DEFAULT NULL,
  `gbp` bigint(100) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of world
-- ----------------------------
INSERT INTO `world` VALUES (1, 'Afghanistan ', 'Asia      ', 652230, 25500100, 20343000000);
INSERT INTO `world` VALUES (2, 'Albania     ', 'Europe    ', 28748, 2831741, 12960000000);
INSERT INTO `world` VALUES (3, 'Algeria     ', 'Africa    ', 2381741, 37100000, 188681000000);
INSERT INTO `world` VALUES (4, 'Andorra     ', 'Europe    ', 468, 78115, 3712000000);
INSERT INTO `world` VALUES (5, 'Angola      Africa  ', 'Africa    ', 1246700, 20609294, 100990000000);

SET FOREIGN_KEY_CHECKS = 1

问题

如果一个国家满足下述两个条件之一，则认为该国是 大国 ：

  • 面积至少为 300 万平方公里（即，3000000 km2），或者
  • 人口至少为 2500 万（即 25000000）

编写一个 SQL 查询以报告 大国 的国家名称、人口和面积。

按 任意顺序 返回结果表。

查询结果格式如下例所示。

图片.png

分析

这个真的是非常简单 算是最基础的SQL语句 面积 至少(>=) 300万平方 或者（or） 人口至少（>=） 2500万 字面意思翻译一下就是SQL 语句

SELECT name , population ,area from world where area >= 3000000 or population >=25000000

图片.png

上面这个是最简单一种方式 但是我们知道 Or 是不走索引 所以我们应该还是有其余的解法

我们连接除了and or 还有 UNION 其实or就是将两个条件的数据集进行结合 UNION 也是这种意思 但是缺点是 需要进行两次查询 要说哪种更好 感觉我区别不大 就是一种写法上的思想

SELECT name, population, area
FROM world
WHERE area >= 3000000 
UNION
SELECT name, population, area FROM world WHERE population >= 25000000

图片.png

简单的解释一下OR 和 UNION的一些东西

对于单列来说，用or是没有任何问题的，但是or涉及到多个列的时候，每次select只能选取一个index，如果选择了area，population就需要进行table-scan，即全部扫描一遍，但是使用union就可以解决这个问题，分别使用area和population上面的index进行查询。 但是这里还会有一个问题就是，UNION会对结果进行排序去重，可能会降低一些performance(这有可能是方法一比方法二快的原因），所以最佳的选择应该是两种方法都进行尝试比较

简单一点的话可以直接使用OR 这个问题比较简单 如果思路错了就真的要好好训练一下了

中等

难度：

src=http%20__qq.yh31.com_tp_zjbq_201203060912442848.gif&refe.gifsrc=http%20__qq.yh31.com_tp_zjbq_201203060912442848.gif&refe.gif src=http%20__qq.yh31.com_tp_zjbq_201203060912442848.gif&refe.gif

创建表

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;

表对应关系：

图片.png

问题

查询"1"课程比"2"课程成绩高的学生的信息及课程分数

示例图：

图片.png

分析

这个问题 就是 学生表student 和 成绩表 score 之间的关联 首先的话我们可以分析出来 课程1 比 课程 2 成绩高的 这个就是在成绩表中机型操作 之前文章说过 一张表中出现比较第一反应就是尝试 自连接 所以我们第一步是自连接

1、成绩表自连接

select * from score sc1,score sc2

2、加入一些过滤条件

这里自连接需要带上一些条件

  • 课程 1 和 课程 2 我们只需要看这两门课程的数据 也就是表一保留课程1 表二保留课程2 方便比较 翻译SQl 语句 ===》 sc1.c_id = 1 and sc2.c_id = 2
  • 课程 1 成绩 大于课程 2 翻译SQl ===》 select * from score sc1,score sc2 where sc1.c_id = 1 and sc2.c_id = 2 and sc1.s_score > sc2.s_score
  • 同学的信息 肯定是同一个人的 课程1 成绩 大于课程2 成绩 所以加上条件 sc1.s_id = sc2.s_id

生成SQl 语句

select * from score sc1,score sc2 where sc1.c_id = 1 and sc2.c_id = 2 and  sc1.s_score > sc2.s_score and 
 sc1.s_id = sc2.s_id

图片.png

我们只需要 s_id 和 s_score 精简之后

图片.png

这个时候我们只需要拿到学生信息就行了 我们有s_id 拿到学生信息岂不是易如反掌

3、连接学生表

select st.* , sc.s_score from student st inner join  (select DISTINCT sc1.s_id,sc1.s_score from score sc1,score sc2 where sc1.s_id = sc2.s_id and   sc1.c_id = 1 and sc2.c_id = 2 and  sc1.s_score > sc2.s_score) sc on sc.s_id = st.id

得到输出结果

图片.png

答案


![图片.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/4adb033a2b724402af2f3dbb76f63963~tplv-k3u1fbpfcp-watermark.image?)

知识点

  • 表之间的自连接
  • 内连接
  • 子查询

还有一种题解 就不详细解释了 可以直接看看答案思考一下

select a.*,b.s_score as score FROM student a inner JOIN score b ON a.id = b.s_id and b.c_id= 1
LEFT JOIN score c on a.id=c.s_id and c.c_id= 2 or c.c_id = NULL
WHERE b.s_score>c.s_score

知识点

  • 内连接
  • 左连接

其实差不多基本都是一个思路

本文完结 后续持续更新相关SQL思维训练 可关注专栏或者关注本人

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

src=http __img2.biaoqingjia.com_biaoqing_201810_2c3993f64eec252da6d674f9d80fc4e9.gif&refer=http __img2.biaoqingjia.gif

推荐阅读相关文章：小白也能看到索引的使用和规则

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

avatar
文章
阅读
粉丝