字符串匹配模式
1 mysql> SELECT name FROM metal;
2
3 +----------+
4
5 | name |
6
7 +----------+
8
9 | copper |
10
11 | gold |
12
13 | iron |
14
15 | lead |
16
17 | mercury |
18
19 | platinum |
20
21 | silver |
22
23 | tin |
24
25 +----------+
26
27 8 rows in set (0.00 sec)
28
29
30
31 #查询‘co’结尾的字符串
32
33 mysql> SELECT name FROM metal WHERE name LIKE 'co%';
34
35 +--------+
36
37 | name |
38
39 +--------+
40
41 | copper |
42
43 +--------+
44
45 1 row in set (0.00 sec)
46
47
48
49 #查询‘er’开始的字符串
50
51 mysql> SELECT name FROM metal WHERE name LIKE '%er';
52
53 +--------+
54
55 | name |
56
57 +--------+
58
59 | copper |
60
61 | silver |
62
63 +--------+
64
65 2 rows in set (0.00 sec)
66
67
68
69 #包含’er’ 字符串的
70
71 mysql> SELECT name FROM metal WHERE name LIKE '%er%';
72
73 +---------+
74
75 | name |
76
77 +---------+
78
79 | copper |
80
81 | mercury |
82
83 | silver |
84
85 +---------+
86
87 3 rows in set (0.00 sec)
88
89
90
91
92
93 #查询特定位置
94
95 mysql> SELECT name FROM metal WHERE name LIKE '___per%';
96
97 +--------+
98
99 | name |
100
101 +--------+
102
103 | copper |
104
105 +--------+
106
107 1 row in set (0.00 sec)
108
109
110
111 # NOT LIKE 相反查询
112
113 #不含有 i 字符
114
115 mysql> SELECT name FROM metal WHERE name NOT LIKE '%i%';
116
117 +---------+
118
119 | name |
120
121 +---------+
122
123 | copper |
124
125 | gold |
126
127 | lead |
128
129 | mercury |
130
131 +---------+
132
133 4 rows in set (0.00 sec)
134
135
2
3 +----------+
4
5 | name |
6
7 +----------+
8
9 | copper |
10
11 | gold |
12
13 | iron |
14
15 | lead |
16
17 | mercury |
18
19 | platinum |
20
21 | silver |
22
23 | tin |
24
25 +----------+
26
27 8 rows in set (0.00 sec)
28
29
30
31 #查询‘co’结尾的字符串
32
33 mysql> SELECT name FROM metal WHERE name LIKE 'co%';
34
35 +--------+
36
37 | name |
38
39 +--------+
40
41 | copper |
42
43 +--------+
44
45 1 row in set (0.00 sec)
46
47
48
49 #查询‘er’开始的字符串
50
51 mysql> SELECT name FROM metal WHERE name LIKE '%er';
52
53 +--------+
54
55 | name |
56
57 +--------+
58
59 | copper |
60
61 | silver |
62
63 +--------+
64
65 2 rows in set (0.00 sec)
66
67
68
69 #包含’er’ 字符串的
70
71 mysql> SELECT name FROM metal WHERE name LIKE '%er%';
72
73 +---------+
74
75 | name |
76
77 +---------+
78
79 | copper |
80
81 | mercury |
82
83 | silver |
84
85 +---------+
86
87 3 rows in set (0.00 sec)
88
89
90
91
92
93 #查询特定位置
94
95 mysql> SELECT name FROM metal WHERE name LIKE '___per%';
96
97 +--------+
98
99 | name |
100
101 +--------+
102
103 | copper |
104
105 +--------+
106
107 1 row in set (0.00 sec)
108
109
110
111 # NOT LIKE 相反查询
112
113 #不含有 i 字符
114
115 mysql> SELECT name FROM metal WHERE name NOT LIKE '%i%';
116
117 +---------+
118
119 | name |
120
121 +---------+
122
123 | copper |
124
125 | gold |
126
127 | lead |
128
129 | mercury |
130
131 +---------+
132
133 4 rows in set (0.00 sec)
134
135
正则表达式匹配
代码
#以 ‘co’开始
mysql> SELECT name FROM metal WHERE name REGEXP '^co';
+--------+
| name |
+--------+
| copper |
+--------+
1 row in set (0.00 sec)
#以 ‘er’ 结束
mysql> SELECT name FROM metal WHERE name REGEXP 'er$';
+--------+
| name |
+--------+
| copper |
| silver |
+--------+
2 rows in set (0.00 sec)
#包含 ‘er’
mysql> SELECT name FROM metal WHERE name REGEXP 'er';
+---------+
| name |
+---------+
| copper |
| mercury |
| silver |
+---------+
3 rows in set (0.00 sec)
#以 ‘aeiou’中的一个字符开头，或者以 ‘er’结尾的字符串
mysql> SELECT name FROM metal WHERE name REGEXP '^[aeiou]|er$';
+--------+
| name |
+--------+
| copper |
| iron |
| silver |
+--------+
3 rows in set (0.00 sec)
正则表达式只能用于单字符集，对于UTF8或者sjis双字符字符集不起作用
mysql> SELECT name FROM metal WHERE name REGEXP '^co';
+--------+
| name |
+--------+
| copper |
+--------+
1 row in set (0.00 sec)
#以 ‘er’ 结束
mysql> SELECT name FROM metal WHERE name REGEXP 'er$';
+--------+
| name |
+--------+
| copper |
| silver |
+--------+
2 rows in set (0.00 sec)
#包含 ‘er’
mysql> SELECT name FROM metal WHERE name REGEXP 'er';
+---------+
| name |
+---------+
| copper |
| mercury |
| silver |
+---------+
3 rows in set (0.00 sec)
#以 ‘aeiou’中的一个字符开头，或者以 ‘er’结尾的字符串
mysql> SELECT name FROM metal WHERE name REGEXP '^[aeiou]|er$';
+--------+
| name |
+--------+
| copper |
| iron |
| silver |
+--------+
3 rows in set (0.00 sec)
正则表达式只能用于单字符集，对于UTF8或者sjis双字符字符集不起作用