经常忘的几个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