flat-file database
CSV
key value
relational database
SQL
C
R
U
D
CREATE
READ
UPDATE
DELETE
CREATE, INSERT
SELECT
UPDATE
DELETE, DROP
...
CREATE TABLE table (column type, ...);
sqlite3
sqlite3 FILE
.mode csv
.import FILE TABLE
.quit
.schema
SELECT columns FROM table;
...
AVG
COUNT
DISTINCT
LOWER
MAX
MIN
UPPER
...
WHERE
LIKE
ORDER BY
LIMIT
GROUP BY
...
INSERT INTO table (column, ...) VALUES(value, ...);
NULL
DELETE FROM table WHERE condition;
UPDATE table SET column = value WHERE condition;
IMDb
one-to-one
BLOB
INTEGER
NUMERIC
REAL
TEXT
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
JOIN
id title show_id rating
386676 The Office 386676 …
title id show_id rating
The Office 386676 386676 …
title id show_id rating
The Office 386676 386676 …
title id show_id rating
The Office 386676 386676 …
title id rating
The Office 386676 …
title rating
The Office …
one-to-many
id title show_id genre
63881 Catweazle 63881 Adventure
63881 Comedy
63881 Family
title id show_id genre
Catweazle 63881 63881 Adventure
63881 Comedy
63881 Family
title id show_id genre
Catweazle 63881 63881 Adventure
63881 Comedy
63881 Family
title id show_id genre
Catweazle 63881 63881 Adventure
63881 Comedy
63881 Family
title id show_id genre
Catweazle 63881 63881 Adventure
Catweazle 63881 63881 Comedy
Catweazle 63881 63881 Family
title id genre
Catweazle 63881 Adventure
Catweazle 63881 Comedy
Catweazle 63881 Family
title genre
Catweazle Adventure
Catweazle Comedy
Catweazle Family
genre
Adventure
Comedy
Family
many-to-many
indexes
CREATE INDEX name ON table (column, ...);
B-trees
from cs50 import SQL
cs50.readthedocs.io/libraries/cs50/python/#cs50.SQL
race conditions
rows = db.execute("SELECT likes FROM posts WHERE id = ?", id);
likes = rows[0]["likes"]
db.execute("UPDATE posts SET likes = ? WHERE id = ?", likes + 1, id);
rows = db.execute("SELECT likes FROM posts WHERE id = ?", id);
likes = rows[0]["likes"]
db.execute("UPDATE posts SET likes = ? WHERE id = ?", likes + 1, id);
BEGIN TRANSACTION
COMMIT
ROLLBACK
db.execute("BEGIN TRANSACTION")
rows = db.execute("SELECT likes FROM posts WHERE id = ?", id);
likes = rows[0]["likes"]
db.execute("UPDATE posts SET likes = ? WHERE id = ?", likes + 1, id);
db.execute("COMMIT")
SQL injection attacks
rows = db.execute("SELECT * FROM users WHERE username = ? AND password = ?", username, password)
if rows:
...
rows = db.execute("SELECT * FROM users WHERE username = ? AND password = ?", username, password)
if rows:
...
rows = db.execute("SELECT * FROM users WHERE username = ? AND password = ?", username, password)
if rows:
...
rows = db.execute(f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'")
if rows:
...
rows = db.execute(f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'")
if rows:
...
rows = db.execute(f"SELECT * FROM users WHERE username = '[email protected]'--' AND password = '{password}'")
if rows:
...
rows = db.execute(f"SELECT * FROM users WHERE username = '[email protected]'--' AND password = '{password}'")
if rows:
...
rows = db.execute(f"SELECT * FROM users WHERE username = '[email protected]''--' AND password = '{password}'")
if rows:
...
rows = db.execute("SELECT * FROM users WHERE username = ? AND password = ?", username, password)
if rows:
...
