SQL advanced (10) function, sql10 Function

SQL advanced (10) function, sql10 Function
SQL advanced (10) function the SQL GROUP BY statement is used in combination with the aggregate function to GROUP the result set based on one or more columns.
SQL GROUP BY syntax

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name

SQL GROUP BY 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 amount (total order) for each customer ).
We want to use the group by statement to combine the customer.

SELECT Customer,SUM(OrderPrice) FROM OrdersGROUP BY Customer

The result set is similar to the following:

Customer	SUM (OrderPrice)
Bush	2000
Carter	1700
Adams	2000

Let's take a look at what will happen if group by is omitted:

SELECT Customer,SUM(OrderPrice) FROM Orders

The result set is similar to the following:

Customer	SUM (OrderPrice)
Bush	5700
Carter	5700
Bush	5700
Bush	5700
Adams	5700
Carter	5700

Group by more than one column
You can also apply the group by statement to more than one column, as shown in the following figure:

SELECT Customer,OrderDate,SUM(OrderPrice) FROM OrdersGROUP BY Customer,OrderDate

The HAVING clause of the SQL having clause is added to the SQL statement because the WHERE keyword cannot be used with the aggregate function.
SQL HAVING syntax

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value

For an SQL HAVING 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 customers whose total order amount is less than 2000.

SELECT Customer,SUM(OrderPrice) FROM OrdersGROUP BY CustomerHAVING SUM(OrderPrice)<2000

The result set is similar:

Customer	SUM (OrderPrice)
Carter	1700

Now we want to find the customer "Bush" or "Adams" with more than 1500 of the total order amount.
We add a common WHERE clause to the SQL statement:

SELECT Customer,SUM(OrderPrice) FROM OrdersWHERE Customer='Bush' OR Customer='Adams'GROUP BY CustomerHAVING SUM(OrderPrice)>1500

Result set:

Customer	SUM (OrderPrice)
Bush	2000
Adams	2000

SQL ucase () function SQL UCASE () syntax

SELECT UCASE(column_name) FROM table_name

SQL UCASE () instance
We have the following "Persons" table:

Id	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Th Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Now we want to select the content in the "LastName" and "FirstName" columns and convert the "LastName" column to uppercase.

SELECT UCASE(LastName) as LastName,FirstName FROM Persons

The result set is similar to the following:

LastName	FirstName
ADAMS	John
BUSH	George
CARTER	Thomas

SQL lcase () function SQL LCASE () syntax

SELECT LCASE(column_name) FROM table_name

SQL LCASE () instance
We have the following "Persons" table:

Id	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Th Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Now we want to select the content in the "LastName" and "FirstName" columns and convert the "LastName" column to lowercase letters.

SELECT LCASE(LastName) as LastName,FirstName FROM Persons

The result set is similar to the following:

LastName	FirstName
Adams	John
Bush	George
Carter	Thomas

SQL MID () function
The MID function is used to extract characters from text fields.
SQL MID () syntax

SELECT MID(column_name,start[,length]) FROM table_name

Parameters	Description
Column_name	Required. The field to extract characters.
Start	Required. Specify the start position (the start value is 1 ).
Length	Optional. The number of characters to return. If omitted, the MID () function returns the remaining text.

SQL MID () instance
We have the following "Persons" table:

Id	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Th Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Now, we want to extract the first three characters from the "City" column.

SELECT MID(City,1,3) as SmallCity FROM Persons

The result set is similar to the following:

SmallCity
Lon
New
Bei

SQL LEN () function
The LEN function returns the length of the median in a text field.
SQL LEN () syntax

SELECT LEN(column_name) FROM table_name

For SQL LEN () instances, we have the following "Persons" table:

Id	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Th Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Now we want to get the length of the value in the "City" column.

SELECT LEN(City) as LengthOfAddress FROM Persons

The result set is similar to the following:

LengthOfCity
6
8
7

The SQL ROUND () function ROUND () is used to ROUND a numeric field to a specified decimal place.
SQL ROUND () syntax

SELECT ROUND(column_name,decimals) FROM table_name

Parameters	Description
Column_name	Required. The field to be rounded.
Decimals	Required. Number of decimal places to be returned.

SQL ROUND () instance
We have the following "Products" table:

Prod_Id	ProductName	Unit	UnitPrice
1	Gold	1000 GB	32.35
2	Silver	1000 GB	11.56
3	Copper	1000 GB	6.85

Now we want to round the name and price to the nearest integer.

SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products

The result set is similar to the following:

ProductName	UnitPrice
Gold	32
Silver	12
Copper	7

SQL NOW () function
The NOW function returns the current date and time.
SQL NOW () syntax

SELECT NOW() FROM table_name

SQL NOW () instance
We have the following "Products" table:

Prod_Id	ProductName	Unit	UnitPrice
1	Gold	1000 GB	32.35
2	Silver	1000 GB	11.56
3	Copper	1000 GB	6.85

Now, we want to display the name and price of the day.

SELECT ProductName, UnitPrice, Now() as PerDate FROM Products

The result set is similar to the following:

ProductName	UnitPrice	PerDate
Gold	32.35	12/29/2008 11:36:05 AM
Silver	11.56	12/29/2008 11:36:05 AM
Copper	6.85	12/29/2008 11:36:05 AM

The SQL FORMAT () function FORMAT is used to FORMAT the field display.
SQL FORMAT () syntax

SELECT FORMAT(column_name,format) FROM table_name

Parameters	Description
Column_name	Required. The field to be formatted.
Format	Required. Required format.

SQL FORMAT () instance
We have the following "Products" table:

Prod_Id	ProductName	Unit	UnitPrice
1	Gold	1000 GB	32.35
2	Silver	1000 GB	11.56
3	Copper	1000 GB	6.85

Now, we want to display the name and price for the day date (the date is displayed in the "YYYY-MM-DD" format ").

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDateFROM Products

The result set is similar to the following:

ProductName	UnitPrice	PerDate
Gold	32.35	12/29/2008
Silver	11.56	12/29/2008
Copper	6.85	12/29/2008

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

SQL get time functions convert (varchar (10), getdate (), 111)

Select convert (varchar (10), getdate (), 111)
The result is without 00:00:00.
You can see that 00:00:00 is because you inserted this value into a datatime column, and the following is automatically supplemented.