Lezione 4: Query Avanzate e Modifica Dati

Introduzione

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.

Query SELECT Avanzate

Struttura Base della SELECT

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;

Filtri con WHERE

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;

Ordinamento con ORDER BY

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;

Limitare i Risultati con LIMIT

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;

Funzioni di Aggregazione

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;

Raggruppamento con GROUP BY

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

JOIN: Unire Dati da Più Tabelle

I JOIN permettono di combinare dati provenienti da più tabelle in base a una relazione comune.

INNER JOIN

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;

LEFT JOIN

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;

RIGHT JOIN

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;

JOIN Multipli

È 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;

INSERT: Inserire Nuovi Dati

Sintassi Base

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');

Inserimento Multiplo

È 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');

INSERT con SELECT

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

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');

UPDATE: Modificare Dati Esistenti

Sintassi Base

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;

UPDATE con Condizioni Complesse

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;

UPDATE con JOIN

È 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;

DELETE: Eliminare Dati

Sintassi Base

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';

DELETE con JOIN

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;

Altri Comandi Utili

TRUNCATE

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

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

Creare Indici per Ottimizzare le Query

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;

Grafici e Visualizzazione Dati con MySQL

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.

Preparare Dati per i Grafici

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;

Tipi di Grafici e Query Corrispondenti

-- Esempio: Numero di ordini per stato
SELECT stato_ordine, COUNT(*) AS numero_ordini
FROM ordini
GROUP BY stato_ordine
ORDER BY numero_ordini DESC;

-- 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;

-- 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;

Esportare Dati per Grafici

Esistono diversi metodi per esportare i risultati delle query e crearci sopra dei grafici:

-- 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';

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

Query Utili per Dashboard Aziendali

-- 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;

Esempio Completo: Dashboard Vendite

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;

Esercizi Pratici