Удалить столбец ГОРОД из АДРЕСА?

ALTER TABLE addresses DROP COLUMN city;

┌──────────────────────────────────────────────────────────────────────────────┐
│                    TABLE SCAN vs INDEXED LOOKUP                              │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  Without Index (Full Table Scan)              With Index (B-Tree)            │
│                                                                              │
│  📚 📚 📚 📚 📚 📚 📚 📚 📚 📚               [M]─────┐                      │
│  📚 📚 📚 📚 📚 📚 📚 📚 📚 📚              /         \                     │
│  📚 📚 📚 📚 📚 📚 📚 📚 📚 📚            [G]         [S]                   │
│  📚 📚 📚 📚 📚 📚 📚 📚 📚 📚           /   \       /   \                  │
│  📚 📚 📚 📚 📚 📚 📚 📚 📚 📚         [C]   [J]   [P]   [W]               │
│       ↑                                      |    |    |    |                │
│   Check EVERY book                        Look up 'Smith': go right to S     │
│   O(n) — slow for large n               O(log n) — scales to billions        │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘

-- Create a basic index
CREATE INDEX idx_email ON users(email);

-- Create a UNIQUE index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_username ON users(username);

-- Composite index (multi-column)
CREATE INDEX idx_name_age ON users(last_name, first_name, age);

-- Prefix index (for long text columns)
CREATE INDEX idx_bio_prefix ON users(bio(100));

-- Covering index (includes all columns needed for a query)
CREATE INDEX idx_covering ON orders(customer_id, status, total) 
INCLUDE (created_at);  -- PostgreSQL / SQL Server syntax

┌──────────────────────────────────────────────────────────────────────────────┐
│              CLUSTERED vs NON-CLUSTERED INDEX                                │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  CLUSTERED INDEX                  NON-CLUSTERED INDEX                        │
│  (Only ONE per table)             (Many per table)                           │
│                                                                              │
│  The table IS the index.          The index is a SEPARATE structure.         │
│                                                                              │
│  ┌─────────────────┐              ┌──────────────┐     ┌─────────────────┐   │
│  │ ID │ Data       │              │ Index Key    │────→│ ID │ Data       │   │
│  ├────┼────────────┤              ├──────────────┤     ├────┼────────────┤   │
│  │ 1  │ Alice, ... │              │ Alice → 1    │     │ 1  │ Alice, ... │   │
│  │ 2  │ Bob, ...   │              │ Bob   → 2    │     │ 2  │ Bob, ...   │   │
│  │ 3  │ Carol, ... │              │ Carol → 3    │     │ 3  │ Carol, ... │   │
│  └────┴────────────┘              └──────────────┘     └─────────────────┘   │
│                                                                              │
│  Data stored IN ORDER.           Index contains pointers to actual rows.     │
│  Physically reorders table.      Index + lookup required.                    │
│  Range queries are blazing fast. Extra storage overhead.                     │
│                                                                              │
│  MySQL InnoDB: PK is clustered.  MySQL: Secondary indexes point to PK.       │
│  SQL Server: Can choose.         PostgreSQL: Only non-clustered (Heap).      │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────────────────┐
│                    THE SQL INJECTION ATTACK                                  │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  VULNERABLE CODE:                                                            │
│  query = "SELECT * FROM users WHERE username = '" + input + "'";            │
│                                                                              │
│  ATTACKER INPUTS:              RESULTING QUERY:                              │
│  ' OR '1'='1                   SELECT * FROM users WHERE username = ''       │
│                                 OR '1'='1'  ← ALWAYS TRUE!                   │
│                                                                              │
│  RESULT: Returns ALL users. Attacker is now logged in as everyone.           │
│                                                                              │
│  WORSE INPUT:                                                                │
│  '; DROP TABLE users; --                                                      │
│                                                                              │
│  RESULTING QUERY:                                                            │
│  SELECT * FROM users WHERE username = ''; DROP TABLE users; --'              │
│                                                                              │
│  RESULT: Your users table is GONE.                                           │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘

# ❌ BAD — String concatenation (NEVER DO THIS)
cursor.execute(f"SELECT * FROM users WHERE id = {user_input}")

# ✅ GOOD — Parameterized query (database escapes input)
cursor.execute("SELECT * FROM users WHERE id = %s", (user_input,))

# ✅ GOOD — Prepared statement
stmt = db.prepare("SELECT * FROM users WHERE id = ?")
stmt.execute(user_input)

-- ❌ VULNERABLE
SELECT * FROM users WHERE email = '$email';

-- ✅ SAFE (Prepared statement)
SELECT * FROM users WHERE email = ?;