SQL

Il nome SQL sta per Structured Query Language. Più che un semplice linguaggio di query: si compone di una parte DDL e di una DML.
– DDL: definizione di domini, tabelle, indici, autorizzazioni, viste, vincoli, procedure, trigger
– DML: linguaggio di query, linguaggio di modifica, comandi transazionali
Storia:
– Prima proposta: SEQUEL (IBM Research, 1974)
– Prima implementazione commerciale in SQL/DS (IBM, 1981)

Definizione di schemi in SQL

• Uno schema è una collezione di oggetti:
– domini, tabelle, indici, asserzioni, viste, privilegi
• Uno schema ha un nome e un proprietario
• Sintassi:
create schema [ NomeSchema ]
[ [ authorization ] Autorizzazione ]
{ DefinizioneElementoSchema }

Domini

• I domini specificano i valori ammissibili per gli attributi
– Simili ai meccanismi di definizione dei tipi dei linguaggi di programmazione
• Due categorie
– Elementari (predefiniti dallo standard, elementary o built‐in)
– Definiti dall’utente (user‐defined).
Domini elementari
Dati numerici
• BIT[(M)]
• TINYINT[(M)] [UNSIGNED] [ZEROFILL]
• SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
• MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
• INT[(M)] [UNSIGNED] [ZEROFILL]
• BIGINT[(M)] [UNSIGNED] [ZEROFILL]
• FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
• DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
• DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

Domini elementari

Le indicazioni comprese fra parentesi quadre sono opzionali. Come vedete, tutti i dati numerici escluso il BIT possono avere le opzioni UNSIGNED e ZEROFILL. Con la prima si specifica che il numero è senza segno, per cui non saranno consentiti valori negativi. Con la seconda si indica al server di memorizzare i numeri con degli zeri davanti nel caso in cui la lunghezza sia inferiore a quella massima prevista. Se usate ZEROFILL MySQL aggiungerà automaticamente UNSIGNED

Domini elementari

Il dato di tipo BIT è un dato che contiene il numero di bit specificato con M (1 per default), che può andare da 1 a 64. Nelle versioni precedenti era considerato sinonimo di TINYINT(1). Un valore di questo tipo può essere indicato ad es. con ‘111’, che rappresenta in questo caso tre bit a 1 (corrispondenti al valore decimale 7).

Domini elementari
I dati di tipo TINYINT, SMALLINT, MEDIUMINT, INT e BIGINT rappresentano numeri interi composti rispettivamente da 1, 2, 3, 4 e 8 bytes. Il TINYINT può contenere 256 valori, che vanno da ‐128 a +127 oppure da 0 a 255 nel caso di UNSIGNED. Allo stesso modo, SMALLINT può contenere 65536 valori, MEDIUMINT 16.777.216, INT oltre 4 miliardi, BIGINT circa 18 miliardi di miliardi.

Domini elementari

FLOAT e DOUBLE rappresentano i numeri in virgola mobile. M rappresenta il numero totale di cifre rappresentate pp e D il numero di cifre decimali.
FLOAT è a “precisione singola”: i suoi limiti teorici vanno da ‐3.402823466E+38 a ‐1.175494351E‐38 e da 1.175494351E‐38 a 3.402823466E+38, oltre allo zero.
I valori DOUBLE sono invece a “precisione doppia”: i limiti teorici sono da ‐1.7976931348623157E+308  a ‐2.2250738585072014E‐308 e da 2.2250738585072014E‐308 a 1.7976931348623157E+308, oltre allo zero.
Per entrambi dall’hardware i tipi di dato i limiti reali dipendono dall hardware e dal sistema operativo. Se M e D non sono indicati i valori possono essere memorizzati fino ai limiti effettivi. Per questi dati l’uso di UNSIGNED disabilita i valori negativi, ma non ha effetto sui valori massimi positivi memorizzabili. La precisione dei numeri in virgola mobile è affidabile fino (circa) alla settima cifra decimale per i FLOAT e alla quindicesima per i DOUBLE. Una colonna FLOAT occupa 4 byte, una DOUBLE ne occupa 8 .

Domini elementari

