SQL Izibonelelo

Funda ngeDDL, iDML kunye neeJOIN

ULwimi oluCwangcisiweyo lweeNgxelo lunye lwezakhiwo ezibalulekileyo zokwakha izakhiwo zamatye eencwadi zamanje. I-SQL ichaza iindlela ezisetyenziselwa ukudala nokusebenzisa ulwazi olusondeleyo kuzo zonke iipulatifomu ezinkulu. Ekuboneni kokuqala, ulwimi lungabonakala luloyiko kwaye luyinkimbinkimbi, kodwa akusiyo yonke into enzima.

Oku kungeniswa kwizinto ezisemgangathweni emva kweSQL kuthatha ukhangelelwaniso olufutshane kwimithetho ephambili esetyenziselwa ukudala nokuguqula iinkcukacha zolwazi.

Malunga neSQL

Ukuchaneka ngokuchanekileyo kweSQL yinkinga yokubambisana phakathi kwendawo yoluntu yolwazi. Kwizinga layo leSQL, i-American National Standards Institute yabhengeza ukuba ukubiza ngokusemthethweni kukuthi "ngumgca." Nangona kunjalo, abaqeqeshi abaninzi beenkcukacha zeenkcukacha baye bathabatha isichazamazwi sokuthi "ukulandelana." Okukhethiweyo kukukho.

I-SQL ivela kwiinambuzane ezininzi. I-Oracle yolwazi isebenzisa i-PL / SQL yayo. I-Microsoft SQL Server isebenzisa iTransact-SQL. Zonke iinguqu zisekelwe kwinqanaba le-ANSI SQL. Le ntshayelelo isebenzisa imiyalelo ye-ANQ-ehambisanayo ye-SQL esebenza kuyo nayiphi na inkqubo yenkcazelo yolwazi lwangoku.

DDL kunye neDML

Imiyalelo ye-SQL ingahlukaniswa zibe zimbini iilwimi eziphambili. ULwimi lweNkcazelo yeData (DDL) luqulethe imiyalelo esetyenziselwa ukudala nokutshabalalisa iinkcukacha kunye nezinto zeenkcukacha. Emva kokuba isiseko sedatha sichazwe ngeDDL, abalawuli beenkcukacha kunye nabasebenzisi bangasebenzisa uLwimi lokuLawulwa kweData (DML) ukufaka, ukufumana nokuguqula idatha equlethwe kuyo.

Iinkcukacha zeeNkcazo zeeLwimi

Iilwimi Inkcazo yoLwimi isetyenziselwa ukudala nokutshabalalisa iinkcukacha kunye nezinto zeenkcukacha. Le miyalelo ibhekiswe ngokubanzi ngabalawuli beenkcukacha ngexesha lokulungisa kunye neendlela zokususa zeprojekthi yedatha. Nantsi ukukhangela kwisakhiwo kunye nokusetyenziswa kwemithetho emine ye-DDL:

DALA. Ukufaka inkqubo yolawulo lwekhompyutha kwikhompyutha kukuvumela ukuba udale uphinde uphathe amaninzi eenkcukacha ezizimeleyo. Umzekelo, unokuba ufuna ukugcina i-database yabathengi bamakhasimende kwisebe lakho lokuthengisa kunye neenkcukacha zabasebenzi kwiSebe lakho le-HR. Umyalelo WOKUSETYENZISWA usetyenziselwa ukusekwa nganye yalezi zolwazi kwiqonga lakho. Umzekelo, umyalelo:

DALA ABASEBENZI BABASEBENZI

kudala idatha engenanto ebizwa ngokuthi "abasebenzi" kwi-DBMS yakho. Emva kokudala isiseko sedatha, isinyathelo esilandelayo kukudala iitafile eziqulethe idatha. Olunye uhlobo oluthile lwe-CREATE umyalelo lungasetyenziswa kule njongo. Umyalelo:

DALA ITHEBHU siqu_info (i-first_name char (20) ayikho, i-last_name char (20) ayikho, i-employee_id int ingenanto)

usungula itafile ebizwa ngokuthi "personal_info" kwi-database yangoku. Kulo mzekelo, itafile iqulethe iimpawu ezintathu: kuqala_name, last_name kunye nomsebenzi_id kunye nolwazi olongezelelweyo.

SEBENZISA. Umyalelo we-USE uvumela ukuba uchaze i-database ofuna ukuyisebenzisana ne-DBMS yakho. Ngokomzekelo, ukuba usebenza ngokusisiseko kwi-database kwaye ufuna ukukhupha imiyalelo ethile eya kubachaphazela isiseko semisebenzi, baqalise ngomyalelo olandelayo we-SQL:

