1. Introduzione ai database

Per gestire i database MySQL che creiamo possiamo utilizzare diversi programmi: il più comune è phpMyAdmin. Si tratta di una web application che consente di gestire i dati da un’interfaccia. Per scaricarla sul nostro computer dobbiamo installare Xampp, una piattaforma per creare un server locale sul nostro computer scaricabile gratuitamente dal sito https://www.apachefriends.org. Per accedere a phpMyAdmin ci basterà aprire un qualsiasi browser e digitare l’indirizzo localhost/phpMyAdmin.

L’interfaccia di phpMyAdmin

L’interfaccia grafica di phpMyAdmin consente di creare database, tabelle e modificare ogni sua parte. Nel nostro lavoro quotidiano useremo l’interfaccia solo per controllare i dati inseriti nel database e per piccole modifiche. Gli inserimenti, le modifiche più importanti verranno fatte con un linguaggio di scripting che vedremo più avanti.

Qui in basso possiamo vedere la home page di phpMyAdmin. A sinistra possiamo vedere i database presenti, accanto ad ogni database c’è un tasto + che permette di vedere le tabelle al suo interno. Notiamo che ci sono alcuni database necessari al funzionamento di phpMyAdmin.

Cliccando su un database vediamo l’elenco delle sue tabelle, come mostrato nella foto sottostante. Cliccando su una tabelle possiamo vederne il contenuto, modificarle o creare nuove tabelle.

Creiamo un nuovo database

Dalla home di phpMyAdmin clicchiamo sul link in alto Database e poi scriviamo il nome del database che vogliamo creare nel campo input che si trova sotto la scritta crea un nuovo database.

Creiamo una nuova tabella

Per creare una tabella di un database selezioniamo il database e cerchiamo nella pagina il form per creare una nuova tabella: si trova in fondo dopo l’elenco delle tabelle, chiaramente se non ci sono tabelle sarà in alto, come nella foto sottostante.

Dopo aver deciso il nome della tabella e il numero dei campi, apparirà una nuova schermata, che consente di definire il nome e il tipo di dato di ogni singolo campo.

Modificare una tabella

È possibile modificare le tabelle già create da phpMyAdmin cliccando sul nome della tabella dall’elenco delle tabelle del database. Si aprirà una schermata in cui si vedono i dati della tabella ed è possibile eseguire alcune operazioni, come rinominare la tabella. Come mostrato in figura dall’interno di una tabella cliccare su operazioni (il tasto evidenziato in figura) e tra le varie operazioni possiamo rinominarla modificando il valore accanto alla voce Rinomina la tabella in.

Ceare un utente con phpMyAdmin

Dalla home page di phpMyAdmin clicchiamo su utenti e poi su aggiungi account utente. Si aprirà la schermata sottostante:

Nel campo utente metteremo il nome del nostro utente, nel campo host dobbiamo selezionare locale, nel campo password dobbiamo inserire una qualsiasi password (essendo in locale e non in un  ambiente reale non dobbiamo preoccuparci della sicurezza di questa password) e possiamo lasciare il resto invariato. Fatto questo dobbiamo assegnare i privilegi di questo utente: scendiamo un po’ più giù e troveremo il form per effettuare questa operazione, mostrato in figura:

Selezioniamo la voce seleziona tutto accanto alla scritta privilegi globali e poi salviamo. Abbiamo il creato un utente che avrà accesso a tutti i database del nostro computer.

Tipi di dati principali inseribili in un database

Scelta del tipo di dato

Il tipo di dato da scegliere è sempre il più piccolo in base alle nostre esigenze. Ad esempio se devo scegliere il tipo di dato per una variabile numeroPostiAuto sceglierò TINYINT perché so che un’auto non potrà mai avere più di 128 posti auto.

Quando scegliamo un tipo di dato MySQL memorizzerà per ogni riga lo spazio che occupa quel tipo di dato, ad esempio se per la variabile che abbiamo avessimo scelto INT, MySQL avrebbe riservato 11 byte a parità di valore inserito.

Questi operatori consentono di fare query un po’ più elaborate, e quindi di selezionare quello che ci serve con maggiore precisione. Consentono infatti di aggiungere delle condizioni alla query.

Ad esempio, guardiamo questa query:

SELECT * FROM tabella1 WHERE id<5 AND nome LIKE ‘Carlo’

La prima parte di questa query è l’ultima query di esempio del campo SELECT, a cui abbiamo aggiunto questa condizione:

AND nome LIKE ‘Carlo’

con questa aggiunta non basta più che l’id sia maggiore di 5 per essere selezionato, ma si aggiunge la condizione  per cui nome deve essere uguale a ‘Carlo’.

Vediamo ora il costrutto OR, consideriamo la seguente query:

SELECT * FROM tabella1 WHERE id<5 OR nome LIKE ‘Carlo’

