Dott.sa Sara Zuppiroli Prof. Marco Di Felice Basi di Dati ...zuppirol/MySql.pdf · MySQL Basi di...

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


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


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


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/


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


Installazione

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


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


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;


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();


Esercizi

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


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


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.


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.



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



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


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;


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)];


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 ’ ) ;


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;


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;


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


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


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


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.


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.


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.


Esempio di Create Table

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


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


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


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


Esercizio


Tool Grafici

MysqlWorkbenchMyAdmin


Bibliografia

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


Dott.sa Sara Zuppiroli Prof. Marco Di Felice Basi di Dati ...zuppirol/MySql.pdf · MySQL Basi di...

Documents

Transcript of Dott.sa Sara Zuppiroli Prof. Marco Di Felice Basi di Dati ...zuppirol/MySql.pdf · MySQL Basi di...