The MySQL database provides a number of functions including:
- Mathematical functions;
- String functions;
- Date and time functions;
- conditional judgment function;
- System Information function;
- cryptographic functions;
- Format function;
First, mathematical functions
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) |
Second, String function
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 bits collection. str1 corresponds to bit 0, str2 corresponds to bit 1, and so on. 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 SELECT substring_index (' a*b ', ' * ', -1)--B SELECT Substring_index (Substring_index (' a*b*c*d*e ', ' * ', 3), ' * ', -1)--C |
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. |
Three, date Time function
MySQL's date and time functions are primarily used to process DateTime.
Function |
Description |
Curdate (), Current_date () |
Returns the current date SELECT Curdate () ->2014-12-17 |
Curtime (), Current_time |
Return Current time SELECT Curtime () ->15:59:02 |
Now (), Current_timestamp (), localtime (), Sysdate (), Localtimestamp () |
Returns the current date and time SELECT Now () ->2014-12-17 15:59:02 |
Unix_timestamp () |
Returns the current time as a Unix timestamp SELECT Unix_timestamp () ->1418803177 |
Unix_timestamp (d) |
Returns time d as a Unix timestamp SELECT unix_timestamp (' 2011-11-11 11:11:11 ') ->1320981071 |
From_unixtime (d) |
Time to convert the time of the Unix timestamp to the normal format SELECT From_unixtime (1320981071) ->2011-11-11 11:11:11 |
Utc_date () |
Returns the UTC date SELECT Utc_date () ->2014-12-17 |
Utc_time () |
return UTC time SELECT Utc_time () ->08:01:45 (8 hours slow) |
MONTH (d) |
Returns the month value in Date D, 1->12 SELECT MONTH (' 2011-11-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 ') ->november |
Dayname (d) |
Return Date D is the day of the week, such as Monday,tuesday SELECT dayname (' 2011-11-11 11:11:11 ') ->friday |
DAYOFWEEK (d) |
Date d today is the day of the week, 1 weeks, 2 weeks a SELECT DAYOFWEEK (' 2011-11-11 11:11:11 ') ->6 |
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 ') ->45 |
DayOfYear (d) |
Calculated Date D is the day ordinal of this year SELECT dayofyear (' 2011-11-11 11:11:11 ') ->315 |
DayOfMonth (d) |
Calculated Date D is the day of the month SELECT dayofmonth (' 2011-11-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 ') ->4 |
HOUR (t) |
Returns the hour value in T SELECT HOUR (' 1:2:3 ') ->1 |
MINUTE (t) |
Returns the minute value in T SELECT MINUTE (' 1:2:3 ') ->2 |
SECOND (t) |
Returns the seconds value in T SELECT SECOND (' 1:2:3 ') ->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 ') ->11 The value of type is: 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 |
Time_to_sec (t) |
Convert time t to seconds SELECT time_to_sec (' 1:12:00 ') ->4320 |
Sec_to_time (s) |
Converts the time in seconds to the format of the last seconds SELECT Sec_to_time (4320) ->01:12:00 |
To_days (d) |
Calculate Date D for days from January 1, 00 SELECT to_days (' 0001-01-01 01:01:01 ') ->366 |
From_days (N) |
Calculates the date of n days from January 1, 00 SELECT from_days (1111) ->0003-01-16 |
DATEDIFF (D1,D2) |
Calculate the number of days separated by date d1->d2 SELECT DATEDIFF (' 2001-01-01 ', ' 2001-02-02 ') ->-32 |
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) ->2011-11-12 11:11:11 (default is Day) SELECT adddate (' 2011-11-11 11:11:11 ', INTERVAL 5 MINUTE) ->2011-11-11 11:16:11 (the value of type is similar to the one listed above) |
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) ->2011-11-10 11:11:11 (default is Day) |
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) ->2011-11-11 11:06:11 (the value of type is similar to the one listed above) |
Addtime (T,n) |
Time t plus n seconds SELECT addtime (' 2011-11-11 11:11:11 ', 5) ->2011-11-11 11:11:16 (sec) |
Subtime (T,n) |
Time t minus n seconds SELECT subtime (' 2011-11-11 11:11:11 ', 5) ->2011-11-11 11:11:06 (sec) |
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 ') ->2011-11-11 11:11:11 AM |
Time_format (T,F) |
Show time t as required by expression F SELECT Time_format (' 11:11:11 ', '%r ') 11:11:11 AM |
Get_format (Type,s) |
Get time Format function for national area Select Get_format (date, ' USA ') ->%m.%d.%y (Note that this strange string is returned (format string)) |
Four, conditional judgment function
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.
SELECT Case 1 If 1 then ' I am 1 ' when 2 Then ' I am 2 ' else ' who are you '
Five, System Information function
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 () ->5.0.67-community-nt |
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 |
Six, encryption function
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 ') ->*23ae809ddacaf96af0fd78ed04b6a265e05aa257
2, 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 ') ->202cb962ac59075b964b07152d234b70
3, 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 ') ->123
Vii. other functions
1. 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
- ASCII (s) returns the ASCII code of the first character of the string s;
- BIN (x) returns the binary encoding of x;
- Hex (x) returns the hexadecimal encoding of x;
- The OCT (x) returns the octal encoding of X;
- CONV (X,F1,F2) returns the F1 binary number into the F2 number of binary;
3, the function of the IP address and the number mutual conversion
- The Inet_aton (IP) function can convert an IP address to a digital representation, and the IP value needs to be quoted;
- The Inet_ntoa (n) function converts the number n to the IP form.
SELECT Inet_aton (' 192.168.0.1 ') ->3232235521select inet_ntoa (3232235521) ->192.168.0.1
4. Lock function and unlock function
- The Get_lock (name,time) function defines a lock named Nam with a duration of time seconds. Returns 1 if the lock succeeds, 0 if an attempt times out, or null if an error is encountered.
- The Release_lock (name) function unlocks a lock named name. Returns 1 if the unlock succeeds, or 0 if the attempt times out, and returns null if the unlock fails;
- The Is_free_lock (name) function determines whether a lock named name has been used. If used, returns 0, otherwise, returns 1;
Select Get_lock (' MySQL ', ten) ->1 (lasts 10 seconds) Select Is_free_lock (' mysql ') ->1 Select Release_ LOCK (' MySQL ') ->1
5. 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) ->GBK
7. Convert data type
- CAST (x as type)
- CONVERT (X,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-18
MySQL Basic functions