如何在 MySQL 表中选择特定范围的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15573959/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select particular range of values in MySQL table?
提问by Ameer
The following is my MySQL table. I want to select a particular range of values from my table hello.
以下是我的 MySQL 表。我想从我的表中选择特定范围的值hello。
name age job gender
A 33 dgfd m
b 44 gdfg f
c 21 jhkh m
e 23 etertr m
How would I select a male whose age falls into the age category of 20-30 years.
我将如何选择年龄属于 20-30 岁年龄段的男性。
SELECT hello.*
WHERE hello.age='20-30' AND hello.gender='m';
回答by Taryn
You can use a WHEREclause to filter the data:
您可以使用WHERE子句来过滤数据：
select name, age, job, gender
from hello
where age >=20
and age <=30
and gender = 'm'
This can also be written using BETWEEN:
这也可以使用BETWEEN：
select name, age, job, gender
from hello
where age between 20 and 30
and gender = 'm'
See SQL Fiddle with Demo.
Typically you will want to store a date of birth instead of the ageof a person, then the age can be calculated when needed.
通常，您需要存储出生日期而不是age人的出生日期，然后可以在需要时计算年龄。
回答by Kermit
SELECT name
FROM hello
WHERE age BETWEEN 20 AND 30
AND gender = 'm'
Don't store age. Store a date field and calculate the age. What would happen if the person got older?
不要存储age。存储日期字段并计算年龄。如果人老了会怎样？
回答by Niro
SELECT * FROM hello WHERE age>=20 AND age <=30 AND gender='m';
SELECT * FROM hello WHERE age>=20 AND age <=30 AND gender='m';
回答by Absolute?ER?
Because ages change from year-to-year you can do this.
因为年龄每年都在变化，所以你可以这样做。
Setup the table like this:
像这样设置表格：
delimiter $$
CREATE TABLE `hello` (
`name` varchar(45) NOT NULL,
`birthdate` date DEFAULT NULL,
`job` varchar(45) DEFAULT NULL,
`gender` enum('m','f') DEFAULT NULL,
PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8$$
Values I'm using:
我正在使用的值：
'A', '1980-08-04', 'clerk', 'm'
'B', '1969-10-12', 'waitress', 'f'
'C', '1992-09-16', 'pilot', 'm'
'd', '1991-02-21', 'unemployed', 'm'
SQL query:
SQL查询：
select name,TIMESTAMPDIFF(YEAR,birthdate,current_date) as age,job,gender from hello where birthdate > current_date - interval 30 YEAR and birthdate < current_date - interval 20 year;
Returned answers to the query
返回的查询答案
name age job gender
C 20 pilot m
d 22 unemployed m
Added to SQLFiddle here. http://www.sqlfiddle.com/#!2/0143c/1/0
在此处添加到 SQLFiddle。 http://www.sqlfiddle.com/#!2/0143c/1/0
