SQL-imibuzo umyalo

SQL - omunye okuvame kakhulu izilimi izinhlelo ukwenza nokuphatha database, kanye izenzo ezihlukahlukene ngedatha uqobo.

Njengoba umkhuba ubonisa, kulula kakhulu ukusebenzisa futhi kwenza iningi silulumagama esezingeni olimini lwesiNgisi livela kwelesiLatini. Njenganoma iyiphi enye ulimi lokwakha izinhlelo, SQL has ukuhlakanipha kwaso kanye miyalelo, iqoqo nemiyalo eyisisekelo kanye nemithetho ukusetshenziswa yabo.

Ukwahlukanisa imiyalo SQL

Zonke imiyalo ejwayelekile SQL angabhekwa esekelwe lapho babelibangise khona. Njengoba isisekelo ngezigaba vneglasnoy kungathatha amasethi ezifana:

1. ukhononda imiyalo.

2. Amathimba esakhelwe izinqubo nemisebenzi.

3. trigger futhi uhlelo amatafula lokuyala.

4. Imisa inhlanganisela ukusebenza usuku string eziguquguqukayo.

5. Ekuyala ekusebenteni ngedatha namathebula.

Lokhu kuhlukaniswa kuyisigaba engapheli, kodwa eziyisisekelo SQL ulimi umyalo amasethi zakhelwe yingenxa zalezi zinhlobo.

Uma ucabangela Ukwahlukaniswa ulimi, ingasaphathwa ukuthi jikelele, njengoba kufakazelwa ububanzi bokusebenzisa yayo. Lolu limi kanye nokuhlela ezahlukeneyo zalo zisetshenziswa nje imvelo ejwayelekile kodwa nakwezinye izinhlelo, okuyinto, ngandlela-thile, uke wasebenzisa.

Ububanzi ukusetshenziswa SQL ingabonwa ngokombono isofthiwe ehhovisi, okungukuthi MicrosoftAccess. Lolu limi, noma kunalokho, zinhlobo ayo - MySQL, ungakwazi ukuphatha semininingwane Inthanethi. Ngisho Oracle ukuthuthukiswa imvelo ekusebenziseni kwabo imiyalo SQL imibuzo.

Ukusebenzisa SQL e MicrosoftAccess

Omunye ezivamile ukusetshenziswa kolimi software egciniwe izinhlelo iphakethe kubhekwa MicrosoftOffice. Ucwaningo le software ihlinzeka Yiqiniso esikoleni computer science, futhi efunda ibanga leshumi nanye kubhekwa yokuphatha semininingwane uhlelo MicrosoftAccess.

Kuyinto cwaningo isicelo abafundi bajwayelane ukuthuthukiswa kolimi of yolwazi futhi uthole nolwazi oluyisisekelo lokuthi zonke ifake. SQL Finyelela ithimba kunalokho bakudala, yebo, uma sicabanga ngendlela okhokhelwayo. Running imiyalo enjalo silula, futhi ingashintshwa kumhleli ikhodi.

Cabangela isibonelo esikhethekile:

Pe_SurName KHETHA

KUSUKELA Pherson

LAPHO Pe_Name = 'uMariya';

Kususelwa syntax umyalo, ungakwazi ukuqonda ukuthi ibuyisela igama lomsebenzisi lomuntu, kulesi simo, nowesifazane ogama lakhe noMariya, okuyinto lilondolozwe etafuleni egciniwe yothintana naye.

Nakuba ukusetshenziswa SQL e Finyelela kukhawulwe, ngezinye izikhathi imibuzo enjalo elula ungakwazi kakhulu lula ukuqaliswa imisebenzi eyabelwe.

Ukusebenzisa izitatimende SQL ku-Oracle

Oracle - kungcono cishe kuphela Microsoft SQL Server nesincintisana sína. Kuyinto kule ndawo intuthuko nokulawula database njalo kuholela ngcono inkampani isofthiwe izici umkhiqizo Microsoft njengoba mncintiswano - kuyinto injini yenqubekela phambili. Naphezu ukubangisana njalo, SQL Oracle ithimba okuphindaphindiwe SQL. Kumele kuqashelwe ukuthi nakuba Oracle futhi kubhekwa cishe ikhophi egcwele SQL, logic walesi simiso sezinto, futhi ulimi ubhekwa kulula.

