Corso di Basi di Dati

Corso di Basi di Dati Un esempio di RDBMS: Il tool MySQL Home page del corso: http://www.cs.unibo.it/~difelice/dbsi/

MySQL: Overview del tool • MySQL DBMS basatosulmodellorelazionale (RDBMS) • Sviluppatonel 1995 della MySQL AB, dal 2008 di proprieta’ della SUN, ora di proprieta’ di Oracle Corporation. • Disponibile in diverse versioni(Enterprise Edition, Community Server, Cluster, …) • Ultimaversione: 5.6.14 (Community) • Piattaforme: Windows/Linux/Mac OSX

MySQL: Overview del tool LICENZE D’USO • MySQL e’ un tool Open Source (dal 2000). • Esistonodiversi fork del progetto (MariaDB, disponibile per GNU/Linux). • Due possibililicenze: • GNU/GPL (progetti open-source) • Commerciale, per applicazionisviluppate da OEMs, ISVs, VARs, …

MySQL: Overview del tool Database installations and deployment plans Gartner study, 2008

MySQL: Overview del tool • MySQL nelmercatodeiDBMS open source ...

MySQL: Overview del tool • Supporta gran parte deicostrutti del linguaggioSQL 2.0 (viste, query annidate, vincoli di chiave, etc), con trigger e visteaggiornabili. • Supportal’esecuzione di transazionisu un tipoparticolare di tabelle (INNODB). • Supportamoltetipologie di datinumerici, testuali (es. VARCHAR), temporali (es. DATE) e binari (es. file di dati). • Dispone di un propriolinguaggio di estensioneproceduraleper definire le stored procedures.

MySQL: Overview del tool • MySQL NON ha limitiesplicitisulladimensionemassima di un database e sulnumero di tabelle(eccetto per INNODB 4 bilioni). • Il numeromassimo di righe in unatabelladipendedaivincoliimposti dal sistemaoperativosulladimensionemassima di un file.

MySQL: Overview del tool • Non esistonoproblemi dal punto di vista dellaconcorrenza in termini di numeromassimo di connessionisimultanee al server MySQL. • max_user_connection[0: 4294967295] • … Tuttavia, iproblemiemergono –ovviamente- dal punto di vista dellerisorse (es. memoria), per cui ilnumeroeffettivo di connessionisimultaneesupportatedipendedallecapacita’ e dallerisorse hardware dell’elaboratore.

MySQL: Overview del tool • Connessione via shell: • shell> mysql –user=utente –p database • Alcuneopzioni di connessione: • -host=indirizzo (-h) • -user=utente (-u) • -password=password (-p) • -port=porta (-P) • -socket=path –(S) • -execute=istruzione (-e)

MySQL: Overview del tool • Connessione via shell: • shell> mysql –user=utente –p database • Alcuneopzioni di connessione: • -host=indirizzo (-h) • -user=utente (-u) • -password=password (-p) • -port=porta (-P) • -socket=path –(S) • -execute=istruzione (-e) shell> mysql –u root -proot shell> mysql –h localhost –u marco –P 9999 < script.sql -p

MySQL: Overview del tool • Creare un nuovoutente (locale): • mysql> CREATE USER nome@localhost; • Impostare la password di un utente: • mysql> SET PASSWORD FOR nome@localhost=PASSWORD(‘passwd’); • Creare un nuovoutente con password: • mysql> CREATE USER nome@localhost IDENTIFIED BY ‘passwd’; • Cancellare un utente: • mysql> DROP USER nome@localhost;

MySQL: Overview del tool • L’autenticazioneavvieneattraverso la combinazione: nomeutente@host. • La gestionedeidatidegliutentiavvieneattraverso la tabellamysql.User. E’ possibilelasciare vuotoil campo user. • mysql> SELECT * FROM mysql.USER;

MySQL: Overview del tool • Per vederequalidbsonopresentinelsistema: • mysql> SHOW databases; • Per impostareildbcorrente: • mysql> use nome_database; • Due modiper assegnare/rimuovere un privilegioad un determinatoutente: • Comandi SQL: REVOKE | INSERT • Aggiornaretabellemysql.user, mysql.db, mysql.tables_priv, attraverso INSERT/UPDATE.

MySQL: Overview del tool MySQL supportadiversi tipi di “storage engine” (in pratica, tipi di tabelle), tra cui iprincipalisono: INNODB MyISAM • NONsupportailsistematransazionale. • Maggiore efficienza. • Minoreconsumo di spaziosumemoriasecondaria. • Supportailsistematransazionale • Supportaivincoli di chiaviesterne. • Maggiore robustezzaaiguasti.

