SEFOC – SETTORE FORMAZIONE CONTINUA DEL PERSONALE

CORSO MYSQL AVANZATO

ROBERTO PENNOLINO – CARMELO MASSIMO PRIOLO

PALERMO – 07 | 06 | 2011

CORSO MYSQL AVANZATOROBERTO PENNOLINO

PALERMO07 | 06 | 2011

Programma del corso

• Differenti tipi di tabelle in MySQL (Roberto Pennolino)• MyISAM, InnoDB

• 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)

• 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)

Documentazione

MySQL Server:IP: 147.163.15.254Utente: 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;

WHERELa 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• ^ 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• abc| cde sequenza ‘abc’ o ‘cde’• (abc)* 0 o più sequenze di ‘abc’• {n} esattamente n occorrenze• {n,m} occorrenze in numero comprese tra n ed m. m opzionale• [a-z] range di caratteri tra a e z• [^a-z] carattere non appartenente a “a,b,c,…,z”• \\. Per introdurre un carattere speciale. Esempio \\+ \\*

c* equivale c{0,} ‘1\\+2’ Equivale alla stringa ‘1+2’c+ equivale c{1,} c? equivale c{0,1}

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 CartesianoA 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

ID nome IDAteneo

1 Lagalla 3

2 Valenti 3

3 Rossi 4

IDAteneo Ateneo

3 Unipa

4 Unimi

ID nome IDA IDB Ateneo

1 Lagalla 3 3 Unipa

1 Lagalla 3 4 Unimi

2 Valenti 3 3 Unipa

2 Valenti 3 4 Unimi

3 Rossi 4 3 Unipa

3 Rossi 4 4 Unimi

A

B

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.

ID nome IDAteneo

1 Lagalla 3

2 Valenti 3

3 Rossi 4

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

A

B

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

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

A

B

RIGHT JOINSelect 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

ID nome IDAteneo

1 Lagalla 3

2 Valenti 3

3 Rossi 4

4 Bianchi 1

IDAteneo Ateneo

3 Unipa

4 Unimi

5 Unict

ID nome IDA IDB Ateneo

1 Lagalla 3 3 Unipa

2 Valenti 3 3 Unipa

3 Rossi 4 4 Unimi

NULL NULL NULL 5 Unict

A

B

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

CreazionePrepare 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

• SintassiDELIMITER $$Create Function <nome funzione> ( parametri )RETURNS <tipo Parametro>DETERMINISTICBEGIN

DECLARE <variabili locali>;<corpo della funzione>RETURN <valore>;

END$$DELIMITER ;

Esempio di funzione

• Funzione che raddoppia i valori di ingressoDELIMITER $$Create Function Raddoppia ( v INT )RETURNS INTDETERMINISTICBEGIN

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 subqueryErrore…select raddoppia ( select max(idprogetto) from

progetti );

Stored Procedure

• SintassiDELIMITER $$

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, minimoFrom 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:] REPEATstatement_listUNTIL search_conditionEND REPEAT

• La WHILE esce dal ciclo se la condizione è FALSA

[begin_label:] WHILE search_condition DOstatement_listEND WHILE