Oracle uhlelo usebenzisa isethi ezithile imiyalo akuyona eyinkimbinkimbi ezinjalo. Uma sicabangela amakhono idatha izindawo ukuthuthukiswa egciniwe, Oracle akanawo isakhiwo eziyinkimbinkimbi imibuzo izidleke.

Lo mehluko wenza ivumela izikhathi eziningi ukuze kusheshiswe umsebenzi ngedatha, kodwa, ngokuphambene, kuholela ukusetshenziswa kahle inkumbulo, kwezinye izimo ngabanye. Oracle isakhiwo kakhulu eyakhelwe amathebula zesikhashana kanye ukusetshenziswa kwazo. Njengoba isibonelo: imiyalo SQL ohlelweni zisekelwe Isifaniso namazinga ulimi SQL ngokwayo, nakuba ayengazi kakhulu kuhluke kuwo.

SELECTCONCAT (CONCAT (CONCAT ( 'Employee', sname), CONCAT (SUBSTR (fname, 0, 1), SUBSTR (otch, 0, 1))), CONCAT ( 'prinyatnarabotu', acceptdate)) KUSUKELA abasebenzi LAPHO acceptdate> TO_DATE ('01 .01.80 ',' dd.mm.yyyy ');

Lokhu nombuzo uzobuya idatha emayelana nabasebenzi, abasebenzako isikhathi esithile. Nakuba isakhiwo umbuzo kuhlukile Microsoft SQL Server, ukukhishwa SQL imiyalo kulezi zinhlelo iyafana, ngaphandle imininingwane ezincane.

Ukusebenzisa SQL ku-inthanethi

Njengoba sekunama-World Wide Web, okungukuthi i-Inthanethi, ulimi SQL uyanda ububanzi bokusebenzisa. Njengoba yaziwa, inethiwekhi kugcinwa ulwazi oluningi, kodwa akuyona kunezinxushunxushu futhi kufakwe amawebhusayithi kanye amaseva ngokuvumelana izimfuneko ezithile.

Ukulondoloza ulwazi ku-intanethi, kanye nakwezinye izindawo, banesibopho ngqo database, kanye sites zokulawula. Ngokuvamile, amasayithi kanye nekhodi ihlelwe ngezilimi ezahlukene izinhlelo, kodwa database kusekelwe uhlobo SQL, futhi yilona limi ekudalweni egciniwe, ngamakhasimende e-MySQL interface web.

I-syntax futhi isethi eziyisisekelo imiyalo lolo limi ukukopisha ngokuphelele zonke SQL ajwayelekile, nabanye anezele wakhe, okuyinto amnike ngokungafani Microsoft tSQL Server.

SQL imiyalo efanayo ngokuphelele-syntax nje kuphela kodwa futhi iqoqo esezingeni kwemagama umsebenzi. Umahluko owokuthi esicelweni ucingo nokuhlelwa kwezinhlangano. Ngokwesibonelo, cabanga ngesicelo ukuze udale ithebula entsha, ungowokuqala lokho abakufundisayo izingane ezikoleni kukhompyutha:

$ Link = mysqli_connect ( 'localhost', "impande", '', 'umhloli');

uma (! $ isixhumanisi) die ( "Iphutha");

$ Umbuzo = 'ukudala abasebenzisi etafuleni (

ngemvume VARCHAR (20)

iphasiwedi VARCHAR (20)

');

uma (mysqli_query ($ isixhumanisi, $ umbuzo)) echo "Ithebula zadalwa.";

elseecho "Itafula engakaze wadala:" .mysqli_error ();

mysqli_close ($ isixhumanisi);

Ngenxa isicelo esinjalo, ungathola "abasebenzisi" esisha etafuleni, lapho kuzoba khona izindawo ezimbili: igama lomsebenzisi nephasiwedi.

Syntax washintsha ngaphansi I-Web, kodwa kususelwa MicrosoftSQLServer ithimba.

Isakhiwo Imibuzo MicrosoftSQLServer

Isampula isethi ethile amathebula idatha ingenye imisebenzi esemqoka SQL. Ukuze imisebenzi enjalo, inqobo khetha umyalo e SQL. Yilokho ngakho sichaziwe ngezansi.

