MySQL Common functions

First, mathematical functions
Mathematical functions are mainly used for processing numbers, including integers, floating-point numbers, and so on.

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

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, 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 (25)--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

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

CONCAT (S1,s2,...)
Combine strings s1,s2 and more into a single string

SELECT CONCAT (' 12 ', ' 34 ')--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

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
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 (' ABCA ', ' a ', ' X ')--xbcx

STRCMP (S1,S2) comparing strings S1 and S2

SUBSTRING (S,n,len) Gets the string starting from the nth position in the string s with the length Len

LOCATE (S1,s), POSITION (S1 in s)
Gets the starting position of the S1 from the string s

SELECT LOCATE (' B ', ' abc ')--2

REVERSE (s)
Reverse the order of the string s

SELECT REVERSE (' abc ')--CBA

FIELD (S,s1,s2 ...)
Returns the first string position that matches the string s

SELECT FIELD (' C ', ' A ', ' B ', ' C ')--3

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

Year (d),
MONTH (d)
Day (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

Iv. System Information functions
System information functions are used to query the MySQL database for system information.

function action
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 DB name
USER (), System_user ()
Returns the current user

Five, 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 ')
->;vx
SELECT DECODE ('; VX ', ' Xxoo ')
->123

Insert into login values (' LCH ', ' Alvin ', Encode (' 123 ', ' Xxoo '), ' 50 ')

Select Name,decode (Password, ' Xxoo ') from login where username= ' LCH '

MySQL Common functions