Database knowledge collation commonly used in SQL Server 2008

Database Knowledge Points:

First, the calculated field

1 NULL 's name into "anonymous";

Expression: Nullif (expression1, expression2)
Meaning: If two expressions are not equivalent, NULLIF returns the value of the first expression1.
If two expressions are equivalent, NULLIF returns a null value of the first expression1 type.

2, beween andcalculated fields such as: FsalarybetweenFage*1.5+ -  andFage*1.8+ the3, SQL syntax calculation, instead of the calculation of code such as:UpdateTSetFgroup=Id/Ten

Second, the combination of query Union

1 , the Union operator can combine two query result sets of two (or more) query statements into a single result set; 2 , Connection principle:   First, each result set must have the same number of columns, and the second is that each result set column must be type-compatible.  3.Default rule:   the Union operator merges two query result sets, where fully duplicated rows of data are merged for a 4,UNION all to get all result sets.

third, matching

1, _ (complement)------single charactermatch 2,%(All)----multi-character match 3,  []----set matching   such as:[AB]%, with "A" or "B" length, length arbitrary.   Note: The collection is reversed, " ^", such as"[^ab]%"

Four, in statements

1 , multi-value detection   such as:SELECT from t_employee   WHEREin (   -  - 2, range value detection (not advocated, recommended with between ...) and ... ）

V. WHERE 1=1 using dynamically assembled SQL

1, the designation of a forever true condition, do not need to judge where the existence of, 2, easy to cause performance loss; 3, preferably where and and are to be spliced.

Vi. window function row_number ()

1, meaning: Calculate the row number of each row of data in the result set (starting from 1 count)

2. Example: SELECT row_number () over (ORDER by fsalary), Fnumber,fname,fsalary,fage
From T_employee

3. Extension:
Data from line 3rd to line 5th:
SELECT * FROM
(
SELECT row_number () over (ORDER by Fsalary DESC) as RowNum,
Fnumber,fname,fsalary,fage from T_employee
) as a
WHERE a.rownum>=3 and A.rownum<=5

Vii. Mathematical Functions

1、ABS() function, to find absolute value;
2、POWER() Function: General power (sex,2), the first argument is the expression to be exponentiation, and the second argument is a power. such as SelectPOWER(2,3) result is 8.
3、SQRT() function to calculate the square root;
4、RAND() function to find the random number (Select RAND();)
5, rounded to the maximum integer,CEILING() function; rounded to the smallest integer, Floor() function; rounding,ROUND() function. 6、Datename() function optional value alias description Yearyy, yyyy year Quarter QQ, Q quarterMonthmm, M month dayofyear Dy, y one day of the year DayDD, D date Week wk, WW week Weekday DW weekday Hour hh hours Minute mi, n minutes Se Cond SS, S-SEC millisecond Ms Ms instance:SelectF_guid,Datename(weekday,birthdate) Week fromt_teacherarchives
7、CAST() and CONVERT () two instances of the function:SelectIDnumber, Right(IDnumber,6) after six bits fromt_teacherarchivesSelectIDnumber,cast( Right(IDnumber,6) as INTEGER)+1  as 'after six bits'  fromt_teacherarchivesSelectIDnumber,CONVERT(INTEGER, Right(IDnumber,6))+1  as 'after six bits'  fromT_teacherarchives (note type)

8. COALESCE () function
function: null value processing
Expression: COALESCE (Expression,value1,value2......,valuen)
Instance:
Select F_name,f_csrq,coalesce (F_CSRQ, ' 2015-10-10 ') as an important period
From t_studentarchives where f_name = ' 11 '
(if F_CSRQ is null, the important period is 2015-10-10; otherwise, the important period is F_CSRQ)
Select F_name,f_csrq,f_zz,coalesce (f_csrq,f_zz, ' 2015-10-10 ')
As important period from t_studentarchives where f_name = ' 11 '
(if F_CSRQ is null, then Judge F_zz again, understand ibid.)
Simplified version: ISNULL () function

Database knowledge collation commonly used in SQL Server 2008