• Choose a category
• All
• Classic-Fiction

| Carrots |

| Safe |

| Detonator |

| JetPack 1000 |

| JetPack 2000 |

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

* * *

Note: Unsorted Data

If you tried this query yourself, you might have discovered that the data was displayed in a different order than shown here. If this is the case, don’t worry—it is working exactly as it is supposed to. If query results are not explicitly sorted (we get to that in the next chapter), data will be returned in no order of any significance. It might be the order in which the data was added to the table, but it might not. As long as your query returned the same number of rows, then it is working.

* * *

A simple SELECT statement like the one just shown returns all the rows in a table. Data is not filtered (so as to retrieve a subset of the results), nor is it sorted. We discuss these topics in the next few chapters.

* * *

Note: Terminating Statements

Multiple SQL statements must be separated by semicolons (the ; character). MariaDB (like most DBMSs) does not require that a semicolon be specified after single statements. Of course, you can always add a semicolon if you want. It’ll do no harm, even if it isn’t needed.

If you are using the mysql command line client, the semicolon is always needed (as was explained in Chapter 2, “Introducing MariaDB”).

* * *

* * *

Note: SQL Statements and Case

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.

However, be aware that while the SQL language is not case sensitive, identifiers (the names of databases, tables, and columns) might be. As a best practice, pick a case convention, and use it consistently.

* * *

* * *

Tip: Use of White Space

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.

* * *

Retrieving Multiple Columns

To retrieve multiple columns from a table, the same SELECT statement is used. The only difference is that multiple column names must be specified after the SELECT keyword, and each column must be separated by a comma.

* * *

Tip: Take Care with Commas

When selecting multiple columns, be sure to specify a comma between each column name, but not after the last column name. Doing so generates an error.

* * *

The following SELECT statement retrieves three columns from the products table:

Input

SELECT prod_id, prod_name, prod_price

FROM products;

Analysis

Just as in the prior example, this statement uses the SELECT statement to retrieve data from the products table. In this example, three column names are specified, each separated by a comma. The output from this statement is as follows:

Output

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

| prod_id | prod_name | prod_price |

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

| ANV01 | .5 ton anvil | 5.99 |

| ANV02 | 1 ton anvil | 9.99 |

| ANV03 | 2 ton anvil | 14.99 |

| OL1 | Oil can | 8.99 |

| FU1 | Fuses | 3.42 |

| SLING | Sling | 4.49 |

| TNT1 | TNT (1 stick) | 2.50 |

| TNT2 | TNT (5 sticks) | 10.00 |

| FB | Bird seed | 10.00 |

| FC | Carrots | 2.50 |

| SAFE | Safe | 50.00 |

| DTNTR | Detonator | 13.00 |

| JP1000 | JetPack 1000 | 35.00 |

| JP2000 | JetPack 2000 | 55.00 |

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

* * *

Note: Presentation of Data

SQL statements typically return raw, unformatted data. Data formatting is a presentation issue, not a retrieval issue. Therefore, presentation (for example, alignment and displaying the price values as currency amounts with the currency symbol and commas) is typically specified in the application that displays the data. Actual raw retrieved data (without application-provided formatting) is rarely displayed as is.

* * *