Preface
SQL is a skill that program developers cannot avoid, and mysql is currently the most popular database. Knowing some of its built-in functions can make our usual development work smoother and more convenient.
- Date and time functions
- String function
- Mathematical calculation related functions
- Conditional judgment function
- Encryption and compression functions
- Aggregate function
- Format or type conversion function
- Lock function and unlock function
Follow the public account, communicate together, search on WeChat: sneak forward
1 Time and date functions
| Function | Function description |
| --- | --- |
| CURDATE(),CURRENT_DATE() |Returns the current date, accurate to year, month, and day|
|CURTIME(),CURRENT_TIM | Returns the current time, hour, minute, and second |
|NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),LOCALTIMESTAMP() |Returns the current date and time: year, month, day, hour, minute, and second|
|UNIX_TIMESTAMP() |Return the current time as a UNIX timestamp|
|UNIX_TIMESTAMP(timeStr) | Return the string of time timeStr as a UNIX timestamp|
|FROM_UNIXTIME(timestamp) | Convert UNIX timestamp timestamp to common format time|
|UTC_DATE() |Returns UTC date|
|UTC_TIME() | Return UTC time|
|MONTH(dateTime) | Returns the month value in date d, 1～12 SELECT MONTH('2021-06-05 11:11:11')->06|
|MONTHNAME(dateTime) |Returns the month name in dateTime, such as Janyary|
|DAYNAME(dateTime) |Return date dateTime is the day of the week, such as Monday,Tuesday|
|DAYOFWEEK(dateTime) |Date dateTime Today is the day of the week, 1:Sunday, 2:Monday...7:Saturday|
|WEEKDAY(dateTime) |Date dateTime Today is the day of the week, 0 means Monday, 1 means Tuesday|
|WEEK(dateTime), WEEKOFYEAR(dateTime) |The calculation date dateTime is the first few weeks of the year, and the range is 0->53 |
|DAYOFYEAR(dateTime) |The calculation date dateTime is the day of the year|
|DAYOFMONTH(dateTime) |The calculation date dateTime is the day of the month|
|QUARTER(dateTime) |Returns the date dateTime is the first season, returns 1->4 |
|HOUR(t) |Returns the hour value in t SELECT HOUR('5:13:14') ->5 |
|MINUTE(t) |Returns the minute value in t SELECT MINUTE('5:13:14') ->13 |
|SECOND(t) |Returns the second value in t SELECT SECOND('5:13:14') ->14 |
|EXTRACT(type FROM dateTime) | Get the specified value from dateTime, type specifies the returned value SELECT EXTRACT(MINUTE FROM '2021-06-05 11:13:14') ->13 |
TIME_TO_SEC(time) | Convert time to seconds: SELECT TIME_TO_SEC('01:12:00') ->4320
SEC_TO_TIME(second) | Convert the time in seconds to the format of hours, minutes and seconds SELECT SEC_TO_TIME(4320)->01:12:00
TO_DAYS(dateTime) | Calculate the number of days between the date dateTime and January 1, 0000
DATEDIFF(date1,date2) |Calculate the number of days between date1->date2 SELECT DATEDIFF('2001-01-01','2001-02-02')->-32
ADDDATE(d,n) | Calculate the actual date d plus the date of n days
ADDDATE(d, INTERVAL expr type) | Calculate the start date d plus the date after a period of time; SELECT ADDDATE('2021-06-11 11:13:14', INTERVAL 5 MINUTE)->2021-06-11 11:18:14 (The value of TYPE is similar to the function listed above)
SUBDATE(d,n) | Date d minus the date after n days
SUBDATE(d,INTERVAL expr type) | Date d minus the date after a period of time
ADDTIME(t,n) | Time t plus n seconds
SUBTIME(t,n) | Time t minus n seconds
DATE_FORMAT(d,f) | Display the date d as required by the expression f
TIME_FORMAT(t,f) | Display the time t as required by the expression f
- The type of EXTRACT method can be: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, HOUR_MINUTE, DAY_MICROSECOND, HOUR_MINUTE, DAY_MICROSECOND, HOUR_MINUTE, DAY_MICROSECOND,
2 String functions
| function | Function description |
|---|
| CHAR_LENGTH(s) | Returns the number of characters in the string s |
| LENGTH(s) | Returns the length of the string s |
| CONCAT(s1,s2,...) | Combine multiple strings such as string s1, s2 into one string |
| CONCAT_WS(x,s1,s2,...) | Same as CONCAT(s1,s2,...) function, but use x as the connector |
| INSERT(s1,x,len,s2) | Replace the string starting at the x position of s1 with the string s2 and the length is len |
| UPPER(s),UCAASE(S) | Turn all letters of the string s into uppercase letters |
| LOWER(s),LCASE(s) | Turn all letters of the string s into lowercase letters |
| LEFT(s,n) | Returns the first n characters of the string s |
| RIGHT(s,n) | Returns the last n characters of the string s |
| LPAD(s1,len,s2) | Use the string s2 to fill the beginning of s1 so that the length of the string reaches len |
| RPAD(s1,len,s2) | Use the string s2 to fill the end of s1 so that the length of the string reaches len |
| LTRIM(s) | Remove the spaces at the beginning of the string s |
| RTRIM(s) | Remove the spaces at the end of the string s |
| TRIM(s) | Remove the spaces at the beginning and end of the string s |
| TRIM(s1 FROM s) | Remove the string s1 at the beginning and end of the string s SELECT TRIM('@' FROM'@@abc@@') -> abc |
| REPEAT(s,n) | Repeat string s n times |
| SPACE(n) | Return n spaces |
| REPLACE(s,s1,s2) | Replace string s1 in string s with string s2 |
| STRCMP(s1,s2) | Compare strings s1 and s2 |
| SUBSTRING(s,n,len) | Get a string of length len starting from the nth position in the string s |
| MID(s,n,len) | Same as SUBSTRING(s,n,len) |
| LOCATE(s1,s),POSITION(s1 IN s) | Get the start position of s1 from the string s |
| INSTR(s,s1） | Get the start position of s1 from the string s |
| REVERSE(s) | Reverse the order of the string s |
| FIELD(s,s1,s2...) | Returns the position of the first string matching the string s; SELECT FIELD('c','a','b','c') -> 3 |
| SUBSTRING_INDEX | Returns the substring after the count-th occurrence of the delimiter delim from the string str |
3 Mathematical calculation related functions]
| function | Function description |
|---|
| ABS(x) | Returns the absolute value of x |
| CEIL(x),CEILING(x) | Rounded up |
| FLOOR(x) | Round down |
| RAND() | Returns a random number ranging from 0 to 1 |
| RAND(x) | |
| SIGN(x) | Determine whether the value is positive or negative or zero |
| PI() | Returns the pi (3.141593) |
| TRUNCATE(x,y) | Return the value x to the y place after the decimal point (truncated) |
| ROUND(x) | Returns the integer closest to x, rounded up |
| ROUND(x,y) | Keep the value of y digits after the x decimal point, but round off when truncating |
| POW(x,y).POWER(x,y) | Returns x to the power of y |
| SQRT(x) | Returns the square root of x |
| EXP(x) | Return e to the power of x SELECT EXP(3) - 20.085536923188 |
| MOD(x,y) | Returns the remainder after dividing x by y |
| LOG(x) | Returns the natural logarithm (the logarithm to the base e) |
| LOG10(x) | Returns the base 10 logarithm |
| RADIANS(x) | Convert angle to radians |
| DEGREES(x) | Convert radians to angles |
| SIN(x) | Find the sine value (parameter is radians) |
| ASIN(x) | Find the arc sine (parameter is radians) |
| COS(x) | Find the cosine value (parameter is radians) |
| ACOS(x) | Find the arc cosine (parameter is radians) |
| TAN(x) | Find the tangent value (parameter is radians) |
| ATAN(x) | ATAN2(x) find the arctangent value (parameter is radians) |
| COT(x) | Find the cotangent value (parameter is radians) |
4 Conditional judgment function
| function | Function description |
|---|
| IF(expr1,expr2,expr3) | If expr1 is not 0 or NULL, return the value of expr2, otherwise return the value of expr3 |
| IFNULL(expr1,expr2) | If expr1 is not NULL, return expr1, otherwise return expr2 |
| NULLIF(expr1,expr2) | If expr1=expr2 then return NULL, otherwise return expr2 |
| CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END | Return result when compare_value=value |
| CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END | Return result when condition is TRUE |
5 Encryption and compression functions
| function | Function description |
|---|
| MD5 | The MD5(str) function can hash the string str, which can be used for some common data encryption that does not need to be decrypted |
| SHA1(str), SHA(str) | Calculate the checksum of 160 digits, and return a string of 40 hexadecimal digits, and return NULL when str is NULL |
| SHA2(str, hash_length) | Calculate the hash method of SHA-2 series (SHA-224, SHA-256, SHA-384, and SHA-512) |
| ENCRYPT(str[,salt]) | Use unix crypt() to encrypt str, salt must have at least two characters |
| ENCODE(str,pswd) | The ENCODE function can use the encrypted password pswd to encrypt the string str |
| DECODE(crypt_str,pswd) | Corresponding to ENCODE function |
| MAX([DISTINCT] expr) | Minimum |
| MIN([DISTINCT] expr) | Maximum value |
| SUM([DISTINCT] expr) | Cumulative sum |
6 Aggregate functions
| function | Function description |
|---|
| AVG([DISTINCT] expr) | Returns the average value of expr, the distinct option is used to ignore duplicate values |
| COUNT([DISTINCT] expr) | Returns the number of non-zero values of expr in select, the return value is of type bigint |
| GROUP_CONCAT | The non-null value in the connection group, if there is no non-null value, NULL is returned |
7 Format or type conversion function
| function | Function description |
|---|
| CONV(N,from_base,to_base) | Change the base of the number N, the return value is a string composed of the numbers in the base |
| INET_ATON(expr) | ip string to number |
| INET_NTOA(expr) | Number to ip string |
| CAST(expr AS type) | Convert data type |
| CONVERT(expr,type) | The type can be BINARY[(N)], CHAR[(N)], DATE, DATETIME, DECIMAL[(M[,D])], DECIMAL[(M[,D])], TIME, UNSIGNED [INTEGER], etc. Wait |
| CONVERT(expr USING cs) | Change the character set of the string s into cs, cs such as utf8, etc. |
8 Lock function and unlock function
| function | Function description |
|---|
| GET_LOCK(name,time) | The function defines a lock named nam with a duration of time seconds. If the lock is successful, it returns 1; if the attempt times out, it returns 0; if it encounters an error, it returns NULL. |
| RELEASE_LOCK(name) | The function releases the lock named name. If the unlock is successful, it returns 1; if the attempt is timed out, it returns 0. If the unlock fails, it returns NULL; |
| IS_FREE_LOCK(name) | The function determines whether the lock named name has been used. If used, return 0, otherwise, return 1 |
Welcome refers to the error in the text
Reference article
java
程序员
mysql
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。