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;
表对应关系：
中等
难度：
问题
查询平均成绩大于等于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练习题 可追更
本文正在参加「技术专题19期 漫谈数据库技术」活动
