MySQL select from where multiple conditions

Maybe one of the most used MySQL commands is SELECT, that is the way to stract the information from the database, but of course one does not need all the info inside a database, therefore one should limit the info coming out from the table, there is WHERE statement comes into play, with it one can limit the data to only the one that complies with certain condition. What if that is still too wide?. Then it can be used multiple conditions. Here some options:

Working with two conditions

Using AND with two or more conditions the query can be narrowed to meet your needs.

SELECT * FROM table WHERE column1 = 'var1' AND column2 = 'var2';

Only when the two conditions are met the row is stracted from the database's table. What if any of them should be met to get the data?

SELECT * FROM table WHERE column1 = 'var1' OR column2 = 'var2';

Using OR will tell MySQL to return data if one or both conditions are met.

Working with more than two conditions

If more than two conditions need to be met in order to show a result, you need to use parenthesis and nest the conditions according to your needs. This time it will be easier with examples.

Consider this table:

| fname      | lname     | gender | age |
| John       | Smith     | M      | 30  |
| Jane       | Doe       | F      | 20  |
| Richard    | Stallman  | M      | 70  |
| John       | Doe       | M      | 20  |

If you want to get all young male's names use this query.

SELECT * FROM table WHERE gender = M AND age >=  '18' AND age <= '50';

If you want to get all young people with last name Doe or Smith, use this query.

SELECT * FROM table WHERE age >= '18' AND age <= '50' AND (lname = 'Doe' OR lname = 'Smith');

As you can see we are using parenthesis to get a result from last names, because you want one or the other, then you use AND to get the age range and finally an AND to join the results from age range and the results from names.

SELECT unique_id,COUNT(unique_id)
FROM yourtblname
GROUP BY unique_id
HAVING COUNT(unique_id) >1
posted @ 2019-09-26 16:09  FreePress  阅读(230)  评论(0编辑  收藏  举报