In questo articolo potrai imparare ad usare alcuni comandi del linguaggio SQL di base, che puoi applicare nel sistema di gestione di database open source MySQL (o in altri database che usano lo stesso linguaggio).
MySQL è il gestore di database relazionale più comunemente usato. La sua facilità d’uso e la licenza open source hanno notevolmente contribuito a questa popolarità. Nell’articolo che trovi seguendo il link potrai trovare le istruzioni su come installare e configurare MySQL in Ubuntu.
Se vorrai proseguire nella lettura di questo articolo, invece ti presenterò diverse funzionalità MySQL, per lo più attinenti alle nozioni di base.
Nota: le query MySQL (comandi) non fanno distinzione tra maiuscole e minuscole; tuttavia, è prassi comune utilizzare i caratteri TUTTI I MAIUSCOLI per le parole chiave di comando effettive e i caratteri minuscoli per il resto.
Connessione e disconnessione dal server MySQL
Per poter inserire le query SQL, devi prima di tutto connetterti al server usando MySQL e utilizzare il prompt MySQL. Il comando per fare questo è:
mysql -h nome_host -u utente -p
-h è usato per specificare un nome host (se il server si trova su un’altra macchina, se non lo è, basta ometterlo), -u indica l’utente da usare e -p specifica che si desidera inserire una password.
Sebbene non sia raccomandato (per motivi di sicurezza), è possibile inserire la password direttamente nel comando digitandola subito dopo -p. Ad esempio, se la password per test_user è 1234 e si sta tentando di connettersi alla macchina locale che si sta utilizzando, è possibile utilizzare:
mysql -u test_user -p1234
Se hai immesso correttamente i parametri richiesti, verrai accolto dal prompt della shell MySQL (mysql>).
Per disconnettersi dal server e lasciare il prompt mysql, digitare:
QUIT
Come alternative puoi anche digitare quit (MySQL è case insensitive) o \q. Premi Invio per uscire.
Puoi anche generare informazioni sulla versione con un semplice comando:
sudo mysqladmin -u versione radice -p
Nota: assicurati di essere connesso al server prima di inserire una delle query che sono indicate in questo articolo.
Se vuoi vedere un elenco di tutte le opzioni disponibili, usa:
mysql --help
Linguaggio SQL: utilizzo delle query
MySQL memorizza i dati nelle tabelle e utilizza comandi chiamati query (SQL significa Structured Query Language, ovvero linguaggio di query strutturato).
Prima di passare alla memorizzazione, all’accesso e alla modifica dei dati, andremo ad occuparci delle query di base.
Poiché MySQL utilizza le tabelle, anche l’output delle query verrà visualizzato in forma tabellare.
Tutti i comandi SQL devono essere seguiti da un punto e virgola (;), sebbene esistano delle eccezioni (in particolare: QUIT).
Puoi invece separare le colonne con una virgola (,).
Ecco alcuni esempi di base:
mysql> SELECT VERSION (); mysql> SELEZIONA CURRENT_DATE; mysql> SELECT VERSION (), CURRENT_DATE;
Ad esempio, la terza query dovrebbe stampare qualcosa di simile a questo:
+ ---------------- + ---------------------- + | VERSIONE () | CURRENT_DATE | + ---------------- + ---------------------- + | 8.0.15 | 2019-04-13 | + ---------------- + ---------------------- + 1 riga in set (0,41 sec)
Poiché il punto e virgola (;) segna la fine di un’istruzione, è anche possibile scrivere più istruzioni su una singola riga.
Ad esempio, invece di:
mysql> SELECT VERSION (); mysql> SELEZIONA CURRENT_DATE;
Potresti anche scrivere:
mysql> SELECT VERSION (); SELEZIONA CURRENT_DATE;
Per migliorare la leggibilità se vuoi puoi anche inserire query su più righe (se non includi un punto e virgola alla fine della riga prima di premere Invio). In tal caso, MySQL inserirà semplicemente un prompt diverso per consentire di continuare il comando.
Per esempio:
mysql> SELEZIONA -> VERSIONE () ->, -> CURRENT_DATE;
Le query multilinea si verificano anche se non si termina una stringa su una riga (una parola circondata da “o”).
Se si desidera annullare una query, digitare \c e premere Invio.
Questi prompts hanno significati diversi:
- mysql> = pronto per una nuova query
- -> = in attesa della riga successiva della query su più righe
- ‘> = In attesa della riga successiva, in attesa del completamento di una stringa iniziata con una virgoletta singola (‘)
- “> = In attesa della riga successiva, in attesa del completamento di una stringa iniziata con una virgoletta doppia (“)
- `> = in attesa della riga successiva, in attesa del completamento di un identificatore iniziato con un apice (`)
- / *> = in attesa della riga successiva, in attesa del completamento di un commento iniziato con / *
È anche possibile stampare l’ora corrente (hh: mm: ss) insieme alla data corrente con il comando NOW (), così come è possibile conoscere l’utente con cui ci si è connessi usando USER ():
mysql> SELECT NOW (); mysql> SELECT USER ();
Questi comandi produrranno qualcosa di simile a questo:
+ --------------------------- + | NOW () | + --------------------------- + | 2019-04-13 23:53:48 | + --------------------------- + 1 riga in set (0,00 secondi) + ---------------------- + | USER () | + ---------------------- + | root @ localhost | + ---------------------- + 1 riga in set (0,00 secondi)
MySQL ti consente anche di eseguire calcoli matematici; se digiti:
mysql> SELECT COS (PI () / 3), (10-2 + 4) / 3;
Avrai come risultato:
+ ----------------------------- + ------------------- + | COS (PI () / 3 ) | (10-2 + 4) / 3 | + ----------------------------- + ------------------- + | 0,5000000000000001 | 4.0000 | + ----------------------------- + ------------------- +
Sintassi SQL: come usare i database in MySQL
1. Ottenere informazioni sui database
Prima di tutto, puoi elencare i database disponibili con:
mysql> SHOW DATABASES;
Puoi anche vedere il database selezionato con:
mysql> SELECT DATABASE ();
Questo produrrà NULL se nessun database è selezionato.
Ecco un esempio di output per le due affermazioni citate:
+ ----------------------------- + | Database | + ----------------------------- + | information_schema | | mysql | | performance_schema | | sys | + ----------------------------- + + ----------------- + | DATABASE () | + ----------------- + | NULL | + ----------------- +
2. Creazione di database
La creazione di un database viene fatta semplicemente inserendo il comando:
mysql> CREATE DATABASE example_db;
Nota: in Ubuntu (o qualsiasi altro sistema basato su Unix) i nomi del database e delle tabelle sono sensibili al maiuscolo/minuscolo.
3. Selezione dei database
Per selezionare un database, devi menzionare quello che vuoi usare tramite la seguente istruzione SQL:
mysql> USE example_db;
In caso di successo, riceverai il messaggio:
Database changed
In caso contrario, riceverai un errore che ti dice che MySQL non riesce a trovare il database specificato.
È anche possibile selezionare un database quando ci si connette al server citando il nome di un database esistente alla fine del comando:
mysql -h nome_host -u nome_utente -p esempio_tabella
Per esempio:
mysql -u root -p esempio_tabella
Utilizzare le tabelle in SQL
1. Ottenere informazioni sulle tabelle
Per elencare le tabelle nel database corrente, utilizzare:
mysql> SHOW TABLES;
Nota: assicurarsi di aver preventivamente selezionato un database.
Se il database è vuoto (ad esempio un db nuovo appena creato), l’output sarà:
Empty set (0,00 secondi)
Dopo aver creato le tabelle, l’output sarà lungo la linea di:
+ ------------------------------ + | Tables_in_example_db | + ------------------------------ + | table_1 | | table_2 | + ------------------------------ + 1 riga in set (0,00 secondi)
2. Creazione di tabelle
Per creare le tabelle, devi specificare il layout: le colonne e il tipo di dati che devono essere archiviati.
Nell’esempio qui sotto, è indicato il comando per memorizzare le informazioni su un gruppo di persone: nome, data di nascita, sesso, nazione.
Ecco come è possibile creare questa tabella tramite l’istruzione SQL standard:
mysql> CREATE TABLE tabella_1 (nome VARCHAR (30), data_di_nascita DATE, sesso CHAR (1), nazione VARCHAR (40));
Nota: è anche possibile scrivere il comando su più righe.
Potrai notare che è stato indicato il nome della tabella (tabella_1) e il nome delle colonne (nome, data di nascita, sesso, paese). Dopo i nomi delle colonne, va specificato il tipo di dati che vengono memorizzati. VARCHAR (n) sono stringhe di un massimo di n caratteri di lunghezza, DATE è auto-esplicativo (formato CCYY-MM-DD) e CHAR (1) significa un singolo carattere (in particolare, intendo usare ‘m’ e ‘f ‘per maschio e femmina). Altri tipi comuni includono INT (interi), BOOL (booleani), TIME (hh: mm: ss).
Ci sono molti tipi di dati disponibili per l’uso in MySQL (numerico, stringa, data e ora), ma puoi anche utilizzare tipi di dati più complessi, come ad esempio AUTO_INCREMENT.
La tabella verrà ora visualizzata se usi il comando SHOW TABLES.
Se lo desideri, puoi modificare il layout di una tabella usando ALTER TABLE:
mysql> ALTER TABLE tabella_1 ADD email VARCHAR (50); mysql> ALTER TABLE tabella_1 DROP data_di_nascita;
Questi esempi hanno aggiunto una colonna (primo esempio) e cancellato una colonna (secondo esempio).
3. Descrizione delle tabelle
Puoi vedere la struttura di una tabella in qualsiasi momento con:
mysql> DESCRIBE <nome_tabella>;
Per esempio:
mysql> DESCRIBE tabella_1;
produrrà come uscita:
+ ------------------- + ---------------- + ----- + -------- + ------------ + ------ + | Campo | Tipo | Null | Chiave | Predefinito | Extra | + ------------------- + ---------------- + ----- + -------- + ------------ + ------ + | nome | varchar (30) | SÌ | | NULL | | | data_di_nascita | data | SÌ | | NULL | | | sesso | char (1) | SÌ | | NULL | | | nazione | varchar (40) | SÌ | | NULL | | + ------------------- + ---------------- + ----- + -------- + ------------ + ------ + 4 righe in set (0,00 secondi)
4. Eliminazione di tabelle
Il comando per l’eliminazione delle tabelle è:
DROP TABLE <nome_tabella>;
5. Inserimento di dati nelle tabelle
Per inserire dati, devi specificare i valori da introdurre in ogni colonna (nello stesso ordine della definizione della tabella). Per valori vuoti o sconosciuti dovresti usare NULL. Assicurati che i valori non numerici siano tra virgolette (‘) o doppie virgolette (“). I valori dovrebbero essere separati da virgole (,).
Ecco alcuni esempi per Giorgio e Sarah:
mysql> INSERT INTO tabella_1 VALUES ('Giorgio', '1970-06-24', 'm', 'Italia');
mysql> INSERT INTO tabella_1 VALUES ('Sarah', '1984-08-19', 'f', NULL);
6. Cancellare i contenuti dalle tabelle
Se vuoi svuotare una tabella (cancellare tutte le righe), usa il comando SQL:
DELETE FROM nome_tabella;
Utilizzando WHERE, puoi eliminare righe specifiche:
DELETE FROM <nome_tabella> WHERE <nome_colonna> = valore
7. Aggiornamento delle voci di tabella
La sintassi per l’aggiornamento di una voce è:
UPDATE nome_tabella SET col = 'valore' WHERE condizioni
Per esempio:
UPDATE tabella_1 SET nazione = 'Italia' WHERE nome = 'Roberto'
Se non si specificano le condizioni, tutte le voci verranno modificate.
8. Linguaggio SQL: recupero dei dati dalle tabelle
Il comando MySQL utilizzato per estrarre i dati dalle tabelle è SELECT. La struttura di una tale affermazione è:
SELECT cosa FROM dove WHERE condizioni;
Vedremo insieme alcune applicazioni comuni, in modo che tu possa capire come estrarre esattamente quello che vuoi dal database.
Selezione di tutti i dati
Prima di tutto, l’uso più semplice è quello di visualizzare tutti i dati da una tabella. Per esempio:
mysql> SELECT * FROM tabella_1;
Il carattere jolly (*) sta per tutto, tabella_1 è la tabella da cui voglio estrarre le informazioni. Puoi vedere che ho omesso la parte WHERE; è facoltativo avere condizioni per i dati selezionati.
Selezione di dati specifici
Prima di tutto andremo a selezionare le righe di nostro interesse.
Per selezionare righe particolari, devi specificare le condizioni che restringono i dati:
mysql> SELECT * FROM tabella_1 WHERE nome = 'Sarah'; mysql> SELECT * FROM tabella_1 WHERE sesso = 'm'; mysql> SELECT * FROM tabella_1 WHERE data_di_nascita <'1990-1-1'; mysql> SELECT * FROM tabella_1 WHERE sesso = 'f' AND data_di_nascita > '1991-1-1'; mysql> SELECT * FROM tabella_1 WHERE sesso = 'm' OR nazione= 'France'; mysql> SELECT * FROM tabella_1 WHERE data_di_nascita IS NOT NULL;
Nei primi due esempi, ho semplicemente confrontato stringhe (maiuscole e minuscole). Puoi anche confrontare valori come date e numeri interi con operatori di confronto (>, <,> =, <=, =). <> è usato per indicare “non uguale”. È possibile specificare più condizioni utilizzando operatori logici (AND, OR). Tieni però conto che AND ha precedenza più alta di OR. È meglio usare le parentesi quando si hanno condizioni più complesse.
IS NOT NULL è un modo per visualizzare solo le righe che non hanno un valore per la colonna specificata.
Non è possibile utilizzare gli operatori di confronto aritmetico con NULL, poiché rappresenta un valore mancante (anche il risultato sarà NULL). È necessario utilizzare IS NULL e IS NOT NULL.
Ora ti parlerò della visualizzazione di colonne specifiche.
Per fare ciò, devi specificare le colonne che desideri visualizzare, separate da virgole. Per esempio:
mysql> SELECT nome, data_di_nascita FROM tabella_1;
Puoi anche sbarazzarti dei dati ripetuti. Ad esempio, se desidero ottenere tutte le date di nascita (senza ottenere lo stesso valore più volte se più persone sono nate in quella data), userò:
mysql> SELECT DISTINCT data_di_nascita FROM tabella_1;
Questo mostrerà solo i risultati DISTINCT.
Per essere ancora più specifici, puoi combinare la visualizzazione di particolari colonne con condizioni (WHERE):
mysql> SELECT nome, sesso FROM tabella_1 WHERE country = 'Francia' AND data_di_nascita <'1991-1-1';
Ordinamento dei dati
Per ordinare i dati, devi utilizzare ORDER_BY:
mysql> SELECT nome FROM tabella_1 ORDER BY data_di_nascita;
Puoi vedere che ho combinato questo con la selezione di dati specifici.
Il comando sopra mostrerà i nomi di tutte le voci, ordinate in ordine crescente e per data di nascita.
Puoi anche ordinare in ordine decrescente:
mysql> SELECT nome FROM tabella_1 ORDER BY data_di_nascita DESC;
L’ordinamento può essere applicato su più colonne. Ad esempio, per ordinare in ordine decrescente per data di nascita, e le persone nate alla stessa data in ordine crescente per nome, dovrai usare:
mysql> SELECT nome FROM tabella_1 ORDER BY data_di_nascita DESC, nome;
Manipolazione delle date
Puoi ottenere la data corrente puoi usare CURDATE (). Usando questo comando e un altro anno, puoi calcolare una differenza (ad es. per ottenere l’età di una persona) con TIMESTAMPDIFF ():
mysql> SELECT nome, data_di_nascita, CURDATE (), -> TIMESTAMPDIFF (ANNO, data_di_nascita, CURDATE ()) AS età -> FROM tabella_1 ORDER BY età;
Ecco l’output che potrai ottenere:
+ ------ + --------------- + ---------- + --- + | nome | data di nascita | CURDATE () | età | + ------ + --------------- + ---------- + --- + | Emilia | 1994-07-19 | 2019-05-13 | 24 | | Daniela| 1992-08-04 | 2019-05-13 | 26 | | Gianna | 1992-08-04 | 2019-05-13 | 26 | | Ugo | 1985-03-11 | 2019-05-13 | 34 | | Sarah | 1980-04-24 | 2019-05-13 | 38 | + ------ + --------------- + ---------- + --- +
TIMESTAMPDIFF () accetta come argomenti l’unità da utilizzare per il risultato (ANNO) e due date (data di nascita, CURDATE ()) per cui calcolare la differenza. La parola chiave AS (alias) nomina la colonna risultante e facilita il lavoro (in questo esempio: ordinamento per età).
Per fare riferimento a parti specifiche delle date, è possibile utilizzare YEAR (), MONTH () e DAYOFMONTH (), utilizzando la data come argomento.
Per esempio:
mysql> SELECT nome, data di nascita, MESE (data di nascita) FROM tabella_1;
È possibile confrontare i risultati (mesi, anni, giorni) proprio come i numeri normali. Tuttavia, per confrontarli con cose come il mese successivo, non puoi semplicemente aggiungere il valore 1 a CURDATE (), poiché ciò potrebbe farti controllare per il mese 13 o altro valore che non ha senso. La soluzione alternativa è INTERVAL e DATE_ADD ():
mysql> SELECT nome, data_di_nascita -> FROM tabella_1 -> WHERE MESE (data_di_nascita) = MESE (DATE_ADD (CURDATE (), INTERVAL 1 MONTH));
Puoi anche usare l’operatore modulo (MOD):
mysql> nome SELECT, data_di_nascita -> FROM animali -> WHERE MESE (data_di_nascita) = MOD (MESE (CURDATE ()), 12) + 1;
L’utilizzo di date non valide restituirà NULL e produrrà WARNINGS, che potrai vedere con:
mysql> SHOW WARNINGS;
Usare il pattern matching
In MySQL, il carattere “_” rappresenta qualsiasi carattere singolo e il carattere “%” rappresenta caratteri maggiori di 0. Questi modelli sono (per impostazione predefinita) senza distinzione tra maiuscole e minuscole. Invece di = e <>, per usare i pattern devi usare LIKE e NOT LIKE:
mysql> SELECT birth_date FROM tabella_1 WHERE name LIKE '%a%'; mysql> SELECT birth_date FROM tabella_1 WHERE name LIKE '%b'; mysql> SELECT birth_date FROM tabella_1 WHERE name LIKE 'c%'; mysql> SELECT * FROM tabella_1 WHERE name LIKE '___';
Questi esempi selezionano le date di nascita delle voci in cui il nome contiene ‘a’ (primo esempio), termina con ‘b’ (secondo esempio) o inizia con ‘c’ (terzo esempio). L’ultimo esempio seleziona le righe in cui il nome ha esattamente tre caratteri (tre istanze di “_”).
Puoi anche utilizzare le espressioni regolari estese, con REGEXP_LIKE () (anche gli operatori REGEXP e RLIKE). Le espressioni regolari vanno oltre lo scopo di questa guida, ma puoi consultare ulteriori informazioni facendo una veloce ricerca su un motore di ricerca.
Conteggio dei risultati
Il conteggio dei dati è importante e ha molti usi nel mondo reale. MySQL usa COUNT () per tali compiti. L’esempio più semplice è il conteggio delle voci di una tabella:
mysql> SELECT COUNT (*) FROM tabella_1;
È anche possibile dividere il conteggio tra i gruppi. Ad esempio, potrei GROUP BY paese e visualizzare quante voci sono in ogni paese:
mysql> SELECT paese, COUNT (*) FROM table_1 GROUP BY country;
Puoi menzionare anche gruppi più specifici inserendo più colonne per raggruppare.
Per esempio:
mysql> SELECT paese, sesso, COUNT (*) FROM tabella_1 GROUP BY country, sex;
Questi risultati potrebbero anche essere utilizzati insieme a WHERE per restringere l’output.
Fai attenzione quando fai i conteggi. Se specifichi qualcos’altro da stampare oltre a COUNT () e non menzioni quelle colonne dopo GROUP BY, potresti ottenere un errore o risultati imprevisti (vedi ONLY_FULL_GROUP_BY).
Utilizzare più tabelle
Questo è qualcosa che potresti voler fare in un contesto leggermente più complesso.
Ad esempio, immagina che ci sia un’altra tabella (tabella_2) che memorizza la data (colonna data) in cui le persone (colonna nome) che hanno partecipato alle riunioni (colonna riunione).
Puoi mostrare quanti anni hanno le persone in questi tavoli quando hanno partecipato alle riunioni:
mysql> SELECT tabella_1.nome, TIMESTAMPDIFF (ANNO, data_di_nascita, data) AS age, riunione -> FROM tabella_1 INNER JOIN tabella_2 ON tabella_1.nome = tabella_2.nome;
Questo produrrà qualcosa come:
+ ----- + --- + ------------------- + | nome | età | incontro | + ----- + --- + ------------------- + | Emily | 21 | Dog Lovers Club | | Emily | 22 | Dog Lovers Club | | Emily | 23 | Hackathon | | Smith | 36 | TED Talk | | Smith | 38 | Partita del calcio | + ----- + ----+ ------------------- +
Proverò a spiegare la sintassi.
Per nome, abbiamo dovuto menzionare tabella_1.nome e tabella_2.nome, poiché la colonna è presente in entrambe le tabelle (per birth_date, date e meeting non è stato necessario specificare la tabella, poiché sono uniche per una di esse).
La parte interessante è questa:
FROM tabella_1 INNER JOIN tabella_2 ON tabella_1.nome = tabella_2.nome;
INNER JOIN mette insieme le tabelle e prende la riga che ha qualcosa in comune, una condizione specificata dalla parola chiave ON; in questo caso, dove i nomi corrispondono.
Nota: puoi anche mettere insieme la stessa tabella con se stessa, magari per confrontare due risultati SELECT.
Utilizzo della modalità batch in MySQL
Un’altra caratteristica utile è la modalità batch.
In pratica, invece della shell interattiva puoi mettere le istruzioni in un file ed eseguirle in sequenza:
mysql -h nome_host -u nome_utente -p < nome_batch_file
Ti verrà quindi richiesto di inserire una password (se richiesta per quello specifico utente). Se vuoi continuare a far girare lo script invece di fermarti quando avviene un errore, usa -force.
È anche possibile reindirizzare l’output su un altro programma o su un file:
mysql -h nome_host -u nome_utente -p <batch_file | less mysql -h nome_host -u nome_utente -p <batch_file > nome_file_uscita
Se si è nella shell MySQL interattiva e si desidera eseguire uno script, utilizzare uno di questi due:
mysql> source batch_file; mysql> \. batch_file;
Conclusione
In questo articolo, abbiamo trattato diversi modi in cui è possibile utilizzare MySQL per gestire database tabulari, anche approfondendo alcune funzionalità più avanzate.
Sarei felice se questa guida sulle principali istruzioni SQL abbia potuto aiutare utenti in difficoltà e principianti. Anche se non sei un principiante, spero che tu abbia potuto imparare qualcosa di cui non eri al corrente.
Immagine di copertina: Chiffre01 [CC BY-SA 4.0], attraverso Wikimedia Commons