<<
>>

经常忘的几个msyql用法，replace,if等

张映 发表于 2010-05-19

分类目录： mysql

标签：, , , , , , , ,

一，测试表结构

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 COMMENT '评论时间',
  PRIMARY KEY  (`c_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- 导出表中的数据 `comment`
-- 

INSERT INTO `comment` (`c_id`, `u_id`, `name`, `content`, `datetime`) VALUES
(1, 1, '张映', '触发器测试', '2010-05-17 23:04:58'),
(2, 1, '张映', '111111111', '2010-05-19 23:06:05'),
(3, 1, 'tank', '使代码更简单', '2010-05-20 23:06:17');

c_id u_id name content datetime
1 1 张映 触发器测试 2010-05-17 23:04:58
2 1 张映 11111111 2010-05-19 23:06:05
3 2 tank 使代码更简单 2010-05-20 23:06:17

二，mysql的replace

每次用到replace我都要查一下，因为数据库里面的数据很重要，所以我很小心，也没有特意去记过，这次一定要记住。用replace替换表中的111，换成222

replace(string ,search_string,replace_string)
update comment set content=replace(content,'1111','2222')  where c_id = '2';

三，if的用法

1，用法一，存储过程和function用

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF
mysql> create procedure testifthen()
    -> begin
    ->
    ->     declare userid int;
    ->     select u_id into userid  from comment where c_id=3;
    ->
    ->     if userid  = 3 then
    ->       update comment set content='userid3' where c_id=3;
    ->
    ->     else
    ->         update comment set content='useridequalother' where c_id=3;
    ->
    -> END IF;
    -> end;|
Query OK, 0 rows affected (0.00 sec)

mysql> call testifthen();
    -> |
Query OK, 1 row affected (0.00 sec)

2，用法二

IF (condition ,statement_true_list,statement_false_list)
SELECT if( u_id =1, '张映', 'tank' ) AS uname,name FROM `comment`

四，case when的用法

1，语法说明

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

2，根据上表举例一

 SELECT CASE u_id
WHEN 1
THEN '张映'
WHEN 2
THEN 'tank'
ELSE 'no'
END AS uname, name
FROM COMMENT

3，根据上表举例二

 SELECT CASE
WHEN u_id =1
THEN '张映'
WHEN u_id =2
THEN 'tank'
ELSE 'no'
END AS uname, name
FROM COMMENT

看到不同了吗

五，substring ,substr,substring_index

1,substring,substr这二个可以互换的

SUBSTRING(str ,pos)
SUBSTRING(str FROM pos)
SUBSTRING(str ,pos,len)
SUBSTRING(str FROM pos FOR len)
SELECT * FROM `comment` WHERE substring(datetime,1,10)='2010-05-19'

SELECT * FROM `comment`  WHERE substring( datetime FROM 1 FOR 10 ) = '2010-05-19'

SELECT * FROM `comment` WHERE substr(datetime,1,10)='2010-05-19'

SELECT  * FROM `comment`  WHERE substr( datetime FROM 1 FOR 10 ) = '2010-05-19'

这里要注意，这里的字符串下标是从1开始的，不是从0开始的。根一般语言不同

2,substring_index

SUBSTRING_index(str ,delim,count)
SELECT  *   FROM  `comment`  WHERE substring_index( datetime,  ' ', 1  )  =  '2010-05-12'


转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/mysql/741.html
<<
>>

相关文章