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，测试表数据

二，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';