Iniziare con SQL in 5 passaggi

Iniziare con SQL in 5 passaggi' can be condensed to 'Iniziare con SQL in 5 passi'.

Introduzione al Linguaggio di Query Strutturato

Quando si tratta di gestire e manipolare i dati nei database relazionali, il Linguaggio di Query Strutturato (SQL) è il nome più importante nel settore. SQL è un importante linguaggio specifico di dominio che serve come fondamento per la gestione dei database e fornisce un modo standardizzato per interagire con i database. Con i dati che sono la forza trainante dietro le decisioni e l’innovazione, SQL rimane una tecnologia essenziale che richiede attenzione di alto livello da parte degli analisti dei dati, degli sviluppatori e dei data scientist.

SQL è stato originariamente sviluppato da IBM negli anni ’70 e standardizzato da ANSI e ISO alla fine degli anni ’80. Tutti i tipi di organizzazioni – dalle piccole imprese alle università alle grandi aziende – si affidano a database SQL come MySQL, SQL Server e PostgreSQL per gestire dati su larga scala. L’importanza di SQL continua a crescere con l’espansione delle industrie basate sui dati. La sua applicazione universale lo rende una competenza vitale per vari professionisti, nel campo dei dati e non solo.

SQL consente agli utenti di eseguire varie attività relative ai dati, tra cui:

Interrogare i dati
Inserire nuovi record
Aggiornare i record esistenti
Eliminare i record
Creare e modificare tabelle

Questo tutorial offrirà una panoramica passo-passo di SQL, concentrandosi sull’inizio con ampi esempi pratici.

Passaggio 1: Preparazione dell’Ambiente SQL

Scelta di un Sistema di Gestione di Database SQL (DBMS)

Prima di immergersi nelle query SQL, sarà necessario scegliere un sistema di gestione di database (DBMS) che si adatti alle esigenze del progetto. Il DBMS serve come base per le attività SQL, offrendo diverse funzionalità, ottimizzazioni delle prestazioni e modelli di prezzo. La scelta del DBMS può avere un impatto significativo su come si interagisce con i dati.

MySQL: Open source, ampiamente adottato, utilizzato da Facebook e Google. Adatto a una varietà di applicazioni, dai piccoli progetti alle applicazioni aziendali di livello enterprise.
PostgreSQL: Open source, funzionalità robuste, utilizzato da Apple. Conosciuto per le sue prestazioni e la conformità agli standard.
SQL Server Express: Opzione di livello base di Microsoft. Ideale per applicazioni VoAGI di piccole dimensioni con requisiti limitati di scalabilità.
SQLite: Leggero, senza server e autosufficiente. Ideale per app mobili e piccoli progetti.

Guida all’installazione di MySQL

Per il bene di questo tutorial, ci concentreremo su MySQL a causa della sua diffusa utilizzo e delle sue funzionalità complete. L’installazione di MySQL è un processo semplice:

Visita il sito web di MySQL e scarica l’installer appropriato per il tuo sistema operativo.
Esegui l’installer, seguendo le istruzioni visualizzate.
Durante l’installazione, ti verrà chiesto di creare un account amministratore. Assicurati di ricordare o archiviare in modo sicuro la password di amministratore.
Una volta completata l’installazione, puoi accedere alla shell di MySQL aprendo un terminale e digitando mysql -u root -p. Ti verrà chiesto di inserire la password di amministratore.
Dopo un accesso riuscito, sarai accolto dalla prompt di MySQL, che indica che il server MySQL è attivo e in esecuzione.

Configurazione di un IDE SQL

Un Ambiente di Sviluppo Integrato (IDE) può migliorare significativamente l’esperienza di scrittura del codice SQL fornendo funzionalità come il completamento automatico, l’evidenziazione della sintassi e la visualizzazione del database. Un IDE non è strettamente necessario per l’esecuzione di query SQL, ma è altamente consigliato per compiti più complessi e progetti più grandi.

