Queries with SELECT

SELECT is by far is the most common SQL command. This command enables the user to query the data of the database and retrieve data that match specified criteria.

General Syntax of the SELECT Command

The general syntax of the SELECT statement is as follows:

SELECT column1, column2, ..., columnn 
FROM table1, table2, ..., tablen
[WHERE clause]

The column names following the select keyword determine the columns which will be returned in the results. In other words: Columns not listed in column₁, column₂, ..., column_n will not appear in the result. An arbitrary number of column names can be selected. To select all columns the asterisk "*" can be used.

Prearrangement

Befor we can go on with our tutorial on SELECT, we have to do some preparations, so that you can reproduce the examples in the following subchapters. We have prepared a file with a complete database "musicarchive", which can be downloaded: musicarchive.sql
You can install (restore) this database as described in our previous chapter. Just type in the following command in a command shell, but not the mysql shell:

mysql -u root -p < musicarchive.sql

After this, you can start the mysql shell and use the musicarchive:

bernd@venus:~/bodenseo/kurse/mysql$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 69
Server version: 5.1.61-0ubuntu0.11.04.1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use musicarchive;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 

Viewing Everything

The database musicarchive has one table, which is called "cd". We could have called it "cds" as well, because it contains more than one CD. This is a general naming problem. Some database designers use use nothing but plurals for all there table names, while others prefer the singular forms. Whichever way you prefer, the only thing which matters is, that you remain consistent, i.e. either only plural forms or only singular forms, but mixing the two concepts should be taboo.
We can print out the content of the whole table with the following command:

mysql> SELECT * FROM cd;
+-----------+----------------------+--------------------------------+-------------+------+
| cd_number | artist               | title                          | genre       | year |
+-----------+----------------------+--------------------------------+-------------+------+
|         1 | Massive Attack       | Blue Lines                     | Trip Hop    | 1991 |
|         2 | Massive Attack       | Protection                     | Trip Hop    | 1994 |
|         3 | Massive Attack       | Mezzanine                      | Trip Hop    | 1998 |
|         4 | Massive Attack       | 100th Window                   | Trip Hop    | 2003 |
|         5 | Massive Attack       | Heligoland                     | Trip Hop    | 2010 |
|         6 | Led Zeppelin         | Led Zeppelin                   | Rock        | 1969 |
|         7 | Led Zeppelin         | Led Zeppelin II                | Rock        | 1969 |
|         8 | Led Zeppelin         | Led Zeppelin III               | Rock        | 1970 |
|         9 | Led Zeppelin         | Led Zeppelin IV                | Rock        | 1971 |
|        10 | Led Zeppelin         | Houses of The Holy             | Rock        | 1971 |
|        11 | Nils Petter Molvaer  | Khmer                          | Fusion Jazz | 1997 |
|        12 | Nils Petter Molvaer  | Solid Ether                    | Fusion Jazz | 2000 |
|        13 | Nils Petter Molvaer  | Barboon Moon                   | Fusion Jazz | 2011 |
|        14 | Miles Davis          | Kind of Blue                   | Jazz        | 1959 |
|        15 | Miles Davis          | Sketches of Spain              | Jazz        | 1960 |
|        16 | Miles Davis          | Bitches Brew                   | Jazz        | 1970 |
|        17 | Miles Davis          | Tutu                           | Jazz        | 1986 |
|        18 | Miles Davis          | Amandla                        | Jazz        | 1989 |
|        19 | Miles Davis          | Doo-Bop                        | Jazz        | 1992 |
|        20 | John Mayall          | John Mayall Plays John Mayall  | Blues       | 1964 |
|        21 | John Mayall          | Blues Breakers with Eric Clapt | Blues       | 1966 |
|        22 | John Mayall          | A Hard Road                    | Blues       | 1967 |
|        23 | John Mayall          | The Turning Point              | Blues       | 1967 |
|        24 | John Mayall          | Jazz Blues Fusion              | Jazz Blues  | 1971 |
|        25 | John Mayall          | In the Palace of the King      | Blues       | 2007 |
|        26 | John Mayall          | Tough                          | Blues Rock  | 2009 |
|        27 | Igor Stravinsky      | Le Sacre du Printemps; The Fir | Classical   | 2003 |
|        28 | Igor Stravinsky      | Works for Violin and Piano     | Classical   | 1987 |
|        29 | Ludwig van Beethoven | The Five Piano Concertos       | Classical   | 1995 |
|        30 | The Indelicates      | American Demo                  | Indie Rock  | 2008 |
|        31 | The Indelicates      | American Demo                  | Indie Rock  | 2010 |
+-----------+----------------------+--------------------------------+-------------+------+
31 rows in set (0.00 sec)

mysql> 

The asterisk is used to select all columns.

Selecting Columns

As we mentioned in the beginning of this chapter, we can select arbitrary columns by putting the column names - separated by commas - after the keyword SELECT:

