48th Day class MySQL correlation function

First, mathematical functions

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

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

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 year, month, and day of the date D

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

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 ... 7 Saturday

SELECT DAYOFWEEK (' 2011-11-11 11:11:11 ')->6

WEEKDAY (d)

Date d is today 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

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 database name

USER (), System_user ()

Returns the current user

Five, encryption function

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 '

48th Day class MySQL correlation function