| 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 term is used to return rows of records that meet certain criteria. A SELECT query looks like so: SELECT whatcolumns FROM whattable You just ask MySQL to return whichever columns you want (the data stored in them, that is) for a particular table. For the columns, one option is to list them individually, with each separated from the next by a comma: SELECT user_id, first_name, last_name FROM users
Another option is to use an asterisk, meaning that you want to view every column: SELECT * 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 that you will not be using. The second is order: you can return columns in an order other than their layout in the table. The thirdand you'll see this in Chapter 6is accessibility: it allows you to manipulate the values in those columns using functions. To select data from a table: 1. | Retrieve all the data from the expense_categories table (Figure 5.4).
SELECT * FROM expense_categories;
This very basic SQL command will retrieve every column of every row stored within that table and present them.
Figure 5.4. This is a simple SELECT query, returning all of the table's records and columns. 
INSERT...SELECT Another way you can populate one table is by selecting data from another table. To do so, use an INSERT...SELECT query. The syntax is [View full width] INSERT INTO tablename (col1, col2, ...) SELECT colA, colB, FROM  othertable
As with any INSERT query, the important consideration is that one value is supplied for each column (listed or in the table). This query isn't frequently used, but it can be quite the time saver in the right circumstances. |
| | | 2. | Retrieve just the client_id and client_name fields from clients (Figure 5.5).
SELECT client_id, client_name FROM clients;
Instead of showing the data from every field in the clients table, you can use the SELECT statement to limit yourself to only the pertinent information.
Figure 5.5. You can limit the information returned by a SELECT query by specifying the columns to include. 
| | | 3. | Using the information retrieved in Steps 1 and 2, populate the expenses table (Figure 5.6).
INSERT INTO expenses VALUES (NULL, 3, 19.99, 'Larry Ullman\'s "MySQL: Visual QuickStart Guide"', '2002-04-20'), (NULL, 1, 105.50, 'Palmer House Hotel, Chicago', '2002-1-26'), (NULL, 2, 689.00, 'Flight to Chicago', '2002-01-26'), (NULL, 5, 99.99, 'Mmmm...software', NULL);
Now that you can view the primary keys from the expense_categories and clients tables (expense_category_id and client_id, respectively), it's possible to insert data into the other two tables. Because the database is relational, it's important to align records, matching up primary keys in one table with foreign keys in another. Thus, to indicate that an expense is a Book, you enter the expense using 3 as the expense_category_id. Maintaining these relationships is at the heart of a normalized database and requires use of both SELECT and INSERT commands.
Figure 5.6. Now that my expense_category_id foreign keys have been established, I can populate the expenses table. 
| 4. | Populate the invoices table.
For the invoices records, you'll need to match the client_id in the invoices table with the client_id in the clients table. Your queries might look like this:
[View full width] INSERT INTO invoices VALUES (NULL,4,'2006-04-24','1902.34', 'Conjugation: verbs, nouns, adjectives.', NULL); INSERT INTO invoices (client_id, invoice_date, invoice_amount, invoice_description) VALUES (4,'2008-07-20','942.00','Technical writing.');
| 5. | Continue with this process until the database has oodles of information in it, in every table.
Throughout the rest of this chapter I will be performing queries based upon the records I enter into my database. Should your database not have the same specific records (clients, expense categories, etc.) as mine, change the particulars accordingly. That being said, the fundamental thinking behind the following queries should still apply regardless of the data, since the accounting database has a set column and table structure.
|
 Tips
Strange as it may sound, you can actually use SELECT without naming tables or columns. You'll see this in action in the next chapter. 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 returned. Compare Figure 5.5 with Figure 5.7. Figure 5.7. Changing the order of the columns in your SELECT query alters the order of the retrieved data.  With SELECT, you can even retrieve the same column multiple times, allowing you to manipulate the column in many different ways.
|
