1 / 58

Corso di Basi di Dati

Corso di Basi di Dati. Il Linguaggio SQL Home page del corso : http:// www.cs.unibo.it /~ difelice / dbsi /. Il Linguaggio SQL. SQL ( Structured Query Language ) e’ il linguaggio di riferimento per le basi di dati relazionali . Diverse versioni del linguaggio :

edita
Download Presentation

Corso di Basi di Dati

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Corso di Basi di Dati Il Linguaggio SQL Home page del corso: http://www.cs.unibo.it/~difelice/dbsi/

  2. Il Linguaggio SQL • SQL (Structured Query Language) e’ illinguaggio di riferimento per le basi di datirelazionali. • Diverse versioni del linguaggio: • SQL-86  Costrutti base • SQL-89  Integrita’ referenziale • SQL-92 (SQL2)  Modellorelazionale, struttura a livelli • SQL:1999 (SQL3)  Modello ad oggetti • SQL:2003 (SQL3)  Nuoveparti: SQL/JRT, SQL/XML • SQL:2006 (SQL3)  Estensione di SQL/XML • SQL:2008 (SQL3)  Lieviaggiunte

  3. SQL: DML Es. Estrarreilcodicedellostrutturatochericeve lo stipendiopiu’ alto. STRUTTURATI

  4. SQL: DML Es. Estrarreilcodicedellostrutturatochericeve lo stipendiopiu’ alto. SELECT CODICE, MAX(STIPENDIO) FROM STRUTTURATI ERRORE! • SELECT MAX(STIPENDIO) restituisce solo un valore!! • SELECT CODICE restituiscepiu’ di un valore!!

  5. SQL: DML Es. Estrarreilcodicedellostrutturatochericeve lo stipendiopiu’ alto. SELECT CODICE FROM STRUTTURATI WHERE STIPENDIO= MAX(STIPENDIO) ERRORE! • L’operatoreaggregatoMAXsiapplicasullaSELECT e vienevalutatodopo la WHERE …

  6. SQL: DML Nellaclausolawhere, oltre ad espressionisemplici, possonocomparireespressionicomplesse in cui ilvalore di un attributovieneconfrontato con ilrisultato di un’altra query (query annidate). NOTA: Si staconfrontandoun singolo valore con ilrisultato di una query (quindi potenzialmenteunatabella). SELECT FROM WHERE (Attributoexpr SELECT FROM WHERE)

  7. SQL: DML Es. Estrarreilcodicedellostrutturatochericeve lo stipendiopiu’ alto. STRUTTURATI

  8. SQL: DML Es. Estrarreilcodicedellostrutturatochericeve lo stipendiopiu’ alto. QUERY ESTERNA SELECT CODICE FROM STRUTTURATI WHERE (STIPENDIO = SELECT MAX(STIPENDIO) FROM STRUTTURATI) QUERY INTERNA

  9. SQL: DML Nelcasoprecedente, la query internarestituisce solo un valore … Cosaaccade se la query internarestuiscepiu’ di un valore? Glioperatori di confronto <,=,> non sipossonoutilizzare in questocaso! Es. Estrarrenome e cognomedeglistrutturati del dipartimento di Informaticacheguadagnanoquanto un lorocollega di Fisica.

  10. SQL: DML Nelcasoprecedente, la query internarestituisce solo un valore … Cosaaccade se la query internarestuiscepiu’ di un valore? SELECT NOME, COGNOME FROM STRUTTURATI WHERE (DIPARTIMENTO=“INFORMATICA”) AND (STIPENDIO = (SELECT STIPENDIO FROM STRUTTURATI WHERE (DIPARTIMENTO=“FISICA”))) NON FUNZIONA!

  11. SQL: DML • Esistonooperatorispeciali di confrontonelcaso di interrogazioniannidate: • any la rigasoddisfa la condizione se e’ veroilconfrontotrailvalore dell’ attributoedALMENO UNOdeivaloriritornatidalla query annidata. • all  a rigasoddisfa la condizione se e’ veroilconfrontotrailvalore dell’ attributo e TUTTIivaloriritornatidalla query annidata.

  12. SQL: DML Es. Estrarrenome e cognomedeglistrutturati del dipartimento di Informaticacheguadagnanoquanto un lorocollega di Fisica. SELECT NOME, COGNOME FROM STRUTTURATI WHERE (DIPARTIMENTO=“INFORMATICA”) AND (STIPENDIO = ANY (SELECT STIPENDIO FROM STRUTTURATI WHERE (DIPARTIMENTO=“FISICA”)))

  13. SQL: DML Es. Estrarrenome e cognomedeglistrutturati del dipartimento di Informaticacheguadagnanopiu’ di tuttiilorocolleghidi Fisica. SELECT NOME, COGNOME FROM STRUTTURATI WHERE (DIPARTIMENTO=“INFORMATICA”) AND (STIPENDIO > ALL (SELECT STIPENDIO FROM STRUTTURATI WHERE (DIPARTIMENTO=“FISICA”)))

  14. SQL: DML • Le interrogazioninidificatepossonoessere: • Semplici non c’e’ passaggio di bindingtra un contestoall’altro. Le interrogazionivengonovalutatedallapiu’ internaallapiu’ esterna. • Complesse c’e’ passaggio di binding attraversovariabilicondivisetra le varieinterrogazioni. In questocaso, le interrogazionipiu’ interne vengonovalutatesuognitupla.

  15. SQL: DML STEP1: Vienevalutata la query piu’ interna… SELECT NOME, COGNOME FROM STRUTTURATI WHERE (DIPARTIMENTO=“INFORMATICA”) AND (STIPENDIO > ALL (SELECT STIPENDIO FROM STRUTTURATI WHERE (DIPARTIMENTO=“FISICA”)))

  16. SQL: DML STEP2: Vieneconfrontataciascunarigadellatabella piu’ esterna con ilrisultatodella query interna … SELECT NOME, COGNOME FROM STRUTTURATI WHERE (DIPARTIMENTO=“INFORMATICA”) AND (STIPENDIO > ALL (SELECT STIPENDIO …

  17. SQL: DML STEP2: Vieneconfrontataciascunarigadellatabella piu’ esterna con ilrisultatodella query interna … SELECT NOME, COGNOME FROM STRUTTURATI WHERE (DIPARTIMENTO=“INFORMATICA”) AND (STIPENDIO > ALL (SELECT STIPENDIO …

  18. SQL: DML STEP2: Vieneconfrontataciascunarigadellatabella piu’ esterna con ilrisultatodella query interna … SELECT NOME, COGNOME FROM STRUTTURATI WHERE (DIPARTIMENTO=“INFORMATICA”) AND (STIPENDIO > ALL (SELECT STIPENDIO FROM STRUTTURATI WHERE (DIPARTIMENTO=“FISICA”)))

  19. SQL: DML • Le interrogazioninidificatepossonoessere: • Semplici non c’e’ passaggio di bindingtra un contestoall’altro. Le interrogazionivengonovalutatedallapiu’ internaallapiu’ esterna. • Complesse c’e’ passaggio di binding attraversovariabilicondivisetra le varieinterrogazioni. In questocaso, le interrogazionipiu’ interne vengonovalutatesuognitupla.

  20. SQL: DML Es. Estrarrenome/cognomedegliimpiegatichehannoomonimi (stessonome/cognome di altriimpiegati). IMPIEGATI

  21. SQL: DML Es. Estrarrenome/cognomedegliimpiegatichehannoomonimi (stessonome/cognome di altriimpiegati). A=IMPIEGATI B=IMPIEGATI for i=0 … |A| for j=0 … |B| if ((A[i].Nome==B[j].Nome) AND (A[i].Cognome== B[j].Cognome)) Include IMPIEGATI[i] into the result E’ necessariovalutare due volte la tabella IMPIEGATI!!

  22. SQL: DML Es. Estrarrenome/cognomedegliimpiegatichehannoomonimi (stessonome/cognome di altriimpiegati). SELECT NOME, COGNOME FROM IMPIEGATI AS I WHERE (I.NOME,I.COGNOME) = (SELECT NOME, COGNOME FROM IMPIEGATI AS I2 WHERE (I.NOME=I2.NOME) AND (I.COGNOME=I2.COGNOME) AND (I.CODICE <> I2.CODICE))

  23. SQL: DML Funzionamento: La query piu’ internavienevalutatasuciascunatupladella query piu’ esterna... I I2

  24. SQL: DML Funzionamento: La query piu’ internavienevalutatasuciascunatupladella query piu’ esterna... I I2

  25. SQL: DML In alcunicasi, le query annidatepossonoessereriscritteusandocostrutti di join tratabelle o self-join (prodottocartesiano + selezione). SELECT NOME, COGNOME FROM IMPIEGATI AS I,IMPIEGATI AS I2 WHERE (I.NOME=I2.NOME) AND (I.COGNOME=I2.COGNOME) AND (I.CODICE <> I2.CODICE))

  26. SQL: DML Il costruttoin restituiscetruese un certovalore e’ contenutonelrisultato di unainterrogazionenidificata, falsealtrimenti. SELECT ListaAttributi FROM TabellaEsterna WHERE Valore/iINSELECT ListaAttributi2 FROM TabellaInterna WHERE Condizione Nelcaso di piu’ di 1 valoresiutilizzailcostrutture di tuple.

  27. SQL: DML In manieraequivalente, usandol’operatoreinedicostruttori di tupla: SELECT CODICE FROM IMPIEGATI AS I WHERE (I.NOME,I.COGNOME) NOT IN (SELECT NOME, COGNOME FROM IMPIEGATI AS I2 WHERE (I.NOME=I2.NOME) AND (I.COGNOME=I2.COGNOME) AND (I.CODICE <> I2.CODICE))

  28. SQL: DML Il costruttoexistrestituiscetrue se l’interrogazionenidificatarestituisce un risultato non vuoto(>=1 elementotrovato). SELECT ListaAttributi FROM TabellaEsterna WHERE EXIST SELECT ListaAttributi2 FROM TabellaInterna WHERE Condizione Controlla se ilnumero di righedella query interna>0

  29. SQL: DML Es. Estrarrenome/cognomedegliimpiegaticheNONhannoomonimi (stessonome/cognome di altriimpiegati). IMPIEGATI

  30. SQL: DML SELECT NOME, COGNOME FROM IMPIEGATI AS I WHERE NOT EXISTS (SELECT * FROM IMPIEGATI AS I2 WHERE (I.NOME=I2.NOME) AND (I.COGNOME=I2.COGNOME) AND (I.CODICE <> I2.CODICE)) Q. E’ possibilescrivere la stessa query senzausareinterrogazioniannidate?

  31. SQL: DML Esempio (interrogazioninidificate).Datoilseguente schema: FILM(Titolo, Anno, Regista) REGISTA(Nome, AnnoNascita) ATTORE(Nome, AnnoNascita) RECITAZIONE(TitoloFilm, NomeAttore) Selezionareinomideiregistichehannorecitato solo in film diretti da lorostessi.

  32. SQL: DML Unapossibilesoluzione con 3 query annidate… SELECT NOME FROM REGISTA R1 WHERE (NOT EXIST (SELECT * FROM FILM F WHERE ((F.TITOLO=ANY SELECT R.TITOLOFILM FROM RECITAZIONE R WHERE (R.NOMEATTORE=R1.NOME) ) AND (R1.NAME<>F.REGISTA)) ) )

  33. SQL: Viste Le visterappresentano “tabellevirtuali” ottenute da daticontenute in altretabelle del database. Ogni vista ha associato un nomeedunalista di attributi, e siottiene dal risultato di unaselect. create view NomeView [ListaAttributi] as SELECTSQL [with [local | cascade] check option]

  34. SQL: Viste PROPRIETA’ delle VISTE • I datidelleviste NON sonofisicamentememorizzati a parte, in quantodipendono da altretabelle(ad eccezionedellevistematerializzate). • Le visteesistonoa livello di schema ma non hannoistanzeproprie. • Le operazioni di aggiornamento di vistepotrebbero non essereconsentite in alcuni DBMS.

  35. SQL: Viste • Q. A che serve definireuna vista? • Implementaremeccanismi di indipendenzatraillivellologicoedillivelloesterno. • Scrivereinterrogazionicomplesse, semplificandone la sintassi. • Garantire la retro-compatibilita’ con precedentiversionidello schema del DB, in caso di ristrutturazionedellostesso.

  36. SQL: Viste Data la tabella PROFESSORI, definireunavista “STUDENTI” in cui simostrano solo le informazionianagrafiche (nome, cognome, codice, data nascita) deidocenti. PROFESSORI

  37. SQL: Viste Data la tabella PROFESSORI, definireuna vista “STUDENTI” in cui simostrano solo le informazionianagrafiche (nome, cognome, codice, data nascita) deidocenti. CREATE VIEW STUDENTI(CODICE,NOME,COGNOME, DATANASCITA) AS SELECT CODICE,NOME,COGNOME,NASCITA FROM PROFESSORI

  38. SQL: Viste • Q. A che serve definireuna vista? • Implementaremeccanismi di indipendenzatraillivellologicoedillivelloesterno. • Scrivereinterrogazionicomplesse, semplificandone la sintassi. • Garantire la retro-compatibilita’ con precedentiversionidello schema del DB, in caso di ristrutturazionedellostesso.

  39. SQL: Viste Es. Estrarreilnome del dipartimentoche ha la spesapiu’ alta in stipendi. STRUTTURATI

  40. SQL: Viste L’interrogazioneseguentepotrebbenon essereconsentitasualcuni DBMS … SELECT DIPARTIMENTO FROM STRUTTURATI GROUP BY DIPARTIMENTO HAVING SUM(STIPENDIO)>= ALL SELECT SUM(STIPENDIO) FROM STRUTTURATI GROUP BY DIPARTIMENTO

  41. SQL: Viste Soluzione. Creareuna vista chevisualizzi la sommatotaledeglistipendi di ciascundipartimento. CREATE VIEW SPESEDIPARTIMENTI (NOMEDIP, SPESA) AS SELECT DIPARTIMENTO, SUM(STIPENDI) FROM STRUTTURATI GROUPBY DIPARTIMENTO

  42. SQL: Viste STEP2. Estrarreilnome del dipartimentoche ha la spesapiu’ alta in stipendiusando la vista SPESEDIPARTIMENTI. SELECT NOMEDIP FROM SPESEDIPARTIMENTI WHERE SPESA=(SELECT MAX(STIPENDI) FROM SPESEDIPARTIMENTI) Q: E’ possibilescrivere la stessainterrogazionesenzausareuna vista?

  43. SQL: Viste • Q. A che serve definireuna vista? • Implementaremeccanismi di indipendenzatraillivellologicoedillivelloesterno. • Scrivereinterrogazionicomplesse, semplificandone la sintassi. • Garantire la retro-compatibilita’ con precedentiversionidello schema del DB, in caso di ristrutturazionedellostesso.

  44. SQL: Viste Si supponga di avere un DB, in cui la tabella: ESAMI(Matricola,Nome,Cognome,Data,Voto) Vienesostituita con le tabelle: STUDENTI(Matricola,Nome,Cognome) PROVE(Matricola,Data,Voto) Con le viste, e’ possibilemantenereanche la visioneoriginariadel DB …

  45. SQL: Viste Con le viste, e’ possibilemantenereanche la visioneoriginariadel DB … CREATE VIEW ESAMI(MATRICOLA, NOME, COGNOME, DATA, VOTO) ASSELECT S.*, P.DATA, P.VOTO FROM STUDENTI AS S, PROVE AS P WHERE S.MATRICOLA=P.MATRICOLA

  46. SQL: Viste In generale, l’aggiornamento di una vista e’ un’ operazione molto delicata, ed e’ consentita solo in un sottoinsieme (limitato) di casi … In molti DBMS commerciali, non e’ consentitol’aggiornamento di vistechesonoottenute da piu’ di unatabella. CREATE VIEW CAPI(NOME, TELEFONO) AS SELECT I.NOME,U.TEL FROM IMPIEGATI AS I,UFFICI AS U WHERE (I.NOME=U.NOME) AND (I.RUOLO=“C”) Piu’ di unatabella, vista non aggiornabile!!

  47. SQL: Viste L’opzioneWITH CHECK OPTIONconsente di definirevisteaggiornabili, a condizioneche le tuple aggiornatecontinuino ad appartenerealla vista (in pratica, la tuplaaggiornata non deveviolare la clausolaWHERE). CREATE VIEW PROFESSORIRICCHI(CODICE,NOME,COGNOME,STIPENDIO) AS SELECT CODICE,NOME,COGNOME,STIPENDIO FROM PROFESSORI WHERE (STIPENDIO>=30000)

  48. SQL: Viste PROFESSORI UPDATE PROFESSORIRICCHI SET STIPENDIO=20000 WHERE (CODICE=5) PROFESSORIRICCHI Operazione NON consentita!!

  49. SQL: Viste • Una vista puo’ esserecostruita a partire da altrevistedello schema (viste derivate) … • E’ possibilel’aggiornamento di viste derivate? • WITH LOCAL CHECK OPTION  Il controllo di validita’ silimitaalla vista corrente. • WITH CASCADECHECK OPTION  Il controllo di validita’ siestendericorsivamente a tutte le viste da cui la corrente e’ derivata.

  50. SQL: Viste Le Common Table Expression (CTE) rappresentanovistetemporaneechepossonoessereusate in una query come se fosserouna view a tuttiglieffetti. Differenza con la view  una CTE non esiste a livello di schema del DB! WITH NAME(Attributi) AS …SQLCommand SQL Query (including VIEW in its definition) …

More Related