Guida Completa SQL per Principianti

🔧 DDL - Data Definition Language

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 TABLE

Descrizione

Crea una nuova tabella nel database, definendo il nome delle colonne, i tipi di dati e i vincoli (constraints).

📊 Tabella Riassuntiva dei Tipi di Dati SQL (MySQL/MariaDB)

Quando crei una tabella, ogni colonna deve avere un tipo di dato. Questo dice al database che tipo di informazioni può contenere (numeri, testi, date...). Scegliere il tipo giusto è fondamentale per risparmiare spazio e velocizzare le query. Ecco la guida completa con esempi reali e spiegazioni approfondite.

🔢 Numeri Interi (senza virgola) e decimali

Tipo	Dimensione	Intervallo (con segno)	Intervallo UNSIGNED	Esempio d'uso
`TINYINT`	1 byte	-128 a 127	0 a 255	Età, voto scolastico (0-10), booleano
`SMALLINT`	2 byte	-32.768 a 32.767	0 a 65.535	Quantità di prodotti in magazzino
`INT`	4 byte	-2 miliardi a +2 miliardi	0 a 4 miliardi	ID utente, contatori normali
`BIGINT`	8 byte	-9,2×10¹⁸ a +9,2×10¹⁸	0 a 1,8×10¹⁹	Visualizzazioni pagina, ID per sistemi enormi

💰 Numeri con la Virgola (decimali)

Tipo	Precisione	A cosa serve	Esempio
`DECIMAL(p,s)`	Esatta (massimo 65 cifre totali)	Soldi, prezzi, voti precisi	`DECIMAL(5,2)` → max 999.99
`FLOAT`	Approssimata (~7 cifre)	Misure scientifiche, coordinate	3.1415927 (pi greco)
`DOUBLE`	Approssimata (~15 cifre)	Calcoli più precisi	3.14159265358979

💡 Cosa significa UNSIGNED?
Di default i numeri interi possono essere positivi o negativi. Aggiungendo UNSIGNED dici al database: "questo numero sarà sempre ≥ 0". In questo modo raddoppi il massimo positivo (perché non usi spazio per il segno). È perfetto per ID, quantità, età, ecc.
Esempio: eta TINYINT UNSIGNED → può andare da 0 a 255, ideale per l'età.

📐 Come si legge DECIMAL(p,s)?
• p = precisione totale (cifre totali prima + dopo la virgola)
• s = scala (cifre dopo la virgola)
Esempi reali:
→ DECIMAL(4,2) → 2 cifre dopo la virgola, totale 4 cifre → max 99,99 (utile per prezzi piccoli)
→ DECIMAL(2,1) → 1 cifra dopo la virgola, totale 2 cifre → max 9,9 (ottimo per i voti scolastici da 0 a 10, ad esempio 9.5)
→ DECIMAL(5,2) → 2 cifre dopo la virgola, totale 5 cifre → max 999,99 ideale per prezzi inferiori a 1000€

⚠️ Regola d'oro: per i soldi usa sempre DECIMAL! FLOAT e DOUBLE introducono errori di arrotondamento (es. 0.1 + 0.2 potrebbe dare 0.30000000000000004).

Tipo	Lunghezza massima	Caratteristiche	Quando usarlo
`CHAR(n)`	fissa (max 255)	Occupare sempre n caratteri (riempiendo con spazi)	Codici a lunghezza fissa: codice fiscale (16 caratteri), targa (7 caratteri), sesso (1 carattere: 'M'/'F'), stato (2 caratteri: esempio IT). È più veloce di VARCHAR per ricerche esatte ma va usato solo per testi di cui si conosce il numero delle lettere altrimenti ad esempio se uso char(10) per un campo ed inserisco nel campo una parola di 2 lettere, avrò comunque occupato lo spazio di 10 lettere .
`VARCHAR(n)`	variabile (max 65.535)	Nel data base occupa solo lo spazio dei caratteri effettivi + 1 o 2 byte necessari per indicare la lunghezza della stringa	Nomi, cognomi, email, indirizzi – testi di lunghezza variabile.
`TEXT`	65.535 caratteri	Testo lungo	Articoli, descrizioni lunghe, commenti.
`LONGTEXT`	4 GB	Testo lunghissimo	Libri, documenti, log.
`ENUM('val1','val2')`	fino a 65.535 valori	Lista chiusa di opzioni	esempi: Stato ordine ('annullato','in lavorazione','spedito','consegnato'), Esito scrutinio ('Promosso','Rimandato','Bocciato').

🔍 CHAR vs VARCHAR: un esempio concreto
Immagina di memorizzare un codice fiscale italiano: è sempre esattamente 16 caratteri. Se usi VARCHAR(16), ogni codice occuperà 16 caratteri + 1 byte per indicare il numero di caratteri . Con CHAR(16) occupa esattamente 16 byte (non è necessario il byte aggiuntivo per indicare il numero di caratteri) ed è più veloce per le ricerche perché il database sa che tutti i valori hanno la stessa lunghezza. Quindi:
→ CHAR: lunghezza fissa (es. codici, hash, flag).
→ VARCHAR: lunghezza variabile (es. nomi, indirizzi).

📅 Date e Orari – Formati e utilizzo

Tipo	Formato SQL	Esempio di inserimento	Descrizione
`DATE`	`'YYYY-MM-DD'`	`'2026-03-26'`	Solo data (anno-mese-giorno). Usalo per date di nascita, scadenze.
`TIME`	`'HH:MM:SS'`	`'14:30:00'`	Solo ora (ore:minuti:secondi). Per durate o orari fissi.
`DATETIME`	`'YYYY-MM-DD HH:MM:SS'`	`'2026-03-26 14:30:00'`	Data e ora insieme. Non cambia con il fuso orario. Ideale per eventi.
`TIMESTAMP`	`'YYYY-MM-DD HH:MM:SS'`	`'2026-03-26 14:30:00'`	Memorizza un istante (secondi dal 1970). Viene convertito automaticamente nel fuso orario della connessione. Ottimo per "creato il" o "modificato il".
`YEAR`	`'YYYY'`	`'2026'`	Solo anno (4 cifre). Per campi come "anno di laurea".

📅 Come scrivere le date in SQL
Le date e gli orari vanno sempre scritti tra apici singoli, nel formato standard ISO 8601 aaaa/mm/gg ove a è cifra per anno, m cifra per mese, g cifra per giorno (es. '2026-03-26' per DATE per indicare il 26/03/2026, '2026-03-26 14:30:00' per DATETIME). Questo formato è universale e non crea ambiguità.
Esempio di inserimento:

INSERT INTO eventi (nome, data_evento, ora_inizio)
VALUES ('Conferenza', '2026-03-26', '14:30:00');

🕒 DATETIME vs TIMESTAMP – quale scegliere?
• DATETIME: memorizza la data/ora esatta come la vedi. Non viene modificata in base al fuso orario. Perfetta per date di nascita, orari di eventi fissi, o quando devi preservare il valore originale.
• TIMESTAMP: memorizza un numero di secondi dal 1970 (UTC). Quando leggi il dato, MySQL lo converte automaticamente nel fuso orario della connessione. È ottimo per campi come data_creazione e ultima_modifica, perché puoi usare la funzione CURRENT_TIMESTAMP e l’opzione ON UPDATE CURRENT_TIMESTAMP per aggiornarlo automaticamente.
Esempio di tabella con TIMESTAMP auto‑gestito:

CREATE TABLE articoli (
  id INT PRIMARY KEY,
  titolo VARCHAR(200),
  creato TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  modificato TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

⚠️ Attenzione: TIMESTAMP ha un intervallo limitato (fino al 2038). Per date oltre il 2038 usa DATETIME.

⚠️ Errori comuni con le date
• Usare un formato sbagliato: MySQL accetta solo 'YYYY-MM-DD'. Un formato come '26/03/2026' verrà interpretato come '2026-03-26' solo se la modalità SQL lo permette, ma non è affidabile.
• Dimenticare gli apici: WHERE data = 2026-03-26 causerà un errore perché senza apici viene visto come una sottrazione.
• Confondere DATETIME con TIMESTAMP quando si lavora con fusi orari: se la tua applicazione serve utenti in diverse time zone, TIMESTAMP è più comodo perché si adatta automaticamente.

✅ Booleani e Altri Tipi Speciali

Tipo	Descrizione	Esempio
`BOOLEAN / BOOL`	Alias di `TINYINT(1)`.Invece di usare boolean usiamo TINYINT(1) che andrà ad indicare TRUE = 1, FALSE = 0.	`attivo BOOLEAN DEFAULT TRUE`
`BLOB`	Binary Large Object – per file binari (immagini, PDF,video)	Meglio salvare il file su disco e memorizzare in tabella solo il percorso.
`JSON`	Memorizza oggetti JSON validi. Supporta funzioni di interrogazione.	`{"nome": "Mario", "preferenze": {"tema": "scuro"}}`
`UUID`	Identificatore univoco universale a 128 bit.	`550e8400-e29b-41d4-a716-446655440000`
`SET`	Insieme di valori (puoi scegliere zero o più opzioni; quindi Usa ENUM quando il campo deve assumere esattamente uno tra un insieme fisso di valori come per esitoScrutinio, statoOrdine, Sesso; Usa SET quando il campo può contenere una combinazione di valori da un insieme prefissato come permessiFile, hobby, lingue parlate ma generalmente è meglio usare una tabella che contiene i dati considerati ).	Hobby('Lettura','Musica','Sport','Collezionismo').

🎯 BOOLEAN in MySQL: come funziona davvero
MySQL non ha un tipo booleano nativo. BOOLEAN è solo un sinonimo di TINYINT(1). Quando scrivi TRUE in una query, viene convertito in 1; FALSE diventa 0.
Esempio pratico:

CREATE TABLE utenti (
  id INT PRIMARY KEY,
  attivo BOOLEAN DEFAULT FALSE
);
INSERT INTO utenti (id, attivo) VALUES (1, TRUE);  -- Inserisce 1
SELECT * FROM utenti WHERE attivo;            -- Corretto, restituisce i riga con attivo=1
SELECT * FROM utenti WHERE attivo = TRUE;     -- Stessa cosa
SELECT * FROM utenti WHERE attivo = 1;          -- Equivalente

⚠️ Attenzione: TINYINT(1) tecnicamente può contenere qualsiasi numero da 0 a 255 (se UNSIGNED). Per coerenza, usa solo 0/1 e sfrutta la sintassi TRUE/FALSE per rendere il codice più leggibile.

🔒 Vincoli (Constraints) – Regole per proteggere i dati

I vincoli definiscono regole aggiuntive per le colonne, garantendo l'integrità dei dati. Ecco i più importanti, con esempi:

NOT NULL
La colonna non può essere vuota (NULL).
Es: nome VARCHAR(50) NOT NULL

UNIQUE
Impedisce duplicati.
Es: email VARCHAR(100) UNIQUE

DEFAULT
Valore predefinito se non specificato.
Es: attivo BOOLEAN DEFAULT TRUE

PRIMARY KEY
Identifica univocamente ogni riga (combina NOT NULL e UNIQUE).
Es: CF char(16)PRIMARY KEY

FOREIGN KEY
Collega due tabelle (integrità referenziale).
Es: CONSTRAINT fk_ordini_utenti FOREIGN KEY (utente_id) REFERENCES utenti(id)

CHECK
Verifica una condizione su una colonna (vincolo di dominio) o su più colonne della stessa riga (vincolo di tupla).
Es: CONSTRAINT chk_utenti_eta CHECK (eta >= 18), CONSTRAINT chk_prenotazioni_date CHECK (data_inizio < data_fine)

-- Esempio completo di tabella con vincoli
CREATE TABLE utenti (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(30) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  data_registrazione DATETIME DEFAULT CURRENT_TIMESTAMP,
  attivo BOOLEAN DEFAULT TRUE,
  eta TINYINT UNSIGNED,
  CONSTRAINT chk_utenti_eta CHECK (eta >= 18)
);

🏷️ Dare sempre un nome ai vincoli: `CONSTRAINT nomeVincolo`

Ogni vincolo di dominio, di tupla e di integrità referenziale va dichiarato con la parola chiave CONSTRAINT seguita dal nome, prima della clausola:

-- vincolo di dominio / di tupla
CONSTRAINT chk_ordini_sconto CHECK (sconto >= 0 AND sconto <= prezzo_totale),
-- vincolo di integrità referenziale
CONSTRAINT fk_ordini_clienti FOREIGN KEY (cliente_id)
  REFERENCES clienti (id)

Convenzioni: chk_nomeTabella_nomeRegola per dominio e tupla, fk_tabellaReferenziante_tabellaReferenziata per le chiavi esterne.

Il nome del vincolo è importante perché permette di:

Eliminare il vincolo: ALTER TABLE ... DROP CONSTRAINT nomeVincolo (per le FK anche DROP FOREIGN KEY, per i CHECK anche DROP CHECK) richiede il nome; un vincolo senza nome riceve un nome generato dal DBMS, che va prima cercato.
Ispezionare le impostazioni: i nomi si interrogano in INFORMATION_SCHEMA.TABLE_CONSTRAINTS, INFORMATION_SCHEMA.CHECK_CONSTRAINTS e INFORMATION_SCHEMA.KEY_COLUMN_USAGE; senza nome la ricerca diventa per tentativi.
Capire i messaggi di errore: MariaDB riporta il nome del vincolo violato (es. CONSTRAINT 'chk_ordini_sconto' failed for 'negozio'.'ordini'), quindi si capisce subito quale regola è scattata; con nomi generati il messaggio è molto meno parlante.
Documentare lo schema: un nome esplicito dice quale regola di business protegge il vincolo.

La PRIMARY KEY non ha bisogno di un nome: in MySQL/MariaDB si chiama sempre PRIMARY.

🧱 Vincoli di Dominio e di Tupla (CHECK)

Oltre ai vincoli già elencati (PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, DEFAULT), la CREATE TABLE può contenere i vincoli di dominio e i vincoli di tupla. Entrambi si scrivono con CHECK (...): cambia solo a cosa si applica la condizione. Sono vincoli intra-relazionali: riguardano una sola riga della tabella, mai più righe o tabelle diverse.

Vincolo di dominio — riguarda una sola colonna e limita i valori che quella colonna può assumere, cioè il "dominio" dell'attributo. Esempi: prezzo > 0, eta >= 18, esito IN ('Promosso','Rimandato','Bocciato'), email LIKE '%@%.%'. In parte il dominio è già fissato dal tipo di dato (TINYINT UNSIGNED non accetta negativi, DATE non accetta testo, ENUM accetta solo i valori elencati): il CHECK serve per i limiti che il tipo da solo non esprime.

Vincolo di tupla — riguarda più colonne della stessa riga e confronta i loro valori. Esempi: data_check_in < data_check_out, sconto BETWEEN 0 AND prezzo_totale, quantita > 0 OR stato = 'esaurito'. Non può guardare altre righe né altre tabelle: regole del tipo "lo stipendio non supera quello del capo" non sono esprimibili con un CHECK (servono trigger o logica applicativa).

📝 In sede d'esame
I vincoli di dominio e di tupla vanno dichiarati dentro la CREATE TABLE, insieme alle colonne: fanno parte della definizione dello schema esattamente come la chiave primaria e le chiavi esterne. Un vincolo aggiunto dopo con ALTER TABLE è sintatticamente corretto, ma non è la risposta attesa quando l'esercizio chiede di "creare la tabella con i vincoli".

Come si scrive un CHECK con nome

Sintassi

-- 1) Vincolo di dominio: CHECK di tabella, con nome
CREATE TABLE nome_tabella (
  colonna tipo_dato,
  ...
  CONSTRAINT nome_check CHECK (condizione_sulla_colonna)
);

