Specifying the order

The ORDER BY keyword, briefly mentioned previously, can be used to change the order of the results from a query. The default for ORDER BY is ascending, so if you want alphabetical order for the author column, you would just type in ORDER BY author. To select in reverse order, add the DESC keyword after author. For example, use the following to select the authors in alphabetical order:

SELECT * FROM authors ORDER BY author;

This displays:

| author_id | title_id | author

+----

------+-----

-----+-----------------

-- +

|

2 |

1 | Aaron Weber

1

|

5 |

9 | Alex Martelli

1

|

3 |

2 | Arnold Robbins

1

|

1 1

1 | Ellen Siever

1

|

4 |

2 | Nelson Beebe

1

+----

------+-----

-----+-----------------

-- +

Next, we'll select from more than one table. Joining tables together

The SELECT statement allows you to query more than one table at a time. Example 7-3 creates the purchases table and adds a couple of sample entries.

Example 7-3. The SQL to create and populate a purchases table that links user_ids and title_ids to a purchase_id

CREATE TABLE purchases ( purchase_id int NOT NULL AUTO_INCREMENT, user_id varchar(10) NOT NULL, title_id int(11) NOT NULL, purchased timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (purchase_id));

INSERT INTO ~purchases~ VALUES (1, 'mdavis', 2, '2005-11-26 17:04:29'); INSERT INTO ~purchases~ VALUES (2, 'mdavis', 1, '2005-11-26 17:05:58');

Example 7-3 returns:

SELECT * FROM purchases;

2 | 2005-11-26 17:04:29 | 1 | 2005-11-26 17:05:58 |

To create a query that lists the purchases, author, and pages, enter the following SELECT statement:

SELECT books.*, author FROM books, authors WHERE books.title_id = authors.title_id; which produces:

| 2 | Classic Shell Scripting | 256 | Arnold Robbins

| 2 | Classic Shell Scripting | 256 | Nelson Beebe

The books.*, author portion tells the database to select all the fields from the books table, but only the author from the authors table. The WHERE books.title_id = authors.title_id portion links the tables together by the title_id.

You could have selected *, which includes all the fields from both tables, but the title_id field would be included twice, since it's in both tables. There's no limit to how many tables and columns you can join together.

Was this article helpful?

0 0

Post a comment