MYSQL queries can benefit from the use of several operators and clauses that we have not yet covered. Although for the sake of our examples we will be mostly using the SELECT statement, most of these options will work on other statements, such as UPDATE and DELETE as needed.
To begin, let's jump right into some examples using operators.
SELECT * FROM pets WHERE name LIKE '%T%' AND gender LIKE 'Female';
SELECT * FROM pets WHERE (breed LIKE 'cat' AND gender LIKE 'male') OR (breed LIKE 'dog' AND gender LIKE 'male');
See how operators can be used to narrow down the results even more by combining multiple conditions? And how parenthesis can be used to group conditions together in order to indicate which condition takes priority (because the AND operator is prioritized over the OR operator)?
Notice that unless you have inserted additional rows into our example pets table, the second example will not return any results.
Generally results are returned in the order that they are encountered in (or inserted into) a MYSQL table. You can, however, specify the order that you want your results returned, using ORDER BY. This will cause your results to be returned in alphabetical or numerical order, depending on the contents of the column you are ordering by.
By default, when ORDER BY is used, the results are returned in ascending order (ASC), with the smallest values first, followed by the larger values. If you want the results to be in descending order (larger values first, followed by smaller) you can add DESC to the query.
SELECT * FROM pets ORDER BY dateofbirth;
SELECT * FROM pets ORDER BY gender DESC;
You can limit the number of rows affected by a statement using the LIMIT clause. When given a single numerical argument it will limit the number of rows selected, deleted, updated etc. according to the number specified.
LIMIT can accept two numerical arguments instead of one, and will use the first argument as the offset of the first row to return, and the second argument as the maximum number of rows to return. (Note: LIMIT uses 0 as the offset of the initial row instead of 1.)
SELECT * FROM pets LIMIT 3;
SELECT * FROM pets LIMIT 2, 5;
The first example will return the first 3 rows from the pets table. The second example will return 4 rows (rows 3-6, which are the 2nd, 3rd, 4th and 5th rows since the offset is 0).
At some point in time you might have enough data built up that values begin to repeat themselves. We might end up with two dogs named Chester, for example. But what if you want to make a list of all of your pet's names, in alphabetical order, without multiple values? The DISTINCT clause can make that happen.
SELECT DISTINCT name FROM pets ORDER BY name;
There are many instances that you will run into where you will want to count rows. The COUNT() function is used for this purpose. Let's begin by counting all of the rows in our table:
SELECT COUNT(*) FROM pets;
We can be more selective in the rows that we count as well.
SELECT COUNT(*) FROM pets WHERE name LIKE 'Sylvester';
We can use GROUP BY along with COUNT() if we want to get fancy and, for example, determine how many male pets we have versus how many female.
mysql> SELECT gender, COUNT(*) FROM pets GROUP BY gender;
+--------+----------+
| gender | COUNT(*) |
+--------+----------+
| Female | 2 |
| Male | 4 |
+--------+----------+
2 rows in set (0.00 sec)
You probably want to test some ideas of your own at this point, so give it a try!