1

Corso di Laurea in Biotecnologie

Informatica(Basi di Dati)

SQL: Data Manipulation Language (DML)

Anno Accademico 2009/2010Da:• Atzeni, Ceri, Paraboschi, Torlone - Basi di Dati• Lucidi del Corso di Basi di Dati 1, Prof. Carlo Batini, Laurea in Informatica, AA 2007-2008

2

IntroduzioneCos’è SQL (Structured Query Language)?E’ un linguaggio che consente di gestire le Basi di Dati relazionali. Esso è contemporaneamente un:

Data Definition Language (DDL), cioè consente di creare lo schema della Base di Dati (e delle relazioni) Data Manipulation Language (DML), cioè consente di manipolare le istanze della Base di Dati

3

IntroduzioneVedremo come SQL (come DML) permetta di: interrogare la Base di Dati modificare la Base di Dati eseguendo:

inserimenti cancellazioni modifiche

Nota Bene: in questa e nelle successive slide le parole scritte inmaiuscolo (salvo indicazione contraria) sono parole appartenenti al

linguaggio

4

Interrogazione di una Base di DatiL’istruzione SQL per interrogare una Base di Dati è SELECT. Ci limitiamo alla seguente sintassi:

SELECT <attrList>FROM <tableList>[WHERE <condition>]dove <attrList> è la lista degli attributi (separati da virgola) i cui valori devono essere riprodotti come esito dell’interrogazione, <tableList> è la lista delle tabelle (separate da virgola) coinvolte nell’interrogazione, e <condition> è una condizione da soddisfare (vedi nel seguito…). La clausola WHERE è opzionale.

5

Interrogazione di una Base di DatiCome funziona l’istruzione SELECT?

1. Viene eseguito il prodotto cartesiano delle tabelle specificate in <tableList> (seguendo lo stesso ordine in cui vengono elencate)

2. Dall’insieme di ennuple del prodotto cartesiano del punto 1, vengono selezionate solo quelle che soddisfano <condition> della clausola WHERE (se esiste). Se non esiste la clausola WHERE vengono prese tutte le ennuple del prodotto cartesiano

3. Delle ennuple al punto 2 vengono forniti solo i valori relativi agli attributi specificati in <attrList>

6

Interrogazione di una Base di DatiCome funziona l’istruzione SELECT?

Nel caso particolare in cui <tableList> sia composta da un’unica tabella R, allora al punto 1 precedente vengono prese tutte le ennuple di R così come sono.

NOTA: Gli esempi successivi fanno riferimento alla Base di Dati della prossima slide per cui si suppone di avere già creato lo schema. In particolare sono state definite le chiavi primarie (in rosso) di Studenti e Corsi e le due chiavi esterne (in blu) di Esami. Non si suppongno altri vincoli.

7

Interrogazione di una Base di Dati (esempio)

276545 Rossi

485745 Neri

200768 Verdi

587614 Rossi

Maria

Anna

Fabio

Luca

25/11/1971

23/04/1972

12/02/1972

10/10/1971

Matricola Cognome Nome DataNascita

01 Analisi03 Chimica04 Chimica

GianiMelliBelli

Codice Titolo Docente

276545 28276545 27200768 24

010404

MatricolaStudente Voto CodiceCorso

Studenti

Esami

Corsi

8

Interrogazione di una Base di Dati (esempio1)L’istruzione:

SELECT Cognome, NomeFROM Studenti

produce l’output:

Rossi

Neri

Verdi

Rossi

Maria

Anna

Fabio

Luca

9

Interrogazione di una Base di Dati (esempio2)L’istruzione:

SELECT Cognome, NomeFROM StudentiWHERE Cognome=‘Rossi’

produce l’output:

Rossi

Rossi

Maria

Luca

10

Interrogazione di una Base di Dati (esempio3)L’istruzione:

SELECT Cognome, Nome, DataNascitaFROM StudentiWHERE Matricola=276545

produce l’output:

Rossi Maria 25/11/1971

11

Interrogazione di una Base di Dati (esempio4)L’istruzione:

SELECT Cognome, Nome, VotoFROM Studenti, EsamiWHERE Matricola=276545 AND

Matricola=MatricolaStudente

produce l’output:

Rossi

Rossi

Maria

Maria

28

27

12

Interrogazione di una Base di Dati (esempio5)L’istruzione:

SELECT Cognome, Nome, Voto, TitoloFROM Studenti, Esami, CorsiWHERE Matricola=276545 AND

Matricola=MatricolaStudente AND Codice=CodiceCorso

produce l’output:

Rossi

Rossi

Maria

Maria

28

27

Analisi

Chimica

13

Interrogazione di una Base di Dati (esempio5)L’istruzione:

SELECT Cognome, Nome, Voto, TitoloFROM Studenti, Esami, CorsiWHERE Matricola=276545 AND

Matricola=MatricolaStudente AND Codice=CodiceCorso AND Voto > 27

produce l’output:

Rossi Maria 28 Analisi

14

