Some functions in SQL (long-term update: ）

Objective

In the recent reading of other people's SQL, see some functions, such as Left (), right () and so on, what is the meaning of curiosity, after the query think it is very useful, hereby recorded. Blogs are updated regularly when new functions are encountered.

————————————————————————————————————————————————————

Body 1. Left () and right ()

In a SQL to meet the Left (Character,integer) function, feel curious, Baidu after the discovery of meaning is: return to Character,integer (character) from the first to the second integer. If the integer is negative, then an empty string is returned.

Right () in the same vein.

For example: Left ("ABCDEFG", 3) The result is: ABC, because to take ABCDEFG of the first three characters, that is, ABC.

It is important to note that there are no two functions in Oracle, the corresponding substr (string, intercept start position, intercept end position) in Oracle.

2. ISNULL (A, B)

With respect to IsNull (A, A, b), the meaning is: If A is null, then the value of B. is returned, and if not NULL, the value of a is returned. A common application is when aggregation is summed.

As an example:

ISNULL (sum (num), 0): returns 0 if the sum of the NUM columns results in null (that is, the database column has no value).

3. Now () function: <<mysql can use the >>now () function, which is used to get the current time function. MySQL and SQL Server are currently tested and can only be used in MySQL.

--mysql Database

SQL Server database, unrecognized built-in function

4. Day (), month (), year () Time function

• Day () function: Gets the date value of the query result
• Month () function: Gets the month value of the query result
• Year () function: Gets the years value of the query result

The normal year of the query, the 8-bit data format.

6-bit time format, if the last two bits of the year <= 12, then the system will default to the last two bits as the month and date to process, the first two bits default to 20+ "date of the first two bits", so get the time of December 01, 2020, need special attention!

If your year after the two-bit >12, then will be directly error! Because it cannot be converted to data larger than December. So it is usually recommended time format bit 8 bit!

So: If the time format is determined to be 6-bit, then it is recommended to use the left () function, not the year () function.

5. DATEADD () and Date_add () functions

In SQL Server, a few days later, the DATEADD () function is used, and the Date_add () function adds the specified time interval to the date.

The Date_add (date,interval expr type) Date parameter is a valid date expression. The expr parameter is the time interval that you want to add. The type parameter can be the following value: See W3cschool link

In MySQL, the Date_add () function is used, and the DATEADD () function adds or subtracts the specified time interval from the date.

DATEADD (datepart,number, date): The date argument 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. the datepart parameter can be the following value: See W3cschool link

Use the following:

Methods in SQL Server

Methods in MySQL

It is especially important to note that the time function of the date is more, refer to the W3cschool:w3cschool database time function link, which has MySQL and SQL Server time function.

6. Union and UNION ALL

When writing SQL, some data storage may involve the sub-database table, query, you may need to query all the sub-table, this time, you need to use the Union or union AL.

UNION operator: The result set used to merge two or more SELECT statements, it is important to note that the SELECT statement inside the Union must have the same number of

Columns, columns must also have similar data types, and the order of the columns in each SELECT statement must be the same.

SELECT  from User UNION  SELECT  from  The result is: Query the Name field from the user table and the User1 table, respectively, and merge together.

It is important to note that the Union operation conforms to the result set, does not allow repetition, and if not heavy, it needs to use union all.

Case sql: (ref: 73468979) Use a foreach loop, connect with union All, simplify operations, query multiple tables, and merge result sets. The list of sub-Libraries must be skills!

<SelectId="Getfourteenhotpost" ParameterType="Map" Resultmap="Productcommentsinfoandroid">          SelectT.comments_id,t.product_id,t.comment,t.order_path from (          <foreach Collection="Tablenames" Item="Item" Separator="UNION  All">              (SELECTC.comments_id,c.product_id,c.comment,i.order_path,c.p_index,c.t_index,c.title,c.time from${item} asC Left Join' Gshop_comments_img ' asI onc.comments_id=i.comments_idwhereC.object_type=2  andC.display=1  andC.is_show=1                andC.t_index=1               GROUP  byc.product_idORDER  byC.p_indexASC, C.t_indexdesc, C.titledesc, C.timedescLimit -)            </Foreach>) TGROUP  byt.product_idORDER  byT.p_indexASC, T.t_indexdesc, T.titledesc, T.timedescLimit -      </Select>  

————————————————————————————————————————————————————

Postscript

Here are just a few of the functions that are currently being met and will continue to update if you encounter them later.

Some functions in SQL (long-term update: ）