这一节从group by和having两个关键语法入手，学习一下写sql的基本思路。

数据库准备：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
-- ----------------------------
-- 学生表
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- student表数据
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'lilei', '19', 'female');
INSERT INTO `student` VALUES ('2', 'huangmeimei', '18', 'male');
INSERT INTO `student` VALUES ('3', 'pollu', '17', 'female');
INSERT INTO `student` VALUES ('4', 'tom', '18', 'male');
INSERT INTO `student` VALUES ('5', 'david', '17', 'male');
INSERT INTO `student` VALUES ('6', 'lucy', '19', 'female');
INSERT INTO `student` VALUES ('7', 'jacky', '20', 'male');

-- ----------------------------
-- 课程表
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `course_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`course_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- course表数据
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'chinese');
INSERT INTO `course` VALUES ('2', 'math');
INSERT INTO `course` VALUES ('3', 'english');
INSERT INTO `course` VALUES ('4', 'physics');

-- ----------------------------
-- 分数表
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `student_id` int(11) DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- score表数据
-- ----------------------------
INSERT INTO `score` VALUES ('1', '2', '78');
INSERT INTO `score` VALUES ('1', '3', '67');
INSERT INTO `score` VALUES ('1', '4', '67');
INSERT INTO `score` VALUES ('2', '1', '52');
INSERT INTO `score` VALUES ('2', '2', '81');
INSERT INTO `score` VALUES ('2', '3', '92');
INSERT INTO `score` VALUES ('2', '4', '67');
INSERT INTO `score` VALUES ('3', '1', '52');
INSERT INTO `score` VALUES ('3', '2', '47');
INSERT INTO `score` VALUES ('3', '3', '88');
INSERT INTO `score` VALUES ('3', '4', '67');
INSERT INTO `score` VALUES ('4', '2', '88');
INSERT INTO `score` VALUES ('4', '3', '90');
INSERT INTO `score` VALUES ('4', '4', '67');
INSERT INTO `score` VALUES ('5', '1', '52');
INSERT INTO `score` VALUES ('5', '3', '78');
INSERT INTO `score` VALUES ('5', '4', '67');
INSERT INTO `score` VALUES ('6', '1', '52');
INSERT INTO `score` VALUES ('6', '2', '68');
INSERT INTO `score` VALUES ('6', '4', '67');
INSERT INTO `score` VALUES ('1', '1', '52');
INSERT INTO `score` VALUES ('5', '2', '72');
INSERT INTO `score` VALUES ('7', '2', '72');

第一个问题

查询所有同学的学号、选课数、总成绩

针对sql问题，不需要一口气全部写出来，我们先分解逐个击破，最后再合体。

分析题目，他要查询的是每个学生的学号，选课数，总成绩

那么先把关键字列出来：

1
2
3
4
5
#首先是要查询，肯定有select关键字
select

#要查询的几个关键字段
student_id,count(course_id),sum(score)

我们看到，有一些函数在里面，比如count和sum，那么我们会想到一般情况下是与group by结合使用的。

因为是查询每个学生，那么必然是根据每个学生的id进行分组了。

1
group by student_id

此时，因为涉及的student_id,course_id以及score只需要一张score表就可以解决,那么拼接起来就是：

image

我们进行explain分析一下：

1
2
3
explain SELECT student_id,count(course_id),sum(score)
from score
group by student_id

显示：

image

基本的原理就是：首先根据group by进行分组，分组出来的数据缓存到一张临时表中，然后再做count之类的计算显示。

并且，本题是针对一张表，所以有一个规则是：如果用group by，那么你的select语句中选出的列要么是group by里用到的列，要么就是sum min等列函数的列。所以这里group by和后面是student_id，所以select后面可以查询student_id，但是不能查询course_id等字段。

第二个问题

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

注意观察，其实就是比上一个问题多一个字段name，但是区别比较大，因为一张score表已经不够用了。这个时候还需要student表了，即两张表联合查询。那么只要搞一个连接条件即可：

image

第三个问题

查询平均成绩大于60分的同学的学号和平均成绩

我们再来分解看看：

1
2
3
4
5
6
7
8
#查询肯定用到select
select 

#要查询的两个字段
student_id,avg(score) 

#由于存在avg，那么必然要分组
group by student_id

最后，有一个条件是：平均成绩大于60分，此时就需要对查询出来的分组进行过滤筛选了，此时having闪亮登场。

image

测试了一下，下面两条sql都是一样的效果：

1
2
select * from course where course_id = 1
select * from course having course_id = 1

第四个问题

查询没有学全所有课的同学的学号、姓名

这个稍微复杂一点点，我们还是分解来看看：

1
2
3
4
5
6
7
8
9
10
11
#查询肯定用到select
select 

#要查询的两个字段
student_id,name

#由于存在avg，那么必然要分组
group by student_id

#两张表连接，要起个别名
where sc.student_id = stu.student_id

因为需要查询课程没有学满的学生，所以需要先查询所有课程的数量：

1
select count(1) from course

此时，我们需要利用这个查询语句作为结果再进行查询，即子查询。对于上面的分组要进行筛选

1
having count(sc.course_id) < (select count(1) from course)

所以最终的语句是：

image