10-31c# base--aggregation function and grouping of database query statements

In query statements, there are also methods and properties, which are collectively referred to as functions at query time, and are convenient to use when querying

Aggregate functions (both for field operations)

Aggregation is an expression that shrinks a series of input values, for example, to a single value.

Select*from Biao

1 、--averaging avg: Returns a value (the average of a non-null value) only for operations of numeric types

Select AVG (age) has no column name after execution from biao--

Select AVG (age) as nianing a column name after execution of the from Biao---, plus as.

--There must be a column name between the Select...from.

--as (...) It can be an alias.

--Add condition with where:

Example: Select avg (Yuwen) as language average from Biao where banji= ' 1 class '

2 、--Total count, only 2 are either count (*), or (distinct) go heavy. (Returns the size of the aggregation that contains null values and duplicate values)

1) Select count (*) from Biao count (*) query table All

Select COUNT (*) from Biao where name like ' King% '

2) Select COUNT (distinct Banji) from Biao

3 、--Maximum max () + field (returns the maximum value of a non-null value)

Select MAX (Yuwen) as ... (+ alias) from Biao where banji= ' 1 class '

4 、--Minimum min () + field (minimum value that returns a non-null value)

Select MIN (Yuwen) as ... (+ alias) from Biao where banji= ' 2 class '

5 、--sum sum (returns the sum of non-null values)

Select SUM (Yuwen) from Biao

Select SUM (Yuwen) from Biao where ....

6, combined with the aggregate function (these aggregate functions, all return a row of values)

Select Avg. As average age, COUNT (*) as number from Biao where banji= ' 1 class '

The above 5 kinds are commonly used aggregation function (key memory), aggregation function contains all (all) and distinct (go to weight), nothing is written, default is all;

In addition, the aggregation function also includes 13 kinds of count-big,checksum.

Cases:

7 、--GROUP by (equivalent to grouping in an aggregate function), for a column operation. You can combine the aggregation functions learned above

Unlike aggregate functions, an aggregate function can display only one value, while grouping displays a set of values (each group).

Select Banji from Biao GROUP by Banji

---Group and combine aggregate function operations as required

Select Banji AVG (Yuwen) from Biao GROUP by Banji

--grouping can also be added to a condition where

Select nianing from Biao where nianling>20 GROUP by nianling

(supplemental) Relational operators:

=/!=/

Example: Select Nianling,count (*) from Biao where nianling between 20and GROUP by nianling

Select Grade,count (*) from Xuesheng where yuwen>60 group by grade

8 、--Having a group by IS unique, (only the selection criteria of the aggregate function can be added)

Having is screened for statistically good results, so the premise of having a having must use GROUP by.

Select Grade,count (*) from Xuesheng where yuwen>60 group by grade have COUNT (*) >9

To perform a having condition order:

1) Query the entire table

2) See Where Condition first

3) After filtering and performing group by

4) Execute the previous aggregate function separately for each group.

5) Finally executes the aggregation function that has the back.

9. Group By....order by ....

Example: Select banji,count (*) from Biao where yuwen>75 Group by Banji ORDER by COUNT (*) desc

Execute ORDER BY condition sequence:

1) Query the entire table

2) See Where Condition first

3) After filtering and performing group by

4) Execute the previous aggregate function separately for each group.

5) Final execution of order by: The aggregation function that follows.

Examples:

SelectGrade,max (Shuxue) asMathematics highest, MIN (Shuxue)AsMath lowest, MAX (Yuwen) asLanguage highest, MIN (Yuwen)AsLanguage minimum, MAX (Yingyu) asEnglish highest, MIN (Yingyu)AsEnglish minimumFromXuesheng group grade max (Shuxue) > by desc

Mathematical functions

1 、---Absolute Value
Select ABS ( -7)-- is the investigation results are displayed in the Results box

2. Print ABS ( -4)-- display in message box

Select Code,xingming,sex,nianling,weight,abs (Yuwen) as Yuwen from Xuesheng
Select Code,xingming,sex,nianling,weight,yuwen from Xuesheng where ABS (Yuwen) >90
select* from Xuesheng where ABS (Yuwen) >90

3 、---Upper Limit
Select CEILING (1.1)--ceiling Upper Limit
Select CEILING (Shuxue) from Xuesheng where (shuxue-ceiling (Shuxue))!=0
SELECT * from Xuesheng where CEILING (shuxue) >=90--ceiling format in query statements

4 、---Lower Limit
Select Floor (1.5)
Select Floor (Shuxue) The format of the from Xuesheng where Code=2--floor in the query statement

5 、--Second Party
Select POWER (3,2)--Indicates that 3 of the 2 times equals a few to return a few
Select ' This is ' +xingming+ ' age squared ', POWER (nianling,2) from xuesheng--when only age data type is string

6 、---Rounding
Select ROUND (2.54655,0)--(A, B) a indicates the number to be rounded and the number of digits to be rounded to the decimal point
Select Shuxe,round (shuxue,0) as Shehou from Xuesheng--round in query format

7 、--Square Root
Select SQRT (4)

8 、----Squared
Select SQUARE (3)

10-31c# base--aggregation function and grouping of database query statements