MySQL

Basi di Dati e Sistemi InformativiProf. Marco Di FeliceDott.sa Sara Zuppiroli

A.A. 2012-2013

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 1 / 34

Gli strumenti che vedremo

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 2 / 34

Come imposteremo le lezioni

Per ogni strumento:Breve introduzioneInstallazione sul proprio pc del toolCreazione ed accesso a un DBComandi SQL da terminale e da interfaccia graficaEsempio di implementazione di un piccolo DBFunzionalità avanzateConfronto tra i due strumenti

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 3 / 34

Introduzione a MySql

MySQL è un Database system relazionale open sourceIl codice venne sviluppato dal 1979 da TcX ataconsult, edal 1996 abbiamo una versione che supporta SQL.Ad oggi MySQL supporta le piattaforme Linux, UNIX, OS Xe Windows.Supporta la gestione di tabelle, foreign keys, joins, views,triggers, and stored procedures.Include i seguenti tipi di dato: INTEGER, NUMERIC,BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, eTIMESTAMP, inoltre salva immagini, suoni e video.Ha interfacce per i seguenti linguaggi PHP, .Net, Perl, ...

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 4 / 34

Licenza

La licenza di MySQL AB è di tipo GPL (General Public License)È una licenza che impone delle condizioni a chiridistribuisce il software, se queste non sono rispettate lalicenza risulta essere nulla.Nel caso di MySQL AB le condizioni sono:

I Se si sta producendo Software con licenza GPL è possibileusare liberamente MySQL con licenza GPL

I Se si sta producendo Software con licenza OEM, ISV e/oVAR allora MySQL offre una licenza commerciale OEM(Original Equipment Manufacturer)

http://www.mysql.com/about/legal/licensing/oem/

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 5 / 34

Convenzioni

Useremo le stesse convenzioni applicate dal manuale MySQL,quindi:

parentesi quadre ([ ]) per indicare codice opzionale pipe (|)per indicare opzioni alternative racchiuse fra parentesigraffe ( { }) quando è obbligatorio indicarne almeno unapuntini · · · per indicare codice omesso o codice ripetibile

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 6 / 34

Installazione

Ci colleghiamo alla paginahttp://www.mysql.com/downloads/Selezionare MySQL Community ServerEseguire il download del programma

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 7 / 34

Connessione da shell

Aprire una shellAccedere alla Directory dove è stato installato MySql, adesempio in usr local bin mysqlConnettersi come root con questo comando:./mysql − u root al server

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 8 / 34

I primi passi come Admin

Alcuni comandi utili per amministrare il server di MySQLPer invocare la guida ai primi comandi: help; o ?;Cambiare la password di un utente: SET PASSWORD FOR<nomeutente>@localhost =PASSWORD(’nuovaPassword’);Creare un nuovo utente CREATE USERnomeutente@localhost;Creare un nuovo utente con Pasword CREATE USER <nomeutente > @localhost IDENTIFIED BY ′ < password ′;

Eliminare un utente DROP USER<nomeutente>@localhost;

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 9 / 34

I primi passi come Admin

Per accedere a un Database salvato: use nomedb;I Per visualizzare le tabelle appartenenti al database in uso:

show tables;I Per visualizzare la struttura di una tabella:

DESCRIBE nometabella;

Impostare una Password per unutenteSET PASSWORD FOR nomeutente@localhost =PASSWORD ′password ′;

Mostrare i database: show databases;Per visualizzare la versione di MySQL in uso: selectversion();

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 10 / 34

Esercizi

Impostare la password di rootCreare un utente con user mariorossi e password provaAccedere a mysql col nuovo utente

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 11 / 34

I privilegi

MySQL fornisce quattro livelli di privilegi: Global, Database,Table, Column.Per assegnare (cancellare) un privilegio ad un utente sihanno due possibilità:

I si usa il comando GRANT(REVOKE):GRANT | REVOKE SELECT ONnomedatabase.nometabella TO nomeutente@localhost[IDENTIFIED BY < password > ]WITH GRANT OPTION;

I si aggiornano le tabelle mysql.user, mysql.db,mysql .tables_priv ,mysql .column_priv dove sono gestitiutenti e privilegi attraverso i comandi SQL di INSERT,UPDATE, DELETE

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 12 / 34

Gestione dei database

