MySQL Select Statements

The SELECT statement is used to select or fetch data from a your MySQL Database. It’s the most common type of query issued to a database. There are many ways to query a database, but were going to focus on the SELECT statement itself and the many different things you can do with it.

Below, you are seeing every aspect of the SELECT statement, but don’t get scared. It’s not that complicated and this MySQL Tutorial is going to simplify it.  Half of the options below you will most likely never use in a real world scenario and everything with in brackets “[ ]” are optional.

SELECT
 [ALL | DISTINCT | DISTINCTROW ]
 [HIGH_PRIORITY]
 [STRAIGHT_JOIN]
 [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
 [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
 select_expr [, select_expr ...]
 [FROM table_references
 [WHERE where_condition]
 [GROUP BY {col_name | expr | position}
 [ASC | DESC], ... [WITH ROLLUP]]
 [HAVING where_condition]
 [ORDER BY {col_name | expr | position}
 [ASC | DESC], ...]
 [LIMIT {[offset,] row_count | row_count OFFSET offset}]
 [PROCEDURE procedure_name(argument_list)]
 [INTO OUTFILE 'file_name' export_options
 | INTO DUMPFILE 'file_name'
 | INTO var_name [, var_name]]
 [FOR UPDATE | LOCK IN SHARE MODE]]

This tutorial will cover basic SELECT statements to the more advanced SELECT Statements to show you the power of MySQL.

Before we can write a SELECT statement we need database tables and some data to perform the SELECT query against.  Please see the image below for a simple database design, which are actual tables used at unlocktheinbox.com to maintain their sitemaps.  This is a real world example.

In the image above we have two database tables we are going to be work working with, sitemap_categories and sitemap_info. Sitemap_categories has a zero to many relationship with sitemap_info. Which means the categories in sitemap_categories can appear 0 to many times in the sitemap_info table. You can read more about database relationships here:

Now that we have a relational database consisting of two tables, we now need some sample data to perform queries against.  Below we are going to work with a sub-set of ata found at Unlock The Inbox Sitemaps to display this pre-populated data we use one of the most common SELECT statement as seen below.  The “*” stands for “all columns”, so the statement translated means “Select all columns from the table named sitemap_categories” in the MySQL database without any type of restrictions.

Select * from sitemap_categories;

Since there was no defined restrictions on the “SELECT” Query all the data was returned which is listed above, as you can see they have 6 different categories used to populate their sitemap.

Now we want to see all the different URL’s they are using, so we will do a similar query to pull back all the information in the sitemap_info table

select * from sitemap_info;

As you can see they have a total of 15 different URL’s in their sitemap (This is just a sub-set of the original data) to make it easy for us to work with.

Now that we know how to do a simple “SELECT *” query, lets get a little more advanced.

If you want to know how many categories are in the sitemap_categories table you will use the count command. Both commands below will return the same results, but it’s best practice to do the count on the primary key of the table and to name the column.

select count(sm_category_id) from sitemap_categories;
select count(*) from sitemap_categories;

The only difference between the two queries besides speed optimization is the name of the column. Notice how the column is named count(sm_category_id) above? The second query would name the column count(*). When you do functions in MySQL the column names are the function names, but with MySQL you can change that really fast by adding a column alias as seen below.

select count(sm_category_id) as category_total 
from sitemap_categories;

You can make up columns on the fly in MySQL, they don’t have to be in a table, for example

select count(sm_category_id) as category_total,'phphelp.com' as website, 1+1 as number
from sitemap_categories;

As you can see you can do a lot with just SELECT portion of a SELECT Statement, earlier we spoke about restricting the data you want to retrieve. That’s what’s the WHERE clause is for. For example:

select * from sitemap_categories
where sm_category_id in (1,3,5) ;

After running the query, you can see how we limited the data coming back to 3 distinct rows by using the WHERE statement by restricting the sm_category_id column to 3 values using the IN function.

In the where clause you can do many different operations to restrict data. For example:

select * from sitemap_categories
where sm_category_id > 3 and SM_Category_name like 'S%';

In this example, we are telling MySQL to give me back all “*” the data where sm_category_id is greater then three and where the sm_category_name starts with “S”. As you can see we can build the where clause to restrict data in a number of different ways.

The next most common functionality in a SELECT statement is the GROUP BY Clause.  GROUP BY is commonly used with a SUM or a COUNT statement

select sm_category_id, Count(sm_category_id) as count from sitemap_info
group by sm_category_id;

In the query above we are asking MySQL to tell us how many items in each category that we have.

As you can see above, sm_category_id of 1 has a total 5 Page URLS.

A lot of times, we want to order the data that comes back from MySQL to make it easier to work with, so were use the ORDER BY Command and the direction we want to order it by ASCending or DESCending. You can order by single or multiple columns. For Example:

select * from sitemap_categories
order by sm_category_id desc

In the query above, we are asking MySQL to show us all the data in sitemap_categories and order the sm_categories_id from highest to lowest as seen below.

Before this point, we have been using just a single table to do our queries against, now lets join two tables together and query them.

select a.*, b.page_name from sitemap_categories a,
sitemap_info b
where a.sm_category_id = b.sm_category_id

In the above query, we are renaming or aliasing the sitemap_categories to the letter “a” and sitemap_info to “b” to make things easier to work with. When you query multiple tables you are joining them together. In the example, above we are doing in INNER JOIN and joining the tables on the sm_category_id as seen in the WHERE clause. Then we are selecting all columns from table “a” and just the page_name from table “b”. And the results are seen below.