Linguaggio Sql.

SQL
(STRUCTURED QUERY LANGUAGE)
Prof. Nicoletta D‟Alpaos & Prof. Andrea Borghesan
SQL
DDL
Data Definition Language
DML
Data Manipulation Language
DCL
Data Control Language
DDL
Obiettivo:
Definire la struttura delle relazioni del database (DDL: Data
Definition Language)
DDL
Create
Drop
Alter
2
Create database
Il comando create database serve a creare un nuovo database, che
potrà contenere :
 Tabelle
 Viste
 stored procedure
 Trigger
 altri tipi di oggetti.
3
Nota 1: in Access i database devono essere creati all‟interno dell‟ambiente GUI.
Nota 2: esiste anche:
ALTER DATABASE nome_database CHARACTER SET caratteri;
(caratteri per esempio latin1).
Create database
CREATE DATABASE nome_database;
Esempio:
CREATE DATABASE azienda;
4
Drop database
Il comando drop database serve a cancellare un database.
DROP DATABASE nome_database;
Esempio:
DROP DATABASE azienda;
5
Create table
Il comando create table ha la funzione di creare una nuova
tabella (o tavola).
Una tabella è composta da:
 attributi
 domini
 vincoli
6
Create table
Il nome della tabella:
1. Può essere scritto indifferentemente in MAIUSCOLO o in
minuscolo
2. Può essere formato da lettere e numeri, ma il primo carattere
deve sempre essere una lettera;
3. Non può superare i 30 caratteri di lunghezza;
4. Non può avere lo stesso nome di una tabella o vista già esistente
sullo stesso utente di database.
La tabella può :
 essere creata vuota (create table)
 essere creata e riempita di dati (create table + select)
