How to use WHERE statements in MySQL

Within the realm of MySQL database admin, you will at some point find yourself sifting through the massive collection of data found in your data center databases. This is all fine and good when you have a GUI tool for the task.

But what if you need to drop to the command line? What do you do? You make use of WHERE statements. What is a A WHERE statement? Simple. This tool is used in MySQL queries to filter records so that they meet a specific condition. Any rows that do not meet the specified condition are eliminated from the results of the query. WHERE statements are very handy to use, and I'm going to show you how.

SEE: Disaster recovery and business continuity plan (Tech Pro Research)

What you need

To make this work, you need an installation of MySQL server up and running. I'll assume you have that available. I will also assume you have data on your MySQL server. If not, follow the steps in How to add data into MySQL tables from the command line (I'll actually follow the example shown in that article).

With that said, let's get to work.

Using the database

In the above mentioned piece, we created a database called TECHREPUBLIC and added data to it. In order to make use of the WHERE statement, we must first change to the TECHREPUBLIC database. So log into your MySQL prompt with the command:

mysql -u root -p

If you get an error, when attempting to log in to the MySQL prompt, you might have to instead issue the command:

sudo mysql -u root -p

Change to the TECHREPUBLIC database with the command:

USE TECHREPUBLIC;

You should now see that you are using the correct database (Figure A).

SELECT and WHERE

In order to use the WHERE statement, you must first know how to use the SELECT statement. What this statement does is specify, which columns to read data from. So, in our TECHREPUBLIC database we have a table called MEMBERS2. In that table we have columns:

• firstname
• lastname
• email

Say we want to view an entry in the lastname column that contains 'wallen.' To do that, we use both the SELECT and WHERE statements, such that SELECT instructs the query to select from the lastname column and WHERE says to filter out every instance but those that do not equal a specified string. With WHERE statements you can test for the following:

• Equality, using =.
• Inequality, using !=.
• Less-than, using <.
• Less-than or equal-to, using <=.
• Greater than, using >.
• Greater than or equal to, using >=.
• BETWEEN
• IN
• EXISTS
• LIKE
• IS NULL
• IS NOT NULL

For our example, we'll use the equality test.

So, remember, we're testing the lastname column, in the MEMBERS2 table, for entries that are equal to 'wallen'. Here's how to do that (from the MySQL prompt):

SELECT * FROM MEMBERS2 WHERE lastname = 'wallen';

The results will show only those entries whose lastname column is equal to 'wallen' (Figure B).

If you want to filter out all entries with the last name 'wallen', you could use the inequality filter like so:

SELECT * FROM MEMBERS2 WHERE lastname != 'wallen';

The results from that query (Figure C) will list all entries but those whose lastname column include 'wallen'.

Using Wildcards

Let's say, for example, that you can't remember the full last name, but you know it begins with a 'w'. How do you use a WHERE statement to produce such an entry? With a wildcard. For MySQL, the wildcard is the % character. In order to make use of the wildcard, you cannot use the equality test. Instead you must use the LIKE test, as such:

SELECT * FROM MEMBERS2 WHERE lastname LIKE 'w%';

The results will display any lastname column entry that starts with a w (Figure D).

And that's the gist of using the WHERE statement with MySQL. When you don't have the power of a user-friendly GUI, and you need to search for data, this statement will become incredibly useful.

Also see

• How to export and import MySQL databases (TechRepublic)
• How to install phpMyAdmin on Ubuntu 18.04 (TechRepublic)
• How to install MySQL on CentOS 7 (TechRepublic)
• How to add data into MySQL tables from the command line (TechRepublic)
• MariaDB unifies its platform (ZDNet)
• The data center is dead: Here's what comes next (ZDNet)
• Best cloud services for small businesses (CNET)
• DevOps: More must-read coverage (TechRepublic on Flipboard)