I SQL yam NAWE: inkcazelo, umzekelo ngokusebenzisa umyalelo kunye neziphakamiso

Uphuhliso database lwezibonelelo Internet ayahlukanga ukusuka zolwazi umgangatho ophuhliswe kwinkqubo ka- MS SQL SERVER. Ngokuqhelekileyo, kuba olu hlobo resource sebenzisa ulwimi MY SQL, nangona linokusetyenziswa uphuhliso ziziveliso ze-software eqhelekileyo ukusetyenziswa zasekuhlaleni. Kodwa ke umbuzo kweli nqaku musa ukuya ngayo.

xa amaninzi ukusebenza oovimba ngalinye iilwimi umsebenzi ukwenza idata isampuli luvela iintlobo ezahlukeneyo zeengxelo, iigrafu kunye njalo njalo. Njengoko umthetho, ukuphunyezwa kwale hlobo iingxaki kufuneka usebenzise omnye kodwa eziliqela iitafile, edibanisa libe isicelo esisodwa, nzima kakhulu isakhiwo sayo. Kuyimfuneko ukuba ukuthatha ingqalelo, njengoko kufuneka data output njengoko uya 'zokuvala izikhewu' itafile, yaye yintoni umphumo iya kuba elamkelekileyo kakhulu uhlelo. Ukusombulula ezi ngxaki, sebenzisa enye yezi lwimi standard kuyilo I SQL yam - Joyina.

Ingcamango amagama Joyina

Iilwimi uphuhliso database, akunamsebenzi nokuba yintoni uhlobo lolwimi, ukuthatha njengesiseko amazwi umgangatho izichazi IsiNgesi (leyo kutheni imeko ozaziyo IsiNgesi, wena bude lula ukusebenza ngee tafile). Ukuphumeza unxulumano etafileni isampuli ethatyathiweyo ilizwi elifanayo - Joyina. Ngolwimi lwe software sedata esetyenziswa SQL yam. Translation of inkonzo ilizwi ncam ngolwimi - "umbutho".

Ukutolikwa I SQL yam isakhiwo - Joyina, nayiphi na enye yazo iya kuba kanye enye. Ukuba izakhi injongo yokwakha, ezizezi umphandle umsebenzi wakhe, sifumana izinto ezilandelayo ezixabisekileyo: uyilo izakuvumela ukuqokelela amasimi efunekayo izintlu ezahlukeneyo, okanye imibuzo ekwiseli kwisampula enye.

Iintlobo lwezoyilo uhlangane

Ukuba umdwelisi nkqubo kufuneka uqokelele isampuli ngokweetafile ezininzi, yaye uyazi ukuba amasimi ephambili kubo yaye yintoni uhlobo lwe data ezifunekayo ingxelo, ungasebenzisa enye izakhiwo ezisisiseko yombutho, ukuze kufikelelwe isiphumo ezinqwenelekayo. izakhiwo ezisisiseko (ukudibanisa iitheyibhile) zine:

1. Inner ukujoyina.
2. Cross Joyina.
3. Iqhosha.
4. Ekunene Joyina.

Kuxhomekeka umsebenzi, ngamnye zoyilo ezisemgangathweni iya kunika iziphumo ezahlukeneyo, nto leyo eza kufumana iingxelo ezahlukeneyo iparameters ngexeshana elifutshane.

Ukuyila kwaziswa iitafile kusebenziso lasemva

Phambi kokuba uqale, umzekelo, ukuba siqwalasele iindlela ukusebenza uyakha data fusion kufuneka ukulungiselela izintlu eziliqela, ngalo siza kuqhubeka nokusebenza. Oku kuya kunceda ukubonisa siseko bonke abasebenzi, ukongeza, ukuze wabaqalayo baya kufunda zonke ezingundoqo iitafile lula kweenkqubo.

Le theyibhile yokuqala iya ukuchaza ezinye izinto ngalo abantu nazo njalo ubomi bakhe bonke.

Le theyibhile yesibini iya ukuchaza ezinye iimpawu zezinto etafileni yokuqala ukuvumela umsebenzi phambili kunye nabo.

Ngokubanzi, yayingamacwecwe amabini iya kuba yanele ukubonisa umzekelo umsebenzi wabo. Ngoku ungakwazi uqhubeke ukuqwalaselwa bobuqili yoyilo zethu.

Ukusebenzisa Inner Joyina

Ukuba usebenzisa isakhiwo I SQL - Joyina Ineer ekufaneleke ukuba siyicinge ezinye iimpawu zayo. Le kuyilo ikuvumela ukuba ukhethe kuzo zombini iitafile kuphela ezo rekhodi ukuba kule yokuqala kwitheyibhile yesibini. Zisebenza njani? Le theyibhile yokuqala sinazo inkosi iqhosha - ID, nto leyo ibonisa inani lothotho inani iirekhodi kule theyibhile.

