Strutture dati e database/Linguaggio SQL

Premessa

La sigla SQL sta per Structured Query Language ovvero si tratta di un linguaggio strutturato per interrogare i database. In origine era chiamato SEQUEL (Structured English QUEry Language).

Definizione dei dati

Definizioni di schemi, tabelle, vincoli e primary key

Creazione database e tabelle

Interrogare un database: istruzione SELECT

La sintassi del comando SELECT è definita in questo modo:

SELECT Espressione_su_Colonna [[as] Nuovo_Nome_Colonna]
       {, Espressione_su_Colonna [[as] Nuovo_Nome Colonna]}
FROM Nome_Tabella [[as] alias]
       {, Nome_Tabella [[as] alias]}
[WHERE Condizione]

Join

La sintassi di interrogazione prevista per implementare al suo interno le condizioni di join è la seguente:

SELECT Espressione_su_Colonna [[as] Nuovo_Nome_Colonna]
       {, Espressione_su_Colonna [[as] Nuovo_Nome Colonna]}
FROM Nome_Tabella [[as] alias]
       {, Tipo_di_Join join Nome_Tabella [[as] alias] on
    Condizione_di_Join}
[WHERE Condizione]

Ordinamento

Durante un'interrogazione dei dati presenti nel nostro database, utilizzando la clasula Order by riusciamo a stabilire dei particolari criteri di ordinamento:

SELECT Espressione_su_Colonna [[as] Nuovo_Nome_Colonna]
       {, Espressione_su_Colonna [[as] Nuovo_Nome Colonna]}
FROM Nome_Tabella [[as] alias]
       {, Nome_Tabella [[as] alias]}
[WHERE Condizione]
[ORDER BY Nome_Colonna [asc|desc]
            {, Nome_Colonna [asc|desc]}]

Operatori aggregati

Interrogazioni con raggruppamento

Interrogazioni di tipo insiemistico

Interrogazioni nidificate

Sintassi completa del comando SELECT

Possiamo, ora, scrivere una sintassi più completa per l'interrogazione del nostro database tramite il comando SELECT:

SELECT Espressione_su_Colonna [[as] Nuovo_Nome_Colonna]
       {, Espressione_su_Colonna [[as] Nuovo_Nome Colonna]}
FROM Nome_Tabella [[as] alias]
       {, Tipo_di_Join join Nome_Tabella [[as] alias] on
    Condizione_di_Join}
[WHERE Condizione]
[ORDER BY Nome_Colonna [asc|desc]
            {, Nome_Colonna [asc|desc]}]
[GROUP BY Nome_colonna]
[HAVING Condizione_su_colonne_aggregate]

Modifiche di schemi di tabelle: istruzione ALTER TABLE

Manipolazione dei dati: INSERT, DELETE, UPDATE

Inserimento righe

Il comando che permette di popolare il database è INSERT. La sua sintassi più utilizzata è la seguente:

INSERT INTO Nome_Tabella [( Lista_Colonne )]
  VALUES ( Lista_Valori )

In alternativa si può anche usare:

INSERT INTO Nome_Tabella [( Lista_Colonne )]
   ( Select_di_SQL )

La quale permette di inserire nella tabella tutte quelle righe che sono il risultato del costrutto SELECT.

Eliminazione righe

Per eliminare righe da una tabella si usa il comando DELETE:

DELETE FROM Nome_Tabella [WHERE Condizione]

Modificare le righe

Con il comando UPDATE riusciamo a modificare i valori presenti nel database. La sintassi è la seguente:

UPDATE Nome_Tabella
    SET Nome_Colonna = 
    < Espressione | Select_di_SQL | null | default >
    {, Nome_Colonna = 
    < Espressione | Select_di_SQL | null | default >}
[WHERE Condizione]

Sicurezza: l'uso dei privilegi

Manuali d'utilizzo dei software di gestione dei database

MySQL

Tipi di dati gestiti da MySQL

DATI DI TIPO NUMERICO

Numeri interi:

TINYINT	min. -128	max 127
SMALLINT	min. -32768	max 32767
MEDIUMINT	min. -8388608	max 8388607
INT	min. -2147483648	max 2147483647
BIGINT	min. -9223372036854775808	max 9223372036854775807

