Samar da Bayanan Data da Tables A SQL

Samar da Database

Shin kuna shirye don fara samar da bayanan bayanai da tebur tare da Harshen Bincike ? A cikin wannan labarin, zamu gano yadda ake aiwatar da allo da hannu tare da CREATE DATABASE da CREATE TABLE dokokin. Idan kun kasance sabon zuwa SQL, za ku iya so mu sake nazarin mu SQL Basics labarin farko.

Bukatun Kasuwanci

Kafin mu zauna a kan keyboard, muna bukatar mu tabbatar cewa muna da cikakken fahimtar bukatun abokin ciniki. Mene ne hanya mafi kyau don samun wannan basira? Magana da abokin ciniki, ba shakka! Bayan da muke zaune tare da Daraktan Cibiyar Harkokin Kasuwancin na XYZ, mun fahimci cewa sun kasance kamfanin tallace-tallace na widget din kuma suna da sha'awar biyan bayanai akan ma'aikatan tallan su.

XYZ Corporation ta raba rassan tallan su a cikin yankunan gabas da yammacin, wanda aka rarraba kowannensu zuwa yankuna da dama wanda ke biyan kuɗi. Kungiyar HR tana so su bi ƙasar da kowane ma'aikacin ke rufewa da kuma bayanin aikin albashin ma'aikaci da tsarin kulawa. Don saduwa da waɗannan bukatun, mun tsara ɗakunan bayanai wanda ke kunshe da teburin uku, wanda aka nuna a cikin Siffar Abokin Hulɗa a wannan shafin.

Zaɓin Platform Database

Mun yanke shawarar yin amfani da tsarin sarrafa bayanai (ko DBMS) wanda aka gina a kan Harshen Sakamakon Sakamakon (SQL). Saboda haka, duk bayananmu da ka'idodin tsari na rufi ya kamata a rubuta tare da misali ANSI SQL.

A matsayin ƙarin amfani, ta amfani da ANSI-compliant SQL za su tabbatar da cewa waɗannan umarni za su yi aiki a kan wani DBMS cewa goyon bayan SQL daidaituwa , ciki har da Oracle da Microsoft SQL Server. Idan ba a zabi wani dandalin don database ɗinka ba tukuna, labarin Abubuwan Zaɓuɓɓukan Zabin Bayanai na Intanit suna tafiya da kai ta hanyar tsari.

Samar da Database

Mataki na farko shine don ƙirƙirar bayanan kanta. Mutane da yawa tsarin gudanarwa na samar da jerin jerin zaɓuɓɓuka don tsara tsarin siginan bayanai a wannan mataki, amma database din kawai ya yarda da sauƙin ƙirƙirar bayanai. Kamar yadda dukkanin umarninmu, ƙila za ku iya tuntuɓar takardun don DBMS don ƙayyade idan duk matakan da aka ci gaba da goyan bayan tsarinku na musamman ya dace da bukatunku. Bari mu yi amfani da umarnin CREATE DATABASE don kafa database ɗinmu:

Ƙaddara DATABASE ma'aikacin

Yi la'akari da mahimmanci da aka yi amfani da shi a cikin misali a sama. An yi amfani da shi a tsakanin masu shirye-shirye na SQL don amfani da manyan haruffa don kalmomi na SQL kamar "CREATE" da "DATABASE" yayin amfani da duk haruffan ƙananan haruffan sunayen masu amfani da sunaye kamar sunan "ma'aikata". Wadannan tarurruka suna ba da damar sauƙi.

Ci gaba da karanta wannan koyawa yayin da muka kirkiro Tables don database.

Ƙarin Ilimi

Idan kuna son ƙarin koyo game da Harshen Sakamakon Structured , karanta Gabatarwa zuwa ga SQL ko shiga don samun kyautar e-mail na Learning Learning kyauta.

Yanzu da muka tsara da kuma ƙirƙirar bayananmu, mun shirya don fara samar da tebur uku da aka yi amfani da su don adana bayanan ma'aikatan kamfanin XYZ Corporation. Za mu aiwatar da Tables da muka tsara a cikin ɓangaren baya na wannan koyawa.

Samar da Saitin Mu na farko

Teburinmu na farko ya ƙunshi bayanan sirri ga kowane ma'aikacin kamfaninmu. Muna bukatar mu hada da sunan ma'aikacin, albashi, ID, da kuma manajan. Kyakkyawan tsari ne don rarrabe sunayen karshe da na farko a cikin bangarori daban daban don sauƙaƙe bincike da tsarawa a nan gaba. Har ila yau, za mu ci gaba da lura da manajan kowane ma'aikacin ta hanyar sakawa da bayanin ma'aikaciyar mai kulawa a kowane rikodin ma'aikaci. Bari mu fara la'akari da kwamfutar ma'aikata da ake bukata.

Bayanan RahotanniYa sanya halayen mai sarrafa ID ga kowane ma'aikaci. Daga samfurin samfurin da aka nuna, za mu iya ƙayyade cewa Sue Scampi ne mai sarrafa duka Tom Kendall da John Smith. Duk da haka, babu wani bayani a cikin database a kan mai sarrafa Sue, kamar yadda NULL shigarwa a cikin jere.

Yanzu za mu iya amfani da SQL don ƙirƙirar tebur a cikin bayanan ma'aikatanmu. Kafin muyi haka, bari mu tabbatar da cewa muna cikin matattun bayanai ta hanyar samar da umarnin USE:

Amfani da ma'aikatan;

A madadin, "ma'aikatan DATABASE"; umarni zai yi wannan aikin. Yanzu za mu iya dauka kallon SQL umurnin amfani da su don ƙirƙirar ma'aikatanmu tebur:

