Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use the having keyword of mysql". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to use the having keyword of mysql.

In mysql, the having keyword needs to be used with the SELECT statement to filter the grouped data, with the syntax "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: filtering grouping

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

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

HAVING query condition

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.

However, there are also several differences between the WHERE and HAVING keywords:

In general, WHERE is used to filter rows of data, while HAVING is used to filter packets.

Aggregate functions cannot be used in WHERE query conditions, while aggregate functions can be used in HAVING query conditions.

WHERE filters before the data packet, while HAVING filters after the data packet.

WHERE filters database files, while HAVING filters query results. That is, WHERE filters directly based on the fields in the data table, while HAVING filters based on the fields that have been queried earlier.

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

The following is an example to give you a more intuitive understanding of the similarities and differences between WHERE and HAVING keywords.

Example 1

Use HAVING and WHERE keywords to find out the name, gender and height of the students whose height is greater than 150 in the tb_students_info table. The SQL statement and the run result are as follows.

Mysql > SELECT name,sex,height FROM tb_students_info-> HAVING height > 150 +-+ | name | sex | height | +-+ | Dany | male | 160 | Green | male | 158 | Henry | female | 185 | Jane | male | 162 | Jim | female | 175 | John | female | 172 | Lily | male | 165th | Susan | | male | 170 | Thomas | female | 178 | Tom | female | 165,165 | +-+ 10 rows in set (0.00 sec) mysql > SELECT name | Sex,height FROM tb_students_info-> WHERE height > 150 +-+ | name | sex | height | +-+ | Dany | male | 160 | Green | male | 158 | Henry | female | 185 | Jane | male | 162 | Jim | female | 175 | John | female | 172 | Lily | male | 165th | Susan | | male | 170 | Thomas | female | 178 | Tom | female | 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. But 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 the students greater than 150 in the tb_students_info table (compared to example 1, there is no height field this time). The SQL statement and the run result are as follows.

Mysql > SELECT name,sex FROM tb_students_info-> WHERE height > 150 +-+-+ | name | sex | +-+-+ | Dany | male | | Green | male | Henry | female | Jane | male | Jim | female | John | female | Lily | male | Susan | male | Thomas | female | Tom | female | +-+-+ 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'

As you can see from the results, if the height field used in the HAVING query condition is not queried after the SELECT keyword, MySQL will prompt the error message: the column "height" in the "having clause" is unknown.

Example 3

The data in the tb_students_info table are grouped according to the height field, and the names, gender and height of the students whose average height is more than 170are queried by using HAVING and WHERE keywords respectively. The SQL statement and the run result 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 | female | 172 | Jim | female | 175 | Thomas | | Henry | Henry | female | 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 you can see from the results, if you use aggregate functions in WHERE query conditions, MySQL prompts an error message: invalid use of group functions.

Thank you for your reading, the above is the content of "how to use the having keyword of mysql". After the study of this article, I believe you have a deeper understanding of how to use the having keyword of mysql. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.