Take you for a spin. Javaweb development of six-mysql Basic grammar and examples (4)

count the total amount of money for each classified item by category name "Sort Query"

The columns of the query are sorted in SQL, using the keyword ORDER BY. By default it is ascending sort (from small to large sort order keyword ASC). Using a descending sort requires the use of the keyword desc.

Query the student's information, the information of the query according to the language score to sort.

Default condition

To add ASC:

Sort from large to small in terms of language scores.

Sort in descending order of English scores, if the English scores are the same, in descending order of math fractions.

Query all students surnamed Liang, sorted in descending order of mathematics.

"Aggregate function"

SUM (); ---sum.

AVG (); ---average.

Count (); The number of---statistics.

Max (); ---to find the maximum value.

Min (); ---to find the minimum value.

The number of all students in the statistics table.

Number of students surnamed Liang in the statistics table

Calculate the total score of all students ' English scores

Calculate the total scores of students in various disciplines

At the moment, the results of two queries are the same, but the two queries are essentially different. Above is the vertical statistic value, and the following is the value of the horizontal statistic. The current value is the same.

Now insert a null value:

Across the statistic, null+78+92 the final result is NULL. Vertical direction sum (English) +sum (math) +sum (Chinese) also counts the values of 78 and 92. You can also use the Ifnull function.

Find the average score of mathematics

What is the highest score to take out math?

What is the minimum score for obtaining a language score?

Get the highest score in the language of the student surnamed Zhang

"Grouping of Queries"

To group the resulting data, use the keyword GROUP BY. The primary purpose of grouping is to work with aggregate functions to analyze the statistical situation of the data.

CREATE TABLE Product (

ID int primary KEY auto_increment,

Name varchar (20),

Price Double,

CNAME varchar (20)

);

INSERT into product values (NULL, ' washing machine ', 1000, ' home appliances ');

INSERT into product values (NULL, ' refrigerator ', 3000, ' home appliance ');

INSERT into product values (NULL, ' washing machine ', 1000, ' home appliances ');

INSERT into product values (NULL, ' Air conditioning ', 2000, ' Home Appliances ');

INSERT into product values (NULL, ' computer ', 4000, ' computer Office ');

INSERT into product values (NULL, ' mechanical keyboard ', 300, ' Computer Office ');

INSERT into product values (NULL, ' mechanical keyboard ', 300, ' Computer Office ');

Group by the name of the item.

Group by the name of the category of the product

Count the number of items purchased per product by product name

To count the total amount of money spent on each type of commodity according to the name of the commodity

Total amount of goods spent on household appliances

The total amount of money per classified item is counted according to the name of the classification. Find out what the total amount of money is greater than 5000.

The above wording is wrong!!! You cannot add an aggregate function after a where condition. The conditions with grouping statistics need to be added to the having behind.

1.1.1.2 a summary of the query statement:

The query's statement is the most varied statement. The structure is as follows:

Select ... From ... where ... group by ... having ...; order by ...;

Sfwgho ...

Take you for a spin. Javaweb development of six-mysql Basic grammar and examples (4)