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';
转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/mysql/1115.html