ì SQL –aspetti essenziali - people.unica.it€¦ · SQL ì SQL è una delle ragioni che hanno...

ì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


3

Storia

ì PropostoinizialmentedaIBMconilnomeSEQUEL(structuredenglish querylanguage)anni‘70

ì StandardizzatoANSInel1986eISOnel1987ì SQL-86(oSQL1)ì SQL-92(oSQL2)ì SQL:1999ì SQL:2003ì SQL:2006ì SQL:2008ì SQL:2011


4

ì SuccessorediIngres,DBMSsviluppatodaMichaelStonebraker all’UniversitàdiBerkley (UCB)dal1977al1985ì M.Stonebraker,vincitoredelpremioACMTuring nel

2014

ì Postgres fusviluppatonelperiodo1986-1994daStonebreaker aUCB

ì Dal1996Postgres èstatosviluppatocomeprogettoopen-sourcecolnomePostgreSQL


5

ìDDL


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 ;


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


9

CREATE TABLE

ì Creaunanuovarelazioneì Nomeì Attributievincoli

CREATE TABLE COMPANY.EMPLOYEE ...oppure

CREATE TABLE EMPLOYEE ...

ì Letabellecreateconl’istruzioneCREATE TABLEsonochiamatetabelledibase


10

Esempio


11

Esempio


12

Esempio


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


14

ìTipi di Dato e Domini


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)


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


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


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);


19

ìSpecifica dei vincoli in SQL


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


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


22

Esempio


23

Esempio


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,


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


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


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);


28

ìInterrogazioni fondamentali in SQL


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)


30

Struttura di base di una interrogazione

SELECT <elencoattributi>FROM <elencotabelle>WHERE <condizioni>

Principalioperatorilogiciusatiperesprimerele<condizioni><<=>==><>

Utilizzatipertuttiitipididatoordinati(numerici,testo,data,ora,ecc.)


31

Interrogazione 0

ì Mostraredatadinascitaeindirizzodi‘JohnSmith’

ì GliattributinellaclausolaSELECT sonoanchechiamatiattributidiproiezione

ì LecondizioninellaclausolaWHERE sonoanchechiamatecondizionidiselezione

ì Possiamopensareall’esecuzionediunainterrogazionecomesefosseuncicloiterativo


32

Risultato Interrogazione 0


33

Interrogazione 1

ì Mostrarenomeeindirizzodeidipendentichelavoranoneldipartimento‘Ricerca’

ì LacondizioneDnumber =Dno èdettacondizionedijoin


34



35

Interrogazione 2

ì Perogniprogettoconsedea‘Stafford’elencareilnumerodelprogetto,ilnumerodeldipartimentochelocontrolla,ilcognome,l’indirizzoeladatadinascitadeldirettoredeldipartimento


36



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


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


39

Interrogazione 8

ì Perciascundipendentemostrareilnomeeilcognome,eilnomeeilcognomedelsuoimmediatosupervisoreSELECT E.fname, E.lname, S.fname, S.lname

FROM Employee E, Employee S

WHERE E.superssn = S.ssn;


40



41

Clausola WHERE mancante

ì Nessuna condizione di selezione

Risultato:CROSS PRODUCTì tutte le possibili combinazioni di tuple


42

Interrogazione 9 e 10

ì I9: MostraregliSSNdituttigliimpiegatiSELECT ssn

FROM Employee;

ì I10: MostraretuttelepossibilicombinazionifragliSSNdegliimpiegatieinomideidipartimenti

SELECT ssn, dname

FROM Employee, Department;


43



44



45

Uso di * nella clausola SELECT

ì Permettedimostraretuttigliattributidelletupleselezionate


46

ìOperatori Insiemistici


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


48

Interrogazione 11 e 11A

ì I11: MostrailsalariodituttiIdipendenti

ì I11A: Mostratuttiivaloridistintidisalario


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


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’)


51

ìConfronti di sottostringhe


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


53

Interrogazione 12

ì SitrovinotuttiidipendentiilcuiindirizzoèaHouston,inTexas

SELECT fname, lname

FROM Employee

WHERE address LIKE ‘%Houston, TX%’


54

Interrogazione 12A

ì Sitrovinotuttiidipendentinatineglianni50

SELECT fname, lname

FROM Employee

WHERE bdate LIKE ‘__5_______’


55

ìOperatori aritmetici e selezioni per intervallo


56

Operatori aritmetici

ì Nella clausola SELECT possono essere usati

ì addizione (+)

ì sottrazione (–)

ì moltiplicazione (*)

ì divisione (/)


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’


58

Selezione per intervallo

ì Quandolacondizionediselezioneèun’intervallodivalori,duealternativeì valore>=v_minANDvalore<=v_maxì valoreBETWEEN v_minANDv_max


59

Interrogazione 13

ì MostrareIdipendentiilcuistipendioècompresofra30.000e50.000euro

SELECT fname, lname

FROM employee

WHERE salary BETWEEN 30000 AND50000


60

ìOrdinamento dei risultati


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


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


63

In sintesi…


64

ìInserimenti, Aggiornamenti e Cancellazioni di tuple in SQL


65

INSERT

ì Due alternative

ì Inserimento tuple singole

ì Inserimento di un insieme di valori risultato di

una interrogazione


66

DELETE

ì Cancella da una relazione le tuple che

soddisfano una certa condizione

ì clausola WHERE


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


68

ì SQL –aspetti essenziali - people.unica.it€¦ · SQL ì SQL è una delle ragioni che hanno...

Documents

Transcript of ì SQL –aspetti essenziali - people.unica.it€¦ · SQL ì SQL è una delle ragioni che hanno...