Laboratorio Di Basi Di Dati 02 Il D B M S My S Q L

1

A.A. 2005/2006 Basi di Dati e Laboratorio 1

Corso di Basi di Dati e Laboratorio

MySQLAlfio Ferrara - Stefano Montanelli


Introduzione

• MySQL– http://www.mysql.com– DBMS relazionale Open Source (licenza GPL)– Applicazione Client – Server– La distribuzione consiste in un server e una

applicazione client da riga di comando (mysql)– Possibilità di usare una GUI per

l’amministrazione del server (phpMyAdmin)


Caratteristiche principali

• MySQL:– Scritto in C e C++– Portabilità

• Windows [9.x, Me, NT, 2K, XP], Linux, FreeBSD[2.x, 3.x, 4.x], Mac OS X, SunOS 4.0, Solaris 2.5…

– APIs• C, C++, Eiffel, java, Perl, PHP, Python…

– Supporto al multi-threading• Possibilità di usare CPU multiple se disponibili

2


Caratteristiche principali

• MySQL:– Connettività

• TCP/IP socket, UNIX socket, Named Pipes• ODBC driver, JDBC driver

– Supporto alle transazioni mediante la modalitàInnoDB

– Scalabilità e limiti • Gestisce basi di dati con grandi quantitativi di dati.

– Suporta fino a 60.000 tabelle e 5.000.000.000 di record– Supporta fino a 64 indici in una singola tabella


MySQL e SQL

• Aderenza a SQL99– Istruzioni CREATE TABLE, ALTER TABLE– Operatori SELECT, WHERE, GROUP BY, ORDER

BY– Funzioni COUNT(), COUNT(DISTINCT...), AVG(),

STD(), SUM(), MAX(), MIN()– LEFT/RIGHT OUTER JOIN– Alias su tabelle e colonne– Istruzioni di INSERT, DELETE, UPDATE– Sotto-selezioni (dalla versione 4.1)– Viste (dalla versione 5.0)– Trigger e stored procedures (dalla versione 5.0)– UNION (dalla versione 5.0)


MySQL e SQL

• Estensioni allo standard SQL99– Istruzione REPLACE

• Sintetizza le istruzioni DELETE + INSERT

– Supporto dei tipi di dato MEDIUMINT, SET, ENUM, TEXT

– Supporto ai vincoli di attributo AUTO_INCREMENT, BINARY, NULL, UNSIGNED, ZEROFILL

– Supporto a funzioni regolari e funzioni proprietarie nell’istruzione SELECT

• TRIM, LAST_INSERT_ID, CONCAT, CAST, MOD

– Supporto agli operatori || e && per sostituire gli operatori logici OR e AND

3


MySQL e SQL

• Differenze con lo standard SQL99– Implementazione del tipo di dati VARCHAR– Assenza del costrutto INTERSECT– Assenza del costrutto EXCEPT– Utilizzo del comando REVOKE

• I privilegi su una tabella non sono automaticamente revocati con la sua eliminazioni ma è necessario utilizzare un’istruzione REVOKE esplicita

– Implementazione dei vincoli di integrità• Richiede l’utilizzo di modalità InnoDB (motore

transazionale conforme alle proprietà ACID)


Architettura di MySQL

• MySQL è basato su un’architettura multi-livello composta da un insieme di sotto-sistemi primari e di componenti di supporto

• I diversi componenti interagiscono per garantire– Lettura di query– Analisi di query– Esecuzione di query– Memorizzazione/restituzione dei risultati delle query

• Sistemi primari– Query Engine– Storage Manager – Buffer Manager – Transaction Manager – Recovery Manager

• Componenti di supporto– Process manager– Function libraries



Transaction managerBuffer manager

Recovery manager Query engine

Storage manager

Sotto-sistemi primari

Componenti di supporto

Process manager Function libraries

4



• Query engine. È composto da tre componenti– Syntax Parser

