Share
Oggi, continuiamo il nostro viaggio nel mondo dei sistemi di database SQL e relazionali. In questa terza parte della serie, impareremo come lavorare con più tabelle che hanno relazioni reciproche. In primo luogo, esamineremo alcuni concetti chiave e quindi inizieremo a lavorare con le query JOIN in SQL.
Puoi anche vedere i database SQL in azione controllando gli script SQL, le app e i componenti aggiuntivi su Envato Market.
Quando si crea un database, il buon senso impone di utilizzare tabelle separate per diversi tipi di entità. Alcuni esempi sono: clienti, ordini, articoli, messaggi, ecc ... Ma dobbiamo anche avere relazioni tra queste tabelle. Ad esempio, i clienti effettuano ordini e gli ordini contengono articoli. Queste relazioni devono essere rappresentate nel database. Inoltre, quando si recuperano dati con SQL, è necessario utilizzare determinati tipi di query JOIN per ottenere ciò di cui abbiamo bisogno.
Esistono diversi tipi di relazioni tra database. Oggi parleremo di quanto segue:
Quando selezioniamo i dati da più tabelle con relazioni, utilizzeremo la query JOIN. Esistono diversi tipi di JOIN e apprenderemo quanto segue:
Impareremo anche a conoscere la clausola ON e la clausola USING.
Diciamo che hai un tavolo 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". Tieni presente che questo tipo di relazione non è molto comune. La nostra tabella iniziale che includeva l'indirizzo insieme al cliente avrebbe potuto funzionare bene nella maggior parte dei casi.
Si noti che ora esiste un campo denominato "address_id" nella tabella Customers, che fa riferimento al record corrispondente nella tabella Address. Questo è chiamato "Foreign Key" e viene utilizzato per tutti i tipi di relazioni tra database. Tratteremo questo argomento più avanti nell'articolo.
Possiamo visualizzare la relazione tra il cliente e record di indirizzo come questo:
Si noti che l'esistenza di una relazione può essere facoltativa, come avere un record del cliente che non ha un record di indirizzo correlato.
Questo è il tipo di relazione più comunemente usato. Considerare un sito di e-commerce, con il seguente:
In questi casi avremmo bisogno di creare relazioni "da uno a molti". Ecco un esempio:
Ogni cliente può avere zero, uno o più ordini. Ma un ordine può appartenere a un solo cliente.
In alcuni casi, potresti aver bisogno di più istanze su entrambi i lati della relazione. Ad esempio, ogni ordine può contenere più articoli. E ogni oggetto può anche essere in più ordini.
Per queste relazioni, dobbiamo creare una tabella aggiuntiva:
La tabella Items_Orders ha un solo scopo, ovvero creare una relazione "Da molti a molti" tra gli articoli e gli ordini.
Ecco come possiamo visualizzare questo tipo di relazione:
Se desideri includere i record items_orders nel grafico, potrebbe essere simile al seguente:
Questo è usato quando una tabella ha bisogno di avere una relazione con se stessa. Ad esempio, supponiamo tu abbia un programma di riferimento. I clienti possono inviare altri clienti al tuo sito di acquisti. La tabella potrebbe essere simile a questa:
I clienti 102 e 103 sono stati indirizzati dal cliente 101.
Questo in realtà può anche essere simile alla relazione "uno a molti" poiché un cliente può inviare più clienti. Inoltre può essere visualizzato come una struttura ad albero:
Un cliente potrebbe riferirsi a zero, uno o più clienti. Ogni cliente può essere indirizzato da un solo cliente o nessuno.
Se desideri creare una relazione "molti a molti" che faccia riferimento a te stesso, avrai bisogno di una tabella aggiuntiva come quella di cui abbiamo parlato nell'ultima sezione.
Finora abbiamo solo imparato a conoscere alcuni concetti. Ora è il momento di darle vita usando SQL. Per questa parte, dobbiamo capire cosa sono le chiavi esterne.
Negli esempi di relazione sopra, abbiamo sempre avuto questi campi "**** _ id" che facevano riferimento a una colonna in un'altra tabella. In questo esempio, la colonna customer_id nella tabella Ordini è una colonna Chiave esterna:
Con un database come MySQL, ci sono due modi per creare colonne con chiavi esterne:
Creiamo una semplice tabella clienti:
CREA TABELLA clienti (customer_id INT AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR (100));
Ora la tabella degli ordini, che conterrà una chiave esterna:
CREATE gli ordini TABLE (order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, importo DOUBLE, FOREIGN KEY (customer_id) REFERENCES clienti (customer_id));
Entrambe le colonne (customers.customer_id e orders.customer_id) devono avere la stessa struttura di dati esatta. Se uno è INT, l'altro non dovrebbe essere BIGINT per esempio.
Si noti che in MySQL solo il motore InnoDB ha il pieno supporto per le chiavi esterne. Ma altri motori di archiviazione ti permetteranno comunque di specificarli senza dare alcun errore. Anche la colonna Chiave esterna viene indicizzata automaticamente, a meno che tu non specifichi un altro indice.
È possibile creare la stessa tabella degli ordini senza dichiarare esplicitamente la colonna customer_id come chiave esterna:
CREATE gli ordini TABLE (order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, importo DOUBLE, INDEX (customer_id));
Quando si recuperano dati con una query JOIN, è comunque possibile considerare questa colonna come una chiave esterna anche se il motore di database non è a conoscenza di tale relazione.
SELEZIONA * Dagli ordini UNISCI I clienti USING (customer_id)
Stiamo andando a conoscere ulteriori informazioni su JOIN nell'articolo.
Il mio attuale software preferito per la progettazione di database e la visualizzazione delle relazioni con le chiavi esterne è MySQL Workbench.
Una volta che hai progettato il tuo database, puoi esportare l'SQL ed eseguirlo sul tuo server. Questo è molto utile per progetti di database più grandi e complessi.
Per recuperare i dati da un database che ha relazioni, spesso è necessario utilizzare le query JOIN.
Prima di iniziare, creiamo le tabelle e alcuni dati di esempio con cui lavorare.
Accentsconagua
CREA TABELLA clienti (customer_id INT AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR (100)); CREATE gli ordini TABLE (order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, importo DOUBLE, FOREIGN KEY (customer_id) REFERENCES clienti (customer_id)); INSERIRE 'clienti' ('customer_id', 'customer_name') VALUES (1, 'Adam'), (2, 'Andy'), (3, 'Joe'), (4, 'Sandy'); INSERISCI 'ordini' ('order_id', 'customer_id', 'amount') VALORI (1, 1, 19.99), (2, 1, 35.15), (3, 3, 17.56), (4, 4, 12.34) ; Abbiamo 4 clienti. Un cliente ha due ordini, due clienti hanno un ordine ciascuno e un cliente non ha un ordine. Ora vediamo i diversi tipi di query JOIN che possiamo eseguire su queste tabelle.
Questo è il tipo predefinito di query JOIN quando non viene specificata alcuna condizione.
Il risultato è un cosiddetto "prodotto cartesiano" delle tabelle. Significa che ogni riga della prima tabella è abbinata a ciascuna riga della seconda tabella. Poiché ogni tabella aveva 4 righe, abbiamo finito per ottenere un risultato di 16 righe.
La parola chiave JOIN può essere sostituita facoltativamente con una virgola.
Ovviamente questo tipo di risultato di solito non è utile. Diamo un'occhiata agli altri tipi di 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. Quindi, MySQL si unirà ai record solo quando il valore di questa colonna corrisponde a due record.
Come puoi vedere la colonna customer_id viene visualizzata solo una volta, poiché il motore del database considera questa come la colonna comune. Possiamo vedere i due ordini piazzati da Adam e gli altri due ordini di Joe e Sandy. Finalmente riceviamo alcune informazioni utili.
Quando viene specificata una condizione di join, viene eseguita un'unione interna. In questo caso, sarebbe una buona idea avere il campo customer_id corrispondente su entrambe le tabelle. I risultati dovrebbero essere simili al Natural Join.
I risultati sono gli stessi tranne una piccola differenza. La colonna customer_id viene ripetuta due volte, una volta per ogni tabella. Il motivo è che abbiamo semplicemente chiesto al database di abbinare i valori su queste due colonne. Ma in realtà non è consapevole che rappresentino le stesse informazioni.
Aggiungiamo alcune condizioni alla query.
Questa volta abbiamo ricevuto solo ordini superiori a $ 15.
Prima di passare ad altri tipi di join, dobbiamo guardare la clausola ON. Questo è utile per mettere le condizioni JOIN in una clausola separata.
Ora possiamo distinguere la condizione JOIN dalle condizioni della clausola WHERE. Ma c'è anche una leggera differenza di funzionalità. Lo vedremo negli esempi LEFT JOIN.
La clausola USING è simile alla clausola ON, ma è più breve. Se una colonna ha lo stesso nome su entrambe le tabelle, possiamo specificarla qui.
In effetti, questo è molto simile al NATURAL JOIN, quindi la colonna join (customer_id) non viene ripetuta due volte nei risultati.
A LEFT JOIN è un tipo di outer join. In queste query, se non viene trovata alcuna corrispondenza dalla seconda tabella, viene comunque visualizzato il record della prima tabella.
Anche se Andy non ha ordini, il suo record viene ancora mostrato. I valori sotto le colonne della seconda tabella sono impostati su NULL.
Questo è anche utile per trovare record che non hanno relazioni. Ad esempio, possiamo cercare clienti che non hanno effettuato ordini.
Tutto ciò che abbiamo fatto è stato cercare i valori NULL per order_id.
Si noti inoltre che la parola chiave OUTER è facoltativa. Puoi usare LEFT JOIN invece di LEFT OUTER JOIN.
Ora esaminiamo una query con una condizione.
Quindi cosa è successo a Andy e Sandy? LEFT JOIN avrebbe dovuto restituire i clienti senza ordini corrispondenti. Il problema è che la clausola WHERE blocca tali risultati. Per ottenerli possiamo provare ad includere anche la condizione NULL.
Abbiamo Andy ma non Sandy. Ancora questo non sembra giusto. Per ottenere quello che vogliamo, dobbiamo usare la clausola ON.
Ora abbiamo ottenuto tutti e tutti gli ordini superiori a $ 15. Come ho detto prima, la clausola ON talvolta ha funzionalità leggermente diverse rispetto alla clausola WHERE. In un outer join come questo, vengono incluse le righe anche se non corrispondono alle condizioni della clausola ON.
UN GIUNTO ESTERNO GIUSTO funziona esattamente allo stesso modo, ma l'ordine dei tavoli è invertito.
Questa volta non abbiamo risultati NULL perché ogni ordine ha un record cliente corrispondente. Possiamo cambiare l'ordine delle tabelle e ottenere gli stessi risultati ottenuti da LEFT OUTER JOIN.
Ora abbiamo quei valori NULL perché la tabella dei clienti si trova sul lato destro del join.
Grazie per aver letto l'articolo Spero ti sia piaciuto! Si prega di lasciare i vostri commenti e domande, e buona giornata!
Non dimenticare di controllare script, app e componenti aggiuntivi SQL su Envato Market. Avrai un'idea di cosa è possibile con i database SQL e potresti trovare la soluzione perfetta per aiutarti con il tuo attuale progetto in via di sviluppo.
Seguici su Twitter o iscriviti al feed Nettuts + RSS per i migliori tutorial di sviluppo web sul web.
