Shulou(Shulou.com)06/01 Report--

Function representation returns a value with a specific relationship to the value of the input parameter MySQL provides a large number of rich functions that will be often used in database management and data query and operation. Through the data processing database function can become more powerful and more flexible to meet different needs. All kinds of functions are mainly divided into the following types of mathematical functions, string functions, date and time functions, conditional judgment functions, system information functions and encryption functions.

5.1. Mathematical function

Absolute value function ABS (x)

Mysql > SELECT ABS (2), ABS (- 3.3), ABS (- 33) +-+ | ABS (2) | ABS (- 3.3) | ABS (- 33) | +-+ | 2 | 3.3 | 33 | +-+- +-+ 1 row in set (0.00 sec)

Returns the pi function PI ()

Mysql > SELECT PI (); +-+ | PI () | +-+ | 3.141593 | +-+ 1 row in set (3.141593 sec)

Square root function SQRT (x)

Mysql > SELECT SQRT (9), SQRT (30), SQRT (- 30) +-+ | SQRT (9) | SQRT (30) | SQRT (- 30) | +-+ | 3 | 5.477225575051661 | NULL | +- -+ 1 row in set (0.00 sec)

The remainder function MOD (XBI y)

Mysql > SELECT MOD (31), MOD (234), MOD (45.5) +-+ | MOD (31) | MOD (234) | MOD (45.5) | + + | 7 | 2 | 3.5 | + -+ 1 row in set (0.00 sec)

Get integer functions CEIL (x), CEILING (x), FLOOR (x)

Mysql > SELECT CEIL (3.35), CEILING (3.35) # return the smallest integer not less than x +-+ | CEIL (- 3.35) | CEILING (3.35) | +-3 | 4 | +-+ -+ 1 row in set (0.00 sec) mysql > SELECT FLOOR (- 3.35) FLOOR (3.35) # return the maximum integer not greater than x +-+-+ | FLOOR (- 3.35) | FLOOR (3.35) | +-+-+ |-4 | 3 | +-- -+ 1 row in set (0.00 sec)

Get random number functions RAND (), RAND (x)

Mysql > SELECT RAND (), RAND (), RAND () # return the random number +-+ between 0-1 | RAND () | +- -+ | 0.8727586752481373 | 0.6464434700519252 | 0.6139413552488585 | +- -+ 1 row in set (0.00 sec) mysql > SELECT RAND (10) RAND (10), RAND (11) # x is used as a seed value to generate repetitive sequences +-+ | RAND (10) | RAND (10) | RAND (11) | +- -+ | 0.6570515219653505 | 0.6570515219653505 | 0.907234631392392 | +- -+ 1 row in set (0.00 sec)

The functions ROUND (x), ROUND (xpene y) and TRUNCATE (xperiary y)

Mysql > SELECT ROUND (- 1.14,1.67), ROUND (1.15) # return the integer closest to x to round the x value +-+ | ROUND (- 1.14) | ROUND (1.67) | ROUND (1.15) | +- -+ |-1 | 2 | 1 | +-1 row in set (0.00 sec) mysql > SELECT ROUND (- 1.14) ROUND (1.67), ROUND (1.15) +-+ | ROUND (- 1.14) | ROUND (1.67) | ROUND (1.15) -1) | +-+ |-1.1 | 2 | 0 | +- -+ 1 row in set (0.00 sec) # returns the integer value closest to x. If y is a negative decimal point, it will retain mysql > SELECT TRUNCATE (1.31) to the left of the decimal point TRUNCATE (1.99), TRUNCATE (1.99) +-+ | TRUNCATE (1.31) | TRUNCATE (1.99) | TRUNCATE (1.99) | +-- -+ | 1.31.9 | 1 | +-+ 1 row in set (0.00 sec) # returns the number x of y places rounded to the decimal point

Symbolic function SIGN (x)

Mysql > SELECT SIGN (- 21), SIGN (0), SIGN (21) +-+ | SIGN (- 21) | SIGN (0) | SIGN (21) | +-+ |-1 | 0 | 1 | +- -+-+ 1 row in set (0.00 sec)

