六、MySQL查询数据
在fruits表中，查询f_name字段以字母’b’开头的记录mysql> SELECT * FROM fruits WHERE f_name REGEXP '^b';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 |101 | blackberry | 10.20 |
| b2 |104 | berry | 7.60 |
| t1 |102 | banana | 10.30 |
+------+------+------------+---------+
3 rows in set (0.00 sec)
在fruits表中，查询f_name字段以“be”开头的记录
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^be';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b2 |104 | berry| 7.60 |
+------+------+--------+---------+
1 row in set (0.00 sec)
在fruits表中，查询f_name字段以字母’y’结尾的记录
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'y$';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 |101 | blackberry | 10.20 |
| b2 |104 | berry | 7.60 |
| c0 |101 | cherry | 3.20 |
| m2 |105 | xbabay | 2.60 |
+------+------+------------+---------+
4 rows in set (0.00 sec)
在fruits表中，查询f_name字段以字符串“rry”结尾的记录
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'rry$';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 |101 | blackberry | 10.20 |
| b2 |104 | berry | 7.60 |
| c0 |101 | cherry | 3.20 |
+------+------+------------+---------+
3 rows in set (0.00 sec)
在fruits表中，查询f_name字段值包含字母’a’与’g’且两个字母之间只有一个字母的记录
mysql>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 |
+------+------+--------+---------+
2 rows in set (0.00 sec)
在fruits表中，查询f_name字段值以字母’b’开头，且’b’后面出现字母’a’的记录
mysql> 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 |
+------+------+------------+---------+
3 rows in set (0.00 sec)
在fruits表中，查询f_name字段值以字母’b’开头，且’b’后面出现字母’a’至少一次的记录
mysql>SELECT * FROM fruits WHERE f_name REGEXP '^ba+';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| t1 |102 | banana | 10.30 |
+------+------+--------+---------+
1 row in set (0.00 sec)
在fruits表中，查询f_name字段值包含字符串“on”的记录
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on';
+------+------+---------+---------+
| f_id | s_id | f_name| f_price |
+------+------+---------+---------+
| bs2|105 | melon | 8.20 |
| l2 |104 | lemon | 6.40 |
| o2 |103 | coconut | 9.20 |
+------+------+---------+---------+
3 rows in set (0.00 sec)
在fruits表中，查询f_name字段值包含字符串“on”或者“ap”的记录
mysql> 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 |
+------+------+---------+---------+
6 rows in set (0.00 sec)
在fruits表中，使用LIKE运算符查询f_name字段值为“on”的记录
mysql>SELECT * FROM fruits WHERE f_name LIKE 'on';
Empty set (0.00 sec)
在fruits表中，查找f_name字段中包含字母’o’或者’t’的记录
mysql>SELECT * FROM fruits WHERE f_name REGEXP '';
+------+------+---------+---------+
| 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 |
+------+------+---------+---------+
7 rows in set (0.00 sec)
在fruits表，查询s_id字段中数值中包含4、5或者6的记录
mysql>SELECT * FROM fruits WHERE s_id REGEXP '';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b2 |104 | berry| 7.60 |
| bs2|105 | melon| 8.20 |
| l2 |104 | lemon| 6.40 |
| m1 |106 | mango| 15.60 |
| m2 |105 | xbabay | 2.60 |
| m3 |105 | xxtt | 11.60 |
+------+------+--------+---------+
6 rows in set (0.00 sec)
在fruits表中，查询f_id字段包含字母a~e和数字1~2以外的字符的记录
mysql>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 |
+------+------+---------+---------+
12 rows in set (0.00 sec)
在fruits表中，查询f_name字段值出现字母’x’至少2次的记录
mysql> 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 |
+------+------+--------+---------+
2 rows in set (0.00 sec)
在fruits表中，查询f_name字段值出现字符串“ba”最少1次，最多3次的记录
mysql>SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';
+------+------+---------+---------+
| f_id | s_id | f_name| f_price |
+------+------+---------+---------+
| m2 |105 | xbabay| 2.60 |
| t1 |102 | banana| 10.30 |
| t4 |107 | xbababa | 3.60 |
+------+------+---------+---------+
3 rows in set (0.00 sec)
页:
[1]