Interogări MySQL în mai multe tabele (JOIN-uri) centru IT

materiale didactice pentru orele de informatică

(ultima modificare pe această pagină: 13.11.2016)

join-uri

Tutorial video

Dacă îți place doar videoclipul uita-te la, cu greu vei învăța ceva. Pentru cel mai bun efect de învățare, lucrați direct pe computer și urmați exemplele.

Folii

(ultima sincronizare a prezentării PDF: 02.08.2017)

Dacă nu puteți vedea o prezentare PDF, faceți clic aici pentru a o descărca: Descărcare directă prezentare PDF

Această secvență presupune cunoașterea următorului conținut: Introducere în interogările SELECT în MySQL.

Exerciții cu soluții detaliate și explicații pot fi găsite aici: Exerciții despre JOIN-uri în MySQL

MySQL dump "customer-simply.sql"

Pentru a putea înțelege exemplele din diapozitive și din tutorialul video, descărcați acest dump MySQL: customer-simply.sql, deschideți-l în Workbench (File -> Open SQL Script ...) și lăsați-l să ruleze. Se creează o bază de date „Clienți” care conține două tabele („Locații” și „Clienți”).

Solicitați date din două tabele

Exemplu de scenariu: Imaginea prezintă două tabele conectate care sunt legate cu o cheie străină. Gândiți-vă care sunt cheile principale și care sunt cheile străine.

Soluţie:
Clienți(Număr de înregistrare client, numele clientului, prenumele clientului, telefonul clientului, ↑ țara clientului, numărul clientului)
țări (landID, countryName, countryDelivery status)

Cheile principale sunt, prin urmare, ID-ul clientului și ID-ul țării (recunoscut prin subliniere), cheia străină este customerLand în tabelul clienților (recunoscută prin săgeata din fața customerCountry). Cheia străină kundeLand se referă la cheia principală a țărilor din tabel.

Fila „client” conține, prin urmare, un câmp „customerLand”. ID-ul unei țări este introdus aici, care se referă la câmpul landID din tabelul „țări”.

Dacă doriți să afișați numele clientului și numele țării în text integral într-o interogare SELECT, trebuie să preluați date din ambele tabele.

Cod pentru a genera aceste două tabele:

cu câteva date fictive:

Opțiunea 1: ÎNREGISTRARE/ÎNREGISTRARE ÎN STÂNGA

Selectează câmpurile specificate (customerID, customerName, customerCountry, countryID, countryName) din cele două tabele clienți și țări (client JOIN țări) și se limitează la cazurile în care customerCountry se potrivește cu countryID (WHERE clientCountry = countryID).

Dacă declarația WHERE este omisă aici, toate înregistrările de date de la „clienți” sunt combinate cu toate înregistrările de date din „țări”, astfel încât, cu 3 țări și 4 clienți, sunt afișate 12 linii în total (dintre care patru sunt de trei ori fiecare - deci 8 redundant).

Acest rezultat inutil se numește produs cartezian. Revedeți întotdeauna rezultatele interogării pentru a vedea că ați dat sens rezultatelor interogării cu constrângeri corecte.

Dacă în tabele Câmpuri cu nume identice trebuie specificat tabelul din care trebuie luată valoarea, sintaxă: nume tabel.nume câmp

ÎNREGISTRARE STÂNGA

Dacă o valoare pentru customerCountry este specificată în tabelul „clienți” care nu poate fi găsită în tabelul „țări”, atunci această înregistrare de date nu este afișată. O astfel de înregistrare de date incorectă ar fi în plus față de listarea SQL de mai sus, de ex.

LandID 9 nu există, astfel încât înregistrarea de date nu este afișată în interogarea de mai sus.

Cu LEFT JOIN se înregistrează toate înregistrările de date din tabelul din stânga („stânga”):

Atenție: „UNDE” se înlocuiește cu „ACTIVAT”.

Opțiunea 2: notație implicită

În loc să scriem JOIN în mod explicit, putem pur și simplu să separăm tabelele care trebuie interogate cu virgule:

De altfel, INNER JOIN este exact la fel ca JOIN. Se vorbește despre zahăr sintactic.

Exerciții

Toate exercițiile pot fi găsite în colecția de materiale (există și toate fișierele suplimentare, cum ar fi imagini, diagrame de clasă sau șabloane HTML!).

Puteți descărca exercițiul curent aici ca fișier txt.

***** Exerciții: MySQL - SELECT: Interogați mai multe tabele cu JOIN-uri *****


MySQL06_1: produs cartezian

Explicați în propriile cuvinte de ce această interogare nu dă un rezultat semnificativ:

--- Începe codul MySQL ---
SELECT * DE LA clienți, locații
--- Sfârșitul codului ---

MySQL06_2: interogări simple pe două tabele - DB client-simplu

Utilizați această bază de date pentru acest exercițiu: 07mysql/_dumps/customers/customers-simply.sql

A) Introduceți numele, codul poștal și orașul tuturor clienților. Lista conține numele clientului și numele locului în care locuiește.

B) Introduceți numele și locul de reședință al tuturor clienților care au codul poștal 79312.

C) Introduceți numele și locul de reședință al tuturor clienților care locuiesc în Emmendingen (criteriul de restricție NU este codul poștal, ci „Emmendingen”).

D) Introduceți numele, locul de reședință și numărul de locuitori pentru toți clienții care locuiesc într-un loc cu peste 70.000 de locuitori.

E) Enumerați toate locurile cu mai puțin de 1.000.000 de locuitori.

F) Introduceți numele clientului și numele locului pentru toți clienții care locuiesc în locuri cu o populație cuprinsă între 100.000 și 1.500.000.

G) Introduceți numele clientului, codul poștal și numele orașului pentru toți clienții al căror nume conține un "e" și toate orașele care conțin un "u" sau un "r" (deci frEd din stUden este afișat exact ca jEssE din bRnz, frEd din săruri, dar nu și Martin din hamburg).

MySQL06_2a: Exerciții cu privire la interogări pe două tabele: Librărie

Utilizați această bază de date pentru acest exercițiu:
07mysql/_dumps/buchladen/buchladen.sql

A) Enumerați toți furnizorii care locuiesc în Reute. Ieșire: numele furnizorului, numele locului, codul poștal

B) Afișați o listă a tuturor editorilor ale căror nume încep cu L. Ediție: numele editorului, numele locului; vă rugăm să sortați după numele locului în ordine descrescătoare.

C) Afișați o listă cu toți furnizorii ale căror nume încep cu Schus. sau Loe . începe și cine stă în Emmendingen. Ieșire: numele furnizorului, numele locului, sortate după numele furnizorului în ordine crescătoare.

D) Afișați o listă a tuturor locurilor, dar numai a acelor locuri în care se află un editor cu un n în numele său.


E) Furnizați o listă cu toate cărțile și furnizorii acestora (atenție: consultați 3 tabele!)

F) Furnizați o listă a tuturor cărților care costă mai mult de 10 euro (ediție: numele furnizorului, titlul cărții, prețul de vânzare) - Atenție: consultați 3 tabele

G) Afișați o listă a tuturor cărților (ediție: titlul cărții, categorie) - Atenție: interogați 3 tabele

MySQL06_3: JOIN vs. ÎNREGISTRARE STÂNGA

Utilizați această bază de date pentru acest exercițiu: 07mysql/_dumps/customers/customers-simply.sql

Uită-te la următoarele două afirmații și explică în ce fel vor diferi tabelele de rezultate. După aceea, încercați comenzile și verificați răspunsul.

--- Începe codul MySQL ---
-- Declarația 1: ÎNREGISTRARE LA STÂNGA
SELECȚIONEAZĂ k.customer_id, k.name, o.name DE LA clienți AS k LEFT JOIN locații AS o ON sau cod poștal = k.ort_postcode ORDER BY k.customer_id

-- Declarația 2: EQUI-JOIN
SELECT k.customer_id, k.name, o.name FROM client AS k, location AS o WHERE or postcode = k.ort_postcode ORDER BY k.customer_id
--- Sfârșitul codului ---

MySQL06_4: Exerciții la interogări de până la 5 tabele: Librărie

Utilizați această bază de date pentru acest exercițiu:
07mysql/_dumps/buchladen/buchladen.sql


A) Căutăm toți furnizorii cu sediul în Freiburg. Introduceți numele furnizorului, locația furnizorului și codul poștal.
(Soluție: se produc trei furnizori.)

B) Căutăm toți editorii cu sediul la München. Introduceți numele și locul publicării editorului.
(Soluție: există trei editori.)

C) Căutăm toate cărțile care au fost publicate de Verlag Assal. Introduceți titlul cărții, anul publicării și numele editorului, sortate după anul publicării în ordine descrescătoare.
(Sugestie: sunt publicate patru cărți.)

D) Căutăm toate cărțile de la furnizorul Schustermann. Titlul cărții de ieșire și numele furnizorului.
(Sugestie: vor fi publicate trei cărți.)

E) Se doresc toate thrillerele. Titlul cărții de ieșire și categoria, sortate după titlul cărții.
(Sugestie: Se vor da cinci cărți, prima este „Un ultim sărut”.)

F) Căutăm toate romanele de dragoste. Afișați titlul cărții, categoria și numele editorului, sortate după titlul cărții în ordine crescătoare.
(Soluție: sunt publicate șapte înregistrări, mai întâi „Medicul de țară și secretarul său”.)

G) Căutăm toate cărțile de Sabrina Müller. Introduceți numele, prenumele și titlul cărții autorului, sortate după titlul cărții în ordine descrescătoare.
(Sfat pentru soluție: sunt returnate patru înregistrări, mai întâi „Wild Hunt for John Smith - Retribution”).

H) Căutați sunt toți thriller-urile Sabrinei Müller. Includeți numele autorului, titlul cărții și categoria.
(Soluție: sunt scoase două seturi de date.)

(Atenție: încă nu puteți face asta!)
Căutăm toate cărțile de Sabrina Müller care pot fi clasificate în categoriile thriller sau umor. Includeți numele autorului, titlul cărții și categoria.

Pentru ca cartea „Wild Hunt on John Smith - The Revenge” să nu apară de două ori, trebuie să grupați rezultatul după titlul cărții adăugând ORDER BY în fața ORDER BY:

--- Începe codul MySQL ---
GRUPUL PE titlu
--- Sfârșitul codului ---

Pentru ca ambele categorii să fie afișate acum și în coloana „descriere” pentru această carte, nu utilizați pur și simplu numele câmpului „descriere” pentru ieșirea categoriei, ci mai degrabă
--- Începe codul MySQL ---
GROUP_CONCAT (nume)
--- Sfârșitul codului ---
Aceasta înseamnă că pentru cărțile clasificate în mai multe categorii, toate categoriile sunt afișate într-un singur câmp, separate prin virgule.
(Soluție: sunt înregistrate trei înregistrări de date.)