Xa uyila itafile yesibini, isitshixo efanayo isetyenziswa njenge inani ngokulandelelana, umzekelo kunokubonwa la manani. Xa ukhetha i data Khetha ingxelo iza kuqinisekisa isiphumo ubhala kuphela amanani serial ungqamane - yoko, bona yokuqala kwitheyibhile yesibini.

Xa ulwakhiwo kuyimfuneko ukuze uqonde ukuba yintoni na ekufuneka data. Impazamo eqhelekileyo, ingakumbi ngenxa abaphuhlisi sedata, oko ukusetyenziswa ngqiqweni yaye akufanelekanga loyilo Inner Joyina. Ngokomzekelo, I SQL yam yangaphakathi Joyina, uyakwazi ukuqwalasela umbhalo eza kubuyela ukuba i ngaphambili echazwe zigcwaliswe kunye neetafile yezinto ulwazi kunye neempawu zazo. Kodwa ke kungakho iindlela ezininzi ukusebenzisa kuyilo. Kulo mba SQL yam ulwimi bhetyebhetye kakhulu. Ngoko ke, uyakwazi ukubona imizekelo usebenzisa I SQL yam yangaphakathi Joyina.

Kunye iitafile ngaphandle kokucacisa naziphi i parameters. Kulo mzekelo, sifumana iziphumo sicwangciso:

Ukuba abonakalise Isisombululo elithi: Ukusebenzisa inkonzo, kuyimfuneko ukuba siqwalasele izitshixo eziphambili iirekhodi kwiitheyibhile, i iseti japan ziya kutshintsha kakhulu. Kulo mzekelo, sifumana intshayelelo, nto leyo eza kubuyela kuphela ezo imiqolo ukuba abe inkosi izitshixo efanayo.

Amathuba okuba ngobuwena sesithathu ukusetyenziswa, xa ucelwa ngelizwi «kwi» ibonisa amasimi zimiselwe ukubakho iitafile wokujoyina. Kulo mzekelo, isampuli uya kubuya le ngcaciso ilandelayo:

Iimpawu Sebenzisa Ekhohlo Dibanisa

Xa siqwalasela enye indlela yokudibanisa iitafile usebenzisa isakhiwo I SQL yam - Joyina, omnye unokujonga umahluko data, ezithi ofakwe. Loo ndlela usele design.

Ukusebenzisa isakhiwo Ekhohlo Dibanisa I SQL yam kwezinye izinto kunye, njengokuba Inner, ifuna ukuqonda ngokucacileyo iziphumo ekufuneka ifunyanwe.

Kulo mzekelo, kuqala kukhetha konke iingxelo etafileni yokuqala, kunye noko ngakumbi kuqhotyoshelwe kwi iipropati itafile yesibini. Kulo mzekelo, xa kukho irekhodi, umzekelo, isitshixo i "isitulo" kolu luhlu lokuqala kunye netafile yesibini Akukho propati kuyo, ibonisa umsebenzisi Ngasekhohlo phambi yokungena kwi null, leyo uxelela wenkqubo ukuba iimpawu ngalo mbandela lingavakali .

Ukusebenzisa le uyilo kuya kukunceda ukuba zeziphi Fields okanye, umzekelo, iimpahla evenkileni lingakulungele ixabiso, ixesha iwaranti , njalo-njalo.

Sebenzisa Umzekelo Ekhohlo

Ukuze kuqwalaselwa ukuba uyilo yokusebenza yomsebenzisi Ekhohlo Dibanisa I SQL yam usebenzisa itafile ochazwe ngaphambili. Umzekelo, kufuneka ukhethe uluhlu lonke iimveliso ukuba evenkileni, uze ujonge kuba inxenye yabo lungazaliyo iimpawu okanye iipropati. Kulo mzekelo, isampuli uya ukubonisa zonke iimveliso, kwaye abo akukho impahla, uya kukuthwala nulls.

Apho ukusetyenziswa zokwakha Joyina

Ngenxa parameter, umbutho inokuquka ayibonisi kuphela amasimi apho kufuneka wenze itafile uqhagamshelwano, kodwa ziquka umqhubi Apho iimeko.

Ngokomzekelo, cinga nje script ekufuneka ibuyiselwe kuthi kuphela ezo iimbali bangakholwanga phantsi umqondiso. Kulo mzekelo, ngaba ufuna ukongeza uyilo Dibanisa amagama umqhubi yaye zibonisa ukuba kufuneka ibuyiselwe ngenxa yoko.

Xa esetyenziswa I SQL yam Dibanisa - Apho kufuneka iqondwe ngokucacileyo ukuba ubonisa kuphela ezo ngxelo leyo iquka imeko ngasentla, kwaye isampuli ziya kuba zezi zilandelayo:

Ezi zicelo zikuvumela ukuba wenze data kwiisampula ethile ephathelele imeko Ndingumqondisi ekhethiweyo. iimeko ezinjalo kubonisa kunokwenzeka eziliqela xa kuhlaziywa parameters ukhetho data simanywe ezitafileni.

Ukusebenzisa Dibanisa ukutshintsha idata kwiitheyibhile

Yila Dibanisa, enyanisweni, jikelele. It ikuvumela ukuba wenze nje ezahlukeneyo iisampuli, kodwa kwakhona ukudibanisa izicelo ukusuka kwenye ezitafileni eziliqela, ukumisela imiqathango eyongezelelweyo kwisampula. Design nayo zisetyenziselwe eminye imisebenzi data. Ngenxa yoko, Joyina ingasetyenziswa ukutshintsha idata kule theyibhile. Okanye kunoko, ukucacisa iimeko itafile, okanye kwiimeko apho kukho imfuneko ukuhlaziya idatha ezitafileni ezininzi iimeko ezifanayo.

Ngokomzekelo, cinga le ngxaki zilandelayo. Njengoko iitafile ezintathu apho kukho ubungqina. Kuyimfuneko ukuba enze utshintsho lwe data zombini theyibhile usebenzisa umbuzo omnye. Nje ukusombulula loo imisebenzi ungasetyenziswa umyalelo Update Joyina design. Joyina kanye uhlobo lwesakhiwo luxhomekeke, ngendlela efanayo xa kwimeko data intshumayelo kwisiphumo ofuna ukufumana wenkqubo.

Cinga umzekelo olula. Kufuneka uhlaziye idatha isicelo esinye kwezi meko efanayo. Olu hlobo zombuzo sakhiwe ukwandisa ledatha. Kutheni ubhale imibuzo ezahlukeneyo ngasinye iitafile, ukuba uchithe yonke idatha ngokulungisa isicelo elinye? UMZEKELO I SQL yam Update Dibanisa kwimeko yethu kungaba:

Ukwakha imibuzo enzima

Lidla xa usebenza database nemibuzo kuyimfuneko ukwakha kuphela ezitshatileyo iitafile eziliqela, kodwa ukusetyenziswa sub-imibuzo. iingxaki ezinjalo kakhulu kunzima ukuqonda ukuba iinkcukacha Ndingumqondisi basandula kuqalisa ukuzisebenzisa. Le ubunzima kukuba kufuneka bacinge ngokusebenzisa nyathelo ngalinye, ngawaphi data ukusuka itheyibhile okanye umbuzo kufuneka ukufumana nendlela kwixesha elizayo kufuneka ukuya emsebenzini.

Ukuze siyiqonde ngakumbi ethile kungathathwa (in I SQL yam Dibanisa) imizekelo imibuzo enzima. Ukuba ezintsha yaye nje ziqala ukusebenza kunye oovimba, olu qeqesho luya kuzuza kuphela. Lwalo elifanelekileyo I SQL yam Ekhohlo uya Dibanisa imizekelo.

Le umbuzo kuya kubuyela ukuba iirekhodi 58 contract kwentengiso, leyo igcwele okanye kukho imali eseleyo imali kuba umhla ekhethiweyo. Kulo mzekelo, umhla wangoku. Kwakhona kwisampulu idityaniswe imeko ukuba isivumelwano kufuneka abalinganiswa isihloko - "123". Le ingcaciso-screen (data) ziya kukhethwa - ihlelwa inani yesivumelwano.

Lo mzekelo ulandelayo ubonisa iinkcukacha zazo zonke iintlawulo, nto leyo iya kubonisa inani contract.

ukusebenzisa subqueries

Njengoko kuchaziwe ngaphambili, xa usebenza edatha inokudityaniswa kuphela netafile, kodwa itafile kunye nesicelo. Oku kuyilwa kakhulu kusetyenziswa ukukhawulezisa umbuzo kunye nokwenza kwayo.

Umzekelo, ukuba ufuna etafileni, leyo amakhulukhulu emasimini, bathi, a iirekhodi iwaka, khetha amasimi ezimbini kuphela, kuyimfuneko ukuba kusetyenziswe nombuzo ibuyisela kuphela kwindawo efunekayo, lidibanise ngayo nesampula main data. Ngokomzekelo I SQL yam Dibanisa Khetha uyakwazi ukuqwalasela isicelo hlobo:

Ngoko ke zonke iindlela zokusetyenziswa standard uyilo I SQL yam, nomgangatho kuphela. Indlela yokusebenzisa ekuyileni Dibanisa nangayiphi na indlela, uhlelo ugqiba, kodwa kubalulekile ukukhumbula yaye athathele ingqalelo yintoni umphumo kufuneka afunyenwe umbuzo.