BABI TABLE ma'aikata (ma'aikata INTEGER BA NULL, sunan mai suna VARCHAR (25) BA NULL, sunan farko VARCHAR (25) BA NULL, rahoton INTEGER NULL);

Kamar yadda misali na sama, lura cewa yarjejeniyar shirye-shiryen ya nuna cewa muna amfani da dukkan harufan haruffa don kalmomi na SQL da ƙananan haruffa don ginshiƙan mai suna da kuma tebur. Umurnin da ke sama zai iya zama da damuwa a farkon, amma akwai ainihin tsari mai sauƙi a baya. Ga ra'ayayyen ra'ayi wanda zai iya ɓoye abubuwa a bit:

CREATE TABLE allo_name (sunayen attribut_name datatype, ..., sunayen attribute_name datatype);

Halayen da Bayanan Bayanan

A cikin misali na baya, sunan launi yana ma'aikata kuma mun haɗa da halayen hudu: ma'aikata, sunan mai suna, sunan farko, da kuma rahoto. Wannan datatype yana nuna irin bayanin da muke so a adana a kowane filin. ID na ma'aikaci mai sauki ne, don haka za mu yi amfani da INTEGER datatype don aikin ma'aikata da kuma rahotanni. Sunan ma'aikatan za su kasance nau'in haruffa na tsawon tsayi kuma ba sa tsammanin kowane ma'aikaci yana da sunan farko ko na karshe fiye da haruffa 25. Saboda haka, zamu yi amfani da nauyin VARCHAR (25) don waɗannan filayen.

NULL Lambobin

Zamu iya saka ko NULL ko BA NULL a cikin zaɓin zaɓi na bayanin CREATE. Wannan kawai yana fada wa database ko an yarda da dabi'un NULL (ko komai) don wannan halayyar lokacin daɗa layuka zuwa ga bayanai. A cikin misalinmu, sashen HR yana buƙatar cewa an adana ID da cikakken suna don kowane ma'aikaci. Duk da haka, ba kowane ma'aikaci yana da manajan - Babban Shugaba ba rahotanni ga kowa ba! - saboda haka mun yarda da shigarwar NULL a wannan filin. Ka lura cewa NULL shine ƙimar tsohuwar kuma ƙetare wannan zaɓin zai ba da izinin NULL ga alama.

Gina Tables masu Sauke

Yanzu bari mu dubi yankuna. Daga duba wannan bayanan, yana nuna cewa muna buƙatar adana lamba da kuma igiyoyi guda biyu masu tsayi. Kamar yadda muka nuna misali, ba mu tsammanin ID na Yanki don cinye fiye da haruffa 25. Duk da haka, wasu daga cikin yankunanmu na da sunaye mafi yawa, saboda haka za mu fadada tsawon lokacin da wannan alamar ta kasance zuwa haruffa 40. Bari mu dubi SQL daidai:

Sake TABLE yankuna (yankin ƙasa INTEGER BAYAN NULL, ƙasa Description VARCHAR (40) BA NULL, yankin VARCHAR (25) BA NULL);

A ƙarshe, za mu yi amfani da tebur ma'aikatan ma'aikata don adana dangantaka tsakanin ma'aikata da yankuna. Ana adana cikakken bayani game da kowane ma'aikaci da ƙasa a ɗakunanmu biyu na baya. Saboda haka, muna buƙatar adana lambobin lambobi biyu a wannan tebur. Idan muna buƙatar fadada wannan bayanin za mu iya amfani da JOIN a cikin sharuɗɗan zaɓi na bayanai don samun bayani daga tebur masu yawa. Wannan hanyar adana bayanai yana rage redundancy a cikin bayananmu kuma yana tabbatar da amfani da sararin samaniya a kan ɗakunan ajiyar mu. Za mu rufe umarnin JOIN a cikin zurfin koyawa a gaba. A nan ne lambar SQL don aiwatar da teburinmu na karshe:

Sake TABLE ma'aikata (ma'aikata INTEGER BA NULL, ƙasashen waje INTEGER BA NULL);

Sha'anin Ma'aikata na SQL ya ba da damar canza tsarin Tsarin Bayanan Halitta

Idan kun kasance mai mahimmanci a yau, kuna iya lura cewa mun "ba zato ba tsammani" ya bar ɗaya daga cikin abubuwan da ake buƙata a yayin aiwatar da ɗakunan kwamfutarmu. Ma'aikatar HRYZ ta Kamfanin HR ta bukaci bayanin bayanin albashin ma'aikaci na bayanai kuma mun yi watsi da samar da wannan a cikin layukan da muka gina.

Duk da haka, duk ba'a rasa. Zamu iya amfani da umurnin ALTER TABLE don ƙara wannan halayen zuwa ga data kasance data kasance. Muna so mu adana albashi a matsayin adadin lamba. Rubutun yana da kama da wannan dokar CREATE TABLE, a nan shi ne:

SANTA TABLE ma'aikata ADD albashi INTEGER NULL;

Lura cewa mun kayyade cewa an ƙyale dabi'u NULL don wannan alamar. A mafi yawan lokuta, babu wani zaɓi lokacin daɗa wani shafi zuwa layin da ke ciki. Wannan shi ne saboda gaskiyar cewa tebur riga ya ƙunshi layuka ba tare da shigarwa ga wannan alamar ba. Saboda haka, DBMS ta saka ta atomatik a NULL darajar don cika kullun.

Kuma wannan yana kunshe da kullun mu a cikin tsarin SQL da kuma tsarin tsari. Bincika sau da yawa don sababbin sabbin abubuwa a cikin jerin darussan SQL. Idan kuna son tunatarwar e-mail lokacin da aka kara sabbin abubuwa a shafin yanar gizo game da Databases, tabbas ku biyan kuɗi zuwa kashin mu!