MySQL JOIN: tsananguro, muenzaniso kushandisa murayiro uye vanorumbidza

Kugadzirwa imwe Database paIndaneti zvinhu hakuiti wakasiyana mureza Databases muhurukuro hurongwa MS SQL Server. Kazhinji, nokuti ichi mhando zvekuvaka vanoshandisa MY mutauro SQL, kunyange inogona kushandawo kune kukura neezvitendero Software zvigadzirwa kuti vemo kushandiswa. Asi mubvunzo munyaya usafamba nezvazvo.

Kazhinji kana tichishanda Databases pose mumitauro basa kuti mharidzo Data kuti zvinopiwa nenzira dzakasiyana-siyana nemishumo, calculators uye zvichingodaro. Sezvo kutonga, Implementation kuti matambudziko aya tinofanira kushandisa hakuna mumwe asi wandei matafura, complicating zvikuru nechivako kwayo kuabatanidza kuva chikumbiro chete,. Zvakakodzera kufunga, sezvo vaifanira kuva goho Data se vachava "kubata" tafura, uye chii tichava zvikuru zvinogamuchirika kuti programmer. Kugadzirisa matambudziko aya, shandisa rimwe vomureza MySQL magadzirirwo mutauro - Join.

Pfungwa mashoko Join

Languages Database kukura, pasinei mhando mutauro, kutora sezvikonzero mureza mashoko Chirungu nemaduramazwi (ndiro sei mamiriro kuti unoziva Chirungu, imi bude nyore kushanda matafura). Kuti pave kwokubatana tafura iri mharidzo atora shoko rakafanana - Join. Mumutauro Database Software akashandisa SQL My. Translation kweshoko basa chaizvo kufanana mumutauro - "mubatanidzwa".

Dudziro MySQL mamiriro - Join, mumwe ichazovapo zvakangofanana. Kana iwe decipher chinangwa kuvakwa, kureva pepa rehurukuro basa rake, tinowana zvinotevera tsika: magadzirirwo kuchaita kuunganidza zvaidiwa minda akasiyana matafura, kana nested queries mune rimwe mharidzo.

Mhando mazano kubatanidza

Kana programmer anofanira kutora nzwisa multiple matafura, uye anoziva kuminda anokosha mavari uye worudzii date runodikanwa mashoko, unogona kushandisa rimwe inokosha hurongwa kushamwaridzana, kuitira kuti awane achida mugumisiro. Basic zvivako (kubatana matafura) ina:

1. Inner vanobatana.
2. Cross Join.
3. Left vanobatana.
4. Right Join.

Zvichienderana basa, mumwe nomumwe vomureza mazano vachapa zvakasiyana zvabuda, izvo vachawana mishumo parameters siyana mune pfupi.

Kuunza uye rizare matafura kushandiswa pave paya

Usati watanga Somuenzaniso, kuti funga zvigadziriswe kushanda pamwe nemashoko Fusion constructs vanofanira kugadzirira matafura akawanda, iro ticharamba kushanda. Izvi zvichabatsira kuti varatidze misimboti dzaishandisa zvose, mukuwedzera, saka kuti beginners vachadzidza zvose zvinokosha nyore zvirongwa pamatafura.

Tafura vokutanga vachava kutsanangura zvimwe zvinhu izvo vanhu vari kuramba vasangana muupenyu hwake hwose.

The tafura yechipiri achaita kutsanangura dzimwe zvivako zvinhu kubva pakutanga tafura kubvumira mimwe basa navo.

Kazhinji, mabwendefa maviri ichava zvakakwana kuratidza muenzaniso basa ravo. Zvino unogona mberi kune anoshanda kufunga mazano edu.

Kushandisa Inner Join

Kana uri kushandisa MySQL mamiriro - Join Ineer akakosha kufunga zvimwe zvinhu zvayo. zvakagadzirwa Izvi zvinobvumira kuti kusarudza kubva matafura maviri chete zvinyorwa avo vari vokutanga uye tafura wechipiri. Ungazviita sei? tafura yokutanga tine tenzi kiyi - ID, iyo inoratidza Serial nhamba yamashoko ari patafura.

