MySQL Statistical query Implementation code _MYSQL

Number of rows of statistical data
The SELECT COUNT () from syntax is used to count rows of data from a datasheet.

Grammar:

SELECT COUNT (column) from Tb_name
This SQL syntax is used to count the number of rows in a field, but it can be a * number, but not multiple fields in count ().

Example:

Copy Code code as follows:

<?php
$conn = @mysql_connect ("localhost", "root", "root123");
if (! $conn) {
Die ("Connection Database failed:". Mysql_error ());
}

mysql_select_db ("Test", $conn);
$sql = "Select COUNT (UID) from user";
$row = Mysql_fetch_array (mysql_query ($sql));
echo "Total Users:", $row [0], "bit";
?>

Browser display:

Total Users: 4-bit

Description
Although the parameter in count () can be a field name, if you only want the number of data records in the table, consider the effectiveness of the statistics primary key count (ID) or use COUNT (*) directly, and try to avoid using column data properties as character classes.

now that you've seen this, let's share two examples.

Count in the MySQL query statistics function

Today I came across a topic: Statistics of all female students with a total score greater than 90
I have just started to write: $sql = "Select Girls ' results from the use where score >"; $result = mysql_query ($sql);
$row = mysql_num_rows ($result); echo "total: $row";
But 100 is OK, if it is 10,000, it is not to be very slow AH!! Later a friend told me to use the Count function, which I just remembered.
Change the SQL statement above to read:
$sql = "SELECT count (*), female students ' results from the use Group by female results > 90";
So the query statement is much faster.

The number of MySQL query statistics implemented by single SELECT statement

How is the use of a single SELECT statement to realize the number of MySQL query statistics? Use too much, such as a report card, you have to check the number of people and the number of failed, how to query out?
MySQL Query statistics number of simple statements must be this:

Copy Code code as follows:

Select A.name,count_neg,count_plus from
(select count (ID) as Count_plus,name from Score2 where score >=60 Group by name) A,
(select count (ID) as Count_neg,name from Score2 where score <=60 GROUP by name) b
where A.name=b.name

That is, you must use at least 2 statements.

Today happened to find MySQL support if, then creatively use if to achieve it:

Copy Code code as follows:

Select Name, SUM (if (score>=60,1,0)), SUM (if (score<60,1,0)) from Score2 GROUP by name

How to achieve MySQL query statistics by a single SELECT statement simple.

The principle is to be greater than 60 and assign a value of 1, then sum is counted.