• È responsabile della decomposizione dei comandi SQL in ingresso in una forma comprensibile ai moduli deputati all’esecuzione

• Verifica la correttezza sintattica dei comandi SQL• Verifica i permessi di accesso agli oggetti richiesti

– Query Optimizer• Verifica le possibili ottimizzazioni: Individua gli indici da

utilizzare per reperire i dati richiesti nel modo più efficiente possibile

• Predispone il piano di esecuzione della query da sottoporre a execution component

– Execution Component• È responsabile dell’esecuzione del comando SQL ricevuto



• Storage manager– Ha il compito di interfacciare il sistema

operativo per soddisfare le richieste di scrittura di dati su disco in modo efficiente

• Buffer manager– Ha il compito di gestire le problematiche

relative alla memoria nelle interazioni fra queryengine e storage manager

– Utilizza politiche di query caching efficiente che garantiscono ottimi tempi di risposta



• Transaction manager– Ha il compito di gestire le questioni legate alla

concorrenza durante l’accesso ai dati– Utilizza il componente Lock manager che gestisce il

blocco dei vari oggetti per garantire l’accesso simultaneo a utenti multipli in modo consistente

• Recovery manager– Ha il compito di mantenere una copia dei dati a tutela

di possibili perdite in caso di eventi imprevisti – Mantiene il log degli eventi significativi all’interno del

DBMS

5



• Process manager– Gestisce le connessioni con i client mediante i moduli

interni di network connection management– Sincronizza i task e i processi in competizione

mediante i moduli interni di • multi-threading• thread locking• performing thread-safe operations

• Function libraries– Contiene procedure di interesse generale utilizzate da

tutti gli altri sotto-sistemi• Manipolazione di stringhe• Operazioni di ordinamento• Gestione della memoria di sistema (dipendente da SO) • I/O su file (dipendente da SO)


Installazione

• Possibilità offerte per l’installazione:– Compilazione dei sorgenti

• Prevalentemente per Linux/MacOSX• In Windows tramite VC++ 6.0

– Utilizzo di una distribuzione binaria• Disponibile sia per Linux/MacOSX sia per Windows

• Installare la versione stabile più recente– Ad oggi la versione 5.0.15


Installazione – Linux• groupadd mysql• useradd -g mysql mysql• gunzip < mysql-VERSION.tar.gz | tar -xvf –• cd mysql-VERSION• ./configure --prefix=/usr/local/mysql• make• make install• scripts/mysql_install_db • chown -R root /usr/local/mysql• chown -R mysql /usr/local/mysql/var • chgrp -R mysql /usr/local/mysql• cp support-files/my-medium.cnf /etc/my.cnf• /usr/local/mysql/bin/mysqld_safe --user=mysql &

6


Installazione – Windows

• Se si utilizza una versione 2K/XP ènecessario avere i permessi di amministratore

• Scompattare il file della distribuzione in una cartella temporanea

• Eseguire il file setup.exe e seguire la procedura di installazione

– Se si utilizza una versione 2K/XP, selezionare il server mysqld-nt

– Installare con supporto alle connessioni remote (porta predefinita di ascolto: 3306)


MySQL

• Inizialmente su MySQL sono presenti due database di sistema

– Il database MySQL che contiene tutte le impostazioni del DBMS

– Il database information_schema che contiene il dizionario dei dati

• Prima connessione al DBMS– MySQL accetta di default connessione da parte di

qualsiasi utente– L’utente root è creato automaticamente e ha

privilegi di amministrazione sul DBMS– mysql –u root –p

• L’opzione –p viene specificata se root ha una password


Connessione al server

• Aprire una shell di comando • Connessione: invocare il client MySQL

dalla cartella contenente i file binari del DBMS– shell> mysql -h nomehost -u nomeuser -p

• Disconnesione: utilizzare il comando quit– mysql> quit

7


Gestione database

