Gli strumenti che vedremo

MySQL

Basi di Dati e Sistemi Informativi Prof. Marco Di Felice

Dott.sa Sara Zuppiroli

A.A. 2012-2013

Gli strumenti che vedremo

Come imposteremo le lezioni

Per ogni strumento:

Breve introduzione

Installazione sul proprio pc del tool Creazione ed accesso a un DB

Comandi SQL da terminale e da interfaccia grafica Esempio di implementazione di un piccolo DB

Funzionalità avanzate

Confronto tra i due strumenti

Introduzione a MySql

MySQL è un Database system relazionale open source Il codice venne sviluppato dal 1979 da TcX ataconsult, e dal 1996 abbiamo una versione che supporta SQL.

Ad oggi MySQL supporta le piattaforme Linux, UNIX, OS X e 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, e TIMESTAMP, 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 chi

ridistribuisce il software, se queste non sono rispettate la licenza risulta essere nulla.

Nel caso di MySQL AB le condizioni sono:

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

I Se si sta producendo Software con licenza OEM, ISV e/o VAR 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 parentesi

graffe ( { }) quando è obbligatorio indicarne almeno una puntini · · · per indicare codice omesso o codice ripetibile

Installazione

Ci colleghiamo alla pagina

http://www.mysql.com/downloads/

Selezionare MySQL Community Server Eseguire il download del programma

Connessione da shell

Aprire una shell

Accedere alla Directory dove è stato installato MySql, ad esempio in usr local bin mysql

Connettersi come root con questo comando:

./mysql − u root al server

I primi passi come Admin

Alcuni comandi utili per amministrare il server di MySQL Per 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 USER nomeutente@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 un

utenteSET PASSWORD FOR nomeutente@localhost = PASSWORD ⁰password⁰;

Mostrare i database: show databases;

Per visualizzare la versione di MySQL in uso: select version();

Esercizi

Impostare la password di root

Creare un utente con user mariorossi e password prova Accedere 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 si hanno due possibilità:

I si usa il comando GRANT

(REVOKE):GRANT | REVOKE SELECT ON

nomedatabase.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 gestiti utenti 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_db

IF NOT EXISTS si evita la segnalazione di errore nel caso esista il database con lo stesso nome

Bisogna avere il privilegio CREATE/DROP DATABASE

In MySQL un database è una sotto directory nella directory dei dati. Quindi per creare un nuovo database basta

inserire 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 nel Database in Uso, altrimenti indico il nome del db.

I parametri opzionali

I TEMPORARY la tabella creata sia valida solo per la presente connessione, se esiste una tabella omonima rimane nascosta

I IF NOT EXISTS per evitare il messaggio di errore

I SELECT i dati estratti dalla select vengono inseriti nella tabella creata.

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 NULL

F DEFAULT indica il valore di default, deve essere una costante oppure CURRENT_TIMESTAMP

F SELECT i dati estratti dalla select vengono inseriti nella tabella creata.

F AUTO_INCREMENT gestisce il valore di tipo intero come un contatore automatico

F UNIQUE indice che non ha valori duplicati

Creare una tabella 3

per aggiungere un vincolo a una tabella esterna si usa il comando:

FOREIGN KEY (nome_colonna_int) REFERENCES nome_tabella_ext [(colonna_ext)]

[ON DELETE | ON UPDATE

RESTRICT | CASCADE | SET NULL | NO ACTION];

Il comando

CREATE TABLE nuova_tabella LIKE tabella_originale;

crea una tabella vuota con la stessa struttura della tabella originale

Modificare una tabella

Per modificare una tabella già creata si usa il comando:

ALTER [IGNORE ] TABLE nome_tabella

alter _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_persona

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’insert

INSERT 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 , T i p o U t e n t e ) VALUES( ’ mario ’ , ’ prova ’ , ’ x ’ ) ;

INSERT INTO u t e n t i ( UserName , Password , T i p o U t e n t e ) VALUES( ’ b i a n c h i , ’ prova ’ , ’ x ’ ) ,

VALUES( ’ r o s s i ’ , ’ prova ’ , ’ x ’ ) , VALUES( ’ v e r d i ’ , ’ prova ’ , ’ x ’ ) ;

