# MySQL function

Source: Internet
Author: User

Tags: time function alt Common Insert replace MMU span data encryption random number

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 XSELECT ABS ( -1) --Return 1 Ceil (x), CEILING (x) Returns the smallest integer greater than or equal to XSELECT ceil (1.5) --Return 2 Floor (x) Returns the largest integer less than or equal to XSELECT Floor (1.5) --Return 1 RAND () Returns the random number of 0->1SELECT RAND () --0.93099315644334 RAND (x) Returns the random number of 0->1 with the same number of random numbers returned at the same X valueSELECT 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 truncatedSELECT ROUND (1.23456,3) -- 1.235 POW (x, y). POWER (x, y) Returns the Y-order of XSELECT POW (2,3) --8 SQRT (x) Returns the square root of XSELECT SQRT ( 5 ) EXP (x) Returns the X-square of ESELECT EXP (3) --20.085536923188 MOD (x, y) Returns the remainder after x divided by ySELECT MOD (5,2) --1 LOG (x) Returns the natural logarithm (base e logarithm)SELECT LOG (20.085536923188)--3 LOG10 (x) Returns the base 10 logarithmSELECT LOG10 ( 2 ) RADIANS (x) Convert an angle to radiansSELECT RADIANS (180)--3.1415926535898 DEGREES (x) Convert radians to anglesSELECT 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 sSELECT char_length (' Hello 123 ')--5 LENGTH (s) Returns the length of the string sSELECT LENGTH (' Hello 123 ')--9 CONCAT (S1,s2,...) Combine strings s1,s2 and more into a single stringSELECT CONCAT (' A ', ')- -1234 Concat_ws (X,s1,s2,...) Tong Concat (S1,s2,...) function, but each string is added directly to the XSELECT concat_ws (' @ ', ' a ', ' "') '- -[email protected] INSERT (S1,X,LEN,S2) Replace string S2 with S1 x position starting at Len lengthSELECT INSERT (' 12345 ', 1,3, ' abc ')- -Abc45 UPPER (s), ucaase (s) Converts all letters of the string s to uppercaseSELECT UPPER (' abc ')- -ABC LOWER (s), LCASE (s) Turn all the letters of the string s into lowercase lettersSELECT LOWER (' abc ')--ABC Left (S,n) Returns the first n characters of a string sSELECT left (' ABCDE ', 2) --AB Right (S,n) Returns the second n characters of the string sSELECT right (' ABCDE ', 2)--DE Lpad (S1,LEN,S2) String S2 to fill the beginning of the S1, so that the string length reaches LenSELECT 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 LenSELECT 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 S1SELECT TRIM (' @ ' from ' @@[email protected]@ ') --ABC REPEAT (S,n) Repeats the string s n timesSELECT REPEAT (' AB ', 3) --Ababab SPACE (N) Returns n Spaces REPLACE (S,S1,S2) S2 the string to a string in an alternate string s S1SELECT 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 sSELECT LOCATE (' B ', ' abc ')--2 INSTR (S,S1) Gets the starting position of the S1 from the string sSELECT INSTR (' abc ', ' B ')--2 REVERSE (s) Reverse the order of the string sSELECT REVERSE (' abc ')- -CBA ELT (N,s1,s2,...) Returns the nth stringSELECT 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 sSELECT 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)--ASELECT substring_index (' a*b ', ' * ', -1)--BSELECT 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 dateSELECT Curdate ()->2014-12-17 Curtime (), Current_time Return Current timeSELECT Curtime ()->15:59:02 Now (), Current_timestamp (), localtime (),Sysdate (), Localtimestamp () Returns the current date and timeSELECT Now ()->2014-12-17 15:59:02 Unix_timestamp () Returns the current time as a Unix timestampSELECT Unix_timestamp ()->1418803177 Unix_timestamp (d) Returns time d as a Unix timestampSELECT unix_timestamp (' 2011-11-11 11:11:11 ')->1320981071 From_unixtime (d) Time to convert the time of the Unix timestamp to the normal formatSELECT From_unixtime (1320981071)->2011-11-11 11:11:11 Utc_date () Returns the UTC dateSELECT Utc_date ()->2014-12-17 Utc_time () return UTC timeSELECT Utc_time ()->08:01:45 (8 hours slow) MONTH (d) Returns the month value in Date D, 1->12SELECT MONTH (' 2011-11-11 11:11:11 ')->11 MONTHNAME (d) Returns the month name in the date, such as JanyarySELECT MONTHNAME (' 2011-11-11 11:11:11 ')->november Dayname (d) Return Date D is the day of the week, such as Monday,tuesdaySELECT dayname (' 2011-11-11 11:11:11 ')->friday DAYOFWEEK (d) Date d today is the day of the week, 1 weeks, 2 weeks aSELECT 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->53SELECT WEEK (' 2011-11-11 11:11:11 ')->45 DayOfYear (d) Calculated Date D is the day ordinal of this yearSELECT dayofyear (' 2011-11-11 11:11:11 ')->315 DayOfMonth (d) Calculated Date D is the day of the monthSELECT dayofmonth (' 2011-11-11 11:11:11 ')->11 QUARTER (d) Return Date D is the first season, return 1->4SELECT QUARTER (' 2011-11-11 11:11:11 ')->4 HOUR (t) Returns the hour value in TSELECT HOUR (' 1:2:3 ')->1 MINUTE (t) Returns the minute value in TSELECT MINUTE (' 1:2:3 ')->2 SECOND (t) Returns the seconds value in TSELECT SECOND (' 1:2:3 ')->3 EXTRACT (type from D) Gets the specified value from Date D, type specifies the value returnedSELECT EXTRACT (MINUTE from ' 2011-11-11 11:11:11 ')->11The value of type is:MicrosecondSECONDMINUTEHOURDayWEEKMONTHQUARTERYearSecond_microsecondMinute_microsecondMinute_secondHour_microsecondHour_secondHour_minuteDay_microsecondDay_secondDay_minuteDay_hourYear_month Time_to_sec (t) Convert time t to secondsSELECT time_to_sec (' 1:12:00 ')->4320 Sec_to_time (s) Converts the time in seconds to the format of the last secondsSELECT Sec_to_time (4320)->01:12:00 To_days (d) Calculate Date D for days from January 1, 00SELECT to_days (' 0001-01-01 01:01:01 ')->366 From_days (N) Calculates the date of n days from January 1, 00SELECT from_days (1111)->0003-01-16 DATEDIFF (D1,D2) Calculate the number of days separated by date d1->d2SELECT 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 periodSELECT 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 daySELECT 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 periodSELECT 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 secondsSELECT addtime (' 2011-11-11 11:11:11 ', 5)->2011-11-11 11:11:16 (sec) Subtime (T,n) Time t minus n secondsSELECT 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 FSELECT 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 FSELECT Time_format (' 11:11:11 ', '%r ')11:11:11 AM Get_format (Type,s) Get time Format function for national areaSelect 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 databaseSELECT 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.

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 function

Related Keywords:
Related Article