Geek Answers Handbook

How to select a specific range of values ​​in a MySQL table?

Below is my MySQL table. I want to select a specific range of values ​​from my hello table.

 name age job gender A 33 dgfd m b 44 gdfg f c 21 jhkh m e 23 etertr m

How would I choose a man whose age falls into the age category of 20-30 years.

 SELECT hello.* WHERE hello.age='20-30' AND hello.gender='m';
+4
source share
4 answers

You can use the WHERE to filter data:

 select name, age, job, gender from hello where age >=20 and age <=30 and gender = 'm'

See SQL Fiddle with Demo

This can also be written using BETWEEN :

 select name, age, job, gender from hello where age between 20 and 30 and gender = 'm'

See SQL Fiddle with Demo .

Usually you need to save the date of birth instead of the person’s age , then age can be calculated when necessary.

+13
source 
 SELECT name FROM hello WHERE age BETWEEN 20 AND 30 AND gender = 'm'

Do not store age . Save the date field and calculate the age. What happens if a person gets older?

+11
source

SELECT * FROM hello WHERE age>=20 AND age <=30 AND gender='m';

0
source

Because age changes from year to year, you can do it.

Set the table as follows:

 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$$

The values ​​I use are:

 '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:

 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;

Query Responses Returned

 name age job gender C 20 pilot m d 22 unemployed m

Added to SQLFiddle here. http://www.sqlfiddle.com/#!2/0143c/1/0

0
source

More articles:


All Articles