Topic: SELECT'ai, JOIN'ai

Bandau vis aiškiai ir tvirtai įvažiuoti į "JOIN'us". Anksčiau vis kažkaip pavykdavo ir be jų susidoroti, tačiau suvokiu, kad su JOIN'ais galima žymiai suoptimizuoti ir padaryt lankstesnes užklausas.

Tai vat, pirmas dominantis pavyzdžio sprendimas būtų toks - yra dvi lenteles:

* Tekstai (t_id:int, tekstas:text)
* Lankytojai (l_id:int, t_pid:int)

Esmė tokia: kai lankytojas atidaro tekstą yra įrašomas įrašas į `Lankytojai`, kad tas lankytojas yra pamatęs tam tikrą tekstą.
Tai dabar reikia tokio SELECT'o, kad ištraukti tam tikro lankytojo (pagal `Lankytojai`.`l_id`) visus peržiūrėtus ir neperžiūrėtus tekstus (gali būti ir du atskiri SELECT'ai).

Vadovaujantis aibių teorijom, algebriniais veiksmais ir kitais abrikosais reikia išgauti daugmaž tokius poaibius:

Neperžiūrėti tekstai                 Peržiūrėti tekstai
    http://callia.homeip.net/files/poaibiai1.png                      http://callia.homeip.net/files/poaibiai3.png

Iš esmės, peržiūrėtus tekstus pavyko išgauti su tokią užklausa:

SELECT tekstai.*, lankytojai.*
FROM tekstai
LEFT JOIN lankytojai
ON lankytojai.t_id = tekstai.t_id 
WHERE lankytojai.l_id = 5

Neperžiūrėtų tekstų su viena patogia užklausa nelabai pavyksta išgauti.. Neįsivaizduoju dar taip vaizdžiai JOIN'us, todėl kreipiuosi dėl pagalbos.:)

P.S.: Nereikia galvoti kokio nors kito, gal geresnio, sprendimo, tiesiog domina būtent tokio pavyzdžio sprendimas


Tęsiant temą, gal kas taip pat vaizdžiai pateiktų JOIN'ų pavyzdžius kitiems veiksmams su lentelėm.
Gal kam daugiau tai būtų naudinga, gal padės suvokti "JOIN'us";)

Atimtis
http://callia.homeip.net/files/poaibiai1.png

Irgi atimtis?
http://callia.homeip.net/files/poaibiai2.png

Sandauga
http://callia.homeip.net/files/poaibiai3.png

Sudėtis
http://callia.homeip.net/files/poaibiai4.png

Re: SELECT'ai, JOIN'ai

Iš pradžiu norėčiau paminėti, kad tikriausiai negalima apie duomenų bazės lenteles taip tiesiogiai kalbėti kaip apie abies (iš matematikos konteksto). Kodėl? Todėl, kad duomenų bazės lentelės turi stulpelius, kurie turi ryšius su kitais stulpeliais (tiek iš tos pačios lentelės, tiek iš kitų lentelių). Minėtą aibių savoką (su operacijom, logika ir pan.) galima naudoti, bet reikėtu labiau viską konkretizuoti. Taip pat nesu tikras kaip ir kokio lygio aibių sąvokos yra naudojamos duomenų bazių teorijoje.

Kad plačiai žinoma aibių sąvoka netinka kalbant apie JOIN'us galima aiškinti ir žvelgiant į pateiktus grafinius aibių atvaizdus. Blogumas tame, kad duomenų bazių lentelėse yra įrašai (eilutės), kuriuos sudaro stulpeliuose esanti informacija. Vienas stulpelis gali aprašyti lankytojus (lankytojo ID), o kitas perskaitytas knygas (teksto ID). Taigi mes vienoje lentelėje jau turim duomenis apie du skirtingus objektus: lankytojus ir tekstus. Taip pat neaišku ką laikome mūsų tiriamu objektu: bet kurį lentelės įrašą (eilutė), unikalų lankytoją ar unikalų tekstą (nes tas pats lankytojas ar tas pats tekstas gali būti paminėtas daugiau nei viename įraše).

