Coming full circle in the MySQL Beginner Basics Series, this blog post will highlight simple SELECT queries for retrieving and reading the data in tables.
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus) and MySQL 5.7.20 for these exercises.
Blog Posts In The Series
For an overview of the series as of the time of this writing, check out the list of posts below.
- CREATE Database, User, and Table – Easy examples with MySQL
- ALTER TABLE: Examples with MySQL – Beginner Series
- Populating a MySQL table with the INSERT statement – Beginner Series.
- UPDATE and DELETE: Examples with MySQL – Beginner Series
Photo by Tobias Fischer on Unsplash
Sample Database and Table
In this blog post I will be using a mock HR database and in particular, the countries table found therein.
This database can easily be downloaded from many resources on the web, including the MySQL version in this post, and installed on your system.
All the present tables are listed below.
+--------------+
| Tables_in_hr |
+--------------+
| countries |
| departments |
| employees |
| job_history |
| jobs |
| locations |
| regions |
+--------------+
7 rows in set (0.00 sec)
And a description of the countries table I will be querying:
+--------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| COUNTRY_ID | varchar(2) | NO | PRI | NULL | |
| COUNTRY_NAME | varchar(40) | YES | | NULL | |
| REGION_ID | decimal(10,0) | YES | MUL | NULL | |
+--------------+---------------+------+-----+---------+-------+
3 rows in set (0.18 sec)
SELECT Basics
A basic SELECT query often used is the, ‘select everything’ query, as shown below:
+------------+--------------------------+-----------+
| COUNTRY_ID | COUNTRY_NAME | REGION_ID |
+------------+--------------------------+-----------+
| AR | Argentina | 2 |
| AU | Australia | 3 |
| BE | Belgium | 1 |
| BR | Brazil | 2 |
| CA | Canada | 2 |
| CH | Switzerland | 1 |
| CN | China | 3 |
| DE | Germany | 1 |
| DK | Denmark | 1 |
| EG | Egypt | 4 |
| FR | France | 1 |
| HK | HongKong | 3 |
| IL | Israel | 4 |
| IN | India | 3 |
| IT | Italy | 1 |
| JP | Japan | 3 |
| KW | Kuwait | 4 |
| MX | Mexico | 2 |
| NG | Nigeria | 4 |
| NL | Netherlands | 1 |
| SG | Singapore | 3 |
| UK | United Kingdom | 1 |
| US | United States of America | 2 |
| ZM | Zambia | 4 |
| ZW | Zimbabwe | 4 |
+------------+--------------------------+-----------+
25 rows in set (0.03 sec)
The * character denotes all in this context. It is saying return everything from the table there is, in the query results.
Another common SELECT query, retrieves a total count of records found in the table.
This is a handy query to run when exploring new tables you are unfamiliar with.
+----------+
| COUNT(*) |
+----------+
| 25 |
+----------+
1 row in set (0.02 sec)
Cool. The countries table has 25 records.
You can also restrict how many records are returned by appending the LIMIT clause to the end of aSELECT query like so.
-> FROM countries
-> LIMIT 5;
+------------+--------------+-----------+
| COUNTRY_ID | COUNTRY_NAME | REGION_ID |
+------------+--------------+-----------+
| AR | Argentina | 2 |
| AU | Australia | 3 |
| BE | Belgium | 1 |
| BR | Brazil | 2 |
| CA | Canada | 2 |
+------------+--------------+-----------+
5 rows in set (0.00 sec)
The syntax LIMIT some_number only returns at most, that many records and can possibly return less. (ie.. You query a table, limiting the result set to just 5 rows, but the table has only 4 rows to begin with.)
The real power of SELECT is the ability to list out specific column names you want to be returned.
Below I fetch just the COUNTRY_NAME column.
-> FROM countries
-> LIMIT 5;
+--------------+
| COUNTRY_NAME |
+--------------+
| Argentina |
| Australia |
| Belgium |
| Brazil |
| Canada |
+--------------+
5 rows in set (0.00 sec)
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
However, you can list out multiple columns. Just separate them with commas as shown below.
-> FROM countries
-> LIMIT 3;
+------------+--------------+
| COUNTRY_ID | COUNTRY_NAME |
+------------+--------------+
| AR | Argentina |
| AU | Australia |
| BE | Belgium |
+------------+--------------+
3 rows in set (0.00 sec)
Mixed order in a SELECT query is fine too,
-> FROM countries
-> LIMIT 3;
+--------------+------------+
| COUNTRY_NAME | COUNTRY_ID |
+--------------+------------+
| Argentina | AR |
| Australia | AU |
| Belgium | BE |
+--------------+------------+
3 rows in set (0.00 sec)
From the above examples, you can surmise that the common syntax for SELECT queries is:
Distinct Columns
You may have noticed the REGION_ID field has several identical values.
Oftentimes, you may want only one value returned, where there are many alike.
The DISTINCT modifier basically tosses out duplicate rows.
Have a look.
+-----------+
| REGION_ID |
+-----------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 3 |
| 3 |
| 3 |
| 3 |
| 3 |
| 3 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
+-----------+
25 rows in set (0.00 sec)
“What are the different region id’s to choose from?” you wonder.
-> FROM countries;
+-----------+
| REGION_ID |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
+-----------+
4 rows in set (0.06 sec)
In the above SELECT query, DISTINCT returns non-duplicates.
Now you know what region_id’s are in the table to choose from.
One-Offs and Ad Hoc Queries
You can run a quick ad-hoc SELECT to perform calculations on the fly as well.
Some addition,
+--------+
| 10 + 2 |
+--------+
| 12 |
+--------+
1 row in set (0.02 sec)
or returning an all-uppercase version of a name using the UPPER() function.
+---------------+
| UPPER('josh') |
+---------------+
| JOSH |
+---------------+
1 row in set (0.04 sec)
Explore More
This blog post is just a quick introduction to simple SELECT queries. SELECT is a power-house of a clause and is considered the workhorse of SQL. SELECT queries can be rich, complex, and super specific.
I encourage you to explore the official MySQL 5.7 Online Manual for in-depth study, clarification, and options.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
3 thoughts on “SELECT clause queries – MySQL Beginner Basics Series.”