Okuyisisekelo SQL

Funda nge-DDL, i-DML ne-JOINs

Ulimi Lokusebenza Oluhlelekile lungenye yezakhiwo ezibalulekile zokwakha izakhiwo zesimanje. I-SQL ichaza izindlela ezisetshenziselwa ukudala nokuphatha yolwazi oluhlobene kuzo zonke izipulatifomu ezinkulu. Ekuboneni kokuqala, ulimi lungase lubonakale lusabisa futhi luyinkimbinkimbi, kodwa akuyona yonke into enzima.

Lokhu kungeniswa kwezinto eziyisisekelo ngemuva kwe-SQL kuhlolisisa eminye yemilayezo esemqoka esetshenziselwa ukwakha nokuguqula imininingwane.

Mayelana ne-SQL

Ukubiza okulungile kwe-SQL kuyinkinga yokuphikisana phakathi komphakathi wedatha. Ezingeni layo le-SQL, i-American National Standards Institute yamemezela ukuthi ukubiza okusemthethweni "kuyigcawu el." Kodwa-ke, abaningi abaqeqeshiwe basebenzi bezenzo baye bathatha isichazamazwi slang "sequel." Ukuzikhethela kungokwakho.

I-SQL ifika kuma-flavour amaningi. I-Oracle yolwazi isebenzisa i-PL / SQL yayo yokuthengisa. I-Microsoft SQL Server isebenzisa i-Transact-SQL. Zonke ukuhlukahluka kusekelwe embonini ye-ANSI SQL. Lesi sethulo sisebenzisa imiyalo ye-ANQ-ehambisanayo ye-SQL esebenza nanoma yisiphi isistimu yesistimu yezinhlelo zesimanje.

I-DDL ne-DML

Imiyalo ye-SQL ingahlukaniswa ngezilimi ezimbili ezisemqoka. Ulimi Lokuchazwa Kwemininingwane (DDL) liqukethe imiyalo esetshenziselwa ukwakha nokubhubhisa imininingwane yolwazi kanye nezinto ezigciniwe. Ngemuva kokuthi isakhiwo sedatha sichazwe nge-DDL, abaqondisi bezinqolobane kanye nabasebenzisi bangasebenzisa uLimi Lokuphathwa Kwedatha (DML) ukufaka, ukuthola futhi ukuguqula idatha equkethwe kuyo.

Izincazelo zokusetshenziswa koLwazi lweDatha

Ulimi Lokuchazwa Kwemininingwane lisetshenziselwa ukwakha nokubhubhisa imininingwane yolwazi kanye nezinto ezigciniwe. Le miyalo isetshenziswa ngokuyinhloko ngabaqondisi bezinqolobane ngesikhathi sokusetha nokususwa kwephrojekthi ye-database. Nakhu ukubheka isakhiwo nokusetshenziswa kwemithetho emine ye-DDL:

DALA. Ukufaka uhlelo lokuphathwa kwedatha kwikhompyutha kukuvumela ukuba udale futhi uphathe imininingwane eminingi emele. Isibonelo, ungase ufune ukugcina imininingwane yabathengi bamakhasimende emnyangweni wakho wokuthengisa kanye nolwazi lwabasebenzi kumnyango wakho we-HR. Umyalo wokudala usetshenziselwa ukusungula ngayinye yalezi zolwazi ku-platform yakho. Isibonelo, umyalo:

DALA abasebenzi baseDATABASE

yenza i-database engenalutho okuthiwa "abasebenzi" ku-DBMS yakho. Ngemuva kokudala i-database, isinyathelo esilandelayo ukudala amatafula aqukethe idatha. Okunye okuhlukile komyalo wokudala kungasetshenziswa kule njongo. Umyalo:

DALA ITHEBULA siqu_info (igama le-first_name char (20) alikho, i-last_name char (20) ayikho null, i-employee_id int ingenalutho)

usungula itafula elibizwa ngokuthi "personal_info" ku-database yamanje. Esikhathini, ithebula liqukethe izici ezintathu: kuqala_name, last_name kanye ne-employee_id kanye nolunye ulwazi olwengeziwe.

Sebenzisa. Umyalo we-USE uvumela ukuthi ucacise database ofuna ukusebenzisana nayo ngaphakathi kwe-DBMS yakho. Isibonelo, uma okwamanje usebenza ku-database yokuthengisa futhi ufuna ukukhipha iminye imiyalo ezosithinta isistimu yomsebenzi, baqalelele ngomyalo olandelayo we-SQL:

