Advertisement
  1. Code
  2. Databases

SQL per Principianti :Parte 3 - Relazioni del Database

by
Length:LongLanguages:

Italian (Italiano) translation by Piergiorgio Sansone (you can also view the original English article)

Oggi, continuiamo il nostro viaggio nel mondo di SQL e nel sistema di un database relazionale. In questa terza parte della serie, studieremo come si lavora con tabelle multiple che hanno delle relazioni tra di loro. Innanzitutto, vedremo molti concetti chiave e cominciamo lavorando con le query di JOIN in SQL.

Potete vedere anche i database SQL in azione controllando gli script SQL, applicazioni e componenti aggiuntivi sul mercato di Envato.

Guardate anche:

Introduzione

Quando un database viene creato, il buon senso ci dice di usare tabelle separate per tipi differenti di entità. Molti esempi sono : clienti, ordini, elementi, messaggi etc....Ma abbiamo bisogno anche di creare una relazione tra queste tabelle. Per esempio, i clienti effettuano gli ordini, e gli ordini contengono elementi. Queste relazioni devono essere rappresentati nel database. Inoltre, durante il recupero dei dati con SQL, abbiamo bisogno di utilizzare determinati tipi di query JOIN per ottenere quello che ci serve.

Esistono diversi tipi di relazioni di database. Oggi ci accingiamo a coprire le seguenti:

  • Le relazioni uno a uno (1:1)
  • Le relazioni  Uno a Molti e Molti a Uno (1:m - m:1)
  • Le relazioni Molti a Molti (m:m)
  • Le relazioni Auto-Referenti

Quando si selezionano dati da più tabelle con relazioni, useremo la query di JOIN. Ci sono diversi tipi di JOIN, vediamo di conoscerle :

  • Cross join
  • Join 
  • Inner join
  • Left (Outer) Join
  • Right (Outer) Join

Impareremo anche la clausola ON e la clausola USING.

La Relazione Uno a Uno

Diciamo che avete una tabella per i clienti:

Possiamo mettere le informazioni sull'indirizzo del cliente su una tabella separata:

Ora abbiamo una relazione tra la tabella clienti e la tabella indirizzi. Se ogni indirizzo può appartenere a un solo cliente, questa relazione è "Uno a Uno". (1:1) Tenete a mente che questo tipo di relazione non è molto comune. La nostra tabella iniziale che comprendeva l'indirizzo con il cliente potrebbe aver lavorato bene nella maggior parte dei casi.

Si noti che ora c'è un campo denominato "address_id" nella tabella clienti, a cui si riferisce il record corrispondente nella tabella indirizzi. Questa è chiamata "Foreign Key" (chiave esterna) e viene utilizzata per tutti i tipi di relazioni di database. Tratteremo questo argomento più avanti nell'articolo.

Possiamo visualizzare la relazione tra il cliente e il record dell' indirizzo in questo modo:

Notate che l'esistenza di una relazione può essere opzionale, come avere un record di un cliente che non ha alcun record di indirizzo correlato.

Relazione Uno a Molti e Molti ad Uno

Questo è il tipo di relazione più comunemente usato . Prendete in considerazione un sito di e-commerce, con il seguente:

  • I clienti possono effettuare molti ordini.
  • Gli ordini possono contenere molti elementi.
  • Gli elementi possono avere descrizioni in molte lingue.

In questi casi dobbiamo creare relazioni "Uno a molti". Ecco un esempio:

Ogni cliente può avere zero, uno o più ordini. Ma un ordine può appartenere a un solo cliente.

Relazioni Molti a Molti

In alcuni casi, potrebbe essere necessario avere più istanze su entrambi i lati della relazione. Ad esempio, ogni ordine può contenere più elementi. E ogni elemento può essere anche in più ordini.

Per queste relazioni, dobbiamo creare una tabella  supplementare:

La tabella Elementi_Ordini ha un solo scopo, ed è quello di creare una relazione "Molti a Molti" tra gli elementi e gli ordini.

Qui possiamo vedere una rappresentazione di questo tipo di relazione.

Se volete includere le righe elemento_ordine nel grafico, potrebbe essere cosi:

Le relazioni Auto-Referenti 

