MySQL Part 5: Refining Select Statements

Introduction

In this article, we will walk through some popular SELECT statements in MySQL.

In Part 4 of this series, we created new columns of data using various string functions.  Click the link below for more information.

MySQL Part 4: String Functions

To begin your MySQL session in the goormIDE, please be sure to type this code into the terminal.

mysql-ctl cli

Please ensure that the bike_shop.sql file is selected and available.  If not, double-click it on the left-hand side.  This is where we will type most of our code. If there is information saved in there, go ahead and clear out that information.  

DISTINCT Function

The DISTINCT function will display how many unique values there are for a particular column or set of columns.  First, let's make a simple SELECT statement on the orderlines table.

SELECT order_id FROM orderlines;

Be sure to save and run the source file in the terminal section.

SOURCE bike_shop.sql;

The results of the query are listed below.

 

As you can tell from the limited screenshot, many order numbers are listed multiple times.  If we want to know how many unique order_ids we have, we can use the DISTINCT function.  See example below

SELECT DISTINCT order_id FROM orderlines;

ORDER BY Function

Another useful function in MySQL is the ORDER BY function.  This allows you to sort by one or more columns.  Listed below is a simple SELECT statement without using the ORDER BY clause.

SELECT 
    order_id,
    order_line,
    order_date 
    FROM orderlines;

To sort by order date in descending order, just add the following to your SELECT statement.

SELECT 
    order_id,
    order_line,
    order_date 
    FROM orderlines
    
    ORDER BY order_date DESC;

LIMIT Function

The LIMIT function is beneficial for selecting the first n rows of the data set.  For example, if you wanted to choose the highest-priced bike, the code below would limit your results to one record. 

SELECT 
    bike_id,
    model,
    description,
    price
    FROM bikes
    
    ORDER BY price DESC
    
    LIMIT 1;

WHERE Function

The WHERE function acts as a filter based on the criteria you provide.  You can have one or more criteria that you use to filter.  In the query below, we are only interested in selecting bikes with a price greater than $5,000.

SELECT 
    bike_id,
    model,
    description,
    price
    FROM bikes
    
    WHERE price > 5000
    
    ORDER BY price DESC;

LIKE Function

The LIKE function allows you to search for a wildcard within a column.  To find all the bike models that contain "Hi-Mod" anywhere within the text, you can use the code below.  The article listed below goes into great detail discussing the LIKE and wildcard filter options in MySQL.

https://www.techonthenet.com/mysql/like.php 

SELECT 
    bike_id,
    model,
    description,
    price
    FROM bikes
    
    WHERE model LIKE ('%Hi-Mod%');
    
    ORDER BY price DESC;
    
    LIMIT 1;

Obviously, once you start working with real data sets, you will most likely use many combinations of these functions together.  In the next article, we will discuss aggregations.

MySQL Part 6: Aggregations

For a complete list of the entire set of MySQL-related articles, please see the links below.

MySQL Part 1: goormIDE
MySQL Part 2: Create Database
MySQL Part 3: Create Tables
MySQL Part 4: String Functions
MySQL Part 5: Refining Select Statements
MySQL Part 6: Aggregations
MySQL Part 7: Dates
MySQL Part 8: Logical Operations
MySQL Part 9: Joins