SQL1 Modifica dei dati inserimento cancellazione modifica.

transcript

Modifica dei dati inserimento cancellazione modifica

Insertinsert into <NomeTabella> [(<colonna_i>,

…, <colonna_j>)] values (<valore_i>, …, <valore_j>);

deve essere specificato un valore per ogni colonna della lista

se una colonna non è specificata, viene inserito il valore null

se non viene fornita la lista delle colonne, deve essere specificato un valore per ogni colonna della tabella così come appare nella istruzione create

Esempioinsert into PROGETTO (PNO, PNOME, COMPONENTI, BUDGET, PSTART, PMGR) values (313, 'BD', 3,150000.15, to_date('06-02-2001','dd-mm-yyyy'), 7411);

insert into PROGETTO values (314, 'SI', 7411, 5, 350000.75, to_date('12-02-2001','dd-mm-yyyy'), null);

Insert (2)insert into <NomeTabella>

[(<colonna_i>, …, <colonna_j>)] <querySQL>;

create table VECCHI_IMP (INO number(4) not null,

ASS_DATA date);insert into VECCHI_IMP select INO, DATA_ASS

from IMPIEGATI where DATA_ASS<'31-DIC-60';

Updates

updates <tabella> set <colonna_i>=<espressione_i>, …, <colonna_j>=<espressione_j>[where <condizione>];

espressione può essere una costante, una espressione funzionale, una query SQL

Esempio

update IMPIEGATI set

LAVORO='DIRIGENTE', DIPNO=20,

STIPENDIO=STIPENDIO+1000

where INOME='FORD';

(Il sig. Ford ha avuto un avanzamento di carriera)

Esempio

update IMPIEGATI set

STIPENDIO=STIPENDIO*1.15

where DIPNO in (10,30);

(Gli impiegati dei dipartimenti 10 e 30 hanno avuto un aumento dello stipendio del 15%)

Esempio

update IMPIEGATI setSTIPENDIO = (select min(STIPENDIO) from IMPIEGATI where LAVORO='DIRIGENTE')where LAVORO='VENDITORE' and DIPNO=30;

(I venditori del dipartimento 30 ricevono lo stesso stipendio del manager che ha lo stipendio più basso)

Delete

delete from <tabella> [where <condizione>];

senza la clausola where, vengono cancellate tutte le righe

SQL 10

Esempio

delete from PROGETTO

where PEND < sysdate;

(Cancella tutti i progetti terminati alla data odierna)

SQL 11

Commit e Rollback commit: rende permanenti le

modifiche fatte durante l'ultima transazione quit, create, drop realizzano un

commit implicito rollback: annulla le modifiche fatte

durante l'ultima transazione

SQL 12

Query su più tabelle (JOIN)select [distinct] [<alias_k>.]<colonna_i>,

…, [<alias_m>.]<colonna_j>

from <tabella_1>[<alias_1>],…,

<tabella_n>[<alias_n>][where <condizione>];

SQL 13

Se colonne di tabelle diverse hanno lo stesso nome, è necessario usare la notazione<tabella>.<colonna>

per eliminare ambiguità.Al posto di <tabella> si può usare

l'alias

SQL 14

Esempiselect INOME, I.DIPNO, DIPNOMEfrom IMPIEGATI I, DIPARTIMENTO Dwhere I.DIPNO=D.DIPNO and LAVORO='VENDITORE';

select INOME, DIPNOME, PNOME from IMPIEGATI I, DIPARTIMENTO D, PROGETTO Pwhere I.INO=P.PMGR and D.DIPNO=I.DIPNO;

select I1.INOME, I2.INOME from IMPIEGATI I1, IMPIEGATI I2where I1.CAPO=I2.INO;

SQL 15

Query annidate

subqueryquery annidata

La condizione in una clausola where può contenere anche una query:

select … where … (select …)

SQL 16

Interpretazione delle query annidate

1. viene eseguita la subquery2. il risultato viene salvato in una

tabella temporanea3. il controllo della query esterna

viene fatto sulle righe della tabella temporanea

SQL 17

Regole per le query annidate

più condizioni possono essere combinate con gli operatori and, or

si può arrivare fino a 16 livelli di annidamento!

una query esterna non può far riferimento a tabelle della query più interna (viceversa è possibile)

se non specificato esplicitamente, i nomi di colonna fanno riferimento alle tabelle più “vicine”

SQL 18

Subquery

<espressione> [not] in (<subquery>)

<espressione> <operatore_cfr> [any | all] (<subquery>)

[not] exists (<subquery>)

SQL 19

Esempioselect INOME, STIPENDIO from IMPIEGATI

where INO in

(select PMGR from PROGETTO

where PSTART < ’31-DIC-90’)