La query è uguale a quella di prima, tranne per un dettaglio: al posto della parola chiave AND c’è la parola chiave OR. Questo fa cambiare completamente quello che restituisce la query: mentre prima venivano restituite tutte le riche che avevano id maggiore di 5 e nome Carlo, mettendo l’OR al posto di AND restituirà tutti i campi che avranno o id maggiore di 5 o nome Carlo. Il comando OR infatti per recuperare la riga chiede che almeno una delle due condizioni sia soddisfatta, non entrambe.

Vediamo ora il caso in cui dobbiamo selezionare tutte le righe di una tabella tranne quelle che rispecchiano una determinata condizione. Ad esempio, predndere tutti gli elementi di tabella1 tranne  quelli che hanno come nome carlo. In questo caso dobbiamo utilizzare l’operatore NOT preceduto dal campo che non deve essere uguale al valore che vogliamo evitare. Questo operatore però potrebbe essere usato in due modi

1. confronto di una stringa, come l’esempio sopra. In tal caso dobbiamo far seguire l’operatore NOT dalla parola LIKE ‘valoreDaEvitare’.

Nel caso di prima, la query è:

SELECT * FROM tabella1 WHERE nome NOT LIKE ‘Carlo’

2. I valori da confrontare sono il risultato di un’altra query, ad esempio voglio prendere tutte le righe di tabella 1 il cui id non è in tabella 2. Un caso potrebbe essere selezionare gli utenti che non hanno già ottenuto un determinato servizio. Ogni volta che l’utente ottiene quel servizio viene registrato in tabella2. In questo caso si usa l’operatore NOT seguito da IN.

SELECT * FROM tabella1 WHERE id NOT IN (SELECT id FROM tabella2)

Creare query più complesse

Una query di solito prevede più condizioni, alcune delle quali devono essere verificate prima di altre. Supponiamo di avere una tabella utente con questi campi:

• id
• nome
• cognome
• tipo
• eta
• anniEsperienza
• residenza

Supponiamo ora di voler estrarre che un utente sia di tipo “operaio”, lavori da 10 anni, abbia un’età inferiore ai 35 anni e risieda a Catania o Palermo.

SELECT * FROM utente WHERE tipo LIKE ‘operaio’ AND eta <35 AND anniEsperienza >10 AND (residenza LIKE ‘Catania’ OR residenza LIKE ‘Palermo’)

Le parole chiave COUNT e SUM

Può capitare di dover semplicemente contare le occorrenze di una tabella, oppure di sommare un certo numero di valori (Ad esempio l’incasso di un giorno). Per questo ci vengono in aiuto i comandi MySQL COUNT e SUM.

Il comando COUNT consente di contare le occorrenze che rispettano le condizioni che vogliamo inserire, ad esempio la query:

SELECT COUNT(*) FROM articoli

restituirà il numero totale degli articoli nel database. Possiamo ovviamente creare query più complesse, come ad esempio:

SELECT COUNT(*) FROM articoli WHERE id=1

in questo caso la query restituirà solo il numero degli articoli con id categoria 1.

Il comando SUM consente di sommare i campi numerici delle occorrenze di una tabella, senza  costringerci ad elaborare queste operazioni con il linguaggio di programmazione che usiamo. Facciamo un esempio, supponiamo di avere questa tabella ordini (ovviamente molto semplificata):

