Common functions of MySQL
1. String class function
(1) CONCAT (s 1, 2, 3.) Connection string
For example: SELECT CONCAT ('1m 2') FROM DUAL
Output: 12
(2) LOWER (s) changes all strings to lowercase
For example: SELECT LOWER ('ABC') FROM DUAL
Output: abc
(3) UPPER (s) changes all strings to uppercase
For example: SELECT UPPER ('abc') FROM DUAL
Output: ABC
(4) LTRIM (s) remove the space on the left side of the string
For example: select LTRIM ('abc') from dual
Output: abc
(5) RTRIM (s) remove the space on the right side of the string
For example: select LTRIM ('abc') from dual
Output: abc
(6) TRIM (s) removes the spaces on the left and right sides of the string
For example: select LTRIM ('abc') from dual
Output: abc
(7) LPAD uses the string pad to fill the left side of s until the length reaches len
For example: SELECT LPAD ('1pm, 5pm, 0') FROM DUAL
Output: 00001
(8) RPAD uses the string pad to fill the right side of s until the length reaches len
For example: SELECT RPAD ('1pm, 5pm, 0') FROM DUAL
Output: 10000
(9) REPEAT (sPerry x) returns after repeating s by x
For example: select REPEAT ('axioma5) from dual
Output: aaaaa
(10) REPLACE replaces the character in the string containing form with target
For example: SELECT REPLACE ('abc','a','A') FROM DUAL
Output: Abc
(11) STRCMP (S1 Magi S2) compare S1 with S2. If the same returns 0 Magi S2 greater than S1, return 1 Magi S2 less than S1 return-1
For example: SELECT STRCMP, FROM DUAL.
Output:-1 0 1
(12) LEFT (sjournal x) returns x characters to the left of the string
For example: SELECT LEFT ('abc',2) FROM DUAL
Output: ab
(13) RIGHT (sjournal x) returns x characters to the right of the string
For example: SELECT RIGHT ('abc',2) FROM DUAL
Output: bc
(14) MID (sdepartment xrem y) returns the string x position starting y characters
For example: SELECT MID ('abcd',3,2) FROM DUAL
Output: cd
(15) SUBSTRING returns the string x bit starting y characters, which is basically the same as MID.
For example: SELECT SUBSTRING ('abcd',3,2) FROM DUAL
Output: cd
(16) INSERT (srecoery xrem yref) replaces the string x position starting y characters with form characters
For example: SELECT INSERT ('abcd',3,2,'FF') FROM DUAL
Output: abFF
(17) LENGTH (s) returns the length of s
For example: SELECT LENGTH ('123') FROM DUAL
Output: 3
(18) REVERSE (s) returns s to reverse the order
For example: SELECT REVERSE ('abc') FROM DUAL
Output: cba
two。 Digital class function
(1) CEIL (n) returns an integer greater than the maximum of n
For example: SELECT CEIL (100.1) FROM DUAL
Output: 101
(2) FLOOR (n) returns an integer less than the maximum of n
For example: SELECT CEIL (100.1) FROM DUAL
Output: 100
(3) RAND () returns a random number from 0 to 1
For example: SELECT RAND () FROM DUAL
Output: 0.13824381133377908
(4) ROUND (nQuery y) rounds n to retain y decimal places
For example: SELECT ROUND (10.127jue 2) FROM DUAL
Output: 10.13
(5) TRUNCATE (nQuery y) preserves n with y decimal places
For example: SELECT TRUNCATE (10.127777777) FROM DUAL
Output: 10.12
3. Date class function
(1) NOW () returns the current date and time
For example: SELECT NOW () FROM DUAL
Output: 2017-06-06 20:44:58
(2) CURDATE () returns the current date
For example: SELECT CURDATE () FROM DUAL
Output: 2017-06-06
(3) CURTIME () returns the current time
For example: select CURTIME () from dual
Output: 20:46:15
(4) WEEK (date) returns the week of the year
For example: SELECT WEEK ('2017-06-06') FROM DUAL
Output: 23
(5) YEAR (date) returns the year in the date
For example: SELECT YEAR ('2017-06-06') FROM DUAL
Output: 2017
(6) DATE_FORMAT (sfocus pattern) formatted date
For example: SELECT DATE_FORMAT (NOW (),'% Y% m% d') FROM DUAL
Output: 20170606
Format parameters:
% y represents a two-digit year. For example: (2017 returns 17)
% Y represents a four-digit year. For example: (2017 returns 2017)
% m represents a two-digit month. For example: (01jue 02pr .12)
% c represents the month of the number. For example: (1, 2, 3, 4, 4, 12)
% M means Yueming, English name.
D represents the number of days in two digits. For example: (01pr 02pr 03pr .31)
% e represents the number of days of the number. For example (1, 2, 3, 4, 4, and 31)
% H represents two-digit hours, 24-hour system. For example: (01pr 02pr .pr 24)
% I represents the number of two-digit minutes. For example: (01pd02.pr. 60)
S s represents the number of seconds of two digits. For example: (01pd02.pr. 60)
(7) DATEDIFF (D1 and D2) returns the number of days between D1 and D2
For example: SELECT DATEDIFF ('2017-06-07) FROM DUAL
Output: 1
(8) DATE_ADD (d _ intermediate y type) adds a specified type y value to the date
For example: select DATE_ADD ('2017-06-07 recording interval 1 month) from dual
Output: 2017-07-07
Type parameters:
YEAR represents year
MONTH represents month
DAY represents day
HOUR stands for hours
MINUTE stands for minutes
SECOND stands for seconds
(9) TO_DAYS (date) returns the number of days date has been in since 20 years.
4. Flow control class function
(1) IF (vQuery tmenf) returns t if v is true, otherwise returns f
For example: SELECT IF (1pm, 1pm, 2') FROM DUAL
Output: 2
(2) IFNULL (v1PowerV2) if v1 does not return v1 for null, otherwise return v2
For example: SELECT IFNULL (NULL,'1') FROM DUAL
Output: 1
(3) CASE WHEN v then v1 end determines that if v is true, return v1.
For example: SELECT CASE WHEN 1, 1 THEN 'true' ELSE' false' END FROM DUAL
Output: true
For example: SELECT CASE WHEN 1 THEN 'true' WHEN 2' 2 THEN 'test' ELSE' false' END FROM DUAL
Output: test
5. Conversion class function
(1) CAST (v as type) converts data types.
Type parameters:
Character type: CHAR
Date: DATE
Time: TIME
Date and time type: DATETIME
Floating point numbers: DECIMAL
Integer: SIGNED
Unsigned integers: UNSIGNED
6. Database class function
(1) DATABASE () returns the current database name
(2) VERSION () returns the current database version
(3) INET_ATON (ip) returns the IP represented by numbers.
(4) INET_NTOA (num) converts numeric IP to IP
(5) PASSWORD (s) returns the encrypted version
(6) MD5 (s) returns the MD5 encryption value
