MySQL research MySQL common built-in functions fully resolved



Description

1) can be used in the select/update/delete , and where, the

2) in the function, the field name as an argument, the value of the variable is the value of each row corresponding to the field.

3) in programming languages such as the functions provided in C + + , most ofMySQL also provides, for complete information about MySQL functions, see theMySQL Reference Manual "

First, the string function "more commonly used, need to master"

1, concat (s1,s2,..., sn) # concatenate incoming arguments into a string

Selectconcat (' abc ', ' Def ');

Selectconcat (name, ' Age was ', age) from users;

2.Insert (STR,M,N,INSER_STR) # replaces The n characters of Str starting with the M position with a Inser_str

Selectinsert (' ABCdef ', 2, 3, ' 123456′);

Selectinsert (name,3,2, ' HAHA ') from users;

Selectinsert (name,2,2, ' 00′) from users;

3,lower (str)/upper (str) # convert string str to lowercase / Uppercase

Selectlower (' Hello '), upper (' Hello ');

selectlower (' hello ') as ' Hello',Upper (' Hello ') as ' hello ';

select* from users where upper (name) = ' AAA ';

4,Left (str,n)/right (str,n) # returns the leftmost / rightmost n Characters of str , respectively, if n <=> NULL If nothing is returned

Selectleft (' 123′,3), right (' 123456′,3), left (' 123′,null);

5,lpad (Str,n,pad)/rpad (str,n,pad) # with the string pad to the leftmost / rightmost of str to fill, know to meet str contains n characters until

Selectname,lpad (name,10, ' # '), Rpad (name,10, ' @ ') from users;

6.Trim (str)/ltrim (str)/rtrim ( str) # Remove string str left/Right space / left space / white space

Selectconcat (' # ', Trim ("abc"), ' # '), concat (' # ', LTrim (' abc '), ' # '), concat (' # ', RTrim (' abc '), ' # ');

7. Replace(STR,SEAR_STR,SUB_STR) # replaces all occurrences of sear_str string in string str with sub_str

Select replace (' ABCDEFGABCD ', ' CD ', ' XXX ');

8,strcmp (str1,str2) # Compare string str1in ASCII code,str2, return 1(str1 < str2)/0 (str1= str2)/1 (str1 > str2)

selectstrcmp (' AA ', ' BB '), strcmp (' AA ', ' AA '), strcmp (' BB ', ' AA ');

9,substring (str,n,m) # returns string from n ,m character length in string str

Selectsubstring (' ABCdef ', 2, 3);

Selectname,substring (name,1,2) as subname from users;

Second, numerical function

1,abs (x) # returns the absolute value of x

Selectabs (Ten), ABS (-10);

Selectabs (age) from users;

2,ceil (x) # returns the smallest integer greater than x

3, Floor(x) # returns the largest integer less than x

Selectceil (2.1), ceil (2.5), ceil (2.9), floor (2.1), floor (2.5), floor (2.9)

4,mod (x, y) # returns the modulo of x/y , same as x%y function

Selectmod (null,11);

The Game Programming Network www.cgzhw.com has the detailed explanation, here will not repeat.

Third, date function

1,curdate () # returns the current date

2,curtime () # returns the current time

Selectcurdate (), Curtime ();

3. Now() # returns the current date + time

Selectnow ();

4,Unix_timestamp (Now ()) # Returns the timestamp of the current time of the Unix

Selectunix_timestamp (now ()); # number of seconds from the beginningof thecomputer (1971-1-100: xx:)

5,from_unixtime () # converts a timestamp (integer) to the form of date + time (xx-xx-xxxx:xx:xx)

Selectfrom_unixtime (1392853616);

6,Week (Now ()) # Returns the current time is the first week

7, Year(Now ()) # Returns the current XX years

8,Hour (now ())/hour (Curtime ()) # Returns the number of hours in the current time

9,minute (Curtime ()) # Returns the number of minutes in the current period

...

Selectweek (now ()), Year (now ()), Hour (now ());

Selectweek (From_unixtime (1392853616)) # Returns the number of cycles in a UNIX timestamp

MonthName (now ())/monthname (Curdate ()) # returns the English name of the current month

One,date_format (now (), "%y-%m-%d%h:%i%s") # to format the period of time

Selectdate_format (now (), "%y-%m-%d%h:%i%s");

Selectdate_format (now (), "%y%m%d%h:%i%s");

Iv. Process Control functions

1. If(value,true,false) # returns trueif value value is true, otherwise, returns false

Selectif (Salary >, ' Hight ', ' low ') from salary;

Selectid,salary, if (Salary <=> null, ' null ', ' NOT null ') from salary;

2,ifnull (value1,value2) # If value1 is not empty, return value1, otherwise return value2

# can be used to replace null values

Selectifnull (salary,0.00) from salary;

3.casewhen [value] then ... else ... end # If the value is True , then the statement after then executes the eles After the statement, do not forget the end!

Selectcase when salary <= and "low" else "Hight" End from salary;

V. Other functions

1, Database() # current databases

2, Version() # Current database version

3, User() # currently logged in users

Selectdatabase ();

4,Inet_aton (IP) #ip address network byte order

Selectinet_aton (' 192.168.139.1′);

5,Inet_ntoa # returns the IP represented by the number

Selectinet_ntoa (3232271105);

6,password (str) # returns the encrypted str string

Selectpassword ("123456″"); # Returns a single-digit encrypted string that is used to encrypt the MySQL system User

7,MD5 () # data encryption in the application , such as in C + + programs

SELECTMD5 ("123456");