1、AND&OR操作符
筛选出sex=m且品种为猫或狗的记录：
mysql> select * from pet; +----------+-------------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------------+---------+------+------------+------------+ | Puffball | Diane | hamster | f | 2000-09-22 | NULL | | Cuihua | ZhouXingChi | DOG | M | 1999-09-21 | NULL | | DNN | Huimin | Cat | f | 2018-07-07 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1909-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1989-09-01 | NULL | | Slim | Benny | snake | m | 2006-04-21 | NULL | | Puffball | Diane | hamster | f | 2009-10-12 | NULL | +----------+-------------+---------+------+------------+------------+ 10 rows in set (0.00 sec) mysql> select * from pet where species='cat' or species='dog' and sex='m'; +--------+-------------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------------+---------+------+------------+------------+ | Cuihua | ZhouXingChi | DOG | M | 1999-09-21 | NULL | | DNN | Huimin | Cat | f | 2018-07-07 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Fang | Benny | dog | m | 1909-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | +--------+-------------+---------+------+------------+------------+ 5 rows in set (0.00 sec) mysql> select * from pet where (species='cat' or species='dog') and sex='m'; +--------+-------------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------------+---------+------+------------+------------+ | Cuihua | ZhouXingChi | DOG | M | 1999-09-21 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Fang | Benny | dog | m | 1909-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | +--------+-------------+---------+------+------------+------------+
WHERE可包含任意数目的AND和OR操作符，允许两者结合以进行复杂和高级的过滤。
AND操作符的优先级大于OR操作符，在使用具有AND和OR操作符的WHERE子句都因该使用圆括号明确的分组。
使用IN操作符，与OR相同的功能：
mysql> select * from pet where species in ('dog','cat') and sex='m'; +--------+-------------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------------+---------+------+------------+------------+ | Cuihua | ZhouXingChi | DOG | M | 1999-09-21 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Fang | Benny | dog | m | 1909-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | +--------+-------------+---------+------+------------+------------+
使用IN操作符的优点：
1.使用更长的合法选项清单时，更清楚直观；
2.计算的次序更容易管理（使用的操作符更少）；
3.IN操作符比OR操作符清单执行更快；
4.可以包含其他SELECT语句，更动态的建立WHERE子句。
NOT与IN连用：
mysql> select * from pet where species not in ('dog','cat') and sex='m'; +------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +------+-------+---------+------+------------+-------+ | Slim | Benny | snake | m | 2006-04-21 | NULL | +------+-------+---------+------+------------+-------+
2、通配符
%可匹配0个、1个、多个字符，_只能匹配1个字符。
使用通配符时需记住：
1.不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符；
2.在确实需要使用通配符时，除非绝对有必要，否则绝不要把它们用在搜索模式的开始处。置于开始处，搜索起来时最慢的；
3.仔细注意通配符的位置，如果放错地方，可能不能返回想要的数据。