APPUNTI DI SQL
Gli appunti qui forniti vogliono essere un riferimento scritto di alcuni degli argomenti trattati a lezione per
gli studenti a cui vengono messi a disposizione. Non viene fornita alcuna garanzia di completezza
sull’argomento trattato, essendo solo appunti informali.
Il libro di riferimento, a cui spesso si rimanda per eventuali riferimenti e approfondimenti, è “Basi Di Dati –
modelli e linguaggi di interrogazione ; McGraw-Hill; terza edizione”.
Nelle prossima trattazione, ed in particolare della stesura delle query sql, spesso si utilizzano caratteri
upper case ed altre volte no. SQL non è case sensitive, pertanto l’utilizzo di un carattere piccolo o grande
non influenza il risultato, e tuttavia la differenza mostrata nelle presente stesura è la semplice, volta per
volta, messa in evidenza.
SQL (structured query language, oggi nome a se stante)
DDL (Data Definition Language)
DML (Data Manipolation Language)
DATI:
atomici
strutturati
DATI ATOMICI:
character [varyng] [(lunghezza)]
char; varchar
bit [varyng] [(lunghezza)]
---------------------------------------------------------------------------decimal [(precisione [,scala] )]
scala: quante cifre significative devono sparire
numeric [(precisione [,scala] )]
scala: quante cifre significative devono sparire
integer
smallint
---------------------------------------------------------------------------float [(precisione)]
real
precisione: cifre per la mantissa
precisione fissa
double precision precisione doppia alla precedente
---------------------------------------------------------------------------Mercogliano Isidoro - Appunti di SQL -Bozza n°3
- pagina: 1
date
data senza ora
time [(precisione)] [with time zone]
precisione: per la frazione del secondo
timestamp [(precisione)] [with time zone]
precisione: per la frazione del secondo
---------------------------------------------------------------------------boolean
blob
clob
---------------------------------------------------------------------------NULL: indica assenza di valore
VARI
create scheme NOMEDB; (crea il database)
show databases; (mostra tutti I database esistenti)
use NOMEDB; (imposta il database da utilizzare)
show tables; (mostra tutte le tabelle si un database)
CREAZIONE TABELLA
CREATE TABLE <tabella> (
<campo> <tipo> [ NULL | NOT NULL ] [ <chiave> ] [ <vincolo> ],
<campo> <tipo> [ NULL | NOT NULL ] [ <chiave> ] [ <vincolo> ],
...
)
create table nome_tabella(
nome_attributo dominio [valore di default] [vincoli]
, etc..
altri vincoli
)
Mercogliano Isidoro - Appunti di SQL -Bozza n°3
- pagina: 2
Es:
create table nominativi(
cognome varchar(256),
nome varchar(256)
);
L’opzione “DEFAULT <valore | NULL >” , inserita subito dopo la definizione del dominio, permette di
definire valori di default.
Vincoli
La violazione di un vincolo, usualmente (vedasi vincoli inter-relazionali), non permette l’operazione.
Vincoli intrarelazionali
NOT NULL
Impossibilità di assumere valore NULL.
es: nome varchar(20) NOT NULL
UNIQUE (impostazione di una superchiave)
Impossibilità di assumere valori uguali ad uno o più attributi. I valori null sono assunti tutti diversi tra loro.
Due modi:
-
su un solo attributo: si specifica l’attributo seguito dalla parola unique
-
su più attributi: dopo aver definito tutti gli attributi della tabella, si elencano quelli a cui fornire il
vincolo: unique(attr1, attr…)
PRIMARY KEY
È come unique ma implica anche il vincolo NOT NULL
Definizione identica a unique.
Vincoli inter-relazionali
(foreigni key = chiave esterna)
Crea un legame tra la tabella interna, il cui valore deve essere diverso da null, e quella esterna su un
attributo con vincolo unique o primary key.
Mercogliano Isidoro - Appunti di SQL -Bozza n°3
- pagina: 3
Brevemente:
FOREIGN KEY (attr1)
REFERENCES tabella_esterna(attr)
Completo:
CONSTRAINT nome_vincolo (rigo opzionale e dipendente dall’implementazione)
FOREIGN KEY (attr1)
REFERENCES tabella_esterna(attr)
On <delete | update>
<cascade | set null | set default | no action>
VIOLAZIONE:
Violazioni del vincolo della tabella interna (che viene vista come subordinata) non vengono permesse.
Violazioni del vincolo (modifica o cancellazione) della tabella esterna (master) :
-
cascade: il nuovo valore dell’attributo della tabella esterna viene riportato su ttutte le
corrispondenti della tabella interna.
-
Set null: all’attributo referente viene assegnato NULL
-
Set default: all’attributo referente viene assegnato il valore di default
-
No action: l’azione non viene esegiuta
Modifica delle relazioni
Vedi pag. 108
Interrogazioni
Vengono esaminate dal query optimizer per la loro ottimizzazione.
Query base:
select lista_attributi
from nome_tabella
[where condizioni]
Query base:
select Attr [ [as] alias ] {, Attr [ [as] alias ]}
from nome_tabella [[as] alias ] {, nome_tabella [[as] alias ]}
[where condizioni]
WHERE ammette un’espressione booleana tramite gli operatori:
-
And; or; not
Mercogliano Isidoro - Appunti di SQL -Bozza n°3
- pagina: 4
-
=; <>(diverso); >; <; <=; >=
-
ordine di precedenza a mezzo di parentesi
-
like (confronto tra stringhe: vedi dopo)
LIKE:
utilizzato nella clausola where con due caratteri speciali:
“_”: carattere arbitrario
“%”: stringa arbitraria di lunghezza arbitraria (eventualmente nulla)
Esempio:
where cognome like ‘_o%i’
DISTINCT:
Nelle selezioni può facilmente accadere che il risultato presenti dei duplicati. Ove non siano richiesti, è
possibile rimuoverli tramite la clausola “DISTINCT”, immediatamente dopo “select”, il cui opposto è “ALL”,
valore di default.
Esempio di utilizzo:
select [ DISTINCT | ALL ] attr, from…
JOIN INTERNI ED ESTERNI:
Tipicamente è possibile realizzare un join con una select su due o più tabelle.
Es:
select *
from tab1, tab2
where tab1.attr1=tab2.attr2
Lo standard sql mette a disposizione i seguenti costrutti.
select Attr [ [as] alias ] {, Attr [ [as] alias ]}
from nome_tabella [[as] alias ]
{ [TIPOJOIN] join nome_tabella [[as] alias ] on Condizione_di_join}
[where alter_condizioni]
Tipo di join:
-
inner
-
left outer
Mercogliano Isidoro - Appunti di SQL -Bozza n°3
- pagina: 5
-
right outer
-
full outer
L'inner, è il join interno.
Il left outer join: fornisce il risultato esteso con le righe della tabella a sinistra.
In modo analogo si definisce il comportamento degli altri.
USO DI VARIABILI:
Tramite l’utilizzo degli alias vengono a crearsi delle variabili il cui utilizzo non si limita ad una semplice
ridenominazione: è infatti possibile riferirsi, per esempio, alla stessa tabella con due variabili differenti le
quali rappresentano due istanze della tabella stessa.
Es:
..
from Tab1 as A1, Tab1 as A2,
…
Ancora una volta ne viene a guadagnare l’espressività generale.
Nelle selezioni nidificate (non ancora esaminate) vale lo scope per le variabili.
ORDINAMENTI:
è possibile specificare un ordinamento delle righe delle selezioni terminando la selezione in questo modo:
…
ORDER BY attr_di_ordinamento [ ASC | DESC ]
{, attr_di_ordinamento [ ASC | DESC ]}
ASC (ascendente) è il valore di default. DESC è l’opposto.
Operatori aggregati
(estendono l’algebra relazionale)
Sono operatori che agiscono non più su una tupla alla volta, bensì su insiemi di tuple.
In prima analisi vediamo l’operatore count(*), il quale conta il numero di righe.
Utilizzo:
select count(*)
from tabella
where condizione
Nella risoluzione della query vengono esaminate prima le ultime due righe, ed il risultato viene passato
all’operatore.
Mercogliano Isidoro - Appunti di SQL -Bozza n°3
- pagina: 6
Nello standard SQL ci sono 5 operatori:
count (< *| [distinct|all] lista_attributi > )
< sum | max | min | avg > (< [distinct|all] lista_attributi > )
Sum e avg ammettono espressioni che contengono valori numerici o intervalli di tempo. Max e min
ammettono espressioni su cui sia definito un ordinamento.
Rimane ovvio il significato degli operatori (deducibile dal nome).
Le varie implementazioni sql spesso offrono ulteriori operatori.
Gli operatori non possono essere utilizzati per condizionare le select (cioè utilizzati dopo il where).
Raggruppamenti
È possibile definire un ordinamento delle righe tramite l’inserimento in coda della clausola:
order by attr.
Gli operatori precedentemente introdotti, possono in questo modo agire su gruppo di tuple.
Esiste una limitazione di sintassi: vedi pag 131.
PREDICATI SUI GRUPPI:
per effettuare una selezione sui gruppi si utilizza la clausola:
having …
vedi pag 133
L’utilizzo di questo costrutto senza il precedente order by, tratta le tuple come un unico gruppo.
Interrogazioni di tipo sistemistico
selectSQL
<union | intersec | except > [all]
selectSQL
except sta per “differenza”.
[all] va inserito in quanto questa volta il valore di defaul, quello utilizzato in caso in cui non sia inserito
alcunché, è distinct (che toglie tutti i valori uguali).
Mercogliano Isidoro - Appunti di SQL -Bozza n°3
- pagina: 7
Interrogazioni nidificate
Viene inserita una interrogazione (select) in una clausola where di un’altra interrogazione.
Select..
From…
where attr operatore any|all ( selectSQL )
Dove:
Operatore: =; <>(diverso); >; <; <=; >=
Any: la condizione deve essere verificata per almeno una tupla
All: la condizione deve essere verificata per tutte le tuple
I domini devono essere confrontabili
Per rappresentare il controllo di appartenenza o esclusione rispetto ad un’insieme, SQL mette a
disposizione due operatori “in” e “not in”, i quali risultano del tutto identici a “= any” e “<> all”.
Interrogazioni nidificate complesse
Pag140
Inserimento
(dei dati nelle tabelle)
insert into nome_tabella (attr.1,attr.2,..) values (‘val1’,val2,..);
oppure
insert into nome_tabella ( selectSQL )
nell’ultimo caso serve la compatibilità tra i domini.
Cancellazione di righe
Delete from nome_tabella
where condizione;
condizione: rispetta la sintassi delle select; può valutare anche select nidificate.
Cancellazione di tabelle
Drop table nome_tabella cascade | restrict
Cascade: elimina anche le viste e le tabelle che nella loro definizione fanno riferimento alla tabella
Mercogliano Isidoro - Appunti di SQL -Bozza n°3
- pagina: 8
Restrict: se vi sono viste o tabelle che vi fanno riferimento, allora la cancellazione fallisce.
Modifica dei dati
Update nome_tabella
set attr = <espressione | selectSQL | NULL | defaul >
{, attr = <espressione | selectSQL | NULL | defaul >}
[where condizione]
Es:
Update alunno
set anno =3
where id=1;
Vincoli di integrità generici
Tutti i vincoli si possono anche in un altro modo, peraltro più espressivo, e cioè tramite la clausola:
check ( condizione )
applicato al seguito della definizione di ciascun attributo. Tutti gli altri vincoli possono essere specificati in
questo modo, il quale è però meno pratico rispetto ai vincoli su visti. Inoltre di perde la possibilità di
definire una politica di reazione.
Asserzioni
Sono vincoli definiti tramite la clausola check ed associati allo schema.
Create assertion nome_asserzione
Check (condizione)
Politiche di controllo
Il controllo dei vincoli può essere
-
immediato
-
differito (da controllare alla fine di una transazione)
Mercogliano Isidoro - Appunti di SQL -Bozza n°3
- pagina: 9
Viste
Vedi pag. 160
Funzioni scalari
Vedi pag. 165
Controllo dell’accesso
SQL permette di specificare chi opera, su cosa opera e come.
Si basa sul concetto di privilegio. Ogni privilegio è caratterizzato da:
1- la risorsa a cui si riferisce
2- l’utente che concede il privilegio
3- l’utente che riceve il privilegio
4- l’azione che viene permessa alla risorsa
5- se il privilegio può essere trasmesso o meno ad altri utenti
Quando una risorsa viene creata, il sistema concede automaticamente tutti i privilegi su tale risorsa al
creatore. Esiste inoltre un utente predefinito (_system) che rappresenta il database administrator, il quale
possiede tutti i privilegi su tutte le risorse.
I privilegi disponibili sono:
-
insert
-
update
-
delete
-
select
-
references (per fornire l’autorizzazione ad effettuare un riferimento)
-
usage
drop ed alter possono essere concessi ma rimangono prerogativa del creatore.
Concedere privilegi:
grant Privilegi on Risorsa to Utente [with grant option]
Privilegi: se tutti allora “all privileges”
with grant option: indica se il privilegio può essere propagato
Mercogliano Isidoro - Appunti di SQL -Bozza n°3
- pagina: 10
Rimuovere privilegio:
revoke Privilegi on Risorsa from Utente [restrict | cascade]
Nei privilegi si può anche specificare “grant option”.
Nella revoca dei privilegi può nascere un problema quando l’utente aveva concesso, a sua volta, altri
privilegi. In questo caso, il comportamento è specificato secondo i valori:
-
restrict: è il valore di default, ed indica di non eseguire la revoca qualora li si debba revoca anche ad
altri utenti
-
cascade: li toglie a tutti
SQL-3 ha introdotto l’accesso basato sui ruoli.
Transazioni
Start transaction
…
…
< commit | rollnack > work
Procedure
Sql permette procedure molto molto semplici.
Trigger
Detti anche regole attive, seguono il paradigma evento-condizione-azione (ECA).
Ogni trigger si attiva dopo uno specifico evento e se si verifica una condizione, eseguono un’azione.
Documento redatto da Mercogliano Isidoro con diritti di cui art. 2576 c.c.
Mercogliano Isidoro - Appunti di SQL -Bozza n°3
- pagina: 11
