Database and SQL

Flat-file database

CSV

Relational database

SQL

C

R

U

D

CREATE

READ

UPDATE

DELETE

(in SQL)

CREATE, INSERT

SELECT

UPDATE

DELETE, DROP

...

CREATE TABLE table (column type, ...);

sqlite3

(in sqlite3 shell)

.mode csv
.import FILE TABLE

(in sqlite3 shell)

SELECT columns FROM table;

AVG
COUNT
DISTINCT
LOWER
MAX
MIN
UPPER

...

WHERE
LIKE
ORDER BY
LIMIT
GROUP BY
...

INSERT INTO table (column, ...) VALUES(value, ...);

UPDATE table SET column = value WHERE condition;

DELETE FROM table WHERE condition;

IMDB

INTEGER
REAL
NUMERIC
TEXT
BLOB

NOT NULL
UNIQUE

PRIMARY KEY
FOREIGN KEY

JOIN
...

Indexes

CREATE INDEX name ON table (column, ...);

Transaction

id = 'CmUv48DLvxd'

rows = db.execute("SELECT likes FROM posts WHERE id = ?", id)

likes = rows[0][3]

db.execute("UPDATE posts SET likes = ? WHERE id = ?", likes + 1, id)

BEGIN TRANSACTION
COMMIT
ROLLBACK

id = 'CmUv48DLvxd'

db.execute("BEGIN TRANSACTION")

rows = db.execute("SELECT likes FROM posts WHERE id = ?", id)

likes = rows[0][3]

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(
    f"SELECT * FROM users WHERE username='{username}' AND password='{password}'"
    )

if rows:
    ...

rows = db.execute(
    "SELECT * FROM users WHERE username='leomessi'--' AND password='{password}"
    )

if rows:
    ...