Summary of Important SQL commands
| SELECT | extracts data from a DB |
| UPDATE | updates data in a DB |
| DELETE | deletes data from a DB |
| INSERT INTO | inserts new data into a DB |
| CREATE DATABASE | creates a new DB |
| ALTER DATABASE | modifies a DB |
| CREATE TABLE | creates a new table |
| ALTER TABLE | modifies a table |
| DROP TABLE | deletes a table |
| CREATE INDEX | creates an index (search key) |
| DROP INDEX | deletes an index |
SELECT
selects columns specified, * means all the columns in the table
- DISTINCT: returns only distinct values
- FROM: decides which table to extract columns from
WHERE
sets conditions using different operators (not only used in SELECT statements but also in UPDATE, DELETE, etc.)
| Operator | Function |
| = | Equal to |
| <> or != | Not equal to |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| BETWEEN | Between a given range |
| LIKE | Search for a specific pattern |
| IN | Checks whether an item exists in a list |
| Filter for records | |
| AND | displays a record if all the conditions given by `AND` are true |
| OR | displays a record if any of the conditions given by `OR` is true |
| NOT | displays a record if the condition(s) is NOT TRUE |
ORDER BY
used to sort the result-set in ascending or descending order (ascending in default)
SELECT col1, col2, ... FROM table_name
ORDER BY col1, col2, ... ASC|DESC
Operator Examples
SELECT population FROM world
WHERE name = 'Germany'SELECT name, population FROM world
WHERE name IN ('Sweden', 'Norway', 'Denmark');# Find the country that start with Y
SELECT name FROM world
WHERE name LIKE 'Y%'! `%` covers all the characters(a wildcard) and `_` is a single wildcard
# Find the capital and the name where the capital includes the name of the country
SELECT capital, name FROM world
WHERE capital LIKE concat('%', name, '%')! `concat()` adds two or more strings together
# For Monaco-Ville, the name is Monaco and the extension is -Ville.
# Show the name and the extension where the capital is an extension of name of the country.
SELECT name, replace(capital, name, '') as ext
FROM world
WHERE capital LIKE concat(name, '%') AND capital > name! `replace(f, s1, s2)` returns the string f with s1 replaced with s2