Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 1
BASI DI DATI
ESERCITAZIONI -03-MSSQL - SQL
Email: [email protected]
Università di TrentoCorso di Laurea in Ingegneria dell’Informazione e dell’Organizzazione
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 2
Inserire curriculum..
.. In Comunità On Line
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 3
SQL: Il linguaggio dei DBMSSQL: Il linguaggio dei DBMSSQL = Structured Query Language
Anni ’70Definizione del modello relazionale.Prima definizione del linguaggio SQL (allora SEQUEL)Prototipi di DBMS relazionali- System R (IBM)- Ingres (Università di Berkeley)
Anni ’80Standardizzazione di SQL. Primi DB commerciali- Oracle- IBM DB2
Anni ’90Standard ISO-ANSI-SQL2 (SQL 92)
Anni ’00Standard ISO-ANSI-SQL3 (SQL 99) Recepimento non completato.
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 4
SQL: Il linguaggio dei DBMS
DDL (Data Definition Language) Definizione Schemi e autorizzazioni
(create database – drop database – alter table – drop table)
DML (Data Manipulation Language) Aggiornamento istanze DB
(insert into – update - delete) Estrazione informazioni da DB
(Select [all|distinct])
DCL (Data Control Language) Comandi di vario tipo compreso controllo degli accessi
SQL = DDL + DML + DCL
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 5
Principali comandi in SQL (DDL)
Crea DBcreate database “<database>”create database “prova”
Elimina DBdrop database “<database>”drop database “prova”
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 6
Principali comandi in SQL (DDL) Crea una tabella
create table <nome tabella> (nomecampo | tipo | constaint, ……)create table clienti ( chiave integer not null primary key, nome varchar(30), …..)
Modifica una tabella esistente alter table <nome tabella> add nomecampo | tipo | constaint, drop nomecampo, …… alter table clienti add indirizzo varchar(30), drop frazione add primary key (codice)
Elimina una tabelladrop table <nome tabella>drop table clienti
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 7
Create Database & Table
create database PROVA
go
use PROVA
create table TBinPROVA (conta integer ..)
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 8
Principali comandi in SQL (DDL)
Crea una vistacreate view <nome vista> <campi> as <select>
create view UltimaFattura (fornitore,data,importo) as Select fornitore,data,importo from fatture F Where f.Data = ( Select Max(Data) From fatture F1 Where (F1.fornitore = F.fornitore) )
Elimina un vistadrop view <nome vista>
drop view Ultimafattura
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 9
Principali comandi in SQL (DDL)
Crea una dominioIn SQL è possibile creare un dominio personalizzato ereditando caratteristiche dai principali data types
create domain <nome domain> as <caratteristica domain>
create domain VOTO as SMALLINTDEFAULT NULLCHECK (VALUE >= 18 and VALUE <= 30 )
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 10
Principali comandi in SQL (DML)
Estrazione dati da Tabella: SELECT
Le interrogazione in un DB avvengono trammite l’istruzione SELECT
Select ATTRIBUTO
From TABELLA
Where CONDIZIONE
L’interrogazione SQL restituisce i valori degli attributi all’interno della tabella selezionata che soddisfano alla condizione specificata.
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 11
Principali comandi in SQL (DML)
Estrazione dati da Tabellaselect [all | distinct] <lista attributi> from <lista tabelle> where<condizione> group by <lista attributi> having <condizione> order by <lista attributi>
select * from fornitori
SELECT LastName, FirstName
FROM dbo.Employees
WHERE (LastName LIKE %K%)
ORDER BY FirstName
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 12
Principali comandi in SQL (DML)
Estrazione dati da Tabella
SELECT TOP 10 *FROM EmployeesWHERE City = London
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 13
WHERE L’argomento della clausola where può essere un
espressione booleana costruita a partire da predicati semplici combinati con gli operatori logici.
Predicati semplici: A sinistra il valore dell’attributo specificato, a destra un valore costante
= …uguale<> …diverso< …minore di> …maggiore di>= …maggiore e uguale di<= …minore e uguale di
Operatori logici: ANDORNOT
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 14
LIKE Serve per porre un uguaglianza non completa. Viene utilizzato insieme a caratteri speciali. Se questi non
esistono nella condizione assume il valore di “=“
Caratteri speciali: _ (underscore) o ! (punto esclamativo): indicano la presenza di un
qualsiasi carattere % (percentuale) o * (asterisco): indicano la presenza di una serie di
caratteri qualsiasi
SELECT *FROM EmployeesWHERE FirstNameLIKE R_be*
ROBERTRIBERIBES
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 15
BETWEEN Serve per identificare un range in cui applicare la
condizione. Si specifica un intervallo di valori in cui deve essere
compreso il valore affinché sia considerato vero
SELECT *FROM ProductsWHERE (UnitPrice BETWEEN 18 AND 20)
SELECT *FROM EmployeesWHERE (LastName BETWEEN 'f*' AND 'l*')
Federico
Giovanni
Laura
18
18,5
19
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 16
IN
Serve per specificare una serie di valori a cui deve soddisfare la condizione.
SELECT *FROM ProductsWHERE UnitPrice IN (18, 19)
Si evita di fare 2 where sullo stesso attributo
WHERE UnitPrice = 18 or UnitPrice = 19
18
19
19
18
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 17
IS NULL Serve per specificare un estrazione con riferimento al
valore NULL
IS NULL oppure IS NOT NULL
Tutti i campi di una colonna che hanno valore NULL Importante ricordare che 0 e “” posseggono un valore,
quindi non sono NULL
SELECT *FROM EmployeesWHERE Region IS NULL
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 18
DISTINCT Serve per non ottenere righe duplicate Se nella select viene applicato a più colonne, adopererà
l’univocità su tutte le colonne considerate
SELECT DISTINCT CityFROM Employees
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 19
ORDER BY
La clausola ORDER BY serve per stabilire l’ordine di visualizzazione
Vi è poi la possibilità di specificare se l’ordinamento avviene per valori ascendenti (ASC) o discendenti (DESC) Di default è impostato ASC
SELECT *
FROM Employees
ORDER BY LastName DESC
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 20
Count / sum / max / min / avg Permettono di ottenere un valore singolo
elaborando un insieme di righe. Vengono applicati sulle righe che soddisfano la condizione presente nella clausola where e hanno il seguente significato: count: conteggia le righe che soddisfano un criterio sum: restituisce la somma dei valori posseduti dall'espressione
costrutta sui nomi degli attributi max: individua il massimo in un insieme di valori min: individua il minimo in un insieme di valori avg: restituisce la media dei valori dell'espressione
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 21
GROUP BY Group by permette di specificare come dividere le tabella
in sottoinsiemi. La clausola group by ammette come argomento un
insieme di attributi e la query raggrupperà le righe che possiedono gli stessi valori per questo insieme di attributi.
SELECT Dipendente, SUM(Stipendio)FROM dbo.tbGROUP BY Dipendente
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 22
Principali comandi in SQL (DML)
Inserimento dati in Tabellainsert into <nome tabella> (campo1,campo2) values (valore1,valore2)
INSERT into employees (firstname, lastname) values ('Paolo','Rossi')
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 23
Principali comandi in SQL (DML)
Aggiornamento dati in Tabella (prevalentemente su condizione where)update <nome tabella> set campo = valore where <condizione>
update employees set firstname='Verdi'
where lastname='Paolo'
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 24
Principali comandi in SQL (DML)
Eliminazione dati in Tabella(prevalentemente su condizione where)
delete <nome tabella> where <condizione>
delete employees where firstname='Verdi'
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 25
Links SQL Injection Tecniche: SQL Injection
(http://sicurezza.html.it/articoli/leggi/973/tecniche-sql-injection/4/)
SQL injection Basic Tutorial (http://www.governmentsecurity.org/articles/SQLinjectionBasicTutorial.php)
SQL Injection: Modes of Attack, Defence, and Why It Matters (http://www.governmentsecurity.org/articles/SQLInjectionModesofAttackDefenceandWhyItMatters.php)
Advanced SQL Injection in SQL Server Applications (http://www.di.unito.it/~matteo/DIDATTICA/aa06/RdE/advanced_sql_injection.pdf)
Steve Friedl's Unixwiz.net Tech Tips: SQL Injection Attacks by Example (http://www.unixwiz.net/techtips/sql-injection.html)
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 26
Lavorando su SQL Importare il db “CDs”
1. Ordinare in modo discendente rispetto al nome tabella tCantante selezionando tutte le colonne
2. Ordinare in modo ascendente rispetto al titolo la tabella tCD selezionando tutte le colonne
3. Ordinare in modo ascendente rispetto al titolo la tabella tCD selezionando le colonne cd_id, cd_titolo, cd_prezzo, cd_anno
4. Ordinare in modo ascendente rispetto al titolo la tabella tCD selezionando le colonne cd_id, cd_titolo, cd_prezzo, cd_annoFiltrare rispetto ai cd prodotti prima del 2000
5. Ordinare in modo ascendente rispetto al titolo la tabella tCD selezionando le colonne cd_id, cd_titolo, cd_prezzo, cd_annoFiltrare rispetto ai cd prodotti nel 1990 e nel 2003
6. Ordinare in modo ascendente rispetto al titolo la tabella tCD selezionando le colonne cd_id, cd_titolo, cd_prezzo, cd_annoFiltrare rispetto ai cd prodotti tra il 1990 e il 2003
Basi di Dati - 2006-2007 (L. Colazzo - F. Magagnino) 27
Lavorando su SQL7. Visualizza il prezzo più alto tra tutti i cd
8. Ordinare rispetto al titolo la tabella tCanzone selezionando le colonne canzone_id, canzone_titoloFiltrare rispetto alle canzoni il cui titolo inizia per "b" oppure "u"
9. Dalla tabella tRelCdCanzone visualizzare il numero di canzoni per ogni cd (id del cd)Dare il nome "Numero Canzoni" alla colonna con le quantità
10. Nella tabella tCantante inserire un nuovo record (un nuovo cantante)cantante_nome=Sting, cantante_descrizione=Sting è un cantante famoso, cantante_sitointernet=www.sting.com, cantante_genere_id=1 (rok, lo sappiamo dalla tabella genere)
11. Nella tabella tCantante aggiornare alcuni campi del record riferito a Stingcantante_descrizione=Sting è un cantante molto famoso, cantante_sitointernet=www.sting.it
12. Cancellare dalla tabella tCantante il record riferito a Sting
Top Related