Imigomo yokwakha iqembu silula, futhi umyalo khetha kakhulu SQL yakhiwa kanje. Ngokwesibonelo, kukhona itafula lapho idatha ziyatholakala abasebenzi, isibonelo, igama Yomuntu. Thina aveze umraro ukuthi etafuleni udinga ukukhetha idatha emayelana nabasebenzi, okuyinto usuku lokuzalwa - ku isikhawu kusuka lokuqala luka-January kuya lokuqala ngo-March wonyaka wamanje, kukonke. Ngesampuli enjalo kuyadingeka ukuze akhiphe umyalo SQL, okuyinto design ejwayelekile hhayi kuphela, kodwa futhi isimo Ukukhetha:

Khetha * kusuka Person

Lapho P_BerthDay> = '01 / 01/2016 'futhi P_BerthDay <= '03 / 01/2016'

Ukwenza lo myalo uzobuya yonke idatha mayelana abasebenzi, ogama lwekutalwa esikhathini, okuyinto imisiwe nguwe. Ngezinye izikhathi ungahle ukuba inselele ukubonisa igama kuphela zokugcina, igama lokuqala patronymic isisebenzi. Kuze kube yimanje, isicelo ukwakha ngendlela ethé ukuhluka kancane, isibonelo, njengoba kulandela:

SelectP_Name - igama

P_SurName - nesibongo

P_Patronimic - patronymic

kusukela Person

Lapho P_BerthDay> = '01 / 01/2016 'futhi P_BerthDay <= '03 / 01/2016'

Nokho, lokhu kuyinto nje Ukukhetha lutho. He is, empeleni akuphazamisi lutho, kodwa kuphela inikeza ulwazi. Kodwa uma unquma ukuthatha sina ulimi SQL, kuzodingeka ukuba zifunde ukwenza izinguquko database, kusukela ukwakhiwa zabo ngaphandle akunakwenzeka. Indlela lokhu okwenziwa kuzoxoxwa ngezansi.

Basic SQL imiyalo ukushintsha idatha

I-syntax yakhiwe hhayi kuphela querying, kodwa futhi ngoba idatha zokukhwabanisa. Empeleni, uhlelo msebenzi database ngokubhala script for amasampula nemibiko, kodwa ngezinye izikhathi kudingeka wenze izinguquko etafuleni. SQL imiyalo uhlu ngoba ukwenza kanjalo incane futhi siqukethe amaqembu ezintathu eziyinhloko:

1. Faka (Trans. Faka).

2. Isibuyekezo (Trans. Update).

3. Susa (Trans. Susa).

Inhloso yalezi amaqembu kulula ukunquma, kwanele nje ukuhumusha igama labo. Lezi imiyalo asebenziseka kalula futhi ungenayo i-ukwakhiwa kuyinkimbinkimbi kohlelo, kodwa kuyafaneleka ukusho ukuthi abanye babo, uma esetshenziswa ngendlela engafanele, kungabangela umonakalo ongalungiseki database.

Njengomthetho, ngaphambi kokusebenzisa lezi imiyalo MSSQL okudingeka uyicabangele futhi ikhumbule zonke nangemiphumela engaba khona kokusebenza kwabo.

Ngemva kokufunda le miyalo, uzokwazi ukuqala ngokugcwele ukusebenza amathebula egciniwe, kanjalo ushintshe futhi wenze ezinye eziguquguqukayo entsha ochazayo ku noma ususe ubudala.

Faka ithimba

Ukufaka idatha ku itafula usebenzisa iqembu nephephile - Faka. idatha ngokungalungile elifakiwe njalo kungenzeka ukuba asuse bese wengeza database futhi.

Faka umyalo ukufaka idatha entsha kuthebula futhi ikuvumela ukuba wengeze njengendlela isethi ephelele, futhi ngokukhetha.

Ngokwesibonelo, cabanga ngokufaka umyalo ku echazwe ngaphambilini ithebula Lomuntu. Ukuze umyalo SQL kumele ukusebenzisa ethebuleni idatha, okuvumela ukufaka yonke idatha ku etafuleni noma uligcwalise ngokukhetha.

Faka ku umuntu

Khetha 'Grigoriev' 'Vitali', 'Petrovich', '1/1/1988'

