How to count query results in mysql

Statistical method: 1. Use "COUNT(*)" to count all query results, and the syntax is "SELECT COUNT(*) FROM table name;". 2. Use "COUNT (field name)" to count non-NULL results, the syntax is "SELECT COUNT (column name)" FROM table name;". 3. Use "COUNT (DISTINCT field name)" to count non-NULL results without duplication, Syntax "SELECT COUNT(DISTINCT column name)" FROM table name;".

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

In mysql, you can use the COUNT() function to count query results.

MySQL COUNT() function

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

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

COUNT() function has three syntaxes:

COUNT(*)
COUNT(字段名)
COUNT(DISTINCT 字段名)

COUNT(*)

COUNT(*)The function returns The number of rows in the result set returned by the SELECT statement. COUNT(*)The function counts rows containing NULL and non-NULL values, that is: all rows.

If you use the COUNT(*) function to count numeric rows in a table without using a WHERE clause to select additional columns, it performs very quickly.

This optimization only applies to MyISAM tables because the row number of a MyISAM table is stored in the table_rows column of the tables table of the information_schema database; therefore, MySQL can retrieve it very quickly.

COUNT(column name)

COUNT(field name)Returns the number of rows that do not contain NULL values.

COUNT(DISTINCT column name)

COUNT(DISTINCT field name)Returns the number of unique rows that do not contain NULL values.

• The DISTINCT operator allows different values ​​in the calculation result, that is, deduplication.

MySQL COUNT example

• Create a new table named demo and insert Some sample data

-- create a demos table
CREATE TABLE IF NOT EXISTS demos(
 id int auto_increment primary key,
 val int 
);

-- insert some sample data
INSERT INTO demos(val)
VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5);

-- select data from demos table
SELECT * FROM demos;

• To count all rows in the demos table, please use the COUNT(*) function

SELECT COUNT(*) FROM demos;

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

SELECT COUNT(*) FROM demos WHERE val = 2;

Two NULL values ​​in the val column will be ignored.

• To count the unique rows in the demos table, you can add the DISTINCT operator to the COUNT function, as follows:

SELECT COUNT(DISTINCT val) FROM demos;

Ignore the two duplicate values ​​1, 2 and the two NULL values ​​in the count.

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to count query results in mysql. For more information, please follow other related articles on the PHP Chinese website!