Interrogazione di una Base di Dati (esempio6)L’istruzione:

SELECT *FROM Studenti

è equivalente al comando:

SELECT Matricola, Cognome, Nome, DataNascita

FROM Studenti

* è un’abbreviazione per l’intera lista di attributi della tabella della clausola FROM.

15

Interrogazione di una Base di DatiNel caso in cui le tabelle della clausola FROM

condividano il nome di qualche attributo, per evitare ambiguità di interpretazione nella <attrList>, si devono specificare gli attributi preceduti dal nome della tabella a cui appartengano e da un punto ‘.’

Ad esempio se nella nostra Base di Dati al posto di MatricolaStudente della tabella Esami si mettesse semplicemente Matricola (stesso nome della chiave primaria della tabella Studenti), si potrebbero avere ambiguità in una SELECT che coinvolge entrambe le tabelle ed entrambi gli attributi.

16

Interrogazione di una Base di Dati (esempio7)L’istruzione:

SELECT MatricolaFROM Studenti, Esami

è ambiguo in quanto non si capisce se si intende Matricola in Studenti o Matricola in Esami. Ad esempio se si intende Matricola di Studenti, il comando corretto è:

SELECT Studenti.MatricolaFROM Studenti, Esami

17

Interrogazione di una Base di Dati

Nella condizione della clausola WHERE, si può usare un particolare operatore denominato LIKE che permette di selezionare stringhe di caratteri che rispettano un certo modello che contiene: caratteri specifici caratteri jolly

_ che denota un arbitrario carattere % che denota una stringa con un

numero arbitrario di caratteri

18

Interrogazione di una Base di Dati (esempio8)L’istruzione:

SELECT Matricola, Cognome, NomeFROM StudentiWHERE Cognome LIKE ‘%er%’

produce l’output:

485745 Neri

200768 Verdi

Anna

Fabio

19

Interrogazione di una Base di Dati (esempio9)L’istruzione:

SELECT Matricola, Cognome, NomeFROM StudentiWHERE Cognome LIKE ‘%er_’

produce l’output:

485745 Neri Anna

20

Interrogazione di una Base di DatiNella condizione della clausola WHERE, si

può usare IS NULL per ricercare le ennuple con valori nulli.

Ad esempio il comando:

SELECT Matricola, Cognome, NomeFROM StudentiWHERE Cognome IS NULL

produce un output vuoto poiché nessuna ennupla in Studenti ha valore NULL in corrispondenza di Cognome.

21

Interrogazione di una Base di Dati

Il comando SELECT permette l’uso di una serie di operatori aggregati che vengono valutati solo dopo avere eseguito l’interrogazione di base e che servono per:

contare le ennuple COUNT trovare il minimo MIN trovare il massimo MAX effettuare la somma SUM effettuare la media AVG

22

Interrogazione di una Base di DatiL’operatore COUNT conta il numero di ennuple

di una tabella dopo aver eseguito un’interrogazione di base

Ad esempio:

SELECT COUNT(*)FROM StudentiWHERE Cognome=‘Rossi’

produce il numero di studenti (nella tabella Studenti) il cui cognome è Rossi. L’interrogazione di base si ottiene eliminando l’operatore COUNT.Attenzione! COUNT non tiene conto dei valori nulli, cioè elimina, prima diiniziare il conteggio, le ennuple (generate dall’interrogazione di base)

composte di soli valori nulli.

23

Interrogazione di una Base di DatiL’operatore MIN trova il minimo valore per un

(solo) attributo di una tabella dopo aver eseguito un’interrogazione di base

Ad esempio:

SELECT MIN(DataNascita)FROM Studenti

produce la data di nascita dello studente (tabella Studenti) più anziano. L’interrogazione di base si ottiene eliminando l’operatore MIN.Attenzione! MIN non tiene conto dei valori nulli, cioè elimina, prima di

trovare il minimo, i valori nulli per l’attributo specificato come argomento.

24

Interrogazione di una Base di DatiL’operatore MAX trova il massimo valore per un

(solo) attributo di una tabella dopo aver eseguito un’interrogazione di base

Ad esempio:

SELECT MAX(DataNascita)FROM Studenti

produce la data di nascita dello studente (tabella Studenti) più giovane. L’interrogazione di base si ottiene eliminando l’operatore MAX.Attenzione! MAX non tiene conto dei valori nulli, cioè elimina, prima di

trovare il massimo, i valori nulli per l’attributo specificato come argomento.

25

Interrogazione di una Base di DatiL’operatore SUM effettua la somma dei valori

relativi a un (solo) attributo di una tabella dopo aver eseguito un’interrogazione di base

Ad esempio:

SELECT SUM(Voto)FROM Esami

produce la somma di tutti i voti presenti nella tabella Esami. L’interrogazione di base si ottiene eliminando l’operatore SUM.

Attenzione! SUM non tiene conto dei valori nulli, cioè elimina, prima ditrovare la somma, i valori nulli per l’attributo specificato come argomento.