mysql> SELECT artist, title FROM cd;
+----------------------+--------------------------------+
| artist               | title                          |
+----------------------+--------------------------------+
| Massive Attack       | Blue Lines                     |
| Massive Attack       | Protection                     |
| Massive Attack       | Mezzanine                      |
| Massive Attack       | 100th Window                   |
| Massive Attack       | Heligoland                     |
| Led Zeppelin         | Led Zeppelin                   |
| Led Zeppelin         | Led Zeppelin II                |
| Led Zeppelin         | Led Zeppelin III               |
| Led Zeppelin         | Led Zeppelin IV                |
| Led Zeppelin         | Houses of The Holy             |
| Nils Petter Molvaer  | Khmer                          |
| Nils Petter Molvaer  | Solid Ether                    |
| Nils Petter Molvaer  | Barboon Moon                   |
| Miles Davis          | Kind of Blue                   |
| Miles Davis          | Sketches of Spain              |
| Miles Davis          | Bitches Brew                   |
| Miles Davis          | Tutu                           |
| Miles Davis          | Amandla                        |
| Miles Davis          | Doo-Bop                        |
| John Mayall          | John Mayall Plays John Mayall  |
| John Mayall          | Blues Breakers with Eric Clapt |
| John Mayall          | A Hard Road                    |
| John Mayall          | The Turning Point              |
| John Mayall          | Jazz Blues Fusion              |
| John Mayall          | In the Palace of the King      |
| John Mayall          | Tough                          |
| Igor Stravinsky      | Le Sacre du Printemps; The Fir |
| Igor Stravinsky      | Works for Violin and Piano     |
| Ludwig van Beethoven | The Five Piano Concertos       |
| The Indelicates      | American Demo                  |
| The Indelicates      | American Demo                  |
+----------------------+--------------------------------+
31 rows in set (0.00 sec)

mysql>

The WHERE Clause

By using a where clause with a criteria we can specify which data values or rows shall be displayed. We demonstrate this in the following example. We print out all CDs, which have been publishes after the year 2000, not including the year 2000 itself:

mysql> SELECT artist, title, year FROM cd WHERE year > 2000;
+---------------------+--------------------------------+------+
| artist              | title                          | year |
+---------------------+--------------------------------+------+
| Massive Attack      | 100th Window                   | 2003 |
| Massive Attack      | Heligoland                     | 2010 |
| Nils Petter Molvaer | Barboon Moon                   | 2011 |
| John Mayall         | In the Palace of the King      | 2007 |
| John Mayall         | Tough                          | 2009 |
| Igor Stravinsky     | Le Sacre du Printemps; The Fir | 2003 |
| The Indelicates     | American Demo                  | 2008 |
| The Indelicates     | American Demo                  | 2010 |
+---------------------+--------------------------------+------+
8 rows in set (0.00 sec)

mysql> 

If the CDs from the year 2000 shall be printed as well, we would have to change the ">" into a ">=".

Posh up the SELECT Clause

So far, we solely used column names from our table after the SELECT keyword. There is a lot more we can do to posh up the result. We can do things like

• Adding literals like numbers and strings
• Expressions, for example "2012 - year"
• Use built-in functions, e.g. UPPER(artist) or even user defined functions

We demonstrate this in the following example:

mysql> SELECT UPPER(artist), title, 2012 - year, "new" FROM cd WHERE year > 2008;
+---------------------+---------------+-------------+-----+
| UPPER(artist)       | title         | 2012 - year | new |
+---------------------+---------------+-------------+-----+
| MASSIVE ATTACK      | Heligoland    |           2 | new |
| NILS PETTER MOLVAER | Barboon Moon  |           1 | new |
| JOHN MAYALL         | Tough         |           3 | new |
| THE INDELICATES     | American Demo |           2 | new |
+---------------------+---------------+-------------+-----+
4 rows in set (0.00 sec)

mysql> 

Column Aliases

You might not like that the title for the first column in our previous example was output as "UPPER(artist)" and the third column as "2012 - year". We could have imagined titles like "Title" instead of "UPPER(artist)" or "Age in Years" instead of "2012 - year". We can accomplish this by using alias names for our columns. It's very easy to do: You just have to add a column alias after the element, as can be seen in the following example:

mysql> SELECT UPPER(artist) Artist, title Title, 2012 - year "Age in years", "new" FROM cd WHERE year > 2008;
+---------------------+---------------+--------------+-----+
| Artist              | Title         | Age in years | new |
+---------------------+---------------+--------------+-----+
| MASSIVE ATTACK      | Heligoland    |            2 | new |
| NILS PETTER MOLVAER | Barboon Moon  |            1 | new |
| JOHN MAYALL         | Tough         |            3 | new |
| THE INDELICATES     | American Demo |            2 | new |
+---------------------+---------------+--------------+-----+
4 rows in set (0.00 sec)

mysql> 

Removing Duplicates

Let's have a look at the following example:

SELECT artist from cd;

The output consists of a list of artists, where each artist appears multiple times, because we have included various CDs of each artist. We haven't included the output, because we do not want to pester the webspace by this redundant appearances. But you can type it in yourself.
Getting rid of these duplicate rows can be easily achieved. You have to add the keyword DISTINCT directly after the SELECT keyword:

mysql> SELECT DISTINCT artist from cd;
+----------------------+
| artist               |
+----------------------+
| Massive Attack       |
| Led Zeppelin         |
| Nils Petter Molvaer  |
| Miles Davis          |
| John Mayall          |
| Igor Stravinsky      |
| Ludwig van Beethoven |
| The Indelicates      |
+----------------------+
8 rows in set (0.00 sec)

SELECT without FROM

It may sound strange at first, but we don't always need the FROM clause. If we just want to execute built-in functions or excecute expressions, it clear that we do not need tables. You can see this in the following examples:

mysql> SELECT USER(), VERSION(), DATABASE();
+----------------+-------------------------+--------------+
| USER()         | VERSION()               | DATABASE()   |
+----------------+-------------------------+--------------+
| root@localhost | 5.1.61-0ubuntu0.11.04.1 | musicarchive |
+----------------+-------------------------+--------------+
1 row in set (0.00 sec)

mysql> SELECT CURDATE(), DATE_ADD(CURDATE(), INTERVAL 33 DAY);
+------------+--------------------------------------+
| CURDATE()  | DATE_ADD(CURDATE(), INTERVAL 33 DAY) |
+------------+--------------------------------------+
| 2012-04-23 | 2012-05-26                           |
+------------+--------------------------------------+
1 row in set (0.00 sec)

mysql>