and DIPNO=20;

Seleziona gli impiegati del dipartimento 20 (ed il loro stipendio) che partecipano a progetti partiti prima del 31/12/90

SQL 20

Esempio

select * from IMPIEGATI

where DIPNO in

(select DIPNO from DIPARTIMENTI

where CITTA =‘BOSTON’);

Seleziona gli impiegati dei dipartimenti di BostonPoiché la subquery ha come risposta un solo valore,

si poteva usare = invece di in.

SQL 21

Subquery correlata

Subquery che fa riferimento a colonne di tabelle utilizzate nella query principale

Non si può usare l’intepretazione vista per le query annidate!!

SQL 22

Interpretazione delle subquery correlate

1. Per ogni riga della query esterna,1. viene valutata la subquery2. viene valutato il predicato della

query esterna

SQL 23

Esempio

select * from IMPIEGATI I1

where DIPNO in

(select DIPNO from IMPIEGATI [I2]

where [I2.]INO=I1.CAPO);

Seleziona i dipendenti che lavorano nello stesso dipartimento del loro capo

SQL 24

any e all

<espressione> <operatore_cfr> [any | all] (<subquery>)

confrontano <espressione> con ogni valore selezionato da <subquery>

SQL 25

any la condizione è vera se esiste

almeno una riga selezionata dalla subquery per cui il confronto ha successo

se la subquery restiuisce un insieme vuoto, la condizione fallisce

SQL 26

Esempioselect * from IMPIEGATI

where STIPENDIO >= any

(select STIPENDIO from IMPIEGATI

where DIPNO = 30)

and DIPNO = 10

Seleziona gli impiegati del dipartimento 10 che hanno uno stipendio maggiore o uguale di almeno un impiegato del dipartimento 30

SQL 27

all la condizione è vera se il confronto

ha successo per tutte le righe selezionate dalla subquery

se la subquery restiuisce un insieme vuoto, la condizione ha successo

SQL 28

Esempioselect * from IMPIEGATI

where STIPENDIO > all

(select STIPENDIO from IMPIEGATI

where DIPNO = 30)

and DIPNO <> 30

Seleziona gli impiegati che non lavorano nel dipartimento 30 e che hanno uno stipendio maggiore di tutti gli impiegati del dipartimento 30

SQL 29

Equivalenze in è equivalente a = any not in è equivalente a <> all

SQL 30

Exists

[not] exists (<subquery>)

restituisce valore vero solo se l’interrogazione fornisce un risultato non vuoto [vuoto].

ha senso solo per query correlate

SQL 31

Esempio

select * from DIPARTIMENTIwhere not exists(select * from IMPIEGATI where DIPNO=DIPARTIMENTI.DIPNO);

Restituisce i dipartimenti che non hanno impiegati

SQL 32

Operazioni su risultati di query

<query_1> [union [all] | intersect | minus ] <query_2>

SQL 33

union Restituisce l'unione delle tuple

risultato delle due query. Se non viene specificata la clausola all, i duplicati vengono eliminati

select INO from IMPIEGATIunionselect CAPO from IMPIEGATI;

SQL 34

intersect Restituisce l'intersezione delle

tuple risultato delle due query.

select INO from IMPIEGATI

intersect

select CAPO from IMPIEGATI;

SQL 35

minus Restituisce le tuple che sono

risultato della prima query ma che non sono risultato della seconda

select INO from IMPIEGATI

select CAPO from IMPIEGATI;

SQL 36

Query con raggruppamento Consentono di applicare le funzioni di

gruppo non a tutte le righe ma a gruppi di righe che soddisfano certe proprietà

select <colonna/e> from <tabella/e>where <condizione>group by <colonna/e_gruppo>[having <condizione/i_gruppo>];

SQL 37

Interpretazione1. Le righe risultato della select che hanno

gli stessi valori di <colonna/e_gruppo> vengono raggruppate.

2. Le funzioni di gruppo specificate nella select vengono applicate a ciascun gruppo separatamente.

N.B. solo le colonne di <colonna/e_gruppo> possono essere listate nella clausola select senza funzioni di gruppo

SQL 38

Esempiselect DIPNO, min(STIPENDIO), max(STIPENDIO)

from IMPIEGATIgroup by DIPNO;

select DIPNO, min(STIPENDIO), max(STIPENDIO)

from IMPIEGATIwhere LAVORO='VENDITORE'group by DIPNO;

SQL 39

having Dopo che i gruppi sono stati

formati, se ne possono eliminare alcuni in base a certe condizioni

select DIPNO, min(STIPENDIO), max(STIPENDIO)

from IMPIEGATIgroup by DIPNOhaving count(*)>3;

SQL 40

