Case sensitive selection
These three methods are equivalent.
SELECT name FROM `customers` WHERE CAST(name AS BINARY) LIKE '%Net%'
SELECT name FROM `customers` WHERE name COLLATE utf8_bin LIKE '%Net%'
SELECT name FROM `customers` WHERE name LIKE BINARY '%Net%'
It also works for non-latin characters. This search will find only names with an “ñ” in the name. If you leave out the BINARY you will get all the names with an “n” in the name.
SELECT name FROM `customers` WHERE name LIKE BINARY '%Muñoz%'
BETWEEN
You can search fields containing numbers or characters using BETWEEN.
SELECT * FROM `customers` WHERE id BETWEEN 900 AND 999
You can also search a character string of numbers with BETWEEN.
SELECT * FROM `customers` WHERE zip BETWEEN 90000 AND 99999
SELECT * FROM `customers` WHERE zip*2 BETWEEN 90000 AND 99999
These also work:
SELECT * FROM `customers` WHERE name BETWEEN 'Ja' AND 'Jz'
SELECT * FROM `customers` WHERE name BETWEEN 'Jam' AND 'Jaz'
SELECT * FROM `customers` WHERE city BETWEEN 'A' AND 'M'
You might expect this to work, but it doesn’t. The first set of characters must be alphabetically before the second set of characters.
SELECT * FROM `customers` WHERE name BETWEEN 'Ja' AND 'Hz'
IN
This could come in handy if you want to restrict searches to just a few of the possible values in a field.
SELECT * FROM `customers` WHERE city IN ('Tulsa', 'Orlando')
Derived Columns: CONCAT
mySQL uses the CONCAT function not the concat operator, ||, for concatenation.
SELECT CONCAT(city, ', ', state) AS City_State FROM `customers`
This displays the column heading for the result as City_State, but it is not available for use in a WHERE clause. This fails with an unknown column error:
SELECT CONCAT(city, ', ', state) AS City_State
FROM `customers`
WHERE City_State = 'Anchorage, AK'
However, you can wrap the query as follows,
SELECT *
FROM (SELECT CONCAT(city, ', ', state) AS City_State
FROM `customers`)
AS firstQuery
WHERE City_State = 'Anchorage, AK'
This doesn’t work in phpMyAdmin or mySQL but works in other DBMSs:
SELECT * FROM `customers`
WHERE city || ', ' || state = 'Anchorage' || ', ' || 'AK'
Substring gotcha
If you want to find the cities that start with ‘Los’, you might be tempted to write the SUBSTRING as SUBSTRING(city, 0, 2) like any normal string access e.g. PHP. But MySQL starts with 1 and then has the number of characters you want to extract. So you’d do it like this:
SELECT city FROM `customers` WHERE SUBSTRING(city FROM 1 FOR 3) LIKE 'Los'
UPPER and LOWER
You can transform fields or literals in SELECT, WHERE, and ORDER BY clauses.
Since all of the state abbreviations are in upper case, this works:
SELECT state FROM `customers` WHERE state COLLATE utf8_bin LIKE UPPER('%ca%')
as does this:
SELECT state FROM `customers` WHERE LOWER(state) COLLATE utf8_bin LIKE'%ca%'
Combining operators
You might want to uppercase the address for mailing labels, this is one way to do it.
SELECT UPPER(CONCAT(city, ', ', state, ' ', zip)) AS City_State_Zip FROM `customers`
The result is
ST. ALBERT, AB T8N 2N9
ANCHORAGE, AK 99503
Interesting Query
Try this query:
SELECT CURRENT_DATE AS 'Date', CURRENT_TIME AS 'Time', 'Well Golly' AS 'Site'
It yields one row:
Date Time Site
2014-12-05 13:19:38 Well Golly
Then try this one:
SELECT CURRENT_DATE AS 'Date', CURRENT_TIME AS 'Time', 'Well Golly' AS 'Site', name FROM `customers`
It yields one row for each row in the database:
Date Time Site name
2014-12-05 13:31:59 Well Golly Carmen Edsel
2014-12-05 13:31:59 Well Golly Jitka Dragonola
2014-12-05 13:31:59 Well Golly Deryk Bealer
GROUP BY and COUNT(*)
When testing this one out, I found some values that shouldn’t be in the state field.
SELECT state, COUNT(*) FROM `customers` GROUP BY state
state COUNT(*)
D 1
da 1
If you only want selected states, you can use the IN selection criterion:
SELECT state, COUNT(*)
FROM `customers`
WHERE state IN ('CA', 'IN')
GROUP BY state
Or if you want to find the number of customers in the top 20 cities, ordered from most to least.
SELECT city, state, COUNT(*) as count
FROM `customers`
GROUP BY city, state
ORDER BY count DESC
LIMIT 20
Note that the SELECT and GROUP BY must have the same fields or you will get incorrect results. In this case, leaving out the state in the group by yields the most customers in Brooklyn, FL
HAVING
Use the HAVING search condition to filter the results.
SELECT city, state, COUNT(*) as count
FROM `customers`
GROUP BY city, state
HAVING COUNT(*) > 50
ORDER BY count DESC
HAVING is applied after GROUP BY. Its main use is to filter on aggregates—SUM, COUNT, AVG, etc.
In general, you can’t reference aliases in WHERE or GROUP BY clauses, but MySQL allows referencing SELECT level aliases in GROUP BY, ORDER BY and HAVING.