SELECT * [column ] FROM table [WHERE condition ] [ORDER BY column [asc|desc]]
The options in square brackets are optional. Some examples:
| SELECT * FROM tab | all records from the table tab |
| SELECT name, firstname FROM tab | only columns last name and first name |
| SELECT * FROM tab ORDER BY name | all records from the table tab sorted by name |
| SELECT * FROM tab WHERE salutation = 'Mr' ORDER BY name | all records with salutation “Mr.” sorted by name |
| SELECT * FROM tab WHERE name = 'Meier' and firstname = 'Luka' | searches for “Luka Meier” (both conditions (should be) met) |
| SELECT * FROM tab WHERE name = 'Meier' or name = 'Mayer' | one of the two conditions should be met |
| SELECT * FROM tab WHERE name in ('Meier', 'Meyer', 'Muller') | name must be listed under the specified name(s) |
| SELECT * FROM tab WHERE name LIKE '%house% ' | all records that occur in the field name "house" |
| SELECT * FROM tab WHERE year between 1999 and 2014 | numbers can be specified without quotes |
| SELECT count (*) FROM tab | determines the number of records in a table |
| SELECT concat (firstame, ' ', name) as fname FROM tab | connects last name and firstname in a new field fname |
| SELECT sum(price) FROM tab | determines the sum of all values in a table column |
UPDATE table SET column1 = value1, [column2 = value2], [...] [WHEREcondition]
| UPDATE tab SET institute = 'PHBern' | replaces institute with PHBern in all record fields |
| UPDATE tab SET booked='Y', cust=33 WHERE seat=6 | updates multiple columns |
| UPDATE tab SET salutation = 'Ms' WHERE salutation = 'f' | replaces f with Ms in the field salutation |
| UPDATE tab SET price = price * 1.52 | calculations can be performed in the update statement |
DELETE FROM table [WHERE condition]
| DELETE FROM tab | deletes all records in the table tab |
| DELETE FROM tab WHERE name = "Meier" | deletes the record with name = Meier |