Ukudala yolwazi namathebula Ku-SQL

Ukudala i-Database

Ingabe usukulungele ukuqala ukudala ulwazi lwamatafula namatafula ngolimi oluthile olwenziwe ngombuzo ? Kulesi sihloko, sihlola inqubo yokwakha amatafula ngesandla nge- CREATE DATABASE kanye ne-CREATE TABLE imiyalo. Uma ususha ku-SQL, ungase ufise ukubuyekezwa okokuqala kwe- SQL Basics article.

Izidingo zebhizinisi

Ngaphambi kokuba sihlale phansi ekhibhodi, kudingeka siqiniseke ukuthi sinokuqonda okuqinile izidingo zamakhasimende. Iyiphi indlela engcono kakhulu yokuthola lokhu kuqondisisa? Ukukhuluma ikhasimende, yebo! Ngemuva kokuhlala phansi nomqondisi we-Human Resources Director we-XYZ, sifunde ukuthi bayinkampani yokuthengisa iwijethi futhi ngokuyinhloko banentshisekelo ekulandeleni ulwazi kubasebenzi babo bokuthengisa.

I-XYZ Corporation ihlukanisa amandla ayo okuthengisa emaphandleni asempumalanga nasentshonalanga, ngayinye ehlukaniswe ibe yizindawo eziningi ezihlanganiswe nokuthengiswa komuntu ngamunye. Umnyango we-HR ungathanda ukulandelela indawo ehlanganiswe nesisebenzi ngasinye kanye nolwazi lomholo lomsebenzi ngamunye nesakhiwo sokuphatha. Ukuhlangabezana nalezi zidingo, senze i-database enamatafula amathathu, aboniswe kumdwebo we- Entity-Relationship kuleli khasi.

Ukukhetha i-Platform yedatha

Sinqume ukusebenzisa uhlelo lokuphathwa kwedatha (noma i-DBMS) eyakhelwe kuLimi Oluhlelekile Lokusebenza (SQL). Ngakho-ke, yonke imiyalo yethu yedatha kanye nemibhalo yokudala ithebula kufanele ibhalwe nge-ANSI SQL ejwayelekile engqondweni.

Njengenzuzo eyengeziwe, ukusebenzisa i-ANSI-ehambisanayo ne-SQL izoqinisekisa ukuthi le miyalo izosebenza kunoma iyiphi i-DBMS esekela izinga elijwayelekile le-SQL , kuhlanganise ne-Oracle ne-Microsoft SQL Server. Uma ungakhethi indawo yesikhala sakho sedatha, i- database ye-Database Software Options ikuhamba ngenqubo yokukhetha.

Ukudala i-Database

Isinyathelo sethu sokuqala ukudala idatha ngokwayo. Izinhlelo eziningi zokuphathwa kwedatha zinikeza uchungechunge lwezinketho zokwenza izilungiselelo zemininingwane kulesi sinyathelo, kodwa i-database yethu ivumela kuphela ukudala okulula idatha. Njengawo wonke imiyalo yethu, ungase ufise ukubonisana namadokhumenti e-DBMS yakho ukuze unqume ukuthi ngabe yimiphi imingcele ehambile exhaswe yisistimu yakho ehlangabezana nezidingo zakho. Masisebenzise umyalo we-CREATE DATABASE ukusetha database yethu:

DALA abasebenzi base-DATABASE

Thatha inothi elikhethekile le-capital esetshenziswa esibonelweni esingenhla. Kuyinto evamile phakathi kwabamemezeli be-SQL ukusebenzisa zonke izinhlamvu zamagama angukhiye angukhiye njenge-"CREATE" no- "DATABASE" ngenkathi usebenzisa wonke amagama aphansi ngaphansi kwamagama ahlongozwa ngumsebenzisi njengegama "labasebenzi" lesedatha sedatha. Lezi zivumelwano zinikeza ukufunda okulula.

Qhubeka ufunda lesi sifundo njengoba sakha amatafula we database yethu.

Ukufunda Okuningi

Uma ungathanda ukufunda okwengeziwe ngolimi olwenziwe ngohlelo lokufunda, funda isingeniso ku-SQL noma ubhalisele izifundo zethu zamahhala e-Learning SQL e-mail.