The exponentiation functions POW (x ~ y), POWER (x ~ ~ y) and EXP (x)

Mysql > SELECT POW (2), POWER (2), POW (2), POWER (2) # return x to the power of y +-+ | POW (2jue 2) | POWER (2jue 2) | POW (2Lindi 2) | POWER (2) -2) | +-+ | 4 | 4 | 0.25 | 0.25 | +- -+ 1 row in set (0.04 sec) mysql > SELECT EXP (3) EXP (- 3), EXP (0) # return the x power with e as the base +-+ | EXP (3) | EXP (- 3) | EXP (0) | + -+-+ | 20.085536923187668 | 0.049787068367863944 | 1 | +-+ 1 row in set (0.02 sec)

Logarithmic operation functions LOG (x) and LOG10 (x)

Mysql > SELECT LOG (3), LOG (- 3) # return the natural logarithm of x relative to the logarithm of radix e +-+-+ | LOG (3) | LOG (- 3) | +-+ | 1.0986122886681098 | NULL | +- -+-+ 1 row in set (0.00 sec) mysql > SELECT LOG10 (2) LOG10 (100), LOG10 (- 100) # returns the logarithm +-+ of x relative to radix 10 | LOG10 (2) | LOG10 (100) | LOG10 (- 100) | +-- -+ | 0.3010299956639812 | 2 | NULL | +-+ 1 row in set (0.00 sec)

Functions RADIANS (x) and DEGREES (x) for conversion between Angle and Radian

Mysql > SELECT RADIANS (90), RADIANS # convert angle to radians +-+-+ | RADIANS (90) | RADIANS (180) | +-+ | 1.5707963267948966 | 3.141592653589793 | +- -+-+ 1 row in set (0.00 sec) mysql > SELECT DEGREES (PI ()) DEGREES (PI () / 2) # Radian is converted to angle +-+-+ | DEGREES (PI ()) | DEGREES (PI () / 2) | +-+-+ | 180 | 90 | +- -+-+ 1 row in set (0.00 sec)

Sine function SIN (x) and Arcsine function ASIN (x)

