Guida Completa SQL per Principianti

id	nome	cognome	stipendio
1	Mario	Rossi	2500.00
2	Laura	Bianchi	2800.00

id	nome	cognome	stipendio	email
1	Mario	Rossi	2500.00	NULL
2	Laura	Bianchi	2800.00	NULL

id	nome	cognome	email
1	Mario	Rossi	[email protected]
2	Laura	Bianchi	[email protected]

id	nome	cognome
1	Mario	Rossi
2	Laura	Bianchi

id	nome	cognome
1	Mario	Rossi
2	Laura	Bianchi
Tabella e dati che verranno eliminati da DROP TABLE

id	nome	cognome	stipendio
1	Mario	Rossi	2500.00
2	Laura	Bianchi	2800.00
3	Giuseppe	Verdi	3000.00
Righe che verranno eliminate da TRUNCATE

id	nome	cognome	stipendio	data_assunzione
1	Mario	Rossi	2500.00	2023-03-01
2	Laura	Bianchi	2800.00	2023-06-15

id	nome	cognome	stipendio	data_assunzione
1	Mario	Rossi	2500.00	2023-03-01
2	Laura	Bianchi	2800.00	2023-06-15
3	Anna	Neri	2700.00	2024-01-15

id	nome	cognome	stipendio	data_assunzione
1	Mario	Rossi	3000.00	2023-03-01
2	Laura	Bianchi	2800.00	2023-06-15

id	nome	cognome	stipendio	data_assunzione
1	Mario	Rossi	3000.00	2023-03-01
2	Laura	Bianchi	2800.00	2023-06-15
4	Luca	Ferrari	2600.00	2024-03-01
5	Sofia	Ricci	2900.00	2024-02-10
6	Marco	Conti	3100.00	2024-04-05

id	nome	cognome	stipendio
1	Mario	Rossi	2500.00
2	Laura	Bianchi	2800.00
3	Anna	Neri	2700.00

id	nome	cognome	stipendio
1	Mario	Rossi	3000.00
2	Laura	Bianchi	2800.00
3	Anna	Neri	2700.00

id	nome	cognome	stipendio	email
1	Mario	Rossi	3000.00	NULL
2	Laura	Bianchi	2800.00	[email protected]

id	nome	cognome	stipendio	email
1	Mario	Rossi	3200.00	[email protected]
2	Laura	Bianchi	2800.00	[email protected]

id	nome	cognome	stipendio
1	Mario	Rossi	3000.00
2	Laura	Bianchi	2800.00
3	Anna	Neri	2700.00
Riga che verrà eliminata (id=3)

id	nome	cognome	stipendio
1	Mario	Rossi	3000.00
2	Laura	Bianchi	2800.00

🔧 DDL Avanzato - MySQL/MariaDB

Il Data Definition Language (DDL) rappresenta il cuore della gestione della struttura del database in MySQL e MariaDB. Mentre le operazioni base permettono di creare tabelle semplici, il DDL avanzato consente di implementare soluzioni sofisticate per l'organizzazione dei dati, l'ottimizzazione delle performance e il mantenimento dell'integrità referenziale. Questa sezione esplora le istruzioni più potenti e frequentemente utilizzate dai database administrator e dagli sviluppatori esperti, fornendo esempi pratici e best practice per ogni comando.

---

DDL

CREATE DATABASE

Descrizione

L'istruzione CREATE DATABASE è il fondamento di ogni progetto database in MySQL e MariaDB. Questo comando crea un nuovo database, che funziona come contenitore principale per tutti gli oggetti del database quali tabelle, viste, stored procedure e trigger. Il database rappresenta il livello più alto di organizzazione dei dati e deve essere pianificato con attenzione prima di procedere con la creazione degli oggetti contenuti.

Quando crei un database, MySQL genera automaticamente una directory nel file system del server con il nome specificato. All'interno di questa directory verranno memorizzati tutti i file relativi alle tabelle e agli indici creati successivamente. È importante scegliere un nome significativo e conforme alle convenzioni di naming del tuo progetto, poiché modificare il nome di un database esistente richiede operazioni complesse che possono influenzare le applicazioni connesse.

La clausola opzionale IF NOT EXISTS è particolarmente utile negli script di installazione e nei processi di deployment automatizzati. Questa clausola previene la generazione di errori nel caso in cui il database esista già, permettendo allo script di continuare l'esecuzione senza interruzioni. Senza questa clausola, un tentativo di creare un database già esistente genererebbe un errore che arresterebbe l'esecuzione dello script.

Sintassi

CREATE DATABASE [IF NOT EXISTS] nome_database;

Esempi Pratici

CREATE DATABASE azienda;

Spiegazione: Questo comando crea un nuovo database denominato "azienda". Dopo l'esecuzione, il database sarà visibile nell'elenco dei database disponibili e potrà essere utilizzato per creare tabelle e altri oggetti. È la forma più semplice di creazione database, adatta quando si è sicuri che il database non esista già e non si hanno requisiti specifici per il character set.

CREATE DATABASE IF NOT EXISTS ecommerce;

Spiegazione: Questo comando tenta di creare il database "ecommerce" solo se non esiste già. La clausola IF NOT EXISTS previene la generazione di un errore se il database è già presente. Questa forma è particolarmente utile negli script di setup che potrebbero essere eseguiti più volte, come negli ambienti di sviluppo, nei container Docker, o nei processi di deployment automatizzato. Senza questa clausola, l'esecuzione dello script si arresterebbe con un errore di "database already exists".

CREATE DATABASE applicazione_web

CHARACTER SET utf8mb4

COLLATE utf8mb4_unicode_ci;

Spiegazione: Questo comando crea un database ottimizzato per memorizzare testo in più lingue, inclusi caratteri speciali e emoji. Il character set utf8mb4 è la codifica Unicode completa che supporta tutti i caratteri esistenti, inclusi quelli delle lingue asiatiche e i simboli emoji. La collazione utf8mb4_unicode_ci garantisce confronti case-insensitive (la "A" e la "a" sono considerate uguali) e ordinamenti linguistici corretti secondo le regole Unicode. Questa configurazione è lo standard consigliato per tutte le nuove applicazioni moderne.

Nota Operativa

Per iniziare a utilizzare il database appena creato, devi selezionarlo con l'istruzione USE nome_database. Questa selezione rimane attiva per tutta la sessione corrente del client MySQL. In molte applicazioni, la stringa di connessione specifica già il database da utilizzare, rendendo il comando USE necessario solo durante le operazioni manuali di amministrazione.

Best Practice

**Convenzioni di naming**: Utilizza nomi descrittivi in minuscolo con underscore per separare le parole (es. gestione_magazzino invece di GM o gestionemagazzino)
**Character set**: Definisci sempre il character set e la collazione a livello di database per garantire coerenza con le applicazioni che supportano Unicode
**Backup**: Dopo la creazione, configura immediatamente le procedure di backup per proteggere i dati
**Privilegi**: Assegna i privilegi appropriati agli utenti subito dopo la creazione per garantire la sicurezza dei dati

DDL

CREATE INDEX

Descrizione

L'istruzione CREATE INDEX è uno degli strumenti più potenti per ottimizzare le performance delle query in MySQL e MariaDB. Un indice è una struttura dati che permette al database di trovare righe specifiche senza dover eseguire una scansione completa della tabella (full table scan). Pensate a un indice come all'indice analitico di un libro: invece di leggere ogni pagina per trovare un'informazione, potete consultare l'indice e saltare direttamente alle pagine rilevanti.

Gli indici in MySQL e MariaDB sono implementati come B-tree (Balanced Tree), una struttura dati che mantiene i dati ordinati e permette ricerche, inserimenti e cancellazioni in tempo logaritmico. Questa implementazione è ottimizzata per funzionare efficacemente con i dischi rigidi tradizionali, minimizzando il numero di accessi al disco necessari per ogni operazione di ricerca.

Tuttavia, gli indici non sono gratuiti in termini di risorse. Ogni indice occupa spazio su disco (a volte significativo, quanto i dati stessi) e ogni operazione di inserimento, aggiornamento o cancellazione deve aggiornare tutti gli indici associati alla tabella. Per questo motivo, è fondamentale trovare il giusto equilibrio tra il numero di indici e le performance delle operazioni di scrittura.

Sintassi

CREATE INDEX nome_indice ON nome_tabella (colonna1, colonna2);

Esempi Pratici

CREATE INDEX idx_cognome

ON dipendenti (cognome);

Spiegazione: Questo comando crea un indice sulla colonna cognome della tabella dipendenti e lo nomina idx_cognome. Dopo la creazione, le query che cercano o ordinano per cognome saranno significativamente più rapide. Ad esempio, una query come SELECT * FROM dipendenti WHERE cognome = 'Rossi' non dovrà più scansionare tutte le righe della tabella, ma potrà utilizzare l'indice per trovare direttamente le righe corrispondenti. Questo tipo di indice è ideale per colonne con alta cardinalità (molti valori diversi) e frequentemente utilizzate nelle clausole WHERE.

Prima (senza indice)

id	nome	cognome	reparto
1	Mario	Rossi	Amministrazione
2	Laura	Bianchi	Vendite
3	Giuseppe	Verdi	Produzione
4	Anna	Rossi	Marketing
5	Marco	Neri	Amministrazione

Query: SELECT * FROM dipendenti WHERE cognome = 'Rossi'

Tempo: Scansione completa di 5 righe

Risultato: 2 righe trovate

➜

Dopo (con indice)

id	nome	cognome	reparto
1	Mario	Rossi	Amministrazione
2	Laura	Bianchi	Vendite
3	Giuseppe	Verdi	Produzione
4	Anna	Rossi	Marketing
5	Marco	Neri	Amministrazione

📑 Indice idx_cognito creato:

Cognome	Puntatore
Bianchi	➜ Riga 2
Neri	➜ Riga 5
Rossi	➜ Riga 1, 4
Verdi	➜ Riga 3

Query: SELECT * FROM dipendenti WHERE cognome = 'Rossi'

Tempo: Ricerca diretta nell'indice → accesso immediato

Risultato: 2 righe trovate

CREATE INDEX idx_reparto_data

ON dipendenti (reparto_id, data_assunzione);

Spiegazione: Questo comando crea un indice composto che include due colonne: reparto_id e data_assunzione. Un indice composto è particolarmente efficace quando le query filtrano su più colonne contemporaneamente. L'ordine delle colonne nell'indice è fondamentale: le colonne più frequentemente utilizzate nei filtri WHERE dovrebbero apparire per prime. In questo caso, l'indice è ottimizzato per query come SELECT * FROM dipendenti WHERE reparto_id = 5 ORDER BY data_assunzione DESC. L'indice permette sia il filtraggio per reparto che l'ordinamento per data_assunzione senza necessità di operazioni aggiuntive di sort.

Prima (senza indice)

id	nome	reparto_id	data_assunzione
1	Mario	1	2023-03-01
2	Laura	2	2022-11-15
3	Giuseppe	1	2023-01-20
4	Anna	2	2023-06-10
5	Marco	3	2021-09-05
Query: `SELECT * FROM dipendenti WHERE reparto_id = 1 ORDER BY data_assunzione` Operazioni: Filtro + Sort su 5 righe Tempo: O(n log n) per l'ordinamento

➜

Dopo (con indice composto)

id	nome	reparto_id	data_assunzione
1	Mario	1	2023-03-01
2	Laura	2	2022-11-15
3	Giuseppe	1	2023-01-20
4	Anna	2	2023-06-10
5	Marco	3	2021-09-05

📑 Indice idx_reparto_data creato:

reparto_id	data_assunzione	Puntatore
1	2023-01-20	➜ Riga 3
1	2023-03-01	➜ Riga 1
2	2022-11-15	➜ Riga 2
2	2023-06-10	➜ Riga 4
3	2021-09-05	➜ Riga 5
Query: `SELECT * FROM dipendenti WHERE reparto_id = 1 ORDER BY data_assunzione` Operazioni: Ricerca diretta + dati già ordinati Tempo: O(log n) per la ricerca

CREATE UNIQUE INDEX idx_email_unica

ON utenti (email);

Spiegazione: Questo comando crea un indice univoco sulla colonna email della tabella utenti. L'indice univoco non solo ottimizza le ricerche, ma garantisce anche che ogni valore nella colonna indicizzata sia univoco in tutta la tabella. Questo comando fallirà se si tenta di inserire due utenti con la stessa email, fornendo un layer di validazione automatico a livello di database. È fondamentale per implementare vincoli di unicità su colonne come email, codice fiscale, numero di partita IVA, o qualsiasi altro identificativo che deve essere unico per ogni record.

Prima (solo vincolo NOT NULL)

id	nome	email
1	Mario	[email protected]
2	Laura	[email protected]
3	Anonimo	[email protected]
Problema: Duplicato non rilevato!

➜

Dopo (con indice univoco)

id	nome	email
1	Mario	[email protected]
2	Laura	[email protected]
3	Tentativo inserimento	[email protected]
❌ ERRORE: Duplicate entry '[email protected]' for key 'idx_email_unica' Inserimento bloccato dall'indice univoco!

CREATE FULLTEXT INDEX idx_contenuto_articolo

ON articoli (titolo, contenuto);

Spiegazione: Questo comando crea un indice FULLTEXT sulle colonne titolo e contenuto della tabella articoli. L'indice FULLTEXT permette ricerche testuali avanzate utilizzando la funzione MATCH AGAINST, che consente di trovare articoli basandosi sul significato delle parole e sulla loro rilevanza, non solo su corrispondenze esatte. Questo tipo di indice è fondamentale per implementare funzionalità di ricerca come quelle presenti nei blog, nei CMS, o nei motori di ricerca interni. Supporta ricerche per parole singole, frasi, boolean mode, e ricerche con ranking di rilevanza.

Prima (LIKE '%...%')

id	titolo	contenuto
1	Introduzione a MySQL	MySQL è un database relazionale molto popolare...
2	Guida SQL	Impara SQL con esempi pratici e tutorial...
3	Database MySQL	Questo articolo spiega come ottimizzare MySQL...
Query: `SELECT * FROM articoli WHERE contenuto LIKE '%database%'` Problema: Scansione completa, nessun ranking di rilevanza

