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);
2.实例演示
功能:根据条件表达式对分组后的内容进行过滤,因此使用having时通常都会先使用group by。
1 -- 查询平均分大于68分的人 2 SELECT sname,AVG(score) FROM student GROUP BY sname HAVING AVG(score)>68;
3.HAVING和WHERE的异同
相同
都是对数据过滤,只保留有效的数据。
不同
WHERE是对原始的记录过滤,HAVING是对分组之后的记录过滤; WHERE必须的写在HAVING的前面,顺序不可颠倒否则运行出错。