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 of MySQL also provides, for complete information about MySQL functions, please refer to theMySQL reference manual

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

1, concat (s1,s2,..., sn) #把传入的参数连接成一个字符串

Selectconcat (' abc ', ' Def ');

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

2.Insert (STR,M,N,INSER_STR) #将str with n characters starting from M position to 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) #将字符串str converted 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) #分别返回str leftmost /rightmost n characters, if n<=> NULL then nothing returns

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

5,Lpad (Str,n,pad)/rpad (str,n,pad) #用字符串pad to the leftmost/rightmost of STR to fill, know to satisfy Str contains n characters so far

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

6,trim (str)/ltrim (str)/rtrim (str) #去除字符串str left / right space/Zoling

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

7.replace (STR,SEAR_STR,SUB_STR) #将字符串all occurrences of the SEAR_STR string in STR are replaced with sub_str

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

8,strcmp (str1,str2) #以ASCII code comparison string str1,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) #返回字符串str from N,m character length string

Selectsubstring (' ABCdef ', 2, 3);

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

Second, numerical function

1,ABS (x) #返回The absolute value of x

Selectabs (Ten), ABS (-10);

Selectabs (age) from users;

2,ceil (x) #返回大于the smallest integer of x

3, floor(x) #返回小于the largest integer of x

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

4,mod (x, y) #返回x/y mode, the same as the 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 () #返回当前日期

2,curtime () #返回当前时间

Selectcurdate (), Curtime ();

3. Now() #返回当前日期+ Time

Selectnow ();

4,Unix_timestamp (now ()) #返回time stamp of Unix current time

Selectunix_timestamp (now ()); #从计算机元年 (1971-1-100:00) to the present number of seconds

5,From_unixtime () #将时间戳 (integer) converted to "date + time (xx-xx-xxxx:xx:xx)" Form

Selectfrom_unixtime (1392853616);

6.Week (now ()) #返回当前时间是第几周

7,Year (now ()) #返回当前是xx years

8,Hour (now ())/hour (Curtime ()) #返回当前时间的小时数

9,minute (Curtime ()) #返回当期的分钟数

...

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

Selectweek (From_unixtime (1392853616)); #返回number of cycles in a UNIX timestamp

10,MonthName (now ())/monthname (Curdate ()) #返回当前月的英文名

11,Date_format (now (), "%y-%m-%d%h:%i%s") #将当期时间格式化

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) #如果 value is true, returns true, otherwise, returns false

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

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

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

#可以用来进行空值替换

Selectifnull (salary,0.00) from salary;

3,Casewhen [value] then ... else ... end #如果 value is true, execute then statement, or execute eles statement, do not forget end!

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

V. Other functions

1.database () #当前数据库

2.version () #当前数据库版本

3.User () #当前登录用户

Selectdatabase ();

4,Inet_aton (IP) #ip地址的网络字节顺序

Selectinet_aton (' 192.168.139.1′);

5,inet_ntoa #返回数字所代表的IP

Selectinet_ntoa (3232271105);

6,password (str) #返回加密的str string

Selectpassword ("123456″"); #返回一个41-bit long encrypted string, just to encrypt the MySQL system user

7,MD5 () #在应用程序中进行数据加密, such as in C + + programs

SELECTMD5 ("123456");