SQL-imibuzo umyalelo

I-SQL yenye yeelwimi ezininzi eziqhelekileyo zokudala nokulawula i-database, kunye nokuqhuba iintlobo ezahlukeneyo zedatha ngokwayo.

Njengoko umkhuba ubonisa, kulula kakhulu ukufunda nokuyenza isona sigama esisisigxina solimi lwesiNgesi. Njengaye nawuphi na ulwimi lolwimi, i-SQL inemigca yayo kunye ne-syntax, isethi yemiyalelo eyisiseko kunye nemithetho yokuyisebenzisa.

Ukwahlulelwa kwemilayezo yeelwimi yeSQL

Yonke imiyalelo ye-SQL esemgangathweni ingaqwalaselwa ngokusekelwe kwinjongo yabo. Njengesizathu sokubeka bucala, umntu unokuthatha iisethi ezifana nale:

1. Imiyalelo yokwakha imibuzo.

2. Imiyalelo yenkqubo yokwakha kunye nemisebenzi.

3. I-Trigger kunye nemiyalelo yeetafile yenkqubo.

4. Umdibaniso usetha ukusebenzisana nomhla kunye nemiguqu ekhoyo.

5. Imiyalelo yokusebenza kunye nedatha kunye neetafile.

Olu hlobo luya kuqhubeka ngonaphakade, kodwa iisethi ezisisiseko zomyalelo weelwimi lweSQL ziya kwakhiwa ngokusekelwe kwezi ntlobo.

Ukuqwalasela ukuhlelwa kolwimi, umntu akanakunceda kodwa uthetha ukuba yinto yonke, njengoko kuboniswe ngumlinganiselo wokusetyenziswa kwayo. Ulwimi lwenkqubo kunye neentlobo zalo ezihlukeneyo asetyenziswanga kuphela kwiimeko eziqhelekileyo, kodwa nakwezinye iinkqubo oye wasebenzisa enye indlela.

Ubungakanani bokusebenzisa i-SQL bunokujongwa ukusuka kwindawo yokujonga kwesoftware ye-ofisi, okuyi-Microsoft Access. Olu lwimi, okanye kunoko, inguqulo yalo - i-MySQL, ikuvumela ukuba ulandele iinkcukacha kwi-intanethi. Nangona indawo yokuphuhlisa i-Oracle isebenzisa imiyalelo ye-SQL njengesiseko sayo.

Ukusebenzisa iSQL kwi-Microsoft Access

Esinye sezibonelo ezilula zokusebenzisa ulwimi kwiprogram yolwazi-nkcukacha yi-MicrosoftOffice software iphakheji. Ukufundwa kwesi sixhobo sobonelelo kunikezelwa kwiklasi yesikolo ye-computer yesayensi, kwaye kwinqanaba leshumi elinanye i-MicrosoftAccess yolawulo lweenkcukacha zenkcukacha ziqwalaselwa.

Ngethuba lokufunda le sicelo ukuba abafundi baqhelane nolwimi lokuphuhliswa kwedatha kwaye bafumane ukuqonda okuyisiseko kwento yonke kuyo. Imithetho ye-SQL yokufikelela ayifuni, ngokuqinisekileyo, ukuba uyayibheka ngokufanelekileyo. Ukuphunyezwa kwemiyalelo enjalo kulula, kwaye zidalwe kumhleli wekhowudi.

Makhe siqwalasele umzekelo onokhenkethi:

HLOLA iPe_SurName

KUPherson

LAPHO Pe_Name = 'Mariya';

Ngokusekelwe kwi-syntax yomyalelo, uyaqonda ukuba iya kubuyisa igama lokugqibela lomsebenzisi, kulo mzekelo umfazi ogama linguMariya, eligcinwe kwitheyibhile yolwazi lweenkcukacha.

Nangona ukusetyenziswa kwe-SQL kwi-Access kungagqibekanga, ngamanye amaxesha imibuzo enjalo elula ingenza kube lula ukwenza umsebenzi owenziwe.

Ukusebenzisa imiyalelo ye-SQL kwi-Oracle

I-Oracle mhlawumbi ngumncintiswano omkhulu kwiMicrosoft SQL Server. Kule ndawo ukuphuhliswa kunye nokulawulwa kwedata ehlala iqhubela phambili ekuphuculeni imisebenzi yemveliso ye-Microsoft software, ekubeni ukhuphiswano lwenjini yenkqubela phambili. Nangona kukho ukuphikisana, i-Oracle's SQL imilayelo iphinda ibuye i-SQL. Kufuneka kuqatshelwe ukuba, nangona i-Oracle ithathwa njengokuba ikopi epheleleyo ye-SQL, ingqiqo yale nkqubo kunye nolwimi lonke libhekwa njengelula.

