Lesson 4 - MySQL step by step: Selecting Data (Searching)

In the previous lesson, MySQL step by step: Inserting And Deleting Table Data, we learned to create and delete entries. Today, we're going to focus on the most beautiful part, and that's selecting data. We'll query data or, if you want, search for data in a database table.

Data selection is the key feature of every database that makes it easy to select complex data with relatively simple queries. From a simple user selection by his id (e.g. to view his details in the application), we can search for users who meet certain properties, order the results according to different criteria, or even join multiple tables in the query, use different functions, and nest queries into others (more about that in the further lessons).

Test data

Before trying our queries, it's always a good idea to have some test data to work with so we don't have just our 4 users. Let's insert some entries to our user table to work with. I've prepared something for you. First, empty your table (so we have the same data):

DELETE FROM `user`;

Next, run the following SQL query:

INSERT INTO `user` (
        `first_name`,
        `last_name`,
        `birth_date`,
        `articles_count`
)
VALUES
('John',  'Smith',  '1984-11-03', 17),
('Steven', 'Murphy', '1942-10-17', 12),
('George', 'Lam', '1958-7-10', 5),
('Linda', 'Martin', '1935-5-15', 6),
('Donald', 'Brown', '1967-4-17', 2),
('Aron', 'Heaven', '1995-2-20', 1),
('Paul', 'Lee', '1984-4-18', 1),
('David', 'Clark', '1973-5-14', 3),
('Mark', 'Wilson', '1969-6-2', 7),
('Sarah', 'Johnson', '1962-7-3', 8),
('Charles', 'Lopez', '1974-9-10', 0),
('Jennifer', 'Williams', '1976-10-2', 1),
('Daniel', 'Jones', '1948-11-3', 1),
('Betty', 'Miller', '1947-5-9', 1),
('Michelle', 'Davis', '1956-3-7', 0),
('Mary', 'Taylor', '1954-2-11', 5),
('Barbara', 'Thomas', '1978-1-21', 3),
('Donna', 'Johnson', '1949-7-26', 12),
('Joseph', 'Murphy', '1973-7-28', 4),
('Helen', 'Murphy', '1980-8-11', 8),
('Jeff', 'Moore', '1982-9-30', 18),
('Anthony', 'Jackson', '1961-1-15', 2),
('Nancy', 'Thompson', '1950-8-29', 4),
('Edward', 'White', '1974-2-26', 5),
('Lucy', 'Harris', '1983-3-2', 2),
('Paul', 'Walker', '1991-5-1', 9),
('Carol', 'Young', '1992-12-17', 9),
('James', 'Baker', '1956-11-15', 4),
('Patricia', 'Adams', '1953-10-20', 6),
('Lisa', 'Green', '1967-5-6', 3),
('John', 'Johnson', '1946-3-10', 6);

We have 31 users in the database. This should be enough to test basic queries.

(By the way, note that we can specify multiple VALUES at a time in the INSERT statement, so multiple rows are inserted by a single query).

Querying

In phpMyAdmin, you can query data (search/select) by using the Search option in the top bar. You can try it, just enter some value into a value column. If you specify more than one, multiple values will be used in the search. Don't worry about the operators for now, we're going to explain them later.

phpMyAdmin was initially a kind of helper, but now it's no longer interesting for us. We're going to use it just to run our queries and display the results.

After the search, phpMyAdmin shows us a SELECT query. It always generates something more than is really needed. The basic query for selecting all Johns from our table would look like this:

SELECT * FROM `user` WHERE `first_name` = 'John';

The command is probably understandable, the asterisk indicates that we want to select all the columns.

As the result of the query, phpMyAdmin shows us something like this:

8    John   Smith       1984-11-03      17
37   John   Johnson     1946-3-10       6

Tables usually have a lot of columns, and we're usually interested only in some of them. In order not to overload the database by transferring loads of unnecessary data back to our application, we'll always try to specify just the columns we need. Let's suppose we only need the last names of the people named John and the number of their articles. Let's modify the query:

SELECT `last_name`, `articles_count` FROM `user` WHERE `first_name` = 'John';

The result:

Smith       17
Johnson     6

Don't be lazy indeed, and if you don't need almost all the columns, list just the values you're interested in at the moment in your SELECT clauses. Always try to limit the condition as much as possible at the database level, don't pull the entire table into your application to filter it there. Let's say, your application wouldn't be very fast that way The list of columns we want the query to return has nothing to do with other columns we use in the query. So we can search by ten columns but return only one.

As with DELETE, a query without WHERE will also work:

SELECT * FROM `user`;

In this case, all the users will be selected from the table.

As for conditions, it's the same as for DELETE, the WHERE clause works the same here. Let's try it. We'll select all users born after 1960 and with a number of articles greater than 5:

SELECT * FROM `user` WHERE `birth_date` >= '1960-1-1' AND `articles_count` > 5;

The result:

8     John      Smith        1984-11-03    17
16    Mark      Wilson       1969-06-02    7
17    Sarah     Johnson      1962-07-03    8
27    Helen     Murphy       1980-08-11    8
28    Jeff      Moore        1982-09-30    18
33    Paul      Walker       1991-05-01    9
34    Carol     Young        1992-12-17    9

Operators

You can surely use basic operators =, >, <, >=, <=, != already. In SQL, there's more of them, let's talk about LIKE, IN, and BETWEEN.

LIKE

LIKE allows us to search for text values by part of the text. It works similarly as the = (equal) operator, but we can use 2 wildcards:

• % (percent) indicates any number of arbitrary characters.
• _ (underscore) indicates one arbitrary character.

Let's try some queries with the LIKE operator. We'll find last names of people starting with W:

SELECT `last_name` FROM `user` WHERE `last_name` LIKE 'w%';

As usual, we enter the text we're searching for in apostrophes, we put wildcards just in some places. The search is case-insensitive. The query result is going to be as follows:

Wilson
Williams
White
Walker

Now, let's try to find five-letter-long last names that have O as the 2nd character:

SELECT `last_name` FROM `user` WHERE `last_name` LIKE '_o___';

The result:

Lopez
Jones
Moore
Young

Now you probably have an idea how LIKE works. We could come up with lots of usages, mostly we use it with percentage signs on both sides for full-text search (e.g. to search for a phrase in an article).

IN == IN allows us to search by a list of values. Let's make a list of names and search for the users with the following names:

SELECT `first_name`, `last_name` FROM `user` WHERE `first_name` IN ('John', 'Paul', 'Barbara');

The result:

John        Smith
Paul        Lee
Barbara     Thomas
Paul        Walker
John        Johnson

The IN operator is also used in sub-queries, but we still have enough time for that

BETWEEN

The last operator to explain today is BETWEEN. It's nothing more than a shortened condition >= AND <=. We already know that we can compare dates naturally, so let's find the users, who were born between 1980 and 1990:

SELECT `first_name`, `last_name`, `birth_date` FROM `user` WHERE `birth_date` BETWEEN '1980-1-1' AND '1990-1-1';

We write AND between the 2 values.

The result:

John        Smith       1984-11-03
Paul        Lee         1984-04-18
Helen       Murphy      1980-08-11
Jeff        Moore       1982-09-30
Lucy        Harris      1983-03-02

That's all for today. We'll stick to SELECT for a couple more lessons, actually most of the lessons. In the next lesson, MySQL step by step: Ordering, Limit, and Aggregation, we'll look at sorting and aggregation functions.