SQL kusungidwa njira: Pangani ndi kugwiritsa

SQL kusungidwa njira ndi executable mapulogalamu gawo kuti akhoza kusungidwa mu Nawonso achichepere a mu mawonekedwe a zinthu zosiyanasiyana. M'mawu ena, ndi chinthu chimene chili ndi SQL-mawu. ndondomeko izi kusungidwa akhoza anaphedwa kasitomala ntchito kupeza ntchito zabwino. Komanso, maofesi amenewa nthawi zambiri amatchedwa kwa zolembedwa zina kapena ngakhale gawo lina lirilonse.

Malonje

Anthu ambiri amakhulupirira kuti ali ofanana ndi ndondomeko ya zosiyanasiyana zilankhulo mapulogalamu waudindo (motero, kupatula MS SQL). Mwina izi ndi zoona. Iwo ndi magawo ofanana, iwo akhoza apereke mtengo wofanana. Komanso, nthawi zina, iwo amakhudzana. Mwachitsanzo, iwo ali pamodzi zinasokoneza makompyuta a DDL ndi deta DML, komanso ntchito wosuta (malamulo dzina - UDF).

SQL njira kwenikweni kusungidwa ndi osiyanasiyana ubwino kuti kusiyanitsa pakati njira zimenezi. Safety, masiyanidwe mapulogalamu zokolola - zonse izi amakopeka ogwiritsa ntchito ndi zinasokoneza makompyuta, anthu ambiri. Pachimake pa kutchuka anabwera ndondomeko zaka 2005-2010, pamene ine ndiri ndi pulogalamu pa "Microsoft" pansi pa dzina «SQL Server Management situdiyo». Mothandizidwa ndi mzimuwo ntchito ndi zinasokoneza makompyuta wakhala zambiri mosavuta, bwino ndi yabwino kwambiri. Chaka ndi chaka, ichi njira akupereka mauthenga zinatchuka mu chilengedwe mapulogalamu. Lero, MS SQL Server ndi chizolowezi mwamtheradi pulogalamu, amene owerenga kuti "kulankhula" ndi zinasokoneza makompyuta, anaima pa ndime ndi "kupambana".

Pamene mukundiitana ndondomeko, izo yomweyo kukonzedwa ndi makina popanda njira zosafunika ndi phukusi wosuta. Ndiye mukhoza kuchita kanthu ndi zomwe: kufufutidwa, kunyongedwa, kusintha. Pa zonsezi ndi mu DDL-woyendetsa, amene osakwatiwa-handedly kuchita zochita zovuta pa zinthu zimenezo. Ndipo izo zonse zimachitika kwambiri mwamsanga, ndi makina Kodi kwenikweni yodzaza. liwiro Izi ndi zokolola tiyeni inu mwamsanga kusamutsa yambiri mfundo wosuta ku Seva ndi mosemphanitsa.

Kugwiritsa ntchito iyi ndi nkhani, pali njira zamakono angapo mapulogalamu zinenero. Zikuphatikizapo Mwachitsanzo, PL / SQL ku kasamalidwe dongosolo Nawonso achichepere Oracle, PSQL mu InterBase ndi machitidwe Firebird, komanso tingachipeze powerenga "maykrosoftovskih» transact-SQL. Onse a iwo apangidwa kulenga ndi kuthamanga ndondomeko kusungidwa, kulola kugwiritsa ntchito ma aligorivimu awo pa handlers lalikulu Nawonso achichepere. M'pofunika ndi kuonetsetsa kuti anthu amene amachita kasamalidwe ka mfundo zimenezi, akhoza kuteteza zinthu zonse ku maphwando wosaloleka lachitatu, choncho chilengedwe, kusinthidwa kapena kufufutidwa deta zina.

zokolola

zinthu Nawonso achichepere izi zikhoza ma m'njira zosiyanasiyana. Zimenezi zimathandiza owerenga kusankha mtundu wa ndondomeko kuti angadziwire ambiri, amene amapulumutsa nthawi ndi mphamvu. Komanso, ndondomeko yokha kukonzedwa, n'kupeŵa nthawi yaikulu ntchito pa mgwirizano pakati pa makina ndi wosuta. gawo akhoza reprogrammed ndipo anasintha m'njira yoyenera nthawi mwamtheradi iliyonse. Makamaka Dziwani liwiro limene za kukhazikitsidwa kwa ndondomeko SQL kusungidwa kumachitika: ndondomeko ndi mofulumira ena yofanana ndi iyo, kulipangitsa kukhala yabwino ndi mosalekeza.

