PHP —

Querying with SQL SELECT

We begin this section by covering the basics of using the SELECT statement. We then introduce the WHERE clause for selecting data that matches a condition. The section concludes with an introduction to the more advanced features of SELECT statements.

Basic Querying

The SELECT statement is used to query a database and for all output operations in SQL. Consider an example query:

SELECT surname, firstname FROM customer;

This outputs the values of the attributes surname and firstname from all rows, or records, in the customer table. Assuming we previously inserted four rows when we created the winestore database, the output from the MySQL command interpreter is:

+-----------+-----------+
| surname   | firstname |
+-----------+-----------+
| Marzalla  | Dimitria  |
| LaTrobe   | Anthony   |
| Fong      | Nicholas  |
| Stribling | James     |
+-----------+-----------+
4 rows in set (0.04 sec)

Any attributes of a table may be listed in a SELECT statement by separating each with a comma. If all attributes are required, the shortcut of an asterisk character (*) can be used. Consider the statement:

SELECT * FROM region;

This outputs all the data from the table region:

+-----------+---------------------+-------------+------+
| region_id | region_name         | description | map  |
+-----------+---------------------+-------------+------+
|         1 | Goulburn Valley     | NULL        | NULL |
|         2 | Rutherglen          | NULL        | NULL |
|         3 | Coonawarra          | NULL        | NULL |
|         4 | Upper Hunter Valley | NULL        | NULL |
+-----------+---------------------+-------------+------+
4 rows in set (0.07 sec)

SELECT statements can also output data that isn't from a database. Consider the following example:

SELECT curtime(  );

This example runs a function that displays the current time:

+-----------+
| curtime(  ) |
+-----------+
| 08:41:50  |
+-----------+
1 row in set (0.02 sec)

The SELECT statement can even be used as a simple calculator, using the mathematical functions described in the later section:

SELECT log(100)*4*pi(  );

This outputs:

+-----------------+
| log(100)*4*pi(  ) |
+-----------------+
|       57.870275 |
+-----------------+
1 row in set (0.19 sec)