MySQL and mSQL

Queries

The last common SQL command used is the one that enables you to view the data in the database: SELECT. This action is by far the most common action performed in SQL. While data entry and modifications do happen on occasion, most databases spend the vast majority of their lives serving up data for reading. The general form of the SELECT statement is as follows:

SELECT column1, column2, ..., columnN 
FROM table1, table2, ..., tableN
[WHERE clause]

This syntax is certainly the most common way in which you will retrieve data from any SQL database. Of course, there are variations for performing complex and powerful queries, especially under MySQL. We cover the full range of the SELECT syntax in Chapter 15.

The first part of a SELECT statement enumerates the columns you wish to retrieve. You may specify a "*" to say that you want to select all columns. The FROM clause specifies which tables those columns come from. The WHERE clause identifies the specific rows to be used and enables you to specify how to join two tables.

Joins

Joins put the “relational” in relational databases. Specifically, a join enables you to match a row from one table up with a row in another table. The basic form of a join is what you may hear sometimes described as an inner join . Joining tables is a matter of specifying equality in columns from two tables:

SELECT book.title, author.name 
FROM author, book
WHERE book.author = author.id

Consider a database where the book table looks like Table 6.3.

Table 6-3. A ...