Kuumba Database
Wakagadzirira kutanga kuumba databases uye matafura neLimi yeKurongwa Mutauro ? Muchikamu chino, tinotsvaga nzira yekuumba matafura nema CREATE DATABASE uye CREATE TABLE mirairo. Kana uri mutsva kuSQL, unogona kuda kuongorora SQL Basics yenyaya yako kutanga.
Business Requirements
Tisati tagara pasi pa keyboard, tinofanira kuve nechokwadi chokuti tine kunzwisisa kwakasimba kwezvinodiwa nevatengi. Ndeipi nzira yakanakisisa yekuwana iyi kunzwisisa? Kutaura nemutengi, hongu! Mushure mekugara pasi ne XYZ's Human Resources Director, takadzidza kuti ihotera yekutengesa widget uye inonyanya kufarira mukutsvaga ruzivo pamusoro pevashandi vavo vanotengesa.
XYZ Corporation inoparadzanisa kushambadzira kwayo kumabvazuva nekumadokero kwezvikamu, imwe neimwe iyo yakakamurwa kuva nharaunda dzakawanda dzakaputirwa nekutengesa reps. Dhipatimendi reH HR inoda kuongorora nharaunda yakavharwa nemushandi wega wega pamwe chete nemashoko ese emubhadhari wevashandi uye sarudzo yepamusoro. Kuti tikwanise kuita izvi, takagadzira dheta rekushandisa rine matafura matatu, akaratidzwa muChiratidziro cheMiti-Chirongwa pane peji ino.
Kusarudza Database Database
Takasarudza kushandisa database yekushandura (kana DBMS) iyo yakavakirwa paItructured Query Language (SQL). Nokudaro, yese yedhesi redu uye zvigadziro zvekusika zvinyorwa zvinofanira kunyorwa neA standard SQL mupfungwa.
Sekuwedzera kubatsirwa, uchishandisa ANSI-yakakwana SQL ichaita kuti mirairo iyi ishande pane chero DBMS inotsigira SQL yakawanda , kusanganisira Oracle uye Microsoft SQL Server. Kana usina kusarudza nzvimbo ye database yako ikozvino, iyo nyaya Database Database Options inokutungamirira kuburikidza nekusarudza.
Kuumba Database
Nhanho yedu yekutanga ndeyokusika dhesiyo pachayo. Nzvimbo dzakawanda dzehurongwa hwekuchengetedzwa kwemashoko dzinopa maitiro ezvimwe maitiro ekugadzirisa zvigadziro zvemashoko echinyorwa ichi, asi database yedu inongobvumira kushandiswa kusinganzwisisiki kwed database. Sezvo nemitemo yedu yese, unogona kuda kutarisa mabhuku eDBMS yako kuti uone kana mamwe mazamu akafambiswa anotsigirwa nehutano hwako anosangana zvaunoda. Ngatishandise murairo we CREATE DATABASE kuisa database yedu:
VADZA VATATASI vashandi Tora ruzivo rwakanyanya rwemari inoshandiswa mumuenzaniso wepamusoro. Izvo zvinowanzoitwa pakati pevaridzi veSQL kushandisa mashizha makuru eSQL mazwi akadai se "CREATE" uye "DATABASE" pavanenge vachishandisa mazita ose ekuderera kwemashoko anotsanangurwa nevashandi sezita re "vashandi" dhesi yezita redu. Iyi magungano inopa nyore kuverenga.
Ramba uchiverenga iyi zvidzidzo sezvo isu tichigadzira tafura ye database yedu.
Kudzidza Zvimwe
Kana uchida kudzidza zvakawanda pamusoro peShoko Rakagadzirirwa Mutauro , verenga Sumo yeSQL kana kunyora kwekudzidza kwedu yega SQL yeE-mail yakasununguka.
Iye zvino zvatakagadzira uye takasika dhesi redu, takagadzirira kutanga kusika matafura matatu anoshandiswa kuchengetedza data yevashandi veXYZ Corporation. Tichave tichishandura mahwendefa atakagadzirwa muchikamu chekare chekudzidzisa.
Kuumba Yekutanga Tafura
Tafura yedu yekutanga inosanganisira data yega yega yevashandi vekambani yedu. Tinofanira kuisa zita reumwe wevashandi, mubhadharo, ID, uye maneja. Zvakanaka kugadzira tsika yekuparadzanisa mazita ekupedzisira uye ekutanga mumasangano akaenzana kuita nyore kutsvaga deta uye kuronga munguva yemberi. Uyewo, ticharamba tichiona mutariri wevashandi wega wega nekuisa dhipatimendi kumubhizimisi wevashandi vese mune imwe rekodhi yevashandi. Ngatitangei kutarisa tafura yebasa raidiwa.
The ReportsTo attribute inodzika ID yejaja yemubhadhari kune umwe neumwe mushandi. Kubva pane zvinyorwa zvinyorwa zvakaratidzwa, tinogona kuona kuti Sue Scampi ndiye mutungamiriri weTom Kendall naJohn Smith. Zvisinei, hapana ruzivo mune dhesiyo pane maneja waSue, sezvakaratidzwa neNULL kupinda mumutsara wake.
Iye zvino isu tinogona kushandisa SQL kuti tive tafura yevashandi vedu. Tisati taita kudaro, regai tione kuti isu tiri mundima yakarurama nekupa mutemo weUSE:
Zvimwe, "vashandi veDATABASE;" murayiro waizoita basa rimwe chete. Iye zvino tinokwanisa kutarisa mutemo we SQL unoshandiswa kuumba vashandi vedu tafura:
DENGA TABLE Vashandi (mushandi weInTEGER NOT NULL, zita rekupedzisira VARCHAR (25) HERE NULL, firstname VARCHAR (25) HERE NULL, reportsto INTEGER NULL);Sezvakaitwa nemuenzaniso wepamusoro, tarisa kuti gungano rekugadzirira rinoreva kuti tinoshandisa tsamba dzose dzeSQL mazwi makuru uye mabhesi ekuderera kwevashandisi vanonzi ma column and tables. Murairo wepamusoro unogona kunge wakaita sekuvhiringidza pakutanga, asi pane chaizvo chimiro chiri nyore shure kwacho. Heino mhedziso yakawanda inogona kujekesa zvinhu zvishoma:
DENGA TABLE table_name (attribute_name zvinyorwa zvepa datatype, ..., attribute_name zvinyorwa zvepa datatype);Unhu uye Dhiyabhorosi
Mumuenzaniso wekare, zita retafura ndiro vashandi uye isu tinosanganisira maitiro mana: mushandi, lastname, firstname, uye reportsto. Iyo datatype inoratidza rudzi rwemashoko atinoda kuchengeta mundima imwe neimwe. Mushandi weDhiyabhorosi isiri nhamba yakakwana, saka tichashandisa dhidhiyo yeInTEGER yebasa revashandi uye yepa reportsto field. Vashandi vemazita vachave masimbi ehutano hwehurefu hwakasiyana uye hatitarisiri chero mushandi wekutanga ane zita rekutanga kana rekupedzisira kwenguva refu kupfuura makumi maviri nemakumi maviri. Saka, tichashandisa vARCHAR (25) rudzi rweminda iyi.
NULL Values
Tinogonawo kutsanangura NULL kana NOT NULL mundima yekusarudza mushoko re CREATE. Izvi zvinongotsanangurira dheseti kana NULL (kana isina chinhu) maitiro inobvumirwa kune izvozvi paunowedzera mitsara kune database. Muchiitiko chedu, dhipatimendi reH HR inoda kuti ID yevashandi uye zita rakakwana richengetwe kune umwe neumwe mushandi. Zvisinei, havasi vashandi vose vane maneja - Mukuru weGurukota anozivisa kune munhu! - saka tinobvumira NULL kupinda mumunda iwoyo. Cherechedza kuti NULL ndiyo yakakosha kukosha uye kusasiya iyi sarudzo ichabvumira zvachose NULL maitiro emunhu.
Kuvaka Rambai Tables
Zvino ngatitarisei munharaunda tafura. Kubva nekukurumidza kuona iyi data, zvinoratidzika kuti tinoda kuchengeta integer uye tsvimbo mbiri dzakasiyana-siyana. Sezvo nemuenzaniso wedu wekare, hatitarisiri Nharaunda yeNharaunda kuti tidye zvinopfuura makumi maviri nemasere. Zvisinei, dzimwe nharaunda dzedu dzine mazita akawanda, saka tichawedzera urefu hwakagamuchirwa hwechirevo ichocho kumashiri makumi mana. Ngatitarisei SQL yakafanana:
DZENGA DZIMWE nzvimbo (ndima) INTEGER NOT NULL, ndima Inotsanangurwa VARCHAR (40) NOT NULL, regionid VARCHAR (25) HERE NULL);Pakupedzisira, tichashandisa tafura yeH employyeeTerritories kuchengeta hukama pakati pevashandi nenzvimbo. Tsanangudzo yakajeka pamusoro pemubati wega wega nendima yakachengetwa mumatafura edu apfuura maviri. Nokudaro, isu chete tinoda kuchengeta nhamba mbiri mbiri dzekutsvaga mutafura iyi. Kana tichida kuwedzera ruzivo urwu tinogona kushandisa JOIN mumitemo yedu yekusarudza data kuti tive nemashoko kubva pamatafura akawanda. Iyi nzira yekuchengetedza dambudziko inoderedza redundancy mu database yedu uye inogadzirisa kushandiswa kwakakwana kwenzvimbo pane zvatinotarisira. Tichavhara murairo weJOIN mu-zvakadzika mune zvidzidzo zvemangwana. Heinoi SQL code kuti tishandise tafura yedu yokupedzisira:
DZENGA TABLE employeterritories (mushandi MUNTEGER NOT NULL, nharaunda INTEGER NOT NULL);Mhizha SQL Inopa Kushandura Kugadzirwa kweDhamasiko Mushure mekusika
Kana iwe unonyanya kuchenjera nhasi, ungadaro wakacherechedza kuti isu "takanganisa" takasiya imwe yezvigadziro zvinoshandiswa pakushandura tafura dzedu dzepa database. XYZ Corporation's HR Director yakakumbira kuti ruzivo rwemashandiro ehutori hwevashandi vepa database uye isu tisina kurega kupa izvi mumatafura epa database yatakagadzira.
Zvisinei, zvose hazvina kurasikirwa. Tinogona kushandisa mutemo we ALTER TABLE kuti tiwedzere chirevo ichi kune database yedu iripo. Tinoda kuchengeta mubhadharo sechinhu chakanaka. Izwi rinonyanya kufanana neiro re CREATE TABLE command, heino kuti:
Cherechedza kuti takataura kuti tsika dzeNULL dzakabvumirwa nekuda kwechikonzero ichi. Muzviitiko zvakawanda, hapana chaunosarudza paunowedzera chinyorwa kune tafura iripo. Izvi zvinokonzerwa nekuti tefura iripo ine mitsara isina chekupinda kwechikonzero ichi. Nokudaro, DBMS inowanzoisa mukana weNULL wokuzadza chinhu chisina basa.
Uye iyo inoputira kutarisa kwatinoita SQL deta uye yekugadzira tafura. Tarisai kazhinji pane zvitsva zvitsva muSQL yedu tutorial series. Kana iwe uchida e-mail yekuyeuchidza apo zvinyorwa zvitsva zvinowanikwa kuZvaDatabases site, iva nechokwadi chekunyora kune tsamba yedu!