2. Gestire un database da interfaccia grafica

Cos’è un database?

Un database è uno speciale tipo di file progettato per immagazzinare grandi quantità di informazioni e per essere utilizzato dai programmi per memorizzare i dati necessari al loro funzionamento. Esistono vari tipi di database, quelli che vedremo noi sono di tipo MySQL. Altri database famosi sono Oracle, Sql server (il database di Microsoft) e MongoDb.

Differenza tra un database e un altro tipo di file

La differenza tra un database e un normale file sta nella velocità di recupero delle informazioni. Supponiamo di salvare in un database le impostazioni del nostro programma (Ad esempio il nome azienda, indirizzo, nome sito ed email). 

Queste informazioni possono essere salvate tranquillamente su un file, ad esempio un file .txt. Il tempo necessario al nostro programma per recuperare le informazioni, però, è di 3 -4 volte superiore se vengono prese dai file, e questa differenza aumenta al crescere della quantità dei dati da immagazzinare. Questo perché nel database oltre ai dati che inserisce l’utente il database salva altre informazioni per essere più rapido nella risposta.

Struttura di un database

I database sono organizzati in tabelle, che al loro interno conengono delle colonne dove vengono inseriti i dati. Si veda la figura sottostante.

Le colonne di una tabella possono contenere dati di diverso tipo, che occupano una diversa capacità nel database. Per ogni colonna quindi andremo a scrivere il tipo di dato, e il database si regolerà sulla quantità di spazio che deve riservare ad ognuna di esse. Molto spesso le tabelle hanno una colonna chiamata id che serve come identificativo di quella riga. Immaginiamo di avere una tabella contenente centinaia di utenti. Per identificarli in modo univoco possiamo creare una colonna chiamata id, che assegna un id unico ad ogni riga. Più avanti approfondiremo questo concetto.

Gli utenti di un database

MySQL consente di creare degli utenti e stabilire quali utenti hanno accesso a un determinato database. Possiamo anche stabilire cosa può fare un utente in quel database, ad esempio possiamo stabilire che un utente abbia accesso solo in lettura, un altro utente abbia accesso in lettura e scrittura, ma non gli sia consentito di cancellare elementi, e molte altre opzioni. Quando creiamo un utente di un database dobbiamo comunicare a MySQL cosa questo utente può fare. Le operazioni che un utente può compiere sono chiamate privilegi e vengono assegnate per ciascun database. Se diamo pieni poteri ad un utente su un database si dice che gli stiamo assegnando tutti i privilegi. Ad esempio può esserci l’utente mario che può accedere al database database1 con tutti i privilegi, e poi potremmo avere un altro utente antonio che può accedere al database1 in lettura e al database2 con tutti i privilegi.

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.

Ad ogni tabella di un database corrisponde un’entità (ad esempio la tabella auto, la tabella attori, la tabella cinema, la tabella utenti e cosi via), ma le tabelle possono aver bisogno di essere collegate le une alle altre. Per fare questo esiste

Abbiamo diversi tipi di relazioni

• relazione 1 a 1
  Supponiamo che un’azienda voglia memorizzare gli indirizzi di residenza dei propri utenti. Questa è una relazione 1 a 1 perché ad ogni utente corrisponde un indirizzo. Per fare questa associazione possiamo inserire nella tabella utenti l’id dell’indirizzo, e nella tabella indirizzo l’id dell’utente.
• relazione 1 a N
  supponiamo di avere una tabella utenti e una tabella ordini. Ad ogni ordine corrisponde un solo utente, ovviamente un utente può aver fatto più ordini. Per fare questa associazione inseriamo nella nostra tabella ordini un campo idUtente, dove memorizzeremo l’id dell’utente che ha eseguito l’ordine
• relazioni N a N
  supponiamo di avere una tabella ordini e una tabella prodotti. In ogni ordine è possibile inserire più prodotti, e ovviamente un prodotto può comparire in più ordini. Per fare questa associazione ci serve una nuova tabella, contenente l’id dell’ordine e l’id del prodotto. Il codice MySQL per creare questa tabella potrebbe essere:

	CREATE TABLE `associazioneOrdiniProdotto` (

	        `idOrdine` INT( 11 ) NOT NULL ,

	        `idProdotto` INT( 11 ) NOT NULL ,

	        ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

è possibile aggiungere degli attributi alle relazioni N a N, ad esempio potremmo voler memorizzare la quantità di un determinato prodotto in un ordine. In questo caso la query per la creazione diventerebbe:

	CREATE TABLE `associazioneOrdiniProdotto` (

	        `idOrdine` INT( 11 ) NOT NULL ,

	        `idProdotto` INT( 11 ) NOT NULL ,

	        `quantita` INT( 11 ) NOT NULL ,

	        ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;