DBeaver: Open source e supporta una vasta gamma di DBMS, tra cui MySQL, PostgreSQL, SQLite e SQL Server.
MySQL Workbench: Sviluppato da Oracle, questo è l’IDE ufficiale per MySQL e offre strumenti completi su misura per MySQL.

Dopo aver scaricato e installato il tuo IDE scelto, dovrai collegarlo al tuo server MySQL. Questo di solito comporta la specifica dell’indirizzo IP del server (localhost se il server è sulla tua macchina), il numero di porta (di solito 3306 per MySQL) e le credenziali per un utente di database autorizzato.

Verifica della configurazione

Verifichiamo che tutto funzioni correttamente. Puoi farlo eseguendo una semplice query SQL per visualizzare tutti i database esistenti:

SHOW DATABASES;

Se questa query restituisce un elenco di database e nessun errore, allora complimenti! L’ambiente SQL è stato configurato correttamente e sei pronto per iniziare la programmazione SQL.

Passo 2: Sintassi e comandi SQL di base

Creazione di un database e di tabelle

Prima di aggiungere o manipolare dati, avrai bisogno di almeno un database e una tabella. La creazione di un database e di una tabella viene effettuata tramite:

CREATE DATABASE sql_tutorial;
USE sql_tutorial;
CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT, 
  name VARCHAR(50),
  email VARCHAR(50)
);

Manipolazione dei dati

Ora sei pronto per la manipolazione dei dati. Diamo un’occhiata alle operazioni CRUD di base:

Inserimento: INSERT INTO customers (name, email) VALUES ('John Doe', '[email protected]');
Query: SELECT * FROM customers;
Aggiornamento: UPDATE customers SET email = '[email protected]' WHERE id = 1;
Eliminazione: DELETE FROM customers WHERE id = 1;

Filtraggio e ordinamento

Il filtraggio in SQL consiste nell’utilizzare condizioni per recuperare selettivamente righe da una tabella, spesso utilizzando la clausola WHERE. L’ordinamento in SQL dispone i dati recuperati in un ordine specifico, tipicamente utilizzando la clausola ORDER BY. La paginazione in SQL suddivide il set di risultati in blocchi più piccoli, visualizzando un numero limitato di righe per pagina.

Filtro: SELECT * FROM customers WHERE name = 'John Doe';
Ordinamento: SELECT * FROM customers ORDER BY name ASC;
Paginazione: SELECT * FROM customers LIMIT 10 OFFSET 20;

Tipi di dati e vincoli

Comprendere i tipi di dati e i vincoli è fondamentale per definire la struttura delle tabelle. I tipi di dati specificano che tipo di dati può contenere una colonna, come interi, testo o date. I vincoli impongono limitazioni per garantire l’integrità dei dati.

Tipi interi: INT, SMALLINT, TINYINT, ecc. Utilizzati per memorizzare numeri interi.
Tipi decimali: FLOAT, DOUBLE, DECIMAL. Adatti per memorizzare numeri con cifre decimali.
Tipi di caratteri: CHAR, VARCHAR, TEXT. Utilizzati per dati di testo.
Data e ora: DATE, TIME, DATETIME, TIMESTAMP. Progettati per memorizzare informazioni su data e ora.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE,
    email VARCHAR(50) UNIQUE,
    salary FLOAT CHECK (salary > 0)
  );

Nell’esempio precedente, il vincolo NOT NULL garantisce che una colonna non possa avere un valore NULL. Il vincolo UNIQUE garantisce che tutti i valori in una colonna siano unici. Il vincolo CHECK convalida che lo stipendio debba essere maggiore di zero.

Passo 3: Concetti SQL più avanzati

Unione di tabelle

Le unioni vengono utilizzate per combinare righe provenienti da due o più tabelle in base a una colonna correlata tra di loro. Sono fondamentali quando si desidera recuperare dati distribuiti su più tabelle. Comprendere le unioni è fondamentale per le query SQL complesse.

INNER JOIN: SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;
LEFT JOIN: SELECT * FROM orders LEFT JOIN customers ON orders.customer_id = customers.id;
RIGHT JOIN: SELECT * FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id;

