Tutorial MySQL per principianti
Alla base del nostro universo digitale si trova una mole di dati in costante aumento. Elementi centrali di Internet e del mondo interconnesso sono i Database Management System come MySQL che consentono di elaborare elettronicamente grandi quantità di dati, di memorizzarli logicamente e di conservarli permanentemente. Così le banche dati complesse vengono suddivise in quantità più piccole e messe in relazione tra di loro, se necessario. Nel nostro tutorial MySQL per principianti vi forniamo le basi per la gestione dei database e vi indichiamo grazie a degli esempi come ottimizzare la gestione del vostro progetto web con MySQL.
Che cos’è MySQL?
MySQL fa parte dei sistemi di gestione di basi di dati relazionali più popolari al mondo, insieme a Oracle e Microsoft SQL Server (trovate una panoramica recente sui DBMS su db-engines.com). Il software, sviluppato nel 1994 dall’azienda svedese MySQL AB, è oggi sotto l’ala protettrice della Oracle Corporation e viene gestito sulla base di un sistema di licenza duale: oltre a una variante Enterprise proprietaria, Oracle ne mette a disposizione anche un’altra con licenza GPL e quindi open source. Questa doppia licenza dà alle aziende la possibilità di sviluppare delle proprie applicazioni utilizzando MySQL senza che queste debbano sottostare alla licenza open source. Nella community open source l’acquisto di MySQL da parte di Oracle ha sollevato però prevalentemente delle critiche. MySQL è scritto in C e C++ e dispone di un parser SQL basato su Yacc con un tokenizer autosviluppato (scanner lessicale). Il Database Management System si contraddistingue inoltre per un ampio supporto del sistema operativo.
L’abbreviazione SQL sta per “Structured Query Language”, un linguaggio informatico che serve per la gestione delle strutture di database. Tra le operazioni possibili con questo linguaggio rientrano la ricerca, l’inserzione, l’aggiornamento e l’eliminazione di banche dati.
MariaDB: il fork di MySQL con potenziale
L’integrazione del progetto MySQL nel portfolio dei prodotti Oracle si è imbattuta soprattutto nei sospetti e nelle critiche degli sviluppatori. Ciò dipende innanzitutto dal fatto che le differenze tra una versione MySQL con licenza GPL e il prodotto Enterprise a pagamento sono in costante aumento. Le nuove funzioni del Database Management System sono sempre più spesso rilasciate solo per la versione proprietaria. I database degli errori non resi pubblici e i test necessari che, invece, vengono trascurati, fanno pensare alla community che si dia poca rilevanza al progetto open source, passato sotto l’ala protettrice del software Goliath della Oracle; ne consegue così un distacco della community.
Già dal 2009 il team principale degli sviluppatori intorno all’ideatore di MySQL Michael “Monty“ Widenius ha voltato le spalle al popolare sistema di database e ha avviato con MariaDB un fork open source del software. Alla fine del 2012 sono state introdotte con Fedora, OpenSUSE, Slackware e Arch Linux le prime distribuzioni Linux che prevedevano il passaggio da MySQL a MariaDB nell’installazione standard. Innumerevoli progetti open source e illustri aziende di software e piattaforme web hanno seguito il loro esempio, come Mozilla, Ubuntu, Google, Red Hat Enterprise Linux, Web of Trust, Team Speak, la Wikimedia Foundation e il software XAMPP, di cui si tratterà in seguito.
Già da ora emerge che MariaDB, a differenza della versione MySQL open source, viene costantemente sviluppata. Tutti gli indizi lasciano perciò supporre che il fork supererà ben presto il progetto da cui ha preso le mosse.
Già dal 2009 il team principale degli sviluppatori intorno all’ideatore di MySQL Michael “Monty“ Widenius ha voltato le spalle al popolare sistema di database e ha avviato con MariaDB un fork open source del software. Alla fine del 2012 sono state introdotte con Fedora, OpenSUSE, Slackware e Arch Linux le prime distribuzioni Linux che prevedevano il passaggio da MySQL a MariaDB nell’installazione standard. Innumerevoli progetti open source e illustri aziende di software e piattaforme web hanno seguito il loro esempio, come Mozilla, Ubuntu, Google, Red Hat Enterprise Linux, Web of Trust, Team Speak, la Wikimedia Foundation e il software XAMPP, di cui si tratterà in seguito.
Già da ora emerge che MariaDB, a differenza della versione MySQL open source, viene costantemente sviluppata. Tutti gli indizi lasciano perciò supporre che il fork supererà ben presto il progetto da cui ha preso le mosse.
Nell’ambito dei software viene indicato come fork un ramo di sviluppo che nasce dalla divisione di un progetto (il più delle volte open source). Un fork si basa sul codice sorgente del progetto principale e lo sviluppa in un altro progetto indipendente.
Sistemi di database
Oggigiorno la gestione elettronica dei dati avviene per la maggior parte nei sistemi di database (DBS), che si compongono principalmente di due componenti: il database stesso (DB) e il Database Management System (DBMS), necessario alla sua gestione.
- Il Database Management System: il DBMS è un software come MySQL che serve per la gestione del DBS. Tra i compiti di questo software di gestione rientrano la strutturazione dei dati secondo un modello di banca dati predefinito. Così il DBMS controlla i permessi di scrittura e lettura sul database, gestisce grandi quantità di dati e gli accessi simultanei al database, oltre che occuparsi che vengano rispettate le linee guida per l’integrità, la protezione e la sicurezza dei dati.
- Il database: un database è formato da banche dati correlate tra loro a livello contenutistico, ad esempio i dati del cliente o del CMS. Un DBMS può gestire contemporaneamente più database.
Il database relazionale
Per definizione MySQL è un DBMS relazionale. Ciò significa che la gestione dei dati si basa su un modello di database tabellare. Tutti i dati che MySQL elabora vengono salvati nelle tabelle che si possono mettere in relazione tramite chiavi (keys).
Chiariamo questo concetto grazie a un semplice esempio. Di seguito si trovano le due tabelle autori e opere:
Chiariamo questo concetto grazie a un semplice esempio. Di seguito si trovano le due tabelle autori e opere:
Tutte le tabelle di un database relazionale sono composte da colonne e righe. Ogni colonna di una tabella rappresenta un attributo specifico. Nella tabella autori si trovano ad esempio gli attributi id, nome e cognome. Le righe di una tabella vengono indicate come rows e contengono ognuna dei record. Un record viene solitamente identificato univocamente, ovvero numerato, tramite una chiave primaria. Al momento della creazione della tabella viene definito quale attributo funge da chiave primaria. Il prerequisito è che la chiave primaria consenta un’assegnazione univoca. Così ogni chiave primaria può essere utilizzata una sola volta all’interno di una colonna. Si consiglia una numerazione tramite ID.
La tabella opere presenta oltre alla chiave primaria id_opere, anche id_autori come chiave esterna (foreign key) e crea così una relazione tra le tabelle, consentendo di collegare i record di una tabella con quelli dell’altra. Se si mettono in relazione due tabelle di un database relazionale, si parla di un join, che si può realizzare tramite la seguente interrogazione del database: “Ricerca tutte le opere dell’autore John Ronald Reuel Tolkien comprensive dell’anno di pubblicazione”.
La tabella opere presenta oltre alla chiave primaria id_opere, anche id_autori come chiave esterna (foreign key) e crea così una relazione tra le tabelle, consentendo di collegare i record di una tabella con quelli dell’altra. Se si mettono in relazione due tabelle di un database relazionale, si parla di un join, che si può realizzare tramite la seguente interrogazione del database: “Ricerca tutte le opere dell’autore John Ronald Reuel Tolkien comprensive dell’anno di pubblicazione”.
Tolkien è elencato nella tabella autori con la chiave primaria id_autori. Per ricercare tutte le opere dell’autore, viene utilizzato nella tabella opere come chiave esterna. Vengono così richiamate tutte le rows, che sono collegate con id_autori 1.
Abitualmente le operazioni del database MySQL vengono realizzate ricorrendo ai comandi SQL standard come SELECT, INSERT, UPDATE e DELETE. Approfondiremo questi comandi nei prossimi paragrafi del nostro tutorial MySQL.
Ovviamente si sarebbero potuti salvare i dati sui due autori e le loro opere in un'unica tabella. Una gestione dei dati di questo tipo comporta che un database contenga una grande quantità di voci ridondanti, perché ad esempio i dati delle colonne nome e cognome sarebbero dovuti essere presentati singolarmente per ogni opera. Una ridondanza simile non sovraccarica solo la memoria, ma porta anche al fatto che debbano essere effettuati degli aggiornamenti in più parti del database. Lavorando con i sistemi di database relazionali, ci si limita perciò a rappresentare un dato per tabella. In questo caso si parla di una normalizzazione dei dati.
Il campo di applicazione principale di MySQL è la memorizzazione dei dati nell’ambito delle pagine web dinamiche. La combinazione di MySQL con il web server Apache e il linguaggio di scripting PHP o Perl si è affermato come struttura software classica nello sviluppo web. Lo stack per il web si può realizzare con tutti i sistemi operativi comuni per il server, prendendo quindi il nome di LAMP (Linux), MAMP (macOS) o WAMP (Windows).
Per i principianti di MySQL consigliamo di utilizzare l’ambiente di test locale XAMPP, così da accumulare delle prime esperienze con il sistema di gestione di database. Da notare però che la sua ultima versione si basa su MariaDB.
Abitualmente le operazioni del database MySQL vengono realizzate ricorrendo ai comandi SQL standard come SELECT, INSERT, UPDATE e DELETE. Approfondiremo questi comandi nei prossimi paragrafi del nostro tutorial MySQL.
Ovviamente si sarebbero potuti salvare i dati sui due autori e le loro opere in un'unica tabella. Una gestione dei dati di questo tipo comporta che un database contenga una grande quantità di voci ridondanti, perché ad esempio i dati delle colonne nome e cognome sarebbero dovuti essere presentati singolarmente per ogni opera. Una ridondanza simile non sovraccarica solo la memoria, ma porta anche al fatto che debbano essere effettuati degli aggiornamenti in più parti del database. Lavorando con i sistemi di database relazionali, ci si limita perciò a rappresentare un dato per tabella. In questo caso si parla di una normalizzazione dei dati.
Il campo di applicazione principale di MySQL è la memorizzazione dei dati nell’ambito delle pagine web dinamiche. La combinazione di MySQL con il web server Apache e il linguaggio di scripting PHP o Perl si è affermato come struttura software classica nello sviluppo web. Lo stack per il web si può realizzare con tutti i sistemi operativi comuni per il server, prendendo quindi il nome di LAMP (Linux), MAMP (macOS) o WAMP (Windows).
Per i principianti di MySQL consigliamo di utilizzare l’ambiente di test locale XAMPP, così da accumulare delle prime esperienze con il sistema di gestione di database. Da notare però che la sua ultima versione si basa su MariaDB.
Installazione del Database Management System
Per farvi comprendere meglio le basi MySQL, utilizzeremo degli esempi concreti. La nostra introduzione a MySQL si serve dell’ambiente di test XAMPP. Gli snippet del codice e gli screenshot si concentrano sulle operazioni del database che sono state realizzate tramite PHP grazie a un server Apache in locale su un computer Windows. Al posto del classico database MySQL viene utilizzato il fork MariaDB. Al momento i due sistemi di database dimostrano una totale compatibilità per quanto riguarda l'esecuzione delle operazioni. Per voi, in quanto utenti finali, nell’ambito di un tutorial per principianti non fa differenza lavorare con un database MySQL o MariaDB. Come installare un simile ambiente di test locale sul vostro computer Windows è l’argomento principale del nostro tutorial su XAMPP. Se volete fare un po’ di gavetta per imparare a usare i database relazionali, vi consigliamo di optare direttamente per MariaDB. Un ambiente di test alternativo basato su MySQL è disponibile gratuitamente con AMPPS. Inoltre c’è la possibilità di impostare uno stack personalizzato. Se necessario, MySQL e MariaDB si possono combinare con diversi sistemi operativi, web server e linguaggi di scripting. I pacchetti di download gratuiti, con licenza GPL, sono disponibili su mysql.it e mariadb.com. Trovate una guida dettagliata per l’installazione sulle diverse piattaforme nella documentazione in inglese di MySQL e MariaDB.
Gestione del database con phpMyAdmin
Nella gestione di MySQL ci imbattiamo nell’applicazione web gratuita phpMyAdmin, già contenuta nel pacchetto di installazione di XAMPP, ma che è anche disponibile separatamente sul sito del progetto ufficiale come pacchetto da scaricare. phpMyAdmin si presenta come un software standard per l’amministrazione di database MySQL nel World Wide Web. L’applicazione web scritta in PHP e JavaScript consente di eseguire operazioni di database tramite un’interfaccia grafica. Così create e gestite le tabelle del vostro database relazionale facilmente con un click sul browser. La conoscenza dei comandi SQL non è inizialmente necessaria.
Aprire phpMyAdmin
Una volta che il pacchetto software XAMPP è stato installato, avviate dal pannello di controllo il Database Management System (MySQL o MariaDB), in maniera analoga agli altri componenti dello stack di test. Per fare ciò utilizzate l’apposito pulsante “Start” sotto “Actions”. Per poter aprire phpMyAdmin dal browser, dovete avviare prima di tutto il web server Apache. I moduli attivati sono segnalati in verde nel pannello di controllo di XAMPP. In aggiunta visualizzate lo stato attuale dei moduli di XAMPP tramite degli avvisi comunicati nella finestra di testo.
XAMPP è stato sviluppato nell’ambito del progetto software Apache Friends come sistema di test compatto per essere utilizzato sul computer locale. Il pacchetto software non è pensato per mettere a disposizione i servizi web su Internet. Per via di innumerevoli limitazioni in ambito della sicurezza, XAMPP non è adatto a essere usato come sistema produttivo.
Nel funzionamento di test locale avete a disposizione l’interfaccia utente del software di amministrazione su http://localhost/phpmyadmin/.
Se avete definito nell’installazione MySQL una password per l’account root, phpMyAdmin ve la richiede in una schermata di login. Se utilizzate phpMyAdmin nell’ambito di un prodotto di web hosting, i corrispettivi dati di login vi vengono assegnati dal rispettivo provider. È raro che in questo caso si disponga dei permessi di root.
Dopo che il login è andato a buon fine, phpMyAdmin vi presenta la pagina iniziale dell’applicazione, che vi dà la possibilità di modificare le impostazioni di base per i set di caratteri (collazione) della connessione MySQL e di scegliere la modalità di visualizzazione desiderata (lingua, design e dimensione del font). Inoltre nella pagina destra trovate una panoramica dei dati più importanti del vostro server database, del web server utilizzato, così come di informazioni sulla versione attuale di phpMyAdmin. La barra del menu della pagina iniziale è strutturata sotto forma di schede, come tutte le altre dell’applicazione. Quindi si hanno a disposizione le tab Databases, SQL, Status, User accounts, Export, Import, Settings, Replication, Variables e More.
Se avete definito nell’installazione MySQL una password per l’account root, phpMyAdmin ve la richiede in una schermata di login. Se utilizzate phpMyAdmin nell’ambito di un prodotto di web hosting, i corrispettivi dati di login vi vengono assegnati dal rispettivo provider. È raro che in questo caso si disponga dei permessi di root.
Dopo che il login è andato a buon fine, phpMyAdmin vi presenta la pagina iniziale dell’applicazione, che vi dà la possibilità di modificare le impostazioni di base per i set di caratteri (collazione) della connessione MySQL e di scegliere la modalità di visualizzazione desiderata (lingua, design e dimensione del font). Inoltre nella pagina destra trovate una panoramica dei dati più importanti del vostro server database, del web server utilizzato, così come di informazioni sulla versione attuale di phpMyAdmin. La barra del menu della pagina iniziale è strutturata sotto forma di schede, come tutte le altre dell’applicazione. Quindi si hanno a disposizione le tab Databases, SQL, Status, User accounts, Export, Import, Settings, Replication, Variables e More.
Sull’angolo sinistro dell’interfaccia utente trovate il pannello di navigazione. Qui sono presentate tutte le tabelle a cui potete accedere dal vostro database grazie a phpMyAdmin. Sotto il logo del programma nell’angolo sinistro in alto, il software mostra i link alla pagina iniziale e mette a disposizione la documentazione ufficiale. Inoltre avete la possibilità di configurare il pannello di navigazione e aggiornare la sua visualizzazione.
Iniziamo il nostro corso crash MySQL, creando il nostro primo database.
Iniziamo il nostro corso crash MySQL, creando il nostro primo database.
Creare un database
Per creare un database con phpMyAdmin, selezionate per prima cosa la tab “Databases“ nella barra del menu della pagina iniziale.
Inserite il nome scelto per il vostro database nel campo apposito sotto “Create database“ (crea database) e scegliete un set di caratteri. Vi consigliamo la collazione utf8mb4_unicode_ci. Con la scelta di un set di caratteri comunicate al server database quale codifica deve essere utilizzata per i dati da inviare e da ricevere. Le varianti mb4 consentono anche l’uso di caratteri esotici, come simboli o emoticon che si trovano al di là dello standard Unicode (Basic Multilingual Plane), e sono perciò consigliate.
Confermate quanto inserito con un click su “Create“ (crea). Il database creato verrà visualizzato nel pannello di navigazione sulla pagina sinistra dello schermo. I nuovi database sono inizialmente vuoti. Per inserire dei dati, create nel prossimo passaggio una tabella.
Creare delle tabelle
Per creare una nuova tabella, selezionate il database appropriato e navigate nella barra del menu nella tab “Structure“ (Struttura).
Create una tabella, inserendo il nome (ad esempio users) e il numero di colonne richieste nell’interfaccia “Create table” (crea tabella). Ricordatevi che ogni colonna rappresenta un attributo di un record. Se avete bisogno di altre colonne, potete aggiungerle successivamente.
Se, ad esempio, volete creare un database per gli utenti del vostro sito, le seguenti voci si prestano a essere utilizzate per le colonne della tabella:
Se, ad esempio, volete creare un database per gli utenti del vostro sito, le seguenti voci si prestano a essere utilizzate per le colonne della tabella:
Colonna | Descrizione |
---|---|
id | Numero di identificazione univoco per ogni utente |
forename | Il nome dell’utente |
surname | Il cognome dell‘utente |
L’indirizzo e-mail dell‘utente | |
password | La password dell‘utente |
created_at | La data in cui è stata creata la voce |
updated_at | La data in cui è stata aggiornata la voce |
Per il vostro database per gli utenti create così una tabella users con sette colonne. Confermate quanto inserito premendo il pulsante “Go”.
Dopo che la tabella è stata creata, phpMyAdmin vi offre la possibilità di definire le voci per le colonne della tabella e di decidere le impostazioni di formato per i dati previsti.
Nella seguente tabella trovate una descrizione della struttura della tabella e delle possibili formattazioni.
Opzione | Descrizione |
---|---|
Name | A ogni colonna di una tabella del database viene assegnato un nome, che può essere scelto a piacere (seppur con alcune limitazioni). Sono ammesse le lettere latine (maiuscole e minuscole, ma senza altri caratteri speciali), cifre, il simbolo del dollaro e l’underscore. Quest’ultimo può essere utilizzato come alternativa ai caratteri non consentiti (errato: user id; giusto: user_id). Il nome delle colonne non può essere composto solo da cifre. Inoltre nel linguaggio di database SQL si trovano diverse keyword, che sono riservate per compiti precisi. Una lista di tutte le keyword è disponibile sulla documentazione di MySQL. Potete aggirare la maggior parte di queste limitazioni, ma così dovete sempre mettere sulla relativa colonna i backtick (``). Valgono le stesse regole anche per i nomi delle tabelle e gli altri nomi su. Si consiglia di utilizzare dei nomi delle colonne appropriati che si addicano all’attributo corrispondente. |
Type | Il tipo di dati indica quali dati sono salvati in una colonna. MySQL e MariaDB vi consentono di definire i dati come numeri interi e in virgola mobile, di inserire indicazioni temporali e date, oltre che stringhe testuali e dati binari. Trovate una descrizione nella tabella dei tipi di dati. |
Length/Values | Per alcuni tipi di dati (ad esempio per le stringhe testuali) potete assegnare ai valori di una colonna una lunghezza massima. Questa formattazione è però opzionale. |
Default | L’opzione “Default“ vi consente di definire un valore standard per una colonna, che viene così sempre inserito automaticamente quando un record non contiene nessun valore per la relativa colonna. |
Collation | Con l’opzione “Collation“ definite un tipo di carattere specifico per una colonna che può variare dalle impostazioni globali del database. Potete cambiare la codifica a livello tabellare anche per tutte le colonne. |
Attributes | Alcuni tipi di dati si possono specificare meglio tramite attributi opzionali. Così potete ad esempio stabilire con gli attributi signed e unsigned, se un numero intero o in virgola mobile può accettare anche valori negativi (signed) o solo positivi (unsigned). |
Index | Con l’opzione “Index“ definite le regole per l’indicizzazione. Se scegliete per una colonna l’impostazione dell’indice PRIMARY, allora questa sarà valida come chiave primaria della tabella. L’impostazione UNIQUE stabilisce che i valori all’interno di questa colonna possono essere salvati solo una volta. Così, se necessario, si possono evitare i doppioni. |
A_I | L’abbreviazione “A_I“ sta per AUTO_INCREMENT e incarica il Database Management System di conteggiare un valore automaticamente, quando non viene indicato al momento della creazione di un record. Questa opzione viene utilizzata nell’indicizzazione dei record. |
Comments | Il campo “Comments“ consente di inserire dei commenti nelle colonne delle tabelle. |
Le opzioni presentate qui comprendono le impostazioni più importanti per la formattazione delle colonne delle tabelle. Se con l’aiuto della barra di scorrimento scorrete ancora la pagina a destra, trovate altre impostazioni a cui non abbiamo fatto riferimento in questo tutorial MySQL.
La tabella qui sotto presenta i diversi tipi di dati elaborabili con MySQL e MariaDB, così come i relativi domini di valori e lo spazio richiesto.
La tabella qui sotto presenta i diversi tipi di dati elaborabili con MySQL e MariaDB, così come i relativi domini di valori e lo spazio richiesto.
Tipo | Descrizione | Dominio di valori | Spazio richiesto |
---|---|---|---|
TINYINT | Un numero intero molto piccolo | Senza segni antecedenti: da 0 fino a 255 Con segni antecedenti: da -128 fino a +127 | 1 Byte |
SMALLINT | Un numero intero piccolo | Senza segni antecedenti: da 0 fino a 65.535 Con segni antecedenti: da -32.768 fino a +32.767 | 2 Byte |
MEDIUMINT | Un numero intero di dimensione media | Senza segni antecedenti: da 0 fino a 16.777.215 Con segni antecedenti: da -8.388.608 fino a +8.388.607 | 3 Byte |
INT/INTEGER | Un numero intero di dimensione normale | Senza segni antecedenti: da 0 fino a 4.294.967.295 Con segni antecedenti: da -2.147.483.648 fino a +2.147.483.647 | 4 Byte |
BIGINT | Un numero intero grande | Senza segni antecedenti: da 0 fino a 18.446.744.073.709.551.615 Con segni antecedenti: da -9.223.372.036.854.775.808 fino a +9.223.372.036.854.775.807 | 8 Byte |
FLOAT | Un numero in virgola mobile a precisione singola | Senza segni antecedenti: da 0 fino a 3,4e+38 Con segni antecedenti: da -3,4e+38 fino a 3,4e+38 | 4 Byte |
DOUBLE | Un numero in virgola mobile a precisione doppia | Senza segni antecedenti: da 0 fino a 3,4e+38 Con segni antecedenti: da -3,4e+38 fino a 3,4e+38 | 8 Byte |
DATE | Data nel formato 'YYYY-MM-DD' | Da '1000-01-01' fino a '9999-12-31' | 3 Byte |
TIME | Indicazione dell’ora nel formato 'HH:MM:SS.ssssss' | Da '-838:59:59.999999' fino a '838:59:59.999999' | 3 Byte |
DATETIME | Indicazione della data nel formato 'YYYY-MM-DD HH:MM:SS.ssssss' | Corrisponde a DATE e TIME (fino a 23:59:59.999999 ore) | 8 Byte |
TIMESTAMP | Timestamp nel formato 'YYYY-MM-DD HH:MM:DD' | Da '1970-01-01 00:00:01' (UTC) fino a '2038-01-19 05:14:07' (UTC) | 4 Byte |
YEAR | Anni compresi dal 1901 al 2155 | Dal 1901 fino al 2155 e 0000 | 1 Byte |
CHAR | Sequenza di caratteri di lunghezza fissa; il numero dei caratteri corrisponde a M | Per M: da 0 fino a 255 caratteri | M Byte |
VARCHAR | Sequenza di caratteri di lunghezza variabile; il numero dei caratteri corrisponde a M | Per M: da 0 fino a 65.535 caratteri | Max. M + 2 Byte |
TINYTEXT | Sequenza di caratteri molto breve di lunghezza variabile; il numero dei caratteri corrisponde a M | Per M: da 0 fino a 255 caratteri | M + 1 Byte |
TEXT | Sequenza di caratteri di lunghezza variabile; il numero dei caratteri corrisponde a M | Per M: da 0 fino a 65.535 caratteri | M + 2 Byte |
MEDIUMTEXT | Sequenza di caratteri media di lunghezza variabile; il numero dei caratteri corrisponde a M | Per M: da 0 fino a 16.777.215 caratteri | M + 3 Byte |
LONGTEXT | Sequenza di caratteri lunga di lunghezza variabile; il numero dei caratteri corrisponde a M | Per M: da 0 fino a 4.294.967.295 caratteri (4 GB) | M + 4 Byte |
BLOB | Un BLOB (Binary Large Object) è un oggetto binario con dati di lunghezza variabile (ad esempio immagini e file audio) | Lunghezza max. di M: 65.535 Byte | M + 2 Byte |
TINYBLOB | Piccolo oggetto binario con dati di lunghezza variabile | Lunghezza max. di M: 255 Byte | M + 1 Byte |
MEDIUMBLOB | Oggetto binario medio con dati di lunghezza variabile | Lunghezza max. di M: 16.777.215 Byte | M + 3 |
LONGBLOB | Oggetto binario grande con dati di lunghezza variabile | Lunghezza max. di M: 4.294.967.295 Byte (4 GB). | M + 4 Byte |
ENUM (Enumerazione) | Una stringa i cui valori consentiti sono definiti durante la creazione delle colonne | Massimo 65,535 elementi diversi | 1 o 2 byte, a seconda del numero dei valori possibili |
SET | Una stringa i cui valori consentiti sono definiti durante la creazione delle tabelle. È possibile una scelta multipla | Massimo 64 valori diversi | 1, 2, 3, 4, o 8 byte, a seconda del numero dei valori possibili |
Per la tabella di esempio users abbiamo optato per le seguenti impostazioni:
i possibili valori per la colonna id sono stati definiti come numeri interi (Integer, INT) e sono contrassegnati dall’attributo UNSIGNED. La colonna id può accettare solo valori numerici positivi. Alla voce “Index“ abbiamo scelto per id l’opzione PRIMARY. Il numero di identificazione funge così da chiave primaria per la tabella users. La spunta su “A_I“ (Auto_Increment) segnala al Database Management System che gli ID per ogni voce devono essere generati automaticamente con numeri progressivi.
I valori per le colonne forename, surname, email e password sono stati definiti come tipo di dati VARCHAR. Si tratta così di sequenze di caratteri variabili, di cui abbiamo limitato la lunghezza (M) con l’opzione “Length/Value“ a 50 caratteri. Per la colonna email è stata anche attivata l’opzione Index UNIQUE. In questo modo ci assicuriamo che ogni indirizzo e-mail nella nostra tabella sia salvato solo una volta.
Per le colonne created_at e updated_at abbiamo scelto il tipo di dati TIMESTAMP. Il Database Management System salva le indicazioni temporali per la creazione e l’aggiornamento dei record nel formato YYYY-MM-DD HH:MM:DD. Visto che il sistema per ogni nuova voce deve creare automaticamente un timestamp, scegliamo per la colonna created_at il valore standard CURRENT_TIMESTAMP. La colonna updated_at diventa rilevante solo quando aggiorniamo una voce. Perciò consentiamo i valori Null per questa colonna e impostiamo NULL come valore standard.
i possibili valori per la colonna id sono stati definiti come numeri interi (Integer, INT) e sono contrassegnati dall’attributo UNSIGNED. La colonna id può accettare solo valori numerici positivi. Alla voce “Index“ abbiamo scelto per id l’opzione PRIMARY. Il numero di identificazione funge così da chiave primaria per la tabella users. La spunta su “A_I“ (Auto_Increment) segnala al Database Management System che gli ID per ogni voce devono essere generati automaticamente con numeri progressivi.
I valori per le colonne forename, surname, email e password sono stati definiti come tipo di dati VARCHAR. Si tratta così di sequenze di caratteri variabili, di cui abbiamo limitato la lunghezza (M) con l’opzione “Length/Value“ a 50 caratteri. Per la colonna email è stata anche attivata l’opzione Index UNIQUE. In questo modo ci assicuriamo che ogni indirizzo e-mail nella nostra tabella sia salvato solo una volta.
Per le colonne created_at e updated_at abbiamo scelto il tipo di dati TIMESTAMP. Il Database Management System salva le indicazioni temporali per la creazione e l’aggiornamento dei record nel formato YYYY-MM-DD HH:MM:DD. Visto che il sistema per ogni nuova voce deve creare automaticamente un timestamp, scegliamo per la colonna created_at il valore standard CURRENT_TIMESTAMP. La colonna updated_at diventa rilevante solo quando aggiorniamo una voce. Perciò consentiamo i valori Null per questa colonna e impostiamo NULL come valore standard.
Il valore NULL nel linguaggio PHP indica un campo vuoto. Un campo di dati ha il valore NULL, se non gli è ancora stato assegnato alcun valore.
Come Storage Engine (motore di archiviazione) utilizziamo il formato standard delle tabelle MySQL, InnoDB.
Tutte le impostazioni delle tabelle che avete intrapreso tramite l’interfaccia grafica vengono convertite da phpMyAdmin nel codice SQL. Se necessario, questo codice si può riprodurre tramite il pulsante “Anteprima SQL”.
Tutte le impostazioni delle tabelle che avete intrapreso tramite l’interfaccia grafica vengono convertite da phpMyAdmin nel codice SQL. Se necessario, questo codice si può riprodurre tramite il pulsante “Anteprima SQL”.
CREATE TABLE test.users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT , forename VARCHAR(50) NOT NULL , surname VARCHAR(50) NOT NULL , email VARCHAR(50) NOT NULL , password VARCHAR(50) NOT NULL , created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , updated_at TIMESTAMP NULL DEFAULT NULL , PRIMARY KEY (id), UNIQUE (email)) ENGINE = InnoDB;
Una descrizione dettagliata della sintassi SQL verrà fornita nel paragrafo dedicato alle interrogazioni del database.
Un click su “Save“ salva le impostazioni prese. La tabella users viene mostrata nel pannello di navigazione sotto il database test.
Un click su “Save“ salva le impostazioni prese. La tabella users viene mostrata nel pannello di navigazione sotto il database test.
Gestire le tabelle
Per gestire una tabella creata, cliccate sul nome della tabella nel pannello di navigazione. phpMyAdmin vi indica nella tab “Browse” (Visualizza) una panoramica dei dati salvati nella rispettiva tabella. Nel caso della nostra tabella di esempio, la richiesta non dà nessun risultato, visto che non ci sono ancora dei record nella tabella users.
Per la gestione di tabelle di dati avete a disposizione nella barra del menu diverse tab. Se volete cambiare la struttura di una tabella di dati, selezionate la tab “Structure”. Aggiungete nuovi record alla tabella dalla tab “Insert” (aggiungi). Inoltre phpMyAdmin consente di ricercare dati nelle tabelle, di gestire i permessi e di esportare i record o di importarli da altre tabelle.
Modificare la struttura della tabella
Se volete aggiungere successivamente altre colonne alla vostra tabella, eliminare quelle già esistenti o modificarle, spostatevi sulla tab “Structure”.
Aggiungete le colonne tramite il pulsante “Add columns”, indicando il numero desiderato delle nuove colonne, così come la rispettiva posizione.
Nella seguente illustrazione viene aggiunta una nuova colonna dopo updated_at:
Aggiungete le colonne tramite il pulsante “Add columns”, indicando il numero desiderato delle nuove colonne, così come la rispettiva posizione.
Nella seguente illustrazione viene aggiunta una nuova colonna dopo updated_at:
Se volete eliminare le colonne già esistenti, selezionate la spunta nel riquadro apposito e cliccate infine su “Drop”.
La modifica di una colonna avviene tramite il pulsante “Change“. Arrivate a una finestra di modifica che assomiglia a quella che già conoscete per la creazione delle tabelle:
Le modifiche alla struttura delle tabelle possono portare in alcune circostanze alla perdita di dati. Prima di elaborare o eliminare le colonne delle tabelle già esistenti, dovreste in ogni caso creare un backup del vostro database. Spostatevi nella tab “Export” (Esporta), scegliete il formato di dati desiderato per il backup e confermate con “Go”. Si apre una finestra di dialogo in cui il vostro browser richiede la destinazione di archiviazione per il download. Rappresenta un’alternativa al backup del database da phpMyAdmin il programma di sicurezza gratuito MySQLDumper.
Creare i record della tabella
Per riempire la vostra tabella con i dati tramite phpMyAdmin, avete due possibilità: importare i record da un file esterno (ad esempio da un backup) o creare manualmente le voci della tabella. Selezionate la tabella di esempio users e cliccate sulla tab “Insert“ (aggiungi).
phpMyAdmin vi mostra la seguente maschera di inserimento dati:
phpMyAdmin vi mostra la seguente maschera di inserimento dati:
Sotto “Column“ sono elencate le colonne che abbiamo definito per la tabella users. Sotto “Type” trovate informazioni su quale tipo di dati è previsto per la relativa colonna, insieme a un limite di caratteri approssimativo indicato tra parantesi. Tralasciamo la sezione “Function” e andiamo direttamente su “Value”, dove definiamo i valori per le singole colonne della nostra tabella di esempio.
Nel paragrafo precedente abbiamo configurato la tabella users di modo tale che i campi dei dati per le colonne id, created_at e updated_at attingano automaticamente ai contenuti del Database Management System. Nella colonna id assegniamo tramite AUTO_INCREMENT un numero di identificazione progressivo per ogni nuova voce. Il campo created_at viene dotato automaticamente di un timestamp attuale e per updated_at il sistema assegna prima di tutto un valore standard NULL. Così dobbiamo inserire manualmente i dati per le colonne forename, surname, email e password. Chiariamo questo concetto utilizzando dei dati utente fittizi:
Nel paragrafo precedente abbiamo configurato la tabella users di modo tale che i campi dei dati per le colonne id, created_at e updated_at attingano automaticamente ai contenuti del Database Management System. Nella colonna id assegniamo tramite AUTO_INCREMENT un numero di identificazione progressivo per ogni nuova voce. Il campo created_at viene dotato automaticamente di un timestamp attuale e per updated_at il sistema assegna prima di tutto un valore standard NULL. Così dobbiamo inserire manualmente i dati per le colonne forename, surname, email e password. Chiariamo questo concetto utilizzando dei dati utente fittizi:
- forename: John
- surname: Doe
- email: john@doe.com
- password: qwertz
Con un click su “Go” riportate i dati nella vostra tabella. phpMyAdmin passa automaticamente alla tab “SQL” e vi mostra l’operazione del database eseguita come statement secondo la sintassi SQL:
INSERT INTO users (id, forename, surname, email, password, created_at, updated_at) VALUES (NULL, 'John', 'Doe', 'john@doe.com', 'qwertz', CURRENT_TIMESTAMP, NULL);
Principalmente tutte le operazioni del database, che eseguite facilmente tramite phpMyAdmin attraverso un’interfaccia grafica, si possono anche annotare nel linguaggio di database SQL. Tale procedimento è un processo standard nell’ambito dello sviluppo web.
Le così chiamate queries SQL (interrogazioni) si trovano nel codice sorgente di tutte le applicazioni web dinamiche e consentono al web server l’interazione con il database. Il linguaggio di database SQL si basa quindi su comandi, ad esempio per richiamare i dati e utilizzarli nell’ambito dell’esecuzione dei programmi. I comandi SQL più importanti, cioè SELECT, INSERT, DELETE e UPDATE, così come la sintassi delle operazioni di database essenziali, verranno trattati nei prossimi paragrafi del nostro tutorial MySQL.
Prima di tutto riempiamo la nostra tabella users con altri dati utente e guardiamo il riepilogo generale della tabella nella tab “Browse”:
Le così chiamate queries SQL (interrogazioni) si trovano nel codice sorgente di tutte le applicazioni web dinamiche e consentono al web server l’interazione con il database. Il linguaggio di database SQL si basa quindi su comandi, ad esempio per richiamare i dati e utilizzarli nell’ambito dell’esecuzione dei programmi. I comandi SQL più importanti, cioè SELECT, INSERT, DELETE e UPDATE, così come la sintassi delle operazioni di database essenziali, verranno trattati nei prossimi paragrafi del nostro tutorial MySQL.
Prima di tutto riempiamo la nostra tabella users con altri dati utente e guardiamo il riepilogo generale della tabella nella tab “Browse”:
Un click sul nome della colonna ordina la tabella secondo la sequenza desiderata.
Stabilire una connessione al database
Dopo aver riempito la tabella di esempio users con i record, nei prossimi paragrafi vedremo come si possono interrogare i dati immessi tramite PHP attraverso il web server Apache.
Per fare ciò, stabiliamo nel primo passaggio una connessione al database. Nel linguaggio PHP sono a disposizione tre interfacce: MySQL Extension, MySQL Improved Extension (MySQLi) e PHP Data Objects (PDO).
Per poter effettuare delle richieste al database nell’ambito di uno script PHP, bisogna prima di tutto autentificarsi. Una connessione database tramite PDO viene instaurata ricorrendo alla seguente stringa di codice:
Per fare ciò, stabiliamo nel primo passaggio una connessione al database. Nel linguaggio PHP sono a disposizione tre interfacce: MySQL Extension, MySQL Improved Extension (MySQLi) e PHP Data Objects (PDO).
- MySQL Extension: MySQL Extension è un’interfaccia migliorata di MySQL che prima era molto popolare, ma oggi risulta antiquata. Rispetto a MySQLi e PDO la vecchia MySQL Extension ha lo svantaggio di non supportare né i prepared statements, né i parametri indicati.
- MySQLi: MySQLi è una versione migliorata della classica estensione PHP per l’accesso ai database MySQL. L’interfaccia lavora sia in maniera procedurale che orientata agli oggetti. Viene utilizzata solo sui database MySQL e MariaDB.
- PDO: nel caso di PHP Data Objects (PDO) si tratta di un’interfaccia orientata agli oggetti che mette a disposizione un livello di astrazione per l’accesso ai dati. Così non solo si possono integrare tramite PDO i database MySQL, ma anche altri sistemi di database come PostgreSQL, Oracle, MSSQL o SQLite nel PHP.
Per poter effettuare delle richieste al database nell’ambito di uno script PHP, bisogna prima di tutto autentificarsi. Una connessione database tramite PDO viene instaurata ricorrendo alla seguente stringa di codice:
<?php
$pdo = new PDO('DSN', 'username', 'password');
?>
Si consiglia di inserirla all’inizio di ogni script che contiene operazioni del database.
Utilizziamo la parola chiave PHP new, per creare un’istanza della classe base PDO. I suoi costruttori prevedono essenzialmente tre parametri, cioè il Data Source Name (DSN), un nome utente e la password per il database, se presente. Nel nostro caso il DSN è composto dai seguenti parametri:
Utilizziamo la parola chiave PHP new, per creare un’istanza della classe base PDO. I suoi costruttori prevedono essenzialmente tre parametri, cioè il Data Source Name (DSN), un nome utente e la password per il database, se presente. Nel nostro caso il DSN è composto dai seguenti parametri:
- Driver PDO del database: mysql
- Server database (host=): localhost
- Nome del database (dbname=): test
- Set di caratteri (charset=): utf8
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
?>
La connessione al database viene salvata in una variabile $pdo, così nel corso del codice del programma potete rimandare alla connessione del database.
Se è stata stabilita una connessione al database, potete inviare con il codice di script seguente un numero illimitato di richieste al database. Una volta terminato lo script, si interrompe anche la connessione al database.
Se è stata stabilita una connessione al database, potete inviare con il codice di script seguente un numero illimitato di richieste al database. Una volta terminato lo script, si interrompe anche la connessione al database.
Interrogare i dati con SELECT, INSERT, UPDATE e DELETE
Per richiamare i dati dal nostro database, ricorriamo al linguaggio di database SQL, che si basa semanticamente sull’inglese e viene mantenuto volontariamente semplice. La sintassi SQL è ampiamente autoesplicativa.
Nel linguaggio SQL si lavora con statement, altrimenti denominati come interrogazioni o richieste.
Una semplice query SELECT è composta, ad esempio, dai seguenti componenti:
Nel linguaggio SQL si lavora con statement, altrimenti denominati come interrogazioni o richieste.
Una semplice query SELECT è composta, ad esempio, dai seguenti componenti:
SELECT colonna FROM tabella;
Prima di tutto definite il comando SQL SELECT e le rispettive colonne e tabelle alle quali deve fare riferimento il comando; un punto e virgola chiude lo statement.
Inoltre avete la possibilità di ampliare lo statement con una condizione opzionale e una funzione di classificazione o di raggruppamento:
Inoltre avete la possibilità di ampliare lo statement con una condizione opzionale e una funzione di classificazione o di raggruppamento:
SELECT colonna FROM tabella WHERE condizione ORDER BY ordine_della_sequenza;
In questo caso per convenzione i comandi SQL si scrivono in maiuscolo, mentre il nome del database, delle tabelle e degli altri campi sono minuscoli, per motivi di leggibilità. SQL è principalmente un linguaggio non formattato e non fa perciò differenza tra maiuscolo e minuscolo.
Se utilizzate i nomi delle tabelle e delle colonne che corrispondono alle parole chiave SQL già definite (non consigliato), dovete inserire i backtick (``).
Vi chiariamo la sintassi di semplici statement SQL portando ad esempio i comandi SELECT, INSERT, UPDATE e DELETE.
Se utilizzate i nomi delle tabelle e delle colonne che corrispondono alle parole chiave SQL già definite (non consigliato), dovete inserire i backtick (``).
Vi chiariamo la sintassi di semplici statement SQL portando ad esempio i comandi SELECT, INSERT, UPDATE e DELETE.
SELECT
Utilizzate il comando SELECT per richiamare le serie di dati (rows) selezionate da un numero indefinito di tabelle. Se ad esempio volete che vengano visualizzati sul browser il nome, il cognome e gli indirizzi e-mail di tutti gli utenti presenti nella tabella di esempio, create nella cartella htdocs del vostro ambiente XAMPP un nuovo file PHP text.php e inserite il seguente script:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT forename, surname, email FROM users";
foreach ($pdo->query($sql) as $row) {
echo $row['forename'] . " " . $row['surname'] . "<br />";
echo "E-Mail: " . $row['email'] . "<br /><br />";
}
?>
Il codice di esempio si legge così: prima iniziamo lo script con il tag di apertura PHP <?php. Nella riga 2 instauriamo la connessione al nostro database test su localhost e la salviamo nella variabile $pdo. Lo statement SQL con il comando SELECT si trova nella riga 3. Qui si rimanda al Database Management System per richiamare le colonne forename, surname e email dalla tabella users. Salviamo questo statement nelle variabili $sql.
Le righe da 4 a 7 indicano un ciclo foreach, che ci offre la possibilità di iterare un array qualsiasi, cioè di attraversare per gradi una struttura di dati. Quale array vogliamo iterare e come i dati richiesti devono essere salvati, lo definiamo tra parentesi dopo il costrutto foreach:
Le righe da 4 a 7 indicano un ciclo foreach, che ci offre la possibilità di iterare un array qualsiasi, cioè di attraversare per gradi una struttura di dati. Quale array vogliamo iterare e come i dati richiesti devono essere salvati, lo definiamo tra parentesi dopo il costrutto foreach:
$pdo->query($sql) as $row
La variabile $pdo prende in considerazione il database desiderato tramite la connessione definita nella riga 2, che inviamo con la funzione query() salvata nello statement SQL della variabile $sql.
Il web server annuncia così le colonne forename, surname e email della tabella users dal database test e attraversa, nell’ambito di un ciclo foreach, ogni singola riga della tabella. La parola chiave PHP as definisce dove devono essere salvati i dati nella variabile array $row.
Nel primo passaggio del ciclo foreach questo array si presenta così.
Il web server annuncia così le colonne forename, surname e email della tabella users dal database test e attraversa, nell’ambito di un ciclo foreach, ogni singola riga della tabella. La parola chiave PHP as definisce dove devono essere salvati i dati nella variabile array $row.
Nel primo passaggio del ciclo foreach questo array si presenta così.
$row = array (
forename => John,
surname => Doe,
email => john@doe.com
)
Parte del ciclo foreach è inoltre nell’esempio attuale la riproduzione del testo tramite echo in ogni passaggio del ciclo. Attraversiamo così ogni riga della tabella users singolarmente, leggiamo i dati creati per le colonne definite nello statement SQL e le visualizziamo sul browser.
Se tutte le colonne devono essere lette da una tabella di dati, utilizzate nello statement SQL l’asterisco (*) come segnaposto.
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM users";
foreach ($pdo->query($sql) as $row) {
echo $row['forename'] . " " . $row['surname'] . "<br />";
echo "E-Mail: " . $row['email'] . "<br />";
echo "Created at: " . $row['created_at'] . "<br /><br />";
}
?>
Così abbiamo la possibilità di utilizzare tutti i dati creati nella tabella users nell’ambito dello script. Nel seguente screenshot è stato aggiunto nel testo un timestamp relativo alla creazione del record:
In entrambi gli esempi presentati precedentemente il web server restituisce i dati utente nell’ordine in cui sono stati inseriti nella tabella users (in base all’ID). Se volete visualizzare i dati secondo un certo ordine, definite la sequenza con la parola chiave SQL ORDER BY. Nel seguente esempio i dati vengono visualizzati in ordine alfabetico per nome:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT forename, surname, email FROM users ORDER BY forename";
foreach ($pdo->query($sql) as $row) {
echo $row['forename'] . " " . $row['surname'] . "<br />";
echo "E-Mail: " . $row['email'] . "<br /><br />";
}
?>
INSERT
La creazione di record nel database raramente avviene manualmente tramite phpMyAdmin. Di solito i dati vengono scritti nel database nell’ambito dell’esecuzione degli script da parte del web server, ad esempio nel momento in cui un utente compila un modulo online su un sito o un cliente lascia un commento su un negozio online. In entrambi i casi viene utilizzato in background il comando SQL INSERT. Uno statement SQL con il comando INSERT viene creato secondo lo schema seguente:
INSERT INTO tabella (colonna1, colonna2, colonna3) VALUES (valore1, valore2, valore3);
E si legge così: apri la tabella citata e aggiungi i valori 1, 2 e 3 nelle colonne 1, 2 e 3.
Un semplice script PHP per aggiungere alla nostra tabella di esempio users un altro record, potrebbe essere così:
Un semplice script PHP per aggiungere alla nostra tabella di esempio users un altro record, potrebbe essere così:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "INSERT INTO users (forename, surname, email, password)
VALUES ('Paddy', 'Irish', 'paddy@irish.com', 'qaywsx')";
if ($pdo->exec($sql) === 1)
echo "New record created successfully";
?>
Prima di tutto stabiliamo la connessione al database e salviamola nella variabile $pdo. Infine definiamo lo statement SQL e salviamolo nella variabile $sql. Nella riga 5 utilizziamo l’operatore freccia (->), per ricorrere alla variabile $pdo ed eseguire con la funzione exec() lo statement SQL salvato su $sql.
Per essere sicuri che il nostro script aggiunga solo un record nella tabella users, verifichiamo il numero delle righe coinvolte con una condizione if. In questo modo la stringa New record created successfully viene visualizzata sul browser solo se il numero dei record aggiunti ammonta a 1. Se lo script viene eseguito di nuovo, non viene mostrato il messaggio. Le voci doppie impediscono così che il valore email venga definito come UNIQUE.
Per essere sicuri che il nostro script aggiunga solo un record nella tabella users, verifichiamo il numero delle righe coinvolte con una condizione if. In questo modo la stringa New record created successfully viene visualizzata sul browser solo se il numero dei record aggiunti ammonta a 1. Se lo script viene eseguito di nuovo, non viene mostrato il messaggio. Le voci doppie impediscono così che il valore email venga definito come UNIQUE.
Aprendo la panoramica della nostra tabella di esempio users nel database test, notiamo che la tabella è stata ampliata del record 5. Il numero di identificazione progressivo e il timestamp sono stati aggiunti intenzionalmente in automatico.
UPDATE
Se volete aggiornare un record già esistente, utilizzate il comando SQL UPDATE secondo lo schema seguente:
UPDATE tabella SET colonna1 = valore1, colonna2 = valore2 WHERE colonna3 = valore3
Espresso a parole questo statement SQL significa: seleziona la tabella indicata e sostituisci il valore nella colonna1 con il valore1 e il valore nella colonna2 con il valore2, a patto che la colonna3 contenga il valore3. Attenzione: se dimenticate di inserire la condizione, MySQL sovrascrive i campi coinvolti in tutti i record.
Abbiamo così a che fare con uno statement SQL che collega un’operazione database a una condizione. Trasposto alla nostra tabella di esempio, l’indirizzo e-mail dell’utente John Doe si può aggiornare tramite lo script PHP seguente:
Abbiamo così a che fare con uno statement SQL che collega un’operazione database a una condizione. Trasposto alla nostra tabella di esempio, l’indirizzo e-mail dell’utente John Doe si può aggiornare tramite lo script PHP seguente:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "UPDATE users
SET email = 'john.doe@example.com', updated_at = NOW() WHERE id = 1";
if ($pdo->exec($sql) === 1)
echo "Update successful";
?>
Nello statement SQL definiamo che il valore attuale nella colonna email deve essere sostituito tramite il nuovo valore john.doe@example.com, a condizione che il valore nella colonna id sia pari a 1. Aggiorniamo così solo il record con la chiave primaria 1. Inoltre aggiorniamo nello stesso statement SQL il valore per la colonna updated_at con la funzione MySQL now(), che restituisce il timestamp attuale. Lo statement SQL viene eseguito come prima con la riga di codice $pdo->exec($sql) all’interno di una condizione if.
Se l’update è andato a buon fine, su phpMyAdmin nella tab “Browse” vi verrà mostrata la tabella aggiornata:
Nell’esempio abbiamo aggiornato un indirizzo e-mail e sostituito il valore standard NULL nella colonna updated_at con un timestamp. Il comando UPDATE consente inoltre di trasferire i valori da una colonna a un’altra. Ad esempio si può mettere in atto questa operazione, se ampliamo la nostra tabella di esempio users con la colonna email_registration. Ciò dà la possibilità di distinguere tra due indirizzi e-mail: uno che è stato inviato durante la registrazione e un indirizzo di contatto attuale che si può cambiare con il tempo. Prima di tutto entrambi gli indirizzi sono però uguali, di modo che i valori possono essere spostati da un campo a un altro. Così per cominciare creiamo la nuova colonna email_registration con phpMyAdmin tramite il pulsante “Add columns” nella tab “Structure”:
Per trasferire i valori, utilizziamo il seguente statement UPDATE:
UPDATE users SET email_registration = email
Visto che vogliamo aggiornare tutti i record, non formuliamo alcuna condizione per l’update
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "UPDATE users SET email_registration = email";
$affected = $pdo->exec($sql);
echo "$affected rows updated<br>";
?>
Se lo script viene eseguito dal web server, il Database Management System ottiene i valori delle colonne email per tutti i record nella colonna email_registration.
DELETE
Si possono eliminare i record del database grazie al comando SQL DELETE, che utilizzate secondo il seguente schema:
DELETE FROM tabella WHERE colonna = valore
Se nel vostro database lavorate con ID, grazie a questi è possibile identificare i record da cancellare. Nel caso in cui vogliate ad esempio eliminare il record 5 dalla nostra tabella di esempio, procedete come segue:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "DELETE FROM users WHERE id = 5";
$affected = $pdo->exec($sql);
echo "$affected rows updated<br>";
?>
Il comando SQL DELETE elimina sempre un’intera riga di database. Se volete eliminare solo i valori in colonne specifiche di un record, realizzate questo intento con uno statement UPDATE. Con UPDATE tabella SET colonna = NULL WHERE …potete assegnare a una colonna il valore NULL, premesso ovviamente che per la colonna coinvolta abbiate consentito l’uso del valore NULL.
Prepared statements
Con PDO si possono impostare le operazioni del database come prepared statement. Queste “richieste già pronte” sono oggi normale routine nello sviluppo web e vengono supportate da tutti i moderni Database Management System.
Negli esempi fatti fino a qui abbiamo presentato i valori dei parametri direttamente nello statement SQL. Invece, i prepared statement lavorano con i segnaposto, che vengono sostituiti solo successivamente con dei valori. Ciò consente al Database Management System di verificare la validità dei parametri, prima di elaborarli. I prepared statement offrono una protezione efficace contro la SQL injection, a patto che vengano inseriti in maniera regolare nel codice sorgente. In questo schema di attacco gli hacker creano o cambiano i comandi SQL per arrivare ai dati sensibili, per sovrascriverli o inserire abusivamente i propri comandi in un sistema.
La SQL injection si basa su una vulnerabilità conosciuta quando si lavora con i database SQL: se gli input utente vengono trasferiti ad esempio con parametri statici tramite $_GET, ciò offre la possibilità agli hacker di arricchire l’input con i metacaratteri, comportando così degli effetti indesiderati, a patto che giungano all’interprete SQL senza mascheratura. Tramite richieste parametrizzate si può evitare efficacemente tutto questo. I prepared statement fungono così da template per i comandi SQL che possono essere comunicati separatamente dai parametri effettivi al database. Questi convalidano i dati trasmessi, mascherano automaticamente i metacaratteri e aggiungono i parametri al posto del segnaposto nello statement SQL.
I prepared statement offrono, oltre all’aspetto sicurezza, anche un miglioramento della performance. Ciò è dimostrato quando lo stesso comando SQL deve venire eseguito in un ciclo con parametri diversi. Dopo che un prepared statement è stato analizzato una volta, sarà presente nel sistema di database e deve essere semplicemente eseguito con nuovi parametri. Così facendo, si possono notevolmente accelerare le richieste che risultano più complicate.
Nel PDO i prepared statement vengono messi in atto grazie alla funzione prepare(), che prepara uno statement per l’esecuzione e restituisce un oggetto di statement. Come segnaposto per i rispettivi valori vengono utilizzati un punto interrogativo (?) o un parametro nominativo (Named Parameter).
Negli esempi fatti fino a qui abbiamo presentato i valori dei parametri direttamente nello statement SQL. Invece, i prepared statement lavorano con i segnaposto, che vengono sostituiti solo successivamente con dei valori. Ciò consente al Database Management System di verificare la validità dei parametri, prima di elaborarli. I prepared statement offrono una protezione efficace contro la SQL injection, a patto che vengano inseriti in maniera regolare nel codice sorgente. In questo schema di attacco gli hacker creano o cambiano i comandi SQL per arrivare ai dati sensibili, per sovrascriverli o inserire abusivamente i propri comandi in un sistema.
La SQL injection si basa su una vulnerabilità conosciuta quando si lavora con i database SQL: se gli input utente vengono trasferiti ad esempio con parametri statici tramite $_GET, ciò offre la possibilità agli hacker di arricchire l’input con i metacaratteri, comportando così degli effetti indesiderati, a patto che giungano all’interprete SQL senza mascheratura. Tramite richieste parametrizzate si può evitare efficacemente tutto questo. I prepared statement fungono così da template per i comandi SQL che possono essere comunicati separatamente dai parametri effettivi al database. Questi convalidano i dati trasmessi, mascherano automaticamente i metacaratteri e aggiungono i parametri al posto del segnaposto nello statement SQL.
I prepared statement offrono, oltre all’aspetto sicurezza, anche un miglioramento della performance. Ciò è dimostrato quando lo stesso comando SQL deve venire eseguito in un ciclo con parametri diversi. Dopo che un prepared statement è stato analizzato una volta, sarà presente nel sistema di database e deve essere semplicemente eseguito con nuovi parametri. Così facendo, si possono notevolmente accelerare le richieste che risultano più complicate.
Nel PDO i prepared statement vengono messi in atto grazie alla funzione prepare(), che prepara uno statement per l’esecuzione e restituisce un oggetto di statement. Come segnaposto per i rispettivi valori vengono utilizzati un punto interrogativo (?) o un parametro nominativo (Named Parameter).
Prepared statement con parametri senza nome
L’esempio di codice seguente indica l’operazione del database INSERT come prepared statement con parametri senza nome:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
// Preparare lo statement SQL
$statement = $pdo->prepare("INSERT INTO users (forename, surname, email, password)
VALUES (?, ?, ?, ?)");
// Unire i parametri
$statement ->bindParam(1, $forename);
$statement ->bindParam(2, $surname);
$statement ->bindParam(3, $email);
$statement ->bindParam(4, $password);
// Inserire i record
$forename = "Anders";
$surname = "Andersen";
$email = "anders@andersen.com";
$password = "mnbvc";
if ($statement->execute())
echo "New record $forename created successfully<br>";
$forename = "Matti";
$surname = "Meikäläinen";
$email = "matti@meikäläinen.com";
$password = "jhgfd";
// Visualizzazione dello stato
if ($statement->execute())
echo "New record $forename created successfully<br>";
?>
Per prima cosa generiamo grazie alla funzione prepare() un oggetto di statement della richiesta desiderata e lo salviamo nell’array $statement. Al posto di valori di parametri concreti viene usato il punto interrogativo come segnaposto.
Se uno statement SQL comprende solo il segnaposto, i valori trasmessi separatamente devono essere collegati a questo nel codice seguente. Nel PHP si utilizza anche la funzione bindParam(). Utilizziamo l’operatore freccia (->) per ricorrere al metodo dell’oggetto $statement e assegnarlo a questa variabile (1 corrisponde al primo punto interrogativo, 2 al secondo, ecc.).
Il template SQL così creato può ora essere eseguito con i parametri desiderati tutte le volte che si vuole. Nell’esempio corrente definiamo i valori delle variabili per due record. L’esecuzione dello statement SQL già pronto avviene per ogni record tramite execute().
Se uno statement SQL comprende solo il segnaposto, i valori trasmessi separatamente devono essere collegati a questo nel codice seguente. Nel PHP si utilizza anche la funzione bindParam(). Utilizziamo l’operatore freccia (->) per ricorrere al metodo dell’oggetto $statement e assegnarlo a questa variabile (1 corrisponde al primo punto interrogativo, 2 al secondo, ecc.).
Il template SQL così creato può ora essere eseguito con i parametri desiderati tutte le volte che si vuole. Nell’esempio corrente definiamo i valori delle variabili per due record. L’esecuzione dello statement SQL già pronto avviene per ogni record tramite execute().
I prepared statement con parametri nominativi
Più chiaro di un segnaposto con il punto interrogativo sono i parametri nominativi. Si tratta così di parametri personalizzati a cui si può dare un nome, se necessario, secondo il seguente schema:
:esempio
I parametri nominativi non devono però contenere spazi vuoti o trattini (-). Utilizzate al loro posto un underscore (_).
Il seguente esempio indica l’operazione di database INSERT come prepared statement con parametri nominativi:
Il seguente esempio indica l’operazione di database INSERT come prepared statement con parametri nominativi:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
// Preparare lo statement SQL
$statement = $pdo->prepare("INSERT INTO users (forename, surname, email, password)
VALUES (:forename, :surname, :email, :password)");
// Unire i parametri
$statement ->bindParam(':forename', $forename);
$statement ->bindParam(':surname', $surname);
$statement ->bindParam(':email', $email);
$statement ->bindParam(':password', $password);
// Inserire i record
$forename = "Anders";
$surname = "Andersen";
$email = "anders@andersen.com";
$password = "mnbvc";
if ($statement->execute())
echo "New record $forename created successfully<br>";
$forename = "Matti";
$surname = "Meikäläinen";
$email = "matti@meikäläinen.com";
$password = "jhgfd";
$statement->execute();
// Visualizzazione dello stato
if ($statement->execute())
echo "New record $forename created successfully<br>";
?>
Nel prepared statement si trovano i parametri nominativi :forename, :surname, :email e :password, che uniamo tramite bindParam() alle variabili $forename, $surname, $email e $password. Nell’esempio appena riportato abbiamo scelto di far coincidere il nome dei parametri e delle variabili con quello utilizzato per le varie colonne nella nostra tabella di esempio. Questo però non avviene in automatico. Una denominazione unitaria è tuttavia consigliata, così da avere un codice sorgente di facile lettura. L’assegnazione dei valori delle variabili e l’esecuzione degli statement SQL avviene analogamente all’esempio proposto precedentemente.
Funzioni per la data e il tempo su MySQL
MySQL e MariaDB supportano diverse funzioni per lavorare con le indicazioni temporali e la data. Trovate una lista completa a questo indirizzo. Nel nostro tutorial MySQL per principianti ci limitiamo a esporre solo alcuni di questi.
Funzione per la data e il tempo | Descrizione |
---|---|
CURRENT_TIMESTAMP() / NOW() | Nell’esempio del comando SQL UPDATE avete già incontrato la funzione NOW(). In questo caso si tratta solo di un sinonimo della funzione CURRENT_TIMESTAMP(). La funzione viene applicata sempre, quando nell’ambito di un’operazione di database deve essere inserita la data corrente comprensiva di ora. |
CURDATE() / CURRENT_DATE() | La funzione CURDATE() fornisce la data corrente. |
CURTIME() / CURRENT_TIME() | La funzione CURTIME() fornisce l’ora attuale. |
DAY() / DAYOFMONTH() | Fornisce il giorno del mese (0–31); necessita di una data o di un timestamp come base di partenza. |
DAYOFWEEK() | Fornisce il giorno della settimana (1 = domenica); necessita di una data o di un timestamp come base di partenza. |
MONTH() | Fornisce il mese (1–12); necessita di una data o di un timestamp come base di partenza. |
YEAR() | Fornisce un anno (1000–9999, 0); necessita di una data o di un timestamp come base di partenza. |
DATE() | Estrae la data dall’ora o dalla data indicata; necessita di una data o di un timestamp come base di partenza. |
TIME() | Estrae la data dall’ora o dalla data indicata; necessita di una data o di un timestamp come base di partenza. |
DATE_FORMAT() | Formatta l’indicazione dell’ora o della data in base ai parametri inseriti; necessita di una data o di un timestamp come base di partenza. |
Un possibile scenario di applicazione per le funzioni per l’ora e la data su MySQL è dato ad esempio dalle interrogazioni del database, in cui devono essere analizzati tutti i record che sono stati creati in un giorno preciso.
Il seguente script fornisce tutti i record della nostra tabella di esempio users, che è stata creata oggi:
Il seguente script fornisce tutti i record della nostra tabella di esempio users, che è stata creata oggi:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT forename, surname, email FROM users WHERE DATE(created_at) = CURDATE()";
foreach ($pdo->query($sql) as $row) {
echo $row['forename'] . " " . $row['surname'] . "<br />";
echo "E-Mail: " . $row['email'] . "<br /><br />";
}
?>
Per visualizzare solo le voci di oggi, utilizziamo la seguente condizione nello statement SQL:
DATE(created_at) = CURDATE()
Per prima cosa estraiamo la data con l’aiuto della funzione DATE() dal timestamp salvato nella colonna created_at e lo sincronizziamo con la data attuale nel prossimo passaggio. Il comando SELECT seleziona così solo le voci in cui il timestamp coincide con la data corrente.
In alternativa possiamo scegliere la voce che abbiamo aggiornato al 16.12.2016. Così dobbiamo solo adattare la condizione del nostro statement SQL:
In alternativa possiamo scegliere la voce che abbiamo aggiornato al 16.12.2016. Così dobbiamo solo adattare la condizione del nostro statement SQL:
SELECT forename, surname, email FROM users WHERE DATE(updated_at) = '2016-12-16'
In questo caso l’indicazione della data estratta dal timestamp viene aggiornata con una data concreta. Inoltre le richieste vengono delimitate all'interno di un anno, di un mese o di un giorno in particolare.
Il seguente statement fa riferimento a tutte le voci nella tabella users, che sono state create a dicembre:
Il seguente statement fa riferimento a tutte le voci nella tabella users, che sono state create a dicembre:
SELECT forename, surname, email FROM users WHERE month(created_at) = 12";
Oltre al segno dell’uguale, SQL supporta anche nelle condizioni i seguenti operatori di confronto:
Operatore di confronto | Descrizione |
---|---|
= | È uguale |
< | È più piccolo di |
> | È più grande di |
<= | È più piccolo o uguale |
>= | È più grande o uguale |
!= | Non è uguale |
Inoltre si possono collegare più condizioni con gli operatori logici:
Operatore logico | Descrizione |
---|---|
OR o || | O logica |
AND o && | E logica |
Il seguente statement seleziona ad esempio tutte le voci che sono state create dopo febbraio e prima di aprile:
SELECT forename, surname, email FROM users WHERE MONTH(created_at) > 2 AND MONTH(created_at) < 4";
Finora abbiamo salvato nel nostro database le indicazioni della data e quelle temporali nel formato indicato. Con MySQL e MariaDB non siete però obbligati ad utilizzare proprio quello. La funzione DATE_FORMAT() vi dà la possibilità di formattare i dati e l’ora a vostro piacimento con i parametri opzionali.
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT forename, surname, DATE_FORMAT(created_at,'%D %M %Y') AS created_at FROM users WHERE id=1";
foreach ($pdo->query($sql) as $row) {
echo $row['forename'] . " " . $row['surname'] . " your profile was created at: " . $row['created_at'] . ".<br /><br />";
}
?>
La tabella seguente indica i possibili parametri per la funzione DATE_FORMAT() in base alla documentazione MySQL.
Parametro | Descrizione | Dominio dei valori/Esempi |
---|---|---|
%c | Indicazione numerica del mese con 1 o 2 cifre | Da 0 a 12 |
%d | Indicazione numerica del giorno in un mese con 2 cifre | Da 00 a 31 |
%e | Indicazione numerica del giorno in un mese con 1 o 2 cifre | Da 0 a 31 |
%H | Visualizzazione dell’ora a 2 cifre | Da 00 a 23 |
%i | Indicazione numerica dei minuti a 2 cifre | Da 00 a 59 |
%j | Indicazione numerica del giorno in un anno con 3 cifre | Da 001 a 366 |
%k | Indicazione numerica dell’ora con 1 o 2 cifre | Da 0 a 23 |
%M | Nome del mese nello schema di riferimento corrente | January, February, March, ecc. |
%m | Indicazione numerica del mese con 2 cifre | Da 00 a 12 |
%s | Indicazione numerica dei secondi con 2 cifre | Da 00 a 59 |
%T | Ora nel formato a 24 ore (abbreviato in '%H:%i:%S') | HH:MM:SS |
%u | Numero della settimana in un anno, cominciando da lunedì | Da 00 a 53 |
%W | Nome del giorno della settimana nello schema di riferimento corrente | Monday, Tuesday, ecc. |
%w | Giorno della settimana | 0 = Domenica, 6 = Sabato |
%Y | Numero dell‘anno a 4 cifre | Per esempio 2016 |
%y | Numero dell‘anno a 2 cifre | Per esempio 16 |
Messaggi di errore MySQL
Se non si può eseguire uno script come si vorrebbe, ciò dipende solitamente dagli errori sintattici nel codice sorgente o dalle tabelle, colonne e variabili che presentano dei nomi errati. Il server database non mostra però per forza un messaggio di errore. Spesso non si ottiene il risultato sperato e non seguono neanche indicazioni di un’operazione errata.
Con errorInfo() PDO mette perciò a disposizione una funzione con cui si può richiamare in modo mirato informazioni avanzate sugli errori verificatisi nell’ultima operazione di database, ad esempio per visualizzarle sul browser.
Nel seguente script per l’aggiornamento dell’indirizzo e-mail viene utilizzata la funzione errorInfo() combinata a un ciclo if, la cui condizione è la corretta esecuzione dello statement SQL. Se questo viene eseguito senza errori, il web server restituisce la stringa Update successful. Altrimenti viene eseguito il codice presentato sotto else.
Nell’esempio attuale informiamo l’utente che si è verificato un errore SQL e forniamo, tramite errorInfo(), lo statement SQL e le informazioni avanzate sugli errori:
Con errorInfo() PDO mette perciò a disposizione una funzione con cui si può richiamare in modo mirato informazioni avanzate sugli errori verificatisi nell’ultima operazione di database, ad esempio per visualizzarle sul browser.
Nel seguente script per l’aggiornamento dell’indirizzo e-mail viene utilizzata la funzione errorInfo() combinata a un ciclo if, la cui condizione è la corretta esecuzione dello statement SQL. Se questo viene eseguito senza errori, il web server restituisce la stringa Update successful. Altrimenti viene eseguito il codice presentato sotto else.
Nell’esempio attuale informiamo l’utente che si è verificato un errore SQL e forniamo, tramite errorInfo(), lo statement SQL e le informazioni avanzate sugli errori:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$statement = $pdo -> prepare("UPDATE user SET password = :password, updated_at = NOW() WHERE id = 1");
$statement ->bindParam(':password', $password);
$password = "YXDli_89%s";
if ($statement ->execute()){
echo "Update successful";
} else {
echo "SQL Error <br />";
echo $statement->queryString."<br />";
echo $statement->errorInfo()[2];
}
?>
Se eseguiamo lo script dal web server, riceviamo le seguenti informazioni:
SQL Error
UPDATE user SET password = :password, updated_at = NOW() WHERE id = 1
Table 'test.user' doesn't exist
Il comando SQL UPDATE si riferisce a una tabella con il nome user, ma il nome che abbiamo dato alla nostra tabella di esempio è users. Il server SQL non riesce perciò a trovare la tabella richiesta e restituisce il messaggio Table 'test.user' doesn't exist. La causa dell’errore è così solamente un errore di digitazione nello statement SQL; errore facilmente risolvibile.
Nel caso dei valori che vengono dati dalla funzione errorInfo() si tratta di un array che comprende tre elementi:
[0] = Il codice di errore SQL
[1] = I driver specifici del codice di errore
[2] = I driver specifici del messaggio di errore
Quali informazioni richiamate tramite errorInfo(), lo definite tramite l’indicazione dell’elemento desiderato tra parentesi quadre.
Nel caso dei valori che vengono dati dalla funzione errorInfo() si tratta di un array che comprende tre elementi:
[0] = Il codice di errore SQL
[1] = I driver specifici del codice di errore
[2] = I driver specifici del messaggio di errore
Quali informazioni richiamate tramite errorInfo(), lo definite tramite l’indicazione dell’elemento desiderato tra parentesi quadre.
Nella prassi le informazioni dettagliate sugli errori vengono però visualizzate raramente sul browser. Gli utenti hanno poche possibilità di riuscire a risolvere il problema con queste informazioni. Invece, i potenziali hacker sfruttano questi messaggi di errore per intercettare le richieste SQL e rilevare così le vulnerabilità di un’applicazione. Perciò si consiglia di informare gli utenti in maniera generica in caso di errori e di salvare internamente le informazioni relative a degli errori concreti. Si può ad esempio procedere nel modo seguente:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$statement = $pdo->prepare("UPDATE user SET password = :password, updated_at = NOW() WHERE id = 1");
$statement->bindParam(':password', $password);
$password = "YXDli_89%s";
if ($statement->execute()) {
echo "Update successful";
} else {
echo "Purtroppo si è verificato un errore nell’aggiornamento della password. Si prega di contattare il nostro amministratore all’indirizzo: admin@sito.it.";
$error = DATE("Y-m-d H:i:s") . " - " . __FILE__ . " - " . $statement->queryString . " - " . $statement->errorInfo()[2] . " \r\n";
file_put_contents("sqlerrors.log", $error, FILE_APPEND);
}
?>
Al posto di restituire sul browser un valore di ritorno della funzione errorInfo() tramite echo, lo salviamo nella variabile $error con il timestamp attuale, il percorso al file e con lo statement SQL coinvolto.
La funzione PHP DATE() restituisce il timestamp UNIX nel formato inserito. La cosiddetta costante magica __FILE__ ci fornisce l’intero percorso per il file test.php. Lo statement SQL corrente viene richiamato nell’esempio precedente tramite $statement->queryString. Infine i dati salvati su $error vengono memorizzati come testo nel file sqlerrors.log, contenuto nella cartella htdocs, grazie alla funzione file_put_contents().
La funzione PHP DATE() restituisce il timestamp UNIX nel formato inserito. La cosiddetta costante magica __FILE__ ci fornisce l’intero percorso per il file test.php. Lo statement SQL corrente viene richiamato nell’esempio precedente tramite $statement->queryString. Infine i dati salvati su $error vengono memorizzati come testo nel file sqlerrors.log, contenuto nella cartella htdocs, grazie alla funzione file_put_contents().
JOIN
Come scritto nel paragrafo sui database relazionali, possiamo interrogare contemporaneamente i dati di più tabelle. Dopo aver preso confidenza con le operazioni di database fondamentali, riprendiamole, perché vi dimostriamo a questo punto come collegare le diverse tabelle del vostro database in un join (in italiano “Connessione“ o “Unione“).
L’unione delle tabelle nell’ambito di una richiesta avviene grazie al comando SQL JOIN. Così vengono collegate due o più tabelle normalizzate attraverso delle colonne in comune. Ciò si può realizzare tramite chiavi esterne.
Dimostriamo il collegamento delle tabelle del database con il seguente esempio:
L’unione delle tabelle nell’ambito di una richiesta avviene grazie al comando SQL JOIN. Così vengono collegate due o più tabelle normalizzate attraverso delle colonne in comune. Ciò si può realizzare tramite chiavi esterne.
Dimostriamo il collegamento delle tabelle del database con il seguente esempio:
La tabella indica una selezione delle nostre canzoni preferite degli anni 60 e dovrebbe servire a rappresentazione di un esempio negativo di una struttura del database sbagliata.
Di primo acchito si nota che le tabelle presentano innumerevoli campi di dati ridondanti. Risolviamo la situazione, suddividendo i dati nell’ambito della normalizzazione in tabelle separate e collegandoli con chiavi esterne.
Di primo acchito si nota che le tabelle presentano innumerevoli campi di dati ridondanti. Risolviamo la situazione, suddividendo i dati nell’ambito della normalizzazione in tabelle separate e collegandoli con chiavi esterne.
Forme normali
Una buona struttura del database si contraddistingue per via della poca ridondanza. Si possono evitare le voci doppie tramite la normalizzazione delle tabelle. All’interno del modello di database relazionale si sono affermate tre forme normali che si basano le une sulle altre e che prestabiliscono regole fisse per la strutturazione ottimale dei dati.
Prima forma normale
Una tabella corrisponde alla prima forma normale, quando tutti i valori degli attributi sono atomici. I valori di un attributo si considerano atomici, quando contengono solo un’informazione. Questo concetto si può chiarire grazie al nostro esempio negativo.
Considerate, ad esempio, le colonne album_title e interpret nella tabella album. Al posto di eseguire le informazioni contenute nella tabella in una colonna separata, le abbiamo rese volutamente facili e abbiamo annotato le informazioni sull’anno di pubblicazione di un album e la presenza del disco semplicemente tra parentesi tonde, dietro il titolo dell’album e le indicazioni sull’interprete. Ma pagheremo le conseguenze della nostra scelta in un secondo momento, quando ad esempio vogliamo richiedere tutti i titoli che sono stati pubblicati in un anno in particolare.
Vi consigliamo perciò di creare le tabelle secondo le regole della prima forma normale. Per la nostra tabella di esempio sarebbe così:
Considerate, ad esempio, le colonne album_title e interpret nella tabella album. Al posto di eseguire le informazioni contenute nella tabella in una colonna separata, le abbiamo rese volutamente facili e abbiamo annotato le informazioni sull’anno di pubblicazione di un album e la presenza del disco semplicemente tra parentesi tonde, dietro il titolo dell’album e le indicazioni sull’interprete. Ma pagheremo le conseguenze della nostra scelta in un secondo momento, quando ad esempio vogliamo richiedere tutti i titoli che sono stati pubblicati in un anno in particolare.
Vi consigliamo perciò di creare le tabelle secondo le regole della prima forma normale. Per la nostra tabella di esempio sarebbe così:
Tutti i dati sono ora separati e analizzabili quindi senza ulteriori indugi. La nostra tabella però contiene ancora, come in precedenza, delle ridondanze. Vi mostriamo nei prossimi paragrafi come si possono eliminare.
Seconda forma normale
Una tabella corrisponde alla seconda forma normale quando tutte le condizioni della prima forma normale sono soddisfatte e ogni attributo non chiave è, da un punto di vista delle funzioni, completamente dipendente dalla chiave primaria globale delle tabelle.
Spesso le tabelle sono composte solo da una colonna che svolge la funzione di chiave primaria. Le tabelle di questo tipo corrispondono automaticamente a quelle della seconda forma normale, quando le condizioni della prima forma normale sono soddisfatte. A volte emerge che la chiave primaria di una tabella è composta da due colonne, come è il caso nella nostra tabella di esempio.
Per aprire un titolo desiderato dalla colonna title della tabella title, ci servirebbe album_id come anche il numero del titolo abbinato nella colonna track. Il titolo Sympathy for the Devil si può aprire ad esempio tramite la chiave primaria album_ID = 3 AND track = 1. Abbiamo quindi a che fare con una chiave primaria composta, necessaria però solo per le richieste che si riferiscono alla colonna title. Le colonne album_title, released, interpret e years_active dipendono esclusivamente da album_id. Per queste colonne non esiste così nessuna dipendenza funzionale della chiave primaria in comune. Le condizioni della seconda forma normale non sono perciò soddisfatte.
Ciò si può cambiare, memorizzando la colonna title in una nuova tabella e collegandola tramite una chiave esterna (album_id) con la tabella di destinazione:
Spesso le tabelle sono composte solo da una colonna che svolge la funzione di chiave primaria. Le tabelle di questo tipo corrispondono automaticamente a quelle della seconda forma normale, quando le condizioni della prima forma normale sono soddisfatte. A volte emerge che la chiave primaria di una tabella è composta da due colonne, come è il caso nella nostra tabella di esempio.
Per aprire un titolo desiderato dalla colonna title della tabella title, ci servirebbe album_id come anche il numero del titolo abbinato nella colonna track. Il titolo Sympathy for the Devil si può aprire ad esempio tramite la chiave primaria album_ID = 3 AND track = 1. Abbiamo quindi a che fare con una chiave primaria composta, necessaria però solo per le richieste che si riferiscono alla colonna title. Le colonne album_title, released, interpret e years_active dipendono esclusivamente da album_id. Per queste colonne non esiste così nessuna dipendenza funzionale della chiave primaria in comune. Le condizioni della seconda forma normale non sono perciò soddisfatte.
Ciò si può cambiare, memorizzando la colonna title in una nuova tabella e collegandola tramite una chiave esterna (album_id) con la tabella di destinazione:
La tabella elaborata album comprende solo un’unica chiave primaria e soddisfa così automaticamente le condizioni della seconda forma normale. La nuova tabella title contiene solo la colonna non chiave title, che è da entrambi le parti dipendente dalla chiave primaria (album_id e track), dal punto di vista funzionale, e corrisponde così anche a quella della seconda forma normale.
Ma anche nella seconda forma normale la nostra tabella di dati album comprende voci ridondanti.
Ma anche nella seconda forma normale la nostra tabella di dati album comprende voci ridondanti.
Terza forma normale
Se una tabella dovesse corrispondere alla terza forma normale, tutte le condizioni della seconda forma normale devono essere soddisfatte (e quindi anche quella della prima forma normale). Inoltre vale la regola che nessun attributo non chiave deve trovarsi in una dipendenza transitiva a un attributo chiave. Questa condizione sembra complicata, ma si può chiarire con poche parole: sussiste sempre una dipendenza transitiva quando un attributo non chiave dipende da un altro attributo non chiave.
Proprio questo coincide con la nostra tabella di esempio album. Qui si trovano le colonne interpret e years_active. Mentre l’interprete si può comunicare tramite album_id, l’indicazione dell’anno dell’uscita dell’album è di nuovo dipendente dall’interprete ed è quindi transitivo dall’ album_id. Questo porta con sé uno svantaggio, ovvero che il Database Management System salva automaticamente un valore ridondante nella colonna years_active, ogni qualvolta che viene inserito un nuovo album di un interprete già riprodotto.
Per soddisfare le condizioni della terza forma normale e quindi rimuovere tutte le ridondanze dalla nostra tabella, dobbiamo memorizzare anche la colonna interpret comprensiva di years_active in una tabella separata e collegarla tramite chiave esterna (interpret_id) con la tabella di destinazione album.
Otteniamo così tre tabelle normalizzate: interpret, album e title.
Proprio questo coincide con la nostra tabella di esempio album. Qui si trovano le colonne interpret e years_active. Mentre l’interprete si può comunicare tramite album_id, l’indicazione dell’anno dell’uscita dell’album è di nuovo dipendente dall’interprete ed è quindi transitivo dall’ album_id. Questo porta con sé uno svantaggio, ovvero che il Database Management System salva automaticamente un valore ridondante nella colonna years_active, ogni qualvolta che viene inserito un nuovo album di un interprete già riprodotto.
Per soddisfare le condizioni della terza forma normale e quindi rimuovere tutte le ridondanze dalla nostra tabella, dobbiamo memorizzare anche la colonna interpret comprensiva di years_active in una tabella separata e collegarla tramite chiave esterna (interpret_id) con la tabella di destinazione album.
Otteniamo così tre tabelle normalizzate: interpret, album e title.
Se ora volessimo riprodurre un titolo preciso nel nostro database comprensivo delle informazioni riguardanti album e inteprete, dovremmo collegare le tre tabelle separate con il comando SQL JOIN e la rispettiva chiave esterna.
Definire la chiave esterna tramite phpMyAdmin
Se avete scelto InnoDB come database engine, definite facilmente le relazioni della chiave esterna tramite l’interfaccia grafica del vostro software di amministrazione phpMyAdmin. Così può essere utilizzata la chiave primaria di una tabella come chiave esterna in molte altre tabelle.
Nel nostro esempio abbiamo bisogno di due connessioni, per collegare le tre tabelle normalizzate album, interpret e title:
Nel nostro esempio abbiamo bisogno di due connessioni, per collegare le tre tabelle normalizzate album, interpret e title:
- Per la prima connessione utilizziamo la chiave primaria album_id della tabella album come chiave esterna nella tabella title.
- Per la seconda connessione utilizziamo la chiave primaria interpret_id della tabella interpret come chiave esterna nella tabella album.
Per il collegamento di tabelle di dati è necessario che una colonna presenti sempre gli attributi UNIQUE o INDEX, così da poter fungere da chiave esterna.
La relazione tra chiave primaria e chiave esterna corrisponde di solito al tipo di relazione 1:n. Ogni campo di dati nella colonna della chiave primaria della tabella A è collegato a molti (n) campi di dati nella colonna della chiave esterna della tabella B. Ma ogni campo di dati nella colonna della chiave esterna della tabella B si riferisce sempre proprio a un campo di dati nella colonna della chiave primaria della tabella A. Ad esempio, abbiamo quattro voci nella colonna della chiave primaria album_id, contenuta nella tabella album, che sono collegate tramite la chiave esterna title.album_id con otto voci della tabella title.
La relazione tra chiave primaria e chiave esterna corrisponde di solito al tipo di relazione 1:n. Ogni campo di dati nella colonna della chiave primaria della tabella A è collegato a molti (n) campi di dati nella colonna della chiave esterna della tabella B. Ma ogni campo di dati nella colonna della chiave esterna della tabella B si riferisce sempre proprio a un campo di dati nella colonna della chiave primaria della tabella A. Ad esempio, abbiamo quattro voci nella colonna della chiave primaria album_id, contenuta nella tabella album, che sono collegate tramite la chiave esterna title.album_id con otto voci della tabella title.
Per impostare i collegamenti desiderati, creiamo le tabelle album, interpret e title su phpMyAdmin e definiamo la nostra chiave primaria nell’ambito della creazione della tabella, come già descritto, tramite l’opzione “Index”. Inoltre fate attenzione al fatto che le colonne che devono fungere dopo da chiave esterna, vengano contrassegnate ugualmente tramite l’opzione dell’indice come INDEX o UNIQUE. Per le relazioni 1:n si adatta però solo INDEX, perché i valori in un campo UNIQUE non si possono ripetere.
Nel prossimo passaggio definiamo la chiave esterna, prendendo ad esempio la tabella album. Perciò scegliamo la tabella nel pannello di amministrazione e apriamo la tab Structure nella barra del menu. Qui si trova il pulsante “Relation view“ (visualizzazione delle relazioni):
Nel prossimo passaggio definiamo la chiave esterna, prendendo ad esempio la tabella album. Perciò scegliamo la tabella nel pannello di amministrazione e apriamo la tab Structure nella barra del menu. Qui si trova il pulsante “Relation view“ (visualizzazione delle relazioni):
Tramite il campo di input "Foreign key constraints“, nella schermata delle relazioni di una tabella, definite le relazioni della chiave esterna:
Nella tabella album la colonna interpret_id deve fungere da chiave esterna, che si basa sulla chiave primaria interpret_id della tabella interpret.
Selezioniamo così dal menu a tendina sotto “Column“ la colonna interpret_id come chiave esterna. Fate attenzione che qua vengano eseguite solo le colonne che sono state contrassegnate come INDEX, UNIQUE o PRIMARY. Nel campo di input “Foreign key constraint (InnoDB)“, formato da tre parti, definiamo su quale chiave primaria di quale tabella di quale database si deve basare la nostra chiave esterna. Scegliamo come segue:
Database: test
Tabella: interpret
Chiave primaria: interpret_id
Il campo “Constraint name“ può rimanere vuoto. In questo caso il Database Management System assegna automaticamente un nome. Tuttavia è necessario che definiate come si debba comportare una tabella con la chiave esterna, nel caso in cui venga modificata o eliminata la chiave primaria che ne sta alla base.
Se ad esempio viene eliminato un interprete dalla tabella genitore interpret, scompare anche la chiave primaria connessa a questo record. Bisogna così chiarire cosa deve succedere con le voci, che si riferiscono a questo record tramite chiave esterna; nel nostro esempio si tratta quindi degli album di un interprete.
Per determinare il comportamento di una tabella con una chiave esterna nel caso di un UPDATE o DELETE, su MySQL o MariaDB sono a vostra disposizione quattro opzioni.
Database: test
Tabella: interpret
Chiave primaria: interpret_id
Il campo “Constraint name“ può rimanere vuoto. In questo caso il Database Management System assegna automaticamente un nome. Tuttavia è necessario che definiate come si debba comportare una tabella con la chiave esterna, nel caso in cui venga modificata o eliminata la chiave primaria che ne sta alla base.
Se ad esempio viene eliminato un interprete dalla tabella genitore interpret, scompare anche la chiave primaria connessa a questo record. Bisogna così chiarire cosa deve succedere con le voci, che si riferiscono a questo record tramite chiave esterna; nel nostro esempio si tratta quindi degli album di un interprete.
Per determinare il comportamento di una tabella con una chiave esterna nel caso di un UPDATE o DELETE, su MySQL o MariaDB sono a vostra disposizione quattro opzioni.
- RESTRICT: l’opzione RESTRICT esclude una modifica della tabella genitore, a patto che esistano altre tabelle, che rimandino a questa. Nel nostro caso un record non potrebbe essere eliminato dalla tabella interpret, se a questo vi sono collegati dei record nella tabella album.
- CASCADE: l’opzione CASCADE è responsabile del fatto che una modifica nella tabella genitore viene trasmessa a tutte le tabelle. Se ad esempio ci basiamo su interpret_id del gruppo The Rolling Stones da 2 a 8, questa modifica viene applicata nell’opzione Foreign key CASCADE anche per tutte le tabelle che utilizzano l’interpret_id come chiave esterna. Se viene eliminata una voce nella tabella genitore, questa porta al fatto che anche tutti i record collegati con questa voce vengano eliminati nelle altre tabelle. Attenzione: in questo modo se si cancellano singole voci, è possibile che si verifichi l’eliminazione di molteplici record.
- SET NULL: se selezionate l’opzione SET NULL, il valore nella colonna della chiave esterna viene impostato su NULL, non appena la chiave primaria nella tabella genitore viene cambiato o eliminato.
- NO ACTION: su MySQL l’opzione NO ACTION è l’equivalente dell’opzione RESTRICT.
Tipi di JOIN su MySQL e MariaDB
Le relazioni della chiave esterna vi consentono di aprire contemporaneamente dati da diverse tabelle con un unico statement SQL. Perciò su MySQL e MariaDB avete a disposizione quattro tipi di JOIN:
La sintassi di un INNER JOIN corrisponde al seguente schema di base:
- INNER JOIN: nel caso di un INNER JOIN il Database Management System ricerca delle voci comuni in entrambe le tabelle collegate tramite JOIN. Vengono letti solo i record in cui ci sono delle corrispondenze, ovvero laddove i valori nelle colonne collegate (chiave primaria e chiave esterna) coincidono in entrambe le tabelle.
- OUTER JOIN: in un OUTER JOIN si distingue tra tabella di sinistra e di destra. A differenza di un INNER JOIN non vengono solo letti i record in cui si trovano delle corrispondenze in entrambe le tabelle, ma anche tutti i record rimanenti della tabella di destra e/o di sinistra.
- LEFT JOIN: vengono letti tutti i record della tabella sinistra e quelli della tabella destra dove vi sono delle corrispondenze.
- RIGHT JOIN: vengono letti tutti i record della tabella destra e quelli della tabella sinistra in cui si trovano delle corrispondenze.
La sintassi di un INNER JOIN corrisponde al seguente schema di base:
SELECT * FROM tabella1
INNER JOIN tabella2 ON tabella1.chiaveesterna= tabella2.chiaveprimaria
WHERE colonna = valore
Il comando SQL SELECT combinato con il segnaposto * dà l’istruzione al Database Management System di analizzare i valori di tutte le colonne per far valere le condizioni delle clausole ON e WHERE.
Visto che si tratta di un INNER JOIN, vengono raccolti solo i record in cui si trova una corrispondenza tra la chiave esterna della tabella1 e la chiave primaria della tabella2, prese dal database. Inoltre grazie alla clausola WHERE si può definire un filtro opzionale.
Ve lo spieghiamo con un esempio che fa riferimento alle nostre tabelle normalizzate album, interpret e title:
Visto che si tratta di un INNER JOIN, vengono raccolti solo i record in cui si trova una corrispondenza tra la chiave esterna della tabella1 e la chiave primaria della tabella2, prese dal database. Inoltre grazie alla clausola WHERE si può definire un filtro opzionale.
Ve lo spieghiamo con un esempio che fa riferimento alle nostre tabelle normalizzate album, interpret e title:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM album
INNER JOIN interpret ON album.interpret_id = interpret.interpret_id";
foreach ($pdo->query($sql) as $row) {
echo $row['album_title'] . " was released by " . $row['interpret'] . " in " . $row['released'] . ".<br /><br />";
}
?>
Lo script di esempio vi indica un INNER JOIN, dove la tabella album viene collegata con quella interpret. Analizziamo solo i record in cui sussiste una corrispondenza tra chiave primaria ed esterna.
album.interpret_id = interpret.interpret_id
Nella nostra banca dati tutti questi sono dei record (un LEFT o RIGHT JOIN avrebbe perciò portato allo stesso risultato). Infine visualizziamo sul browser i valori letti tramite un ciclo foreach e il costrutto linguistico echo.
In uno statement SQL abbiamo così analizzato le indicazioni sull’interprete dalla tabella interpret e le informazioni sul titolo dell’album e l’anno di pubblicazione del disco contenute nella tabella album.
Con una condizione nella clausola WHERE si può delimitare quali record del join vengano visualizzati. Se ad esempio vogliamo solo visualizzare gli album che sono stati pubblicati nel 1968, possiamo procedere nella seguente maniera:
Con una condizione nella clausola WHERE si può delimitare quali record del join vengano visualizzati. Se ad esempio vogliamo solo visualizzare gli album che sono stati pubblicati nel 1968, possiamo procedere nella seguente maniera:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM album
INNER JOIN interpret ON album.interpret_id = interpret.interpret_id WHERE released = 1968";
foreach ($pdo->query($sql) as $row) {
echo $row['album_title'] . " was released by " . $row['interpret'] . " in " . $row['released'] . ".<br /><br />";
}
?>
Tramite la condizione WHERE released = 1968 limitiamo la riproduzione sul browser a un album. Beggars Banquet dei Rolling Stones è l’unico album nel nostro chiaro database, che è stato pubblicato nel 1968.
Grazie al comando JOIN si possono unire molte tabelle a una serie di dati. Nell’esempio seguente colleghiamo la tabella album in un INNER JOIN con le tabelle interpret e title, per poter visualizzare tutte le informazioni sui titoli delle canzoni salvate nel database.
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM album
INNER JOIN interpret ON album.interpret_id = interpret.interpret_id
INNER JOIN title ON album.album_id = title.album_id";
foreach ($pdo->query($sql) as $row) {
echo $row['title'] . " was released by " . $row['interpret'] . " as Track " . $row['track'] . " on " . $row['album_title'] . " in " . $row['released'] . ".<br /><br />";
}
?>
Anche qui, se necessario, possiamo definire una clausola WHERE con un filtro. Ad esempio quando vogliamo riprodurre solo le informazioni sulla traccia 7 dell’album “Abbey Road“.
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM album
INNER JOIN interpret ON album.interpret_id = interpret.interpret_id
INNER JOIN title ON album.album_id = title.album_id WHERE track = 7 AND album_title = 'Abbey Road'";
foreach ($pdo->query($sql) as $row) {
echo $row['title'] . " was released by " . $row['interpret'] . " as Track " . $row['track'] . " on " . $row['album_title'] . " in " . $row['released'] . ".<br /><br />";
}
?>
Fate attenzione al fatto che nella tabella title abbiamo a che fare con una chiave primaria multipla. Se volessimo reindirizzare a un titolo preciso, abbiamo bisogno, oltre che del numero della traccia, anche dell’album_id, che abbiamo creato insieme con il titolo dell’album nella tabella album.
Diventare degli esperti
Il nostro tutorial MySQL per principianti è solo un assaggio, che vi permette di avvicinarvi alle basi dei sistemi di database basati sul linguaggio SQL e che getta luce sulle semplici operazioni di database, grazie all’uso di esempi concreti. Se il vostro interesse travalicasse le possibilità di applicazione rappresentate, vi consigliamo di leggere la documentazione dei DBMS MySQL e MariaDB, che potete trovare nel paragrafo introduttivo. Inoltre su Internet trovate innumerevoli siti, che propongono tutorial ed esempi di applicazione per questo famoso Database Management System. In più si consiglia la piattaforma Internet Stack Overflow, dove un’attiva community di utenti di più 6,5 milioni di sviluppatori rispondono alle domande poste e si scambiano soluzioni ai problemi insorti durante lo sviluppo del software. Ovviamente anche qui su IONOS Digital Guide avete a disposizione molti articoli di approfondimento sui database, che trovate controllando tra i tag presenti.