Laboratorio -Introduzione a MySQL

Introduzione a MySQL
Cinzia Cappiello
Alessandro Raffio
Politecnico di Milano
Prima di iniziare… qualche dettaglio su MySQL
MySQL è un sistema di gestione di basi di dati relazionali
(RDBMS) Æcomposto da un insieme di relazioni
MySQL è stabile e veloce
MySQL comprende tutte le caratteristiche considerate più
importanti dalla comunità delle basi di dati, quali:
Transazioni
Vincoli a livello di record
Chiavi esterne
Interrogazioni annidate
Ricerca testuale
MySQL esiste sia in forma gratuita che in forma
commerciale. Ultima versione è MySQL 5.0 scaricabile
all’indirizzo:
www.mysql.org/
2
1
Preparazione
Accesso all’area condivisa
Start Æ Programs Æ MySQL
Avvio Server MySQL
Start Æ Programs Æ MySQL Æ Start MySQL Server
Avvio Client MySQL
Start Æ Programs Æ MySQL Æ MySQL Client
3
Schema database d’esempio
DATILIBRO (ISBN, Titolo, AnnoPubblicazione,
CasaEditrice, PrimoAutore, Genere, Consigliato)
Libro (Collocazione, ISBN, DataArchiviazione)
Prestito (Collocazione, CodiceUtente,
DataPrestito, DataRestituzione)
Utente (Codice, Cognome, Nome, Indirizzo,
Telefono)
Commento (CodiceUtente, ISBN, commento,
giudizio)
4
2
Creazione di basi di dati e tabelle
Creazione del database
Per creare un database si usano le istruzioni:
mysql> create database nome database;
Nel nostro esempio digitare il comando:
mysql> create database biblioteca;
Si può controllare che il comando ha funzionato
digitando:
mysql> show databases;
nella lista delle basi di dati presenti nel sistema
dovreste vedere anche la base di dati biblioteca
6
3
Selezione di una base di dati
Prima di creare le tabelle, o fare qualsiasi
operazione sul database è opportuno segnalare a
MySQL che si intende utilizzare la base di dati
creata. E’ necessario digitare:
mysql> use nome database;
Nel nostro esempio digitare il comando:
mysql> use biblioteca;
In questo modo la base di dati risulta selezionata e
tutto quello che si fa da questo momento andrà a
cambiare la base di dati selezionata
7
Creazione di una tabella
Sintassi completa comando:
mysql> create [temporary] table [if not exist]
nome tabella [(definizioni_create)] [opzioni
tabella] [comandi di selezione]
Definizioni create - sintassi:
Æ nome_campo TIPO [NOT NULL|NULL][DEFAULT
valore_standard][auto_increment][primary
key][definizione di chiave esterna
(references)],
Opzioni tabella - sintassi:
type = tipo tabella;
Per verificare la creazione di una tabella si può usare l’
istruzione:
mysql> show tables;
8
4
Creazione di tabelle- tipi di tabelle
MyISAM: tipo di tabelle standard, supportano la
compressione ma non gestiscono le transazioni
InnoDB: sono transaction-safe (proprietà ACIDe),
supportano le transazioni e i lock di riga
BDB (Berkeley DB): motore di memorizzazione che
supporta transazioni e lock a livello di pagina
HEAP: tabelle che vengono salvate interamente in
memoria locale e mai su disco, sono molto veloci
ma hanno dimensione limitata e non possono
essere recuperate in caso di guasto del sistema
9
Creazione di tabelle – Tipi di colonne e di dati MySQL
Tipi numerici: vengono usati per salvare numeri
NUMERIC o DECIMAL (DEC)= tipi esatti a virgola mobile, vengono di solito usati per le valute. Hanno lo
stesso intervallo di valori dei numeri a virgola mobile
INTEGER (INT) = indica l’intero standard a 4 byte con un intervallo di 232 valori possibili
FLOAT = indica un numero reale a virgola mobile a precisione semplice. Rappresenta un numero tra 1.18
x10-38 e 3.40 x1038
DOUBLE = indica un numero reale a virgola mobile e precisione doppia. Rappresenta un numero tra 2.23
x10-308 e 1.80 x10308
Tipi string o text: supporta diversi tipi di stringa o testo
CHAR=memorizza stringhe di lunghezza fissa. Il tipo char è sempre seguito dalla specifica della lungezza
della stringa. Es. char(20)
VARCHAR=memorizza stringhe a lunghezza variabile
TEXT o BLOB= servono per memorizzare testi più lunghi. BLOB indica un oggetto binario di grandi
dimensioni. Entrambi memorizzano fino a 65535 caratteri. BLOB dovrebbe memorizzare dati binari più
che testi veri e propri.
ENUM=tipo che permette di enumerare una lista di valori possibili. Es:
Genere enum (‘m’,’f’)
SET= simile a ENUM eccetto nel fatto che le righe possono contenere un insieme di valori della lista
specificata
Tipi date e time:
DATE= formato AAAA-MM-GG
TIME=formato HH:MM:SS
DATETIME=formato AAAA-MM-GG HH:MM:SS
TIMESTAMP=se non si inserisce nulla in questa colonna viene automaticamente memorizzato il momento
in cui la riga viene modificata o inserita nel database
YEAR= esiste in due varianti YEAR(2) o YEAR (4)
10
5
Esempio – tabella DATILIBRO
Nel nostro esempio, creare la tabella DATILIBRO (ISBN,
Titolo, AnnoPubblicazione, CasaEditrice, PrimoAutore,
Genere, Consigliato)
mysql> create table DATILIBRO(
Æ ISBN varchar (20) NOT NULL,
Æ Titolo varchar (40),
Æ AnnoPubblicazione Year(2),
Æ CasaEditrice varchar (30),
Æ PrimoAutore varchar(30),
Æ Genere varchar (30),
Æ Consigliato enum (‘s’,’n’),
Æ PRIMARY KEY (ISBN)
Æ ) type = InnoDB;
11
Esempio – tabelle LIBRO e PRESTITO
Libro (Collocazione, ISBN, DataArchiviazione)
mysql> create table LIBRO(
Æ Collocazione varchar (20) NOT NULL,
Æ ISBN varchar (20) not null,
Æ DataArchiviazione Date,
Æ PRIMARY KEY (Collocazione)
Æ ) type = InnoDB;
Utente (Codice, Cognome, Nome, Indirizzo, Telefono)
mysql> create table Utente(
Æ Codice int NOT NULL auto_increment primary key,
Æ Cognome varchar (30),
Æ Nome varchar (30),
Æ Indirizzo varchar (40),
Æ Telefono varchar (15)
Æ ) type = InnoDB;
12
6
Esempio – Tabelle Utente e Commento
Prestito (Collocazione, CodiceUtente, DataPrestito, DataRestituzione)
mysql> create table PRESTITO(
Æ Collocazione varchar (20) NOT NULL,
Æ Codiceutente int NOT NULL references utente(codice),
Æ DataPrestito Date NOT NULL,
Æ DataRestituzione Date,
Æ PRIMARY KEY (Collocazione, CodiceUtente, DataPrestito)
Æ ) type = InnoDB;
Commento (CodiceUtente, ISBN, commento, giudizio)
mysql> create table Commento(
Æ CodiceUtente int NOT NULL references utente(codice),
Æ ISBN varchar (20) not null,
Æ commento text,
Æ giudizio enum (‘p’, ‘n’),
Æ PRIMARY KEY (CodiceUtente, iSBN)
Æ ) type = InnoDB;
13
Visualizzazione schema di una tabella
Il comando per visualizzare lo schema di una
tabella:
mysql> describe nome_tabella;
es. describe datilibro;
Il comando per caricare una base di dati da file di
script:
mysql> source nome_file;
14
7
Eliminazione di basi di dati
Eliminazione intera basi di dati
mysql> drop database nome_database;
Eliminazione tabella- sintassi generale:
mysql> drop [temporary] table [if
exists] nome_tabella[,
nome_tabella2,…];
15
Modificare struttura della tabella
Rinominare la tabella:
mysql> ALTER TABLE nomevecchio_tabella
Æ RENAME nome_nuovo tabella;
Aggiungere un nuovo campo alla tabella
mysql> ALTER TABLE nome_tabella
Æ ADD nome TIPO;
Eliminare un campo della tabella
mysql> ALTER TABLE nome_tabella
Æ DROP nome;
Modificare un attributo della tabella
mysql> ALTER TABLE nome_tabella
Æ MODIFY nome_campovecchio nome_nuovocampo TIPO;
Aggiungere una chiave
mysql> ALTER TABLE nome_tabella
Æ ADD PRIMARY KEY (campo);
16
8
Inserimento, cancellazione e
modifica dei dati
Inserimento dati
Per inserire righe in una tabella si usa il comando
INSERT
Le tabelle vengono popolate inserendo uno alla
volta i record (tuple) che le compongono con le
seguenti istruzioni:
mysql> INSERT INTO nome_tabella VALUES
(‘contenuto primo campo’, ‘contenuto
secondo campo’,…);
I valori di tipo stringa e di tipo data vanno inseriti
tra apici, i valori numerici non vanno inclusi tra
apici
18
9
Esempio di inserimento
mysql> INSERT INTO datilibro VALUES
Æ(’88-386-6030-1’, ‘basi di dati’, ‘2003’,
‘Mc-Graw Hill’, ‘Atzeni’, ‘Informatica’,
‘n’),
Æ(‘88 386 0762-1’, ‘elementi di fisica’,
‘1998’, ‘Pearson’, ‘Villa’,‘Fisica’, ‘s’);
mysql> INSERT INTO Libro VALUES
Æ(‘INF3’,’88-386-6030-1’, ’ 2003/11/10’),
Æ(‘FIS1’,‘88 386 0762-1’, ’ 1999/04/01’);
19
Inserimento dati
Per verificare che i dati siano stati inseriti usare
l’operazione di SELECT
mysql> SELECT * FROM nometabella;
Risoluzione problemi di conflitti con chiave
primaria si utilizza il costrutto Replace: funziona
come INSERT ma qualora si verificasse un conflitto
su una chiave, la nuova riga andrebbe a sostituire
quella vecchia
mysql> REPLACE INTO nome_tabella VALUES
(‘contenuto primo campo’, ‘contenuto
secondo campo’,…);
20
10
Cancellazione dati
Eliminazione di uno o più record da una tabella.
Comando per cancellare tutte le righe di una
tabella:
mysql> DELETE FROM nome_tabella;
Oppure
mysql> TRUNCATE TABLE nome_tabella;
Si possono specificare quali righe eliminare con
l’ausilio di una condizione WHERE. esempio
mysql> DELETE FROM nome_tabella
Æ WHERE (casaeditrice=‘Mc-Graw Hill’);
21
Modifica dei dati
Per modificare dati all’interno della base di dati si può
utilizzare il comando UPDATE con la seguente sintassi:
mysql> UPDATE nome_tabella
Æ SET nome_col1=espressione1 [,
nome_col2=espressione2,…]
Æ [WHERE condizione];
Esempio:
mysql> UPDATE PRESTITO
Æ SET dataresa=‘2005/11/29’
Æ WHERE collocazione=‘INF1’ AND codiceutente=15
and dataprestito=’ 2005/10/15’;
22
11
Interrogazioni in MySQL
Interrogazioni semplici
Selezionare tutti dati relativi ai clienti:
mysql> SELECT * from utente;
Selezionare solo alcune colonne, ad esempio titolo
e autore dei libri presenti in biblioteca:
mysql> SELECT titolo, autore from
datilibro;
Selezionare alcune righe usando la condizione
WHERE. Ad esempio selezionare i titoli dei libri
della Mc-Graw Hill:
mysql> SELECT titolo from datilibro
Æ Where casaeditrice = ‘mc-Graw Hill’;
24
12
Interrogazioni semplici
Utilizzo del costrutto COUNT. Esempio: visualizzare
quanti prestiti ci sono stati nel mese di ottobre
2005
mysql> SELECT count(*) from prestito
Æ Where dataprestito >= ’2005/10/01’ and
dataprestito <= ’2005/10/31’;
Utilizzo del costrutto GROUP BY. Esempio:
visualizzare quanti prestiti per libro ci sono stati
mysql> SELECT count(*) as numero
prestito, ISBN from prestito
Æ Group by ISBN;
25
Interrogazioni avanzate
Utilizzo di join. Esempio: trovare i titoli dei libri di informatica presi a
prestito nel giugno 2005
mysql> SELECT titolo from prestito join libro join
datilibro
Æ Where prestito.collocazione = libro.collocazione and
libro.ISBN = datilibro.ISBN and dataprestito >= ’
2005/06/01/’and dataprestito <= ’ 2005/06/30’;
Utilizzo di query annidate. Esempio: Trovare i codici e i nomi degli
utenti che non hanno mai preso in prestito libri di Informatica della McGraw Hill
mysql>SELECT codice, nome, cognome from utente Where
codice not in
Æ(SELECT codice utente join prestito join libro join
datilibro
Æ Where utente.codice=prestito.codiceutente and
prestito.collocazione = libro.collocazione and
libro.ISBN = datilibro.ISBN and
Æ genere= ’informatica’and casaeditrice= ’Mc-Graw Hill’;
26
13