PHPPamokos.lt


6. Lentelių sąryšiai ir kelios lentelės vienoje užklausoje

Praeitoje pamokoje išmokome ištraukti bei filtruoti duomenis iš lentelės su SELECT užklausos pagalba. Dabar pažiūrėsime, kaip tai galima padaryti iš kelių lentelių iš karto.

Lentelių sąryšiai ir išoriniai raktai

Pradėkime nuo to, kad lentelės turi būti tarpusavyje kažkaip surištos, kad galėtume vienoje užklausoje apdoroti jas abi. Tam yra naudojami taip vadinami išoriniai raktai (angl. "foreign keys"). Kalbant paprastai, išorinis raktas yra objektas, kuriuo nusakoma kad lentelės A stulpelis A1 yra surištas su lentelės B stulpeliu B1. Dar kartais vartojami žodžiai, kad lentelės stulpelis "rodo į" kitos lentelės kitą stulpelį.

Paimkime visiškai konkretų pavyzdį - sakykime, kad turime užsakymų lentelę ORDERS ir klientų lentelę CUSTOMERS. Ir žinome, kad mums reikės ateityje reikės rašyti užklausą, kuri grąžina sąrašą visų konkretaus kliento užsakymų. Tai reiškia, kad užsakymų lentelėje turi būti laukas, nusakantis klientą, ar ne? Tik jokiu būdu nedarykite tekstinio lauko su kliento vardu ir pavarde - tai labai neefektyvu: reikia kurti INT tipo lauką skaičių, kuris sutaptų su CUSTOMERS lentelės pirminiu raktu - lauku id.

Dažniausiai tokio lauko pavadinimas yra išorinės lentelės pavadinimas (gali būti vienaskaita), tada pabraukimo ženklas ir id: pvz customer_id, order_id jei lentelė orders, koks nors user_id jei lentelė users ir panašiai.

Schematiškai tai atrodo taip:

Kaip matote, lentelės orders laukas customer_id "rodo" į lentelės customers lauką id. Realiai tai reiškia, kad norint surišti duomenis, reikės, kad jeigu kliento ID numeris customers lentelėje yra, sakykime, 128, tai tada lentelės orders laukas customer_id turi būti lygus irgi 128.

Tokio išorinio rakto kūrimas realiai susideda iš dviejų etapų: pradžioje kuriamas lentelės laukas, o po to jau pats išorinis raktas. Jei kuriame patys su SQL kalba, tai atrodytų daugmaž taip: sakykime, kad pačias lenteles su reikalingais laukais jau turime, ir mums reikia sukurti tik raktą, taigi:

ALTER TABLE orders 
ADD CONSTRAINT FK_customer 
FOREIGN KEY (customer_id) REFERENCES customers(id);

Atrodo sudėtingai, ar ne? Tiesą pasakius, su phpMyAdmin pagalba sukursite išorinį raktą ir be jokių SQL žinių, bet tiems, kam norisi suprasti giliau - trumpai pakomentuosiu šios užklausos atskiras dalis:

  • ALTER TABLE orders - lentelės redagavimas
  • ADD CONSTRAINT FK_customer - paties rakto pridėjimas į lentelę, dažniausiai raktai vadinami FK_laukas (FK - nuo "Foreign Key")
  • FOREIGN KEY (customer_id) - nurodome, kad tai yra būtent išorinis raktas (yra ir kitokių), ir kad jis uždedamas būtent ant lauko customer_id
  • REFERENCES customers(id) - į kurios lentelės kurį lauką "rodo" tas išorinis laukas

SELECT ... JOIN sakiniai

Taigi, turime dvi lenteles ir išorinį raktą - pabandykime ištraukti duomenis pagal užduotį, nurodytą skyrelio pradžioje. Ištraukime visus konkretaus kliento užsakymus. Lentelės surišamos su komanda JOIN:

SELECT customers.first_name, customers.last_name, orders.price, orders.order_time
FROM orders
JOIN customers ON orders.customer_id = customers.id
ORDER BY orders.id

SELECT, FROM ir ORDER BY komandos jums turėtų būti jau žinomos. Taip pat prisiminkite kaip sakiau, kad norint ištraukti konkrečios lentelės konkretų lauką jie atskiriami tašku - pvz customers.first_name. O dabar pažiūrėkime atskirai į JOIN.

