I database relazionali

Il linguaggio SQL

SQL (Structured Query Language) è un linguaggio standardizzato, dichiarativo e procedurale che gira intorno all'uso delle "query" per interrogare il database, quindi leggere, modificare, inserire e cancellare i dati presenti nelle tabelle. In pratica bisogna creare e assegnare il nome al database e alle tabelle, dopodichè riempire queste ultime con coppie di chiavi/valori. Tutto questo si può fare tramite phpMyAdmin che renderà il lavoro più semplice oppure farlo manualmente attraverso le query SQL. Come già ribadito in un altro articolo, PHP permette l'elaborazione e la gestione dei dati di un database.

SQL mette a disposizione sette categorie di operatori che sono: assegnazione, confronto, stringa, aritmetici, condizionali, logici e tra bit. Vediamoli nel dettaglio:

Operatori di assegnazione

Gli operatori di assegnazione assegnano un valore a una variabile o a un campo.

• = Esprime un'assegnazione e non restituisce alcun valore.
• := Esprime un'assegnazione di un valore ad una variabile non ancora istanziata e non restituisce alcun valore.

Operatori di confronto

Gli operatori di confronto servono a determinare uguaglianze e disuguaglianze tra valori e ad effettuare ricerche all'interno dei dati. Di seguito uno schema tabellare:

• = Esprime uguaglianza tra due valori numerici o stringhe di caratteri (dove non è usato come operatore di assegnazione)
• IS Si usa per verificare se un valore è NULL, oppure se corrisponde a un valore booleano (TRUE, FALSE, UNKNOWN).
• LIKE Esprime somiglianza tra due valori letterali: con l'operatore LIKE è possibile usare, per i confronti, i caratteri speciali % (sostituisce un arbitrario numero di lettere) e _ (sostituisce una lettera arbitraria)
• < Stabilisce se un valore è minore di un altro
• > Stabilisce se un valore è maggiore di un altro
• <= Stabilisce se un valore è minore o uguale di un altro
• >= Stabilisce se un valore è maggiore o uguale di un altro
• <> Stabilisce se due valori sono diversi tra loro
• != equivale a <>
• BETWEEN ... AND Recupera un valore compreso tra due valori
• IN Stabilisce se un valore è contenuto in una lista di valori possibili
• EXISTS Stabilisce se una determinata subquery restituisce un valore
• ANY o SOME Stabilisce se una determinata subquery restituisce almeno uno dei valori specificati
• ALL Stabilisce se una determinata subquery restituisce tutti i valori desiderati

Ad alcuni di questi operatori corrisponde un operatore contrario che fa uso del termine NOT:

• IS NOT
• NOT LIKE
• NOT BETWEEN
• NOT IN
• NOT EXISTS

Operatori stringa

Gli operatori stringa accettano come operandi due stringhe e restituiscono un valore booleano o stringa.

Operatore LIKE
L'operatore LIKE (viene usato anche MATCHES), di solito, è alla base dei criteri di ricerca per parole chiave e delle query effettuate da un motore di ricerca. Il carattere underscore ("_"), detto wildchar, esegue la ricerca su un singolo carattere arbitrario; il simbolo "%" è detto operatore jolly ed esegue la ricerca su una stringa arbitraria, ovvero su un intervallo di valori numerici. Per ricercare il carattere underscore all'interno di un'espressione utilizzare la sintassi "[_]".

Operatori SIMILAR TO e REGEXP
L'operatore SIMILAR TO è parte dello standard SQL e consente di verificare se una stringa corrisponde a una data espressione regolare. La sintassi utilizzata è differente dagli standard de facto che sono stati stabiliti da Perl e da POSIX e per questo motivo l'adozione di tale operatore da parte dei produttori è piuttosto limitata. L'operatore REGEXP è più diffuso e anch'esso consente di verificare se una stringa corrisponde a una data espressione regolare. La sintassi utilizzata varia a seconda dell'implementazione SQL, tuttavia in genere è piuttosto aderente agli standard de facto stabiliti dal Perl o da POSIX. L'SQL prevede anche gli operatori complementari NOT SIMILAR TO e NOT REGEXP.

BINARY
Questo operatore serve a rendere case-sensitive l'applicazione delle espressioni regolari, cioè a far sì che le lettere maiuscole siano considerate differenti dalle minuscole.

