mysql rows 16-21

If I have a table: ID开发者_如何学C, num,varchar

where ID is an integer, either 1,2 or 3. then num is a number, counting from 1 to 100. varchar is just some text.

So in total we have 300 rows, in no particular ordering in this table.

What query can I use to get the rows with ID=2 and num from 16-21 out of this table?

(resulting in 6 rows total)

How about

SELECT * from yourtable where ID = 2 AND num >= 16 AND num <= 21

Or, equivalent using BETWEEN

SELECT * from yourtable where ID = 2 AND num BETWEEN 16 AND 21

Create an index to have faster lookups later (but will slow down your inserts a bit):

CREATE INDEX indexname on yourtable (ID,num);

SELECT * FROM TABLE WHERE ID = 2 AND NUM > 15 AND NUM < 22;

where TABLE is the name of your table. In general, given that you're selecting on columns ID and NUM they should probably be indexed for faster retrieval (ie the database doesn;t have to check every row). Although given your table is small it probably wont make much difference here.

This should do it:

SELECT * FROM table WHERE id = 2 AND num > 15 AND num < 22