MySQL

Retrieving, Sorting and Filtering Data

How to retrieve, sort, and display information from a database

The SELECT Statement

SELECT statement purpose is to retrieve information from one or more tables. This SQL statement you'll probably use most frequently.

To use SELECT to retrieve table data you must, at a minimum, specify two pieces of information what you want to select, and from where you want to select it.

Fetch single column data using SELECT

We'll start with a simple SQL SELECT statement. The following statement uses the SELECT statement to retrieve a single column called prod_name from the products table. The desired column name is specified right after the SELECT keyword, and the FROM keyword specifies the name of the table from which to retrieve the data. 

SELECT prod_name
FROM products;

The output from this statement is shown in the following:

+------------+
| prod_name  |
+------------+
| product 1  |
| product 2  |
| product 3  |
| product 4  |
| product 5  |
| product 6  |
| product 7  |
| product 8  |
| product 9  |
| product 10 |
| product 11 |
+------------+

A simple SELECT statement like the one just shown above returns all the rows in a table. The returned data is not filtered and sorted.

White Spaces

All extra white space within a SQL statement is ignored when that statement is processed. SQL statements can be specified on one long line or broken up over many lines. Most SQL developers find that breaking up statements over multiple lines makes them easier to read and debug.

SQL Statements Case Sensitivity

It is important to note that SQL statements are not case sensitive, so SELECT is the same as select, which is the same as Select. Many SQL developers find that using uppercase for all SQL keywords and lowercase for column and table names makes code easier to read and debug.

Be aware that while the SQL language is not case sensitive. In MySQL 4.1 and earlier, identifiers (the names of databases, tables, and columns) were case sensitive by default, and as of MySQL 4.1.1, identifiers are not case sensitive by default.

Terminating Statements

Multiple SQL statements must be separated by semicolons. However, MySQL does not require that a semicolon be specified after single statements. If you are using the mysql command-line client, the semicolon is always needed.