SQL ezigcinwe iinkqubo: yokwakha nokusebenzisa

Iinkqubo ze SQL ezigcinwe ezi eliphunyeziweyo imodyuli software ezinokuthi zigcinwe kwiziko ledatha ngokohlobo izinto ezahlukeneyo. Ngamanye amazwi, ukuba into iqulethe SQL-mazwi. Ezi nkqubo agcinwe zingaphunyezwa kwi client isicelo ukufumana umsebenzi olungileyo. Ukongeza, loo maziko kudla ngokuthiwa kwamanye amaphepha eempendulo okanye naluphi na kwicandelo.

isingeniso

Abantu abaninzi bakholelwa ukuba ezifanayo neenkqubo ezahlukeneyo iilwimi ekwinqanaba eliphezulu lwenkqubo (ngokulandelelanayo, ngaphandle MS SQL). Mhlawumbi oku kuyinyaniso. Baye efanayo parameters, bangakhupha ixabiso efanayo. Ngaphezu koko, kwezinye iimeko, beza nabo. Umzekelo, baya kuhlanganiswa kunye nezo DDL kunye data DML, kwakunye nemisebenzi yomsebenzisi (igama ikhowudi - UDF).

Iinkqubo SQL Eneneni egcinwe kukho uluhlu olubanzi lwezinto ocinga ukuba ukwahlula phakathi iinkqubo ezinjalo. Safety, Ukwahluka imveliso senkqubo - konke oku itsala abasebenzisi ukusebenza iinkcukacha, ngakumbi nangakumbi. Kwincopho ethandwayo kwafika iinkqubo iminyaka 2005-2010, xa ndithe ndafumana ucwangciso ukusuka 'Microsoft "phantsi kwegama« SQL Management Server Studio ». Ngoncedo lwawo, umsebenzi kunye edatha iye yaba lula kakhulu, practical ngakumbi lula ngakumbi. Unyaka emva konyaka, eli indlela yokudlulisela inkcazelo wazuza kuthandwa kwindawo nodweliso lwenkqubo i. Namhlanje, MS SQL Server inkqubo yesiqhelo ngokupheleleyo, nto leyo ukuba abasebenzisi ukuba "akhulume" kunye iinkcukacha, wema phezu belingana "Excel".

Xa ukubiza inkqubo, oko nangoko kusingathwa ngumncedisi ngaphandle iinkqubo ezingeyomfuneko kunye nongenelelo yomsebenzisi. Unako ke ngoko wenze nayiphi na intshukumo kunye ulwazi: nokususa, abulawe, utshintsho. Phezu konke oku kwi DDL-umqhubi, ngubani single-handedly ukwenza iintshukumo ezintsonkothileyo kwezo zinto. Kwaye yonke into yenzeka ngokukhawuleza kakhulu, kwaye umncedisi ayilungelelani zilayishwe. Le speed kunye nemveliso ake ngokukhawuleza udlulise ulwazi oluninzi ukusuka umsebenzisi kumncedisi and vice versa.

Ukwenza lo msebenzi kunye ulwazi, kukho ezininzi ubugcisa iilwimi kweenkqubo. Ezi ziquka, umzekelo, PL / SQL ukusuka kwinkqubo yolawulo ledatha Oracle, PSQL e InterBase kunye neenkqubo Firebird, kwakunye Classic "maykrosoftovskih» Transact-SQL. Bonke bephela zenzelwe ukuba ukuyila ukubaleka iinkqubo igcinwe, elikuvumela ukuba basebenzise ubuchule babo phezu abaphatha database enkulu. Kuyimfuneko ukuqinisekisa ukuba abo ukuphumeza ulawulo lolwazi enjalo, uyakwazi ukukhusela zonke izinto ukususela lwentengo wesithathu yaye, ke ngoko, indalo, ukulungiswa okanye ukususa i data ezithile.

ukuvelisa

Ezi izinto database inokwenziwa ngeendlela ezahlukeneyo. Oku kuvumela abasebenzisi ukuba ukhethe uhlobo inkqubo eziya kuba zezona zifaneleke, leyo konga ixesha namandla. Ukongeza, le nkqubo ngokwayo luyasetyenzwa, ngaloo ndlela sikuphephe ixesha elikhulu echithwe unxibelelwano phakathi iseva kunye umsebenzisi. Imodyuli unako reprogrammed kwaye itshintshwe kwicala lasekunene kwi ngokupheleleyo naliphi na ixesha. Ingakumbi Kuyaphawuleka isantya apho kwasungulwa inkqubo ze SQL ezigcinwe kwenzeka: le nkqubo ngokukhawuleza nezinye ezifana nayo, eyenza ibe lula yaye ubhetyebhetye.

ukuphepha

