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

This article mainly introduces mysql how to query and count the quantity, the article is very detailed, has a certain reference value, interested friends must read it!

In mysql, you can use the select statement to query the data and use the COUNT () function to count the number of query results, with the syntax "SELECT COUNT (*) FROM table name [...];" or "SELECT COUNT (field name) FROM table name [...];".

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

In mysql, you can use the select statement to query the data and use the COUNT () function to count the number of query results.

The syntax format of SELECT is as follows:

SELECT {* |} [FROM, … [WHERE [GROUP BY [HAVING [{} …]] [ORDER BY] [LIMIT [,]

Among them, the meaning of each clause is as follows:

{* |} contains a list of fields with asterisk wildcards, indicating the name of the field to be queried.

,... Tables 1 and 2 represent the sources of query data, which can be single or multiple

WHERE is optional, and if selected, the query data must meet the query criteria.

GROUP BY

That tells MySQL how to display the queried data and group it by the specified field

[ORDER BY

This clause tells MySQL in what order the queried data is displayed, which can be sorted in ascending (ASC) and descending (DESC) order, which is ascending by default.

[LIMIT [,]], this clause tells MySQL to display the number of pieces of data that are queried each time.

The COUNT () function counts the total number of record rows contained in the data table, or returns the number of data rows contained in the column based on the query results.

COUNT (*) calculates the total number of rows in the table, regardless of whether a column has a numeric value or is null.

COUNT (expression) calculates the number of rows that do not contain null values.

COUNT (DISTINCT expression) returns the unique number of rows that do not contain a NULL value

The return type of the COUNT () function is BIGINT. If no matching row is found, the COUNT () function returns 0.

MySQL COUNT example

Let's create a new table called demo and insert some sample data.

USE testdb;-- create a demos tableCREATE TABLE IF NOT EXISTS demos (id int auto_increment primary key, val int);-- insert some sample dataINSERT INTO demos (val) VALUES (1), (1), (2), (2), (NULL), (3), (4), (NULL), (5);-- select data from demos tableSELECT * FROM demos

Execute the above query and get the following results-

+-+ | id | val | +-+-+ | 1 | 1 | 2 | 3 | 3 | 4 | 2 | 5 | NULL | 6 | 3 | 7 | 4 | 8 | NULL | 9 | 5 | +-+-- + 9 rows in set

To calculate all rows in the demos table, use the COUNT (*) function, as follows:

Mysql > SELECT COUNT (*) FROM demos;+-+ | COUNT (*) | +-+ | 9 | +-+ 1 row in set

You can add a WHERE clause to specify a condition to count, for example, to calculate only rows whose val column contains a value equal to 2, using the following query:

Mysql > SELECT COUNT (*) FROM demos WHEREval = 2 | COUNT (*) | +-+ | 2 | +-+ 1 row in set

If you specify a val column in the COUNT function, the COUNT function counts all rows whose val column contains only non-null values. Please refer to the following query:

SELECT COUNT (*) FROM demos WHEREval = 2

Two null values in the val column are ignored.

To calculate the unique row in the demos table, you can add the DISTINCT operator to the COUNT function, as shown in the query statement:

SELECT COUNT (DISTINCT val) FROM demos

Execute the above query and get the following results-

Mysql > SELECT COUNT (DISTINCT val) FROM demos;+-+ | COUNT (DISTINCT val) | +-+ | 5 | +-+ 1 row in set

Two duplicate values, 1, 2, and two null values are ignored in the count.

MySQL COUNT with GROUP BY

We often use the COUNT function in conjunction with the GROUP BY clause to count the data in different groups. See the structure of the following products table-

Mysql > desc products +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | productCode | varchar (15) | NO | PRI | | productName | varchar (70) | NO | MUL | NULL | | | productLine | varchar (50) | NO | MUL | NULL | productScale | varchar (10) | NO | | NULL | | productVendor | varchar (50) | NO | | NULL | | productDescription | text | NO | NULL | | quantityInStock | smallint ( 6) | NO | | NULL | | buyPrice | decimal (10L2) | NO | | NULL | | MSRP | decimal (10L2) | NO | | NULL | | stockValue | double | YES | NULL | STORED GENERATED | +- -+-+ 10 rows in set

For example, to find the number of products in each product family, you can use the COUNT function with the GROUP BY clause, as shown in the following query:

SELECT productline, count (*) FROM products GROUP BY productline

Execute the above code to get the following results-

Mysql > SELECT productline, count (*) FROM products GROUP BY productline +-+-+ | productline | count (*) | +-+-+ | Classic Cars | 38 | Motorcycles | 13 | Planes | 12 | Ships | 9 | Trains | 3 | | Trucks and Buses | 11 | | Vintage Cars | 24 | +-+-+ 7 rows in set

To find the number of products provided by the vendor, use the following query:

SELECT productvendor, count (*) FROM products GROUP BY productvendor

Execute the above code to get the following results-

Mysql > SELECT productvendor, count (*) FROM products GROUP BY productvendor +-- +-+ | productvendor | count (*) | +-+-+ | Autoart Studio Design | 8 | Carousel DieCast Legends | 9 | Classic Metal Creations | 10 | | Exoto Designs | 9 | Gearbox Collectibles | 9 | Highway 66 Mini Classics | 9 | Min Lin Diecast | 8 | Motor City Art Classics | 9 | Red Start Diecast | 7 | Second Gear Diecast | 8 | Studio M Art Models | 8 | Unimax Art Galleries | 8 | Welly Diecast Productions | 8 | +- -- +-+ 13 rows in set

To find out which vendor provides at least nine products, you can use the HAVING clause in the COUNT function, as shown in the following query statement:

SELECT productvendor, count (*) FROM products GROUP BY productvendorHAVING count (*) > = 9

Execute the above code to get the following results-

Mysql > SELECT productvendor, count (*) FROM products GROUP BY productvendorHAVING count (*) > = 9 +-- +-+ | productvendor | count (*) | +-+-+ | Carousel DieCast Legends | 9 | Classic Metal Creations | 10 | | Exoto Designs | 9 | | Gearbox Collectibles | 9 | | Highway 66 Mini Classics | 9 | | Motor City Art Classics | 9 | +-+-+ 6 rows in setMySQL COUNT IF

You can use the control flow features in the COUNT function, such as IF,IFNULL,CASE, and so on, to count rows whose values match the criteria.

For example, the following query can find out how many cancelled, suspended and controversial orders:

SELECT COUNT (IF (status='Cancelled',1, NULL)) 'Cancelled', COUNT (IF (status='On Hold',1, NULL))' On Hold', COUNT (IF (status='Disputed',1, NULL)) 'Disputed'FROM orders

Execute the above code to get the following results-

Mysql > SELECT COUNT (IF (status='Cancelled',1, NULL)) 'Cancelled', COUNT (IF (status='On Hold',1, NULL))' On Hold', COUNT (IF (status='Disputed',1, NULL)) 'Disputed'FROM orders +-+ | Cancelled | On Hold | Disputed | +-+ | 6 | 4 | 3 | +-+ 1 row in set

If the status of the order is cancelled, retained, or disputed, the IF function returns 1, otherwise it returns NULL. The COUNT function only counts 1, not a null value, so the query returns the number of orders based on the appropriate status.

The above is all the contents of the article "how to query and count the number of mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.