SQL function collation

1. CONVERT ()
Definition: (1) General functions for converting dates to new data types
(2) Date/Time data can be displayed in different formats
Syntax: CONVERT (data_type (length), Data_to_be_converted,style)
Parameter: data_type (length) specifies the target data type (with optional lengths). The data_to_be_converted contains values that need to be converted. style Specifies the date/time output format
Example: CONVERT (VARCHAR), GETDATE (), 113)
Results: DEC 2008 16:25:46.635
2, Upper ()
Definition: Returns a string that is changed to uppercase based on all characters mapped by the current character set.
Example: Upper (' Allah ')
Results: ALLAH
3, Lower ()
Definition: Returns a string that returns a lowercase character based on the current character set mapping for all characters changed to lowercase.
4, IsNull ()
Definition: Replace NULL with the specified replacement value
Microsoft's ISNULL () function is used to specify how NULL values are handled.
NVL (), Ifnull (), and coalesce () functions can also achieve the same result.
Syntax: ISNULL (check_expression, Replacement_value)
Parameter: check_expression the expression that will be checked for null. Check_expression can be of any type.
Replacement_value the expression that will be returned when check_expression is null. Replacement_value must have the same type as check_expresssion.
Return type: Returns the same type as Check_expression
Note: If Check_expression is not NULL, the value of the expression is returned, otherwise Replacement_value is returned.
5, Row_number () over
Open Window function
6. AVG ()
Definition: The AVG function returns the average of a numeric column. NULL values are not included in the calculation.
Syntax: SELECT AVG (column_name) from table_name
7. Count ()
Definition: Count (column_name) function returns the number of values for the specified column (NULL does not count in)
Syntax: SELECT COUNT (column_name) from table_name
8. First ()
Definition: the first () function returns the value of one record in the specified field
Tip: You can use the order BY statement to sort records.
Syntax: Select First (column_name) from table_name
9. Last ()
Definition: The last () function returns the value of the final record in the specified field.
Tip: You can sort records by using the ORDER BY statement
Syntax: Select Last (column_name) from table_name
10. Max ()
Definition: The MAX function returns the maximum value in a column. NULL values are not included in the calculation.
Syntax: select MAX (column_name) from table_name
Note: MIN and MAX can also be used for text columns to get the highest or lowest values in alphabetical order.
11, Min ()
Definition: The Min function returns the minimum value in a column. NULL values are not included in the calculation.
Syntax: select min (column_name) from table_name
Note: MIN and MAX can also be used for text columns to get the highest or lowest values in alphabetical order.
12. SUM ()
Definition: The SUM function returns the total number of numeric columns (total)
Syntax: select SUM (column_name) from table_name
13. GROUP BY
Definition: A GROUP BY statement is used to combine aggregate functions to group result sets based on one or more columns
Syntax: SELECT column_name, Aggregate_function (column_name) from table_name
WHERE column_name operator Value
GROUP by column_name
14. Having
The addition of the HAVING clause in SQL is because the WHERE keyword cannot be used with the aggregate function.
Syntax: SELECT column_name, Aggregate_function (column_name)
From table_name
WHERE column_name operator Value
GROUP by column_name
Having aggregate_function (column_name) operator value
Example: Select Customer,sum (orderprice) from Orders
GROUP BY Customer
Having sum (orderprice) <2000
15, UCase ()
Definition: The UCASE function converts the value of a field to uppercase.
Syntax: SELECT UCASE (column_name) from table_name
16, LCase ()
Definition: The LCASE function converts the value of a field to lowercase.
Syntax: SELECT LCASE (column_name) from table_name
17, Mid ()
Definition: The MID function is used to extract characters from a text field
Syntax: SELECT MID (Column_name,start[,length]) from table_name
Parameters: Description:
column_name required, fields to extract characters from
Start required, specified starting position (starting value is 1)
Length is optional, and the mid () function returns the remaining text if omitted, if the string is to be returned.
18, Len ()
Definition: LEN function returns the length of a value in a text field
Syntax: SELECT LEN (column_name) from table_name
19, round ()
Definition: The ROUND function is used to round a numeric field to a specified number of decimal digits. Rounded to the nearest integer
Syntax: SELECT ROUND (column_name,decimals) from table_name
Parameters: Description:
column_name required, fields to be rounded
Decimals required, specifies the number of decimal digits to be returned
20. Now ()
Definition: The NOW function returns the current date and time.
Tip: If you are using a SQL Server database, use the GETDATE () function to get the current datetime
Syntax: SELECT now () from table_name
21. Format ()
Definition: The Format function is used for formatting the display of a field
Syntax: SELECT FORMAT (Column_name,format) from table_name
Parameters: Description:
column_name required, fields to format
Format required, specified formatting
Example: SELECT ProductName, UnitPrice, FORMAT (now (), ' Yyyy-mm-dd ') as Perdate
From Products

/*****/
Inner JOIN, left JOIN, right join, and full join differences

INNER join: Returns a row if there is at least one match in the table
Left JOIN: Returns all rows from the table, even if there is no match in the right table
Right JOIN: Returns all rows from the correct table even if there is no match in the left table
Full JOIN: Returns a row if there is a match in one of the tables

SQL add columns, modify columns, delete columns
1 Adding columns:
ALTER TABLE TableName add columnName varchar (30)
2.1 Modifying column types
ALTER TABLE tableName ALTER COLUMN columnName varchar (30)
2.2 Modifying column names
exec sp_rename ' tablename.column1 ', ' Column2 '
(Modify the Column1 column name of the table name TableName to Column2)
3 Deleting columns
ALTER TABLE tableName drop column ColumnName

SQL Server DATEADD ()
Syntax: DATEADD (datepart,number,date)
The date parameter is a valid day expression. The number is the number of intervals you want to add, and for the future time, this is positive, and for the past time, this number is negative
Example: Add 2 days to "OrderDate" so you can find the payment date
SELECT Orderid,dateadd (day,2,orderdate) as Orderpaydate
From Orders

SQL function collation