如果您不想在两者之间包含结束值,请使用以下语法-SELECT * FROM yourTableName WHERE yourColumnName BETWEEN yourStartingValue and yourEndingValue and
yourColumnName not in (yourEndingValue );
为了理解上述语法,让我们创建一个表。创建表的查询如下-mysql> create table BetweenWithoutEndPoints
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> Name varchar(20),
-> Age int,
-> PRIMARY KEY(Id)
-> );
现在,您可以使用insert命令在表中插入一些记录。查询如下-mysql> insert into BetweenWithoutEndPoints(Name,Age) values('Mike',23);
mysql> insert into BetweenWithoutEndPoints(Name,Age) values('Larry',25);
mysql> insert into BetweenWithoutEndPoints(Name,Age) values('David',28);
mysql> insert into BetweenWithoutEndPoints(Name,Age) values('Sam',26);
mysql> insert into BetweenWithoutEndPoints(Name,Age) values('Carol',21);
mysql> insert into BetweenWithoutEndPoints(Name,Age) values('John',29);
mysql> insert into BetweenWithoutEndPoints(Name,Age) values('James',20);
mysql> insert into BetweenWithoutEndPoints(Name,Age) values('Robert',26);
mysql> insert into BetweenWithoutEndPoints(Name,Age) values('Michael',30);
使用select语句显示表中的所有记录。查询如下-mysql> select *from BetweenWithoutEndPoints;
以下是输出:+----+---------+------+
| Id | Name | Age |
+----+---------+------+
| 1 | Mike | 23 |
| 2 | Larry | 25 |
| 3 | David | 28 |
| 4 | Sam | 26 |
| 5 | Carol | 21 |
| 6 | John | 29 |
| 7 | James | 20 |
| 8 | Robert | 26 |
| 9 | Michael | 30 |
+----+---------+------+
9 rows in set (0.00 sec)
这是没有获得起点和终点之间的MySQL查询。以下查询不包含3和8:mysql> select *from BetweenWithoutEndPoints
-> where Id between 3 and 8 and Id not in (3, 8);
以下是输出:+----+-------+------+
| Id | Name | Age |
+----+-------+------+
| 4 | Sam | 26 |
| 5 | Carol | 21 |
| 6 | John | 29 |
| 7 | James | 20 |
+----+-------+------+
4 rows in set (0.04 sec)
如果不想只包含终点,请使用以下查询:mysql> select *from BetweenWithoutEndPoints
-> where Id between 3 and 8 and Id not in (8);
以下是输出:+----+-------+------+
| Id | Name | Age |
+----+-------+------+
| 3 | David | 28 |
| 4 | Sam | 26 |
| 5 | Carol | 21 |
| 6 | John | 29 |
| 7 | James | 20 |
+----+-------+------+
5 rows in set (0.03 sec)