Olu hlobo processing ulwazi yohlukile iinkqubo ezifanayo kuba inika ukhuseleko lekhamera. Oku kuqinisekiswa yi kukuba abanye abasebenzisi esuka iinkqubo ekufikeleleni lupheliswe ngokupheleleyo. Oku kuya kwenza uMlawuli ukuba baqhube imisebenzi nabo ngokuzimeleyo, ngaphandle koloyiko iimbalelwano okanye imvume yokungena kwiziko ledatha.

transfer data

Unxibelelwano phakathi inkqubo ze SQL ezigcinwe kwaye isicelo client kukusebenzisa i parameters uze ubuyele amaxabiso. Le yokugqibela akuyomfuneko ukuba adlulisele idatha indlela yokwenza egciniweyo, kodwa ulwazi (ubukhulu becala ngokwesicelo lomsebenzisi), yaye alungiswa ngenxa SQL. indlela Xa agcinwe sele zigqityiwe umsebenzi wayo, nto uthumela iipakethi data emva (kodwa, kwakhona, xa lifuna) ukuba isicelo ebiza kusetyenziswa iindlela ngeendlela ngokokude kusetyenziswa lokutsalela inkqubo ze SQL ezigcinwe babuye, umzekelo:

- transmission data nge Isiphumo Uhlobo parameter;

- transmission data nge kwingxelo lokubuya;

- transmission Data nge nokukhetha yenethiwekhi.

Kwaye ngoku khangela indlela le nkqubo ikhangeleka ngaphakathi efanayo.

1. Yenza Exec-zigcinwa kwi inkqubo SQL

Unako ukudala inkqubo e-MS SQL (Managment Studio). Emva kokuba inkqubo idaliwe, oko idweliswe kwi udumbo ledatha ofakwe, apho inkqubo lwenziwa yi kumsebenzisi kwendalo. Ukuze uphumeze SQL iinkqubo agcinwe ngokusebenzisa Exec-inkqubo equlathe Igama lento.

Xa ekudaleni igama iza inkqubo kuqala, uze emva koko wenza parameters enye okanye ngaphezulu ukuba abelwe kuye. Parameters ingaba yokuzikhethela. Emva kokuba parameter (s), okt umzimba inkqubo, ukuba kubhaliwe kwathiwa, kuyimfuneko ukwenza eminye imisebenzi kuyimfuneko.

Into yokuba umzimba sinokuba izinto zasekuhlaleni, ibekwe kuyo, yaye ezi izinto ezi basekuhlaleni ngokunxulumene neenkqubo. Ngamanye amazwi, ukuba kuqwalaselwa kuphela ngaphakathi unyango umzimba SQL Server Microsoft. iinkqubo agcinwe kulo mzekelo kuthathwa zasekuhlaleni.

Ngenxa yoko, ukudala inkqubo, kufuneka sibe negama inkqubo kunye parameter ubuncinane omnye njengequmrhu inkqubo. Nceda qaphela ukuba ukhetho kakhulu kule meko kukudala kunye nokuphunyezwa kweenkqubo kunye negama schema somhleli.

Iqumrhu nkqubo nalo naluphi na uhlobo kwabaqhubi SQL, umzekelo, njengokwenza itafile, ufaka omnye okanye ngaphezulu etafileni, ekusekeni uhlobo ledatha nangesimilo, njalo-njalo. Noko ke, umzimba inkqubo evumela eminye imisebenzi kulo. Ezinye umda zibalulekileyo adweliswe apha ngezantsi:

- umzimba ukudala nayiphi na enye indlela yokwenza egciniweyo;

- umzimba kufuneka Ayinakwenza indlela yokucinga malunga into;

- umzimba, azivuleli kusungula.

2. Misela variable emzimbeni inkqubo

Ungenza izinto bengingqi inkqubo umzimba, baze emva koko baya kuba phakathi umzimba inkqubo. A yokusebenza olungileyo ukwenza umahluko ekuqaleni umzimba indlela yokwenza egciniweyo. Kodwa Ungacwangcisa kwakhona variables naphi na emzimbeni kwento.

Maxa wambi uqaphele ukuba ezinye variables zicwangciswa kumqolo omnye, kunye neguquguqukayo ngalinye ezahlulwe sisithuba isiphumlisi. Kananjalo qaphela ukuba le variable ziqaliswe @. Xa umzimba nkqubo, uyakwazi ukucwangcisa kwi ofuna. Umzekelo, variable @ NAME1 unako kwaziswa ekuyeni ekupheleni umzimba inkqubo. Ukuze ukuba zinyule ixabiso variable wathi usebenzisa iseti data zobuqu. Mu kuya kwa nshita imeko xa ngaphezu kwesinye-bume yaxela kumqolo omnye, omnye kuphela uluhlu lwe data zobuqu esetyenzisiweyo kule meko.

