Transcript ISTRUZIONI SQL

TIPI STANDARD DI SQL
BOOLEAN
Valori: vero, falso
CHAR(n)
Stringa di lunghezza n (fissa)
DATE
Data nella forma MM/GG/AA
TIME
Ora nella forma HH:MM
INTEGER
Numero intero con precisione 10
SMALLINT
Numero intero con precisione 5
FLOAT
Numero reale con mantissa di precisione 15
Precisione indica il numero max di cifre che il n° può contenere, escluso segno e . (dec)
Mantissa indica il numero di cifre che seguono il punto decimale.
Le costanti stringa sono delimitate tramite l’apice ‘.
Il linguaggio SQL può controllare la presenza e l’assenza di Null in una colonna. Tutti i
risultati di confronto numerici o stringa con attributi di valore Null sono sconosciuti.
DBMS (DataBase Management System)
Un DBMS deve essere in grado di:
• Permettere la creazione di una nuova base di dati, definendo la composizione
degli archivi, la loro articolazione, le correlazioni logiche tra essi, gli accessi
ed i vincoli al progetto, mediante il DDL (Data Definition Language);
• Facilitare gli utenti nell’inserimento dati, cancellazione e variazione dei dati del
DB, sfruttando il DML (Data Manipulation Language);
• Rendere possibile l’estrazione di informazioni dal DB mediante il linguaggio di
interrogazione QL (Query Language);
• Fornire o revocare agli utenti i permessi necessari per poter utilizzare i
comandi DML e DDL, usando il linguaggio DCL (Data Control Language).
DDL:
CREATE TABLE, ALTER T., DROP T.
DML:
INSERT INTO TABLE, DELETE, UPDATE … SET
DQL:
SELECT, WHERE, SELECT + WHERE
2
CREATE TABLE
CREATE TABLE Impiegati
(ID
Cognome
Nome
Residenza
Stipendio
Dipartimento
smallint primary key,
char(15) not null,
char(15) not null,
char(15) default ‘*** Manca Residenza’,
float,
char(15) references Dipartimenti(Codice));
UNIQUE (Cognome, Nome, Dipartimento);
Altre costruzioni per le chiavi:
1) Se la chiave primaria è composta da più attributi si mettono alla fine
primary key (Matricola, CodCorso);
2) La chiave esterna è dichiarata come modifica:
ALTER TABLE Impiegati
add constraint ffk foreign key (Dipartimento) references Dipartimenti(Codice);
ALTER TABLE
Serve per aggiungere una nuova colonna ed è seguito da ADD:
ALTER TABLE Impiegati
ADD AnnoNascita;
Serve per cancellare una colonna ed è seguito da DROP:
ALTER TABLE Impiegati
DROP Residenza;
CREATE INDEX
Viene utilizzato per creare un nuovo indice su una tabella esistente, che
esegue una selezione su uno o più campi specifici.
CREATE UNIQUE INDEX IndiceImpiegati
ON (Cognome, Nome);
INSERT, DELETE e UPDATE
Per inserire i valori di una nuova riga in Impiegati:
INSERT INTO Impiegati (ID,Nome,Cognome,Residenza,Stipendio,
Dipartimento) VALUES (20, ‘Mario’, ‘Rossi’, ‘Caserta’, 31500, ‘Mag’);
Per assegnare il dipendente con ID=20 al dipartimento Produzione:
UPDATE Impiegati
SET Dipartimento= ‘Prod’
WHERE ID= 20;
Per aumentare del 5% lo stipendio di tutti i dipendenti del Dipart. Produzione:
UPDATE Impiegati
SET Stipendio= Stipendio*1,05
WHERE Dipartimento= ‘Prod’;
Per cancellare da Impiegati i dati del dipendente con ID=20:
DELETE FROM Impiegati
WHERE ID= 20;
Query di inserimento: INSERT INTO
IDUser
FullName
Indirizzo
Interno
1
Mario Bianchi
Via Verdi, 5
7894
2
Carlo Verdi
Via Roma, 45
1546
INSERT INTO dbase.tabella_dipendenti (IDUser,FullName,Interno) values
(3,’Antonio Rossi’, 1234);
IDUser
FullName
Indirizzo
Interno
1
Mario Bianchi
Via Verdi, 5
7894
2
Carlo Verdi
Via Roma, 45
1546
3
Antonio Rossi
1234
UPDATE … SET … WHERE
Questa istruzione serve per modificare i dati in una tabella.
UPDATE Persone SET Nome=‘Paperone’ WHERE Cognome= ‘De Paperoni’;
Query di cancellazione: DELETE
DELETE FROM Dbase.Persone WHERE Cognome= ‘De Paperoni’;
ALIAS
Sono usati per dare nomi ai campi (1) e alle tabelle (2).
(1) SELECT campo AS alias_campo FROM tabella;
(2) SELECT campo1, campo2 FROM Nometabella AS AliasTabella;
• Query di impiegati (nome, cognome impiegati, descrizione dipartimento) che
hanno sede lavorativa a Roma
SELECT I.Cognome, I.Nome, D.Descrizione
FROM Impiegati AS I, Dipartimenti AS D
WHERE I.Dipartimento = D.Codice AND D.Sede = ‘Roma’;
ISTRUZIONI SQL
SELECT è usata per selezionare dei dati da una tabella.
Sintassi SELECT campo1, campo2, … FROM NomeDB.NomeTabella;
Esempio SELECT id, nome, telefono FROM Dbtelefoni.telefoni;
id
nome
telefono
1
Pippo
0444/543188
2
Pluto
0444/991761
3
Paperino
0444/746111
4
Topolino
0444/519922
Per vedere tutti i campi del database SELECT * FROM Dbtelefoni.telefoni;
id
nome
indirizzo
telefono
1
Pippo
Via Rossi, 4
0444/543188
2
Pluto
Via Piave, 11
0444/991761
3
Paperino
Via Roma, 1
0444/746111
4
Topolino
Via Verdi, 6
0444/519922
ISTRUZIONI SQL
SELECT DISTINCT è utilizzata per restituire soltanto dei valori distinti.
SELECT Società FROM Ordini;
Società
Microsoft
Oracle
Microsoft
HP
Mentre SELECT DISTINCT Società FROM Ordini;
Società
Microsoft
Oracle
HP
ORDER BY
È possibile ordinare l’ elenco ottenuto con il comando ORDER BY NomeCampo.
SELECT id, nome, telefono FROM Dbtelefoni.telefoni ORDER BY nome;
id
nome
telefono
3
Paperino
0444/746111
1
Pippo
0444/543188
2
Pluto
0444/991761
4
Topolino
0444/519922
SELECT id, nome, telefono FROM Dbtelefoni.telefoni ORDER BY nome DESC;
id
nome
telefono
4
Topolino
0444/519922
2
Pluto
0444/991761
1
Pippo
0444/543188
3
Paperino
0444/746111
WHERE
SELECT id, nome, telefono FROM Dbtelefoni.telefoni WHERE nome= ‘Topolino’
ORDER BY nome DESC;
id
4
nome
Topolino
telefono
0444/519922
SELECT id, nome, telefono FROM Dbtelefoni.telefoni WHERE nome= ‘Topolino’
AND id >= 4;
id
nome
telefono
4
Topolino
0444/519922
9
Topolino
0444/214569
WHERE
SELECT id, nome, telefono FROM Dbtelefoni.telefoni WHERE nome= ‘Topolino’ OR
id >= 4;
id
nome
telefono
4
Topolino
0444/519922
45
Paperoga
0444/124589
9
Topolino
0444/214569
12
Clarabella
0444/927856
Da notare che se il valore che viene cercato è una stringa va inserito tra apici.
SELECT id, nome, telefono FROM Dbtelefoni.telefoni WHERE nome= Topolino OR
id >= 4;
restituirebbe un errore perché Topolino è una stringa.
BETWEEN … AND
Seleziona un intervallo di dati tra due valori, che possono essere numeri, testo, date.
SELECT * FROM telefoni WHERE id BETWEEN 4 AND 22;
oppure
SELECT * FROM telefoni WHERE id NOT BETWEEN 4 AND 22;
LIKE
SELECT id, nome FROM Dbtelefoni.telefoni WHERE nome LIKE ‘%ert%’;
id
nome
405
Roberto
421
Alberto
437
Roberto
783
Umberto
• SELECT id, nome FROM Dbtelefoni.telefoni WHERE nome LIKE ‘Pa%’;
Cerca quello che inizia con Pa e finisce con qualche altra.
• SELECT id, nome FROM Dbtelefoni.telefoni WHERE nome LIKE ‘_upa’
Cerca le stringhe di 4 caratteri che finiscono con ‘upa’.
Invece:
• SELECT id, nome FROM Dbtelefoni.telefoni WHERE nome
LIKE ‘Anna_M%’ ESCAPE
In questo caso l’ underscore ‘_’ non è sostituto di un carattere ma è proprio _,
quindi metto ESCAPE.
ALTRE CONDIZIONI DEL WHERE
IN
Controlla se un valore appartiene ad uno dei valori della
lista precisata dopo In all’interno della condizione.
INTO
È usato per indicare i valori dentro una selezione.
IS NULL
Controlla la presenza del valore null in una colonna.
IS NOT NULL
Per valutare la condizione opposta alla precedente.
GROUP BY
Permette di raggruppare un insieme di righe aventi lo
stesso valore nelle colonne indicate dalla clausola.*
HAVING
(con il Group by): dopo l’having, è controllato il valore
restituito dalle funzioni di aggregazione.
TOP
Restituisce le prime n righe di una tabella.
AND
È applicato prima di OR.
OR
NOT
È applicato prima di AND.
*: tutti gli attributi che compaiono nella lista accanto al Select devono essere inclusi nel
Group by oppure devono essere argomenti di una funzione di aggregazione.
FUNZIONI DI AGGREGAZIONE
SELECT funzione(campo) FROM NomeTabella;
Funzione
Descrizione
AVG (coloumn)
Restituisce il valore medio di una colonna
COUNT (coloumn)
Restituisce il numero di righe (escludendo quelle con
valore null) di una colonna
COUNT (*)
Restituisce il numero di righe selezionate
FIRST (coloumn)
Restituisce il valore del primo record di un campo
specificato
LAST (coloumn)
Restituisce il valore dell’ultimo record di un campo
specificato
MAX [or MIN](coloumn)
Restituisce il valore massimo [o minimo] in una colonna
SUM (coloumn)
Restituisce la somma dei valori di una colonna
QUERY ANNIDATE
Si chiamano così perché il comando Select offre la possibilità di inserire un
comando Select all’interno della struttura di un altro comando Select.
• Query dei clienti (nome, cognome, reddito) che hanno il reddito massimo
SELECT nome, cognome, reddito
FROM Clienti
WHERE (reddito= SELECT MAX (reddito)
FROM Clienti));
• Query dei clienti (nome, cognome, reddito) che abitano in città che hanno
almeno 2 clienti
SELECT nome, cognome, reddito
FROM Clienti
WHERE città IN (SELECT città
FROM Clienti
GROUP BY città
HAVING COUNT (*) >= 2);
QUERY ANNIDATE
ANY è utilizzato in una clausola Where in espressioni : x > ANY Elenco.
• Query dei dipendenti (nome, cognome) del Dipartimento Amministrazione
che hanno lo stipendio superiore a quello di almeno un impiegato del MKT
SELECT nome, cognome
FROM Impiegati
WHERE Dipartimento= ‘Amm’ AND
Stipendio > ANY (SELECT stipendio
FROM Impiegati
WHERE Dipartimento= ‘Mkt’ );
ALL è usato in una clausola Where in espressioni : x <= ALL Elenco.
• Query dei dipendenti (nome, cognome) dell’ Amministrazione che hanno lo
stipendio superiore a quello di tutti i dipendenti del MKT
SELECT nome, cognome
FROM Impiegati
WHERE Dipartimento= ‘Amm’ AND
Stipendio > ALL (SELECT stipendio
FROM Impiegati
WHERE Dipartimento= ‘Mkt’ );
QUERY ANNIDATE
Valgono le seguenti equivalenze:
Attributo IN (Select…)
equivale a:
Attributo = ANY (Select…)
Attributo NOT IN (Select…)
equivale a: Attributo <> ALL (Select…)
EXISTS è utilizzato in una clausola Where in espressioni : EXISTS Elenco.
• Query dei dipendenti (nome, cognome, scostamento dalla media degli
stipendi) del MKT, solo se esistono dipendenti che lavorano in quel dipart.
SELECT nome, cognome, stipendio – (SELECT AVG(stipendio)
FROM Impiegati
WHERE Dipartimento= ‘Mkt’)
FROM Impiegati
WHERE EXISTS (SELECT *
FROM Impiegati
WHERE Dipartimento= ‘Mkt’ );