Database (iii)

Statistics-aggregate functions (statistical functions)  

1, COUNT (*)--Total

Select COUNT (*) from chinastates where AreaCode like ' _ _ _ _ '--statistics chinastates The total number of cities in the table (AreaCode like ' _ _ _ ' stands for the prefecture-level cities).

After Ps:count is the column name, the column is counted, then the null value in the query column is ignored and is not counted.

2, avg--average

Select AVG (price) from car where brand in (' b001 ', ' b002 ', ' b003 ', ' b004 ')--the car table in the series code for ' b001 ', ' b002 ', ' b003 ', ' b004 ' mean.

3, sum--sum

Select SUM (price) from car--statistics The sum of all prices in the car table, () can be written in multiple columns, such as: Price/ids.

SELECT *, (price*0.9) as Price from car--statistics Price after 90 percent is displayed at the end of the table, the column is named Price.

Select Code code, name, brand, time-to-market, oil consumption, powers power, exhaust displacement, price, pic image path from car--the name of the list in Chinese characters.

4, max--maximum value

Select Max from car--displays the price maximum value.

5, min--minimum value

The Select min from car--displays the price minimum value.

Grouping  

1. Select Oil,count (*) from car GROUP by oil ORDER by COUNT (*) desc--is grouped according to oil, showing the number of oil columns and no groups and descending order.

2, select Oil,count (*) from the car where price>50 group by the ORDER by COUNT (*) desc--to the price greater than 50 in accordance with the oil group and descending order.

3. Select Type,count (*) from fruit GROUP By type has COUNT (*) >1--is grouped by type and displays the type and number of numbers greater than 1 in the group.

Ps:having is filtered for statistically good results, so the use of having must use GROUP by.

function  

1. Power function

sqrt--Square Root

2, take approximate function

①ceiling

Ceiling (3.2)--4

②floor

Floor (3.2)--3

③round (ROUND function T-SQL in the index)

Print round (345.987,0)--346.000 0 refers to the retention of several decimal places.

Print round (345.876,-1)--350.000-1 refers to rounding to 10 bits.

Print round (345.876,-2)--300.000-2 refers to rounding to the nearest hundred.

3. Symbolic function

The abs--absolute value can be used for two-column values subtraction, regardless of whether the column values are positive or negative.

4. Other functions

rand--0 to 1 random number (in time as seed), never equal to 1.

Database (iii)