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).
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
INSERT statement, so multiple rows are inserted by a single
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';
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
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
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
SELECT * FROM `user` WHERE `birth_date` >= '1960-1-1' AND `articles_count` > 5;
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
You can surely use basic operators
already. In SQL, there's more of them, let's talk about
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
%(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
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
as the 2nd character:
SELECT `last_name` FROM `user` WHERE `last_name` LIKE '_o___';
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 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');
John Smith Paul Lee Barbara Thomas Paul Walker John Johnson
IN operator is also used in sub-queries, but we still have
enough time for that
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
SELECT `first_name`, `last_name`, `birth_date` FROM `user` WHERE `birth_date` BETWEEN '1980-1-1' AND '1990-1-1';
AND between the 2 values.
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.
No one has commented yet - be the first!