La sostenibilità nel business è difficile senza una corretta gestione dei dati. Costruire un sistema ben definito è essenziale per gestire le operazioni interne ed esterne. Una visione dettagliata dei dati aziendali aiuta a comprendere il flusso di lavoro complessivo e a misurare le prestazioni. Inoltre, l'uso di database fa risparmiare tempo e consente di accedere alle informazioni senza sforzo.
Cos'è un database?
Un database è un sistema organizzato utilizzato per memorizzare informazioni in modo sicuro. Offre una categorizzazione e consente la creazione di tabelle per ordinare tipi simili di elementi sotto un unico gruppo. Le tabelle offrono opzioni per elencare le informazioni in più righe e colonne. Di conseguenza, la qualità e la coerenza dei dati migliorano. Oltre alle capacità di archiviazione e di facile accesso, i database svolgono un ruolo significativo nella salvaguardia della privacy e nella sicurezza dei sistemi. Che sia ad hoc o parte di un processo codificato in un'applicazione, il recupero dei dati è uno degli aspetti più critici della gestione dei database.
Una query in un DMS relazionale è qualsiasi comando utilizzato per recuperare dati da una tabella. SELECT istruzioni sono quasi sempre utilizzate in Structured Query Language (SQL) query. Esistono diversi metodi per recuperare informazioni da un database. In genere, gli sviluppatori preferiscono l'opzione della riga di comando perché è veloce ed efficace. Consente di inviare query in modo fluido.
In questa guida, ti presenteremo MySQL e parleremo di come lavorare con le query in MySQL. Iniziamo!
Prerequisiti
Per seguire questo tutorial, avrai bisogno di quanto segue:
-
L'ultima versione di Ubuntu sia installata sul tuo sistema.
-
Gli utenti di sistema devono avere privilegi sudo.
-
Se riscontri difficoltà ad accedere al tuo account sudo, considera di modificare la password root di MySQL.
-
-
MySQL sia configurato sul tuo server.
Passo 1 — Creare e configurare un database
Prima di iniziare e scrivere la nostra prima query SQL, dobbiamo creare un database e aggiungervi tabelle. Successivamente, popoleremo le tabelle utilizzando dati di esempio. Lavorare con i database ti aiuterà a comprendere la configurazione di base e ti darà sicurezza nel lavorare con le query.
L'esperienza pratica è il modo più efficace per costruire concetti e comprendere l'importanza dei database. In questa guida, ti guideremo attraverso l'uso dei database e la loro importanza per risolvere problemi del mondo reale. Verifichiamo uno scenario per capire come i database possono aiutare a organizzare i record in modo strutturato.
Scenario: Creeremo un database in cui un gruppo di studenti universitari andrà in vacanza per festeggiare i propri compleanni. Prima di andare in vacanza, hanno in programma di partecipare a una gara di nuoto amichevole. Inoltre, ogni amico del gruppo pianifica una lista di cose da fare per rendere il viaggio emozionante e piacevole.
Innanzitutto, apri MySQL come utente root:
|
1 |
$ sudo mysql |
Quindi, crea un database eseguendo il seguente comando:
|
1 2 3 |
mysql> CREATE DATABASE `vacation`; Query OK, 1 riga interessata (0.52 sec) |
Successivamente, utilizziamo il nostro database usando il seguente comando:
|
1 |
mysql> USE vacation; |
Dopodiché, creeremo delle tabelle nel nostro database. Nomina la prima tabella come celebration. La nostra tabella avrà colonne per i nomi dei nostri amici (name), i tornei che hanno vinto (wins) e anche una colonna per il loro miglior tempo (time):
|
1 2 3 4 5 6 7 |
mysql> CREATE TABLE celebration ( -> name varchar(50), -> wins real, -> time real -> ); Query OK, 0 righe interessate (2.03 sec) |
Popoleremo quindi la nostra tabella con i dati:
|
1 2 3 4 5 6 7 8 9 |
mysql> INSERT INTO celebration (name, wins, time) VALUES('Austin', '4', '15.5'), ('Ivan','7','9'), ('Aisha','10','5'), ('Zane','13','7.5'); Query OK, 4 righe interessate (0.29 sec) Record: 4 Duplicati: 0 Avvisi: 0 |
Quindi, crea un'altra tabella all'interno del nostro database per memorizzare le informazioni sulle loro attività di compleanno preferite. Creeremo una tabella chiamata vacation e avrà le seguenti colonne:
|
List |
Detail |
|
name: |
Memorizza il nome di ciascun amico. |
|
birthdate: |
Traccia la data di nascita di ciascun individuo. |
|
activity: |
Mantiene un registro della loro attività preferita. |
|
Destination: |
Memorizza le informazioni sulla destinazione preferita di ciascun individuo. |
|
meal: |
Traccia il pasto preferito da un individuo. |
|
1 2 3 4 5 6 7 8 |
mysql> CREATE TABLE vacation ( name varchar(50), birthdate date, activity varchar(50), destination varchar(50), meal varchar(50) ); Query OK, 0 righe interessate (0.09 sec) |
Popola la tabella con i dati:
|
1 2 3 4 5 6 7 8 9 10 |
mysql> CREATE TABLE vacation ( name varchar(50), birthdate date, activity varchar(50), destination varchar(50), meal varchar(50) ); Query OK, 0 righe interessate (0.09 sec) Record: 4 Duplicati: 0 Avvisi: 0 |
Hai completato la configurazione del tuo database.
Passo 2 — Iniziare con le istruzioni SELECT
Le query in SQL normalmente iniziano con SELECT. Viene utilizzato nelle query per specificare quali colonne di una tabella devono essere restituite con i risultati. Una query deve anche includere sempre FROM, che viene utilizzato per specificare la tabella su cui la query eseguirà l'interrogazione.
Le query seguono la sintassi seguente:
|
1 |
mysql> SELECT colonna_da_selezionare FROM tabella_da_selezionare WHERE si_applicano_determinate_condizioni; |
Useremo la sintassi della query per restituire la colonna meal dalla tabella vacation tabella:
|
1 |
mysql> SELECT meal FROM vacation; |
Il nostro output sarà il seguente:
|
1 2 3 4 5 6 7 8 9 10 |
+-------+ | meal | +-------+ | Bistecca | | Sushi | | Patatine fritte | | Tofu | +-------+ 4 righe in set (0.00 sec) |
Puoi anche selezionare più colonne utilizzando una colonna per separarle:
|
1 |
mysql> SELECT name, destination FROM vacation; |
Output:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+-------------+ | name | destination | +--------+-------------+ | Austin | Maldive | | Ivan | Mauritius | | Aisha | Colorado | | Zane | Bora Bora | +--------+-------------+ 4 righe in set (0.00 sec) |
Puoi anche utilizzare un asterisco (*) se vuoi rappresentare tutte le colonne della tabella:
|
1 |
mysql> SELECT * FROM celebration; |
Output:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------+------+ | name | wins | time | +--------+------+------+ | Austin | 4 | 15.5 | | Ivan | 7 | 9 | | Aisha | 10 | 5 | | Zane | 13 | 7.5 | +--------+------+------+ 4 righe in set (0.00 sec) |
Se vuoi filtrare i record che soddisfano una determinata condizione, usi WHERE. Le righe che non soddisfano la condizione specificata vengono eliminate dai risultati. La clausola WHERE utilizza la seguente sintassi:
|
1 |
mysql> . . . WHERE nome_colonna operatore_di_confronto valore |
È un operatore di confronto che definisce come la colonna specificata deve essere confrontata con il valore. I comuni operatori di confronto SQL includono:
|
Operatore |
Uso |
|
= |
Uguaglianza |
|
!= |
Disuguaglianza |
|
< |
Minore di |
|
> |
Maggiore di |
|
<= |
Minore o uguale a |
|
>= |
Maggiore o uguale a |
|
BETWEEN |
Verifica se il valore rientra nell'intervallo specificato. |
|
IN |
Verifica se il valore di una riga è contenuto in un insieme di valori specificati. |
|
EXISTS |
Verifica se esiste una riga |
|
LIKE |
Verifica se un valore corrisponde alla stringa specificata |
|
IS NULL |
Verifica la presenza di valori nulli |
|
IS NOT NULL |
Verifica la presenza di tutti i valori diversi da NULL |
Se volessi trovare la destinazione preferita di Aisha, potresti usare la query qui sotto:
|
1 |
mysql> SELECT destination FROM vacation WHERE name ='Aisha'; |
La query restituirà quindi:
|
1 2 3 4 5 6 7 |
+-------------+ | destination | +-------------+ | Colorado | +-------------+ 1 riga in set (0.03 sec) |
SQL supporta l'uso di caratteri jolly, che sono particolarmente utili nelle WHERE clausole. I simboli di percentuale ( %) indicano zero o più caratteri sconosciuti, mentre i trattini bassi ( _) indicano un singolo carattere sconosciuto. Questi sono utili se si desidera trovare una voce specifica in una tabella ma non si è sicuri della voce stessa.
Ad esempio, se avessi dimenticato la destinazione preferita di un amico e conoscessi solo la lettera con cui inizia, ad esempio “m”, puoi trovare il nome della destinazione utilizzando la seguente query:
|
1 |
mysql> SELECT destination FROM vacation WHERE destination LIKE 'm%'; |
La query restituirà:
|
1 2 3 4 5 6 7 8 |
+-------------+ | destination | +-------------+ | Maldives | | Mauritius | +-------------+ 2 righe in set (0.04 sec) |
Quando si lavora con i database, si possono incontrare colonne o tabelle con nomi relativamente lunghi o difficili da leggere. In questi casi, è possibile rendere i nomi più leggibili utilizzando la parola chiave AS per creare un alias. Gli alias creati con AS sono validi solo per la durata della query per la quale sono stati creati:
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT name AS E, destination as D, activity as A FROM vacation; +--------+-----------+-------------------+ | E | D | A | +--------+-----------+-------------------+ | Austin | Maldives | skiing | | Ivan | Mauritius | paragliding | | Aisha | Colorado | mountain climbing | | Zane | Bora Bora | fishing | +--------+-----------+-------------------+ 4 righe in set (0.00 sec) |
Passo 3 — Introduzione alle funzioni di aggregazione
Quando si lavora con i dati, non sempre si desidera vedere i dati stessi. Spesso si preferisce avere informazioni sui dati. Eseguendo una query SELECT, è possibile interpretare o eseguire calcoli sui dati utilizzando la sintassi SQL. Queste sono denominate funzioni di aggregazione.
La funzione COUNT conta e restituisce il numero di righe che soddisfano un insieme specifico di criteri. Ad esempio, se si desidera sapere quanti amici preferiscono andare alle Maldives, è possibile utilizzare la seguente query:
|
1 |
mysql> SELECT COUNT(destination) FROM vacation WHERE destination = 'Maldives'; |
Verranno restituiti i seguenti risultati:
|
1 2 3 4 5 6 7 |
+--------------------+ | COUNT(destination) | +--------------------+ | 1 | +--------------------+ 1 riga in set (0.06 sec) |
MIN viene utilizzato per trovare il valore più piccolo all'interno di una colonna specificata:
|
1 |
mysql> SELECT MIN(wins) FROM celebration; |
La query restituirà:
|
1 2 3 4 5 6 7 |
+-----------+ | MIN(wins) | +-----------+ | 4 | +-----------+ 1 riga in set (0.02 sec) |
MAX viene utilizzato per trovare il valore numerico più grande in una determinata colonna:
|
1 |
mysql> SELECT MAX(wins) FROM celebration; |
L'output previsto è:
|
1 2 3 4 5 6 7 |
+-----------+ | MAX(wins) | +-----------+ | 13 | +-----------+ 1 riga in set (0.03 sec) |
Sia la funzione MIN che la funzione MAX possono essere utilizzate su dati numerici e alfabetici. Quando applicata a una colonna di valori stringa, la funzione MIN restituisce il primo valore in ordine alfabetico.
La funzione MIN restituisce il primo valore in ordine alfabetico:
|
1 |
mysql> SELECT MIN(name) FROM celebration; |
Ecco quale sarà l'output:
|
1 2 3 4 5 6 7 |
+-----------+ | MIN(name) | +-----------+ | Aisha | +-----------+ 1 riga in set (0.00 sec) |
La funzione MAX restituisce l'ultimo valore in ordine alfabetico:
|
1 |
mysql> SELECT MAX(name) FROM celebration; |
Questo sarà l'output:
|
1 2 3 4 5 6 7 |
+-----------+ | MAX(name) | +-----------+ | Zane | +-----------+ 1 riga in set (0.00 sec) |
Passo 4 — Manipolare i risultati delle query
Un'altra clausola molto utilizzata è la GROUP BY. Viene utilizzata quando si esegue una funzione di aggregazione su una colonna ma in relazione ai valori corrispondenti in un'altra:
|
1 |
mysql> SELECT COUNT(name), activity FROM vacation GROUP BY activity; |
L'output sarà:
|
1 2 3 4 5 6 7 8 9 10 |
+-------------+-------------------+ | COUNT(name) | activity | +-------------+-------------------+ | 1 | sci | | 1 | parapendio | | 1 | scalata in montagna | | 1 | pesca | +-------------+-------------------+ 4 righe in set (0.04 sec) |
Per ordinare i risultati della query, utilizza la clausola ORDER BY. I valori numerici sono ordinati in modo crescente per impostazione predefinita, mentre i valori di testo sono ordinati in ordine alfabetico. La query seguente elenca le colonne name e birthdate, ma ordina i risultati per birthdate:
|
1 |
mysql> SELECT name, birthdate FROM vacation ORDER BY birthdate; |
L'output sarà il seguente:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------------+ | name | birthdate | +--------+------------+ | Zane | 1996-01-01 | | Aisha | 1999-07-24 | | Austin | 2002-01-07 | | Ivan | 2010-12-18 | +--------+------------+ 4 righe in set (0.04 sec) |
L'output è in ordine crescente; per ordinare in ordine decrescente, chiudi la query con la parola DESC:
|
1 |
mysql> SELECT name, birthdate FROM vacation ORDER BY birthdate DESC; |
Dai un'occhiata all'output:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------------+ | name | birthdate | +--------+------------+ | Ivan | 2010-12-18 | | Austin | 2002-01-07 | | Aisha | 1999-07-24 | | Zane | 1996-01-01 | +--------+------------+ 4 righe in set (0.00 sec) |
La clausola HAVING è stata aggiunta a SQL per fornire una funzionalità simile alla clausola WHERE, pur essendo compatibile con le funzioni di aggregazione. La differenza tra queste due clausole è che WHERE serve per fare riferimento a record singoli e HAVING si riferisce a record di gruppo. A tal fine, la clausola GROUP BY deve essere presente ogni volta che viene utilizzata una clausola HAVING:
|
1 |
mysql> SELECT COUNT(name), activity FROM vacation GROUP BY activity HAVING COUNT(name) >= 1; |
La query restituirà:
|
1 2 3 4 5 6 7 8 |
+-------------+-------------------+ | COUNT(name) | activity | +-------------+-------------------+ | 1 | sci | | 1 | parapendio | | 1 | scalata in montagna | | 1 | pesca | +-------------+-------------------+ |
Il COUNT è 1 per tutti perché non ci sono due amici a cui piace la stessa attività.
Passo 5 — Eseguire query su più tabelle
La clausola JOIN può essere utilizzata nel risultato di una query per combinare righe da due o più tabelle. Ottiene questo risultato individuando una colonna correlata tra le tabelle e ordinando l'output in modo appropriato.
SELECT istruzioni che includono una clausola JOIN seguono la sintassi seguente:
|
1 2 3 |
mysql> SELECT table1.column1, table2.column2 mysql> FROM table1 mysql> JOIN table2 ON table1.related_column=table2.related_column; |
Se volessi acquistare per ciascuno dei tuoi amici un trofeo per le loro vittorie durante il nuoto nei loro compleanni, potresti creare una query che unisca entrambe le tabelle per aiutarti a trovare tutte le informazioni desiderate con una singola query:
|
1 2 3 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate FROM celebration JOIN vacation ON celebration.name=vacation.name; |
L'output sarà:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------+------------+ | name | wins | birthdate | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | +--------+------+------------+ 4 righe in set (0.00 sec) |
Questa è una clausola di inner JOIN. Questo perché seleziona tutti i record che hanno valori corrispondenti in entrambe le tabelle e li stampa in un set di risultati. I record che non corrispondono alla query non vengono inclusi. Possiamo includere una nuova riga nelle nostre tabelle che non corrisponde a nessuna voce:
|
1 2 3 4 |
mysql> INSERT INTO celebration(name,wins,time) VALUES('Ella', '1', '120'); Query OK, 1 riga interessata (0.01 sec) |
|
1 2 3 4 |
mysql> INSERT INTO celebration(name,wins,time) VALUES('Ella', '1', '120'); Query OK, 1 riga interessata (0.01 sec) |
Quindi, esegui nuovamente l'istruzione SELECT con la clausola JOIN:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate -> FROM celebration -> JOIN vacation ON celebration.name=vacation.name; +--------+------+------------+ | name | wins | birthdate | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | +--------+------+------------+ 4 righe in set (0.00 sec) |
Poiché la tabella celebration non ha alcuna voce per Peter e la tabella vacation non ha alcuna voce per Ella, tali record sono assenti.
Possiamo restituire tutti i record da una delle tabelle utilizzando una clausola di outer JOIN. Questa può essere una LEFT JOIN o una RIGHT JOIN. Una LEFT JOIN restituisce tutti i record della tabella di sinistra e solo i record corrispondenti della tabella di destra. La tabella di sinistra nel contesto dei join esterni è quella a cui fa riferimento la clausola FROM, e la tabella di destra è qualsiasi tabella a cui si fa riferimento dopo l'istruzione JOIN.
Esegui nuovamente la query ma utilizza una clausola LEFT JOIN:
|
1 2 3 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate FROM celebration LEFT JOIN vacation ON celebration.name=vacation.name; |
Il comando restituirà tutti i record dalla tabella di sinistra ( celebration) anche se non ha un record corrispondente nella tabella di destra. Quando non c'è un record corrispondente nella tabella di destra, viene restituito come NULL:
|
1 2 3 4 5 6 7 8 9 10 11 |
+--------+------+------------+ | name | wins | birthdate | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | | Ella | 1 | NULL | +--------+------+------------+ 5 righe in set (0.00 sec) |
Questa è ora la clausola RIGHT JOIN:
|
1 2 3 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate FROM celebration RIGHT JOIN vacation ON celebration.name=vacation.name; |
Verranno restituiti tutti i valori della tabella di destra (vacation). Poiché il birthdate di Peter è registrato nella tabella di destra e non nella tabella di sinistra, le colonne name e wins restituiranno valori NULL in quelle righe:
|
1 2 3 4 5 6 7 8 9 10 11 |
+--------+------+------------+ | nome | vittorie | data_nascita | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | | NULL | NULL | 1991-05-03 | +--------+------+------------+ 5 righe in set (0.01 sec) |
Puoi usare la clausola UNION invece di JOIN per interrogare i record da più tabelle. L'operatore UNION si differenzia dalla clausola JOIN per il fatto che combina i risultati di due istruzioni SELECT in una singola colonna, anziché stampare i risultati di più tabelle come colonne univoche utilizzando una singola istruzione SELECT.
Puoi eseguire questa query per illustrare:
|
1 |
mysql> SELECT nome FROM celebration UNION SELECT nome FROM vacation; |
La query rimuove le voci duplicate. Questo è il comportamento predefinito dell'operatore UNION:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
+--------+ | nome | +--------+ | Austin | | Ivan | | Aisha | | Zane | | Ella | | Peter | +--------+ 6 righe in set (0.00 sec) |
Per restituire tutte le voci (più i duplicati) usa l'operatore UNION ALL:
|
1 |
mysql> SELECT nome FROM celebration UNION ALL SELECT nome FROM vacation; |
Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
+--------+ | nome | +--------+ | Austin | | Ivan | | Aisha | | Zane | | Ella | | Austin | | Ivan | | Aisha | | Zane | | Peter | +--------+ 10 righe in set (0.00 sec) |
Le sottoquery sono un altro metodo per interrogare più tabelle. Le sottoquery sono query racchiuse all'interno di un'altra query (noto anche come query interne o nidificate). Queste sono utili quando si desidera confrontare i risultati di una query con i risultati di una funzione di aggregazione separata.
Useremo l'esempio di cercare di scoprire quale amico ha vinto più tornei di nuoto rispetto a Ella. Invece di chiedere quante partite ha vinto Ella e poi eseguire un'altra query per vedere chi ha vinto più partite di quelle, puoi calcolare entrambe con una singola query:
|
1 2 3 4 |
mysql> SELECT nome, vittorie FROM celebration -> WHERE vittorie > ( -> SELECT vittorie FROM celebration WHERE nome = 'Ella' -> ); |
La query restituirà:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------+ | nome | vittorie | +--------+------+ | Austin | 4 | | Ivan | 7 | | Aisha | 10 | | Zane | 13 | +--------+------+ 4 righe in set (0.06 sec) |
Se volessi portare i tuoi amici in una vacanza a sorpresa, puoi usare una query per vedere chi ha più vittorie e restituire la loro destinazione:
|
1 2 3 4 |
mysql> SELECT nome, destinazione, attività, pasto -> FROM vacation -> WHERE nome = ( SELECT nome FROM celebration -> WHERE vittorie = (SELECT MAX(vittorie) FROM celebration)); |
La query restituirà:
|
1 2 3 4 5 6 7 |
+------+-------------+----------+------+ | nome | destinazione | attività | pasto | +------+-------------+----------+------+ | Zane | Bora Bora | pesca | Tofu | +------+-------------+----------+------+ 1 riga in set (0.00 sec) |
Questa istruzione contiene una sottoquery all'interno di una sottoquery.
Conclusione
La generazione di query è una delle attività più comuni nella gestione dei database. Esistono diversi strumenti di amministrazione del database come phpMyAdmin e pgAdmin che puoi utilizzare per lavorare con le query e visualizzarne i risultati. Tuttavia, l'istruzione SELECT da riga di comando è la scelta preferita per la sua facilità d'uso e l'eccellente controllo.
Inoltre, ci sono molti tutorial sui database che puoi esplorare dal nostro blog:
- SQLite vs MySQL vs. PostgreSQL: Sistemi di gestione di database relazionali a confronto
- Soluzioni rapide per riparare tabelle corrotte in MySQL: un tutorial
- Come installare MySQL su Centos 7
- Utente MySQL — Creare e concedere permessi
- Configurazione della replicazione in MySQL su Ubuntu
Buon computing!
Commenti
Ancora nessun commento. Scrivi il primo.