Selecting Data from Your MySQL Tables
See a sample chapter from latest edition of Sams Teach Yourself SQL in 24 Hours, 6th Edition Summarizing Data Results from a Query in SQL.
This chapter is from the book
This chapter is from the book
This chapter is from the book
You've finally populated your tables with some data; now it's time to learn how to get it back out! The SQL command for retrieving data is SELECT, and in this hour you'll learn how to use it to select everything in your table or just specific pieces of information from that table.
In this hour, you will learn
-
The SELECT command syntax
-
How to order and limit your results
-
How to use basic functions in SELECT expressions
-
How to use the WHERE clause
Using the SELECT Command
SELECT is the SQL command used to retrieve records. This command syntax can be totally simplistic or very complicated. As you become more comfortable with database programming, you will learn to enhance your SELECT statements, ultimately making your database do as much work as possible and not overworking your programming language of choice.
The most basic SELECT syntax looks like this:
SELECT expressions_and_columns FROM table_name [WHERE some_condition_is_true] [ORDER BY some_column [ASC | DESC]] [LIMIT offset, rows]
Start with the first line:
SELECT expressions_and_columns FROM table_name
One handy expression is the * symbol, which stands for "everything". So, to select "everything" (all rows, all columns) from the master_name table, your SQL statement would be
SELECT * FROM master_name;
Depending on how much data you inserted into the master_name table during the previous hour, your results will vary, but it may look something like this:
mysql> SELECT * FROM master_name; +---------+---------------------+---------------------+-----------+----------+ | name_id | name_dateadded | name_datemodified | firstname | lastname | +---------+---------------------+---------------------+-----------+----------+ | 1 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | John | Smith | | 2 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | Jane | Smith | | 3 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | Jimbo | Jones | | 4 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | Andy | Smith | | 7 | 2001-10-29 14:16:21 | 2001-10-29 14:16:21 | Chris | Jones | | 45 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | Anna | Bell | | 44 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | Jimmy | Carr | | 43 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | Albert | Smith | | 42 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | John | Doe | +---------+---------------------+---------------------+-----------+----------+ 9 rows in set (0.00 sec)
As you can see, MySQL creates a lovely table as part of the result set, with the names of the columns along the first row. If you want to select only specific columns, replace the * with the names of the columns, separated by commas. The following statement selects just the name_id, firstname and lastname fields from the master_name table.
mysql> SELECT name_id, firstname, lastname FROM master_name; +---------+-----------+----------+ | name_id | firstname | lastname | +---------+-----------+----------+ | 1 | John | Smith | | 2 | Jane | Smith | | 3 | Jimbo | Jones | | 4 | Andy | Smith | | 7 | Chris | Jones | | 45 | Anna | Bell | | 44 | Jimmy | Carr | | 43 | Albert | Smith | | 42 | John | Doe | +---------+-----------+----------+ 9 rows in set (0.00 sec)
A useful expression used with SELECT is DISTINCT, which (not surprisingly) will return distinct occurrences in a result set. For example, the master_name table has more than one person with the last name of "Smith". If you wanted to select last names without repeating results, you would use DISTINCT:
mysql> SELECT DISTINCT lastname FROM master_name; +----------+ | lastname | +----------+ | Bell | | Carr | | Doe | | Jones | | Smith | +----------+ 5 rows in set (0.00 sec)
Ordering SELECT Results
By default, results of SELECT queries are ordered as they appear in the table. If you want to order results a specific way, such as by date, ID, name, and so on, specify your requirements using the ORDER BY clause. In the following statement, results are ordered by lastname:
mysql> SELECT name_id, firstname, lastname FROM master_name ORDER BY lastname; +---------+-----------+----------+ | name_id | firstname | lastname | +---------+-----------+----------+ | 45 | Anna | Bell | | 44 | Jimmy | Carr | | 42 | John | Doe | | 3 | Jimbo | Jones | | 7 | Chris | Jones | | 1 | John | Smith | | 2 | Jane | Smith | | 4 | Andy | Smith | | 43 | Albert | Smith | +---------+-----------+----------+ 9 rows in set (0.00 sec)
TIP
When selecting results from a table without specifying a sort order, the results may or may not be ordered by their key value. This occurs because MySQL reuses the space taken up by previously deleted rows. In other words, if you add records with ID values of 1 through 5, then delete the record with ID number 4, then add another record (ID number 6), the records may appear in the table in this order: 1, 2, 3, 6, 5.
The default sorting of ORDER BY results is ascending (ASC); strings sort from A to Z, integers start at 0, dates sort from oldest to newest. You can also specify a descending sort, using DESC:
mysql> SELECT name_id, firstname, lastname FROM master_name -> ORDER BY lastname DESC; +---------+-----------+----------+ | name_id | firstname | lastname | +---------+-----------+----------+ | 1 | John | Smith | | 2 | Jane | Smith | | 4 | Andy | Smith | | 43 | Albert | Smith | | 3 | Jimbo | Jones | | 7 | Chris | Jones | | 42 | John | Doe | | 44 | Jimmy | Carr | | 45 | Anna | Bell | +---------+-----------+----------+ 9 rows in set (0.00 sec)
You're not limited to sorting by just one field—you can specify as many fields as you want, separated by commas. The sorting priority is by list order, so if you use ORDER BY lastname, firstname, the results will be sorted by lastname, then by firstname:
mysql> SELECT name_id, firstname, lastname FROM master_name -> ORDER BY lastname, firstname; +---------+-----------+----------+ | name_id | firstname | lastname | +---------+-----------+----------+ | 45 | Anna | Bell | | 44 | Jimmy | Carr | | 42 | John | Doe | | 7 | Chris | Jones | | 3 | Jimbo | Jones | | 43 | Albert | Smith | | 4 | Andy | Smith | | 2 | Jane | Smith | | 1 | John | Smith | +---------+-----------+----------+ 9 rows in set (0.00 sec)
Limiting Your Results
You can use the LIMIT clause to return only a certain number of records in your SELECT query result. There are two requirements when using the LIMIT clause: offset and number of rows. The offset is the starting position, and the number of rows should be self-explanatory.
TIP
For the most part, counting while programming always starts at 0, not 1. For example: 0, 1, 2, 3 instead of 1, 2, 3, 4.
An example would be to select only the first 5 records from master_name, ordered by lastname:
mysql> SELECT * FROM master_name ORDER BY lastname LIMIT 0, 5; +---------+---------------------+---------------------+-----------+----------+ | name_id | name_dateadded | name_datemodified | firstname | lastname | +---------+---------------------+---------------------+-----------+----------+ | 45 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | Anna | Bell | | 44 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | Jimmy | Carr | | 42 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | John | Doe | | 3 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | Jimbo | Jones | | 7 | 2001-10-29 14:16:21 | 2001-10-29 14:16:21 | Chris | Jones | +---------+---------------------+---------------------+-----------+----------+ 5 rows in set (0.00 sec)
The LIMIT clause can be quite useful in an actual application. For example, you can use the LIMIT clause within a series of SELECT statements to essentially page through results in steps:
SELECT * FROM master_name ORDER BY lastname LIMIT 0, 5;
SELECT * FROM master_name ORDER BY lastname LIMIT 6, 5;
SELECT * FROM master_name ORDER BY lastname LIMIT 11, 5;
If you specify an offset and number of rows in your query and no results are found, you won't see an error—just an empty result set. For example, if the master_name table contains only 9 records, a query with a LIMIT offset of 11 will produce no results:
mysql> SELECT * FROM master_name ORDER BY lastname LIMIT 11, 5; Empty set (0.00 sec)
In Web-based applications, when lists of data are displayed with links like "previous 10" and "next 10," it's a safe bet that a LIMIT clause is at work.
Using Some Aggregate Functions with SELECT
MySQL has many built-in functions that allow you to perform all sorts of operations on integers, strings, and dates. These functions will be explained in detail in later hours, but a few of the simpler functions described here should whet your appetite.
If you want to know how many records are in your table, you could just select all records and look for the response that says "5 rows in set," or however many there are. If you have a million rows, selecting all one million rows just to see how many there are is not the speediest way to find your answer. Instead, you can use the COUNT() function, which counts all non-NULL values in a given column. If you have a primary key defined in your table, you know that field can't be NULL. Knowing this, if you use COUNT() on the primary key, you can obtain a count of all the records in the table.
Using a test table containing almost 4000 rows of integer and string data, a select of all the rows took 0.03 seconds.
mysql> SELECT * FROM test_table; +----+--------------+ | id | string_field | +----+--------------+ | 1 | I Love MySQL | | 2 | I Love MySQL | | 3 | I Love MySQL | | 4 | I Love MySQL | | 5 | I Love MySQL | ... | 3890 | | +------+--------------+ 3890 rows in set (0.03 sec)
Using COUNT(), the query was faster:
mysql> SELECT COUNT(id) FROM test_table; +-----------+ | count(id) | +-----------+ | 3890 | +-----------+ 1 row in set (0.00 sec)
COUNT() returns only one row in the result set. This result is equal to the actual number of rows in the table, which is what your query was asking. The column header for the result set is COUNT(id), the expression used in the SELECT query.
You can use AS to produce your own column headers—not just when using functions but during any SELECT statement. For example, to name your column row_count, use:
mysql> SELECT COUNT(id) AS row_count FROM test_table; +-----------+ | row_count | +-----------+ | 3890 | +-----------+ 1 row in set (0.00 sec)
You can use COUNT() in conjunction with the DISTINCT expression you learned earlier. Instead of selecting the actual distinct last names from the master_name table, you can select the count of those names:
mysql> SELECT COUNT(DISTINCT lastname) AS lastname_count FROM master_name; +----------------+ | lastname_count | +----------------+ | 5 | +----------------+ 1 row in set (0.00 sec)
A few other basic functions are MIN() and MAX(), which are used to select the minimum and maximum values in a field.
For example, select the minimum and maximum values for name_id from the master_name table:
mysql> SELECT MIN(name_id) FROM master_name; +--------------+ | MIN(name_id) | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)
mysql> SELECT MAX(name_id) FROM master_name; +--------------+ | MAX(name_id) | +--------------+ | 45 | +--------------+ 1 row in set (0.00 sec)
TIP
Using MAX(name_id) would not produce an accurate count of the number of records in the master_name table. Although the primary key auto-increments, records could have been deleted throughout the table's existence. In the current master_name table, there are only 9 records, but the value of MAX(name_id) is 45 because numerous dummy records have been added and deleted. Always use COUNT() and not MAX() to find the number of records in a table.
If the field is a date, MIN() will return the earliest date, while MAX() returns the latest date:
mysql> SELECT MIN(name_dateadded) FROM master_name; +---------------------+ | MIN(name_dateadded) | +---------------------+ | 0000-00-00 00:00:00 | +---------------------+ 1 row in set (0.00 sec)
mysql> SELECT MAX(name_dateadded) FROM master_name; +---------------------+ | MAX(name_dateadded) | +---------------------+ | 2001-10-29 14:16:21 | +---------------------+ 1 row in set (0.00 sec)
This is just a sampling of the numerous built-in MySQL functions, which range from obscure to "can't live without them," most of which you'll learn about in Hours 15, 16, and 17.