Selecting data with SQL | SELECT statement and WHERE clause in SQL

In this post I am going to take a look at the SELECT statement for selecting data and the WHERE clause for filtering the results.

Before everything else, we have to start the MySQL server, connect to it, and select a database. You can do that by running the following commands:

To start the MySQL server:

service mysql start

To connect to the MySQL server:

mysql -u root -p

To select the Chinook database:

use Chinook;

Ok, we selected the Chinook database. Since it is made of tables we want now to see the tables.

See the tables in the Chinook database:

show tables;

You should get something like this:

mysql> show tables;
+-------------------+
| Tables_in_Chinook |
+-------------------+
| Album             |
| Artist            |
| Customer          |
| Employee          |
| Genre             |
| Invoice           |
| InvoiceLine       |
| MediaType         |
| Playlist          |
| PlaylistTrack     |
| Track             |
+-------------------+
11 rows in set (0.00 sec)

You want to see what is inside of a particular table? No problem!

See a table:

desc Customer;

You should see something like this:

mysql> desc Customer;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| CustomerId   | int(11)     | NO   | PRI | NULL    | auto_increment |
| FirstName    | varchar(40) | NO   |     | NULL    |                |
| LastName     | varchar(20) | NO   |     | NULL    |                |
| Company      | varchar(80) | YES  |     | NULL    |                |
| Address      | varchar(70) | YES  |     | NULL    |                |
| City         | varchar(40) | YES  |     | NULL    |                |
| State        | varchar(40) | YES  |     | NULL    |                |
| Country      | varchar(40) | YES  |     | NULL    |                |
| PostalCode   | varchar(10) | YES  |     | NULL    |                |
| Phone        | varchar(24) | YES  |     | NULL    |                |
| Fax          | varchar(24) | YES  |     | NULL    |                |
| Email        | varchar(60) | NO   |     | NULL    |                |
| SupportRepId | int(11)     | YES  | MUL | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)

Let’s say you want to see the FirstName only.

mysql> SELECT FirstName FROM Customer;
+------------+
| FirstName  |
+------------+
| Luís       |
| Leonie     |
| François   |
...

...      |
| Luis       |
| Manoj      |
| Puja       |
+------------+
59 rows in set (0.00 sec)

You might want to select more than a row.

mysql> SELECT FirstName, LastName FROM Customer;
+------------+---------------+
| FirstName  | LastName      |
+------------+---------------+
| Luís       | Gonçalves     |
| Leonie     | Köhler        |
| François   | Tremblay      |
...

...   |
| Luis       | Rojas         |
| Manoj      | Pareek        |
| Puja       | Srivastava    |
+------------+---------------+
59 rows in set (0.00 sec)

Of course you could select everything from a table.

mysql> SELECT * FROM Customer;
+------------+------------+---------------+---------------------------------------------------+-------------------------------------------+-----------------------+--------+----------------+------------+---------------------+--------------------+-------------------------------+--------------+
| CustomerId | FirstName  | LastName      | Company                                           | Address                                   | City                  | State  | Country        | PostalCode | Phone               | Fax                | Email                         | SupportRepId |
+------------+------------+---------------+---------------------------------------------------+-------------------------------------------+-----------------------+--------+----------------+------------+---------------------+--------------------+-------------------------------+--------------+
|          1 | Luís       | Gonçalves     | Embraer - Empresa Brasileira de Aeronáutica S.A.  | Av. Brigadeiro Faria Lima, 2170           | São José dos Campos   | SP     | Brazil         | 12227-000  | +55 (12) 3923-5555  | +55 (12) 3923-5566 | luisg@embraer.com.br          |            3 |
|          2 | Leonie     | Köhler        | NULL                                              | Theodor-Heuss-Straße 34                   | Stuttgart             | NULL   | Germany        | 70174      | +49 0711 2842222    | NULL               | leonekohler@surfeu.de         |            5 |
|          3 | François   | Tremblay      | NULL                                              | 1498 rue Bélanger                         | Montréal              | QC     | Canada         | H2G 1A7    | +1 (514) 721-4711   | NULL               | ftremblay@gmail.com           |            3 |
|          

