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

This article mainly explains "how to use having in mysql". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn how to use having in mysql.

In mysql, the having clause is used to filter all kinds of data after grouping, usually in conjunction with "group by", which makes up for the deficiency that the where keyword cannot be used in conjunction with aggregate functions.

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

What is the use of having in mysql

The reason for adding the HAVING clause to SQL is that the WHERE keyword cannot be used with aggregate functions.

The HAVING clause allows us to filter the grouped data.

SQL HAVING syntax

SELECT column_name, aggregate_function (column_name) FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function (column_name) operator value

Demo database

In this tutorial, we will use the RUNOOB sample database.

Here is the data selected from the "Websites" table:

+-- + | id | name | url | alexa | country | +-+-- -+ | 1 | Google | https://www.google.cm/ | 1 | USA | | 2 | Taobao | https://www.taobao.com/ | 13 | CN | | 3 | Rookie tutorial | http://www.runoob.com/ | 4689 | CN | | 4 | Weibo | http://weibo.com/ | 20 | CN | | 5 | Facebook | https://www.facebook.com/ | 3 | USA | | 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND | +-+-- -+

Here is the data from the access log table of the "access_log" website:

Mysql > SELECT * FROM access_log +-+ | aid | site_id | count | date | +-+ | 1 | 1 | 45 | 2016-05-10 | 2 | 3 | 100 | 2016-05- | 13 | 3 | 1 | 2016 | 2016-05-14 | 4 | 2 | 10 | 2016-05-14 | 5 | 5 | 20 | 2016-05-14 | 6 | 4 | 13 | 2016-05-15 | 7 | 3 | 2016-05-15 | 8 | 5 | 545 | 2016-05-16 | 9 | 3 | 2016-05-17 | | | +-+ 9 rows in set (0.00 sec) |

SQL HAVING instance

Now we want to find websites with a total of more than 200 visitors.

We use the following SQL statement:

SELECT Websites.name, Websites.url, SUM (access_log.count) AS nums FROM (access_logINNER JOIN WebsitesON access_log.site_id=Websites.id) GROUP BY Websites.nameHAVING SUM (access_log.count) > 200

The output of executing the above SQL is as follows:

At this point, I believe you have a deeper understanding of "how to use having in mysql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.