Vengono utilizzate quando una tabella ha bisogno di una relazione con se stessa. Ad esempio, diciamo che avete un programma di riferimento. I Clienti possono riferire altri clienti al tuo sito di e-commerce. La tabella potrebbe essere cosi:

I Clienti 102 e 103 fanno riferimento al cliente 101.

Questa può essere, anche simile ad una relazione "uno a molti" da un cliente possono riferirsi molti clienti. Inoltre può essere visualizzata come una struttura ad albero.

Un cliente potrebbe fare riferimento a zero, uno o più clienti. Ogni cliente può essere rinviato da un solo cliente, o nessuno affatto.

Se si desidera creare una Auto-Referenza  "molti a molti" della relazione, bisognerebbe avere una tabella supplementare  proprio come abbiamo visto nell'ultima sezione.

Chiavi esterne

Finora abbiamo solo imparato alcuni dei concetti. Ora è il momento di metterli in pratica utilizzando SQL. Per fare questo, abbiamo bisogno di capire quali sono le chiavi esterne.

Negli esempi di relazioni precedenti, abbiamo sempre avuto questi campi " * * * _ID" a cui fa riferimento una colonna in un'altra tabella. In questo esempio, la colonna di customer_id nella tabella ordini è una colonna di chiave esterna:

Con un database come MySQL, ci sono due modi per creare colonne di chiavi esterne:

Definire in modo esplicito la chiave esterna

Creiamo una semplice tabella clienti :

Ora la tabella ordini, che conterrà una chiave esterna:

Entrambe le colonne (customers.customer_id e orders.customer_id) dovrebbero avere la stessa struttura di dati uguale e identica. Se uno è di tipo INT, l'altra non dovrebbe essere BIGINT per esempio.

Siete pregati di notare che in MySQL solo il motore InnoDB ha pieno supporto per chiavi esterne. Ma altri motori di archiviazione consentono comunque di specificarle senza dare alcun errore.  La colonna di chiave esterna viene anche indicizzata automaticamente, a meno che non le si specifichi un altro indice.

Senza dichiarazione esplicita

Stessa tabella ordini può essere creata senza dichiarare in modo esplicito che la colonna customer_id sia una chiave esterna:

Quando si recuperano dati con una query JOIN, è possibile trattare questa colonna come una chiave esterna anche se il motore del database non è a conoscenza di tale rapporto.

Ci accingiamo a conoscere le query JOIN ulteriormente nell'articolo.

Visualizzare le relazioni

Il mio software preferito attuale per la progettazione di database e visualizzare le relazioni di chiave esterna è MySQL Workbench.

Una volta che si progetta il vostro database, si può esportare il codice SQL ed eseguirlo sul vostro server. Questo è molto utile per la progettazione dei database più grandi e più complessi.

Query di JOIN

Per recuperare dati da un database che ha delle relazioni, abbiamo spesso bisogno di utilizzare le query JOIN.

Prima di iniziare, cerchiamo di creare le tabelle e alcuni dati di esempio con cui lavorare.

Abbiamo 4 clienti. Un cliente ha due ordini, due clienti hanno un ordine per uno e un cliente non ha nessun ordine. Ora vediamo i diversi tipi di query JOIN che possiamo eseguire su queste tabelle.

Cross Join

Questo è il tipo di query JOIN predefinito quando non viene specificata nessuna condizione.

Il risultato è un cosiddetto "prodotto cartesiano" delle tabelle. Vuol dire che ogni riga della prima tabella è abbinato con ogni riga della seconda tabella. Poiché ogni tavolo aveva 4 righe, abbiamo finito per ottenere un risultato di 16 righe.

La parola chiave JOIN può essere eventualmente sostituita con una virgola invece.

Naturalmente questo tipo di risultato non è di solito utile. Quindi diamo un'occhiata agli altri tipi di join.

Natural Join

Con questo tipo di query JOIN, le tabelle devono avere un nome di colonna corrispondente. Nel nostro caso, entrambe le tabelle hanno la colonna customer_id. Così, MySQL unirà i record solo quando il valore di questa colonna avrà corrispondenza su due records.