MySQL: Overview del tool • Per creare un nuovo database: • mysql> CREATE DATABASE [IF NOT EXIST] nome_db; • Per rimuovere un database: • mysql> DROP [IF EXIST] nome_db; • In MySQL, un database e’ unasottodirectorydella directory deidati (/Applications/MAMP/db/mysql/). Aggiungere un nuovo database corrispondeallacreazione di unanuova directory.

MySQL: Overview del tool • Per creareunatabella: • CREATE [TEMPORARY] TABLE • nome_tabella | nome_db.nome_tabella • [definizioneattributi] • [opzioni] • [select] • E’ possibilegenerareunatabellavalida solo per la sessionecorrente(opzioneTEMPORARY). • E’ possibilepopolare la tabella con ilrisultato di una query SELECT da altretabelle.

MySQL: Overview del tool • Alcuneopzionivalidesulletabelle: • ENGINE = tipo_tabella (ISAM|INNODB) • AUTO_INCREMENT = valore • AVG_ROW_LENGTH = valore • CHECKSUM = { 0 | 1 } • COMMENT = stringa • MAX_ROWS= valore

MySQL: Overview del tool • Per creareunatabella: • CREATE [TEMPORARY] TABLE • nome_tabella | nome_db.nome_tabella • [definizioneattributi] • [opzioni] • [select] • E’ possibilegenerareunatabellavalida solo per la sessionecorrente(opzioneTEMPORARY). • E’ possibilepopolare la tabella con ilrisultato di una query SELECT da altretabelle.

MySQL: Overview del tool • Sintassi per specificareunacolonnadellatabella: • Nome_colonna TIPO • [NOT NULL | NULL] [DEFAULT valore] • [AUTO_INCREMENT] • [UNIQUE | PRIMARY KEY] • [COMMENT ‘commento’] • [reference_definition] Definizionedeivincoli di integrita’ referenziale

MySQL: Overview del tool • Per definireivincoli di integrita’ referenziale: • FOREIGN KEY (nome_colonna_interna) • REFERENCES nome_tabella_esterna (nome_colonna_esterna) • [ON DELETE | ON UPDATE • RESTRICT | CASCADE | SET NULL | • NO ACTION ] • Funziona con tabelle di tipo INNODB …

MySQL: Overview del tool • Sintassi per specificareunacolonnadellatabella: • Nome_colonnaTIPO • [NOT NULL | NULL] [DEFAULT valore] • [AUTO_INCREMENT] • [UNIQUE | PRIMARY KEY] • [COMMENT ‘commento’] • [reference_definition] Quali tipi di datoe’ possibilememorizzare in un DB MySQL?

MySQL: Overview del tool • Tipi di datonumericisupportati da MySQL: • BIT • TINYINT [UNSIGNED][ZEROFILL] • SMALLINT [UNSIGNED][ZEROFILL] • MEDIUMINT [UNSIGNED][ZEROFILL] • INT [UNSIGNED][ZEROFILL] • BIGINT [UNSIGNED][ZEROFILL] • FLOAT [UNSIGNED][ZEROFILL] • DOUBLE [UNSIGNED][ZEROFILL] • DECIMAL [UNSIGNED][ZEROFILL]

MySQL: Overview del tool • Tipi di datotemporalisupportati da MySQL: • DATE • DATETIME • TIMESTAMP [M] • TIME • YEAR [(2,4)] • Per conosceredata/timestamp da shell: • mysql> SELECT NOW(); • mysql> SELECT CURTIME();

MySQL: Overview del tool • (Alcuni) Tipi di datostringa di caratteri o byte: • CHAR(M) [BINARY | ASCII | UNICODE] • VARCHAR(M) [BINARY] • BINARY(M) • VARBINARY(M) • TINYBLOB • TINYTEXT • BLOB(M) • TEXT(M) • LONGBLOB

MySQL: Overview del tool Esempio di creazione di unatabellain MYSQL CREATE TABLE Impiegati ( codicesmallint not null auto_increment primary key, nomevarchar(200) not null, cognomevarchar(100) not null, salario double default 1000, anno date) engine=innodb;

MySQL: Overview del tool • Popolamento di datiattraversol’INSERT : • INSERT [LOW_PRIORITY | DELAY | HIGH_PRIORITY] [IGNORE] • [INTO] nome_tabella [(nome_colonne,…)] • VALUES ({espressione | DEFAULT}, …) • [ON DUPLICATE KEY • UPDATE nome_colonna=espressione, …] • E’ possibilespecificareunapriorita’dell’inserimento • deidati, nelcaso in cui la tabellasiausata da altri.