Abasebenzisi rhoqo ukubuza umbuzo othi: "Indlela abele amaxabiso ezininzi kwingxelo omnye kumzimba inkqubo 'Well?. umbuzo umdla, kodwa oku kulula kakhulu kunokuba ucinga. Impendulo: ngoncedo ngababini ezifana «Khetha var = value '. Ungasebenzisa ezi ngababini, wodwa ngeziphumlisi.

3. Yenza indlela SQL ezigcinwe

Xa iintlobo ngeentlobo imizekelo yabantu ibonisa ukudala inkqubo elula zigcinwe kwaye aphumeze oko. Nangona kunjalo, le nkqubo ukuze athathe loo parameters ukuba inkqubo ubizo iya kuba nexabiso kusondela kuyo (kodwa hayi njalo). Ukuba ziyavumelana, ngoko iqala iinkqubo ehambelana ngaphakathi emzimbeni. Umzekelo, ukuba ukudala inkqubo eza ndiwuthimbe lo mzi, kummandla evela umfowuneli uze ubuyele idatha malunga indlela ababhali zibhekisela isixeko ehambisana kunye mmandla. Le nkqubo iya buza uvimba itafile ababhali, umzekelo, pubs, kuba ngokwenza oku ukubala ababhali. Ukuze ufumane ezi iinkcukacha, umzekelo, i-Google ukulandwa elishicilelwe SQL kunye nephepha SQL2005.

Kulo mzekelo elidlulileyo, inkqubo kuthatha parameters ezimbini, nto leyo IsiNgesi wayeza kubizwa ngokuba kwemiqathango @State kunye @City. Uhlobo lwedatha ekwafanekisa uhlobo echazwe kwi sicelo. Umzimba nqubo variables yangaphakathi @TotalAuthors (zonke ababhali), kunye nale variable esetyenziselwa ukubonisa inani labo. Okulandelayo kuza isicelo yokukhetha icandelo zonke izibalo. Ekugqibeleni, ixabiso ebalwa iyavezwa kwi window output usebenzisa ingxelo yoshicilelo.

Indlela ukwenza inkqubo ze SQL ezigcinwe

Kukho iindlela ezimbini ukwenza le nkqubo. Indlela yokuqala iboniswe ngokugqithisela parameters noluhlu comma-ezahlulwe kwenziwa emva igama inkqubo. Masithi kufuneka amaxabiso amabini (njengoko umzekelo ophelileyo). Ezi nqobo zisemgangathweni uqokelele kusetyenziswa izinto kunye nenkqubo @State @City. Kule ndlela, kubalulekile Ipharamitha umyalelo ugqithiso. Le ndlela kuthiwa ukulandelelana ukudluliselwa i lwezimvo. Xa indlela yesibini, i parameters sele abelwe ngqo, apho umyalelo ayibalulekanga. Le ndlela yesibini yaziwa ngokuba ekudluliseni lokuphikisana igama.

Lomgaqo zitenxe kancinane boqobo. Noko kunjalo, njengoko bekunjalo kumzekelo wokuqala, kodwa i parameters kwatshintsha apha kuphela. Oko kukuthi @City parameter igcinwa kuqala, yaye @State agcinwe ecaleni ixabiso elisisiseko. Ucwangciso olungagqibekanga sidla zabelwe ngokwahlukeneyo. SQL iinkqubo egcinwe yile ezilula parameters. Kulo mzekelo, ngaphandle kokuba ukhetho "i UT 'ithatha ixabiso lokungagqibeki lika" CA'. Xa ngobuwena yesibini udlula ixabiso kuphela elinye yingxoxo @City, kunye sokungagqibeki ukhetho @State ukuba "CA '. badwelisi benkqubo abanamava bacetyiswa ukuba zonke izinto engagqibekanga zibekwe kufutshane ekupheleni koluhlu parameter. Kungenjalo, uphumezo akwenzeki, ngoko ke kufuneka sisebenze kunye nokudluliselwa igama iimpikiswano ukuba elide ngakumbi ezintsonkothileyo.

4. Iinkqubo egcinwe Kumncedisi we SQL: iindlela yokubuyisela

Kukho iindlela ezintathu ezinkulu ukuthumela idatha egcinwe kwinkqubo ebizwa. zidweliswe apha ngezantsi:

- ukubuyela amaxabiso indlela yokwenza egciniweyo;

- Isiphumo parameter iinkqubo agcinwe;

- Khetha enye iinkqubo egciniweyo.

4.1 Amaxabiso Return kweenkqubo ze SQL ezigcinwe

Kule nkqubo, inkqubo icwangcisa ixabiso variable zasekuhlaleni kwaye ibuyisela kuyo. Le nkqubo kwakhona ukubuyela ngqo ixabiso rhoqo. Kule mizekelo ilandelayo, sidale inkqubo ubuyisela inani elipheleleyo abaxhasi. Ukuba uthelekisa lo kunye nenkqubo yangaphambili, ungabona ukuba ixabiso yoshicilelo ithathwa yi-reverse.

