These are my personal reference notes. This not a tutorial or anything like that. All of these examples are taken from SQL Pocket Guide, Second Edition. It's a good pocket reference. You should buy it.
Select Syntax
SELECT expression_list
FROM data_source
WHERE predicates
GROUP BY expression_list
HAVING predicates
ORDER BY expression_list
Predicates
BETWEEN
Tests that a value falls within the given range.
mysql> SELECT * FROM county; +----+-----------+------------+ | id | name | population | +----+-----------+------------+ | 2 | Alger | 9862 | | 6 | Baraga | 8746 | | 7 | Ontonogan | 7818 | | 9 | Dickenson | 27472 | | 10 | Gogebic | 17370 | | 11 | Delta | 38520 | +----+-----------+------------+ 6 rows in set (0.00 sec)
mysql> SELECT c.name
-> FROM county c
-> WHERE c.population BETWEEN 5000 AND 10000;
+-----------+
| name |
+-----------+
| Alger |
| Baraga |
| Ontonogan |
+-----------+
3 rows in set (0.00 sec)EXISTS/NOT EXISTS
Test for the existence (or non-existence) of rows matching conditions.
mysql> SELECT * FROM owner; +----+-----------------+--------------+---------+ | id | name | phone | type | +----+-----------------+--------------+---------+ | 1 | Pictured Rocks | 906.387.2607 | public | | 2 | Michigan Nature | 517.655.5655 | private | | 3 | AF LLC | NULL | private | | 4 | MI DNR | 906-228-6561 | public | | 5 | Horseshoe Falls | 906.387.2635 | private | +----+-----------------+--------------+---------+ 5 rows in set (0.00 sec) mysql> SELECT owner_id FROM upfall; +----------+ | owner_id | +----------+ | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | 1 | | 1 | | 1 | | 2 | | 2 | | 2 | | 3 | | 4 | +----------+ 16 rows in set (0.00 sec)
mysql> SELECT o.id, o.name
-> FROM owner o
-> WHERE EXISTS (SELECT * FROM upfall u
-> WHERE u.owner_id = o.id);
+----+-----------------+
| id | name |
+----+-----------------+
| 1 | Pictured Rocks |
| 2 | Michigan Nature |
| 3 | AF LLC |
| 4 | MI DNR |
+----+-----------------+
4 rows in set (0.06 sec)mysql> SELECT o.id, o.name
-> FROM owner o
-> WHERE NOT EXISTS (SELECT * FROM upfall u
-> WHERE u.owner_id = o.id);
+----+-----------------+
| id | name |
+----+-----------------+
| 5 | Horseshoe Falls |
+----+-----------------+IN/NOT IN
Test if a value falls within the given range.
mysql> SELECT o.id, o.name
-> FROM owner o
-> WHERE o.id IN (1, 2, 3, 4);
+----+-----------------+
| id | name |
+----+-----------------+
| 1 | Pictured Rocks |
| 2 | Michigan Nature |
| 3 | AF LLC |
| 4 | MI DNR |
+----+-----------------+
4 rows in set (0.00 sec)mysql> SELECT o.id, o.name
-> FROM owner o
-> WHERE o.id IN (SELECT u.owner_id
-> FROM upfall u);
+----+-----------------+
| id | name |
+----+-----------------+
| 1 | Pictured Rocks |
| 2 | Michigan Nature |
| 3 | AF LLC |
| 4 | MI DNR |
+----+-----------------+
4 rows in set (0.00 sec)LIKE/NOT LIKE
mysql> SELECT u.id, u.name
-> FROM upfall u;
+----+-----------------+
| id | name |
+----+-----------------+
| 1 | Munising Falls |
| 2 | Tannery Falls |
| 3 | Alger Falls |
| 4 | Wagner Falls |
| 5 | Horseshoe Falls |
| 6 | Miners Falls |
| 7 | Little Miners |
| 8 | Scott Falls |
| 9 | Canyon Falls |
| 10 | Agate Falls |
| 11 | Bond Falls |
| 12 | Fumee Falls |
| 13 | Kakibika Falls |
| 14 | Rapid River Fls |
| 30 | Twin Falls #1 |
| 31 | Twin Falls #2 |
+----+-----------------+
16 rows in set (0.00 sec)Find all entries containing the word "Miners":
mysql> SELECT u.id, u.name FROM upfall u WHERE u.name LIKE '%Miners%'; +----+---------------+ | id | name | +----+---------------+ | 6 | Miners Falls | | 7 | Little Miners | +----+---------------+ 2 rows in set (0.00 sec)
This is not case sensitive.
mysql> SELECT u.id, u.name FROM upfall u WHERE u.name LIKE '%miners%'; +----+---------------+ | id | name | +----+---------------+ | 6 | Miners Falls | | 7 | Little Miners | +----+---------------+ 2 rows in set (0.00 sec)
Finding entries starting with "Miners", and then finding entries ending with "Miners":
mysql> SELECT u.id, u.name FROM upfall u WHERE u.name LIKE 'Miners%'; +----+--------------+ | id | name | +----+--------------+ | 6 | Miners Falls | +----+--------------+ 1 row in set (0.00 sec) mysql> SELECT u.id, u.name FROM upfall u WHERE u.name LIKE '%Miners'; +----+---------------+ | id | name | +----+---------------+ | 7 | Little Miners | +----+---------------+ 1 row in set (0.01 sec)
Finding all entries that do not contain "Miners":
mysql> SELECT u.id, u.name
-> FROM upfall u
-> WHERE u.name NOT LIKE '%Miners%';
+----+-----------------+
| id | name |
+----+-----------------+
| 1 | Munising Falls |
| 2 | Tannery Falls |
| 3 | Alger Falls |
| 4 | Wagner Falls |
| 5 | Horseshoe Falls |
| 8 | Scott Falls |
| 9 | Canyon Falls |
| 10 | Agate Falls |
| 11 | Bond Falls |
| 12 | Fumee Falls |
| 13 | Kakibika Falls |
| 14 | Rapid River Fls |
| 30 | Twin Falls #1 |
| 31 | Twin Falls #2 |
+----+-----------------+
14 rows in set (0.00 sec)Escape characters with a backslash or the ESCAPE (note, in MySQL the backslash is also the string escape character and must also be escaped.)
mysql> SELECT u.id, u.name
-> FROM upfall u
-> WHERE u.name NOT LIKE '%\%%';REGEXP
MySQL allows for POSIX Extende Regular Expressions. Use backslash to escape string literals.
mysql> SELECT u.id, u.name
-> FROM upfall u
-> WHERE u.name REGEXP '(Fumee|Fumie|Fumy)';
+----+-------------+
| id | name |
+----+-------------+
| 12 | Fumee Falls |
+----+-------------+
1 row in set (0.00 sec)