• I dati DECIMAL rappresentano infine numeri “esatti”, con M cifre totali di cui D decimali. I valori di default sono 10 per M e 0 per D.
• I valori limite per questi dati sono gli stessi di DOUBLE. Il massimo di cifre consentite è 65 per M e 30 per D.

Domini elementari
Date e tempo
• DATE
• DATETIME
• TIMESTAMP[(M)]
• TIME
• YEAR[(2|4)]

Domini elementari

• DATE
– Una colonna DATE può contenere date da ‘1000‐01‐01’ (1° gennaio 1000) a ‘9999‐12‐31’ (31 dicembre 9999). MySQL visualizza le date nel formato che vi abbiamo appena mostrato, ma vi consente di inserirle sotto forma di stringhe o numeri.

Domini elementari

• DATETIME
– Una colonna DATETIME contiene una data e un’ora, con lo stesso range visto per DATE. La visualizzazione è nel formato ‘AAAA‐MM‐GG HH:MM:SS’, ma anche in questo caso possono essere usati formati diversi per l’inserimento.
– Prima di MySQL 5.0.2 era sempre possibile inserire date o datetime a 0, oppure valorizzare a zero il giorno (o il giorno e mese) di una data. Era anche possibile indicare date non valide (ad es. ‘1999‐04‐31’). A partire da MySQL 5.0.2 questi comportamenti sono controllati da alcuni valori di SQL mode:
• ALLOW_INVALID_DATES è necessario per consentire date non valide: in sua assenza, le date non valide in strict mode provocheranno un errore; senza strict mode verranno convertite a con 0 un warning;
• NO_ZERO_DATE non accetta date a 0 (‘0000‐00‐00’): in strict mode verrà causato un errore a meno che non sia usata IGNORE; senza strict mode saranno comunque accettate con un warning;
• NO_ZERO_IN_DATE non accetta valori 0 per giorno e mese: in strict mode verrà generato errore, oppure inserita una data a 0 con IGNORE; senza strict mode saranno accettati con unwarning

Domini elementari

• TIMESTAMP
– In un TIMESTAMP possono essere memorizzati i valori corrispondenti al timestamp Unix, che vanno dalla mezzanotte del 1° gennaio 1970 ad un momento imprecisato dell’anno 2037.
– Questo tipo di dato è utile per memorizzare automaticamente il momento dell’aggiornamento di una riga di tabella: infatti MySQL può impostare in automatico una colonna TIMESTAMP di una tabella nel momento in cui viene effettuata una INSERT o un UPDATE. La visualizzazione del timestamp avviene nello stesso formato del DATETIME.

Domini elementari

• TIMESTAMP
– Vediamo la possibile definizione di una colonna TIMESTAMP:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
– Con questa dichiarazione, la colonna viene automaticamente inizializzata e successivamente aggiornata ad ogni modifica della riga con il timestamp del momento. Se omettete una delle due dichiarazioni, solo l’altra sarà attiva; ovviamente per la clausola DEFAULT potete anche fornire un valore costante.

– Quando inserite un valore in un timestamp indicando NULL, se la colonna non supporta valori NULL verrà inserito il CURRENT_TIMESTAMP.

Domini elementari

• TIME
– Una colonna TIME contiene un valore di tempo (ore, minuti e secondi) che va da ‘‐838:59:59’ a ‘838:59:59’. Anche qui la visualizzazione avviene nel formato indicato, ma è possibile usare formati diversi per l’inserimento.
• YEAR
– Infine la colonna YEAR rappresenta, su quattro cifre, un anno compreso fra 1901 e 2155, oppure 0000. Su due cifre invece i valori vanno da 70 (1970) a 69 (2069).

Domini elementari

Date e tempo
• I valori relativi al tempo possono essere inseriti sia come stringhe che come numeri, e MySQL vi consente di utilizzare, nel caso delle stringhe, molti caratteri diversi come separatori.
• L’importante però è che l’ordine dei valori sia sempre anno‐mese‐giorno‐ore‐minuti‐secondi. Quando usate i separatori nelle stringhe potete anche omettere gli zeri non significativi (ad es. è consentito ‘2005‐9‐21’ ma dovete usare ‘20050921’).
Domini elementari
Stringhe
• Le colonne di tipo stringa, a partire da MySQL 4.1, possono avere un attributo CHARACTER SET che indica l’insieme di caratteri utilizzato per la colonna, e un attributo COLLATE che indica la collation relativa. Vediamo un esempio:

