MySQL BETWEEN 用法与实例
MySQL BETWEEN 用法与实例
本文介绍了在 MySQL 数据库中，如何使用 BETWEEN 运算符确定一个值是否介于某两个值之间。
BETWEEN 运算符确定一个值是否介于某两个值之间。BETWEEN 运算符常用于比较数字和日期类型的数据。
MySQL BETWEEN 语法
BETWEEN 运算符是一个三目运算符，它需要 3 个操作数。下面 BETWEEN 运算符的语法：
expression BETWEEN min AND max expression NOT BETWEEN min AND max
使用说明：
expression可以是一个字段名、值或其他的表达式（比如函数调用、运算等）。min是范围的最小值（包含）。max是范围的最大值（包含）。AND是一个固定的连接词，用来连接min和max。NOT是否定运算符，NOT BETWEEN表达的含义与BETWEEN相反。
BETWEEN 运算符相当于是下面写法的简写形式：
expression >= min AND expression <= max
NOT BETWEEN 运算符相当于是下面写法的简写形式：
expression < min OR expression > max
MySQL BETWEEN 运算规则
当字段的值或者给定的值介于 min 和 max 两个值之间时，BETWEEN 运算符返回 1。否则，返回 0。
BETWEEN 运算规则如下：
如果左侧的
expression介于min和max之间时，BETWEEN返回1，否则返回0。SELECT 1 BETWEEN 1 AND 3, 2 BETWEEN 1 AND 3, 3 BETWEEN 1 AND 3, 4 BETWEEN 1 AND 3;
+-------------------+-------------------+-------------------+-------------------+ | 1 BETWEEN 1 AND 3 | 2 BETWEEN 1 AND 3 | 3 BETWEEN 1 AND 3 | 4 BETWEEN 1 AND 3 | +-------------------+-------------------+-------------------+-------------------+ | 1 | 1 | 1 | 0 | +-------------------+-------------------+-------------------+-------------------+
如果
expression,min,max至少有一个NULL时，BETWEEN运算符的返回NULL。SELECT NULL BETWEEN 1 AND 3, 1 BETWEEN NULL AND 3, 1 BETWEEN 1 AND NULL;
+----------------------+----------------------+----------------------+ | NULL BETWEEN 1 AND 3 | 1 BETWEEN NULL AND 3 | 1 BETWEEN 1 AND NULL | +----------------------+----------------------+----------------------+ | NULL | NULL | NULL | +----------------------+----------------------+----------------------+
MySQL BETWEEN 实例
BETWEEN 运算符常用于数字和日期类型的数据之间的比较。我们看 1 个实际的例子。
在以下实例中，我们使用 Sakila 示例数据库中的 film 表作为演示。
BETWEEN
以下 SQL 语句使用 BETWEEN 运算符检索影片赔偿价格介于 1 和 10 之间的影片：
SELECT film_id, title, replacement_cost FROM film WHERE replacement_cost BETWEEN 1 AND 10;
+---------+------------------------+------------------+ | film_id | title | replacement_cost | +---------+------------------------+------------------+ | 23 | ANACONDA CONFESSIONS | 9.99 | | 150 | CIDER DESIRE | 9.99 | | 182 | CONTROL ANTHEM | 9.99 | | 203 | DAISY MENAGERIE | 9.99 | | 221 | DELIVERANCE MULHOLLAND | 9.99 | | 260 | DUDE BLINDNESS | 9.99 | | 272 | EDGE KISSING | 9.99 | | 281 | ENCINO ELF | 9.99 | | 299 | FACTORY DRAGON | 9.99 | | 307 | FELLOWSHIP AUTUMN | 9.99 | | 348 | GANDHI KWAI | 9.99 | | 389 | GUNFIGHTER MUSSOLINI | 9.99 | | 409 | HEARTBREAKERS BRIGHT | 9.99 | | 476 | JASON TRAP | 9.99 | | 501 | KISSING DOLLS | 9.99 | | 522 | LIFE TWISTED | 9.99 | | 551 | MAIDEN HOME | 9.99 | | 565 | MATRIX SNOWMAN | 9.99 | | 572 | METROPOLIS COMA | 9.99 | | 623 | NEWTON LABYRINTH | 9.99 | | 627 | NORTH TEQUILA | 9.99 | | 629 | NOTORIOUS REUNION | 9.99 | | 639 | OPPOSITE NECKLACE | 9.99 | | 656 | PAPI NECKLACE | 9.99 | | 662 | PATHS CONTROL | 9.99 | | 686 | PLUTO OLEANDER | 9.99 | | 705 | PURPLE MOVIE | 9.99 | | 747 | ROXANNE REBEL | 9.99 | | 841 | STAR OPERATION | 9.99 | | 845 | STEPMOM DREAM | 9.99 | | 846 | STING PERSONAL | 9.99 | | 863 | SUN CONFESSIONS | 9.99 | | 875 | TALENTED HOMICIDE | 9.99 | | 880 | TELEMARK HEARTBREAKERS | 9.99 | | 886 | THEORY MERMAID | 9.99 | | 888 | THIN SAGEBRUSH | 9.99 | | 912 | TROJAN TOMORROW | 9.99 | | 915 | TRUMAN CRAZY | 9.99 | | 931 | VALENTINE VANISHING | 9.99 | | 953 | WAIT CIDER | 9.99 | | 996 | YOUNG LANGUAGE | 9.99 | +---------+------------------------+------------------+
此 SQL 语句与下面不使用 BETWEEN 的语句的结果相同：
SELECT film_id, title, replacement_cost FROM film WHERE replacement_cost >= 1 AND replacement_cost <= 10;
NOT BETWEEN
以下 SQL 语句使用 BETWEEN 运算符检索影片赔偿价格不介于 1 和 10 之间的影片：
SELECT film_id, title, replacement_cost FROM film WHERE replacement_cost NOT BETWEEN 10 AND 50;
+---------+------------------------+------------------+ | film_id | title | replacement_cost | +---------+------------------------+------------------+ | 23 | ANACONDA CONFESSIONS | 9.99 | | 150 | CIDER DESIRE | 9.99 | | 182 | CONTROL ANTHEM | 9.99 | | 203 | DAISY MENAGERIE | 9.99 | | 221 | DELIVERANCE MULHOLLAND | 9.99 | | 260 | DUDE BLINDNESS | 9.99 | | 272 | EDGE KISSING | 9.99 | | 281 | ENCINO ELF | 9.99 | | 299 | FACTORY DRAGON | 9.99 | | 307 | FELLOWSHIP AUTUMN | 9.99 | | 348 | GANDHI KWAI | 9.99 | | 389 | GUNFIGHTER MUSSOLINI | 9.99 | | 409 | HEARTBREAKERS BRIGHT | 9.99 | | 476 | JASON TRAP | 9.99 | | 501 | KISSING DOLLS | 9.99 | | 522 | LIFE TWISTED | 9.99 | | 551 | MAIDEN HOME | 9.99 | | 565 | MATRIX SNOWMAN | 9.99 | | 572 | METROPOLIS COMA | 9.99 | | 623 | NEWTON LABYRINTH | 9.99 | | 627 | NORTH TEQUILA | 9.99 | | 629 | NOTORIOUS REUNION | 9.99 | | 639 | OPPOSITE NECKLACE | 9.99 | | 656 | PAPI NECKLACE | 9.99 | | 662 | PATHS CONTROL | 9.99 | | 686 | PLUTO OLEANDER | 9.99 | | 705 | PURPLE MOVIE | 9.99 | | 747 | ROXANNE REBEL | 9.99 | | 841 | STAR OPERATION | 9.99 | | 845 | STEPMOM DREAM | 9.99 | | 846 | STING PERSONAL | 9.99 | | 863 | SUN CONFESSIONS | 9.99 | | 875 | TALENTED HOMICIDE | 9.99 | | 880 | TELEMARK HEARTBREAKERS | 9.99 | | 886 | THEORY MERMAID | 9.99 | | 888 | THIN SAGEBRUSH | 9.99 | | 912 | TROJAN TOMORROW | 9.99 | | 915 | TRUMAN CRAZY | 9.99 | | 931 | VALENTINE VANISHING | 9.99 | | 953 | WAIT CIDER | 9.99 | | 996 | YOUNG LANGUAGE | 9.99 | +---------+------------------------+------------------+
此 SQL 语句与下面不使用 NOT BETWEEN 的语句的结果相同：
SELECT film_id, title, replacement_cost FROM film WHERE replacement_cost < 10 OR replacement_cost > 50;
结论
本文通过实例介绍了 MySQL 中 BETWEEN 运算符的语法和用法。 BETWEEN 运算符的用法要点如下：
BETWEEN运算符来测试一个值是否位于一个数值区间或者时间区间内。BETWEEN min AND max中min是区间的最小值，max是区间的最大值。expression BETWEEN min AND max与expression >= min AND expression <= max结果相同。BETWEEN运算符可以结合NOT运算符进行否定操作。expression NOT BETWEEN min AND max与expression < min OR expression > max结果相同。
