Part 1 of this sequence of blog posts provided a preamble and showed how to create databases. This post introduces the basic SELECT command, which shows you what lies within your database and as it’s name implies allows you to select only parts of the data contained within.
The basic form of SELECT is:
* indicates that all fields should be returned from the table ‘my_contacts’, where the first_name field = ‘Anne’. We don’t have to take all the fields from a table:
As well as the equivalence operator =, we can also use comparison operators <> (not equal), <, >, <=, >= these work not only with numerical values, but also with text values. WHERE clauses can also be combined with AND and OR operators.
The second select using the BETWEEN keyword is equivalent to the first.
In addition there are wildcards, % meaning ‘any number of characters’ and _ meaning ‘one character’ which are accessed via the LIKE keyword:
This first search will return ‘Tim’, ‘Slim’, and ‘Ephraim’.
This second search will only return ‘Tim’. NULL is special, nothing equals NULL but you can check if something is NULL:
Comparisons can be made to a list with the IN keyword:
SELECT drink_name FROM drink_info
WHERE rating IN ( ‘good’, ‘excellent’, ‘average’ );
Finally, the NOT operator can be used to find the inverse of the selection made, the NOT keyword goes directly after IN but otherwise goes after WHERE:
WHEREratingNOT IN (‘good’,‘excellent’,‘average’);
Keywords: AND, OR, BETWEEN, IS NULL, NOT, LIKE, IN