Accesso a tabelle di altri utenti Per accedere a tabelle di altri

utenti (sempre che se ne abbiano i privilegi) bisogna far precedere il nome di tabella dal nome dell'utente<user>.<tabella>

SQL 41

Sinonimi Si possono creare sinonimi sia per

le proprie tabelle che per quelle di altri utenti:

create synonym <sinonimo> for [<user>.]<tabella>;

SQL 42

Commenti alle definizioni Si possono aggiungere commenti esplicativi alle

definizioni di tabelle e alle colonne

comment on table <tabella> is '<testo>';

comment on column <tabella>.<colonna> is '<testo>';

I commenti sono inseriti nelle viste del dizionario dei dati USER_TAB_COMMENTD e USER_COL_COMMENTS

SQL 43

Modifiche di definizioni E' possibile modificare la struttura

di una tabella aggiungendo colonne aggiungendo vincoli di tabella modificando definizioni di colonne

SQL 44

alter table

alter table <tabella> add (<definizione_colonna>);

alter table <tabella> add (<vincoli_di_tabella>)

alter table <tabella> modify (<nuova_definizione_colonna>);

SQL 45

Cancellazione di una tabella

drop table <tabella> [cascade constraints];

Cancella le righe e la definizione di una tabella

SQL 46

Una vista è una tabella virtuale costruita selezionando righe e colonne di altre tabelle.

Una vista può essere usata come le altre tabelle per la ricerca e per la modifica di dati.

Una vista viene rivalutata ogni volta che vi si accede.

SQL 47

Viste (2)create view <nome_vista> [(<colonna/e>)] as<query> [with check option [constraint

<nome_vincolo>]];

se (<colonna/e>) non viene specificato, le colonne della vista avranno lo stesso nome delle colonne selezionate

il vincolo with check option consente di rifiutare le modifiche e gli inserimenti di righe nella vista che non rispondono alla definizione, ovvero che non verrebbo selezionati dalla query

SQL 48

Esempi

create view DIP20 as select INOME, LAVORO, STIPENDIO*12 STIP_ANNUO from IMPIEGATI where DIPNO=20;

create view DIP20 (INOME, LAVORO, STIP_ANNUO) as select INOME, LAVORO, STIPENDIO*12 from IMPIEGATI where DIPNO=20;

SQL 49

Cancellazione di viste

delete <nome_vista>;

SQL 50

Vincoli di integrità check constraint

limita i valori possibili per un attributo

foreign key constraint specifica interdipendenze tra relazioni

SQL 51

check constraint specifica il rango dei possibili valori di uno o

più attributi o certe condizioni che tali valori devono soddisfare

[constraint <nome>] check (<condizione>)

può essere un vincolo di colonna (se condizione agisce su una sola colonna) o di tabella (se condizione agisce su più colonne)

SQL 52

Esempiocreate table IMPIEGATI (

INOME varchar2(30) constraint CK_NOME check(INOME=upper(INOME)),

STIPENDIO number(5,2) check (STIPENDIO >= 500),

DIPNO number(3) check (DIPNO between 10 and 100)

SQL 53

Esempiocreate table PROGETTO (

COMPONENTI number(5) constraint CK_COMPONENTI check(COMPONENTI>2),

constraint CK_DATE check(PEND > PSTART)

SQL 54

foreign key constraint crea un legame tra i valori di un attributo

della tabella corrente (interna) ed i valori di un attributo chiave primaria di un'altra tabella (esterna), in modo tale che ogni valore dell'attributo della tabella interna, se non nullo, sia presente tra i valori dell'attributo chiave primaria della tabella esterna

può essere un vincolo di colonna o un vincolo di tabella

SQL 55

foreign key

[constraint <nome>] [foreign key (<colonna/e>)]references <tabella>

[(<colonna/e>)][on delete cascade]

SQL 56

la clausola foreign key deve essere usata se si tratta di un vincolo di tabella, ovvero se coinvolge più colonne e deve indicare le colonne della tabella interna coinvolte

references indica quali colonne della tabella esterna sono coinvolte; in assenza di specifica viene presa la colonna che costituisce la chiave primaria della tabella esterna

on delete cascade specifica che se viene cancellato un elemento dalla tabella esterna, tutte le righe della tabella interna che hanno il valore dell'attributo cancellato, vengono a loro volta cancellate

SQL 57

INO number(4) primary key,

DIPNO number(3) references DIPARTIMENTO(DIPNO)

SQL 58

Esempiocreate table PROGETTO (

PNO number(3) primary key,

PMGR number(4) not null references IMPIEGATI,

SQL 59

INO number(4) primary key,

CAPO number(4) not null references IMPIEGATI,

SQL1 Modifica dei dati inserimento cancellazione modifica.

Documents