The MySQL database provides a number of functions including:
Mathematical functions are mainly used for processing numbers, including integers, floating-point numbers, and so on.
| Function | Role |
| ABS (x) | Returns the absolute value of X SELECT ABS ( -1) --Return 1 |
| Ceil (x), CEILING (x) | Returns the smallest integer greater than or equal to X SELECT ceil (1.5) --Return 2 |
| Floor (x) | Returns the largest integer less than or equal to X SELECT Floor (1.5) --Return 1 |
| RAND () | Returns the random number of 0->1 SELECT RAND () --0.93099315644334 |
| RAND (x) | Returns the random number of 0->1 with the same number of random numbers returned at the same X value SELECT RAND (2) --1.5865798029924 |
| Sign (x) | Returns the x symbol, x is negative, 0, positive numbers are returned-1, 0, and 1, respectively. SELECT sign (-10)--(-1) |
| PI () | return Pi (3.141593) SELECT PI () --3.141593 |
| TRUNCATE (x, y) | Returns the value of x reserved to the Y-bit after the decimal point (the biggest difference from round is that it will not be rounded) SELECT TRUNCATE (1.23456,3)--1.234 |
| ROUND (x) | Returns the nearest integer from X SELECT ROUND (1.23456) --1 |
| ROUND (x, y) | The value of the Y-bit after the decimal point is retained, but is rounded when truncated SELECT ROUND (1.23456,3) -- 1.235 |
| POW (x, y). POWER (x, y) | Returns the Y-order of X SELECT POW (2,3) --8 |
| SQRT (x) | Returns the square root of X SELECT SQRT ( 5 ) |
| EXP (x) | Returns the X-square of E SELECT EXP (3) --20.085536923188 |
| MOD (x, y) | Returns the remainder after x divided by y SELECT MOD (5,2) --1 |
| LOG (x) | Returns the natural logarithm (base e logarithm) SELECT LOG (20.085536923188)--3 |
| LOG10 (x) | Returns the base 10 logarithm SELECT LOG10 ( 2 ) |
| RADIANS (x) | Convert an angle to radians SELECT RADIANS (180)--3.1415926535898 |
| DEGREES (x) | Convert radians to angles SELECT DEGREES (3.1415926535898) -- |
| SIN (x) | To find the sine value (the parameter is radians) SELECT SIN (RADIANS ()) --0.5 |
| ASIN (x) | To find the sine value (parameter is radians) |
| COS (x) | Calculate cosine (parameter is radians) |
| ACOS (x) | Inverse cosine value (parameter is radians) |
| TAN (x) | To find the tangent value (the parameter is radians) |
| ATAN (x) ATAN2 (x) | Calculate the inverse tangent value (parameter is radians) |
| COT (x) | Cotangent value (parameter is radians) |
String functions are the most common type of function in MySQL, and string functions are primarily used to manipulate strings in a table.
| Function | Description |
| Char_length (s) | Returns the number of characters in the string s SELECT char_length (' Hello 123 ')--5 |
| LENGTH (s) | Returns the length of the string s SELECT LENGTH (' Hello 123 ')--9 |
| CONCAT (S1,s2,...) | Combine strings s1,s2 and more into a single string SELECT CONCAT (' A ', ')- -1234 |
| Concat_ws (X,s1,s2,...) | Tong Concat (S1,s2,...) function, but each string is added directly to the X SELECT concat_ws (' @ ', ' a ', ' "') '- -[email protected] |
| INSERT (S1,X,LEN,S2) | Replace string S2 with S1 x position starting at Len length SELECT INSERT (' 12345 ', 1,3, ' abc ')- -Abc45 |
| UPPER (s), ucaase (s) | Converts all letters of the string s to uppercase SELECT UPPER (' abc ')- -ABC |
| LOWER (s), LCASE (s) | Turn all the letters of the string s into lowercase letters SELECT LOWER (' abc ')--ABC |
| Left (S,n) | Returns the first n characters of a string s SELECT left (' ABCDE ', 2) --AB |
| Right (S,n) | Returns the second n characters of the string s SELECT right (' ABCDE ', 2)--DE |
| Lpad (S1,LEN,S2) | String S2 to fill the beginning of the S1, so that the string length reaches Len SELECT lpad (' abc ', 5, ' xx ')- -Xxabc |
| Rpad (S1,LEN,S2) | String S2 to fill the end of the S1, so that the length of the string reaches Len SELECT rpad (' abc ', 5, ' xx ')- -Abcxx |
| LTRIM (s) | Remove the space at the beginning of the string s |
| RTRIM (s) | Remove the space at the end of the string s |
| TRIM (s) | Remove the space at the beginning and end of the string s |
| TRIM (S1 from S) | Removes the string at the beginning and end of the string s S1 SELECT TRIM (' @ ' from ' @@[email protected]@ ') --ABC |
| REPEAT (S,n) | Repeats the string s n times SELECT REPEAT (' AB ', 3) --Ababab |
| SPACE (N) | Returns n Spaces |
| REPLACE (S,S1,S2) | S2 the string to a string in an alternate string s S1 SELECT REPLACE (' abc ', ' A ', ' X ') --XBC |
| STRCMP (S1,S2) | Compare strings S1 and S2 |
| SUBSTRING (S,n,len) | Gets a string starting from the nth position in the string s with the length Len |
| MID (S,n,len) | With substring (s,n,len) |
| LOCATE (S1,s), POSITION (S1 in s) | Gets the starting position of the S1 from the string s SELECT LOCATE (' B ', ' abc ')--2 |
| INSTR (S,S1) | Gets the starting position of the S1 from the string s SELECT INSTR (' abc ', ' B ')--2 |
| REVERSE (s) | Reverse the order of the string s SELECT REVERSE (' abc ')- -CBA |
| ELT (N,s1,s2,...) | Returns the nth string SELECT ELT (2, ' A ', ' B ', ' C ')--B |
| Export_set (X,S1,S2) | Returns a string where you get an "on" string for each bit in "bits", and for each reset (reset) bit, you get a "off" string. Each string is delimited with "separator" (Default ","), and only "bits" of "number_of_bits" (default 64) bits are used. SELECT Export_set (5, ' Y ', ' N ', ', ', 4) --Y,n,y,n |
| FIELD (S,s1,s2 ...) | Returns the first string position that matches the string s SELECT FIELD (' C ', ' A ', ' B ', ' C ')--3 |
| Find_in_set (S1,S2) | Returns the position of a string that matches S1 in the string s2 |
| Make_set (X,S1,S2) | Returns a collection containing the“,” A string of characters separated by substrings, consisting of a string of corresponding bits in the SELECT Make_set (1|4, ' A ', ' B ', ' C '); --A,c |
| Substring_index | Returns the substring that appears after the delimiter Delim from the count of the string str. If Count is a positive number, returns the string to the left of the count of characters. If count is a negative number, returns the string (the absolute value of count (from the right)) to the right of the character. SELECT substring_index (' a*b ', ' * ', 1)--A |
| Load_file (file_name) | Reads the file and returns the file contents as a string. The file must be on the server, you must specify the full path name to the file, and you must have file permissions. The file must have all the content readable and less than max_allowed_packet. If the file does not exist or because one of the above reasons cannot be read, the function returns NULL. |
MySQL's date and time functions are primarily used to process DateTime.
| Function | Description |
| Curdate (), Current_date () | Returns the current date SELECT Curdate () |
| Curtime (), Current_time | Return Current time SELECT Curtime () |
Now (), Current_timestamp (), localtime (), Sysdate (), Localtimestamp () |
Returns the current date and time SELECT Now () |
| Unix_timestamp () | Returns the current time as a Unix timestamp SELECT Unix_timestamp () |
| Unix_timestamp (d) | Returns time d as a Unix timestamp SELECT unix_timestamp (' 2011-11-11 11:11:11 ') |
| From_unixtime (d) | Time to convert the time of the Unix timestamp to the normal format SELECT From_unixtime (1320981071) |
| Utc_date () | Returns the UTC date SELECT Utc_date () |
| Utc_time () | return UTC time SELECT Utc_time () |
| MONTH (d) | Returns the month value in Date D, 1->12 SELECT MONTH (' 2011-11-11 11:11:11 ') |
| MONTHNAME (d) | Returns the month name in the date, such as Janyary SELECT MONTHNAME (' 2011-11-11 11:11:11 ') |
| Dayname (d) | Return Date D is the day of the week, such as Monday,tuesday SELECT dayname (' 2011-11-11 11:11:11 ') |
| DAYOFWEEK (d) | Date d today is the day of the week, 1 weeks, 2 weeks a SELECT DAYOFWEEK (' 2011-11-11 11:11:11 ') |
| WEEKDAY (d) | Date d today is the day of the week, 0 means Monday, 1 means Tuesday |
| WEEK (d), WeekOfYear (d) | Calculated Date d is the week ordinal of the year, the range is 0->53 SELECT WEEK (' 2011-11-11 11:11:11 ') |
| DayOfYear (d) | Calculated Date D is the day ordinal of this year SELECT dayofyear (' 2011-11-11 11:11:11 ') |
| DayOfMonth (d) | Calculated Date D is the day of the month SELECT dayofmonth (' 2011-11-11 11:11:11 ') |
| QUARTER (d) | Return Date D is the first season, return 1->4 SELECT QUARTER (' 2011-11-11 11:11:11 ') |
| HOUR (t) | Returns the hour value in T SELECT HOUR (' 1:2:3 ') |
| MINUTE (t) | Returns the minute value in T SELECT MINUTE (' 1:2:3 ') |
| SECOND (t) | Returns the seconds value in T SELECT SECOND (' 1:2:3 ') |
| EXTRACT (type from D) | Gets the specified value from Date D, type specifies the value returned SELECT EXTRACT (MINUTE from ' 2011-11-11 11:11:11 ') The value of type is: Microsecond |
| Time_to_sec (t) | Convert time t to seconds SELECT time_to_sec (' 1:12:00 ') |
| Sec_to_time (s) | Converts the time in seconds to the format of the last seconds SELECT Sec_to_time (4320) |
| To_days (d) | Calculate Date D for days from January 1, 00 SELECT to_days (' 0001-01-01 01:01:01 ') |
| From_days (N) | Calculates the date of n days from January 1, 00 SELECT from_days (1111) |
| DATEDIFF (D1,D2) | Calculate the number of days separated by date d1->d2 SELECT DATEDIFF (' 2001-01-01 ', ' 2001-02-02 ') |
| Adddate (D,n) | Calculate Date d plus n days of date |
| Adddate (D,interval expr type) | Calculate the start Date D plus the date after a time period SELECT adddate (' 2011-11-11 11:11:11 ', 1) SELECT adddate (' 2011-11-11 11:11:11 ', INTERVAL 5 MINUTE) |
| Date_add (D,interval expr type) | Ditto |
| Subdate (D,n) | Date d minus n days after day SELECT subdate (' 2011-11-11 11:11:11 ', 1) |
| Subdate (D,interval expr type) | Date d minus the date after one time period SELECT subdate (' 2011-11-11 11:11:11 ', INTERVAL 5 MINUTE) |
| Addtime (T,n) | Time t plus n seconds SELECT addtime (' 2011-11-11 11:11:11 ', 5) |
| Subtime (T,n) | Time t minus n seconds SELECT subtime (' 2011-11-11 11:11:11 ', 5) |
| Date_format (D,F) | Display Date d as required by expression F SELECT date_format (' 2011-11-11 11:11:11 ', '%y-%m-%d%r ') |
| Time_format (T,F) | Show time t as required by expression F SELECT Time_format (' 11:11:11 ', '%r ') |
| Get_format (Type,s) | Get time Format function for national area Select Get_format (date, ' USA ') |
1. IF (EXPR,V1,V2) function
If the expression expr is true, returns the result V1; otherwise, the result v2.
SELECT IF (1 > 0, ' correct ', ' Error '), correct
2, ifnull (V1,V2) function
If the value of V1 is not NULL, V1 is returned, otherwise v2 is returned.
SELECT ifnull (null, ' Hello word ')->hello Word
3. Case
Syntax 1:
When the case is E1 then v1 the E2 then E2 ... ELSE Vnend
The case represents the start of the function and end indicates the end of the function. If the E1 is established, then return to V1, if E2 is established, then return to V2, when all is not established return to VN, and when there is a set up, the latter will not be executed.
SELECT case to 1 > 0 Then ' 1 > 0 ' when 2 > 0 Then ' 2 > 0 ' ELSE ' 3 > 0 ' end->1 > 0
Syntax 2:
When the case expr is E1 then v1 the E1 then v1 ... ELSE Vnend
Returns v1 if the value of the expression expr equals e1, or E2 if it equals E2. Otherwise, the VN is returned.
Five, System Information functionSELECT Case 1 If 1 then ' I am 1 ' when 2 Then ' I am 2 ' else ' who are you '
System information functions are used to query the MySQL database for system information.
| Function | Role |
| VERSION () | Returns the version number of the database SELECT VERSION () |
| connection_id () | Returns the number of connections to the server |
| DATABASE (), SCHEMA | Returns the current database name |
USER (), System_user (), Session_user (), Current_User (), Current_User |
Returns the current user |
| CHARSET (str) | Returns the character set of the string str |
| COLLATION (str) | Returns the character arrangement of the string str |
| LAST_INSERT_ID () | Returns the most recently generated auto_increment value |
Cryptographic functions are functions that MySQL uses to encrypt data.
1, PASSWORD (str)
The function can encrypt the string str, in general, PASSWORD (str) is used to encrypt the user's password.
SELECT PASSWORD (' 123 ') ->*23ae809ddacaf96af0fd78ed04b6a265e05aa2572, MD5
The MD5 (str) function hashes the string str and can be used for some common data encryption that does not require decryption.
SELECT MD5 (' 123 ') ->202cb962ac59075b964b07152d234b703, ENCODE (STR,PSWD_STR) and decode (CRYPT_STR,PSWD_STR)
The Encode function can use the encrypted password Pswd_str to encrypt the string str, and the result of the encryption is a binary number that needs to be saved with a BLOB type of field. This function is a pair with decode and requires the same password to decrypt it.
SELECT ENCODE (' 123 ', ' Xxoo ') ->;vxselect DECODE ('; VX ', ' Xxoo ') ->1231. Formatted function format (x,n)
The format (x,n) function formats the number x and retains the x to the N-bit after the decimal point.
SELECT FORMAT (3.1415926,3) ->3.142
2, different binary numbers to convert
3, the function of the IP address and the number mutual conversion
SELECT Inet_aton (' 192.168.0.1 ') ->3232235521select inet_ntoa (3232235521) ->192.168.0.14. Lock function and unlock function
Select Get_lock (' MySQL ', ten) ->1 (lasts 10 seconds) Select Is_free_lock (' mysql ') ->1 Select Release_ LOCK (' MySQL ') ->15. Functions that perform the specified operation repeatedly
The BENCHMARK (count.expr) function repeats the expression expr to count this, and then returns the execution time. This function can be used to determine the speed of the MySQL processing expression.
SELECT BENCHMARK (10000,now ()) ->0 return to system time 10,000
6, changing the character set function
The CONVERT (S USING CS) function changes the character set of the string S to CS.
Select CHARSET (' abc ') ->utf-8 Select CHARSET (CONVERT (' abc ' USING GBK) ->GBK7. Convert data type
These two functions are only for binary, CHAR, DATE, DATETIME, Time, signed integer, UNSIGNED Integer.
Select CAST (' 123 ' as UNSIGNED INTEGER) + 1 ->124select ' 123 ' + 1 ->124 In fact MySQL can default to convert select CAST (now () as DA TE)
->2014-12-18Transferred from: http://www.cnblogs.com/kissdodog/p/4168721.html
MySQL Common functions
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
