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:
SELECT*FROMmy_contacts
WHEREfirst_name=‘Anne’;
* 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:
SELECTfirst_name,last_name,emailFROMmy_contacts
WHEREfirst_name=‘Anne’;
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.
SELECTdrink_nameFROMdrink_info
WHEREcalories>=30
ANDcalories<=60;
SELECTdrink_nameFROMdrink_info
WHEREcaloriesBETWEEN30AND60;
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:
SELECTfirst_nameFROMmy_contacts
WHEREfirst_nameLIKE‘%im’;
This first search will return ‘Tim’, ‘Slim’, and ‘Ephraim’.
SELECTfirst_nameFROMmy_contacts
WHEREfirst_nameLIKE‘_im’;
This second search will only return ‘Tim’. NULL is special, nothing equals NULL but you can check if something is NULL:
SELECTfirst_nameFROMmy_contactsWHEREflagISNULL;
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:
SELECTdrink_nameFROMdrink_info
WHEREratingNOT IN (‘good’,‘excellent’,‘average’);
SELECTfirst_nameFROMmy_contacts
WHERE NOTfirst_nameLIKE‘_im’;
Keywords: AND, OR, BETWEEN, IS NULL, NOT, LIKE, IN