• MySQL è case insensitive• Tutti i comandi necessitano di semicolon

(;) finale• Visualizzazione dei database presenti sul

server (vincolato dai privilegi dell’utente)– mysql> show databases;

• Creazione database– mysql> create database nomedatabase;

• Connessione ad un database– mysql> connect nomedb– mysql> use nomedb


Livelli dei privilegi

• Privilegi globali (*.*)– Si applicano a tutti i database presenti sul

server. Sono memorizzati nella tabella mysql.user

• Privilegi database (dbname.*)– Si applicano a tutte le tabelle presenti in

dbname. Sono memorizzati nelle tabelle mysql.host, mysql.db


Livelli dei privilegi

• Privilegi tabella (dbname.tblname)– Si applicano a tutte le colonne presenti in

tblname. Sono memorizzati nella tabella mysql.tables_priv

• Privilegi colonna (colname)– Si applicano al campo colname specificato.

Sono memorizzati nella tabella mysql.columns_priv

8


Lista dei privilegi

• ALL [PRIVILEGES]– Escluso WITH GRANT

OPTION• ALTER• CREATE• DELETE• DROP• EXECUTE• INDEX• INSERT

• LOCK TABLES• PROCESS• RELOAD (server)• SELECT• SHOW DATABASES• SHUTDOWN (server)• SUPER (kill)• UPDATE• GRANT OPTION• …


Gestione privilegiGRANT priv_type [(column_list)] […] ON {tblname | * | *.* | dbname.*} TO username

[IDENTIFIED BY [PASSWORD] 'password'] […][WITH GRANT OPTION]

REVOKE priv_type [(column_list)] […] ON {tblname | * | *.* | dbname.*} FROM username […]

Esempio:mysql> grant all on provadb.* to

provausr@localhost identified by ‘provapsw'


Gestione privilegi

• Considerazioni– E’ consentito l’uso di wildcards

• % è un carattere speciale che sostituisce qualunque stringa

– Gli utenti sono caratterizzati dall’host da cui si connettono al server

• nomeuser@nomehost– E’ possibile modificare la password di un

utente• mysql> SET PASSWORD FOR

nomeuser@nomehost = PASSWORD('newpassword');

9


Gestione e creazione tabelle

• Visualizzazione delle tabelle contenute in un database– mysql> show tables;

• Creazione di tabelle – CREATE TABLE tblname [(vincoli di creazione,…)];

• Visualizzazione della struttura di una tabella– DESCRIBE tblname;– SHOW CREATE TABLE tblname;


Vincoli di creazione tabelle

• Vincoli di creazione– colname dominio [NOT NULL | NULL]

[DEFAULT valoredefault] [AUTO_INCREMENT] [PRIMARY KEY] [vincoli di integrità] or PRIMARY KEY (colname,…) or INDEX [indexname] (colname,…) or UNIQUE [INDEX] [indexname] (colname,…) or FOREIGN KEY [indexname] (colname,…)

[vincoli di integrità]


Tipi supportati

• SMALLINT[(length)] [UNSIGNED] [ZEROFILL]

• INT[(length)] [UNSIGNED] [ZEROFILL]

• INTEGER[(length)] [UNSIGNED] [ZEROFILL]

• REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]

• DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]

• FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]

• DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]

• CHAR(length) [BINARY] • VARCHAR(length)

[BINARY] • DATETIME• BLOB• TEXT• ENUM(value1,value2,…) • SET(value1,value2,...)

10


Vincoli di integrità

• Vincoli di integrità– REFERENCES tblname [(colname,…)] [ON DELETE opzioni] [ON UPDATE opzioni]

• Opzioni sui vincoli– RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

I vincoli di integrità in MySQL sono supportati solo in modalità INNODB


Esempi di CREATE TABLE

• Esempi– create table utente(login varchar(10), psw varchar(10), nome varchar(20), cognome varchar(20), email varchar(60), admin enum('n','y'), PRIMARY KEY (login));

