It took quite a lot of time, but we are finally in the most exciting—and useful—section related to databases: querying data. Querying data refers to asking MySQL to return rows from the specified table and optionally filtering these results by a set of rules. You can also choose to get specific fields instead of the whole row. In order to query data, we will use the SELECT command, as follows:
mysql> SELECT firstname, surname, type FROM customer; +-----------+---------+---------+ | firstname | surname | type | +-----------+---------+---------+ | Han | Solo | premium | | James | Kirk | basic | +-----------+---------+---------+ 2 rows in set (0.00 sec)
One of the simplest ways to query data is to specify the fields of interest after SELECT and specify the table with the FROM keyword. As we did not add any filters—mostly known as conditions—to the query, we got all the rows there. Sometimes, this is the desired behavior, but the most common thing to do is to add conditions to the query to retrieve only the rows that we need. Use the WHERE keyword to achieve this.
mysql> SELECT firstname, surname, type FROM customer -> WHERE id = 1; +-----------+---------+---------+ | firstname | surname | type | +-----------+---------+---------+ | Han | Solo | premium | +-----------+---------+---------+ 1 row in set (0.00 sec)
Adding conditions is very similar to when we created Boolean expressions in PHP. We will specify the name of the field, an operator, and a value, and MySQL will retrieve only the rows that return true to this expression. In this case, we asked for the customers that had the ID 1, and MySQL returned one row: the one that had an ID of exactly 1.
A common query would be to get the books that start with some text. We cannot construct this expression with any comparison operand that you know, such as = and < or >, since we want to match only a part of the string. For this, MySQL has the LIKE operator, which takes a string that can contain wildcards. A wildcard is a character that represents a rule, matching any number of characters that follows the rule. For example, the % wildcard represents any number of characters, so using the 1% string would match any string that starts with 1 and is followed by any number or characters, matching strings such as 1984 or 1Q84. Let's consider the following example:
mysql> SELECT title, author, price FROM book -> WHERE title LIKE "1%"; +------------+-----------------+-------+ | title | author | price | +------------+-----------------+-------+ | 1984 | George Orwell | 7.5 | | 1Q84 | Haruki Murakami | 9.75 | | 19 minutes | Jodi Picoult | 10 | +------------+-----------------+-------+ 3 rows in set (0.00 sec)
We asked for all the books whose title starts with 1, and we got three rows. You can imagine how useful this operator is, especially when we implement a search utility in our application.
As in PHP, MySQL also allows you to add logical operators—that is, operators that take operands and perform a logical operation, returning Boolean values as a result. The most common logical operators are, as in PHP, AND and OR. AND returns true if both the expressions are true and OR returns true if either of the operands is true. Let's consider an example, as follows:
mysql> SELECT title, author, price FROM book -> WHERE title LIKE "1%" AND stock > 0; +------------+-----------------+-------+ | title | author | price | +------------+-----------------+-------+ | 1984 | George Orwell | 7.5 | | 1Q84 | Haruki Murakami | 9.75 | +------------+-----------------+-------+ 2 rows in set (0.00 sec)
This example is very similar to the previous one, but we added an extra condition. We asked for all titles starting with 1 and whether there is stock available. This is why one of the books does not show as it does not satisfy both conditions. You can add as many conditions as you need with logical operators but bear in mind that AND operators take precedence over OR. If you want to change this precedence, you can always wrap expressions with a parenthesis, as in PHP.
So far, we have retrieved specific fields when querying for data, but we could ask for all the fields in a given table. To do this, we will just use the * wildcard in SELECT. Let's select all the fields for the customers via the following code:
mysql> SELECT * FROM customer G *************************** 1. row *************************** id: 1 firstname: Han surname: Solo email: [email protected] type: premium *************************** 2. row *************************** id: 2 firstname: James surname: Kirk email: enter@prise type: basic 2 rows in set (0.00 sec)
You can retrieve more information than just fields. For example, you can use COUNT to retrieve the amount of rows that satisfy the given conditions instead of retrieving all the columns. This way is faster than retrieving all the columns and then counting them because you save time in reducing the size of the response. Let's consider how it would look:
mysql> SELECT COUNT(*) FROM borrowed_books -> WHERE customer_id = 1 AND end IS NOT NULL; +----------+ | COUNT(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec)
As you can note, the response says 1, which means that there is only one borrowed book that satisfies the conditions. However, check the conditions; you will note that we used another familiar logical operator: NOT. NOT negates the expression, as ! does in PHP. Note also that we do not use the equal sign to compare with null values. In MySQL, you have to use IS instead of the equals sign in order to compare with NULL. So, the second condition would be satisfied when a borrowed book has an end date that is not null.
Let's finish this section by adding two more features when querying data. The first one is the ability to specify in what order the rows should be returned. To do this, just use the keyword ORDER BY followed by the name of the field that you want to order by. You could also specify whether you want to order in ascending mode, which is by default, or in the descending mode, which can be done by appending DESC. The other feature is the ability to limit the amount of rows to return using LIMIT and the amount of rows to retrieve. Now, run the following:
mysql> SELECT id, title, author, isbn FROM book -> ORDER BY title LIMIT 4; +----+-------------+-----------------+---------------+ | id | title | author | isbn | +----+-------------+-----------------+---------------+ | 5 | 19 minutes | Jodi Picoult | 9780753179246 | | 1 | 1984 | George Orwell | 9780882339726 | | 2 | 1Q84 | Haruki Murakami | 9789724621081 | | 3 | Animal Farm | George Orwell | 9780736692427 | +----+-------------+-----------------+---------------+ 4 rows in set (0.00 sec)