Taip pat operacijų su aibėm grafinė išraiška netinka atvaizduoti operacijom su duomenų bazių lentelėmis, nes dvimačiai vaizdai neperteikia visų niuansų (kai vienas įrašas iš vienos lentelės siejamas su daug įrašų iš kitos ir pan.).

Bendru atveju nesu susidūręs su tokiu duomenų bazių lentelių vaizdavimo metodu ar bandymu sieti su aibėm, manau, kad tai būtų klaidingas būdas bandyti suvokti kaip veikia JOIN'ai ;)

Paprastai kalbant JOIN'ą apibūdinčiau kaip operaciją, kuri leidžia sujungti lenteles pagal norimus kriterijus.

... vėliau papildysiu pavyzdžiais ir paaiškinimais

3 (edited by Aleksandras 2006-11-29 14:13:43)

Re: SELECT'ai, JOIN'ai

Gal ištiesu per nelyg abstrakčiai būtų aiškinti tokiom "grafinėm aibėm" apie duomenų bazes.
Tiesiog čia taip paprastai (gal pernelyg paprastai;]) pabandžiau populistiškai pasinauduoti reliacinė algebra, kurios esmė ir yra tai, kad "kol ryšiai yra aibės, tai ir priemonės tiems ryšiams manipuliuoti gali būti pagrįsti tradiciškomis teoriškomis-aibių operacijomis, papildytomis kai kuriom specialiom, speficiniom duomenų bazių operacijomis" (sorry, bet truputi versta, bet esmė, manau, suprasti galima;]). T.y. čia be tokių tradiciskų veiksmų su aibėm, kaip susikirtymas, apjungimas, skirtumas, sandauga, dar prisideda ir projekcija, prijungimas, dalyba.. ir t.t. ir t.t. čia jau tikrai biški per giliai šaknis knysti reiktų, ko man nelabai ir reikia.;)

Rašant čia apie tai, biški pagooglinau su naujom idėjom, apie tas visokias algebras. Tai radau keletą straipsnių apie tas algebras ir duomenų bazes. Pasirodo tie visi veiksmai "susikirtymas", "sandauga" ir pan, tarsi ir verčiasi kaip "inner/outer/left/right/etc JOIN".:) Jeigu įdomu, tai straipsneliai yra čia:

* http://ru.wikipedia.org/wiki/&#1056 … amp;#1072; (rus) (grynai matematiškai apie rel. algebrą);
* http://en.wikipedia.org/wiki/Relational_algebra (eng) (panašiai, tik su realiais pavyzdžiai su DB lentelėm - kas darosi su lentelių įrašais, jas įvairiai apjungiant; čia jau radau daugmaž tą, ko man ir reikėjo;]);
* http://en.wikipedia.org/wiki/Relational_model (eng) (kaip viskas tai pavirto į reliacinės duomenų bazės).
* http://www.mstu.edu.ru/education/materi … h_4_4.html (rus) (čia galima rasti panašių paveiksliukų, kokius pateikiau ir aš, bet šalia yra pavaizduota ir kas gaunasi su įrašais, atliekant tam tirkas operacijas)


šiaip ar taip lauksiu iš Tavęs pavyzdžiu ir paaiškinimų.;) Pavyzdžių tai radau išties nemažai visokių, kurie lyg ir padėjo susivokti, tačiau vat su vienu iš pirmų pavyzdžiu, kurį prireikė padaryti, jau kilo klausimų. Tai pagrinde ir kreipiausi dėl to, kad padėtumėte suvokti JOIN'us su sau aktualiais pavyzdžiais.:)

Re: SELECT'ai, JOIN'ai

Aleksandras wrote:

