So you know how to use INSERT, SELECT, UPDATE and DELETE in MySQL with both hands tied behind your back (answer: type with your nose). How about some more advanced tricks to keep things interesting?
So you know how to do “SELECT * FROM somewhere WHERE this = ‘that'”, which returns an exact match. That’s great for pulling out things by id numbers but what about searching for something a bit more vague? Say you’ve got a field which contains the string “that’s amazing!”, the above SELECT statement wouldn’t return this string. What you need is the LIKE operator. “SELECT * FROM somewhere WHERE this LIKE ‘that'”. This still wouldn’t work. Why not? Because you need to put at least one wildcard in there.
% – one or more characters.
_ – one character.
[a list] – any single character in a list.
[^a list] or [!a list] – any single character not in the list.
So “ba%” would return “badger”, “bad” and “Bangladesh” (it’s not case sensitive) but wouldn’t return “samba”.
And “ba_” would return “bad” but none of the others.
“[cbt]ar” would return “car”, “bar” and “tar”, but wouldn’t return “bar steward” or “carnage”.
“[!c]unt” would return “runt” and “punt” but wouldn’t…you get the idea.
What about searching for multiple values, such as finding ‘this’ and ‘that’? You could make a long string of conditions like this: “SELECT * FROM somewhere WHERE something = ‘this’ OR WHERE something = ‘that’. This might be fine for a small statement but will quickly get out of hand. So you do this:
SELECT * FROM haystack WHERE needle IN (‘this’, ‘that’)
The IN operator isn’t quite as readable as most SQL, but it works if you think about it like “WHERE [the value in the field called] needle [is] IN [the following array]”.
Simple as pie.
SELECT * FROM somewhere WHERE value BETWEEN 10 AND 100
The BETWEEN operator may or may not be inclusive (i.e. may or may not include values of 10 or 100). You’ll want to check this before you rely on it.
Also, note that the BETWEEN operator can be used on dates. This is VERY useful.
SELECT * FROM somereallylongtablenamethatyoucantbebotheredtotypeeachtime AS shortname WHERE shortname.column = VALUE
This is useful if you’ve got a very long SQL statement with a join which means typing out a very long table name for each column name. Also useful if your column names are long/unreadable. For example:
SELECT jrsnctmnsuykwim AS val FROM somewhere
From this you’ll get a result set and instead of an awkward, meaningless name (it stands for “jakes really stupidly named column that makes no sense unless you know what it means”) can be refered to as val. Useful.
Joins and Unions
I’ll probably do a post dedicated to these at some point.
SELECT * FROM a_table INTO backup_table
That’d copy the entire a_table into backup_table. Which would back it up.
Want more detail? Then go here