These three methods are equivalent.
\n
\nSELECT name FROM `customers` WHERE CAST(name AS BINARY) LIKE '%Net%'\n\nSELECT name FROM `customers` WHERE name COLLATE utf8_bin LIKE '%Net%'\n\nSELECT name FROM `customers` WHERE name LIKE BINARY '%Net%'\n<\/code><\/pre><\/p>\nIt also works for non-latin characters. This search will find only names with an \u201c\u00f1\u201d in the name. If you leave out the BINARY you will get all the names with an \u201cn\u201d in the name.
\n
\nSELECT name FROM `customers` WHERE name LIKE BINARY '%Mu\u00f1oz%'\n<\/code><\/pre><\/p>\nBETWEEN<\/h2>\n
You can search fields containing numbers or characters using BETWEEN.
\n
\nSELECT * FROM `customers` WHERE id BETWEEN 900 AND 999\n<\/code><\/pre><\/p>\nYou can also search a character string of numbers with BETWEEN.
\n
\nSELECT * FROM `customers` WHERE zip BETWEEN 90000 AND 99999\nSELECT * FROM `customers` WHERE zip*2 BETWEEN 90000 AND 99999\n<\/code><\/pre>
\nThese also work:
\n\nSELECT * FROM `customers` WHERE name BETWEEN 'Ja' AND 'Jz'\nSELECT * FROM `customers` WHERE name BETWEEN 'Jam' AND 'Jaz'\nSELECT * FROM `customers` WHERE city BETWEEN 'A' AND 'M'\n<\/code><\/pre>
\nYou might expect this to work, but it doesn\u2019t. The first set of characters must be alphabetically before the second set of characters.
\n\nSELECT * FROM `customers` WHERE name BETWEEN 'Ja' AND 'Hz'\n<\/code><\/pre><\/p>\nIN<\/h2>\n
This could come in handy if you want to restrict searches to just a few of the possible values in a field.
\n
\nSELECT * FROM `customers` WHERE city IN ('Tulsa', 'Orlando')\n<\/code><\/pre><\/p>\nDerived Columns: CONCAT<\/h2>\n
mySQL uses the CONCAT function not the concat operator, ||, for concatenation.
\n
\nSELECT CONCAT(city, ', ', state) AS City_State FROM `customers`\n<\/code><\/pre>
\nThis 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:
\n\nSELECT CONCAT(city, ', ', state) AS City_State \nFROM `customers` \nWHERE City_State = 'Anchorage, AK'\n<\/code><\/pre>
\nHowever, you can wrap the query as follows,
\n\nSELECT *\nFROM (SELECT CONCAT(city, ', ', state) AS City_State \nFROM `customers`) \nAS firstQuery\nWHERE City_State = 'Anchorage, AK'\n<\/code><\/pre><\/p>\nThis doesn\u2019t work in phpMyAdmin or mySQL but works in other DBMSs:
\n
\nSELECT * FROM `customers`\nWHERE city || ', ' || state = 'Anchorage' || ', ' || 'AK'\n<\/code><\/pre><\/p>\nSubstring gotcha<\/h2>\n
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\u2019d do it like this:
\n
\nSELECT city FROM `customers` WHERE SUBSTRING(city FROM 1 FOR 3) LIKE 'Los'\n<\/code><\/pre><\/p>\nUPPER and LOWER<\/h2>\n
You can transform fields or literals in SELECT, WHERE, and ORDER BY clauses.<\/p>\n
Since all of the state abbreviations are in upper case, this works:
\n
\nSELECT state FROM `customers` WHERE state COLLATE utf8_bin LIKE UPPER('%ca%')\n<\/code><\/pre>
\nas does this:
\n\nSELECT state FROM `customers` WHERE LOWER(state) COLLATE utf8_bin LIKE'%ca%'\n<\/code><\/pre><\/p>\nCombining operators<\/h2>\n
You might want to uppercase the address for mailing labels, this is one way to do it.
\n
\nSELECT UPPER(CONCAT(city, ', ', state, ' ', zip)) AS City_State_Zip FROM `customers`\n<\/code><\/pre>
\nThe result is
\n\nST. ALBERT, AB T8N 2N9\nANCHORAGE, AK 99503\n<\/code><\/pre><\/p>\nInteresting Query<\/h2>\n
Try this query:
\n
\nSELECT CURRENT_DATE AS 'Date', CURRENT_TIME AS 'Time', 'Well Golly' AS 'Site'\n<\/code><\/pre>
\nIt yields one row:
\n\nDate Time Site\n2014-12-05 13:19:38 Well Golly\n<\/code><\/pre>
\nThen try this one:
\n\nSELECT CURRENT_DATE AS 'Date', CURRENT_TIME AS 'Time', 'Well Golly' AS 'Site', name FROM `customers`\n<\/code><\/pre>
\nIt yields one row for each row in the database:
\n\nDate Time Site name\n2014-12-05 13:31:59 Well Golly Carmen Edsel\n2014-12-05 13:31:59 Well Golly Jitka Dragonola\n2014-12-05 13:31:59 Well Golly Deryk Bealer\n<\/code><\/pre><\/p>\nGROUP BY and COUNT(*)<\/h2>\n
When testing this one out, I found some values that shouldn\u2019t be in the state field.
\n
\nSELECT state, COUNT(*) FROM `customers` GROUP BY state \n\nstate COUNT(*)\nD 1\nda 1\n<\/code><\/pre>
\nIf you only want selected states, you can use the IN selection criterion:
\n\nSELECT state, COUNT(*) \nFROM `customers` \nWHERE state IN ('CA', 'IN') \nGROUP BY state \n<\/code><\/pre>
\nOr if you want to find the number of customers in the top 20 cities, ordered from most to least.
\n\nSELECT city, state, COUNT(*) as count \nFROM `customers` \nGROUP BY city, state \nORDER BY count DESC \nLIMIT 20\n<\/code><\/pre>
\nNote 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 <\/p>\nHAVING<\/h2>\n
Use the HAVING search condition to filter the results.
\n
\nSELECT city, state, COUNT(*) as count \nFROM `customers` \nGROUP BY city, state \nHAVING COUNT(*) > 50 \nORDER BY count DESC\n<\/code><\/pre>
\nHAVING is applied after GROUP BY. Its main use is to filter on aggregates\u2014SUM, COUNT, AVG, etc.<\/p>\nIn general, you can\u2019t<\/b> reference aliases in WHERE or GROUP BY clauses, but MySQL allows referencing SELECT level aliases in GROUP BY, ORDER BY and HAVING.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 \u201c\u00f1\u201d in the name. … Continue reading Miscellaneous MySQL<\/span><\/a><\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[],"class_list":["post-2046","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2046","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2046"}],"version-history":[{"count":0,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2046\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2046"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2046"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2046"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}