An Overview of Queries in MySQL featured image

An Overview of Queries in MySQL

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:

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:

Then, create a database by running the following command:

Next, let’s use our database using the following command:

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):

We will then populate our table with data:

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.

Populate the table with data:

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:

We will use the query syntax to return the meal column from the vacation table:

Our output will be as follows:

You can also select multiple columns by using a column to separate them:

Output:

You can also use an asterisk (*) if you want to represent all columns in the table:

Output:

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:

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:

The query will then return:

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:

The query will return:

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:

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:

The following results will be returned:

MIN is used to find the smallest value within a specified column:

The query will output:

MAX is used to find the largest numeric value in a given column:

The expected output is:

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:

Here is what the output will be:

The MAX function returns the last value alphabetically:

This is what the output will be:

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:

The output will be:

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:

The output will be as follows:

The output is in ascending order to sort in descending order close the query with the word DESC:

Take a look at the output:

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:

The query will output:

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:

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:

The output will be:

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:

Then, re-run the SELECT statement with the JOIN clause:

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:

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:

This is now the RIGHT JOIN clause:

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:

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:

The query removes duplicate entries. This is the default behaviour of the UNION operator:

To return all entries (plus duplicates) use the UNION ALL operator:

Output:

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:

The query will return:

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:

The query will return:

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:

Happy Computing!

About Hark Labs

Software Engineer and enthusiast about new technologies