Come si può vedere la colonna customer_id viene visualizzata solo una volta questa volta, perché il motore di database, viene considerato come la colonna comune. Possiamo vedere i due ordini di Adam, e gli altri due ordini di Joe e Sandy. Infine stiamo ottenendo alcune informazioni utili.

Inner Join

Quando viene specificata una condizione di join, viene eseguita una Inner Join. In questo caso, sarebbe una buona idea avere il campo customer_id corrispondere su entrambe le tabelle. I risultati dovrebbero essere simili alla Natural Join.

I risultati sono gli stessi, tranne una piccola differenza. La colonna customer_id è ripetuta due volte, una volta per ogni tabella. Il motivo è, abbiamo semplicemente chiesto al database di confrontare in base ai valori su queste due colonne. Ma in realtà non sa che rappresentano le stesse informazioni.

Aggiungiamo alcune ulteriori condizioni per la query.

Questa volta abbiamo ricevuto solo gli ordini oltre $15.

Clausola ON

Prima di passare ad altri tipi di join, abbiamo bisogno di guardare la clausola ON. Questo è utile per mettere le condizioni di JOIN in una clausola separata.

Ora possiamo distinguere la condizione di JOIN dalle condizioni della clausola WHERE. Ma c'è anche una leggera differenza nella funzionalità. Lo vedremo negli esempi di LEFT JOIN.

Clausola USING

UTILIZZANDO la clausola è simile alla clausola ON, ma è più corta. Se una colonna ha lo stesso nome in entrambe le tabelle, possiamo specificarlo qui.

In realtà, questo è molto simile alla Natural JOIN, così la colonna di join (customer_id) non viene ripetuta due volte nei risultati.

Left (Outer) Join

Un LEFT JOIN è un tipo di Outer Join. In queste query, se non c'è nessuna corrispondenza trovata dalla seconda tabella, il record della prima tabella è ancora visualizzato.

Anche se Andy non ha nessun ordine, il suo record viene ancora visualizzato. I valori sotto le colonne della seconda tabella vengono impostati su NULL.

Questo è anche utile per individuare i record che non hanno rapporti. Ad esempio, possiamo cercare per i clienti che non hanno effettuato alcun ordine.

Tutto quello che abbiamo fatto è stato guardare i valori NULL per  order_id.

Si noti inoltre che la parola chiave OUTER è facoltativa. È possibile utilizzare solo LEFT JOIN anziché LEFT OUTER JOIN.

Istruzioni condizionali

Ora diamo un'occhiata a una query con una condizione.

Così che cosa è successo a Andy e Sandy? LEFT JOIN avrebbe dovuto restituire i clienti con nessun ordine corrispondente. Il problema è che la clausola WHERE sta bloccando quei risultati. Per farli che possiamo provare a includere anche la condizione di NULL.

Abbiamo Andy ma nessun record di Sandy.Questo non sembra ancora giusto. Per ottenere ciò che vogliamo, abbiamo bisogno di utilizzare la clausola ON.

Ora abbiamo tutti gli ordini superiori a $15. Come detto in precedenza, la clausola ON a volte ha funzionalità leggermente diverse rispetto alla clausola WHERE. In una Outer Join come questa, le righe sono incluse quando non si verifica la condizione della clausola ON.

Right (Outer) Join

Un RIGHT OUTER JOIN funziona esattamente allo stesso modo, ma l'ordine delle tabelle è invertito.

Questa volta non abbiamo nessun risultato NULL perché ogni ordine ha un record cliente corrispondente. Possiamo modificare l'ordine delle tabelle e ottenere gli stessi risultati come abbiamo fatto noi da LEFT OUTER JOIN.

Ora abbiamo quei valori NULL perché la tabella clienti è sul lato destro del join.

Conclusione

Grazie per la lettura dell'articolo. Spero che ti sia piaciuto! Si prega di lasciare i vostri commenti e domande e hanno un grande giorno!

Non dimenticate di controllare gli script SQL, applicazioni e componenti aggiuntivi sul mercato di Envato. Si otterrà un senso di ciò che è possibile fare con i database SQL, e potreste trovare la soluzione perfetta per aiutarvi con il vostro progetto di sviluppo.

Seguiteci su Twitter, o sottoscrivete il Nettuts + RSS Feed per il web i migliori tutorial per lo sviluppo sul web.

Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.