1.準(zhǔn)備
CREATE DATABASE mahaiwuji;
USE mahaiwuji;
CREATE TABLE student (
sid INT (4) PRIMARY KEY,
sname VARCHAR (36),
course VARCHAR (36),
score INT,
grade INT (4)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO student VALUES (1,'aa','語(yǔ)文',60,1);
INSERT INTO student VALUES (2,'aa','數(shù)學(xué)',70,1);
INSERT INTO student VALUES (3,'aa','英語(yǔ)',80,1);
INSERT INTO student VALUES (4,'bb','語(yǔ)文',70,1);
INSERT INTO student VALUES (5,'bb','數(shù)學(xué)',60,1);
INSERT INTO student VALUES (6,'bb','英語(yǔ)',60,1);
INSERT INTO student VALUES (7,'cc','語(yǔ)文',90,2);
INSERT INTO student VALUES (8,'cc','數(shù)學(xué)',50,2);
INSERT INTO student VALUES (9,'cc','英語(yǔ)',60,2);
INSERT INTO student VALUES (10,'dd','語(yǔ)文',70,2);
INSERT INTO student VALUES (11,'dd','數(shù)學(xué)',60,2);
INSERT INTO student VALUES (12,'dd','英語(yǔ)',90,2);
s23_1.png
2.實(shí)例演示
功能：根據(jù)條件表達(dá)式對(duì)分組后的內(nèi)容進(jìn)行過(guò)濾耘子，因此使用having時(shí)通常都會(huì)先使用group by啼辣。
-- 查詢(xún)平均分大于68分的人
SELECT sname,AVG(score) FROM student GROUP BY sname HAVING AVG(score)>68;
s23_2.png
3.HAVING和WHERE的異同
相同
都是對(duì)數(shù)據(jù)過(guò)濾吏恭，只保留有效的數(shù)據(jù)等缀。
不同
WHERE是對(duì)原始的記錄過(guò)濾，HAVING是對(duì)分組之后的記錄過(guò)濾熬词；
WHERE必須的寫(xiě)在HAVING的前面栅表，順序不可顛倒否則運(yùn)行出錯(cuò)笋鄙。