MySQL: Overview del tool • Popolamento di datiattraverso la REPLACE: • REPLACE [LOW_PRIORITY | DELAYED] • [INTO] nome_tabella [(nome_colonna, …)] • VALUES ({espressione | DEFAULT}, …) • Estensione (MySQL) del costrutto di INSERT. • Consente di rimpiazzaredellerighepresistenti con • dellenuoverighe, qualorasiverifichi un problema • di inserimentocon chiavedoppia.

MySQL: Overview del tool • Popolamento di datiattraverso la LOAD: • LOAD DATA [LOCAL] INFILE ‘file.txt’ • [REPLACE | IGNORE] • INTO TABLE nome_tabella • [FIELDS • [TERMINATED BY ‘stringa’] • [ENCLOSED BY ‘stringa’] • [ESCAPED BY ‘stringa’] ] • [LINES • [STARTING BY ‘stringa] • [TERMINATED BY ‘stringa’]] • [IGNORE numero LINES] Popolo la tabella apartiredaidatipresenti in “file.txt”, specificando iseperatoridellecolonne edeventualmente le righe da filtrare …

MySQL: Overview del tool • Ricerca di datiattraverso la SELECT: • SELECT [ALL | DISTINCT | DISTINCTROW] • lista_colonne • [INTO OUTFILE ‘nome_file’ | • INTO DUMPFILE ‘nome_file’ ] • FROM lista_tabelle • [WHERE condizione] • [GROUP BY {nome_colonna}] • [HAVING condizione] • [ORDER BY {nome_colonna}] • [LIMIT [offset,] numero_righe]

MySQL: Overview del tool • Cancellazione di datiattraverso la DELETE: • DELETE [LOW_PRIORITY][IGNORE][QUICK] • FROM nome_tabella • [WHERE condizione] • [LIMIT numero_righe] • Azzerare la tabellaattraversola TRUNCATE: • TRUNCATE nome_tabella • Aggiornamento di datiattraversol’UPDATE: • UPDATE [LOW_PRIORITY][IGNORE] • SET {nome_colonna=espressione, ….} • WHERE condizione

MySQL: Overview del tool • Creazionedi regoleattiveattraversoTRIGGER • CREATE TRIGGER nometipo • ON tabella FOR EACH ROW istruzioniSQL • Iltipospecifical’eventocheattivail trigger: • BEFORE INSERT • BEFORE UPDATE • BEFORE DELETE • AFTER INSERT • AFTER UPDATE • AFTER DELETE

MySQL: Overview del tool Esempio di definizione di trigger in MYSQL CREATE TRIGGER upd_check BEFORE INSERT ON Impiegati FOR EACH ROW BEGIN IF NEW.Salario > 300 THEN SET NEW.Salario=300; END IF; END;