CREATE TABLE tabella(
c1 CHAR(20) CHARACTER SET utf8,
c2 CHAR(20) CHARACTER SET latin1 COLLATE latin1_bin);

• In questa tabella abbiamo la colonna c1 definita col set di caratteri utf8 e la relativa collation di default; e la colonna c2 col set di caratteri latin1 e la relativa collation binaria. La lunghezza specificata è relativa al numero di caratteri (il numero di byte infatti può variare in base ai set di caratteri usati e al contenuto della colonna).

Domini elementari

Stringhe
• [NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE]
• [NATIONAL] VARCHAR(M) [BINARY]
• BINARY(M)
• VARBINARY(M)
• TINYBLOB
• TINYTEXT
• BLOB[(M)]
• TEXT[(M)]
• MEDIUMBLOB
• MEDIUMTEXT
• LONGBLOB
• LONGTEXT
• ENUM(‘valore1′,’valore2’,…)
• SET(‘valore1′,’valore2’,…)

Domini elementari

Stringhe
• CHAR è una stringa di lunghezza fissa (M) riempita con spazi a destra al momento della memorizzazione, che vengono eliminati in fase di lettura. La lunghezza prevista va da 0 a 255 caratteri. L’opzione NATIONAL indica che la stringa deve usare il set di caratteri di default. L’attributo BINARY indica che deve essere usata la collation binaria del set di caratteri utilizzato. ASCII assegna il character set latin1, UNICODE assegna ucs.

Domini elementari
Stringhe
• VARCHAR è una stringa a lunghezza variabile; le sue caratteristiche sono variate a partire da MySQL 5.0.3: in precedenza infatti la lunghezza massima era 255 e gli spazi vuoti a destra venivano eliminati in fase di memorizzazione; ora invece ciò non avviene più e la lunghezza massima dichiarabile è salita a 65535 caratteri. Gli attributi NATIONAL e BINARY hanno lo stesso significato visto in CHAR. Se definite una colonna VARCHAR con meno di 4 caratteri sarà trasformata in CHAR.

Domini elementari

Stringhe
• BINARY e VARBINARY corrispondono a CHAR e VARCHAR, ma memorizzano stringhe di byte invece che di caratteri. Non hanno quindi character set. I valori BINARY ricevono un riempimento a destra di byte 0x00 a partire da MySQL 5.0.15; in precedenza il riempimento era a spazi e veniva rimosso in fase di lettura. Nei valori VARBINARY, fino a MySQL 5.0.3 gli spazi finali venivano rimossi in lettura.

Domini elementari

Stringhe
• I formati di tipo BLOB e TEXT sono utilizzati rispettivamente per valori binari e di testo.
• La lunghezza massima è 255 caratteri per TINYBLOB e TINYTEXT, 65535 per BLOB e TEXT, 16.777.215 per MEDIUMBLOB e MEDIUMTEXT, 4 gigabyte per LONGBLOB e LONGTEXT.

Domini elementari

Stringhe
• Una colonna ENUM può contenere uno dei valori elencati nella definizione, oppure NULL o una stringa vuota, che viene assegnata quando si cerca di inserire un valore non valido. I valori possibili possono essere fino a 65535.
• Una colonna SET, come la ENUM, prevede un insieme di valori possibili (fino a 64), ma in questo caso la colonna può assumere anche più di un valore, oppure nessuno.

Domini definiti dagli utenti

• Paragonabile alla definizione dei tipi nei linguaggi di programmazione: si definiscono i valori ammissibili per un oggetto
• Un dominio è caratterizzato da
– nome
– dominio elementare
– valore di default insieme di vincoli (constraint)
• Sintassi:
create domain NomeDominio as DominioElementare
[ ValoreDefault ] [ Constraints ]

Domini definiti dagli utenti

• Esempio:
create domain Voto as smallint default null
• Rispetto ai linguaggi di programmazione
+ vincoli, valori di default, domini di base più ricchi
‐ costruttori assenti (solo ridenominazione di domini)

Domini definiti dagli utenti

