前言

通过思路解析 分析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;

表对应关系：

中等

难度：

问题

查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

示例

友情提示： 可以不看分析练习一下 看一下能不能写出效果

分析

这个首先来说比较简单 检索几个比较有用次

• 平均成绩 ===》avg
• 大于等于60 ===》>= 60
• 学生（student） 成绩（score）
• 求学生的平均成绩肯定要数据隔离 且 统一 也就是分组 ===》 group up
• 筛选出大于等于60的学生 不知道记不记得 having 这个和 group up 连用的东西 相当于 group by 的 where

拼接SQL

select s.id,s.s_name,avg(sco.s_score) AS 平均成绩 from student s inner join score sco
on s.id = sco.s_id GROUP BY sco.s_id HAVING avg(sco.s_score) >= 60

知识点

• 内连接
• 分组 group by
• 筛选 having
• 平均值 avg
• 还有一个可以对结果的数字小数点 进行保留位数 ROUND

中等

难度：

问题

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

示例

分析

这两个题基本都是 分组统计 所以上一个题 做出来或者 有一定基础的人会感觉比较简单

• 课程的数量和总和 分别对应 count 和 sum
• 同一个学生 group by
• 连个表进行连接 student 为主表 left join

SQL 语句：

select st.id,st.s_name,count(sc.c_id) AS 课程数,sum(sc.s_score) 成绩总计
from student st left join score sc on sc.s_id =  st.id GROUP BY st.id

知识点

• 左连接

• 分组 group by

• 计数 count 求和 sum

总结

在解决这种统计数据的时候 可以选择去使用group by 进行输一局分组 但是 MySQL 中有宽松模式 和 严格模式

宽松模式下 select 可以查询 group by 中没有的字段

严格模式下 select 不可以查询除啦 group by 中的字段

例如 ： 宽松模式下 select name，age from student group by name 不会出现错误

严格模式下 select name，age from student group by name 会出现报错 select name，age from student group by name，age 必须要这样写

这里还有一个细节就是在进行表连接的时候我们通过left join 进行数据连接 因为我们不想损失 student 表中的数据 哪怕这个学生没有选择课程和没有成绩 我们也要查询出来这个学生 在使用连接的时候要根据实际的数据情况去进行判断使用哪一个

末尾

周日愉快 文章完结🥰

持续更新SQL相关系列 最近主要是找一些比较有意思的SQL练习题 可追更