第五章 MySQL常用函数

第五章MySQL常用函数字符串函数数值函数日期时间函数流程函数其它函数5.1字符串函数功能CONCAT(S1,S2,…,SN)LTRIM(STR)INSERT(STR,X,Y,INSTR)RTRIM(STR)LOWER(STR)REPEAT(STR,X)UPPER(STR)REPLACE(STR,A,B)LEFT(STR,X)STRCMP(S1,S2)RIGHT(STR,X)TRIM(STR)LPAD(STR,N,PAD)SUBSTRING(STR,X,Y)RPAD(STR,N,PAD)Concat(s1,s2,……,sn)Mysqlselectconcat(‘aaa’,’bbb’,’ccc’),concat(‘aaa’,null);Insert(str,x,y,instr)Mysqlselectinsert(‘beijing2008you’,12,3,’me’);Lower(str),upper(str)Mysqlselectlower(‘BEIJING2008’),upper(‘beijing2008’);Left(str,x),right(str,x)Mysqlselectleft(‘beijing2008’,7),right(‘beijing2008’,4);Lpad(str,n,pad),rpad(str,n,pad)Mysqlselectlpad(‘2008’,20,’beijing’),rpad(‘beijing’,20,’2008’);Ltrim(str),rtrim(str)Mysqlselectltrim(‘beijing’),rtrim(‘beijing’);Repeat(str,x)Mysqlselectrepeat(‘mysql’,3);Replace(str,a,b)Mysqlselectreplace(‘beijing2008’,’2008’,’2155’);Strcmp(s1,s2)Mysqlselectstrcmp(‘a’,’b’),strcmp(‘b’,’b’),strcmp(‘c’,’b’);Trim(str)Mysqlselecttrim(‘$beijing2008$’);Substring(str,x,y)Mysqlselectsubstring(‘beijing2008’,8,4),substring(‘beijing2008’,1,7);5.2数值函数函数ABS(X)RAND(X)CEIL(X)ROUND(X,Y)FLOOR(X)TRUNCATE(X,Y)MOD(X,Y)Abs(x)Mysqlselectabs(-0.8),abs(0.8);Ceil(x)Mysqlselectceil(-0.8),ceil(0.8);Floor(x)Mysqlselectfloor(-0.8),floor(0.8);Mod(x,y)Mysqlselectmod(15,10),mod(1,11),mod(null,10);Rand()Mysqlselectrand(),rand();Round(x,y)Mysqlselectround(1.1),round(1.1,2),round(1,2);Truncat(x,y)Mysqlselectround(1.235,2),truncat(1.235,2);5.3日期时间函数函数CURDATE()HOUR()CURTIME()MINUTE()NOW()MONTHNAME()UNIX_TIMESTAMP()DATE_FORMAT()FROM_UNIXTIME()DATE_ADD()WEEK()DATEDIFF()YEAR()CURDATE()Mysqlselectcurdate();CURTIME()Mysqlselectcurtime();NOW()Mysqlselectnow();Unix_timestamp()Mysqlselectunix_timestamp(now());From_unixtime()Mysqlselectfrom_unixtime(1184134516);WEEK(),YEAR()Mysqlselectweek(now()),year(now());Hour(),minute()Mysqlselecthour(curtime()),minute(curtime());Monthdate()Mysqlselectmonthdate(now());Date_format()Mysqlselectdate_format(now(),’%m,%d,%y’);Date_add()Mysqlselectnow()current,date_add(now(),interval31day)after31days,date_add(now(),interval‘1_2’year_month)after_oneyear_twomonth;Date_add()Mysqlselectnow()current,date_add(now(),interval-31day)after31days,date_add(now(),interval‘-1_-2’year_month)after_oneyear_twomonth;Datediff()Mysqlselectdatediff(‘2008-08-08’,now());5.4流程函数函数If(value,t,f)Ifnull(value1,value2)Casewhen(value1)then(result)else(default)endCase(expr)when(value1)then(result)else(default)endMysqlcreatetables(useridint,saldecimal(10,2));Mysqlinsertintosvalues(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);Mysqlselect*froms;If(value,t,f)Mysqlselectif(sal2000,’high’,’low’)froms;Ifnull(value1,value2)Mysqlselectifnull(sal,0)froms;Casewhen(value1)then(result)else(default)endMysqlselectcasewhensal2000then‘low’else‘high’endfroms;Case(expr)when(value1)then(result)else(default)endMysqlselectcasesalwhen1000then‘low’when2000then‘mid’else‘high’endfroms;5.5其它函数函数Database()Inet_ntoa(num)Version()Password(str)User()Md5()Inet_aton(ip)Database()Mysqlselectdatabase();Version()Mysqlselectversion();User()Mysqlselectuser();Inet_aton()Mysqlselectinet_aton(‘192.168.1.1’);Inet_ntoa()Mysqlselectinet_ntoa(‘3232235777’);Mysqlselect*fromtest;Password(str)Mysqlselectpassword(‘123456’);Md5(str)Mysqlselectmd5(‘123456’);