Šioje užklausoje yra viena pagrindinė lentelė orders ir viena šalutinė "prijungiama" lentelė customers. Tai būtent tai ir nusako JOIN: prijungiama lentele customers pagal taisyklę (ON) kad orders.customer_id turi būti lygus customers.id. Juk paprasta, ar ne? Pažiūrėkime, koks bus tokios užklausos rezultatas:

Taip pat reikia žinoti, kad trys skirtingi JOIN tipai, skirstomų pagal griežtumą. Iš esmės, tas tipas atsako į klausimą "ką daryti, jeigu šalutinėje lentelėje nerasta eilučių pagal sąryšį - ar grąžinti pagrindinę eilutę, ar ne?"

Paprastas JOIN, kitaip dar vadinamas INNER JOIN, veikia tokiu principu: jeigu pagal sąryšį šalutinėje lentelėje įrašų nerasta, tada negrąžinamas joks įrašas, negrąžinama ir pagrindinė eilutė. Kitaip tariant, jeigu pagal customer_id lentelėje customers nerastas klientas, tada ir užsakymai nebus rodomi.

LEFT JOIN veikia priešingai - pradžioje ieškomi įrašai pagrindinėje lentelėje, ir tada "dėl viso pikto" patikrinama - gal yra susijusių eilučių šalutinėje lentelėje pagal sąryšį, ir jei taip - jie yra prijungiami, jeigu ne - tie laukai tiesiog įgauna reikšmę NULL.

Taip pat yra ir RIGHT JOIN - jis yra naudojamas gana retai, bet jei sutiksite - kad žinotumėte, kaip jis veikia. Jis veikia atvirkštiniu principu nuo LEFT JOIN sąryšio - pirmiausia randa visas eilutes šalutinėje lentelėje, ir prie jų prijungia visas eilutes, kurios bus surastos pagrindinėje lentelėje. Toks naudojimas neturi labai didelės prasmės, nes viskas apsiverčia aukštyn kojomis - ir tokią užklausą prasmingiau rašyti per LEFT JOIN, padarant lentelę customers pagrindine lentele, prie jos jungiant orders per LEFT JOIN.

Atskleisiu jums viešą paslaptį - kad veiktų užklausos su JOIN, net nebūtina kurti išorinio rakto - užtenka turėti skirtingose lentelėse tuos du laukus ir juos nurodyti ON sąlygoje, užklausa suveiks taip pat. Tas FOREIGN KEY raktas reikalingas tvirtesniam sąryšiui, kad duomenų vientisumas nenutrūktų (apie tai kiek žemiau).

Daugiau nei dvi lentelės

Dar vienas dalykas - galima JOIN naudoti kelis kartus toje pačioje užklausoje - jungti kelias lenteles:

SELECT customers.first_name, customers.last_name, orders.price, orders.order_time, products.name
FROM orders
JOIN customers ON orders.customer_id = customers.id
JOIN products ON orders.product_id = products.id
ORDER BY orders.id

Dar daugiau - galima jungti ne tik su pagrindine lentele, bet ir su šalutine lentele, kuri tam kartui tampa ir pagrindine:

SELECT customers.first_name, customers.last_name, orders.price, orders.order_time, cities.name
FROM orders
JOIN customers ON orders.customer_id = customers.id
JOIN cities ON customers.city_id = cities.id
ORDER BY orders.id

Pasenęs metodas: lentelės per kablelį

Taip pat galima jungti lenteles net ir be JOIN. Turbūt šiam momentui jums jau sukasi galva nuo visokių variantų - bet vėlgi, čia yra nebūtina žinoti ir mažai naudojamas "senas" variantas - rašyti lenteles tiesiog per kablelį:

SELECT customers.first_name, customers.last_name, orders.price, orders.order_time
FROM orders, customers 
WHERE orders.customer_id = customers.id
ORDER BY orders.id

Tokia užklausa taip pat veiks ir atrodo trumpesnė, bet turi du didelius minusus: sunkiau skaitoma (neaišku, kur pagrindinė lentelė ir kur šalutinė) ir jei jums kada prireiks keisti duomenų bazę iš MySQL į kokią kitą, ne faktas kad ji suveiks. Tad naudoti tikrai nepatarčiau - bet vėlgi, kad suprastumėte, jei pamatytumėte kokiame senesniame projekte.

CASCADE ar RESTRICT - leisti ar uždrausti?

