MYSQL基础函数

一、数学函数

1.ABS(x) #返回x的绝对值　　
SELECT ABS(-1) -- 返回1
2.CEIL(x),CEILING(x) #返回大于或等于x的最小整数　　
SELECT CEIL(1.5) -- 返回2
3.FLOOR(x)  #返回小于或等于x的最大整数　　
SELECT FLOOR(1.5) -- 返回1
4.RAND() #返回0->1的随机数
SELECT RAND() --0.93099315644334
5.PI() #返回圆周率(3.141593）
SELECT PI() --3.141593
6.TRUNCATE(x,y) #返回数值x保留到小数点后y位的值（与ROUND最大的区别是不会进行四舍五入）　　
SELECT TRUNCATE(1.23456,3) -- 1.234
7.ROUND(x,y) #保留x小数点后y位的值，但截断时要进行四舍五入
SELECT ROUND(1.23456,3) -- 1.235
8.POW(x,y).POWER(x,y)   #返回x的y次方
SELECT POW(2,3) -- 8
9.SQRT(x) #返回x的平方根　　
SELECT SQRT(25) -- 5　
10.EXP(x) #返回e的x次方　　
SELECT EXP(3) -- 20.085536923188
11.MOD(x,y) #返回x除以y以后的余数　　
SELECT MOD(5,2) -- 1

二、字符串函数

1.CHAR_LENGTH(s) #返回字符串s的字符数
SELECT CHAR_LENGTH('你好123') -- 5
2.CONCAT(s1,s2,...) #将字符串s1,s2等多个字符串合并为一个字符串
SELECT CONCAT('12','34') -- 1234
3.CONCAT_WS(x,s1,s2,...) #同CONCAT(s1,s2,...)函数，但是每个字符串直接要加上x分割
SELECT CONCAT_WS('@','12','34') -- 12@34
4.INSERT(s1,x,len,s2) #将字符串s2替换s1的x位置开始长度为len的字符串
SELECT INSERT('12345',1,3,'abc') -- abc45
5.UPPER(s),UCAASE(S) #将字符串s的所有字母变成大写字母
SELECT UPPER('abc') -- ABC
6.LOWER(s),LCASE(s) #将字符串s的所有字母变成小写字母
SELECT LOWER('ABC') -- abc
7.LEFT(s,n) #返回字符串s的前n个字符
SELECT LEFT('abcde',2) -- ab
8.RIGHT(s,n) #返回字符串s的后n个字符
SELECT RIGHT('abcde',2) -- de
9.LTRIM(s) #去掉字符串s开始处的空格
10.RTRIM(s) #去掉字符串s结尾处的空格
11.TRIM(s) #去掉字符串s开始和结尾处的空格
SELECT TRIM('@' FROM '@@abc@@') -- abc
12.REPEAT(s,n) #将字符串s重复n次
SELECT REPEAT('ab',3) -- ababab
13.SPACE(n) #返回n个空格
14.REPLACE(s,s1,s2) #将字符串s2替代字符串s中的字符串s1
SELECT REPLACE('abca','a','x') --xbcx
15.STRCMP(s1,s2) #比较字符串s1和s2
16.SUBSTRING(s,n,len) #获取从字符串s中的第n个位置开始长度为len的字符串
17.LOCATE(s1,s),POSITION(s1 IN s) #从字符串s中获取s1的开始位置
SELECT LOCATE('b', 'abc') -- 2
18.REVERSE(s) #将字符串s的顺序反过来
SELECT REVERSE('abc') -- cba
19.FIELD(s,s1,s2...) #返回第一个与字符串s匹配的字符串位置
SELECT FIELD('c','a','b','c') -- 3

三、日期时间函数

1.CURDATE(),CURRENT_DATE() #返回当前日期
SELECT CURDATE() ->2014-12-17
2.CURTIME(),CURRENT_TIME #返回当前时间
SELECT CURTIME() ->15:59:02
3.NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),LOCALTIMESTAMP() #返回当前日期和时间
SELECT NOW() ->2014-12-17 15:59:02
4.YEAR(d),MONTH(d)DAY(d) #返回日期d中的月份值
SELECT MONTH('2011-11-11 11:11:11') ->11
5.MONTHNAME(d) #返回日期当中的月份名称，如Janyary
SELECT MONTHNAME('2011-11-11 11:11:11') ->November
6.DAYNAME(d) #返回日期d是星期几，如Monday,Tuesday
SELECT DAYNAME('2011-11-11 11:11:11') ->Friday
7.DAYOFWEEK(d) #日期d今天是星期几，1星期日，2星期一
SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6
8.WEEKDAY(d) #日期d今天是星期几，0表示星期一，1表示星期二
9.WEEK(d)，WEEKOFYEAR(d) #计算日期d是本年的第几个星期，范围是0->53
SELECT WEEK('2011-11-11 11:11:11') ->45
10.DAYOFYEAR(d) #计算日期d是本年的第几天
SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315
11.DAYOFMONTH(d) #计算日期d是本月的第几天
SELECT DAYOFMONTH('2011-11-11 11:11:11') ->11
12.QUARTER(d) #返回日期d是第几季节，返回1->4
SELECT QUARTER('2011-11-11 11:11:11') ->4
13.HOUR(t) #返回t中的小时值
SELECT HOUR('1:2:3') ->1
14.MINUTE(t) #返回t中的分钟值
SELECT MINUTE('1:2:3') ->2
15.SECOND(t) #返回t中的秒钟值
SELECT SECOND('1:2:3') ->3
16.创建存储过程，遍历生成下个月的表
CREATE PROCEDURE `launch`() 
BEGIN
declare i date;  #定义i变量  
declare j date;  #定义j变量  
set i= date_add(curdate()-day(curdate())+1,interval 1 month);  
set j= date_add(curdate()-day(curdate())+1,interval 2 month);  
while(i<j) do  #对学号的循环  
    SET @STMT :=CONCAT("CREATE TABLE `xiaoheiban2_",date_format(i, '%Y%m%d'),"` (
  `xhb_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `text` text NOT NULL COMMENT '晓黑板推送内容',
  `update_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '晓黑板推送时间',
  PRIMARY KEY (`xhb_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7759246 DEFAULT CHARSET=utf8 COMMENT='晓黑板';");
    PREPARE STMT FROM @STMT;
    EXECUTE STMT;
    set i=date_add(i,interval +1 day);  
end while;

END 

四、系统信息函数

1.VERSION() #返回数据库的版本号
SELECT VERSION() ->5.0.67-community-nt
2.CONNECTION_ID() #返回服务器的连接数
3.DATABASE(),SCHEMA() #返回当前数据库名
4.USER(),SYSTEM_USER() #返回当前用户

五、加密函数

1.PASSWORD(str) #该函数可以对字符串str进行加密，一般情况下，PASSWORD(str)用于给用户的密码加密。
SELECT PASSWORD('123') ->*23AE809DDACAF96AF0FD78ED04B6A265E05AA257
2.MD5(str) #函数可以对字符串str进行散列，可以用于一些普通的不需要解密的数据加密。
SELECT md5('123') ->202cb962ac59075b964b07152d234b70
3.ENCODE(str,pswd_str)与DECODE(crypt_str,pswd_str) #ENCODE函数可以使用加密密码pswd_str来加密字符串str，加密结果是二进制数，需要使用BLOB类型的字段保存。该函数与DECODE是一对，需要同样的密码才能够解密。
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'