........

57 | Luis       | Rojas         | NULL                                              | Calle Lira, 198                           | Santiago              | NULL   | Chile          | NULL       | +56 (0)2 635 4444   | NULL               | luisrojas@yahoo.cl            |            5 |
|         58 | Manoj      | Pareek        | NULL                                              | 12,Community Centre                       | Delhi                 | NULL   | India          | 110017     | +91 0124 39883988   | NULL               | manoj.pareek@rediff.com       |            3 |
|         59 | Puja       | Srivastava    | NULL                                              | 3,Raj Bhavan Road                         | Bangalore             | NULL   | India          | 560001     | +91 080 22289999    | NULL               | puja_srivastava@yahoo.in      |            3 |
+------------+------------+---------------+---------------------------------------------------+-------------------------------------------+-----------------------+--------+----------------+------------+---------------------+--------------------+-------------------------------+--------------+
59 rows in set (0.01 sec)

You can filter the result using a WHERE clause.

mysql> mysql> SELECT * FROM Customer
    -> WHERE Country = 'Canada';
+------------+-----------+----------+---------------+------------------------+-------------+-------+---------+------------+-------------------+-------------------+------------------------+--------------+
| CustomerId | FirstName | LastName | Company       | Address                | City        | State | Country | PostalCode | Phone             | Fax               | Email                  | SupportRepId |
+------------+-----------+----------+---------------+------------------------+-------------+-------+---------+------------+-------------------+-------------------+------------------------+--------------+
|          3 | François  | Tremblay | NULL          | 1498 rue Bélanger      | Montréal    | QC    | Canada  | H2G 1A7    | +1 (514) 721-4711 | NULL              | ftremblay@gmail.com    |            3 |
|         14 | Mark      | Philips  | Telus         | 8210 111 ST NW         | Edmonton    | AB    | Canada  | T6G 2C7    | +1 (780) 434-4554 | +1 (780) 434-5565 | mphilips12@shaw.ca     |            5 |
|         15 | Jennifer  | Peterson | Rogers Canada | 700 W Pender Street    | Vancouver   | BC    | Canada  | V6C 1G8    | +1 (604) 688-2255 | +1 (604) 688-8756 | jenniferp@rogers.ca    |            3 |
|         29 | Robert    | Brown    | NULL          | 796 Dundas Street West | Toronto     | ON    | Canada  | M6J 1V1    | +1 (416) 363-8888 | NULL              | robbrown@shaw.ca       |            3 |
|         30 | Edward    | Francis  | NULL          | 230 Elgin Street       | Ottawa      | ON    | Canada  | K2P 1L7    | +1 (613) 234-3322 | NULL              | edfrancis@yachoo.ca    |            3 |
|         31 | Martha    | Silk     | NULL          | 194A Chain Lake Drive  | Halifax     | NS    | Canada  | B3S 1C5    | +1 (902) 450-0450 | NULL              | marthasilk@gmail.com   |            5 |
|         32 | Aaron     | Mitchell | NULL          | 696 Osborne Street     | Winnipeg    | MB    | Canada  | R3L 2B9    | +1 (204) 452-6452 | NULL              | aaronmitchell@yahoo.ca |            4 |
|         33 | Ellie     | Sullivan | NULL          | 5112 48 Street         | Yellowknife | NT    | Canada  | X1A 1N6    | +1 (867) 920-2233 | NULL              | ellie.sullivan@shaw.ca |            3 |
+------------+-----------+----------+---------------+------------------------+-------------+-------+---------+------------+-------------------+-------------------+------------------------+--------------+
8 rows in set (0.00 sec)

The result we get is only the customers from Canada, 8 rows.

Leave a Reply