Numeri decimali:

FLOAT (escluso lo 0)	min. ±1.175494351E-38	max ±1.175494351E+38
DOUBLE (escluso lo 0)	min. ±2.2250738585072014E-308	max ±2.2250738585072014E+308
DECIMAL (M,D) dipendente dai valori di M e D, lascia la definizione all'utente

M+2 = totale complessivo delle cifre che fanno parte del numero, comprendendo anche il segno e il punto decimale. D = totale delle cifre decimali.

Es: DECIMAL (8,3) --> min -99999.999 max 99999.999

Attributi applicabili ai dati di tipo numerico:

UNSIGNED: impedisce al campo di assumere valori negativi.

ZEROFILL: fa visualizzare una serie di numeri allineandoli in verticale in modo che gli spazi vuoti vengano riempiti con degli zeri.

AUTO_INCREMENT: può essere associato solo a campi con numeri interi; incrementa automaticamente di una unità il valore numerico del campo cui è attribuito per ogni nuovo record inserito.

DATI DI TIPO TESTO E MULTIMEDIALI

Una stringa è un gruppo di caratteri più o meno lungo; un acronimo è una stringa, ma lo sono anche una parola, un cognome e nome, un proverbio.

Dati di tipo testo

Stringhe fino a 255 caratteri sono considerate di piccole dimensioni e possono essere di due tipi:

CHAR (M): occupa un numero di bytes pari alla lunghezza dichiarata (M).

VARCHAR (M): occupa un numero di bytes variabile, pari al numero di caratteri occupati dal dato inserito, fino al massimo dichiarato (M).

Attributi applicabili ai dati di tipo CHAR e VARCHAR:

BINARY: impone che il contenuto venga considerato come una stringa binaria; di conseguenza il programma terrà conto di maiuscole e minuscole.

Stringhe di grandi dimensioni (oltre i 255 caratteri) vengono gestite nelle tipologie TEXT e BLOB. Tutti i tipi di file, in realtà, possono essere considerati come stringhe di dati; in questo senso TEXT e BLOB verranno applicati anche a dati di tipo multimediale.

Stringhe multimediali

TEXT nelle varianti:	TINYTEXT	bytes 2⁸-1
	TEXT	bytes 2¹⁶-1
	MEDIUMTEXT	bytes 2²⁴-1
	LONGTEXT	bytes 2³²-1
BLOB nelle varianti:	TINYBLOB	bytes 2⁸-1
	BLOB	bytes 2¹⁶-1
	MEDIUMBLOB	bytes 2²⁴-1
	LONGBLOB	bytes 2³²-1

Mentre TEXT significa solo "testo", il termine BLOB è acronimo di: Binary Large OBject. I campi di questo tipo vengono gestiti in modalità binaria.

Gruppi (array) di stringhe predefinite

MySQL prevede due tipi di dato per la gestione di campi di tipo testo che debbano contenere solo stringhe scelte tra un insieme di valori (array) predefiniti:

ENUM: può contenere solo uno dei valori appartenenti all'insieme

SET: può contenere anche due o più valori scelti tra quelli appartenenti all'insieme.

DATA, ORA, TIMESTAMP

Abbiamo due possibilità di gestione di questi dati:

Date Complete

TIMESTAMP(M)DATETIME: il valore contenuto in uno dei due tipi di campo contiene sempre tutte le informazioni assegnabili: anno, mese, giorno, ora, minuto, secondo.

M = valore pari da 4 a 14 che determina il tipo di visualizzazione:

TIMESTAMP (14): AAAMMGGHHMMSS

TIMESTAMP (12): AAMMGGHHMMSS

TIMESTAMP (10): AAMMGGHHMM

TIMESTAMP (8): AAAAMMGG

TIMESTAMP (6): AAMMGG

TIMESTAMP (4): AAMM

Valore massimo supportato da un campo di tipo TIMESTAMP: su macchine a 32 bit: dal 1970 al 2030 su macchine a 64 bit: dal 1970 al 2106

