Sustainability in business is difficult without proper data management. Building a well-defined system is essential to managing internal and external operations. Detailed insight into business data helps to understand the overall workflow and measure performance. Moreover, using databases saves time and allows to access information effortlessly.
What is a database?
A database is an organized system used to store information in a secure way. It offers categorization and allows for the creation of tables to sort similar kinds of items under one group. Tables provide options to list information in multiple rows and columns. As a result, the quality and consistency of data improve. Besides storage and easy-to-access capabilities, databases play a significant role in safeguarding privacy and securing systems. Whether ad hoc or part of a process coded into an application, retrieving data is one of the most critical aspects of database management.
A query in relational DMS is any command used to retrieve data from a table. SELECT statements are almost always used in Structured Query Language (SQL) queries. There are several methods to retrieve information from a database. Generally, developers prefer the command line option because it is fast and effective. It allows submitting queries seamlessly.
In this guide, we will introduce you to MySQL and discuss how to work with queries in MySQL. Let’s Start!
Prerequisites
To follow along with this tutorial, you’ll need the following:
-
The latest version of Ubuntu is installed on your system.
-
System users must have sudo privileges.
-
If you face difficulty accessing your sudo account, consider changing the MySQL root password.
-
-
MySQL is set up on your server.
Step 1 — Create and Set Up a Database
Before we start and write our first SQL query, we need to create a database and add tables to it. After that, we will populate the tables using sample data. Working with databases will help you understand the basic setup and instill confidence in working with queries.
Hands-on experience is the most effective way to build concepts and understand the importance of databases. In this guide, we will walk you through the use of databases and their significance to solve real-world problems. Let’s check a scenario to understand how databases can help organize records in a structured way.
Scenario: We will create a database where a group of college students is going out for a vacation to celebrate their birthdays. Before going on the holiday, they plan to participate in a friendly swimming competition. Also, every friend in the group plans a to-do list of activities to make their trip thrilling and enjoyable.
First, open MySQL as the root user:
|
1 |
$ sudo mysql |
Then, create a database by running the following command:
|
1 2 3 |
mysql> CREATE DATABASE `vacation`; Query OK, 1 row affected (0.52 sec) |
Next, let’s use our database using the following command:
|
1 |
mysql> USE vacation; |
After that, we will create tables on our database. Name the first table as celebration. Our table will have columns for the names of our friends (name), the tournaments they have won (wins) and also a column for their best time (time):
|
1 2 3 4 5 6 7 |
mysql> CREATE TABLE celebration ( -> name varchar(50), -> wins real, -> time real -> ); Query OK, 0 rows affected (2.03 sec) |
We will then populate our table with data:
|
1 2 3 4 5 6 7 8 9 |
mysql> INSERT INTO celebration (name, wins, time) VALUES('Austin', '4', '15.5'), ('Ivan','7','9'), ('Aisha','10','5'), ('Zane','13','7.5'); Query OK, 4 rows affected (0.29 sec) Records: 4 Duplicates: 0 Warnings: 0 |
Then, create another table within our database to store information about their favourite birthday activities. We will create a table called vacation and have the following columns:
|
List |
Detail |
|
name: |
Store the name of each friend. |
|
birthdate: |
Tracks the birth date of each individual. |
|
activity: |
Keeps a record of their favourite activity. |
|
Destination: |
Stores the information of the favourite destination of each individual. |
|
meal: |
Tracks the favorite meal liked by an individual. |
|
1 2 3 4 5 6 7 8 |
mysql> CREATE TABLE vacation ( name varchar(50), birthdate date, activity varchar(50), destination varchar(50), meal varchar(50) ); Query OK, 0 rows affected (0.09 sec) |
Populate the table with data:
|
1 2 3 4 5 6 7 8 9 10 |
mysql> CREATE TABLE vacation ( name varchar(50), birthdate date, activity varchar(50), destination varchar(50), meal varchar(50) ); Query OK, 0 rows affected (0.09 sec) Records: 4 Duplicates: 0 Warnings: 0 |
You are now done setting up your database.
Step 2 — Get Started with SELECT Statements
Queries in SQL normally begin with SELECT. It is used in queries to specify which columns in a table should be returned with the results. A query must also always include FROM, which is used to specify the table that the statement will query.
Queries follow the syntax below:
|
1 |
mysql> SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply; |
We will use the query syntax to return the meal column from the vacation table:
|
1 |
mysql> SELECT meal FROM vacation; |
Our output will be as follows:
|
1 2 3 4 5 6 7 8 9 10 |
+-------+ | meal | +-------+ | Steak | | Sushi | | Fries | | Tofu | +-------+ 4 rows in set (0.00 sec) |
You can also select multiple columns by using a column to separate them:
|
1 |
mysql> SELECT name, destination FROM vacation; |
Output:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+-------------+ | name | destination | +--------+-------------+ | Austin | Maldives | | Ivan | Mauritius | | Aisha | Colorado | | Zane | Bora Bora | +--------+-------------+ 4 rows in set (0.00 sec) |
You can also use an asterisk (*) if you want to represent all columns in the table:
|
1 |
mysql> SELECT * FROM celebration; |
Output:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------+------+ | name | wins | time | +--------+------+------+ | Austin | 4 | 15.5 | | Ivan | 7 | 9 | | Aisha | 10 | 5 | | Zane | 13 | 7.5 | +--------+------+------+ 4 rows in set (0.00 sec) |
If you want to filter records that meet a specified condition, you use WHERE. The rows that don’t meet the specified condition are eliminated from the results. The WHERE clause uses the following syntax:
|
1 |
mysql> . . . WHERE column_name comparison_operator value |
It is a comparison operator that defines how the specified column should be compared against the value. Common SQL comparison operators include:
|
Operator |
Use |
|
= |
Equality |
|
!= |
Inequality |
|
< |
Less than |
|
> |
Greater than |
|
<= |
Less than or equal to |
|
>= |
Greater than or equal to |
|
BETWEEN |
Tests whether the value lies within the given range. |
|
IN |
Tests whether a row’s value is contained in a set of specified values. |
|
EXISTS |
Tests whether a row exists |
|
LIKE |
Tests whether a value matches the specified string |
|
IS NULL |
Tests for null values |
|
IS NOT NULL |
Tests for all values other than NULL |
If you wanted to find Aisha’s favorite destination you could use the query below:
|
1 |
mysql> SELECT destination FROM vacation WHERE name ='Aisha'; |
The query will then return:
|
1 2 3 4 5 6 7 |
+-------------+ | destination | +-------------+ | Colorado | +-------------+ 1 row in set (0.03 sec) |
SQL supports the use of wildcard characters, which are especially useful in WHERE clauses. Percentage signs ( %) denote zero or more unknown characters, while underscores ( _) denote a single unknown character. These are helpful if you want to find a specific entry in a table but aren’t sure about the entry.
For instance, if you had forgotten the favorite destination of a friend and only knew the letter, it starts with, for example, “m.” You can find the destination name by use of the following query:
|
1 |
mysql> SELECT destination FROM vacation WHERE destination LIKE 'm%'; |
The query will return:
|
1 2 3 4 5 6 7 8 |
+-------------+ | destination | +-------------+ | Maldives | | Mauritius | +-------------+ 2 rows in set (0.04 sec) |
When working with databases, you may encounter columns or tables with relatively long or difficult-to-read names. In these cases, you can make the names more readable by using the AS keyword to create an alias. Aliases created with AS are only valid for the duration of the query for which they were created:
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT name AS E, destination as D, activity as A FROM vacation; +--------+-----------+-------------------+ | E | D | A | +--------+-----------+-------------------+ | Austin | Maldives | skiing | | Ivan | Mauritius | paragliding | | Aisha | Colorado | mountain climbing | | Zane | Bora Bora | fishing | +--------+-----------+-------------------+ 4 rows in set (0.00 sec) |
Step 3 — Introduction to Aggregate Functions
When working with data, you don’t always want to see the data itself. You’d rather have information about the data. By issuing a SELECT query, you can interpret or run calculations on your data using the SQL syntax. These are referred to as aggregate functions.
The COUNT function counts and returns the number of rows that meet a specific set of criteria. For example, if you want to know how many friends prefer going to the Maldives, you can use the following query:
|
1 |
mysql> SELECT COUNT(destination) FROM vacation WHERE destination = 'Maldives'; |
The following results will be returned:
|
1 2 3 4 5 6 7 |
+--------------------+ | COUNT(destination) | +--------------------+ | 1 | +--------------------+ 1 row in set (0.06 sec) |
MIN is used to find the smallest value within a specified column:
|
1 |
mysql> SELECT MIN(wins) FROM celebration; |
The query will output:
|
1 2 3 4 5 6 7 |
+-----------+ | MIN(wins) | +-----------+ | 4 | +-----------+ 1 row in set (0.02 sec) |
MAX is used to find the largest numeric value in a given column:
|
1 |
mysql> SELECT MAX(wins) FROM celebration; |
The expected output is:
|
1 2 3 4 5 6 7 |
+-----------+ | MAX(wins) | +-----------+ | 13 | +-----------+ 1 row in set (0.03 sec) |
Both the MIN and MAX functions can be used on numeric and alphabetic data. When applied to a column of string values, the MIN function returns the first value alphabetically.
The MIN functions return the first value alphabetically:
|
1 |
mysql> SELECT MIN(name) FROM celebration; |
Here is what the output will be:
|
1 2 3 4 5 6 7 |
+-----------+ | MIN(name) | +-----------+ | Aisha | +-----------+ 1 row in set (0.00 sec) |
The MAX function returns the last value alphabetically:
|
1 |
mysql> SELECT MAX(name) FROM celebration; |
This is what the output will be:
|
1 2 3 4 5 6 7 |
+-----------+ | MAX(name) | +-----------+ | Zane | +-----------+ 1 row in set (0.00 sec) |
Step 4 — Manipulate Query Outputs
Another popular clause that is used is the GROUP BY clause. It is used when performing an aggregate function on one column but in relation to matching values in another:
|
1 |
mysql> SELECT COUNT(name), activity FROM vacation GROUP BY activity; |
The output will be:
|
1 2 3 4 5 6 7 8 9 10 |
+-------------+-------------------+ | COUNT(name) | activity | +-------------+-------------------+ | 1 | skiing | | 1 | paragliding | | 1 | mountain climbing | | 1 | fishing | +-------------+-------------------+ 4 rows in set (0.04 sec) |
To sort query results, use the ORDER BY clause. Numeric values are sorted ascending by default, while text values are sorted alphabetically. The query below lists the name and birthdate columns, but sorts the results by birthdate:
|
1 |
mysql> SELECT name, birthdate FROM vacation ORDER BY birthdate; |
The output will be as follows:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------------+ | name | birthdate | +--------+------------+ | Zane | 1996-01-01 | | Aisha | 1999-07-24 | | Austin | 2002-01-07 | | Ivan | 2010-12-18 | +--------+------------+ 4 rows in set (0.04 sec) |
The output is in ascending order to sort in descending order close the query with the word DESC:
|
1 |
mysql> SELECT name, birthdate FROM vacation ORDER BY birthdate DESC; |
Take a look at the output:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------------+ | name | birthdate | +--------+------------+ | Ivan | 2010-12-18 | | Austin | 2002-01-07 | | Aisha | 1999-07-24 | | Zane | 1996-01-01 | +--------+------------+ 4 rows in set (0.00 sec) |
The HAVING clause was added to SQL to provide similar functionality to the WHERE clause while also being compatible with aggregate functions. The difference between these two clauses is that WHERE is for referring to individual records and HAVING refers to group records. To that end, the GROUP BY clause must be present whenever a HAVING clause is used:
|
1 |
mysql> SELECT COUNT(name), activity FROM vacation GROUP BY activity HAVING COUNT(name) >= 1; |
The query will output:
|
1 2 3 4 5 6 7 8 |
+-------------+-------------------+ | COUNT(name) | activity | +-------------+-------------------+ | 1 | skiing | | 1 | paragliding | | 1 | mountain climbing | | 1 | fishing | +-------------+-------------------+ |
The COUNT is 1 on all because no two friends like the same activity.
Step 5 — Query Multiple Tables
The JOIN clause can be used in a query result to combine rows from two or more tables. It accomplishes this by locating a related column between the tables and sorting the output appropriately.
SELECT statements that include a JOIN clause follow the syntax below:
|
1 2 3 |
mysql> SELECT table1.column1, table2.column2 mysql> FROM table1 mysql> JOIN table2 ON table1.related_column=table2.related_column; |
If you wanted to buy each of your friends a trophy for their wins while swimming on their birthdays, you could create a query that will join both tables to help you find all the information you want with a single query:
|
1 2 3 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate FROM celebration JOIN vacation ON celebration.name=vacation.name; |
The output will be:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------+------------+ | name | wins | birthdate | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | +--------+------+------------+ 4 rows in set (0.00 sec) |
This is an inner JOIN clause. That is because it selects all the records that have matching values in both tables and prints them to a result set. Records that don’t match the query are not included. We can include a new row in our tables that does not correspond with any entry:
|
1 2 3 4 |
mysql> INSERT INTO celebration(name,wins,time) VALUES('Ella', '1', '120'); Query OK, 1 row affected (0.01 sec) |
|
1 2 3 4 |
mysql> INSERT INTO celebration(name,wins,time) VALUES('Ella', '1', '120'); Query OK, 1 row affected (0.01 sec) |
Then, re-run the SELECT statement with the JOIN clause:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate -> FROM celebration -> JOIN vacation ON celebration.name=vacation.name; +--------+------+------------+ | name | wins | birthdate | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | +--------+------+------------+ 4 rows in set (0.00 sec) |
Because the celebration table has no entry for Peter and the vacation table has no entry for Ella, those records are absent.
We can return all the records from one of the tables by using an outer JOIN clause. This can be either a LEFT JOIN or a RIGHT JOIN. A LEFT JOIN returns all records from the left table and only matched records from the right table. The left table in the context of outer joins is the one referenced by the FROM clause, and the right table is any table referenced after the JOIN statement.
Run the query again but use a LEFT JOIN clause:
|
1 2 3 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate FROM celebration LEFT JOIN vacation ON celebration.name=vacation.name; |
The command will return all records from the left table ( celebration) even if it does not have a corresponding record in the right table. When there isn’t a matching record on the right table, it is returned as NULL:
|
1 2 3 4 5 6 7 8 9 10 11 |
+--------+------+------------+ | name | wins | birthdate | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | | Ella | 1 | NULL | +--------+------+------------+ 5 rows in set (0.00 sec) |
This is now the RIGHT JOIN clause:
|
1 2 3 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate FROM celebration RIGHT JOIN vacation ON celebration.name=vacation.name; |
All values from the right table will be returned (vacation). Because Peter’s birthdate is recorded in the right table and not on the left table name and wins, columns will return NULL values in those rows:
|
1 2 3 4 5 6 7 8 9 10 11 |
+--------+------+------------+ | name | wins | birthdate | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | | NULL | NULL | 1991-05-03 | +--------+------+------------+ 5 rows in set (0.01 sec) |
You can use the UNION clause instead of JOIN to query records from multiple tables. The UNION operator differs from the JOIN clause in that it combines the results of two SELECT statements into a single column rather than printing results from multiple tables as unique columns using a single SELECT statement.
You can run this query to illustrate:
|
1 |
mysql> SELECT name FROM celebration UNION SELECT name FROM vacation; |
The query removes duplicate entries. This is the default behaviour of the UNION operator:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
+--------+ | name | +--------+ | Austin | | Ivan | | Aisha | | Zane | | Ella | | Peter | +--------+ 6 rows in set (0.00 sec) |
To return all entries (plus duplicates) use the UNION ALL operator:
|
1 |
mysql> SELECT name FROM celebration UNION ALL SELECT name FROM vacation; |
Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
+--------+ | name | +--------+ | Austin | | Ivan | | Aisha | | Zane | | Ella | | Austin | | Ivan | | Aisha | | Zane | | Peter | +--------+ 10 rows in set (0.00 sec) |
Subqueries are another method for querying multiple tables. Subqueries are queries that are enclosed within another query (also known as inner or nested queries). These are useful when you want to compare the results of a query to the results of a separate aggregate function.
We will use the example of trying to find which friend has won more swimming tournaments than Ella. Instead of querying how many matches, Ella has won and then running another query to see who has won more games than that, you can calculate both with a single query:
|
1 2 3 4 |
mysql> SELECT name, wins FROM celebration -> WHERE wins > ( -> SELECT wins FROM celebration WHERE name = 'Ella' -> ); |
The query will return:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------+ | name | wins | +--------+------+ | Austin | 4 | | Ivan | 7 | | Aisha | 10 | | Zane | 13 | +--------+------+ 4 rows in set (0.06 sec) |
If you wanted to take your friends on a surprise vacation, you can use a query to see who has the most wins and return their destination:
|
1 2 3 4 |
mysql> SELECT name, destination, activity, meal -> FROM vacation -> WHERE name = ( SELECT name FROM celebration -> WHERE wins = (SELECT MAX(wins) FROM celebration)); |
The query will return:
|
1 2 3 4 5 6 7 |
+------+-------------+----------+------+ | name | destination | activity | meal | +------+-------------+----------+------+ | Zane | Bora Bora | fishing | Tofu | +------+-------------+----------+------+ 1 row in set (0.00 sec) |
This statement contains a subquery within a subquery.
Conclusion
Query generation is one of the most common tasks in database management. There are several database administration tools like phpMyAdmin and pgAdmin that you can use to work with queries and visualize their results. However, the SELECT statement from the command line is the most preferred choice because of its ease of use and excellent control.
Furthermore, there are many tutorials on databases that you can explore from our blog:
- SQLite vs MySQL vs. PostgreSQL: Relational Database Management Systems Compared
- Quick Solutions to Repair Corrupted Tables in MySQL: A Tutorial
- How to Install MySQL on Centos 7
- MySQL User — Create and Grant Permissions
- Setting Up Replication in MySQL on Ubuntu
Happy Computing!
- How To Enable, Create and Use the .htaccess File: A Tutorial - March 8, 2023
- An Overview of Queries in MySQL - October 28, 2022
- Introduction to Cookies: Understanding and Working with JavaScript Cookies - October 25, 2022
- An Overview of Data Types in Ruby - October 24, 2022
- The Architecture of Iptables and Netfilter - October 10, 2022