➜

Dopo (FULLTEXT con MATCH AGAINST)

id	titolo	contenuto
1	Introduzione a MySQL	MySQL è un database relazionale molto popolare...
2	Guida SQL	Impara SQL con esempi pratici e tutorial...
3	Database MySQL	Questo articolo spiega come ottimizzare MySQL...
Query: `SELECT titolo, MATCH(titolo, contenuto) AGAINST('database MySQL') AS rilevanza FROM articoli WHERE MATCH(titolo, contenuto) AGAINST('database MySQL') ORDER BY rilevanza DESC` Risultato: Articoli ordinati per rilevanza (database MySQL = rilevanza alta)

Quando utilizzare gli indici

Gli indici sono particolarmente efficaci nelle seguenti situazioni:

**Colonne in WHERE**: Le colonne frequentemente utilizzate nelle clausole WHERE traggono grande beneficio dagli indici
**Join**: Le colonne usate per unire tabelle (JOIN ON) dovrebbero sempre essere indicizzate
**ORDER BY**: Gli indici possono eliminare la necessità di un file sort esplicito
**GROUP BY**: Simili agli ORDER BY, gli indici possono velocizzare le aggregazioni
**Colonne UNIQUE**: Le constraint di unicità richiedono implicitamente un indice univoco

Attenzione alle performance

Nonostante i benefici, gli indici hanno un costo:

**Spazio disco**: Ogni indice occupa spazio, a volte quanto i dati stessi
**Rallentamento scritture**: Ogni INSERT, UPDATE, DELETE deve aggiornare tutti gli indici della tabella
**Overhead di manutenzione**: Il database deve mantenere gli indici bilanciati
**Piano di esecuzione complesso**: Troppi indici possono confondere l'ottimizzatore del query planner

Una regola empirica è creare indici solo quando si hanno problemi di performance documentati, piuttosto che creare indici preventivamente per ogni possibile utilizzo futuro.

Verifica dell'utilizzo degli indici

Per verificare se le query stanno utilizzando gli indici creati, puoi utilizzare il comando EXPLAIN:

Ad esempio se vogliamo verificare se la query SELECT * FROM dipendenti WHERE cognome = 'Rossi'; utilizza un indice usiamo l'istruzione:

EXPLAIN

SELECT * FROM dipendenti WHERE cognome = 'Rossi';

L'output della query soprastante mostrerà se l'indice viene utilizzato e come. Supponiamo che la tabella dipendenti non abbia un indice sulla colonna cognome. In tal caso l’output potrebbe essere: "Seq Scan on dipendenti (cost=0.00..15.00 rows=2 width=128) Filter: (cognome = 'Rossi'::text)"

Se invece esiste un indice su cognome , l'output potrebbe essere del tipo: "Index Scan using idx_cognome on dipendenti (cost=0.28..8.30 rows=2 width=128) Index Cond: (cognome = 'Rossi'::text)" e quest'ultimo evidentemente mostra l'uso dell'indice.

DDL

ALTER TABLE RENAME COLUMN

Descrizione

L'istruzione ALTER TABLE RENAME COLUMN è disponibile a partire da MySQL 8.0 e rappresenta un miglioramento significativo rispetto ai metodi precedenti per rinominare colonne. Questa operazione modifica il nome di una colonna esistente senza alterarne il tipo di dato, i vincoli, gli indici o i dati contenuti. La modifica è istantanea e non richiede la ricostruzione della tabella o la rielaborazione dei dati.

Prima di MySQL 8.0, rinominare una colonna richiedeva tecniche più complesse e rischiose, come creare una nuova colonna con il nome desiderato, copiare i dati, e poi eliminare la colonna originale. Questo approccio era lento, occupava spazio temporaneo significativo e poteva causare problemi con le applicazioni che accedevano alla tabella durante la migrazione.

La nuova sintassi RENAME COLUMN è atomica e sicura: la tabella rimane accessibile durante l'operazione, e se qualcosa va storto, la transazione può essere annullata. Inoltre, gli indici che fanno riferimento alla colonna vengono automaticamente aggiornati per riflettere il nuovo nome, eliminando la necessità di interventi manuali.

È importante notare che questa operazione non modifica il contenuto o la struttura dei dati, ma solo il nome della colonna. Tutti gli indici, le chiavi esterne, le viste e le stored procedure che facevano riferimento alla colonna continueranno a funzionare correttamente dopo la rinominazione.

Sintassi (MySQL 8.0+)

ALTER TABLE nome_tabella

RENAME COLUMN vecchio_nome TO nuovo_nome;

Esempi Pratici

ALTER TABLE dipendenti

RENAME COLUMN data_assunzione TO assunzione;

Spiegazione: Questo comando rinomina la colonna data_assunzione in assunzione nella tabella dipendenti. La modifica è istantanea e tutti i dati precedentemente contenuti nella colonna rimangono intatti. Questo tipo di rinominazione è utile quando si desidera utilizzare nomi più brevi e concisi che rendano le query più leggibili. L'operazione non richiede downtime e la tabella rimane accessibile per letture e scritture durante l'esecuzione del comando.

Prima

id	nome	cognome	data_assunzione
1	Mario	Rossi	2023-03-01
2	Laura	Bianchi	2022-11-15
3	Giuseppe	Verdi	2023-01-20

➜

Dopo

id	nome	cognome	assunzione
1	Mario	Rossi	2023-03-01
2	Laura	Bianchi	2022-11-15
3	Giuseppe	Verdi	2023-01-20

ALTER TABLE clienti

RENAME COLUMN ragione_sociale TO denominazione;

Spiegazione: Questo comando rinomina la colonna ragione_sociale in denominazione nella tabella clienti. In questo esempio, la colonna viene rinominata per conformarsi a uno standard di naming più conciso e moderno. Questo tipo di modifica è comune durante le fasi di refactoring o quando si consolidano database ereditati che utilizzano terminologie datate. Il nuovo nome "denominazione" è più breve e altrettanto chiaro, migliorando la leggibilità delle query SQL.

Prima

id	ragione_sociale	P.IVA
1	Azienda Alpha S.r.l.	12345678901
2	Beta Industries S.p.A.	98765432109

➜

Dopo

id	denominazione	P.IVA
1	Azienda Alpha S.r.l.	12345678901
2	Beta Industries S.p.A.	98765432109

ALTER TABLE ordini

RENAME COLUMN dt_creazione TO data_creazione_ordine;

Spiegazione: Questo comando rinomina la colonna dt_creazione in data_creazione_ordine nella tabella ordini. Questo esempio mostra come rendere il nome della colonna più descrittivo aggiungendo contesto. Un nome più lungo ma chiaro migliora la manutenibilità del codice e riduce la possibilità di errori quando gli sviluppatori scrivono query, specialmente quando lavorano su tabelle con molte colonne che potrebbero avere nomi simili. La convenzione "data_" rende immediatamente chiaro che si tratta di una data, mentre "_ordine" specifica l'entità a cui si riferisce.

Prima

id	dt_creazione	totale
100	2024-01-15	150.00
101	2024-01-16	230.50

➜

Dopo

id	data_creazione_ordine	totale
100	2024-01-15	150.00
101	2024-01-16	230.50

Nota per versioni precedenti

Se stai utilizzando MySQL 5.7 o MariaDB, puoi ottenere lo stesso risultato con una procedura in tre passaggi:

-- Passo 1: Aggiungi la nuova colonna con lo stesso tipo

ALTER TABLE dipendenti

ADD COLUMN assunzione DATE AFTER cognome;

-- Passo 2: Copia i dati dalla vecchia colonna

UPDATE dipendenti SET assunzione = data_assunzione;

-- Passo 3: Elimina la colonna originale

ALTER TABLE dipendenti

DROP COLUMN data_assunzione;

Questa procedura richiede più tempo e spazio temporaneo, specialmente per tabelle di grandi dimensioni, ed è per questo che la sintassi RENAME COLUMN è preferibile quando disponibile.

Impatto su applicazioni e codice

Prima di rinominare una colonna in un ambiente di produzione, considera:

**Query SQL**: Tutte le query che fanno riferimento alla colonna devono essere aggiornate
**ORM**: Se usi un Object-Relational Mapper, aggiorna i mapping delle entità
**API**: Se la colonna è esposta tramite API, documenta il cambiamento
**Viste e stored procedure**: Verifica e aggiorna tutti gli oggetti dipendenti
**Backup**: Assicurati che i backup recenti possano essere ripristinati correttamente

Una strategia consigliata è creare prima la nuova colonna con il nome corretto, mantenere entrambe le colonne durante una fase di transizione, e poi rimuovere la vecchia colonna dopo aver verificato che tutto funzioni.

DDL

ALTER TABLE ADD CONSTRAINT FOREIGN KEY

Descrizione

L'istruzione ALTER TABLE ADD CONSTRAINT FOREIGN KEY è il pilastro dell'integrità referenziale in un database. Una chiave esterna crea un legame "parentela" tra due tabelle (padre e figlia), garantendo che non possano esistere riferimenti a dati inesistenti. Trasforma un semplice archivio di dati in un sistema coerente dove le relazioni sono garantite dal motore del database.

La tabella figlia contiene la chiave esterna che punta alla chiave primaria (o unica) della tabella padre. È fondamentale che i tipi di dati delle due colonne siano identici (es. entrambi INT UNSIGNED). Il motore di storage InnoDB è obbligatorio per far funzionare queste restrizioni in MySQL/MariaDB.

La potenza delle Foreign Key risiede nelle clausole ON DELETE e ON UPDATE. Esse determinano il "fato" dei dati figli quando il padre viene modificato o cancellato: scegliere CASCADE, SET NULL o RESTRICT ha implicazioni drastiche sulla logica di business e sulla sicurezza dei dati.

Sintassi

ALTER TABLE tabella_figlia

ADD CONSTRAINT nome_fk

FOREIGN KEY (colonna_figlia)

REFERENCES tabella_padre (colonna_padre)

ON DELETE [AZIONE]

ON UPDATE [AZIONE];

Dove [AZIONE] può essere: CASCADE, SET NULL, RESTRICT, NO ACTION.

Esempio 1:ON DELETE CASCADE (Eliminazione a cascata)

ALTER TABLE ordini

ADD CONSTRAINT fk_ordini_clienti

FOREIGN KEY (cliente_id)

REFERENCES clienti (id)

ON DELETE CASCADE

ON UPDATE CASCADE;

Logica: L'opzione CASCADE rende il database un "domino". Se elimini un cliente (padre), il database elimina automaticamente tutti i suoi ordini (figli). Non puoi avere un ordine senza cliente.

Stato Iniziale

Tabella: clienti

id	nome
1	Mario Rossi
2	Laura Bianchi
3	Giuseppe Verdi

Tabella: ordini

id	cliente_id	importo
100	1	150.00
101	2	230.00
102	3	95.00
103	3	40.00

DELETE FROM clienti WHERE id = 3;

➜

Dopo (ON DELETE CASCADE)

Tabella: clienti

id	nome
1	Mario Rossi
2	Laura Bianchi

Tabella: ordini

id	cliente_id	importo
100	1	150.00
101	2	230.00

✅ Cliente 3 eliminato → Ordini 102 e 103 eliminati automaticamente!

Esempio 2: ON DELETE SET NULL

ALTER TABLE dipendenti

ADD CONSTRAINT fk_dipendenti_reparti

FOREIGN KEY (reparto_id)

REFERENCES reparti (id)

ON DELETE SET NULL

ON UPDATE CASCADE;

Logica: Qui usiamo SET NULL. Se un reparto viene chiuso (padre eliminato), non vogliamo licenziare i dipendenti (figli). Vogliamo solo che smettano di appartenere a quel reparto. Il campo reparto_id diventa NULL. Nota: La colonna nella tabella figlia deve accettare NULL.

Prima (Reparto Marketing esiste)

Tabella: reparti

id	nome
1	IT
2	Marketing

Tabella: dipendenti

id	nome	reparto_id
1	Mario Rossi	1
2	Giulia Verdi	2

➜

Dopo (ON DELETE SET NULL)

Tabella: reparti

id	nome
1	IT

Tabella: dipendenti

id	nome	reparto_id
1	Mario Rossi	1
2	Giulia Verdi	NULL

✅ Reparto marketing eliminato, ma Giulia è ancora in azienda (senza assegnazione).

Esempio 3: ON DELETE RESTRICT

ALTER TABLE prodotti

ADD CONSTRAINT fk_prodotti_categorie

FOREIGN KEY (categoria_id)

REFERENCES categorie (id)

ON DELETE RESTRICT

ON UPDATE NO ACTION;