Njengoba manje senze futhi sidalwe database yethu, silungele ukuqala ukudala amatafula amathathu asetshenziselwa ukugcina idatha ye-XYZ Corporation yabasebenzi. Sizobe sisebenzisa amatafula esakhelwe esigabeni sangaphambilini salesi sifundo.

Ukudala Ithebula Lethu Lokuqala

Ithebula lethu lokuqala liqukethe imininingwane yomuntu ngamunye wesisebenzi senkampani yethu. Sidinga ukufaka igama lomsebenzi ngamunye, umholo, i-ID, kanye nomphathi. Kuhle ukwenza umklamo wokuhlukanisa amagama wokugcina nabokuqala abe yizinkambu ezihlukene ukuze kube lula ukusesha idatha nokukhetha esikhathini esizayo. Futhi, sizogcina umphathi wesisebenzi ngasinye ngokufaka ireferensi kubunikazi bezisebenzi zomphathi kumrekhodi ngamunye wesisebenzi. Ake siqale sibheke ithebula lesisebenzi elifunwa.

ImibikoUkuthi isici sigcina i-ID yomphathi kumsebenzi ngamunye. Kusukela kumarekhodi esampula abonisiwe, singanquma ukuthi uSue Scampi ungumphathi womabili uTom Kendall noJohn Smith. Kodwa-ke, ayikho imininingwane ku-database kumphathi we-Sue, njengoba kuboniswe ukungena kwe-NULL emgqeni wakhe.

Manje singasebenzisa i-SQL ukudala itafula kubasebenzi bethu base database. Ngaphambi kokuba senze kanjalo, masiqinisekise ukuthi sise database efanele ngokukhipha umyalo we-USE:

Basebenzisa abasebenzi;

Ngaphandle kwalokho, "abasebenzi be-DATABASE;" umyalo uzokwenza umsebenzi ofanayo. Manje singabheka umyalo we-SQL osetshenziselwa ukudala abasebenzisi bethu itafula:

DALA izisebenzi ze- TABLE (umsebenzisi we-INTEGER NOT NULL, igama lokugcina elithi VARCHAR (25) AKUKHO NULL, kuqala igama elithi VARCHAR (25) AKUKHO NULL, kubika i-INTEGER NULL);

Njengalesi sibonelo esingenhla, inothi ukuthi umhlangano wokuhlela usho ukuthi sisebenzisa zonke izinhlamvu zamagama angukhiye angu-SQL namagama angaphansi kwekholomu namathebula okuthiwa ngumsebenzisi. Umyalo ongenhla ungabonakala udidekile ekuqaleni, kodwa empeleni kukhona isakhiwo esilandela ngemuva kwalo. Nasi umbono ojwayelekile ongase ususe izinto kancane:

DALA ITHEBULA ithebula_nomfanekiso (izinketho zedatha ye-attribute_name, ..., izinketho ze-datagrampe ze-attribute_name);

Izinhlamvu Nezinhlobo Zedata

Esikhathini esandulele, igama lesithebula ngabasebenzi futhi sihlanganisa izimfanelo ezine: umqashi, igama lokugcina, igama lokuqala, ne-reportsto. I-datatype ibonisa uhlobo lwazi olufisa ukulondoloza ensimini ngayinye. I-ID yomsebenzi inombolo ephelele yenombolo, ngakho-ke sizosebenzisa i-INTEGER yedatha yezintambo zombili insizakalo yomsebenzi kanye nensimu yokubika. Amagama omsebenzi ayoba ngamacu omlingiswa obude obuhlukile futhi asilindeli ukuthi isisebenzi sinegama lokuqala noma elide kunamagama angama-25. Ngakho-ke, sizosebenzisa uhlobo lwe-VARCHAR (25) kulezi zinsimu.

Amanani we-NULL

Singacacisa noma i-NULL noma NOT NULL enkambu yokukhetha yesitatimende se-CREATE. Lokhu kumane kuchaza i-database ukuthi ngabe amanani we-NULL (noma angenalutho) avunyelwe kulowo mfanelo lapho enezela imigqa ku-database. Esikhathini sethu, umnyango wakwa-HR udinga ukuthi i-ID yomsebenzi kanye negama eliphelele ligcinwe kumsebenzi ngamunye. Kodwa-ke, akuwona wonke umsebenzi onomphathi - i-CEO ayibiki muntu! - ngakho-ke sivumela ukungena kwe-NULL kuleyo nsimu. Qaphela ukuthi i-NULL iyinani elizenzakalelayo futhi ukushiya le nketho kuzovumela ngokugcwele amanani we-NULL ngenhloso.