-- 2) Vincolo di tupla: CHECK di tabella, con nome
CREATE TABLE nome_tabella (
  colonna1 tipo_dato,
  colonna2 tipo_dato,
  ...
  CONSTRAINT nome_check CHECK (condizione_su_più_colonne)
);

Il CHECK può essere scritto anche inline, subito dopo la colonna, ma in quel caso MariaDB non accetta CONSTRAINT nome dentro la definizione della colonna: per dare un nome al vincolo — cosa obbligatoria — si usa sempre la forma di tabella, con la condizione elencata dopo le colonne. Vale sia per i vincoli di dominio sia per quelli di tupla. L'inline resta ammesso solo senza nome e solo se la condizione usa esclusivamente quella colonna; se coinvolge altre colonne il DBMS lo rifiuta (in MySQL: errore 3813 Column check constraint references other column). Il nome (CONSTRAINT nome_check) va sempre scritto: senza nome il DBMS ne genera uno e ritrovarlo per ispezionarlo o eliminarlo diventa difficile (vedi il riquadro Dare sempre un nome ai vincoli). Gli esempi seguenti mostrano i quattro casi concreti: cosa funziona e cosa il DBMS rifiuta.

Inline o di tabella: i quattro casi, con esempio

-- CASO 1 — FORMA DI TABELLA CON NOME: sempre accettata, è quella da usare
CREATE TABLE prodotti (
  id INT UNSIGNED PRIMARY KEY,
  prezzo DECIMAL(10,2) NOT NULL,
  sconto DECIMAL(10,2) NOT NULL DEFAULT 0,
  -- dominio: una sola colonna
  CONSTRAINT chk_prodotti_prezzo CHECK (prezzo > 0),
  -- tupla: confronta due colonne della stessa riga
  CONSTRAINT chk_prodotti_sconto CHECK (sconto <= prezzo)
);

-- CASO 2 — INLINE CON NOME: rifiutato (dopo una colonna non si può scrivere CONSTRAINT)
CREATE TABLE prodotti (
  prezzo DECIMAL(10,2) NOT NULL CONSTRAINT chk_prodotti_prezzo CHECK (prezzo > 0)
);
-- ERROR 1064 (42000): You have an error in your SQL syntax ...
-- ... near 'CONSTRAINT chk_prodotti_prezzo CHECK (prezzo > 0)'

-- CASO 3 — INLINE SENZA NOME, condizione su una sola colonna: accettato,
-- ma il nome lo sceglie il DBMS (MySQL: prodotti_chk_1, MariaDB: CONSTRAINT_1)
CREATE TABLE prodotti (
  prezzo DECIMAL(10,2) NOT NULL CHECK (prezzo > 0)
);
-- il vincolo esiste, ma per eliminarlo bisogna prima cercare il nome generato:
-- ALTER TABLE prodotti DROP CHECK prodotti_chk_1;   -- nome non scelto da noi

-- CASO 4 — INLINE SENZA NOME che guarda un'altra colonna: rifiutato
CREATE TABLE ordini (
  prezzo_totale DECIMAL(10,2) NOT NULL,
  sconto DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (sconto <= prezzo_totale)
);
-- ERROR 3813 (HY000): Check constraint 'ordini_chk_1' references other column.

-- CASO 4 (versione corretta): stessa regola, spostata a livello di tabella e con nome
CREATE TABLE ordini (
  prezzo_totale DECIMAL(10,2) NOT NULL,
  sconto DECIMAL(10,2) NOT NULL DEFAULT 0,
  CONSTRAINT chk_ordini_sconto
    CHECK (sconto >= 0 AND sconto <= prezzo_totale)
);

In breve: il caso 1 è l'unico da scrivere in un compito; i casi 2 e 4 sono errori che il DBMS blocca; il caso 3 è sintatticamente lecito ma lascia il vincolo con un nome generato, quindi non si sa come citarlo in un DROP CHECK.

Esempio 1 — Vincoli di dominio

CREATE TABLE prodotti (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(100) NOT NULL,
  prezzo DECIMAL(10,2) NOT NULL,
  quantita SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  stato ENUM('disponibile','esaurito','sospeso') NOT NULL DEFAULT 'disponibile',
  CONSTRAINT chk_prodotti_prezzo CHECK (prezzo > 0)
);

Spiegazione. Tre vincoli di dominio espressi in tre modi diversi: prezzo > 0 è un CHECK vero e proprio (il tipo DECIMAL da solo accetterebbe anche -19.99); quantita SMALLINT UNSIGNED esprime il dominio "interi non negativi" già col tipo; stato ENUM(...) ci dice che stato può avere come valore 'disponibile' o 'esaurito' o 'sospeso'. Nota che prezzo > 0 esclude anche lo zero: se lo zero è un valore lecito (prodotto omaggio) si scrive prezzo >= 0.

Esempio 2 — Dominio, NULL e validazione di formato

CREATE TABLE clienti (
  id INT UNSIGNED PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  eta TINYINT UNSIGNED NOT NULL,
  CONSTRAINT chk_clienti_email CHECK (email LIKE '%@%.%'),
  CONSTRAINT chk_clienti_eta CHECK (eta BETWEEN 18 AND 120)
);

Spiegazione. email LIKE '%@%.%', ovvero controlla che la email contenga una chiocciola seguita da almeno un punto: non garantisce che l'indirizzo esista, ma blocca gli errori più grossolani. eta BETWEEN 18 AND 120 è un intervallo di dominio. Attenzione al NULL: se eta fosse nullable, un NULL renderebbe la condizione UNKNOWN e il vincolo sarebbe considerato rispettato; qui il problema non si pone perché la colonna è NOT NULL. Regola pratica: se il CHECK presuppone un valore, aggiungi anche NOT NULL.

Esempio 3 — Vincolo di tupla su due colonne

CREATE TABLE prenotazioni (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  camera_id INT UNSIGNED NOT NULL,
  data_check_in DATE NOT NULL,
  data_check_out DATE NOT NULL,
  CONSTRAINT chk_prenotazioni_date CHECK (data_check_in < data_check_out)
);

Spiegazione: il vincolo impedisce che sia commesso l'errore di inserire una data_check_in successiva alla data_check_out.

Esempio 4 — Vincolo di tupla con AND

CREATE TABLE ordini (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  prezzo_totale DECIMAL(10,2) NOT NULL,
  sconto DECIMAL(10,2) NOT NULL DEFAULT 0,
  CONSTRAINT chk_ordini_sconto
    CHECK (sconto >= 0 AND sconto <= prezzo_totale)
);

