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

一，测试表结构

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