MySQL Tutorial: Making Select Statements

The select keyword, returns the values of the fields or columns found in your database table. You would use this if you want to select - view, list... - the contents of your table.

Basic Structure

SELECT field_name FROM table_name;

• field_name refers to a column in your database table
• table_name is the name of your database table :D
• Description: This would select all values under the field_name of your table

Examples

To make a better visualization let us use this table from Jim's inventory for his fruit store. The table contains the kind of fruit and amount.

fruit_stock
---------------------------
| fruit ........| amount |
---------------------------
| apple......| 12......... |
---------------------------
| banana..| 5............ |
---------------------------
| grapes...| 7............ |
---------------------------

For this example, the table_name is fruit_stock and the fields are fruit and amount.

Let us say we would like to view all the fruits Jim has in his stock. We could do it this way:

>>SELECT fruit FROM fruit_stock;

In this select statement, we used the field_name fruit so that we could get a list containing all the fruits in Jim's inventory. The results are as follows:

fruit
------------
apple
------------
banana
------------
grapes
------------

Now what if we would like to see all the amount and not the fruit? Just simply replace fruit with amount. But what if we would like to see both the fruit and its amount? All that needs to be done is to add all the fields of the table and separate each with a comma ( , ) like so:

>>SELECT fruit, amount FROM fruit_stock;

Result:
---------------------------
| fruit ........| amount |
---------------------------
| apple......| 12......... |
---------------------------
| banana..| 5............ |
---------------------------
| grapes...| 7............ |
---------------------------

But things would get really hard if our table contains lots of columns and typing in each field would be hard. And so an easier way is to make use of the * symbol, meaning, all.

>>SELECT * FROM fruit_stock;

This would result to the same one as putting in all the field_names in the query.

Now lets us add a little bit more control on what results we would like to have. How about viewing a specific fruit and its amount. This could be done by adding the WHERE clause. The WHERE clause simply needs a condition to limit the result.

For example, we would like to know how many grapes Jim has in his stock. This could be done by

>>SELECT * FROM fruit_stock WHERE fruit = 'grapes';

The result would then be:

-----------------------------
| fruit ..........| amount |
-----------------------------
| grapes......| 7........... |
-----------------------------

In using the WHERE clause, simply specify the field_name containing the value you would like to search - the condition, in this case, the fruit must be a grape. But what if we used the condition fruit = 'orange'? Well, the result will be empty since the table fruit_stock does not contain any oranges. Now this could be really helpful in checking users logging into their account - does this user exist in my table? is he registered? - just to give a few examples. (:

And here's one more thing before we end this tutorial, what if we would like to know how many fruits Jim has in his stock? We simply use COUNT(*) - count all the rows - all, because of the symbol * - of the table like this:

>>SELECT COUNT(*) FROMfruit_stock;

And the result:

COUNT(*)
--------------
3
--------------

So there, this is just the basic stuff but I do hope this would help you get a start in learning to make database queries. Cheers!