SEFOC – SETTORE FORMAZIONE CONTINUA DEL PERSONALE
CORSO MYSQL AVANZATO
ROBERTO PENNOLINO – CARMELO MASSIMO PRIOLO
PALERMO – 07 | 06 | 2011
Programma del corso
•
Differenti tipi di tabelle in MySQL (Roberto Pennolino)
•
•
Differenti tipi di dati (Roberto Pennolino)
•
•
•
•
•
•
•
•
dati numerici - dati alfanumerici - dati temporali
Opzioni di definizione dei dati – DDL (Roberto Pennolino)
Creazione di tabelle (Roberto Pennolino)
Inserimento e modifica di dati nelle tabelle (Carmelo Massimo Priolo)
•
•
•
•
MyISAM, InnoDB
Insert, replace, update, delete, truncate table
Select (Carmelo Massimo Priolo)
Uso degli alias in MySQL (Carmelo Massimo Priolo)
where, order by, aggregazione (group by, having), calcolo dei valori, query nidificate, like, null, inner join, outer
join (Carmelo Massimo Priolo)
Integrità referenziale (Carmelo Massimo Priolo)
Viste (Roberto Pennolino)
Importazione ed esportazione dei dati (Roberto Pennolino)
Backup e restore del database (Roberto Pennolino)
CORSO MYSQL AVANZATO
ROBERTO PENNOLINO
PALERMO
07 | 06 | 2011
Documentazione
MySQL Server:
IP:
147.163.15.254
Utente:
utente<x>
Password:
utente<x>
Slide e documentazione di riferimento.
http://www.supportoricerca.unipa.it/j/media/corsomysql/slidemysql1.ppt
http://www.supportoricerca.unipa.it/j/media/corsomysql/Dispensa_MySQL_5.0.pdf
http://www.supportoricerca.unipa.it/j/media/corsomysql/Dump.sql
http://www.supportoricerca.unipa.it/j/media/corsomysql/refman-5.5-en.a4.pdf
Struttura del DB di esempio
SELECT 1/n
Sintassi
• SELECT <s_espressione>
• FROM <tabelle>
• WHERE <condizioni>
• GROUP BY <g_espressione>
• HAVING
• ORDER BY <colonne>
• LIMIT <n>
SELECT 2/n
• Select * from progetti
Visualizza tutte le righe e tutte le colonne della
tabella progetti
• Select 1 +1 Esempio di espressione senza rif. Tabella
•
Il risultato è una tabella di una sola riga e colonna con valore 2. La colonna
avrà nome “1+1”
• Select 1 + 1 As somma (ALIAS su colonna)
La colonna del risultato dell’espressione si
chiamerà “somma”
SELECT
Select cognome, nome from anagrafica
Seleziona cognome e nome dalla tabella anagrafica
Select left(cognome, 3), left(nome,3) from anagrafica;
Prende i primi 3 caratteri del nome e del cognome
Select left(T.cognome, 3) c3, left(T.nome,3) n3 from anagrafica as T;
Stessa della precedente con alias di colonna (c3 ed n3) e di tabella.
Nota. La LEFT restituisce la stringa in input se questa è minore del
numero di caratteri richiesti
SELECT – Funzioni
•
•
•
•
•
•
Concat( stringa1,….,stringa2) concatena più stringhe
Length( stringa) numero caratteri di stringa
Left( stringa, n ) Estrae i primi n caratteri di stringa
Right( stringa, n ) Estrae gli ultimi n caratteri di stringa
Substr( stringa, pos, n ) prende n caratteri a partire da pos
Coalesce( espr1, espr2, ….., esprn ) Restituisce la prima
espressione non nulla
• Nullif( espr1, espr2 ) Restituisce NULL se le espressioni sono
uguali
• IFNull ( espr1, espr2 ) ritorna la prima espressione non nulla. Come
Coalesce ma con 2 soli parametri
• IF( cond, espr1T, espr2F )
SELECT – funzioni di aggregazione
•
•
•
•
Avg() – Calcola la media aritmetica dei valori. Null non considerati;
Min() – Trova il valore minimo dell’insieme
Max() - Trova il valore Massimo dell’insieme
Sum() – Calcola la somma degli elementi dell’insieme. Null non
considerati;
• Count(*) – Conta le righe della tabella;
• Count(col) – Il numero degli elementi della colonna ‘col’. I NULL
sono esclusi dal conteggio;
Esempio:
Select count(*) from progetti;
Conta il numero di righe della tabella progetti
Esempi di funzioni di aggregazione
• Select max(costo) from costiorari;
• Select avg(costo) from costiorari
• Select min(costo) from costiorari
• Select min(costo) minimo, max(costo) massimo
From CostiOrari;
WHERE
La clausola Where permette di applicare un filtro alle righe da
selezionare. Le righe che soddisfano il filtro saranno selezionate.
Select cognome, nome from anagrafica Where cognome=‘rossi’;
Seleziona tutte le righe che hanno rossi nella colonna “cognome”
Select cognome, nome from anagrafica Where left(cognome,3)=‘ros’
and left(nome,2)=‘al’ ;
Select * from anagrafica Where cognome like ‘r%’;
% = Qualsiasi sequenza di caratteri, anche di lunghezza 0
_ = Esattamente un carattere
Select * from anagrafica Where cognome like ‘ross_’;
Prende le righe con cognome di 5 caratteri con i primi 4
caratteri uguali a ‘ross’
WHERE – Espressioni regolari
• Le espressioni regolari sono regole attraverso le
quali si possono rappresentare insiemi di
stringhe. Un’espressione regolare ritorna 1 se
una data stringa rispetta un determinato ‘pattern
matching’
• Select * from anagrafica where nome REGEXP
‘^[a-c]’
• Prende tutti i nomi che iniziano per a,b o c
REGEXP - caratteri
•
•
•
•
•
•
•
•
•
•
•
•
•
^
$
.
*
+
?
abc| cde
(abc)*
{n}
{n,m}
[a-z]
[^a-z]
\\.
inizio riga
fine riga
qualunque caratter incluso return e new line
zero o più ripetizioni del carattere/sequ.za che precede
uno o più ripetizioni del carattere/sequ.za che precede
Zero o 1 carattere
sequenza ‘abc’ o ‘cde’
0 o più sequenze di ‘abc’
esattamente n occorrenze
occorrenze in numero comprese tra n ed m. m opzionale
range di caratteri tra a e z
carattere non appartenente a “a,b,c,…,z”
Per introdurre un carattere speciale. Esempio \\+ \\*
c* equivale c{0,}
c+ equivale c{1,}
c? equivale c{0,1}
‘1\\+2’
Equivale alla stringa ‘1+2’
REGEXP
Select * from anagrafica
Where nome REGEXP ‘^(max)+$’
• Prende tutte le righe della tabella anagrafica con nome
composto da una o più ripetizioni della stringa ‘max’
• ‘max’, ‘maxmax’, ‘maxmaxmax’, ecc….
• Scrivere una select che estrae da anagrafica tutti i
nominativi che iniziano per a, b o c
• Soluzione:
• Select * from anagrafica where nome REGEXP ‘^[a-c]’
JOIN – Prodotto Cartesiano
A JOIN B
è il prodotto cartesiano tra A e B
Select A.ID, A.nome, A.IDAteneo IDA, B.IDAteneo IDB, B.Ateneo from A
JOIN B
Equivale a:
Select A.ID, A.nome, A.IDAteneo IDA, B.IDAteneo IDB, B.Ateneo from A,B
Genera una tabella con nA*nB righe.
Sono tutte le possibile coppie formate da una riga di A ed una riga di B
A
B
ID
nome
IDAteneo
ID
nome
IDA
IDB
Ateneo
1
Lagalla
3
1
Lagalla
3
3
Unipa
2
Valenti
3
1
Lagalla
3
4
Unimi
3
Rossi
4
2
Valenti
3
3
Unipa
2
Valenti
3
4
Unimi
3
Rossi
4
3
Unipa
3
Rossi
4
4
Unimi
IDAteneo
Ateneo
3
Unipa
4
Unimi
INNER JOIN
Select A.ID, A.nome, A.IDAteneo IDA, B.IDAteneo IDB, B.Ateneo
From A inner join B On A.idateneo = B.idateneo
• Il risultato sono tutte le coppie di righe, una da A e l’altra da B, tali
che idateneo sia uguale in entrambe.
A
B
ID
nome
IDAteneo
1
Lagalla
3
2
Valenti
3
Rossi
IDAteneo
Ateneo
3
Unipa
4
Unimi
ID
nome
IDA
IDB
Ateneo
3
1
Lagalla
3
3
Unipa
4
2
Valenti
3
3
Unipa
3
Rossi
4
4
Unimi
LEFT JOIN
Select A.ID, A.nome, A.IDAteneo, B.IDAteneo, B.Ateneo
From A left join B On A.idateneo = B.idateneo
• Il risultato sono tutte le coppie di righe, una da A e l’altra da B, tali
che idateneo sia uguale in entrambe. Se non esiste la riga B
corrispondente, questa è sostituita da NULL
A
B
ID
nome
IDAteneo
1
Lagalla
3
2
Valenti
3
3
Rossi
4
4
Bianchi
1
IDAteneo
Ateneo
3
Unipa
4
Unimi
ID
nome
IDA
IDB
Ateneo
1
Lagalla
3
3
Unipa
2
Valenti
3
3
Unipa
3
Rossi
4
4
Unimi
4
Bianchi
1
NULL
NULL
RIGHT JOIN
Select A.ID, A.nome, A.IDAteneo, B.IDAteneo, B.Ateneo
From A right join B On A.idateneo = B.idateneo
• Il risultato sono tutte le coppie di righe, una da A e l’altra da B, tali
che idateneo sia uguale in entrambe. Se non esiste la riga A
corrispondente, questa è sostituita da NULL
A
B
ID
nome
IDAteneo
1
Lagalla
3
2
Valenti
3
ID
nome
IDA
IDB
Ateneo
3
Rossi
4
1
Lagalla
3
3
Unipa
4
Bianchi
1
2
Valenti
3
3
Unipa
3
Rossi
4
4
Unimi
NULL
NULL
NULL
5
Unict
IDAteneo
Ateneo
3
Unipa
4
Unimi
5
Unict
Group by - Having
• La clausola group by permette di raggruppare
le righe secondo valori uguali di colonne
specificate
• Select count(*), idanagrafica from costiorari
Group by idAnagrafica;
• Select max(costo), idanagrafica from costiorari
Group by idAnagrafica;
• Select max(costo) as mcosto, idanagrafica from
costiorari Group by idAnagrafica Having mcosto
<= 20 ;
Campi tipo Datetime
Formati che indicano una Data.
• 'YYYY-MM-DD HH:MM:SS' o 'YY-MM-DD HH:MM:SS‘
• 'YYYYMMDDHHMMSS' o ‘YYMMDDHHMMSS'
• 'YYYY-MM-DD' o 'YY-MM-DD‘
• 'YYYYMMDD' o 'YYMMDD'
• Come separatore si può utilizzare qualsiasi carattere
‘2010@12@01’ Indica il 1 dicembre 2010
Datetime: Funzioni
•
•
•
•
•
•
•
Now()
Data ed ora corrente
Current_Date() Data senza orario
Curtime()
Ora corrente (TIME)
DATE_ADD( <data>, INTERVAL <n> unit )
DATE_ADD( <data>, <ndays> )
DATE_SUB( <data>, INTERVAL <n> unit )
DATE_SUB( <data>, <ndays> )
DATE_ADD( ‘20111201’, INTERVAL 2 DAY )
Cosa restituisce?
DateTime: unit
•
•
•
•
•
•
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
Campi Binary Large Object: BLOB
I campi di tipo BLOB servono per memorizzare grandi quantità di dati binari
provenienti da file.
Esistono tipi di campi BLOB: BLOB, MEDIUMBLOB e LARGEBLOB
•TINYBLOB può contenere massimo 255 byte. E’ utilizzato 1 byte per indicare
la dimensione del dato memorizzato;
•BLOB può contenere massimo 65.535 byte (65Kb). Sono usati 2 byte per
indicare la dimensione del dato memorizzato;
•MEDIUMBLOB può contenere massimo 16.777.215 byte (16 Mb). Sono usati
3 byte per la dimensione del dato;
•LARGEBLOG può contenere fino a 4.294.967.295 byte (4 Gb). Usati 4 byte.
I campi Blob occupano L + <numero byte utilizzati per la lunghezza>
Campi Binary Large Object: BLOB
•Per utilizzare i campi BLOB, occorre accedere al filesystem del Server
•L’Utente deve avere il privilegio FILE per leggere/scrivere sul
filesystem
•GRANT FILE on *.* TO ‘<utente>’@’<server>’;
•GRANT FILE on *.* TO ‘utente99’@’%’; (esempio )
Esempio per MySql Server su Windows
•Update t Set b=LOAD_FILE(“C:/immagine.jpg”) Where idT=7;
•Select b From t Where idT=7 INTO DUMPFILE “c:/immagine2.jpg”;
•Insert into t (b) values ( LOAD_FILE(“C:/immagine.jpg”) );
Campi Binary Large Object: BLOB
Esempio per Linux/Unix
•Update t Set b=LOAD_FILE(“/tmp/mysql/prova.pdf”) Where idT=7;
•Select b From t Where idT=7 INTO DUMPFILE “/tmp/mysql/prova2.jpg”;
•Insert into t (b) values ( LOAD_FILE(“/tmp/mysql/prova3.jpg”) );
NOTA
•La Select non sovrascrive eventuali file esistenti. Ciò è fatto per motivi di
sicurezza per evitare sovrascritture di file di sistema;
•Il Path del file deve essere ASSOLUTO sia per Windows che per Linux;
•Su linux la LOAD_FILE vuole le doppie virgolette obbligatoriamente;
SubQuery
• Una subquery è una select contenuta dentro un’altra
istruzione
• Select * from costiorari Where costo = (select min(costo)
from costiorari )
• Cosa fa questa istruzione?
Subqueries with EXISTS or NOT EXISTS
• Select * From costiorari Where Exists (Select *
From anagrafica);
Esempio realistico: Subquery correlate.
• Select * From anagrafica Where EXISTS (Select * From progetti
Where progetti.idResponsabileScientifico = anagrafica.idanagrafica
)
• Quesito.
• Trovare i docenti con il compenso massimo e minimo
Subquery su clausola FROM
• La subquery può ritornare un insieme di righe, quindi
una tabella che può essere utilizzata in una istruzione
Select.
• Select * from ( Select nome, cognome from Anagrafica
Where nome like ‘al%’ ) as tab Where tab.cognome like
‘bi%’;
• Questa istruzione nella subquery estrae tutte le righe
con nome che inizia per AL. La query esterna filtra da
queste righe solo quelle che hanno in cognome che iniza
per BI
Prepared Statement
• Sono istruzioni SQL memorizzate nel Server, che possono accettare
parametri ed eseguite su richiesta
• Sono compilare, quindi sintatticamente corrette
• Prendono variabili come parametri
Creazione
Prepare stmt1 From ‘Select * from progetti Where idProgetto=?’
Esecuzione:
Set @variabile = ‘1’;
Execute stmt1 Using @variabile;
Eliminazione:
Deallocate Prepare stmt1;
Select Into variabili
• Esiste la possibilità di interrogare le tabelle e mettere il risultato
dentro dell variabile. Il risultato deve essere uno scalare.
• Creazione Variabili.
Set @minimo=0, @massimo=0;
• Inizializzazione
Select min(costo), max(costo) Into @minimo, @massimo From
costiorari;
• Stampiamo il risultato
Select @minimo, @massimo;
Query dinamiche
• Set @c='idProgetto, Nome';
• Set @t ='Progetti’;
• Set @sql = concat('Select ', @c, ' From ' ,
@t);
• Prepare sqldinamica From @sql;
• Execute sqldinamica;
• Deallocate Prepare sqldinamica;
Creare Funzioni
• Sintassi
DELIMITER $$
Create Function <nome funzione> ( parametri )
RETURNS <tipo Parametro>
DETERMINISTIC
BEGIN
DECLARE <variabili locali>;
<corpo della funzione>
RETURN <valore>;
END$$
DELIMITER ;
Esempio di funzione
• Funzione che raddoppia i valori di ingresso
DELIMITER $$
Create Function Raddoppia ( v INT )
RETURNS INT
DETERMINISTIC
BEGIN
RETURN v+v;
END$$
DELIMITER ;
Esecuzione della funzione
• Esempi di chiamata di funzione
• Select Raddoppia(10);
• Select Raddoppia(10+10);
• Select Raddoppia(10 * 3);
• Non supporta le subquery
Errore…
select raddoppia ( select max(idprogetto) from
progetti );
Stored Procedure
• Sintassi
DELIMITER $$
CREATE PROCEDURE <nome> ( <parametri> )
BEGIN
<comandi>
END$$
DELIMITER ;
Esempio di Stored Procedure
DELIMITER $$
CREATE PROCEDURE ProcTest (IN filtro varchar(64) )
BEGIN
Select * from progetti inner join timesheets on
progetti.idprogetto=timesheets.idprogetto
Where nome = filtro;
END$$
DELIMITER ;
Esecuzione della Stored Procedure
• Il comando Call permette di invocare una
stored procedure.
Call ProcTest(‘ProgettoA’);
Questo camando chiama la Stored
Procedure ProcTest passando il
parametro ‘ProgettoA’
Esempio di Stored Procedure e
parametri locali
DELIMITER $$
CREATE PROCEDURE ProcTest2 ()
BEGIN
Declare massimo int;
Declare minimo int;
Select max(idanagrafica) , min(idanagrafica) into massimo, minimo
From anagrafica;
select minimo, massimo;
END$$
DELIMITER ;
CURSORI
•
•
•
•
Declare <nome> CURSOR FOR Select….
Open <nome>
Fetch <nome> INTO <variabili>
Close <nome>
• LA FETCH dentro un LOOP
• Occorre un HANDLER per la gestione
della fine dell’elenco dei record
Esempio 1/2
• DELIMITER $$
• CREATE PROCEDURE `cursore`()
• BEGIN
• Declare cof DECIMAL(9,2) DEFAULT 0.0;
• Declare progetto varchar(32);
• Declare NonTrovato int DEFAULT 0;
• Declare Somma Decimal(11,2) DEFAULT 0.0;
• Declare curr CURSOR FOR select cofinanziamento, nome from
progetti;
• Declare CONTINUE HANDLER FOR NOT FOUND SET
NonTrovato = 1;
Esempio 2/2
Open curr;
read_loop: LOOP
Fetch curr INTO cof, progetto;
IF NonTrovato THEN
LEAVE read_loop;
END IF;
Set somma = somma + cof;
END LOOP;
Close curr;
Select somma;
END$$
DELIMITER;
Il comando IF
• IF <condizione> THEN
<istruzioni>
• [ELSEIF <condizione1> THEN
<istruzioni2> ...
• [ELSE <istruzioni3>]
• END IF
I LOOP 1/2
• [begin_label:] LOOP
statement_list
END LOOP
• LEAVE label
• ITERATE label
I LOOP 2/2
• La Repeat Esce dal ciclo appena la condicione è VERA
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT
• La WHILE esce dal ciclo se la condizione è FALSA
[begin_label:] WHILE search_condition DO
statement_list
END WHILE