chitetezo

Mtundu wa processing mudziwe isiyana ndi njira zofanana kuti lili chitetezo kuzikonzanso. Izi n'kotsimikizika chifukwa chomwe anthu ena ndondomeko kupeza angathe kuthetsedwa kwathunthu. Izi adzalola Woyang'anira kuchita ntchito ndi iwo paokha, popanda kuopa kudutsana kapena mwayi wosaloleka kuti Nawonso achichepere.

ukuthamana

Kulankhulana pakati pa SQL ndondomeko kusungidwa ndi ntchito kasitomala kugwiritsa ntchito magawo ndi kubwerera mfundo. Yotsirizira Sikuti popereka deta ndi ndondomeko kusungidwa, koma mfundo (makamaka pa pempho malangizo), ndi kukonzedwa kwa SQL. Kamodzi kusungidwa ndondomeko wamaliza ntchito yake, izo amatumiza mapaketi deta kumbuyo (koma, kachiwiri, ngati n'koyenera) kugwiritsa ntchito kuitana ntchito njira zosiyanasiyana imene akhoza akuyendera ngati kuitana kwa SQL ndondomeko kusungidwa ndi kubwerera Mwachitsanzo:

- deta kufala kudzera mtundu linanena bungwe chizindikiro;

- deta kufala kudzera mawu kubwerera;

- Data kufala kudzera chonyamulira kusankha.

Ndipo tsopano momwe ndondomeko iyi ikuoneka mkati yemweyo.

1. Pangani EXEC-kusungidwa mu ndondomeko SQL

Mutha kupanga ndondomeko mu MS SQL (Managment situdiyo). Pambuyo ndondomeko analenga, kuti kutchulidwa pa programmable mfundo Nawonso achichepere, imene ndondomeko imagwiridwa ndi woyendetsa za chilengedwe. Kudzapereka SQL kusungidwa njira ntchito EXEC kuyenera kuti muli chinthu dzina.

Polenga dzina abwera ndondomeko yoyamba, ndipo kenako anapanga magawo chimodzi kapena zingapo zimene anamuuza. Magawo akhoza kukhala okhulupirika. Pambuyo chizindikiro (s), mwachitsanzo thupi la ndondomeko, kulembedwa, m'pofunika kuchita ntchito zina zofunika.

chakuti thupi ndi zosintha m'deralo, ili mmenemo, ndi zosintha zimenezi zili m'dera ndi poyerekezera ndi odwala. M'mawu ena, iwo tinganene kuti yekha mu thupi mankhwala Microsoft SQL yawonongeka. njira kusungidwa mu nkhani iyi amaonedwa m'deralo.

Choncho, kulenga ndondomeko, tiyenera dzina ndondomeko ndi chizindikiro chimodzi monga ndondomeko thupi. Chonde dziwani kuti njira yabwino Pankhaniyi ndi chilengedwe ndi kukhazikitsa ndondomeko ndi chithunzithunzi dzina classifier lapansi.

Ndondomeko za thupi ndi mtundu uliwonse wa ntchito SQL Mwachitsanzo, monga kukhazikitsa tebulo, kulowetsa mizere chimodzi kapena zingapo pa tebulo, anakhazikitsa mtundu Nawonso achichepere ndi khalidwe, ndi zina zotero. Komabe, ndondomeko thupi kukuchepetsa ntchito zina mwa izo. Ena zofooka zofunika zili m'munsimu:

- thupi si kulenga ina kusungidwa ndondomeko;

- thupi sayenera kulenga maganizo olakwika za chinthu;

- thupi si kulenga akapsa iliyonse.

2. Sankhani variable mu thupi la ndondomeko

Mukhoza kupanga zosintha m'dera ndondomeko ya thupi, ndipo adzakhala mwa ndondomeko thupi. Mwambo wabwino ndi kulenga variable pa chiyambi cha ndondomeko thupi kusungidwa. Koma inu akhoza ku- zosintha paliponse mu thupi la chinthu.

Nthawi zina mukhoza kuona kuti ena zosintha ali mu mzere womwewo, ndipo aliyense variable ankasiyana koma ndi. Komanso dziwani kuti variable ndi prefixed ndi @. Mu thupi la ndondomeko, mukhoza anapereka variable imene mukufuna. Mwachitsanzo, variable @ NAME1 akhoza analengeza kutha kwa ndondomeko thupi. Kuti musankhe mtengo kwa variable ndi ananena ntchito ya munthu deta. Mosiyana ndi zimene zinachitika pamene variable kuposa wina ananena mu mzere womwewo, kabudula mmodzi yekha deta ntchito imeneyi.

Ogwiritsa amakonda kufunsa funso ili: "Kodi imene mfundo angapo mawu imodzi mu thupi la ndondomeko 'Chabwino. Chidwi funso, koma ichi mosavuta kuposa momwe inu mukuganizira. Yankho: mothandizidwa ndi awiriawiri monga «Select var = phindu". Mukhoza kugwiritsa ntchito awiriawiri awa, ankasiyana mipatuliro.

3. Pangani SQL kusungidwa ndondomeko

Zosiyanasiyana zitsanzo za anthu amasonyeza kupanga kusungidwa ndondomeko yosavuta motsatira izo. Komabe, njira amatenga magawo ngati kuti ndondomeko maitanidwe izo ndi mtengo pafupi ndi iye (osati nthawi zonse). Ngati iwo zikugwirizana, ndiyeno nkuyamba njira lolingana mwa thupi. Mwachitsanzo, ngati munakonza njira kuti adzatenga mzinda ndi M'dera woyimba ndi kubwerera deta mmene olemba amatchula mzinda ofanana ndi dera. Ndondomeko adzakhala kafufuzidwe Nawonso achichepere tebulo la olemba Mwachitsanzo, Pubs, pochita izi mawerengedwe olemba. Kuti mupeze zinasokoneza makompyuta awa, mwachitsanzo, Google Downloads ndi SQL script ndi tsamba SQL2005.

Mu Mwachitsanzo yapita, ndondomeko amatenga magawo awiri, amene English adzatchedwa salembedwa @State ndi @City. Mtundu deta limafanana ndi mtundu kutchulidwa ntchito. Ndondomeko thupi zosintha mkati @TotalAuthors (olemba zonse), ndipo variable ichi ntchito kusonyeza chiwerengero chawo. Kenako gawo kusankha pempho kuti onse udindo. Pomaliza, phindu masamu chimaonetsedwa zenera linanena bungwe ntchito mawu kulisindikiza.

Kodi kuchita SQL kusungidwa ndondomeko

Pali njira ziwiri kuchita ndondomeko. Njira yoyamba popewa kudutsa magawo monga mndandanda koma anasiyana zachitika dzina ndondomeko. Tiyerekeze tili mfundo ziwiri (monga chitsanzo yapita). makhalidwe awa anasonkhana ntchito zosintha ndi @State @City ndondomeko. Mu njira imeneyi, magawo yofunika kwambiri kuti kufala. njira imeneyi imatchedwa kufala ndondomeko ya mfundo. Mu njira yachiwiri, magawo kale anatumizidwa mwachindunji, zimene zinachitikira kuti si zofunika. Njira yachiwiri amadziwika monga kufala kwa mfundo dzina lake.

Ndondomeko mwina pang'ono kupatuka pa mmene. Mulimonsemo, monga chitsanzo m'mbuyomu, koma magawo ali ankatembenukira kuno. Kuti ndi @City chizindikiro awasungira woyamba, ndipo @State kusungidwa pafupi ndi mtengo ofikira. Kuika kusakhulupirika nthawi allocated payokha. SQL kusungidwa njira ndi magawo zazing'ono. Pankhaniyi, anapereka njira "pa UT 'm'malo mtengo kusakhulupirika kwa" CA ". Mu chimake chachiwiri akudutsa chimodzi chokha mkangano kufunika kwa @City, ndi @State njira defaults kuti "CA". mapulogalamu odziwa akulangizidwa kuti onse kusakhulupirika zosintha zili pafupi ku mapeto a mndandanda chizindikiro. Apo ayi, aphedwe sizotheka, ndiye inu muyenera kugwira ntchito ndi kutengerapo zifukwa wotchedwa kuti yaitali yovuta.

4. Kusunga Opaleshoni SQL Server: njira yobwerera

Pali njira atatu akulu kutumiza deta kusungidwa mu ndondomeko otchedwa. Iwo ali m'munsiwa:

- kubwerera mfundo kusungidwa ndondomeko;

- linanena bungwe chizindikiro kusungidwa njira;

- Sankhani chimodzi mwa njira kusungidwa.

4.1 Bwererani mfundo za kayendetsedwe SQL kusungidwa

Mu njirayi, ndondomeko amapereka kufunika kwa variable m'dera ndi kubwerera izo. Ndondomeko akhoza mwachindunji kubwerera mtengo nthawi zonse. Mu zotsatirazi, ife tapanga njira umene umabwerera chiwerengero cha othandizira. Ngati ife kufanizira izi ndi ndondomeko yapita, mukhoza kuona kuti phindu kusindikizidwa m'malo ndi kuthetsa.

Tsopano tiyeni tione mmene angachitire ndondomeko ndi kusonyeza phindu, Bwezani. Kuchita njira chofunika kukhazikitsa ndi variable yosindikiza, yomwe ikuchitika pambuyo ndondomekoyi. Onani kuti m'malo asindikiza woyendetsa angagwiritse ntchito Select-woyendetsa Mwachitsanzo, Select @RetValue, ndi OutputValue.

4.2 linanena bungwe chizindikiro SQL kusungidwa njira

mtengo Kuyankha angagwiritsidwe ntchito ndikubweza variable umodzi kuti taonera chitsanzo m'mbuyomu. Ntchito linanena bungwe amalola ndondomeko kutumiza zosintha chimodzi kapena zingapo chipani akuitana. Linanena bungwe chizindikiro ndiwo amaimira nthawi mawu fungulo «linanena bungwe» polenga ndondomeko. Ngati chizindikiro ndi chinaneneratu ngati chizindikiro linanena bungwe, ndondomeko chinthu azigawira izo mtengo. njira kusungidwa SQL zitsanzo za zimene Tingaone pansipa, mu nkhani yoteroyo kubwerera ku nkhani yomaliza.

Muchisanzo chimenechi, kudzakhala azisangalalo awiri dzina: @TotalAuthors ndi @TotalNoContract. Iwo kutchulidwa mndandanda chizindikiro. zosintha ameneŵa amapatsidwa makhalidwe mwa ndondomeko thupi. Pamene tigwiritsa ntchito magawo linanena bungwe, ndi woyimba akhoza kuona phindu mu ndondomeko thupi.

Komanso, mu nkhani yapita, zosintha ziwiri amayesedwa kuona makhalidwe amene anaika njira kusungidwa, MS SQL Server ngati chizindikiro linanena bungwe. Ndiye ndondomeko imagwiridwa ndi kutsatira mtengo zachilendo «CA» chizindikiro. Magawo zotsatirazi linanena bungwe, choncho ananena zosintha zonse zapita m'njira akuchipatala. Chonde dziwani kuti pamene variable linanena bungwe nfundo yaikhulu chinafotokozanso pano. Pambuyo ndondomeko udzatha bwinobwino, phindu anabwerera pogwiritsa ntchito magawo linanena bungwe amalembedwa uthenga zenera.

4.3 Posankha SQL kusungidwa njira

Njira imeneyi ntchito kubwerera akonzedwa mu mawonekedwe tebulo deta mfundo (RecordSet) poyimba ndondomeko kusungidwa. Mu chitsanzo ichi SQL kusungidwa ndondomeko ndi magawo @AuthID akupempha tebulo "olemba" imathandiza mbiri anabwerera ndi izi @AuthId chizindikiro. Select woyendetsa amasankha zimene ayenera anabwerera ku kuitana ndondomeko kusungidwa. Pamene njira kusungidwa AuthId opatsirana kumbuyo. Chotero ndondomeko ndi nthawi akubwerera umboni umodzi basi palibe konse. Komabe, ndondomeko kusungidwa alibe malamulo alionse pa adzabwerenso kulowa kuposa wina. Nthawi zambiri zitsanzo angapezeke amene kubwerera deta anasankha ntchito magawo ndi zosintha masamu anachita ndi kupereka kuchuluka kwa mfundo okwana.

pomaliza

A ndondomeko kusungidwa ndi pulogalamu wokongola kwambiri, anabwerera kapena anasamutsidwa, komanso kukhazikitsa zosintha zofunika chifukwa ntchito ndi kasitomala. Popeza ndondomeko kusungidwa ndi kumupha pa makina yokha, deta kuwombola m'mavoliyumu lalikulu pakati pa makina ndi kasitomala ntchito (kwa kuwerengetsera ena) zikhoza kupewedwa. Izi zimathandiza kuchepetsa katundu pa SQL Seva, amene, ndithudi, ndi dzanja la mbale wawo. Mmodzi wa subspecies ndi kusungidwa njira T SQL, koma phunziro awo pakufunika anthu amene amachita chilengedwe cha Nawonso achichepere chidwi. Palinso lalikulu, ngakhale yaikulu kuchuluka kwa mfundo zina zabwino zimene zingakhale zothandiza mu kuphunzira njira zogwirira kusungidwa Komabe, kufunika kwa aliyense amene mukufuna kuchita mapulogalamu zolimba, kuphatikizapo mwaukadaulo.