MySQL 查询探索（二）使用正则查询

#查询nickname,以’ipuxin2’开头的记录：
SELECT * FROM `user` WHERE nickname REGEXP '^ipuxin2';
ipuxin2356997
ipuxin2

#查询nickname字段以字母’91’结尾的记录：
 SELECT * FROM `user` WHERE nickname REGEXP '91$';
ipuxin2356991

#查询f_name字段值包含字母’a’（在前）与’g’（在后）且两个字母之间只有一个字母的记录，
 SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| bs1  |  102 | orange | 11.20 |
| m1  |  106 | mango | 15.60 |
+------+------+--------+---------+

# 查询f_name字段值以字母’b’开头，且’b’后面有0个或多个’a’的记录：
# + 号表示至少有一个。
 SELECT * FROM fruits WHERE f_name REGEXP '^ba*';
+------+------+------------+------------+
| f_id | s_id | f_name    | f_price  |
+------+------+------------+------------+
| b1  |  101 | blackberry |   10.20 |
| b2  |  104 | berry     |    7.60 |
| t1  |  102 | banana    |   10.30 |
+------+------+------------+--------------+

#查询f_name字段值包含字符串“on”的记录，作用同like：
SELECT * FROM fruits WHERE f_name regexp 'on';
SELECT * FROM fruits WHERE f_name like '%on%';
+------+------+-----------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+-----------+---------+
| bs2  |  105 | melon  |  8.20  |
| l2   |  104 | lemon  |  6.40  |
| o2   |  103 | coconut |  9.20  |
+------+------+------------+---------+

#查询f_name字段值包含字符串“on”或者“ap”的记录：
SELECT * FROM fruits WHERE f_name regexp 'on|ap';
+------+------+----------+---------+
| f_id | s_id | f_name  | f_price |
+-------+-------+-----------+---------+
| a1   |  101 | apple   |    5.20 |
| a2   |  103 | apricot |    2.20 |
| bs2  |  105 | melon   |    8.20 |
| l2   |  104 | lemon   |    6.40 |
| o2   |  103 | coconut |    9.20 |
| t2   |  102 | grape   |    5.30 |
+-------+-------+----------+----------+

#查找f_name字段中包含字母’o’或者’t’的记录，“[a-z]”表示集合区间为从a~z的字母：
SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| a2   |  103 | apricot |    2.20 |
| bs1  |  102 | orange  |   11.20 |
| bs2  |  105 | melon   |    8.20 |
| l2   |  104 | lemon   |    6.40 |
| m1   |  106 | mango   |   15.60 |
| m3   |  105 | xxtt    |   11.60 |
| o2   |  103 | coconut |    9.20 |
+------+------+---------+---------+

# 对比：查询以 a 或 b 开头的记录
SELECT * FROM fruits WHERE f_id REGEXP '^[a-b]';
#查询f_id字段不含字母a~e与数字1~2相连的字符的记录：
SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| b5   |  107 | xxxx    |    3.60 |
| bs1  |  102 | orange  |   11.20 |
| bs2  |  105 | melon   |    8.20 |
| c0   |  101 | cherry  |    3.20 |
| l2   |  104 | lemon   |    6.40 |
| m1   |  106 | mango   |   15.60 |
| m2   |  105 | xbabay  |    2.60 |
| m3   |  105 | xxtt    |   11.60 |
| o2   |  103 | coconut |    9.20 |
| t1   |  102 | banana  |   10.30 |
| t2   |  102 | grape   |    5.30 |
| t4   |  107 | xbababa |    3.60 |
+------+------+---------+---------+

#查询f_name字段值出现字母’x’至少2次的记录：
  SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b5  |  107 | xxxx  |   3.60 |
| m3  |  105 | xxtt  |  11.60 |
+------+-------+--------+---------+

DROP TABLE IF EXISTS `fruits`;
CREATE TABLE `fruits` (
  `f_id` char(10) NOT NULL,
  `s_id` int(11) NOT NULL,
  `f_name` char(255) NOT NULL,
  `f_price` decimal(8,2) NOT NULL,
  PRIMARY KEY (`f_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of fruits
-- ----------------------------
BEGIN;
INSERT INTO `fruits` VALUES ('a', 109, 'about', 99.00);
INSERT INTO `fruits` VALUES ('a1', 101, 'apple', 5.20);
INSERT INTO `fruits` VALUES ('a2', 103, 'apricot', 2.20);
INSERT INTO `fruits` VALUES ('b1', 101, 'blackberry', 10.20);
INSERT INTO `fruits` VALUES ('b2', 104, 'berry', 7.60);
INSERT INTO `fruits` VALUES ('b5', 107, 'xxxx', 3.60);
INSERT INTO `fruits` VALUES ('bs1', 102, 'orange', 11.20);
INSERT INTO `fruits` VALUES ('bs2', 105, 'melon', 8.20);
INSERT INTO `fruits` VALUES ('c0', 101, 'cherry', 3.20);
INSERT INTO `fruits` VALUES ('l2', 104, 'lemon', 6.40);
INSERT INTO `fruits` VALUES ('m1', 106, 'mango', 15.60);
INSERT INTO `fruits` VALUES ('m2', 105, 'xbabay', 2.60);
INSERT INTO `fruits` VALUES ('m3', 105, 'xxtt', 11.60);
INSERT INTO `fruits` VALUES ('o2', 103, 'coconut', 9.20);
INSERT INTO `fruits` VALUES ('t1', 102, 'banana', 10.30);
INSERT INTO `fruits` VALUES ('t2', 102, 'grape', 5.30);
INSERT INTO `fruits` VALUES ('t4', 107, 'xbababa', 3.60);