Mysql and mysql download

Source: Internet
Author: User
Tags bit set first string mathematical functions mysql download natural logarithm

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.

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.