COLLATE
Questo operatore serve a specificare quale collazione (insieme di regole per l'ordinamento di un set di caratteri) deve essere utilizzata nell'espressione corrente.

Operatori aritmetici

Gli operatori aritmetici accettano operatori di un tipo numerico (interi o decimali) e restituiscono il risultato dell'operazione aritmetica corrispondente. Di seguito uno schema tabellare:

• + Effettua un'addizione, o lascia immutato il segno di un numero
• - Effettua una sottrazione, o inverte il segno di un numero
• * Effettua una moltiplicazione
• / Effettua una divisione
• MOD Restituisce il resto di una divisione
• DIV Restituisce la parte intera di una divisione

Operatori condizionali

L'unico operatore condizionale di SQL è WHERE (dove) e serve a definire criteri di ricerca mirati.

Operatori logici

Gli operatori logici di SQL appartengono agli operatori logici booleani e sono AND (e), OR (o, oppure), NOT (non), XOR (detto anche "OR esclusivo").

• L'operatore logico AND, che lega due condizioni, restituisce il valore TRUE se e solo se entrambi gli operandi sono veri.
• L'operatore logico OR, che lega due condizioni, restituisce TRUE se e solo se almeno uno degli operandi è vero.
• L'operatore NOT accetta un solo operando e restituisce il valore inverso: falso se questo è vero, vero se questo è falso.
• L'operatore XOR, che accetta due condizioni, restituisce TRUE se e solo se uno solo degli operandi è vero.

Operatori tra bit

Lo standard SQL non prevede questa famiglia di operatori, tuttavia essa è presente in molte implementazioni. Questi operatori effettuano le operazioni AND, OR, NOT e XOR a livello di bit.

DDL: Definizione dei dati

DDL (Data Definition Language – linguaggio di definizione dei dati) serve a creare, modificare o eliminare gli oggetti in un database. Sono i comandi DDL a definire la struttura del database e quindi dei dati ivi contenuti, ma non fornisce gli strumenti per modificare i dati stessi per il quale si usa il DML (Data Manipulation Language). L'utente deve avere i permessi necessari per agire sulla struttura del database e questi permessi vengono assegnati tramite il DCL (Data Control Language).

DML: Manipolazione dei dati

DML (Data Manipulation Language – linguaggio di manipolazione dei dati) fornisce i comandi per inserire, modificare ed eliminare i dati all'interno delle tabelle di un database. La struttura di questi dati deve già essere stata definita tramite il DDL. Inoltre, il permesso di accedere a tali dati deve essere precedentemente assegnato all'utente tramite il DCL.

Insert
Il comando ha la funzione di inserire i dati nelle tabelle. Le colonne (o campi) di destinazione dei valori possono essere o meno dichiarate nel comando. Se non vengono dichiarate, è necessario passare al comando un valore per ogni colonna della tabella, rispettando rigorosamente l'ordine delle colonne stesse. Se, invece, le colonne di destinazione vengono dichiarate, è possibile indicare le sole colonne per le quali vengono passati dei valori, purché vengano inseriti comunque i valori per tutte le colonne not null (che non possono essere nulle) della tabella.
Di per sé il comando insert opera inserendo in tabella una sola riga per volta. È possibile, però, inserire più di una riga "in modo automatico" passando all'insert una serie di righe (o tuple) risultanti da un comando di select, purché tali righe siano compatibili con le colonne della tabella su cui si inseriscono i dati.

Come utilizzare Insert?

Insert semplice:
INSERT INTO nome_tabella VALUES (elenco valori, tutti, rispettando l’ordine dei campi della tabella);
oppure
INSERT INTO nome_tabella (elenco dei campi interessati dall’inserimento) VALUES (elenco valori, tutti, rispettando l’ordine dei campi dichiarati sopra);

Insert mediante select:
INSERT INTO nome_tabella (elenco dei campi interessati dall’inserimento) AS SELECT ...;

Update
Il comando update ha la triplice funzione di modificare i dati delle tabelle. Il nome di ogni campo che deve essere modificato va dichiarato dopo la parola chiave SET e deve essere seguito dal simbolo " = " (uguale) e dal nuovo valore che deve assumere.
È possibile modificare più campi della stessa riga in un unico comando update, separandoli l'uno dall'altro con il simbolo ", " (virgola). Il comando generico aggiorna tutte le righe della tabella. È possibile restringerne il numero applicando la parola chiave aggiuntiva WHERE, che permette di effettuare una selezione qualitativa delle righe imponendo delle condizioni sui dati presenti nelle righe prima dell'aggiornamento.

Come utilizzare Update?

Update generico:
UPDATE nome_tabella SET nome_campo1 = 'valore1_nuovo', nome_campo2 = 'valore2_nuovo', ... ;
Update con condizione:
UPDATE nome_tabella SET nome_campo1 = 'valore1_nuovo', nome_campo2 = 'valore2_nuovo' WHERE nome_campo3 = 'valore';

Delete
Il comando delete ha la funzione di cancellare i dati dalle tabelle. Come il comando update anche delete può operare in modo generico cancellando tutte le righe della tabella oppure può identificare le righe da cancellare mediante la parola chiave aggiuntiva WHERE e la condizione (o le condizioni) ad essa associata.

Come utilizzare Delete?

Delete generico:
DELETE FROM nome_tabella;
Delete con condizione:
DELETE FROM nome_tabella WHERE nome_campo = 'valore';

Truncate Table
Come già detto, una Delete priva di clausola Where cancella tutte le righe dalla tabella specificata. Tuttavia, esiste un altro modo per svuotare completamente una tabella: il comando Truncate Table. Alcuni DBMS implementano questo comando (che non è presente nello standard SQL) in modo più veloce rispetto a una Delete, ad esempio cancellando il file dei dati senza leggerlo e ricreandolo vuoto. Generalmente però questo impedisce che vengano restituite alcune informazioni accessorie, come il numero di righe cancellate.

Come utilizzare Truncate Table?

TRUNCATE [TABLE] nome_tabella

La transazione
Una transazione è un blocco di istruzioni che sono strettamente correlate tra loro. Nel caso in cui per qualsiasi motivo (un errore interno al DBMS, un errore nelle istruzioni SQL, un errore di sistema) una delle istruzioni non arrivi a compimento, l'intera transazione verrà annullata. Si dice quindi che una transazione è un'operazione atomica, ossia non è divisibile: se non viene eseguita interamente, non verrà eseguita affatto. Questo è uno degli strumenti più utilizzati per garantire l'integrità dei dati.
Per iniziare esplicitamente una transazione si utilizza il comando BEGIN TRANSACTION. La mancanza di questo comando fa sì che ogni istruzione funga da transazione a sé stante.

Come utilizzare Begin Transaction?

BEGIN TRANSACTION [transaction_name];

Commit
Per confermare la transazione si utilizza il comando COMMIT. Con esso tutte le modifiche effettuate sui dati in precedenza vengono memorizzate.

Come utilizzare Commit?

COMMIT[TRANSACTION] [transaction_name];

Rollback
Per annullare la transazione si utilizza il comando ROLLBACK. Con esso tutte le modifiche effettuate sui dati in precedenza (a partire dall'inizio della transazione) vengono cancellate.

Come utilizzare Rollback?

ROLLBACK [TRANSACTION] [transaction_name];

DQL: Interrogazione dei dati

DQL (Data Query Language – linguaggio di interrogazione dei dati) comprende i comandi per leggere ed elaborare i dati presenti in un database. Questi dati devono essere stati inseriti attraverso il DML in strutture create con il DDL, mentre il DCL stabilisce se l'utente può accedervi.

Select
Col comando select abbiamo la possibilità di estrarre i dati, in modo mirato, dal database.

Come utilizzare Select?

SELECT [ALL | DISTINCT | TOP] lista_elementi_selezione FROM lista_riferimenti_tabella [ WHERE espressione_condizionale ] [ GROUP BY lista_colonne [HAVING Condizione] ] [ ORDER BY lista_colonne ];
dove:

- lista_elementi_selezione è l'elenco dei campi da estrarre (separati tra loro con una virgola);
- lista_riferimenti_tabella è l'elenco delle tabelle da cui estrarre i dati;
- espressione_condizionale rappresenta l'elenco delle condizioni, ovvero dei requisiti che un campo deve rispettare per poter essere prelevato dall'interrogazione (le condizioni sono specificate mediante gli operatori di confronto, connettori logici e comparatori come between, in, like, is null);
- lista_colonne è la colonna o le colonne che devono essere prese come riferimento per l'ordinamento dei dati in uscita.

Di default il comando SELECT agisce con il metodo ALL, ma specificando DISTINCT (o UNIQUE a seconda delle implementazioni) è possibile eliminare dai risultati le righe duplicate.
La clausola GROUP BY serve per raggruppare per tupla i risultati mentre la relativa condizione HAVINGserve per filtrarne i risultati.

SELECT fornitore, sum(importo_fattura) FROM fatture GROUP BY fornitore HAVING sum(importo_fattura)>10.000

In questo caso vengono filtrati i fornitori che hanno un totale fatturato maggiore di 10.000.
La clausola ORDER BY serve per ordinare i risultati in base a uno o più campi.
LIMIT (o TOP, a seconda delle implementazioni) limita il numero delle righe fornite: LIMIT 10 prende le prime 10 righe della mia tabella. È anche possibile scartare un certo numero di righe all'inizio dei risultati aggiungendo un parametro a LIMIT o la clausola OFFSET.

L'SQL standard non prevede alcun ordinamento se non si specifica la clausola ORDER BY, pertanto senza di essa anche LIMIT ha un effetto imprevedibile. Un esempio è il seguente:

SELECT DISTINCT cognome, nome, citta_residenza FROM utenti WHERE anni > = 18 ORDER BY cognome

Questa query estrae l'elenco di tutti gli utenti maggiorenni ordinando l'output in base al cognome. Nelle clausole GROUP BY e ORDER BY i nomi dei campi si possono sostituire con il numero corrispondente all'ordine della funzione SELECT: nell'ultimo caso ORDER BY cognome si può scrivere ORDER BY 1.
La definizione di select è comunque molto più ampia, prevede molte altre opzioni ma in linea di massima con queste opzioni si compongono la maggior parte delle interrogazioni.

SELECT DISTINCT * FROM utenti

L'asterisco permette di includere nella selezione tutte le colonne della tabella utenti.

Join
Una forma di select composto tra più tabelle con uno o più campi comuni si ottiene attraverso la clausola JOIN.

Subquery
Le subquery possono essere inserite ovunque il linguaggio SQL ammetta un'espressione che restituisce un singolo valore e nella clausola FROM. In questo secondo caso, le subquery sono chiamate anche tabelle derivate (derived table).
Le subquery propriamente dette possono restituire un singolo valore, oppure un insieme di risultati, a seconda dei casi. Un esempio piuttosto semplice è quello in cui si vogliono estrarre da una tabella i valori numerici superiori alla media. Una sola Select non può leggere la media e al contempo i valori che la superano. A questo scopo si avrà una select che legge la media:

SELECT AVG(campo1) FROM mia_tabella;

Questa query verrà inserita nella clausola WHERE della query più esterna; la subquery viene eseguita per prima:

SELECT * FROM mia_tabella WHERE campo1 > (SELECT AVG(campo1) FROM mia_tabella);

Come si vede, da un punto di vista sintattico è necessario porre le subquery tra parentesi.

DCL: Controllo sui dati

DCL serve a fornire o revocare agli utenti i permessi necessari per poter utilizzare i comandi DML e DDL, oltre agli stessi comandi DCL (che gli servono per poter a sua volta modificare i permessi su alcuni oggetti).

Grant
Il comando GRANT fornisce o concede uno o più permessi a un determinato utente (es: il permesso di inserimento in una tabella, di modificarla o eliminarla) e di amministratore di valore.

Revoke
Il comando REVOKE revoca uno o più permessi a un determinato utente (es: il permesso di cancellazione da una tabella).

Dunque, cari lettori, abbiamo dato un'idea generale di quello che è in grado di fare il linguaggio SQL su un database relazionale. Ci tengo a ricordare che, come per gli articoli di programmazione front-end e back-end, ho solo trattato le nozioni base di questo linguaggio; c'è ancora molto da approfondire e questo articolo potrebbe essere uno stimolo in più per farlo.

A presto, GG-CREATOR

La parte inerente ai comandi SQL è opera di Wikipedia.