Logica: RESTRICT (o l'equivalente predefinito) è il "Guardiano". Se provi a eliminare una categoria che contiene ancora prodotti, il database blocca l'operazione con un errore. Ti obbliga a gestire manualmente i prodotti prima di chiudere la categoria. È l'opzione più sicura per evitare cancellazioni accidentali.

Tentativo di eliminare categoria

Tabella: categorie

id	nome
1	Informatica
2	Libri (Attiva)

Tabella: prodotti

id	nome	categoria_id
10	Monitor	1
11	D&D Manuale	2
12	Harry Potter	2

❌ Errore: Cannot delete or update a parent row: a foreign key constraint fails.

Non puoi eliminare la categoria "Libri" perché ci sono prodotti (ID 11, 12) collegati.

Esempio 4: Foreign Key Auto-referenziale (Gerarchie)

ALTER TABLE dipendenti
    
    ADD CONSTRAINT fk_dipendenti_manager
    
    FOREIGN KEY (manager_id)
    
    REFERENCES dipendenti (id)
    
    ON DELETE SET NULL;

Logica: Una tabella può referenziare se stessa. È utile per strutture ad albero (organigrammi). Un dipendente è un record nella tabella, ma il suo manager_id punta all'id di un altro record nella stessa tabella.

Visualizzazione Organigramma

id (PK)	nome dipendente	manager_id (FK -> id)	Note
10	CEO Azienda	NULL	Nessun manager (è il capo)
20	Resp. IT	10	Il suo manager è il CEO (id 10)
21	Sviluppatore Senior	20	Il suo manager è il Resp. IT (id 20)
22	Stagista	21	Il suo manager è lo Sviluppatore (id 21)

✅ Se cancelliamo lo Sviluppatore Senior (id=21), lo stagista (id=22) non viene cancellato, ma il suo manager_id diventa NULL (perde il riferimento al capo).

Esempio 5: Aggiornamento a cascata (ON UPDATE CASCADE)

Caso d'uso: Utilizzo di chiavi non numeriche (es. codici alfanumerici) che possono essere rinominati. Se cambia il codice del "padre", deve cambiare ovunque sia stato usato.

ALTER TABLE libri
ADD CONSTRAINT fk_libri_generi
FOREIGN KEY (codice_genere)
REFERENCES generi (codice)
ON UPDATE CASCADE;

Scenario: La tabella generi usa codici brevi (es. 'THR' per Thriller). Decidiamo di cambiare il codice in una forma più estesa ('THRI'). Senza CASCADE, dovremmo aggiornare manualmente migliaia di libri. Con CASCADE, il database propaga la modifica.

Stato Attuale

Tabella: generi (Padre)

codice (PK)	nome
THR	Thriller
SF	Sci-Fi

Tabella: libri (Figlia)

titolo	codice_genere (FK)
Il silenzio degli innocenti	THR
Shining	THR

UPDATE generi SET codice = 'THRI' WHERE codice = 'THR';

➜

Dopo l'UPDATE

Tabella: generi

codice	nome
THRI	Thriller
SF	Sci-Fi

Tabella: libri

titolo	codice_genere
Il silenzio degli innocenti	THRI
Shining	THRI

✅ La modifica del codice nella tabella padre si è propagata automaticamente a tutti i libri collegati!

Esempio 6: Aggiornamento con disassociazione (ON UPDATE SET NULL)

Caso d'uso: Se l'identificativo del padre cambia, non vogliamo che i figli seguano il nuovo identificativo, ma preferiamo "sganciarli" (NULL), magari per richiedere una riassegnazione manuale.

ALTER TABLE dipendenti
ADD CONSTRAINT_fk_dip_progetti
FOREIGN KEY (codice_progetto)
REFERENCES progetti (codice)
ON UPDATE SET NULL;

Scenario: Un progetto viene rinominato da 'PRJ_ALPHA' a 'PRJ_BETA'. Per policy aziendale, se un progetto cambia codice fondamentale, i dipendenti assegnati al vecchio codice vengono "sganciati" (NULL) affinché un manager possa decidere se riassegnarli al nuovo codice 'PRJ_BETA' manualmente o ad altro.

Prima dell'aggiornamento

Tabella: progetti (Padre)

codice	stato
PRJ_OLD	Attivo

Tabella: dipendenti (Figlia)

nome	codice_progetto (FK)
Mario Rossi	PRJ_OLD
Luigi Verdi	PRJ_OLD

UPDATE progetti SET codice = 'PRJ_NEW' WHERE codice = 'PRJ_OLD';

➜

Dopo l'UPDATE

Tabella: progetti

codice	stato
PRJ_NEW	Attivo

Tabella: dipendenti

nome	codice_progetto
Mario Rossi	NULL
Luigi Verdi	NULL

⚠️ Il codice progetto è cambiato, ma i dipendenti sono stati disassociati (NULL). Devono essere riassegnati manualmente.

Esempio 7: Aggiornamento e cancellazione a cascata (ON UPDATE CASCADE + ON DELETE CASCADE)

Caso d'uso: Gestione di codici categoria dinamici. Quando un codice categoria viene modificato, l'aggiornamento si propaga automaticamente. Quando una categoria viene eliminata, tutti gli elementi associati vengono rimossi in sicurezza.

ALTER TABLE prodotti
ADD CONSTRAINT fk_prodotti_categorie
FOREIGN KEY (codice_categoria)
REFERENCES categorie (codice)
ON UPDATE CASCADE
ON DELETE CASCADE;

Scenario:

ON UPDATE CASCADE: Se il codice categoria 'ELE' viene rinominato in 'ELET', tutti i prodotti aggiornano automaticamente il riferimento.
ON DELETE CASCADE: Se la categoria 'ABG' (Abbigliamento) viene eliminata, tutti i prodotti associati vengono cancellati automaticamente per mantenere l'integrità referenziale.

Nota: Entrambe le clausole sono opzionali e vanno definite esplicitamente quando necessarie.

Stato Iniziale (Prima dell'UPDATE)

Tabella: categorie (Padre)

codice (PK)	nome
ELE	Elettronica
ABG	Abbigliamento

Tabella: prodotti (Figlia)

prodotto	codice_categoria (FK)
Smartphone X	ELE
Cuffie Pro	ELE
Maglione Inverno	ABG

UPDATE categorie SET codice = 'ELET' WHERE codice = 'ELE';

➜

Dopo l'UPDATE

Tabella: categorie

codice	nome
ELET	Elettronica
ABG	Abbigliamento

Tabella: prodotti

prodotto	codice_categoria
Smartphone X	ELET
Cuffie Pro	ELET
Maglione Inverno	ABG

✅ L'aggiornamento del codice categoria si è propagato automaticamente a tutti i prodotti!

----------

Stato Iniziale (Prima della DELETE)

Tabella: categorie (Padre)

codice (PK)	nome
ELET	Elettronica
ABG	Abbigliamento

Tabella: prodotti (Figlia)

prodotto	codice_categoria (FK)
Smartphone X	ELET
Cuffie Pro	ELET
Maglione Inverno	ABG
Sciarpa di Lana	ABG

DELETE FROM categorie WHERE codice = 'ABG';

➜

Dopo la DELETE

Tabella: categorie

codice	nome
ELET	Elettronica

Tabella: prodotti

prodotto	codice_categoria
Smartphone X	ELET
Cuffie Pro	ELET

✅ L'eliminazione della categoria ha rimosso automaticamente tutti i prodotti associati!

Esempio 8: Chiave Esterna Composta (Composite Key)

ALTER TABLE movimenti_magazzino
    
    ADD CONSTRAINT fk_movimenti_inventario
    
    FOREIGN KEY (prodotto_id, magazzino_id)
    
    REFERENCES inventario (prodotto_id, magazzino_id)
    
    ON DELETE RESTRICT;

Logica: In questo caso la chiave primaria della tabella padre (inventario) è formata da due colonne. La foreign key deve riferirsi a entrambe contemporaneamente. Questo garantisce che un movimento sia valido solo per quella specifica combinazione di Prodotto e Magazzino.

Tabella Padre: inventario (PK Composta)

prodotto_id (PK)	magazzino_id (PK)	quantità
101	A	50
101	B	20

Lo stesso prodotto (101) esiste in due magazzini diversi.

➜

Tabella Figlia: movimenti_magazzino (FK Composta)

id_mov	prodotto_id (FK)	magazzino_id (FK)	tipo
900	101	A	Entrata
901	101	B	Uscita
902	101	C	Uscita

❌ Errore nel movimento 902: La coppia (101, C) non esiste nella tabella inventario!

Esempio 9: Relazione Uno-a-Uno (One-to-One)

ALTER TABLE profili_utenti
    
    ADD CONSTRAINT fk_profili_utenti
    
    FOREIGN KEY (utente_id)
    
    REFERENCES utenti (id)
    
    ON DELETE CASCADE;

-- NOTA: Per renderla 1-a-1, la colonna utente_id DEVE essere UNIQUE
ALTER TABLE profili_utenti
ADD UNIQUE (utente_id);

Logica: A differenza della classica relazione 1-a-Molti, qui ogni riga della tabella figlia è legata a una sola riga padre e viceversa. Si usa tipicamente per separare dati sensibili (es. carta di credito, bio estesa) dalla tabella principale degli utenti. La chiave è che la FK nella tabella figlia ha un vincolo UNIQUE.

Tabella Padre: utenti (Login)
id	email	password
1	mario@email.com	******
2	luigi@email.com	******

⬇️ (Relazione UNICA)

Tabella Figlia: profili_utenti (Dettagli)
id	utente_id (FK, UNIQUE)	indirizzo_spedizione
50	1	Via Roma 1
51	2	Via Milano 2

✅ Ogni utente ha al massimo un profilo. Non puoi creare due profili per l'utente 1 grazie al vincolo UNIQUE.

Opzioni ON DELETE e ON UPDATE a confronto

Azione	Comportamento	Quando usarlo
`CASCADE`	PROPAGA ELIMINAZIONE/MODIFICA AI FIGLI: Con l'eliminazione(l'aggiornamento) di una riga del padre si ha l'eliminazione (l'aggiornamento) delle righe dei figli collegate	Relazioni forte dipendenza (Ordine effettuato /Righe Ordine effettuato).
`SET NULL`	IMPOSTA NULL NEL FIGLIO:Con l'eliminazione(l'aggiornamento) di una riga del padre si ha l'impostazione a null nelle righe dei figli collegate	Dati storici o opzionali (Dipendenti/Reparto).
`RESTRICT`	BLOCCA L'OPERAZIONE SUL PADRE: Se cancello una riga del padre che è collegata a righe dell'entità figlia, la cancellazione è bloccata	Sicurezza massima (Categorie/Prodotti).
`NO ACTION`	SIMILE A RESTRICT (verifica in differita).	Compatibilità SQL standard, logiche complesse.

Convenzioni per il nome dei constraint

Un buon nome per la foreign key facilita il debugging e la manutenzione:

Sintassi consigliata: fk_[tabella_figlia]_[tabella_padre]_[colonna_opzionale]
Esempio chiaro: fk_ordini_clienti dice subito che è il legame tra ordini e clienti.
Evitare: Nomi generici come fk1, fk_clienti (ambiguo). Usa sempre il prefisso fk_.

Gestione degli errori comuni

Creare una Foreign Key a volte fallisce. Ecco come risolvere i problemi più frequenti:

Errore 1215 (Cannot add foreign key): Solitamente causa da tipi di dati diversi (es. padre INT, figlio BIGINT) o mancato supporto InnoDB. Verifica che charset e collation siano identici.
Errore 1452 (Cannot add or update a child row): Ci sono già dati "orfani" nella tabella figlia. Prima di creare il vincolo, pulisci i dati:

-- Trova i dati sporchi che violerebbero la nuova FK

SELECT figlio.*

FROM tabella_figlio figlio

LEFT JOIN tabella_padre padre ON figlio.colonna_fk = padre.id

WHERE padre.id IS NULL AND figlio.colonna_fk IS NOT NULL;

Verifica e rimozione dei constraint

Per identificare il nome esatto del constraint (che spesso viene generato automaticamente se non specificato):

SHOW CREATE TABLE nome_tabella;

Per rimuovere il vincolo:

ALTER TABLE nome_tabella

DROP FOREIGN KEY nome_del_constraint;

Definizione dei vincoli al momento della creazione delle tabelle

I vincoli (di integrità referenziale, di dominio o di tupla) possono – e spesso è buona pratica – essere definiti direttamente durante la creazione della tabella con CREATE TABLE. Questo rende lo schema più chiaro fin dall’inizio e garantisce coerenza sin dal primo inserimento di dati.

Esempi:

-- Vincolo di dominio: valore positivo
CREATE TABLE prodotti (
id INT PRIMARY KEY,
nome VARCHAR(100) NOT NULL,
prezzo DECIMAL(10,2) CHECK (prezzo > 0)
);

Spiegazione: Il vincolo CHECK (prezzo > 0) è un esempio di vincolo di dominio: impone che il valore del campo prezzo sia sempre maggiore di zero. MySQL lo accetta nella sintassi (anche se in alcune versioni non lo applica rigorosamente a meno che non si usi il motore InnoDB con SQL mode abilitato), ma altri DBMS come PostgreSQL o SQL Server lo applicano in modo rigoroso. È comunque una buona pratica dichiararlo per chiarezza semantica.

-- Vincolo di tupla: almeno uno tra email o telefono deve essere presente
CREATE TABLE contatti (
id INT PRIMARY KEY,
email VARCHAR(100),
telefono VARCHAR(20),
CHECK (email IS NOT NULL OR telefono IS NOT NULL)
);

Spiegazione: Questo è un vincolo di tupla perché coinvolge più attributi della stessa riga. La condizione assicura che ogni record abbia almeno un mezzo di contatto: o l’email, o il telefono (o entrambi). Senza questo vincolo, si potrebbero inserire contatti completamente inutilizzabili.

-- Vincolo di integrità referenziale (foreign key)
CREATE TABLE ordini (
id INT PRIMARY KEY,
cliente_id INT NOT NULL,
data_ordine DATE NOT NULL,
FOREIGN KEY fk_ordini_clienti (cliente_id)
REFERENCES clienti(id)
ON DELETE CASCADE
);

Spiegazione: Qui viene definito un vincolo di integrità referenziale tramite una foreign key. Il campo cliente_id deve corrispondere a un valore esistente nella colonna id della tabella clienti. L’opzione ON DELETE CASCADE specifica che, se un cliente viene eliminato, tutti i suoi ordini verranno cancellati automaticamente. Il nome del vincolo (fk_ordini_clienti) segue la convenzione suggerita, rendendo facile identificarne il ruolo durante la manutenzione.

Definire i vincoli in fase di creazione evita errori successivi e documenta esplicitamente le regole del modello dati.

Vincoli di Dominio e di Tupla con CHECK

Descrizione

I vincoli di dominio e di tupla vengono implementati in SQL utilizzando l'istruzione CHECK. Questi vincoli permettono di definire regole di validazione personalizzate sui dati inseriti o modificati in una tabella, garantendo che rispettino specifiche condizioni logiche definite dallo sviluppatore.

Vincoli di Dominio: Applicano regole di validazione su una singola colonna. Ad esempio, possono garantire che un'età sia sempre positiva, che un prezzo sia maggiore di zero, o che una data di nascita sia nel passato.