TIMESTAMP permette la registrazione di un timbro temporale, fornendo così uno strumento di verifica dell'ordine di inserimento dei record. Per memorizzare l'ora attuale di sistema in un campo di tipo TIMESTAMP si assegna il valore NULL al campo; questo non esclude la possibilità di memorizzare data ed ora al suo interno.

In un campo di tipo DATETIME possono essere memorizzati valori che appartengono ad un ambito temporale che va da:

minimo 1000-01-01 00:00:00 massimo 9999-12-31 23:59:59

Questo consente di superare le limitazioni di TIMESTAMP ma raddoppia l'utilizzo di memoria (da 4 a 8 byte).

Date parziali

Ogni campo gestisce una parte specifica:

date: anno-mese-giorno; time: ora-minuto-secondo; year: solo l'anno

DATE: min. 1000-01-01 max 9999-12-31

TIME: min. -838:59:59 max 838:59:59 (le ore non solo possono essere negative, ma anche superare la cifra 24 perché il campo potrebbe servire a registrare il tempo che intercorre tra due date)

YEAR: min. 1901 max 2155

Il numero di anni diversi che si possono inserire tra 1901 e 2155 è 255

Y2K e input delle date

Salvo altre indicazioni MySQL interpreta le date in input come appartenenti all'intervallo 1970 (anno di nascita di Unix)-2069, quindi l'anno "90" è il 1990, mentre l'anno "03" è il 2003. Stringhe riconosciute automaticamente come data: 20031029 -- 2003/10/29 -- 2003-10-29

Gestione dei dati

INSERIMENTO DATI

Per inserimento dati si intende la creazione di un nuovo record; l'istruzione utilizzata a questo scopo è INSERT e può avere tre tipi di sintassi diverse a seconda di come si vogliono riempire i campi. Si possono aggiungere dati:

• assegnando loro dei valori chiamandoli per nome singolarmente (SET)
• fornendo i dati da inserire in un elenco le cui voci corrispondono alla posizione nel record per ogni campo (VALUES)
• prelevando i dati leggendoli da un'altra tabella (SELECT)

INSERT con SET

INSERT [INTO] nome_tabella SET nome_campo1=espressione1, nome_campo2=espressione2, ...;

Esempio:
INSERT INTO tabella_film SET genere="horror", titolo="halloween" ;

Per vedere il risultato dell'istruzione possiamo eseguire una query sulla tabella con questa istruzione:
SELECT * FROM tabella_film ;

• SELECT = comando SQL per le interrogazioni su tabelle di database
• * = tutti i campi
• FROM = clausola che viene seguita dal nome della tabella
• tabella_film = nome della tabella su cui eseguire l'interrogazione
• ; = carattere che indica il termine della query

Se al posto di "*" venissero messi uno o più nomi di campi della tabella, mysql restituirebbe solo i valori di quei campi.

INSERT con VALUES e da tabelle

INSERT [INTO] nome_tabella [(nome_campo1, ...)] VALUES (espressione1, ...), (...) ... ;

Esempio:
INSERT INTO 'tabella_film' VALUES (NULL, '001', 'American Beauty', 'DVD') ;

La sintassi completa (prima riga dell'istruzione) prevederebbe anche l'elenco dei campi in cui vengono inseriti i dati, ma non è necessario poiché i valori devono essere elencati in fila rispetto ai campi presenti. Il valore NULL in questo caso si riferisce ad esempio ad un campo con valore AUTO_INCREMENT che quindi genera il proprio valore da solo.

Per inserire dati da un'altra tabella eseguiremo semplicemente quest'istruzione:
INSERT [INTO] nome_tabella [nome_campo1, ...)] SELECT * FROM nome_tabella ;

AGGIORNAMENTO DATI

In una tabella di database non è possibile modificare un solo campo, ma è necessario intervenire su tutto il record con un'operazione che si svolge in tre fasi:

FASE1: lettura del record che contiene il campo (o i campi) che vogliamo modificare
FASE2: assegnazione dei nuovi valori ai campi
FASE3: registrazione della nuova versione del record con i valori aggiornati

L'istruzione mysql per aggiornare i dati di un record è UPDATE; l'aggiornamento con UPDATE può essere eseguito contemporaneamente su un gruppo di record, fino a poter aggiornare tutti i record della tabella con una sola istruzione.