7
Create table
CREATE TABLE nome_tabella (
nome_campo tipo [(lunghezza)] [NOT NULL | NULL]
[DEFAULT valore_di_default]
[AUTO_INCREMENT]
[[PRIMARY] KEY] [UNIQUE]
[CHECK (espressione)]
[PRIMARY KEY (nome_campo [,…])]
[[UNIQUE] INDEX [nome_indice] (nome_campo [,…])]
[FOREIGN KEY [nome_indice] (nome_campo [,…])
REFERENCES nome_tabella_f nome_campo_f
[ON
DELETE | ON UPDATE
(CASCADE | SET
DEFAULT | SET NULL |
RESTRICT) ] );
8
Create table
CREATE TABLE nome_tabella
AS
SELECT ...;
La struttura della tabella, in questo caso, è definita implicitamente dal numero
di colonne (o campi) estratti dalla select, dal tipo di dato di ciascuna colonna e
dai nomi delle rispettive colonne estratte dalla select (o dagli alias ad esse
applicati).
9
Create table
Esempio:
CREATE TABLE tblAmici (
codice SMALLINT PRIMARY KEY,
nome CHAR (20) NOT NULL,
cognome CHAR (30),
telefono VARCHAR (15) DEFAULT ‘0’,
data_nascita date,
gruppo INTEGER,
UNIQUE (nome,cognome),
FOREIGN KEY (gruppo) REFERENCES
compagnie (id_gruppo) ON DELETE CASCADE
)
10
Create table
…. Che cos’è il tipo ?
Domini:
 Domini elementari (predefiniti)
 Domini definiti dall'utente (semplici, ma riutilizzabili)
(vedi più avanti)
11
Create table
…. Foreign key ?
REFERENCES nome_tabella [ ( colonna1 [ , colonna2 ... ] ) ]
[ ON DELETE { CASCADE | SET DEFAULT | SET NULL } ]
[ ON UPDATE { CASCADE | SET DEFAULT | SET NULL } ]
Le clausole ON DELETE e ON UPDATE indicano quale azione deve essere compiuta nel
caso in cui una tupla nella tabella referenziata venga eliminata o aggiornata. Infatti in tali
casi nella colonna referenziante (che e' quella che si sta definendo) potrebbero esserci dei
valori inconsistenti. Le azioni possono essere:
o CASCADE: eliminare la tupla contenente la colonna referenziante (nel caso di ON
DELETE) o aggiornare anche la colonna referenziante (nel caso di ON UPDATE).
o SET DEFAULT: assegnare alla colonna referenziante il suo valore di default.
o SET NULL: assegnare alla colonna referenziante il valore NULL.
12
Drop table
Il comando “drop table” consente di distruggere una tabella,
eliminandola fisicamente dal database.
 Come tutti i comandi DDL è un'operazione irreversibile, e
provoca la perdita di tutti i dati contenuti nella tabella.
DROP TABLE nome_tabella;
13
Rename
Il comando rename consente di rinominare una tabella.
RENAME nome_tabellaTO nuovo_nome_tabella;
14
SQL
(STRUCTURED QUERY LANGUAGE)
DML
15
DML
Modificare i dati contenuti nel database, con operazioni di
inserimento, variazione e cancellazione (DML: Data
Manipulation Language)
16
DML
Insert
Update
Delete
Select
Insert
Il comando “insert into” permette l’inserimento dei nuovi
dati
INSERT INTO nome_tabella [nome_campo [, …]]
{VALUES (espressione[,…]) | SELECT ….}
17
Insert
Esempi (1):
INSERT INTO studenti (matricola, nome, cognome, via, citta,
telefono)
VALUES (100, “Mario”, “Rossi”, “viale indipendenza 15”, “Milano”,
“02-12345678”);
INSERT INTO studenti (matricola, nome, cognome, via, citta,
telefono) VALUES
(100, “Mario”, “Rossi”, “viale indipendenza 15”, “Milano”, “0212345678”),
(101,“Valentina”,”Bianchi”, “Piazza delle Erbe”, “Padova”, “04998765432”);
18
Insert
Esempi (2):
INSERT INTO studenti
VALUES (100, “Mario”, “Rossi”);
INSERT INTO studenti (matricola, nome, cognome, citta)
SELECT matricola, nome, cognome, citta
FROM nuoviIscritti
WHERE citta = “Milano”;
19
Update
Il comando “update” permette l’aggiornamento di una
tabella
UPDATE nome_tabella
SET nome_campo = { espressione | NULL | DEFAULT |
SELECT …}
WHERE condizione };
20
Update
Esempi:
UPDATE studenti SET classe = classe+1
WHERE promosso = true AND classe < 5;
21
Delete
Il comando “delete from” permette l’eliminazione di righe
di una tabella
DELETE FROM nome_tabella
[WHERE condizione ][LIMIT massimo_numero righe];
Nota access non supporta “limit”
22
Delete
Esempi:
DELETE FROM studenti
WHERE promosso = true AND classe = 5;
DELETE FROM nuoviIscritti;
(che differenza c’è con DROP TABLE nuoviIscritti;
?)
Nota: se il vincolo di integrità non è definito nello schema di creazione della tabella,
l’eliminazione è sempre possibile, altrimenti …. (vedi foreign key)
23
SQL
(STRUCTURED QUERY LANGUAGE)
DCL
24
DCL
Gestire il controllo degli accessi e i permessi per gli utenti
(DCL: Data Control Language)
DCL
Grant
Revoke
25
SQL
(STRUCTURED QUERY LANGUAGE)
QUERY Language
26
QUERY Language
Porre interrogazioni al database
SELECT [ALL | DISTINCT]
{* | (nome_campo [AS alias])[,…]}
FROM nome_tabella [AS alias] [,…]
[WHERE condizione]
[GROUP BY nome_campo]
[HAVING condizione]
[ORDER BY (nome_campo) [ASC | DESC] [,…]
[LIMIT [ riga_iniziale,][righe]];
Nota: LIMIT per MYSQL; TOP per ACCESS.
27
Operatori relazionali
 selezione
 proiezione
 congiunzione (join)
28
Selezione
L’operazione di selezione (non confondere con il comando SQL, SELECT !)
estrae un sottoinsieme di righe da una tabella in base a delle
condizioni imposte ai valori contenuti in una o più colonne.
29
Proiezione
L’operazione di proiezione estrae un sottoinsieme di colonne da una
tabella
30
Esempio selezione:
SELECT * FROM articoli WHERE colore= “Rosso”;
31
Esempio proiezione:
SELECT colore FROM articoli;
32
Esempio:
SELECT DISTINCT colore FROM articoli ORDER BY colore;
33
Esempio selezione e proiezione:
SELECT descrizione FROM articoli WHERE colore= “Rosso”;
34
Join
L’operazione di congiunzione (non confondere nella
traduzione dall’inglese UNION con JOIN) combina le
operazioni di prodotto e di selezione tra due (o più
tabelle); viene cioè effettuato prima il prodotto fra le
tabelle e poi estratte solo le righe che soddisfano una
determinata condizione tra le colonne della prima e della
seconda tabella.
35
Join
Date le tabelle di partenza
tblA e tblB:
36
Join
Outer join
37
Esempio selezione:
SELECT * FROM TblLibri WHERE prezzo < 20,00;
38
Esempio proiezione:
SELECT Titolo, isbn FROM TblLibri ORDER BY Titolo;
39
Esempio join:
SELECT * FROM TblLibri,TblEditore
WHERE TblLibri.Id_CasaEditrice = TblEditori.Id_CasaEditrice;
SELECT * FROM TblLibri INNER JOIN TblEditore
ON TblLibri.Id_CasaEditrice = TblEditori.Id_CasaEditrice;
40
Esempio outer join:
SELECT * FROM TblLibri LEFT JOIN TblEditore
ON TblLibri.Id_CasaEditrice = TblEditori.Id_CasaEditrice;
41
Esempio outer join :
SELECT * FROM TblLibri RIGHT JOIN TblEditore
ON TblLibri.Id_CasaEditrice = TblEditori.Id_CasaEditrice;
42
Altri operatori
 ORDER BY
 LIKE
 BETWEEN
 IN
43
Esempio:
SELECT isbn,titolo FROM TblLibri WHERE
DataPubblicazione BETWEEN #01/01/2005 # AND #31/12/2005#
AND isbn LIKE “88-*” ORDER BY isbn DESC;
44
Esempio:
SELECT editore FROM TblEditori
WHERE provincia IN („TO‟ , „PD‟, „VE‟);
45
Esempio:
SELECT titolo FROM TblLibri
WHERE Id_CasaEditrice IN (
SELECT Id_CasaEditrice FROM TblEditori
WHERE provincia =“MI”);
…query nidificata…
46
Operatori di aggregazione
 AVG
 COUNT
 MAX
 MIN
 SUM
47
Operatori di aggregazione (2)
 STDEV (access)
 STDDEV (mysql)
 VAR (access)
 VARIANCE (mysql)
48
Esempio:
SELECT AVG(Prezzo) FROM TblLibri; 21,28
SELECT COUNT(*) FROM TblLibri;
5
SELECT COUNT(Id_CasaEditrice) FROM TblLibri;
4
SELECT COUNT(*) - COUNT(Id_CasaEditrice) FROM TblLibri;
SELECT COUNT(DISTINCT Id_CasaEditrice) FROM TblLibri;
(ma solo mysql)
49
1
3
Raggruppamenti
“GROUP BY” serve per raggruppare ed elaborare diverse
righe che nella tabella origine hanno valori uguali in una (o
più) determinata colonna
50
Condizioni di gruppo
“HAVING” permette di porre condizioni sulla nuova tabella
ottenuta dopo il raggruppamento
SELECT …
FROM … [JOIN] … [WHERE] ….
GROUP BY ….
[HAVING condizione];
51
Esempio:
SELECT trimestre, SUM (totale) AS venduto
FROM TblVendite
GROUP BY trimestre;
52
Esempio:
SELECT zona, AVG (totale) AS media
FROM TblVendite
GROUP BY zona
HAVING AVG (totale) < 1000;
53
Appendice
54
Caratteri Jolly (ACCESS)
Carat
tere
*
Utilizzo
Esempio
Corrisponde a un numero qualsiasi di no* trova nome, note e
caratteri. Può essere utilizzato come
notare
primo o ultimo carattere nella stringa
dei caratteri.
?
Corrisponde a qualsiasi singolo
carattere alfabetico.
B?llo trova ballo, bello
e bollo
[]
Corrisponde a qualsiasi singolo
carattere all'interno delle parentesi
quadre
B[ae]llo trova ballo e
bello, ma non bollo
55
Caratteri Jolly (ACCESS)
Carat
tere
Utilizzo
Esempio
!
Corrisponde a qualsiasi carattere non
incluso nelle parentesi quadre
b[!ae]llo trova bollo e
bullo, ma non ballo e
bello
-
Corrisponde a uno qualsiasi dei
b[a-c]d trova bad, bbd
caratteri di un intervallo. È necessario e bcd
specificare l'intervallo in ordine
crescente (dalla A alla Z, non dalla Z
alla A)
#
Corrisponde a qualsiasi singolo
carattere numerico
Corrisponde a qualsiasi
singolo carattere
numerico.
1#3 trova 103, 113
56