Transcript SQL - TIMvision
Matteo Longhi
Basi di Dati
S
tructured
Q
uery
L
anguage
Appunti
1
Introduzione • Standard creato nel 1976 da IBM • Aggiornato (versione 2) nel 1992 (ANSI X3.135 e ISO 9075) • Consente di:
–
DDL
: definire la struttura del DB –
DML
: modificare i dati contenuti nel DB 2
DDL: Data Definition Language
Esempio:
(
CREATE TABLE
Personale
matricola cognome char(5), char(30), nome codFiscale dataAssunzione char(20), char(16) not null, date, filiale Funzione livello stipBase via cap citta prov smallint, char(15), smallint, float, char(25), char(5), char(20), char(2) );
3
Tipi di dato
• • • • • • •
CHARACTER(n) CHAR(n) Stringa di lunghezza n DATE TIME INTEGER SMALLINT REAL FLOAT Data nella forma mm/gg/aa Ora nel formato hh:mm Numero intero da -2147483648 a -21474836487 Numero da -32768 a 32767
4
Modifica dei Dati
Per inserire un nuovo record:
INSERT INTO
Personale (matricola,cognome,nome,…,prov)
VALUES
(‘AB541’,’Rossi’,’Mario’,…,’CO’);
Per aggiornare record esistenti:
UPDATE
Personale
SET
livello = 6
WHERE
livello=5;
Per cancellare record esistenti:
DELETE FROM WHERE
Personale stipBase<750; 5
Query
Le principali operazioni di query (interrogazione) sono:
Proiezione
:
visualizzare solo le colonne (campi) specificati
Selezione
:
visualizzare solo le righe (record) che soddisfano una certa condizione
Congiunzione (Join)
:
unire più tabelle
6
Simple Query
Sintassi generale: SELECT FROM [WHERE [DISTINCT] {
Simple Query: esempi
Tabelle:
PERSONALE
(matricola, cognome, nome, codFiscale, dataAssunzione, filiale, funzione, livello, stipBase, via, cap, citta, prov)
DIPENDENZA
(codFiliale, descrizione, indirizzo)
Query - cognome, nome e codice fiscale di tutto il personale:
SELECT cognome, nome, codFiscale FROM Personale
Query - cognome, nome e codice fiscale degli impiegati:
SELECT cognome, nome, codFiscale FROM Personale WHERE funzione=‘impiegato’ 8
Simple Query: DISTINCT
DISTINCT: elimina le righe duplicate nella tabella risultante.
Query – elenco delle professioni presenti in azienda:
SELECT DISTINCT funzione FROM Personale
funzione
Impiegato Dirigente Segretario …
Senza l’uso il distinct:
SELECT funzione FROM Personale
funzione
Impiegato Impiegato Impiegato Dirigente Segretario Segretario … 9
Simple Query: * e AS
Il simbolo * sostituisce l’elenco di tutti i campi.
Query – tutti i dati dei dipendenti che abitano in provincia di milano:
SELECT * FROM Personale WHERE prov=‘MI’ La clausola
AS
permette di modificare l’intestazione di una colonna.
Query – l’elenco delle province da cui provengono i dipendenti:
SELECT DISTINCT prov AS Provincia FROM Personale 10
Query : congiunzione (JOIN)
Query – cognome, nome e indirizzo della filiale in cui lavora ogni dipendente
SELECT Personale.cognome, Personale.nome, Dipendenza.indirizzo
FROM Personale, Dipendenza WHERE Personale.filiale=Dipendenza.codFiliale
E’ necessario specificare la corrispondenza tra la chiave primaria di una tabella e la chiave esterna dell’altra (condizione di join).
11
Query : JOIN (2)
Query – cognome, nome e indirizzo della filiale in cui lavora ogni dipendente
SELECT P.cognome, P.nome, D.indirizzo
FROM Personale AS P, Dipendenza AS D WHERE P.filiale=D.codFiliale
12
Query : JOIN (3)
Nel caso sia necessario effettuare la congiunzione di tre tabelle:
SELECT * FROM Tab1, Tab2, Tab3 WHERE Tab1.chiave=Tab2.chiaveExt AND Tab2.chiave=Tab3.chiaveExt
13
Query : Funzioni di Aggregazione
Funzione
COUNT SUM AVG MIN MAX
Valore restituito
numero di righe somma media valore minimo valore massimo Il risultato è UN SOLO valore!
14
Query : Funzioni di Aggregazione (2)
Query – Numero di impiegati
SELECT COUNT(*) FROM Personale WHERE funzione=‘impiegato’
Query – Numero di province di provenienza degli impiegati
SELECT COUNT( DISTINCT(prov) ) FROM Personale WHERE funzione=‘impiegato’ 15
Query : Funzioni di Aggregazione (3)
Query – La somma degli stipendi che appartengono al livello 3
SELECT SUM(StipBase) FROM Personale WHERE livello=3
Query – Lo stipendio medio degli impiegati
SELECT AVG(StipBase) FROM Personale WHERE funzione=‘impiegato’
Query – Lo stipendio minimo e massimo
SELECT MIN(StipBase), MAX(StipBase) FROM Personale 16
Query : Condizioni di ricerca (1)
BETWEEN
: controlla se un valore è compreso in un intervallo, estremi compresi Query – Cognome e nome dei dipendenti assunti nel 2002
SELECT Cognome, Nome FROM Personale WHERE dataAss BETWEEN 01/01/2001 AND 31/12/2001 (NOT) IN: controlla se un valore appartiene ad un insieme
Query – Cognome e nome dei dipendenti che risiedono nelle province di Milano, Como, Lecco e Bergamo.
SELECT Cognome, Nome FROM Personale WHERE prov IN (‘MI’, ‘CO’, ‘LC’, ‘BG’) 17
Query : Condizioni di ricerca (2)
(NOT) LIKE: confronta un valore usando caratteri jolly
Query – I cognomi dei dipendenti che iniziano per “Ros”.
SELECT Cognome FROM Personale WHERE Cognome LIKE ‘Ros%’
IS (NOT) NULL
: controlla se un campo non è stato compilato Query – Cognome e nome dei dipendenti di cui non si conosce la provincia di residenza.
SELECT Cognome, Nome FROM Personale WHERE prov IS NULL 18
Query : Ordinamenti
ORDER BY ASC|DESC: ordina i risultati in senso crescente (ASC, di
default) o decrescente (DESC) Query – I cognomi dei dipendenti in ordine crescente
SELECT Cognome FROM Personale ORDER BY Cognome
Query – I cognomi degli impiegati in ordine di decrescente di stipendio e, a parità di stipendio, in ordine di cognome crescente
SELECT Cognome, stipBase FROM Personale WHERE funzione=‘impiegato’ ORDER BY stipBase DESC, Cognome 19
Query : Raggruppamenti
GROUP BY: raggruppa le righe aventi lo stesso valore
nelle colonne indicate.
le funzioni di aggregazione restituiscono un valore per ogni raggruppamento
Query – Il numero di impiegati in ciascuna filiale.
SELECT filiale, COUNT(*) AS NumeroDipendenti FROM Personale WHERE funzione=‘impiegato’ GROUP BY filiale 20
Query : Raggruppamenti (2)
HAVING: aggiunge una condizione al GROUP BY.
Si possono usare le funzioni di aggregazione (anche in questo caso riferite ad ogni singolo raggruppamento).
Query – Lo stipendio medio degli impiegati nelle filiali con più di 10 dipendenti.
SELECT filiale, AVG(stipBase) AS StipendioMedio FROM Personale WHERE funzione=‘impiegato’ GROUP BY filiale HAVING COUNT(*)>10 21
Query nidificate
Nella condizione (WHERE) è possibile confrontare un valore con il risultato di un’altra query.
Query – Cognome e nome dei dipendenti con stipendio inferiore alla media
SELECT cognome, nome FROM Personale WHERE stipBase < ( SELECT AVG(stipBase) FROM Personale ) NB: il confronto “<“ è possibile perché la seconda query, grazie alla funzione di aggregazione, restituisce UN SOLO risultato.
22
Query nidificate (2)
Query – Cognome, nome e indirizzo della filiale in cui lavorano i dipendenti per i quali lo stipendio è superiore alla media degli stipendi degli impiegati.
SELECT P.cognome, P.nome, D.indirizzo
FROM Personale AS P, Dipendenza AS D WHERE P.filiale=D.codFiliale AND P.stipBase > ( SELECT AVG(stipBase) ) FROM Personale WHERE funzione=‘impiegato’ 23
Query nidificate: ANY
ANY
: la condizione è vera se il confronto è vero per almeno uno dei valori restituiti dalla seconda query.
Query – Dipendenti che non sono impiegati e che hanno lo stipendio superiore a quello di un qualsiasi impiegato.
SELECT cognome, nome, funzione FROM Personale WHERE funzione <>’impiegato’ AND stipBase > ANY ( SELECT stipBase FROM Personale WHERE funzione=‘impiegato’ ) 24
Query nidificate: ALL
ALL
: la condizione è vera se il confronto è vero per ciascuno dei valori restituiti dalla seconda query.
Query – Dipendenti che non sono impiegati e che hanno lo stipendio superiore a quello di tutti gli impiegati.
SELECT cognome, nome, funzione FROM Personale WHERE funzione <>’impiegato’ AND stipBase > ALL ( SELECT stipBase FROM Personale WHERE funzione=‘impiegato’ ) 25
Query nidificate: IN
(NOT) IN
: la condizione è vera se il valore è compreso tra i valori restituiti dalla seconda query Query – Cognome e nome dei dipendenti che lavorano in filiali con più di 10 dipendenti.
SELECT cognome, nome FROM Personale WHERE filiale IN ( SELECT filiale FROM Personale GROUP BY Filiale HAVING count(*)>10 ) 26
Query nidificate: EXIST
(NOT) EXIST
: la condizione è vera se la seconda query restituisce almeno un risultato Query – Elenco dei dipendenti solo se non esistono dipendenti di sesto livello.
SELECT cognome, nome FROM Personale WHERE NOT EXIST ( SELECT * FROM Personale WHERE livello=6 ) 27