Banche dati e MySQL Introduzione a MySQL

Dott.Ing.Ivan Ferrazzi
Banche dati
e
MySQL
Introduzione
a
MySQL
Dott. Ing. Ivan Ferrazzi
V1.1 del 05/02/2014
1/39
Dott.Ing.Ivan Ferrazzi
Copyright ©2014 Dott.Ing. Ivan Ferrazzi
Permission is granted to copy, distribute and/or modify this
document under the terms of the GNU Free Documentation
License, Version 1.2 or any later version published by the
Free Software Foundation; with no Invariant Sections, no
Front-Cover Texts, and no Back-Cover Texts.
2/39
Dott.Ing.Ivan Ferrazzi
Indice generale
INTRODUZIONE....................................................................................................4
La banca dati...................................................................................................4
Database Management System (DBMS)..........................................................5
Programmi applicativi......................................................................................5
Database relazionali...........................................................................................6
Le tabelle.........................................................................................................6
Il concetto di “chiave primaria”.......................................................................7
Gli indici...........................................................................................................7
Le forme di normalizzazione...............................................................................9
La prima forma normale (prima normalizzazione)...........................................9
La seconda forma normale (seconda normalizzazione).................................10
La terza forma normale (terza normalizzazione)...........................................11
Sistema client-server........................................................................................13
MySQL..............................................................................................................14
La shell di MySql............................................................................................14
La gestione degli utenti e dei permessi.........................................................16
La gestione delle banche dati........................................................................18
La gestione delle tabelle................................................................................19
La gestione dei record...................................................................................27
Inserire record con INSERT INTO...........................................................................................27
Modificare record con UPDATE..............................................................................................28
Eliminare record con DELETE FROM.....................................................................................28
La proiezione con il comando SELECT...........................................................29
Il blocco WHERE.....................................................................................................................31
Il blocco ORDER BY................................................................................................................33
Il blocco LIMIT.........................................................................................................................33
Il blocco GROUP BY................................................................................................................34
Il blocco HAVING....................................................................................................................34
Le relazioni con LEFT JOIN, RIGHT JOIN, FULL JOIN e INNER JOIN..............................36
Le subquery...................................................................................................38
Le subquery nel blocco WHERE..............................................................................................38
Le subquery nel blocco FROM.................................................................................................38
3/39
Dott.Ing.Ivan Ferrazzi
INTRODUZIONE
Il termine banca dati viene comunemente utilizzato per indicare un archivio nel
quale viene memorizzata una notevole quantità di dati. Da un punto di vista
tecnico, però, il sistema di archiviazione è composto da più elementi, ossia una
banca dati, un Database Management System (DBMS) ed un'eventuale
programma applicativo (programma con il quale lavora l'utente finale).
Le singole parti sono strutturate in modo tale da consentire l'inserimento, la
cancellazione, la modifica e l'estrapolazione dei dati stessi.
Al giorno d'oggi è diventato ormai indispensabile avvalersi di questi sistemi per
l'organizzazione dei dati, basti pensare a quanto sia utile poter immagazzinare i
dati di un censimento all'interno di una banca dati. Tutto ciò permette di
risparmiare tonnellate di carta e di velocizzare la ricerca dei dati, che altrimenti
sarebbe lenta e laboriosa.
La banca dati.
Come banca dati (o sistema di archiviazione) si intende, quindi, il metodo
tecnico utilizzato per la memorizzazione dei dati e gli algoritmi di
velocizzazione e ottimizzazione delle singole operazioni, come l'inserimento, la
modifica, oppure l'eliminazione di dati od insieme di essi. Inoltre, fa parte della
banca dati, anche il tipo di memorizzazione della struttura della tabella, dei
campi e degli eventuali indici (discussi in seguito).
4/39
Dott.Ing.Ivan Ferrazzi
Database Management System (DBMS).
Il Database Management System è il software in grado di gestire la banca dati.
Infatti, la banca dati ed il Database Management System sono due cose ben
distinte.
Il DBMS è il software che permette di interagire con i dati archiviati all'interno
della banca dati.
Paragonato al mondo reale possiamo utilizzare il seguente semplice esempio:
Se vediamo un bambino in spiaggia giocare con delle conchiglie, le conchiglie
potrebbero rappresentare il database, mentre il bambino che le maneggia,
sarebbe il Database Management System.
L'utilizzo di un DBMS facilita anche la condivisione di un singolo archivio
all'interno di una rete informatica. In questo caso un computer può mettere a
disposizione il servizio di banca dati mediante un DBMS Server ad altri
computer collegati alla stessa rete informatica forniti di un DBMS Client.
Questo sistema permette di alleggerire i computer Client centralizzando tutte
le operazioni su un'unica banca dati presente su un computer Server.
Un aspetto particolare riguarda l' autorizzazione per l'accesso ai dati, in quanto
il DBMS dovrà essere dotato di un sistema di protezione, i dati contenuti nelle
tabelle del database dovranno essere accessibili solo alle persone autorizzate.
L'autorizzazione dovrà quindi essere assegnata dall'amministratore di sistema.
Programmi applicativi.
I database sono strutturati in modo tale da poter essere utilizzati da programmi
applicativi, che possono interagire con essi.
I programmi applicativi possono essere scritti in diversi linguaggi (es. C, C++,
Java, Visualbasic, PHP, ecc.) e mettono a disposizione dell'utente un'interfaccia
grafica semplice con la quale è possibile gestire le varie operazioni
(inserimento, modifica e cancellazione) per una corretta gestione dei dati
memorizzati.
5/39
Dott.Ing.Ivan Ferrazzi
Database relazionali
I database relazionali utilizzano come struttura principale per la
memorizzazione dei dati le tabelle. Una tabella, così come la conosciamo da
Excel, oppure Calc, è composta da colonne e righe, dove la singola colonna
contiene dati di formato uguale e la singola riga dati che appartengono ad un
insieme logico. Potremo avere, come esempio, una tabella composta dalle
colonne Cognome, Nome ed Indirizzo, mentre nelle singole righe troviamo i
dati, che insieme, forniscono un'informazione logica (dati di una persona).
Questa tipologia di database viene chiamata relazionale, perché permette di
mettere in relazione due o più tabelle. L'insieme dei dati Cognome, Nome e
Indirizzo appartenente alla tabella clienti può essere messo in relazione con
l'insieme dei dati Articolo, Quantità e Prezzo appartenente alla tabella acquisti.
Le tabelle
Le tabelle sono gli elementi fondamentali per l'organizzazione dei dati in un
database relazionale. La struttura di una tabella viene definita tramite l'utilizzo
dei campi (chiamati anche attributi), che descrivono quello che sarà il
contenuto di una colonna. I campi possono essere definiti mediante valori
numerici, geometrici, relativi a date, a tempo oppure alfabetici.
La scelta del tipo di campo, ossia contenitore per i nostri dati, è fondamentale
per una efficace progettazione di un database. Con una corretta definizione del
tipo di colonna si può rendere un database veloce ed economico (in termini di
byte).
Immaginate di essere il responsabile di un magazzino, facciamo di un
ferramenta. Vi viene dato il compito di organizzare i prodotti in modo che
6/39
Dott.Ing.Ivan Ferrazzi
occupino il minor spazio possibile, che siano organizzati per tipo e che sia il più
facile possibile trovarli.
In magazzino vi viene messo a disposizione uno scaffale e una vasta gamma di
contenitori che potrete utilizzare per portare a termine l'incarico che vi è stato
dato. Una delle possibilità che avete è quella di prendere tutti gli articoli
(bulloni, viti, rondelle, chiavi e tutti gli altri oggetti del caso) e di gettarli in un
unico contenitore molto grande. Lo spazio utilizzato è relativamente poco ed il
tempo utilizzato per l'archiviazione di un nuovo articolo è quasi inesistente,
perché basta metterlo nell'unico contenitore esistente. Il tempo che però viene
utilizzato per estrarre dal contenitore un articolo che stiamo cercando è molto
elevato.
Se volete un magazzino veramente efficiente dovete fare attenzione a queste
regole: per oggetti piccoli, piccoli contenitori, per oggetti grandi, grandi
contenitori, oggetti di forma particolare, contenitori adatti all’oggetto.
La cosa sembra ovvia, ma in un database, questa situazione può rendere una
applicazione più o meno efficiente, e nei nostri “scaffali digitali” la scelta del
tipo di contenitore di dati può essere fatta in modo ancora più precisa, non solo
per le loro dimensioni ma anche per il loro utilizzo.
L'insieme logico di dati presenti all'interno di una tabella (ossia la singola riga)
viene chiamata record.
Il concetto di “chiave primaria”
La chiave primaria è un campo, oppure un'insieme di campi, che permettono di
identificare univocamente un record di una tabella. All'interno della medesima
tabella non possono esistere due record con un uguale valore assegnato
all'interno del campo, o dei campi, che definiscono la chiave primaria.
La chiave primaria di una tabella può essere formata da uno o più campi. Una
chiave primaria definita da più campi viene comunemente chiamata chiave
composta.
Nel caso di chiave composta il valore viene dato dalla combinazione dei campi
coinvolti. L'utilizzo di una chiave primaria all'interno di una tabella è
consigliabile, ma non obbligatorio.
Gli indici
Un indice è un sistema che permette di avere un ordinamento logico di dati
strutturato in maniera tale da ottimizzarne la ricerca dei contenuti. Nei libri, ad
esempio, utilizziamo un indice per cercare un preciso capitolo.
In una tabella l'indice viene creato automaticamente con la chiave primaria,
utilizzando la medesima come valore di ordinamento. Dopo ogni operazione,
quindi inserimento, modifica, oppure eliminazione di un record, l'intero
contenuto dove essere riordinato per chiave primaria. L'aggiunta di un indice
ha come risultato l'ottimizzazione di ricerche effettuate su valori all'interno di
esso.
7/39
Dott.Ing.Ivan Ferrazzi
Nei libri tecnici troviamo a volte degli indici che permettono di trovare le
pagine all'interno delle quali appare una determinata parola, un determinato
diagramma o del specifico materiale fotografico.
Così come è possibile aggiungere ulteriori indici (oltre a quello principale) ai
libri è possibile aggiungere anche ulteriori indici alle tabelle. Un'operazione su
una tabella avrà come effetto l'aggiornamento di tutti gli indici usati. La ricerca
di un valore all'interno di un qualsiasi campo può diventare molto veloce, ma
l'aggiornamento o l'inserimento di nuovi record potrebbe diventare molto
laborioso. Si consiglia quindi di utilizzare gli indici con criterio dato che ogni
indice aggiunto, oltre al rallentamento di operazioni base, occupa spazio
prezioso sul disco fisso.
8/39
Dott.Ing.Ivan Ferrazzi
Le forme di normalizzazione
La normalizzazione è una procedura con la quale si elimina la ridondanza e il
rischio di inconsistenza dei dati all'interno delle tabelle di un database. Creare
una struttura di banca dati con le strutture delle tabelle ed i contenuti che non
tengono conto delle forme di normalizzazione può essere quindi molto
rischioso. Esistono varie forme di normalizzazione, ma ci limiteremo a spiegare
le prime tre forme principali.
La prima forma normale (prima normalizzazione).
Una tabella si trova in prima forma normale quando:
●
●
●
●
ogni riga della tabella contiene lo stesso numero di colonne;
ogni valore presente all'interno della stessa colonna è dello stesso tipo
(numerico, stringa, data, ecc.);
ogni campo (ossia colonna) dell'intera tabella contiene dei valori che in
base al loro utilizzo non possono essere ulteriormente scomponibili;
esiste almeno un campo definito come chiave primaria.
Prendiamo come esempio una tabella che contiene dei semplici contatti (il
campo sottolineato, quindi Nr, è definito come chiave primaria):
Nr
Cognome
Nome
Data di nascita
1
Rossi
Mario
01/05/1958
2
Verdi
Alberto
05/09/1988
9/39
Dott.Ing.Ivan Ferrazzi
Se utilizziamo il campo Data di nascita solo come campo per la memorizzazione
della data di nascita possiamo dire di avere una tabelle che si trova in prima
forma normale. Nel momento in cui si volesse utilizzare il campo Data di
nascita per metterlo in relazione con la tabella Segni dello Zodiaco e dare
quindi la possibilità al sistema di estrarre in automatico il relativo segno dello
Zodiaco di ogni nostro contatto in base al mese di nascita non avremmo più
una tabella che si trova in prima forma normale.
In questo caso dobbiamo scomporre nelle sue parti elementari anche il campo
Data di nascita come segue:
Nr
Cognome
Nome
Giorno
Mese
Anno
1
Rossi
Mario
01
05
1958
2
Verdi
Alberto
05
09
1988
Ora abbiamo una tabella che, in base al suo utilizzo, si trova in prima forma
normale.
La seconda forma normale (seconda normalizzazione).
Una tabella si trova nella seconda forma normale quando le regole della prima
forma normale sono rispettate e tutti i campi definiti come non-chiave primaria
dipendono dall'intera chiave primaria.
Prendiamo la seguente tabella come esempio:
Articoli
CodArticolo Descrizione Giacenza Magazzino Locazione magazzino
0001
maglia
130
M10
Via Mascialli 2
0002
jeans
65
M11
Via Gramsci 10
0003
cappello
100
M10
Via Mascialli 2
In questo caso abbiamo una chiave primaria composta dai campi CodArticolo e
Magazzino. Come possiamo notare questa tabella si trova in prima forma
normale.
Per motivi logistici dobbiamo ora spostare il magazzino identificato dal codice
M10 da Via Mascialli 2 a Via Roma 4. Questa modifica va naturalmente
apportata anche alla nostra tabella. Dopo aver dato al sistema il comando per
l'aggiornamento del campo Locazione magazzino come desiderato, inizia
l'elaborazione. Modificato il primo record corrispondente (nel nostro caso
l'articolo con il codice 0001) il sistema va in crash. L'elaborazione non è
riuscita, quindi, a raggiungere il terzo record che andava a sua volta modificato
come il primo. Il contenuto della nostra tabella è ora il seguente:
10/39
Dott.Ing.Ivan Ferrazzi
Articoli
CodArticolo Descrizione Giacenza Magazzino Locazione magazzino
0001
maglia
130
M10
Via Roma 4
0002
jeans
65
M11
Via Gramsci 10
0003
cappello
100
M10
Via Mascialli 2
Come possiamo notare esiste ora un'incongruenza tra il primo ed il terzo
record. Per portare questa tabella in seconda forma normale dobbiamo
suddividerla in due tabelle diverse. Questo è naturalmente possibile solo in
caso di chiave primaria composta, come nel nostro caso.
Si otterranno quindi le seguenti tabelle
Articoli
Magazzini
CodArticolo Magazzino Descrizione
Giacenza
Magazzino
Locazione
0001
M10
maglia
130
M10
Via Roma 4
0002
M11
jeans
65
M11
Via Gramsci 10
0003
M10
cappello
100
La terza forma normale (terza normalizzazione).
Una tabella si trova in terza forma normale quando le regole delle prime due
forme normali sono rispettate e tutti i campi non-chiave dipendono dalla chiave
primaria.
Prendiamo come esempio la seguente tabella
Matricola Cognome
Nome
Alunni
Scuola
MA0001
Rossi
Mario
ITC Marini
Via Garibaldi 2
MA0002
Verdi
Alberto
Liceo Classico A
Via Leopardi 8
MA0003
Albertini
Rosa
ITC Marini
Via Garibaldi 2
Indirizzo scuola
Come possiamo notare non tutti i campi non-chiave dipendono dalla chiave
primaria Matricola. Il campo Indirizzo scuola, infatti, non dipende dal campo
Matricola, ma dal campo Scuola che non fa parte della chiave primaria.
Per portare la tabella in terza forma normale dobbiamo anche qui suddividerla
in due tabelle, ossia
Alunni
Matricola Cognome Nome
Scuole
Scuola
11/39
Scuola
Indirizzo
Dott.Ing.Ivan Ferrazzi
MA0001
Rossi
Mario
ITC Marini
ITC Marini Via Garibaldi 2
MA0002
Verdi
Alberto
Liceo A
Liceo A
MA0003
Albertini
Rosa
ITC Marini
12/39
Via Leopardi 8
Dott.Ing.Ivan Ferrazzi
Sistema client-server
Il sistema client-server è un metodo con il quale si possono condividere
applicazioni in rete.
Questo sistema si ha quando esiste un computer detto server che mette a
disposizione un servizio ed almeno un computer detto client che usufruisca di
esso.
Come precedentemente accennato un DBMS (Database Management System)
può fungere da servizio server, e mettere quindi a disposizione il servizio di
condivisione database su una rete informatica, o da client. Il database, ossia
l'intero sistema di tabelle ed i loro contenuti, viene installato sul rispettivo
server, mentre gli altri computer in rete ne utilizzano le risorse mediante DBMS
installato come client.
Tutte le operazioni richieste vengono quindi inviate mediante connessione tra
DBMS client e server, il server ne elabora il risultato che poi viene mandato al
client utilizzando la medesima connessione.
13/39
Dott.Ing.Ivan Ferrazzi
MySQL
Mysql è il database opensource più famoso al mondo, perché gratuito,
qualitativamente buono e viene messo a disposizione su un qualsiasi
webserver con sistema operativo GNU/Linux, oppure Microsoft.
La shell di MySql.
Il DBMS di MySql viene messo a disposizione mediante una semplice shell,
ossia un ambiente da prompt di comandi che permette di interagire
direttamente con il sistema.
Sia su sistemi operativi GNU/Linux, che su sistemi Microsoft, il programma per
far partire il prompt dei comandi di MySql è mysql. Il comando viene
normalmente utilizzato come segue
mysql [-h host] [-u utente [-p[password]]] [database] [-N] [-e “query”]
-h host
-u utente
Questo parametro permette di definire il computer in rete al
quale vogliamo connetterci, quindi al computer che funge da
server MySql. Il valore host può essere inserito come nome della
macchina oppure come indirizzo IP. Nel caso in qui questo
parametro venisse omesso verrà tentata la connessione al
computer localhost, ossia il computer sul quale si sta lavorando.
Questo parametro permette di definire il nome dell'utente con il
quale si vuole effettuare la connessione alla banca dati. Nel caso
in cui l'utente necessiti di password per accedere al servizio è
indispensabile utilizzare il parametro ­p.
Omettendo il parametro ­u (e quindi ­p) il sistema cercherà di
14/39
Dott.Ing.Ivan Ferrazzi
-p[password]
database
-e “query”
-N
connettersi utilizzando root come utente privo di password.
Questo parametro permette a MySql di visualizzare la richiesta di
inserimento password una volta confermato il comando. La
richiesta non viene visualizzata se si passa la password
direttamente all'interno della riga di comando. Attenzione! La
password va inserita direttamente dopo l'opzione -p senza
lasciare spazi.
E' possibile scegliere la banca dati alla quale vogliamo
connetterci.
Questo parametro permette di eseguire una query inclusa tra
virgolette e mostrare il risultato direttamente all'interno del
terminale. Questa opzione può essere utilizzata in caso di
redirect del risultato di una query.
Questo parametro permette di disabilitare la visualizzazione della
riga di intestazione nei risultati delle query inviate con -e.
Un altro comando molto utile è mysqldump che stampa all'interno dello standard
output del terminale i comandi MySQL che descrivono la banca dati in
questione. Possiamo sfruttare mysqldump per realizzare delle copie di sicurezza o
per migrare la nostra banca dati da un server ad un altro. Il comando viene
normalmente utilizzato come segue
mysqldump [-h host] [-u utente [-p[password]]] database [tabella1...tabellaN]
-h host
-u utente
-p[password]
database
Tabella1...N
Questo parametro permette di definire il computer in rete al
quale vogliamo connetterci, quindi al computer che funge da
server MySql. Il valore host può essere inserito come nome della
macchina oppure come indirizzo IP. Nel caso in qui questo
parametro venisse omesso verrà tentata la connessione al
computer localhost, ossia il computer sul quale si sta lavorando.
Questo parametro permette di definire il nome dell'utente con il
quale si vuole effettuare la connessione alla banca dati. Nel caso
in cui l'utente necessiti di password per accedere al servizio è
indispensabile utilizzare il parametro -p.
Omettendo il parametro -u (e quindi -p) il sistema cercherà di
connettersi utilizzando root come utente privo di password.
Questo parametro permette a MySql di visualizzare la richiesta di
inserimento password una volta confermato il comando. La
richiesta non viene visualizzata se si passa la password
direttamente all'interno della riga di comando. Attenzione! La
password va inserita direttamente dopo l'opzione -p senza
lasciare spazi.
E' possibile scegliere la banca dati della quale vogliamo creare il
dump.
E' possibile indicare il nome delle tabella che vogliamo includere
all'interno del nostro dump.
Per creare un file dump della nostra banca dati come backup e/o migrazione da
15/39
Dott.Ing.Ivan Ferrazzi
Per creare un file dump della nostra banca dati come backup e/o migrazione da
un server ad un altro è possibile effettuare il redirect dello standard output
verso un file come segue
mysqldump -u pippo -p12345 db_test > dump_db_test.sql
Questo comando creerà il file dump_db_test.sql all'interno della cartella attuale
che conterrà la struttura della banca dati db_test alla quale si accede con
utente pippo e password 12345.
La gestione degli utenti e dei permessi.
Mysql permette una capillare gestione degli utenti. All'installazione del
programma ci verrà chiesto di inserire la password di root, cioè l'utente
amministratore di sistema. L'utente root avrà il “potere” di creare nuovi utenti
e di dare loro la possibilità (o meno) di eseguire determinate operazioni, a
partire da semplici richieste di informazioni contenute all'interno di determinate
tabelle, fino alla loro completa eliminazione. L'utente root, essendo
amministratore, non ha limiti di operabilità.
Per creare un utente utilizziamo il seguente comando:
CREATE USER utente[@host] [IDENTIFIED BY 'password'];
utente
host
password
Questo parametro permette di definire il nome del nuovo utente
che si vuole creare.
Questo parametro permette di definire il nome del computer,
l'indirizzo IP oppure la rete dalla quale l'utente avrà il permesso
di connettersi al server MySql. Nel caso in cui si omette questo
parametro l'utente viene registrato con il valore ' %', ossia può
accedere da una qualsiasi postazione in rete.
Identifica la password che l'utente dovrà utilizzare per effettuare
una corretta connessione. Se omettiamo questo parametro
l'utente rimane privo di password.
Alcuni esempi sono:
CREATE USER pippo@localhost IDENTIFIED BY 'pippopwd';
CREATE USER pluto IDENTIFIED BY 'pluto123';
CREATE USER [email protected] IDENTIFIED BY 'pap12345';
Per cancellare un utente dal sistema utilizziamo il comando:
DROP USER utente[@host];
utente
Questo parametro permette di definire il nome dell'utente che si
desidera eliminare.
16/39
Dott.Ing.Ivan Ferrazzi
host
Il medesimo utente può avere il diritto di connettersi al server
MySql da più postazione. Con questo parametro definiamo
l'utente che vogliamo eliminare in base al nome del computer,
l'indirizzo IP oppure la rete dalla quale effettua la connessione.
Nel caso in cui si omette questo parametro viene eliminato
l'utente indipendentemente dalla sua posizione di accesso.
Dopo aver creato un'utente possiamo assegnarli i diritti di accesso sulle varie
operazioni possibili all'interno del sistema MySql con il seguente comando:
GRANT permesso ON database.tabella TO utente[@host];
permesso
database
tabella
utente
host
Questo parametro permette di definire il permesso che si vuole
assegnare all'utente desiderato (vedi seguente tabella).
Permette di definire la banca dati alla quale fanno riferimento i
permessi assegnati. Con il valore * assegniamo i permessi a tutte
le banche dati disponibili.
Permette di definire la tabella all'interno della banca dati
specificata alla quale fanno riferimento i permessi assegnati. Con
il valore * assegniamo i permessi a tutte le tabelle della banca
dati (o di tutte le banche dati se utilizzato il valore *) in
questione.
Questo parametro permette di definire l'utente al quale verranno
assegnati i vari permessi.
Permette di definire il nome del computer, l'indirizzo IP oppure la
rete dell'utente al quale si vuole assegnare i vari permessi.
I permessi principali che si possono assegnare con l'utilizzo di questo comando
sono elencati nella seguente tabella. La colonna a sinistra contiene il codice da
utilizzare come permesso, mentre quella a destra contiene i comandi per i quali
si ottiene il permesso di esecuzione.
Permesso
Comandi utilizzabili
ALL
tutte esclusa GRANT
ALTER
ALTER TABLE
CREATE
CREATE TABLE
CREATE TEMPORARY TABLES
CREATE TEMPORARY TABLE
CREATE VIEW
CREATE VIEW
DELETE
DELETE
DROP
DROP TABLE
INDEX
CREATE INDEX, DROP INDEX
INSERT
INSERT
LOCK TABLES
LOCK TABLES
SELECT
SELECT
SHOW VIEW
SHOW CREATE VIEW
17/39
Dott.Ing.Ivan Ferrazzi
UPDATE
UPDATE
USAGE
nessuna
GRANT OPTION
GRANT, REVOKE
CREATE USER
CREATE USER, DROP USER, RENAME USER, REVOKE ALL
PRIVILEGES
FILE
SELECT ... INTO OUTFILE, LOAD DATA INFILE
PROCESS
SHOW FULL PROCESSLIST
RELOAD
FLUSH
SHOW DATABASES
SHOW DATABASES
SHUTDOWN
mysqladmin shutdown
SUPER
KILL, SET GLOBAL
Così come è possibile assegnare dei permessi ci viene data anche la possibilità
di revocare eventuali permessi con il seguente comando:
REVOKE permesso ON database.tabella FROM utente[@host];
Per la descrizione dei parametri si fa riferimento al comando GRANT.
La gestione delle banche dati.
Come prima cosa creiamo una banca dati inserendo un nome che la
identificherà in futuro. La banca dati che creiamo diventerà il “contenitore” di
tutte le tabelle che inseriremo successivamente.
Per semplificare la comprensione dei vari comandi MySql creiamo un esempio
pratico da seguire passo dopo passo. Come esempio prendiamo la gestione dei
lettori, dei libri, degli autori, così come del noleggio di una biblioteca.
Il comando che utilizziamo per la creazione della banca dati è il seguente:
CREATE DATABASE [IF NOT EXISTS] nome [CHARACTER SET caratteri];
IF NOT EXISTS
nome
caratteri
Permette di creare il database solamente se non esiste.
Permette di definire il nome della banca dati che vogliamo
creare.
Permette di aggiungere il charset che il database deve utilizzare
(es. utf-8).
Nel nostro caso la banca dati si chiamerà biblioteca, quindi digitiamo:
CREATE DATABASE biblioteca;
A livello globale MySql ci mette a disposizione altri comandi che possono
essere utili per una completa gestione del sistema.
Possiamo, infatti, eliminare una banca dati non più utilizzata, o creata
18/39
Dott.Ing.Ivan Ferrazzi
erroneamente, con il comando DROP DATABASE come segue:
DROP DATABASE [IF EXISTS] nome;
IF EXISTS
nome
Permette di definire eliminare la banca dati solamente se
effettivamente esiste.
Permette di definire il nome della banca dati da eliminare.
Per visualizzare un elenco di tutte le banche dati attualmente presenti
all'interno del nostro sistema digitiamo:
SHOW DATABASES;
La gestione delle tabelle.
Prima di iniziare a gestire le nostre tabelle è indispensabile effettuare una
connessione alla banca dati con la quale vogliamo lavorare. Se ci siamo appena
collegati al server MySql e non abbiamo ancora effettuato nessuna connessione
ad una banca dati presente possiamo utilizzare il comando:
CONNECT database;
database
Permette di definire il nome della banca dati alla quale vogliamo
connetterci, e quindi, con la quale vogliamo lavorare.
Nel caso in cui fossimo già connessi ad una banca dati possiamo utilizzare il
comando use per spostarci all'interno di un'altra banca dati come segue:
USE database;
database
Permette di definire il nome della banca dati alla quale vogliamo
connetterci, e quindi, con la quale vogliamo lavorare.
Nel nostro esempio possiamo quindi digitare:
CONNECT biblioteca;
Tutte le operazioni che effettueremo da ora in poi verranno eseguito sulla
banca dati biblioteca.
Per creare una nuova tabella utilizziamo il comando CREATE TABLE come
segue:
CREATE TABLE [IF NOT EXISTS] tabella (
campo1
tipo_dato1
opzione1,
campo2
tipo_dato2
opzione2,
campo3
tipo_dato3
opzione3,
...
19/39
Dott.Ing.Ivan Ferrazzi
);
PRIMARY KEY(campo1[,campo2]),
[FOREIGN KEY (campo1[,campo2])
REFERENCES tabella(campo1[,campo2])
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
]
tabella
campoX
tipo_datoX
opzioneX
Permette di definire il nome della nuova tabella che vogliamo
creare.
Permette di definire il nome del campo da utilizzare all'interno
della tabella.
Permette di definire il tipo di dato che verrà inserito all'interno
del campo (vedi seguente tabella).
Permette di assegnare al campo in questione ulteriori
caratteristiche. Quelle più usate sono AUTO_INCREMENT (solo per
campi numerici), che definisce campi il cui valore viene
automaticamente incrementato di 1 ad ogni nuovo record
aggiunto, NULL, che permette valori vuoti all'interno del campo
stesso, NOT NULL, che non permette l'inserimento di valori vuoti
all'interno di questo campo, DEFAULT (valore), che permette di
definire il valore di default utilizzato nel caso in cui il valore del
campo non venisse specificato con l'utilizzo del comando INSERT
INTO, PRIMARY KEY, che inserito come opzione definisce solo quel
campo come chiave primaria, ed infine, ZEROFILL (solo per campi
numerici), che riempie lo spazio a sinistra del valore numerico nel
campo di zeri fino all'esaurimento della memoria assegnata.
Esempio: se si inserisce in una colonna definita con INT(10)
ZEROFILL il valore 755, il database visualizzerà 0000000755.
Il blocco FOREIGN KEY permette di definire il campo (o i campi) che fanno
riferimento al campo (o i campi) della chiave primaria di un'altra tabella
(REFERENCES …). Le opzioni aggiuntive ON UPDATE e ON DELETE permettono di
indicare come MySQL deve comportarsi in caso di modifica oppure eliminazione
di record che stanno in relazione con questa tabella. I valori che possiamo
utilizzare sono
RESTRICT
CASCADE
SET NULL
NO ACTION
La modifica della chiave primaria sulla tabella principale oppure
l'eliminazione del relativo record esclude operazioni sulla tabella
in relazione.
La modifica della chiave primaria sulla tabella principale oppure
l'eliminazione del relativo record ha come conseguenza la
modifica dei dati nella tabella in relazione oppure l'eliminazione
del record di riferimento.
La modifica della chiave primaria sulla tabella principale oppure
l'eliminazione del relativo record ha come conseguenza
l'inserimento di campi nulli all'interno della tabella di riferimento.
Stesso di RESTRICT.
20/39
Dott.Ing.Ivan Ferrazzi
MySql ci mette a disposizione tipi di dati numerici, oppure di stringa. I tipi di
dati che possiamo utilizzare per definire dei campi numerici sono i seguenti:
TINYINT
SMALLINT
MEDIUMINT
INT
BIGINT
FLOAT(M,D)
DOUBLE(M,D)
DECIMAL(M,D)
Può contenere da valori che vanno da -128 a 127. Se al campo
viene assegnata la proprietà UNSIGNED, potranno essere
memorizzati soltanto numeri positivi con un valore compreso tra
0 e 255. La memoria occupata sarà di 1 byte.
Può contenere valori che vanno da -32.768 a 32.767. Se al
campo viene assegnata la proprietà UNSIGNED, potranno essere
memorizzati soltanto numeri positivi con un valore compreso tra
0 e 65.535. La memoria occupata sarà di 2 byte.
Può contenere valori che vanno da -8.388.608 a 8.388.607. Se al
campo viene assegnata la proprietà UNSIGNED, potranno essere
memorizzati soltanto numeri positivi con un valore compreso tra
0 e 16.777.215. La memoria occupata sarà di 3 byte.
Può contenere valori che vanno da -2.147.483.648 a
2.147.483.647. Se al campo viene assegnata la proprietà
UNSIGNED, potranno essere memorizzati soltanto numeri positivi
con un valore compreso tra 0 e 4.294.967.295. La memoria
occupata sarà di 4 byte.
Può contenere valori che vanno da -9.223.372.036.854.775.808 a
9.223.372.036.854.775.807. Se al campo viene assegnata la
proprietà UNSIGNED, potranno essere memorizzati soltanto numeri
positivi compresi tra 0 e 18.446.744.073.709.550.615. La
memoria occupata sarà di 8 byte.
Può contenere numeri con virgola mobile. Il parametro M definisce
il numero di cifre prima della virgola, mentre D definisce il
numero di cifre dopo la virgola. I decimali contenuti dopo la
virgola saranno arrotondati per difetto. La memoria occupata
sarà di 4 byte.
Può contenere numeri con virgola mobile. Il parametro M definisce
il numero di cifre prima della virgola, mentre D definisce il
numero di cifre dopo la virgola. I decimali contenuti dopo la
virgola saranno arrotondati per difetto. La memoria occupata
sarà di 8 byte.
Può contenere numeri con virgola mobile. Il parametro M definisce
il numero di cifre prima della virgola, mentre D definisce il
numero di cifre dopo la virgola. I decimali contenuti dopo la
virgola saranno arrotondati per difetto. La memoria occupata
sarà di M + 2 byte.
I tipi di dati che possiamo utilizzare per definire dei campi stringa sono i
seguenti:
CHAR(C)
Può contenere caratteri alfanumerici, fino ad una lunghezza
massima di 255 caratteri. La lunghezza massima del valore
inserito nella colonna viene definita dal parametro C. Nel caso in
21/39
Dott.Ing.Ivan Ferrazzi
VARCHAR(C)
TINYTEXT
TINYBLOB
TEXT
BLOB
MEDIUMTEXT
MEDIUMBLOB
LONGTEXT
LONGBLOB
cui il valore inserito fosse di lunghezza inferiore alla lunghezza
massima definita, lo spazio fisico occupato sul disco fisso sarebbe
comunque quello definito dalla lunghezza massima.
Esempio: In un campo definito con CHAR(10) inseriamo la parola
'ciao'. Nonostante la parola inserita sia di 4 byte (1 byte per
carattere) lo spazio fisico occupato sarebbe comunque di 10
byte.
Può contenere caratteri alfanumerici, fino ad una lunghezza
massima di 255 caratteri. La lunghezza massima del valore
inserito nella colonna viene definita dal parametro C. Nel caso in
cui il valore inserito fosse di lunghezza inferiore alla lunghezza
massima definita lo spazio fisico occupato sul disco fisso sarebbe
quello pari alla lunghezza del valore più un carattere che
determina la fine della stringa.
Ritornando all'esempio precedente abbiamo: In un campo
definito con VARCHAR(10) inseriamo la parola 'ciao'. Lo spazio
fisico occupato sarebbe quindi 4 byte (1 byte per carattere del
valore inserito) + 1 byte (carattere '\0' che definisce la chiusura
della stringa) per uno spazio complessivo di 5 byte.
Il tipo VARCHAR occupa, quindi, meno spazio, ma è più lento da
gestire rispetto al tipo CHAR. Si consiglia quindi di utilizzare VARCHAR
solamente nei casi in cui la lunghezza del valore non fosse fissa o
conosciuta dall'inizio (ad esempio nel caso di cognomi, nomi,
indirizzi, ecc.).
Può contenere caratteri alfanumerici, fino a 255 caratteri. La
memoria occupata è di (lunghezza) + 1.
Può contenere caratteri alfanumerici, fino a 255 caratteri. La
memoria occupata è di (lunghezza) + 1.
Può contenere caratteri alfanumerici, fino a 65.535 caratteri. La
memoria occupata è di (lunghezza) + 2.
Può contenere caratteri alfanumerici, fino a 65.535 caratteri. La
memoria occupata è di (lunghezza) + 2.
Può contenere caratteri alfanumerici, fino a 16.777.215 caratteri.
La memoria occupata è di (lunghezza) + 3.
Può contenere caratteri alfanumerici, fino a 16.777.215 caratteri.
La memoria occupata è di (lunghezza) + 3.
Può contenere caratteri alfanumerici, fino a 4.294.967.295
caratteri. La memoria occupata è di (lunghezza) + 4.
Può contenere caratteri alfanumerici, fino a 4.294.967.295
caratteri. La memoria occupata è di (lunghezza) + 4.
I tipi di campo BLOB sono classificati case sensitive, perché sensibili alle lettere
maiuscole, a differenza dei tipi di campo TEXT. Bisogna, quindi, fare molta
attenzione ad utilizzare i tipi di campo BLOB: “mysql” e “MySQL” vengono gestiti
come due valori diversi.
DATETIME
Memorizza la data e l'ora, compresa di minuti e secondi. Il
22/39
Dott.Ing.Ivan Ferrazzi
DATE
TIME
YEAR
TIMESTAMP(N)
ENUM(lista)
SET(lista)
formato che viene utilizzato è 'YYYY-MM-DD HH:MM:SS' e può
assumere valori che vanno da '1000-01-01 00:00:00' a '9999-12-31
23:59:59'.
Memorizza la data nel formato 'YYYY-MM-DD' e può assumere valori
che vanno da '1000-01-01' a '9999-12-31'.
Memorizza l’ora e può assumere valori che vanno da '-838:59:59'
a '838:59:59'.
Memorizza l’anno nel formato 'YYYY' e può assumere valori che
vanno da '1901' a '2155'.
Memorizza in modo automatico ad ogni record inserito, la data e
l'ora correnti. Il parametro N permette di definire il formato del
valore memorizzato come segue:
TIMESTAMP(14) per memorizzare YYYY-MM-DD HH:MM:SS
TIMESTAMP(12) per memorizzare YY-MM-DD HH:MM:SS
TIMESTAMP(10) per memorizzare YY-MM-DD HH:MM
TIMESTAMP(8) per memorizzare YYYY-MM-DD
TIMESTAMP(6) per memorizzare YY-MM-DD
TIMESTAMP(4) per memorizzare YY-MM
TIMESTAMP(2) per memorizzare YY
Permette di definire una lista di valori possibili per il campo in
questione. I valori vengono inseriti tra apici e separati da virgole.
Possiamo, ad esempio, definire un campo per la memorizzazione
dei valori SI/NO come segue: ENUM ('S','N'). Un campo definito
con l'utilizzo di questo tipo può comunque contenere anche il
valore nullo.
La lista fornita come parametro può contenere fino ad un
massimo di 65.535 voci. Inoltre è molto importante ricordare che
l'elaborazione di queste colonne è più rapida rispetto a quelle che
definiscono i campi di stringa, come VARCHAR, TEXT, ecc.
Uguale al tipo di campo ENUM con l'unica differenza nel poter
definire fino ad un massimo di 64 voci come lista di valori
possibili.
E' possibile creare una nuova tabella basandosi sulla struttura di una tabella già
esistente. In questo caso possiamo scrivere
CREATE TABLE nuova_tabella LIKE vecchia_tabella;
Tornando al nostro esempio di gestione di biblioteca abbiamo ora bisogno di
creare le quattro tabelle che ci accompagneranno per tutto il nostro percorso: i
lettori, gli autori, i libri ed il noleggio. La struttura delle tabelle verrà tenuta il
più semplice possibile. Non tentiamo, quindi, di mettere in piedi un esempio
qualitativamente pratico, ma semplice dal punto di vista della comprensione.
Digitiamo quindi:
CREATE TABLE lettori (
IDLettore INT(11) NOT NULL AUTO_INCREMENT,
Cognome VARCHAR(30),
23/39
Dott.Ing.Ivan Ferrazzi
Nome VARCHAR(20),
Data_nascita DATE,
PRIMARY KEY (IDLettore)
);
La prima tabella che abbiamo creato è la tabella per la memorizzazione dei
lettori. Il numero che permetterà di identificare il lettore ( PRIMARY KEY) viene
memorizzato all'interno del campo IDLettore il cui valore non potrà mai essere
nullo (NOT NULL) e aumenterà automaticamente ad ogni nuovo inserimento
(AUTO_INCREMENT).
CREATE TABLE autori (
IDAutore INT(11) NOT NULL AUTO_INCREMENT,
Cognome VARCHAR(30),
Nome VARCHAR(20),
Data_nascita DATE,
PRIMARY KEY (IDAutore)
);
La seconda tabelle che abbiamo creato è la tabella per la memorizzazione degli
autori. La struttura è identica a quella utilizzata per i lettori.
CREATE TABLE libri (
IDLibro INT(11) NOT NULL AUTO_INCREMENT,
IDAutore INT(11),
Titolo VARCHAR(50),
Genere ENUM ('Romanzo','Giallo','Horror'),
PRIMARY KEY (IDLibro),
FOREIGN KEY (IDAutore) REFERENCES autori(IDAutore)
ON DELETE CASCADE
ON UPDATE CASCADE
);
La terza tabella che abbiamo creato è la tabella libri. Il campo che permetterà
di identificare il libro univocamente (PRIMARY KEY) è il campo IDLibro, che non
potrà contenere valori nulli (NOT NULL) e verrà incrementato automaticamente
ad ogni nuovo inserimento (AUTO_INCREMENT). Un campo molto importante è
IDAutore che conterrà il valore IDAutore della tabella autori dell'autore che ha
scritto il libro (per semplificare gestiamo libri scritti da un solo autore). Questo
campo verrà, quindi, utilizzato per mettere in relazione questa tabella con la
tabella autori. La relazione la definiamo con FOREIGN KEY definendo che la
cancellazione o la modifica dell'autore avrà come conseguenza la cancellazione
o la modifica dei relativi libri.
Inoltre notiamo il campo Genere definito come ENUM che permette di limitare a
Romanzo, Giallo e Horror i generi di libro che possono essere inseriti.
CREATE TABLE noleggi (
IDNoleggio INT(11) NOT NULL AUTO_INCREMENT,
DataNoleggio DATE,
IDLibro INT(11),
IDLettore INT(11),
Riportato ENUM ('S','N') DEFAULT ('N'),
PRIMARY KEY (IDNoleggio),
24/39
Dott.Ing.Ivan Ferrazzi
);
FOREIGN KEY (IDLibro) REFERENCES libri(IDLibro)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (IDLettore) REFERENCES lettori(IDLettore)
ON DELETE CASCADE
ON UPDATE CASCADE
L'ultima tabella che abbiamo creato è la tabella noleggi che permette di
memorizzare i vari noleggi che vengono effettuati durante il periodo di
gestione. IDNoleggio è il numero progressivo che permetterà di identificare il
noleggio effettuato, mentre DataNoleggio permetterà la memorizzazione della
data di quando il noleggio è avvenuto.
IDLibro e IDLettore conterranno i valori dei rispettivi campi dalle tabelle libri e
lettori, permetteranno, quindi, di mettere in relazioni le due tabelle. Il campo
riportato, che può contenere solamente i valori 'S' oppure 'N', viene utilizzato
per memorizzare la riconsegna del libro. Se questo valore non viene definito
all'interno del comando INSERT INTO il valore inserito sarà 'N' (DEFAULT).
Per eliminare un'eventuale tabella non più utilizzata o creata erroneamente
possiamo utilizzare il seguente comando:
DROP TABLE [IF EXISTS] tabella[, tabella];
tabella
Permette di definire il nome della tabella da eliminare.
E' anche possibile rinominare una tabella esistente con
RENAME TABLE vecchia_tabella TO nuova_tabella[, v_tabella2 TO n_tabella2];
Il seguente comando permette di elencare tutte le tabelle presenti all'interno
della banca dati alla quale ci siamo connessi:
SHOW TABLES;
Una volta certi della presenza di una tabella ne possiamo verificare la propria
struttura con il comando:
DESCRIBE tabella;
oppure
DESC
tabella;
tabella
Permette di definire il nome della tabella per la quale visualizzare
la struttura su schermo.
Il comando che permette di alterare la struttura della tabella nelle sue singole
parti è ALTER TABLE. L'utilizzo di questo comando ha come conseguenza la
rielaborazione dei record in base all'operazione richiesta e non la loro
25/39
Dott.Ing.Ivan Ferrazzi
cancellazione.
Per motivi di complessità del comando stesso ci limitiamo a trattare solamente
le forme più utilizzate, ossia ADD, per aggiungere nuovi campi, CHANGE, per
modificare campi esistenti, e DROP, per eliminare campi indesiderati:
ALTER TABLE tabella ADD [COLUMN] campo tipo_dato [AFTER campo2];
tabella
campo
tipo_dato
campo2
Permette di definire il nome della tabella per la quale verrà
effettuata la modifica.
Permette di definire il nome del campo da aggiungere alla
tabella.
Permette di definire il tipo di dato per il quale verrà utilizzato il
campo (vedi CREATE TABLE).
Normalmente il nuovo campo viene inserito come ultimo campo
della tabella. Possiamo usare AFTER per definire il campo
attualmente presente all'interno della tabella dopo il quale verrà
inserito il nuovo campo.
ALTER TABLE tabella CHANGE [COLUMN] campo_v campo_n tipo_dato;
tabella
campo_v
campo_n
tipo_dato
Permette di definire il nome della tabella per la quale verrà
effettuata la modifica.
Permette di definire il nome del campo per il quale dovrà avere
effetto la modifica.
Permette di definire il nuovo nome da assegnare al campo
campo_v. Se vogliamo lasciare invariato il nome e modificare
solamente il tipo di dato avremo campo_n che sarà uguale al valore
di campo_v.
Permette di definire il nuovo tipo di dato che vogliamo assegnare
al campo in questione.
ALTER TABLE tabella DROP [COLUMN] campo;
tabella
campo
Permette di definire il nome della tabella per la quale verrà
effettuata la modifica.
Permette di definire il nome del campo che vogliamo eliminare.
Per tornare al nostro esempio poniamo il caso di esserci dimenticato del campo
CAP all'interno della tabella per la memorizzazione dei lettori. Per il campo CAP
utilizziamo il tipo di dato CHAR(5). Per modificare la rispettiva tabella scriviamo:
ALTER TABLE lettori ADD COLUMN CAP CHAR(5);
Utilizziamo il comando
DESCRIBE lettori;
per verificare la nuova struttura della tabella in questione.
26/39
Dott.Ing.Ivan Ferrazzi
La gestione dei record.
Una volta create le tabelle desiderate possiamo proseguire con la gestione dei
record. Come gestione dei record si intenda l'inserimento di nuovi record, la
modifica di record esistenti e/o l'eventuale eliminazione di record non più
utilizzati.
Inserire record con INSERT INTO
Per effettuare l'inserimento di un nuovo record usiamo il seguente comando:
INSERT INTO tabella (campo1,campo2,...,campoN)
VALUES (valore1,valore2,...,valoreN);
tabella
campoX
valoreX
Permette di definire il nome della tabella all'interno della quale si
vuole aggiungere il record desiderato.
Permette di definire il nome del campo X per il quale si vuole
aggiungere il valore X.
Permette di definire il valore da assegnare al campo X. Il valore
va aggiunto tra apici (') se il campo è di tipo testuale (es. CHAR,
VARCHAR, DATE, ecc.) e senza apici in caso di tipo numerico (es.
FLOAT, INT, ecc.).
Inseriamo ora i seguenti record alla tabella autori del nostro esempio:
INSERT INTO autori (Cognome,Nome,Data_nascita)
VALUES ('Rossi','Mario','1958-01-31');
INSERT INTO autori (Cognome,Nome,Data_nascita)
VALUES ('Verdi','Antonio','1945-03-16');
Con questi due comandi inseriamo due autori all'interno della rispettiva tabella.
Notiamo l'omissione del campo IDAutore, non necessario perché definito come
AUTO_INCREMENT (il valore viene incrementato automaticamente ad ogni nuovo
inserimento). Il sistema assegnerà quindi all'autore Rossi Mario il valore 1 come
IDAutore e all'autore Verdi Antonio il valore 2.
Aggiungiamo ora i seguenti libri:
INSERT INTO libri (IDAutore,Titolo,Genere)
VALUES (1,'Il mio Destino','Romanzo');
INSERT INTO libri (IDAutore,Titolo,Genere)
VALUES (1,'Non aprite quel portone','Horror');
INSERT INTO libri (IDAutore,Titolo,Genere)
VALUES (2,'Con le ali in mano','Romanzo');
INSERT INTO libri (IDAutore,Titolo,Genere)
VALUES (2,'Senza dubbio','Giallo');
Anche qui notiamo l'omissione del campo IDLibro perché definito come
AUTO_INCREMENT. Inoltre notiamo l'inserimento dei valori testuali (es. Titolo) con
gli apici, mentre il valore numerico (IDAutore) viene inserito senza.
Continuiamo a riempire anche le restanti tabelle come segue:
27/39
Dott.Ing.Ivan Ferrazzi
INSERT INTO lettori (Cognome,Nome,Data_nascita)
VALUES ('Franceschi','Franco','1975-04-15');
INSERT INTO lettori (Cognome,Nome,Data_nascita)
VALUES ('Alberti','Alberto','1978-08-02');
INSERT INTO noleggi (DataNoleggio,IDLibro,IDLettore)
VALUES ('2009-01-16',1,1);
INSERT INTO noleggi (DataNoleggio,IDLibro,IDLettore)
VALUES ('2009-02-02',2,2);
Abbiamo quindi aggiunto due nuovi lettori che hanno a loro volta noleggiato un
libro a testa. Il primo ha noleggiato il libro con IDLibro uguale a 1, mentre il
secondo ha noleggiato il libro con IDLibro uguale a 2. Inoltre notiamo che
nell'inserimento dei record nella tabella dei noleggi possiamo omettere il valore
del campo Riportato perché definito con un valore di default (N).
Modificare record con UPDATE
Oltre all'inserimento dei record abbiamo anche la possibilità di modificare i
singoli valori presenti all'interno di ogni singolo record. Per modificare un
record usiamo:
UPDATE tabella SET campo1=valore1[,campo2=valore2,...] WHERE condizione;
tabella
campoX
valoreX
criteri
Permette di definire il nome della tabella all'interno della quale si
vuole modificare il record desiderato.
Permette di definire il nome del campo X per il quale si vuole
modificare il valore X.
Permette di definire il valore da assegnare al campo X. Il valore
va aggiunto tra apici (') se il campo è di tipo testuale (es. CHAR,
VARCHAR, DATE, ecc.) e senza apici in caso di tipo numerico (es.
FLOAT, INT, ecc.).
Permette di identificare il record per il quale si intende eseguire
la modifica. I criteri vengono definiti con la forma campo=valore. E'
possibile combinare in serie più criteri mediante AND o OR logico.
Nel momento in cui il primo lettore restituisca il suo libro possiamo modificare il
campo Riportato come segue:
UPDATE noleggi SET Riportato='S' WHERE IDLettore=1 AND IDLibro=1;
Eliminare record con DELETE FROM
Possiamo anche eliminare definitivamente da una tabella record non più
utilizzati. Se avessimo inserito il secondo noleggio per errore lo si potrebbe
eliminare con il comando:
DELETE FROM tabella WHERE criteri;
28/39
Dott.Ing.Ivan Ferrazzi
tabella
criteri
Permette di definire il nome della tabella dalla quale si vuole
eliminare il record.
Permette di identificare il record che si vuole elminare. Anche qui
possiamo definire i criteri con la forma campo=valore. E' possibile
combinare in serie più criteri mediante AND o OR logico.
ossia
DELETE FROM noleggi WHERE IDLettore=2;
La proiezione con il comando SELECT.
Il comando SELECT permette di proiettare il contenuto di una o più tabelle sullo
schermo visualizzandone i contenuti a piacere. La struttura semplice del
comando è la seguente:
SELECT campo1,campo2,...,campoN FROM tabella;
campoX
tabella
Permette di definire il nome del campo per il quale si vuole
visualizzare il valore dei record elencati. Possiamo utilizzare
anche l'asterisco (*) al posto dei singoli campi per visualizzare
sullo schermo tutti i campi disponibili.
Permette di identificare la tabella dalla quale vogliamo elencare i
record contenuti.
Per visualizzare su schermo tutti i libri registrati all'interno della nostra banca
dati possiamo scrivere:
SELECT * FROM libri;
Il risultato sarà il seguente:
IDLibro
IDAutore
Titolo
Genere
============================================================
1
1
Il mio Destino
Romanzo
2
1
Non aprite quel portone
Horror
3
2
Con le ali in mano
Romanzo
4
2
Senza dubbio
Giallo
Possiamo però limitare anche la proiezione dei campi definendoli direttamente
all'interno del blocco SELECT come segue:
SELECT Titolo,Genere FROM libri;
che avrà come risultato:
Titolo
Genere
=====================================
Il mio Destino
Romanzo
29/39
Dott.Ing.Ivan Ferrazzi
Non aprite quel portone
Con le ali in mano
Senza dubbio
Horror
Romanzo
Giallo
Per sapere quali sono i genere di libri che abbiamo registrato all'interno della
nostra banca dati possiamo scrivere:
SELECT Genere FROM libri;
Il risultato sarebbe il seguente:
Genere
========
Romanzo
Horror
Romanzo
Giallo
In questo caso, però, notiamo una ripetizione indesiderata. A noi interessa
sapere il genere dei libri che abbiamo; il valore Romanzo dovrebbe quindi venire
fuori una volta sola. Per questo motivo possiamo aggiungere al blocco SELECT la
direttiva DISTINCT che evita di estrarre valori doppi. Nel nostro esempio
possiamo usare:
SELECT DISTINCT Genere FROM libri;
per mostrare il risultato come segue:
Genere
========
Romanzo
Horror
Giallo
I valori dei campi che vengono utilizzati per la proiezione all'interno del blocco
SELECT possono essere elaborati mediante apposite funzioni. Una di queste è la
funzione CONCAT che permette di concatenare il valore di due o più campi
all'interno di un'unica colonna di risultato. Possiamo quindi mostrare il valore
dei campi Cognome e Nome proiettato all'interno di un'unica colonna come segue:
SELECT CONCAT(Cognome,Nome) FROM autori;
Il risultato verrebbe visualizzato su schermo come segue:
CONCAT(Cognome,Nome)
====================
RossiMario
VerdiAntonio
Nel risultato notiamo due cose che potrebbero dare fastidio. La prima è il titolo
della colonna CONCAT(Cognome,Nome) e la seconda è il concatenamento senza
spazi dei due valori Cognome e Nome (es. RossiMario).
30/39
Dott.Ing.Ivan Ferrazzi
Il titolo può essere modificato utilizzando la direttava AS in combinazione con la
funzione utilizzata, mentre possiamo aggiungere una nuova posizione di
concatenamento tra i valori Cognome e Nome che contiene uno spazio vuoto per
staccare in visualizzazione i due valori.
Modifichiamo quindi il precedente comando come segue:
SELECT CONCAT(Cognome,' ',Nome) AS Autori FROM autori;
per restituire il seguente risultato:
Autori
=============
Rossi Mario
Verdi Antonio
Il blocco WHERE.
Con i blocchi SELECT e FROM possiamo modificare la proiezione del risultato, ma
non possiamo definire quali record vogliamo visualizzare; vengono visualizzati
sempre tutti i record presenti all'interno di una specifica tabella. Per questo
motivo possiamo aggiungere ai blocchi visti fino ad ora il blocco WHERE che
permette di selezionare, in base a specifici criteri, quali record mostrare come
risultato.
La struttura del SELECT viene quindi modificata nella seguente maniera:
SELECT campo1,campo2,...,campoN FROM tabella WHERE criteri;
Nella sezione criteri possiamo trovare più blocchi di criteri legati l'uno all'altro
mediante AND oppure OR logico. Il singolo blocco di criteri può avere il seguente
formato:
campo1 {=|>|<|>=|<=|<>} valore
Possiamo utilizzare un semplice blocco di criteri utilizzando il formato qui sopra
per estrarre dalla nostra tabella libri tutti i titoli dei romanzi.
SELECT Titolo FROM libri WHERE Genere='Romanzo';
Il risultato è quello mostrato qui di seguito:
Titolo
===================
Il mio Destino
Con le ali in mano
Notiamo all'interno del blocco dei criteri l'utilizzo degli apici per confrontare
valori di tipo testuale come CHAR, VARCHAR, DATE, ecc.
Per estrarre tutti i lettori nati nell'anno 1975 scriviamo:
31/39
Dott.Ing.Ivan Ferrazzi
SELECT Cognome,Nome FROM lettori
WHERE Data_nascita>='1975-01-01' AND Data_nascita<='1975-12-31';
Come risultato vediamo:
Cognome
Nome
=====================
Franceschi
Franco
Notiamo l'utilizzo degli operatori maggiore di o uguale a (>=) e minore di o
uguale a (<=) con AND come operatore logico. L'operatore AND restituisce un
record solo se sia il primo che il secondo blocco di criteri restituiscono esito
positivo.
Gli operatori visti precedentemente permettono di filtrare determinati record
con valori nei campi che corrispondono a valori predefiniti, ma non permettono
di filtrare definendo solamente una parte del valore. Se volessimo estrarre, ad
esempio, tutti gli autori il quale cognome inizia con la lettera R non lo
potremmo fare con i semplici operatori visti fino ad ora.
E' possibile però utilizzare all'interno del blocco WHERE l'operatore LIKE che mette
a disposizione i caratteri jolly. I caratteri jolly sono dei caratteri che prendono il
posto di uno o una serie di caratteri non definiti in dettaglio. Il carattere %
(percentuale) permette di rimpiazzare nessuno o un numero non definito di
caratteri.
Scriviamo il seguente comando:
SELECT Cognome,Nome FROM autori WHERE Cognome LIKE 'R%';
per restituire il seguente risultato:
Cognome
Nome
=================
Rossi
Mario
Un'altro operatore che possiamo utilizzare all'interno del blocco WHERE è
BETWEEN ... AND ... . Questo operatore permette di definire aree di valori da
estrarre. Per riprendere l'esempio precedente dove abbiamo estratto tutti i
lettori nati nell'anno 1975 possiamo scrivere:
SELECT Cognome,Nome FROM lettori
WHERE Data_nascita BETWEEN '1975-01-01' AND '1975-12-31';
che restituisce il seguente risultato:
Cognome
Nome
=====================
Franceschi
Franco
Con l'operatore IN possiamo estrarre tutti i record i quali valori nei campi
corrispondono ad una lista di possibili valori. L'operatore NOT IN ne esclude la
32/39
Dott.Ing.Ivan Ferrazzi
visualizzazione. Per estrarre tutti i libri appartenenti alla categoria Giallo e
Romanzo possiamo scrivere:
SELECT Titolo,Genere FROM libri
WHERE Genere IN ('Romanzo','Giallo');
Il risultato è:
Titolo
Genere
================================
Il mio Destino
Romanzo
Con le ali in mano
Romanzo
Senza dubbio
Giallo
Come descritto precedentemente possiamo utilizzare gli operatori logici AND e/o
OR per concatenare più criteri. L'operatore logico AND estrae tutti i record che
restituiscono esito positivo a tutti i criteri concatenati, mentre l'operatore OR
restituisce i record con esito positivo in almeno uno dei criteri.
Il precedente risultato può essere restituito anche con il seguente comando:
SELECT Titolo,Genere FROM libri
WHERE Genere='Romanzo' OR Genere='Giallo';
Il blocco ORDER BY
Il SELECT mette a disposizione anche il blocco ORDER BY che permette di
visualizzare il risultato in ordine crescente (ASC) oppure decrescente (DESC)
come segue:
SELECT Titolo,Genere FROM libri ORDER BY Titolo DESC;
Titolo
Genere
================================
Con le ali in mano
Romanzo
Il mio Destino
Romanzo
Non aprite quel portone
Horror
Senza dubbio
Giallo
E' possibile ordinare per più colonne. In questo caso elenchiamo le colonne
interessate con l'apposita opzione di ordinamento separandole con una virgola.
SELECT Titolo,Genere FROM libri ORDER BY Genere DESC, Titolo DESC;
Il blocco LIMIT
Il numero delle righe visualizzate come risultato può essere modificato con il
blocco LIMIT. Per visualizzare solamente i primi due risultati del elenco
precedente scriviamo:
SELECT Titolo,Genere FROM libri ORDER BY Titolo DESC LIMIT 2;
33/39
Dott.Ing.Ivan Ferrazzi
Titolo
Genere
================================
Con le ali in mano
Romanzo
Il mio Destino
Romanzo
E' possibile estrarre un determinato numero di record a partire da una
posizione precisa. In questo caso scriviamo
SELECT Titolo,Genere FROM libri ORDER BY Titolo DESC LIMIT 10,10;
Il primo parametro indica il record di partenza (i record partono da indice 0)
mentre il secondo indica il numero di record da estrarre. Nell'esempio appena
visto verranno estratte le prime 10 righe a partire dall'undicesimo record.
Il blocco GROUP BY.
In alcuni casi può essere indispensabile raggruppare dei record in base a campi
uguali ed eseguire determinate funzioni su altri campi non identici. Questa
funzione viene appunto chiamata raggruppamento e si ottiene utilizzando il
blocco GROUP BY in combinazione con una delle funzioni MIN, MAX, AVG, SUM, COUNT,
ecc.
Per sapere quanti libri di ogni genere abbiamo nella nostra biblioteca possiamo
scrivere:
SELECT Genere,COUNT(Titolo) FROM libri GROUP BY Genere;
Come risultato vediamo:
Genere
COUNT(Titolo)
=======================
Romanzo
2
Horror
1
Giallo
1
L'intero risultato viene raggruppato in base al campo Genere, mentre sul campo
Titolo viene utilizzato la funzione COUNT che conta il numero dei record che sono
stati raggruppati.
La funzione MIN restituisce il valore più basso nel gruppo di record raggruppati,
MAX restituisce il valore più alto, AVG la media, mentre SUM restituisce la somma
di tutti i valori raggruppati.
Il blocco GROUP BY deve quindi contenere l'elenco di tutti i campi separati da una
virgola che vengono utilizzati all'interno di una funzione di raggruppamento.
Il blocco HAVING.
In aggiunta al blocco GROUP BY possiamo applicare anche il blocco HAVING che
permette di definire quali tra i record raggruppati devono essere inseriti nella
funzione di raggruppamento. La struttura del SELECT avrebbe quindi il seguente
aspetto:
34/39
Dott.Ing.Ivan Ferrazzi
SELECT campo1,campo2,COUNT(campo3) FROM tabella
GROUP BY campo1,campo2 HAVING criteri
Nei nostri esempi abbiamo fino ad ora utilizzato solamente una tabella come
punto di riferimento per l'estrazione dei campi. Nel blocco FROM possiamo però
aggiungere anche più di una tabella. In questo caso possiamo utilizzare una
sigla in combinazione al nome della tabella per definire da quale tabella
vogliamo estrarre il campo desiderato. Vediamo il seguente esempio:
SELECT a.IDAutore,a.Cognome,a.Nome,l.IDAutore,l.Titolo
FROM autori a,libri l;
Nel blocco FROM inseriamo le tabelle che ci interessano, ossia autori e libri,
separate da una virgola. Dopo ogni nome di tabella aggiungiamo una sigla, a
per la tabella autori e l per la tabella libri che permetteranno di identificare
l'appartenenza dei campi nel blocco SELECT. Infatti scriviamo a.IDAutore, a.Cognome e a.Nome per far capire al sistema che vogliamo proiettare il contenuto
del campi IDAutore, Cognome e Nome della tabella autori e l.IDAutore e l.Titolo
per i campi IDAutore e Titolo della tabella libri.
Il risultato del precedente esempio è il seguente:
a.IDAutore a.Cognome
a.Nome
l.IDAutore
l.Titolo
=================================================================
1
Rossi
Mario
1
Il mio Deserto
1
Rossi
Mario
1
Non aprite quel portone
1
Rossi
Mario
2
Con le ali in mano
1
Rossi
Mario
2
Senza dubbio
2
Verdi
Antonio
1
Il mio Deserto
2
Verdi
Antonio
1
Non aprite quel portone
2
Verdi
Antonio
2
Con le ali in mano
2
Verdi
Antonio
2
Senza dubbio
Vediamo subito che nel risultato visualizzato un qualcosa non funziona.
Utilizzando solamente i blocchi SELECT e FROM e aggiungendo più di una tabella il
risultato che ne deriva è il prodotto cartesiano di tutti i record di una tabella
con tutti i record dell'altra, come visualizzato nel risultato.
Notiamo che i record corretti sono quelli dove il valore nel campo a.IDAutore è
uguale al campo l.IDAutore. Per estrarre quindi solamente queste righe
possiamo utilizzare il blocco WHERE. Possiamo quindi modificare il comando come
segue:
SELECT a.IDAutore,a.Cognome,a.Nome,l.IDAutore,l.Titolo
FROM autori a,libri l WHERE a.IDAutore=l.IDAutore;
Il nuovo risultato è quindi il seguente:
a.IDAutore a.Cognome
a.Nome
l.IDAutore
l.Titolo
=================================================================
1
Rossi
Mario
1
Il mio Deserto
1
Rossi
Mario
1
Non aprite quel portone
2
Verdi
Antonio
2
Con le ali in mano
35/39
Dott.Ing.Ivan Ferrazzi
2
Verdi
Antonio
2
Senza dubbio
Con il blocco WHERE siamo quindi riusciti a mettere in piedi una relazione tra la
tabella autori e libri.
Una relazione definita come nell'esempio precedente non restituisce sempre il
risultato voluto. Prendiamo in considerazione l'aggiunta di un nuovo lettore che
non ha ancora noleggiato nessun libro come segue:
INSERT INTO lettori (Cognome,Nome,Data_nascita)
VALUES ('Filippi','Antonella','1972-06-10');
Ora vogliamo estrarre dalla nostra banca dati l'elenco dei lettori con il numero
dei libri che hanno noleggiato sino ad ora. Creiamo quindi la relazione tra le
tabella lettori e noleggi come precedentemente illustrato:
SELECT l.Cognome,l.Nome,COUNT(n.IDLibro) AS Libri
FROM lettori l,noleggi n
WHERE l.IDLettore=n.IDLettore GROUP BY l.Cognome,l.Nome
Apparirà il seguente risultato:
l.Cognome
l.Nome
Libri
===================================
Franceschi
Franco
1
Alberti
Alberto
1
Le relazioni con LEFT JOIN, RIGHT JOIN, FULL JOIN e INNER JOIN.
Notiamo subito l'estrazione dei due lettore che hanno effettuato un noleggio,
ma notiamo anche che il nuovo lettore non viene estratto perché privo di
noleggi. Per visualizzare anche i record che non hanno per forza una
corrispondenza nella tabella messa in relazione dobbiamo utilizzare la direttiva
JOIN.
Il JOIN può essere utilizzato come LEFT JOIN, RIGHT JOIN, FULL JOIN oppure INNER
JOIN. Il LEFT JOIN permette di effettuare una relazione tra due tabelle partendo
da quella di sinistra compila una tabella di risultato nella quale vengono inseriti
tutti i record della tabella di sinistra con la relativa corrispondenza della tabella
di destra. Nel caso in cui la tabella di destra non contiene record corrispondenti
inserisce una serie di valori NULL.
Il comando viene utilizzato come segue:
SELECT campo1,campo2,...campoN
FROM tabella1 LEFT JOIN tabella2 ON (criteri);
tabellaX
campoX
criteri
Permette di definire il nome delle tabelle che vengono messe in
relazione.
Permette di definire il nome del campo X che si vuole visualizzare
nel risultato.
Permette di identificare i campi mediante i quali si intende creare
36/39
Dott.Ing.Ivan Ferrazzi
la
relazione.
I
criteri
vengono definiti con la forma
possibile combinare in serie più
campo_tabella1=campo_tabella2. E'
criteri mediante AND o OR logico.
Se modifichiamo la richiesta del nostro esempio precedente abbiamo:
SELECT l.Cognome,l.Nome,COUNT(n.IDLibro) AS Libri
FROM lettori l LEFT JOIN noleggi n ON (l.IDLettore=n.IDLettore)
GROUP BY l.Cognome,l.Nome
che restituirà il seguente risultato:
l.Cognome
l.Nome
Libri
===================================
Franceschi
Franco
1
Alberti
Alberto
1
Filippi
Antonella
NULL
Il RIGHT JOIN funziona allo stesso modo del LEFT JOIN, ma prende come tabella di
riferimento quella di destra. Il FULL JOIN permette di combinare i due JOIN
precedenti mostrando quindi sia i record che non hanno una corrispondenza da
una, sia quelli che non hanno una corrispondenza dall'altra parte. L' INNER JOIN
permette di estrarre solamente i record che hanno una reciproca
corrispondenza.
Vediamo i seguenti esempi per rendere più chiaro il concetto:
SELECT l.Cognome,l.Nome,COUNT(n.IDLibro) AS Libri
FROM noleggi n RIGHT JOIN lettori l ON (n.IDLettore=l.IDLettore)
GROUP BY l.Cognome,l.Nome
ha come risultato:
l.Cognome
l.Nome
Libri
===================================
Franceschi
Franco
1
Alberti
Alberto
1
Filippi
Antonella
NULL
mentre
SELECT l.Cognome,l.Nome,COUNT(n.IDLibro) AS Libri
FROM noleggi n INNER JOIN lettori l ON (n.IDLettore=l.IDLettore)
GROUP BY l.Cognome,l.Nome
restituisce:
l.Cognome
l.Nome
Libri
===================================
Franceschi
Franco
1
Alberti
Alberto
1
37/39
Dott.Ing.Ivan Ferrazzi
Le subquery.
Le subquery permettono di estrarre dei record in base a valori estratti tramite
seconda query. Le subquery si possono inserire all'interno del blocco WHERE
oppure all'interno del blocco FROM.
Le subquery nel blocco WHERE
Le parole chiave che possiamo usare per paragonare un campo con una serie
di record sono ANY, SOME, ALL, IN e NOT IN. Vediamo il seguente esempio
SELECT * FROM t1 WHERE c1 < ANY (SELECT c2 FROM t2)
In questo caso vengono estratti tutti i record dove il valore di c1 è inferiore ad
almeno uno dei valori c2 estratti dalla tabella t2. La parola chiave SOME è
identica all'utilizzo della parola chiave ANY.
Possiamo anche estrarre i record dove il valore di c1 è uguale ad almeno uno
dei valori in c2 della tabella t2. In questo caso scriviamo
SELECT * FROM t1 WHERE c1 = ANY (SELECT c2 FROM t2)
oppure
SELECT * FROM t1 WHERE c1 IN (SELECT c2 FROM t2)
Per estrarre tutti i record dove il valore di c1 è superiore a tutti i valori c2
estratti dalla tabella t2 scriviamo
SELECT * FROM t1 WHERE c1 > ALL (SELECT c2 FROM t2)
La seguente query estrae invece tutti i record dove il valore di c1 non è
presente all'interno del campo c2 della tabella t2.
SELECT * FROM t1 WHERE c1 > ALL (SELECT c2 FROM t2)
oppure
SELECT * FROM t1 WHERE c1 NOT IN (SELECT c2 FROM t2)
E' possibile paragonare anche più di una colonna utilizzando il seguente
formato
SELECT * FROM t1 WHERE c1,c2 NOT IN (SELECT c3,c4 FROM t2)
Le subquery nel blocco FROM
In questo caso possiamo utilizzare la subquery per far creare al sistema una
tabella interna con la quale andiamo poi a concatenare altre tabella a piacere
38/39
Dott.Ing.Ivan Ferrazzi
SELECT t.c1,t2.c3 FROM (SELECT c1,c2 FROM t1) AS t, t2 WHERE t.c1=t2.c3
Internamente il sistema crea la tabella temporanea in base alla subquery
SELECT c1,c2 FROM t1
chiamandola con l'alias definito dopo AS, ossia t. Poi si occupa dei controlli con
relativa proiezione.
39/39