Corso di SQL: 3 – Inserimento, Aggiornamento e Cancellazione

3 – Inserimento, Aggiornamento e Cancellazione

Obiettivi

• Comprendere e utilizzare le istruzioni fondamentali DML: INSERT, UPDATE, DELETE.

• Imparare le buone pratiche: transazioni, prepared statements, controllo integrità, prevenzione SQL injection.

• Realizzare una tabella clienti e popolarla con esempi pratici.

---

1. Concetti preliminari: ACID e transazioni

Prima di modificare dati in un database è importante conoscere le proprietà ACID:

• Atomicità: tutte le operazioni di una transazione avvengono tutte o nessuna.

• Consistenza: la transazione porta il DB da uno stato consistente a un altro.

• Isolamento: transazioni concorrenti non si interferiscono.

• Durabilità: una volta confermata, la transazione è persistente.

Usare BEGIN / COMMIT / ROLLBACK per raggruppare operazioni e poter annullare in caso di errore.

Esempio (PostgreSQL / MySQL-ish):

BEGIN;
-- serie di operazioni INSERT/UPDATE/DELETE
COMMIT;
-- o in caso di errore
ROLLBACK;

---

2. INSERT: inserire record

Sintassi base

INSERT INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3);

Esempio semplice

INSERT INTO customers (first_name, last_name, email, city)
VALUES ('Luca', 'Bianchi', 'luca.bianchi@example.com', 'Firenze');

Insert multiplo (batch)

INSERT INTO customers (first_name, last_name, email, city)
VALUES
  ('Anna', 'Rossi', 'anna.rossi@example.com', 'Milano'),
  ('Marco', 'Verdi', 'marco.verdi@example.com', 'Torino'),
  ('Giulia', 'Neri', 'giulia.neri@example.com', 'Bologna');

Insert da SELECT (usato per migrazioni / copie)

INSERT INTO customers_backup (first_name, last_name, email, city)
SELECT first_name, last_name, email, city FROM customers
WHERE created_at < '2023-01-01';

Insert con RETURNING (PostgreSQL)

Per ottenere l'id generato:

INSERT INTO customers (first_name, last_name, email)
VALUES ('Paolo', 'Rossi', 'paolo.rossi@example.com')
RETURNING id;

---

3. UPDATE: aggiornare record

Sintassi base

UPDATE table_name
SET col1 = new_val1, col2 = new_val2
WHERE condition;

Attenzione: senza WHERE aggiornerai tutte le righe.

Esempio

UPDATE customers
SET city = 'Firenze', updated_at = CURRENT_TIMESTAMP
WHERE email = 'luca.bianchi@example.com';

Update con JOIN (es. aggiornare da altra tabella)

PostgreSQL / MySQL (diversa sintassi):

-- MySQL style
UPDATE customers c
JOIN regions r ON c.region_code = r.code
SET c.tax_rate = r.default_tax
WHERE r.country = 'IT';

-- PostgreSQL style (FROM):
UPDATE customers c
SET tax_rate = r.default_tax
FROM regions r
WHERE c.region_code = r.code AND r.country = 'IT';

---

4. DELETE: cancellare record

Sintassi base

DELETE FROM table_name
WHERE condition;

Attenzione: senza WHERE eliminerai tutti i record.

Esempio

DELETE FROM customers
WHERE last_login < '2018-01-01';

Cancellazione sicura con transazione

BEGIN;
DELETE FROM orders WHERE status = 'test';
-- verifiche manuali qui...
COMMIT;
-- o se qualcosa va storto
ROLLBACK;

---

5. Upsert: inserire o aggiornare (INSERT ... ON CONFLICT / REPLACE / MERGE)

Per evitare duplicati e aggiornare se esiste già (diverso per dialetti):

PostgreSQL (ON CONFLICT)

INSERT INTO customers (email, first_name, last_name, city)
VALUES ('anna.rossi@example.com', 'Anna', 'Rossi', 'Milano')
ON CONFLICT (email) DO UPDATE
SET first_name = EXCLUDED.first_name,
    last_name = EXCLUDED.last_name,
    city = EXCLUDED.city,
    updated_at = CURRENT_TIMESTAMP;