Rašant čia apie tai, biški pagooglinau su naujom idėjom, apie tas visokias algebras. Tai radau keletą straipsnių apie tas algebras ir duomenų bazes. Pasirodo tie visi veiksmai "susikirtymas", "sandauga" ir pan, tarsi ir verčiasi kaip "inner/outer/left/right/etc JOIN".:) Jeigu įdomu, tai straipsneliai yra čia:

* http://ru.wikipedia.org/wiki/&#1056 … amp;#1072; (rus) (grynai matematiškai apie rel. algebrą);
* http://en.wikipedia.org/wiki/Relational_algebra (eng) (panašiai, tik su realiais pavyzdžiai su DB lentelėm - kas darosi su lentelių įrašais, jas įvairiai apjungiant; čia jau radau daugmaž tą, ko man ir reikėjo;]);
* http://en.wikipedia.org/wiki/Relational_model (eng) (kaip viskas tai pavirto į reliacinės duomenų bazės).
* http://www.mstu.edu.ru/education/materi … h_4_4.html (rus) (čia galima rasti panašių paveiksliukų, kokius pateikiau ir aš, bet šalia yra pavaizduota ir kas gaunasi su įrašais, atliekant tam tirkas operacijas)

Taip, bet reikia nepamiršti, kad yra tokie dalykai kaip UNION ir pan. Tam tikros realicinės algebros operacijos atliekamos kitais funkcionalumais, o JOIN'ai padengia tik dalį tos visos metamatikos. Aišku, beveik neabejoju, kad daugumos duomenų bazių serverių vidinė realizacija remiasi matematiniais metodais/modeliais.

Atvaizduoti JOIN'us man labiausiai tiktų šie paveiksliukai ;)

http://www.mysql.lt/forumas/img/forumas_mysql_003_join.png ir http://www.mysql.lt/forumas/img/forumas_mysql_005_join.png

Aleksandras wrote:

šiaip ar taip lauksiu iš Tavęs pavyzdžiu ir paaiškinimų.;) Pavyzdžių tai radau išties nemažai visokių, kurie lyg ir padėjo susivokti, tačiau vat su vienu iš pirmų pavyzdžiu, kurį prireikė padaryti, jau kilo klausimų. Tai pagrinde ir kreipiausi dėl to, kad padėtumėte suvokti JOIN'us su sau aktualiais pavyzdžiais.:)

Nežinau ar pora pavyzdžių padės daug suprasti. Realiai reikia skaityti metodiškai parašytas knygas apie duomenų bazes, nes darbui su duomenų bazėmis reikalingos žinios apie struktūras, ryšius, duomenų normalizacijas ir pan.

Tavo užduotį truputį praplėčiau, tikėdamasis, kad ji tiks ir platesniam žmonių ratui.

Taigi tarkime, kad turime 3 lenteles: Lankytojai, Tekstai ir Lankytojai_Tekstai

http://www.mysql.lt/forumas/img/forumas_mysql_004_join.png

Kadangi:
a) ryšiai gali būti kelių tipų (pvz.: vienas su vienu, vienas su daug, daug su daug);
b) ryšiai dažniausiai realizuojami per stulpelius, kurie būna Raktai ar bent jau Indeksai;
c) MySQL'as neteikia ryšių tipizavimo galimybių;

Todėl Lankytojų ir Tekstų sujungimui naudojama papildoma lentelė (Lankytojai_Tekstai), kuri apibūdins koks Lankytojas kokį Tekstą skaitė. Labai neisigilinant tik paminėsiu, kad vienas Lankytojas gali būti skaitęs daug Tekstų po daugiau nei vieną katą.

Taigi pavyzdžiai:
1) Pateikti visų Lankytojų, kurie skaitė bent po vieną Tekstą sąrašą, kartu pateikiant ir skaitytą Tekstą

Užklausa:

SELECT Lankytojas, Tekstas
FROM Lankytojai
    INNER JOIN Lankytojai_Tekstai ON Lankytojai.Lankytojo_ID = Lankytojai_Tekstai.Lankytojo_ID
    INNER JOIN Tekstai ON Lankytojai_Tekstai.Teksto_ID = Tekstai.Teksto_ID