MS SQL Iseva umyalo plan onjalo ngokuzenzakalelayo nigcwalisiwe ngokwazi konke amaseli etafuleni ngedatha ecacisiwe. Kunezimo lapho umsebenzi akanayo kokuqala phakathi, isibonelo, wafika ezosebenza phezu exchange evela eJalimane. Kulokhu, ukusebenzisa idatha umyalo ifaka lokhu okulandelayo, oyoletha etafuleni kuphela lokho okudingekayo. I-syntax lo myalo simiswe ngalendlela lelandzelako:

Insertintoperson (P_Name, P_SurName, P_BerthDay)

Amanani ( 'kaDavide,' Hook ',' 2/11/1986)

Leli thimba igcwalisa kuphela yeseli elicacisiwe, futhi bonke abanye kuyoba null.

Umyalo ukushintsha idatha

Ukushintsha idatha njengoba wonke umugqa, futhi amanye amaseli Ukusebenzisa i-Update SQL umyalo. Yenza lo myalo idinga kuphela ngemibandela ethile, okungukuthi, ngokunembile abonisa lapho umugqa we inombolo edingekayo ukuze wenze izinguquko.

Buyekeza SQL umyalo has a-syntax elula. Ukuqinisekisa ukusetshenziswa ngendlela efanele, nawe ucacise ukuthi iyiphi idatha ku ikholomu futhi irekhodi kufanele zishintshwe. Ngokulandelayo, ukwakha iskripthi nokwenza ke. Cabangela nasi isibonelo. Sidinga ukushintsha usuku lokuzalwa kaDavide Hook okubaliwe ethebuleni isisebenzi inombolo 5.

Buyekeza Person

Setha P_BerthDay = '02 / 10/1986 'lapho P_ID = 5

Isimo (e iskripthi) ngeke kushintshe usuku lokuzalwa wonke amarekhodi etafuleni, bese ubuyekeza kuphela ezidingekayo.

Kuyinto lokhu ithimba program zisebenzisa avame, ngoba ikuvumela ukuba ushintshe idatha ku etafuleni ngaphandle kokudala umonakalo ebalulekile kulo lonke ulwazi.

Imiyalo ukusebenzisa izinqubo esakhelwe nemisebenzi

Ngosizo SQL ulimi, awukwazi kuphela ukwakha imibuzo, kodwa futhi ukwakha izindlela esakhelwe ngokusebenzisana idatha. Njengomthetho, zikhona izikhathi lapho ufuna ukusebenzisa emzimbeni isampula yemibuzo ebhaliwe ngaphambili.

Ukwahlulela ngokunengqondo ke udinga ukopishe bese unamathisela umbhalo isampula endaweni efanele, kodwa ungakwazi ukwenza esilula impela leso. Cabangela lesi sibonelo lapho ukusebenza esibonakalayo inkinobho uboniswa ukuphrinta umbiko, isibonelo nge-Excel. Lo msebenzi kuzokwenziwa njengoba kudingeka. Ngezinhloso ezinjalo, akhelwa izinqubo egcinwe. Imiyalo imibuzo SQL, kuleli cala, inqubo kanye zibangelwa iqembu SQLExec.

Ake sithi futhi inqubo izinsuku Ukuhoxiswa lokuzalwa abasebenzi kanye echazwe ngaphambilini Umuntu ithebula yadalwa. Kulokhu, asikho isidingo ukubhala lonke nombuzo. Ukuze uthole imininingwane edingekayo yanele ukuba akhiphe umyalo Exec [igama inqubo] bese udlulisa nemingcele adingekayo ukuze izibonelo zalokho. Njengoba isibonelo singacabangela ezosetshenziswa ekwakheni uhlobo ezifana inqubo:

CREATEPROCEDUREPrintPerson

@DB smalldatetime

@DE smalldatetime

NJENGOBA

HLELA NOCOUNT KU;

* KHETHA kusukela Person

KUSUKELA HumanResources.vEmployeeDepartmentHistory

LAPHO P_BerthDay> = @DB futhi P_BerthDay <= @DE

ANDEndDateISNULL;

GO

Le nqubo ibuyisela lonke ulwazi mayelana nabasebenzi kabani lokuzalwa kuzoba isikhathi esithile esinikeziwe.

Inhlangano ubuqotho idatha. trigger