CREATE TABLE `ordini` (

        `id` INT( 11 ) NOT NULL AUTO_INCREMENT ,

        `idUtente` INT( 11 ) NOT NULL ,

        `importo` INT( 11 ) NOT NULL ,

        `dataOrdine` DATE NOT NULL ,

        PRIMARY KEY ( `id` )

        ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

Per sapere l’incasso totale effettuato possiamo eseguire questa query:

SELECT SUM(importo) FROM ordini

Se invece vogliamo sapere solo l’incasso di un dato giorno (ad esempio il primo Gennaio 2020) possiamo scrivere questa query:

SELECT SUM(importo) FROM ordini WHERE dataOrdine LIKE ‘2020-01-01’

Cercare occorrenze nel database contenenti parte di una parola chiave

Molto spesso può capitare di cercare nel database righe che non presentano una corrispondenza esatta con la parola chiave. Si pensi ad esempio quando si scrivono i campi del motore di ricerca di un sito che parla di automobili: se un utente scrive come parola chiave “Ferrari” la query di ricerca non deve restituire gli articoli che hanno come titolo Ferrari, ma che contengono la parola Ferrari. Ad esempio potrebbe restituire risultati di questo tipo:

• Ferrari SF90: la nuova monoposto per il mondiale F1 2019
• Mick Schumacher in Ferrari Driver Academy: è ufficiale
• International GT Open 2018 a Monza con la Ferrari prima in classifica

Si tratta di titoli di articoli che contengono la parola Ferrari, ma anche molte altre. Questo si ottiene con la query di ricerca:

SELECT * FROM articoli WHERE titolo LIKE ‘%Ferrari%’

soffermiamoci un attimo ad osservare la parte finale della query: LIKE ‘%Ferrari%’: notiamo la presenza del simbolo % prima e dopo la parola Ferrari. Quel simbolo indica a MySQL che prima e dopo la parola chiave specificata possono esserci altre parole. Da una query di questo tipo possono scaturire i risultati visti sopra, altrimenti se avessimo scritto questa query:

SELECT * FROM articoli WHERE titolo LIKE ‘Ferrari’

non avremmo ottenuto alcun risultato, in quanto gli articoli visti sopra hanno titoli che contengono la parola Ferrari, ma non solo questa parola. 

Sono possibili anche due varianti di questa query:

SELECT * FROM articoli WHERE titolo LIKE ‘Ferrari%’

In questo caso il simbolo % sta solo dopo la parola Ferrari, quindi questa query cerca tutti gli articoli che iniziano con la parola Ferrari, e possono essere seguiti da un qualsiasi numero di parole.

SELECT * FROM articoli WHERE titolo LIKE ‘%Ferrari’

In questo caso il simbolo % sta solo prima della parola Ferrari, quindi questa query cerca tutti gli articoli che termineranno con la parola Ferrari, e possono essere preceduti da un qualsiasi numero di parole.

Può capitare di dover recuperare le informazioni da più di una tabella, ad esempio supponiamo di dover recuperare le informazioni di un articolo e quelle della sua categoria. Tra la tabella articolo e la tabella categoria che è una relazione 1 a N, nella tabella articoli c’è un campo categoria che contiene l’id della categoria. Potremmo pensare di fare due query, la prima per recuperare i dati dell’articolo, la seconda per recuperare i dati della categoria.

Questo però è inefficiente, perché possiamo creare un’unica query che ci consente di recuperare tutte le informazioni che ci servono, grazie alla clausola JOIN. Vediamo un esempio

SELECT * FROM articoli AS a JOIN categorie AS c

ON a.categoria = c.id

La parola chiave AS serve per facilitare la scrittura della query, per riferirsi alle tabelle con un alias più compatto. Ad esempio quando scriviamo … articoli AS a … stiamo comunicando al database che d’ora in avanti nella query ci riferiremmo alla tabella articoli con la parola a. Analogamente la scritta … categorie AS c … indica che d’ora in avanti nella query ci riferiremmo alla tabella categoria con la parola chiave c.

La clausola JOIN comunica al database che deve restituire i valori di entrambe le tabelle. La parola chiave ON serve per specificare qual è la relazione tra le due tabelle.

Nota bene: quando usiamo la clausola JOIN MySQL restituirà solo le occorrenze che rispettano tale clausola.

Con riferimento alla query sopra supponiamo di avere queste tabelle:

Tabella articoli

Tabella categorie

In questo caso la query di inizio pagina restituirà solamente due righe, perché non troverà alcuna corrispondenza nella tabella categorie per l’articolo con id = 3.  Per lo stesso motivo non verrà mostrata l’occorrenza Categoria 2, perché nella tabella articoli non c’è nessun articolo che è collegato ad essa.

Usare WHERE nelle query con JOIN

Come in altri contesti possiamo trovarci a voler estrarre solo poche righe e non l’intero contenuto della tabella. Anche in questo caso possiamo usare la parola chiave WHERE., ad esempio:

SELECT * FROM articoli AS a JOIN categorie AS c

ON a.categoria = c.id

WHERE a.id = 3

Dal momento che stiamo cercando tra più tabelle, le limitazioni devono indicare di quale tabella si tratta, per questo le colonne di una tabella vengono precedute dal carattere (o parola) indicato all’inizio della query dopo la parola AS.

NOTA: anche in questo caso vale la regola descritta a pagina precedente, se eseguissimo la query in database con le tabelle descritte non otterremmo alcun risultato perché un articolo con id 3 è presente, ma MySQL non troverebbe alcuna corrispondenza per categoria = 0

Selezionare i dati in output

Osserviamo la query fatta sopra: notiamo che inizia con SELECT * … il che significa che stiamo chiedendo al database di recuperare tutti i dati. Essendo un JOIN il risultato di questa query restituirà tutte le colonne di entrambe le tabelle. Possiamo impostare la query in modo che ci restituisca solo una parte di questi dati, ad esempio:

SELECT a.id, a.titolo, c.nome FROM articoli AS a JOIN categorie AS c

ON a.categoria = c.id

WHERE a.id = 3

con questa query cosi fatta stiamo chiedendo al database di darci solo tre elementi: l’id dell’articolo, il titolo dell’articolo e il nome della categoria

SELECT a.id, c.* FROM articoli AS a JOIN categorie AS c

ON a.categoria = c.id

WHERE a.id = 3

con quest’altra query invece abbiamo chiesto al database di restituirci l’id dell’articolo e tutte le colonne della tabella categorie.

Ci sono dei casi in cui vogliamo dare un nome diverso alla colonna, ad esempio perché ci sono due colonne che hanno lo stesso nome. Possiamo farlo aggiungendo la parola chiave AS seguita dal nome che vogliamo assegnare. Ad esempio:

SELECT a.id, a.titolo, c.id AS idCategoria, c.nome FROM articoli AS a JOIN categorie AS c

ON a.categoria = c.id

WHERE a.id = 3