PHP Common functions

Mathematical function mathematical function is mainly used for processing numbers, including integral type, floating point number 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 difference from round is not rounded)

SELECT TRUNCATE (1.23456,3)--1.234

ROUND (x, y) retains the value of x after the decimal point, 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-second side of E

SELECT EXP (3)--20.085536923188

MOD (x, y) returns the remainder after x divided by y

SELECT MOD (5,2)--1

String functions are the most commonly used 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) replaces string S2 with S1 's x position starting at Len's length

SELECT INSERT (' 12345 ', 1,3, ' abc ')--abc45

UPPER (s), ucaase (s) converts all letters of the string s into uppercase letters

SELECT UPPER (' abc ')--ABC

LOWER (s), LCASE (s) turns all 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) minus the space at the end of the string s trim (s) minus 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) replaces the string S2 string s with strings 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 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

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 returns the 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 first few weeks of the year, and the scope 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 ordinal of the month

SELECT dayofmonth (' 2011-11-11 11:11:11 ')->11

QUARTER (d) Return Date D is the first season, return to 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

System Information Function System Information function is used to query the MySQL database system information.

function versions () 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 user name (), System_user () returns the current

Cryptographic function Encryption function is the function 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 '

PHP Common functions