MorkaLork Development

Interesting stuff I've picked up over the years...

Mysql 7: WHERE

2009-04-16 21:15:08 | 152 views | sql mysql table where search

WHERE:


If we want to pick out a certain record or a record with a certain attribute the SQL comes with a method to do so, by using the keyword WHERE.


This will be our table 'person':

|--------------------+--------------------+--------------------|
|Name |Age |Gender |
|____________________+____________________+____________________|
|Bob 34 Male |
|Silvia 67 Female |
|Magnus 24 Male |
|Lisa 32 Female |
+--------------------------------------------------------------+


Syntax:

SELECT column
FROM table_name
WHERE condition


The condition we'll start by searching for all male records.

SELECT * 

FROM person
WHERE Gender='Male';



|--------------------+--------------------+--------------------|
|Name |Age |Gender |
|____________________+____________________+____________________|
|Bob 34 Male |
|Magnus 24 Male |
+--------------------------------------------------------------+


Now we have picked out only those records with persons having a male gender. You can use the keywords AND and OR as well.

SELECT * 

FROM person
WHERE Gender='Male'
AND Age<30;


|--------------------+--------------------+--------------------|
|Name |Age |Gender |
|____________________+____________________+____________________|
|Magnus 24 Male |
+--------------------------------------------------------------+


We can use both statements at the same time. AND will however go before OR, such as this:
('statement' AND 'statement') OR 'statement'

Just as in math, we can show what will be handled first. The AND statements can be shown above within paranthesis to illustrate how they are handled before the OR statement.

Syntax:

SELECT column
FROM table_name
WHERE condition
AND condition
OR condition


Example:


SELECT *
FROM person
WHERE Gender='Male'
AND Age<30
OR Gender='Female'


|--------------------+--------------------+--------------------|
|Name |Age |Gender |
|____________________+____________________+____________________|
|Silvia 67 Female |
|Magnus 24 Male |
|Lisa 32 Female |
+--------------------------------------------------------------+


As we can see here, we want either all records that contain a Male person under 30 years of age, or is a female. If you are female, there are no other conditions to be met. We could add more conditions if we want to, but this example is getting quite long now.



Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.