SQL: definizione (DDL) e manipolazione

Creazione di un database MySQL
Dopo esserci collegati al server MySQL con:
mysql -u root -p -h pc_docente
dal prompt possiamo creare un database col comando:
mysql> CREATE DATABASE video_miocognome;
… e poi impostarlo come database di default:
mysql> USE video_miocognome;
NOTA: Una volta creato il database, è possibile anche selezionarlo
direttamente nel comando di avvio della shell di MySQL con:
mysql -u root -p -h pc_docente video_miocognome
Per eliminare un database, con relativa perdita di tutti i dati,
facciamo uso del comando:
mysql> DROP DATABASE video_miocognome;
Per uscire dalla shell di MySQL:
mysql> exit;
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
1
Esecuzione dei comandi: l’interfaccia
grafica “Query Browser” (1)
Per inviare i comandi ad un server MySQL è possibile utilizzare il
programma “Query Browser”, come indicato nelle figure sottostanti:
Forniamo i dati per l’accesso al database
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
2
Esecuzione dei comandi: l’interfaccia
grafica “Query Browser” (2)
Ad esempio, per listare i database creati sul server:
1 - Immettiamo il comando
in questa casella …
2 – Clicchiamo sul pulsante “Execute”
oppure premiamo Ctrl+Invio …
3 – I risultati ci vengono mostrati
nell’area sottostante “Resultset 1”
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
3
I tipi di dati numerici
Le colonne delle tabelle possono essere di tipo:
Numerico, Stringa, Data/Ora. I principali tipi numerici sono:
Nome
TINYINT
Val. Minimo
Val. Massimo
-128
+127
BOOLEAN
SMALLINT
MEDIUMINT
INTEGER
BIGINT
Nome
Note
Unsigned 0-255 (2^8)
0=Falso, <>0=Vero
-32.768
32.767
-8.388.608
8.388.607
-2.147.483.648
2.147.483.647
-9223372036854775808
9223372036854775807
Unsigned 0-65.535 (2^16)
Unsigned 0-16.777.215 (2^24)
Unsigned 0-4.294.967.295 (2^32)
Unsigned (2^64) 0-18446744073709551615
Note
FLOAT
Numero a singola precisione. Valori da -3.402823466E+38 a -1.175494351E-38, 0, da 1.175494351E-38
a 3.402823466E+38. Unsigned=non negativo
DOUBLE
Numero a precisione doppia. Values da -1.7976931348623157E+308 a -2.2250738585072014E-308, 0,
e da 2.2250738585072014E-308 a 1.7976931348623157E+308. Unsigned=non negativo
Per questi tipi (bool escluso) è possibile aggiungere la parola chiave UNSIGNED. Il tipo SERIAL equivale a “BIGINT
UNSIGNED NOT NULL AUTO_INCREMENT” ed è un progressivo automatico.
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
4
I tipi di dati stringa e data/ora
I principali tipi di dati stringa sono:
Tipo
Num. Min.
di caratteri
Num. Max di caratteri
CHAR(M)
0
255
VARCHAR(M)
0
65.532 (255 fino alla v. 4)
TEXT
0
65.535
Note
A lunghezza fissa
A lunghezza variabile
I principali tipi di dati data/ora sono:
Tipo
DATE
Val. Min.
Val. Max
'1000-01-01'
'9999-12-31'
DATETIME
'1000-01-01 00:00:00'
'9999-12-31 23:59:59'
TIMESTAMP
'1970-01-01 00:00:00'
‘2037-12-31 23:59:59’
'-838:59:59'
'838:59:59'
TIME *
Note
Formato
'YYYY-MM-DD'
Formato
'HH:MM:SS'
* Il numero di ore è così elevato (838) per consentire differenze tra orari che superino le 24 ore.
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
5
Lo schema del database
video_miocognome
PRESTITI
REGISTI
SUPPORTI
Codice supporto
Tipo_supporto
Codice_film
Quantità
ATTORI
Codice_attore
Cognome
Nome
1
1
8
8
8
Codice_film
Titolo
Codice_regista
Codice_protagon
Anno
1
8
1
8
Codice_regista
Cognome
Nome
FILM
Codice prestito
Codice_supporto
Codice_cliente
Data_prestito
Data_restituzione
Prezzo
CLIENTI
Codice_modulo
Cognome
Nome
Telefono
1
Le relazioni sono di tipo:
-Uno-a-uno
-Uno-a-molti (in questo schema)
-Molti-a-molti
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
6
Le prime tre forme normali
NF = Normal Form (Esempi)
Schema errato
1NF
2NF
3NF
Schema corretto
ATTORI
ATTORI
Codice: (es. 12)
Nome: (es. Sam Neill)
Codice
Cognome
Nome
FILM
FILM
Codice
Titolo
Nome Regista ...
Codice
Titolo
Cod. Regista (registi in altra tabella) …
PRESTITI
PRESTITI
Codice
Codice supporto
Codice Cliente
N. telefono cliente …
Codice
Codice supporto
Codice Cliente
N. telefono cliente (tabella clienti)
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
7
Creazione di una tabella
DDL (Data Definition Language)
L’istruzione per creare la tabella “registi”, da eseguire al prompt di
MySQL, è la seguente:
CREATE TABLE registi (
codice_regista INTEGER PRIMARY KEY,
nome VARCHAR(50) NOT NULL,
cognome VARCHAR(50) NOT NULL
);
Significato delle parole chiave:
PRIMARY KEY: indica che il campo codice è la chiave primaria
NOT NULL: indica che il valore è da inserire obbligatoriamente, cioè
non può essere non valorizzato (NULL)
Per eliminare una tabella, l’istruzione è la seguente:
DROP TABLE registi;
Note: premendo i tasti direzionali “Sù” e “Giù” possiamo scorrere la “storia” dei comandi
inviati con la shell di MySQL. Per chiarezza del codice SQL è consigliato scrivere le parole
chiave in maiuscolo, quelle scelte dall’utente in minuscolo.
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
8
Modifica della struttura di una
tabella (DDL)
Aggiunta di una colonna (campo):
ALTER TABLE registi ADD COLUMN note VARCHAR(100);
Modifica di una colonna:
ALTER TABLE registi MODIFY COLUMN note VARCHAR(150);
Vediamo il risultato con:
DESC registi;
Eliminazione di una colonna:
ALTER TABLE registi DROP COLUMN note;
DESC registi;
Creazione di un indice univoco:
CREATE UNIQUE INDEX idx_registi ON registi(nome,cognome);
La parola “unique” è opzionale. Se inserita indica che la coppia nome+cognome dei registi
non deve ripetersi. Un indice è utile per una ricerca rapida basata sui campi indicizzati ma,
allo stesso tempo, rallenta i tempi di esecuzione delle altre istruzioni di manipolazioni dei dati.
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
9
Creazione della tabella “attori”
DDL (Data Definition Language)
L’istruzione per creare la tabella “attori”, da eseguire al prompt di
MySQL, è la seguente:
CREATE TABLE attori (
codice_attore INTEGER PRIMARY KEY,
nome VARCHAR(50) NOT NULL,
cognome VARCHAR(50) NOT NULL
);
Creazione di un indice univoco:
CREATE UNIQUE INDEX idx_attori ON attori(nome,cognome);
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
10
Relazioni fra tabelle (1)
Le relazioni possono essere impostate solo se le tabelle sono di tipo
INNODB, per cui è necessario modificare il tipo tabella:
ALTER TABLE registi TYPE=INNODB;
ALTER TABLE attori TYPE=INNODB;
Creiamo la tabella “film”, collegata col le tabelle “registi” ed “attori” in
quanto per ogni film impostiamo un regista ed un attore protagonista:
CREATE TABLE film (
codice_film INTEGER PRIMARY KEY,
titolo VARCHAR(100) NOT NULL,
codice_regista INTEGER NOT NULL,
FOREIGN KEY (codice_regista) REFERENCES registi(codice_regista) ON
DELETE CASCADE,
codice_attore INTEGER NOT NULL,
FOREIGN KEY (codice_attore) REFERENCES attori(codice_attore) ON
DELETE CASCADE,
anno INTEGER NOT NULL
) TYPE=INNODB;
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
11
Relazioni fra tabelle (2)
Con riferimento alla relazione tra la tabella “film” e la tabella
“registi”, notiamo che il campo che collega le due tabelle è il codice
regista e, nella tabella “film”, è chiamato “chiave esterna” mentre
nella tabella “registi” deve essere chiave primaria.
Per rivedere l’SQL di creazione delle tabelle:
SHOW CREATE TABLE registi;
SHOW CREATE TABLE attori;
SHOW CREATE TABLE film;
La clausola “ON DELETE CASCADE” consente di eliminare
automaticamente a catena tutti i film associati ad un regista se
questo ultimo viene eliminato (USARE CON CAUTELA).
Lo stesso vale per la cancellazione di un attore.
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
12
Esercitazione N. 2.1
Create le tabelle “supporti”, “clienti” e “prestiti”:
SUPPORTI
Campo
Tipo
Ampiezza Vincoli
codice_supporto
tipo
codice_film
quantità
Intero
Varchar
Intero
Intero
3
Non nullo
Non nullo
Non nullo
CLIENTI
Campo
Tipo
Ampiezza Vincoli
codice_cliente
nome
cognome
telefono
Intero
Varchar
Varchar
Varchar
50
50
40
Non nullo
Non nullo
Non nullo
ATTENZIONE:
- individuare opportunamente
chiavi primarie e chiavi esterne
- non specificare la clausola
“ON DELETE CASCADE” sulla
tabella “prestiti”
PRESTITI
Campo
Tipo
codice_prestito
codice_supporto
codice_cliente
data_prestito
data_restituzione
prezzo
Intero
Intero
Intero
Data
Data
Float
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
Ampiezza Vincoli
Non nullo
Non nullo
Non nullo
13
DML (Data Manipulation Language)
INSERT - inserimento di record (1)
REGISTI
ATTORI
FILM
SUPPORTI
INSERT INTO registi VALUES(1,’Roberto’,’Rossellini’);
INSERT INTO registi VALUES(2,’Steven’,’Spielberg’);
INSERT INTO attori VALUES(1,’Anna’,’Magnani’);
INSERT INTO attori VALUES(2,’Sam’,’Neill’);
INSERT INTO film VALUES
(1,'Roma città aperta',1,1,1945);
INSERT INTO film VALUES
(2,'Jurassic Park',2,2,1993);
INSERT INTO supporti VALUES
(1,’DVD’,1,3);
INSERT INTO supporti VALUES
(2,’VHS’,2,1);
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
14
DML (Data Manipulation Language)
INSERT - inserimento di record (2)
CLIENTI
INSERT INTO clienti VALUES
(1,'Enrico','Verdi','333-444');
INSERT INTO clienti VALUES
(2,'Flavia','Conti','333-555');
INSERT INTO clienti VALUES
(3,'Roberta','Toscano','333-666');
PRESTITI
INSERT INTO prestiti VALUES
(1,1,1,'2003-09-11','2003-09-15',1);
INSERT INTO prestiti VALUES
(2,1,3,'2003-10-01',NULL,1);
INSERT INTO prestiti (codice_prestito,
codice_supporto, codice_cliente,
data_prestito, prezzo)
VALUES (3,1,2,'2003-11-03',2);
INSERT INTO prestiti VALUES
(4,2,2,'2003-12-01',NULL,2.50);
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
15
DML (Data Manipulation Language)
SELECT - 1
Estrazione di tutti i corsi inseriti ordinati per titolo:
SELECT codice_film, titolo FROM film ORDER BY titolo;
(al posto della lista dei campi è possibile mettere * per estrarre TUTTI i campi della tabella)
Estrazione di tutti i prestiti del cliente “Conti” (con codice 2), ordinati
per “data_prestito” a partire dal più recente per i quali non è stato
ancora restituito il supporto (JOIN):
SELECT
c.cognome, c.nome, p.data_prestito, p.prezzo
FROM
c, p sono detti
clienti c, prestiti p
‘alias’ e sono utili
WHERE
per referenziare le
c.codice_cliente=p. codice_cliente
tabelle in modo
abbreviato
AND p.data_restituzione IS NULL
ORDER BY
p.data_prestito DESC;
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
16
Esercitazione N. 2.2
Inserire almeno due righe a piacere in
ogni tabella creata:
+------------------------+
| Tables_in_video_diture |
+------------------------+
| attori
|
| clienti
|
| film
|
| prestiti
|
| registi
|
| supporti
|
+------------------------+
6 rows in set (0.00 sec)
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
17
DML (Data Manipulation Language)
UPDATE
Per aggiornare un campo (o più campi) di una tabella:
UPDATE prestiti SET data_restituzione=‘2003-09-20’
WHERE codice_prestito=1;
Aggiornamento dei dati relativi al prestito con codice 2 (due campi,
data restituzione e prezzo):
UPDATE prestiti SET
data_restituzione=‘2003-10-15’, prezzo=2.50
WHERE codice_prestito=2;
NOTE: Se non si specifica alcuna condizione (WHERE) l’aggiornamento
verrà effettuato su TUTTI I RECORD DELLA TABELLA.
I valori dei campi data e testo sono rinchiusi tra APICI, quelli numerici
non hanno delimitatori.
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
18
DML (Data Manipulation Language)
DELETE
Per eliminare uno o più record da una tabella è necessario
specificarne il nome e una condizione (se la condizione non viene
specificata, verranno eliminati TUTTI I RECORD).
Eliminazione del prestito con codice 3:
DELETE FROM prestiti WHERE codice_prestito=3;
Se tentiamo di cancellare un cliente con prestiti, verrà emesso un
messaggio di errore(*) a causa della violazione del vincolo di
integrità impostato sulla tabella “prestiti” (su codice_cliente):
DELETE FROM clienti WHERE codice_cliente=2;
ERROR 1217 (23000): Cannot delete or update a parent row: a
foreign key constraint fails
(*) solo se non è stata specificata la clausola “ON DELETE CASCADE” nella creazione
della tabella.
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
19
Esercitazione N. 2.3
Scrivere le istruzioni SQL per:
 Aggiornare la data di restituzione del
prestito con codice 2 a ‘2005-01-23’
 Cancellare il cliente con codice 4
(l’operazione potrebbe non riuscire … perché ?)
 Selezionare tutti gli attori ordinati per
cognome e nome
 Selezionare il titolo ed il nome del regista di
tutti i film archiviati
CORSI DI FORMAZIONE - Basi di Dati: MySql - Parte 2 - Novembre 2005
20