Mysql and mysql download

Mysql and mysql download

There are too many functions provided by Mysql, and many of them have been seen before. Don't use them. Someone in the garden made a comprehensive. MYSQL function.

I will copy all of the information he has written here, and insert some experiences he has used in his project.

I. mathematical functions

Mathematical functions, to be honest, I have never used them for the time being, that is, I have never used them when making statistics in the system. all the items that can be processed in the program are processed in the program. the database is mainly responsible for reading and writing data.

Mathematical functions are mainly used to process numbers, including integer and floating point numbers.

Function	Function
ABS (x)	Returns the absolute value of x. Select abs (-1) -- returns 1
CEIL (x) CEILING (x)	Returns the smallest integer greater than or equal to x. Select ceil (1.5) -- returns 2
FLOOR (x)	Returns the largest integer less than or equal to x. Select floor (1.5) -- returns 1
RAND ()	Returns a random number ranging from 0 to 1. Select rand () -- 0.93099315644334
RAND (x)	Returns a random number ranging from 0 to> 1, and returns the same random number with the same x value. Select rand (2) --- 1.5865798029924
SIGN (x)	Returns the symbols of x, which are negative, 0, and positive. Returns-1, 0, and 1 respectively. Select sign (-10) -- (-1)
PI ()	Returns the circumference rate (3.141593) Select pi () -- 3.141593
TRUNCATE (x, y)	Returns the value x that is retained to the y-digit after the decimal point (the biggest difference with ROUND is that it will not be rounded off) Select truncate (1.23456, 3) -- 1.234
ROUND (x)	Returns the nearest integer of x, select round (1.23456) -- 1.
ROUND (x, y)	The value of y after the x decimal point is retained. Select round (1.23456, 3) -- 1.235
POW (x, y) POWER (x, y)	Returns the Power y of x. Select pow (2, 3) -- 8
SQRT (x)	Returns the square root of x. Select sqrt (25) -- 5
EXP (x)	Returns the x power of e. Select exp (3) -- 20.085536923188
MOD (x, y)	Returns the remainder after dividing x by y. Select mod (5, 2) -- 1
LOG (x)	Returns the natural logarithm (base on e) Select log (20.085536923188) -- 3
LOG10 (x)	Returns the base-10 logarithm. SELECT LOG10 (100) -- 2
RADIANS (x)	Converts degrees to radians. Select radians (180) -- 3.1415926535898
DEGREES (x)	Converts radians to degrees. Select degrees (3.1415926535898) -- 180
SIN (x)	Returns the sine (the parameter is radian) Select sin (RADIANS (30) -- 0.5
ASIN (x)	Returns the arc sine (the parameter is radian)
COS (x)	Returns the cosine (the parameter is radian)
ACOS (x)	Returns the arc cosine (the parameter is radian)
TAN (x)	Returns the arc tangent value (the parameter is radian)
ATAN (x) ATAN2 (x)	Returns the arc tangent value (the parameter is radian)
COT (x)	Returns the cotangent value (the parameter is radian)

 

Ii. String Functions

Many string functions are used in the program. I use many functions and will mark them in red.

String functions are the most commonly used functions in MySQL. String functions are mainly used to process strings in tables.

Function	Description
CHAR_LENGTH (s)	Returns the number of characters in string s. SELECT CHAR_LENGTH ('Hello 123 ') -- 5
LENGTH (s)	Returns the length of string s. Select length ('Hello 123 ') -- 9
CONCAT (s1, s2 ,...)	Merges multiple strings, such as s1 and s2, into one string. Select concat ('12', '34') -- 1234
GROUP_CONCAT (id)	Select id, pid, GROUP_CONCAT (id) from ztree group by pid; After grouping, splice the selected columns and specify the concatenated string Group_contact (id SEPARATOR '-')
CONCAT_WS (x, s1, s2 ,...)	The same as the CONCAT (s1, s2,...) function, but the x SELECT CONCAT_WS ('@', '12', '34') -- 12 @ 34
INSERT (s1, x, len, s2)	Replace string s2 with the string whose start length is len at the x position of s1 Select insert ('2013', 1, 3, 'abc') -- abc45
UPPER (s), UCAASE (S)	Converts all letters of string s into uppercase letters. Select upper ('abc') -- abc
LOWER (s), LCASE (s)	Converts all letters of string s to lowercase letters. Select lower ('abc') -- ABC
LEFT (s, n)	Returns the first n characters of string s. Select left ('abcde', 2) -- AB
RIGHT (s, n)	Returns the last n characters of string s. Select right ('abcde', 2) -- de
LPAD (s1, len, s2)	String s2 to fill the start of 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 s1, so that the length of the string reaches len Select rpad ('abc', 5, 'xx') -- abcxx
LTRIM (s)	Removes spaces at the beginning of string s.
RTRIM (s)	Removes spaces at the end of string s.
TRIM (s)	Removes spaces at the beginning and end of string s.
TRIM (s1 FROM s)	Removes the start and end strings s1 from string s. Select trim ('@' from' @ abc @ ') -- abc
REPEAT (s, n)	Repeat string s n times Select repeat ('AB', 3) -- ababab
SPACE (n)	Returns n spaces.
REPLACE (s, s1, s2)	Replace string s2 with string s1 in string s. Select replace ('abc', 'A', 'x') -- xbc
STRCMP (s1, s2)	Comparing strings s1 and s2
SUBSTRING (s, n, len)	Returns the string with the length of len starting from the nth position in string s.
MID (s, n, len)	Same as SUBSTRING (s, n, len)
LOCATE (s1, s) POSITION (s1 IN s)	Obtain the start position of s1 from string s. Select locate ('B', 'abc') -- 2
INSTR (s, s1)	Obtain the start position of s1 from string s. Select instr ('abc', 'B') -- 2
REVERSE (s)	Returns the order of 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. Here, for each bit set in "bits", you get a "on" string, and for each reset (reset) bit, You get a "off" string. Each string is separated by "separator" (default ","), and only the "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 position of the first string matching string s. Select field ('C', 'A', 'B', 'C') -- 3
FIND_IN_SET (s1, s2)	Returns the position of the string matching s1 in string s2. For example, in the 11,12, 13, the values 1 and 11 cannot be found, and the values 11 can be found. FIND_IN_SET ('11', '11, 12,13 ')-> 1
MAKE_SET (x, s1, s2)	Returns a set (including"," A string consisting of substrings separated by characters.bitsThe string in the set.str1Corresponding to bit 0,str21, and so on. SELECT MAKE_SET (1 | 4, 'A', 'B', 'C'); -- a, c
SUBSTRING_INDEX	Returns the substring after the delimiter delim that appears from the count of the str string. If count is a positive number, the string to the left of the count character is returned. If count is a negative number, return the string to the right of the character (the absolute value of count (number from the right. SELECT SUBSTRING_INDEX ('a * B ',' * ', 1) -- SELECT SUBSTRING_INDEX ('a * B ',' * ',-1) -- B SELECT SUBSTRING_INDEX ('a * B * c * d * E', '*', 3), '*',-1) -- c
LOAD_FILE (file_name)	Read the file and return the file content as a string. The file must be on the server. You must specify the full path name of the file, You must have file permission. All content of the file must be readable and smaller than max_allowed_packet. If the file does not exist or cannot be read due to one of the above reasons, the function returns NULL.

 

Iii. Date and Time Functions

MySQL date and time functions are mainly used to process date and time.

Function	Description
CURDATE () CURRENT_DATE ()	Returns the current date. Select curdate () ->
CURTIME () CURRENT_TIME	Returns the current time. Select curtime () -> 15:59:02
NOW () CURRENT_TIMESTAMP () LOCALTIME () SYSDATE () LOCALTIMESTAMP ()	Returns the current date and time. Select now () -> 15:59:02
UNIX_TIMESTAMP ()	Returns the current time as a UNIX timestamp. SELECT UNIX_TIMESTAMP () -> 1418803177
UNIX_TIMESTAMP (d)	Returns Time d in UNIX timestamp format. SELECT UNIX_TIMESTAMP ('2017-11-11 11:11:11 ') -> 1320981071
FROM_UNIXTIME (d)	Converts the UNIX timestamp time to a normal format. SELECT FROM_UNIXTIME (1320981071) -> 11:11:11, 2011-11-11
UTC_DATE ()	Returns the UTC date. SELECT UTC_DATE () ->
UTC_TIME ()	Returns UTC time. SELECT UTC_TIME () -> 08:01:45 (8 hours slow)
MONTH (d)	Returns the month value in date d, 1-> 12. Select month ('2017-11-11 11:11:11 ') -> 11
MONTHNAME (d)	Returns the name of a month in a date, such as Janyary. Select monthname ('2017-11-11 11:11:11 ') -> November
DAYNAME (d)	Returns the day of a week, such as Monday and Tuesday. Select dayname ('2017-11-11 11:11:11 ') -> Friday
DAYOFWEEK (d)	Date d: Today is the day of the week, 1 Sunday, 2 Monday Select dayofweek ('2017-11-11 11:11:11 ') -> 6
WEEKDAY (d)	Date d today is the day of the week, 0 indicates Monday, 1 indicates Tuesday
WEEK (d) WEEKOFYEAR (d)	The calculation date d is the week of the current year. The value range is 0-> 53. Select week ('2017-11-11 11:11:11 ') -> 45
DAYOFYEAR (d)	Calculation date d is the day of the year Select dayofyear ('2017-11-11 11:11:11 ') -> 315
DAYOFMONTH (d)	Calculation date d is the day of the month Select dayofmonth ('2017-11-11 11:11:11 ') -> 11
QUARTER (d)	Returns the season of date d. Returns 1-> 4. Select quarter ('2017-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 second value in t. Select second ('1: 2: 3 ') -> 3
EXTRACT (type FROM d)	Gets the specified value from date d, and type specifies the returned value. Select extract (minute from '2017-11-11 11:11:11 ') -> 11 Type can be set: 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 second SELECT TIME_TO_SEC ('1: 12: 00 ') -> 4320
SEC_TO_TIME (s)	Converts time s in seconds to the format of hour, minute, and second. SELECT SEC_TO_TIME (4320) -> 01:12:00
TO_DAYS (d)	Calculate the number of days on the date d from January 1, January 1, 0000. SELECT TO_DAYS ('2017-01-01 01:01:01 ') -> 366
FROM_DAYS (n)	Calculate the date n days from January 1, January 1, 0000 SELECT FROM_DAYS (1111) -> 0003-01-16
DATEDIFF (d1, d2)	Calculate the number of days between d1-> d2 Select datediff ('2017-01-01 ', '2017-02-02 ') ->-32
ADDDATE (d, n)	Calculate the date plus the date of n days.
ADDDATE (d, INTERVAL expr type)	Calculate start date d plus date after a time period Select adddate ('2017-11-11 11:11:11 ', 1) -> 11:11:11 (the default value is Day) Select adddate ('2017-11-11 11:11:11 ', INTERVAL 5 MINUTE) -> 11:16:11 (the TYPE value is similar to the function listed above)
DATE_ADD (d, INTERVAL expr type)	Same as above
SUBDATE (d, n)	Date d minus the date after n days Select subdate ('2017-11-11 11:11:11 ', 1) -> 11:11:11 (day by default)
SUBDATE (d, INTERVAL expr type)	Date d minus the date after a time period Select subdate ('2017-11-11 11:11:11 ', INTERVAL 5 MINUTE) -> 11:06:11 (the TYPE value is similar to the function listed above)
ADDTIME (t, n)	Time t plus n seconds Select addtime ('2017-11-11 11:11:11 ', 5) -> 11:11:16 (seconds)
SUBTIME (t, n)	Time t minus n seconds Select subtime ('2017-11-11 11:11:11 ', 5) -> 11:11:06 (seconds)
DATE_FORMAT (d, f)	Display date d according to expression f SELECT DATE_FORMAT ('2017-11-11 11:11:11 ',' % Y-% m-% d % R ') -> 2011-11-11 11:11:11 AM
TIME_FORMAT (t, f)	Display time t according to expression f SELECT TIME_FORMAT ('11: 11: 11', '% R ') 11:11:11 AM
GET_FORMAT (type, s)	Obtain the time format function of a country or region Select get_format (date, 'USA ') -> % M. % d. % Y (note that this strange string (format String) is returned ))

 

Iv. Conditional judgment Functions

　1. IF (expr, v1, v2) Function

If the expression expr is true, return result v1; otherwise, return result v2.

Select if (1> 0, 'true', 'error')-> correct

　　2. IFNULL (v1, v2) Function

If the v1 value is not NULL, v1 is returned; otherwise, v2 is returned.

Here I usually use ifnull (null, 0) or ifnull (null ,'')

SELECT IFNULL(null,'Hello Word')->Hello Word

　　3. CASE

　　Syntax 1:

CASE 　　WHEN e1　　THEN v1　　WHEN e2　　THEN e2　　...　　ELSE vnEND

CASE indicates the start of the function, and END indicates the END of the function. If e1 is true, v1 is returned. If e2 is true, v2 is returned. If none is true, vn is returned. If one is true, the subsequent operations are not executed.

SELECT CASE 　　WHEN 1 > 0 THEN '1 > 0'　　WHEN 2 > 0 THEN '2 > 0'　　ELSE '3 > 0'　　END->1 > 0

　　Syntax 2:

CASE expr 　　WHEN e1 THEN v1　　WHEN e1 THEN v1　　...　　ELSE vnEND

If the expression expr value is equal to e1, v1 is returned; if it is equal to e2, e2 is returned. Otherwise, return vn.

Select case 1 WHEN 1 THEN 'I'm 1' WHEN 2 THEN' I'm 2' ELSE 'Who are you'

This case is often used for Row-to-column conversion, for example, converting a score into a two-dimensional table.

 

V. system information functions

The system information function is used to query the system information of the MySQL database.

Function	Function
VERSION ()	Returns the database version number. Select version () -> 5.0.67-community-nt
CONNECTION_ID ()	Number of returned server connections
DATABASE () SCHEMA ()	Returns the name of the current database.
USER () SYSTEM_USER () SESSION_USER () CURRENT_USER () CURRENT_USER	Returns the current user
CHARSET (str)	Returns the str character set.
COLLATION (str)	Returns the string 'str' character arrangement.
LAST_INSERT_ID ()	Returns the most recently generated AUTO_INCREMENT value.

 

Vi. encryption functions

The encryption function is used by MySQL to encrypt data.

　　1. PASSWORD (str)

This function can encrypt the str string. Generally, PASSWORD (str) is used to encrypt the user's PASSWORD.

SELECT PASSWORD('123')    ->*23AE809DDACAF96AF0FD78ED04B6A265E05AA257

　　2. MD5

The MD5 (str) function can hash the string 'str' and encrypt common data that does not need to be decrypted.

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'. The encrypted result is a binary number and needs to be saved using BLOB fields. This function is a pair of DECODE and requires the same password for decryption.

SELECT ENCODE('123','xxoo')    ->;vxSELECT DECODE(';vx','xxoo')    ->123

 

VII. Other functions

　　1. FORMAT (x, n)

The FORMAT (x, n) function can FORMAT the number x and retain x to n digits after the decimal point.

SELECT FORMAT(3.1415926,3)    ->3.142

　　2. convert numbers in different hexadecimal formats

• ASCII (s) returns the ASCII code of the first character of string s;
• BIN (x) returns the binary code of x;
• HEX (x) returns the hexadecimal code of x;
• OCT (x) returns the octal code of x;
• CONV (x, f1, f2) returns the f1 hexadecimal number to the f2 hexadecimal number;

　　3. Functions for converting IP addresses and numbers

• The INET_ATON (IP) function can convert an IP address to a number. The IP value must be enclosed by quotation marks;
• The INET_NTOA (n) function can convert the number n to an IP address.

SELECT INET_ATON('192.168.0.1')    ->3232235521SELECT INET_NTOA(3232235521)    ->192.168.0.1

　　4. Locking and unlocking Functions

• The GET_LOCK (name, time) function defines a lock named nam with a duration of time seconds. If the lock succeeds, 1 is returned. If the attempt times out, 0 is returned. If an error occurs, NULL is returned.
• The RELEASE_LOCK (name) function removes the lock named name. If the unlock succeeds, 1 is returned. If the attempt times out, 0 is returned. If the unlock fails, NULL is returned;
• The IS_FREE_LOCK (name) function checks whether a lock named name has been used. If yes, 0 is returned. Otherwise, 1 is returned;

SELECT GET_LOCK ('mysql', 10)-> 1 (lasting 10 seconds) SELECT IS_FREE_LOCK ('mysql')-> 1 SELECT RELEASE_LOCK ('mysql')-> 1

　　5. Repeat the function of the specified operation

The BENCHMARK (count. expr) function repeats count in the expression expr, and returns the execution time. This function can be used to determine the speed at which MySQL processes expressions.

Select benchmark (10000, NOW ()-> 0 return system time 10 thousand

　　6. Functions for changing character sets

The CONVERT (s USING cs) function converts the character set of string s to cs.

SELECT CHARSET('ABC')    ->utf-8    SELECT CHARSET(CONVERT('ABC' USING gbk))    ->gbk

　　7. convert data types

• CAST (x AS type)
• CONVERT (x, type)

Note that the cast ('1' as int) type is incorrect.

These two functions only apply to BINARY, CHAR, DATE, DATETIME, TIME, signed integer, and unsigned integer.

Select cast ('200' as unsigned integer) + 1-> 123 SELECT '200' + 1-> 124 in fact, MySQL can convert select cast (NOW () as date) by default)
->

 

Finally, I would like to thank this mysql function again. If my copyright is infringed in this article, please leave a message for me. I will reclaim this blog and leave it for my reference.