The first several work as you would expect:
!= does not equal
> greater than
>= greater than or equal to
< less than
<= less than or equal to
LIKE and NOT LIKE
NOT LIKE are SQL operators that let you add in wild-card symbols, so you could have a meta query that looks like this:
'key' => 'name',
'value' => 'Pat',
'compare' => 'LIKE'
This would return all posts where the meta value "name" has the string "Pat". In this case, "Pat" "Patricia" and "Patrick" would all be returned back to you. There's a non-WordPress tutorial explanation here.
Adding the wildcard character
% isn't necessary, because it gets added by default like @Herb said in his below answer. Like this:
$meta_value = '%' . like_escape( $meta_value ) . '%'; - see source.
IN and NOT IN
NOT IN select any matches that are in (or not in) the given array. So you could do something like this:
'key' => 'color',
'value' => array('red', 'green', 'blue')
'compare' => 'IN'
and it would get all posts that have the color set to either red, green, or blue. Using 'NOT IN' gets the reverse, any posts that have a value set to anything else than what's in the array.
The generated SQL for this would look something like this:
SELECT * FROM posts_meta WHERE value IN ("red", "green", "blue")
BETWEEN and NOT BETWEEN
NOT BETWEEN allow you to define a range of values that could be correct, and require you to give two values in an array in your meta_query:
'key' => 'price',
'value' => array(20,30)
'compare' => 'BETWEEN'
This will get you all posts where the price is between 20 and 30. This person digs into an example with dates.
NOT EXISTS is just like what it sounds - the meta value isn't set or is set to a null value. All you need for that query is the key and comparison operator:
'key' => 'price',
'compare' => 'NOT EXISTS'
This person needed to query non-existent meta values, and needed them to play nice with others.
Hope this helps!