7个比较适用mysql函数举例
mysql的函数有很多，下面举几个适用，但是容易被人忽视的函数
一，准备测试表和数据
1，测试表
CREATE TABLE `comment` ( `c_id` int(11) NOT NULL auto_increment COMMENT '评论ID', `u_id` int(11) NOT NULL COMMENT '用户ID', `name` varchar(50) NOT NULL default '' COMMENT '用户名称', `content` varchar(1000) NOT NULL default '' COMMENT '评论内容', `datetime` timestamp NOT NULL default CURRENT_TIMESTAMP, `num1` int(11) default NULL, `num2` int(11) default NULL, PRIMARY KEY (`c_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2，测试表数据
| c_id | u_id | name | content | datetime | num1 | num2 |
|---|---|---|---|---|---|---|
| 1 | 1 | test | 2222222211 | 2010-11-10 15:16:00 | 21 | 12 |
| 2 | 1 | test2 | tank | 2010-11-10 15:01:00 | 133 | 219 |
| 3 | 2 | tank | zhangy | 2010-11-10 15:11:00 | 67 | 16 |
| 4 | 3 | test4 | test | 2010-11-15 16:01:26 | 34 | NULL |
二，mysql常用函数，以及实例
1，GREATEST（求最大值）和LEAST（求最小值）
mysql> SELECT c_id, GREATEST( num1, num2 ) AS max, num1, num2 from comment where num1 != "" and num2 != ""; +------+------+------+------+ | c_id | max | num1 | num2 | +------+------+------+------+ | 1 | 21 | 21 | 12 | | 2 | 219 | 133 | 219 | | 3 | 67 | 67 | 16 | +------+------+------+------+ 3 rows in set (0.00 sec) mysql> SELECT c_id, LEAST( num1, num2 ) AS max, num1, num2 from comment where nu m1 != "" and num2 != ""; +------+------+------+------+ | c_id | max | num1 | num2 | +------+------+------+------+ | 1 | 12 | 21 | 12 | | 2 | 133 | 133 | 219 | | 3 | 16 | 67 | 16 | +------+------+------+------+ 3 rows in set (0.00 sec)
2，CONCAT_WS函数
mysql> SELECT CONCAT_WS( ',', name, content, datetime ) FROM `comment`; +-------------------------------------------+ | CONCAT_WS( ',', name, content, datetime ) | +-------------------------------------------+ | test,2222222211,2010-11-10 15:16:00 | | test2,tank,2010-11-10 15:01:00 | | tank,zhangy,2010-11-10 15:11:00 | | test4,test,2010-11-15 16:01:26 | +-------------------------------------------+ 4 rows in set (0.00 sec)
3，INTERVAL函数
mysql> select * from comment where datetime <= (SELECT now( ) - INTERVAL 10 h our AS time_start ); //10个小时前评论的数据 +------+------+-------+------------+---------------------+------+------+ | c_id | u_id | name | content | datetime | num1 | num2 | +------+------+-------+------------+---------------------+------+------+ | 1 | 1 | test | 2222222211 | 2010-11-10 15:16:00 | 21 | 12 | | 2 | 1 | test2 | tank | 2010-11-10 15:01:00 | 133 | 219 | | 3 | 2 | tank | zhangy | 2010-11-10 15:11:00 | 67 | 16 | +------+------+-------+------------+---------------------+------+------+ 3 rows in set (0.00 sec)
4，last_insert_id函数
mysql> insert into comment(u_id,name,content,datetime,num1,num2)values(2,'test5' ,'good',now(),3,4); Query OK, 1 row affected (0.00 sec) mysql> select last_insert_id(); //取得最后一次插入的ID +------------------+ | last_insert_id() | +------------------+ | 5 | +------------------+ 1 row in set (0.00 sec)
5，REGEXP函数
mysql> SELECT * FROM `comment` WHERE content REGEXP '[0-9]+'; +------+------+------+------------+---------------------+------+------+ | c_id | u_id | name | content | datetime | num1 | num2 | +------+------+------+------------+---------------------+------+------+ | 1 | 1 | test | 2222222211 | 2010-11-10 15:16:00 | 21 | 12 | +------+------+------+------------+---------------------+------+------+ 1 row in set (0.00 sec)
6，rand函数
mysql> select cast(rand()*100 as unsigned) as rand_num; +----------+ | rand_num | +----------+ | 57 | +----------+ 1 row in set (0.00 sec)
7，常用日期函数day,month,hour,time,now等等
mysql> select * from comment where day(datetime) = '15' limit 1; +------+------+-------+---------+---------------------+------+------+ | c_id | u_id | name | content | datetime | num1 | num2 | +------+------+-------+---------+---------------------+------+------+ | 4 | 3 | test4 | test | 2010-11-15 16:01:26 | 34 | NULL | +------+------+-------+---------+---------------------+------+------+ 1 row in set (0.00 sec) mysql> select * from comment where month(datetime) = '11' limit 1; +------+------+------+------------+---------------------+------+------+ | c_id | u_id | name | content | datetime | num1 | num2 | +------+------+------+------------+---------------------+------+------+ | 1 | 1 | test | 2222222211 | 2010-11-10 15:16:00 | 21 | 12 | +------+------+------+------------+---------------------+------+------+ 1 row in set (0.00 sec) mysql> select week(now()); //显示当前周数 +-------------+ | week(now()) | +-------------+ | 46 | +-------------+ 1 row in set (0.00 sec) mysql> select hour(now()); //显示当前的小时数 +-------------+ | hour(now()) | +-------------+ | 17 | +-------------+ 1 row in set (0.00 sec) mysql> select c_id,date_format(datetime,'%Y%m%d%H%i%s') as new_date from comment ; //格式化日期 +------+----------------+ | c_id | new_date | +------+----------------+ | 1 | 20101110151600 | | 2 | 20101110150100 | | 3 | 20101110151100 | | 4 | 20101115160126 | | 5 | 20101115173546 | +------+----------------+ 5 rows in set (0.00 sec)
了解这些函数其他，可以使我们的程序更加的有效一点，代码更加的简洁一点。由于个人习惯问题，
select * from comment where day(datetime) = '15'
我习惯了下面的写法，上面这个方法更加的明了
select * from comment where substring(datetime,9,2) = '15';
