23.MySQL中的HAVING(分组过滤)

 1 CREATE DATABASE mahaiwuji;
 2 
 3 USE mahaiwuji;
 4 
 5 CREATE TABLE student (
 6     sid INT (4) PRIMARY KEY,
 7     sname VARCHAR (36),
 8     course VARCHAR (36),
 9     score INT,
10     grade INT (4)
11 ) ENGINE = INNODB DEFAULT CHARSET = utf8;
12 
13 INSERT INTO student VALUES (1,‘aa‘,‘语文‘,60,1);
14 INSERT INTO student VALUES (2,‘aa‘,‘数学‘,70,1);
15 INSERT INTO student VALUES (3,‘aa‘,‘英语‘,80,1);
16 
17 INSERT INTO student VALUES (4,‘bb‘,‘语文‘,70,1);
18 INSERT INTO student VALUES (5,‘bb‘,‘数学‘,60,1);
19 INSERT INTO student VALUES (6,‘bb‘,‘英语‘,60,1);
20 
21 INSERT INTO student VALUES (7,‘cc‘,‘语文‘,90,2);
22 INSERT INTO student VALUES (8,‘cc‘,‘数学‘,50,2);
23 INSERT INTO student VALUES (9,‘cc‘,‘英语‘,60,2);
24 
25 INSERT INTO student VALUES (10,‘dd‘,‘语文‘,70,2);
26 INSERT INTO student VALUES (11,‘dd‘,‘数学‘,60,2);
27 INSERT INTO student VALUES (12,‘dd‘,‘英语‘,90,2);

1 -- 查询平均分大于68分的人
2 SELECT sname,AVG(score) FROM student GROUP BY sname HAVING AVG(score)>68;