This page discusses SQL queries that help us view existing data/information: SHOW, SELECT, and DESCRIBE. Since SQL is standardized, the queries presented here would apply equally to other databases as well.
For the sake of consistency, we use a fictitious database to support a company that sells Toys and we call it "Blue Sky Toys". The database manages a chain of stores distributed across various locations and the database keeps a record of employees, locations, items, and inventory.
The SHOW command provides information about databases, tables, or columns. The query, "SHOW DATABASES", lists all databases present on the the MySQL server. Once we know all the databases present, then we can use "USE " query to select a database. After selecting a database, the query, "SHOW TABLES", shows all the tables in the selected databases.
We start by connecting to the MySQL database server running on the local machine. We login as root. To illustrate the queries, we have created a database "blue_sky_toys", various tables, and inserted dummy records in these tables.
[user@codingbison ~]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.1.52 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | blue_sky_toys | | mysql | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> USE blue_sky_toys; 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> SHOW TABLES; +-------------------------+ | Tables_in_blue_sky_toys | +-------------------------+ | employees | | inventory | | items | | stores | +-------------------------+ 4 rows in set (0.00 sec)
Let us summarize the above output. With "USE blue_sky_toys" statement, we are able to use the Blue Sky Toy company's database. The database has four tables: (a) employees to store information about employees, (b) stores to store (pun intended!) information about various store locations, (c) items to store information about toys being sold, and (d) inventory to store quantities of items available at various stores.
To understand these tables a little more, we use "SHOW COLUMNS" query to list columns and the column properties from tables. In fact, we can use yet another type of statement, "DESCRIBE", to view table structure.
mysql> SHOW COLUMNS FROM items; +------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+---------+-------+ | item_code | int(11) | NO | PRI | NULL | | | item_name | char(128) | YES | | NULL | | | item_type | tinyint(3) unsigned | YES | | NULL | | | item_price | decimal(10,2) | YES | | NULL | | +------------+---------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> DESCRIBE items; +------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+---------+-------+ | item_code | int(11) | NO | PRI | NULL | | | item_name | char(128) | YES | | NULL | | | item_type | tinyint(3) unsigned | YES | | NULL | | | item_price | decimal(10,2) | YES | | NULL | | +------------+---------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
The above output shows that the "items" table contains four columns: item_code (which is the primary key) that contains a unique code for each item, item_name to hold the name of the toys, item_type to hold the type of toys (toys or action-figures), and item_price that stores the price of toys.
Please note the type of these fields. The column, item_code is an integer (that can hold upto 2^32 -1 values). The column, item_name is a char type with a maximum length of 128 and so it can hold names upto 128 characters. The column, item_type is a small tinyint; a tinyint is of 1 byte size and can store 256 values. Lastly, the column, item_price is a decimal and so it can store fractional values (since prices can be fractional numbers).
Let us continue to use SHOW queries to view columns for the remaining three tables as well.
mysql> SHOW COLUMNS FROM stores; +-------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+---------+-------+ | store_id | smallint(6) | NO | PRI | NULL | | | store_location | char(128) | YES | | NULL | | +-------------------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> SHOW COLUMNS FROM employees; +-------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+---------+-------+ | employee_id | int(11) | NO | PRI | NULL | | | employee_name | char(128) | YES | | NULL | | | date_of_birth | date | YES | | NULL | | | store_id | smallint(6) | YES | | NULL | | | employee_address | char(255) | YES | | NULL | | +-------------------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> SHOW COLUMNS FROM inventory; +-------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+---------+-------+ | store_id | smallint(6) | YES | | NULL | | | item_code | int(11) | YES | | NULL | | | item_quantity | smallint(6) | YES | | NULL | | +-------------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
Let us summarize the above output.
The "stores" table has two columns; one to hold store_location_id (a primary key) and the other to provide address of that location. The "employees" table has several columns to hold a unique employee ID (primary key), employee name, their date of birth, the store location, where they work, and the address of the employee. Lastly, the "inventory" table has three columns: one to hold the location ID, where the item is present, one to hold the item_code of each item, and lastly, the quantities of each item.
Now that we have understood the structure of each tables, let us use another type of query, "SELECT" query, to see records present in these tables. Using a simple query (simplest query perhaps!), "SELECT * FROM <table_name>", we can print all the records present in the table.
We start with the "stores" table. The output (provided below) displays records from the "stores" table and we find that the company has stores in four different locations.
mysql> SELECT * FROM stores; +----------+---------------------------------+ | store_id | store_location | +----------+---------------------------------+ | 1 | 110 Ocean Drive, Springfield | | 2 | 220 Pacific Avenue, Quahog | | 3 | 330 Atlantic Way, Langely Falls | | 4 | 440 Sea Way, Springfield | +----------+---------------------------------+ 4 rows in set (0.00 sec)
For the employees table, the output (provided below) displays information about all the employees. It shows, among other things, that the company has 6 employees working in 3 different stores. We should note that there are currently no employees working at store with store_id 4, perhaps, the location is still under construction!
mysql> SELECT * FROM employees; +-------------+---------------+---------------+----------+------------------------------------+ | employee_id | employee_name | date_of_birth | store_id | employee_address | +-------------+---------------+---------------+----------+------------------------------------+ | 101 | Homer Simpson | 1955-07-23 | 1 | 742 Evergreen Terrace, Springfield | | 103 | Peter Griffin | 1967-06-28 | 2 | 31 Spooner Street, Quahog | | 112 | Ned Flanders | 1960-03-07 | 1 | 740 Evergreen Terrace, Springfield | | 120 | Marge Simpson | 1965-05-21 | 1 | 742 Evergreen Terrace, Springfield | | 121 | Lois Griffin | 1968-01-18 | 2 | 31 Spooner Street, Quahog | | 110 | Stan Smith | 1970-12-05 | 3 | 43 Cherry Street, Langely Falls | +-------------+---------------+---------------+----------+------------------------------------+ 6 rows in set (0.00 sec)
Next, we print records of the "items" table. The output (provided below) shows that company sells both toys (item_type 1) and action figures (item_type 2). Also, the company sells a total of 7 toys.
mysql> SELECT * FROM items; +-----------+-----------------+-----------+------------+ | item_code | item_name | item_type | item_price | +-----------+-----------------+-----------+------------+ | 1001 | Captain America | 2 | 29.99 | | 1002 | Alphie | 1 | 33.49 | | 1003 | Elmo | 1 | 13.49 | | 1004 | Iron Man | 2 | 25.49 | | 1005 | Super Man | 2 | 17.49 | | 1006 | Wonder Woman | 2 | 23.49 | | 1007 | Transformers | 1 | 19.99 | +-----------+-----------------+-----------+------------+ 7 rows in set (0.00 sec)
Lastly, we print the company's inventory stored in the "inventory" table. For some of the stores, store_id is still NULL; perhaps, the item is still in transit and so it has not been allocated to any of the stores. As a minor note, we find that the item with item_code 1004 (the "Iron Man" action figure) is not present in any of the stores but instead, it is in transit.
mysql> SELECT * FROM inventory; +----------+-----------+---------------+ | store_id | item_code | item_quantity | +----------+-----------+---------------+ | 1 | 1003 | 11 | | 1 | 1007 | 7 | | 2 | 1003 | 11 | | 2 | 1001 | 2 | | 2 | 1006 | 17 | | 3 | 1003 | 3 | | 3 | 1005 | 22 | | NULL | 1001 | 40 | | NULL | 1002 | 14 | | NULL | 1006 | 25 | | NULL | 1004 | 10 | +----------+-----------+---------------+ 11 rows in set (0.00 sec)
By default, a SELECT query returns all the records but more often than not, we would want to be more selective on selecting records. For such cases, we can use the WHERE keyword to provide a condition and using this keyword, SELECT returns only those records for which the provided condition holds true. The WHERE keyword can be specified after the table name(s): "SELECT * FROM table WHERE condition;".
Let us say we want to list all the employees who work at the store_id is 1.
mysql> SELECT * FROM employees WHERE store_id = 1; +-------------+---------------+---------------+----------+------------------------------------+ | employee_id | employee_name | date_of_birth | store_id | employee_address | +-------------+---------------+---------------+----------+------------------------------------+ | 101 | Homer Simpson | 1955-07-23 | 1 | 742 Evergreen Terrace, Springfield | | 112 | Ned Flanders | 1960-03-07 | 1 | 740 Evergreen Terrace, Springfield | | 120 | Marge Simpson | 1965-05-21 | 1 | 742 Evergreen Terrace, Springfield | +-------------+---------------+---------------+----------+------------------------------------+ 3 rows in set (0.34 sec)
As an other example, let us list all the employees who were born in or after 1965:
mysql> SELECT * FROM employees WHERE date_of_birth > "1965-00-00"; +-------------+---------------+---------------+----------+------------------------------------+ | employee_id | employee_name | date_of_birth | store_id | employee_address | +-------------+---------------+---------------+----------+------------------------------------+ | 103 | Peter Griffin | 1967-06-28 | 2 | 31 Spooner Street, Quahog | | 120 | Marge Simpson | 1965-05-21 | 1 | 742 Evergreen Terrace, Springfield | | 121 | Lois Griffin | 1968-01-18 | 2 | 31 Spooner Street, Quahog | | 110 | Stan Smith | 1970-12-05 | 3 | 43 Cherry Street, Langely Falls | +-------------+---------------+---------------+----------+------------------------------------+ 4 rows in set (0.00 sec)
Next, let us say, we want to get more selective and list all employees who have birthdays in July. For this, we can use a LIKE keyword; a LIKE query allows us to match records based on a pattern. LIKE can also be used for date.
As an example, "SELECT * FROM employees WHERE date_of_birth LIKE "%-07-%";" would retrieve all employees who have their birthdays in July. This is because the pattern "%-07-%";" would match with any record of the form "XXXX-07-XX".
mysql> SELECT * FROM employees WHERE employee_name LIKE "% Simpson"; +-------------+---------------+---------------+----------+------------------------------------+ | employee_id | employee_name | date_of_birth | store_id | employee_address | +-------------+---------------+---------------+----------+------------------------------------+ | 101 | Homer Simpson | 1955-07-23 | 1 | 742 Evergreen Terrace, Springfield | | 120 | Marge Simpson | 1965-05-21 | 1 | 742 Evergreen Terrace, Springfield | +-------------+---------------+---------------+----------+------------------------------------+ 2 rows in set (0.00 sec)
A WHERE clause also allows us to generate output from multiple tables. For this, we need to specify the list of tables after the FROM keyword and before the WHERE clause. Next, we can provide conditions using columns from different table. Please note that since both employees and store tables have the same name for store_id, we use "employees.store_id" and "stores.store_location" to differentiate the two columns. We use this to generate a list of employees who work in Quahog. For this, we use the store_id (in this case 2) as the key and then match stores table's location with Quahog.
mysql> SELECT employee_name, employee_address, stores.store_id FROM
-> employees, stores WHERE employees.store_id = stores.store_id
-> AND stores.store_location LIKE "%Quahog";
+---------------+---------------------------+----------+
| employee_name | employee_address | store_id |
+---------------+---------------------------+----------+
| Peter Griffin | 31 Spooner Street, Quahog | 2 |
| Lois Griffin | 31 Spooner Street, Quahog | 2 |
+---------------+---------------------------+----------+
2 rows in set (0.00 sec)
To make the query more readable and in some cases, shorter, we can consider using an alias. We can provide an alias for a column or a table using the "AS" keyword. Here is a rewrite of the above query but we rename the printed columns and the table using aliases:
mysql> SELECT employee_name AS name, employee_address AS address, str.store_id AS store
-> FROM employees AS emp, stores AS str WHERE emp.store_id = str.store_id
-> AND str.store_location LIKE "%Quahog";
+---------------+---------------------------+-------+
| name | address | store |
+---------------+---------------------------+-------+
| Peter Griffin | 31 Spooner Street, Quahog | 2 |
| Lois Griffin | 31 Spooner Street, Quahog | 2 |
+---------------+---------------------------+-------+
2 rows in set (0.00 sec)
For SELECT based output, we can use the "ORDER BY" keyword to provide output in a pre-specified order. We provide the name of the column that we wish to order when using the "ORDER BY" clause. As an example, here is a query that prints all employees, ordered by their store_id.
mysql> SELECT * FROM employees ORDER BY store_id; +-------------+---------------+---------------+----------+------------------------------------+ | employee_id | employee_name | date_of_birth | store_id | employee_address | +-------------+---------------+---------------+----------+------------------------------------+ | 101 | Homer Simpson | 1955-07-23 | 1 | 742 Evergreen Terrace, Springfield | | 112 | Ned Flanders | 1960-03-07 | 1 | 740 Evergreen Terrace, Springfield | | 120 | Marge Simpson | 1965-05-21 | 1 | 742 Evergreen Terrace, Springfield | | 103 | Peter Griffin | 1967-06-28 | 2 | 31 Spooner Street, Quahog | | 121 | Lois Griffin | 1968-01-18 | 2 | 31 Spooner Street, Quahog | | 110 | Stan Smith | 1970-12-05 | 3 | 43 Cherry Street, Langely Falls | +-------------+---------------+---------------+----------+------------------------------------+ 6 rows in set (0.00 sec)
Another useful keyword is "LIMIT" that allows us to limit the number of rows affected. For example, providing "LIMIT 4" with employees instructs MySQL to print only the first 4 records.
mysql> SELECT * FROM employees ORDER BY store_id LIMIT 4; +-------------+---------------+---------------+----------+------------------------------------+ | employee_id | employee_name | date_of_birth | store_id | employee_address | +-------------+---------------+---------------+----------+------------------------------------+ | 101 | Homer Simpson | 1955-07-23 | 1 | 742 Evergreen Terrace, Springfield | | 112 | Ned Flanders | 1960-03-07 | 1 | 740 Evergreen Terrace, Springfield | | 120 | Marge Simpson | 1965-05-21 | 1 | 742 Evergreen Terrace, Springfield | | 103 | Peter Griffin | 1967-06-28 | 2 | 31 Spooner Street, Quahog | +-------------+---------------+---------------+----------+------------------------------------+ 4 rows in set (0.00 sec)
Lastly, if the displayed output has duplicate entries, then we can trim the duplicate entries by using the "DISTINCT" keyword.
mysql> SELECT DISTINCT item_code FROM inventory ORDER BY item_code; +-----------+ | item_code | +-----------+ | 1001 | | 1002 | | 1003 | | 1004 | | 1005 | | 1006 | | 1007 | +-----------+ 7 rows in set (0.00 sec)
SQL provides various aggregation functions that operate on the entire set of records, instead of individual records: MIN() returns the minimum value of a column, MAX() returns the maximum value of a column, AVG() returns the average value of a column, and SUM() returns the sum of all the values of a column. For example, we can run a query to get the item that has the minimum price (that of item "Elmo"):
mysql> SELECT MIN(item_price) FROM items; +-----------------+ | MIN(item_price) | +-----------------+ | 13.49 | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM items WHERE item_price = 13.49; +-----------+-----------+-----------+------------+ | item_code | item_name | item_type | item_price | +-----------+-----------+-----------+------------+ | 1003 | Elmo | 1 | 13.49 | +-----------+-----------+-----------+------------+ 1 row in set (0.00 sec) mysql> SELECT item_code, item_name, MIN(item_price) FROM items; +-----------+-----------------+-----------------+ | item_code | item_name | MIN(item_price) | +-----------+-----------------+-----------------+ | 1001 | Captain America | 13.49 | +-----------+-----------------+-----------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM items LIMIT 1; +-----------+-----------------+-----------+------------+ | item_code | item_name | item_type | item_price | +-----------+-----------------+-----------+------------+ | 1001 | Captain America | 2 | 29.99 | +-----------+-----------------+-----------+------------+ 1 row in set (0.00 sec)
Please note that if we print the item_code and item_name with the MIN clause, then it prints the first record from the table (which is that of "Captain America"); we confirm it using "SELECT * FROM items LIMIT 1" query.
Likewise, running "SELECT item_code, item_name, MAX(item_price) FROM items;" would return a column with name "MAX(item_price)" and with a value of 33.49 (that of item "Alphie"). Along the same lines, running "SELECT AVG(item_price) FROM items;" would return a column with name "AVG(item_price)" and with a value of 23.347143.
Sometimes, we need to run the aggregate functions not on the entire data-set but on groups of data. A group of data can be based on a certain column. SQL provides "GROUP BY" keyword to handle such requirements. Let us create groups of records based on store_id; thus, we form 4 groups since we have 4 locations.
mysql> SELECT store_id, SUM(item_quantity) FROM inventory GROUP BY store_id; +----------+--------------------+ | store_id | SUM(item_quantity) | +----------+--------------------+ | NULL | 89 | | 1 | 18 | | 2 | 30 | | 3 | 25 | +----------+--------------------+ 4 rows in set (0.00 sec) mysql> mysql> SELECT item_code, SUM(item_quantity) FROM inventory GROUP BY item_code; +-----------+--------------------+ | item_code | SUM(item_quantity) | +-----------+--------------------+ | 1001 | 42 | | 1002 | 14 | | 1003 | 25 | | 1004 | 10 | | 1005 | 22 | | 1006 | 42 | | 1007 | 7 | +-----------+--------------------+ 7 rows in set (0.00 sec)
The GROUP BY only shows the first record of the group. In the following output, the query displays only one record for each item_code, even though each item_code has multiple records!
mysql> SELECT * FROM inventory GROUP BY item_code; +----------+-----------+---------------+ | store_id | item_code | item_quantity | +----------+-----------+---------------+ | 2 | 1001 | 2 | | NULL | 1002 | 14 | | 1 | 1003 | 11 | | NULL | 1004 | 10 | | 3 | 1005 | 22 | | 2 | 1006 | 17 | | 1 | 1007 | 7 | +----------+-----------+---------------+ 7 rows in set (0.00 sec)