Now that the database has some records in it, you can begin to retrieve the information with the most used of all SQL terms, SELECT. This type of query returns rows of records that match certain criteria, using the syntax SELECT which_columns FROM which_table The simplest SELECT query is SELECT * FROM tablename The asterisk means that you want to view every column. Your other choice would be to specify the columns to be returned, with each separated from the next by a comma. SELECT user_id, first_name, last_name FROM users There are a few benefits to being explicit about which columns are selected. The first is performance: There's no reason to fetch columns you will not be using. The second is order: You can return columns in an order other than their layout in the table. Thirdand you'll see this later in the chapterit allows you to manipulate the values in those columns using functions. To select data from a table 1. | Retrieve all the data from the users table (Figure 4.11).
SELECT * FROM users; Figure 4.11. The SELECT * FROM tablename query returns every column for every record. This very basic SQL command will retrieve every column of every row stored within that table.
| 2. | Retrieve just the first and last names from users (Figure 4.12).
SELECT first_name, last_name FROM users; Figure 4.12. All of the records but only two of the columns are returned by this query. Instead of showing the data from every field in the users table, you can use the SELECT statement to limit yourself to only the pertinent information.
| Tips Strange as it may sound, you can actually use SELECT without naming tables or columns. For example, SELECT NOW(); (Figure 4.13). Figure 4.13. Many queries can be run without specifying a database or table. This query returns the current date and time, according to MySQL.
The order in which you list columns in your SELECT statement (assuming you are not retrieving everything) dictates the order in which the values are presented (compare Figure 4.12 with Figure 4.14). Figure 4.14. Columns will be displayed by MySQL in the order dictated by your query when you specify them.
With SELECT, you can even retrieve the same column multiple times, a feature that enables you to manipulate the column's data in many different ways.
|