Inkqubo ye-Oracle ayinaso isakhiwo esiyinkimbinkimbi xa usebenzisa imigaqo ethile. Ukuba sicinga ngamakhono okuphuhliso lwendawo yolwazi, i-Oracle ayinaso isakhiwo esiyinkimbinkimbi yemibuzo enemibuzo.

Lo mahluko uvumela amaxesha amaninzi ukukhawulezisa umsebenzi kunye nedatha, kodwa, ngokuchaseneyo, ikhokelela ekusebenziseni kakuhle imemori, kwezinye iimeko ezithile. Isakhiwo se-Oracle sakhiwa ngokutsha kwietafile zesikhashana kunye nokusetyenziswa kwazo. Ngokomzekelo: imiyalelo ye-SQL kule nkqubo yakhiwa ngokufana nemigangatho yeelwimi le-SQL ngokwalo, nangona iyingcambu kwaye ihluke kuyo.

I-SELECTCONCAT (CONCAT (CONCATANT), CONCAT (SUBSTR (fname, 0, 1), SUBSTR (i-otch, 0, 1))), CONCAT ('Yamkeleka, yamkeleka)) KWABASEBENZI ABAKHO bayamkela> ('01 .01.80 ',' dd.mm.yyyy ');

Lo mbuzo uza kubuyisa idatha malunga nabasebenzi abaqeshwe ixesha elithile. Nangona isakhiwo sesicelo sihluke kwi- Microsoft SQL Server, ukuphunyezwa kwemiyalezo ye-SQL kule nkqubo kufana, ngaphandle kweenkcukacha ezincinane.

Ukusebenzisa i-SQL kwi-intanethi

Ngokuza kweWebhu yeWebhu yehlabathi, oko kukuthi, i-intanethi, ububanzi bokusetyenziswa kweelwimi lwe-SQL landa. Njengoko uyazi, inethwekhi igcina ulwazi oluninzi, kodwa ayikho indawo engathandekiyo, kodwa iposwe kwiisayithi kunye namaseva ngokwemiqathango ethile.

Ukugcinwa kolwazi kwi-intanethi, njengakwezinye iindawo, ngokufanelekileyo kukuxanduva lweenkcukacha zolwazi, kwaye iisayithi ziinkqubo zolawulo. Ngokuqhelekileyo, iisayithi kunye nekhowudi yazo zihlelwe ngeelwimi ezahlukeneyo, kodwa i-database isekelwe kwenye yeentlobo ze-SQL, oko kukuthi, ulwimi lwendalo yolwazi, olujoliswe kwi-intanethi ye-intanethi ye-MySQL.

I-syntax kunye nesethi yomgaqo-siseko welo lwimi likopisha ngokupheleleyo i-SQL eqhelekileyo, kodwa kunye nezinye izongezo zayo, ezenza ukuba umehluko kwi-Microsoft tSQL Server.

Imithetho ye-SQL ayifani ngokufanayo nje kuphela kwi-syntax, kodwa nakwii-standard standard of words words. Ukwahlukana kukuphela kokufowunelwa nokulungiswa kwesicelo. Umzekelo, unokuqwalasela umbuzo wokudala itafile entsha, ngowokuqala abantwana abafunda kwizikolo kwi-computer yesayensi:

$ Link = mysqli_connect ('localhost', 'root', '', 'tester');

Ukuba (! $ Link Link) iyafa ("Iphutha");

I-Query = 'ukudala abasebenzisi beetafile (

Ukungena kwi-VARCHAR (20),

Iphasiwedi VARCHAR (20)

) ';

Ukuba (i-mysqli_query (ikhonkco yedola, i-$ query)) yisela "Itafile yenziwe.";

Elseecho "Itafile ayidalwanga:" .mysqli_error ();

I-Mysqli_close (ikhonkco yeedola);

Ngenxa yesicelo, unokufumana ietafile entsha "Abasebenzisi", okuya kuba neendawo ezimbini: ukungena ngemvume kunye nephasiwedi.

I-syntax ishintshwe kwiWebhu, kodwa ngokusekelwe kwimilayezo ye-MicrosoftSQLServer.

Imibuzo yokwakha ye-Microsoft SQL Server

Ukukhethwa kwiilayibhile zetekethi ethile yenye yemisebenzi esemqoka ye-SQL. Kulo msebenzi, umyalelo wokukhetha kwi-SQL unikezelwa. Kuya kuxutyushwa ngezantsi.

