How to use the mysql having keyword

In mysql, the having keyword needs to be used together with the SELECT statement to filter the grouped data. The syntax is "SELECT {*|field column name} FROM data table name HAVING query condition;".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

MySQL HAVING: Filter grouping

In MySQL, you can use the HAVING keyword to filter grouped data.

The syntax format for using the HAVING keyword is as follows:

HAVING 查询条件;

Both the HAVING keyword and the WHERE keyword can be used to filter data, and HAVING supports all operators and syntax in the WHERE keyword.

But there are also the following differences between the WHERE and HAVING keywords:

• Generally, WHERE is used to filter data rows, while HAVING is used to filter groups.

• Aggregation functions cannot be used in WHERE query conditions, but aggregate functions can be used in HAVING query conditions.

• WHERE filters before data grouping, while HAVING filters after data grouping.

• WHERE filters against database files, while HAVING filters against query results. In other words, WHERE filters directly based on the fields in the data table, while HAVING filters based on the fields that have been queried previously.

• Field aliases cannot be used in WHERE query conditions, but field aliases can be used in HAVING query conditions.

The following examples will give you a more intuitive understanding of the similarities and differences between the WHERE and HAVING keywords.

Example 1

Use the HAVING and WHERE keywords to query the names, gender and height of students whose height is greater than 150 in the tb_students_info table. The SQL statements and running results are as follows.

mysql> SELECT name,sex,height FROM tb_students_info 
    -> HAVING height>150;
+--------+------+--------+
| name   | sex  | height |
+--------+------+--------+
| Dany   | 男   |    160 |
| Green  | 男   |    158 |
| Henry  | 女   |    185 |
| Jane   | 男   |    162 |
| Jim    | 女   |    175 |
| John   | 女   |    172 |
| Lily   | 男   |    165 |
| Susan  | 男   |    170 |
| Thomas | 女   |    178 |
| Tom    | 女   |    165 |
+--------+------+--------+
10 rows in set (0.00 sec)

mysql> SELECT name,sex,height FROM tb_students_info 
    -> WHERE height>150;
+--------+------+--------+
| name   | sex  | height |
+--------+------+--------+
| Dany   | 男   |    160 |
| Green  | 男   |    158 |
| Henry  | 女   |    185 |
| Jane   | 男   |    162 |
| Jim    | 女   |    175 |
| John   | 女   |    172 |
| Lily   | 男   |    165 |
| Susan  | 男   |    170 |
| Thomas | 女   |    178 |
| Tom    | 女   |    165 |
+--------+------+--------+
10 rows in set (0.00 sec)

In the above example, because the height field has been queried after the SELECT keyword, both HAVING and WHERE can be used. However, if the height field is not queried after the SELECT keyword, MySQL will report an error.

Example 2

Use the HAVING and WHERE keywords to query the names and genders of students whose height is greater than 150 in the tb_students_info table (compared to Example 1, this time there are no Query the height field). The SQL statements and running results are as follows.

mysql> SELECT name,sex FROM tb_students_info 
    -> WHERE height>150;
+--------+------+
| name   | sex  |
+--------+------+
| Dany   | 男   |
| Green  | 男   |
| Henry  | 女   |
| Jane   | 男   |
| Jim    | 女   |
| John   | 女   |
| Lily   | 男   |
| Susan  | 男   |
| Thomas | 女   |
| Tom    | 女   |
+--------+------+

10 rows in set (0.00 sec)
mysql> SELECT name,sex FROM tb_students_info HAVING height>150;
ERROR 1054 (42S22): Unknown column 'height' in 'having clause'

It can be seen from the results that if the height field used in the HAVING query condition is not queried after the SELECT keyword, MySQL will prompt an error message: The column "height" in the "having clause" is unknown."

Example 3

Group the data in the tb_students_info table according to the height field, and use the HAVING and WHERE keywords to query the students whose average height after grouping is greater than 170 Name, gender and height. The SQL statement and running results are as follows.

mysql> SELECT GROUP_CONCAT(name),sex,height FROM tb_students_info 
    -> GROUP BY height 
    -> HAVING AVG(height)>170;
+--------------------+------+--------+
| GROUP_CONCAT(name) | sex  | height |
+--------------------+------+--------+
| John               | 女   |    172 |
| Jim                | 女   |    175 |
| Thomas             | 女   |    178 |
| Henry              | 女   |    185 |
+--------------------+------+--------+
4 rows in set (0.00 sec)

mysql> SELECT GROUP_CONCAT(name),sex,height FROM tb_students_info WHERE AVG(height)>170 GROUP BY height;
ERROR 1111 (HY000): Invalid use of group function

As can be seen from the results, if an aggregate function is used in the WHERE query condition, MySQL will prompt an error message: Invalid use of group function.

【Related recommendations: mysql video tutorial】

The above is the detailed content of How to use the mysql having keyword. For more information, please follow other related articles on the PHP Chinese website!