MySQL: Overview del tool • Creazionedi visteattraversoilcomando VIEW • CREATE [OR REPLACE] • [ALGORITHM = (UNDEFINED | MERGE | TEMPTABLE) • VIEW nome [(listacolonne)] • AS selectSQL • [WITH [CASCADED|LOCAL] CHECK OPTION] • E’ possibiledefinirevisteaggiornabili(attraverso la clausolaWITH CHECK OPTION). • E’ possibilespecificarel’algoritmo per costruire la vista (es. MERGEconsente di crearevisteparametrizzate).

MySQL: Overview del tool • Creazionedi stored procedures in MySQL: • CREATE PROCEDURE nomeProcedura • ({[IN|OUT] nomeParametrotipo) • BEGIN • [dichiarazione di variabililocali] • [istruzioni SQL] • END; • Insieme di istruzioni SQL memorizzatenel DBMS, cui e’ associato un nomeunivoco. • Puo’ ricevereparametri in input, puo’ restituirepiu’ di un valore in output. Il corpocontieneistruzioni SQL

MySQL: Overview del tool Esempio di definizione di stored procedure in MYSQL CREATE PROCEDURE nomeImpiegato (IN cod INT, OUT nomeI VARCHAR(200)) BEGIN SELECT NOME AS NOMEI FROM IMPIEGATI WHERE (CODICE=cod); END; mysql > CALL nomeImpiegato(200,@var); mysql > SELECT @var;

MySQL: Overview del tool • Dichiarazione di variabililocali: • DECLARE a INT DEFAULT 0; • Costrutti di selezione(IF THEN ELSEIF ELSE): • IF Condizione THEN • IstruzioniSQL • [ELSE IstruzioniSQL] • ENDIF; • Costruttiiterativi(WHILE/LOOP/REPEAT): • [nome] WHILE Condizione DO • IstruzioniSQL • END WHILE [nome];

MySQL: Overview del tool • Dichiarazione di cursori di query SQL: • DECLARE nomeCursore CURSOR FOR selectSQL; • OPEN nomeCursore • FETCH nomeCursore INTO nomeVariabili; • CLOSE nomeCursore • I cursoriconsentono di eseguire query SQL e salvareilrisultato (result set) in unalista. • La listarisultantepuo’ essereiteratamentevisitataattraversoilcomando di FETCH.

MySQL: Overview del tool Esempio di di stored procedure con cursoriin MYSQL CREATE PROCEDURE nomeImpiegato (IN salarioMax INT, OUT valido BIT) BEGIN DECLARE fine INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT salario FROM IMPIEGATI; DECLARE CONTINUE HANDLER FOR NOT FOUND SET fine=1;

MySQL: Overview del tool Esempio di stored procedure con cursoriin MYSQL SET valido=1; OPEN cur; ciclo: WHILE NOT fine DO FETCH cur INTO salarioCor; IF salarioCor > salarioMax THEN valido=0; END IF; END WHILE ciclo; END;

MySQL: Overview del tool • Gestionedelletransazioni per tabelleINNODB: • Di default, la modalita’ autocommit e’ abilitata, quindituttigli aggiornamenti sonoeffettuatiimmediatamentesul database. • Nelcaso in cui gliautocommitsianodisabilitati, e’ necessarioindicarel’iniziodellatransazione (START TRANSACTION) e terminarla con un comando di COMMIT o ROLLBACK.

MySQL: Overview del tool Esempio di transazionein MYSQL SET AUTOCOMMIT = 0; START TRANSACTION INSERT INTO IMPIEGATO (Nome, Cognome, Salario) VALUES (‘Michele’,’Rossi’,1200); INSERT INTO IMPIEGATO (Nome, Cognome, Salario) VALUES (‘Carlo’,’Bianchi’,1000); COMMIT

MySQL: Overview del tool • MySQL offrequattrolivelli di isolamento: • READ UNCOMMITTED  sonovisibiligli aggiornamenti non consolidatifatti da altri. • READ COMMITTED  aggiornamenti visibili solo se consolidati (ossia solo dopoCOMMIT). • REPEATABLE READ  tutte le letture di un dato operate da unatransazioneleggonosempre lo stessovalore (comportamento di default). • SERIALIZABLE  lettura di un datobloccagli aggiornamenti fino al terminedellatransazionestessache ha lettoildato (lock applicato ad ogniSELECT).

MySQL: Overview del tool • MySQL offrequattrolivelli di isolamento: • READ UNCOMMITTED  sonovisibiligli aggiornamenti non consolidatifatti da altri. • READ COMMITTED  aggiornamenti visibili solo se consolidati (ossia solo dopoCOMMIT). • REPEATABLE READ  tutte le letture di un dato operate da unatransazioneleggonosempre lo stessovalore (comportamento di default). • SERIALIZABLE  lettura di un datobloccagli aggiornamenti fino al terminedellatransazionestessache ha lettoildato (lock applicato ad ogniSELECT). SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPETEABLE READ | SERIALIZABLE }

MySQL: Overview del tool • Il tool mysqldumpconsente di effettuarebackup del contenuto di un database (o di tutti). Backup di tuttii database con tabelle INNODB mysqldump –single-transaction –all-database > nomefile Backup di unospecifico database con tabelle INNODB mysqldump –single-transaction nomedb > nomefile Ripristino di un database (o tutti) da un file di backup mysql [nomedb] < nomefile

MySQL: Overview del tool • L’installazione di MySQL include moltiprogrammi di utilita’: • mysqladmin  Amministrazione di MySQL • mysqlcheck  Check di tabelle • mysqldump Dump di database su file • mysqlshow  Mostra la struttura di tabelle • myisampack  Comprime le tabellesu disco di tipoMyISAM (tabelle in sola lettura) • …

MySQL: Overview del tool • Il tool MySQL Workbench fornisceuna GUI (Graphical User Interface) all’utilizzodi MySQL. • TOOL INTEGRATO: • Amministrazione di database • Sviluppo di SQL • Modellazione e progettazione

MySQL: Overview del tool • PrincipalidifferenzetraMySQL e Oracle

Corso di Basi di Dati