La creazione/cancellazione di un database:CREATE | DROP DATABASE [IF NOT EXISTS]([IF EXIST ]) nome_dbIF NOT EXISTS si evita la segnalazione di errore nel casoesista il database con lo stesso nomeBisogna avere il privilegio CREATE/DROP DATABASEIn MySQL un database è una sotto directory nella directorydei dati. Quindi per creare un nuovo database bastainserire una directory nella cartella dati.

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 13 / 34

Creare una tabella 1

CREATE [TEMPORARY ] TABLE [IF NOT EXISTS]nome_tabella | nome_db.nome_tabella[(definizione, · · · )] [select ];Se si indica nome_tabella allora creo una tabella nelDatabase in Uso, altrimenti indico il nome del db.I parametri opzionali

I TEMPORARY la tabella creata sia valida solo per lapresente connessione, se esiste una tabella omonimarimane nascosta

I IF NOT EXISTS per evitare il messaggio di erroreI SELECT i dati estratti dalla select vengono inseriti nella

tabella creata.

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 14 / 34

Creare una tabella 2

con [definizione] si definiscono le colonne della tabella:I nome_colonna tipo [NOT NULL | NULL] [DEFAULT valore]

[AUTO_INCREMENT ] [UNIQUE [KEY ] | [PRIMARY ] KEY ][COMMENT ′commento′] [reference_definition]

I I parametri opzionali:F [NOTNULL | NULL] indica se il campo in colonna non deve

essere NULL, oppure può essere NULLF DEFAULT indica il valore di default, deve essere una

costante oppure CURRENT_TIMESTAMPF SELECT i dati estratti dalla select vengono inseriti nella

tabella creata.F AUTO_INCREMENT gestisce il valore di tipo intero come un

contatore automaticoF UNIQUE indice che non ha valori duplicatiF PRIMARY KEY indica la chiave primaria

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 15 / 34

Creare una tabella 3

per aggiungere un vincolo a una tabella esterna si usa ilcomando:FOREIGN KEY (nome_colonna_int) REFERENCESnome_tabella_ext [(colonna_ext)][ON DELETE | ON UPDATERESTRICT | CASCADE | SET NULL | NO ACTION];

Il comandoCREATE TABLE nuova_tabella LIKE tabella_originale;crea una tabella vuota con la stessa struttura della tabellaoriginale

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 16 / 34

Modificare una tabellaPer modificare una tabella già creata si usa il comando:ALTER [IGNORE ] TABLE nome_tabellaalter_specification [,alter_specification], · · ·i possibili valori di alter_specification sono:ADD [COLUMN] definizione_colonna[FIRST | AFTER nome_colonna]| ADD [COLUMN] (definizione_colonna, ...)| ADD [CONSTRAINT [simbolo]]PRIMARYKEY [tipo_indice](colonna_indice, ...)|ADD[CONSTRAINT [simbolo]]UNIQUE [INDEX ][tipo_indice][tipo_indice](colonna_indice, ...)alter table IMPIEGO add constraint fk_impiego_personaforeign key (cod_p) references PERSONA(cod_p) on deletecascade;

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 17 / 34

Insert in una tabella

Per inserire valori in una tabella si usa:INSERT INTO nome_tabella (colonna_1, · · · , colonna_n)VALUES (colonna_1, · · · , colonna_n);Se ci sono campi definiti come contatori, o NULL, ecc...,possono non essere indicati nell’insertINSERT multiplo:INSERT INTOnome_tabella (colonna_1, · · · , colonna_n)[VALUES (colonna_1, · · · , colonna_n), · · · ,VALUES (colonna_1, · · · , colonna_n)];

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 18 / 34

Esempio di Insert singolo e multiplo

INSERT INTO u t e n t i (UserName , Password , TipoUtente )VALUES( ’ mario ’ , ’ prova ’ , ’ x ’ ) ;

INSERT INTO u t e n t i (UserName , Password , TipoUtente )VALUES( ’ b ianch i , ’ prova ’ , ’ x ’ ) ,VALUES( ’ r o s s i ’ , ’ prova ’ , ’ x ’ ) ,VALUES( ’ ve rd i ’ , ’ prova ’ , ’ x ’ ) ;

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 19 / 34

Modifica dei valori in tabella

Per modificare un campo si usa il comandoUPDATE nomeTabellaSET [nomeCampo = ′nuovoValore′, ] WHERE id = n;Per modificare tutte le righe di una tabella non si mette laclausola WHEREPer modificare un campo numerico si possono usare leoperazioni per salvare il successoreUPDATE nomeTabella SET nomeCampo =nomeCampo + 1;

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 20 / 34

Cancellazione di un record

