Managing & Using MySQL, 2nd Edition

Queries

The last common SQL command, SELECT , enables you to view the data in the database. 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 to retrieve data from any SQL database. The SELECT statement enables you to identify the columns you want from one or more tables. The WHERE clause identifies the rows with the data you seek.

Of course, there are variations for performing complex and powerful queries. (We cover the full range of the SELECT syntax in Chapter 15.) The simplest form is:

SELECT 1;

This simple, though completely useless query returns a result set with a single row containing a single column with the value of 1. A more useful version of this query might be something like:

mysql> SELECT DATABASE(  );
+------------+
| DATABASE(  ) |
+------------+
| test       |
+------------+
1 row in set (0.01 sec)

The expression DATABASE( ) is a MySQL function that returns the name of the current database. (We will cover functions in more detail later in the chapter.) Nevertheless, you can see how simple SQL can provide a quick-and-dirty way of finding out important information.

Most of the time, however, you should use slightly more complex queries that ...