Ukwakha Amathebula Ahlalayo

Manje ake sibheke izindawo zetafula. Ngokubheka ngokushesha le datha, kubonakala sengathi kudingeka sigcine inamba kanye namacici amabili okuhluka okuhlukahluka. Njengesibonelo sethu sangaphambilini, asilindeli i-ID yesifunda ukuthi idle izinhlamvu ezingaphezu kuka-25. Noma kunjalo, amanye amasimu ethu anegama elide, ngakho-ke sizokwengeza ubude bokuvunyelwa kwalowo mfanelo kuzinhlamvu ezingu-40. Ake sibheke i-SQL ehambelanayo:

DALA izindawo ZEZINDAWO (insimu INTEGER NOT NULL, insimu Incazelo VARCHAR (40) AKUKHO NULL, regionid VARCHAR (25) AKUKHO NULL);

Ekugcineni, sizosebenzisa ithebula le-EmployeeTerritories ukugcina ubuhlobo phakathi kwabasebenzi nezindawo. Ukwaziswa okuningiliziwe kumsebenzi ngamunye nensimu kugcinwe kumatafula ethu amabili adlule. Ngakho-ke, sidinga kuphela ukugcina izinombolo ezimbili zokukhomba ezikuleli tafula. Uma sidinga ukwandisa lolu lwazi singasebenzisa i-JOIN kumilayezo yethu yokukhetha idatha ukuze uthole ulwazi kusuka kumatafula amaningi. Le ndlela yokugcina idatha iyanciphisa ukubuyiswa kwemininingwane ku-database yethu futhi iqinisekise ukusetshenziswa kahle kwesikhala esiteshini sethu sokugcina. Sizomboza umyalo we-JOIN ngokujulile kusifundo esizayo. Nasi ikhodi ye-SQL yokusebenzisa ithebula lethu lokugcina:

DALA ama-employereterritories (umsebenzisi we-INTEGER NOT NULL, indawo ye-INTEGER NOT NULL);

I-SQL yeMikhakha inikeza ukulungisa isakhiwo seDatha emva kokudala

Uma uhlakaniphile kakhulu namuhla, kungenzeka ukuthi uqaphele ukuthi "ngengozi" sishiye enye yezimfuneko zokuklama lapho sisebenzisa amatafula ethu asemininingwane. Umqondisi we-XYZ Corporation wakwa-HR ucele ukuthi ulwazi lomholo wezentengiselwano lomsebenzi wezentengiselwano futhi singanaki ukuhlinzeka lokhu kumatafula egciniwe esiwadalile .

Nokho, konke akulahlekile. Singasebenzisa umyalo we-ALTER TABLE ukwengeza lesi sici kumininingwane yethu ekhona. Sifuna ukugcina umholo njengenani eliphelele. I-syntax ifana kakhulu neyomyalo we-CREATE TABLE, nansi:

Abasebenzi be-ALTER TABLE ADD umholo INTEGER NULL;

Qaphela ukuthi sichaze ukuthi amanani we-NULL avunyelwe kule mbono. Ezimweni eziningi, akukho okukhethwa kukho lapho ungeza ikholomu kuthebula elikhona. Lokhu kungenxa yokuthi itafula selivele liqukethe imigqa engenakho ukungena kule mfanelo. Ngakho-ke, i-DBMS ifaka ngokuzenzekelayo inani le-NULL ukuze ligcwalise okungekho.

Futhi lokho kuhlanganisa ukubuka kwethu ku-database ye-SQL kanye nenqubo yokudala ithebula. Hlola emuva njalo izitolimende ezintsha kumchungechunge wethu wokufundisa we-SQL. Uma ungathanda isikhumbuzo se-imeyili uma izihloko ezintsha zengeziwe kwi-About Database, sicela uqinisekise ukubhalisela iphephandaba lethu!