– create table genere(id int(4) auto_incrementPRIMARY KEY, genere varchar(60), sottogenere varchar(60));

– create table brano(id int(4) auto_incrementPRIMARY KEY, titolo varchar(60), data date, rating enum('1','2','3','4','5'), genere int(4));

– create table album(album int(4) not nullreferences brano(id), brano int(4) not nullreferences brano(id), primary key(album,brano));


ALTER, INSERT

• Modifica alla struttura di tabelle– ALTER [IGNORE] TABLE tblname specifiche

[specifiche,…]

• Inserimento di istanze in tabella– INSERT [IGNORE] [INTO] tblname [(colname,…)]

VALUES ((expression | DEFAULT),…),(…)[ON DUPLICATE KEY UPDATE colname = expression,…]

11


Specifiche di ALTER

• Specifiche di ALTERADD [COLUMN] vincoli_creazione

[FIRST | AFTER] colname] or ADD INDEX [indexname] (colname,…) or ADD PRIMARY KEY (colname,…) or ADD UNIQUE [indexname] (colname,…)or ADD FOREIGN KEY [indexname] (colname,…)

[vincoli di integrità]or ALTER [COLUMN] colname

{SET DEFAULT value | DROP DEFAULT} or CHANGE [COLUMN] old_colname

vincoli di creazione [FIRST|AFTER colname] or MODIFY [COLUMN] vincoli di creazione

[FIRST | AFTER colname] or DROP [COLUMN] colnameor DROP PRIMARY KEY or DROP INDEX indexname


DELETE, UPDATE

• Cancellazione di istanze– DELETE FROM tblname [WHERE condizione] [ORDER BY …] [LIMIT rows]

• Aggiornamento di istanze– UPDATE [IGNORE] tblname

SET colname1=expr1 [colname2=expr2, …] [WHERE condizione] [ORDER BY …] [LIMIT rows]


INDEX e DROP

• Creazione di indici– CREATE [UNIQUE] INDEX indexname ON tblname(colname[(length)],…)

• Drop database– DROP DATABASE dbname

• Drop table– DROP TABLE tblname [tblname,…]

[RESTRICT | CASCADE]

• Drop index– DROP INDEX indexname ON tblname

12


ESEMPI• INSERT INTO utente VALUES ('utentenew',

'utentenew', 'nome utente', 'cognome utente', '[email protected]', 'n');

• INSERT genere (genere) VALUES ('jazz'), ('blues');• ALTER TABLE file ADD numerodownload int(4) AFTER

proprietario;• ALTER TABLE brano ADD FOREIGN KEY (genere)

REFERENCES genere(id);• UPDATE genere SET genere = 'rock',

sottogenere = 'metal' WHERE (genere = 'jazz');• DELETE FROM genere WHERE genere = 'blues';• CREATE INDEX idx_cognome ON utente(cognome);• DROP INDEX idx_cognome ON utente;


Backup

• È possibile eseguire delle copie di backupdei database presenti su un server MySQL– mysqldump [options] [dbname] > outputfile.sql

– Opzioni utili• --all-databases

esegue il backup di tutti i database presenti sul server MySQL

• -u nomeuser

l’utente con cui eseguire l’accesso al server MySQL (implica l’uso dell’opzione –p se nomeuserha impostato una password d’accesso)


Ripristino

• È possibile ripristinare un database a partire da un file precedentemente salvato– mysql [options] [dbname] < inpufile.sql

– dbname non è necessario in caso di database multipli nel file di input

– Opzioni utili• -u nomeuser

l’utente con cui eseguire l’accesso al server MySQL (implica l’uso dell’opzione –p se nomeuserha impostato una password d’accesso)

Laboratorio Di Basi Di Dati 02 Il D B M S My S Q L

Technology

Transcript of Laboratorio Di Basi Di Dati 02 Il D B M S My S Q L