I Join possono essere complessi ma sono incredibilmente potenti quando devi estrarre dati da più tabelle. Passiamo attraverso un esempio dettagliato per chiarire come funzionano i diversi tipi di Join.

Considera due tabelle: Impiegati e Dipartimenti.

-- Tabella Impiegati
CREATE TABLE Impiegati (
  id INT PRIMARY KEY,
  nome VARCHAR(50),
  id_dipartimento INT
);

INSERT INTO Impiegati (id, nome, id_dipartimento) VALUES
(1, 'Winifred', 1),
(2, 'Francisco', 2),
(3, 'Englebert', NULL);

-- Tabella Dipartimenti
CREATE TABLE Dipartimenti (
  id INT PRIMARY KEY,
  nome VARCHAR(50)
);

INSERT INTO Dipartimenti (id, nome) VALUES
(1, 'R&D'),
(2, 'Ingegneria'),
(3, 'Vendite');

Esploriamo diversi tipi di Join:

-- INNER JOIN
-- Restituisce i record che hanno valori corrispondenti in entrambe le tabelle

SELECT I.nome, D.nome 
FROM Impiegati I
INNER JOIN Dipartimenti D ON I.id_dipartimento = D.id;

-- LEFT JOIN (o LEFT OUTER JOIN)
-- Restituisce tutti i record dalla tabella sinistra,
-- e i record corrispondenti dalla tabella destra

SELECT I.nome, D.nome 
FROM Impiegati I
LEFT JOIN Dipartimenti D ON I.id_dipartimento = D.id;

-- RIGHT JOIN (o RIGHT OUTER JOIN)
-- Restituisce tutti i record dalla tabella destra
-- e i record corrispondenti dalla tabella sinistra

SELECT I.nome, D.nome 
FROM Impiegati I
RIGHT JOIN Dipartimenti D ON I.id_dipartimento = D.id;

Negli esempi precedenti, l’INNER JOIN restituisce solo le righe in cui c’è una corrispondenza in entrambe le tabelle. Il LEFT JOIN restituisce tutte le righe dalla tabella sinistra e le righe corrispondenti dalla tabella destra, riempiendo con NULL se non c’è una corrispondenza. Il RIGHT JOIN fa l’opposto, restituendo tutte le righe dalla tabella destra e le righe corrispondenti dalla tabella sinistra.

Raggruppamento e Aggregazione

Le funzioni di aggregazione eseguono un calcolo su un insieme di valori e restituiscono un singolo valore. Le aggregazioni sono comunemente utilizzate insieme alle clausole GROUP BY per suddividere i dati in categorie ed eseguire calcoli su ogni gruppo.

Count: SELECT customer_id, COUNT(id) AS total_orders FROM orders GROUP BY customer_id;
Sum: SELECT customer_id, SUM(order_amount) AS total_spent FROM orders GROUP BY customer_id;
Filtra gruppo: SELECT customer_id, SUM(order_amount) AS total_spent FROM orders GROUP BY customer_id HAVING total_spent > 100;

Sottoquery e Query nidificate

Le sottoquery consentono di eseguire query all’interno di altre query, fornendo un modo per recuperare dati che verranno utilizzati nella query principale come condizione per limitare ulteriormente i dati recuperati.

SELECT *
  FROM customers
  WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE orderdate > '2023-01-01'
  );

Transazioni

Le transazioni sono sequenze di operazioni SQL che vengono eseguite come un’unica unità di lavoro. Sono importanti per mantenere l’integrità delle operazioni del database, soprattutto nei sistemi multiutente. Le transazioni seguono i principi ACID: Atomicità, Coerenza, Isolamento e Durabilità.

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
  COMMIT;

Nell’esempio precedente, entrambe le dichiarazioni UPDATE sono racchiuse all’interno di una transazione. O entrambe si eseguono correttamente, o se si verifica un errore, nessuna viene eseguita, garantendo l’integrità dei dati.