Update di record

UPDATE nome_tabella SET nome_campo1=espressione1, nome_campo2=espressione2, ... [WHERE definizione_where] ;

Esempio:
UPDATE tabella_film SET prezzo="24.00" WHERE supporto="DVD" ;

In questo modo la variante di prezzo verrà applicata a tutti i record corrispondenti al criterio "supporto=DVD"

Altra variante:
UPDATE tabella_film SET prezzo="24.00" WHERE supporto="DVD" AND titolo="American Beauty" ;

L'operatore logico AND stabilisce che entrambe le condizioni debbano essere soddisfatte.

Oltre agli operatori logici, le query possono contenere operatori aritmetici e operatori di confronto. Ai primi appartengono i simboli necessari all'esecuzione di operazioni aritmetiche; gli operatori di confronto consentono di eseguire paragoni tra contenuti dei campi, ad esempio per riconoscere quale sia il maggiore.

Esempio:
UPDATE tabella_film SET prezzo=prezzo*1.2 WHERE prezzo<20.00 ;

Vale a dire che il prezzo dei film inferiori a 20.00 viene aumentato del 20% (1,2).

REPLACE

Il comando REPLACE combina le funzioni di INSERT e UPDATE utilizzando la stessa sintassi:
REPLACE [INTO] nome_tabella SET nome_campo1=espressione1, nome_campo2=espressione2, ... ;

REPLACE [INTO] nome_tabella [(nome_campo1, ...)] VALUES (espressione1, ...) ;

REPLACE [INTO] nome_tabella [(nome_campo1, ...)] SELECT * FROM nome_tabella ;

È evidentemente un comando di grande utilità ma alquanto rischioso nell'utilizzo poiché aggiorna anche dati la cui chiave esiste già, con la possibilità di danneggiare irreparabilmente il lavoro svolto in precedenza.

CANCELLAZIONE DATI

Nella parte relativa alla gestione dati abbiamo preso in considerazione il comando DROP per la cancellazione delle tabelle, esso però compie un'eliminazione completa della tabella in tutta la sua struttura. Col termine "cancellazione dati" possiamo però intendere l'eliminazione o svuotamento di alcuni campi o di un intero record. Quando si svuota un campo di un record e si lascia il record nella tabella, quel campo potrà essere riutilizzato tramite comando UPDATE. Nel caso venga tolta un'intera riga della tabella non sarà più possibile utilizzare quel record, il comando utilizzato è DELETE.

Cancellare il contenuto di campi Per svuotare semplicemente un campo, possiamo utilizzare il comando UPDATE e assegnare il valore NULL al campo in oggetto:
UPDATE tabella_film SET titolo=" " WHERE titolo="American Beauty" ;

Attenzione! Se non specifichiamo la condizione WHERE, verranno svuotato tutti i campi "titolo" di tutti i record!

La cancellazione può essere un azzeramento di valore:
UPDATE tabella_film SET prezzo=0 WHERE titolo="American Beauty" ;

Il numero 0 in un campo numerico rappresenta comunque un valore, se si vuole gestire il campo in modo che non contenga alcun dato:
UPDATE tabella_film SET prezzo=NULL WHERE titolo="American Beauty" ;

Alcune precauzioni:
Il valore 0 in un campo di tipo DATE verrebbe interpretato come "anno 2000", è necessario quindi utilizzare l'attributo NULL.
Il valore NULL in un campo di tipo TIMESTAMP verrebbe interpretato come "data e ora corrente", quindi:

Tipo di dato	Stringa di azzeramento
DATE	0000-00-00
TIME	00:00:00
YEAR	0000
TIMESTAMP	00000000000000
DATETIME	0000-00-00 00:00:00

DELETE record

La cancellazione di uno o più record si effettua tramite comando DELETE:
DELETE FROM nome_tabella [WHERE definizione_where] ;

Esempio:
DELETE FROM tabella_film WHERE film_id="1" ;

Tralasciare la clausola WHERE comporta l'eliminazione di tutti i record della tabella.
Con il comando DROP la tabella viene completamente eliminata, mentre con DELETE eliminiamo solo il contenuto conservando la struttura della tabella stessa.