Dark horse programmer-common SQL functions _ MySQL

Dark horse programmer-common SQL functions bitsCN.com

Dark horse programmer-common SQL functions

Lists some common SQL functions.

Numeric functions

ABS () calculates the absolute value (reminds me of the ABS anti-lock system)

CEILING () is rounded to the maximum integer, and-3.6 is rounded to-3.

FLOOR () rounded to the smallest integer,-3.6 rounded to-4

ROUND () rounding, ROUND (3.141, 2) needs to input two parameters, the first is the operand, the last is the precision

String functions

LEN () calculates the string length

LOWER () to lowercase characters

Convert UPPER () to uppercase characters

LTRIM () removes the left space

RTRIM () remove right Space

SUBSTRING (string, start_position, lenth) string truncation function. the truncation length starting from start_position is lenth.

Date functions

GETDATE () obtains the current date.

The DATEADD (datepart, number, date) function is used to calculate the incremental date. datepart is the unit of measurement, and date is the date to be operated.

Datepart can be selected: year, quarter, month, dayofyear, day, week, weekday, hour, minute, second

DATEDIFF (datepart, startdate, enddate) returns the difference between two dates based on datepart

DATEPART (datepart, date) returns the specified part of the date.

Type conversion

CAST (expression as type)

CONVERT (type, expression)

Null value processing function

ISNULL (expression, value) determines whether expression is returned if expression is not empty; otherwise, value is returned.

Select ISNULL (name, 'Alias name') from Person

Single value judgment

Similar to the switch case statement.

CASE expression

WHEN value1 THEN return1

WHEN value2 THEN return2

ELSE return3

END

When the range is determined after the when, there can be no expression after the case.

ROW_NUMBER () function

The function is to count the row number.

ROW_NUMBER () is a window function. it cannot appear in where and can only appear in select or order.

Select * from

(Select ROW_NUMBER () OVER (order by salary) as rownum, id, name, from Person) as e1

Where e1.rownum> 3 and e1.rownum <5

In this way, you can obtain the desired data row at will.

-The End-

BitsCN.com