Kana ukaronga tafura wechipiri, ndiye kiyi inoshandiswa sezvo kutevedzana nhamba, somuenzaniso kunogona kuonekwa nenhamba. Pakusarudza mashoko Select mashoko achasarudza mugumisiro chete anonyora Serial nhamba rienderane - nokudaro, vari vokutanga uye tafura wechipiri.

Kana kuvaka zvinokosha kunzwisisa chaizvo unofanira kuwana mashoko. The inonyanya chikanganiso, kunyanya Developers pamusoro Database, hazvina musoro uye kukodzera kushandiswa kugadzira Inner Join. Somuenzaniso, MySQL Rwomukati Join, unogona kufunga imwe manyorero kuti vachadzoka nesu kuti kare anotsanangurwa uye akazadzwa tafura ruzivo zvinhu zvavo zvivako. Asi pangava nzira dzakawanda kushandisa nepurani. Panyaya iyi SQL wangu mutauro tefa chaizvo. Saka, unogona kuona mienzaniso kushandisa MySQL Inner Join.

Ndakabatana matafura pasina chaiyo chero parameters. Muchiitiko chino, tinowana mugumisiro urongwa hwakadaro:

Kana isu anoratidza neshoko Solution: Achishandisa basa, zvakakodzera kufunga chikuru kiyi zvinyorwa zviri pamahwendefa, mugumisiro yakatarwa zvichachinja zvinoshamisa. Muchiitiko chino, tinowana mharidzo, izvo achadzoka mumitsara chete avo kuti zvakafanana tenzi kiyi.

Mukana munhu pachake kushandisa, kana chakaita neshoko «iri» rechitatu rinoratidza minda kuti zvinofanira kuitika kubatana matafura. Panyaya iyi, mharidzo vachadzoka zvinotevera mashoko:

Features Shandisa Left Join

Kana tikaongorora imwe nzira tichibatanidza matafura uchishandisa MySQL yakarongeka - Join, mumwe anogona kuona musiyano pakati data, izvo outputted. mumwe mashandiro akadaro Kusiyiwa zvakagadzirwa.

Uchishandisa mamiriro Kusiyiwa Join MySQL ane zvimwe zvinhu uye, sezvo Inner, zvinoda kunyatsonzwisisa migumisiro kuwanikwa.

Muchiitiko chino, kutanga anosarudza zvinyorwa zvose kubva pakutanga patafura, uye zvakare pacho vari vasiyana padenga rechipiri tafura ehupfumi. Muchiitiko chino, kana pane zvinyorwa, somuenzaniso, "pachituru" rokutanga tafura uye tafura wechipiri havasi ehupfumi kwariri, kuti Left opareta kuratidzirana pamberi hwomukova kuti null, iyo anoudza programmer kuti zviratidzo panyaya iyi haina kunzwa .

Tichishandisa zvakagadzirwa ichi chichabatsira kusarudza minda kana Somuenzaniso, nhumbi dziri chitoro hauna kuisa mari, zviri garandi nguva , uye zvichingodaro.

Use Example Left

Waongororwa kuti pamasikiro opareta tsika Kusiyiwa Join MySQL kushandisa kare anotsanangurwa tafura. Somuenzaniso, unofanira kusarudza yose pamazita mishonga zviri chitoro, uye tarisa kuti vamwe vavo havana kubereka maitiro kana zvivako. Panyaya iyi, mharidzo achaita kuratidza zvigadzirwa zvose, uye avo vasina pfuma, uchabereka nulls.

Apo kushandiswa kuvaka Join

Sezvo parameter, ushamwari zvingasanganisira kwete chete anoratidza minda izvo unofanira kugadzira tafura kubatana, asi ringabatanidza opareta kupi ezvinhu.

Somuenzaniso, funga manyorero kuti anofanira kudzokera kwatiri chete zvinyorwa avo vasina kuisa pasi chiratidzo. Muchiitiko chino, unofanira kuwedzera magadzirirwo Join opareta mashoko uye anoratidza kuti ichizodzoka somugumisiro.

Kana kushandiswa MySQL Join - kupi zvinofanira kunzwisiswa zvakajeka kuti dzinoratidza zvinyorwa chete izvo zvinosanganisira pamusoro ezvinhu, uye mharidzo zvichange sezvinotevera:

zvikumbiro izvi kukubvumira kuti mharidzo-dzakananga Data chokuita yasarudzwa programmer ezvinhu. ezvinhu akadaro anogona kuratidza zvakawanda zvinoita apo updating date kusarudzwa parameters batanidzwa pamatafura.

Kushandisa Join kushandura mashoko anenge ari pamabwendefa

Kugadzira Join Chokwadi, nokudenga. Kunoitawo kuti regai chete akasiyana-siyana ivhu, asiwo batanidza kubva kuna zvikumbiro kuna matafura akawanda, kumanikidza mamwe mamiriro mune muenzaniso. Design rinogona kushandiswa mamwe mashoko kushandiswa. Saka, Join zvinogona kushandiswa kuchinja umboo iri tafura. Kana kuti, kuti kujekesa zvinhu zviri patafura, kana mune nguva kunodiwa kuti dzifambirane mashoko ari vakawanda matafura nenguva ezvinhu.

Somuenzaniso, funga nezvedambudziko zvinotevera. Kupiwa matafura zvitatu pane uchapupu. Zvakakodzera tiite shanduko mashoko ari zvose matafura vachishandisa chete mubvunzo. Zvinoitika nekugadzirisa mabasa akadai anogona kushandiswa murayiro Update Join zvakagadzirwa. Join zvikuru mhando mamiriro kunobva, saizvozvo sokune dzokuenzanisira Data pamusoro mugumisiro vanoda kuwana programmer.

Funga muenzaniso nyore. Unofanira dzifambirane mashoko ari chikumbiro chete zvakafanana ezvinhu. mhando iyi mubvunzo inovakwa kuti optimize yacho Database. Sei nyora queries siyana nerimwe matafura, kana unogona kupedza zvose date bata chikumbiro chimwe? MUENZANISO MySQL Update Join kwatiri kungava:

Kuvaka queries dzakaoma

Kazhinji kana kushanda Database queries zvakakodzera kuvaka kwete chete mubatanidzwa wevanhu matafura akawanda, asi nokushandiswa Sub-queries. matambudziko akadaro akaoma chaizvo kunzwisisa kuti ruzivo programmer Databases. The chinetso ndechokuti tinofanira kufunga kuburikidza danho rimwe nerimwe, kusarudza iyo mashoko kubva patafura kana mubvunzo vanofanira kuwana uye sei vanofanira kuenda kubasa mune ramangwana.

Nokuti kunzwisisa zvakawanda chaiwo anogona kunzi (mu MySQL Join) mienzaniso queries dzakaoma. Kana uri mutsva uye vari kutanga kushanda Databases, kurovedza kwakadaro kuchaita chete vanobatsirwa. Akakodzera musimboti achaita MySQL Akasiya Join mienzaniso.

mubvunzo uyu kuchatibatsira kudzokera 58 zvinyorwa okutengesa chibvumirano, ruzere kana pane mari pachiyero nokuda yasarudzwa zuva. Panyaya iyi, magetsi musi. Uye muenzaniso akawedzera mamiriro kuti chibvumirano inofanira kuva rokuti vatambi - "123". The on-screen mashoko (date) achaita arongwe - arongwe vakaverengwa kondirakiti.

Zvinotevera muenzaniso anoratidza mashoko vachibhadhara zvose, izvo zvichaita kuratidza chibvumirano nhamba.

kushandisa subqueries

Sezvambotaurwa, kana tichishanda Databases dzinogona kubatanidzwa kwete chete tafura, asi chikumbiro patafura. zvakagadzirwa iri ainyanya kushandiswa rikurumidze mubvunzo uye ayo Optimization.

Somuenzaniso, kana uchida kubva patafura, iro rine mazana minda uye, uti, chiuru records, sarudza minda maviri chete, zvakakodzera kushandisa mubvunzo kuti anodzoka chete yaidiwa minda, uye nokubatanidza pamwe huru date mharidzo. Somuenzaniso MySQL Join Sarudza anogona kufunga chikumbiro ichi mhando:

Hazvisi nzira dzose kushandiswa neezvitendero mazano MySQL, uye chete mureza. How kushandisa magadzirirwo Join zvayo siyana ayo programmer anosarudza, asi zvinokosha kuyeuka uye kurangarira kuti chii mugumisiro vanofanira kuwana ne mubvunzo.