Mysql > SELECT SIN (1), ROUND (SIN (PI () +-+ | SIN (1) | ROUND (SIN (PI () | +-+ | 0.8414709848078965 | 0 | +- -+-+ 1 row in set (0.00 sec) mysql > SELECT ASIN (3) ASIN (0.84) +-+ | ASIN (3) | ASIN (0.84) | +-+-+ | NULL | 0.9972832223717998 | +-+-+ 1 row in set (0.00 sec)

CoSine function COS (x) and inverse cosine function ACOS (x)

Mysql > SELECT COS (1), COS (PI ()), COS (0) +-+ | COS (1) | COS (PI ()) | COS (0) | +-+ | 0.5403023058681398 |-1 | 1 | +-+ 1 row in set (0.00 sec) mysql > SELECT ACOS (1) ACOS (0.54), ACOS (0) +-+ | ACOS (1) | ACOS (0.54) | ACOS (0) | +- -+ | 0 | 1.0003592173949747 | 1.5707963267948966 | +-+ 1 row in set (0.00 sec)

Tangent function TAN (X), arc tangent function ATAN (x) and cotangent function COT (x)

Mysql > SELECT TAN (0.3), TAN (PI () / 4) +-+ | TAN (0.3) | TAN (PI () / 4) | +-+ | 0.30933624960962325 | 0.99999999999999 | | +-+-+ 1 row in set (0.00 sec) mysql > SELECT ATAN (1) | ATAN (0.393) +-+ | ATAN (1) | ATAN (0.393) | +-+ | 0.7853981633974483 | 0.37445736689641174 | +- -+-+ 1 row in set (0.00 sec) mysql > SELECT COT 1/TAN (0.3) +-+-+ | COT (0.3) | 1/TAN (0.3) | +-+ | 3.2327281437658275 | 3.2327281437658275 | +- -+-+ 1 row in set (0.00 sec)

5.2. String function

Calculate the number of characters in a string CHAR_LENGTH (str)

Mysql > SELECT CHAR_LENGTH ('DATE'), CHAR_LENGTH (' egg') +-+ | CHAR_LENGTH ('DATE') | CHAR_LENGTH (' egg') | +-+-+ | 4 | 3 | +-+-+ 1 row in set (0.00 sec)

Calculate the string byte length LENGTH (str)

Mysql > SELECT LENGTH ('DATE'), LENGTH (' egg') +-+ | LENGTH ('DATE') | LENGTH (' egg') | +-+-+ | 4 | 3 | +-+- -+ 1 row in set (0.00 sec)

Merge string functions CONCAT (s _ 1 ~ 2 ~ (...), CONCAT_WS (x ~ ~ 1 ~ 1 ~ 2,)

Mysql > SELECT CONCAT ('My SQL','5.6'), CONCAT ('My SQL','5.6',NULL) +-+-+ | CONCAT ('My SQL','5.6') | CONCAT ('My SQL','5.6') NULL) | +-+-- + | My SQL5.6 | NULL | + -+ 1 row in set (0.00 sec) # when connecting multiple strings with NULL, the return value is NULLmysql > SELECT CONCAT_WS ('-') 'My SQL','5.6'), CONCAT_WS (' -','My SQL','5.6',NULL) +-+-+ | CONCAT_WS ('-','My SQL','5.6') | CONCAT_WS ('-','My SQL','5.6') NULL) | +-+-+ | My SQL-5.6 | My SQL-5.6 | +- -+-- + 1 row in set (0.00 sec) # ignore NULL when connecting multiple strings with x as the connector when NULL occurs

Replace the string function INSERT (s _ 1 ~ (1) ~ (x) ~ (2))

Mysql > SELECT INSERT ('Quest',2,4,'What') AS col1,-> INSERT (' Quest',-1,4,'What') AS col2,-> INSERT ('Quest',3,100,'What') AS col3 +-+ | col1 | col2 | col3 | +-+ | QWhat | Quest | QuWhat | +-+ 1 row in set (0.00 sec)

Letter case conversion functions LOWER (str), UPPER (str)

Mysql > SELECT LOWER ('BUFF'), LCASE (' Well') +-+ | LOWER ('BUFF') | LCASE (' Well') | +-+-+ | buff | well | +-+- -- + 1 row in set (0.00 sec) mysql > SELECT UPPER ('buff') UCASE ('Well') +-+-+ | UPPER ('buff') | UCASE (' Well') | +-+-+ | BUFF | WELL | +-+-+ 1 row in set (0.00 sec)

Gets the string functions of specified length LEFT (SMagne n) and RIGHT (sMagne n)

Mysql > SELECT LEFT ('FOOTBALL',5); +-+ | LEFT (' FOOTBALL',5) | +-+ | FOOTB | +-+ 1 row in set (0.00 sec) mysql > SELECT RIGHT ('FOOTBALL',5) +-+ | RIGHT ('FOOTBALL',5) | +-+ | TBALL | +-+ 1 row in set (0.00 sec)

The string-filling functions LPAD (S1 rep lenje S2) and RPAD (s1rec lenje S2)

Mysql > SELECT LPAD ('HELLO',4,'??'), LPAD (' HELLO',10,'??') +-+ | LPAD ('HELLO',4,'??') | LPAD (' HELLO',10) '?) | +-- +-- + | HELL |? HELLO | +-- +-- -+ 1 row in set (0.00 sec) mysql > SELECT RPAD ('HELLO' 4), RPAD ('HELLO',10,'??') +-+ | RPAD ('HELLO',4,'??') | RPAD (' HELLO',10) '?) | +-- +-+ | HELL | HELLO? | +-- +-+ 1 row in set (0.00 sec)

The functions LTRIM (s), RTRIM (s), and TRIM (s) that delete spaces

Mysql > SELECT'(BOOK)', CONCAT ('(', LTRIM ('BOOK'),') +-+-+ | (BOOK) | CONCAT ('(', LTRIM ('BOOK')) ')') | +-+-+ | (BOOK) | (BOOK) | +-+-- + 1 row in set (0.00 sec) mysql > SELECT'(BOOK)' CONCAT ('(', RTRIM ('BOOK'),')) +-+-+ | (BOOK) | CONCAT ('(', RTRIM ('BOOK')) ')') | +-+-+ | (BOOK) | (BOOK) | +-+-- + 1 row in set (0.00 sec) mysql > SELECT'(BOOK)' CONCAT ('(', TRIM ('BOOK'),')) +-+-+ | (BOOK) | CONCAT ('(', TRIM ('BOOK')) ')') | +-+-+ | (BOOK) | (BOOK) | +-+-- + 1 row in set (0.00 sec)

Deletes the function TRIM (S1 FROM s) of the specified string

Mysql > SELECT TRIM ('XY' FROM' ASHDLADHXYQWEPIXY') +-+ | TRIM ('XY' FROM' ASHDLADHXYQWEPIXY') | +-+ | ASHDLADHXYQWEPI | +- -+ 1 row in set (0.00 sec)

The function REPEAT that repeatedly generates the string (sline n)

Mysql > SELECT REPEAT ('MYSQL',5); +-- + | REPEAT (' MYSQL',5) | +-+ | MYSQLMYSQLMYSQLMYSQLMYSQL | +-+ 1 row in set (0.00 sec)

Space function SPACE (n)

Mysql > SELECT CONCAT ('(', SPACE (3),')) +-- + | CONCAT ('(', SPACE (3),')') | +-+ | () | +-+ 1 row in set (0.00 sec)

Substitution function REPLACE (s _ journal s _ 1 ~ s _ 2)

Mysql > SELECT REPLACE ('XXX.MYSQL.COM','X','W') +-- + | REPLACE ('XXX.MYSQL.COM','X' 'W') | +-+ | WWW.MYSQL.COM | +-+ 1 row in set (0.00 sec)

Function STRCMP to compare string sizes (s1mems2)

Mysql > SELECT STRCMP ('TXT','TXT2'), STRCMP (' TXT2','TXT'), STRCMP ('TXT','TXT') +-- + | STRCMP ('TXT','TXT2') | STRCMP (' TXT2','TXT') | STRCMP ('TXT') 'TXT') | +-- +-- +-+ |-1 | 1 | 0 | +- -+ 1 row in set (0.00 sec)

The functions to get the string, SUBSTRING (sdepartment njinLen) and MID (srech njorlen).

Mysql > SELECT SUBSTRING ('BREAKFAST',5) AS col1, string from position 5 to end-> SUBSTRING (' BREAKFAST',5,3) AS col2, substring of length 3 from position 5-> SUBSTRING ('lunch',-3) AS col3, position 3 from end to end of string-> SUBSTRING (' lunch',-5,3) AS col4 Self-string +-+ | col1 | col2 | col3 | col4 | +-+ | KFAST | KFA | nch | lun | +-from the end of the 5th position +-+ 1 row in set (0.00 sec)

Functions that match the starting position of the substring LOCATE (S1 IN s), POSITION (S1 IN s), and INSTR (sjournal S1)

Mysql > SELECT LOCATE ('BALL','FOOTBALL'), POSITION (' BALL' IN 'FOOTBALL'), INSTR (' FOOTBALL','BALL') +-+ | LOCATE ('BALL','FOOTBALL') | POSITION (' BALL' IN 'FOOTBALL') | INSTR (' FOOTBALL') 'BALL') | +-+ | 5 | 5 | 5 | +-- + 1 row in set (0.00 sec)

String reverse function REVERSE (s)

Mysql > SELECT REVERSE ('ABCD'); +-+ | REVERSE (' ABCD') | +-+ | DCBA | +-+ 1 row in set (0.00 sec)

Returns the string function ELT at the specified location (nmeme S1, S2...)

Mysql > SELECT ELT (3THZ), ELT (3THZ) +-+-+ | ELT (3pr 1THZ, 2THM, 3THN) | ELT (3MIT. Net). 'OS') | +-+-+ | 3TH | NULL | +-+-+ 1 row in set (0.00 sec)

Returns the function FIELD of the specified string position (s _ 1 ~ 2 _ 2.)

Mysql > SELECT FIELD ('HI','HIHI','HEY','HI','BAS') AS clo1,-> FIELD (' HI','HEY','LO') AS clo2;+-+-+ | clo1 | clo2 | +-+-+ | 3 | 0 | +-+-+ 1 row in set (0.00 sec)

The function FIND_IN_SET that returns the position of the substring (s _ 1 ~ 2)

Mysql > SELECT FIND_IN_SET ('hi','hihi,hey,hi,bas') +-- + | FIND_IN_SET ('hi','hihi,hey,hi Bas') | +-+ | 3 | +-+ 1 row in set (0.00 sec)

Select the function MAKE_SET of the string (xJournal s 1pm s 2p..)

Mysql > SELECT MAKE_SET,-> as col3,-> MAKE_SET. +-+ | col1 | col2 | col3 | col4 | +-+ | A | hello World | hello | | +-+ 1 row in set (0.00 sec)

5.3. Date and time functions

Get the functions CURDATE (), CURRENT_DATE () of the current date

Mysql > SELECT CURDATE (), CURRENT_DATE (), CURDATE () + 0 +-+ | CURDATE () | CURRENT_DATE () | CURDATE () + 0 | +-+ | 2017-08-01 | 2017-08-01 | 20170801 | +-+ 1 row in set (0.00 sec)

Get the functions CURTIME (), CURRENT_TIME () of the current time

Mysql > SELECT CURTIME (), CURRENT_TIME (), CURTIME () + 0 +-+ | CURTIME () | CURRENT_TIME () | CURTIME () + 0 | +-+ | 18:54:38 | 18:54:38 | | 185438.000000 | +-+ 1 row in set (185438.000000 sec) |

Get the functions CURRENT_TIMESTAMP (), LOCALTIME (), NOW (), SYSDATE () of the current date and time

Mysql > SELECT CURRENT_TIMESTAMP (), LOCALTIME (), NOW (), SYSDATE () +-+ | CURRENT_TIMESTAMP () | LOCALTIME () | NOW () | SYSDATE () | +-+ | 2017-08-01 18:55:48 | 2017-08-01 18:55:48 | 2017-08-01 18: 55:48 | 0-08-01 18:55:48 | +-+ 1 row in set (sec)

UNIX timestamp function

Mysql > SELECT UNIX_TIMESTAMP (), UNIX_TIMESTAMP (NOW ()), NOW () +-+-- +-+ | UNIX_TIMESTAMP () | UNIX_TIMESTAMP (NOW ()) | NOW () | +-+- -+ | 1501585038 | 1501585038 | 2017-08-01 18:57:18 | + -+ 1 row in set (0.00 sec) # returns UNIX format time, that is, seconds after 00:00:00 (1970-01-01) mysql > SELECT FROM_UNIXTIME ('1466393937') # convert UNIX timestamp to normal format time +-+ | FROM_UNIXTIME ('1466393937') | +-+ | 2016-06-20 11:38:57 | +- -+ 1 row in set (0.00 sec)

The function UTC_DATE () that returns the date of UTC

Mysql > SELECT UTC_DATE (), UTC_DATE () + 0 UTC_DATE () | UTC_DATE () + 0 | +-+-+ | 2017-08-01 | 20170801 | +-+-+ 1 row in set (0.00 sec)

Function UTC_TIME () that returns UTC time

Mysql > SELECT UTC_TIME (), UTC_TIME () + 0 +-+-+ | UTC_TIME () | UTC_TIME () + 0 | +-+-+ | 11:01:31 | 110131.000000 | +-+-+ 1 row in set (0 sec)

Get the functions MONTH (date), MONTHNAME (date) of the month

Mysql > SELECT MONTH ('2017-07-13'); +-+ | MONTH (' 2017-07-13') | +-+ | 7 | +-+ 1 row in set (0.00 sec) mysql > SELECT MONTHNAME ('2017-07-13') +-+ | MONTHNAME ('0-07-13') | +-+ | July | +-+ 1 row in set (2017 sec)

Get the week's functions DAYNAME (d), DAYOFWEEK (d), WEEKDAY (d)

Mysql > SELECT DAYNAME ('2017-07-13') # return week +-+ | DAYNAME ('2017-07-13') | +-+ | Thursday | +-+ 1 row in set (0.00 sec) mysql > SELECT DAYOFWEEK (' 2017-07-13') # return d corresponds to the index of the week (1 Sunday, 2 Monday) 7 Saturday) +-+ | DAYOFWEEK ('2017-07-13') | +-+ | 5 | +-+ 1 row in set (0.00 sec) mysql > SELECT WEEKDAY (' 2017-07-13') # returns the index of d corresponding to one working day (0 Sunday, 1 Monday) ... Saturday) +-- + | WEEKDAY ('2017-07-13') | +-- + | 3 | +-+ 1 row in set (0.00 sec)

Get the functions WKKE (d), WEEKOFYEAR (d) of the week

Mysql > SELECT WEEK ('2017-08-01'), WEEK ('2017-08-01), WEEK (' 2017-08-01) +-+-+ | WEEK ('2017-08-01') | WEEK ('2017-08-01) | WEEK (' 2017-08-01') 1) | +-+-- + | 31 | 31 | 31 | +- -- + 1 row in set (0.00 sec) mysql > SELECT WEEK ('2017-08-01') WEEKOFYEAR ('2017-08-01') +-+ | WEEK ('2017-08-01') | WEEKOFYEAR ('2017-08-01') | +-+-+ | 31 | 31 | +-+-- + 1 row in set (0.00 sec)

The functions DAYOFYEAR (d) and DAYOFMONTH (d) that get the number of days

Mysql > SELECT DAYOFYEAR ('2017-08-01') +-+ | DAYOFYEAR ('2017-08-01') | +-+ | 213 | +-+ 1 row in set (0.00 sec) mysql > SELECT DAYOFMONTH ('2017-08-01') +-- + | DAYOFMONTH ('2017-08-01') | +-+ | 1 | +-+ 1 row in set (0.00 sec)

Get the function of year, quarter, hour, minute and second

Mysql > SELECT YEAR ('11-02-03'), YEAR ('96-02-03') +-+ | YEAR ('11-02-03') | YEAR ('96-02-03') | +-+ | 2011 | 1996 | +- -+-+ 1 row in set (0.00 sec) mysql > SELECT QUARTER ('11-02-03') QUARTER ('96-02-03') +-+ | QUARTER ('11-02-03') | QUARTER ('96-02-03') | +-+-+ | | 1 | 1 | +-+-+ 1 row in set (0.00 sec) mysql > SELECT HOUR ('10mysql 05mysql 06') HOUR ('15VH 06RU 56') +-+-+ | HOUR ('10 05bureau 06') | HOUR ('15 06bureau 56') | +-+ | 10 | 15 | +- -+-+ 1 row in set (0.00 sec) mysql > SELECT MINUTE MINUTE ('15VH 06RU 56') +-+ | MINUTE ('10 05bureau 06') | MINUTE ('15jus 06bureau 56') | +-+ + | 5 | 6 | +-+-+ 1 row in set (0.00 sec) mysql > SELECT SECOND ('10 mysql 05mysql 06') SECOND ('15VH 06RU 56') +-+ | SECOND ('10 05bureau 06') | SECOND ('15jus 06bureau 56') | +-+ + | 6 | 56 | +-+-+ 1 row in set (0.00 sec)

Function EXTRACT (type FROM date) that gets the specified value of the date

Mysql > SELECT EXTRACT (YEAR FROM '2017-07-03') AS col1,-> EXTRACT (YEAR_MONTH FROM' 2017-07-0301) AS col2,-> EXTRACT (DAY_MINUTE FROM '2017-07-0301) AS col3 +-+ | col1 | col2 | col3 | +-+ | 2017 | 201707 | 30102 | +-+ 1 row in set (0.00 sec)

A function of time and second conversion

Mysql > SELECT TIME_TO_SEC ('23 231400') +-+ | TIME_TO_SEC ('23 sec 23 sec') | +-+ | 84180 | +-1 row in set (0.00 sec) mysql > SELECT SEC_TO_TIME (84180) +-+ | SEC_TO_TIME (84180) | +-+ | 23:23:00 | +-+ 1 sec (84180 sec)

The function DATE_FORMAT (date,formate) that formats the date

The specifier indicates the abbreviated name of a working day (Sun..Sat) b

The written name of the month (Jan..Dec) c

Month number form (1. 12)% D

The date of the month with an English suffix (1th.2nd.3rd.g.d.)% d

Date of the month in numeric form (01. 31)% e

Date of the month in numeric form (1pm. 31)% f

Microseconds (00000. 999999)% H

Represents 24 hours in 2 digits (00.23)% hmine.I

Represents 12 hours in 2 digits (01minute.. 12)% I

Minute number form (00.59)% j

Number of days in a year (001. 366) k

Represents the time in 24 hours l

Time% M in 12 hours

Month name (January..December) m

Month number form (01. 12)% p

Morning AM or afternoon PM%r

Time 12-hour system (hour hh: minute mm: number of seconds ss followed by AM or PM)% S

Represents seconds in 2-digit form (00. 59)% T

Time 24-hour system (hour hh: minute mm: seconds ss)% U

Week (00.53) Sunday is the first day of week u

Week (00. 53) Monday is the first day of the week% V

Week (01. 53) Sunday is the first day of the week and% X uses% v at the same time.

Week (01. 53) Monday is the first day of the week and% x uses% W at the same time.

Name of working day (Sunday.. Saturday)% w

Every day of the week (0 = Sunday. 6 = Saturday)% X

The Sunday of the week is the first day of the week in the form of four digits and% V using% x at the same time.

The year Monday of the week is the first day of the week in the form of 4 digits and% v using% Y at the same time.

The 4-digit form represents the year y

2-digit representation of year%

The'% 'literal character mysql > SELECT DATE_FORMAT (' 1997-10-04 22 AS col1) AS col2 (% W% M% Y') AS col2 +-+ | col1 | col2 | + -+ | Saturday October 1997 | 4th 97 Sat 04 10 Oct 277 | +-+ 1 row in set (0.00 sec)

The function TIME_FORMAT (time,formate) that formats time

Mysql > SELECT TIME_FORMAT ('16Vl00% H% k% h% I% l') +-+ | TIME_FORMAT ('16virtual 0000' '% H% k% h% I% l') | +-+ | 16 16 04 04 4 | +-+ 1 row in set (0.00 sec)

5.4. Conditional judgment function

IF (expr,v1,v2)

Mysql > SELECT IF (1 > 2 TEST','TEST1' 2),-> IF (1 IF (STRCMP ('TEST','TEST1'),' NO','YES') +-- + | IF (1 > 2Magne2) | IF (1 SELECT IFNULL (1 Magne2), IFNULL (NULL,10), IFNULL (1 Lexo) +-+ | IFNULL (1Magazine 2) | IFNULL (NULL,10) | IFNULL (1Univer 0) 'WRONG') | +-+ | 1 | 10 | WRONG | +- -+ 1 row in set (0.00 sec)

5.5. System information function

Get the MySQL version number VERSION ()

Mysql > SELECT VERSION (); +-+ | VERSION () | +-+ | 5.5.56-log | +-+ 1 row in set (0.00 sec)

Get the number of connections CONNECTION_ID ()

Mysql > SELECT CONNECTION_ID (); +-+ | CONNECTION_ID () | +-+ | 6 | +-+ 1 row in set (0.00 sec)

Show running threads

Mysql > SHOW PROCESSLIST # display running thread +-+-+ | Id | User | Host | db | Command | Time | State | Info | +-+- -+ | 6 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | + -+ 1 row in set (0.00 sec)

Get user name

Mysql > SELECT USER (), CURRENT_USER (), SYSTEM_USER () +-+ | USER () | CURRENT_USER () | SYSTEM_USER () | +- -+ | root@localhost | root@localhost | root@localhost | +-+ 1 row in set (0.00 sec)

Gets the character set and sorting method of the string

Mysql > SELECT CHARSET ('ABC'), CHARSET (CONVERT (' ABC' USING latin1)), CHARSET (VERSION ()) +-+-+ | CHARSET ('ABC') | CHARSET (CONVERT (' ABC' USING latin1)) | CHARSET (VERSION ()) | +- -- + | utf8 | latin1 | utf8 | +-+- -- +-+ 1 row in set (0.00 sec) mysql > SELECT COLLATION ('ABC') COLLATION (CONVERT ('ABC' USING utf8)) +-+ | COLLATION ('ABC') | COLLATION (CONVERT (' ABC' USING utf8)) | +-+-- -+ | utf8_general_ci | utf8_general_ci | +-+-- + 1 row in set (0.00 sec)

5.6. Encryption function

Encryption function PASSWORD (str)

Mysql > SELECT PASSWORD ('NEWPD') +-- + | PASSWORD ('NEWPD') | +-- + | * 2AC78BA05A00714DDD77D040F46ABF58440382F2 | +- -- + 1 row in set (0.00 sec)

Encryption function MD5 (str)

Mysql > SELECT MD5 ('NEWPD') +-- + | MD5 ('NEWPD') | +-+ | 825ea75e25db1b886e20b14281447628 | +-- -+ 1 row in set (0.00 sec)

Encryption function ENCODE (str,pswd_str)

Mysql > SELECT ENCODE ('secret','cry'), LENGTH (ENCODE (' secret','cry')) +-+-+ | ENCODE ('secret','cry') | LENGTH (ENCODE (' secret') 'cry')) | +-+-- + | | 6 | +-+- -- + 1 row in set (0.00 sec)

Decryption function DECODE (crypt_str,pswd_str)

Mysql > SELECT DECODE (ENCODE ('secret','cry'),' cry') +-- + | DECODE (ENCODE ('secret','cry')) 'cry') | +-+ | secret | +-+ 1 row in set (0.00 sec)

5.7. Other functions

Formatting function FORMAT (xQuery n)

Mysql > SELECT FORMAT (12332.12345), FORMAT (12332.1) +-+ | FORMAT (12332.12345) | FORMAT (12332.1) | +-+ | 12332.1235 | 12332.1000 | +- -+-+ 1 row in set (0.00 sec)

A function that converts numbers in different bases.

Mysql > SELECT CONV.-> CONV. +-+ | CONV ('a') 16Power2) | CONV (15recover10reagio 2) | CONV (15Magazine 10Magin8) | CONV (15Power10page16) | +-+ | 1010 | 1111 | 17 | F | +- -+ 1 row in set (0.00 sec)