Spiegazione. Impedisce sia gli sconti negativi (che aumenterebbero il prezzo) sia gli sconti superiori al totale (che renderebbero l'importo finale negativo). Le due condizioni sono collegate da AND: entrambe devono essere vere. Un vincolo di tupla può combinare più colonne e più operatori logici in un'unica espressione.

Esempio 5 — Vincolo di tupla con regole condizionali (AND / OR)

CREATE TABLE iscrizioni (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tipo ENUM('universitaria','scolastica','adulti') NOT NULL,
  eta TINYINT UNSIGNED NOT NULL,
  CONSTRAINT chk_iscrizioni_eta CHECK (
    (tipo = 'universitaria' AND eta >= 18)
    OR (tipo = 'scolastica' AND eta BETWEEN 6 AND 19)
    OR (tipo = 'adulti' AND eta >= 30)
  )
);

Spiegazione. Ogni tipo di iscrizione ha una fascia d'età diversa: il CHECK è una catena di casi, ciascuno con la propria condizione. tipo e eta sono NOT NULL, quindi nessun ramo può diventare UNKNOWN: la riga è accettata solo se almeno una delle tre alternative è vera. È il tipico esempio d'esame di vincolo di tupla "complesso".

Cosa succede quando il vincolo viene violato

-- prezzo negativo: viola chk_prodotti_prezzo (dominio)
INSERT INTO prodotti (nome, prezzo) VALUES ('Prodotto errato', -19.99);
-- ERROR 3819 (HY000): Check constraint 'chk_prodotti_prezzo' is violated.

-- sconto maggiore del totale: viola chk_ordini_sconto (tupla)
INSERT INTO ordini (prezzo_totale, sconto) VALUES (100.00, 150.00);
-- ERROR 3819 (HY000): Check constraint 'chk_ordini_sconto' is violated.

-- check-out prima del check-in: viola chk_prenotazioni_date (tupla)
UPDATE prenotazioni SET data_check_out = '2024-02-01' WHERE id = 1;
-- ERROR 3819 (HY000): Check constraint 'chk_prenotazioni_date' is violated.

Il vincolo viene valutato su ogni INSERT e ogni UPDATE della riga; se la condizione è FALSE l'intera istruzione viene rifiutata e la tabella resta invariata (non viene salvata "mezza riga"). Il CHECK agisce anche quando il vincolo è violato da un UPDATE che modifica una sola delle colonne coinvolte, come nell'ultima riga dell'esempio.

Dominio vs tupla: riepilogo

Le due colonne sono le due famiglie di CHECK, le righe rispondono alla stessa domanda per entrambe: si legge per righe. L'unica differenza vera è la prima riga (quante colonne entrano nella condizione); la sintassi, invece, è identica — sempre dopo le colonne e sempre con il nome.

Domanda	Vincolo di dominio	Vincolo di tupla
Cosa controlla	i valori di una sola colonna, riga per riga	la combinazione di due o più colonne della stessa riga
Come si scrive	Allo stesso modo: dopo tutte le colonne, nella forma `CONSTRAINT nome CHECK (condizione)`. La forma inline (subito dopo la colonna) non accetta il nome, quindi non si usa.
Esempio di codice	`CONSTRAINT chk_prodotti_prezzo CHECK (prezzo > 0)`	`CONSTRAINT chk_ordini_sconto CHECK (sconto <= prezzo_totale)`
Condizioni tipiche	`prezzo > 0`, `eta BETWEEN 18 AND 120`, `email LIKE '%@%.%'`	`data_check_in < data_check_out`, `sconto <= prezzo_totale`
Nome consigliato	`chk_` + tabella + colonna → `chk_prodotti_prezzo`	`chk_` + tabella + regola → `chk_ordini_sconto`
Se il valore è NULL	Con un `NULL` la condizione diventa UNKNOWN (né vera né falsa) e il DBMS la considera superata: la riga entra. Es. con `data_check_out` nullable, un `INSERT` che la lascia a `NULL` passa anche se il confronto salta. Rimedio: dichiarare `NOT NULL` le colonne coinvolte.

Come riconoscerli in un esercizio: conta le colonne citate nella condizione — una sola → dominio, due o più → tupla. La distinzione serve a spiegare perché il vincolo è di un tipo o dell'altro, non a cambiare il modo di scriverlo.

Operatori e funzioni utilizzabili in un CHECK

Categoria	Esempi	Uso tipico
Confronto	`=`, `<>`, `<`, `>`, `<=`, `>=`	Confronto di numeri, date, stringhe
Logici	`AND`, `OR`, `NOT`	Combinare più condizioni
Insiemi e intervalli	`IN (...)`, `BETWEEN ... AND ...`	Elenchi di valori ammessi, fasce
Stringhe	`LIKE`, `CHAR_LENGTH()`, `UPPER()`	Validazione di formato e lunghezza
Matematiche	`ABS()`, `ROUND()`, `MOD()`	Calcoli nella condizione

⚠️ Cosa NON si può mettere in un CHECK

Sottoquery (SELECT ...) e riferimenti ad altre tabelle: un CHECK vede solo le colonne della riga corrente.
Funzioni non deterministiche: NOW(), CURDATE(), RAND(), UUID() non sono ammesse. Per regole che dipendono dal tempo o da altre righe servono trigger o logica applicativa.
Colonne diverse da quella dichiarata, se il CHECK è inline (errore 3813): in quel caso il vincolo va spostato a livello di tabella.

Note operative

Versioni: in MySQL i CHECK sono applicati dalla 8.0.16 e in MariaDB dalla 10.2.1. Nelle versioni precedenti la sintassi viene accettata ma il vincolo è ignorato: il comando non dà errore e i dati non vengono controllati.
Nome univoco e sempre presente: ogni CHECK va introdotto da CONSTRAINT nome_check; il nome deve essere univoco nello schema, con prefisso chk_ e un nome parlante (chk_ordini_sconto, non check1). Vedi il riquadro Dare sempre un nome ai vincoli.
Un vincolo per regola: se le regole sono indipendenti, meglio due CHECK separati (messaggi d'errore più chiari) invece di un'unica espressione con molti OR.
Errori: violazione → 3819 in MySQL, 4025 in MariaDB; CHECK inline che usa altre colonne → 3813 in MySQL.
Vincolo dimenticato: se non è stato scritto nella CREATE TABLE, può essere aggiunto dopo con ALTER TABLE ... ADD CONSTRAINT ... CHECK: vedi la sezione aggiunta successiva con ALTER TABLE.

🔧 Vincoli di Dominio, di Tupla e Chiavi Esterne: aggiunta successiva con ALTER TABLE

I vincoli di dominio e di tupla si dichiarano dentro la CREATE TABLE, insieme alle colonne: è lì che vanno scritti quando l'esercizio (o l'esame) chiede di definire una tabella completa di vincoli. La trattazione completa, con definizioni, sintassi ed esempi, è nella sezione Vincoli di Dominio e di Tupla qui sopra.

Questa sotto-sezione ricorda solo il caso in cui il vincolo sia stato dimenticato alla creazione della tabella: in quel caso può essere aggiunto in seguito con ALTER TABLE ... ADD CONSTRAINT ... CHECK. Il vincolo vale da quel momento per tutti gli INSERT e gli UPDATE; le righe già presenti vengono controllate al momento dell'aggiunta e, se qualcuna viola la condizione, il comando fallisce.

Sintassi

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

-- Vincolo di tupla (più colonne della stessa riga)
ALTER TABLE nome_tabella
ADD CONSTRAINT nome_constraint
CHECK (condizione_su_più_colonne);

Esempio

-- Tabella creata senza vincoli: li aggiungiamo ora
ALTER TABLE ordini
ADD CONSTRAINT chk_ordini_sconto
CHECK (sconto >= 0 AND sconto <= prezzo_totale);

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

Spiegazione: il primo è un vincolo di tupla (usa sconto e prezzo_totale della stessa riga), il secondo un vincolo di dominio (solo prezzo). Se nelle righe esistenti c'è già un ordine con sconto > prezzo_totale o un prodotto con prezzo <= 0, il comando viene rifiutato con l'errore 3819 (MySQL) o 4025 (MariaDB): prima si correggono i dati, poi si aggiunge il vincolo.

Esempio: aggiunta di una chiave esterna

Sintassi

-- Chiave esterna (integrità referenziale) aggiunta dopo la creazione
ALTER TABLE tabella_referenziante
ADD CONSTRAINT nome_fk
FOREIGN KEY (colonna_referenziante)
REFERENCES tabella_referenziata (colonna_referenziata)
ON DELETE [AZIONE]
ON UPDATE [AZIONE];

-- La tabella ordini esiste già ma senza vincolo verso i clienti
ALTER TABLE ordini
ADD CONSTRAINT fk_ordini_clienti
FOREIGN KEY (cliente_id) REFERENCES clienti (id)
ON DELETE RESTRICT
ON UPDATE CASCADE;

Spiegazione: il vincolo viene aggiunto alla tabella referenziante (ordini), che contiene la colonna cliente_id, e punta alla tabella referenziata (clienti). Le due colonne devono avere lo stesso tipo di dato (es. entrambe INT UNSIGNED). Con ON DELETE RESTRICT non si può eliminare un cliente che ha ancora ordini; con ON UPDATE CASCADE la modifica di clienti.id si propaga automaticamente a ordini.cliente_id. Se ordini contiene già righe con un cliente_id inesistente in clienti, il comando fallisce (errore 1452): prima si sistemano i dati orfani, poi si aggiunge il vincolo.

Prima la tabella referenziata, poi il vincolo

La tabella clienti deve esistere già quando si aggiunge la FK su ordini. Se manca, ALTER TABLE fallisce con l'errore 1215 (Cannot add foreign key constraint). Inoltre, se la colonna cliente_id contiene NULL e il vincolo è NOT NULL, l'aggiunta viene rifiutata: valgono le stesse regole viste per i vincoli di dominio e di tupla.

Il vincolo aggiunto si verifica con SHOW CREATE TABLE ordini; e si rimuove con ALTER TABLE ordini DROP FOREIGN KEY fk_ordini_clienti; (per i CHECK: DROP CONSTRAINT o DROP CHECK).

Controllare i dati già presenti

Prima di aggiungere il vincolo conviene cercare le righe che lo violerebbero:

SELECT *
FROM ordini
WHERE NOT (sconto >= 0 AND sconto <= prezzo_totale);

Il vincolo aggiunto compare in INFORMATION_SCHEMA.CHECK_CONSTRAINTS e si rimuove con ALTER TABLE nome_tabella DROP CONSTRAINT nome_constraint; (in alternativa DROP CHECK nome_constraint).

💡 Esempi Pratici di CREATE TABLE (commentati)

Tabella Utenti

-- Memorizza gli account utente
CREATE TABLE utenti (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    --Chiave primaria, numeri positivi, si autoincrementa 
  username VARCHAR(30) NOT NULL UNIQUE
    -- Nome utente, massimo 30 caratteri, obbligatorio e unico
  email VARCHAR(100) NOT NULL UNIQUE,
    -- Email, obbligatoria e senza duplicati
  password_hash VARCHAR(255) NOT NULL,
    -- Hash della password, lunghezza standard (es. bcrypt)
  data_registrazione DATETIME DEFAULT CURRENT_TIMESTAMP,
    -- Data di iscrizione, automatica se non fornita
  attivo BOOLEAN DEFAULT TRUE,
    -- Account attivo? 1=attivo, 0=disabilitato
  eta TINYINT UNSIGNED,
    -- Età intera positiva
  CONSTRAINT chk_utenti_eta CHECK (eta >= 18)
    -- Vincolo di dominio: maggiorenni
);

Tabella Prodotti

-- Catalogo prodotti
CREATE TABLE prodotti (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(100) NOT NULL,
    -- Nome prodotto, massimo 100 caratteri
  descrizione TEXT,
    -- Descrizione lunga, può essere vuota
  prezzo DECIMAL(10,2) NOT NULL,
    -- Prezzo con 2 decimali, obbligatorio
  quantita SMALLINT UNSIGNED DEFAULT 0,
    -- Scorte in magazzino, positivo, default 0
  categoria ENUM('elettronica','abbigliamento','casa'),
    -- Lista chiusa di categorie predefinite
  CONSTRAINT chk_prodotti_prezzo CHECK (prezzo >= 0)
    -- Vincolo di dominio: prezzo non negativo
);

Tabella Ordini

-- Ordini effettuati dagli utenti
CREATE TABLE ordini (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -- ID grande per molti ordini nel tempo
  utente_id INT UNSIGNED NOT NULL,
    -- Riferimento all'utente che ha fatto l'ordine
  data_ordine DATETIME DEFAULT CURRENT_TIMESTAMP,
    -- Data di creazione ordine
  totale DECIMAL(10,2) NOT NULL,
    -- Importo totale dell'ordine
  stato VARCHAR(20) DEFAULT 'pending',
    -- Stato (pending, shipped, delivered, cancelled)
  CONSTRAINT chk_ordini_totale CHECK (totale >= 0),
    -- Vincolo di dominio: importo non negativo
  CONSTRAINT fk_ordini_utenti
    FOREIGN KEY (utente_id) REFERENCES utenti(id) ON DELETE CASCADE
    -- Se elimino l'utente, vengono eliminati anche i suoi ordini
);

Tabella Dipendenti (esempio CHAR)

-- Dipendenti aziendali
CREATE TABLE dipendenti (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(50) NOT NULL,
  cognome VARCHAR(50) NOT NULL,
  stipendio DECIMAL(10,2) NOT NULL,
  data_assunzione DATE NOT NULL,
    -- Solo data, non serve ora
  codice_fiscale CHAR(16) NOT NULL UNIQUE,
    -- Codice fiscale italiano: sempre 16 caratteri fissi → CHAR perfetto
  CONSTRAINT chk_dipendenti_stipendio CHECK (stipendio >= 0)
    -- Vincolo di dominio: stipendio non negativo
);

⚠️ Errori comuni da evitare (per principianti)

Usare VARCHAR per campi a lunghezza fissa: ad esempio codice fiscale (16), codice postale (5) → sprechi spazio e performance. Usa CHAR.
Memorizzare i soldi in FLOAT/DOUBLE: porta a errori di arrotondamento (es. 0.1+0.2). Usa DECIMAL.
Dimenticare NOT NULL su campi obbligatori: se non metti NOT NULL, il database accetterà valori NULL, causando poi errori nelle applicazioni.
NON usare UNSIGNED per numeri che non possono essere negativi: ad esempio ID, età, quantità. Raddoppia il massimo valore positivo e evita controlli extra.
Usare TEXT su colonne che devono essere indicizzate: MySQL non può indicizzare completamente un TEXT se non con prefissi. Per ricerche veloci, se il testo è breve (< 255), usa VARCHAR.
Ignorare il set di caratteri: se hai bisogno di emoji o caratteri accentati, imposta il database/tabella su utf8mb4 (non solo utf8).
Errori con le date: usare formati sbagliati o dimenticare gli apici. Ricorda il formato 'YYYY-MM-DD'.

📌 Riepilogo: come scegliere il tipo giusto in 5 punti

ID e contatori: INT UNSIGNED AUTO_INCREMENT (o BIGINT se prevedi miliardi di righe). Per insiemi piccoli usa TINYINT UNSIGNED (es. l'elenco degli alunni di una classe, da 0 a 255) e per valori un po' più grandi ma sempre contenuti SMALLINT UNSIGNED (es. l'elenco dei dipendenti di una fabbrica con 2000 dipendenti, fino a 65.535).
Soldi e prezzi: DECIMAL(p,s) con p sufficiente (es. DECIMAL(10,2)).
Testi brevi (nome, email): VARCHAR(n) con n ragionevole (50-255).
Codici a lunghezza fissa (codice fiscale, targa): CHAR(n).
Flag vero/falso: BOOLEAN (alias di TINYINT(1)) – usa TRUE/FALSE nelle query.

Ricorda sempre di aggiungere NOT NULL quando il campo è obbligatorio e UNSIGNED per numeri positivi.

📌 Questa guida è basata su MySQL/MariaDB. Altri DBMS (PostgreSQL, SQLite, SQL Server) hanno piccole differenze, ma i concetti fondamentali sono gli stessi.

Sintassi

CREATE TABLE nome_tabella (
nome_colonna1 tipo_dato [vincoli],
nome_colonna2 tipo_dato [vincoli],
...
);

Esempio Pratico

CREATE TABLE dipendenti (
id INT PRIMARY KEY,
nome VARCHAR(50) NOT NULL,
cognome VARCHAR(50) NOT NULL,
stipendio DECIMAL(10,2),
data_assunzione DATE
);

Prima (tabella inesistente)

❌ La tabella "dipendenti" non esiste

➜

Dopo

id	nome	cognome	stipendio	data_assunzione

Tabella creata (0 righe)

Nota

I vincoli principali sono: PRIMARY KEY (chiave primaria univoca), NOT NULL (valore obbligatorio), UNIQUE (valore univoco), FOREIGN KEY (chiave esterna), DEFAULT (valore predefinito), CHECK (vincolo di dominio o di tupla, vedi la sezione precedente).

I vincoli di dominio e di tupla vanno dichiarati qui, nella CREATE TABLE: è la forma richiesta in sede d'esame.

La chiave primaria, anche quando è composta, va scritta come primo elemento della tabella, prima degli altri attributi; le chiavi esterne vanno per ultime.

Ogni vincolo di dominio, di tupla e di integrità referenziale va inoltre nominato con CONSTRAINT nomeVincolo: il nome serve per eliminarlo, per ritrovarlo in INFORMATION_SCHEMA e per leggere i messaggi di errore (vedi Dare sempre un nome ai vincoli).

DDL

FOREIGN KEY: integrità referenziale

Descrizione

L'istruzione FOREIGN KEY è il pilastro dell'integrità referenziale in un database. Una chiave esterna crea un legame tra due tabelle, quella referenziante (che contiene la chiave esterna) e quella referenziata (che contiene i valori puntati), 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 referenziante contiene la chiave esterna che punta alla chiave primaria (o unica) della tabella referenziata. È 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 destino delle righe referenzianti quando una riga referenziata viene modificata o cancellata: scegliere CASCADE, SET NULL o RESTRICT ha implicazioni drastiche sulla logica di business e sulla sicurezza dei dati.

📝 In sede d'esame
La foreign key si dichiara dentro la CREATE TABLE della tabella referenziante, insieme alle colonne: è la forma richiesta quando l'esercizio chiede di "creare le tabelle con i vincoli". Negli esempi che seguono ogni vincolo è quindi scritto nella CREATE TABLE; l'uso di ALTER TABLE ... ADD CONSTRAINT è riservato al caso in cui la tabella esista già e il vincolo sia stato dimenticato (sintassi riportata qui sotto, nella stessa sezione).

Ogni esempio mostra: la creazione delle due tabelle (referenziata e referenziante) con il vincolo, lo stato iniziale dei dati, il tentativo di UPDATE o DELETE sulla tabella referenziata e le tabelle dopo l'operazione, per vedere l'effetto della clausola scelta.

Sintassi

-- Forma richiesta: il vincolo nasce con la tabella referenziante
CREATE TABLE tabella_referenziante (
  ...,
  colonna_referenziante tipo_dato [NOT NULL],
  ...,
  CONSTRAINT nome_fk
    FOREIGN KEY (colonna_referenziante)
    REFERENCES tabella_referenziata (colonna_referenziata)
    ON DELETE [AZIONE]
    ON UPDATE [AZIONE]
);

-- Se la tabella esiste già (vincolo dimenticato)
ALTER TABLE tabella_referenziante
ADD CONSTRAINT nome_fk
FOREIGN KEY (colonna_referenziante)
REFERENCES tabella_referenziata (colonna_referenziata)
ON DELETE [AZIONE]
ON UPDATE [AZIONE];

ON DELETE e ON UPDATE sono opzionali (possono essere entrambi presenti; presenti uno dei due; assenti entrambi) e Dove [AZIONE] può essere: CASCADE, SET NULL, RESTRICT, NO ACTION.

Esempio 1:ON DELETE CASCADE (Eliminazione a cascata)

-- Tabella referenziata
CREATE TABLE clienti (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(100) NOT NULL
);

-- Tabella referenziante: il vincolo nasce con la tabella
CREATE TABLE ordini (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  cliente_id INT UNSIGNED NOT NULL,
  importo DECIMAL(10,2) NOT NULL,
  CONSTRAINT fk_ordini_clienti
    FOREIGN KEY (cliente_id) REFERENCES clienti (id)
    ON DELETE CASCADE
);

Logica: L'opzione CASCADE rende il database un "domino". Se elimini un cliente (riga referenziata), il database elimina automaticamente tutti i suoi ordini (righe referenzianti). 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 (con 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

-- Tabella referenziata
CREATE TABLE reparti (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(100) NOT NULL
);

-- Tabella referenziante: reparto_id deve accettare NULL
CREATE TABLE dipendenti (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(100) NOT NULL,
  reparto_id INT UNSIGNED NULL,
  CONSTRAINT fk_dipendenti_reparti
    FOREIGN KEY (reparto_id) REFERENCES reparti (id)
    ON DELETE SET NULL
);

Logica: Qui usiamo SET NULL. Se un reparto viene chiuso (riga referenziata eliminata), non vogliamo licenziare i dipendenti (righe referenzianti). Vogliamo solo che smettano di appartenere a quel reparto. Il campo reparto_id diventa NULL. Nota: La colonna nella tabella referenziante deve accettare NULL.

Prima della cancellazione(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

DELETE FROM reparti WHERE id = 2;

➜

Dopo la cancellazione (Con 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

-- Tabella referenziata
CREATE TABLE categorie (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(100) NOT NULL
);

-- Tabella referenziante
CREATE TABLE prodotti (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(100) NOT NULL,
  categoria_id INT UNSIGNED NOT NULL,
  CONSTRAINT fk_prodotti_categorie
    FOREIGN KEY (categoria_id) REFERENCES categorie (id)
    ON DELETE RESTRICT
);

Logica: RESTRICT (o l'equivalente predefinito: se non scriviamo nulla è come se avessimo scritto ON DELETE RESTRICT) è 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

DELETE FROM categorie WHERE id = 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: 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 nella tabella referenziata, deve cambiare ovunque sia stato usato.

-- Tabella referenziata
CREATE TABLE generi (
  codice CHAR(5) PRIMARY KEY,
  nome VARCHAR(50) NOT NULL
);

-- Tabella referenziante
CREATE TABLE libri (
  titolo VARCHAR(200) PRIMARY KEY,
  codice_genere CHAR(5) NOT NULL,
  CONSTRAINT fk_libri_generi
    FOREIGN KEY (codice_genere) REFERENCES generi (codice)
    ON UPDATE CASCADE
);

Scenario: La tabella generi usa alcuni codici brevi ('TH' per Thriller) . Decidiamo di cambiare i codice brevi in una forma più estesa (sostituiamo 'TH' con 'THRI'; 'SCIFI' lo lasciamo inalterato). Senza CASCADE, dovremmo aggiornare manualmente migliaia di libri di genere Thriller. Con CASCADE, il database propaga la modifica.

Stato Attuale

Tabella: generi (Referenziata)

codice (PK)	nome
TH	Thriller
SCIFI	Science Fiction

Tabella: libri (Referenziante)

titolo	codice_genere (FK)
Il silenzio degli innocenti	TH
Shining	TH
Mare aperto	SCIFI

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

➜

Dopo l'UPDATE

Tabella: generi

codice	nome
THRI	Thriller
SCIFI	Science Fiction

Tabella: libri

titolo	codice_genere
Il silenzio degli innocenti	THRI
Shining	THRI
Mare aperto	SCIFI

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

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

Caso d'uso: Se l'identificativo della riga referenziata cambia, non vogliamo che le righe referenzianti seguano il nuovo identificativo, ma preferiamo "sganciarle" (NULL), magari per richiedere una riassegnazione manuale.

-- Tabella referenziata
CREATE TABLE progetti (
  codice VARCHAR(20) PRIMARY KEY,
  stato VARCHAR(20) NOT NULL
);

-- Tabella referenziante: codice_progetto deve accettare NULL
CREATE TABLE dipendenti (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(100) NOT NULL,
  codice_progetto VARCHAR(20) NULL,
  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 (Referenziata)

codice	stato
PRJ_ALPHA	Attivo

Tabella: dipendenti (Referenziante)

nome	codice_progetto (FK)
Mario Rossi	PRJ_ALPHA
Luigi Verdi	PRJ_ALPHA

UPDATE progetti SET codice = 'PRJ_BETA' WHERE codice = 'PRJ_ALPHA';

➜

Dopo l'UPDATE

Tabella: progetti

codice	stato
PRJ_BETA	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 6: Aggiornamento bloccato (ON UPDATE RESTRICT)

Caso d'uso: Quando si vuole impedire la modifica della chiave primaria nella tabella referenziata se esistono righe collegate nella tabella referenziante. Questo è il comportamento predefinito in molti database e garantisce l'integrità referenziale, evitando che i riferimenti diventino orfani ovvero che righe referenzianti facciano riferimento a righe individuate tramite valori della chiave primaria non più esistenti .

-- Tabella referenziata
CREATE TABLE progetti (
  codice VARCHAR(20) PRIMARY KEY,
  stato VARCHAR(20) NOT NULL
);

-- Tabella referenziante
CREATE TABLE dipendenti (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(100) NOT NULL,
  codice_progetto VARCHAR(20) NOT NULL,
  CONSTRAINT fk_dip_progetti
    FOREIGN KEY (codice_progetto) REFERENCES progetti (codice)
    ON UPDATE RESTRICT
);

Scenario: Un progetto viene rinominato da 'PRJ_ALPHA' a 'PRJ_BETA'. Tuttavia, per policy aziendale, non si permette di cambiare il codice di un progetto se ci sono dipendenti assegnati. L'update viene quindi bloccato per evitare che i dipendenti perdano il riferimento al progetto.

Prima dell'aggiornamento

Tabella: progetti (Referenziata)

codice	stato
PRJ_ALPHA	Attivo

Tabella: dipendenti (Referenziante)

nome	codice_progetto (FK)
Mario Rossi	PRJ_ALPHA
Luigi Verdi	PRJ_ALPHA

UPDATE progetti SET codice = 'PRJ_BETA' WHERE codice = 'PRJ_ALPHA';

➜

Risultato

❌ Errore di vincolo: Impossibile aggiornare la riga nella tabella 'progetti' perché esiste almeno una riga nella tabella 'dipendenti' che vi fa riferimento.

L'aggiornamento è bloccato dal vincolo RESTRICT. Per procedere, è necessario prima eliminare o riassegnare i dipendenti collegati al progetto 'PRJ_ALPHA'.

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.

-- Tabella referenziata
CREATE TABLE categorie (
  codice VARCHAR(10) PRIMARY KEY,
  nome VARCHAR(50) NOT NULL
);

-- Tabella referenziante: due azioni sulla stessa FK
CREATE TABLE prodotti (
  prodotto VARCHAR(100) PRIMARY KEY,
  codice_categoria VARCHAR(10) NOT NULL,
  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 (Referenziata)

codice (PK)	nome
ELE	Elettronica
ABG	Abbigliamento

Tabella: prodotti (Referenziante)

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 (Referenziata)

codice (PK)	nome
ELET	Elettronica
ABG	Abbigliamento

Tabella: prodotti (Referenziante)

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)

-- Tabella referenziata: chiave primaria composta (primo elemento)
    CREATE TABLE inventario (
      PRIMARY KEY (prodotto_id, magazzino_id),
      prodotto_id INT NOT NULL,
      magazzino_id CHAR(1) NOT NULL,
      quantita INT NOT NULL
    );

    -- Tabella referenziante: la FK deve usare entrambe le colonne
    CREATE TABLE movimenti_magazzino (
      id_mov INT PRIMARY KEY,
      prodotto_id INT NOT NULL,
      magazzino_id CHAR(1) NOT NULL,
      tipo ENUM('Entrata','Uscita') NOT NULL,
      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 referenziata (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 Referenziata: inventario

prodotto_id	magazzino_id	quantità
101	A	50
101	B	20

Chiave primaria composta: (prodotto_id, magazzino_id)

➜

Tabella Referenziante: movimenti_magazzino

id_mov	prodotto_id	magazzino_id	tipo
900	101	A	Entrata
901	101	B	Uscita

Foreign key composta: (prodotto_id, magazzino_id) → inventario(prodotto_id, magazzino_id)

Cosa succede se si tenta di inserire un movimento non valido?

INSERT INTO movimenti_magazzino (id_mov, prodotto_id, magazzino_id, tipo) 
VALUES (902, 101, 'C', 'Uscita');

❌ Errore di vincolo: La coppia (101, 'C') non esiste nella tabella inventario!

Il database rifiuta l'inserimento perché la foreign key composta non trova una corrispondenza nella tabella referenziata.

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

-- Tabella 1: Persone (dati anagrafici)
CREATE TABLE persone (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(50) NOT NULL,
    cognome VARCHAR(50) NOT NULL
);

-- Tabella 2: Passaporti (documenti)
CREATE TABLE passaporti (
    numero VARCHAR(20) PRIMARY KEY,
    persona_id INT UNIQUE,  -- Vincolo UNIQUE: 1 passaporto per persona
    data_scadenza DATE,
    CONSTRAINT fk_passaporti_persone
      FOREIGN KEY (persona_id) 
      REFERENCES persone(id) 
      ON DELETE CASCADE
);

Logica: Ogni persona può avere al massimo un passaporto, e ogni passaporto appartiene a una sola persona. Per garantire questo, la colonna persona_id nella tabella passaporti deve avere un vincolo UNIQUE. Se non ci fosse, potremmo inserire due passaporti per la stessa persona. L'opzione ON DELETE CASCADE fa sì che quando una persona viene eliminata, anche il suo passaporto venga rimosso automaticamente.

Tabella Referenziata: persone

id	nome	cognome
1	Mario	Rossi
2	Luigi	Verdi
3	Anna	Neri

Stato iniziale: tre persone registrate. Anna Neri (id=3) non ha ancora un passaporto.

⬇️ (Relazione UNICA)

Tabella Referenziante: passaporti

numero	persona_id (FK, UNIQUE)	data_scadenza
AA1234567	1	2028-12-31
BB7654321	2	2027-06-15

Ogni passaporto è collegato a una sola persona.

✅ Mario Rossi (id=1) ha il passaporto AA1234567. Luigi Verdi (id=2) ha il passaporto BB7654321. Anna Neri (id=3) è registrata ma non ha ancora un passaporto. Nessuna persona ha più di un passaporto grazie al vincolo UNIQUE.

Cosa succede se si tenta di riusare un numero di passaporto già esistente?

-- Anna Neri (id=3) presenta il passaporto di Mario Rossi
INSERT INTO passaporti (numero, persona_id, data_scadenza) 
VALUES ('AA1234567', 3, '2030-01-01');

❌ Errore: Duplicate entry 'AA1234567' for key 'PRIMARY' — il numero di passaporto AA1234567 è già registrato per Mario Rossi (id=1).

Il database rifiuta la riga e la tabella passaporti resta con due righe: un passaporto può appartenere a una sola persona. Se il numero fosse nuovo, invece, l'inserimento riuscirebbe; il caso simmetrico (un secondo passaporto per la stessa persona) è bloccato dal vincolo UNIQUE su persona_id.

Comportamento con ON DELETE CASCADE

DELETE FROM persone WHERE id = 1;

✅ Mario Rossi viene eliminato dalla tabella persone. Grazie a ON DELETE CASCADE, anche il suo passaporto (AA1234567) viene rimosso automaticamente dalla tabella passaporti.

Tabella persone (dopo DELETE)

id	nome	cognome
2	Luigi	Verdi
3	Anna	Neri

Rimangono Luigi Verdi (id=2) e Anna Neri (id=3).

⬇️

Tabella passaporti (dopo DELETE con ON DELETE CASCADE)

numero	persona_id	data_scadenza
BB7654321	2	2027-06-15

Il passaporto AA1234567 (di Mario) è stato rimosso automaticamente.

Esempio 10: Foreign Key Auto-referenziale (Gerarchie)

-- Un'unica tabella: ogni riga referenzia un'altra riga della stessa tabella
    CREATE TABLE dipendenti (
      id INT PRIMARY KEY AUTO_INCREMENT,
      nome VARCHAR(100) NOT NULL,
      manager_id INT NULL,
      CONSTRAINT fk_dipendenti_manager
        FOREIGN KEY (manager_id) REFERENCES dipendenti (id)
        ON DELETE SET NULL
    );

Logica: Una tabella può referenziare se stessa: è utile in caso di gerarchie (es. un dipendente che è responsabile di altri dipendenti). Ogni riga è quindi sia referenziante sia referenziata, e il suo manager_id punta all'id di un'altra riga nella stessa tabella.

Tabella prima cancellazione riga Sviluppatore Senior

id (PK)	nome dipendente	manager_id(FK)	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)

DELETE FROM dipendenti WHERE id = 21;

Tabella dopo cancellazione riga Sviluppatore Senior con ON DELETE SET NULL

id (PK)	nome dipendente	manager_id(FK)	Note
10	CEO Azienda	NULL	Nessun manager (è il capo)
20	Resp. IT	10	Il suo manager è il CEO (id 10)
22	Stagista	NULL	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).

Opzioni ON DELETE e ON UPDATE a confronto

Azione	Comportamento	Quando usarlo
`CASCADE`	PROPAGA ELIMINAZIONE/MODIFICA ALLE RIGHE REFERENZIANTI: Con l'eliminazione (l'aggiornamento) di una riga referenziata si ha l'eliminazione (l'aggiornamento) delle righe referenzianti collegate	Relazioni forte dipendenza (Ordine effettuato /Righe Ordine effettuato).
`SET NULL`	IMPOSTA NULL NELLE RIGHE REFERENZIANTI: Con l'eliminazione (l'aggiornamento) di una riga referenziata si ha l'impostazione a NULL nelle righe referenzianti collegate	Dati storici o opzionali (Dipendenti/Reparto).
`RESTRICT`	BLOCCA L'OPERAZIONE SULLA RIGA REFERENZIATA: Se cancello/aggiorno la PK di una riga referenziata che è collegata a righe referenzianti, la cancellazione/aggiornamento della PK è bloccata	Sicurezza massima (Categorie/Prodotti).
`NO ACTION`	SIMILE A RESTRICT (verifica in differita).	Compatibilità SQL standard, logiche complesse.

Convenzioni per il nome dei constraint

Una foreign key va sempre dichiarata con CONSTRAINT nome_vincolo: il nome serve per eliminarla (DROP FOREIGN KEY), per ritrovarla in INFORMATION_SCHEMA e per leggere i messaggi di errore di MariaDB, che riportano il nome del vincolo violato. Un buon nome facilita anche debugging e manutenzione:

Sintassi consigliata: fk_[tabella_referenziante]_[tabella_referenziata]_[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 causato da tipi di dati diversi (es. colonna referenziata INT, colonna referenziante 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 referenziante. Prima di creare il vincolo, pulisci i dati:

-- Trova i dati sporchi che violerebbero la nuova FK

SELECT referenziante.*

FROM tabella_referenziante referenziante

LEFT JOIN tabella_referenziata referenziata ON referenziante.colonna_fk = referenziata.id

WHERE referenziata.id IS NULL AND referenziante.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;

DDL

ALTER TABLE ADD

Descrizione

Aggiunge una nuova colonna a una tabella esistente.

Sintassi

ALTER TABLE nome_tabella
ADD nome_colonna tipo_dato [vincoli];

Esempio Pratico

ALTER TABLE dipendenti
ADD email VARCHAR(100);

Prima

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

➜

Dopo

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

Nota

La nuova colonna viene aggiunta con valore NULL per tutte le righe esistenti, a meno che non si specifichi un valore DEFAULT.

Vincoli dimenticati alla creazione

Se un vincolo di dominio o di tupla è stato dimenticato nella CREATE TABLE, può essere aggiunto in seguito con ALTER TABLE ... ADD CONSTRAINT ... CHECK (es. ADD CONSTRAINT chk_prezzo CHECK (prezzo > 0)). La trattazione completa dei vincoli resta comunque nella sezione della CREATE TABLE; la sola sintassi per aggiungerli dopo è descritta in questa sezione.

DDL

ALTER TABLE MODIFY

Descrizione

Modifica il tipo di dato o i vincoli di una colonna esistente.

Sintassi

-- MySQL / Oracle
ALTER TABLE nome_tabella
MODIFY nome_colonna nuovo_tipo_dato;

Esempio Pratico

ALTER TABLE dipendenti
MODIFY nome VARCHAR(100);

Struttura Prima

nome VARCHAR(50)

➜

Struttura Dopo

nome
VARCHAR(100)

Attenzione

Modificare il tipo di una colonna può causare perdita di dati se il nuovo tipo è incompatibile o più piccolo del precedente.

DDL

ALTER TABLE DROP COLUMN

Descrizione

Rimuove una colonna esistente dalla tabella, eliminando tutti i dati contenuti in essa.

Sintassi

ALTER TABLE nome_tabella
DROP COLUMN nome_colonna;

Esempio Pratico

ALTER TABLE dipendenti
DROP COLUMN email;

Prima

id	nome	cognome	email
1	Mario	Rossi	mario@email.it
2	Laura	Bianchi	laura@email.it

➜

Dopo

id	nome	cognome
1	Mario	Rossi
2	Laura	Bianchi

Attenzione

L'operazione è irreversibile! Tutti i dati della colonna verranno persi definitivamente.

DDL

ALTER TABLE COMMENT

Descrizione

Aggiunge metadati descrittivi alla tabella o alle colonne. In MySQL/MariaDB, i commenti sono molto utili per documentare il database (es. spiegare unità di misura o codici).

Sintassi

-- Commento sull'intera tabella
ALTER TABLE nome_tabella COMMENT = 'Testo del commento';
-- Commento su una specifica colonna (richiede ridefinizione tipo)
ALTER TABLE nome_tabella
MODIFY nome_colonna tipo_dato COMMENT 'Testo del commento';

Esempio 1: Commento alla Tabella

ALTER TABLE dipendenti
COMMENT = 'Registro storico del personale attivo';

Esempio 2: Commento alla Colonna

ALTER TABLE dipendenti
MODIFY stipendio DECIMAL(10,2) COMMENT 'Importo mensile lordo in Euro';

Metadati Prima le due istruzioni ALTER

dipendenti

stipendio DECIMAL

➜

Metadati Dopo le due istruzioni ALTER

dipendenti

💬 "Registro storico..."

stipendio

DECIMAL
💬 "Importo mensile..."

Visualizzare i commenti

Per vedere i commenti inseriti puoi usare il comando: SHOW FULL COLUMNS FROM dipendenti; oppure SHOW CREATE TABLE dipendenti;

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

DROP TABLE

Descrizione

Elimina completamente una tabella dal database, inclusa la sua struttura e tutti i dati.

Sintassi

DROP TABLE nome_tabella;

Esempio Pratico

DROP TABLE dipendenti;

Prima

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

➜

Dopo

❌ La tabella "dipendenti" non esiste più

Attenzione

Operazione estremamente distruttiva e irreversibile! La tabella e tutti i suoi dati vengono eliminati permanentemente.

DDL

TRUNCATE TABLE

Descrizione

Svuota completamente una tabella eliminando tutte le righe, ma mantenendo la struttura intatta.

Sintassi

TRUNCATE TABLE nome_tabella;

Esempio Pratico

TRUNCATE TABLE dipendenti;

Prima

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

➜

Dopo

id	nome	cognome	stipendio

Tabella vuota (0 righe)

Differenza con DELETE

TRUNCATE è più veloce di DELETE perché non registra ogni singola eliminazione nel log. Inoltre, resetta i contatori AUTO_INCREMENT. Non può essere usato con clausole WHERE.

DDL

Dal testo al modello fisico: un caso completo

Perché questo esempio chiude il DDL

Le istruzioni DDL viste finora non si studiano isolate: sono l'ultima tappa di un percorso che parte da un testo in italiano e arriva alle tabelle reali. L'ordine è sempre lo stesso: testo iniziale → modello E-R → modello logico → modello fisico. Le prime due tappe e la traduzione al modello logico sono spiegate in modelloErLogico.html; il modello fisico è ciò che il DDL costruisce. Il caso seguente percorre tutte e quattro le tappe sullo stesso enunciato, così ogni tabella creata ha una giustificazione visibile e nessuna istruzione resta fine a sé stessa.

Tappa 1Testo iniziale (requisiti)

Una scuola di musica vuole gestire istruttori, corsi e iscrizioni. Di ogni istruttore si conoscono codice fiscale, nome, cognome e data di nascita. Ogni corso ha un codice, un nome (per esempio «Chitarra classica»), un livello (Base, Intermedio, Avanzato) e un numero massimo di partecipanti; è tenuto da un solo istruttore, mentre un istruttore può tenere più corsi. Gli allievi (codice fiscale, nome, cognome, telefono) si iscrivono ai corsi: un allievo può iscriversi a più corsi e un corso può avere molti allievi. Di ogni iscrizione si registrano la data e il voto finale, che manca finché il corso non è terminato.

Le informazioni da conservare sono quattro gruppi (istruttori, corsi, allievi, iscrizioni) e due legami (chi tiene un corso, chi si iscrive a un corso). Il testo dice anche quante volte un'entità può comparire nel legame: è da lì che nascono le cardinalità.

Tappa 2Modello E-R

Convenzioni di modelloErLogico.html: rettangolo = entità, rombo = associazione, pallino pieno = identificatore, pallino vuoto = attributo, coppia (min,max) = cardinalità, e la coppia accanto a un'entità parla dell'altra. TIENE: accanto a CORSO c'è (1,1) — ogni corso ha un solo istruttore; accanto a ISTRUTTORE c'è (0,n) — un istruttore può tenere più corsi. ISCRIZIONE: (0,n) accanto a entrambe, e l'associazione porta i propri attributi.

Le scelte che rendono il modello E-R corretto rispetto al testo:

Identificatori: CF per istruttore e allievo, CodCorso per il corso. Ogni entità ha un identificatore proprio, quindi nessuna entità è debole.
TIENE è 1:N: il testo dice «un solo istruttore per corso, più corsi per istruttore». La coppia accanto a un'entità parla dell'altra: accanto a ISTRUTTORE c'è (0,n) (un istruttore tiene più corsi), accanto a CORSO c'è (1,1) (un corso ha un solo istruttore). Il massimo 1 sta accanto a CORSO, ed è in CORSO che andrà la chiave esterna.
ISCRIZIONE è N:M con attributi: data e voto non appartengono né all'allievo né al corso, ma alla coppia allievo-corso. Per questo sono attributi dell'associazione e non delle entità.
MaxPartecipanti resta attributo di CORSO: è un limite dichiarato dal corso, non un dato dell'iscrizione. Che il limite venga poi rispettato è una regola applicativa (la controlla la pagina PHP, oppure un trigger), non un vincolo esprimibile con una chiave esterna.

Tappa 3Modello logico

La traduzione segue due regole già viste in modelloErLogico.html: ogni entità diventa una tabella; ogni associazione 1:N diventa una chiave esterna nella tabella che ha il massimo 1 accanto a sé, mentre un'associazione N:M diventa una tabella autonoma. Le entità sono tre (ISTRUTTORE, CORSO, ALLIEVO); l'associazione N:M ISCRIZIONE diventa una tabella, quindi le tabelle sono quattro.

Schema relazionale

ISTRUTTORE (CF(PK), Nome, Cognome, DataNascita)
CORSO      (CodCorso(PK), Nome, Livello, MaxPartecipanti, CFIstruttore(FK))
ALLIEVO    (CF(PK), Nome, Cognome, Telefono)
ISCRIZIONE (PK(CFAllievo, CodCorso), DataIscrizione, VotoFinale,
            CFAllievo(FK), CodCorso(FK))

Notazione: PK(...) e FK(...); con la chiave composta i nomi stanno dentro la parentesi. L'ordine è quello del manuale: prima la PK, poi gli attributi propri, per ultime le FK — per questo in ISCRIZIONE CFAllievo e CodCorso compaiono due volte, dentro PK(...) e in coda come FK.

Da TIENE (1:N) nasce la colonna CFIstruttore in CORSO: la FK va nell'entità che ha il massimo 1 accanto a sé — qui CORSO — e punta a ISTRUTTORE.CF. Se la mettessimo in ISTRUTTORE avremmo bisogno di più righe per lo stesso istruttore, cioè di un errore di progetto.
Da ISCRIZIONE (N:M) nasce una tabella con PK composta PK(CFAllievo, CodCorso): le due colonne sono anche FK, scritte in coda. La coppia impedisce di iscrivere due volte lo stesso allievo allo stesso corso, mentre gli attributi dell'associazione restano qui.
Il voto può mancare: nel modello logico VotoFinale ammette NULL, a differenza di DataIscrizione che è obbligatoria. Questa distinzione si trasferisce direttamente nel DDL.

Tappa 4Modello fisico (DDL)

Il modello fisico è la trascrizione dello schema logico in istruzioni DDL per uno specifico DBMS: tipi di dato concreti, vincoli di chiave e regole di integrità referenziale. L'ordine conta: prima le tabelle che non hanno FK, poi quelle che le referenziano.

-- Tappa 4: modello fisico (MySQL / MariaDB)
CREATE DATABASE scuola_musica;
USE scuola_musica;

CREATE TABLE istruttore (
  CF          CHAR(16)    PRIMARY KEY,
  Nome        VARCHAR(50) NOT NULL,
  Cognome     VARCHAR(50) NOT NULL,
  DataNascita DATE        NOT NULL
);

CREATE TABLE allievo (
  CF       CHAR(16)    PRIMARY KEY,
  Nome     VARCHAR(50) NOT NULL,
  Cognome  VARCHAR(50) NOT NULL,
  Telefono VARCHAR(20)
);

CREATE TABLE corso (
  CodCorso        INT      PRIMARY KEY AUTO_INCREMENT,
  Nome            VARCHAR(60) NOT NULL,
  Livello         ENUM('Base', 'Intermedio', 'Avanzato') NOT NULL DEFAULT 'Base',
  MaxPartecipanti SMALLINT NOT NULL,
  CFIstruttore    CHAR(16) NOT NULL,
  CONSTRAINT chk_corso_maxpartecipanti CHECK (MaxPartecipanti > 0),
  CONSTRAINT fk_corso_istruttore FOREIGN KEY (CFIstruttore)
    REFERENCES istruttore(CF)
    ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE iscrizione (
  -- chiave primaria composta: primo elemento della tabella
  PRIMARY KEY (CFAllievo, CodCorso),
  CFAllievo      CHAR(16)     NOT NULL,
  CodCorso       INT          NOT NULL,
  DataIscrizione DATE         NOT NULL,
  VotoFinale     DECIMAL(4,2) NULL,
  CONSTRAINT chk_iscrizione_voto CHECK (VotoFinale BETWEEN 0 AND 10),
  CONSTRAINT fk_iscrizione_allievo FOREIGN KEY (CFAllievo)
    REFERENCES allievo(CF)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_iscrizione_corso FOREIGN KEY (CodCorso)
    REFERENCES corso(CodCorso)
    ON UPDATE CASCADE ON DELETE CASCADE
);

Dal modello logico al DDL, riga per riga

Modello logico	Nel modello fisico
Quattro tabelle	Quattro CREATE TABLE, dopo CREATE DATABASE e `USE`
`CF`, `CodCorso`, coppia `(CFAllievo, CodCorso)`	`PRIMARY KEY` di colonna per le prime due, `PRIMARY KEY` composta per ISCRIZIONE
`CFIstruttore(FK)` in CORSO	`FOREIGN KEY ... REFERENCES istruttore(CF)` con `ON DELETE RESTRICT`: non si cancella un istruttore che ha ancora corsi
FK verso allievo e corso in ISCRIZIONE	Due `FOREIGN KEY` con `ON DELETE CASCADE`: cancellando un allievo o un corso spariscono le iscrizioni collegate
`VotoFinale` opzionale; livello tra tre valori; massimo > 0	`NULL` ammesso, `ENUM('Base','Intermedio','Avanzato')`, CHECK su `VotoFinale` e `MaxPartecipanti`

Perché le FK si scrivono dopo le tabelle che referenziano

Se esegui le istruzioni in ordine, istruttore e allievo esistono già quando corso e iscrizione le citano.

Con questo schema il database esiste ma è vuoto: le tabelle sono «scatole» con la loro struttura, senza righe. Riempirle è compito del DML (INSERT, UPDATE, DELETE), che è la sezione successiva. Da qui in poi è anche lo stesso schema che la pagina web in PHP userà per leggere e scrivere i dati: se il modello fisico è corretto, la pagina non deve «aggiustare» nulla nel database.

📋 Tabella riassuntiva dei comandi DDL

Un comando per riga, con un esempio essenziale e la spiegazione di ciò che l'esempio produce sul database. Gli esempi usano lo schema dipendenti / ordini / clienti delle sezioni precedenti.

Comando	Esempio	Spiegazione dell'esempio
`CREATE DATABASE`	`CREATE DATABASE IF NOT EXISTS azienda;`	Crea il database `azienda` solo se non esiste già, così lo script di setup può essere rieseguito senza errori.
`CREATE TABLE`	`CREATE TABLE dipendenti ( id INT PRIMARY KEY, nome VARCHAR(50) NOT NULL, stipendio DECIMAL(10,2) );`	Crea la tabella con tre colonne: `id` chiave primaria (univoca e non NULL), `nome` obbligatorio, `stipendio` decimale a due cifre.
`CONSTRAINT ... CHECK`	`CREATE TABLE prodotti ( prezzo DECIMAL(10,2) NOT NULL, sconto DECIMAL(10,2) NOT NULL DEFAULT 0, CONSTRAINT chk_prodotti_sconto CHECK (sconto >= 0 AND sconto <= prezzo) );`	Vincolo di tupla: rifiuta ogni riga in cui lo sconto esce dall'intervallo `0 … prezzo`. Va scritto a livello di tabella e con un nome, altrimenti il DBMS ne genera uno non riutilizzabile in un `DROP CHECK`.
`FOREIGN KEY`	`CONSTRAINT fk_ordini_cliente FOREIGN KEY (cliente_id) REFERENCES clienti(id) ON DELETE CASCADE ON UPDATE CASCADE`	Integrità referenziale: un ordine può puntare solo a un cliente esistente; eliminando o modificando quel cliente, la stessa azione si propaga agli ordini.
`ALTER TABLE ... ADD`	`ALTER TABLE dipendenti ADD email VARCHAR(100);`	Aggiunge la colonna `email` a tabella già popolata: le righe esistenti ricevono `NULL`, nessun dato viene perso.
`ALTER TABLE ... MODIFY`	`ALTER TABLE dipendenti MODIFY nome VARCHAR(100);`	Cambia tipo e lunghezza della colonna `nome` conservando i valori già memorizzati.
`ALTER TABLE ... DROP COLUMN`	`ALTER TABLE dipendenti DROP COLUMN email;`	Rimuove la colonna `email` insieme a tutti i valori che conteneva: è irreversibile, va verificato prima cosa si perde.
`ALTER TABLE ... COMMENT`	`ALTER TABLE dipendenti COMMENT = 'Registro storico del personale attivo';`	Salva un commento nei metadati della tabella (compare in `SHOW CREATE TABLE`): documentazione che resta dentro il database.
`ALTER TABLE ... ADD CONSTRAINT`	`ALTER TABLE ordini ADD CONSTRAINT fk_ordini_cliente FOREIGN KEY (cliente_id) REFERENCES clienti(id);`	Aggiunge una chiave esterna a una tabella che esiste già, senza ricrearla: utile quando il vincolo è stato dimenticato.
`ALTER TABLE ... RENAME COLUMN`	`ALTER TABLE dipendenti RENAME COLUMN data_assunzione TO assunzione;`	Rinomina la colonna: i dati e gli eventuali indici restano, cambia solo il nome usato nelle query.
`CREATE INDEX`	`CREATE INDEX idx_cognome ON dipendenti (cognome);`	Crea un indice su `cognome`: le ricerche e gli ordinamenti su quella colonna non richiedono più la scansione completa della tabella.
`DROP TABLE`	`DROP TABLE dipendenti;`	Elimina la tabella con struttura e dati. In MySQL/MariaDB è un comando DDL, quindi esegue un commit implicito: non è annullabile con `ROLLBACK`.
`TRUNCATE TABLE`	`TRUNCATE TABLE dipendenti;`	Svuota la tabella conservandone la struttura: più rapido di `DELETE` senza `WHERE` e di norma azzera il contatore `AUTO_INCREMENT`.

🔧 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 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_cognome 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 riga.

Prima (solo vincolo NOT NULL)

id	nome	email
1	Mario	mario@email.com
2	Laura	laura@email.com
3	Anonimo	laura@email.com
Problema: Duplicato non rilevato!

➜

Dopo (con indice univoco)

id	nome	email
1	Mario	mario@email.com
2	Laura	laura@email.com
3	Tentativo inserimento	laura@email.com
❌ ERRORE: Duplicate entry 'laura@email.com' 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 abbina le parole presenti nel testo e ne calcola un grado di rilevanza (ranking): non è un motore semantico, quindi non riconosce sinonimi né il significato delle parole, ma permette di trovare articoli per parola singola, per frase o con operatori booleani, ordinandoli per rilevanza. 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, modalità booleana (+ - *) e ordinamento per 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, per verificare che la query

SELECT * FROM dipendenti WHERE cognome = 'Rossi';

usiamo un indice, scriviamo 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 MySQL legge tutte le righe (type: ALL, key: NULL):

id  select_type  table       type  possible_keys  key      key_len  ref  rows  filtered  Extra
1   SIMPLE       dipendenti  ALL   NULL            NULL     NULL     NULL  5     100.00

Se invece esiste l'indice idx_cognome, l'output mostra l'uso dell'indice (type: ref, key: idx_cognome):

id  select_type  table       type  possible_keys  key         key_len  ref  rows  filtered  Extra
1   SIMPLE       dipendenti  ref   idx_cognome     idx_cognome  42       NULL  2     100.00  Using index condition

In entrambi i casi la colonna key dice quale indice viene usato: con NULL non viene sfruttato alcun indice, quindi MySQL fa una scansione completa della tabella.

📝 DML - Data Manipulation Language

DML

INSERT INTO

Descrizione

Inserisce una nuova riga (riga) in una tabella esistente.

Sintassi

-- Specificando le colonne
INSERT INTO nome_tabella (colonna1, colonna2, ...)
VALUES (valore1, valore2, ...);
-- Senza specificare le colonne (tutte in ordine)
INSERT INTO nome_tabella
VALUES (valore1, valore2, ...);

Esempio Pratico

Scrivi un'istruzione che inserisce nella tabella dipendenti un nuovo dipendente con id 3, nome Anna, cognome Neri, stipendio 2700.00 e data di assunzione 2024-01-15. La tabella passa così da 2 a 3 righe.

Soluzione:

INSERT INTO dipendenti (id, nome, cognome, stipendio, data_assunzione)
VALUES (3, 'Anna', 'Neri', 2700.00, '2024-01-15');

Prima

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

➜

Dopo

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

DML

INSERT INTO – Più Righe

Esempio: Inserimento di Più Righe

Scrivi un'istruzione che inserisce nella tabella dipendenti tre nuovi dipendenti in una sola volta: (id 3, Anna, Neri, 2700.00, 2024-01-15), (id 4, Luca, Ferrari, 2600.00, 2024-03-01) e (id 5, Sofia, Ricci, 2900.00, 2024-02-10). La tabella passa così da 2 a 5 righe.

Soluzione:

INSERT INTO dipendenti (id, nome, cognome, stipendio, data_assunzione)
VALUES
  (3, 'Anna', 'Neri', 2700.00, '2024-01-15'),
  (4, 'Luca', 'Ferrari', 2600.00, '2024-03-01'),
  (5, 'Sofia', 'Ricci', 2900.00, '2024-02-10');

Prima

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

➜

Dopo

id	nome	cognome	stipendio	data_assunzione
1	Mario	Rossi	3000.00	2023-03-01
2	Laura	Bianchi	2800.00	2023-06-15
3	Anna	Neri	2700.00	2024-01-15
4	Luca	Ferrari	2600.00	2024-03-01
5	Sofia	Ricci	2900.00	2024-02-10

Nota

Con un unico comando INSERT INTO ... VALUES puoi aggiungere molteplici righe separandole con la virgola. È più efficiente di tanti comandi singoli ed è supportato da tutti i principali DBMS (MySQL, PostgreSQL, SQLite, SQL Server).

DML

UPDATE

Descrizione

Modifica i dati esistenti in una o più righe di una tabella.

Sintassi

UPDATE nome_tabella
SET colonna1 = valore1, colonna2 = valore2
WHERE condizione;

Esempio Pratico

Scrivi un'istruzione che aggiorna lo stipendio del dipendente con id 1 (Mario Rossi), portandolo da 2500.00 a 3000.00. Le righe con id 2 e 3 devono restare invariate.

Soluzione:

UPDATE dipendenti
SET stipendio = 3000.00
WHERE id = 1;

Prima

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

➜

Dopo

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

Attenzione

Senza la clausola WHERE, l'UPDATE modifica TUTTE le righe della tabella! Verifica sempre la condizione prima di eseguire.

DML

UPDATE – Più Campi

Esempio: Aggiornamento di Più Campi

Scrivi un'istruzione che aggiorna la riga con id 1 (Mario Rossi) su due colonne: imposta lo stipendio a 3200.00 e l'email, prima NULL, a mario.rossi@azienda.it.

Soluzione:

UPDATE dipendenti
SET
  stipendio = 3200.00,
  email = 'mario.rossi@azienda.it'
WHERE id = 1;

Prima

id	nome	cognome	stipendio	email
1	Mario	Rossi	3000.00	NULL
2	Laura	Bianchi	2800.00	laura@email.it

➜

Dopo

id	nome	cognome	stipendio	email
1	Mario	Rossi	3200.00	mario.rossi@azienda.it
2	Laura	Bianchi	2800.00	laura@email.it

Sintassi Multi-colonna

Nella clausola SET puoi elencare più assegnazioni separate da virgola. Tutti i campi specificati vengono aggiornati contemporaneamente — ma devono appartenere alla stessa tabella. Un singolo comando UPDATE non può modificare colonne di tabelle diverse.

DML

DELETE

Descrizione

Elimina una o più righe da una tabella in base a una condizione specifica.

Sintassi

DELETE FROM nome_tabella
WHERE condizione;

Esempio Pratico

Scrivi un'istruzione che elimina dalla tabella dipendenti il dipendente con id 3 (Anna Neri). Le righe con id 1 e 2 devono restare.

Soluzione:

DELETE FROM dipendenti
WHERE id = 3;

Prima

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)

➜

Dopo

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

Attenzione

Senza la clausola WHERE, DELETE elimina TUTTE le righe della tabella! Questa operazione è irreversibile senza un backup o transazione attiva.

DELETE vs TRUNCATE

DELETE: elimina righe una per una, può usare WHERE, più lento, può essere annullato in una transazione.
TRUNCATE: elimina tutte le righe in blocco, più veloce, resetta AUTO_INCREMENT, non può usare WHERE.

📋 Tabella riassuntiva dei comandi DML

Ogni comando che modifica il contenuto delle tabelle, con un esempio e l'effetto che produce. Il DML lavora sulle righe: la struttura non cambia.

Comando	Esempio	Spiegazione dell'esempio
`INSERT INTO`	`INSERT INTO dipendenti (id, nome, cognome, stipendio, data_assunzione) VALUES (3, 'Anna', 'Neri', 2700.00, '2024-01-15');`	Inserisce una nuova riga: i valori sono nell'ordine delle colonne elencate, così non serve ricordare l'ordine fisico della tabella.
`INSERT INTO ... più VALUES`	`INSERT INTO dipendenti (id, nome, cognome, stipendio, data_assunzione) VALUES (4, 'Luca', 'Ferrari', 2600.00, '2024-03-01'), (5, 'Sofia', 'Ricci', 2900.00, '2024-02-10');`	Inserisce due righe con un'unica istruzione: un solo viaggio verso il server invece di due `INSERT` separati.
`UPDATE`	`UPDATE dipendenti SET stipendio = 3000.00 WHERE id = 1;`	Modifica lo stipendio della sola riga con `id = 1`; senza `WHERE` lo stesso `SET` verrebbe applicato a tutte le righe.
`UPDATE ... più campi`	`UPDATE dipendenti SET stipendio = 3200.00, email = 'mario.rossi@azienda.it' WHERE id = 1;`	Aggiorna due colonne della stessa riga in un unico comando: le assegnazioni sono separate da virgola, non da `AND`.
`DELETE`	`DELETE FROM dipendenti WHERE id = 3;`	Elimina le righe che soddisfano la condizione; senza `WHERE` elimina tutte le righe una per una (più lento di `TRUNCATE` e reversibile con `ROLLBACK`).

📝 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

Scrivi un'istruzione che copia nella tabella ordini_2024 tutti gli ordini del 2024 presenti nella tabella ordini, impostando lo stato a 'COMPLETATO', senza modificare la tabella di origine.

Soluzione:

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

UPDATE con JOIN

Descrizione

Aggiorna i dati di una tabella utilizzando valori provenienti da altre tabelle tramite operazioni di JOIN. In MariaDB/MySQL, questa funzionalità si realizza con una sintassi specifica che non utilizza la clausola FROM dopo UPDATE. Permette di sincronizzare dati tra tabelle correlate in modo efficiente.

Sintassi corretta per MariaDB/MySQL

UPDATE tabella1 [AS] alias1
JOIN tabella2 [AS] alias2 ON condizione_join
SET tabella1.colonna = espressione
[WHERE condizione];

Nota: La clausola FROM dopo UPDATE non è supportata in MariaDB/MySQL. Quella sintassi è tipica di SQL Server o PostgreSQL.

Esempio Pratico: Applicare sconto ai clienti PREMIUM

Scrivi un'istruzione che riduce del 10% il totale degli ordini in stato 'IN_ATTESA' appartenenti a clienti di tipo 'PREMIUM', lasciando invariati tutti gli altri ordini.

Soluzione:

UPDATE ordini o
JOIN clienti c ON o.cliente_id = c.id
SET o.totale = o.totale * 0.9  -- applica sconto del 10%
WHERE c.tipo = 'PREMIUM'
AND o.stato = 'IN_ATTESA';

Stato iniziale delle tabelle

Tabella: clienti

id	nome	tipo
50	Mario Rossi	PREMIUM
51	Luigi Verdi	STANDARD
52	Anna Bianchi	PREMIUM

Tabella: ordini

ordine_id	cliente_id	totale	stato
2001	50	100.00	IN_ATTESA
2002	51	250.00	IN_ATTESA
2003	52	180.00	IN_ATTESA

Nota: la tabella ordini contiene solo il cliente_id (chiave esterna), non il tipo del cliente. Il tipo si recupera tramite JOIN con la tabella clienti.

⬇️ Esegui UPDATE con JOIN

Tabella: ordini (dopo l'UPDATE)

ordine_id	cliente_id	totale	stato
2001	50	90.00	IN_ATTESA
2002	51	250.00	IN_ATTESA
2003	52	162.00	IN_ATTESA

✅ 2 righe aggiornate (sconto del 10% applicato ai clienti PREMIUM)

Analisi del Codice

UPDATE ordini o JOIN clienti c ON o.cliente_id = c.id: La clausola JOIN collega la tabella ordini (alias o) con la tabella clienti (alias c) in base alla corrispondenza tra o.cliente_id e c.id. Questo join crea un set di righe temporaneo che include solo gli ordini per i quali esiste un cliente corrispondente.

SET o.totale = o.totale * 0.9: Per ogni riga del join, il totale dell'ordine viene moltiplicato per 0.9 (applicando uno sconto del 10%).

WHERE c.tipo = 'PREMIUM' AND o.stato = 'IN_ATTESA': Il filtro seleziona solo gli ordini il cui cliente è di tipo PREMIUM e lo stato dell'ordine è IN_ATTESA. Solo queste righe vengono aggiornate.

Risultato: Gli ordini 2001 (cliente 50, PREMIUM) e 2003 (cliente 52, PREMIUM) vengono aggiornati a 90.00 e 162.00. L'ordine 2002 (cliente 51, STANDARD) rimane invariato a 250.00.

Attenzione alle differenze tra DBMS

La sintassi mostrata è specifica per MariaDB e MySQL. Altri database utilizzano strutture diverse:

SQL Server / PostgreSQL: supportano UPDATE ... FROM ... JOIN ... (con FROM dopo UPDATE).
SQLite: non supporta UPDATE con JOIN diretto; richiede una subquery.

Se devi scrivere codice portabile, usa sempre la sintassi del tuo database target o astrai la logica in una view/stored procedure.

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

Scrivi un'istruzione che imposta il campo prezzo_vendita di ogni prodotto alla media dei prezzi dei concorrenti della stessa categoria, aggiornando solo i prodotti che hanno almeno un concorrente in prodotti_concorrenti.

Soluzione:

UPDATE prodotti

SET prezzo_vendita = (

SELECT AVG(prezzo)  

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	100.00
A002	Prodotto B	Abbigliamento	50.00	50.00
A003	Prodotto C	Elettronica	200.00	100.00

3 righe aggiornate

Analisi del Codice

La query setta il prezzo di un prodotto che ha almeno un concorrente uguale alla media dei prezzi dei prodotti concorrenti SET prezzo_vendita = (SELECT AVG(prezzo) FROM prodotti_concorrenti WHERE prodotti_concorrenti.categoria = prodotti.categoria): La subquery correlata calcola il prezzo medio dei concorrenti nella stessa categoria. 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 (se non ci fossero prodotti concorrenti, senza questa istruzione otterrei la media dei prezzi di prodotti concorrenti che non esistono, ovvero il valore NULL).

Risultato: I prodotti A001 e A003 (Elettronica) ottengono prezzo_vendita = €100, media dei prezzi dei prodotti di elettronica, mentre il prodotto A002 (Abbigliamento) ottiene €50 media dei prezzi dei prodotti di abbigliamento .

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 riga obsoleti o cancellare elementi associati a determinate condizioni in tabelle collegate.

Sintassi

DELETE tabella1

FROM tabella1

INNER JOIN tabella2 ON tabella1.chiave = tabella2.chiave

WHERE condizione;

Esempio Pratico

Scrivi un'istruzione che elimina gli articoli delle categorie 'Obsoleta' e 'Discontinua' che non hanno mai venduto (quantita_venduta = 0), lasciando in tabella quelli con almeno una vendita.

Soluzione:

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
B001	Articolo B1	Elettronica	0
B002	Articolo B2	Elettronica	150
B003	Articolo B3	Mobili	0
B004	Articolo B4	Elettronica	75
Righe che soddisfano la condizione DELETE (categoria Obsoleta/Discontinua E quantita_venduta = 0)

⬇️

Dopo

codice	nome	categoria	quantita_venduta
B002	Articolo B2	Elettronica	150
B004	Articolo B4	Elettronica	75

2 righe eliminate

Analisi del Codice

Cancella gli articoli che appartiene alla categoria 'Obsoleta' o 'Discontinua': 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 sintassi mostrata (il nome della tabella da eliminare compare sia in DELETE sia in FROM) è quella di MySQL/MariaDB. Gli altri DBMS usano forme diverse: PostgreSQL scrive DELETE FROM tabella1 USING tabella2 WHERE …, SQL Server DELETE tabella1 FROM tabella1 JOIN …. In ogni DBMS conviene eseguire prima una SELECT per verificare le righe che verranno eliminate.

🔐 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. Attenzione alla versione: dentro GRANT è la sintassi di MySQL 5.7 (e resta valida in MariaDB), mentre in MySQL 8.0 è deprecata: lì l'utente si crea con CREATE USER 'gestore'@'localhost' IDENTIFIED BY 'password123' e i permessi si assegnano con un GRANT successivo. I ruoli di cui parlano le sezioni successive esistono solo da MySQL 8.0 e MariaDB 10.2.

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 riga (INSERT) e modificare riga 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 riga 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: la revoca non è a cascata

Un aspetto critico da considerare quando si utilizza WITH GRANT OPTION è il comportamento del sistema quando si revoca un privilegio. In MySQL e MariaDB la revoca non è a cascata: se 'responsabile' ha a sua volta concesso un privilegio a 'luigi' e poi revocate quel privilegio a 'responsabile', 'luigi' mantiene il privilegio che aveva ricevuto. Per non lasciare permessi attivi è quindi necessario revocarli esplicitamente a ciascun utente, oppure usare SHOW GRANTS per sapere chi li possiede davvero. Vale anche il caso inverso: revocando a 'responsabile' il solo permesso di delegare (GRANT OPTION), gli utenti ai quali aveva già concesso i permessi non vengono toccati. Documentare sempre 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 riga 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.

📋 Tabella riassuntiva dei comandi DCL

Chi può fare cosa: un esempio per ogni comando di concessione, revoca e verifica dei permessi, più la gestione tramite ruoli.

Comando	Esempio	Spiegazione dell'esempio
`GRANT`	`GRANT SELECT, INSERT, UPDATE ON azienda.ordini TO 'gestore'@'localhost';`	Concede i tre privilegi sull'oggetto `azienda.ordini` all'utente `gestore`: ciò che non è concesso resta negato (default deny).
`GRANT ... WITH GRANT OPTION`	`GRANT SELECT, INSERT ON azienda.ordini TO 'responsabile'@'localhost' WITH GRANT OPTION;`	Oltre ai permessi, concede la facoltà di delegarli ad altri utenti: il delegato diventa un «mini-amministratore» per quegli oggetti.
`GRANT su .`	`GRANT SELECT, SHOW VIEW ON . TO 'analista'@'%';`	Privilegi globali, validi su tutti i database e le tabelle del server: qui solo lettura, il profilo tipico di chi fa reportistica.
`REVOKE`	`REVOKE DELETE, DROP ON azienda.ordini FROM 'gestore'@'localhost';`	Rimuove i due privilegi indicati e lascia intatti gli altri: l'utente continua a lavorare senza poter cancellare dati o tabelle.
`REVOKE ALL PRIVILEGES`	`REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'vecchio_impiegato'@'localhost';`	Azzera ogni privilegio e la possibilità di delegarne: l'account esiste ancora ma non può più operare sui dati.
`REVOKE GRANT OPTION FOR`	`REVOKE GRANT OPTION FOR DELETE ON azienda.ordini FROM 'supervisore'@'localhost';`	Toglie solo la delega del permesso `DELETE`: il supervisore può ancora cancellare di persona, ma non concedere quel permesso ad altri.
`SHOW GRANTS`	`SHOW GRANTS FOR 'gestore'@'localhost'; SHOW GRANTS;`	Mostra i permessi in forma di comandi `GRANT` rieseguibili; da solo, senza `FOR`, elenca quelli dell'utente connesso.
`CREATE ROLE`	`CREATE ROLE 'contabile';`	Crea un ruolo vuoto: un contenitore di permessi che potrà essere assegnato a più utenti contemporaneamente.
`GRANT` a ruolo / ruolo a utente	`GRANT SELECT, INSERT ON azienda.fatture TO 'contabile'; GRANT 'contabile' TO 'mario'@'localhost';`	Prima si riempie il ruolo di permessi, poi si assegna il ruolo all'utente: Mario eredita tutti i permessi di `contabile` in un solo passaggio.
`SET ROLE`	`SET ROLE 'contabile', 'lettore_report';`	Attiva i ruoli elencati nella sessione corrente: utile a chi possiede più ruoli e non li vuole tutti attivi insieme.
`DROP ROLE`	`DROP ROLE 'contabile';`	Elimina il ruolo: i permessi che conteneva spariscono da tutti gli utenti a cui era stato assegnato.
`SELECT CURRENT_ROLES()`	`SELECT CURRENT_ROLES();`	Elenca i ruoli attivi nella sessione: serve a capire quali permessi si stanno effettivamente usando.

🔄 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 in blocco: o tutte le operazioni hanno successo, oppure nessuna di esse viene applicata al database.

Per la trattazione completa delle transazioni e delle quattro proprietà ACID, con simulazioni interattive e anomalie di concorrenza, vedi la dispensa sulle transazioni.

Le transazioni garantiscono le proprietà ACID:

Atomicità: tutte le operazioni di una transazione 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;

Nota

Il blocco IF … THEN … ELSE … END IF è sintassi di stored program: MySQL non lo accetta digitandolo al prompt, va inserito dentro una procedura, una funzione o un trigger (si veda l'esempio successivo). Al prompt, la stessa logica si scrive con una query condizionale o con lo EXIT HANDLER di una procedura.

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 riga

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.

📋 Tabella riassuntiva dei comandi TCL

Come si raggruppano più istruzioni in un'unità atomica: apertura, conferma, annullamento (anche parziale) e livello di isolamento.

Comando	Esempio	Spiegazione dell'esempio
`START TRANSACTION / BEGIN`	`START TRANSACTION; -- oppure: BEGIN;`	Apre una transazione esplicita: da questo punto le modifiche sono invisibili agli altri utenti e annullabili con `ROLLBACK`.
`COMMIT`	`START TRANSACTION; UPDATE camere SET disponibile = 0 WHERE id = 101; INSERT INTO prenotazioni (cliente, camera) VALUES ('Mario Rossi', 101); COMMIT;`	Rende definitive tutte le operazioni della transazione: camera e prenotazione diventano permanenti e visibili agli altri utenti.
`ROLLBACK`	`START TRANSACTION; UPDATE conti SET saldo = saldo - 5000 WHERE numero_conto = 'IT001'; ROLLBACK;`	Annulla tutte le operazioni non ancora confermate: il saldo torna esattamente al valore di partenza, come se il prelievo non fosse mai avvenuto.
`SAVEPOINT`	`START TRANSACTION; INSERT INTO ordini (cliente_id, stato) VALUES (1, 'nuovo'); SAVEPOINT ordine_creato; INSERT INTO ordini_dettagli (ordine_id, prodotto_id) VALUES (1, 101); ROLLBACK TO ordine_creato; COMMIT;`	Segna un punto intermedio: `ROLLBACK TO` annulla solo ciò che segue il savepoint, quindi il dettaglio sparisce ma l'ordine resta e viene confermato.
`RELEASE SAVEPOINT`	`SAVEPOINT sp1; UPDATE inventario SET quantita = quantita - 10 WHERE id = 1; RELEASE SAVEPOINT sp1;`	Elimina il savepoint (non l'effetto delle operazioni già eseguite) e libera il nome: da qui non si può più fare `ROLLBACK TO sp1`.
`SET TRANSACTION`	`SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION; SELECT SUM(saldo) FROM conti WHERE tipo = 'deposito'; COMMIT;`	Imposta il livello di isolamento della prossima transazione: `SERIALIZABLE` evita letture incoerenti tra due query, al prezzo di meno concorrenza.

🔍 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.

📋 Tabella riassuntiva dei comandi QL

Le clausole che compongono una SELECT, con un esempio e l'effetto sul risultato. Gli esempi usano le tabelle dipendenti e reparti; per gli approfondimenti vedi i documenti dedicati alla Query Language.

Comando	Esempio	Spiegazione dell'esempio
`SELECT ... FROM`	`SELECT nome, cognome FROM dipendenti;`	Proiezione: sceglie quali colonne restituire per tutte le righe della tabella.
`WHERE`	`SELECT * FROM dipendenti WHERE stipendio > 2500;`	Filtra le righe prima di restituirle: restano solo i dipendenti sopra i 2500 euro.
`DISTINCT`	`SELECT DISTINCT reparto FROM dipendenti;`	Elimina i duplicati dal risultato: ogni reparto compare una volta sola.
`ORDER BY`	`SELECT nome, stipendio FROM dipendenti ORDER BY stipendio DESC;`	Ordina il risultato in senso decrescente (`ASC` è il comportamento predefinito).
`LIMIT`	`SELECT nome FROM dipendenti ORDER BY stipendio DESC LIMIT 5;`	Restituisce solo le prime cinque righe del risultato: qui i cinque stipendi più alti.
`GROUP BY`	`SELECT reparto, AVG(stipendio) AS media FROM dipendenti GROUP BY reparto;`	Raggruppa le righe per reparto e calcola un aggregato per ogni gruppo: una riga di output per reparto.
`HAVING`	`SELECT reparto, COUNT() AS n FROM dipendenti GROUP BY reparto HAVING COUNT() > 3;`	Filtra i gruppi dopo l'aggregazione: restano solo i reparti con più di tre dipendenti (`WHERE` non potrebbe usare un aggregato).
`JOIN`	`SELECT d.nome, r.nome AS reparto FROM dipendenti d JOIN reparti r ON d.reparto_id = r.id;`	Combina le righe di due tabelle sulla condizione `ON`: a ogni dipendente viene associato il nome del suo reparto.
`Subquery`	`SELECT nome FROM dipendenti WHERE stipendio > ( SELECT AVG(stipendio) FROM dipendenti );`	Usa il risultato di un'altra query come termine di confronto: restituisce chi guadagna più della media.

🎯 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 DATABASE

Descrizione

Sintassi

Esempi Pratici

Nota Operativa

Best Practice

CREATE TABLE

Descrizione

📊 Tabella Riassuntiva dei Tipi di Dati SQL (MySQL/MariaDB)

🔢 Numeri Interi (senza virgola) e decimali

💰 Numeri con la Virgola (decimali)

📅 Date e Orari – Formati e utilizzo

✅ Booleani e Altri Tipi Speciali

🔒 Vincoli (Constraints) – Regole per proteggere i dati

🏷️ Dare sempre un nome ai vincoli: CONSTRAINT nomeVincolo

🧱 Vincoli di Dominio e di Tupla (CHECK)

Come si scrive un CHECK con nome

Sintassi

Inline o di tabella: i quattro casi, con esempio

Esempio 1 — Vincoli di dominio

Esempio 2 — Dominio, NULL e validazione di formato

Esempio 3 — Vincolo di tupla su due colonne

Esempio 4 — Vincolo di tupla con AND

Esempio 5 — Vincolo di tupla con regole condizionali (AND / OR)

Cosa succede quando il vincolo viene violato

Dominio vs tupla: riepilogo

Operatori e funzioni utilizzabili in un CHECK

Note operative

🔧 Vincoli di Dominio, di Tupla e Chiavi Esterne: aggiunta successiva con ALTER TABLE

Sintassi

Esempio

Esempio: aggiunta di una chiave esterna

Sintassi

Prima la tabella referenziata, poi il vincolo

Controllare i dati già presenti

💡 Esempi Pratici di CREATE TABLE (commentati)

⚠️ Errori comuni da evitare (per principianti)

📌 Riepilogo: come scegliere il tipo giusto in 5 punti

Sintassi

Esempio Pratico

Nota

FOREIGN KEY: integrità referenziale

Descrizione

Sintassi

Esempio 1:ON DELETE CASCADE (Eliminazione a cascata)

Esempio 2: ON DELETE SET NULL

Esempio 3: ON DELETE RESTRICT

Esempio 4: Aggiornamento a cascata (ON UPDATE CASCADE)

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

Esempio 6: Aggiornamento bloccato (ON UPDATE RESTRICT)

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

Esempio 8: Chiave Esterna Composta (Composite Key)

Cosa succede se si tenta di inserire un movimento non valido?

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

Cosa succede se si tenta di riusare un numero di passaporto già esistente?

Comportamento con ON DELETE CASCADE

Esempio 10: Foreign Key Auto-referenziale (Gerarchie)

Opzioni ON DELETE e ON UPDATE a confronto

Convenzioni per il nome dei constraint

Gestione degli errori comuni

Verifica e rimozione dei constraint

ALTER TABLE ADD

Descrizione

Sintassi

Esempio Pratico

Nota

Vincoli dimenticati alla creazione

ALTER TABLE MODIFY

Descrizione

Sintassi

Esempio Pratico

Attenzione

🏷️ Dare sempre un nome ai vincoli: `CONSTRAINT nomeVincolo`