Share
Astăzi, ne continuăm călătoria în lumea sistemelor SQL și a bazelor de date relaționale. În această parte a seriei, vom învăța cum să lucrăm cu mai multe mese care au relații între ele. Mai întâi, vom trece peste câteva concepte de bază și apoi vom începe să lucrăm cu interogări JOIN în SQL.
De asemenea, puteți vedea bazele de date SQL în acțiune, verificând scripturile, aplicațiile și suplimentele SQL pe Envato Market.
La crearea unei baze de date, bunul simț dictează că folosim tabele separate pentru diferite tipuri de entități. Câteva exemple sunt: clienții, comenzile, articolele, mesajele etc. Dar, de asemenea, trebuie să avem relații între aceste mese. De exemplu, clienții fac comenzi și comenzile conțin elemente. Aceste relații trebuie reprezentate în baza de date. De asemenea, atunci când preluăm date cu SQL, trebuie să folosim anumite tipuri de interogări JOIN pentru a obține ceea ce avem nevoie.
Există mai multe tipuri de relații baze de date. Astăzi vom acoperi următoarele:
Atunci când selectăm date din mai multe tabele cu relații, vom folosi interogarea JOIN. Există mai multe tipuri de JOIN's și vom învăța despre următoarele:
De asemenea, vom afla despre clauza ON și clauza USING.
Să presupunem că aveți o masă pentru clienți:
Putem pune informațiile despre adresa clientului pe o masă separată:
Acum avem o relație între tabelul Clienți și tabelul Adrese. Dacă fiecare adresă poate aparține unui singur client, această relație este "One to One". Țineți minte că acest tip de relație nu este foarte comună. Tabelul inițial care a inclus adresa împreună cu clientul ar fi putut funcționa bine în majoritatea cazurilor.
Observați că acum există un câmp numit "address_id" în tabelul Clienți, care se referă la înregistrarea de potrivire din tabelul Adresă. Aceasta se numește "cheie străină" și se folosește pentru toate tipurile de relații de bază de date. Vom aborda acest subiect mai târziu în articol.
Putem vizualiza relația dintre client și înregistrările adreselor, cum ar fi:
Rețineți că existența unei relații poate fi opțională, ca și cum ați avea un record client care nu are înregistrări de adrese similare.
Acesta este cel mai frecvent utilizat tip de relație. Luați în considerare un site web de comerț electronic, cu următoarele:
În aceste cazuri ar trebui să creăm relații "One to Many". Iată un exemplu:
Fiecare client poate avea comenzi zero, una sau mai multe. Dar o comandă poate aparține unui singur client.
În unele cazuri, este posibil să aveți nevoie de mai multe instanțe pe ambele părți ale relației. De exemplu, fiecare comandă poate conține elemente multiple. Și fiecare element poate fi, de asemenea, în mai multe comenzi.
Pentru aceste relații, trebuie să creați o masă suplimentară:
Tabelul Items_Orders are un singur scop, și anume acela de a crea o relație "Multe până la multe" între articole și comenzi.
Iată cum putem vizualiza acest tip de relație:
Dacă doriți să includeți înregistrările items_orders în grafic, acesta ar putea arăta astfel:
Acest lucru este folosit atunci când un tabel trebuie să aibă o relație cu el însuși. De exemplu, să presupunem că aveți un program de recomandare. Clienții pot trimite alți clienți pe site-ul dvs. de cumpărături. Tabelul poate arăta astfel:
Clienții 102 și 103 au fost trimisi de către client 101.
Acest lucru poate fi, de asemenea, similar cu relația "unul cu multe", deoarece un client poate referi la mai mulți clienți. De asemenea, poate fi vizualizată ca o structură de copac:
Un client poate considera zero, unul sau mai mulți clienți. Fiecare client poate fi trimis de un singur client sau deloc.
Dacă doriți să creați o relație de sine de referință "de la mulți la mulți", veți avea nevoie de o masă suplimentară, la fel cum am vorbit în ultima secțiune.
Până acum, am aflat doar despre unele dintre concepte. Acum este timpul să le aducem la viață folosind SQL. Pentru această parte, trebuie să înțelegem ce chei străine sunt.
În exemplele de relații de mai sus, am avut întotdeauna aceste câmpuri "**** _ id" care au făcut trimitere la o coloană dintr-un alt tabel. În acest exemplu, coloana client_id din tabelul Comenzi este o coloană Cheie străină:
Cu o bază de date cum ar fi MySQL, există două moduri de a crea coloane de chei străine:
Să formăm un tabel simplu pentru clienți:
CREATE TABLE clienți (client_id INT AUTO_INCREMENT PRIMARY KEY, client_name VARCHAR (100));
Acum tabelul de comenzi, care va conține o cheie străină:
Comanda CREATE TABLE (comandă_id INT AUTO_INCREMENT PRIMARY KEY, client_id INT, suma DOUBLE, CUVÂNT CUVÂNT (customer_id) REFERENȚII clienți (client_id));
Ambele coloane (customers.customer_id și orders.customer_id) ar trebui să aibă aceeași structură exactă a datelor. Dacă unul este INT, celălalt nu ar trebui să fie BIGINT de exemplu.
Rețineți că în MySQL numai motorul InnoDB are suport complet pentru Cheile Externe. Dar alte motoare de stocare vă vor permite în continuare să le specificați fără a da vreo eroare. De asemenea, coloana Cheie străină este indexată automat, cu excepția cazului în care specificați alt index pentru aceasta.
Același tabel de comenzi poate fi creat fără a declara în mod explicit că coloana client_id este o cheie străină:
Comenzile CREATE TABLE (ordin_id INT AUTO_INCREMENT CHEIE PRIMARĂ, client_id INT, suma DOUBLE, INDEX (client_id));
La recuperarea datelor cu o interogare JOIN, puteți trata această coloană ca o cheie străină chiar dacă motorul bazei de date nu este conștient de acea relație.
SELECT * FROM comenzi JOIN clienți USING (client_id)
Vom afla mai multe despre interogările JOIN din articol.
Software-ul meu curent preferat pentru proiectarea bazelor de date și pentru vizualizarea relațiilor de chei străine este MySQL Workbench.
Odată ce proiectați baza de date, puteți exporta SQL-ul și îl puteți rula pe serverul dvs. Acest lucru este foarte util pentru proiecte de baze de date mai complexe și mai complexe.
Pentru a prelua date dintr-o bază de date care are relații, trebuie adesea să folosim interogări JOIN.
Înainte de a începe, să creăm tabelele și unele date de probă cu care să lucrăm.
Accentsconagua
CREATE TABLE clienți (client_id INT AUTO_INCREMENT PRIMARY KEY, client_name VARCHAR (100)); Comanda CREATE TABLE (comandă_id INT AUTO_INCREMENT PRIMARY KEY, client_id INT, suma DOUBLE, CUVÂNT CUVÂNT (customer_id) REFERENȚII clienți (client_id)); INSCRIERE IN "clienți" ("customer_id", "customer_name") VALUES (1, "Adam"), (2, "Andy"), (3, "Joe"); (1, 1, 19.99), (2, 1, 35.15), (3, 3, 17.56), (4, 4, 12.34) ; Avem 4 clienți. Un client are două comenzi, doi clienți au câte o singură comandă, iar un client nu are nicio comandă. Acum, să vedem diferitele tipuri de interogări JOIN pe care le putem rula pe aceste mese.
Acesta este tipul implicit de interogare JOIN când nu este specificată nicio condiție.
Rezultatul este un așa-numit "produs cartezian" al meselor. Înseamnă că fiecare rând din prima masă se potrivește cu fiecare rând din al doilea tabel. Din moment ce fiecare tabel avea 4 rânduri, am ajuns să obținem un rezultat de 16 rânduri.
Cuvântul cheie JOIN poate fi opțional înlocuit cu o virgulă.
Desigur, acest tip de rezultat nu este de obicei util. Deci haideți să arătăm celelalte tipuri de asociere.
Cu acest tip de interogare JOIN, tabelele trebuie să aibă un nume de coloană corespunzător. În cazul nostru, ambele tabele au coloana client_id. Deci, MySQL se va alătura înregistrărilor numai atunci când valoarea acestei coloane se potrivește pe două înregistrări.
După cum puteți vedea, coloana client_id este afișată numai o dată, deoarece motorul bazei de date tratează acest lucru ca coloană obișnuită. Putem vedea cele două ordine plasate de Adam, iar celelalte două ordine ale lui Joe și Sandy. În cele din urmă, primim câteva informații utile.
Când este specificată o condiție de conectare, este efectuată o intrare internă. În acest caz, ar fi o idee bună să aveți potrivirea câmpului client_id pe ambele tabele. Rezultatele ar trebui să fie asemănătoare cu cea din "Natural Join".
Rezultatele sunt aceleași, cu excepția unei mici diferențe. Coloana client_id se repetă de două ori, o dată pentru fiecare tabel. Motivul este că am solicitat doar ca baza de date să se potrivească cu valorile din cele două coloane. Dar, de fapt, nu știe că ele reprezintă aceleași informații.
Să adăugăm mai multe condiții la interogare.
De data aceasta am primit doar comenzile de peste $ 15.
Înainte de a trece la alte tipuri de asociere, trebuie să ne uităm la clauza ON. Acest lucru este util pentru punerea condițiilor JOIN într-o clauză separată.
Acum putem distinge condiția JOIN din condițiile clauzei WHERE. Dar există și o mică diferență în funcționalitate. Vom vedea acest lucru în exemplele JOINȚE STÂNGA.
Clauza USING este similară clauzei ON, dar este mai scurtă. Dacă o coloană are același nume pe ambele tabele, o putem specifica aici.
De fapt, aceasta seamănă mult cu JOINTUL NATURAL, astfel încât coloana de îmbinare (client_id) nu se repetă de două ori în rezultate.
O JOINȚE STÂNGA este un tip de Outer Join. În aceste interogări, dacă nu se găsește nici o potrivire din cel de-al doilea tabel, înregistrarea din primul tabel este încă afișată.
Chiar dacă Andy nu are ordine, înregistrarea lui este încă afișată. Valorile din coloanele celui de-al doilea tabel sunt setate la NULL.
Acest lucru este, de asemenea, util pentru găsirea de înregistrări care nu au relații. De exemplu, putem căuta clienți care nu au plasat comenzi.
Tot ce am făcut a fost să căutăm valori NULL pentru ordinul_id.
De asemenea, rețineți că cuvântul cheie OUTER este opțional. Puteți să utilizați LEFT JOIN doar în loc de LEFT OUTOUN JOIN.
Acum, să examinăm o interogare cu o condiție.
Și ce sa întâmplat cu Andy și Sandy? LEFT JOIN trebuia să returneze clienții fără comenzi corespunzătoare. Problema este că clauza WHERE blochează aceste rezultate. Pentru a le obține, putem încerca să includem și condiția NULL.
Îl avem pe Andy, dar nu pe Sandy. Totuși, acest lucru nu pare drept. Pentru a obține ceea ce vrem, trebuie să folosim clauza ON.
Acum, avem toți și toate comenzile de peste 15 USD. După cum am spus mai devreme, clauza ON uneori are o funcționalitate ușor diferită de clauza WHERE. Într-un External Outer Alăturați-vă ca acesta, rândurile sunt incluse chiar dacă nu corespund condițiilor clauzei ON.
O JOINȚĂ EXTERNĂ DREAPTA funcționează exact la fel, dar ordinea meselor este inversată.
De data aceasta nu avem rezultate NULL, deoarece fiecare comandă are o înregistrare de client corespunzătoare. Putem schimba ordinea tabelelor și obținem aceleași rezultate ca și noi de la JOINTERUL OUTER LEFT.
Acum avem aceste valori NULL deoarece tabelul clienților este în partea dreaptă a liniei.
Vă mulțumim că ați citit articolul. Sper ca ti-a placut! Vă rugăm să lăsați comentariile și întrebările dvs. și să aveți o zi minunată!
Nu uitați să verificați scripturile SQL, aplicațiile și suplimentele de pe Envato Market. Veți obține un sentiment al posibilităților cu bazele de date SQL și puteți găsi soluția perfectă pentru a vă ajuta cu proiectul dvs. în curs de dezvoltare.
Urmați-ne pe Twitter sau abonați la Nettuts + RSS Feed pentru cele mai bune tutoriale de dezvoltare web de pe web.