il comando per cancellare un campo:DELETE nomeCampo FROM nomeTabellaWHERE nomeCampo = ′valore′ LIMIT n;LIMIT n: indica il numero n massimo di istanze dacancellarePer cancellare tutti i campi di una tabella: DELETE * FROMnome_tabella;Per cancellare tutti i campi di una tabella e far ripartire icampi contatori da 1 si usa il comando: TRUNCATE TABLEnome_tabella;

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 21 / 34

Creazione di una vista

Per creare o una vista si usa il comando:CREATE [| REPLACE ]VIEW nome [(lista_colonne) AS istruzione_select[WITH [CASCADED | LOCAL]]

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 22 / 34

Le clausole della vista

REPLACE consente di sostituire una vista con lo stessonome eventualmente già esistente.La lista delle colonne, opzionale, sono i nomi delle colonnedalle quali è composta la vista. Si può omettere tale lista eadottare i nomi delle colonne restituiti dalla SELECT.SELECT è l’istruzione che definisce i dati contenuti nellavista. I limiti della select sono:

I non si possono utilizzare variabili,I non si possono contenere sub query nella FROMI non si possono fare riferimenti a tabelle temporanee

non esistono viste temporanee

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 23 / 34

Le caratteristiche della vista

viste aggiornabili: sono le viste in cui è possibile modificarei dati della tabella sottostante passando dalla vista. Unavista, per essere aggiornabile, deve esistere una relazioneuno a uno fra le righe della vista e quelle della tabellasottostante.viste non aggiornabili: non è possibile effettuare modifichealle tabelle direttamente dalla vista

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 24 / 34

Esercizio

Uno studio medico vuole di realizzare un archivio che permettadi gestire i dati utilizzati dai medici che ne fanno parte. I mediciad oggi sono cinque e ognuno di loro deve avere accesso al DBdal proprio computer con una propria password. Il medico devepoter inserire, gestire e cancellare i propri pazienti. Ognipaziente avrà una scheda anagrafica che avrà almeno iseguenti dati: nome, cognome, data di nascita, luogo di nascita,codice fiscale e numero di identificazione della tessera sanitaria.Il paziente avrà una scheda principale in cui verranno indicate lesue patologie croniche, la sua percentuale di invalidità,eventuale, e i medicinali prescritti permanentemente, comel’insulina ad un diabetico per esempio.

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 25 / 34

Esercizio

I medici sono i soli utenti che hanno i permessi di modificare,inserire o cancellare una ricetta. Le farmacie registrate possonoaccedere ai dati relative alle ricette ancora da gestire e quandoarriva il paziente viene consegnato loro il medicinale.

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 26 / 34

Esercizio

Disegnare il diagramma E/RInseriamo le tabelle relativi ad anagrafica, malattia,medicinaCreiamo la vista che permetta di estrarre per ogni utente eil numero totale di esami e medicinali prescritti, il totale diesami e medicinali evasi, il totale di medicinali ed esami insospeso.Si è reso necessario il controllo sulla tabella ricette esingola prescrizione per verificare quali utenti accedono aqueste tabelle in inserimento, modifica e cancellazione.

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 27 / 34

Esempio di Create Table

CREATE TABLE Utente(Id INTEGER AUTO_INCREMENT PRIMARY KEY,UserName VARCHAR(25) UNIQUE ,Password VARCHAR( 25 ) ,TipoUtente VARCHAR(25)) ;

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 28 / 34

Esempio di Create Table

CREATE TABLE Farmacie( Id INT PRIMARY KEY AUTO_INCREMENT,UserName VARCHAR( 25 ) ,NomeFarmacia VARCHAR( 25 ) ,FOREIGN KEY(UserName)REFERENCES U t e n t i (UserName)ON DELETE CASCADE ) ;

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 29 / 34

Esempi di Select

Estrarre tutti i dottori presenti in anagraficaTrovare le ricette che sono state prescritte da un dottore inun certo giorno per un certo pazienteCalcolare quante ricette non sono state evase da nessunafarmacia raggruppate per medico

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 30 / 34

EsercizioDati il modello logico, e i vincoli rappresentati nelle tabelle diseguito, scrivere il codice SQL per creare le tabelle.

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 31 / 34

Esercizio

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 32 / 34

Tool Grafici

MysqlWorkbenchMyAdmin

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 33 / 34

Bibliografia

http://dev.mysql.com/doc/refman/http://www.tecn.it/guida-mysql/

Basi di Dati e Sistemi Informativi () MySQL A.A. 2012-2013 34 / 34