SEFOC – SETTORE FORMAZIONE CONTINUA DEL PERSONALE

1. SEFOC – SETTORE FORMAZIONE CONTINUA DEL PERSONALE CORSO MYSQL AVANZATO ROBERTO PENNOLINO – CARMELO MASSIMO PRIOLO PALERMO – 07 | 06 | 2011

2. 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, truncatetable • 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

3. 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

4. Struttura del DB di esempio

5. SELECT 1/n Sintassi • SELECT <s_espressione> • FROM <tabelle> • WHERE <condizioni> • GROUPBY <g_espressione> • HAVING • ORDER BY <colonne> • LIMIT <n>

6. SELECT 2/n • Select * from progetti Visualizza tutte le righe e tutte le colonne della tabella progetti • Select 1 +1Esempio 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”

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

8. 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 )

9. 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

10. 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;

11. 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’

12. 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

13. 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}

14. 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]’

15. 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

16. 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

17. 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

18. 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

19. Group by - Having • La clausola group by permette di raggruppare le righe secondo valori uguali di colonne specificate • Select count(*), idanagrafica from costiorari Groupby idAnagrafica; • Select max(costo), idanagrafica from costiorari Groupby idAnagrafica; • Select max(costo) as mcosto, idanagrafica from costiorari Groupby idAnagrafica Having mcosto <= 20 ;

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

21. 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?

22. DateTime: unit • SECOND • MINUTE • HOUR • DAY • WEEK • MONTH

23. 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>

24. 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”; • Insertinto t (b) values ( LOAD_FILE(“C:/immagine.jpg”) );

25. 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;

26. SubQuery • Una subquery è una select contenuta dentro un’altra istruzione • Select * from costiorari Where costo = (select min(costo) from costiorari ) • Cosa fa questa istruzione?

27. 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

28. 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

29. 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;

30. 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;

31. Query dinamiche • Set @c='idProgetto, Nome'; • Set @t ='Progetti’; • Set @sql = concat('Select ', @c, ' From ' , @t); • Prepare sqldinamica From @sql; • Execute sqldinamica; • DeallocatePrepare sqldinamica;

32. Creare Funzioni • Sintassi DELIMITER $$ CreateFunction <nome funzione> ( parametri ) RETURNS <tipo Parametro> DETERMINISTIC BEGIN DECLARE <variabili locali>; <corpo della funzione> RETURN <valore>; END$$ DELIMITER ;

33. Esempio di funzione • Funzione che raddoppia i valori di ingresso DELIMITER $$ CreateFunction Raddoppia ( v INT ) RETURNS INT DETERMINISTIC BEGIN RETURN v+v; END$$ DELIMITER ;

34. 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 );

35. Stored Procedure • Sintassi DELIMITER $$ CREATE PROCEDURE <nome> ( <parametri> ) BEGIN <comandi> END$$ DELIMITER ;

36. 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 ;

37. 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’

38. 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 ;

39. 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

40. 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 CURSORFOR select cofinanziamento, nome from progetti; • Declare CONTINUE HANDLER FOR NOT FOUND SET NonTrovato = 1;

41. 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;

42. Il comando IF • IF <condizione> THEN <istruzioni> • [ELSEIF <condizione1> THEN <istruzioni2> ... • [ELSE <istruzioni3>] • END IF

43. I LOOP 1/2 • [begin_label:] LOOP statement_list END LOOP • LEAVE label • ITERATE label

44. 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