Once you have a database, tables, and records, you can create queries to gather information from them.
The SELECT statement is the SQL statement you'll use most often in your queries. It's used to retrieve data from a tablean individual field, multiple fields, or all the fields. You can join tables with a SELECT statement to create complex queries involving more than one table, and you can use additional SQL statements with SELECT to sort and filter information, as you'll see later in this chapter.
You'll enhance your understanding of SQL as you move through the tasks in this chapter, creating queries in the MySQL Monitor (Windows) or the Terminal window (Mac). In Chapter 6, you'll enter SQL queries directly in Dreamweaver (in the advanced Recordset dialog), rather than using the MySQL Monitor or Terminal window.
In the following task, you'll use the SELECT statement to retrieve a single field (last_name), multiple fields (last_name and first_name), and then all the fields from the members table of the travelclub database that you created in the preceding tasks.
To select data using SQL:
Do one of the following:
- To start the MySQL Monitor in Windows, follow Steps 1 through 4 of the task "To create a database using MySQL in Windows," earlier in this chapter.
- To start MySQL in a Terminal window on a Mac, follow Steps 1 through 8 of "To create a database using MySQL on a Mac," earlier in this chapter.
At the mysql prompt, type use travelclub; and press Enter (Windows) or Return (Mac) to choose the travelclub database.
At the prompt, type SELECT last_name FROM members; and press Enter (Windows) or Return (Mac).
This selects the last_name column from the members table. The last_name column appears (Figure 5.12).
Figure 5.12. Using a SQL query to view the last_name column in the members table.
At the prompt, type SELECT last_name, first_name FROM members; and press Enter (Windows) or Return (Mac) to choose those two columns from the members table.
The last_name and the first_name columns appear (Figure 5.13).
Figure 5.13. Viewing two columns in the members table.
At the prompt, type SELECT * FROM members; and press Enter (Windows) or Ret-urn (Mac) to retrieve the entire table. The table appears (Figure 5.14).
Figure 5.14. Selecting an entire table with the wildcard operator (*).
The asterisk (*) above is called a wildcard operator, which selects every column in the table.
At the prompt, do one of the following:
- If you're running Windows, type exit and press Enter to close the MySQL Monitor.
- If you're using a Mac, type exit and press Return to stop MySQL, type exit and press Return to log out, and then quit Terminal and close the Terminal window.