MySQL - Università degli studi di Bergamo

Elaborazione Automatica dei Dati
MySQL
Enrico Cavalli
Anno Accademico 2012-2013
MySQL(1)
•
MySQL è un DBMS Open Source prodotto da MySQL AB acquistato
ora da Oracle. http://www.mysql.com
•
MySQL è realizzato in architettura Client/Server
Client
Server
MySQL
MySQL
Il client dialoga con
l’utente, ne accetta i
comandi che invia al
server per l’esecuzione e
visualizza i risultati
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
DB
Il server è responsabile
della gestione dei dati.
Interagisce con il DB e
invia i risultati al client
che li visualizza
2
MySQL(2)
Esistono diverse versioni di MySQL:
• 5.5. è, al momento, la versione in uso
Versione 5: caratteristiche aggiuntive rispetto alla versione 4
– Viste Logiche, Trigger, Stored Procedure, Stored Function
•
La scelta dei progettisti di MySQL è stata di ottimizzarne la velocità anche rinunciando
ad alcune caratteristiche possedute da altri SQL
•
Il server supporta diversi client sia da linea comandi che grafici, per esempio:
– MySQL Workbench, MySQL Query Browser, MySQL Administrator
– phpMyAdmin
– MySQL-Front
Manuali e documentazione:
http://dev.mysql.com/doc/refman/5.5/en/index.html
http://dev.mysql.com/doc/refman/5.5/en/tutorial.html
http://dev.mysql.com/doc/refman/5.5/en/sql-syntax.html
http://dev.mysql.com/doc/refman/5.5/en/functions.html
http://php.html.it/guide/
fra le guide scegliere phpMyAdminb
3
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
MySQL(3)
•
Esecuzione di MySQL dalla linea comandi:
C:\>mysql –u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.5.10-log ..
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
mysql> HELP
For the complete MySQL Manual online, visit:
http://www.mysql.com/documentation
. . .
For server side help, type 'help contents‘
mysql>
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
...
l’elenco dei comandi
lato client è riportato
nella prossima
diapositiva
4
MySQL(4)
•
Comandi MySQL lato client
?
clear
connect
delimiter
ego
exit
go
help
notee
print
prompt
quit
rehash
source
status
tee
use
(\?)
(\c)
(\r)
(\d)
(\G)
(\q)
(\g)
(\h)
(\t)
(\p)
(\R)
(\q)
(\#)
(\.)
(\s)
(\T)
(\u)
Synonym for `help'.
Clear command.
Reconnect to the server.
Set query delimiter.
Send command to the server, result shown vertically.
Exit mysql. Same as quit.
Send command to mysql server.
Display this help.
Don't write into outfile.
Print current command.
Change your mysql prompt.
Quit mysql.
Rebuild completion hash.
Execute a SQL script file. File name as argument.
Get status information from the server.
Set outfile. Append everything into given outfile.
Use another database. Database name as argument.
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
5
MySQL(5)
•
Guida in linea per i principali comandi:
mysql> HELP CONTENTS
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one
of the following categories:
Administration
Attraverso
Column Types
successive richieste si
Data Definition
identifica il comando per
Data Manipulation
il quale si vogliono
Functions
informazioni
Geographic features
Transactions
mysql> help date
Name: 'DATE'
Description:
A date. The supported range is '1000-01-01' to '9999-12-31'.
MySQL displays DATE values in 'YYYY-MM-DD' format, . . .
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
6
MySQL(6)
mysql> /*******************************************************
mysql> *
Questo è un commento su più righe
mysql> *******************************************************/
mysql>
mysql> -- Questo è un commento su una sola riga
mysql>
mysql> SELECT 3*4;
-- SELECT usato come Print
+-----+
| 3*4 |
+-----+
| 12 |
+-----+
mysql>
mysql>
mysql>
mysql>
SELECT
SELECT
SELECT
SELECT
VERSION();
CURDATE();
NOW();
USER();
mysql> SELECT (5+8)
-> *12
-> ;
-----
Versione di MySQL
Data attuale
Data e ora attuali
Utente connesso al server
-- Un comando può essere scritto su
-- più righe e deve terminare con
-- il carattere ;. Calcola: (5+8)*12
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
7
MySQL(7)
mysql> SHOW DATABASES;
+------------------------+
| Database
|
+------------------------+
| mysql
|
| test
|
+------------------------+
2 rows in set (0.00 sec)
-- Elenca i database sul server
mysql> USE TEST;
Database changed
-- Test diventa db di default
mysql> SHOW TABLES;
Empty set (0.00 sec)
mysql> QUIT;
Mysql e Test sono
sempre presenti
Test db per prove
-- Elenca tabelle/viste in Test
-- SHOW [FULL] TABLES;
In Test non ci
sono tabelle
-- oppure EXIT: fine sessione
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
8
Identificatori e Files fisici in MySQL
Identificatori in MySQL
Tipo di
identificatore
Lunghezzamax
Caratteri ammessi
Database
64
I caratteri ammessi dal S.O. per i nomi delle
directory ad eccezione di \, / e .
Tabella
64
I caratteri ammessi dal S.O. per i nomi dei
file ad eccezione di / e .
Colonna
64
Tutti
Indice
64
Tutti
Alias
255
Tutti
Per ogni database MySQL crea una directory con quel nome.
Ogni tabella del db è rappresentata con un insieme di file che hanno per nome il nome
della tabella e estensione: MYD (per i dati), MYI (per gli indici), FRM (per la struttura).
Le viste logiche sono rappresentate con un solo file di estensione FRM. I trigger danno
origine a un solo file con estensione TRG. Procedure e funzioni sono salvate nella
tabella Proc del database Mysql.
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
10
Tabelle, Viste, Routine, Trigger (1)
Nel database StudFac ci sono le due tabelle Facolta e Studenti (di
tipo MyIsam), le viste logiche Disponibilita, .. , VistaDiProva e un
certo numero di stored routine: FunQB, .. , e un trigger.
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
11
Tabelle, Viste, Routine, Trigger (2)
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
12
Tabelle, Viste, Routine, Trigger (3)
Nel database CollezioneMusicale
ci sono tabelle di tipo InnoDB, oltre
a viste procedure e trigger. Dati e
indici di tabelle InnoDB sono salvati
nel (default) file: Ibdata1 di:
C:\ . . . \MySQL\Data
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
13
Tabelle, Viste, Routine, Trigger (4)
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
14
Tabelle, Viste, Routine, Trigger (5)
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Tipi di dati di MySQL
15
Interi in MySQL (1)
Tipi interi
TINYINT(M)
Limiti
-128
+127
Note
Interi con 1 byte
(0..255) se UNSIGNED
Sinonimo di TINYINT. 0 Falso. Ogni altro
valore Vero
BOOLEAN
SMALLINT(M)
-31768
+32767
Interi con 2 byte
(0..66535) se UNSIGNED
INT(M)
INTEGER(M)
-231
+(231-1)
Interi con 4 byte
(0.. 232-1) se UNSIGNED
BIGINT(M)
-263 +(263-1)
Interi con 8 byte
(0.. 264-1) se UNSIGNED
C’è anche un tipo MEDIUMINT che usa 3 byte e quindi . . .
I valori logici True e False possono essere manipolati algebricamente.
MySQL dispone di un’ampia libreria di funzioni: DIV, MOD, ABS, POW,
SQRT, ...
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
17
Interi in MySQL (2)
mysql> SELECT TRUE, FALSE, TRUE*2.5, FALSE-2;
+------+-------+----------+---------+
| TRUE | FALSE | TRUE*2.5 | FALSE-2 |
+------+-------+----------+---------+
|
1 |
0 | 2.5
|
-2 |
+------+-------+----------+---------+
mysql> SELECT 5/2, 5 div 2, 5 % 2;
+------+---------+-------+
| 5/2 | 5 div 2 | 5 % 2 |
+------+---------+-------+
| 2.50 |
2 |
1 |
+------+---------+-------+
mysql> SELECT Floor(2.6),Ceiling(2.6),Round(2.6),Truncate(2.6,0);
+------------+--------------+------------+-----------------+
| Floor(2.6) | Ceiling(2.6) | Round(2.6) | Truncate(2.6,0) |
+------------+--------------+------------+-----------------+
|
2 |
3 |
3 |
2 |
+------------+--------------+------------+-----------------+
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
18
Reali in MySQL
Tipi reali
Limiti in valore
assoluto
Note
FLOAT(M,D)
1.175494351E-38 ..
3.402823466E+38
Rappresentazione in virgola mobile con 4
byte. Permette di rappresentare i valori
reali con precisione massima di 7 cifre
decimali (M <=7)
1.7976931348623157E+308
.. 2.2250738585072014E-308
Rappresentazione in virgola mobile con 8
byte. Permette di rappresentare i valori
reali con la precisione massima di 15
cifre decimali (M <=15)
DOUBLE
PRECISION(M,D)
DECIMAL(M,D)
NUMERIC(M,D)
Dipende da M e D
DECIMAL
DECIMAL(M)
Rappresentazione in virgola fissa con M
cifre decimali di cui D per la parte
frazionaria. Rappresentazione esatta.
Memorizza i dati occupando M+2 byte
Equivale a DECIMAL(10,0)
Equivale a DECIMAL(M,0)
Con le seguenti limitazioni: D <= M-2, D <= 30. Nel caso delle
rappresentazioni in virgola fissa deve valere anche M <= 64
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
19
Date in MySQL(1)
Tipi data/ora
Limiti della
rappresentazione
Note
DATE
‘1000-01-01’.. ’9999-12-31’
Data nella forma ‘aaaa-mm-gg’
memorizzata in 3 byte
TIME
‘-838:59:59’ .. ‘838:59:59’
Ora nella forma ‘hh:mm:ss’
memorizzata in 3 byte
DATETIME
‘1000-01-01 00:00:00’
‘9999-12-31 23:59:59’
Data ed ora memorizzati in 8 byte
YEAR
YEAR(2)
YEAR(4)
1901 .. 2155
70 .. 69 (1970 .. 2069)
1901 .. 2155
Anno memorizzato in 1 byte.
Rappresentabile come valore numerico o
come stringa.
TIMESTAMP
‘1970-01-01 00:00:00’
sino al 2037
I campi di tipo TIMESTAMP sono utili per
memorizzare data ed ora
dell’inserimento o dell’aggiornamento di
un record. Memorizzato in 4 byte
Rappresentazioni equivalenti a: '2006-12-25 14:05:25'
'2006/12/25 14;05;25‘
'20061225140525‘
'061225140525‘
Come valore numerico: 20061225140525
061225140525
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
20
Date in MySQL(2)
mysql> SELECT CURDATE();
mysql> SELECT CURDATE() + 0;
‘2006-12-15'
20061215
mysql> SELECT CURTIME();
mysql> SELECT CURTIME() + 0;
'23:50:26'
235026
mysql> SELECT Dayofyear('2006-11-21');
325
mysql> SELECT Day('2006-11-21') as Giorno,
->
Monthname('2006-11-21') as Mese,
->
Year('2006-11-21') as Anno;
mysql> SELECT LAST_DAY('2003-02-05');
'2003-02-28'
mysql> SELECT LAST_DAY('2004-02-05');
'2004-02-29'
mysql> SELECT LAST_DAY('2004-01-01 01:01:01'); '2004-01-31'
SELECT Datediff('2006-01-04', '2005-11-21');
44
SELECT Date_Add('2005-10-05', Interval 3 month); 2006-01-05
21
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Date in MySQL(3)
'2001-02-01'
'2001-12-31'
'12:15:30'
mysql> SELECT MAKEDATE(2001,32);
mysql> SELECT MAKEDATE(2001,365);
mysql> SELECT MAKETIME(12,15,30);
mysql> SELECT NOW();
mysql> SELECT NOW() + 0;
mysql>
mysql>
mysql>
mysql>
SELECT
SELECT
SELECT
SELECT
‘2006-12-15 23:50:26'
20061215235026
DAY(NOW()), MONTH(NOW()),YEAR(NOW());
HOUR(NOW()), MINUTE(NOW()),SECOND(NOW());
DAYNAME(NOW());
NOW(), DATE(NOW()), TIME(NOW()), WEEK(NOW());
-Confronto di date
mysql> SELECT TRUE,FALSE,'12-17'>'12-05','10-05'>'11-21';
+------+-------+-----------------+-----------------+
| TRUE | FALSE | '12-17'>'12-05' | '10-05'>'11-21' |
+------+-------+-----------------+-----------------+
|
1 |
0 |
1 |
0 |
+------+-------+-----------------+-----------------+
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
22
Date in MySQL(4)
Altre funzioni utili:
DATE_FORMAT(data,formato)
vedi manuale
FROM_DAYS(intero)
TO_DAYS(data);
restituisce una data
restituisce un intero
intero rappresenta il numero di giorni trascorsi a
partire dall’anno 0 (ma significativo solo a partire
dal 1582, avvento del calendario gregoriano)
mysql> SELECT TO_DAYS('1582-01-01'), 1582*365+1582/4;
+-----------------------+-----------------+
| TO_DAYS('1582-01-01') | 1582*365+1582/4 |
+-----------------------+-----------------+
|
577814 | 577825.50000
|
+-----------------------+-----------------+
1 row in set (0.00 sec)
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
23
Stringhe in MySQL(1)
Tipo Stringa
CHAR ( M )
CHARATCTER ( M )
VARCHAR ( M )
BINARY ( M )
VARBINARY ( M )
Limiti
M = 0 .. 255
M = 0 .. 65535
M = 0 .. 255 ver. 4
M = 0 .. 255
M = 0 .. 65535
TEXT
Fino a 65535 caratteri
BLOB
Fino a 65535 byte
ENUM ( ‘V1’, .. ,’ Vn’ )
SET ( ‘V1’, .. ,’ Vn’ )
Fino a 65535 valori
Fino a 64 valori
Note
Stringhe a lunghezza fissa, non case sensitive,
riempite con spazi. M byte
Stringhe a lunghezza variabile, non case sensitive
Occupano M + 1 byte
Stringhe di tipo CHAR(M), VARCHAR(M) case sensitive
Coincide con VARCHAR ( 65535 )
Occupa M+2 Byte
E’ un TEXT case sensitive
Un dato di tipo ENUM assume un solo valore
tra quelli elencati; occupa fino a 2 bytes
Un dato di tipo SET è un qualsiasi insieme
di valori tra quelli elencati; occupa fino a 8 bytes
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
24
Stringhe in MySQL(2)
\0
Il carattere vuoto (Ascii 0)
\'
L’apice.
\“
Il doppio apice.
\b
Ritorna di un carattere (backspace)
\n
Nuova riga (linefeed)
\r
Ritorno del carrello
\t
Tabulazione (tab)
\Z
(Control Z)
\\
Il carattere ‘\’ (backslash)
\%
Il carattere percentuale
\_
Il carattere di sottolineatura.
Sequenze di ESCAPE
riconosciute da MySQL nelle
stringhe di caratteri per
rappresentare caratteri
speciali
25
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Stringhe in MySQL(3)
Il tipo ENUM ammette un insieme
di valori ammissibili massimo di
65535 valori
ENUM('one', 'two', 'three')
Valore
Indice
NULL
NULL
''
0
'one'
1
'two'
2
'three'
3
In assenza della dichiarazione NOT NULL, il valore NULL è
ammesso; se c’è la dichiarazione NOT NULL il valore di default
è 'one'.
Il tipo SET ammette insiemi formati
da 0 fino a 64 valori
Elemento
Valore
Binario
'a'
1
0001
'b'
2
0010
'c'
4
0100
'd'
8
1000
SET ('a', 'b', 'c', 'd')
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
26
Stringhe in MySQL(4)
mysql> CREATE TABLE TabellaDiInsiemi (
-> Colonna SET('a','b','c','d') );
mysql>
->
->
->
->
->
->
->
->
->
INSERT INTO TabellaDiInsiemi VALUES
('a,b,c'),
('b,c'),
mysql> SELECT Colonna, Colonna+0
('b,b,c'),
-> FROM TabellaDiInsiemi;
('a,d'),
+---------+-----------+
('d,a'),
| Colonna | Colonna+0 |
('d,d,a'),
+---------+-----------+
(''),
| a,b,c
|
7 |
(\N),
| b,c
|
6 |
('d,c');
| b,c
|
6 |
| a,d
|
9 |
| a,d
|
9 |
| a,d
|
9 |
|
|
0 |
| NULL
|
NULL |
| c,d
|
12 |
+---------+-----------+
27
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Stringhe in MySQL(5)
mysql> SELECT ASCII('A');
mysql> SELECT CHAR(65,65,77,77.3,'76');
65
'AAMML'
mysql> SELECT CONCAT('Pippo', ' ', 'Pluto'); 'Pippo Pluto'
mysql> SELECT CONCAT_WS(‘-', ‘AA‘,‘BB‘);
‘AA-BB'
mysql>
mysql>
mysql>
mysql>
mysql>
SELECT
SELECT
SELECT
SELECT
SELECT
LEFT(‘stringa di car’,3)
RIGHT(‘stringa di car’,5)
MID(‘Stringa di car’,5,3);
SUBSTRING(‘Stringa di’,2,3);
REVERSE(‘Stringa’);
‘str’
‘i car’
‘nga’
‘tri’
‘agnirtS’
mysql>
mysql>
mysql>
mysql>
SELECT
SELECT
SELECT
SELECT
LOWER(‘Enrico’);
UPPER(‘Cavalli’);
TRIM(‘
Enrico
REPEAT(‘W’,3);
‘enrico’
‘CAVALLI’
‘Enrico’
‘WWW’
’);
mysql> SELECT LENGTH(‘Stringa di’);
mysql> SELECT LOCATE(‘a’, ‘la mamma’, 3);
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
10
5
28
Esempio con stringhe e date (1)
Erica è nata il 21 novembre 1982. Quanti anni aveva il
12 ottobre 2000?
-- ------------------------------------------------------ Prima soluzione approssimata, spesso imprecisa
-- ----------------------------------------------------SELECT Year('2000-10-12') - Year('1982-11-21');
-- ------------------------------------------------------ Espressione che considera il mese e il giorno
-- restituisce 1 prima del 21 novembre e
-- restituisce 0 dopo il 21 novembre
-- ----------------------------------------------------mysql> SELECT Right('2000-10-12',5) < Right('1982-11-21',5);
+-----------------------------------------------+
| Right('2000-10-12',5) < Right('1982-11-21',5) |
+-----------------------------------------------+
|
1 |
+-----------------------------------------------+
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
29
Esempio con stringhe e date (2)
-- ------------------------------------------------------ Soluzione esatta
-- ----------------------------------------------------mysql> SELECT Year('2000-10-12') - Year('1982-11-21') -> ( Right('2000-10-12',5) < Right('1982-11-21',5) )
-> AS ‘Età di Erica’;
+--------------+
| Età di Erica |
+--------------+
|
17 |
+--------------+
-- ------------------------------------------------------ Età di Erica oggi
-- ----------------------------------------------------mysql> SELECT Year(Curdate()) - Year('1982-11-21') ->
( Right(Curdate(),5) < Right('1982-11-21',5) )
->
AS ‘Età di Erica’;
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
30
Esercizi (1)
Si consideri il database dei conoscenti:
Rubrica(Nome,Cognome,Nascita,Indirizzo,Telefono)
1.
2.
3.
4.
5.
6.
Elencare i conoscenti che compiono gli anni il prossimo mese
Elencare i conoscenti che compiono gli anni la prossima settimana
Elencare i conoscenti che compiono gli anni nei prossimi tre giorni
Elencare i conoscenti maggiorenni
Elencare i bambini con meno di 10 anni
Individuare i conoscenti che, nell’anno corrente, compiono gli anni di domenica
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
31
Esercizi (2)
7.
Elencare i conoscenti che non hanno ancora compiuto gli anni e il numero di giorni che
mancano al rispettivo genetliaco
8. Elencare i conoscenti con Nome, Cognome, Prefisso, NumTelefono
9. Elencare i conoscenti con NomeCompleto e indirizzo
10. Elencare i conoscenti con NomeCompleto, ViaNumero, Città
11. Elencare i conoscenti che risiedono a Bergamo
12. Elencare i conoscenti che risiedono in provincia di Torino
13. Elencare i conoscenti con CAP del tipo 203XX
14. Elencare i conoscenti con le informazioni su ViaNumero, CodicePostale, Città,
Provincia in colonne separate
15. Preparare un elenco con le informazioni necessarie per le spedizioni (NomeCompleto,
ViaNumero, CodicePostale, Citta)
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
32
Esercizi (3)
•
Esercizio di discreta utilità pratica è quello di costruire funzioni per visualizzare
le date con formati italiani:
•
•
•
•
Giorno ( Data )
Mese (Data)
ItalData ( Data )
ItalData (Data, Par)
Nome giorno in italiano
Nome mese in italiano
Lunedì, 4 ottobre 1492
Lunedì, 4 ottobre 1492
4 ottobre 1492
4 - 10 - 1492
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
33
SQL come DDL in MySQL
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
34
Creazione di un database con MySQL (1)
Database per gestire una collezione di dischi e cassette musicali
Artista
Brano
1
IDArtista
Nome
Genere
Note
{PK}
N
Eseguire
Registrazione
N
IDBrano {PK}
TitoloBrano
Genere
DurataBrano
Dimensione
Posizione
Osservazioni
1
Contenere
IDRegistrazione {PK}
TitoloAlbum
EtichettaAlbum
TipoAlbum
DurataMassima
Capacita
DataAggiornamento
Note
Artisti ( IDArtista, Nome, Genere, Note )
Registrazioni ( IDRegistrazione, TitoloAlbum, EtichettaAlbum, TipoAlbum,
DurataMassima, Capacità, DataAggiornamento, Note )
Brani ( IDBrano, TitoloBrano, Genere, DurataBrano, Dimensione, Posizione,
Osservazioni, IDArtista, IDRegistrazione )
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
35
Creazione di un database con MySQL (2)
mysql> CREATE DATABASE CollezioneMusicale;
mysql> USE CollezioneMusicale;
mysql> CREATE TABLE Artisti (
-> IDArtista INT UNSIGNED NOT NULL AUTO_INCREMENT
->
PRIMARY KEY,
-> Nome
VARCHAR(50) NOT NULL,
-> Genere
VARCHAR(10),
-> Noter
VARCHAR(255) );
mysql> CREATE TABLE Registrazioni (
-> IDRegistrazione
INT UNSIGNED NOT NULL
AUTO_INCREMENT,
-> TitoloAlbum
VARCHAR(50) NOT NULL,
-> EtichettaAlbum
VARCHAR(20),
-> TipoAlbum
VARCHAR(10),
-> DurataMassima
INT UNSIGNED DEFAULT 1,
-> Capacita
INT UNSIGNED DEFAULT 1,
-> DataAggiornamento DATE,
-> Note
VARCHAR(255),
-> PRIMARY KEY (IDRegistrazione) );
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
36
Creazione di un database con MySQL (3)
mysql> CREATE TABLE Brani (
-> IDBrano
INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> TitoloBrano
VARCHAR(50) NOT NULL,
-> Genere
VARCHAR(10),
-> DurataBrano
FLOAT(5,2) UNSIGNED DEFAULT 4,
-> Dimensione
INT UNSIGNED,
-> Posizione
SMALLINT UNSIGNED,
-> Osservazioni
VARCHAR(255),
AUTO_INCREMENT: intero
-> IDArtista
INT UNSIGNED,
progressivo automatico
-> IDRegistrazione INT UNSIGNED,
-> PRIMARY KEY (IDBrano)
-> );
PRIMARY KEY ( Col1, Col2 )
mysql> DESCRIBE Artisti;
+-----------+------------------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+-----------+------------------+------+-----+---------+----------------+
| IDArtista | int(10) unsigned |
| PRI | NULL
| auto_increment |
| Nome
| varchar(50)
|
|
|
|
|
| Genere
| varchar(10)
| YES |
| NULL
|
|
| Noter
| varchar(255)
| YES |
| NULL
|
|
+-----------+------------------+------+-----+---------+----------------+
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
37
Creazione di un database con MySQL (4)
mysql> CREATE TEMPORARY TABLE Tabella ( DefinizioneColonne );
mysql> DROP DATABASE NomeDataBase;
mysql> DROP TABLE NomeTabella;
mysql> ALTER TABLE NomeTabella
DROP NomeColonna
ADD NomeColonna DefizioneColonna
CHANGE VecchioNome NuovoNome Definizione;
-- --------------------------------------------------------- Cambiamento di nome alla colonna Noter
-- La colonna Genere passa da 10 a 20 caratteri
-- -------------------------------------------------------mysql> ALTER TABLE Artisti CHANGE Noter Note VARCHAR(255),
->
CHANGE Genere Genere VARCHAR(20);
-- --------------------------------------------------------- Definizione di indici
-- -------------------------------------------------------mysql> CREATE [ UNIQUE ] INDEX NomeIndice
ON NomeTabella ( Colonna [,Colonna] );
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
38
Creazione di un database con MySQL (5)
-- ------------------------------------------------------ Costruzione di un indice su Genere di Brani
-- ----------------------------------------------------mysql> CREATE INDEX ind_genere ON Brani (Genere);
mysql> DESCRIBE Brani Genere;
+--------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+ UNIQUE
| Genere | varchar(10) | YES | MUL | NULL
|
|indice con le
+--------+-------------+------+-----+---------+-------+
caratteristiche
di una chiave
-- ------------------------------------------------------ Indice senza duplicati su CodiceFiscale di Studenti
-- ----------------------------------------------------mysql> CREATE UNIQUE INDEX ind_codfis ON Studenti(CodiceFiscale);
mysql> DESCRIBE Studenti CodiceFiscale;
+---------------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| CodiceFiscale | varchar(16) |
| UNI |
|
|
+---------------+-------------+------+-----+---------+-------+
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
39
Creazione di un database con MySQL (6)
-- ------------------------------------------------------ Genere di Artisti campo di tipo ENUM
-- ----------------------------------------------------mysql> ALTER TABLE Artisti CHANGE Genere Genere
-> ENUM('Pop','Rock','Rap','Opera','Country','Jazz');
-- ------------------------------------------------------ INSERT per inserire dati in una tabella
-- ----------------------------------------------------mysql> INSERT INTO NomeTabella [ ElencoCampi ] VALUES
-> ( ElencoValori) [, .... , ( ElencoValori )];
-- ------------------------------------------------------ Inserimento dati nella tabella Artisti
-- ----------------------------------------------------I campi di tipo data e
mysql> INSERT INTO Artisti VALUES
le stringhe di caratteri
-> (null, 'Pierre Borg', 'Pop', ''),
vanno delimitati con
-> (null, 'Anatolia', 'Rock', '');
apici o doppi apici
-> (3,'The Bees', 'Rap', ''),
-> (\N,'Golden Eagles', 'Pop', '');
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
40
Creazione di un database con MySQL (7)
-- ------------------------------------------------------ Un artista di genere non previsto in ENUM
-- ----------------------------------------------------mysql> INSERT INTO Artisti (Nome, Genere, Note) VALUES
-> ('Nuovo Artista1', 'Dance','Artista genere Dance'),
-> ('Nuovo Artista2', 'pop','Artista pop');
Query OK, 2 rows affected, 1 warning (0.00 sec)
mysql> SELECT * FROM Artisti;
+-----------+----------------+---------+----------------------+
Al posto di Dance
| IDArtista | Nome
| Genere | Note
|
viene immessa la
+-----------+----------------+---------+----------------------+
stringa nulla.
|
1 | Pierre Borg
| Pop
|
Al posto di pop|
|
2 | Anatolia
| Rock
|
compare Pop|
|
3 | The Bees
| Rap
|
|
|
4 | Golden Eagles | Pop
|
|
|
. . . .
|
|
9 | Nuovo Artista1 |
| Artista genere Dance |
|
10 | Nuovo Artista2 | Pop
| Artista pop
|
+-----------+----------------+---------+----------------------+
41
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Creazione di un database con MySQL (8)
La definizione del database
e delle tabelle si effettua anche
MySQL Query Browser o con
PHPMyAdmin. Entrambi i
sistemi eseguono le operazioni
e visualizzano il codice SQL
eseguito
Nel libro: MySQL e database in rete
• MySQL Query Browser: da pagina 123
• PHPMyAdmin da pagina 203
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
42
Esercizi
Si consideri il database CollezioneMusicale. Dopo ogni esercizio
controllare l’effetto dei comandi eseguiti con il comando DESCRIBE.
1.
Creare il database CollezioneMusicale e le tabelle Artisti, Registrazioni e
Brani, definendo le chiavi primarie per le tre tabelle
2.
Modificare le precedenti definizioni per dichiarare che i campi Genere delle
tabelle Artisti e Brani sono di tipo Enum
3.
Creare indici sul campo Nome di Artisti, TitoloBrano di Brani e TitoloAlbum di
Registrazioni
4.
Rinominare il campo TitoloBrano di Brani in Titolo e TitoloAlbum di
Registrazioni in NomeAlbum
5.
Eliminare i campi Osservazioni e Note dalle tabelle Brani e, rispettivamente,
Registrazioni. Ricreare le colonne eliminate nelle due tabelle
6.
Usando il comando INSERT popolate ogni tabella con cinque righe
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
43
SQL come DML in MySQL
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
44
Manipolazione di tabelle (1)
•
INSERT: inserisce righe in una tabella
– INSERT INTO TabellaEsistente VALUES
( Valori ), . . , ( Valori );
– INSERT INTO TabellaEsistente SELECT . . .;
– CREATE TABLE Tabella SELECT . . .;
•
UPDATE: modifica uno o più campi in una tabella
– UPDATE Tabella SET
Colonna1 = Espressione1, ...
WHERE Condizione;
•
DELETE: elimina righe da una tabella
– DELETE FROM Tabella
WHERE Condizione;
45
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Manipolazione di tabelle (2)
+-----------+-----------------+---------+------------+
| IDArtista | Nome
| Genere | Note
|
+-----------+-----------------+---------+------------+
|
1 | Pierre Borg
| Pop
|
|
|
2 |
Anatolia
| Rock
|
|
|
3 |
The Bees
| Rap
|
|
|
4 | Golden Eagles
| Pop
|
|
|
.. | . . .
|
|
|
|
9 | Nuovo Artista 1 |
| Artista .. |
|
10 | Nuovo Artista 2 | Pop
| Artista .. |
+-----------+-----------------+---------+------------+
TRIM elimina gli spazi
iniziali e finali di una
stringa
mysql> UPDATE Artisti
-> SET Nome = TRIM(Nome);
Query OK, 2 row affected (0.40 sec)
Rows riconosciute: 6 Cambiate: 2 Warnings: 0
mysql> UPDATE Artisti
-> SET Genere = 'Country'
-> WHERE IDArtista = 7;
mysql> DELETE FROM Artisti
-> WHERE Note <> '';
mysql> DELETE FROM Artisti
-> WHERE IDArtista >= 9;
-- Comando equivalente
-- al precedente
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
46
Manipolazione di tabelle (3)
mysql> DELETE
-> FROM Artisti;
-- svuota la tabella Artisti
mysql> TRUNCATE Artisti;
-- equivalente al precedente
\N;"Oxford road";"Orange";"LP";48;1;2001-9-27;\N
\N;"Mixed songs: raccolta";\N;"Nastro";120;1;2005-10-15;\N
\N;"Don't hurt her";"Blue disc";"CD-ROM";70;1;2003-2-15;\N
\N;"Best of winter";\N;"CD-RW";1;665600;2005-12-18;\N
LOAD DATA INFILE
per caricare dati da
un file testo
-- ------------------------------------------------------------- Dati contenuti nel file: D:\PhpMySqlDati\Registrazioni.txt
-- Si notino: separatori, delimitatori, a capo
-le date senza delimitatori, i valori nulli
-- -----------------------------------------------------------mysql> LOAD DATA INFILE 'D:\\PhpMySqlDati\\Registrazioni.txt'
-> INTO TABLE Registrazioni
-> FIELDS TERMINATED BY';'
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
47
Manipolazione di tabelle (4)
•
LOAD DATA INFILE è utile per trasferire dati da un database ad un altro.
Esempi:
– Esportare dati da Access in un file di testo e poi importarli in MySQL con il
comando LOAD DATA INFILE
– Esportare dati da una tabella di un certa versione di MySQL e poi
importarla in un altro database con una differente versione di MySQL
– Trasferire dati tra tabelle MySQL in differenti installazioni
•
Per esportare dati da una tabella MySQL in un file di testo c’è un programma
di utilità di nome mysqldump ( Si veda il manuale in linea al paragrafo 8.13 )
8.13
mysqldump
A Database
Backup
Program
8.13
mysqldump
A Database
Backup
Program
===========================================
===========================================
TheThe
`mysqldump'
client
cancan
be be
used
to to
dump
a database
or or
a collection
`mysqldump'
client
used
dump
a database
a collection
of of
databases
forfor
backup
or or
forfor
transferring
thethe
data
to to
another
SQLSQL
databases
backup
transferring
data
another
server
(not
necessarily
a MySQL
server).
server
(not
necessarily
a MySQL
server).
Meglio usare MySQL Query Browser (vedi pagina 140) o PhpMyAdmin (vedi
pagina 218) per esportare i dati in un file di testo
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
48
Manipolazione di tabelle (5)
+-----------+---------------+---------+------+
| IDArtista | Nome
| Genere | Note |
+-----------+---------------+---------+------+
|
1 | Pierre Borg
| Pop
|
|
|
2 | Anatolia
| Rock
|
|
|
3 | The Bees
| Rap
|
|
|
4 | Golden Eagles | Pop
|
|
|
5 | Argolide
| Rock
|
|
|
6 | Heavy Stones | Opera
|
|
|
7 | Steve Martin | Country |
|
+-----------+---------------+---------+------+
ArtistiPreferiti
tabella con artisti di
genere Rock o Pop
mysql> CREATE TABLE ArtistiPreferiti
-> SELECT * FROM Artisti
-> WHERE Genere = 'Pop' or Genere = 'Rock';
-- ------------------------------------------------------------- ArtistiPreferiti con CREATE TABLE e INSERT INTO TABLE
-- -----------------------------------------------------------mysql> CREATE TABLE ArtistiPreferiti
-> SELECT * FROM Artisti
-> WHERE Genere = 'Pop';
mysql> INSERT INTO ArtistiPreferiti
-> SELECT * FROM Artisti
-> WHERE Genere = 'Rock';
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
49
Esercizi
Si suppone che il database CollezioneMusicale contenga almeno 5 righe per ogni
tabella che lo compone. Provvedete a inserirle con il comando INSERT. Dopo ogni
esercizio controllare l’effetto dei comandi eseguiti.
1.
2.
Usate il comando DELETE per eliminare tutte le righe introdotte nella tabella Brani
Usate il comando DELETE per cancellare le righe con ID = 1 e 2 nella tabella
Registrazioni
3.
Usate il comando TRUNCATE per cancellare tutte le righe dalle tabelle Registrazioni e
Artisti
4.
Usate il comando LOAD DATA INFILE per popolare le tabelle del database con i dati
contenuti nei file Artisti.txt, Registrazioni.txt, Brani.txt che troverete nello spazio
riservato al corso
5.
6.
Usate il comando UPDATE per eliminare gli spazi inutili dai nomi dei brani
7.
Create una tabella temporanea con i brani di genere Pop oppure Rock
Inserite una riga per Artisti con il campo Genere contenente un valore non previsto per
il tipo del campo
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
50
Il comando SELECT (1)
SELECT Colonna1, Colonna2, . . .
FROM Tabella1, Tabella2, . . .
FROM
FROM
FROM
FROM
FROM
FROM
R
R
R
R
R
R
CROSS JOIN S
JOIN S ON Condizione
JOIN S USING (Colonna)
NATURAL JOIN S
LEFT JOIN S ON ..
RIGHT JOIN S ON ..
WHERE CondizioniSulleRighe
GROUP BY Colonna-e
HAVING CondizioniSuiRaggruppamenti
ORDER BY Col1 [ASC, DESC] , Col2 [ASC, DESC], .. |
LIMIT NumeroRighe ;
LIMIT 5
LIMIT 1,3
Rand()
-- mostra solo 5 righe
–- mostra le righe 2,3,4
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
51
Il comando SELECT (2)
Congiunzione tra Artisti e Brani sul campo IDArtista
mysql> SELECT Nome, TitoloBrano, B.Genere
-> FROM Artisti A, Brani B
-> WHERE A.IDArtista = B.IDArtista;
mysql> SELECT Nome, TitoloBrano, B.Genere
-> FROM Artisti A JOIN Brani B ON A.IDArtista = B.IDArtista;
mysql> SELECT Nome, TitoloBrano, B.Genere
-> FROM Artisti JOIN Brani B USING(IDArtista);
mysql> SELECT Nome, TitoloBrano, B.Genere
-> FROM Artisti NATURAL JOIN Brani B;
Attenzione !
-- -------------------------------------------------------------Il JOIN precedente equivale a:
-- ------------------------------------------------------------mysql> SELECT Nome, TitoloBrano, B.Genere
-> FROM Artisti JOIN Brani B USING(IDArtista, Genere);
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
52
Il comando SELECT (3)
-- -------------------------------------------------------------Le registrazioni senza brani
-- ------------------------------------------------------------mysql> SELECT TitoloAlbum
-> FROM Registrazioni LEFT JOIN Brani USING (IDRegistrazione)
-> WHERE TitoloBrano IS NULL;
-- -------------------------------------------------------------I brani che non sono inseriti in alcuna registrazione
-- ------------------------------------------------------------mysql> SELECT TitoloBrano
-> FROM Registrazioni RIGHT JOIN Brani USING (IDRegistrazione)
-> WHERE TitoloAlbum IS NULL;
-- ------------------------------------------------------------UNION
-Il FULL JOIN tra Registrazioni e Brani:
UNION ALL
-- ------------------------------------------------------------mysql> ( SELECT R.IDRegistrazione, TitoloAlbum, TitoloBrano
->
FROM Registrazioni R LEFT JOIN Brani B USING(..) )
-> UNION
-- UNION Equivale a UNION DISTINCT
-> ( SELECT R.IDRegistrazione, TitoloAlbum, TitoloBrano
->
FROM Registrazioni R RIGHT JOIN Brani B USING(..) );
53
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Il comando SELECT (4)
mysql> SELECT * FROM Artisti
-> WHERE Nome LIKE 'a%';
+-----------+----------+--------+------+
| IDArtista | Nome
| Genere | Note |
+-----------+----------+--------+------+
|
2 | Anatolia | Rock
|
|
|
5 | Argolide | Rock
|
|
+-----------+----------+--------+------+
mysql> SELECT * FROM Artisti
-> WHERE Nome LIKE BINARY 'a%';
Empty set (0.39 sec)
BINARY
per confrontare
stringhe in modalità
case sensitive
mysql> SELECT * FROM Artisti WHERE Nome = 'anatolia';
+-----------+----------+--------+------+
| IDArtista | Nome
| Genere | Note |
+-----------+----------+--------+------+
|
2 | Anatolia | Rock
|
|
+-----------+----------+--------+------+
mysql> SELECT * FROM Artisti WHERE Nome = BINARY 'anatolia';
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
54
Il comando SELECT (5)
mysql> SELECT TitoloBrano, ROUND(DurataBrano*60)
-> FROM Brani
-> LIMIT 4;
+------------------------+-----------------------+
| TitoloBrano
| ROUND(DurataBrano*60) |
+------------------------+-----------------------+
| A little more
|
285 |
| A few minutes later
|
210 |
| Beside the bed
|
270 |
| Suspected of hjiacking |
315 |
+------------------------+-----------------------+
LIMIT 4
Equivale a:
LIMIT 0, 4
mysql> SELECT TitoloBrano, ROUND(DurataBrano*60)
-> FROM Brani
-> LIMIT 1,2;
+------------------------+-----------------------+
| TitoloBrano
| ROUND(DurataBrano*60) |
+------------------------+-----------------------+
| A few minutes later
|
210 |
| Beside the bed
|
270 |
+------------------------+-----------------------+
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
55
Esempi di query (1)
Tabella
Rubrica
+-----------+-----------+-----------------------------+-------------+------------+
| Nome
| Cognome
| Indirizzo
| Telefono
| Nascita
|
+-----------+-----------+-----------------------------+-------------+------------+
| Giuseppe | Piccinini | Via Garibaldi, 37 - Catania | 095-723651 | 1990-12-04 |
| Francesco | Rossi
| Via Mazzini, 12 - Roma
| 06-4589236 | 1986-10-20 |
| Gianni
| Bassi
| Via Verdi, 8 - Milano
| 02-8825321 | 1988-01-15 |
| Bruno
| Bianchi
| Viale Cavour, 55 - Napoli
| 081-3712453 | 1987-05-16 |
+-----------+-----------+-----------------------------+-------------+------------+
Rubrica( Nome, Cognome, Indirizzo, Telefono, Nascita )
Da Rubrica si vogliono estrarre i seguenti elenchi:
•
Nome e Cognome compattati in un’unica stringa
•
Nome, Cognome e città di residenza delle persone
•
Nome, Cognome e prefisso telefonico
•
Elenco delle persone il cui compleanno cade nel prossimo mese
•
Nome, Cognome e data di nascita visualizzata nel modo
abituale: gg-mm-aaaa
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
56
Esempi di query (2)
Rubrica( Nome, Cognome, Indirizzo, Telefono, Nascita )
-- --------------------------------------------------------------- Nome e Cognome compattati
-- -------------------------------------------------------------mysql> SELECT CONCAT(Nome,' ',Cognome)
CONCAT
-> FROM Rubrica;
LOCATE
-- -------------------------------------------------------------SUBSTRING
-- Nome, Cognome e città di residenza
LEFT
-- Nome, Cognome e prefisso telefonico
-- -------------------------------------------------------------mysql> SELECT Nome, Cognome,
-> SUBSTRING(Indirizzo, LOCATE('-',Indirizzo)+2) AS 'Citta\''
-> FROM Rubrica;
mysql> SELECT Nome, Cognome,
-> LEFT(Telefono, LOCATE('-',Telefono)-1) AS Prefisso
-> FROM Rubrica;
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
57
Esempi di query (3)
-- --------------------------------------------------------------- Elenco delle persone che compiono gli anni il prossimo mese
-- -------------------------------------------------------------mysql> SELECT Nome, Cognome
-> FROM Rubrica
-> WHERE MONTH(Nascita) =
MONTH(ADDDATE(CURDATE(), INTERVAL 1 MONTH));
-- --------------------------------------------------------------- Nome, Cognome e data nascita formattata come: gg-mm-aaaa
-- -------------------------------------------------------------SELECT Nome, Cognome,
CONCAT(DAY(Nascita),'-',MONTH(Nascita),'-',YEAR(Nascita)) AS
CONCAT(DAY(Nascita),'-',MONTHNAME(Nascita),'-',YEAR(Nascita))AS
FROM Rubrica
CURDATE
LIMIT 1;
ADDDATE, DAY
+-----------+-----------+------------+-----------------+
MONTH,
YEAR
| Nome
| Cognome
| gg-mm-aaaa | DataNascita
|
+-----------+-----------+------------+-----------------+
MONTHNAME
| Giuseppe | Piccinini | 4-12-1990 | 4-December-1990 |
+-----------+-----------+------------+-----------------+
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
58
Variabili utente e raggruppamenti
59
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Variabili utente (1)
•
Variabili alle quali si può attribuire un valore e utilizzare in un comando SQL
– SET @NomeVariabile = Espressione;
– @NomeVariabile è case sensitive sino alla versione 4
– mysql> SET @Numero=(SELECT COUNT(*) FROM Brani);
– mysql> SELECT @Numero := COUNT(*)FROM Brani;
mysql> SET @NumBrani
= (SELECT COUNT(*) FROM Brani);
mysql> SELECT @NumBrani, @brani;
+-----------+--------+
| @NumBrani | @brani |
+-----------+--------+
| 20
| NULL
|
+-----------+--------+
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Una variabile alla
quale non sia stato
attribuito alcun valore
vale NULL
60
Variabili utente (2)
Identificare il brano di durata massima
mysql> SELECT MAX(DurataBrano)
-> FROM Brani;
+------------------+
| MAX(DurataBrano) |
+------------------+
|
15.00 |
+------------------+
MAX genera il valore
massimo ma non dà
informazioni sulla
riga da cui proviene
tale valore
mysql> SET @durataMassima = (SELECT MAX(DurataBrano) FROM Brani);
mysql> SELECT TitoloBrano, Genere, DurataBrano AS Durata
-> FROM Brani
-> WHERE DurataBrano = @durataMassima;
+---------------------------+--------+--------+
| TitoloBrano
| Genere | Durata |
+---------------------------+--------+--------+
| I' ve got a man into Rome | Opera | 15.00 |
+---------------------------+--------+--------+
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
61
Variabili utente (3)
-- -------------------------------------------------------------- Naturalmente si possono usare query annidate
-- ------------------------------------------------------------mysql> SELECT TitoloBrano, Genere, DurataBrano AS Durata
-> FROM Brani
-> WHERE DurataBrano = ( SELECT MAX(DurataBrano)
FROM Brani );
-- -------------------------------------------------------------- La clausola LIMIT suggerisce una strategia alternativa
-- ------------------------------------------------------------mysql> SELECT TitoloBrano, Genere, DurataBrano AS Durata
-> FROM Brani
-> ORDER BY Durata DESC
Le due query
-> LIMIT 1;
generano il
medesimo
elenco?
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
62
Raggruppamenti (1)
•
GROUP BY Colonna WITH ROLLUP è utile per la produzione di
informazioni di sintesi e nell’analisi dei dati a scopo direzionale
-- ------------------------------------------------------- Brani per registrazione e numero totale di brani
-- -----------------------------------------------------mysql> SELECT IDRegistrazione, COUNT(*)
-> FROM Brani
-> GROUP BY IDRegistrazione WITH ROLLUP;
+-----------------+----------+
| IDRegistrazione | COUNT(*) |
WITH ROLLUP:
+-----------------+----------+
|
1 |
4 |
La funzione di
|
2 |
5 |
aggregazione viene
|
3 |
1 |
valutata sui singoli
|
6 |
6 |
raggruppamenti e sul
|
10 |
4 |
totale
|
NULL |
20 |
+-----------------+----------+
63
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Raggruppamenti (2)
/*-------------------------------------------------------------Totali per artista e generali oltre ai singoli raggruppamenti
-------------------------------------------------------------*/
mysql> SELECT IDArtista, IDRegistrazione, COUNT(*)
-> FROM Brani
-> GROUP BY IDArtista, IDRegistrazione WITH ROLLUP;
+-----------+-----------------+----------+
| IDArtista | IDRegistrazione | COUNT(*) |
+-----------+-----------------+----------+
|
1 |
1 |
4 |
|
1 |
10 |
2 |
|
1 |
NULL |
6 |
|
2 |
6 |
3 |
|
2 |
NULL |
3 |
|
3 |
6 |
1 |
|
3 |
NULL |
1 |
|
. .
|
. . |
. . |
|
NULL |
NULL |
20 |
+-----------+-----------------+----------+
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Brani dell’artista 1
Brani dell’artista 2
Brani dell’artista 3
Totale Brani
64
Controllo del flusso(1)
•
•
•
•
•
Come modificare tutti quei NULL causati dalle aggregazioni con rollup?
Funzioni per il controllo del flusso: CASE, IF, IFNULL
CASE valore WHEN val THEN res WHEN val THEN res ... ELSE res END
CASE WHEN test THEN res WHEN test THEN res ... ELSE res END
IF( expr1, expr2, expr3)
mysql> SET @dato = 2;
mysql> SELECT CASE @dato
->
WHEN 1 THEN 'uno‘ WHEN 2 THEN 'due'
->
WHEN 3 THEN 'tre‘ ELSE 'maggiore di tre'
->
END AS 'Valore';
+--------+
| Valore |
+--------+
| due
|
+--------+
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
65
Controllo del flusso(2)
mysql> set @voto=26;
mysql> SELECT CASE WHEN @voto<18 THEN 'Insufficiente'
->
WHEN @voto between 18 AND 23 THEN 'Sufficiente'
->
WHEN @voto between 24 AND 27 THEN 'Buono'
->
ELSE 'Ottimo'
->
END AS Risulato;
+----------+
| Risulato |
+----------+
Riscrivere la
| Buono
|
query di diap 63
+----------+
per modificare il
contenuto dei
campi NULL
•
IF( Test, ExprVera, ExprFalsa )
equivale a:
CASE WHEN Test THEN ExprVera ELSE ExprFalsa
•
IFNULL( expr1, expr2 )
equivale a:
IF( expr1 IS NOT NULL , expr1, expr2 )
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
66
Esercizi (1)
1.
Elenco di tutti i brani con il titolo, il genere musicale e il nome dell’artista che esegue il
brano
2.
Elenco dei brani (con titolo, genere e nome dell’artista) eseguiti da artisti che praticano
lo stesso genere del brano eseguito
3.
4.
5.
6.
7.
8.
9.
Ricercare gli artisti che non eseguono alcun brano
Ricercare gli artisti che eseguono più di tre brani
Numero e durata complessiva dei pezzi pop contenuti in Brani
Durata massima, minima e media dei brani catalogati in Brani
Numero dei generi musicali dei brani catalogati in Brani
Elencare i cinque brani più lunghi in archivio
Elencare i brani (TitoloBrano e Genere) che contengono “you” nel titolo. Elencare i
brani che contengono esattamente “You” nel titolo
10. Elencare le registrazioni di capacità massima e con il maggior valore di durata
massima
11. Elencare i brani contenuti nella registrazione il cui nome è contenuto nella variabile
utente @titolo
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
67
Esercizi (2)
12.
13.
14.
15.
16.
17.
18.
19.
20.
Elencare i brani eseguiti da un artista di nome precisato nella variabile utente @artista
Elencare gli artisti che hanno eseguito brani di genere precisato nella variabile utente
@genere
Elencare i brani di durata superiore a quella precisata nella variabile utente @durata
Elencare le registrazioni che contengono più di 4 brani
Elencare i brani di genere musicale differente da quello abitualmente praticato
dall’artista che lo esegue
Indicare il numero di brani contenuti in ogni registrazione
Creare una nuova tabella che contiene nome dell’album e numero dei brani in essa
contenuti
Individuare la registrazione con il maggior numero di brani
Individuare l’artista che esegue il maggior numero di brani (usare una tabella
temporanea creata ad hoc per risolvere il problema)
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
68
Chiavi Esterne, Interrogazioni Nidificate,
Viste Logiche
69
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Chiavi esterne e integrità referenziale (1)
Le chiavi esterne e i conseguenti vincoli di integrità referenziale sono
implementati in MySQL solamente se tutte le tabelle interessate sono di tipo
INNODB
•
MySQL ammette diversi tipi di tabelle; un database può
essere composto da tabelle di tipo differente
Attenzione
al tipo delle
tabelle !
Storage Engine
Description
MyISAM
The binary portable storage engine that is the improved replacement for ISAM.
ISAM
InnoDB
Transaction-safe tables with row locking and foreign keys.
MEMORY
An alias for HEAP.
HEAP (Actually, as of MySQL 4.1, MEMORY is the preferred term.)
BDB
Transaction-safe tables with page locking. Alias di BerkeleyDB
HEAP
The data for this table is stored only in memory.
ISAM
The original MySQL storage engine.
MERGE
A collection of MyISAM tables used as one table.
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Tipo di default:
InnoDb – ver. 5.5
MyISAM – prima
70
Chiavi esterne e integrità referenziale (2)
CREATE TABLE NomeTabella ( ... ) TYPE = NomeTipo;
ALTER TABLE NomeTabella TYPE = NomeTipo;
FOREIGN KEY (Colonne) REFERENCES Tabella(Colonne)
ON DELETE { NO ACTION | CASCADE | SET NULL }
ON UPDATE { NO ACTION | CASCADE | SET NULL }
-- ------------------------------------------------------ Tutte le tabelle implicate devono essere InnoDB
-- ----------------------------------------------------mysql> ALTER TABLE Brani TYPE InnoDB;
Se le tabelle
mysql> ALTER TABLE Artisti TYPE InnoDB;
mysql> ALTER TABLE Brani
-> ADD FOREIGN KEY (IDArtista)
-> REFERENCES Artisti(IDArtista);
non sono tutte
InnoDB il
sistema accetta
il comando ma
non fa nulla
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
71
Chiavi esterne e integrità referenziale (3)
mysql>
mysql>
->
->
ALTER TABLE Registrazioni TYPE InnoDB;
ALTER TABLE Brani ADD
FOREIGN KEY (IDRegistrazione)
REFERENCES Registrazioni(IDRegistrazione);
Per ogni
chiave esterna
mysql> DESCRIBE Brani;
viene creato
+-----------------+----------------------+------+-----+---------+
| Field
| Type
| Null | Key | Default |
un indice
+-----------------+----------------------+------+-----+---------+
| IDBrano
| int(10) unsigned
| NO
| PRI | NULL
|
| TitoloBrano
| varchar(50)
| NO
|
|
|
| Genere
| varchar(10)
| YES |
| NULL
|
| DurataBrano
| float(5,2) unsigned | YES |
| 4.00
|
| Dimensione
| int(10) unsigned
| YES |
| NULL
|
| Posizione
| smallint(5) unsigned | YES |
| NULL
|
| Note
| varchar(255)
| YES |
| NULL
|
| IDArtista
| int(10) unsigned
| YES | MUL | NULL
|
| IDRegistrazione | int(10) unsigned
| YES | MUL | NULL
|
+-----------------+----------------------+------+-----+---------+
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
72
Chiavi esterne e integrità referenziale (4)
-- ------------------------------------------------------ Valgono i vincoli di integrita referenziale
-- ----------------------------------------------------mysql> INSERT INTO Brani (IDBrano, TitoloBrano,
-> Genere, IDArtista, IDRegistrazione) VALUES
-> ( \N,'La mia canzone disperata', 'Pop', 25, 1);
ERROR 1216 (23000): Cannot add or update a child row: a
foreign key constraint fails
mysql> DELETE FROM Registrazioni
-> WHERE IDRegistrazione = 2;
ERROR 1217 (23000): Cannot delete or update a parent row:
a foreign key constraint fails
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
73
Esercizi
Anagrafica( Nome, Cognome, Dipart, Ufficio, Stipendio, Città)
Dipartimenti( Dipart, Indirizzo, Città)
1.
Definire le chiavi esterne nella tabella Brani del database CollezioneMusicale dopo
avere creato tabelle di tipo MyISAM. Inserire in Brani una o più righe che violano i
vincoli di integrità referenziale e osservare cosa succede.
2.
Dopo avere eseguito l’esercizio 1, modificare il tipo delle tre tabelle facendole diventare
di tipo InnoDB. Osservare cosa succede.
3.
Dopo avere modificato il tipo delle tabelle e definito chiavi eterne e imposto vincoli di
integrità referenziale provate a inserire in Brani righe che non rispettano i vincoli e a
cancellare dalla tabella Artisti righe di artisti che sono referenziati nella tabella Brani.
4.
Dopo avere creato le tabelle Anagrafica e Dipartimenti secondo lo schema sopra
specificato definite chiavi esterne e vincoli di integrità referenziale fra Anagrafica e
Dipartimenti, imponendo l’aggiornamento a catena di record correlati.
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
74
Interrogazioni Nidificate (1)
•
MySQL permette di costruire interrogazioni nidificate con una sottoquery nella
clausola FROM
•
Elenco con spazio libero e numero di registrazione per i CD-R e CD-RW:
-- ------------------------------------------------------------- Il comando SELECT che produce lo spazio occupato
-- -----------------------------------------------------------mysql> SELECT B.IDRegistrazione AS Album,
Sum(Dimensione) AS Occupato
-> FROM Brani B JOIN Registrazioni R USING (IDRegistrazione)
-> WHERE R.TipoAlbum = 'CD-R' OR R.TipoAlbum = 'CD-RW'
-> GROUP BY B.IDRegistrazione;
+-------+----------+
| Album | Occupato |
+-------+----------+
|
6 | 37085
|
|
10 | 21435
|
+-------+----------+
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
75
Interrogazioni Nidificate (2)
-- ------------------------------------------------------ Il comando SELECT che produce lo spazio libero
-- ----------------------------------------------------mysql> SELECT V.Album, (Capacita - Occupato) AS SpazioLibero
-> FROM Registrazioni AS T,
->
(SELECT B.IDRegistrazione AS Album,
->
SUM(Dimensione) AS Occupato
->
FROM Brani B JOIN Registrazioni R USING
->
(IDRegistrazione)
->
WHERE R.TipoAlbum = 'CD-R' OR R.TipoAlbum = 'CD-RW'
->
GROUP BY B.IDRegistrazione) AS V
-> WHERE V.Album = T.IDRegistrazione;
Attenzione: la
+-------+--------------+
tabella nella
| Album | SpazioLibero |
query annidata
+-------+--------------+
deve essere
|
6 | 679715
|
rinominata
|
10 | 644165
|
+-------+--------------+
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
76
Viste Logiche (1)
•
Il problema precedente affrontato con una vista logica
-- ---------------------------------------------------------- La vista logica che registra lo spazio occupato
-- --------------------------------------------------------mysql> CREATE VIEW OccupazioneCD AS
-> SELECT B.IDRegistrazione,
->
Sum(Dimensione) AS SpazioOccupato
-> FROM Brani B JOIN Registrazioni R USING (IDRegistrazione)
-> WHERE R.TipoAlbum IN ('CD-R', 'CD-RW')
-> GROUP BY B.IDRegistrazione;
-- --------------------------------------------------------- Query per determinare lo spazio libero su CD
-- -------------------------------------------------------mysql> SELECT R.IDRegistrazione, ( Capacita – SpazioOccupato )
->
AS SpazioLibero
-> FROM Registrazioni R JOIN OccupazioneCD USING
->
(IDRegistrazione);
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
77
Viste Logiche (2)
•
Il “vero” codice che ha creato la vista logica ...
mysql> SHOW CREATE VIEW OccupazioneCD;
CREATE ALGORITHM=UNDEFINED VIEW
`collezionemusicale`.`occupazionecd` AS
select . . .
The optional ALGORITHM clause is a MySQL extension to standard SQL.
ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED. The
default algorithm is UNDEFINED if no ALGORITHM clause is present. The
algorithm affects how MySQL processes the view.
For MERGE, the text of a statement that refers to the view and the view
definition are merged such that parts of the view definition replace
corresponding parts of the statement.
For TEMPTABLE, the results from the view are retrieved into a temporary
table, which then is used to execute the statement.
For UNDEFINED, MySQL chooses which algorithm to use. It prefers MERGE
over TEMPTABLE if possible, because MERGE is usually more efficient and
because a view cannot be updatable if a temporary table is used.
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
78
Viste Logiche (3)
•
La clausola WITH CHECK OPTION permette di controllare i dati immessi con una vista
logica
•
Utile per ovviare alla mancanza della clausola CHECK nel comando CREATE TABLE di
MySQL (la clausola è ammessa ma non fa nulla)
-- The WITH CHECK OPTION clause can be given for an updatable
-- view to prevent inserts or updates to rows except those for
-- which the WHERE clause in the select_statement is true.
-- ------------------------------------------------------------ Durata brani compresa tra 15 secondi e 60 minuti
-- ----------------------------------------------------------mysql-> CREATE VIEW BraniControllati AS
-> SELECT *
-> FROM Brani
-> WHERE DurataBrano BETWEEN 0.25 AND 60.0
-> WITH CHECK OPTION;
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
79
Viste Logiche (4)
•
•
L’inserimento nella tabella Brani avviene senza controlli ..
L’inserimento nella vista logica BraniControllati è controllato . . .
-- ---------------------------------------------------------- Inserimento di un brano con valori non ammessi in Brani
-- --------------------------------------------------------mysql> INSERT INTO Brani VALUES
-> (100,‘Titolo Brano','Pop', 85, \N, \N, \N, 1, 1);
Query OK, 1 row affected (0.08 sec)
mysql> DELETE FROM Brani
-> WHERE IDBrano = 100;
Query OK, 1 row affected (0.07 sec)
-- ------------------------------------------------------------ Un brano con valori scorretti inserito in BraniControllati
-- ----------------------------------------------------------mysql> INSERT INTO BraniControllati VALUES
-> (\N,‘Titolo Brano','Pop', 85, \N, \N, \N, 1, 1);
ERROR 1369 (HY000): CHECK OPTION failed . . .
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
80
Esercizi (1)
•
Calcolare il “vero” spazio libero su CD. (La soluzione proposta nelle precedenti
diapositive non tiene conto di eventuali registrazioni vuote)
•
Definire la vista logica BraniRock:
mysql> SELECT * FROM branirock;
+----------------------------+--------+----------+-----------------------+-----------+
| Titolo
| Durata | Artista | Album
| Posizione |
+----------------------------+--------+----------+-----------------------+-----------+
| The other side of the road |
4.50 | Anatolia | My favourites
|
1 |
| Colonel Rostov
|
6.00 | Argolide | My favourites
|
3 |
| She Smiled
|
4.75 | Anatolia | My favourites
|
6 |
| It was all wrong
|
3.75 | Anatolia | My favourites
|
4 |
| You are a liar
|
4.25 | Argolide | Mixed songs: raccolta |
5 |
+----------------------------+--------+----------+-----------------------+-----------+
•
Interrogare la vista logica BraniRock per produrre la tabella:
+----------------------------+--------+----------+----------------------------------+
| Titolo
| Durata | Artista | Posizione e Titolo Album
|
+----------------------------+--------+----------+----------------------------------+
| The other side of the road |
4.50 | Anatolia | Posto 1 in My favourites
|
| Colonel Rostov
|
6.00 | Argolide | Posto 3 in My favourites
|
| She Smiled
|
4.75 | Anatolia | Posto 6 in My favourites
|
| It was all wrong
|
3.75 | Anatolia | Posto 4 in My favourites
|
| You are a liar
|
4.25 | Argolide | Posto 5 in Mixed songs: raccolta |
+----------------------------+--------+----------+----------------------------------+
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
81
Esercizi (2)
•
Interrogare la vista logica BraniRock per produrre la tabella:
+----------------------------------------+----------------------------+--------+
| Pezzo e Artista
| Album e Posizione
| Durata |
+----------------------------------------+----------------------------+--------+
| The other side of the road -- Anatolia | My favourites -- 1
|
4.50 |
| Colonel Rostov -- Argolide
| My favourites -- 3
|
6.00 |
| She Smiled -- Anatolia
| My favourites -- 6
|
4.75 |
| It was all wrong -- Anatolia
| My favourites -- 4
|
3.75 |
| You are a liar -- Argolide
| Mixed songs: raccolta -- 5 |
4.25 |
+----------------------------------------+----------------------------+--------+
•
Identificare la registrazione che contiene il maggior numero di Brani interrogando una
opportuna vista logica
•
Identificare la registrazione di maggior durata (in base al totale della durata dei brani
contenuti) interrogando una opportuna vista logica
Anagrafica( Nome, Cognome, Dipart, Ufficio, Stipendio, Città)
Dipartimenti( Dipart, Indirizzo, Città)
•
Identificare il dipartimento che ha la media degli stipendi più elevata
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
82
Stored Procedure e Stored Function
Stored Routine (1)
•
Le Stored Procedure e le Stored Function sono procedure memorizzate nel
database che svolgono compiti di norma demandati a programmi applicativi
che risiedono sui client
•
Le Stored Procedure, sono attivate con il comando CALL; ricevono e
restituiscono valori normalmente tramite parametri
•
Le Stored Function svolgono funzioni analoghe a quelle delle funzioni di
libreria (Concat( ), Sqrt( ), .. ) e sono utilizzate in modo analogo
Vantaggi:
• Migliorano la sicurezza
• Semplificano lo sviluppo delle applicazioni: concentrando il codice sul server e
creando componenti riutilizzabili in diversi ambienti e con diversi linguaggi
• Riducono il traffico sulla rete
• Migliorano la portabilità del software
Svantaggi:
• Le Stored Routine appesantiscono il server
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
84
Stored Routine (2)
CREATE FUNCTION NomeFunction ( ElencoParametri )
RETURNS TipoValoreRitornato
CorpoDellaFunction;
Vedi manuale 12.1.15.
-- ----------------------------------------------------- Calcolare i secondi della parte frazionaria di una
-- durata espressa in minuti e frazioni di minuto
-- ---------------------------------------------------DELIMITER //
CREATE FUNCTION GetSec( Tempo Float )
RETURNS Int DETERMINISTIC
BEGIN
DECLARE Minuti, Secondi Int;
SET Minuti = Floor(Tempo);
SET Secondi = (Tempo - Minuti) * 60;
RETURN Secondi;
END //
DELIMITER ;
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
85
Stored Routine (3)
-- ----------------------------------------------------Esempi con GETSEC
-- ---------------------------------------------------mysql> SELECT GetSec(3.5), GetSec(4.25), GetSec(0.2);
+-------------+--------------+-------------+
| GetSec(3.5) | GetSec(4.25) | GetSec(0.2) |
+-------------+--------------+-------------+
|
30 |
15 |
12 |
+-------------+--------------+-------------+
mysql> SELECT 12.75 AS Durata,
-> Concat(Floor(12.75),":",GetSec(12.75)) AS 'MM:SS';
+--------+-------+
| Durata | MM:SS |
+--------+-------+
| 12.75 | 12:45 |
+--------+-------+
Mysql> CREATE FUNCTION MinutiSecondi (Tempo Float)
-> RETURNS Varchar(5) DETERMINISTIC
-> RETURN Concat( Floor(Tempo), ”:”, GetSec(Tempo) );
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
86
Stored Routine (4)
•
Nelle Stored Routine possono comparire strutture di controllo: vedi manuale 12.7.6
– IF THEN ELSE ; IF THEN ELSE IF THEN .. ELSE
– WHILE
– REPEAT
CREATE FUNCTION TestIF (A Int)
RETURNS Int DETERMINISTIC
Begin
Declare X Int;
IF A < 3 THEN
Set X = 1;
ELSE
Set X = 2;
END IF;
Return X;
End //
-- --------------------------------------------TestIF(0)
Restituisce 1
-TestIF(5)
Restituisce 2
-- -------------------------------------------Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
87
Stored Routine (5)
CREATE FUNCTION TestIF2 (A Int)
RETURNS Int DETERMINISTIC
Begin
Declare X Int;
IF A < 0 THEN
Set X = -1;
ELSE IF A = 0 THEN
Set X = 0;
ELSE
Set X = 1;
END IF;
Return X;
End //
-- -------------------------------------------TestIF2(-5) Restituisce -1
-TestIF2(0)
Restituisce 0
-TestIF2(8)
Restituisce 1
-- ------------------------------------------Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
88
Stored Routine (6)
CREATE FUNCTION TestWhile(X Int) RETURNS Int DETERMINISTIC
Begin
WHILE X < 10 DO
SET X = X + 1;
END WHILE;
Return X;
End //
-- --------------------------------------------- TestWhile(12) Restituisce 12
-- TestWhile(0) Restituisce 10
-- -------------------------------------------CREATE FUNCTION TestRepeat(X Int) RETURNS Int
DETERMINISTIC
Begin
REPEAT
Set X = X + 1;
UNTIL X > 0 END REPEAT;
Return X;
End //
-- --------------------------------------------- TestRepeat(10)
Restituisce 11
-- TestRepeat(-5)
Restituisce 1
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
89
Stored Routine (7)
STORED PROCEDURE
VS STORED FUNCTION
•
•
•
Sono richiamate con: CALL NomeProcedura( ElencoParametri )
•
•
•
Una procedura può richiamare altre procedure o funzioni
•
Una procedura può restituire una intera tabella, al di fuori dei parametri, per
effetto di un comando SELECT.
•
In una Function non possono comparire comandi SQL (5.0) mentre è possibile
dalla versione 5.1 di mysql
•
CREATE PROCEDURE NomeProcedura (ElencoParametri)
Ricevono e restituiscono valori tramite parametri
Nelle procedure si possono usare tutte le istruzioni ammesse nelle FUNCTION
ad eccezione di RETURN
Una funzione può richiamare solo funzioni
Una procedura può fare uso di qualsiasi comando SQL ad eccezione di quelli
che permettono di gestire Procedure, Funzioni e Trigger
CorpoDellaProcedura;
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
90
Stored Routine (8)
-- ----------------------------------------------------- Esempio di procedura che restituisce una tabella
-- ---------------------------------------------------mysql> CREATE PROCEDURE BraniOpera()
-> SELECT TitoloBrano, DurataBrano
-> FROM Brani
-> WHERE Genere = "Opera";
mysql> CALL BraniOpera();
+---------------------------+-------------+
| TitoloBrano
| DurataBrano |
+---------------------------+-------------+
| The way we live
|
12.50 |
| I' ve got a man into Rome |
15.00 |
| If I get a better one
|
13.00 |
+---------------------------+-------------+
Creare una procedura per
interrogare un database
con semplici comandi
Mysql> CALL ElencoBrani(”Rock”);
Mysql> CALL ElencoBrani(”Pop”);
91
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Stored Routine (9)
-- ---------------------------------------------------IN, OUT
-La precedente procedura generalizzata
INOUT
-- ---------------------------------------------------mysql> CREATE PROCEDURE ElencoBrani(IN Tipo VARCHAR(10))
-> SELECT TitoloBrano, DurataBrano
-> FROM Brani
-> WHERE Genere = Tipo;
-- ----------------------------------------------------Uso della procedura ElencoBrani
-- ---------------------------------------------------mysql> CALL ElencoBrani(‘Pop’);
mysql> SET @genere = ‘Opera’;
mysql> CALL ElencoBrani(@genere);
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
In una procedura può
essere usato qualsiasi
comando SQL.
Costruiamo una procedura
per inserire un nuovo
artista
92
Stored Routine (10)
-- ----------------------------------------------------Comandi DELIMITER omessi
-- ---------------------------------------------------mysql> CREATE PROCEDURE NuovoArtista
->
( IN Nom Varchar(50), IN Tipo Varchar(10),
->
IN Comm Varchar(255), OUT Codice Int,
->
OUT NumArtisti Int )
-> BEGIN
->
INSERT INTO Artisti VALUES(\N, Nom, Tipo, Comm);
->
SELECT Count(*) INTO NumArtisti
->
FROM Artisti
->
WHERE Genere = Tipo;
->
SELECT last_insert_id() INTO Codice;
last_insert_id( )
-> END //
recupera l’ultimo valore
auto_increment
-- ----------------------------------------------------Uso della procedura
-- ---------------------------------------------------mysql> CALL NuovoArtista(‘NomeArtista’,‘Pop’,‘’, @cod, @numero );
mysql> SELECT @cod AS CodiceArtista,
->
@numero AS NumeroArtisti;
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
93
Stored Routine (11)
-- --------------------------------------------------------------Uso di una procedure per controllare i dati
-- -------------------------------------------------------------mysql> CREATE PROCEDURE NuovoBrano
->
( Titolo Varchar(50), Tipo Varchar(10), Dur Float(5,2),
->
Dim Int, Pos Int, Comm Varchar(255), Artista Int,
->
Registrazione Int,
->
OUT Codice Int, OUT Msg Varchar(30) )
-> BEGIN
->
IF Dur >= 0.25 AND Dur <= 60.0 THEN
->
INSERT INTO Brani VALUES (
->
\N,Titolo,Tipo,Dur,Dim,Pos,Comm,Artista,Registrazione
->
);
->
SELECT last_insert_id() INTO Codice;
->
SET Msg = CONCAT(“Artista num. “,Codice,” inserito”);
->
ELSE
->
SET Msg = “Durata brano non ammessa”;
->
ENDIF;
I parametri
-> END //
possono essere:
IN,
OUT, INOUT
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
94
Stored Routine (12)
-- ---------------------------------------------------------------- row_count() restituisce:
-il numero di righe influenzate da INSERT, UPDATE o DELETE
--1 in caso di errore
-- --------------------------------------------------------------mysql> INSERT INTO Artisti VALUES (1,'nome', 'pop', \N);
ERROR 1062 (23000): Duplicate entry '1' for key 1
mysql> SELECT row_count();
+-------------+
| row_count() |
+-------------+
|
-1 |
+-------------+
mysql> UPDATE Artisti
-> SET Note = '********'
-> WHERE IDArtista = 1000;
mysql> SELECT row_count();
+-------------+
| row_count() |
+-------------+
|
0 |
+-------------+
found_rows()
numero di righe
recuperate da
SELECT
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
95
Stored Routine (13)
-- ---------------------------------------------------------------- Una versione complessa di InserisciBrano
-- --------------------------------------------------------------mysql> create procedure InserisciBrano
->
(IN titolo varchar(50), IN genere varchar(10),
->
IN durata float, IN dimensione int, IN note
->
varchar(255), IN IDArt int, IN IDReg int,
->
OUT numBrani int, OUT artista varchar(50),
->
OUT registrazione varchar(50), OUT msg varchar(30) )
-> begin
->
declare ID, numRighe int;
->
->
select count(*) into numBrani from Brani
->
where IDRegistrazione=IDReg;
->
->
set numBrani = numBrani + 1;
->
insert into Brani values
->
( \N, titolo, genere, durata, dimensione, numBrani,
->
note, IDArt, IDReg);
->
->
select row_count() into numRighe;
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
96
Stored Routine (14)
->
if numRighe > 0 then
->
select last_insert_id() into ID;
->
update Registrazioni
->
set DataAggiornamento = CURDATE()
->
where IDregistrazione = IDReg;
->
select Nome into artista
->
from Artisti
->
where IDArtista = IDArt;
->
select TitoloAlbum into registrazione
->
from Registrazioni
->
where IDRegistrazione = IDReg;
->
set msg = Concat('Inserito brano con codice ', ID,
->
', data registrazione aggiornata');
->
else
->
set numBrani = 0;
->
set artista = '';
->
set registrazione = '';
->
set msg = 'Errore nei dati forniti';
->
end if;
-> end //
97
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Stored Routine (15)
Comandi per la gestione delle stored routine:
•
CREATE PROCEDURE | FUNCTION . . .
–
–
–
–
–
CALL
Dai precedenti esempi:
DECLARE
•
Le Function generalizzano il
concetto di operazione
•
Le Procedure generalizzano il
concetto di istruzione
SET
SELECT ... INTO
RETURN
•
•
SHOW CREATE PROCEDURE | FUNCTION
•
Vedi manuale:
DROP PROCEDURE | FUNCTION
12.4.4 SET Syntax
12.4.5 SHOW Syntax
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
98
Esercizi (1)
Si consideri il database dei conoscenti:
Rubrica(Nome,Cognome,Nascita,Indirizzo,Telefono)
1.
Realizzare una funzione che permette di riconoscere se un conoscente compie gli anni
il prossimo mese
2.
3.
4.
Realizzare la funzione GETCAP ( ) che restituisce il codice postale
5.
Realizzare la funzione GETINDIRIZZO( ) che restituisce la prima parte dell’indirizzo o
“Errore nei dati” se l’indirizzo è mal formattato
6.
7.
Realizzare la funzione GETPREFISSO ( ) che restituisce il prefisso telefonico
Realizzare la funzione GETCITTA ( ) che restituisce il nome della città
Realizzare la funzione GETPROVINCIA ( ) che restituisce la sigla della provincia,
oppure “XX” se ci sono errori
Realizzare una procedura che inserisce una nuova riga in rubrica, controllando che
l’indirizzo sia correttamente formattato
99
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Esercizi (2)
8.
9.
Realizzare una procedura che impedisce l’inserimento di una riga in caso di omonimie
Realizzare una procedura che inserisce una riga in rubrica e restituisce il numero di
righe presenti nella tabella dopo l’inserimento
10. Realizzare una funzione che riceve una data e restituisce il nome del mese in lingua
italiana
11. Realizzare una procedura che inserisce una riga in Rubrica controllando la correttezza
della data di nascita e restituisce la data immessa nel formato gg-nomeMese-aaaa se
la data è corretta oppure un messaggio di errore in caso di data scorretta
12. Realizzare una procedura che inserisce una nuova riga in rubrica, controllando
che
l’indirizzo sia correttamente formattato
13. Realizzare una procedura che inserisce una riga in Brani controllando che siano
rispettati i vincoli di integrità referenziale
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
100
Esercizi (3)
•
•
•
•
•
•
Migliorare la procedura InserisciBrano per controllare che IDArtista e
IDRegistrazione siano corretti.
Nella procedura InserisciBrano controllare anche che il tipo del brano rientri fra
quelli previsti e la sua durata rientri nei limiti stabiliti.
Creare una procedura che restituisce il nome dell’artista con il maggior numero di
brani in archivio
Creare una procedura che restituisce la registrazione con il maggior numero di
brani
Creare una procedura che, dato il nome di un artista, restituisce: il numero di brani
di quell’artista presenti in archivio, e l’elenco delle registrazioni dove essi sono
collocati
…
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
Trigger
101
Trigger (1)
•
Un TRIGGER (grilletto) è un componente del database, abbinato a
una tabella, che viene attivato da un evento sulla tabella
•
Indicato anche come regola Evento-Condizione-Azione: un evento
fa scattare un controllo su una condizione che, se verificata, causa
l’esecuzione dell’azione descritta
•
Usati per:
– aggiornare file di log
– implementare meccanismi di validazione complessi: uno stipendio
non deve essere diminuito, gli iscritti a un corso non possono
essere più di 25, ..
– attuare meccanismi di allerta guidati da eventi: inviare un e-mail a
tutti gli iscritti se cambia la data di un esame, ..
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
103
Trigger (2)
TRIGGER versus STORED ROUTINE
•
•
•
•
•
Un Trigger è invisibile al programmatore
Una Stored Routine deve essere chiamata esplicitamente in una
query o in un batch di comandi e quindi non è invisibile al
programmatore
Un Trigger è abbinato a una specifica tabella di un DB
Una Stored Function è abbinata a un database ed è attivabile anche
da altri database
Una Stored Procedure è abbinata a un database ed è utilizzabile
anche in altri contesti
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
104
Trigger (3)
CREATE TRIGGER NomeTrigger Momento
ON NomeTabella
FOR EACH ROW ComandoDaEseguire;
Evento
•
NomeTabella.NomeTrigger deve essere unico
•
•
•
•
Momento: BEFORE oppure AFTER
•
Un trigger può accedere ai dati della tabella alla quale è abbinato tramite i nomi:
OLD.Colonna, NEW.Colonna, con certe ovvie limitazioni (OLD.Colonna non può essere
usato con INSERT, mentre NEW.Colonna con DELETE)
•
•
Per ogni tabella ci può essere un solo trigger per un dato evento e un certo momento
•
Vedi Manuale in 12.1 e 12.4.5
Evento: INSERT, UPDATE oppure DELETE
Tabella: deve essere una tabella reale e permanente
Comando da eseguire: tutte le istruzioni ammesse per le funzioni ad eccezione del
comando RETURN
Comandi: CREATE TRIGGER xxxxx, DROP TRIGGER xxxx, SHOW TRIGGERS,
SHOW CREATE TRIGGER xxxx, SHOW WARNINGS
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
105
Trigger (4)
•
Nello standard SQL sono possibili Trigger come il seguente che ha lo scopo di
impedire l’inserimento di un nuovo assunto in un dipartimento, se si superano i
100 dipendenti assegnati al dipartimento
CREATE TRIGGER NuovoAssunto AFTER INSERT ON Personale
REFERENCING NEW AS N
WHEN ( 100 < ( SELECT COUNT(*)
FROM Personale
WHERE Dipartimento=N.Dipartimento )
)
FOR EACH ROW
BEGIN
DELETE FROM Personale
WHERE ID = N.ID;
END;
Trigger impossibile da costruire con la versione 5.1 di MySQL. In questa
versione è possibile usare comandi SQL, ma non per accedere alla tabella
associata al trigger
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
106
Trigger (5)
-- --------------------------------------------------------- Meccanismo di conteggio sul genere dei brani inseriti
-- -------------------------------------------------------mysql>
->
->
->
->
->
->
->
->
->
->
->
->
CREATE TRIGGER ContaGenere AFTER INSERT ON Brani
FOR EACH ROW
BEGIN
IF NEW.Genere = ‘Pop' THEN
SET @pop = @pop + 1;
ELSEIF NEW.Genere = ‘Rock' THEN
SET @rock = @rock + 1;
ELSEIF NEW.Genere = ‘Rap' THEN
SET @rap = @rap + 1;
ELSE
Dopo avere inserto
SET @altri = @altri + 1;
righe in Brani è
END IF;
possibile sapere il
END //
numero di brani
inseriti suddivisi per
Mysql> SET @pop=0, @rock=0, @rap=0, @altri=0;
tipo . . .
MySQL> ...
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
107
Trigger (6)
-- ------------------------------------------------------ Trigger per costruire valori di default per la
-- durata di un brano: [0.25,60.0]
-- ----------------------------------------------------mysql>
->
->
->
->
->
->
->
->
->
->
->
->
CREATE TRIGGER InsBrano BEFORE INSERT ON Brani
FOR EACH ROW
BEGIN
IF NEW.DurataBrano < 0.25 THEN
SET NEW.DurataBrano = 0.25;
SET @msg = ‘Durata modificata in 0.25’;
ELSEIF NEW.DurataBrano > 60.0 THEN
SET NEW.DurataBrano = 60.0;
SET @msg = ‘Durata modificata in 60’;
ELSE
SET @msg = ‘Durata brano corretta’;
END IF;
END//
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
108
Trigger (7)
-- ------------------------------------------------------ Trigger per impedire l’inserimento di un brano di
-- durata fuori dai limiti [0.25,60.0]
-- ----------------------------------------------------mysql> CREATE TRIGGER CheckDurata BEFORE INSERT ON Brani
-> FOR EACH ROW
-> BEGIN
->
IF DurataBrano < 0.25 OR DurataBrano > 60.0 THEN
->
SET NEW.IDBrano = ‘999999';
->
SET @msg = ‘Durata fuori dai limiti';
->
ELSE
999999 è un
->
SET @msg = ‘Durata corretta';
brano fittizio già
->
END IF;
immesso nella
-> END //
tabella
-- ----------------------------------------------------------- Poiché 999999 c’è già il tentativo di immettere un altro
-- brano con la stessa chiave è impedito dal sistema
-- ----------------------------------------------------------
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
109
Esercizi
Film ( Titolo, Anno, Lunghezza, aColori, NomeStudio )
1.
Definire un trigger per controllare e fornire valori di default per l’attributo aColori
2.
Definire un trigger per controllare e fornire valori di default per Lunghezza
3.
Definire un trigger per contare i film inseriti in una sessione di lavoro, suddivisi per
decennio, tra il 1980 e il 2006
4.
Definire un trigger per impedire l’immissione di un film con data anteriore al 1950
5.
Definire un trigger per impedire l’immissione di un film se la sua durata è inferiore a 15
o superiore a 180 minuti
6.
Definire un trigger per impedire l’aggiornamento delle durata di un film se essa è
minore a 15 o superiore a 180 minuti
7.
Definire un trigger per impedire la cancellazione dei film della Paramount ?
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
110
Comandi batch
Comandi batch(1)
Uno o più comandi SQL sono inseriti in un file che viene sottoposto al sistema per
l’esecuzione con la direttiva SOURCE. Il file con i comandi è uno SCRIPT
•
Ad esempio: nel database CollezioneMusicale vogliamo ottenere l’elenco dei brani di un
certo genere, in modo parametrizzato, utilizzando lo script ElencoBrani.sql
•
ElencoBrani.sql contiene:
USE CollezioneMusicale;
SELECT *
FROM Brani
WHERE GenereBrano = @genere;
•
Per eseguire lo SCRIPT ElencoBrani.sql:
Situazione
simile a quella
vista in LOAD
DATA INFILE
mysql> SET @genere = ‘Pop’;
mysql> SOURCE ElencoBrani.sql;
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
112
Comandi batch(2)
La preparazione di comandi all’interno di script è particolarmente utile nei
seguenti casi:
•
•
Necessità di eseguire spesso lo stesso comando
•
Sviluppo di una serie di comandi con esecuzione di query, avvio di
procedure, inizializzazioni e impiego di variabili utente per la
realizzazione di query parametriche e per passare dati da un
comando ad un altro
•
Esecuzione sistematica di un comando complesso, o di una serie di
comandi complessi che realizzano azioni procedurali
•
Le utility di MySQL Administrator e PhpMyAdmin effettuano i
salvataggi delle tabelle e del database costruendo script con
l’elenco dei comandi che ricreano la tabella e i dati
Realizzazione di query parametriche
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
113
Comandi batch(3)
Eseguendo il salvataggio ( dump ) della tabella Artisti
• PhPMyAdmin (pagina 228), MySQL Administrator (pagina 141)
viene generato il file Artisti.sql, per cui il dump di Artisti si concretizza in un
comando CREATE TABLE seguito da una serie di comandi INSERT
-- ------------------------------------------------------------ Struttura della tabella `artisti`
-- ----------------------------------------------------------CREATE TABLE `artisti` (
`IDArtista` int(10) unsigned NOT NULL auto_increment,
`Nome` varchar(50) NOT NULL default '',
`Genere` enum('Pop','Rock','Rap','Opera','Country','Jazz')
default NULL,`Note` varchar(255) default NULL,
PRIMARY KEY (`IDArtista`),
UNIQUE KEY `ind_nome` (`Nome`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
. . .
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
114
Comandi batch(4)
-- ------------------------------------------------------------ Dump dei dati per la tabella `artisti`
-- ----------------------------------------------------------INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
•
INTO
INTO
INTO
INTO
INTO
INTO
INTO
`artisti`
`artisti`
`artisti`
`artisti`
`artisti`
`artisti`
`artisti`
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
(1,'Pierre Borg', 'Pop', '');
(2,'Anatolia', 'Rock', '');
(3,'The Bees', 'Rap', '');
(4,'Golden Eagles','Pop', '');
(5,'Argolide', 'Rock', '');
(6,'Heavy Stones','Opera', '');
(7,'Steve Martin','Country', '');
Per creare la tabella Artisti in un db si esegue lo script Artisti.sql:
mysql> USE CollezioneMusicale;
mysql> DROP Table Artisti;
mysql> SOURCE Artisti.sql;
Integrità
referenziale ?
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
115
Esercizi
Risolvere i seguenti problemi preparando i comandi necessari in script da eseguire
con il comando SOURCE
1.
Identificare l’artista che esegue il maggior numero di brani (creando una vista logica
e interrogandola, o creando una tabella temporanea e interrogandola)
2.
Identificare la registrazione che contiene il maggior numero di brani (creando una
vista logica e interrogandola, o creando una tabella temporanea e interrogandola)
3.
Identificare la registrazione di maggior durata ( ... )
4.
Trovare l’artista che esegue il brano più lungo
5.
Elencare i corsi per i quali nessuno studente ha sostenuto esami
6.
Preparare lo script per creare il database CollezioneMusicale e popolarlo con dati
contenuti in file di testo mediante il comando LOAD DATA INFILE
Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo
116