Oggi, Structured Query Language è il mezzo standard per manipolare e interrogare i dati nei database relazionali, anche se con estensioni proprietarie tra i prodotti. La facilità e l’ubiquità di SQL hanno persino portato i creatori di molti archivi di dati “NoSQL” o non relazionali, come Hadoop, ad adottare sottoinsiemi di SQL o a creare i propri linguaggi di query simili a SQL.
Ma SQL non è sempre stato il linguaggio “universale” per i database relazionali. Fin dall’inizio (circa 1980), SQL ha avuto alcuni colpi contro di lui. Molti ricercatori e sviluppatori dell’epoca, me compreso, pensavano che l’overhead di SQL non sarebbe mai stato pratico in un database di produzione.
Chiaramente, ci sbagliavamo. Ma molti credono ancora che, per tutta la facilità e l’accessibilità di SQL, il prezzo richiesto in termini di prestazioni di runtime è spesso troppo alto.
Storia del SQL
Prima che ci fosse SQL, i database avevano interfacce di programmazione strette e navigabili, e tipicamente erano progettati intorno ad uno schema di rete chiamato modello di dati CODASYL. CODASYL (Committee on Data Systems Languages) era un consorzio responsabile del linguaggio di programmazione COBOL (a partire dal 1959) e delle estensioni del linguaggio dei database (a partire da 10 anni dopo).
Quando si programmava su un database CODASYL, si navigava verso i record attraverso i set, che esprimono relazioni uno-a-molti. I vecchi database gerarchici permettono ad un record di appartenere solo ad un set. I database di rete permettono ad un record di appartenere a più insiemi.
Si supponga di voler elencare gli studenti iscritti a CS 101. Per prima cosa dovreste trovare "CS 101" nell’insieme Courses per nome, impostarlo come proprietario o genitore dell’insieme Enrollees, trovare il primo membro (ffm) dell’insieme Enrollees, che è un record Student, ed elencarlo. Poi andresti in un ciclo: Trova il prossimo membro (fnm) e lo elenca. Quando fnm fallisce, si esce dal ciclo.
Questo può sembrare un sacco di lavoro inutile per il programmatore di database, ma era molto efficiente al momento dell’esecuzione. Esperti come Michael Stonebraker dell’Università della California a Berkeley e Ingres hanno sottolineato che fare quel tipo di query in un database CODASYL come IDMS richiedeva circa la metà del tempo della CPU e meno della metà della memoria della stessa query su un database relazionale usando SQL.
Per fare un confronto, la query SQL equivalente per restituire tutti gli studenti di CS 101 sarebbe qualcosa come
SELECT student.name FROM courses, enrollees, students WHERE course.name ="CS 101"
Questa sintassi implica un inner join relazionale (in realtà due), come spiegherò più avanti, e lascia fuori alcuni dettagli importanti, come i campi usati per i join.
Basi di dati relazionali e SQL
Perché rinunciare ad un miglioramento di un fattore due nella velocità di esecuzione e nell’uso della memoria? C’erano due grandi ragioni: facilità di sviluppo e portabilità. Nel 1980 non pensavo che l’una o l’altra contassero molto rispetto alle prestazioni e ai requisiti di memoria, ma quando l’hardware dei computer è migliorato ed è diventato più economico la gente ha smesso di preoccuparsi della velocità di esecuzione e della memoria e si è preoccupata di più dei costi di sviluppo.
In altre parole, la legge di Moore ha ucciso i database CODASYL in favore dei database relazionali. Come è successo, il miglioramento del tempo di sviluppo è stato significativo, ma la portabilità dell’SQL si è rivelata una chimera.
Da dove vengono il modello relazionale e l’SQL? E.F. “Ted” Codd era un informatico dell’IBM San Jose Research Laboratory che elaborò la teoria del modello relazionale negli anni ’60 e la pubblicò nel 1970. IBM fu lenta ad implementare un database relazionale nel tentativo di proteggere i ricavi del suo database CODASYL IMS/DB. Quando IBM finalmente iniziò il suo progetto System R, il team di sviluppo (Don Chamberlin e Ray Boyce) non era sotto Codd, e ignorarono il documento di Codd del 1971 sul linguaggio relazionale Alpha per progettare il proprio linguaggio, SEQUEL (Structured English Query Language). Nel 1979, prima ancora che IBM avesse rilasciato il suo prodotto, Larry Ellison incorporò il linguaggio nel suo database Oracle (usando le pubblicazioni SEQUEL di IBM pre-lancio come specifiche). SEQUEL divenne presto SQL per evitare una violazione del marchio internazionale.
I “tamburi che battono per SQL” (come disse Michael Stonebraker) venivano non solo da Oracle e IBM, ma anche dai clienti. Non era facile assumere o formare progettisti e programmatori di database CODASYL, così SEQUEL (e SQL) sembrava molto più attraente. L’SQL era così attraente negli ultimi anni ’80 che molti venditori di database essenzialmente spillarono un processore di query SQL sopra i loro database CODASYL, con grande disappunto di Codd, che sentiva che i database relazionali dovevano essere progettati da zero per essere relazionali.
Un puro database relazionale, come progettato da Codd, è costruito su tuple raggruppate in relazioni, coerenti con la logica dei predicati del primo ordine. I database relazionali del mondo reale hanno tabelle che contengono campi, vincoli e trigger, e le tabelle sono collegate attraverso chiavi esterne. SQL è usato per dichiarare i dati da restituire, e un processore di query SQL e un ottimizzatore di query trasformano la dichiarazione SQL in un piano di query che viene eseguito dal motore del database.
SQL include un sotto linguaggio per definire gli schemi, il linguaggio di definizione dei dati (DDL), insieme a un sotto linguaggio per modificare i dati, il linguaggio di manipolazione dei dati (DML). Entrambi hanno radici nelle prime specifiche CODASYL. Il terzo sotto-linguaggio in SQL dichiara le query, attraverso l’istruzione SELECT e i join relazionali.
SQL SELECT
L’istruzione SELECT dice all’ottimizzatore di query quali dati restituire, in quali tabelle cercare, quali relazioni seguire, e quale ordine imporre ai dati restituiti. L’ottimizzatore di query deve capire da solo quali indici usare per evitare scansioni di tabelle a forza bruta e ottenere buone prestazioni di query, a meno che il particolare database supporti i suggerimenti agli indici.
Parte dell’arte della progettazione di database relazionali si basa sull’uso giudizioso degli indici. Se si omette un indice per una query frequente, l’intero database può rallentare sotto carichi di lettura pesanti. Se si hanno troppi indici, l’intero database può rallentare sotto carichi pesanti di scrittura e aggiornamento.
Un’altra arte importante è scegliere una buona chiave primaria unica per ogni tabella. Non dovete solo considerare l’impatto della chiave primaria sulle query comuni, ma come giocherà nelle join quando appare come chiave esterna in un’altra tabella, e come influenzerà la località di riferimento dei dati.
Nel caso avanzato di tabelle di database che sono divise in diversi volumi a seconda del valore della chiave primaria, chiamato sharding orizzontale, dovete anche considerare come la chiave primaria influenzerà lo sharding. Suggerimento: volete che la tabella sia distribuita uniformemente tra i volumi, il che suggerisce che non volete usare date stampate o interi consecutivi come chiavi primarie.
Le discussioni sulla dichiarazione SELECT possono iniziare in modo semplice, ma possono diventare rapidamente confuse. Considerate:
SELECT * FROM Customers;
Semplice, giusto? Chiede tutti i campi e tutte le righe della tabella Customers. Supponiamo però che la tabella Customers abbia cento milioni di righe e cento campi, e che uno dei campi sia un grande campo di testo per i commenti. Quanto tempo ci vorrà per estrarre tutti quei dati su una connessione di rete a 10 megabit al secondo se ogni riga contiene in media 1 kilobyte di dati?
Forse dovreste ridurre la quantità di dati che inviate via cavo. Considerate:
SELECT TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount FROM Customers
WHERE state ="Ohio" AND city ="Cleveland"
ORDER BY lastSaleDate DESCENDING;
Ora tirerete giù molti meno dati. Avete chiesto al database di darvi solo quattro campi, di considerare solo le aziende di Cleveland, e di darvi solo le 100 aziende con le vendite più recenti. Per farlo nel modo più efficiente sul server del database, però, la tabella Customers ha bisogno di un indice su state+city per la WHERE e un indice su lastSaleDate per le clausole ORDER BY e TOP 100.
A proposito, TOP 100 è valido per SQL Server e SQL Azure, ma non per MySQL o Oracle. In MySQL, useresti LIMIT 100 dopo la clausola WHERE. In Oracle, usereste un bound su ROWNUM come parte della clausola WHERE, cioè WHERE... AND ROWNUM <=100. Sfortunatamente, gli standard ANSI/ISO SQL (e ce ne sono nove ad oggi, che vanno dal 1986 al 2016) arrivano solo fino a un certo punto, oltre il quale ogni database introduce le proprie clausole e caratteristiche proprietarie.
SQL joins
Finora, ho descritto la sintassi SELECT per le tabelle singole. Prima di poter spiegare le clausole JOIN, è necessario capire le chiavi esterne e le relazioni tra le tabelle. Lo spiegherò usando esempi di DDL, usando la sintassi di SQL Server.
La versione breve di questo è abbastanza semplice. Ogni tabella che volete usare nelle relazioni dovrebbe avere un vincolo di chiave primaria; questo può essere un singolo campo o una combinazione di campi definiti da un’espressione. Per esempio:
CREATE TABLE Persons (
PersonID int NOT NULL PRIMARY KEY,
PersonName char(80),
...
Ogni tabella che ha bisogno di relazionarsi con Persons dovrebbe avere un campo che corrisponde alla Persons chiave primaria, e per preservare l’integrità relazionale questo campo dovrebbe avere un vincolo di chiave esterna. Per esempio:
CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
...
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
Ci sono versioni più lunghe di entrambe le dichiarazioni che usano la parola chiave CONSTRAINT, che permette di nominare il vincolo. Questo è quello che la maggior parte degli strumenti di progettazione di database genera.
Le chiavi primarie sono sempre indicizzate e uniche (i valori del campo non possono essere duplicati). Altri campi possono opzionalmente essere indicizzati. È spesso utile creare degli indici per i campi a chiave esterna e per i campi che appaiono nelle clausole WHERE e ORDER BY, anche se non sempre, a causa del potenziale overhead dovuto alle scritture e agli aggiornamenti.
Come potreste scrivere una query che restituisca tutti gli ordini effettuati da John Doe?
SELECT PersonName, OrderID FROM Persons
INNER JOIN Orders ON Persons.PersonID = Orders.PersonID
WHERE PersonName ="John Doe";
In effetti, ci sono quattro tipi di JOININNEROUTERLEFT, e RIGHT. Il INNER JOIN è quello predefinito (puoi omettere la parola INNER), ed è quello che include solo le righe che contengono valori corrispondenti in entrambe le tabelle. Se volete elencare le persone che hanno ordini o meno, userete un LEFT JOIN, per esempio:
SELECT PersonName, OrderID FROM Persons
LEFT JOIN Orders ON Persons.PersonID = Orders.PersonID
ORDER BY PersonName;
Quando iniziate a fare query che uniscono più di due tabelle, che usano espressioni, o che forzano i tipi di dati, la sintassi può diventare un po’ complicata all’inizio. Fortunatamente, ci sono strumenti di sviluppo di database che possono generare query SQL corrette per voi, spesso trascinando e rilasciando tabelle e campi dal diagramma dello schema in un diagramma di query.
SQL stored procedures
A volte la natura dichiarativa della dichiarazione SELECT non vi porta dove volete andare. La maggior parte dei database ha una struttura chiamata stored procedures; sfortunatamente questa è un’area in cui quasi tutti i database usano estensioni proprietarie agli standard ANSI/ISO SQL.
In SQL Server, il dialetto iniziale per le stored procedures (o stored procs) era Transact-SQL, detto T-SQL; in Oracle, era PL-SQL. Entrambi i database hanno aggiunto altri linguaggi per le stored procedure, come C#, Java e R. Una semplice stored procedure T-SQL potrebbe essere solo una versione parametrizzata di una dichiarazione SELECT. I suoi vantaggi sono la facilità d’uso e l’efficienza. Le stored procedure sono ottimizzate quando vengono salvate, non ogni volta che vengono eseguite.
Una stored procedure T-SQL più complicata potrebbe usare istruzioni SQL multiple, parametri di input e output, variabili locali, blocchi BEGIN...END, condizioni IF...THEN...ELSE, cursori (elaborazione riga per riga di un set), espressioni, tabelle temporanee, e tutta una serie di altre sintassi procedurali. Ovviamente se il linguaggio della stored procedure è C#, Java o R, si useranno le funzioni e la sintassi di quei linguaggi procedurali. In altre parole, nonostante il fatto che la motivazione di SQL era di usare query dichiarative standardizzate, nel mondo reale si vede un sacco di programmazione procedurale del server specifica per il database.
Questo non ci riporta indietro ai brutti vecchi tempi della programmazione del database CODASYL (anche se i cursori si avvicinano), ma si allontana dall’idea che le dichiarazioni SQL dovrebbero essere standardizzate e che le preoccupazioni sulle prestazioni dovrebbero essere lasciate all’ottimizzatore di query del database. Alla fine, un raddoppio delle prestazioni è spesso troppo da lasciare sul tavolo.
Imparare SQL
I siti elencati di seguito possono aiutarvi ad imparare SQL, o a scoprire le stranezze dei vari dialetti SQL.
- Codecademy. Impara SQL. Gratuito e interattivo. Aggiornamento Pro disponibile a pagamento.
- Khan Academy. Introduzione a SQL: Interrogazione e gestione dei dati. Video tutorial gratuito.
- SoloLearn. Fondamenti di SQL. Orientato a MySQL. Gratuito.
- Problemi e soluzioni SQL ed esercizi SQL. Libro di testo interattivo ed esercizi. Gratuito.
- SQLZoo. Un tutorial SQL interattivo sviluppato e mantenuto dalla Edinburgh Napier University. Supporta Microsoft SQL Server, Oracle Database, MySQL, IBM DB2, e PostgreSQL. Gratuito.
- Tutorialspoint. Impara SQL. Solo testo, non interattivo. Gratuito.
- Udacity. Introduzione ai database relazionali. Usa Python e richiede qualche conoscenza di Python. Gratuito.
- Udemy. I corsi gratuiti includono Introduzione ai database e interrogazioni SQL, MySQL Database per principianti, Microsoft SQL per principianti, Hands-on SQL per principianti (
SELECTFROM, eWHERE), e Sachin Quickly Learns (SQL). - Vertabelo Academy. Fondamenti di SQL, Operare sui dati in SQL, Creare tabelle in SQL, e altri otto corsi interattivi SQL. Alcuni corsi hanno test drive gratuiti, dopo i quali ci può essere una tassa. Ci sono sette corsi aggiuntivi per Microsoft SQL Server. Il sito ha anche uno strumento grafico di progettazione di database per PostgreSQL, MySQL, Oracle Database, SQL Server, SQLite, e IBM DB2.
- W3Schools. SQL Tutorial. Gratuito e disponibile senza registrazione.
Documentazione sui database: