3. Introduzione al linguaggio SQL

In questa sezione del capitolo, presentiamo i primi comandi SQL per la creazione e la gestione di una singola tabella. In genere forniamo una versione semplificata di questi comandi. La loro sintassi completa è disponibile nelle guide di riferimento di Firebird (vedere la Sezione 2.2).

Un database viene utilizzato da persone con competenze diverse:

l'amministratore del database è generalmente una persona esperta in SQL e database. È lui che crea le tabelle, poiché questa operazione viene solitamente eseguita una sola volta. Nel corso del tempo, potrebbe essere necessario modificare la struttura. Un database è un insieme di tabelle collegate da relazioni. L'amministratore del database definisce queste relazioni. Inoltre, concede le autorizzazioni ai vari utenti del database. Ad esempio, può specificare che un determinato utente abbia il diritto di visualizzare il contenuto di una tabella ma non di modificarla.
L'utente del database è la persona che dà vita ai dati. A seconda delle autorizzazioni concesse dall'amministratore del database, aggiungerà, modificherà ed eliminerà dati nelle varie tabelle del database. Analizzerà inoltre i dati per estrarre informazioni utili per il buon funzionamento dell'azienda, dell'amministrazione, ecc.

Nella sezione 2.6 abbiamo presentato l'editor SQL dello strumento [IB-Expert]. Questo è lo strumento che useremo. Rivediamo alcuni punti:

È possibile accedere all'editor SQL tramite l'opzione di menu [Strumenti/Editor SQL] o premendo il tasto [F12]

Si aprirà una finestra [Editor SQL] in cui potremo digitare un comando SQL:

La schermata sopra riportata è spesso rappresentata dal testo sottostante:

SQL> select * from BIBLIO

3.1. Tipi di dati Firebird

Quando si crea una tabella, è necessario specificare il tipo di dati che una colonna della tabella può contenere. Qui presentiamo i tipi di dati Firebird più comuni. Si noti che questi tipi di dati possono variare da un DBMS all'altro.

`SMALLINT`	numero intero nell'intervallo [-32768, 32767]: 4
`INTEGER`	numero intero nell'intervallo [–2.147.483.648, 2.147.483.647]: -100
`NUMERICO(n,m) DECIMAL(n,m)`	numero reale con n cifre, di cui m decimali NUMERIC(5,2): -100,23, +027,30
`FLOAT`	numero reale approssimato a 7 cifre significative: 10,4
`DOPPIA PRECISIONE`	numero reale approssimato a 15 cifre significative: -100,89
`CHAR(N) CARATTERE(N)`	Una stringa di esattamente N caratteri. Se la stringa memorizzata ha meno di N caratteri, viene riempita con spazi. CHAR(10): 'ANGERS ' (4 spazi finali)
`VARCHAR(N) CARATTERE VARIABILE(N)`	stringa di massimo N caratteri VARCHAR(10): 'ANGERS'
`DATE`	una data: '2006-01-09' (formato AAAA-MM-GG)
`ORA`	un'ora: '16:43:00' (formato HH:MM:SS)
`TIMESTAMP`	sia data che ora: '2006-01-09 16:43:00' (formato AAAA-MM-GG HH:MM:SS)

La funzione CAST() consente di convertire un tipo in un altro quando necessario. Per convertire un valore V dichiarato come tipo T1 nel tipo T2, si scrive: CAST(V,T2). È possibile eseguire le seguenti conversioni di tipo:

da numero a stringa. Questa conversione di tipo è implicita e non richiede l'uso della funzione CAST. Pertanto, l'operazione 1 + '3' non richiede la conversione del carattere '3'. Il suo risultato è il numero 4.
DATE, TIME, TIMESTAMP in stringhe e viceversa. Pertanto
da TIMESTAMP a TIME o DATE e viceversa

In una tabella, una riga può avere colonne senza valore. Si dice che il valore della colonna è la costante NULL. È possibile verificare la presenza di questo valore utilizzando gli operatori

IS NULL / IS NOT NULL

3.2. Creazione di una tabella

Per imparare a creare una tabella, inizieremo creandone una in modalità [Design] con IBExpert. Per farlo, seguiremo il metodo descritto nella sezione 2.3. In questo modo verrà creata la seguente tabella:

Questa tabella verrà utilizzata per registrare i libri acquistati da una biblioteca. Il significato dei campi è il seguente:

Nome	Tipo	Vincolo	Significato
`ID`	`INTEGER`	`Chiave primaria`	`ID libro`
`TITOLO`	`VARCHAR(30)`	`NON NULLO UNICO`	`Titolo del libro`
`AUTORE`	`VARCHAR(20)`	`NON NULLO`	`Autore`
`GENERE`	`VARCHAR(30)`	`NON NULLO`	`Genere (Romanzo, Poesia, Giallo, Fumetto, ecc.)`
`ACQUISTA`	`DATA`	`NON NULLO`	`Data di acquisto del libro`
`PREZZO`	`NUMERICO(6,2)`	`NON NULLO`	`Prezzo`
`DISPONIBILE`	`CHAR(1)`	`NON NULLO`	`È disponibile? Y (sì), N (no)`

Questa tabella, creata utilizzando la procedura guidata IBEXPERT, avrebbe potuto essere creata direttamente tramite istruzioni SQL. Per visualizzarle, è sufficiente selezionare la scheda [DDL] della tabella:

Il codice SQL utilizzato per creare la tabella [BIBLIO] è il seguente:

SET SQL DIALECT 3;

SET NAMES ISO8859_1;


CREATE TABLE BIBLIO (
    ID INTEGER NOT NULL,
    TITRE VARCHAR(30) NOT NULL,
    AUTEUR VARCHAR(20) NOT NULL,
   GENRE VARCHAR(30) NOT NULL,
   ACHAT DATE NOT NULL,
   PRIX NUMERIC(6,2) NOT NULL,
   DISPONIBLE  CHAR(1) NOT NULL
);

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITRE);
ALTER TABLE BIBLIO ADD CONSTRAINT PK_BIBLIO PRIMARY KEY (ID);

riga 1: proprietario Firebird - indica il livello del dialetto SQL utilizzato
riga 2: specifico di Firebird - specifica il set di caratteri utilizzato
righe 6–14: standard SQL: crea la tabella BIBLIO definendo il nome e il tipo di dati di ciascuna delle sue colonne.
riga 16: standard SQL: crea un vincolo specificando che la colonna TITLE non ammette duplicati
riga 17: standard SQL: specifica che la colonna [ID] è la chiave primaria della tabella. Ciò significa che non possono esserci due righe nella tabella con lo stesso ID. Questo è simile al vincolo [UNIQUE NOT NULL] sulla colonna [TITLE] e, di fatto, la colonna TITLE avrebbe potuto fungere da chiave primaria. La tendenza attuale è quella di utilizzare chiavi primarie che non hanno un significato specifico e sono generate dal DBMS.

La sintassi del comando [CREATE TABLE] è la seguente:

sintassi

CREATE TABLE tabella (nome_colonna1 tipo_colonna1 vincolo_colonna1, nome_colonna2 tipo_colonna2 vincolo_colonna2, ..., nome_colonnaN tipo_colonnaN vincolo_colonnaN, altri vincoli)

azione

crea la tabella table con le colonne specificate

`nome_colonna_i`	nome della colonna i da creare
`tipo_colonna_i`	tipo di dati della colonna i: char(30) numeric(6,2) date timestamp ...
`vincolo_colonna_i`	Vincolo che i dati nella colonna i devono soddisfare. Ecco alcuni esempi: PRIMARY KEY: la colonna è una chiave primaria. Ciò significa che nessuna coppia di righe nella tabella può avere lo stesso valore in questa colonna e, inoltre, è richiesto un valore in questa colonna. Una chiave primaria viene utilizzata principalmente per identificare in modo univoco una riga. NOT NULL : nella colonna non sono ammessi valori nulli. UNIQUE : nessun valore può apparire più di una volta nella colonna. CHECK (condizione): il valore nella colonna deve soddisfare la condizione.
`altri vincoli`	Qui è possibile specificare - vincoli su più colonne: check(col1>col2) - vincoli di chiave esterna

La tabella [BIBLIO] avrebbe potuto essere creata anche con la seguente istruzione SQL:

CREATE TABLE BIBLIO (
    ID INTEGER NOT NULL PRIMARY KEY,
    TITRE VARCHAR(30) NOT NULL UNIQUE,
    AUTEUR VARCHAR(20) NOT NULL,
   GENRE VARCHAR(30) NOT NULL,
   ACHAT DATE NOT NULL,
   PRIX NUMERIC(6,2) NOT NULL,
   DISPONIBLE  CHAR(1) NOT NULL
);

Vediamo come funziona. Apriamo questa query in un editor SQL (F12) per creare una tabella che chiameremo [BIBLIO2]:

Dopo l'esecuzione, è necessario eseguire il commit della transazione per visualizzare il risultato nel database:

Una volta fatto ciò, la tabella appare nel database:

Facendo doppio clic sul suo nome, è possibile visualizzarne la struttura:

Possiamo vedere la definizione che abbiamo creato per la tabella [BIBLIO2]

3.3. Eliminazione di una tabella

L'istruzione SQL per eliminare una tabella è la seguente:

`sintassi`	DROP TABLE tabella
`azione`	Elimina [tabella]

Per eliminare la tabella [BIBLIO2] appena creata, ora eseguiamo il seguente comando SQL:

e confermiamo con [Commit]. La tabella [BIBLIO2] viene eliminata:

3.4. Compilazione di una tabella

Inseriamo una riga nella tabella [BIBLIO] appena creata:

Confermare l'aggiunta della riga con [Commit], quindi fare clic con il tasto destro del mouse sulla riga aggiunta:

e, come mostrato sopra, copia la riga inserita negli appunti come istruzione SQL INSERT. Successivamente, apri un editor di testo qualsiasi e incolla ciò che abbiamo appena copiato. Otteniamo il seguente codice SQL:

INSERT INTO BIBLIO (ID,TITRE,AUTEUR,GENRE,ACHAT,PRIX,DISPONIBLE) VALUES (1,'Candide','Voltaire','Essai','18-OCT-1985',140,'o');

La sintassi di un'istruzione SQL INSERT è la seguente:

`sintassi`	insert into tabella [(colonna1, colonna2, ..)] valori (valore1, valore2, ....)
`azione`	aggiunge una riga (valore1, valore2, ...) alla tabella. Questi valori vengono assegnati alle colonne colonna1, colonna2, ... se presenti; in caso contrario, alle colonne della tabella nell'ordine in cui sono state definite.

Per inserire nuove righe nella tabella [BIBLIO], digiteremo le seguenti istruzioni INSERT nell'editor SQL. Eseguiremo e confermeremo queste istruzioni una per una. Useremo il pulsante [Nuova query] per passare all'istruzione INSERT successiva.

insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (2,'Les fleurs du mal','Baudelaire','Poème','01-jan-78',120,'n');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (3,'Tintin au Tibet','Hergé','BD','10-nov-90',70,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (4,'Du côté de chez Swann','Proust','Roman','08-dec-78',200,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (5,'La terre','Zola','roman','12-jun-90',50,'n');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (6,'Madame Bovary','Flaubert','Roman','12-mar-88',130,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (7,'Manhattan transfer','Dos Passos','Roman','30-aug-87',320,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (8,'Tintin en Amérique','Hergé','BD','15-may-91',70,'o');

Dopo aver eseguito il commit [Commit] delle varie istruzioni SQL, otteniamo la seguente tabella:

3.5. Interrogazione di una tabella

3.5.1. Introduzione

Nell'editor SQL, digitare il seguente comando:

ed eseguirlo. Si ottiene il seguente risultato:

L'istruzione SELECT viene utilizzata per recuperare dati dalle tabelle del database. Questa istruzione ha una sintassi molto ricca. Qui ci concentreremo sulla sintassi per l'interrogazione di una singola tabella. Tratteremo l'interrogazione di più tabelle contemporaneamente in un secondo momento. La sintassi dell'istruzione SQL [SELECT] è la seguente:

sintassi

SELECT [ALL|DISTINCT] [*|espressione1 alias1, espressione2 alias2, ...]

FROM tabella

azione

visualizza i valori di espressione1 per tutte le righe della tabella. espressione1 può essere una colonna o un'espressione più complessa. Il simbolo * indica tutte le colonne. Per impostazione predefinita, vengono visualizzate tutte le righe della tabella (ALL). Se è presente DISTINCT, le righe selezionate identiche vengono visualizzate una sola volta. I valori di espressione1 vengono visualizzati in una colonna denominata espressione1 o alias1 se è stato utilizzato quest'ultimo.

Esempi:

SQL > select titre, auteur from biblio

SQL> select titre,prix from biblio

SQL> select titre TITRE_DU_LIVRE, prix PRIX_ACHAT from biblio

Negli esempi precedenti, abbiamo assegnato degli alias (TITOLO_DEL_LIBRO, PREZZO_DI_ACQUISTO) alle colonne richieste.

3.5.2. Visualizzazione delle righe che soddisfano una condizione

`sintassi`	SELECT .... WHERE condizione
`azione`	Vengono visualizzate solo le righe che soddisfano la condizione

Esempi

SQL> select titre,prix from biblio where prix>100

SQL> select titre,prix,genre from biblio where genre='Roman'

Uno dei libri ha il genere "romanzo" e non "Romanzo". Utilizziamo la funzione UPPER, che converte una stringa in maiuscolo, per ottenere tutti i romanzi.

SQL> select titre,prix,genre from biblio where upper(genre)='ROMAN'

Possiamo combinare le condizioni utilizzando gli operatori logici

`AND`	AND logico
`OR`	OR logico
`NOT`	Negazione logica

SQL> select titre,prix,genre from biblio where upper(genre)='ROMAN' and prix<100

SQL> select titre,genre from biblio

SQL> select titre,genre from biblio where upper(genre)='ROMAN' or upper(genre)='BD'

SQL> select titre,genre from biblio where not( upper(genre)='ROMAN' or upper(genre)='BD')

SQL> select titre,achat from biblio

SQL>select titre,achat from biblio where achat>'31-dec-1987'

SQL> select titre,prix from biblio where prix between 100 and 150

3.5.3. Visualizzazione delle righe in un ordine specifico

Alle sintassi precedenti è possibile aggiungere una clausola ORDER BY specificando l'ordine di visualizzazione desiderato:

`sintassi`	SELECT .... ORDER BY espressione1 [asc\|desc], espressione2 [asc\|desc], ...
`azione`	Le righe risultanti della selezione vengono visualizzate nell'ordine di 1: ordine crescente (asc / ascending, che è l'impostazione predefinita) o decrescente (desc / descending) di expression1 2: se l'espressione1 è uguale, la visualizzazione si basa sui valori dell'espressione2 ecc.

Esempi:

SQL>select titre, genre,prix,achat from biblio order by achat desc

SQL>select titre, genre,prix,achat from biblio order by prix

SQL>select titre, genre,prix,achat from biblio order by genre desc

SQL >select titre, genre,prix,achat from biblio order by genre desc, prix

SQL>select titre, genre,prix,achat from biblio order by genre desc, prix desc

3.6. Eliminazione di righe da una tabella

`sintassi`	DELETE FROM tabella [WHERE condizione]
`Azione`	Elimina le righe della tabella che soddisfano la condizione. Se non viene specificata alcuna condizione, vengono eliminate tutte le righe.

Esempi:

SQL> select titre from biblio

I due comandi riportati di seguito vengono eseguiti uno dopo l'altro:

SQL> delete from biblio where titre='Candide'

SQL> select titre from biblio

3.7. Modifica del contenuto di una tabella

`sintassi`	update table set colonna1 = espressione1, colonna2 = espressione2, ... [where condizione]
`azione`	Per le righe della tabella che soddisfano la condizione (tutte le righe se non c'è alcuna condizione), la colonna1 viene impostata sul valore dell'espressione1.

Esempi:

SQL> select genre from biblio

Scriviamo tutti i generi con l'iniziale maiuscola:

SQL> update biblio set genre=upper(genre)

Verifichiamo:

SQL> select genre from biblio

Visualizza i prezzi:

SQL> select genre,prix from biblio;

Il prezzo dei romanzi aumenta del 5%:

SQL> update biblio set prix=prix*1.05 where genre='ROMAN';

Verifichiamo:

SQL> select genre,prix from biblio

3.8. Aggiornamento permanente di una tabella

Quando vengono apportate modifiche a una tabella, Firebird le applica effettivamente a una copia della tabella. Queste modifiche possono quindi essere rese permanenti o annullate utilizzando i comandi COMMIT e ROLLBACK.

`Sintassi`	COMMIT
`azione`	Rende permanenti gli aggiornamenti apportati alle tabelle dall'ultimo COMMIT.

`sintassi`	ROLLBACK
`azione`	Annulla tutte le modifiche apportate alle tabelle dall'ultimo COMMIT.

Nota

Un COMMIT viene eseguito implicitamente nei seguenti casi:

a) Al momento della disconnessione da Firebird

b) Dopo ogni comando che modifica la struttura delle tabelle: CREATE, ALTER, DROP.

Esempi

Nell'editor SQL, è possibile ripristinare il database a uno stato noto eseguendo il commit di tutte le operazioni effettuate dall'ultimo COMMIT o ROLLBACK:

SQL> commit

Recuperiamo l'elenco dei titoli:

SQL> select titre from biblio

Eliminazione di un titolo:

SQL> delete from biblio where titre='La terre'

Verifica:

SQL> select titre from biblio

Il titolo è stato eliminato con successo. Ora annulleremo tutte le modifiche apportate dall'ultimo COMMIT / ROLLBACK:

SQL> rollback

Verifica:

SQL> select titre from biblio

Il titolo cancellato è ricomparso. Ora recuperiamo l'elenco dei prezzi:

SQL> select prix from biblio

Impostiamo tutti i prezzi a zero.

SQL> update biblio set prix=0

Controlliamo i prezzi:

SQL> select prix from biblio

Annulliamo le modifiche apportate al database:

SQL> rollback

e controlliamo nuovamente i prezzi:

SQL> select prix from biblio

Abbiamo ripristinato i prezzi originali.

3.9. Aggiunta di righe da una tabella a un'altra

È possibile aggiungere righe da una tabella a un'altra quando le loro strutture sono compatibili. Per dimostrarlo, iniziamo creando una tabella [BIBLIO2] con la stessa struttura di [BIBLIO].

Nell'esploratore del database di IBExpert, fare doppio clic sulla tabella [BIBLIO] per accedere alla scheda [DDL]:

In questa scheda troverai l'elenco delle istruzioni SQL utilizzate per generare la tabella [BIBLIO]. Copia tutto questo codice negli appunti (CTRL-A, CTRL-C). Quindi apri uno strumento chiamato [Script Executive] che ti permette di eseguire un elenco di istruzioni SQL:

Si aprirà un editor di testo, nel quale potremo incollare (CTRL-V) il testo precedentemente copiato negli appunti:

Un elenco di comandi SQL viene spesso chiamato script SQL. [Script Executive] ci consentirà di eseguire tale script, mentre l'editor SQL consentiva solo l'esecuzione di un singolo comando alla volta. L'attuale script SQL crea la tabella [BIBLIO]. Facciamo in modo che crei una tabella denominata [BIBLIO2]. Per farlo, basta semplicemente cambiare [BIBLIO] in [BIBLIO2]:

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE TABLE BIBLIO2 (
    ID          INTEGER NOT NULL,
    TITRE       VARCHAR(30) NOT NULL,
    AUTEUR      VARCHAR(20) NOT NULL,
    GENRE       VARCHAR(20) NOT NULL,
    ACHAT       DATE NOT NULL,
    PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
    DISPONIBLE  CHAR(1) NOT NULL
);

ALTER TABLE BIBLIO2 ADD CONSTRAINT UNQ1_BIBLIIO2 UNIQUE (TITRE);

ALTER TABLE BIBLIO2 ADD CONSTRAINT PK_BIBLIIO2 PRIMARY KEY (ID);

Eseguiamo questo script utilizzando il pulsante [Esegui script] qui sotto:

Lo script viene eseguito:

e possiamo vedere la nuova tabella nell'esploratore del database:

Se facciamo doppio clic su [BIBLIO2] per verificarne il contenuto, vediamo che è vuota, il che è normale:

Una variante dell'istruzione SQL INSERT consente di inserire righe da una tabella in un'altra:

`sintassi`	INSERT INTO tabella1 [(colonna1, colonna2, ...)] SELECT colonna1, colonna2, ... FROM tabella2 WHERE condizione
`azione`	Le righe della tabella2 che soddisfano la condizione vengono aggiunte alla tabella1. Le colonne colonna1, colonna2, ... della tabella2 vengono assegnate in ordine a colonna1, colonna2, ... nella tabella1 e devono quindi essere di tipi compatibili.

Torniamo all'editor SQL:

ed eseguiamo la seguente istruzione SQL:

SQL> insert into BIBLIO2 select * from BIBLIO where upper(genre)='ROMAN'

che inserisce in [BIBLIO2] tutte le righe di [BIBLIO] corrispondenti a un romanzo. Dopo aver eseguito l'istruzione SQL, confermiamo con un [Commit]:

SQL> commit

Ora visualizziamo i dati nella tabella [BIBLIO2]:

SQL> select * from BIBLIO2

3.10. Eliminazione di una tabella

`sintassi`	DROP TABLE tabella
`azione`	elimina la tabella

Esempio: eliminazione della tabella BIBLIO2

SQL> drop table BIBLIO2

Conferma la modifica:

SQL> commit

Nell'esploratore del database, aggiorna la visualizzazione della tabella:

Si nota che la tabella [BIBLIO2] è stata eliminata:

3.11. Modifica della struttura di una tabella

`Sintassi`	ALTER TABLE tabella [ ADD nome_colonna1 tipo_colonna1 vincolo_colonna1] [ALTER nome_colonna2 TYPE tipo_colonna2] [DROP nome_colonna3] [ADD vincolo] [DROP CONSTRAINT nome_vincolo]
`azione`	consente di aggiungere (ADD), modificare (ALTER) ed eliminare (DROP) colonne di una tabella. La sintassi column_name1 column_type1 column_constraint1 è la stessa di quella di CREATE TABLE. È inoltre possibile aggiungere o eliminare vincoli di tabella.

Esempio: eseguire in sequenza i seguenti due comandi SQL nell'editor SQL

SQL > alter table biblio add nb_pages numeric(4), alter genre type varchar(30)

SQL> commit

Nell'esploratore del database, controlliamo la struttura della tabella [BIBLIO]:

Le modifiche sono state applicate. Vediamo come è cambiato il contenuto della tabella:

SQL> select * from biblio

La nuova colonna [NB_PAGES] è stata creata ma non contiene valori. Eliminiamo questa colonna:

SQL> alter table biblio drop nb_pages

SQL> commit

Verifichiamo la nuova struttura della tabella [BIBLIO]:

La colonna [NB_PAGES] è effettivamente scomparsa.

3.12. Viste

È possibile avere una vista parziale di una tabella o di più tabelle. Una vista si comporta come una tabella ma non contiene dati. I suoi dati vengono estratti da altre tabelle o viste. Una vista presenta diversi vantaggi:

Un utente potrebbe essere interessato solo a determinate colonne e righe di una data tabella. La vista gli consente di visualizzare solo quelle righe e colonne.
Il proprietario di una tabella potrebbe voler concedere un accesso limitato agli altri utenti. Una vista gli permette di farlo. Gli utenti da lui autorizzati avranno accesso solo alla vista che ha definito.

3.12.1. Creazione di una vista

`sintassi`	CREATE VIEW nome_vista AS SELECT colonna1, colonna2, ... FROM tabella WHERE condizione [ WITH CHECK OPTION ]
`azione`	crea la vista view_name. Si tratta di una tabella con la struttura colonna1, colonna2, ... proveniente dalla tabella e, per le righe, le righe della tabella che soddisfano la condizione (tutte le righe se non è specificata alcuna condizione)
`WITH CHECK OPTION`	Questa clausola opzionale specifica che gli inserimenti e gli aggiornamenti alla vista non devono creare righe che la vista non potrebbe selezionare.

Nota La sintassi di CREATE VIEW è in realtà più complessa di quella presentata sopra e consente, in particolare, la creazione di una vista da più tabelle. Per farlo, l'istruzione SELECT deve semplicemente fare riferimento a più tabelle (vedere il capitolo seguente).

Esempi

Creiamo una vista dalla tabella biblio che includa solo i romanzi (selezione di righe) e solo le colonne titolo, autore e prezzo (selezione di colonne):

SQL> create view romans as select titre,auteur,prix from biblio where upper(genre)='ROMAN';

SQL> commit

Nell'esploratore del database, aggiorna la vista (F5). Viene visualizzata una vista:

È possibile visualizzare l'istruzione SQL associata alla vista. A tal fine, fare doppio clic sulla vista [ROMANS]:

Una vista è simile a una tabella. Ha una struttura:

e un contenuto:

Una vista si usa come una tabella. È possibile eseguire query SQL su di essa. Ecco alcuni esempi da provare nell'editor SQL:

SQL> select * from romans

SQL> insert into biblio values (10,'Le père Goriot','Balzac','Roman','01-sep-91',200,'o')

Il nuovo romanzo è visibile nella vista [ROMANS]?

SQL> select * from romans

Aggiungiamo qualcosa di diverso da un romanzo alla tabella [BIBLIO]:

SQL> insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (11,'Poèmes saturniens','Verlaine','Poème','02-sep-92',200,'o');

Controlliamo la tabella [BIBLIO]:

SQL> select titre, auteur from BIBLIO

Controlliamo la vista [ROMANS]:

SQL> select titre, auteur from ROMANS

Il libro aggiunto non è presente nella vista [ROMANS] perché non aveva upper(genre)='ROMAN'.

3.12.2. Aggiornamento di una vista

È possibile aggiornare una vista proprio come si farebbe con una tabella. Tutte le tabelle da cui vengono estratti i dati della vista sono interessate da questo aggiornamento. Ecco alcuni esempi:

SQL> insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (13,'Le Rouge et le Noir','Stendhal','Roman','03-oct-92',110,'o')

SQL> select * from romans

SQL> select titre, auteur from biblio

Eliminiamo una riga dalla vista [ROMANS]:

SQL> delete from ROMANS where titre='Le Rouge et le Noir'

SQL> select * from romans

SQL> select auteur, titre from BIBLIO

La riga eliminata dalla vista [NOVELS] è stata eliminata anche dalla tabella [BIBLIO]. Ora aumenteremo il prezzo dei libri nella vista [NOVELS]:

SQL> update romans set prix=prix*1.05

Controlliamo in [NOVELS]:

SQL> select * from romans

Qual è stato l'impatto sulla tabella [BIBLIO]?

SQL> select titre, auteur, prix from biblio

I prezzi dei romanzi sono stati effettivamente aumentati del 5% anche in [BIBLIO].

3.12.3. Eliminazione di una vista

`sintassi`	DROP VIEW nome_vista
`azione`	elimina la vista denominata

Esempio

SQL> drop view romans

SQL> commit

Nell'esploratore del database, è possibile aggiornare la vista (F5) per verificare che la vista [ROMANS] sia scomparsa:

3.13. Utilizzo delle funzioni di gruppo

Esistono funzioni che, invece di operare su ogni riga di una tabella, operano su gruppi di righe. Si tratta essenzialmente di funzioni statistiche che ci consentono di calcolare la media, la deviazione standard, ecc., dei dati in una colonna.

`sintassi1`	SELECT f1, f2, .., fn FROM tabella [ WHERE condizione ]
`azione`	Calcola le funzioni statistiche fi su tutte le righe della tabella che soddisfano la condizione.

sintassi2

SELECT f1, f2, .., fn FROM tabella

[ WHERE condizione ]

[ GROUP BY espr1, espr2, ..]

azione

La parola chiave GROUP BY divide le righe della tabella in gruppi. Ogni gruppo contiene le righe per le quali le espressioni expr1, expr2, ... hanno lo stesso valore.

Esempio: GROUP BY genere raggruppa i libri dello stesso genere. La clausola GROUP BY autore,genere raggrupperebbe i libri con lo stesso autore e lo stesso genere. La condizione WHERE rimuove innanzitutto dalla tabella le righe che non soddisfano la condizione. Successivamente, i gruppi vengono formati dalla clausola GROUP BY. Le funzioni di aggregazione vengono quindi calcolate per ciascun gruppo di righe.

`sintassi3`	SELECT f1, f2, ..., fn FROM tabella [ WHERE condizione ] [ GROUP BY espressione] [ AVENDO group_condition]
`azione`	La clausola HAVING filtra i gruppi formati dalla clausola GROUP BY. È quindi sempre associata alla presenza della clausola GROUP BY. Esempio: GROUP BY genere HAVING genere!='ROMANZO'

Le funzioni statistiche disponibili sono le seguenti:

`AVG(espressione)`	media dell'espressione
`COUNT(espressione)`	numero di righe per le quali l'espressione ha un valore
`COUNT(*)`	numero totale di righe nella tabella
`MAX(espressione)`	Valore massimo dell'espressione
`MIN(espressione)`	minimo dell'espressione
`SUM(espressione)`	somma dell'espressione

Esempi

SQL> select prix from biblio

Prezzo medio? Prezzo massimo? Prezzo minimo?

SQL> select avg(prix), max(prix), min (prix) from biblio

SQL> select titre, prix,genre from biblio

Prezzo medio di un romanzo? Prezzo massimo?

SQL> select avg(prix) moyenne, max(prix) prix_maxi from biblio where upper(genre)='ROMAN'

Quanti fumetti?

SQL> select count(*) from biblio where upper(genre)='BD'

Quanti romanzi costano meno di 100 F?

SQL> select count(*) from biblio where upper(genre)='ROMAN' and prix<100

SQL> select genre, prix from biblio

Numero di libri e prezzo medio per libro per i libri dello stesso genere?

SQL> select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE from biblio group by upper(genre)

Stessa domanda, ma solo per i libri che non sono romanzi:

SQL>
select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
group by upper(genre)
having upper(GENRE)!='ROMAN'

Stessa query, ma solo per i libri con codice F inferiore a 150:

SQL> 
select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
where prix<150
group by upper(genre)
having upper(GENRE)!='ROMAN'

Stessa query, ma manteniamo solo i gruppi con un prezzo medio dei libri >100 F

SQL> 
select upper(genre) GENRE, avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
group by upper(genre)
having avg(prix)>100

3.14. Creazione di uno script SQL per una tabella

SQL è un linguaggio standard che può essere utilizzato con molti DBMS. Per poter passare da un DBMS a un altro, è utile esportare un database o semplicemente alcuni suoi elementi sotto forma di script SQL che, una volta rieseguito in un altro DBMS, sarà in grado di ricreare gli elementi esportati nello script.

In questo caso, esporteremo la tabella [BIBLIO]. Selezioniamo l'opzione [Estrai metadati]:

Si noti che è necessario trovarsi nel database da cui si desidera esportare gli elementi. L'opzione avvia una procedura guidata:

`1`	dove generare lo script SQL: in un file (File) negli Appunti nello strumento Script Executive
`2`	nome del file se è selezionata l'opzione [File]
`3`	cosa esportare
`4`	Pulsanti per selezionare (->) o deselezionare (<-) gli oggetti da esportare

Se volessimo esportare l'intero database, selezioneremmo l'opzione [Estrai tutto] in alto. Vogliamo semplicemente esportare la tabella BIBLIO. Per farlo, utilizzando [4], selezioniamo la tabella [BIBLIO] e, utilizzando [2], specifichiamo un file:

Se ci fermiamo qui, verrà esportata solo la struttura della tabella [BIBLIO]. Per esportarne il contenuto, dobbiamo utilizzare la scheda [Data Tables]:

Utilizzare [1] per selezionare la tabella [BIBLIO]:

Utilizza [2] per generare lo script SQL:

Accettiamo la richiesta. Questo ci permette di visualizzare lo script generato nel file [biblio.sql]:

/******************************************************************************/
/****         Generated by IBExpert 2004.06.17 22/01/2006 15:06:13         ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE DATABASE 'D:\data\serge\travail\2005-2006\polys\sql\DBBIBLIO.GDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET ISO8859_1;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE BIBLIO (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      VARCHAR(20) NOT NULL,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 'Baudelaire', 'POèME', '1978-01-01', 120, 'n');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 'Hergé', 'BD', '1990-11-10', 70, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 'Proust', 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 'Zola', 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 'Flaubert', 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 'Dos Passos', 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 'Hergé', 'BD', '1991-05-15', 70, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 'Balzac', 'Roman', '1991-09-01', 210, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 'Verlaine', 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                          Unique Constraints                          ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITRE);


/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT PK_BIBLIO PRIMARY KEY (ID);

Le righe da 1 a 3 sono commenti
Le righe da 5 a 12 sono SQL specifico per Firebird
le righe rimanenti sono SQL standard che dovrebbe essere eseguibile in un DBMS che supporti i tipi di dati dichiarati nella tabella BIBLIO.

Eseguiamo questo script in Firebird per creare una tabella BIBLIO2 che sarà un clone della tabella BIBLIO. Per farlo, usa [Esegui script] (Ctrl-F12):

Carichiamo lo script [biblio.sql] che abbiamo appena generato:

Modifichiamolo in modo da mantenere solo le parti relative alla creazione della tabella e all'inserimento delle righe. La tabella viene rinominata [BIBLIO2]:

CREATE TABLE BIBLIO2 (
    ID          INTEGER NOT NULL,
    TITRE       VARCHAR(30) NOT NULL,
    AUTEUR      VARCHAR(20) NOT NULL,
    GENRE       VARCHAR(30) NOT NULL,
    ACHAT       DATE NOT NULL,
    PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
    DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO BIBLIO2 (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 'Baudelaire', 'POèME', '1978-01-01', 120, 'n');
...

COMMIT WORK;

Eseguiamo questo script:

Possiamo verificare nell'esploratore del database che la tabella [BIBLIO2] sia stata creata e che abbia la struttura e il contenuto previsti: