Sqlite3: A Primer
SQLite transactions are ACID (Atomic, Consistent, Isolated and Durable). This means that data is not lost in the event of crashes, power failures or operating system dumps.
Atomic - Meaning changes cannot be broken down into smaller parts; either the entire transaction is commit or nothing at all.
Consistent - A transaction must ensure to change the database from one valid state to another. If a transaction results in invalid data, the database will revert its previous state.
Isolated - Transactions are isolated from one client to the next. When changes are made to the database the changes are only visible to that client until it is committed. If two people concurrently make changes to a database, they will be transacted sequentially. It does not ensure the order of transactions.
Durability - If a transaction is successfully committed, the changes are permanent going forward, if it is interrupted during the commit it will revert to its previous state.
Please check out Julia Evans work at https://jvns.ca
SQLite3 uses auto-commit mode by default.
Used to query data from one or more tables. Example below:
SELECT DISTINCT column_list FROM table_list JOIN table ON join_condition WHERE row_filter ORDER BY column LIMIT count OFFSET offset GROUP BY column HAVING group_filter;
A breakdown of each clause:
ORDER BYsorts the result set,
DISTINCTgets unique rows in a table,
WHEREis a filter such as,
WHERE employees IS NULL,
LIMIT OFFSETwill return a set number of rows,
GROUP BYsorts columns into groups and applies functions to each group,
HAVINGis another filter that checks for presence of the operator.
SELECT * FROM database_table is acceptable usage in testing or development but in production the query should be explicit.
Used to insert new data be it single row, multiple rows, and default data into tables.
INSERT INTO table1 ( column1, column2 ,..) VALUES ( value1, value2 ,...), ( value1, value2 ,...), ... ( value1, value2 ,...);
The above example shows how to insert multiple rows into a table.
INSERT INTO artists (name) VALUES ("Buddy Rich"), ("Candido"), ("Charlie Byrd");
An example of how to insert into column
name within table
Data can also be inserted via a SELECT statement.
Update already existing data in a table.
UPDATE table SET column_1 = new_value_1, column_2 = new_value_2 WHERE search_condition ORDER column_or_expression LIMIT row_count OFFSET offset;
UPDATE clause informs SQL that some part of the table is going to be amended. the
SET clause signifies and allows the updating of the column/s that precede it. On the left of the assignment operator (=) is the column to be updated and on the right the new value, expression or data. Whilst the
WHERE operator is optional if it is omitted all rows will be updated.
The delete statement allows for deletion of one, multiple or all rows within a table.
DELETE FROM table WHERE search_condition;
Like updating a table,
ORDER BY and
LIMIT clauses can be applied to a delete operation. In addition deletes can be orchestrated through the
In relation databases, data is often distributed amongst many related tables. Foreign Keys are used to associate these tables.
INNER JOIN clause will combine columns from correlated tables.
In fig 1.1 Table A’s ‘f’ column is compared with table B’s ‘f’ column. If the value of the ‘f’ column in the A table equals that of B’s ‘f’ column it will return the match. More simply,
INNER JOIN clauses return rows from table A that have corresponding rows in the B table.
Fig 1.1 Inner Join
INNER JOIN may connect more than two tables. This will require two inner join clauses form the
SELECT trackid, tracks.name AS Track, albums.title AS Album, artists.name AS Artist FROM tracks INNER JOIN albums ON albums.albumid = tracks.albumid INNER JOIN artists ON artists.artistid = albums.artistid;
Example code of a query joining three tables by their Artist. (from Chinook.db found at SQLite’s website tutorial.)
Fig. 1.2 is the result set of the above query.
Full Outer Join
this command is not in SQLite3
Theoretically, the result of a Right Join and Left Join with
NULL values for every column of the table that does not have a matching row.
This is technically not available in SQLite3, however they do offer a workaround.
Given that SQLite3 does not have
RIGHT JOIN or
FULL OUTER JOIN it uses both
LEFT JOIN to emulate it.
It achieves this by switching the position of the
LEFT JOIN clause over both columns. SQLite also uses
UNION ALL to duplicate rows from the result set of both queries. Finally, the use of a
WHERE clause will remove rows already included in the result set of the first
SELECT statement. See the below code for an example.
SELECT d.type, d.color, c.type, c.color FROM dogs d LEFT JOIN cats c USING(color) UNION ALL SELECT d.type, d.color, c.type, c.color FROM cats c LEFT JOIN dogs d USING(color) WHERE d.color IS NULL;
In order to qualify as a relational table, it must have a primary key.
The primary key consists of one or more columns whose data contained within is used to uniquely identify each row in the table. metaphor If rows were mailboxes the primary key would be the street address.
To be a true primary key it must be:
- Unique (data, not the name),
- Must not be NULL or ""
Tables must have primary keys, and these are stored in an index which is used to enforce the uniqueness requirement. Being indexed, accessing it does not necessitate scanning the entire table.
A foreign key is one or more columns in a table that refers to the primary key or unique identifier in another table.
They unlike primary keys can contain NULL, blank or duplicate values. As they allow duplication it is best not to use a foreign key as a primary key, unless it is a one-to-one relationship.
In the above picture we have a Students and Cities table. PERSON_ID and CITY_ID are unique, and therefore make good Primary keys (which they are). Inside the Students table it is possible for several students to be born in the same city. As such the BIRTH_PLACE column is the foreign key to the Cities table. This ensures that there is relationship between the tables and that relation points to something unique in the other table.
BLUF: combines result set of two or more queries into a single result set.
UNION by default removes duplicate rows whereas
UNION ALL does not. As
UNION ALL does not remove duplicates it will process faster. Both statements have the following rules:
- Must have the same number of columns,
- Corresponding columns must be same data type,
- The column names of the first query will determine the column of the combined result set,
HAVINGclauses are applied to each individual query, not the final result set,
ORDER BYis applied to the combined result set, not the individual result sets.
The basis of all interactions with the database. Inserts, updates, deletes, commits and table creation and deletion are all transactions.
Keep up to date with my stuff
Subscribe to get new posts and retrospectives