本文共 2841 字,大约阅读时间需要 9 分钟。
通配符(wildcard)
用来匹配值的一部分的特殊字符。
搜索模式(search pattern)
由字面值、通配符或两者组合构成的搜索条件。 通配符本身实际是SQL的WHERE子句中有特殊含义的字符,SQL支持几种通配符。
%表示任何字符出现任意次数。
MariaDB [test]> insert into linux value('user11','1111','girl',31);Query OK, 1 row affected (0.02 sec)MariaDB [test]> insert into linux value('user111','111111','boy',41);Query OK, 1 row affected (0.00 sec)MariaDB [test]> select * from linux;+---------+--------+------+------+| user | passwd | sex | age |+---------+--------+------+------+| user1 | 111 | boy | 18 || user2 | 222 | girl | 23 || user3 | 333 | boy | 12 || user4 | 444 | boy | 22 || user5 | 555 | boy | 30 || a | passa | boy | 22 || 1 | pass1 | girl | 30 || user1 | pass1 | girl | 18 || user11 | 1111 | girl | 31 || user111 | 111111 | boy | 41 |+---------+--------+------+------+10 rows in set (0.00 sec)MariaDB [test]> select user from linux WHERE user LIKE 'user%';+---------+| user |+---------+| user1 || user2 || user3 || user4 || user5 || user1 || user11 || user111 |+---------+8 rows in set (0.00 sec)
在模式的两端使用两个通配符:
MariaDB [test]> select * from linux WHERE passwd LIKE '%ss%';+-------+--------+------+------+| user | passwd | sex | age |+-------+--------+------+------+| a | passa | boy | 22 || 1 | pass1 | girl | 30 || user1 | pass1 | girl | 18 |+-------+--------+------+------+3 rows in set (0.00 sec)
还可以在中间使用:
MariaDB [test]> select * from linux WHERE user LIKE 'u%1';+---------+--------+------+------+| user | passwd | sex | age |+---------+--------+------+------+| user1 | 111 | boy | 18 || user1 | pass1 | girl | 18 || user11 | 1111 | girl | 31 || user111 | 111111 | boy | 41 |+---------+--------+------+------+4 rows in set (0.00 sec)
重要的是要注意到,除了一个或多个字符外,%还能匹配0个字符
。%代表搜索模式中给定位置的0个、1个或多个字符。
注意: 注意尾空格 尾空格可能会干扰通配符匹配。 注意NULL ,’%’ 不能匹配用值NULL作为产品名的行。
下划线只匹配单个字符而不是多个字符。
MariaDB [test]> select * from linux WHERE user LIKE 'user_';+-------+--------+------+------+| user | passwd | sex | age |+-------+--------+------+------+| user1 | 111 | boy | 18 || user2 | 222 | girl | 23 || user3 | 333 | boy | 12 || user4 | 444 | boy | 22 || user5 | 555 | boy | 30 || user1 | pass1 | girl | 18 |+-------+--------+------+------+6 rows in set (0.00 sec)MariaDB [test]> select * from linux WHERE user LIKE 'user__';+--------+--------+------+------+| user | passwd | sex | age |+--------+--------+------+------+| user11 | 1111 | girl | 31 |+--------+--------+------+------+1 row in set (0.00 sec)MariaDB [test]> select * from linux WHERE user LIKE 'user___';+---------+--------+-----+------+| user | passwd | sex | age |+---------+--------+-----+------+| user111 | 111111 | boy | 41 |+---------+--------+-----+------+1 row in set (0.00 sec)
转载地址:http://euxli.baihongyu.com/