Impara a manipolare i dati con INSERT, UPDATE, DELETE, REPLACE, TRUNCATE e LOAD DATA. Query avanzate, JOIN e ottimizzazione delle performance.
In questa lezione finale approfondiremo le operazioni di modifica dei dati in MySQL e le query avanzate per interrogare database complessi.
Dopo aver imparato a creare database e tabelle nella Lezione 3, ora è il momento di imparare come inserire, modificare ed eliminare i dati, oltre a eseguire query più sofisticate con JOIN, filtri avanzati e aggregazioni.
La sintassi completa di una query SELECT include molte clausole opzionali che permettono di filtrare, ordinare e aggregare i dati:
SELECT colonna1, colonna2, ...
FROM nome_tabella
WHERE condizione
GROUP BY colonna
HAVING condizione_gruppo
ORDER BY colonna ASC|DESC
LIMIT numero;
La clausola WHERE permette di filtrare i risultati in base a condizioni specifiche:
-- Filtro semplice
SELECT * FROM studenti
WHERE eta > 18;
-- Filtri multipli con AND/OR
SELECT * FROM studenti
WHERE eta > 18 AND corso = 'Informatica';
-- Filtro con LIKE (pattern matching)
SELECT * FROM studenti
WHERE nome LIKE 'Mar%'; -- Nomi che iniziano con "Mar"
-- Filtro con IN (lista di valori)
SELECT * FROM studenti
WHERE citta IN ('Roma', 'Milano', 'Firenze');
-- Filtro con BETWEEN (intervallo)
SELECT * FROM studenti
WHERE eta BETWEEN 20 AND 25;
ORDER BY ordina i risultati in modo crescente (ASC) o decrescente (DESC):
-- Ordinamento crescente
SELECT * FROM studenti
ORDER BY cognome ASC;
-- Ordinamento decrescente
SELECT * FROM studenti
ORDER BY eta DESC;
-- Ordinamento multiplo
SELECT * FROM studenti
ORDER BY corso ASC, eta DESC;
LIMIT restringe il numero di risultati restituiti:
-- Primi 10 risultati
SELECT * FROM studenti
LIMIT 10;
-- Risultati dalla posizione 20, ne restituisce 10
SELECT * FROM studenti
LIMIT 20, 10;
Le funzioni di aggregazione calcolano valori su insiemi di righe:
-- Contare il numero di studenti
SELECT COUNT(*) FROM studenti;
-- Età media
SELECT AVG(eta) AS eta_media FROM studenti;
-- Età minima e massima
SELECT MIN(eta) AS eta_minima, MAX(eta) AS eta_massima
FROM studenti;
-- Somma totale
SELECT SUM(crediti) AS crediti_totali FROM esami;
GROUP BY raggruppa i risultati per valori comuni:
-- Contare studenti per corso
SELECT corso, COUNT(*) AS numero_studenti
FROM studenti
GROUP BY corso;
-- Età media per corso
SELECT corso, AVG(eta) AS eta_media
FROM studenti
GROUP BY corso;
-- Filtrare gruppi con HAVING
SELECT corso, COUNT(*) AS numero_studenti
FROM studenti
GROUP BY corso
HAVING COUNT(*) > 10; -- Solo corsi con più di 10 studenti
WHERE filtra le righe prima del raggruppamento, HAVING filtra i gruppi dopo il raggruppamento. HAVING si usa sempre con GROUP BY e può contenere funzioni di aggregazione.
I JOIN permettono di combinare dati provenienti da più tabelle in base a una relazione comune.
Restituisce solo le righe che hanno corrispondenza in entrambe le tabelle:
-- JOIN tra studenti ed esami
SELECT studenti.nome, studenti.cognome, esami.materia, esami.voto
FROM studenti
INNER JOIN esami ON studenti.id = esami.studente_id;
-- Con alias per semplificare
SELECT s.nome, s.cognome, e.materia, e.voto
FROM studenti AS s
INNER JOIN esami AS e ON s.id = e.studente_id;
Restituisce tutte le righe della tabella di sinistra, anche se non hanno corrispondenza nella tabella di destra:
-- Tutti gli studenti, anche quelli senza esami
SELECT s.nome, s.cognome, e.materia, e.voto
FROM studenti AS s
LEFT JOIN esami AS e ON s.id = e.studente_id;
Restituisce tutte le righe della tabella di destra, anche se non hanno corrispondenza nella tabella di sinistra:
-- Tutti gli esami, anche quelli senza studente associato
SELECT s.nome, s.cognome, e.materia, e.voto
FROM studenti AS s
RIGHT JOIN esami AS e ON s.id = e.studente_id;
È possibile eseguire JOIN su più di due tabelle:
-- JOIN tra studenti, esami e corsi
SELECT s.nome, s.cognome, e.materia, e.voto, c.nome_corso
FROM studenti AS s
INNER JOIN esami AS e ON s.id = e.studente_id
INNER JOIN corsi AS c ON e.corso_id = c.id;
Il comando INSERT aggiunge nuove righe a una tabella:
-- Inserimento di una singola riga
INSERT INTO studenti (nome, cognome, eta, corso)
VALUES ('Mario', 'Rossi', 22, 'Informatica');
-- Inserimento senza specificare le colonne (devi fornire tutti i valori)
INSERT INTO studenti
VALUES (1, 'Mario', 'Rossi', 22, 'Informatica');
È possibile inserire più righe contemporaneamente:
-- Inserire più studenti con una sola query
INSERT INTO studenti (nome, cognome, eta, corso)
VALUES
('Mario', 'Rossi', 22, 'Informatica'),
('Laura', 'Bianchi', 21, 'Matematica'),
('Giuseppe', 'Verdi', 23, 'Fisica');
Puoi inserire dati provenienti da un'altra tabella:
-- Copiare studenti da una tabella temporanea
INSERT INTO studenti (nome, cognome, eta, corso)
SELECT nome, cognome, eta, corso
FROM studenti_temp
WHERE anno = 2025;
REPLACE funziona come INSERT, ma se esiste già una riga con la stessa chiave primaria, la elimina e inserisce la nuova:
-- Se esiste uno studente con id=1, lo sostituisce
REPLACE INTO studenti (id, nome, cognome, eta, corso)
VALUES (1, 'Mario', 'Rossi', 23, 'Informatica');
REPLACE elimina completamente la riga esistente e ne inserisce una nuova. Questo può causare la perdita di dati se non specifichi tutte le colonne. Preferisci usare INSERT ... ON DUPLICATE KEY UPDATE per aggiornamenti più sicuri.
Il comando UPDATE modifica i dati esistenti in una tabella:
-- Aggiornare una singola colonna
UPDATE studenti
SET eta = 23
WHERE id = 1;
-- Aggiornare più colonne contemporaneamente
UPDATE studenti
SET eta = 23, corso = 'Ingegneria Informatica'
WHERE id = 1;
Puoi usare condizioni complesse per aggiornare più righe:
-- Aggiornare tutti gli studenti di un corso
UPDATE studenti
SET anno = 2
WHERE corso = 'Informatica' AND eta > 20;
-- Aumentare l'età di tutti gli studenti di 1 anno
UPDATE studenti
SET eta = eta + 1;
È possibile aggiornare dati usando informazioni da altre tabelle:
-- Aggiornare il campo "voto_medio" nella tabella studenti
UPDATE studenti AS s
INNER JOIN (
SELECT studente_id, AVG(voto) AS media
FROM esami
GROUP BY studente_id
) AS e ON s.id = e.studente_id
SET s.voto_medio = e.media;
Se ometti la clausola WHERE in un UPDATE, tutte le righe della tabella verranno aggiornate! Questo può portare a perdite di dati irreversibili. Verifica sempre le condizioni prima di eseguire un UPDATE.
Il comando DELETE rimuove righe da una tabella:
-- Eliminare una singola riga
DELETE FROM studenti
WHERE id = 1;
-- Eliminare più righe con condizioni
DELETE FROM studenti
WHERE eta < 18;
-- Eliminare tutti gli studenti di un corso
DELETE FROM studenti
WHERE corso = 'Fisica';
Puoi eliminare righe usando condizioni provenienti da altre tabelle:
-- Eliminare studenti senza esami
DELETE s
FROM studenti AS s
LEFT JOIN esami AS e ON s.id = e.studente_id
WHERE e.studente_id IS NULL;
Un DELETE senza WHERE eliminerà TUTTE le righe della tabella! Questa operazione è irreversibile se non hai un backup. Usa sempre la clausola WHERE e verifica la query con una SELECT prima di eseguire il DELETE.
TRUNCATE svuota completamente una tabella, ma è più veloce di DELETE perché non registra ogni singola riga eliminata:
-- Svuotare completamente la tabella studenti
TRUNCATE TABLE studenti;
-- Equivalente a DELETE FROM studenti, ma più veloce
LOAD DATA permette di importare grandi quantità di dati da un file (CSV, TXT, ecc.):
-- Importare dati da un file CSV
LOAD DATA INFILE '/percorso/studenti.csv'
INTO TABLE studenti
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- Salta la prima riga (intestazioni)
Esempio di file CSV da importare:
nome,cognome,eta,corso
Mario,Rossi,22,Informatica
Laura,Bianchi,21,Matematica
Giuseppe,Verdi,23,Fisica
Gli indici migliorano notevolmente le performance delle query, specialmente su tabelle grandi:
-- Creare un indice su una singola colonna
CREATE INDEX idx_cognome ON studenti(cognome);
-- Indice su più colonne
CREATE INDEX idx_corso_eta ON studenti(corso, eta);
-- Indice unico (valori univoci)
CREATE UNIQUE INDEX idx_email ON studenti(email);
-- Eliminare un indice
DROP INDEX idx_cognome ON studenti;
MySQL non genera grafici direttamente, ma fornisce i dati che possono essere visualizzati tramite strumenti esterni come Excel, Google Sheets, Power BI, Tableau, o librerie di programmazione.
Per creare grafici efficaci, è essenziale strutturare i dati in modo appropriato con query aggregate:
-- Dati per grafico a barre: Vendite per categoria
SELECT categoria, SUM(quantita) AS totale_vendite
FROM prodotti
GROUP BY categoria
ORDER BY totale_vendite DESC;
-- Dati per grafico a linea: Trend vendite mensili
SELECT
YEAR(data_ordine) AS anno,
MONTH(data_ordine) AS mese,
SUM(totale) AS vendite_mensili
FROM ordini
GROUP BY anno, mese
ORDER BY anno, mese;
-- Dati per grafico a torta: Distribuzione clienti per città
SELECT citta, COUNT(*) AS numero_clienti
FROM clienti
GROUP BY citta;
Utilizzo: Confrontare valori tra diverse categorie.
Query tipo: SELECT categoria, COUNT(*) o SUM() con GROUP BY
-- Esempio: Numero di ordini per stato
SELECT stato_ordine, COUNT(*) AS numero_ordini
FROM ordini
GROUP BY stato_ordine
ORDER BY numero_ordini DESC;
Utilizzo: Visualizzare trend nel tempo (serie temporali).
Query tipo: SELECT con funzioni data (DATE, YEAR, MONTH) e GROUP BY temporale
-- Esempio: Trend registrazioni utenti per giorno
SELECT
DATE(data_registrazione) AS giorno,
COUNT(*) AS nuovi_utenti
FROM utenti
WHERE data_registrazione >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY giorno
ORDER BY giorno;
Utilizzo: Mostrare proporzioni di un intero (percentuali).
Query tipo: SELECT con COUNT(*) o SUM() diviso per il totale
-- Esempio: Percentuale di prodotti per categoria
SELECT
categoria,
COUNT(*) AS quantita,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM prodotti), 2) AS percentuale
FROM prodotti
GROUP BY categoria;
Esistono diversi metodi per esportare i risultati delle query e crearci sopra dei grafici:
Puoi esportare i risultati in formato CSV usando phpMyAdmin o MySQL Workbench:
-- Esportare risultati in CSV (da riga di comando)
SELECT * FROM vendite_mensili
INTO OUTFILE '/tmp/vendite.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Usa librerie di visualizzazione come Chart.js, Plotly, o matplotlib per creare grafici dinamici:
# Python con matplotlib esempio
import mysql.connector
import matplotlib.pyplot as plt
# Connessione al database
conn = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mio_database"
)
cursor = conn.cursor()
cursor.execute("SELECT categoria, SUM(quantita) FROM prodotti GROUP BY categoria")
# Preparare dati per il grafico
categorie = []
quantita = []
for row in cursor:
categorie.append(row[0])
quantita.append(row[1])
# Creare grafico a barre
plt.bar(categorie, quantita)
plt.xlabel('Categoria')
plt.ylabel('Quantità')
plt.title('Vendite per Categoria')
plt.show()
Collega MySQL a strumenti professionali come:
-- KPI: Tasso di crescita mensile
SELECT
DATE_FORMAT(data_ordine, '%Y-%m') AS mese,
COUNT(*) AS ordini,
SUM(totale) AS fatturato,
AVG(totale) AS ticket_medio
FROM ordini
WHERE data_ordine >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY mese
ORDER BY mese;
-- Top 10 prodotti più venduti (per grafico a barre orizzontale)
SELECT
p.nome_prodotto,
SUM(o.quantita) AS unita_vendute,
SUM(o.quantita * p.prezzo) AS ricavo_totale
FROM ordini_dettagli o
JOIN prodotti p ON o.prodotto_id = p.id
GROUP BY p.nome_prodotto
ORDER BY unita_vendute DESC
LIMIT 10;
-- Distribuzione geografica clienti (per mappa heat map)
SELECT
regione,
citta,
COUNT(*) AS numero_clienti,
SUM(totale_acquisti) AS valore_clienti
FROM clienti
GROUP BY regione, citta
ORDER BY numero_clienti DESC;
Ecco un set di query per creare una dashboard completa di analisi vendite:
-- 1. Panoramica KPI principali
SELECT
COUNT(DISTINCT id) AS totale_ordini,
COUNT(DISTINCT cliente_id) AS clienti_attivi,
SUM(totale) AS fatturato_totale,
AVG(totale) AS valore_medio_ordine
FROM ordini
WHERE YEAR(data_ordine) = YEAR(CURDATE());
-- 2. Andamento vendite settimanali (grafico a linea)
SELECT
WEEK(data_ordine) AS settimana,
COUNT(*) AS ordini,
SUM(totale) AS fatturato
FROM ordini
WHERE data_ordine >= DATE_SUB(CURDATE(), INTERVAL 12 WEEK)
GROUP BY settimana
ORDER BY settimana;
-- 3. Categoria prodotti più venduta (grafico a torta)
SELECT
categoria,
COUNT(*) AS prodotti_venduti,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM ordini_dettagli), 1) AS percentuale
FROM ordini_dettagli od
JOIN prodotti p ON od.prodotto_id = p.id
GROUP BY categoria
ORDER BY prodotti_venduti DESC;
-- 4. Confronto performance anno su anno
SELECT
YEAR(data_ordine) AS anno,
MONTH(data_ordine) AS mese,
SUM(totale) AS fatturato
FROM ordini
WHERE YEAR(data_ordine) IN (YEAR(CURDATE()), YEAR(CURDATE()) - 1)
GROUP BY anno, mese
ORDER BY anno, mese;
Query complesse con JOIN multipli e funzioni di aggregazione possono essere lente su tabelle grandi. Considera di creare VIEW o tabelle aggregate per dashboard che vengono consultate frequentemente. Usa anche EXPLAIN per analizzare le query lente.
Scenario: Hai una tabella "prodotti" con colonne: id, nome, categoria, prezzo, quantita.
Richieste:
Scenario: Hai due tabelle: "clienti" (id, nome, email) e "ordini" (id, cliente_id, data, totale).
Richieste:
Scenario: Tabella "dipendenti" con colonne: id, nome, cognome, reparto, stipendio.
Richieste:
Scenario: Database di una scuola con tabelle: "studenti", "corsi", "iscrizioni" (studente_id, corso_id, voto).
Richieste:
Scenario: Database e-commerce con tabelle: "ordini" (id, data, totale, stato), "prodotti" (id, nome, categoria, prezzo).
Richieste:
Suggerimento: Usa DATE_FORMAT(), GROUP BY, aggregazioni (SUM, COUNT, AVG) e ordina i risultati in modo appropriato per il tipo di grafico.
Prima di scrivere query complesse, prova a suddividerle in passaggi più semplici. Usa SELECT per verificare i dati prima di eseguire UPDATE o DELETE. Testa sempre le tue query su un database di sviluppo prima di applicarle in produzione!
Hai completato tutte le 4 lezioni del corso di Introduzione alla Programmazione! Ora è il momento di mettere alla prova le tue conoscenze con il test finale.
Il test include domande su algoritmi, programmazione, MySQL e digital soft skills. Riceverai i risultati immediatamente e potrai scaricare un report completo.