BETWEEN 操作符

操作符 BETWEEN ... AND 會選取介于兩個值之間的數據范圍。這

些值可以是數值、文本或者日期。

SQL BETWEEN 語法

SELECT FROM WHERE (BETWEEN value1 AND value2

mysql教程>

mysql> CREATE TABLE Employee(

->???? id??????????? int,

->???? first_name??? VARCHAR(15),

->???? last_name???? VARCHAR(15),

->???? start_date??? DATE,

->???? end_date????? DATE,

->???? salary??????? FLOAT(8,2),

->???? city????????? VARCHAR(10),

->???? description?? VARCHAR(15)

-> );

Query OK, 0 rows affected (0.05 sec)

mysql>

mysql>

mysql> insert into Employee(id,first_name, last_name,

start_date, end_Date,?? salary,? City,?????? Description)

->????????????? values (1,'Jason',??? 'Martin',

'19960725',? '20060725', 1234.56, 'Toronto',

'Programmer');

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> insert into Employee(id,first_name, last_name,

start_date, end_Date,?? salary,? City,?????? Description)

->?????????????? values(2,'Alison',?? 'Mathews',

'19760321', '19860221', 6661.78, 'Vancouver','Tester');

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> insert into Employee(id,first_name, last_name,

start_date, end_Date,?? salary,? City,?????? Description)

->?????????????? values(3,'James',??? 'Smith',

'19781212', '19900315', 6544.78, 'Vancouver','Tester');

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> insert into Employee(id,first_name, last_name,

start_date, end_Date,?? salary,? City,?????? Description)

->?????????????? values(4,'Celia',??? 'Rice',

'19821024', '19990421', 2344.78, 'Vancouver','Manager');

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> insert into Employee(id,first_name, last_name,

start_date, end_Date,?? salary,? City,?????? Description)

->?????????????? values(5,'Robert',?? 'Black',

'19840115', '19980808', 2334.78,

'www.111com.net','Tester');

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> insert into Employee(id,first_name, last_name,

start_date, end_Date,?? salary,? City,?????? Description)

->?????????????? values(6,'Linda',??? 'Green',

'19870730', '19960104', 4322.78,'New York',? 'Tester');

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> insert into Employee(id,first_name, last_name,

start_date, end_Date,?? salary,? City,?????? Description)

->?????????????? values(7,'David',??? 'Larry',

'19901231', '19980212', 7897.78,'New York',? 'Manager');

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> insert into Employee(id,first_name, last_name,

start_date, end_Date,?? salary,? City,?????? Description)

->?????????????? values(8,'James',??? 'Cat',

'19960917',? '20020415', 1232.78,'Vancouver', 'Tester');

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> select * from Employee;

+------+------------+-----------+------------+-----------

-+---------+-----------+-------------+

| id?? | first_name | last_name | start_date | end_date

| salary? | city????? | description |

+------+------------+-----------+------------+-----------

-+---------+-----------+-------------+

|??? 1 | Jason????? | Martin??? | 1996-07-25 | 2006-07-25

| 1234.56 | Toronto?? | Programmer? |

|??? 2 | Alison???? | Mathews?? | 1976-03-21 | 1986-02-21

| 6661.78 | Vancouver | Tester????? |

|??? 3 | James????? | Smith???? | 1978-12-12 | 1990-03-15

| 6544.78 | Vancouver | Tester????? |

|??? 4 | Celia????? | Rice????? | 1982-10-24 | 1999-04-21

| 2344.78 | Vancouver | Manager???? |

|??? 5 | Robert???? | Black???? | 1984-01-15 | 1998-08-08

| 2334.78 | Vancouver | Tester????? |

|??? 6 | Linda????? | Green???? | 1987-07-30 | 1996-01-04

| 4322.78 | New York? | Tester????? |

|??? 7 | David????? | Larry???? | 1990-12-31 | 1998-02-12

| 7897.78 | New York? | Manager???? |

|??? 8 | James????? | Cat?????? | 1996-09-17 | 2002-04-15

| 1232.78 | Vancouver | Tester????? |

+------+------------+-----------+------------+-----------

-+---------+-----------+-------------+

8 rows in set (0.00 sec)

mysql>

mysql>

mysql>

mysql> SELECT * FROM employee

-> WHERE (employee.salary BETWEEN 1000 AND 2000);

+------+------------+-----------+------------+-----------

-+---------+-----------+-------------+

| id?? | first_name | last_name | start_date | end_date

| salary? | city????? | description |

+------+------------+-----------+------------+-----------

-+---------+-----------+-------------+

|??? 1 | Jason????? | Martin??? | 1996-07-25 | 2006-07-25

| 1234.56 | Toronto?? | Programmer? |

|??? 8 | James????? | Cat?????? | 1996-09-17 | 2002-04-15

| 1232.78 | Vancouver | Tester????? |

+------+------------+-----------+------------+-----------

-+---------+-----------+-------------+

2 rows in set (0.02 sec)

在兩個字符串中間的記錄

mysql> SELECT * FROM employee

-> WHERE (employee.first_name BETWEEN 'Elvis' AND

'Michael');

+------+------------+-----------+------------+-----------

-+---------+-----------+-------------+

| id?? | first_name | last_name | start_date | end_date

| salary? | city????? | description |

+------+------------+-----------+------------+-----------

-+---------+-----------+-------------+

|??? 1 | Jason????? | Martin??? | 1996-07-25 | 2006-07-25

| 1234.56 | Toronto?? | Programmer? |

|??? 3 | James????? | Smith???? | 1978-12-12 | 1990-03-15

| 6544.78 | Vancouver | Tester????? |

|??? 6 | Linda????? | Green???? | 1987-07-30 | 1996-01-04

| 4322.78 | New York? | Tester????? |

|??? 8 | James????? | Cat?????? | 1996-09-17 | 2002-04-15

| 1232.78 | Vancouver | Tester????? |

+------+------------+-----------+------------+-----------

-+---------+-----------+-------------+

如果要判斷不在兩者之間用not between x and y 就KO了，看個簡

單的查詢語句

mysql> SELECT Name, InStock, OnOrder, Reserved

-> FROM Topic

-> WHERE (InStock+OnOrder-Reserved) NOT BETWEEN 10 AND

20

-> ORDER BY Name;

重要事項：不同的數據庫教程對 BETWEEN...AND 操作符的處理方式是

有差異的。某些數據庫會列出介于 "Adams" 和 "Carter" 之間的

人，但不包括 "Adams" 和 "Carter" ；某些數據庫會列出介于

"Adams" 和 "Carter" 之間并包括 "Adams" 和 "Carter" 的人；

而另一些數據庫會列出介于 "Adams" 和 "Carter" 之間的人，包

括 "Adams" ，但不包括 "Carter"

總結

以上是生活随笔為你收集整理的mysql两者之间_sql between 语法与查询两者之间数据方法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。