Passaggio 4: Ottimizzazione e Ottimizzazione delle Prestazioni

Comprendere le prestazioni delle query

Le prestazioni delle query sono cruciali per mantenere un sistema di database reattivo. Una query inefficiente può provocare ritardi, influenzando l’esperienza complessiva dell’utente. Ecco alcuni concetti chiave:

Piani di esecuzione: Questi piani forniscono una roadmap su come verrà eseguita una query, consentendo analisi e ottimizzazioni.
Punti critici: Identificare le parti lente di una query può guidare gli sforzi di ottimizzazione. Strumenti come SQL Server Profiler possono assistere in questo processo.

Strategie di indicizzazione

Gli indici sono strutture dati che migliorano la velocità del recupero dei dati. Sono vitali in database di grandi dimensioni. Ecco come funzionano:

Indice su singola colonna: Un indice su una singola colonna, spesso utilizzato nelle clausole WHERE; CREATE INDEX idx_name ON customers (name);
Indice composito: Un indice su più colonne, utilizzato quando le query filtrano per più campi; CREATE INDEX idx_name_age ON customers (name, age);
Comprendere quando indicizzare: L’indicizzazione migliora la velocità di lettura ma può rallentare le operazioni di inserimento e aggiornamento. È necessaria una considerazione attenta per bilanciare questi fattori.

Ottimizzazione di join e subquery

Join e subquery possono richiedere molte risorse. Le strategie di ottimizzazione includono:

Utilizzo di indici: Applicare indici sui campi di join migliora le prestazioni del join.
Riduzione della complessità: Ridurre il numero di tabelle unite e il numero di righe selezionate.

SELECT customers.name, COUNT(orders.id) AS total_orders
  FROM customers
  JOIN orders ON customers.id = orders.customer_id
  GROUP BY customers.name
  HAVING orders > 2;

Normalizzazione e denormalizzazione del database

La progettazione del database gioca un ruolo significativo nelle prestazioni:

Normalizzazione: Riduce la ridondanza organizzando i dati in tabelle correlate. Ciò può rendere le query più complesse ma garantisce la coerenza dei dati.
Denormalizzazione: Combina tabelle per migliorare le prestazioni di lettura a discapito di una potenziale inconsistenza. Viene utilizzata quando la velocità di lettura è una priorità.

Strumenti di monitoraggio e profilazione

L’utilizzo di strumenti per monitorare le prestazioni garantisce che il database funzioni correttamente:

Performance Schema di MySQL: Offre informazioni sull’esecuzione delle query e sulle prestazioni.
SQL Server Profiler: Consente di tracciare e acquisire eventi di SQL Server, aiutando nell’analisi delle prestazioni.

Best Practice per la scrittura di SQL efficiente

Il rispetto delle best practice rende il codice SQL più manutenibile ed efficiente:

Evitare SELECT *: Selezionare solo le colonne necessarie per ridurre il carico.
Minimizzare i caratteri jolly: Utilizzare i caratteri jolly con parsimonia nelle query LIKE.
Utilizzare EXISTS invece di COUNT: Quando si verifica l’esistenza, EXISTS è più efficiente.

SELECT id, name 
FROM customers 
WHERE EXISTS (
    SELECT 1 
    FROM orders 
    WHERE customer_id = customers.id
);

Manutenzione del database

La manutenzione regolare garantisce prestazioni ottimali:

Aggiornamento delle statistiche: Aiuta il motore del database a prendere decisioni di ottimizzazione.
Ricostruzione degli indici: Nel tempo, gli indici diventano frammentati. La ricostruzione regolare migliora le prestazioni.
Backup: I backup regolari sono essenziali per l’integrità e il ripristino dei dati.

Passaggio 5: Best practice per le prestazioni e la sicurezza

Migliori pratiche per le prestazioni

Ottimizzare le prestazioni delle query SQL e del database è fondamentale per mantenere un sistema reattivo ed efficiente. Ecco alcune migliori pratiche per le prestazioni:

