SQL notes

Structured Query Language
Relational databases
- MySQL
- SQLite

Structured Query Language

Deleting data

The following will remove all rows from mytable where mycondition evaluates to true.

DELETE FROM mytable
WHERE mycondition;

Distinct

SELECT DISTINCT col FROM mytable;

Filtering and sorting

SELECT col_a, col_b
FROM mytable
WHERE condition
ORDER BY col ASC/DESC
LIMIT num_lim;

Joints

SELECT col_from_a, col_from_b
FROM table_a
INNER JOIN table_b
    ON table_a.id = table_b.matching_id
WHERE condition
ORDER BY col ASC/DESC
LIMIT num_lim;

There are also LEFT, RIGHT and FULL joins depending on which set of rows you are sure you want in your results.

Relational databases

The primary key uniquely identifies a row in a table. This is related to the foreign key which uniquely identifies a row in another table.
The combination of multiple columns yields a compound key.
An SQL query returns a table called a result set.

MySQL

Check that the server is running with sudo systemctl status mysql.
Start the MySQL server with sudo systemctl start mysql and stop it with sudo systemctl stop mysql.
Log in with sudo mysql -u <username> -p. If you have just installed MySQL, then you'll need to set up a username and password, using root.

SQLite

DB Browser for SQLite is a GUI for inspecting and interactive with a SQLite database file.
There are five storage classes: null, integer, real, text, and blob. On top of these storage classes there are datatypes, for example Boolean, which consists of the integers 0 (false) and 1 (true). Dates and times can be stored as text, reals or integers.

SQLite3 REPL

.schema <tableName> will show the schema for the given table.
.tables will list the tables in the current database.