Post on 18-Jul-2020
ìSQL – aspetti essenziali
Sommario
ì Definizioni di dati e tipi di dato in SQL
ì Specifica di vincoli in SQL
ì Interrogazioni di base in SQL
ì Istruzioni INSERT, DELETE, e UPDATE
ì Funzionalità aggiuntive
Giorgio Giacinto 2016
2
SQL
ì SQL è una delle ragioni che hanno determinato il successo commerciale dei DBMS
ì SQL
ì Structured Query Language
ì Istruzioni per la definizione dei dati, formulazione di interrogazioni e aggiornamenti (DDL e DML)
ì Due parti
ì Nucleo centrale di specifiche (core)
ì Estensioni
Giorgio Giacinto 2016
3
Storia
ì PropostoinizialmentedaIBMconilnomeSEQUEL(structuredenglish querylanguage)anni‘70
ì StandardizzatoANSInel1986eISOnel1987ì SQL-86(oSQL1)ì SQL-92(oSQL2)ì SQL:1999ì SQL:2003ì SQL:2006ì SQL:2008ì SQL:2011
Giorgio Giacinto 2016
4
ì SuccessorediIngres,DBMSsviluppatodaMichaelStonebraker all’UniversitàdiBerkley (UCB)dal1977al1985ì M.Stonebraker,vincitoredelpremioACMTuring nel
2014
ì Postgres fusviluppatonelperiodo1986-1994daStonebreaker aUCB
ì Dal1996Postgres èstatosviluppatocomeprogettoopen-sourcecolnomePostgreSQL
Giorgio Giacinto 2016
5
ìDDL
Giorgio Giacinto 2016
6
SQL
Definizione dei Dati e Tipi di Dati
ì Terminologia SQL – Modello relazionale
ì Tabella Relazione
ì Riga Tupla
ì Colonna Attributo
ì Istruzione CREATEì Comando principale per la definizione dei dati
ì Tutte le istruzioni SQL sono terminate con ;
Giorgio Giacinto 2016
7
SQL
Schema e Catalogo
ì Schema
ì nome
ì identificatore di autorizzazione
ì un descrittore per ciascun elemento
ì Elementi dello schema
ì Tabelle
ì Vincoli
ì Viste
ì Domini
ì Altri costruttiGiorgio Giacinto 2016
8
SQL
Schema e Catalogo
ì Istruzione CREATE SCHEMACREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith’;
ì Catalogo
ì Raccolta di schemi cui è associato un nome in
un ambiente SQL
ì Ambiente SQL
ì Una installazione di un RDBMS con SQL in un
computer
Giorgio Giacinto 2016
9
CREATE TABLE
ì Creaunanuovarelazioneì Nomeì Attributievincoli
CREATE TABLE COMPANY.EMPLOYEE ...oppure
CREATE TABLE EMPLOYEE ...
ì Letabellecreateconl’istruzioneCREATE TABLEsonochiamatetabelledibase
Giorgio Giacinto 2016
10
Esempio
Giorgio Giacinto 2016
11
Esempio
Giorgio Giacinto 2016
12
Esempio
Giorgio Giacinto 2016
13
Foreign Key
ì Alcune definizioni di FOREIGN KEY possono creare errori
ì Riferimenti circolari
ì Riferimenti a tabelle non ancora create
ì Soluzione
ì Si creano inizialmente le tabelle omettendo i vincoli problematici
ì I vincoli mancanti si aggiungono successivamente usando l’istruzione ALTER TABLE
Giorgio Giacinto 2016
14
ìTipi di Dato e Domini
Giorgio Giacinto 2016
15
Tipi di Dato di Base
ì Numeric
ì Numeri interi: INTEGER, INT, e SMALLINTì Floating-point (numeri reali) : FLOAT o REAL, e
DOUBLE PRECISION
ì Stringhe di caratteri
ì Lunghezza fissa: CHAR(n), CHARACTER(n)ì Lunghezza variabile
VARCHAR(n), CHAR VARYING(n),
CHARACTER VARYING(n)
Giorgio Giacinto 2016
16
Altri tipi di dato di base
ì Stringhe di bit
ì Lunghezza fissa: BIT(n)ì Lunghezza variabile:BIT VARYING(n)
ì BOOLEANì Valori TRUE o FALSE o UNKNOWN
ì DATEì Dieci elementi
ì Composto da YEAR, MONTH, e DAY nel formato YYYY-MM-DD
Giorgio Giacinto 2016
17
Ulteriori tipi di dato
ì TIMESTAMPì Formato dai campi DATE e TIME
ì Almeno 6 posizioni decimali per la rappresentazione delle frazioni di secondo
ì Qualificatore opzionale: WITH TIME ZONE
ì INTERVALì Specifica un valore relativo che può essere
usato per incrementare/decrementare un valore
assoluto di data, tempo e timestamp
Giorgio Giacinto 2016
18
Domini
ì Il nome del domiinio è utile per la definizione
degli attributi
ì Rende agevole la modifica del tipo di dato per
un dominio condiviso da più attributi.
ì Migliora la leggibilità dello schema
ì Esempio
CREATE DOMAIN SSN_TYPE AS CHAR(9);
Giorgio Giacinto 2016
19
ìSpecifica dei vincoli in SQL
Giorgio Giacinto 2016
20
Vincoli di base
ì Vincoli di chiave
ì Vincoli di integrità referenziale (FOREIGN KEY)
ì Restrizioni sui valori degli attributi (domini)
ì Restrizioni sulla presenza di valori NULL
ì Vincoli su singole tuple
Giorgio Giacinto 2016
21
Vincoli su attributi e valori predefiniti
ì NOT NULL ì Specificato per ciascun attributo per il quale non
è consentito l’uso del valore NULL
ì Valore predefinito (default)ì DEFAULT <value>
ì CHECKì Dnumber INT NOT NULL CHECK
(Dnumber > 0 AND Dnumber < 21);ì Può essere usato nella definizione di dominio
Giorgio Giacinto 2016
22
Esempio
Giorgio Giacinto 2016
23
Esempio
Giorgio Giacinto 2016
24
Vincoli di chiave
ì PRIMARY KEYì Specifica l’attributo (o gli attributi) che
costituiscono la chiave primaria della relazione
Dnumber INT PRIMARY KEY,
ì UNIQUEì Specifica altre chiavi candidate
Dname VARCHAR(15) UNIQUE,
Giorgio Giacinto 2016
25
Vincoli di integrità referenziale
ì FOREIGN KEYì Comportamento di default
ì Aggiornamenti e cancellazioni nella tabella riferita
non sono consentite
ì Si possono prevedere azioni alternativeON DELETE e ON UPDATEcon le azioniSET NULL,CASCADE,oSET DEFAULT
Giorgio Giacinto 2016
26
Attribuzione di un nome ai vincoli
ì Facoltativo
ì CONSTRAINTì Consente di fare riferimento a un vincolo al di
fuori dello schema nel quale è definito
ì Utile per successive modiche o cancellazioi del
vincolo
Giorgio Giacinto 2016
27
Vincoli su tuple con CHECK
ì CHECK può essere usato alla fine
dell’istruzione CREATE TABLEì Effettua la verifica su ciascuna tupla
ì Esempio
ì Se nella tabella Dipartimento ci fosse un attributo DATA_CREAZ_DIP, allora
CHECK (Data_creaz_dip <= Data_inizio_dir);
Giorgio Giacinto 2016
28
ìInterrogazioni fondamentali in SQL
Giorgio Giacinto 2016
29
Istruzione SELECT
ì Istruzione fondamentale per recuperare
infomazioni da una base di dati
ì Il linguaggio SQL permette che in una tabella vi
siano più tuple identiche
ì Comportamento difforme dalla definizione di relazione nel modello relazionale
ì Comportamento compatibile con il concetto di
multinsieme (bag di tuple)
Giorgio Giacinto 2016
30
Struttura di base di una interrogazione
SELECT <elencoattributi>FROM <elencotabelle>WHERE <condizioni>
Principalioperatorilogiciusatiperesprimerele<condizioni><<=>==><>
Utilizzatipertuttiitipididatoordinati(numerici,testo,data,ora,ecc.)
Giorgio Giacinto 2016
31
Interrogazione 0
ì Mostraredatadinascitaeindirizzodi‘JohnSmith’
ì GliattributinellaclausolaSELECT sonoanchechiamatiattributidiproiezione
ì LecondizioninellaclausolaWHERE sonoanchechiamatecondizionidiselezione
ì Possiamopensareall’esecuzionediunainterrogazionecomesefosseuncicloiterativo
Giorgio Giacinto 2016
32
Risultato Interrogazione 0
Giorgio Giacinto 2016
33
Interrogazione 1
ì Mostrarenomeeindirizzodeidipendentichelavoranoneldipartimento‘Ricerca’
ì LacondizioneDnumber =Dno èdettacondizionedijoin
Giorgio Giacinto 2016
34
Risultato Interrogazione 1
Giorgio Giacinto 2016
35
Interrogazione 2
ì Perogniprogettoconsedea‘Stafford’elencareilnumerodelprogetto,ilnumerodeldipartimentochelocontrolla,ilcognome,l’indirizzoeladatadinascitadeldirettoredeldipartimento
Giorgio Giacinto 2016
36
Risultato Interrogazione 2
Giorgio Giacinto 2016
37
Nomi di attributi ambigui
ì Lo stesso nome può essere usato per due o
più attributi in tabelle diverse
ì Se in una interrogazione si deve usare un
attributo il cui nome compare in più di una delle
tabelle nel FROM, occorre qualificarlo con il
nome della tabella
Giorgio Giacinto 2016
38
Alias e variabili di tupla
ì È possibile assegnare nomi alternativi per le relazioni presenti nel FROMì abbreviare la scrittura delle interrogazioni
SELECT E.nameFROM Employee AS E
ì In genere la parola ‘AS’ si omette
SELECT E.name
FROM Employee E
ì è possibile anche rinominare gli attributi di una
relazione
ì conoscendo l’ordine degli attributi nella definizione
Giorgio Giacinto 2016
39
Interrogazione 8
ì Perciascundipendentemostrareilnomeeilcognome,eilnomeeilcognomedelsuoimmediatosupervisoreSELECT E.fname, E.lname, S.fname, S.lname
FROM Employee E, Employee S
WHERE E.superssn = S.ssn;
Giorgio Giacinto 2016
40
Risultato Interrogazione 8
Giorgio Giacinto 2016
41
Clausola WHERE mancante
ì Nessuna condizione di selezione
Risultato:CROSS PRODUCTì tutte le possibili combinazioni di tuple
Giorgio Giacinto 2016
42
Interrogazione 9 e 10
ì I9: MostraregliSSNdituttigliimpiegatiSELECT ssn
FROM Employee;
ì I10: MostraretuttelepossibilicombinazionifragliSSNdegliimpiegatieinomideidipartimenti
SELECT ssn, dname
FROM Employee, Department;
Giorgio Giacinto 2016
43
Risultato Interrogazione 9
Giorgio Giacinto 2016
44
Risultato Interrogazione 10
Giorgio Giacinto 2016
45
Uso di * nella clausola SELECT
ì Permettedimostraretuttigliattributidelletupleselezionate
Giorgio Giacinto 2016
46
ìOperatori Insiemistici
Giorgio Giacinto 2016
47
Tabelle e insiemi in SQL
ì SQL non elimina automaticamente eventuali
tuple duplicate fra i risultati di una interrogazioe
ì Per eliminare duplicati dai risultatisi usa la parola DISTINCT in SELECT
Giorgio Giacinto 2016
48
Interrogazione 11 e 11A
ì I11: MostrailsalariodituttiIdipendenti
ì I11A: Mostratuttiivaloridistintidisalario
Giorgio Giacinto 2016
49
Operatori insiemistici in SQL
ì UNION, EXCEPT (differenza), INTERSECTì Sono supportate anche operazioni multinsieme
UNION ALL, EXCEPT ALL, INTERSECT ALL
ì Gli operatori insiemistici sono utilizzabili solo se
le relazioni coinvolte hanno
ì stesso numero di attributi
ì stesso ordine dei domini degli attributi
Giorgio Giacinto 2016
50
Interrogazione 4
ì Creareunelencodituttiinumeridiprogettorelativiaprogettichecoinvolgonodipendentidicognome‘Smith’comepartecipanteocomedirettoredidipartimentochecontrollailprogetto
(SELECT DISTINCT pnumberFROM project,department,employeeWHERE dnum=dnumber ANDmgrssn=ss
AND lname =‘Smith’)UNION(SELECT DISTINCT pnoFROM works_on,employeeWHERE essn =ssn AND lname =‘Smith’)
Giorgio Giacinto 2016
51
ìConfronti di sottostringhe
Giorgio Giacinto 2016
52
L’operatore LIKE
ì Usato per il confronto di sottostringhe rispetto a
un modello
(string pattern matching)
ì il carattere % sostituisce zero o un numero
arbitrario di caratteri
ì Il carattere _ (underscore) sostituisce un singolo
carattere
ì per utilizzare i caratteri %, _, apici, ecc. si può definire un carattere ESCAPE
Giorgio Giacinto 2016
53
Interrogazione 12
ì SitrovinotuttiidipendentiilcuiindirizzoèaHouston,inTexas
SELECT fname, lname
FROM Employee
WHERE address LIKE ‘%Houston, TX%’
Giorgio Giacinto 2016
54
Interrogazione 12A
ì Sitrovinotuttiidipendentinatineglianni50
SELECT fname, lname
FROM Employee
WHERE bdate LIKE ‘__5_______’
Giorgio Giacinto 2016
55
ìOperatori aritmetici e selezioni per intervallo
Giorgio Giacinto 2016
56
Operatori aritmetici
ì Nella clausola SELECT possono essere usati
ì addizione (+)
ì sottrazione (–)
ì moltiplicazione (*)
ì divisione (/)
Giorgio Giacinto 2016
57
Interrogazione 13
ì Mostrareglistipendirisultantidall’attribuzionediunaumentodel10%aidipendentichelavoranoalprogetto‘ProductX’
SELECT e.fname, e.lname, 1.1*e.salary AS new_salary
FROM employee e, project p,works_on w
WHERE e.ssn = w.essn ANDw.pno = p.pnumber ANDp.pname = ‘ProductX’
Giorgio Giacinto 2016
58
Selezione per intervallo
ì Quandolacondizionediselezioneèun’intervallodivalori,duealternativeì valore>=v_minANDvalore<=v_maxì valoreBETWEEN v_minANDv_max
Giorgio Giacinto 2016
59
Interrogazione 13
ì MostrareIdipendentiilcuistipendioècompresofra30.000e50.000euro
SELECT fname, lname
FROM employee
WHERE salary BETWEEN 30000 AND50000
Giorgio Giacinto 2016
60
ìOrdinamento dei risultati
Giorgio Giacinto 2016
61
Clausola ORDER BY
ì Si inserisce al termine della interrogazione
ì Ordinamento discendente con DESCORDER BY D.Dname DESC
ì Ordinamento ascendente con ASCORDER BY E.Lname ASCì Comportamento predefinito
Giorgio Giacinto 2016
62
Interrogazione 18
ì Mostraretuttiidipendentieiprogettiincuilavoranoordinandoirisultatiinbasealnomedeldipartimentoeidipendentiinordinealfabetico.
SELECT d.dname, e.fname, e.lname, p.pname
FROM department d, employee e,works_on w, project p,
WHERE e.dno = d.dnumber ANDe.ssn = w.essn ANDp.pnumber = w.pno
ORDER BY d.dname, e.lname, e.fname
Giorgio Giacinto 2016
63
In sintesi…
Giorgio Giacinto 2016
64
ìInserimenti, Aggiornamenti e Cancellazioni di tuple in SQL
Giorgio Giacinto 2016
65
INSERT
ì Due alternative
ì Inserimento tuple singole
ì Inserimento di un insieme di valori risultato di
una interrogazione
Giorgio Giacinto 2016
66
DELETE
ì Cancella da una relazione le tuple che
soddisfano una certa condizione
ì clausola WHERE
Giorgio Giacinto 2016
67
UPDATE
ì Modifica gli attributi di una o più tuple che
soddisfano una certa condizione
ì clausola WHERE
ì Clausola SET per specificare gli attributi da
modificare e impostare il nuovo valore
Giorgio Giacinto 2016
68