Modifica dei valori in tabella

Per modificare un campo si usa il comando UPDATE nomeTabella

SET [nomeCampo = ⁰nuovoValore⁰, ] WHERE id = n;

Per modificare tutte le righe di una tabella non si mette la clausola WHERE

Per modificare un campo numerico si possono usare le operazioni per salvare il successore

UPDATE nomeTabella SET nomeCampo = nomeCampo + 1;

Cancellazione di un record

il comando per cancellare un campo:

DELETE nomeCampo FROM nomeTabella WHERE nomeCampo = ⁰valore⁰ LIMIT n;

LIMIT n: indica il numero n massimo di istanze da cancellare

Per cancellare tutti i campi di una tabella: DELETE * FROM nome_tabella;

Per cancellare tutti i campi di una tabella e far ripartire i

campi contatori da 1 si usa il comando: TRUNCATE TABLE nome_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 stesso nome eventualmente già esistente.

La lista delle colonne, opzionale, sono i nomi delle colonne dalle quali è composta la vista. Si può omettere tale lista e adottare i nomi delle colonne restituiti dalla SELECT.

SELECT è l’istruzione che definisce i dati contenuti nella vista. I limiti della select sono:

I non si possono utilizzare variabili,

I non si possono contenere sub query nella FROM

I non si possono fare riferimenti a tabelle temporanee

non esistono viste temporanee

Le caratteristiche della vista

viste aggiornabili: sono le viste in cui è possibile modificare i dati della tabella sottostante passando dalla vista. Una

vista, per essere aggiornabile, deve esistere una relazione uno a uno fra le righe della vista e quelle della tabella

sottostante.

viste non aggiornabili: non è possibile effettuare modifiche alle tabelle direttamente dalla vista

Esercizio

Uno studio medico vuole di realizzare un archivio che permetta di gestire i dati utilizzati dai medici che ne fanno parte. I medici ad oggi sono cinque e ognuno di loro deve avere accesso al DB dal proprio computer con una propria password. Il medico deve poter inserire, gestire e cancellare i propri pazienti. Ogni

paziente avrà una scheda anagrafica che avrà almeno i

seguenti 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 le sue patologie croniche, la sua percentuale di invalidità,

eventuale, e i medicinali prescritti permanentemente, come l’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 possono accedere ai dati relative alle ricette ancora da gestire e quando arriva il paziente viene consegnato loro il medicinale.

Esercizio

Disegnare il diagramma E/R

Inseriamo le tabelle relativi ad anagrafica, malattia, medicina

Creiamo la vista che permetta di estrarre per ogni utente e il numero totale di esami e medicinali prescritti, il totale di esami e medicinali evasi, il totale di medicinali ed esami in sospeso.

Si è reso necessario il controllo sulla tabella ricette e

singola prescrizione per verificare quali utenti accedono a queste tabelle in inserimento, modifica e cancellazione.

Esempio di Create Table

CREATE TABLE U t e n t e (

I d INTEGER AUTO_INCREMENT PRIMARY KEY, UserName VARCHAR( 2 5 ) UNIQUE,

Password VARCHAR( 2 5 ) , T i p o U t e n t e VARCHAR( 2 5 )

) ;

Esempio di Create Table

CREATE TABLE Farmacie

( I d INT PRIMARY KEY AUTO_INCREMENT, UserName VARCHAR( 2 5 ) ,

NomeFarmacia VARCHAR( 2 5 ) , FOREIGN KEY( UserName )

REFERENCES U t e n t i ( UserName ) ON DELETE CASCADE ) ;

Esempi di Select

Estrarre tutti i dottori presenti in anagrafica

Trovare le ricette che sono state prescritte da un dottore in un certo giorno per un certo paziente

Calcolare quante ricette non sono state evase da nessuna farmacia raggruppate per medico

Esercizio

Dati il modello logico, e i vincoli rappresentati nelle tabelle di seguito, scrivere il codice SQL per creare le tabelle.

Esercizio

Tool Grafici

MysqlWorkbench MyAdmin

Bibliografia

http://dev.mysql.com/doc/refman/

http://www.tecn.it/guida-mysql/