Imithetho yokwakha umyalelo ilula, kwaye umyalelo wokukhetha kwi-SQL ukhiwe ngale ndlela ilandelayo. Ngokomzekelo, kukho itafile apho kukho idatha malunga nomqeshwa, okwenza, umzekelo, unegama lomntu. Masimise umsebenzi ovela etafileni kuyimfuneko ukukhetha idatha malunga nabaqeshwa abanomhla wokuzalwa ophakathi kwithuba lokuqala ukususela ngowokuqala kukaJanuwari ukuya ku-Matshi wokuqala kunyaka okhoyo. Kuloo sampuli, kufuneka usebenzise umyalelo we-SQL ongeke ube neyakhekileyo isakhiwo, kodwa kunye nemeko yokukhetha:

Khetha * ukusuka kumntu

Apho P_BerthDay> = '01 / 01/2016 'kunye neP_BerthDay <= '03 / 01/2016'

Ukuphunyezwa komyalelo onjalo kuya kubuya yonke idatha malunga nabaqeshwa abanokuzalwa kwabo ngexesha elibekwe nguwe. Ngamanye amaxesha umsebenzi unokuba ukufumana igama elidlulileyo kuphela, igama lokuqala kunye nomntu onomsebenzi womsebenzi. Ukwenza oku, umbuzo kufuneka wakhiwe ngokuthe kancinane, umzekelo, ngale ndlela:

KhethaP_Name ligama

P_SurName - igama lokugqibela

P_Patronimic - patronymic

Ukusuka kumntu

Apho P_BerthDay> = '01 / 01/2016 'kunye neP_BerthDay <= '03 / 01/2016'

Nangona kunjalo, oku kukukhethwa kwinto ethile. Yena, ngokwenene, ayichaphazeli nantoni na, kodwa unikezela ngolwazi kuphela. Kodwa ukuba unquma ukuthatha i-SQL ngokungathí sina, kuya kufuneka ufunde indlela yokwenza utshintsho kwiinkcukacha zolwazi, kuba ukuzakha ngaphandle kwayo akunakwenzeka. Indlela oku kwenziwa ngayo iya kuxutyushwa ngezantsi.

Imiyalelo eyisiseko yeSQL yokutshintsha idatha

I-syntax yolwimi ayikwakhiwe nje kuphela ukunyanzelisa, kodwa kunye nokwahlula idatha. Ngokukodwa, umsebenzi weprogram yomgcini-nkcukacha ubhala izikripthi zeesampuli kunye neengxelo, kodwa ngamanye amaxesha kufuneka ulungiselele kwiitheyibhile. Uluhlu lwemiyalelo ye-SQL yezenzo ezinjalo luncinci kwaye luqukethe imiyalelo emithathu ephambili:

1. Faka.

2. Uhlaziyo.

3. Cima.

Injongo yale miyalelo kulula ukuyiqonda, kuba oku kuya kukwanela ukuguqulela igama labo. Le miyalelo kulula ukuyisebenzisa kwaye ayinaso iskimu sokwakha esiyinkimbinkimbi, kodwa kubalulekile ukukhankanya ukuba ezinye zazo, ukuba zisetyenziswe ngendlela engafanelekanga, zingenza umonakalo ongenakulinganiswa kwi-database.

Njengomgaqo, ngaphambi kokuba kusetyenziswe imilayezo ye-MSSQL kufuneka icingelwe ngayo, kwaye ingqalelo yonke imiphumo enokwenzeka yokuphunyezwa kwayo.

Xa ufunde le miyalelo, uya kukwazi ukuqala ngokugcwele kunye neetafile zeenkcukacha, ngokwenza uguqule kwaye uqalise ezinye iziguquko ezintsha okanye ususe abadala.

Umyalelo wokufaka

Ukufaka idatha kwitafile, sebenzisa umyalelo okhuselekileyo - Faka. Idatha engeniswe ngokungalunganga ihlala isuswa kwaye ingeniswa kwisiseko sedata kwakhona.

Umyalelo wokufaka ufakelwe ukufaka idatha entsha kwitheyibhile kwaye ikuvumela ukuba ungeze kokubili isethi epheleleyo kwaye ukhethe.

Ngokomzekelo, cinga umyalelo wokufaka kwitafile yomntu echazwe ngaphambili. Ukuze ufake idatha kwitafile, kufuneka usebenzise umyalelo we-SQL, oya kukuvumela ukuba ufake yonke idatha kwitafile okanye ugcwalise ngokukhethayo.

Faka umntu