• Esempio:
– create domain Voto as smallint default null
– create domain PrezzoQuotidiani
as decimal(3)
default 0,90
not null
• Rispetto ai linguaggi di programmazione
+ vincoli, valori di default, domini di base più ricchi
‐ costruttori assenti (solo ridenominazione di domini).

Definizione di tabelle

• Una tabella SQL consiste di:
– un insieme ordinato di attributi
– un insieme di vincoli (eventualmente vuoto)
• Comando create table
– definisce lo schema di una relazione, creandone un’istanza vuota
• Sintassi:
create table NomeTabella(
NomeAttributo Dominio [ ValoreDiDefault ] [ Constraints ]
{, NomeAttributo Dominio [ ValoreDiDefault ] [ Constraints ] }
[ AltriConstraints ])

Esempio di create table (1)
create table Studente
( Matr character(6) primary key,
Nome varchar(30) not null,
Città varchar(20),
CDip char(3) )

Esempio di create table (1)
create table Esame(
Matr char(6),
CodCorso char(6),
Data date not null,
Voto smallint not null,
primary key(Matr,CodCorso) )

create table Corso(
CodCorso char(6) primary key,
Titolo varchar(30) not null,
Docente varchar(20) )

Vincoli intra‐relazionali
• I vincoli sono condizioni che devono essere verificate da ogni istanza della base di dati
• I vincoli intra‐relazionali coinvolgono una sola relazione (distinguibili ulteriormente a livello di tupla o di tabella)
– not null (su un solo attributo; a livello di tupla)
– unique: permette la definizione di chiavi candidate (opera quindi a livello di tabella);
sintassi:
• per un solo attributo:
– unique, dopo il dominio
• per diversi attributi:
– unique( Attributo {, Attributo } )
• primary key: definisce la chiave primaria (una volta per ogni tabella; implica not null); sintassi come per unique
• check: descritto più avanti (può rappresentare vincoli di ogni tipo)

Esempi di vincoli intra‐relazionali

• Ogni coppia di attributi Nome e Cognome identifica univocamente ogni tupla
Nome character(20) not null,
Cognome character(20) not null,
unique(Nome,Cognome)
• Si noti la differenza con la seguente definizione (più restrittiva):
Nome character(20) not null unique,
Cognome character(20) not null unique,

Vincoli inter‐relazionali

• I vincoli possono tenere conto di diverse relazioni
– check: descritto più avanti
– references e foreign key permettono la definizione di vincoli di integrità referenziale; sintassi:
• per un solo attributo
– references dopo il dominio
• per diversi attributi
– foreign key Attributo {, Attributo})
– references …
• Si possono associare anche delle politiche di reazione alle violazioni dei vincoli di integrità referenziale

Integrità referenziale

• Esprime un legame gerarchico (padre‐figlio) fra tabelle
• Alcuni attributi della tabella figlio sono definiti FOREIGN KEY
• I valori contenuti nella FOREIGN KEY devono essere sempre presenti nella tabella padre.

Esempio: studente ‐ esame

Il problema degli orfani

Gestione degli orfani

• Le reazioni operano sulla tabella interna, in seguito a modifiche alla tabella esterna
• Le violazioni possono essere introdotte (1) da aggiornamenti (update) dell’attributo cui si fa riferimento oppure (2) da cancellazioni di tuple
• Reazioni previste:
– cascade: propaga la modifica
– set null: annulla l’attributo che fa riferimento
– set default: assegna il valore di default all’attributo
– no action: impedisce che la modifica possa avvenire
• La reazione può dipendere dall’evento; sintassi:
on < delete | update >
< cascade | set null | set default | no action >

Gestione degli orfani: cancellazione

Cosa succede degli esami se si cancella uno studente?
• cascade
si cancellano anche gli esami dello studente
• set null
si pone a null la matricola dei relativi esami
• set default
Si pone al valore di default la matricola dei relativi esami
• no action
si impedisce la cancellazione dello studente.

Gestione degli orfani: modifica

Cosa succede degli esami se si modifica la matricola di uno studente?
• cascade
si modifica la matricola degli esami dello studente
• set null
si pone a null la matricola dei relativi esami
• set default
si pone al valore di default la matricola dei relativi esami
• no action
si impedisce la modifica della matricola dello studente.