arba

SELECT Lankytojas, Tekstas
FROM Lankytojai
    INNER JOIN Lankytojai_Tekstai USING (Lankytojo_ID)
    INNER JOIN Tekstai USING (Teksto_ID)

Rezultatas:

+--------------+-----------+
| Lankytojas   | Tekstas   |
+--------------+-----------+
| Lankytojas 1 | Tekstas 2 |
| Lankytojas 2 | Tekstas 3 |
| Lankytojas 3 | Tekstas 1 |
| Lankytojas 3 | Tekstas 6 |
+--------------+-----------+

2) Pateikti visus Lankytojus, kurie neskaitė jokio Teksto

Užklausa:

SELECT Lankytojas
FROM Lankytojai
    LEFT JOIN Lankytojai_Tekstai USING (Lankytojo_ID)
GROUP BY Lankytojai.Lankytojo_ID
HAVING COUNT(Lankytojai_Tekstai.Lankytojo_ID) = 0

Rezultatas: įrašu nėra, nes pagal pateiktus duomenis visi lankytojai kažką skaitė;

3) Pateikti Tekstus, kurių neskaitė joks Lankytojas

Užklausa:

SELECT Tekstas
FROM Tekstai
    LEFT JOIN Lankytojai_Tekstai USING (Teksto_ID)
GROUP BY Tekstai.Teksto_ID
HAVING COUNT(Lankytojai_Tekstai.Teksto_ID) = 0

Rezultatas:

+-----------+
| Tekstas   |
+-----------+
| Tekstas 4 |
| Tekstas 5 |
+-----------+

4) Pateikti visų Lankytojų ir visų Tekstų galimų kombinacijų sąrašą

Užklausa:

SELECT Lankytojas, Tekstas
FROM Lankytojai
    JOIN Tekstai

Rezultatas:

+--------------+-----------+
| Lankytojas   | Tekstas   |
+--------------+-----------+
| Lankytojas 1 | Tekstas 1 |
| Lankytojas 2 | Tekstas 1 |
| Lankytojas 3 | Tekstas 1 |
| Lankytojas 1 | Tekstas 2 |
| Lankytojas 2 | Tekstas 2 |
| Lankytojas 3 | Tekstas 2 |
| Lankytojas 1 | Tekstas 3 |
| Lankytojas 2 | Tekstas 3 |
| Lankytojas 3 | Tekstas 3 |
| Lankytojas 1 | Tekstas 4 |
| Lankytojas 2 | Tekstas 4 |
| Lankytojas 3 | Tekstas 4 |
| Lankytojas 1 | Tekstas 5 |
| Lankytojas 2 | Tekstas 5 |
| Lankytojas 3 | Tekstas 5 |
| Lankytojas 1 | Tekstas 6 |
| Lankytojas 2 | Tekstas 6 |
| Lankytojas 3 | Tekstas 6 |
+--------------+-----------+

Gale pateikiu užklausas duomenų struktūroms sukurti:

CREATE TABLE `Lankytojai` (
  `Lankytojo_ID` int(10) unsigned NOT NULL auto_increment,
  `Lankytojas` varchar(255) NOT NULL,
  PRIMARY KEY  (`Lankytojo_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `Tekstai` (
  `Teksto_ID` int(10) unsigned NOT NULL auto_increment,
  `Tekstas` varchar(255) NOT NULL,
  PRIMARY KEY  (`Teksto_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Lankytojai_Tekstai | CREATE TABLE `Lankytojai_Tekstai` (
  `Lankytojo_ID` int(10) unsigned NOT NULL,
  `Teksto_ID` int(10) unsigned NOT NULL,
  KEY `Lankytojo_ID` (`Lankytojo_ID`,`Teksto_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Galite prašyt norimų pavyzdžių pateiktiems duomenų struktūrai ir duomenims