Khetha 'iGrigoriev', 'Vitaliy', 'Petrovich', '01 / 01/1988 '

Imiyalelo ye-MS SQL SERVER yesi sicwangciso ngokuzenzekelayo ihlala kuzo zonke iiseli etafile kunye nedatha echaziweyo. Kukho imeko xa umqeshwa engenawo igama eliphakathi, umzekelo, wafika wasebenza eJamani ukuba atshintshwe. Kule meko, kufuneka ufeze umyalelo wokufaka idatha oza kubhala kuphela okufunekayo kwitheyibhile. I-syntax yalo myalelo kukuba:

Faka ifayile (P_Name, P_SurName, P_BerthDay)

Imilinganiselo ('David', 'Hook', '02 / 11/1986 ')

Umyalelo onjalo uza kuzalisa kuphela iiseli ezichaziweyo, kwaye zonke ezinye ziya kuba ziphosakeleyo.

Umyalelo wokutshintsha idatha

Ukuhlaziywa kwedatha yomgca wonke okanye ezinye iiseli, sebenzisa umyalelo we-Update SQL. Ukufeza umyalelo onjalo kubalulekile kuphela ngemeko ethile, okuchanekileyo ukucacisa ukuba yeyiphi umgca ngenani eliyimfuneko ukwenza utshintsho.

Umyalelo we-Update SQL une-syntax elula. Ukusetyenziswa okuchanekileyo, kufuneka ucacise ukuba yiyiphi idatha, yiphi ikholomu kunye naluphi ukungena okumele kutshintshwe. Emva koko, qulunqa iskripthi uze uyenze. Makhe siqwalasele umzekelo. Kubalulekile ukutshintsha umhla wokuzalwa kukaDavid Hooke, obandakanywe kwitafile yabasebenzi ngaphantsi kwenombolo 5.

Uhlaziyo lomntu

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

Imeko (kule script) ayiyi kuvumela ukutshintsha umhla wokuzalwa kuzo zonke iirekhodi zetafile, kodwa iya kuhlaziya kuphela ezifunwayo.

Ngulo myalelo abaprogram abasebenzisa rhoqo, kuba kukuvumela ukuba utshintshe idatha kwitafile ngaphandle kokubangela ingozi enkulu kuyo yonke ingcaciso.

Imiyalelo yokusebenzisa iinkqubo nemisebenzi eyakhelwe ngaphakathi

Ukusebenzisa i-SQL, awukwazi kuphela ukwakha imibuzo, kodwa wenze kwakhona iindlela ezakhelwe ukusebenzela kunye nedatha. Njengomthetho, kukho amaxesha apho kufuneka usebenzise emzimbeni wesicelo esinye isampuli ebhaliwe ngaphambili.

Ukuba ugweba ngokucacileyo, kufuneka ukopishe isicatshulwa sesampuli uze unamathisele kwindawo efanelekileyo, kodwa unokwenza ngesisombululo esilula. Makhe sibone umzekelo xa iqhosha liboniswa kwindlela yokusebenza yokushicilela ingxelo, xela kwi-Excel. Lo msebenzi uza kwenziwa njengoko kuyimfuneko. Ngeenjongo ezinjalo, iinkqubo ezigcinwe ngaphakathi zigcinwe. Imiyalelo yokucela i-SQL, kule meko, yinkqubo kwaye ibizwa ngokuba isebenzisa umyalelo we-SQLExec.

Cinga ukuba inkqubo yenziwe ukuba ivelise umhla wokuzalwa kwabasebenzi kwi-table yomntu echazwe ngaphambili. Kule meko, akukho mfuneko yokubhala yonke imibuzo. Ukufumana ulwazi oluyimfuneko, kwanele ukwenza umyalelo we-Exec [igama lomsebenzi] kwaye udlulise iiparamitha eziyimfuneko zokukhethwa. Ngokomzekelo, unokuqwalasela indlela yokudala inkqubo yolu hlobo:

I-CREATEPROCEDURPrintaPerson

@DB uhlawule ixesha

@DE ukufumana ixesha

AS

FUNDA NOCOUNT;

HLOLA * kusuka kumntu

KWI-HumanResources.vEmployeeDepartmentHistory

LAPHO P_BerthDay> = @DB kunye neP_BerthDay <= @DE

ANDEndDateISNULL;

HLELA

Le nkqubo iya kubuya yonke ingcaciso malunga nabasebenzi babo abaza kuba nexesha lokuzalwa ngexesha elililo.

Umbutho wolungiso lweenkcukacha. Abacebisi

