• 周五. 12月 2nd, 2022

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

Database: built in functions of MySQL

[db:作者]

1月 6, 2022

Preface

sql It’s a skill that developers can’t get around , and mysql It’s the most popular database right now , Be familiar with some of its built-in functions , Can make our usual development work more smooth and convenient

  • Time date function
  • String function
  • Mathematical calculation of correlation function
  • Conditional judgment function
  • Encryption and compression functions
  • Aggregate functions
  • Format or type conversion function
  • Lock function and unlock function

Official account , Communicate together , Search on wechat : Sneak forward

github Address , thank star

1 Time date function

| function | Function description |
| — | — |
| CURDATE(),CURRENT_DATE() | Return current date , Accurate to date |
|CURTIME(),CURRENT_TIM | Return current time , Minutes and seconds |
|NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),LOCALTIMESTAMP() | Returns the current date and time : Mm / DD / yyyy HHM / S |
|UNIX_TIMESTAMP() | With UNIX Returns the current time in the form of a timestamp |
|UNIX_TIMESTAMP(timeStr) | Time timeStr String to UNIX Return in the form of a timestamp |
|FROM_UNIXTIME(timestamp) | take UNIX Time stamp timestamp Time to convert to normal format |
|UTC_DATE() | return UTC date |
|UTC_TIME() | return UTC Time |
|MONTH(dateTime) | Return date d The month in is worth ,1~12 SELECT MONTH(‘2021-06-05 11:11:11’)->06|
|MONTHNAME(dateTime) | Return date dateTime The name of the month in it , Such as Janyary|
|DAYNAME(dateTime) | Return date dateTime What day is , Such as Monday,Tuesday|
|DAYOFWEEK(dateTime) | date dateTime What day is today ,1: Sunday ,2: Monday …7: Saturday |
|WEEKDAY(dateTime) | date dateTime What day is today , 0 For Monday ,1 For Tuesday |
|WEEK(dateTime),WEEKOFYEAR(dateTime) | Calculate the date dateTime It’s the first few weeks of the year , The scope is 0->53 |
|DAYOFYEAR(dateTime) | Calculate the date dateTime It’s the day of the year |
|DAYOFMONTH(dateTime) | Calculate the date dateTime It’s the day of the month |
|QUARTER(dateTime) | Return date dateTime What season is it , return 1->4 |
|HOUR(t) | return t The hour value in SELECT HOUR(‘5:13:14’) ->5 |
|MINUTE(t) | return t Minutes in SELECT MINUTE(‘5:13:14’) ->13 |
|SECOND(t) | return t The second value in SELECT SECOND(‘5:13:14’) ->14 |
|EXTRACT(type FROM dateTime) | From the date dateTime Get the specified value from the ,type Specifies the value to be returned SELECT EXTRACT(MINUTE FROM ‘2021-06-05 11:13:14’) ->13 |
TIME_TO_SEC(time) | Time time Convert to seconds :SELECT TIME_TO_SEC(’01:12:00′) ->4320
SEC_TO_TIME(second) | Time in seconds second Convert to minutes and seconds SELECT SEC_TO_TIME(4320)->01:12:00
TO_DAYS(dateTime) | Calculate the date dateTime distance 0000 year 1 month 1 Days of the day
DATEDIFF(date1,date2) | Calculate the date date1->date2 The days between SELECT DATEDIFF(‘2001-01-01′,’2001-02-02’)->-32
ADDDATE(d,n) | Calculate actual date d add n The date of day
ADDDATE(d,INTERVAL expr type) | Calculate start date d Add the date after a period of time ; SELECT ADDDATE(‘2021-06-11 11:13:14’, INTERVAL 5 MINUTE)->2021-06-11 11:18:14 (TYPE The value of is similar to the function listed above )
SUBDATE(d,n) | date d subtract n Days after
SUBDATE(d,INTERVAL expr type) | date d Minus the date after a period of time
ADDTIME(t,n) | Time t add n The second time
SUBTIME(t,n) | Time t subtract n The second time
DATE_FORMAT(d,f) | By expression f To display the date d
TIME_FORMAT(t,f) | By expression f The request shows the time t

  • EXTRACT Methodical type It can be taken as :MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH

2 String function

function Function description
CHAR_LENGTH(s) Return string s The number of characters
LENGTH(s) Return string s The length of
CONCAT(s1,s2,…) The string s1,s2 When multiple strings are merged into a single string
CONCAT_WS(x,s1,s2,…) Same as CONCAT(s1,s2,…) function , But use x As a connector
INSERT(s1,x,len,s2) Use string s2 Replace s1 Of x Position start , The length is len String
UPPER(s),UCAASE(S) The string s All of the letters of the alphabet become capital letters
LOWER(s),LCASE(s) The string s All of the letters of the are changed into lower case letters
LEFT(s,n) Return string s Before n Characters
RIGHT(s,n) Return string s After n Characters
LPAD(s1,len,s2) Use string s2 Fill in s1 At the beginning of , Make the string length to len
RPAD(s1,len,s2) Use string s2 Fill in s1 At the end of , Make the length of the string equal to len
LTRIM(s) Remove string s The space at the beginning
RTRIM(s) Remove string s Space at the end
TRIM(s) Remove string s The spaces at the beginning and the end
TRIM(s1 FROM s) Remove string s The string at the beginning and end of s1 SELECT TRIM(‘@’ FROM ‘@@[email protected]@’) -> abc
REPEAT(s,n) The string s repeat n Time
SPACE(n) return n A space
REPLACE(s,s1,s2) Use string s2 Alternative string s String in s1
STRCMP(s1,s2) Compare strings s1 and s2
SUBSTRING(s,n,len) Get from string s No n The starting length of a position is len String
MID(s,n,len) Same as SUBSTRING(s,n,len)
LOCATE(s1,s),POSITION(s1 IN s) From a string s In order to get s1 The beginning of
INSTR(s,s1) From a string s In order to get s1 The beginning of
REVERSE(s) The string s In reverse order
FIELD(s,s1,s2…) Returns the first and string s Matching string position ;SELECT FIELD(‘c’,’a’,’b’,’c’) -> 3
SUBSTRING_INDEX Return from string str Of the count Separators that appear delim After the string

