Mysql count details and function instance code, mysqlcount

Mysql count details and function instance code, mysqlcount

Mysql count details

The count function is a function used to count records in tables or arrays. The following describes how to use the count function in mysql.

Count (*) returns the number of rows to be retrieved, whether or not it contains a NULL value.

When SELECT is retrieved from a table, but no other columns are retrieved, and there is no WHERE clause, COUNT (*) is optimized to the fastest return speed.

For example:

mysql> SELECT COUNT(*) FROM student;

The COUNT (DISTINCT field) optimization applies only to MyISAM tables because these table types store the exact number of records returned by a function and are very easy to access.

For transaction-type storage engines (InnoDB and BDB), there are many problems with storing a precise number of rows, because multiple transactions may be processed, and each row may have an impact on the number of rows.

Returns the number of non-NULL values.

If no matching item is found, COUNT (DISTINCT) returns 0.

Example

Create a data table for testing for count statistics:

Create table 'user' ('id' int (5) unsigned not null AUTO_INCREMENT, 'name' varchar (10) default null, 'Password' varchar (10) default null, primary key ('id') ENGINE = MyISAM AUTO_INCREMENT = 4 default charset = latin1 test data: 1 name1 1234562 name2 1234563 name3 1234564 name4 NULL

Note the returned results of the following query:

select count(*) from `user`select count(name) from `user`select count(password) from `user`

Output results: 4, 3

Cause analysis:

1. count (*) is to count the number of rows, so the result is 4.

2. count (column_name) is used to count rows not empty in the column, so count (name) = 4, and count (password) = 3.

When using the count function, pay attention to the preceding two points.

If you use group by to GROUP all records of each owner without it, you will get the error message:

mysql> SELECT owner, COUNT(*) FROM pet;ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)with no GROUP columns is illegal if there is no GROUP BY clause

COUNT () and group by classify your data in various ways. The following example shows different methods for conducting an animal census.

Quantity of each animal:

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;+---------+----------+| species | COUNT(*) |+---------+----------+| bird  |    2 || cat   |    2 || dog   |    3 || hamster |    1 || snake  |    1 |+---------+----------+

Number of animals for each gender:

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;+------+----------+| sex | COUNT(*) |+------+----------+| NULL |    1 || f  |    4 || m  |    4 |+------+----------+

(In this output, NULL indicates "unknown gender ".)

Number of animals by type and gender:

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;+---------+------+----------+| species | sex | COUNT(*) |+---------+------+----------+| bird  | NULL |    1 || bird  | f  |    1 || cat   | f  |    1 || cat   | m  |    1 || dog   | f  |    1 || dog   | m  |    2 || hamster | f  |    1 || snake  | m  |    1 |+---------+------+----------+

If you use COUNT (), you do not have to retrieve the entire table. For example, in the previous query, when only a dog or a cat is executed, it should be:

mysql> SELECT species, sex, COUNT(*) FROM pet  -> WHERE species = 'dog' OR species = 'cat'  -> GROUP BY species, sex;+---------+------+----------+| species | sex | COUNT(*) |+---------+------+----------+| cat   | f  |    1 || cat   | m  |    1 || dog   | f  |    1 || dog   | m  |    2 |+---------+------+----------+

Or, if you only need to know the number of sex-based animals with known gender:

mysql> SELECT species, sex, COUNT(*) FROM pet  -> WHERE sex IS NOT NULL  -> GROUP BY species, sex;+---------+------+----------+| species | sex | COUNT(*) |+---------+------+----------+| bird  | f  |    1 || cat   | f  |    1 || cat   | m  |    1 || dog   | f  |    1 || dog   | m  |    2 || hamster | f  |    1 || snake  | m  |    1 |+---------+------+----------+

By the way, mysql's DISTINCT keywords are of a lot of unexpected use.

1. It can be used when count does not repeat records

For example, select count (DISTINCT id) FROM tablename;

Calculate the number of records with different IDs in the talbebname table.

2. You can use

For example, select distinct id FROM tablename;

Returns the specific values of different IDs in the talbebname table.

3. In case 2 above, there will be ambiguity when you need to return results with more than two columns in the mysql table

For example, select distinct id, type FROM tablename;

In fact, the returned result is a result of different IDs and types, that is, DISTINCT serves two fields at the same time. It must be the same id and tyoe to be excluded. It is different from the expected result.

4. At this time, you can use the group_concat function for troubleshooting. However, this mysql function is supported only after mysql4.1.

5. Another solution is to use

SELECT id, type, count(DISTINCT id) FROM tablename

Although such a returned result contains a column of useless count data (You may need this useless data)

The returned result is that only the results with different IDS can be used in concert with the above four types. It depends on what data you need.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!