Database query (ii) learning experience

Data Query (ii)

A Aggregation functions

Main functions	Type
COUNT ()	Count the number of tuples or columns
MIN ()	Statistic tuple or minimum value of a column
SUM ()	Sum of statistics tuples or columns
AVG ()	The average of a statistical tuple or column
MAX ()	The maximum value of a statistic tuple or column

1.

2. when the aggregation function encounters a null value, all but the COUNT (*) skips the null value and the non-null value is processed.

aggregate functions cannot be used in 3.WHERE clauses.

4. aggregation functions can only be used with a having statement in SELECT and GROUP by .

Two Select Branch

1. format:case... END( when followed by a range or a specific value)

The case must be followed by a condition, and when must be followed by a value instead of a condition.

2.

Function	Type
IIF (Boolean_expression,true_value,false_value)	Evaluates to a logical expression, returns an expression if the result is True, or returns another expression if False.
CHOOSE (index, Val_1, val_2 [, Val_n])	Returns the value of a specific position in the list,based on the index value; theindex value can be dynamic
ISNULL (Check_expression, Replacement_value)	Determines whether the expression is null, if null, with another value instead

3.case.. The value in end is a separate column of output values, while the values in ISNULL are substituted for the values in the original column.

Three Formatting

1.

Formatting functions	Type
CONVERT (data_type[(length)],expression [, Style])	The CONVERT () function is a general function that converts dates to new data types and also displays date / time data in different formats.
TRIM ( string )	The space between the ends of the string is removed, acting equal to the result of two functions of RTRIM and LTRIM .
Decimal (N,M)	Indicates that the value has n digits, where the integer n-m bit, and the fractional m bit . When an int type has overflow, it can be processed with the decimal type
ROUND (Column_name,decimals)	COLUMN_NAME represents the field to be entered, decimals represents the number of decimal digits returned

Four Date

1.

Year (), month (), DATE ()

TD valign= "Top" width= "+" >

Function	Type
Get only years, months, days
DATEDIFF (datepart,startdate,enddate)	- Returns the number of date and time boundaries across two specified dates
DATEADD (datepart, number, date)	Returns a new datetime value.
DATEPART (DATEPART, date)	Returns an integer representing the specified date portion of the specified date
Datename (datepart, date) /td>	Returns a string representing the specified date portion of the specified date

2. convert data type when using date for algorithm.

Database query (ii) learning experience