Vincoli di Tupla: Applicano regole di validazione che coinvolgono più colonne della stessa riga. Ad esempio, possono garantire che la data di fine sia successiva alla data di inizio, che lo sconto non superi il prezzo originale, o che un prodotto sia disponibile solo se la quantità è maggiore di zero.

I vincoli CHECK vengono valutati ogni volta che si tenta di inserire o modificare una riga nella tabella. Se la condizione restituisce FALSE, l'operazione viene rifiutata con un errore. Se restituisce NULL (ad esempio quando un campo coinvolto è NULL), il vincolo viene considerato soddisfatto (comportamento standard SQL).

In MySQL e MariaDB, i vincoli CHECK sono supportati a partire dalla versione 8.0.16 (MySQL) e 10.2.1 (MariaDB). Nelle versioni precedenti, la sintassi viene accettata ma il vincolo non viene applicato.

Sintassi

-- Vincolo di dominio (singola colonna)
ALTER TABLE nome_tabella
ADD CONSTRAINT nome_constraint
CHECK (condizione_sulla_colonna);

-- Vincolo di tupla (multiple colonne)
ALTER TABLE nome_tabella
ADD CONSTRAINT nome_constraint
CHECK (condizione_su_più_colonne);

Esempi Pratici

ALTER TABLE prodotti
ADD CONSTRAINT chk_prezzo_positivo
CHECK (prezzo > 0);

Spiegazione: Questo vincolo di dominio garantisce che il prezzo di ogni prodotto sia sempre maggiore di zero. Impedisce l'inserimento di prodotti con prezzo negativo o nullo, il che potrebbe causare problemi nelle applicazioni di e-commerce o nella contabilità. Nota che prezzo > 0 esclude anche lo zero, il che può essere importante per alcuni scenari di business. Se invece volessi permettere anche lo zero, dovresti usare prezzo >= 0.

Inserimento tentativo

