Summary of SQL Common functions

? Functions of the COALESCE () function in SQL
Returns the first non-empty expression in its argument. This function is to judge null, not '.
? SQL Trim () function
Remove two spaces
The SQL syntax does not directly remove the function of two spaces, but LTrim () removes the left space RTrim () to remove the right space.
Combined with the trim () function of SQL, select LTrim (RTrim (usrname))
? Nullif
If two of the specified expressions are equal, a null value is returned.
Grammar Nullif (expression1, expression2)
Parameter Expression1,expression2
Any combination of constants, column names, functions, subqueries or arithmetic operators, bitwise operators, and string operators.
The return type return type is the same as the first expression1.
Output Result:
If two expressions are not equal, NULLIF returns the value of the first expression1.
If two expressions are equal, NULLIF returns a null value of NULL.
SQL Server functions are the same function
Example: PRINT nullif (' 222 ', ')--return 222
PRINT ISNULL (Nullif (' 222 ', ' 222 '), ' 23 ')--returns 23 to prove null

? char function
char (current Date,iso)--Convert to YYYY-MM-DD
char (current Date,usa)--Convert to MM/DD/YYYY
char (current date,eur)--Convert to Dd.mm.yyyy

? Date function
The date () function is the day part of the extract date or date/time expression.
MySQL stores date or date/time values in the database using the following data types:
DATE-Format YYYY-MM-DD
DATETIME-format: Yyyy-mm-dd HH:MM:SS
TIMESTAMP-format: Yyyy-mm-dd HH:MM:SS
Year-format YYYY or YY

SQL Server uses the following data types to store date or date/time values in the database:
DATE-Format YYYY-MM-DD
DATETIME-format: Yyyy-mm-dd HH:MM:SS
smalldatetime-format: Yyyy-mm-dd HH:MM:SS
TIMESTAMP-Format: Unique number

? Rollup usage in SQL
The result set generated by the ROLLUP operator is similar to the result set generated by the CUBE operator.
Here are the specific differences between CUBE and ROLLUP:
The result set generated by cube shows aggregations for all combinations of values in the selected column.
The result set generated by ROLLUP shows the aggregation of a hierarchy of values in the selected column.
ROLLUP Advantages:
(1) ROLLUP returns a single result set, while COMPUTE by returns multiple result sets, and multiple result assemblies increase the complexity of the application code.
(2) ROLLUP can be used in server cursors, while COMPUTE by is not possible.
(3) Sometimes, the query optimizer generates more efficient execution plans for ROLLUP than is generated for COMPUTE by.

Compare the results of group BY, CUBE, and rollup below
To create a table:

CREATE TABLE Depart (department char (10), employee char (6), payroll int) INSERT into depart select ' a ', ' ZHANG ', + insert into depart select ' A ' , ' LI ', depart insert into the select ' a ', ' WANG ', ' insert into depart select ' a ', ' ZHAO ', and insert into depart select ' A ', ' DUAN ', insert into depart select ' B ', ' DUAN ', + insert into depart select ' B ', ' DUAN ', 700

Department Employee salary
A ZHANG 100
A LI 200
A WANG 300
A ZHAO 400
A DUAN 500
B DUAN 600
B DUAN 700

(1) GROUP by

SELECT department, employee, SUM (payroll) as total from depart GROUP by department, employees

　　

Results:
A DUAN 500
B DUAN 1300
A LI 200
A WANG 300
A ZHANG 100
A ZHAO 400
(2) ROLLUP

SELECT department, employee, SUM (payroll) as total from depart GROUP by department, employee with ROLLUP

　　

The results are as follows:

A DUAN LI 2800 a WANG a ZHAO a null DUAN b 1300 b null 1300 NULL NULL

　　

The rollup result set has more than three aggregated information: the total of department A, the total of department B, and the total sum. The total of Duan in department B is included.
Equivalent to the following SQL statement

Select department, employee, SUM (payroll) as total from depart group by department, employee Union SELECT Department, ' NULL ', sum (payroll) as total from depart group by Department Uni On SELECT ' null ', ' null ', SUM (payroll) as total from depart

Results:
A DUAN 500
A LI 200
A NULL 1500
A WANG 300
A ZHANG 100
A ZHAO 400
B DUAN 1300
B NULL 1300
NULL NULL 2800
(3) CUBE

SELECT department, employee, SUM (payroll) as total from depart GROUP by department, employee with CUBE

Results:
A DUAN 500
A LI 200
A WANG 300
A ZHANG 100
A ZHAO 400
A NULL 1500
B DUAN 1300
B NULL 1300
NULL NULL 2800
NULL DUAN 1800
NULL LI 200
NULL WANG 300
NULL ZHANG 100
NULL ZHAO 400
The result set of cube is 5 rows on the basis of the rollup result set, which is equivalent to the result of a GROUP by employee (that is, cube) on the rollup result set on the Union.

SELECT department, employee, SUM (payroll) as total from depart GROUP by department, employee with CUBE

Equivalent to the following SQL statement:

SELECT department, employee, SUM (payroll) as total from depart group by department, employee with Rollupunionselect ' NULL ', employee, SUM (payroll) as total from depart group By employees

Results:
NULL NULL 2800
A NULL 1500
A DUAN 500
A LI 200
A WANG 300
A ZHANG 100
A ZHAO 400
B NULL 1300
B DUAN 1300
NULL DUAN 1800
NULL LI 200
NULL WANG 300
NULL ZHANG 100
NULL ZHAO 400

? UNION operator
The UNION operator is used to combine the result set of two or more SELECT statements.
Note that the SELECT statement inside the UNION must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same.
SQL UNION Syntax

SELECT column_name (s) from Table_name1unionselect column_name (s) from table_name2

Note: By default, the union operator chooses a different value, that is, the Union is going to be heavy. If duplicate values are allowed, use UNION all.
SQL UNION All syntax

SELECT  from table_name1 UNION  All SELECT  from Table_name2

In addition, the column name in the union result set is always equal to the column name in the first SELECT statement in the Union.

Summary of SQL Common functions