mysql数据库replace、regexp的用法

replace、regexp的用法
0 Comments | This entry was posted on Apr 08 2010
mysql replace用法
1.replace into
replace into table (id,name) values('1′,'aa'),('2′,'bb')
此语句的作用是向表table中插入两条记录。如果主键id为1或2不存在
就相当于
insert into table (id,name) values('1′,'aa'),('2′,'bb')
如果存在相同的值则不会插入数据
2.replace(object,search,replace)
把object中出现search的全部替换为replace
select replace('www.163.com','w','Ww')—>WwWwWw.163.com
例：把表table中的name字段中的aa替换为bb
update table set name=replace(name,'aa','bb')
——————————————————————————–
由MySQL提供的模式匹配的其它类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。
扩展正则表达式的一些字符是：
· ‘.'匹配任何单个的字符。
· 字符类“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”、“b”或“c”。为了命名字符的范围，使用一个“-”。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
· “ * ”匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的“x”字符，“[0-9]*”匹配任何数量的数字，而“.*”匹配任何数量的任何字符。
如果REGEXP模式与被测试值的任何地方匹配，模式就匹配(这不同于LIKE模式匹配，只有与整个值匹配，模式才匹配)。
为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“^”或在模式的结尾用“$”。
为了说明扩展正则表达式如何工作，下面使用REGEXP重写上面所示的LIKE查询：
为了找出以“b”开头的名字，使用“^”匹配名字的开始：
mysql> SELECT * FROM pet WHERE name REGEXP ‘^b';
+——–+——–+———+——+————+————+
| name| owner | species | sex | birth | death |
+——–+——–+———+——+————+————+
| Buffy | Harold | dog| f | 1989-05-13 | NULL|
| Bowser | Diane | dog| m | 1989-08-31 | 1995-07-29 |
+——–+——–+———+——+————+————+
如果你想强制使REGEXP比较区分大小写，使用BINARY关键字使其中一个字符串变为二进制字符串。该查询只匹配名称首字母的小写‘b'。
mysql> SELECT * FROM pet WHERE name REGEXP BINARY ‘^b';
为了找出以“fy”结尾的名字，使用“$”匹配名字的结尾：
mysql> SELECT * FROM pet WHERE name REGEXP ‘fy$';
+——–+——–+———+——+————+——-+
| name| owner | species | sex | birth | death |
+——–+——–+———+——+————+——-+
| Fluffy | Harold | cat| f | 1993-02-04 | NULL |
| Buffy | Harold | dog| f | 1989-05-13 | NULL |
+——–+——–+———+——+————+——-+
为了找出包含一个“w”的名字，使用以下查询：
mysql> SELECT * FROM pet WHERE name REGEXP ‘w';
+———-+——-+———+——+————+————+
| name| owner | species | sex | birth | death |
+———-+——-+———+——+————+————+
| Claws | Gwen | cat| m | 1994-03-17 | NULL|
| Bowser| Diane | dog| m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL|
+———-+——-+———+——+————+————+
既然如果一个正则表达式出现在值的任何地方，其模式匹配了，就不必在先前的查询中在模式的两侧放置一个通配符以使得它匹配整个值，就像你使用了一个SQL模式那样。
为了找出包含正好5个字符的名字，使用“^”和“$”匹配名字的开始和结尾，和5个“.”实例在两者之间：
mysql> SELECT * FROM pet WHERE name REGEXP ‘^…..$';
+——-+——–+———+——+————+——-+
| name | owner | species | sex | birth | death |
+——-+——–+———+——+————+——-+
| Claws | Gwen| cat| m | 1994-03-17 | NULL |
| Buffy | Harold | dog| f | 1989-05-13 | NULL |
+——-+——–+———+——+————+——-+
你也可以使用“{n}”“重复n次”操作符重写前面的查询：
mysql> SELECT * FROM pet WHERE name REGEXP ‘^.{5}$';
+——-+——–+———+——+————+——-+
| name | owner | species | sex | birth | death |
+——-+——–+———+——+————+——-+
| Claws | Gwen| cat| m | 1994-03-17 | NULL |
| Buffy | Harold | dog| f | 1989-05-13 | NULL |
+——-+——–+———+——+————+——-+
附录G：MySQL正则表达式 提供了关于正则表达式的句法的详细信息。
3.3.4.8. 计数行数据库经常用于回答这个问题，“某个类型的数据在表中出现的频度?”例如，你可能想要知道你有多少宠物，或每位主人有多少宠物，或你可能想要对你的动物进行各种类型的普查。
计算你拥有动物的总数目与“在pet表中有多少行?”是同样的问题，因为每个宠物有一个记录。COUNT(*)函数计算行数，所以计算动物数目的查询应为：
mysql> SELECT COUNT(*) FROM pet;
+———-+
| COUNT(*) |
+———-+
| 9 |
+———-+
在前面，你检索了拥有宠物的人的名字。如果你想要知道每个主人有多少宠物，你可以使用COUNT( )函数：
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+——–+———-+
| owner | COUNT(*) |
+——–+———-+
| Benny | 2 |
| Diane | 2 |
| Gwen| 3 |
| Harold | 2 |
+——–+———-+
注意，使用GROUP BY对每个owner的所有记录分组，没有它，你会得到错误消息：
mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),…)
with no GROUP columns is illegal if there is no GROUP BY clause
COUNT( )和GROUP BY以各种方式分类你的数据。下列例子显示出进行动物普查操作的不同方式。
每种动物的数量：
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+———+———-+
| species | COUNT(*) |
+———+———-+
| bird | 2 |
| cat| 2 |
| dog| 3 |
| hamster | 1 |
| snake| 1 |
+———+———-+
每种性别的动物数量：
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+——+———-+
| sex | COUNT(*) |
+——+———-+
| NULL | 1 |
| f | 4 |
| m | 4 |
+——+———-+
（在这个输出中，NULL表示“未知性别”。）
按种类和性别组合的动物数量：
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+———+——+———-+
| species | sex | COUNT(*) |
+———+——+———-+
| bird | NULL | 1 |
| bird | f | 1 |
| cat| f | 1 |
| cat| m | 1 |
| dog| f | 1 |
| dog| m | 2 |
| hamster | f | 1 |
| snake| m |1 |
+———+——+———-+
若使用COUNT( )，你不必检索整个表。例如, 前面的查询，当只对狗和猫进行时，应为：
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE species = ‘dog' OR species = ‘cat'
-> GROUP BY species, sex;
+———+——+———-+
| species | sex | COUNT(*) |
+———+——+———-+
| cat| f | 1 |
| cat| m | 1 |
| dog| f | 1 |
| dog| m | 2 |
+———+——+———-+
或，如果你仅需要知道已知性别的按性别的动物数目：
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE sex IS NOT NULL
-> GROUP BY species, sex;
+———+——+———-+
| species | sex | COUNT(*) |
+———+——+———-+
| bird | f | 1 |
| cat| f | 1 |
| cat| m | 1 |
| dog| f | 1 |
| dog| m | 2 |
| hamster | f | 1 |
| snake| m | 1 |
+———+——+———-+
3.3.4.9. 使用1个以上的表
pet表追踪你有哪个宠物。如果你想要记录其它相关信息，例如在他们一生中看兽医或何时后代出生，你需要另外的表。这张表应该像什么呢？需要：
· 它需要包含宠物名字以便你知道每个事件属于哪个动物。
· 需要一个日期以便你知道事件是什么时候发生的。
· 需要一个描述事件的字段。
· 如果你想要对事件进行分类，则需要一个事件类型字段。
综合上述因素，event表的CREATE TABLE语句应为：
mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
-> type VARCHAR(15), remark VARCHAR(255));
对于pet表，最容易的方法是创建包含信息的用定位符分隔的文本文件来装载初始记录：
name
date
type
remark
Fluffy
1995-05-15
litter
4 kittens, 3 female, 1 male
Buffy
1993-06-23
litter
5 puppies, 2 female, 3 male
Buffy
1994-06-19
litter
3 puppies, 3 female
Chirpy
1999-03-21
vet
needed beak straightened
Slim
1997-08-03
vet
broken rib
Bowser
1991-10-12
kennel
Fang
1991-10-12
kennel
Fang
1998-08-28
birthday
Gave him a new chew toy