mysql中charcat_Mysql中的字符串函数

/*+++++++++++++++++++

燕十八 公益PHP培训

课堂地址:YY频道88354001

学习社区:www.zixue.it

Mysql中的字符串函数

以goods表为例:

+----------+--------+------------------------------+

| goods_id | cat_id | goods_name |

+----------+--------+------------------------------+

| 1 | 4 | KD876 |

| 4 | 8 | 诺基亚N85原装充电器

| 3 | 8 | 诺基亚原装5800耳机 |

| 5 | 11 | 索爱原装M2卡读卡器

| 6 | 11 | 胜创KINGMAX内存卡 |

| 7 | 8 | 诺基亚N85原装立体声耳机HS-82

| 8 | 3 | 飞利浦9@9v |

| 9 | 3 | 诺基亚E66 |

| 10 | 3 | 索爱C702c |

| 11 | 3 | 索爱C702c |

+----------+--------+------------------------------+

1:group_cancat函数

select group_concat(goods_id,cat_id) from goods;

+-----------------------------------+

| group_concat(goods_id,cat_id) |

+-----------------------------------+

| 14,48,38,511,611,78,83,93,103,113 |

+-----------------------------------+

//将goods_id 与cat_id合并起来默认一逗号隔开

2:length与char_length函数

mysql> select goods_name, char_length(goods_name)from goods where goods_id=9;

+------------+-------------------------+

| goods_name | char_length(goods_name) |

+------------+-------------------------+

| 诺基亚E66 | 6 |

+------------+-------------------------+

select goods_name, length(goods_name)from goods where goods_id=9;

+------------+--------------------+

| goods_name | length(goods_name) |

+------------+--------------------+

| 诺基亚E66 | 12 |

+------------+--------------------+

length函数是计算字段的字节长度由于我存的utf-8所以length是12,如果是gbk应该是9个;

而char_length函数时计算字符长度因此char_length是6个长度.

3:reverse函数

+------------+---------------------+-------------------------+

| goods_name | reverse(goods_name) | char_length(goods_name) |

+------------+---------------------+-------------------------+

| 诺基亚E66 | 66E亚基诺 | 6 |

+------------+---------------------+-------------------------+

4:position与locate函数

+------------+----------------------------+

| goods_name | position('E'in goods_name) |

+------------+----------------------------+

| 诺基亚E66 | 4 |

+------------+----------------------------+

上题使计算'E'在诺基亚E66出现的位置,函数的用法:

position('字符' in (字串));

locate('字符' , (字串));

两个函数效果一样.

5:now函数

select now();

+---------------------+

| now() |

+---------------------+

| 2012-11-01 13:48:21 |

+---------------------+

求出当前时间函数

select curdate();

+---------------------+

|curdate() |

+---------------------+

| 2012-11-01 |

+---------------------+

select curtime();

+---------------------+

| curtime() |

+---------------------+

| 13:48:21 |

+---------------------+

结果出来,不用多解释;

6:加密函数 MD5(),sha();

selectMD5(111);

+----------------------------------+

| MD5(111) |

+----------------------------------+

| 698d51a19d8a121ce581499d7b701668 |

+----------------------------------+

mysql> select SHA(111);

+------------------------------------------+

| SHA(111) |

+------------------------------------------+

| 6216f8a75fd5bb3d5f22b6f9958cdede3fc086c2 |

+------------------------------------------+

就给数据加密

7:系统调试函数

show tababase;//显示当前选择的数据库

show user;//显示当前用户

8:mysql函数很多知识举出几个常用的函数

++++++++++++++++++++*/