Ezinye MS SQL-umyalo, Omunye angase athi ngisho, umklamo esingagcini nje ukuhlela idatha zokukhwabanisa, kodwa futhi ukuqinisekisa ubuqotho babo. Ngezinhloso ezifana ngolimi yakhelwe ohlelweni design, okuyinto kwakha uMklami yena. Lezi ngokuthi trigger, okunganikeza ukulawula idatha.

Kulokhu, lenhlangano kwezimiso yokuhlola esebenzisa indinganiso SQL-imibuzo umyalo. Ngo-trigger, ungakha eningi izimo kanye nemikhawulo yedatha ezokwenza ukulawula ukufinyelela hhayi kuphela ulwazi kodwa futhi esenqabela ukususwa, ukushintshwa, noma faka idatha.

Nazi izinhlobo imiyalo SQL ukuthi ingasetshenziswa isibangeli, akugcini. Cabangela isibonelo esilandelayo.

Uma thina ukuchaza indlela ngokudala inhlamvu ke izinhlobo imiyalo SQL ziyefana uma udala inqubo. algorithm uqobo uzobe kuchaziwe ngezansi.

Isinyathelo sokuqala siwukuba ukuchaza umyalo isevisi ukudala trigger:

DALA TRIGGER Person_Insert

iphuzu Eceleni itafula:

ONPerson

Ikhomba okuyiwona imisebenzi idatha (kulesi simo, idatha ukushintsha operation).

Isinyathelo esilandelayo ukuze ucacise amatafula kanye eziguquguqukayo:

ukumemezela @ID int. @Date smalldatetime @nID int. @nDatesmalldatetime

Ngaphezu kwalokho ukumemezela cursors ukukhetha amathebula edatha ukususwa kanye Ukufakwa kwemininingwane:

Ukumemezela isikhombisi C1 akhethiwe P_ID, P_BerthDay kusukela ofakiwe

Ukumemezela isikhombisi C2 I-khetha P_ID, P_BerthDay kusukela ukususwa

Ukuchaza izinyathelo Ukukhetha idatha. Uma emzimbeni cursors linqume isimo kanye yokusabela kuso:

uma @ID = @nID futhi @nDate = '01 / 01/2016 '

uqale

ukusebenza Run sMasseges 'akunakwenzeka. Usuku akulungile '

ukuphela

Kuhle kokusho ukuthi inhlamvu ngeke nje ukudala, kodwa futhi ukuvala okwesikhashana. zokukhwabanisa enjalo kungaba ubambe kuphela Unicode kokwenza SQL Iseva umyalo:

altertablePERSONdisabletriggerall - ukukhubaza wonke ama-trigger esidalelwe etafuleni, futhi efanele, altertablePERSONenabletriggerall - ekufakweni.

Lezi SQL eziyisisekelo imiyalo ezisetshenziswa kakhulu, kodwa inhlanganisela yabo kungaba ezihlukahlukene kakhulu. SQL - ulimi lokwakha izinhlelo nezimo kakhulu futhi kunikeza unjiniyela ezingeqi kwezingu amathuba.

isiphetho

Kulokhu okungenhla singaphetha ngokuthi: SQL amakhono olimi kumelwe nakanjani labo ofuna ukuzibandakanya sina izinhlelo. It kusenhliziyweni yonke imisebenzi eyenziwa e-inthanethi futhi database ekhaya. Yingakho uhlelo esizayo kumele ukwazi inani imiyalo ulimi, ngoba kuphela kungaba, ngomqondo ongokomfanekiso, ukuxhumana nge-computer.

Yiqiniso, zikhona amaphutha, njengoba kuzo zonke izinto kuleli zwe, kodwa mancane kakhulu ukuthi umane ukuphaphatheka ngaphambi kokufaneleka. Phakathi kwabo bonke labo ngolimi SQL izinhlelo cishe kuphela into efana nayo, ngoba sikhona yonke indawo, futhi ulwazi ekubhaleni scripts amakhodi ziyisisekelo cishe wonke amasayithi.

Inzuzo enkulu SQL bezogovorchno angabhekwa alula, ngoba phela nguye busuka yezifundo zezikole. Njengoba ekwazi ukumelana ngisho imfundamakhwela uMklami, hhayi ababeyizazi izilimi ngempela.