MySQL (INSERT ... ON DUPLICATE KEY UPDATE)

INSERT INTO customers (email, first_name, last_name)
VALUES ('anna.rossi@example.com', 'Anna', 'Rossi')
ON DUPLICATE KEY UPDATE
first_name = VALUES(first_name),
last_name = VALUES(last_name);

SQL Standard / Enterprise (MERGE)

MERGE INTO customers AS target
USING (VALUES ('anna.rossi@example.com', 'Anna', 'Rossi')) AS src(email, first_name, last_name)
ON target.email = src.email
WHEN MATCHED THEN
  UPDATE SET first_name = src.first_name, last_name = src.last_name
WHEN NOT MATCHED THEN
  INSERT (email, first_name, last_name) VALUES (src.email, src.first_name, src.last_name);

---

6. Buone pratiche e sicurezza

Prepared statements / Parametrized queries

Per evitare SQL injection, mai concatenare input utente direttamente:
Esempio (pseudocodice):

-- NON FARE
EXECUTE 'INSERT INTO customers (email) VALUES (''' || user_email || ''')';

-- FARE (parametrizzato)
PREPARE stmt (text) AS
INSERT INTO customers (email) VALUES ($1);
EXECUTE stmt ('luca@example.com');

In applicazioni usare API del linguaggio (PDO in PHP, PreparedStatement in Java, psycopg2 in Python).

Controlli di integrità

• Constraint: PRIMARY KEY, UNIQUE, NOT NULL, CHECK.

• Foreign key: per mantenere coerenza referenziale.
  Esempio:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  city VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Logging e backup

Effettuare log delle modifiche importanti e backup regolari prima di operazioni massive.

---

7. Esercizio completo: tabella customers con inserimento dati

7.1 Creazione tabella (PostgreSQL-compatible)

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  phone VARCHAR(30),
  city VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP
);

7.2 Inserimento dati (batch)

INSERT INTO customers (email, first_name, last_name, phone, city)
VALUES
  ('luca.bianchi@example.com', 'Luca', 'Bianchi', '+39333111222', 'Firenze'),
  ('anna.rossi@example.com', 'Anna', 'Rossi', '+393491234567', 'Milano'),
  ('marco.verdi@example.com', 'Marco', 'Verdi', NULL, 'Torino');

7.3 Aggiornamento

Aggiungiamo aggiornamento città per Marco:

UPDATE customers
SET city = 'Genova', updated_at = CURRENT_TIMESTAMP
WHERE email = 'marco.verdi@example.com';

7.4 Cancellazione

Rimuoviamo un contatto di test:

DELETE FROM customers
WHERE email = 'test.user@example.com';

7.5 Upsert (PostgreSQL)

INSERT INTO customers (email, first_name, last_name, phone, city)
VALUES ('luca.bianchi@example.com', 'Luca', 'Bianchi', '+39333111222', 'Firenze')
ON CONFLICT (email) DO UPDATE
SET first_name = EXCLUDED.first_name,
    last_name = EXCLUDED.last_name,
    phone = EXCLUDED.phone,
    city = EXCLUDED.city,
    updated_at = CURRENT_TIMESTAMP;

---

8. Esercizi proposti (con suggerimenti)

1. Scrivi uno script SQL che aggiunga 100 clienti fittizi usando INSERT multipli (usa generate_series() in PostgreSQL per velocizzare).

2. Scrivi una transazione che sposti ordini da orders a orders_archive solo per ordini con status = 'cancelled' (uso di BEGIN / COMMIT / ROLLBACK).

3. Implementa una stored procedure che esegue upsert su customers e ritorna l'id del record (dialetto DB a scelta).

4. Crea un CHECK constraint che imponga formato internazionale per phone (es. inizio con '+').

---

9. Note finali

• Testa sempre le modifiche in un ambiente di staging prima di eseguire su produzione.

• Usa transazioni per operazioni multiple correlate.

• Proteggi le query con prepared statements per sicurezza e prestazioni.

• Pianifica indici se le operazioni di aggiornamento/cancellazione frequenti impattano performance: indici aiutano SELECT, ma rallentano UPDATE/INSERT/DELETE. Valuta trade-off.