(relatively) Advanced MySQL

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


About Mr Chimp

I make music, draw pictures, browse the internet, programme, and make sweet, sweet cups of tea until the early hours.
This entry was posted in Uncategorized and tagged , , , , . Bookmark the permalink.

One Response to (relatively) Advanced MySQL

  1. Skilldrick says:

    Evolution of a name: “jakes really stupidly named column that makes no sense unless you know what it means” => “jrsnctmnsuykwim” => “val”. I like it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s