Eminye imiyalelo ye-MS SQL, unokwazi ukusho ukuyila, ungavumeli kuphela ukuququzelela ukusetyenziswa kwedatha, kodwa nokuqinisekisa ukuthembeka kwabo. Ngeenjongo ezinjalo, ulwimi lusetyenziswe inkqubo yoyilo, edala umprogram. Ezi zinto zibizwa ngokuba zizinto ezinokuthi zinikeze ulawulo lweedatha.

Kule meko, imilayezo ye-SQL yombuzo oqhelekileyo isetyenziselwa ukulungiselela imeko yokukhangela. Kwizinto ezibangela, unokwenza iimeko ezininzi kunye nemingcele yokusebenza nedatha eya kunceda ukulawula ukungena kuphela ulwazi, kodwa ukukhusela ukususwa, ukuguqulwa okanye ukufakwa kwedatha.

Iintlobo zeemiyalelo ye-SQL engasetyenziswa kwi-trigger ayinamkhawulo. Cinga ngomzekelo.

Ukuba uchaza indlela yokudala i-trigger, ngoko ke iintlobo zeemiyalelo ye-SQL ziyafana nokudala inkqubo. I-algorithm ngokwayo iya kuchazwa ngezantsi.

Isinyathelo sokuqala kukuchaza umyalelo wenkonzo ukudala iziphumo:

DALA UTHUTHI Umntu_Insert

Emva koko, cacisa ukuba yeyiphi itafile:

KUNYE

Cacisa ukuba yiyiphi intsebenzo kunye nedatha (kwimeko yethu lo msebenzi wokuguqulwa kwedatha).

Isinyathelo esilandelayo kukucacisa iitheyibhile kunye neziguquko:

Memezela @ID int. @Date smalldatetime @nID int. @iDatesmalldatetime

Okulandelayo, simemezela izikratshi ukuba sikhethe idatha esuka kutshintshulwa kwedatha kunye neetafile zokufaka:

Ikhonkco yeSeclare C1 ukuze ukhethe P_ID, P_BerthDay ukusuka kwi-Inserted

Ikhonkco yeSeclare C2 ukuze ukhethe P_ID, P_BerthDay ukususwa

Chaza iindlela zokukhetha idatha. Emva koko, emzimbeni wabakhweli sibabeka umqathango kunye nokuphendula ngayo:

Ukuba @ID = @nID kunye @nDate = '01 / 01/2016 '

Qalisa

I-SMasseges 'Umsebenzi awukwazi ukwenza. Umhla awufanelanga '

Ukuphela

Kufanelekile ukukhankanya ukuba i-trigger ayikwazi ukudala kuphela, kodwa iphinde ikhubaze isikhashana. Olu lungiso lwenziwa kuphela ngumprofeti ngokuqhuba imiyalelo ye-SQL SERVER:

I-AltertablePERSONdisabletriggerall - ukukhubaza zonke izinto ezibangelwa kule tafile, kwaye, ngokufanelekileyo, ziguqulePERSONenabletriggerall - ukuze zibencede.

Le miyalelo eyisiseko ye-SQL isetyenziswe rhoqo, kodwa ukudibanisa kwabo kunokungafani kakhulu. I-SQL yimiyalezo yohlelo oluguquguqukayo kwaye inika ithuba elikhulu lophuhliso.

Isiphelo

Kuzo zonke ezi ngasentla, unokwenza isigqibo esisodwa: ulwazi lweelwimi lwe-SQL luyimfuneko kuphela kulabo abaya kubandakanyeka ngokukodwa kwinkqubo. Ilala entliziyweni yazo yonke imisebenzi eyenziwa kwi-intanethi nakwii-home database. Yingakho umprofeti wenkomfa kufuneka afunde imiyalelo emininzi kulwimi, kuba kuphela ngoncedo onakho, ukuthetha kunye nekhompyutha.

Ewe, kukho iingxaki, njengento yonke apha kweli hlabathi, kodwa ayingabalulekanga kangangokuthi bavele bavele phambi kokufaneleka. Phakathi kwazo zonke iilwimi zeprogram, i-SQL iphela yodwa yoluhlobo lwayo, kuba iphela jikelele, nolwazi lokubhala iincwadi zeempendulo kunye neikhowudi ziphakathi kwentsimi.

Inzuzo ephambili SQL bezogovorchno kunokuthathwa njengento elula yayo, ngenxa yokuba emva kokuba onke, nguye, lowo eyaziswa kwikharityhulam yesikolo. Ekubeni ingaphatha nkqu wenkqubo abo basandula kuqalisa, hayi ngokwenene abawazi ngeelwimi.