Kaip jau minėjau, išoriniai raktai yra nebūtini SELECT ... JOIN vykdymui, bet reikalingi duomenų vientisumui. Kas yra tas "vientisumas"? (angl. "data integrity") Tai yra sąlyga, kuri leidžia išlaikyti duomenis teisingus, net jei vienoje sąryšio pusėje įvyksta kažkokie pasikeitimai. Sakykime, mūsų konkrečiu atveju - kas būtų, jei ištrintume iš customers eilutę su id=128, tai tada lentelėje orders įrašai su reikšme customer_id=128 liktų nepririšti, tarsi "našlaičiai"? Tai teisingai sukurti išoriniai raktai užtikrina, kad atnaujinant duomenis vienoje lentelėje, būtų atitinkamai atnaujinami duomenys ir visose susijusiose lentelėse. Kaip būtent atnaujinami - yra keli variantai.

Kuriant išorinį raktą, komandos pabaigoje galime pridėti dvi papildomas taisykles: ON UPDATE ir ON DELETE. Kitaip tariant - ką MySQL turi daryti šalutinėse lentelėse, jei ištrinamas ar atnaujinamas įrašas pagrindinėje lentelėje?

Pavyzdys - praplečiant prieš tai rašytą rakto sukūrimo komandą:

ALTER TABLE orders 
ADD CONSTRAINT FK_customer 
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON UPDATE CASCADE
ON DELETE CASCADE;

Kaip matome, čia pasirinktas variantas yra CASCADE. Kiti yra RESTRICT, NO ACTION ir SET NULL. Trumpai apie kiekvieną:

RESTRICT arba NO ACTION - jie veikia identiškai: jie neleidžia atnaujinti/trinti duomenų, jei jie turi susijusių šalutinių įrašų. Kitaip tariant, jei nurodyta ON DELETE RESTRICT, tada MySQL neleis iš lentelės customers trinti eilutės su id=128, jei yra bent viena eilutė lentelėje orders su customer_id=128. Tiesiog mes klaidą ir nevykdys užklausos. Šis būdas yra pagrindinis veikimo būdas - t.y., jei nenurodysite jokio ON UPDATE ar ON DELETE veiksmo, bus vykdomas būtent šitas variantas - uždraudimas keisti duomenis. Jis logiškas ir saugiausias - apsaugo mūsų nuo netyčinio duomenų ištrynimo, kai pvz nepagalvojame kad yra susijusių eilučių.

CASCADE - reiškia, kad šalutinėse eilutėse turi vykti toks pat veiksmas: ON UPDATE CASCADE reiškia, kad visose šalutinėse eilutėse taip pat turi būti atnaujinama rakto reikšmė (pvz jei customers.id pakeičiama iš 128 į 129, tada orders.customer_id irgi automatiškai pasikeis iš 128 į 129), o ON DELETE CASCADE reiškia, kad trinant pagrindinę eilutę bus ištrinamos visos su ja susijusios eilutės šalutinėje lentelėje.

SET NULL - sunkiai suvokiu panaudojimo prasmę, bet tokia galimybė yra: trinant/atnaujinant pagrindinę eilutę, šalutinėse eilutėse išorinio rakto laukai bus paverčiami į NULL. Tiesa, naudojant šį būdą, įsitikinkite, kad tie laukai nėra apibrėžti kaip NOT NULL.

UNION - kelių užklausų suryšimas į vieną

Retais atvejais mes turime duomenis dviejose lentelėse, ir jie niekaip nesurišti pagal jokį raktą. Sakykime, turime lenteles income ir expenses, ir mums reikia ištraukti visas finansines operacijas - iš abiejų lentelių. Tam galima naudoti operatorių UNION - tiesiog juo atskiriame kelias užklausas. Svarbu, kad jose visiškai sutaptų rezultato laukai:

SELECT amount, operation_date FROM income
UNION
SELECT amount, operation_date FROM expenses;

Taip pat prie UNION galima pridėti žodelį ALL - jis užtikrins, kad ir pasikartojantys įrašau bus grąžinti.

Tiesa, pats UNION poreikis kalba apie tai, kad gali būti nelogiškai sudėliota duomenų bazės struktūra - bet dažniausiai struktūros taip paprastai nepakeisi, tai šis operatorius kartais būna visai naudingas, kaip "gelbėjimosi šiaudas".

Apibendrinimas

Štai taip MySQL sistemoje veikia sąryšiai tarp lentelių, išoriniai raktai ir jų papildomos taisyklės. Kitame skyrelyje nagrinėsime duomenų grupavimą - jo labiausiai reikia skaičiavimui ir statistinėms ataskaitoms.



(c) 2015-2018. Visais klausimais kreipkitės povilas@laraveldaily.com