ABASEBENZA ABASEBENZI

Kubalulekile ukuhlala uqaphela isiseko osebenza kuyo phambi kokukhupha imiyalelo ye-SQL eqhuba idatha.

ALTER. Emva kokuba udale itafile ngaphakathi kwedatha, unokufuna ukuguqula inkcazelo yayo. Umyalelo we-ALTER uvumela ukuba wenze utshintsho kwisakhiwo setafile ngaphandle kokususa nokuyifumana kwakhona. Qwalasela umyalelo olandelayo:

I-ALTER TABLE person_info ADD imali yomvuzo engekho

Lo mzekelo uneza umxholo omtsha kwitafile ye-personal_info-umvuzo womqeshwa. Ingxabano "yemali" ichaza ukuba umvuzo womqeshwa ugcinwa usebenzisa idireyi kunye nefomathi yentsenti. Ekugqibeleni, igama elithi "null" lithetha i-database ukuba kulungile ukuba le ntsimi ingenalo nantoni na kuyo nayiphi na umsebenzi onikwe.

DROP. Umyalelo wokugqibela weNkcazo yoLwazi lweDatha, i-DROP, isenza ukuba sisuse yonke into egciniweyo ye-database kwi-DBMS yethu. Umzekelo, ukuba sifuna ukususa ngokusisigxina itafile ye-personal_info esiyidalile, siya kusebenzisa umyalelo olandelayo:

YOKUPHATHA ITHEBHA yomuntu_info

Ngokufanayo, umyalelo ongezantsi ungasetyenziselwa ukususa yonke i-database yomqeshwa:

ABASEBENZI BE-DROP

Sebenzisa lo myalelo ngononophelo. Umyalelo we-DROP ususa zonke iinkcukacha zedata kwi-database yakho. Ukuba ufuna ukususa iirekhodi zomntu ngamnye, sebenzisa umyalelo OCUSELEKILEYO woLwimi lokuLawulwa kweDatha.

Iinkcukacha zoLwazi lweeLwimi zokuPhatha

ULwimi lokuLawulwa kweDatha (iDML) lisetyenziselwa ukubuyisa, kufaka nokuguqula ulwazi lweenkcukacha. Le miyalelo isetyenziswe ngabo bonke abasebenzisi beenkcukacha ngexesha lokusebenza rhoqo kwedatha.

MSEBENZI. Umyalelo we-INSERT kwi-SQL isetyenziswe ukwengeza iirekhodi kwietafile ezikhoyo. Ukubuyela kumzekelo we-personal_info ukusuka kwicandelo elidlulileyo, cinga ukuba isebe lethu le-HR lifuna ukongeza umqeshwa omtsha kwisiseko sayo sedatha. Ungasebenzisa umyalelo ofana nalo:

FUMA NGOKUBALULEKILEYO ('bart', 'simpson', 12345, $ 45000)

Qaphela ukuba kukho imilinganiselo emine echazwe kwirekhodi. Ezi zihambelana neempawu zeetafile ngendlela echazwe ngayo: yokuqala_name, last_name, umsebenzisi_id kunye nomvuzo.

UKUQALA. Umyalelo we-SELECT ngumyalelo oqhelekileyo osetyenziswayo kwi-SQL. Ivumela abasebenzisi beenkcukacha ukuba bafumane ulwazi oluthile abanqwenela kwi-database yolwazi. Khangela imizekelo embalwa, kwakhona usebenzisa itafile ye-personal_info ukusuka kwisiseko somqeshwa.

Umyalelo oboniswe ngezantsi ufumana yonke inkcazelo equkethwe kwitafile ye-personal_info. Qaphela ukuba i-asterisk isetyenziswe njenge-wildcard kwi-SQL. Oku kuchaza ngokoqobo "Khetha yonke into evela kwitafile ye-personal_info."

UKUHLOLA * KUSUKA ku-personal_info

Ngaphandle koko, abasebenzisi bangathanda ukukhawulela iimpawu ezifunyanwa kwiziko ledatha. Ngokomzekelo, isebe loLuntu leMfundo lingafuna uludwe lwamagama okugqibela abo bonke abasebenzi kwinkampani. Umyalelo olandelayo we-SQL uza kufumana kuphela olo lwazi:

UKUQALA igama lokugqibela_name FROM_info

ISAHLUKO se-WHERE singasetyenziselwa ukunciphisa iirekhodi ezifunyenwe kulabo badibana neenqobo ezichaziweyo. I-CEO ingaba nomdla wokuhlaziya iirekhodi zabasebenzi bonke abasebenzi abahlawuliweyo. Umyalelo olandelayo ufumana yonke idatha equlethwe kwi-personal_info kwiirekhodi ezinenani lemali elingaphezulu kwama-50,000:

UKUKHETHA * KUSUKA siqu_info LAPHO umvuzo> $ 50000

QAPHELA. Umyalelo we-UPDATE ungasetyenziselwa ukuguqula ulwazi oluqulethwe kwitheyibhile, enoba ubuninzi okanye ngabanye. Kucinga ukuba inkampani inika bonke abaqeshwa i-3% yokunyuka kweendleko zokuhlala kwimivuzo yabo ngonyaka. Umyalelo olandelayo we-SQL ungasetyenziselwa ukufaka isicelo ngokukhawuleza kubo bonke abasebenzi abagcinwe kwiziko ledatha:

QAPHELA u-personal_info UMSEBENZI wemivuzo = umvuzo * 1.03

Xa umqeshwa omtsha uB Bart Simpson ubonisa ukusebenza ngaphaya nangaphezulu kwefowuni yomsebenzi, ulawulo lunqwenela ukuqaphela ukuphunyezwa kwakhe kwe-stellar kunye ne-$ 5,000 ephakanyisiweyo. ISAHLUKO se-WHERE singasetyenziselwa ukukhupha uBart ngenxa yokuphakamisa oku:

HLAWULA u-personal_info UMSEBENZI wemivuzo = umvuzo + $ 5000 OKUBA umqeshwa_id = 12345

SUSELA. Ekugqibeleni, makhe sijonge umyalelo we-DELETE. Uya kufumana ukuba i-syntax yalo myalelo ifana neyomnye umyalelo we-DML. Ngelishwa, ingxelo yethu yakamuva yenkampani ayizange ihlangabezane nokulindela kunye no-Bart ohluphekileyo. Umyalelo OCUSELEKILEYO ngegatya eliphi na i-WHERE lingasetyenziselwa ukususa irekhodi lakhe kwitafile ye-personal_info:

UKUSULWA KWE-personal_info KUNYE umsebenzi_id = 12345

JOINs

Ngoku ukuba ufunde isiseko se-SQL, lixesha lokuqhubela kwelinye leengcamango ezinamandla kunokuba ulwimi lunikele-isitatimende se-JOIN. I-statement yeJOIN ikuvumela ukuba udibanise idatha kwiitafile ezininzi ukwenzela ukucwangcisa ngokuchanekileyo ubuninzi beenkcukacha. Ezi nkcazo apho amandla okwenyaniso ehlala khona.

Ukuphonononga ukusetyenziswa kwesiseko esisisiseko se-JOIN ukudibanisa idatha kwitafile ezimbini, qhubeka nomzekelo usebenzisa i-PERSONAL_INFO itafile uze ungeze itafile eyongezelelweyo kumxube. Cinga ukuba unetafile ebizwa ngokuba yi-DISCIPLINARY_ACTION eyadalwa ngolu hlobo lulandelayo:

DALA ITHEBULI yeluleko-nxaxheba (action_id int intanhla, i-employee_id int ingenanto, iingxoxo zerhafu (500))

Le tafile iqulethe iziphumo zezenzo zoluleko kumsebenzi wabasebenzi. Uya kuphawula ukuba ayinayo nayiphi na ingcaciso malunga nomqeshwa ngaphandle kombolo yomqeshwa. Kulula ukucinga iingxaki ezininzi apho ungafuna ukudibanisa ulwazi oluvela ku-DISCIPLINARY_ACTION kunye ne-PERSONAL_INFO amatafula.

Cinga ukuba unikwe umsebenzi wokwenza ingxelo eluhlu lwezenzo zoluleko ezithathwe kubo bonke abasebenzi abanomvuzo omkhulu kunama-40,000. Ukusetyenziswa komsebenzi we-JOIN, kulo mzekelo, kuchanekileyo. Singawufumana olu lwazi usebenzisa umyalelo olandelayo:

HLOLA u-personal_info.first_name, personal_info.last_name, i-disciplinary_action.com ezivela kwi-personal_info, i-disciplinary_action NAKHO umntu_info.employee_id = uqeqesho_action.employee_id kunye no-personal_info.salary> 40000

Ikhowudi icacisa iitafile ezimbini esifuna ukujoyina kwigatya elisuka kwi-FROM kwaye sibandakanya inkcazo kwinqaku le-WHERE ukukhawulela iziphumo kwiirekhodi ezinxulumene nee-ID zee-Employees kwaye zidibene neendlela zethu zomvuzo ongaphezu kwama-40,000.