Utilizzare gli indici in modo appropriato: Gli indici velocizzano il recupero dei dati, ma possono rallentare le operazioni di modifica dei dati come l’inserimento, l’aggiornamento e l’eliminazione.
Limitare i risultati: Utilizzare la clausola LIMIT per recuperare solo i dati necessari.
Ottimizzare le join: Unire sempre le tabelle su colonne indicizzate o chiavi primarie.
Analizzare i piani di query: Comprendere il piano di esecuzione della query può aiutare a ottimizzare le query.

Migliori pratiche di sicurezza

La sicurezza è fondamentale nel trattare con i database, in quanto spesso contengono informazioni sensibili. Ecco alcune migliori pratiche per migliorare la sicurezza SQL:

crittografare i dati: Crittografare sempre i dati sensibili prima di memorizzarli.
privilegi utente: Concedere agli utenti il minimo indispensabile di privilegi necessari per svolgere i loro compiti.
Prevenzione delle injection SQL: Utilizzare le query parametrizzate per proteggersi dagli attacchi di injection SQL.
Audit regolari: Effettuare regolari audit di sicurezza per individuare vulnerabilità.

Unire prestazioni e sicurezza

Trovare il giusto equilibrio tra prestazioni e sicurezza è spesso una sfida, ma necessario. Ad esempio, sebbene l’indicizzazione possa velocizzare il recupero dei dati, può anche rendere i dati sensibili più accessibili. Pertanto, considerare sempre le implicazioni sulla sicurezza delle strategie di ottimizzazione delle prestazioni.

Esempio: Query sicura ed efficiente

-- Utilizzo di una query parametrizzata per ottimizzare
-- le prestazioni e prevenire le injection SQL

PREPARE secureQuery FROM 'SELECT * FROM users WHERE age > ? AND age < ?';
SET @min_age = 18, @max_age = 35;
EXECUTE secureQuery USING @min_age, @max_age;

Questo esempio utilizza una query parametrizzata, che non solo previene le injection SQL, ma consente anche a MySQL di memorizzare la query nella cache, migliorando le prestazioni.

Andare avanti

Questa guida introduttiva ha coperto i concetti fondamentali e le applicazioni pratiche più popolari di SQL. Dall’avvio all’apprendimento di query complesse, questa guida dovrebbe averti fornito le competenze necessarie per gestire la gestione dei dati attraverso l’uso di esempi dettagliati e con un approccio pratico. Mentre i dati continuano a plasmare il nostro mondo, padroneggiare SQL apre le porte a una varietà di settori, tra cui l’analisi dei dati, l’apprendimento automatico e lo sviluppo software.

Mentre prosegui, prendi in considerazione l’ampliamento del tuo set di competenze SQL con risorse aggiuntive. Siti come il tutorial SQL di w3schools e SQL Practice Exercises su SQLBolt forniscono materiali di studio e esercizi aggiuntivi. Inoltre, i problemi SQL di HackerRank forniscono esercizi di query orientati agli obiettivi. Che tu stia costruendo una piattaforma complessa di analisi dei dati o sviluppando la prossima generazione di applicazioni web, SQL è una competenza che userai sicuramente regolarmente. Ricorda che il percorso per padroneggiare SQL attraversa una lunga strada ed è un viaggio arricchito da una pratica e un apprendimento costanti.

Matthew Mayo (@mattmayo13) ha una laurea magistrale in informatica e un diploma di specializzazione in data mining. Come editore capo di VoAGI, Matthew si impegna a rendere accessibili concetti complessi di data science. I suoi interessi professionali includono l’elaborazione del linguaggio naturale, gli algoritmi di apprendimento automatico ed esplorare l’intelligenza artificiale emergente. È mosso da una missione per democratizzare la conoscenza nella comunità della data science. Matthew programma fin da quando aveva 6 anni.

SQL

Colmare il divario tra clinici e modelli linguistici nel settore sanitario incontra MedAlign, un dataset generato dai clinici per l’inseguimento delle istruzioni nei registri elettronici delle cartelle cliniche.