Ngoku makhe sikhangele ukuba ukwenza njani inkqubo ze ukubonisa ixabiso, ayibuyisele. iinkqubo ezifunekayo ukuseka ushicilelo bume, oluqhutywa emva kwenkqubo. Qaphela ukuba endaweni zokushicilelwa umqhubi uyakwazi ukusebenzisa Khetha-umqhubi, umzekelo, Khetha @RetValue, kunye OutputValue.

4.2 Iinkqubo Isiphumo Ipharamitha SQL ezigcinwe

Ixabiso impendulo zingasetyenziswa ukuba babuyele umahluko esinye siye sabona kumzekelo odlulileyo. Usebenzisa Isiphumo ikuvumela inkqubo ukuthumela variables enye okanye ngaphezulu ukuya iqela ubiza. Isiphumo parameter luchazwa eli lizwi iqhosha «output» njengokuba ixesha xa kudalwa inkqubo. Ukuba eyesibini iparameter ayisebenzi umsebenzi njengoko imveliso i parameter, le into inkqubo kufuneka babele ixabiso. Agciniweyo iinkqubo SQL, imizekelo apho unako kungabonwa ngezantsi, kwityala enjalo babuyele ulwazi lokugqibela.

Kulo mzekelo, apho kuya kubakho igama veki ezimbini: @TotalAuthors kunye @TotalNoContract. Bona kucacisiwe kuluhlu parameter. Ezi izinto babelwa amaxabiso ngaphakathi emzimbeni inkqubo. Xa sisebenzisa parameters imveliso, wocingo uyakwazi ukubona ixabiso elibekwe emzimbeni inkqubo.

Ukongeza, kule imeko yangaphambili, yezahlukileyo ezimbini owamiswayo ukuba ukubona amaxabiso ukuba zifakelwe iinkqubo zigcinwa, Umncedisi MS SQL njengoko imveliso ye parameter. Ngoko ke, lenquba yenzelwa ngokusebenzisa ixabiso eliqhelekileyo «CA» parameter. Imida ezilandelayo output yaye, ke ngoko, wathi lugqithiselwa ezahlukeneyo ngendlela emiselweyo. Nceda uqaphele ukuba xa imveliso elingundoqo variable kwakhona zicaciswe apha. Emva kokuba le nkqubo yagqitywa ngempumelelo, ixabiso babuya ngokusebenzisa i parameters imveliso eboniswe kwi window umyalezo.

4.3 Ukukhetha iinkqubo ze SQL ezigcinwe

Le ndlela isetyenziswa ukubuyisela isethi ngohlobo itafile amaxabiso data (yeerekhodi) yokubiza indlela yokwenza egciniweyo. Kulo mzekelo, ze SQL ezigcinwe inkqubo kunye parameters @AuthID ocela itafile "ababhali" okufika iirekhodi babuya ngulo @AuthId parameter. Khetha umqhubi iyagqiba ukuba loluphi kufuneka zibuyiselwe ukubiza inkqubo egciniweyo. Xa indlela yokwenza egciniweyo AuthId yadluliselwa umva. Loo nkqubo kusoloko ibuyisela irekhodi kuphela omnye okanye namnye konke konke. Noko ke, indlela yokwenza egciniweyo akanalo nelinye izithintelo ukubuya yokungena ngaphezu kwesinye. Kaninzi imizekelo inokufumaneka apho data ukubuyela ekhethiweyo usebenzisa parameters kunye yezahlukileyo ebalwe kwenziwa ngokubonelela ezininzi amaxabiso ewonke.

Ekuphetheni

A indlela yokwenza egciniweyo yinkqubo entle kakhulu, wabuya okanye kudluliswe, ngokunjalo ekumiseleni izinto eziyimfuneko ngenxa isicelo client. Ekubeni indlela yokwenza egciniweyo esiqhutywa kumncedisi ngokwayo, exchange data imithamo ezinkulu phakathi iseva kunye isicelo ngumxhasi (kuba ezinye calculations) uya kuziphepha. Oku kwenza kube lula ukuba kuncitshiswe umthwalo kumncedisi SQL, leyo, Kakade ke, lo esandleni abanini zabo. Enye ohlobo zigcinwa iinkqubo T SQL, kodwa ukufunda kwabo ukuze abo bathatha inxaxheba ekudalweni ledatha umxhelo. Kukho kwakhona elikhulu, nokuba isixa esikhulu kujonge izinto ezinokuba luncedo kwi sifundo iinkqubo igcinwe, Noko ke, le mfuneko abo uceba ukwenza iinkqubo tight, kuquka umsebenzi.