3 Mathematical calculation of correlation function 】

function Function description
ABS(x) return x The absolute value of
CEIL(x),CEILING(x) Rounding up
FLOOR(x) Rounding down
RAND() return 0~1 Random number of ranges
RAND(x)
SIGN(x) Judge whether the value is positive or negative or zero
PI() Return the PI (3.141593)
TRUNCATE(x,y) Returns the value x Keep it after the decimal point y The value of a ( truncation )
ROUND(x) Return from x The nearest integer , rounding
ROUND(x,y) Retain x After the decimal point y The value of a , But the truncation should be rounded
POW(x,y).POWER(x,y) return x Of y Power
SQRT(x) return x The square root of
EXP(x) return e Of x Power SELECT EXP(3) — 20.085536923188
MOD(x,y) return x Divide y The remainder after
LOG(x) Return to the natural logarithm ( With e Log base )
LOG10(x) Return to 10 Log base
RADIANS(x) Convert the Angle to radians
DEGREES(x) Convert radians to degrees
SIN(x) Find the sine ( The parameter is radian )
ASIN(x) Find the inverse sine ( The parameter is radian )
COS(x) Find the cosine ( The parameter is radian )
ACOS(x) Find the inverse cosine ( The parameter is radian )
TAN(x) Find tangent ( The parameter is radian )
ATAN(x) ATAN2(x) Find the arctangent ( The parameter is radian )
COT(x) Find the cotangent value ( The parameter is radian )

4 Conditional judgment function

function Function description
IF(expr1,expr2,expr3) If expr1 Not for 0 perhaps NULL, Then return to expr2 Value , Otherwise return to expr3 Value
IFNULL(expr1,expr2) If expr1 Not for NULL, return expr1, Otherwise return to expr2
NULLIF(expr1,expr2) If expr1=expr2 Then return to NULL, Otherwise return to expr2
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result …] [ELSE result] END When compare_value=value When to return to result
CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END When condition by TRUE When to return to result

5 Encryption and compression functions

function Function description
MD5 MD5(str) Function can be used on string str To hash , It can be used for some common data encryption without decryption
SHA1(str), SHA(str) Calculation 160 Bit checksums , return 40 position 16 A string of decimal numbers , When str by NULL When to return to NULL
SHA2(str, hash_length) Calculation SHA-2 Hash method of series (SHA-224, SHA-256, SHA-384, and SHA-512)
ENCRYPT(str[,salt]) use unix crypt() To encrypt str,salt There must be at least two characters
ENCODE(str,pswd) ENCODE Functions can use encrypted passwords pswd To encrypt the string str
DECODE(crypt_str,pswd) Corresponding ENCODE function
MAX([DISTINCT] expr) minimum value
MIN([DISTINCT] expr) Maximum
SUM([DISTINCT] expr) The cumulative sum

6 Aggregate functions

function Function description
AVG([DISTINCT] expr) return expr Average value ,distinct Option to ignore duplicate values
COUNT([DISTINCT] expr) return select in expr Non – 0 Number of values , The return value is bigint type
GROUP_CONCAT Non null value in connection group , If there is no non null value , Then return to NULL

7 Format or type conversion function

function Function description
CONV(N,from_base,to_base) Change the numbers N Base of , The return value is a string of digits in the decimal system
INET_ATON(expr) ip String to number
INET_NTOA(expr) Number to ip character string
CAST(expr AS type) Convert data type
CONVERT(expr,type) type It can be for BINARY[(N)],CHAR[(N)],DATE,DATETIME, DECIMAL[(M[,D])],DECIMAL[(M[,D])],TIME,UNSIGNED [INTEGER] wait
CONVERT(expr USING cs) The string s The character set of becomes cs,cs Such as utf8 wait

8 Lock function and unlock function

function Function description
GET_LOCK(name,time) The function defines a name called nam、 The duration is time Second lock . If the lock is successful , Then return to 1; If the attempt times out , Then return to 0; If you encounter an error , return NULL.
RELEASE_LOCK(name) The function name is name Lock of . If the unlock is successful , Then return to 1; If the attempt times out , return 0 If the unlock fails , return NULL;
IS_FREE_LOCK(name) Function to determine whether a function named name Lock in . If you use , return 0, otherwise , return 1

Welcome refers to a mistake in the text

Reference article

  • MySQL Common built-in functions and all built-in functions
  • mysql Built in functions \
  • MySQL DATE_FORMAT() function

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注