|
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 );
-
+------+------+-------+------------+---------------------+------+------+
-
| 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();
-
+------------------+
-
| 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';
(责任编辑：IT) |