Basebenzisa abasebenzi

Kubalulekile ukuhlale uqaphela i-database oyisebenzisayo ngaphambi kokukhipha imiyalo ye-SQL ephethe idatha.

ALTER. Uma usungule ithebula ngaphakathi kwedatha, ungase ufune ukuguqula incazelo yayo. Umyalo we-ALTER uvumela ukuthi wenze izinguquko esakhiweni setafula ngaphandle kokususa futhi ukubuyisela kabusha. Bheka umyalo olandelayo:

I-ALTER TABLE yomuntu_info ADD imali yomholo null

Lesi sibonelo sinezela imfanelo entsha etafuleni le-personal_info-isamba somsebenzi. Ingxabano "yezimali" ichaza ukuthi isamba somsebenzi sigcinwa besebenzisa ifomethi yama-dollar namasenti. Okokugcina, igama elingukhiye elithi "null" litshela i-database ukuthi kulungile ukuthi le nsimu ingenayo inzuzo kunoma isiphi isisebenzi esinikeziwe.

DROP. Umyalo wokugcina weDatha Definition Ulimi, i-DROP, usenza sikwazi ukususa zonke izinto ze-database kusuka ku-DBMS yethu. Isibonelo, uma sifuna ukususa unomphela ithebula le-personal_info esilidalile, singasebenzisa umyalo olandelayo:

DROP ITHEBULA_nomuntu siqu

Ngokufanayo, umyalo ongezansi uzosetshenziselwa ukususa yonke imininingwane yesisebenzi:

Abasebenzi be-DROP DATABASE

Sebenzisa lo myalo ngokunakekelwa. Umyalo we-DROP ususa zonke izakhiwo zedatha kusuka ku-database yakho. Uma ufuna ukususa amarekhodi ngabanye, sebenzisa umyalo OYISEKELA weLimi lokuPhathwa kweDatha.

Ukulawulwa Kwemininingwane Yokusetshenziswa Kwemininingwane

Ulimi Lokulawulwa Kwemininingwane (DML) lisetshenziselwa ukuthola, ukufaka nokuguqula imininingwane yolwazi. Le miyalo isetshenziswa yibo bonke abasebenzisi be-database ngesikhathi sokusebenza kwedatha.

BHEKA. Umyalo we-INSERT ku-SQL usetshenziselwa ukwengeza amarekhodi etafuleni ekhona. Ukubuyela esibonelweni se-personal_info kusuka kusigaba sangaphambilini, cabanga ukuthi umnyango wethu we-HR udinga ukwengeza isisebenzi esisha ku-database yayo. Ungasebenzisa umyalo ofana nalena:

BHALISA KWAMAKHAYA we-personal_info ('bart', 'simpson', 12345, $ 45000)

Qaphela ukuthi kunezindinganiso ezine ezichazwe irekhodi. Lezi zihambisana nemfanelo yetafula ngendlela echazwe ngayo: kuqala_name, last_name, employee_id kanye nomholo.

Khetha. Umyalo wokukhetha uwumyalo ovame ukusetshenziswa kakhulu ku-SQL. Ivumela abasebenzisi be-database ukuthi bathole ulwazi oluthile abafisa kusuka ku-database yokusebenza. Bheka izibonelo ezimbalwa, uphinde usebenzise ithebula le-personal_info kusuka kulwazi lomsebenzi.

Umyalo oboniswe ngezansi uthola yonke imininingwane equkethwe kuthebula lomuntu siqu. Qaphela ukuthi i-asterisk isetshenziswa njenge-wildcard ku-SQL. Lokhu kusho ukuthi "Khetha konke kusuka etafuleni lomuntu siqu".

HLOLA * KUSUKA ku-personal_info

Ngaphandle kwalokho, abasebenzisi bangase bafune ukunciphisa izimfanelo ezibuyiselwa ku-database. Isibonelo, uMnyango Wezabasebenzi ungadinga uhlu lwamagama okugcina abo bonke abasebenzi enkampanini. Umyalo olandelayo we-SQL uzothola kuphela lolo lwazi:

Khetha ukhetho lokugcina kusuka ku-personal_info

ISIQEPHU se-WHERE singasetshenziselwa ukunciphisa amarekhodi atholakala kulabo abahlangabezana nezimiso ezicacisiwe. I-CEO ingase ibe nesithakazelo ekubuyekezeni amarekhodi abasebenzi kubasebenzi abakhokhelwa kakhulu. Umyalo olandelayo uthola yonke idatha equkethwe ngaphakathi kwe-personal_info yamarekhodi anenani eliphakeme lemali elingaphezu kwe $ 50,000:

HLOLA * FROM FROM_info LAPHO umholo> $ 50000

QAPHELA. Umyalo we-UPDATE ungasetshenziswa ukuguqula ulwazi oluqukethwe ngaphakathi kwetafula, kungaba ngobuningi noma ngabanye. Cabanga ukuthi inkampani inikezela bonke abasebenzi ukukhuphuka kwezindleko ezingamaphesenti angu-3 emalini yabo ngonyaka. Umyalo olandelayo we-SQL ungasetshenziselwa ukusebenzisa lokhu ngokushesha kubo bonke abasebenzi abagcinwe ku-database:

QAPHELA umlando we-personal_info SET = umholo * 1.03

Lapho isisebenzi esisha uBimmy Simpson sibonisa ukusebenza ngenhla nangaphezulu kokubizelwa komsebenzi, abaphathi bafuna ukuqaphela ukufezwa kwakhe kwe-stellar nge-$ 5,000 yokukhuliswa. Isigaba se-WHERE singasetshenziselwa ukukhipha uBart ngalokhu kuphakamiswa:

QAPHELA umlando we-personal_info we-SET = inkokhelo + $ 5000 LAPHO umsebenzi_id = 12345

SUSELA. Okokugcina, ake sibheke umyalo OYISEKELA. Uzothola ukuthi i-syntax yalo myalo ifana neyomunye umyalo we-DML. Ngeshwa, umbiko wethu wamuva wezinkokhelo wezinkampani awuzange uhlangabezane nokulindela futhi uBart ompofu ubekwe eceleni. Umyalo OYISEKELA nge-WHERE isigatshana angasetshenziswa ukususa irekhodi lakhe kusuka etafuleni le-personal_info:

SUSHA kusuka ku-personal_info LAPHO umsebenzi_id = 12345

JOINs

Njengoba manje ufunde izinto eziyisisekelo ze-SQL, yisikhathi sokuqhubekela phambili komunye wemigomo enamandla kunazo zonke ulimi olunikezayo-isitatimende se-JOIN. Isitatimende se-JOIN sikuvumela ukuba uhlanganise idatha kumathebula amaningi ukuze usebenze ngokucophelela inqwaba yedatha. Lezi zitatimende zilapho amandla eqiniso egciniwe ehlala khona.

Ukuze uhlole ukusetshenziswa kokusebenza okuyisisekelo kwe-JOIN ukuhlanganisa idatha kusuka kumatafula amabili, qhubeka nesibonelo usebenzisa ithebula le-PERSONAL_INFO bese ufaka ithebula elengeziwe kumxube. Cabanga ukuthi unetafula elibizwa nge-DISCIPLINARY_ACTION eladalwe ngesitatimende esilandelayo:

DALA ITHEBULA lokuqondisa izigwegwe_enzo (action_id int hhayi null, employee_id int hhayi null, ukuphawula char (500))

Leli thebula liqukethe imiphumela yokwenza izigwegwe kwabasebenzi benkampani. Uzoqaphela ukuthi ayiqukethe ulwazi mayelana nesisebenzi ngaphandle kwenombolo yomsebenzi. Kulula ukucabangela izimo eziningi lapho ungase ufune ukuhlanganisa ulwazi kusuka kumatafula e-DISCIPLINARY_ACTION no-PERSONAL_INFO.

Cabanga ukuthi usebenze ngokudala umbiko obala izinyathelo zokuqondiswa kwezigwegwe ezithathwe kubo bonke abasebenzi abanemali emikhulu kunezigidi ezingama-40,000. Ukusetshenziswa komsebenzi we-JOIN, kulokhu, kuqondile. Singawuthola lolu lwazi usebenzisa umyalo olandelayo:

HLOLA igama le-personal_info.first_name, personal_info.last_name, izigwegwe_action.com ezivela ku-personal_info, ukuqondiswa kwe-disciplinary_action LAPHO umuntu_info.employee_id = ukuqondiswa komthetho_action.employee_id no-personal_info.salary> 40000

Ikhodi ichaza amatafula amabili esifuna ukujoyina esigatshaneni se-FROM bese sifaka isitatimende ku-WHERE isigatshana ukukhawulela imiphumela kumarekhodi ayenamagama e-ID abasebenzi futhi ahlangabezane nemigomo yethu yomholo omkhulu kunama-40,000.