Tabella: prodotti (prima dell'inserimento)

id	nome	prezzo
1	Smartphone	599.99
2	Cuffie	89.50

Tentativo: INSERT INTO prodotti (id, nome, prezzo) VALUES (3, 'Prodotto Errato', -19.99)

➜

Risultato

Tabella: prodotti (nessuna modifica)

id	nome	prezzo
1	Smartphone	599.99
2	Cuffie	89.50

❌ ERRORE: Check constraint 'chk_prezzo_positivo' is violated

Il prezzo negativo non è permesso dal vincolo di dominio!

ALTER TABLE dipendenti
ADD CONSTRAINT chk_email_valida
CHECK (email LIKE '%@%.%');

Spiegazione: Questo vincolo di dominio verifica che l'indirizzo email contenga il carattere '@' seguito da almeno un punto. È una validazione di base del formato email. Nota che questa è una validazione sintattica molto semplice e non garantisce che l'email sia effettivamente valida o esistente. Per validazioni più complesse, potresti utilizzare espressioni regolari (se supportate dal tuo DBMS) oppure gestire la validazione a livello di applicazione.

ALTER TABLE prenotazioni
ADD CONSTRAINT chk_date_valide
CHECK (data_check_in < data_check_out);

Spiegazione: Questo è un vincolo di tupla che coinvolge due colonne. Garantisce che la data di check-in sia sempre precedente alla data di check-out in una prenotazione alberghiera. Questo tipo di vincolo è fondamentale per prevenire errori logici nei dati. Senza questo vincolo, potrebbero essere inserite prenotazioni con date incoerenti che renderebbero il sistema inutilizzabile per la gestione delle camere.

Inserimento incoerente

Tabella: prenotazioni (prima dell'inserimento)

id	camera_id	data_check_in	data_check_out
1	101	2024-01-15	2024-01-20

Tentativo: INSERT INTO prenotazioni (id, camera_id, data_check_in, data_check_out) VALUES (2, 102, '2024-02-10', '2024-02-05')

➜

Risultato

Tabella: prenotazioni (nessuna modifica)

id	camera_id	data_check_in	data_check_out
1	101	2024-01-15	2024-01-20

❌ ERRORE: Check constraint 'chk_date_valide' is violated

La data di check-out non può essere precedente al check-in!

ALTER TABLE ordini
ADD CONSTRAINT chk_sconto_valido
CHECK (sconto >= 0 AND sconto <= prezzo_totale);

Spiegazione: Questo vincolo di tupla garantisce che lo sconto applicato a un ordine sia sempre compreso tra 0 e il prezzo totale. Impedisce sia sconti negativi (che aumenterebbero il prezzo) sia sconti superiori al prezzo totale (che renderebbero l'importo finale negativo). Questo è un esempio di vincolo complesso che coinvolge condizioni multiple collegate dall'operatore logico AND.

ALTER TABLE studenti
ADD CONSTRAINT chk_eta_iscrizione
CHECK (
    (tipo_iscrizione = 'universitaria' AND eta >= 18)
    OR
    (tipo_iscrizione = 'scolastica' AND eta BETWEEN 6 AND 19)
    OR
    (tipo_iscrizione = 'adulti' AND eta >= 30)
);

Spiegazione: Questo complesso vincolo di tupla implementa regole di business specifiche per diversi tipi di iscrizione. Ogni tipo di iscrizione ha una fascia di età valida differente. Gli studenti universitari devono avere almeno 18 anni, quelli scolastici devono essere tra 6 e 19 anni, mentre i corsi per adulti sono riservati a over 30. Questo esempio mostra come i vincoli CHECK possano catturare regole di dominio complesse direttamente a livello di database.

Operatori e Funzioni Disponibili nei CHECK

Categoria	Esempi	Utilizzo Tipico
Operatori di Confronto	=, <>, <, >, <=, >=	Confronto di valori numerici, date, stringhe
Operatori Logici	AND, OR, NOT	Combinare condizioni multiple
Operatori su Stringhe	LIKE, IN, BETWEEN	Validazione formati, elenchi valori accettati
Funzioni di Data	CURDATE(), YEAR(), DATE_ADD()	Validazione date relative al giorno corrente
Funzioni Matematiche	ABS(), ROUND(), MOD()	Calcoli per validazione complessa

Convenzioni per il Naming dei Vincoli CHECK

Utilizza convenzioni di naming consistenti per i vincoli CHECK:

Prefisso standard: chk_ seguito dal nome della tabella e breve descrizione
Chiarezza: Il nome deve indicare immediatamente quale regola viene applicata
Esempi:
- chk_clienti_email_valida - Validazione email nella tabella clienti
- chk_prenotazioni_date_valide - Validazione coerenza date in prenotazioni
- chk_prodotti_prezzo_positivo - Vincolo di dominio su prezzo prodotti

Gestione degli Errori Comuni

Quando aggiungi vincoli CHECK, potresti incontrare questi errori:

Error 3819: Check constraint is violated: Tentativo di inserire dati che non rispettano il vincolo
Error 3813: Column check constraint references other column: Vincolo di dominio che fa riferimento a colonne non consentite
Error 4025: Constraint is not valid: Vincolo malformato o con sintassi errata

Per verificare quali righe esistenti violano un nuovo vincolo CHECK prima di aggiungerlo:

-- Verifica dati esistenti contro la condizione del vincolo
SELECT * 
FROM nome_tabella
WHERE NOT (condizione_del_vincolo);

Verifica e Rimozione dei Vincoli CHECK

Per verificare i vincoli CHECK esistenti in una tabella:

SELECT CONSTRAINT_NAME, CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'nome_database';

Per disabilitare temporaneamente un vincolo CHECK (se supportato):

ALTER TABLE nome_tabella
ALTER CONSTRAINT nome_constraint DISABLE;

Per rimuovere un vincolo CHECK:

ALTER TABLE nome_tabella
DROP CONSTRAINT nome_constraint;

Limitazioni e Best Practices

Performance: Vincoli CHECK complessi possono impattare le performance di INSERT/UPDATE
Portabilità: Sintassi e funzioni disponibili variano tra diversi DBMS
NULL Handling: Ricorda che NULL rende la condizione NULL, non FALSE
Livello di Validazione: Usa i CHECK per validazioni di dominio semplice; per logiche complesse, considera trigger o validazione a livello applicazione
Documentazione: Documenta sempre i vincoli CHECK complessi nel codice e nella documentazione del database

📝 DML Avanzato - Data Manipulation Language

Questa sezione approfondisce le funzionalità avanzate del linguaggio di manipolazione dei dati (DML), mostrando tecniche sofisticate per l'inserimento, la modifica e la cancellazione dei dati nelle tabelle del database. Ogni comando viene illustrato con esempi pratici dettagliati, analizzando riga per riga il codice SQL e presentando lo stato della tabella prima e dopo l'esecuzione dell'operazione. Le tecniche presentate sono essenziali per gestire scenari complessi di manipolazione dei dati in ambienti di produzione reali.

DML AVANZATO

INSERT con Subquery

Descrizione

Inserisce righe in una tabella utilizzando i risultati di una query SELECT. Questo metodo è ideale per copiare dati tra tabelle, popolare tabelle di backup o creare viste materializzate.

Sintassi

INSERT INTO tabella_destinazione (colonna1, colonna2, ...)

SELECT colonna1, colonna2, ...

FROM tabella_origine

WHERE condizione;

Esempio Pratico

INSERT INTO ordini_2024 (

ordine_id, cliente_id, totale, data_ordine, stato

)

SELECT

ordine_id, 

cliente_id, 

totale, 

data_ordine, 

'COMPLETATO'

FROM ordini

WHERE YEAR(data_ordine) = 2024;

Prima (tabella ordini_2024)

ordine_id	cliente_id	totale	data_ordine	stato
Tabella vuota

➜

Dopo (tabella ordini_2024)

ordine_id	cliente_id	totale	data_ordine	stato
1001	101	250.00	2024-01-15	COMPLETATO
1002	103	180.50	2024-01-20	COMPLETATO
1005	108	320.00	2024-02-10	COMPLETATO

3 righe inserite

Analisi del Codice

INSERT INTO ordini_2024 (ordine_id, cliente_id, totale, data_ordine, stato): Questa riga specifica la tabella di destinazione (ordini_2024) e l'elenco delle colonne che verranno popolate con i nuovi dati. La specificazione esplicita delle colonne garantisce la correttezza dell'operazione anche se la struttura della tabella dovesse cambiare in futuro.

SELECT ordine_id, cliente_id, totale, data_ordine: La clausola SELECT estrae i campi specificati dalla tabella sorgente (ordini), mantenendo i valori originali per questi quattro campi.

'COMPLETATO': Per il campo stato, viene utilizzato un valore costante stringa che sovrascrive qualsiasi valore esistente nella tabella sorgente, dimostrando come sia possibile combinare dati provenienti direttamente dalle tabelle con valori calcolati o costanti durante l'inserimento.

FROM ordini WHERE YEAR(data_ordine) = 2024: La clausola WHERE filtra solo gli ordini dell'anno 2024, utilizzando la funzione YEAR per isolare la componente relativa all'anno dalla data completa. Questo approccio dimostra la potenza delle subquery, che permettono di applicare filtri complessi direttamente nel database.

Nota

Le colonne nel SELECT devono corrispondere esattamente per tipo e posizione a quelle specificate nella INSERT. È possibile omettere alcune colonne se accettano valori NULL o hanno un DEFAULT definito.

DML AVANZATO

INSERT Multi-Riga

Descrizione

Inserisce multiple righe in un'unica istruzione INSERT, migliorando le prestazioni rispetto a INSERT multiple separate. Supportato dalla maggior parte dei database moderni con sintassi leggermente diverse.

Sintassi (SQL Server / PostgreSQL)

INSERT INTO tabella (col1, col2, col3)

VALUES

(valore1a, valore2a, valore3a),

(valore1b, valore2b, valore3b),

(valore1c, valore2c, valore3c);

Esempio Pratico

INSERT INTO prodotti (

codice_prodotto, nome, categoria, prezzo, giacenza

)

VALUES

('PROD001', 'Laptop Dell XPS', 'Elettronica', 1299.99, 15),

('PROD002', 'Mouse Wireless', 'Accessori', 29.99, 100),

('PROD003', 'Monitor 27 pollici', 'Elettronica', 349.99, 8);

Prima (tabella prodotti)

codice_prodotto	nome	categoria	prezzo	giacenza
PROD000	Prodotto Esistente	Varie	99.99	50

➜

Dopo (tabella prodotti)

codice_prodotto	nome	categoria	prezzo	giacenza
PROD000	Prodotto Esistente	Varie	99.99	50
PROD001	Laptop Dell XPS	Elettronica	1299.99	15
PROD002	Mouse Wireless	Accessori	29.99	100
PROD003	Monitor 27 pollici	Elettronica	349.99	8

3 righe inserite (totale: 4)

Analisi del Codice

INSERT INTO prodotti (codice_prodotto, nome, categoria, prezzo, giacenza): La riga specifica la tabella destinataria e l'elenco delle colonne in cui verranno inseriti i valori. L'ordine delle colonne determina la posizione di ogni valore nelle tuple successive.

VALUES ('PROD001', 'Laptop Dell XPS', 'Elettronica', 1299.99, 15): Ogni tupla rappresenta una riga completa da inserire. I valori stringa sono delimitati da apici singoli, mentre i valori numerici non richiedono delimitatori. Le tuple sono separate da virgole.

Efficienza dell'inserimento multi-riga: L'inserimento multi-riga riduce drasticamente l'overhead di parsing e ottimizzazione rispetto a INSERT multiple separate. Il database processa tutte le tuple come un singolo blocco logico, migliorando le prestazioni specialmente con grandi volumi di dati.

Nota

MySQL e PostgreSQL supportano la sintassi multi-valore con VALUES. Oracle richiede l'uso di SELECT UNION ALL. L'inserimento multi-riga riduce il overhead di parsing e migliora le prestazioni del database.

DML AVANZATO

UPDATE con JOIN

Descrizione

Aggiorna i dati di una tabella utilizzando valori provenienti da altre tabelle tramite operazioni di JOIN. Permette di sincronizzare dati tra tabelle correlate senza utilizzare subquery complesse.

Sintassi

UPDATE tabella1

SET tabella1.colonna1 = tabella2.colonna2

FROM tabella1

INNER JOIN tabella2 ON tabella1.chiave = tabella2.chiave

WHERE condizione;

Esempio Pratico

UPDATE ordini

SET ordini.totale = ordini.totale * 1.10

FROM ordini

INNER JOIN clienti ON ordini.cliente_id = clienti.id

WHERE clienti.tipo = 'PREMIUM'

AND ordini.stato = 'IN_ATTESA';

Prima

ordine_id	cliente_id	totale	stato	cliente.tipo
2001	50	100.00	IN_ATTESA	PREMIUM
2002	51	250.00	IN_ATTESA	STANDARD
2003	52	180.00	IN_ATTESA	PREMIUM

➜

Dopo

ordine_id	cliente_id	totale	stato	cliente.tipo
2001	50	110.00	IN_ATTESA	PREMIUM
2002	51	250.00	IN_ATTESA	STANDARD
2003	52	198.00	IN_ATTESA	PREMIUM

2 righe aggiornate (aumento del 10%)

Analisi del Codice

UPDATE ordini SET ordini.totale = ordini.totale * 1.10: La clausola SET specifica l'operazione di aggiornamento moltiplicando il totale attuale per 1.10 (aumento del 10%). La tabella target dell'aggiornamento è specificata subito dopo UPDATE.

FROM ordini INNER JOIN clienti ON ordini.cliente_id = clienti.id: L'INNER JOIN stabilisce la relazione tra ordini e clienti attraverso il campo cliente_id, creando un set di dati temporaneo che combina le informazioni di entrambe le tabelle.

WHERE clienti.tipo = 'PREMIUM' AND ordini.stato = 'IN_ATTESA': Il filtro applica due condizioni simultanee: il cliente deve essere di tipo PREMIUM e l'ordine deve essere nello stato IN_ATTESA. Solo le righe che soddisfano entrambe le condizioni vengono aggiornate.

Risultato: Gli ordini 2001 e 2003 (clienti PREMIUM) vengono aggiornati, mentre l'ordine 2002 (cliente STANDARD) rimane invariato, dimostrando la precisione del filtro applicato.

Nota

La sintassi con FROM JOIN è specifica di SQL Server e PostgreSQL. MySQL richiede una sintassi alternativa con subquery nella clausola SET o nella clausola UPDATE stessa.

DML AVANZATO

UPDATE con Subquery

Descrizione

Utilizza subquery all'interno dell'aggiornamento per calcolare i nuovi valori basandosi su condizioni complesse o aggregazioni di dati provenienti da altre tabelle.

Sintassi

UPDATE tabella

SET colonna = (SELECT aggregazione FROM tabella2 WHERE condizione)

WHERE EXISTS (SELECT 1 FROM tabella2 WHERE condizione);

Esempio Pratico

UPDATE prodotti

SET prezzo_vendita = (

SELECT AVG(prezzo) * 1.20

FROM prodotti_concorrenti

WHERE prodotti_concorrenti.categoria = prodotti.categoria

)

WHERE EXISTS (

SELECT 1

FROM prodotti_concorrenti

WHERE prodotti_concorrenti.categoria = prodotti.categoria

);

Prima

codice	nome	categoria	prezzo_base	prezzo_vendita
A001	Prodotto A	Elettronica	100.00	120.00
A002	Prodotto B	Abbigliamento	50.00	60.00
A003	Prodotto C	Elettronica	200.00	240.00

Dati concorrenti (Elettronica): €85, €95, €120 (Media: €100)
Dati concorrenti (Abbigliamento): €45, €55 (Media: €50)

➜

Dopo

codice	nome	categoria	prezzo_base	prezzo_vendita
A001	Prodotto A	Elettronica	100.00	120.00
A002	Prodotto B	Abbigliamento	50.00	60.00
A003	Prodotto C	Elettronica	200.00	120.00

3 righe aggiornate

Analisi del Codice

SET prezzo_vendita = (SELECT AVG(prezzo) * 1.20 FROM prodotti_concorrenti WHERE prodotti_concorrenti.categoria = prodotti.categoria): La subquery correlata calcola il prezzo medio dei concorrenti nella stessa categoria, moltiplicandolo per un margine del 20%. La condizione nella WHERE della subquery crea la correlazione con la riga esterna.

WHERE EXISTS (SELECT 1 FROM prodotti_concorrenti WHERE prodotti_concorrenti.categoria = prodotti.categoria): La clausola WHERE EXISTS garantisce che l'aggiornamento venga applicato solo ai prodotti per cui esistono dati concorrenti nella stessa categoria, evitando valori NULL.

Risultato: I prodotti A001 e A003 (Elettronica) ottengono prezzo_vendita = €100 × 1.20 = €120, mentre il prodotto A002 (Abbigliamento) ottiene €50 × 1.20 = €60.

Nota

Le subquery correlate vengono eseguite per ogni riga della tabella esterna. Per grandi dataset, considerare l'uso di JOIN per migliorare le prestazioni. Assicurarsi che la subquery restituisca al più una riga o utilizzare funzioni aggregate.

DML AVANZATO

DELETE con JOIN

Descrizione

Rimuove righe da una tabella utilizzando condizioni basate su altre tabelle correlate. Utile per eliminare dati orfani, rimuovere record obsoleti o cancellare elementi associati a determinate condizioni in tabelle collegate.

Sintassi

DELETE FROM tabella1

FROM tabella1

INNER JOIN tabella2 ON tabella1.chiave = tabella2.chiave

WHERE condizione;

Esempio Pratico

DELETE articoli

FROM articoli

INNER JOIN categorie ON articoli.categoria_id = categorie.id

WHERE categorie.nome IN ('Obsoleta', 'Discontinua')

AND articoli.quantita_venduta = 0;

Prima

codice	nome	categoria	quantita_venduta	categoria.stato
B001	Articolo B1	Elettronica	0	Obsoleta
B002	Articolo B2	Elettronica	150	Obsoleta
B003	Articolo B3	Mobili	0	Discontinua
B004	Articolo B4	Elettronica	75	Attiva
Righe che soddisfano la condizione DELETE (categoria Obsoleta/Discontinua E quantita_venduta = 0)

➜

Dopo

codice	nome	categoria	quantita_venduta	categoria.stato
B002	Articolo B2	Elettronica	150	Obsoleta
B004	Articolo B4	Elettronica	75	Attiva

2 righe eliminate

Analisi del Codice

DELETE articoli FROM articoli INNER JOIN categorie ON articoli.categoria_id = categorie.id: La sintassi specifica richiede la tabella da eliminare sia nella DELETE iniziale che come fonte nella FROM, seguita dalla JOIN. Questa duplicazione distingue la tabella target dalle tabelle utilizzate solo per le condizioni.

WHERE categorie.nome IN ('Obsoleta', 'Discontinua') AND articoli.quantita_venduta = 0: Il filtro applica due condizioni cumulative: la categoria deve essere una di quelle obsolete/discontinue E la quantità venduta deve essere zero. Solo le righe che soddisfano entrambe le condizioni vengono eliminate.

Risultato: L'articolo B001 (Obsoleta, 0 vendite) e B003 (Discontinua, 0 vendite) vengono eliminati. B002 rimane pur essendo Obsoleta (ha vendite), B004 rimane perché la sua categoria è Attiva.

Nota

La clausola DELETE con JOIN è supportata da SQL Server e PostgreSQL. MySQL richiede l'eliminazione della tabella principale dalla clausola DELETE iniziale. È consigliabile eseguire prima una SELECT per verificare le righe che verranno eliminate.

DML AVANZATO

MERGE (Upsert)

Descrizione

Combina INSERT, UPDATE e DELETE in un'unica operazione atomica. Quando la riga esiste già nella tabella di destinazione, viene aggiornata; se non esiste, viene inserita. Se necessario, può anche eliminare righe obsolete.

Sintassi

MERGE INTO tabella_target AS target

USING tabella_source AS source

ON target.chiave = source.chiave

WHEN MATCHED THEN

UPDATE SET colonna1 = source.valore1

WHEN NOT MATCHED THEN

INSERT (colonna1, colonna2) VALUES (source.valore1, source.valore2)

WHEN NOT MATCHED BY SOURCE THEN

DELETE;

Esempio Pratico

MERGE INTO inventario_target AS target

USING inventario_source AS source

ON target.codice_prodotto = source.codice_prodotto

WHEN MATCHED THEN

UPDATE SET 
target.quantita = target.quantita + source.quantita,
target.data_aggiornamento = GETDATE()

WHEN NOT MATCHED THEN

INSERT (codice_prodotto, quantita, data_aggiornamento)

VALUES (source.codice_prodotto, source.quantita, GETDATE())

WHEN NOT MATCHED BY SOURCE THEN

DELETE;

Target (prima)

codice	quantita	data_aggiornamento
PROD-A	50	2024-01-01
PROD-B	30	2024-01-01
PROD-C	20	2024-01-01

🔄

Source

codice	quantita	azione
PROD-A	+25	MATCHED
PROD-D	100	INSERT
PROD-E	45	INSERT

🔄

Target (dopo)

codice	quantita	data_aggiornamento
PROD-A	75	2024-03-15
PROD-D	100	2024-03-15
PROD-E	45	2024-03-15

Aggiornato: 1 | Inserito: 2 | Eliminato: 2 (PROD-B, PROD-C)

Analisi del Codice

MERGE INTO inventario_target AS target USING inventario_source AS source ON target.codice_prodotto = source.codice_prodotto: Definisce le tabelle source e target e la condizione di join sulla chiave codice_prodotto. Il MERGE opera su tre rami distinti basati sulla corrispondenza delle chiavi.

WHEN MATCHED THEN UPDATE SET target.quantita = target.quantita + source.quantita: Quando la chiave esiste in entrambe le tabelle, somma le quantità e aggiorna la data. PROD-A viene aggiornato da 50 a 75 (50 + 25).

WHEN NOT MATCHED THEN INSERT (codice_prodotto, quantita, data_aggiornamento) VALUES (source.codice_prodotto, source.quantita, GETDATE()): Quando la chiave esiste solo nella source, inserisce una nuova riga. PROD-D e PROD-E vengono inseriti come nuovi prodotti.

WHEN NOT MATCHED BY SOURCE THEN DELETE: Quando la chiave esiste solo nel target (PROD-B, PROD-C), elimina la riga obsoleta. PROD-B e PROD-C vengono rimossi in quanto non più presenti nella source.

Nota

MERGE è supportato da SQL Server, Oracle e PostgreSQL (sintassi simile). MySQL utilizza INSERT ... ON DUPLICATE KEY UPDATE. Assicurarsi che la condizione ON sia univoca per evitare comportamenti imprevedibili.

DML AVANZATO

OUTPUT Clause

Descrizione

Restituisce le righe modificate da un'istruzione DML, permettendo di catturare i valori prima e dopo la modifica. Utile per auditing, logging e propagazione delle modifiche ad altri sistemi.

Sintassi

UPDATE tabella

SET colonna = nuovo_valore

OUTPUT deleted.colonna, inserted.colonna

WHERE condizione;

Esempio Pratico

UPDATE conti_correnti

SET saldo = saldo - 500.00

OUTPUT 

deleted.numero_conto,

deleted.saldo AS saldo_precedente,

inserted.saldo AS saldo_attuale,

'PRELEVATO' AS tipo_transazione

WHERE cliente_id = 12345

AND saldo >= 500.00;

Tabella OUTPUT generata

numero_conto	saldo_precedente	saldo_attuale	tipo_transazione
CC-0012345	1250.00	750.00	PRELEVATO

1 riga modificata

➜

Tabella conti_correnti (dopo)

cliente_id	numero_conto	saldo	data_ultima_operazione
12345	CC-0012345	750.00	2024-03-15 14:30

Analisi del Codice

SET saldo = saldo - 500.00: L'operazione di aggiornamento sottrae €500 dal saldo corrente del conto, eseguendo un prelievo bancario. La condizione WHERE garantisce che il prelievo avvenga solo se il saldo è sufficiente (>= €500).

OUTPUT deleted.numero_conto, deleted.saldo AS saldo_precedente, inserted.saldo AS saldo_attuale: La clausola OUTPUT cattura i valori prima (deleted) e dopo (inserted) la modifica, permettendo di tracciare l'intera transazione. deleted.saldo (1250.00) mostra il valore precedente, inserted.saldo (750.00) mostra il valore risultante.

'PRELEVATO' AS tipo_transazione: Un valore costante viene incluso nell'OUTPUT per identificare il tipo di operazione. Questo approccio permette di creare audit trail completi direttamente dall'operazione DML.

Pseudo-tabelle deleted e inserted: deleted contiene i valori PRIMA della modifica, inserted contiene i valori DOPO. Per UPDATE, entrambe sono popolate; per INSERT solo inserted; per DELETE solo deleted.

Nota

La pseudo-tabella deleted contiene i valori PRIMA della modifica, mentre inserted contiene i valori DOPO la modifica. Per INSERT, solo inserted è disponibile; per DELETE, solo deleted è disponibile. Supportato da SQL Server e PostgreSQL.

DML AVANZATO

Best Practices e Performance

Linee Guida per Operazioni DML Efficienti

Le operazioni DML avanzate richiedono attenzione particolare alle performance e all'integrità dei dati per garantire che il database rimanga efficiente e coerente anche durante operazioni massive.

1. Utilizzare Transazioni Esplicite

Eseguire sempre le operazioni DML all'interno di transazioni esplicite quando si modificano dati critici. Utilizzare BEGIN TRANSACTION, COMMIT e ROLLBACK per garantire atomicità e permettere il ripristino in caso di errori.

2. Operazioni Batch per Grandi Volumi

Le operazioni su grandi volumi di dati dovrebbero essere suddivise in chunk (batch) per evitare lock prolungati e consumo eccessivo del transaction log. Processare ad esempio 1000 righe alla volta con pause intermedie.

3. Gestione degli Indici

Utilizzare indici appropriati per accelerare le operazioni UPDATE e DELETE con filtri complessi. Tuttavia, indici eccessivi possono rallentare le operazioni INSERT. Bilanciare attentamente il numero e la struttura degli indici in base ai pattern di accesso.

4. Verifica Pre-Operazione

Eseguire sempre una query SELECT equivalente prima di eseguire operazioni UPDATE o DELETE per verificare il numero e le righe che verranno modificate. Questa pratica riduce il rischio di errori e facilita il debugging.

5. Implementare l'Auditing

Utilizzare la clausola OUTPUT o trigger di auditing per garantire la tracciabilità delle modifiche. Questo è fondamentale per compliance, risoluzione problemi e debugging di applicazioni critiche.

6. Evitare Operazioni Non Qualificate

Non eseguire mai UPDATE o DELETE senza clausola WHERE a meno che non sia strettamente necessario e si sia consapevoli dell'impatto. Una singola operazione non qualificata può compromettere l'intera tabella.

Nota Finale

La padronanza delle tecniche DML avanzate richiede pratica ed esperienza. Iniziare sempre con ambienti di test prima di applicare operazioni su database di produzione. Documentare sempre le operazioni complesse e mantenere backup aggiornati prima di eseguire modifiche massive.

🔐 DCL - Data Control Language

Il Data Control Language (DCL) è il sottoinsieme del linguaggio SQL dedicato alla gestione dei permessi e dei diritti di accesso ai dati. In un ambiente di database multiutente, la sicurezza e il controllo degli accessi sono aspetti fondamentali: il DCL permette agli amministratori di definire chi può accedere a quali dati e quali operazioni può eseguire su di essi. Questa sezione del manuale descrive in dettaglio i comandi GRANT, REVOKE, SHOW GRANTS e la gestione dei ruoli, fornendo esempi pratici e spiegazioni approfondite per ogni costrutto.

DCL

GRANT

Descrizione

Il comando GRANT è lo strumento fondamentale per assegnare privilegi a utenti o ruoli su oggetti del database. Un "privilegio" rappresenta il permesso di eseguire una determinata operazione, come leggere dati (SELECT), modificarli (UPDATE, INSERT, DELETE) o modificare la struttura del database stesso (CREATE, ALTER, DROP). Senza privilegi espliciti, un utente non può interagire con il database in alcun modo significativo: il principio di "default deny" garantisce che l'accesso sia sempre negato a meno che non venga esplicitamente concesso. GRANT opera quindi come una "chiave" che apre le porte dell'accesso controllato ai dati, permettendo agli amministratori di implementare il principio del minimo privilegio, secondo il quale ogni utente deve ricevere esattamente i permessi necessari per svolgere le proprie mansioni, nulla di più.

Sintassi

GRANT privilegio [, privilegio ...]
ON [database.]oggetto
TO 'utente'@'host'
[WITH GRANT OPTION]
[IDENTIFIED BY 'password'];

Parametri

privilegio: Il tipo di permesso da assegnare (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ecc.). È possibile specificare più privilegi separandoli con virgole.
database.oggetto: L'ambito del privilegio. Può essere un database specifico (es. azienda.*), una tabella specifica (es. azienda.dipendenti), oppure un oggetto particolare come una vista o una stored procedure.
'utente'@'host': L'identificativo dell'utente destinatario del privilegio, dove "utente" è il nome dell'account e "host" è il nome del computer o l'indirizzo IP da cui l'utente può connettersi.
WITH GRANT OPTION: Clausola opzionale che permette all'utente di trasferire i propri privilegi ad altri utenti.
IDENTIFIED BY 'password': Clausola opzionale che imposta o modifica la password dell'utente durante l'assegnazione del privilegio.

Esempio 1: Assegnazione di permessi standard

GRANT SELECT, INSERT, UPDATE
ON azienda.dipendenti
TO 'gestore'@'localhost'
IDENTIFIED BY 'password123';

🔍 Spiegazione dettagliata

Questo comando rappresenta un'operazione completa di gestione utente e permessi. L'esecuzione di questo statement produce tre effetti distinti ma interconnessi: innanzitutto, viene creato l'utente 'gestore'@'localhost' se non esiste già nel sistema; successivamente, vengono assegnati tre permessi specifici sulla tabella dipendenti del database azienda, ovvero la possibilità di leggere i dati (SELECT), inserire nuovi record (INSERT) e modificare record esistenti (UPDATE); infine, viene impostata la password di autenticazione per l'accesso. È importante notare che il permesso DELETE non è stato incluso, quindi l'utente gestore non potrà cancellare record dalla tabella, una scelta che riflette tipiche esigenze operative dove chi gestisce i dati deve poterli modificare ma non eliminare definitivamente.

🔍 Focus: Comprendere la sintassi 'utente'@'host'

La clausola TO 'utente'@'host' rappresenta uno degli aspetti più importanti della sicurezza in MySQL e non deve essere sottovalutata. Si tratta di un'identità di accesso completa che comprende due componenti distinte ma complementari. La prima componente, l'utente, è semplicemente il nome dell'account che verrà utilizzato durante la connessione al database; la seconda componente, l'host, specifica da quale postazione l'utente è autorizzato a connettersi. Questa distinzione è fondamentale per la sicurezza perché permette di creare lo stesso nome utente (ad esempio 'admin') con permessi diversi a seconda della provenienza della connessione. Un utente 'admin'@'localhost' potrebbe avere tutti i privilegi di amministrazione, mentre 'admin'@'192.168.1.100' potrebbe avere permessi limitati, creando un sistema di sicurezza geograficamente e logisticamente granulare.

I valori comuni per l'host includono:

'localhost': L'utente può connettersi solo dal server stesso dove è installato il database. Questa è la configurazione più sicura per utenti amministrativi, poiché richiede accesso fisico o remoto al server.
'192.168.1.%': L'utente può connettersi da qualsiasi computer nella rete locale 192.168.1.x. Il carattere % funziona come jolly e rappresenta "qualsiasi valore".
'%.esempio.com': L'utente può connettersi da qualsiasi computer con dominio *.esempio.com, utile in ambienti con DNS configurato.
'%': L'utente può connettersi da qualsiasi computer su Internet. Questa configurazione è estremamente pericolosa e dovrebbe essere utilizzata solo con password forti e permessi limitatissimi, se proprio necessaria.

Esempio 2: Delega dei permessi con GRANT OPTION

GRANT SELECT, INSERT
ON azienda.ordini
TO 'responsabile'@'localhost'
WITH GRANT OPTION;

🔍 Spiegazione di WITH GRANT OPTION

La clausola WITH GRANT OPTION trasforma un utente ordinario in un "mini-amministratore" per quanto riguarda i permessi specificati. Per comprendere il meccanismo, è utile fare un'analogia concreta: senza questa opzione, assegnare un permesso a un utente equivale a dargli le chiavi di una stanza; l'utente può entrare e usare la stanza, ma non può fare copie delle chiavi per altri. Con WITH GRANT OPTION, l'utente riceve non solo le chiavi, ma anche il permesso di andare dal fabbro e farne delle copie per chi ritiene opportuno. Questo significa che 'responsabile' potrà eseguire comandi GRANT simili a quello sopra per assegnare i permessi SELECT e INSERT su azienda.ordini ad altri utenti, creando così una struttura gerarchica di delegazione dei permessi.

⚠️ Attenzione: Effetto a cascata nella revoca

Un aspetto critico da considerare quando si utilizza WITH GRANT OPTION è il comportamento del sistema quando si revoca un privilegio. In MySQL e MariaDB moderni, se l'utente 'responsabile' ha a sua volta concesso permessi ad altri utenti (ad esempio a 'luigi'), e successivamente voi revocate il privilegio a 'responsabile', tutti i privilegi derivati concessi da 'responsabile' vengono automaticamente revocati a cascata. Questo meccanismo garantisce che non esistano "permessi orfani" nel sistema, ma può avere conseguenze impreviste se non si pianifica attentamente la struttura dei permessi. È sempre consigliabile documentare la gerarchia dei permessi concessi con GRANT OPTION e verificare con SHOW GRANTS prima di effettuare operazioni di revoca su utenti che hanno delegato permessi ad altri.

Esempio 3: Permessi globali su tutti i database

GRANT SELECT, SHOW VIEW
ON *.*
TO 'lettore'@'%';

🔍 Spiegazione dei permessi globali

L'oggetto *.* nel comando GRANT indica che i permessi si applicano a tutti i database e tutte le tabelle del server. Il permesso SELECT permette di leggere dati da qualsiasi tabella, mentre SHOW VIEW consente di visualizzare la definizione delle viste. Questo tipo di configurazione è tipica per utenti che devono eseguire query di analisi o reportistica su più database, come un analista dati che necessita di accesso in sola lettura all'intero patrimonio informativo dell'azienda. La restrizione a solo SELECT e SHOW VIEW garantisce che l'utente non possa modificare accidentalmente o deliberatamente i dati, una misura di sicurezza essenziale per ambienti analitici.

Privilegi comuni disponibili

I privilegi in MySQL sono organizzati in livelli e categorie. I privilegi a livello di tabella includono SELECT (lettura), INSERT (inserimento), UPDATE (modifica), DELETE (cancellazione), CREATE (creazione), ALTER (modifica struttura), INDEX (gestione indici), DROP (eliminazione) e LOCK TABLES (blocco tabelle). I privilegi a livello di database aggiungono CREATE TEMPORARY TABLES e EXECUTE per le stored procedure. I privilegi globali includono GRANT OPTION (delega), SUPER (operazioni amministrative), PROCESS (visualizzazione processi), FILE (lettura/scrittura file) e SHOW DATABASES (visualizzazione lista database). Per la sicurezza, è sempre consigliabile assegnare il privilegio minimo necessario per svolgere le funzioni richieste.

DCL

REVOKE

Descrizione

Il comando REVOKE è l'operazione complementare a GRANT e serve per rimuovere privilegi precedentemente assegnati a un utente o ruolo. La gestione dei permessi non è un'operazione statica: gli utenti cambiano ruoli aziendali, cambiano dipartimenti o lasciano l'azienda, e in tutti questi casi è necessario poter rimuovere o modificare i loro accessi. REVOKE agisce come una "serratura" che chiude le porte precedentemente aperte, implementando il principio del minimo privilegio nel tempo. Un amministratore deve essere sempre consapevole dei permessi attivi nel sistema e pronto a revocarli quando non sono più necessari, mantenendo così una postura di sicurezza attiva e dinamica.

Sintassi

REVOKE [GRANT OPTION FOR] privilegio [, privilegio ...]
ON [database.]oggetto
FROM 'utente'@'host';

Parametri

GRANT OPTION FOR: Parola chiave opzionale che revoca specificamente la capacità di delegare permessi, senza togliere i permessi stessi.
privilegio: Il tipo di permesso da rimuovere. Se non specificato con GRANT OPTION FOR, il permesso viene rimosso completamente.
database.oggetto: L'ambito del privilegio da revocare, deve corrispondere esattamente a quello con cui è stato concesso.
'utente'@'host': L'identificativo dell'utente da cui revocare il privilegio.

Esempio 1: Revoca parziale di permessi specifici

REVOKE DELETE, DROP
ON azienda.dipendenti
FROM 'gestore'@'localhost';

🔍 Spiegazione dettagliata

Questo comando esegue una "chirurgia" precisa sui permessi dell'utente gestore. Anziché rimuovere tutti i privilegi (operazione che potrebbe essere eccessiva o dannosa), vengono rimossi solo due permessi specifici: DELETE (cancellazione di righe) e DROP (eliminazione della tabella stessa). Questo scenario potrebbe verificarsi quando, ad esempio, l'azienda decide che i gestori non devono più poter cancellare dati dei dipendenti per ragioni di conformità normativa, o quando si vuole impedire l'eliminazione accidentale della tabella. L'utente mantiene tutti gli altri permessi che aveva, come SELECT, INSERT e UPDATE, garantendo la continuità operativa mentre si riduce il profilo di rischio.

Esempio 2: Revoca totale dei privilegi

REVOKE ALL PRIVILEGES, GRANT OPTION
ON *.*
FROM 'vecchio_impiegato'@'localhost';

🔍 Spiegazione della revoca totale

Il comando REVOKE ALL PRIVILEGES è lo strumento più drastico per rimuovere l'accesso a un utente. L'uso di *.* come oggetto indica che si intende rimuovere permessi su tutti i database e tutte le tabelle del server. La combinazione ALL PRIVILEGES con GRANT OPTION assicura che vengano rimossi sia i permessi operativi (SELECT, INSERT, ecc.) sia il permesso speciale di delegare questi permessi ad altri. Dopo l'esecuzione di questo comando, 'vecchio_impiegato' non avrà più alcun privilegio nel database, rendendo l'account essenzialmente inutile per operazioni sui dati. Questo tipo di comando è tipicamente utilizzato quando un dipendente lascia l'azienda o cambia ruolo radicalmente, oppure quando si vuole "resettare" completamente i permessi di un utente per ricostruirli da zero in modo controllato.

Esempio 3: Revoca selettiva della GRANT OPTION

REVOKE GRANT OPTION FOR DELETE
ON azienda.ordini
FROM 'supervisore'@'localhost';

🔍 Spiegazione di REVOKE GRANT OPTION FOR

Questa forma avanzata di REVOKE merita particolare attenzione perché permette di rimuovere la capacità di delegare un permesso specifico senza rimuovere il permesso stesso. In questo esempio, il supervisore perde la possibilità di concedere il permesso DELETE ad altri utenti, ma mantiene la capacità di cancellare record personalmente. Questo scenario è utile quando si vuole permettere a un utente di svolgere il proprio lavoro ma si teme che possa delegare impropriamente permessi pericolosi ad altri. La distinzione tra "poter fare" e "poter far fare" è sottile ma importante nella gestione avanzata della sicurezza dei database.

⚠️ Best practice: Principio del minimo privilegio

La sicurezza di un database si costruisce su un principio fondamentale: ogni utente dovrebbe avere esattamente i permessi necessari per svolgere le proprie funzioni, né più né meno. Questo approccio, noto come "principio del minimo privilegio" (Principle of Least Privilege o PoLP), minimizza i rischi in caso di compromissione di un account, furto di credenziali o errore umano. È buona pratica effettuare periodicamente revisioni dei permessi assegnati, utilizzando SHOW GRANTS per verificare chi ha accesso a cosa, rimuovere tempestivamente i permessi di utenti che hanno cambiato ruolo o lasciato l'azienda, documentare la logica dietro ogni assegnazione di permessi e preferire l'uso dei ruoli per gestire gruppi di permessi in modo coerente. La sicurezza del database non è un evento ma un processo continuo di verifica e aggiustamento.

DCL

SHOW GRANTS

Descrizione

Il comando SHOW GRANTS è lo strumento diagnostico fondamentale per visualizzare i privilegi assegnati a un utente specifico o all'utente corrente. A differenza di GRANT e REVOKE che modificano i permessi, SHOW GRANTS è un comando di sola lettura che fornisce trasparenza sullo stato attuale della sicurezza del database. Questo comando è indispensabile durante le attività di audit di sicurezza, durante il debugging di problemi di accesso (quando un utente segnala di non poter fare qualcosa che dovrebbe poter fare), durante la migrazione di database tra server (per replicare esattamente i permessi) e durante la formazione di nuovi amministratori che devono comprendere la struttura dei permessi esistente. L'output di SHOW GRANTS non è una tabella di dati ma una serie di statement SQL che, se eseguiti, ricreerebbero esattamente quei permessi su un altro server.

Sintassi

SHOW GRANTS [FOR 'utente'@'host'];

Parametri

FOR 'utente'@'host': Clausola opzionale che specifica per quale utente visualizzare i permessi. Se omessa, mostra i permessi dell'utente corrente (quello con cui si è connessi).

Esempio 1: Visualizzazione dei permessi di un utente specifico

SHOW GRANTS FOR 'gestore'@'localhost';

Esempio 2: Visualizzazione dei propri permessi

SHOW GRANTS;

🔍 Interpretazione dell'output

L'output di SHOW GRANTS è particolarmente utile perché mostra i permessi nella forma di comandi SQL eseguibili. Un tipico output potrebbe apparire come:

-- Output esempio:
GRANT SELECT, INSERT, UPDATE ON `azienda`.`dipendenti` TO 'gestore'@'localhost'
GRANT SELECT ON `azienda`.`report` TO 'gestore'@'localhost'

Questa rappresentazione ha diversi vantaggi pratici: primo, è immediatamente leggibile e comprensibile anche per chi non è esperto di tabelle di sistema; secondo, può essere copiata e incollata su un altro server per replicare esattamente gli stessi permessi (utile per ambienti di sviluppo, test e produzione); terzo, può essere salvata come parte della documentazione del database o inclusa in script di deployment. Quando si deve migrare un database o creare un ambiente di test identico a quello di produzione, SHOW GRANTS è il comando che fornisce la "ricetta" esatta dei permessi da ricreare.

Utilizzo pratico in scenari reali

SHOW GRANTS trova applicazione in molte situazioni operative quotidiane. Durante il debugging di problemi di accesso, se un utente segnala di ricevere un errore "Access denied", SHOW GRANTS può rivelare immediatamente se i permessi necessari sono effettivamente assegnati o se c'è un errore di digitazione nell'account utente (ad esempio 'utente'@'%' invece di 'utente'@'localhost'). Durante la preparazione di ambienti di test, si può usare SHOW GRANTS sull'ambiente di produzione per generare gli statement da eseguire sul test. Durante gli audit di sicurezza periodici, SHOW GRANTS permette di verificare che nessun permesso inappropriato sia stato accidentalmente concesso. Durante la rotazione del personale, SHOW GRANTS aiuta a documentare cosa aveva accesso un dipendente prima della sua uscita dall'azienda.

DCL

Gestione dei Ruoli (Roles)

Descrizione

Il sistema dei ruoli rappresenta un'evoluzione fondamentale nella gestione dei permessi SQL, introducendo un livello di astrazione tra i permessi individuali e gli utenti. Un ruolo può essere pensato come un "contenitore" o un "profilo" di permessi che può essere assegnato a più utenti. Invece di assegnare manualmente gli stessi permessi a dieci contabili uno per uno, si crea un ruolo 'contabile' con tutti i permessi necessari, e poi si assegna semplicemente quel ruolo ai dieci utenti. Questo approccio centralizza la gestione dei permessi, riduce drasticamente il rischio di errori e semplifica enormemente la manutenzione nel tempo. Quando serve modificare i permessi per tutti i contabili, si modifica il ruolo una sola volta e la modifica si propaga automaticamente a tutti gli utenti che lo detengono.

Sintassi fondamentale

-- Creazione di un ruolo
CREATE ROLE 'nome_ruolo';

-- Assegnazione di permessi al ruolo
GRANT privilegio [, privilegio ...]
ON [database.]oggetto
TO 'nome_ruolo';

-- Attivazione del ruolo per l'utente corrente
SET ROLE 'nome_ruolo';

-- Assegnazione del ruolo a un utente (versione permanente)
GRANT 'nome_ruolo' TO 'utente'@'host';

-- Visualizzazione dei ruoli attivi
SELECT CURRENT_ROLES();

Componenti del sistema di ruoli

CREATE ROLE: Crea un nuovo ruolo nel database. Il nome del ruolo segue le stesse convenzioni degli utenti e può includere il pattern 'nome'@'host' per ruoli specifici di un particolare host.
GRANT ... TO 'ruolo': Assegna permessi a un ruolo esattamente come si farebbe per un utente. Un ruolo può contenere qualsiasi combinazione di permessi.
GRANT 'ruolo' TO 'utente': Assegna un ruolo a un utente. L'utente "eredita" tutti i permessi contenuti nel ruolo.
SET ROLE: Attiva un ruolo per la sessione corrente. Alcuni sistemi richiedono l'attivazione esplicita, altri possono essere configurati per attivare automaticamente i ruoli all'accesso.
DROP ROLE: Rimuove un ruolo dal sistema. I permessi vengono automaticamente rimossi da tutti gli utenti che lo possedevano.

Esempio completo: Creazione del ruolo "Contabile"

-- Fase 1: Creazione del ruolo
CREATE ROLE 'contabile';

-- Fase 2: Assegnazione permessi al ruolo
GRANT SELECT, INSERT, UPDATE ON azienda.fatture TO 'contabile';
GRANT SELECT ON azienda.clienti TO 'contabile';
GRANT SELECT ON azienda.prodotti TO 'contabile';

-- Fase 3: Assegnazione utenti al ruolo
GRANT 'contabile' TO 'mario'@'localhost';
GRANT 'contabile' TO 'luigi'@'localhost';
GRANT 'contabile' TO 'paola'@'localhost';

🔍 Spiegazione dettagliata delle fasi

L'esempio mostra il processo completo di creazione e popolamento di un ruolo. Nella Fase 1, viene semplicemente creato un "contenitore vuoto" chiamato 'contabile'. Questo ruolo non ha ancora nessun permesso, è solo un nome che potrà essere assegnato agli utenti. Nella Fase 2, si "riempie" il ruolo con i permessi necessari: i contabili possono leggere, inserire e modificare fatture, ma possono solo leggere dati di clienti e prodotti. Notare che i permessi vengono assegnati al ruolo con la sintassi GRANT ... TO 'contabile', dove 'contabile' è trattato come un "utente speciale" che non si connette ma detiene permessi. Nella Fase 3, si assegna il ruolo completato agli utenti individuali. Ogni utente che riceve il ruolo 'contabile' ottiene automaticamente tutti i permessi definiti nel ruolo, senza bisogno di ripetere ogni singolo GRANT.

Esempio: Modifica dei permessi del ruolo

-- Aggiunta di un nuovo permesso al ruolo
GRANT DELETE ON azienda.fatture TO 'contabile';

-- Verifica: tutti i contabili hanno ora il permesso DELETE
SHOW GRANTS FOR 'mario'@'localhost';

🔍 Il potere della centralizzazione

Questo esempio dimostra il vantaggio principale dei ruoli: la modificazione centralizzata. Se l'azienda decide che i contabili devono poter eliminare fatture vecchie o errate, basta eseguire un singolo GRANT al ruolo 'contabile'. Immediatamente, Mario, Luigi, Paola e qualsiasi altro utente a cui è stato assegnato il ruolo ottengono il permesso DELETE sulle fatture. Non c'è bisogno di modificare gli account individuali, non c'è rischio di dimenticare qualcuno, non c'è bisogno di ripetere la stessa operazione dieci volte. Questo principio si applica anche alla revoca: se domani si decide di togliere il permesso DELETE, si esegue REVOKE sul ruolo e la modifica si propaga a tutti.

Esempio: Gestione di ruoli multipli per un utente

-- Creazione di ruoli aggiuntivi
CREATE ROLE 'lettore_report';
GRANT SELECT ON azienda.report_annuali TO 'lettore_report';

-- Assegnazione di ruoli multipli allo stesso utente
GRANT 'contabile' TO 'mario'@'localhost';
GRANT 'lettore_report' TO 'mario'@'localhost';

-- Mario ora ha i permessi di entrambi i ruoli
SET ROLE 'contabile', 'lettore_report';

🔍 Combinazione di ruoli

Un utente può essere assegnato a più ruoli, ottenendo l'unione di tutti i permessi contenuti in ciascuno di essi. Mario, che è un contabile ma deve anche accedere ai report annuali, riceve sia il ruolo 'contabile' che il ruolo 'lettore_report'. I suoi permessi totali saranno la somma dei permessi di entrambi i ruoli. La clausola SET ROLE permette di attivare ruoli specifici per la sessione corrente, utile quando un utente ha ruoli multipli ma non vuole averli tutti attivi contemporaneamente (ad esempio, per limitare temporaneamente i propri permessi durante operazioni delicate). Alcuni database supportano anche ruoli obbligatori che vengono attivati automaticamente all'accesso, o ruoli predefiniti che si attivano di default senza bisogno di SET ROLE esplicito.

Vantaggi strategici dell'utilizzo dei ruoli

L'adozione sistematica dei ruoli porta benefici significativi alla gestione del database. Il primo vantaggio è la riduzione della complessità amministrativa: invece di gestire centinaia di combinazioni di permessi per centinaia di utenti, si gestiscono una dozzina di ruoli ben definiti. Il secondo vantaggio è la coerenza: tutti gli utenti con lo stesso ruolo hanno esattamente gli stessi permessi, eliminando discrepanze accidentali. Il terzo vantaggio è la tracciabilità: quando si deve capire chi può fare cosa, basta guardare i ruoli invece di analizzare ogni singolo account. Il quarto vantaggio è la velocità di onboarding: quando arriva un nuovo dipendente, basta assegnargli i ruoli appropriati invece di costruire manualmente una lista di permessi. Il quinto vantaggio è la facilità di audit: per verificare la sicurezza del database, basta controllare che i ruoli siano configurati correttamente, una verifica molto più semplice che controllare migliaia di assegnazioni individuali.

⚠️ Considerazioni sulla migrazione ai ruoli

L'introduzione dei ruoli in un database esistente richiede una pianificazione attenta. È consigliabile mappare prima tutti i permessi esistenti degli utenti (usando SHOW GRANTS), identificare gruppi naturali di permessi che corrispondono a funzioni aziendali, creare i ruoli corrispondenti, verificare che i permessi del ruolo siano identici a quelli degli utenti che lo riceveranno, assegnare i ruoli agli utenti, rimuovere i permessi individuali duplicati (per evitare confusione) e documentare la nuova struttura. Durante la migrazione, è importante non rimuovere mai permessi prima di aver verificato che i ruoli li sostituiscano completamente. Un approccio graduale, ruolo per ruolo, è preferibile a una migrazione massiva che potrebbe introdurre errori difficili da tracciare.

Riepilogo del Data Control Language

Il DCL rappresenta il cuore della sicurezza in un database relazionale, permettendo di controllare con precisione chi può accedere ai dati e quali operazioni può eseguire. GRANT apre le porte assegnando permessi, REVOKE le chiude rimuovendoli, SHOW GRANTS mostra lo stato attuale dei permessi e i ruoli permettono di gestire permessi in modo centralizzato e scalabile. Una buona pratica di sicurezza richiede di assegnare sempre il minimo privilegio necessario, verificare periodicamente i permessi concessi, utilizzare i ruoli per semplificare la gestione di gruppi di utenti e documentare ogni decisione di sicurezza. La sicurezza del database non è un prodotto ma un processo continuo che richiede attenzione e manutenzione costante.

🔄 TCL - Transaction Control Language

Cos'è il TCL?

Il Transaction Control Language è un sottoinsieme di SQL dedicato alla gestione delle transazioni. Una transazione è un insieme di operazioni SQL che vengono eseguite come un'unica unità atomica: o tutte le operazioni hanno successo, oppure nessuna di esse viene applicata al database.

Le transazioni garantiscono le proprietà ACID:

Atomicità: tutte le operazioni vengono completate o nessuna
Consistenza: il database passa da uno stato valido a un altro stato valido
Isolamento: le transazioni concorrenti non interferiscono tra loro
Durabilità: una volta confermata, la transazione è permanente

TCL

START TRANSACTION / BEGIN

Descrizione

Inizia una nuova transazione esplicita. Da questo momento, tutte le operazioni SQL successive (INSERT, UPDATE, DELETE) fanno parte di un'unica unità logica indivisibile. Le modifiche non saranno visibili agli altri utenti fino al COMMIT e potranno essere annullate con ROLLBACK.

Senza una transazione esplicita, la maggior parte dei DBMS opera in modalità autocommit: ogni singola istruzione viene automaticamente confermata. Usando START TRANSACTION, si disabilita temporaneamente l'autocommit per raggruppare più operazioni.

Sintassi

START TRANSACTION;
-- oppure (sintassi alternativa supportata da MySQL, PostgreSQL)
BEGIN;
-- oppure (SQL Server)
BEGIN TRANSACTION;

Esempio 1: Prenotazione Hotel

START TRANSACTION;

-- Segna la camera come non disponibile
UPDATE camere SET disponibile = 0 WHERE id = 101;

-- Crea la prenotazione
INSERT INTO prenotazioni (cliente, camera, data_inizio, data_fine)
VALUES ('Mario Rossi', 101, '2026-07-01', '2026-07-05');

-- Addebita il pagamento
UPDATE clienti SET saldo = saldo - 400.00 WHERE nome = 'Mario Rossi';

-- Se tutto è andato bene:
COMMIT;

-- Se c'è stato un errore in qualsiasi punto:
-- ROLLBACK;

Esempio 2: Trasferimento Bancario

BEGIN;

-- Preleva dal conto origine
UPDATE conti SET saldo = saldo - 1000 
WHERE numero_conto = 'IT001';

-- Accredita sul conto destinazione
UPDATE conti SET saldo = saldo + 1000 
WHERE numero_conto = 'IT002';

-- Registra il movimento
INSERT INTO movimenti (da_conto, a_conto, importo, data_ora)
VALUES ('IT001', 'IT002', 1000, NOW());

COMMIT;

Perché usare le transazioni?

Senza la transazione, se il sistema si bloccasse dopo il prelievo ma prima dell'accredito, i soldi andrebbero "persi". Con la transazione, o entrambe le operazioni avvengono, o nessuna delle due.

TCL

COMMIT

Descrizione

COMMIT conferma definitivamente tutte le modifiche effettuate durante la transazione corrente, rendendole permanenti nel database. Una volta eseguito il commit:

Le modifiche diventano visibili a tutti gli altri utenti e sessioni
I dati vengono scritti in modo persistente sul disco
Non è più possibile annullare le operazioni con ROLLBACK
I lock sulle risorse vengono rilasciati
La transazione termina e ne inizia implicitamente una nuova

Sintassi

COMMIT;
-- oppure (forma estesa)
COMMIT WORK;

Esempio: Conferma Ordine E-commerce

START TRANSACTION;

-- Crea l'ordine
INSERT INTO ordini (cliente_id, data_ordine, totale)
VALUES (42, NOW(), 159.99);

-- Ottieni l'ID dell'ordine appena creato
SET @ordine_id = LAST_INSERT_ID();

-- Inserisci i prodotti dell'ordine
INSERT INTO ordini_dettagli (ordine_id, prodotto_id, quantita, prezzo)
VALUES (@ordine_id, 101, 2, 49.99),
       (@ordine_id, 205, 1, 59.99);

-- Aggiorna le giacenze di magazzino
UPDATE prodotti SET giacenza = giacenza - 2 WHERE id = 101;
UPDATE prodotti SET giacenza = giacenza - 1 WHERE id = 205;

-- Tutto OK: conferma definitivamente
COMMIT;

Atomicità garantita

Con COMMIT, l'intero ordine (testata, dettagli e aggiornamento magazzino) viene salvato insieme. Se non si esegue il commit, le modifiche rimangono in sospeso e potrebbero essere perse in caso di disconnessione o crash.

Attenzione

Alcune istruzioni DDL (come CREATE TABLE, DROP TABLE, ALTER TABLE) causano un commit implicito in molti DBMS. Questo significa che la transazione viene automaticamente confermata prima e dopo l'esecuzione di tali comandi.

TCL

ROLLBACK

Descrizione

ROLLBACK annulla tutte le modifiche effettuate dall'inizio della transazione corrente, riportando il database allo stato in cui si trovava prima del START TRANSACTION. È l'operazione opposta al COMMIT.

Situazioni tipiche in cui si usa ROLLBACK:

Si verifica un errore durante l'elaborazione
Una condizione di business non è soddisfatta
L'utente annulla l'operazione
Si rileva un'inconsistenza nei dati
Un vincolo di integrità viene violato

Sintassi

ROLLBACK;
-- oppure (forma estesa)
ROLLBACK WORK;

Esempio: Gestione Errori in un Trasferimento

START TRANSACTION;

-- Verifica saldo sufficiente
SELECT saldo INTO @saldo_attuale 
FROM conti WHERE numero_conto = 'IT001';

-- Preleva dal conto origine
UPDATE conti SET saldo = saldo - 5000 
WHERE numero_conto = 'IT001';

-- Controlla se il saldo è andato in negativo
SELECT saldo INTO @nuovo_saldo 
FROM conti WHERE numero_conto = 'IT001';

-- Se il saldo è negativo, annulla tutto
IF @nuovo_saldo < 0 THEN
    ROLLBACK;
    SELECT 'Errore: saldo insufficiente' AS messaggio;
ELSE
    UPDATE conti SET saldo = saldo + 5000 
    WHERE numero_conto = 'IT002';
    COMMIT;
    SELECT 'Trasferimento completato' AS messaggio;
END IF;

Esempio: Rollback Automatico su Errore

-- In MySQL con stored procedure
DELIMITER //
CREATE PROCEDURE trasferisci_fondi(
    IN p_da VARCHAR(20),
    IN p_a VARCHAR(20),
    IN p_importo DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Errore: transazione annullata' AS risultato;
    END;

    START TRANSACTION;
    
    UPDATE conti SET saldo = saldo - p_importo WHERE numero = p_da;
    UPDATE conti SET saldo = saldo + p_importo WHERE numero = p_a;
    
    COMMIT;
    SELECT 'Trasferimento riuscito' AS risultato;
END //
DELIMITER ;

Importante

Dopo un ROLLBACK, non è possibile recuperare le modifiche annullate. Il rollback è un'operazione definitiva che riporta i dati esattamente allo stato precedente all'inizio della transazione.

Rollback implicito

Se una sessione viene chiusa (disconnessione, timeout, crash) senza un COMMIT esplicito, il DBMS esegue automaticamente un ROLLBACK per garantire la consistenza dei dati.

TCL

SAVEPOINT

Descrizione

SAVEPOINT crea un punto di salvataggio all'interno di una transazione. Questo permette di eseguire un rollback parziale, annullando solo le operazioni successive al savepoint senza perdere quelle precedenti.

I savepoint sono utili quando:

Si eseguono operazioni complesse in più fasi
Si vuole poter annullare solo una parte della transazione
Si implementano logiche di retry per singole operazioni
Si gestiscono operazioni opzionali che potrebbero fallire

È possibile creare più savepoint nella stessa transazione, ciascuno con un nome univoco.

Sintassi

-- Crea un punto di salvataggio
SAVEPOINT nome_savepoint;

-- Torna a un punto di salvataggio (rollback parziale)
ROLLBACK TO SAVEPOINT nome_savepoint;
-- oppure
ROLLBACK TO nome_savepoint;

-- Elimina un punto di salvataggio
RELEASE SAVEPOINT nome_savepoint;

Esempio 1: Ordine con Dettagli Opzionali

START TRANSACTION;

-- Fase 1: Crea l'ordine principale (obbligatorio)
INSERT INTO ordini (cliente_id, data_ordine, stato)
VALUES (1, NOW(), 'nuovo');
SET @ordine_id = LAST_INSERT_ID();

-- Savepoint dopo la creazione dell'ordine
SAVEPOINT ordine_creato;

-- Fase 2: Prova ad aggiungere i dettagli
INSERT INTO ordini_dettagli (ordine_id, prodotto_id, quantita)
VALUES (@ordine_id, 101, 5);

-- Savepoint dopo i dettagli
SAVEPOINT dettagli_aggiunti;

-- Fase 3: Prova ad applicare uno sconto (potrebbe fallire)
INSERT INTO sconti_applicati (ordine_id, codice_sconto, importo)
VALUES (@ordine_id, 'PROMO2026', 15.00);

-- Se lo sconto non è valido, torna ai dettagli ma mantieni l'ordine
-- ROLLBACK TO dettagli_aggiunti;

COMMIT;

Esempio 2: Migrazione Dati con Gestione Errori

START TRANSACTION;

-- Migrazione tabella clienti
INSERT INTO clienti_nuovo SELECT * FROM clienti_vecchio;
SAVEPOINT clienti_migrati;

-- Migrazione tabella ordini
INSERT INTO ordini_nuovo SELECT * FROM ordini_vecchio;
SAVEPOINT ordini_migrati;

-- Migrazione tabella prodotti (potrebbe avere problemi)
INSERT INTO prodotti_nuovo SELECT * FROM prodotti_vecchio;

-- Se la migrazione prodotti fallisce:
-- ROLLBACK TO ordini_migrati;
-- (clienti e ordini rimangono migrati)

COMMIT;

Esempio 3: Rilascio di un Savepoint

START TRANSACTION;

UPDATE inventario SET quantita = quantita - 10 WHERE id = 1;
SAVEPOINT sp1;

UPDATE inventario SET quantita = quantita - 5 WHERE id = 2;
SAVEPOINT sp2;

-- Operazione completata con successo, non serve più sp1
RELEASE SAVEPOINT sp1;

-- Ora sp1 non esiste più, ma sp2 è ancora disponibile

COMMIT;

RELEASE SAVEPOINT

RELEASE SAVEPOINT elimina il savepoint indicato. Non esegue commit né rollback, semplicemente rimuove il punto di salvataggio. I savepoint successivi rimangono validi. Usare RELEASE libera risorse del sistema quando un savepoint non è più necessario.

Comportamento dei Savepoint

Un ROLLBACK TO SAVEPOINT annulla solo le operazioni dopo il savepoint
Il savepoint rimane attivo dopo il rollback (può essere riutilizzato)
Un COMMIT o ROLLBACK completo elimina tutti i savepoint
I nomi dei savepoint sono case-sensitive in alcuni DBMS

TCL

SET TRANSACTION

Descrizione

SET TRANSACTION configura le caratteristiche della prossima transazione, in particolare il livello di isolamento. Il livello di isolamento determina come le transazioni concorrenti interagiscono tra loro e quali fenomeni anomali sono permessi.

Sintassi

SET TRANSACTION ISOLATION LEVEL livello;

-- Livelli disponibili (dal meno al più restrittivo):
-- READ UNCOMMITTED
-- READ COMMITTED
-- REPEATABLE READ
-- SERIALIZABLE

I Livelli di Isolamento

Livello	Dirty Read	Non-Repeatable Read	Phantom Read
`READ UNCOMMITTED`	Possibile	Possibile	Possibile
`READ COMMITTED`	Prevenuto	Possibile	Possibile
`REPEATABLE READ`	Prevenuto	Prevenuto	Possibile
`SERIALIZABLE`	Prevenuto	Prevenuto	Prevenuto

Glossario dei Fenomeni

Dirty Read: leggere dati modificati da un'altra transazione non ancora confermata
Non-Repeatable Read: rileggendo gli stessi dati, si ottengono valori diversi perché un'altra transazione li ha modificati
Phantom Read: rieseguendo una query, appaiono o scompaiono righe perché un'altra transazione ha inserito o eliminato record

Esempio: Report Finanziario Consistente

-- Per un report che richiede dati consistenti
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;

SELECT SUM(saldo) AS totale_depositi 
FROM conti WHERE tipo = 'deposito';

SELECT SUM(saldo) AS totale_prestiti 
FROM conti WHERE tipo = 'prestito';

-- I dati sono garantiti consistenti tra le due query

COMMIT;

Trade-off Performance vs Consistenza

Livelli di isolamento più alti garantiscono maggiore consistenza ma riducono la concorrenza e le performance. Scegli il livello minimo necessario per le tue esigenze.

🔍 Query Language (QL)

Approfondimento Separato

La Query Language (QL), focalizzata sull’istruzione SELECT e sulle interrogazioni complesse — tra cui JOIN, GROUP BY, HAVING, subquery, funzioni aggregate, finestre analitiche e ottimizzazione delle prestazioni — è trattata in documenti HTML dedicati.

Questa scelta permette:

una maggiore gradualità didattica,
un focus esclusivo sulla logica delle interrogazioni,
l’inclusione di esempi contestuali e quiz interattivi specifici.

➜ Consulta i file separati per approfondire la potenza della SELECT in MySQL/MariaDB.

🎯 Cos'è SQL?

DDL - Data Definition Language

DML - Data Manipulation Language

DCL - Data Control Language

TCL - Transaction Control Language

QL - Query Language

📚 Approfondimento Separato

🔧 DDL - Data Definition Language

CREATE TABLE

Descrizione

Sintassi

Esempio Pratico

Nota

ALTER TABLE ADD

Descrizione

Sintassi

Esempio Pratico

Nota

ALTER TABLE MODIFY

Descrizione

Sintassi

Esempio Pratico

Attenzione

ALTER TABLE DROP COLUMN

Descrizione

Sintassi

Esempio Pratico

Attenzione

ALTER TABLE COMMENT

Descrizione

Sintassi

Esempio 1: Commento alla Tabella

Esempio 2: Commento alla Colonna

Visualizzare i commenti

DROP TABLE

Descrizione

Sintassi

Esempio Pratico

Attenzione

TRUNCATE TABLE

Descrizione

Sintassi

Esempio Pratico

Differenza con DELETE

📝 DML - Data Manipulation Language

INSERT INTO

Descrizione

Sintassi

Esempio Pratico

INSERT INTO – Più Righe

Esempio: Inserimento di Più Righe

Nota

UPDATE

Descrizione

Sintassi

Esempio Pratico

Attenzione

UPDATE – Più Campi

Esempio: Aggiornamento di Più Campi

Sintassi Multi-colonna

DELETE

Descrizione

Sintassi

Esempio Pratico

Attenzione

DELETE vs TRUNCATE

🔧 DDL Avanzato - MySQL/MariaDB

CREATE DATABASE

Descrizione

Sintassi

Esempi Pratici

Nota Operativa

Best Practice

CREATE INDEX

Descrizione

Sintassi

Esempi Pratici

Quando utilizzare gli indici

Attenzione alle performance

Verifica dell'utilizzo degli indici