26

Interrogazione di una Base di DatiL’operatore AVG effettua la media dei valori

relativi a un (solo) attributo di una tabella dopo aver eseguito un’interrogazione di base

Ad esempio:

SELECT AVG(Voto)FROM Studenti, EsamiWHERE Matricola=276545 AND

Matricola=MatricolaStudente AND Codice=CodiceCorso

produce la media dei voti dello studente con numero di matricola pari a 276545.Attenzione! SUM non tiene conto dei valori nulli, cioè elimina, prima di

trovare la somma, i valori nulli per l’attributo specificato come argomento.

27

Aggiornamento di una Base di Dati

Le operazioni di aggiornamento di una Base di Dati sono:

inserimento INSERT cancellazione DELETE modifica UPDATE

28

Aggiornamento di una Base di DatiL’operazione di inserimento di una ennupla in

una tabella della Base di Dati viene effettuata tramite il comando INSERT:

INSERT INTO <tableName>(<attrList>)VALUES(<valueList>)

dove <tableName> è il nome della tabella in cui si vuole effettuare l’inserimento, <attrList> è la lista degli attributi della tabella per cui si vuole specificare un valore, <valueList> è la lista che specifica i valori degli attributi di <attrList> nello stesso ordine di <attrList>.

29

Aggiornamento di una Base di DatiSe <attrList> viene omessa, allora si fa

riferimento a tutti gli attributi della tabella nello stesso ordine in cui sono stati elencati nella CREATE TABLE.

Attenzione! Per gli attributi non specificati in <attrList> viene inserito il valore di DEFAULT se questo è stato specificato al momento di creazione della tabella, altrimenti viene inserito il valore NULL solo però se questo non viola un eventuale vincolo di NOT NULL specificato per l’attributo stesso (in caso contrario l’operazione viene rifiutata).

30

Aggiornamento di una Base di Dati (esempio1)

L’istruzione:

INSERT INTO Studenti (Matricola, Cognome, Nome)

VALUES(23456, ‘Bianchi’, ‘Stefano’)

inserisce in Studenti la ennupla (23456, ‘Bianchi’, ‘Stefano’, NULL) in quanto il valore per DataNascita non è stato specificato

31

Aggiornamento di una Base di DatiL’operazione di cancellazione di ennuple da

una tabella della Base di Dati viene effettuata tramite il comando DELETE:

DELETE FROM <tableName>[WHERE <condition>]

dove <tableName> è il nome della tabella da cui si vogliono cancellare le ennuple che rispettano la condizione specificata da <condition> (se esiste la clausola WHERE che è opzionale).

Se la clausola WHERE viene omessa, allora tutte le ennuple della tabella vengono cancellate.

32

Aggiornamento di una Base di Dati (esempio2)

L’istruzione:

DELETE FROM StudentiWHERE Cognome=‘Rossi’

cancella dalla tabella Studenti tutti gli studenti che hanno cognome Rossi.

33

Aggiornamento di una Base di DatiL’operazione di aggiornamento di ennuple di

una tabella della Base di Dati viene effettuata tramite il comando UPDATE:

UPDATE <tableName>SET <attrName1>=<value1>,

…,<attrNameN>=<valueN>

[WHERE <condition>]

dove <tableName> è il nome della tabella in cui si vogliono aggiornare le ennuple che rispettano la condizione specificata da <condition> (se esiste la clausola WHERE che è opzionale).

34

Aggiornamento di una Base di DatiSe la clausola WHERE viene omessa, allora

tutte le ennuple della tabella vengono aggiornate.

La clausola SET specifica gli attributi <attrName*> che devono essere aggiornati.

In altre parole si prendono le ennuple che rispettano <condition> (se esiste, altrimenti si prendono tutte) e si aggiorna ogni valore di <attrName*> al valore specificato da <value*>, che può essere: DEFAULT NULL un’espressione e altro… (che non vediamo…)

35

Aggiornamento di una Base di Dati (esempio3)L’istruzione:

UPDATE StudentiSET DataNascita=NULLWHERE Cognome=‘Rossi’

aggiorna al valore NULL la data di nascita degli studenti che hanno cognome Rossi.

36

Aggiornamento di una Base di Dati (esempio4)L’istruzione:

UPDATE StudentiSET DataNascita=DEFAULTWHERE Cognome=‘Rossi’

aggiorna al valore di default (eventualmente specificato per l’attributo DataNascita nell’istruzione CREATE TABLE) la data di nascita degli studenti che hanno cognome Rossi.

37

Aggiornamento di una Base di Dati (esempio5)L’istruzione:

UPDATE EsamiSET Voto=18WHERE Voto > 18 AND Voto <= 20

aggiorna a 18 tutti i voti degli esami con voto compreso tra 18 e 20.

38

Aggiornamento di una Base di Dati (esempio6)L’istruzione:

UPDATE EsamiSET Voto=Voto-1WHERE Voto > 28

decrementa di 1 i voti di tutti gli esami con voto superiore a 28.