Selecting Data


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.




    PHP and MySQL for Dynamic Web Sites. Visual QuickPro Guide
    PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (2nd Edition)
    ISBN: 0321336577
    EAN: 2147483647
    Year: 2005
    Pages: 166
    Authors: Larry Ullman

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net