The function of conversion between IP address and number

Mysql > SELECT INET_ATON ('209.207.224.40') +-- + | INET_ATON ('209.207.224.40') | +-+ | 3520061480 | +-+ 1 row in set ( 0 sec) mysql > SELECT INET_NTOA (3520064480) +-- + | INET_NTOA (3520064480) | +-- + | 209.207.235.224 | +-+ 1 row in set (0.00 sec)

A function that repeats a specified operation

Mysql > SELECT BENCHMARK (5000 Magi password ('NEWPD')) +-+ | BENCHMARK (5000 PASSWORD ('NEWPD')) | +-- + | 0 | +-- + 1 row in set (0.01 sec)

A function that changes the character set

Mysql > SELECT CHARSET ('STRING'), CHARSET (CONVERT (' STRING' USING latin1)) +-+ | CHARSET ('STRING') | CHARSET (CONVERT (' STRING' USING latin1)) | +-+- -+ | utf8 | latin1 | +-+-- + 1 row in set (0.00 sec)

A function that changes the data type

Mysql > SELECT CAST (100AS CHAR (2)), CONVERT ('2010-10-01 12-12-14-12-14-12-12-12-10-01-12-14-12-12-12-10-01-12-12-10-01-12-12-10-01-12-15-12-10-01-12-15-12-10-01-12) +-+-+ | CAST (100AS CHAR (2)) | CONVERT ('2010-10-01 12 purl 12' TIME) | +-- +-- + | 10 | 12:12:12 | + -- + 1 row in set 1 warning (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.