database - INFN-LNF

Stage Estivo
D A T A B A S E
Claudio Bisegni
[email protected]
http://www.lnf.infn.it/~bisegni
introduzione
l’informatica nella gestione dati
•
•
•
•
sistemi informativi, applicazioni bancarie ecc.
un sistema organizzato di dati
grande quantità di dati
efficienza nella gestione
introduzione
perché usiamo un database
•
•
•
•
memorizzare su Database o File
accesso multi utente
accessi in concorrenza lettura/scrittura
velocità di accesso ai dati
introduzione
perché usiamo un database
Schema di un sistema
informativo
introduzione
progettare e usare un database
•
•
analisi dei dati
analisi delle relazioni tra i dati
introduzione
ipotesi di un progetto
•
dati da gestire
•
relazioni logiche dei dati
•
•
•
•
•
Alunni
Classi
Gruppi Culturali (Sport, Scienza, Musica, ecc…).
Un alunno potrà essere associato ad una sola classe.
Un alunno potrà essere associato a nessuno, uno o molti gruppi.
introduzione
Primary Key & Foreign Key
•
Primary Key - identifica univocamente il record
in una tabella
•
•
•
uno o più colonne possono essere usate come primary key
solo valori univoci (ovvio)
Foreign Key - definisce una relazione tra due
tabelle
•
•
•
uno o più colonne possono essere usate come foreign key
la relazione tra due tabelle può avere una o più foreign key
una tabella può essere relazionata a una o più tabelle o con se
stessa
introduzione
Primary Key & Foreign Key
•
PK & FK nella relazioni logiche dei nostri dati
•
•
Un alunno potrà essere associato ad una sola classe.
Un alunno potrà essere associato a nessuno, uno o molti gruppi.
•
•
•
PK - campo id di ogni tabella
FK - alunno: alunno.id_classe verso classe.id
FK - alunno_gruppo: alunno_gruppo.id_alunno verso alunno.id
alunno_gruppo.id_gruppo verso gruppo.id
Linguaggio SQL
Linguaggio SQL (mysql)
introduzione
• SQL Structured Query Language
• • diviso in tre sottoinsiemi
Linguaggio per l’accesso ai database
•
•
DDL - Data Definition Language - creazione e modifica dei database
DML - Data Manipulation Language - inserimento modifica e
cancellazione dei dati
DCL - Data Control Language - gestione utenti e permessi
Linguaggio SQL (mysql)
creazione database
•
connessione come utente root:
mysql –u root –p
•
creazione di un database:
CREATE DATABASE nome_database;
•
cancellazione di un database:
DROP DATABASE nome_database;
Linguaggio SQL (mysql)
creazione e gestione utente
•
connessione come utente root:
mysql –u root –p
•
creazione di un utente:
•
rimozione di un utente:
•
gestione privilegi utente:
CREATE USER ‘nome_utente’@‘nome_host’ IDENTIFIED BY ‘pwd’;
DROP USER ‘nome_utente’@‘nome_host’;
GRANT tipo_privilegio ON {nome_tabella | * | *.* | nome_db.*} TO
‘nome_utente’@‘nome_host’;
REVOKE tipo_privilegio ON {nome_tabella | * | *.* | nome_db.*}
FROM ‘nome_utente’@‘nome_host’;
Linguaggio SQL (mysql)
creazione tabelle
•
creazione di una tabella:
CREATE TABLE ‘nome_tabella’ (
nome_colonna1 parametri,......, nome_colonna1
parametri) TYPE=tipo_tabella
parametri:
• tipo_dato
[NOT NULL | NULL]
[DEFAULT valore_di_default]
[AUTO_INCREMENT]
[UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'commento']
Linguaggio SQL (mysql)
creazione tabelle
•
tipo dato:
BIT[(lunghezza)]
INT[(lunghezza)] [UNSIGNED] [ZEROFILL]
INTEGER[(lunghezza)] [UNSIGNED] [ZEROFILL]
BIGINT[(lunghezza)] [UNSIGNED] [ZEROFILL]
REAL[(lunghezza,decimali)] [UNSIGNED] [ZEROFILL]
DOUBLE[(lunghezza,decimali)] [UNSIGNED] [ZEROFILL]
FLOAT[(lunghezza,decimali)] [UNSIGNED] [ZEROFILL]
DECIMAL(lunghezza,decimali) [UNSIGNED] [ZEROFILL]
NUMERIC(lunghezza,decimali) [UNSIGNED] [ZEROFILL]
DATE
TIMESTAMP
VARCHAR(lunghezza)
[CHARACTER SET charset_name] [COLLATE collation_name]
BLOB
TEXT [BINARY]
Linguaggio SQL (mysql)
creazione tabelle
•
tipo tabella:
•
•
•
MYSQ mette a disposizione vari tipi di algoritmi per costruire tabelle
nel manuale del corso viene usata la tipologia “innodb”
innodb supporta:
•
•
•
•
le transazioni
row locking
foreign key
indici:
•
•
possono essere creati per ogni colonna
•
se ben gestiti migliorano le prestazioni di ricerca
consigliati solo per le colonne su cui si effettuano delle
ricerche(select)
Linguaggio SQL (mysql)
creazione indici e foreign key
•
creazione di un indice:
•
creazione foreign key
ALTER TABLE tabella ADD INDEX (campo)
ALTER TABLE tabella ADD CONSTRAINT nome_foreign_key FOREIGN KEY
nome_campo REFERENCES tabella_destinazione (nome_campo) ON DELETE tipo_fk
ON UPDATE tipo_fk.
tipo_fk:
• RESTRICT, annulla tutte le operazioni di Update e Delete della
tabella_destinazione della FK
• NO ACTION, (Identica alla RESTRICT)
• CASCADE, dopo un operazione di Delete o Update alla tabella_destinazione
applica la Delete o Update ai campi corrispondenti
• SET NULL, dopo un operazione di Delete o Update alla tabella_destinazione
applica il valore nullo ai campi corrispondenti
Linguaggio SQL (mysql)
inserimento
•
inserimento di valori in tabella
INSERT INTO nome_tabella (colonna_1,colonna_2…colonna_N)
VALUES (valore_1, valore_2, …., valore_N)
Linguaggio SQL (mysql)
ricerca
•
ricerca dei dati in tabella
•
SELECT [DISTINCT]
campo_1[,campo_2...]
FROM nome_tabella AS alias
[WHERE condizione_di_ricerca]
[ORDER BY condizione_di_ordinamento [ASC | DESC]]
•
•
campo_N - nome del campo della tabella d visualizzare.
[nome_tabella,alias]. campo_N - forma alternativa per la selezione del
campo da visualizzare (indispensabile nella join).
alias - nome alternativo della tabella, indispensabile nelle join
•
•
•
condizione_di_ricerca - permettono di impostare delle condizioni che
filtrino i dati desiderati es: campo_1 = valore o campo_1 like ‘%nf%’. Si può
specificare se in ordine ascendente o discendente.
like - permette di definire dei valori con i caratteri jolly:
• % - n caratteri qualsiasi
• _ - un carattere qualsiasi
Linguaggio SQL (mysql)
ricerca
•
ricerca dei dati in più tabelle (join)
possibilità di ricercare dati mostrando il risultato combinando campi di diverse
tabelle
SELECT [DISTINCT]
campo_1[,campo_2...]
FROM nome_tabella AS alias [, nome_tabella_2 AS alias_2,...]
[WHERE condizione_di_ricerca]
[ORDER BY condizione_di_ordinamento [ASC | DESC]]
•
•
condizione_di_ricerca - simile alla select tradizionale, in questo caso viene
aggiunta la condizione che mette in uguaglianza i campi FK di una tabella con
i campi PK di un altra, es: alias.nome_campo_fk = alias2.nome_campo_pk
condizione_di_ordinamento - identica alla select tradizionale
Linguaggio SQL (mysql)
modifica
•
modifica dei dati in tabella
UPDATE nome_tabella
SET colonna_1 = valore_1 [, colonna_2 = valore_2 ...]
[WHERE condizione]
condizione - identica alla condizione della select
Linguaggio SQL (mysql)
cancellazione
•
cancellazione dei dati in una tabella
DELETE FROM nome_tabella
[WHERE condizione]
condizione - identica alla condizione della select
Linguaggio SQL (mysql)
transazioni
•
•
cosa sono:
•
permettono, momentaneamente, di modificare i dati nel database senza in realtà
modificare il reale database
•
•
•
le modifiche sono visibili solo alla propria sessione
le modifiche possono essere annullate(rollback) o salvate(commit)
le innodb sopportano le transazioni
attivare le transazioni:
•
AUTOCOMMIT attivo di default - dopo ogni comando sql viene automaticamente
eseguita la commit non esiste quindi nessuna transazione
•
•
SET AUTOCOMMIT 0 - abilitazione delle transazioni
SET AUTOCOMMIT 1 - disabilitazione delle transazioni
Fine