SQL advanced (9) functions, SQL Functions

SQL advanced (9) functions, SQL Functions
SQL advanced (9) function syntax built-in SQL function syntax is:

SELECT function (column) FROM table

In SQL, there are several basic function types and types. The basic types of functions are:

• Aggregate Function
• Scalar functions

The operations of Aggregate functions are oriented to a series of values and return a single value.
Note: If the SELECT statement is used in many other expressions in the SELECT statement project list, the SELECT statement must use the group by statement!
"Persons" table (used in most examples)

Name	Age
Adams, John	38
Bush, George	33
Carter, Thomas	28

Aggregate functions in SQL Server

Function	Description
AVG (column)	Returns the number of rows in a column.
BINARY_CHECKSUM
CHECKSUM
Checksum_timeout
COUNT (column)	Returns the number of rows in a column (excluding NULL values)
COUNT (*)	Returns the number of selected rows.
COUNT (DISTINCT column)	Number of different returned results
FIRST (column)	Returns the value of the first record in the specified domain (not supported by SQLServer2000)
LAST (column)	Returns the value of the last record in the specified domain (not supported by SQLServer2000)
MAX (column)	Returns the maximum value of a column.
MIN (column)	Returns the lowest value of a column.
STDEV (column)
STDEVP (column)
SUM (column)	Returns the sum of a column.
VAR (column)
VARP (column)

Scalar functions
The operation of the Scalar function targets a single value and returns a single value based on the input value.
SQL AVG Function Definition and usage AVG function returns the average value of the value column. NULL values are not included in calculation.
SQL AVG () syntax

SELECT AVG(column_name) FROM table_name

For an SQL AVG () instance, we have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now, we want to calculate the average value of the "OrderPrice" field.
We use the following SQL statement:

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

The result set is similar to the following:

OrderAverage
950

Now, we want to find customers whose OrderPrice value is higher than the average OrderPrice value.

SELECT Customer FROM OrdersWHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

The result set is similar to the following:

Customer
Bush
Carter
Adams

SQL count () syntax SQL COUNT (column_name) syntax
The COUNT (column_name) function returns the number of values in the specified column (NULL is not counted ):

SELECT COUNT(column_name) FROM table_name

SQL COUNT (*) syntax
The COUNT (*) function returns the number of records in the table:

SELECT COUNT(*) FROM table_name

SQL COUNT (DISTINCT column_name) syntax
The COUNT (DISTINCT column_name) function returns the number of different values in the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name

Note: COUNT (DISTINCT) is applicable to ORACLE and Microsoft SQL Server, but cannot be used for Microsoft Access.
SQL COUNT (column_name) instance
We have the following "Orders" tables:

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now, we want to calculate the order number of the customer "Carter.

SELECT COUNT(Customer) AS CustomerNilsen FROM OrdersWHERE Customer='Carter'

The preceding SQL statement returns 2 because the customer Carter has two orders:

CustomerNilsen
2

SQL COUNT (*) instance
If the WHERE clause is omitted, for example:

SELECT COUNT(*) AS NumberOfOrders FROM Orders

The result set is similar to the following:

NumberOfOrders
6

SQL COUNT (DISTINCT column_name) instance
Now, we want to calculate the number of different customers in the "Orders" table.

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

The result set is similar to the following:

NumberOfCustomers
3

The FIRST () function returns the value of the FIRST record in the specified field.
Tip: You can use the order by statement to sort records.
SQL FIRST () syntax

SELECT FIRST(column_name) FROM table_name

SQL FIRST () instance
We have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now, we want to find the first value in the "OrderPrice" column.

SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders

The result set is similar to the following:

FirstOrderPrice
1000

The LAST () function returns the value of the LAST record in the specified field.
Tip: You can use the order by statement to sort records.
SQL LAST () syntax

SELECT LAST(column_name) FROM table_name

SQL LAST () instance
We have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now, we want to find the last value of the "OrderPrice" column.

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

The result set is similar to the following:

LastOrderPrice
100

The MAX () function returns the maximum value in a column. NULL values are not included in calculation.
SQL MAX () syntax

SELECT MAX(column_name) FROM table_name

Note: MIN and MAX can also be used in text columns to obtain the highest or lowest values in alphabetical order. SQL MAX () instance
We have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now, we want to find the maximum value of the "OrderPrice" column.

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

The result set is similar to the following:

LargestOrderPrice
2000

The MIN () function returns the minimum value in a column. NULL values are not included in calculation.
SQL MIN () syntax

SELECT MIN(column_name) FROM table_name

Note: MIN and MAX can also be used in text columns to obtain the highest or lowest values in alphabetical order.
SQL MIN () instance
We have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now, we want to find the minimum value of the "OrderPrice" column.

SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders

The result set is similar to the following:

SmallestOrderPrice
100

The SUM () function returns the total number (total) of numeric columns ).
SQL SUM () syntax

SELECT SUM(column_name) FROM table_name

SQL SUM () instance
We have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now, we want to find the total number of "OrderPrice" fields.

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

The result set is similar to the following:

OrderTotal
5700

What functions does SQL have? The more detailed, the better.

This kind of questions can be searched online, and you don't need to come here to ask
Send you a message
1. Absolute Value
SQL: select abs (-1) value
O: select abs (-1) value from dual
2. INTEGER (large)
S: select ceiling (-1.001) value
O: select ceil (-1.001) value from dual
3. Round (small)
S: select floor (-1.001) value
O: select floor (-1.001) value from dual
4. Round (truncation)
S: select cast (-1.002 as int) value
O: select trunc (-1.002) value from dual
5. Rounding
S: select round (1.23456, 4) value 1.23460
O: select round (1.23456, 4) value from dual 1.2346
6. e is the base power
S: select Exp (1) value 2.7182818284590451
O: select Exp (1) value from dual 2.71828182
7. Take the base logarithm of e.
S: select log (1, 2.7182818284590451) value 1
O: select ln (2.7182818284590451) value from dual; 1
8. Use 10 as the base logarithm.
S: select log10 (10) value 1
O: select log (10, 10) value from dual; 1
9. Square
S: select SQUARE (4) value 16
O: select power (4, 2) value from dual 16
10. Take the square root
S: select SQRT (4) value 2
O: select SQRT (4) value from dual 2
11. Evaluate the base power of any number
S: select power (3, 4) value 81
O: select power (3, 4) value from dual 81
12. Random Number acquisition
S: select rand () value
O: select sys. dbms_random.value (0, 1) value from dual;
13. Get the symbol
S: select sign (-8) value-1
O: select sign (-8) value from dual-1
---------- Mathematical functions
14. Circumference Rate
S: select pi () value 3.1415926535897931
O: Unknown
15. sin, cos, and tan parameters are in radians.
For example, select sin (PI ()/2) value to get 1 (SQLServer)
16. Asin, Acos, Atan, Atan2 return radians
17. radian angle Interchange (, Oracle unknown)
DEGREES: Radian-> Angle
RADIANS: Angle-> radian
--------- Comparison of Values
18. Calculate the maximum value of the Set
S: select max (value) value from
(Select 1 value
Union
Select-2 value
Union
Select 4 value
Union
Select 3 value)
O: sele ...... remaining full text>

Common SQL Functions

1. isnull () null record operation
2. getdate () Get the current time
3. convert () type conversion
4. dateadd () date added
5. datediff () date difference
6. Use substring () to obtain a string.
7. replace () string replacement
8. left (), right () Take the left and right substrings
9. LTrim (), RTrim () removes the Left and Right spaces of the string
10. STR (), int () type conversion