Kupanga Database
Kodi mwakonzeka kuyamba kupanga ma database ndi matebulo ndi Language Structured Query ? M'nkhaniyi, tikufufuzira njira yopanga matebulo palimodzi ndi CREATE DATABASE ndi CREATE TABLE malamulo. Ngati muli atsopano ku SQL, mungakonde kuwonanso nkhani yathu yoyamba ya SQL Basics .
Zofuna za bizinesi
Tisanakhale pansi pa khibhodi, tifunika kuonetsetsa kuti tili ndi chidziwitso chokwanira cha zomwe makasitoma amafuna. Kodi njira yabwino yopezera zimenezi ndi iti? Kulankhula kwa kasitomala, ndithudi! Titakhala pansi ndi Director of Human Resources ya XYZ, taphunzira kuti ndi kampani yogulitsa widget ndipo akufunitsitsa kufufuza zambiri pa ogulitsa awo.
XYZ Corporation imagawaniza malonda awo kumadera akum'maŵa ndi kumadzulo, omwe amagawidwa m'madera ambiri opangidwa ndi wogulitsa. Dipatimenti ya HR ingakonde kufufuza gawo limene wogwira ntchito aliyense ali nalo komanso momwe malipiro ake amathandizira. Kuti tikwaniritse zofunikirazi, tapanga ma database omwe ali ndi matebulo atatu, omwe akuwonetsedwa mu Chithunzi Chachiyanjano pa tsamba ili.
Kusankha Chipangizo Chokhazikika
Tatsimikiza kugwiritsa ntchito njira yosungiramo ma database (kapena DBMS) yomwe imamangidwa pa Language Structured Query (SQL). Choncho, zonse zathu zachinsinsi ndi zolemba zowonjezera ma tebulo ziyenera kulembedwa ndi ANSI SQL yoyenera m'maganizo.
Monga phindu lowonjezera, kugwiritsa ntchito ANSI-zogwirizana ndi SQL kuonetsetsa kuti malamulo awa adzagwira ntchito pa DBMS iliyonse yomwe imathandiza SQL , monga Oracle ndi Microsoft SQL Server. Ngati simunasankhe nsanja yanu yachinsinsi pano, buku la Database Software Options likukutsogolerani.
Kupanga Database
Choyamba choyamba ndikupanga database. Zambiri zamakono zothandizira maofesiwa zimapereka njira zambiri zomwe zingasinthidwe pazitsulo izi, koma mndandanda wathu umalola kuti pakhale zolemba zambiri. Monga ndi malamulo athu onse, mungafune kuwona zolemba za DBMS yanu kuti mudziwe ngati zingapo zothandizira pazinthu zanu zogwirizana ndi zosowa zanu. Tiyeni tigwiritse ntchito lamulo la CREATE DATABASE kuti tikonze database yathu:
LENGANI OTHANDIZA ANTHU Tchulani zapadera zomwe zimagwiritsidwa ntchito mu chitsanzo chapamwamba. Zimagwiritsidwa ntchito pakati pa olemba SQL kugwiritsa ntchito makalata akuluakulu a SQL monga "CREATE" ndi "DATABASE" pogwiritsa ntchito makalata ochepetsera maina omwe amatchulidwa ndi ogwiritsa ntchito monga dzina la "antchito". Misonkhano imeneyi imathandiza kuti anthu aziwerenga mosavuta.
Pitirizani kuŵerenga phunziro ili pamene tikupanga matebulo a makadi athu.
Kuphunzira zambiri
Ngati mukufuna kudziwa zambiri za Language Structure Query , werengani Mawu Oyamba ku SQL kapena lembani pa maphunziro athu omasuka a SQL e-mail.
Tsopano popeza tinapanga ndi kulenga deta yathu, takhala okonzeka kuyamba kupanga matebulo atatu ogwiritsira ntchito deta ya a XYZ Corporation. Tidzakhala tikugwiritsa ntchito matebulo omwe tapanga m'gawo lapitalo la phunziroli.
Kupanga Pepala Lathu Loyamba
Gome lathu loyamba lili ndi deta ya munthu aliyense wa kampani yathu. Tiyenera kuphatikiza dzina la wogwira ntchito, malipiro, chidziwitso, ndi wothandizira. Ndizokonzekera bwino kuti mulekanitse maina otsiriza ndi oyambirira m'magawo osiyanasiyana kuti musamafufuze kufufuza ndi kusanthula mtsogolo. Ndiponso, tidzakhala ndikudziŵika bwino ndi ofesi ya wogwira ntchito aliyense mwa kuika chilembetsero kwa ID ya wogwira ntchitoyo mu rekodi iliyonse ya ogwira ntchito. Tiyeni tiyambe kuyang'ana pa tebulo la ogwira ntchito lomwe mukufuna.
The ReportsTwo chikhalidwe chimagulitsa ID ya abwana kwa wogwira ntchito aliyense. Kuchokera muzitsanzo zomwe tawonetsa, tingathe kudziwa kuti Sue Scampi ndi mtsogoleri wa onse awiri Tom Kendall ndi John Smith. Komabe, palibe chidziwitso mu databata pa mtsogoleri wa Sue, monga momwe akusonyezera ndi kulemba kwa NULL mumzere wake.
Tsopano tikhoza kugwiritsa ntchito SQL kuti tipeze tebulo m'magulu athu ogulitsira. Tisanachite izi, tiyeni tionetsetse kuti tili mu deta yolondola pogwiritsa ntchito lamulo la USUMBA:
Kapena, "antchito a DATABASE;" lamulo likhoza kuchita ntchito yomweyo. Tsopano tikhoza kuyang'ana lamulo la SQL lomwe limagwiritsidwa ntchito popanga antchito athu patebulo:
Pangani ogwira ntchito (wogwira ntchito INTEGER NOT NULL, dzina lake VARCHAR (25) NOT NULL, firstname VARCHAR (25) NOT NULL, report INTEGER NULL);Monga momwe tawonera pamwambapa, pulogalamu yamakonzedwe a msonkhano ikuti timagwiritsa ntchito makalata akuluakulu a SQL mawu achinsinsi ndi makalata ochepetsetsa omwe ali ndi ndondomeko ndi ma tebulo. Lamulo lapamwamba likhoza kuwoneka wosokoneza poyamba, koma pali kwenikweni zosavuta kumbuyo kwake. Pano pali mawonekedwe omwe angawononge zinthu pang'ono:
LENGANI ZINTHU table_name (options_namename datatype, ..., options_name mawonekedwe deta);Makhalidwe ndi Mitundu ya Data
Mu chitsanzo choyambirira, dzina la tebulo ndi antchito ndipo ife timaphatikizapo makhalidwe anai: wogwira ntchito, wotchulidwa dzina, firstname, ndi reportsto. Dongosololi limasonyeza mtundu wa zomwe tikufuna kusunga m'munda uliwonse. Chizindikiro cha ogwira ntchito ndi nambala yeniyeni yosavuta, choncho tidzatha kugwiritsa ntchito deta ya INTEGER kwa munda wa ogwira ntchito komanso gawo lolemba. Maina a ogwira ntchito adzakhala zida zamtundu wautali wosasinthasintha ndipo sitiyembekeza kuti wogwira ntchito aliyense akhale ndi dzina loyamba kapena lomalizira kuposa malemba 25. Choncho, tigwiritsa ntchito mtundu wa VARCHAR (25) pazinthu izi.
Makhalidwe a NULL
Tingathenso kutchulapo NULL kapena NOT NULL mu gawo la zosankha za mawu CREATE. Izi zimangowonjezera deta ngati zilembo za NULL (kapena zopanda kanthu) zimaloledwa pa chikhalidwe chimenecho powonjezera mizera ku deta. Mu chitsanzo chathu, Dipatimenti ya HR imafuna kuti ID ya antchito ndi dzina lathunthu lizisungidwe kwa wogwira ntchito aliyense. Komabe, osati wogwira ntchito aliyense ali ndi abwana - Mtsogoleri wamkulu samawauza aliyense! - kotero timalola zolemba za NULL m'munda umenewo. Dziwani kuti NULL ndiyotheka mtengo ndipo kutaya njirayi kumalola mwatsatanetsatane zoyenera za NULL za chikhumbo.
Kumanga Masamba Otsalira
Tsopano tiyeni tiyang'ane ku madera apakati. Kuyang'ana mofulumira pa deta iyi, zikuwoneka kuti tikuyenera kusunga nambala ndi zingwe ziwiri zautali zosasinthasintha. Mofanana ndi chitsanzo chathu chakale, sitiyembekezera kuti ID ya m'dera lanu idye malemba oposa 25. Komabe, ena mwa magawo athu ali ndi mayina akutali, kotero tidzakulitsa kutalika kwa chikhalidwe chimenecho ku zilembo 40. Tiyeni tiyang'ane pa SQL yofananayo:
DZIWANI ZINTHU madera (gawo) INTEGER NOT NULL, gawo lotanthauzira VARCHAR (40) NOT NULL, regionid VARCHAR (25) SANKHANI);Potsiriza, tigwiritsa ntchito tebulo la EmployeeTerritories kuti tisunge ubale pakati pa antchito ndi magawo. Zindikirani zambiri pa ogwira ntchito ndi gawo lonse zomwe zasungidwa m'matawuni athu awiri apitalo. Choncho, tingofunika kusunga nambala ziwiri zodziwika mu tebulo ili. Ngati tikufuna kufotokoza zambirizi titha kugwiritsa ntchito JOIN m'malamulo athu osankhidwa ndi deta kuti tipeze zambiri kuchokera ku matebulo angapo. Njira iyi yosungira deta imachepetsa redundancy mu deta yathu ndipo imatithandiza kugwiritsa ntchito malo osungirako. Tidzaphimba lamulo la JOIN mwakuya mu phunziro la mtsogolo. Pano pali SQL code kuti tigwiritse ntchito tebulo lathu lotsiriza:
DZIWANI ZOKHUDZA ntchito zogwirira ntchito (wogwira ntchito INTEGER NOT NULL, gawo la INTEGER NOT NULL);Njira SQL Imapereka Kusintha Zomwe Zinalembedwa Pambuyo pa Chilengedwe
Ngati muli ovuta lero, mwina mwawona kuti "mwachisawawa" tasiya zofunikira zomwe timapanga tikamagwiritsa ntchito matebulo athu. XYZ Corporation wa HR Director adafunsa kuti malipoti a malipiro a ogwira ntchito pa data database ndipo ife talephera kupereka izi mu matebulo a deta .
Komabe, zonse sizinawonongeke. Tingagwiritse ntchito lamulo la ALTER TABLE kuti tiwonjezere chidziwitso ichi kwa deta yathu yomwe ilipo. Tikufuna kusungira malipiro ngati mtengo wofunika. Mawu omasulira ndi ofanana ndi a CREATE TABLE lamulo, apa ndi:
Onani kuti tafotokoza kuti zikhalidwe za NULL zimaloledwa pamalingaliro awa. Nthaŵi zambiri, palibe chochita powonjezera khola ku tebulo lomwe liripo. Izi ndi chifukwa chakuti tebulo ili ndi mizere yomwe ilibe chilolezo cha izi. Choncho, DBMS imayika mwachindunji mtengo wa NULL kuti udzaze chopanda pake.
Ndipo izo zimapangitsa kuyang'ana kwathu pa SQL database ndi ndondomeko kulenga ndondomeko. Yang'anani kawirikawiri zowonjezera zatsopano m'masewera athu a SQL. Ngati mungafune kukumbutsani ma imelo pamene nkhani zatsopano zowonjezedwa pa tsamba la Zosungidwa, zitsimikizirani kuti mulembere kalata yathu.