Zomwe Zimayendera SQL

Phunzirani za DDL, DML ndi JOINs

Language Structure Query ndi imodzi mwa zomangamanga za zomangamanga zamakono zamakono. SQL imatanthawuza njira zomwe zimagwiritsidwa ntchito polenga ndi kugwiritsira ntchito zida zokhudzana ndi zibwenzi pamapulatifomu onse akuluakulu. Poyamba, chilankhulocho chimawoneka chowopsya komanso chovuta, koma sizovuta zonse.

Kufotokozera izi kwa zikhazikitso za SQL kumafuna mwachidule zina mwa malamulo akulu omwe amagwiritsa ntchito kulenga ndikusintha mazenera.

About SQL

Kutchulidwa kolondola kwa SQL ndi nkhani yotsutsana m'mudzi wa deta. Muyezo wake wa SQL, American National Standards Institute inalengeza kuti kutchulidwa kwachindunji ndi "mzere". Komabe, akatswiri ambiri a zamasamba adatenga mawu otchulidwa "squel". Chisankho ndi chanu.

SQL imapezeka m'njira zambiri. Malemba oracle amagwiritsa ntchito PL / SQL yake. Microsoft SQL Server imagwiritsa ntchito Transact-SQL. Zosintha zonsezi zimachokera pa mndandanda wa ANSI SQL. Mawu oyambirirawa amagwiritsa ntchito malamulo a SQL ANSI ovomerezeka omwe amagwira ntchito pazinthu zamakono zamakono zamagulu.

DDL ndi DML

Malamulo a SQL angagawidwe m'zinenero ziwiri zazikuluzikulu. Chilankhulo cha Deta (DDL) chili ndi malamulo omwe amagwiritsidwa ntchito polenga ndi kuwononga zinthu ndi zinthu zina. Pambuyo pazomwe maziko a deta akufotokozera ndi DDL, otsogolera ndi ogwiritsa ntchito deta akhoza kugwiritsa ntchito Chilankhulo Chogwiritsira Ntchito (DML) kuti aike, atenge ndi kusintha ma data omwe alimo.

Mafotokozedwe a Chilankhulo cha Deta

Chilankhulo Chakutanthauzira Data chikugwiritsidwa ntchito popanga ndi kuwononga zida ndi zolemba zinthu. Malamulo awa amagwiritsidwa ntchito makamaka ndi oyang'anira detalase panthawi yokonza ndi kuchotsa magawo a polojekiti yachinsinsi. Pano pali kuyang'ana pa kapangidwe ndi kugwiritsa ntchito malamulo anayi a DDL:

LENGANI. Kuyika mawonekedwe apamwamba pa kompyuta kumakupangitsani kuti mupange ndi kuyang'anira mauthenga ambiri odziimira. Mwachitsanzo, mungafunike kusunga deta ya makasitomala anu ku dipatimenti yanu yogulitsa malonda ndi anthu ogwira ntchito ku dipatimenti yanu ya HR. Lamulo la CREATE limagwiritsidwa ntchito kukhazikitsa lirilonse lazomwezi pa nsanja yanu. Mwachitsanzo, lamulo:

DZIWANI OTHANDIZA AKULU

imapanga ndondomeko yopanda kanthu yotchedwa "antchito" pa DBMS yanu. Pambuyo popanga deta, sitepe yotsatira ndiyo kupanga matebulo omwe ali ndi deta. Malamulo ena a CREATE angagwiritsidwe ntchito pazinthu izi. Lamulo:

LENGANI ZIWIRI person_info (choyamba_name char (20) osati null, last_name char (20) osati null, employee_id int osati null)

imakhazikitsa tebulo lotchedwa "personal_info" mu deta yamakono. Mu chitsanzo, tebulo ili ndi zikhumbo zitatu: woyamba_name, last_name ndi employee_id pamodzi ndi zina zowonjezera.

Gwiritsani ntchito. Lamulo logwiritsira ntchito limakulolani kufotokoza deta yomwe mukufuna kugwira nayo mkati mwa DBMS yanu. Mwachitsanzo, ngati panopa mukugwira ntchito yosungirako malonda ndipo mukufuna kupereka malamulo ena omwe angakhudze mzere wa ogwira ntchito, onaninso ndi lamulo lotsatira la SQL:

Gwiritsani ntchito antchito

Ndikofunika kuti muzindikire nthawi zonse zomwe mukugwira ntchito musanapereke malamulo a SQL omwe amayendetsa deta.

ALTER. Mukadapanga tebulo mkati mwachinsinsi, mungafune kusintha tanthauzo lake. Lamulo la ALTER limakulolani kusintha kusintha kwa tebulo popanda kuchotsa ndi kubwezeretsanso. Taonani lamulo ili:

ALTER TABLE person_info ADD ndalama zothandizira

Chitsanzo ichi chikuwonjezera chiyero chatsopano pa tebulo laumwini_info-malipiro a antchito. Ndemanga ya "ndalama" imatanthawuza kuti malipiro a antchito akusungidwa pogwiritsa ntchito madola ndi masentimenti. Potsirizira pake, mawu akuti "null" amagwiritsira ntchito deta kuti ndi bwino kuti malowa asakhale ndi phindu kwa wogwira ntchito iliyonse.

DROP. Lamulo lomaliza la Chidziwitso cha Definition Data, DROP, limatithandiza kuchotsa zinthu zonse kuchokera ku DBMS yathu. Mwachitsanzo, ngati tikufuna kuchotsa mwangwiro tebulo laumwini lomwe tinalilenga, tingagwiritse ntchito lamulo ili:

SANKANI MASABATA anu_info

Mofananamo, lamulo ili m'munsi likugwiritsidwa ntchito pochotsa malo onse ogwira ntchito:

Ogwira ntchito a DROP DATABASE

Gwiritsani ntchito lamulo ili mosamala. Lamulo la DROP limachotsa zipangizo zonse za deta kuchokera ku database yanu. Ngati mukufuna kuchotsa zolemba zanu, gwiritsani ntchito lamulo LA DELETE la Chilankhulo Chogwiritsira Ntchito.

Kugwiritsa Ntchito Dongosolo Malamulo a Chilankhulo

Chilankhulo Chosokoneza Deta (DML) chikugwiritsidwa ntchito kupeza, kuika ndi kusinthira chidziwitso. Malamulo awa amagwiritsidwa ntchito ndi onse ogwiritsa ntchito deta pogwiritsa ntchito ma database.

ONANI. Lamulo la INSERT mu SQL limagwiritsidwa ntchito kuwonjezera zolemba ku tebulo lomwe liripo. Kubwereranso ku chitsanzo chaumwini kuchokera ku gawo lapitalo, taganizirani kuti dipatimenti yathu ya HR ikufunikira kuwonjezera antchito atsopano ku deta yake. Mungagwiritse ntchito lamulo lofanana ndi ili:

ONANI MALAMULO a munthu_info ('bart', 'simpson', 12345, $ 45000)

Onani kuti pali mfundo zinayi zimene zimayikidwa pazomwe zilipo. Izi zikugwirizana ndi zikhumbo za tebulo mu dongosolo lomwe iwo amalembedwa: choyamba_name, last_name, employee_id ndi malipiro.

SANKHANI. Lamulo la SELECT ndilo lamulo lofala kwambiri pa SQL. Amalola anthu ogwiritsa ntchito malo osungirako malonda kuti adziwe zomwe iwo akufuna kuchokera kuntchito yogwirira ntchito. Tawonani zitsanzo zingapo, ndikugwiritsanso ntchito tebulo lapamtima lanu kuchokera ku malo ogwira ntchito.

Lamulo lowonetsedwa pansi likutenga zonse zomwe zili mu tebulo la person_info. Onani kuti asterisk imagwiritsidwa ntchito monga wildcard mu SQL. Izi kwenikweni zimatanthauza "Sankhani zonse kuchokera pa tebulo la person_info."

SANKHANI * KUYAMBIRANA ndi person_info

Mwinanso, ogwiritsa ntchito angafune kuchepetsa makhalidwe omwe amachokera ku database. Mwachitsanzo, Dipatimenti ya Anthu Osowa Mankhwala ingafune mndandanda wa mayina otsiriza a ogwira ntchito onse mu kampaniyo. Lamulo lotsatira la SQL lingapeze chidziwitso chokhacho:

SELECT last_name kuchokera ku person_info

Lamulo la WHERE lingagwiritsidwe ntchito kuchepetsa zolemba zomwe zabwezedwa kwa iwo omwe amakwaniritsa ndondomeko yoyenera. Mtsogoleri wamkulu angakhale ndi chidwi chowongolera olemba onse ogwira ntchito kwambiri. Lamulo lotsatila likutenga zonse zomwe zili muwekha_nfo kwa ma rekodi omwe ali ndi mtengo wapatali kuposa $ 50,000:

CHISANKHO * KUCHOKERA pa person_info KODI PAMENE malipiro> $ 50000

PEZANI. Lamulo la UPDATE lingagwiritsidwe ntchito kusintha zinthu zomwe zili mkati mwa tebulo, kaya zambiri kapena payekha. Tangoganizani kuti kampaniyo imapatsa antchito onse ndalama zokwana 3 peresenti ya ndalama zowonjezera mu malipiro awo pachaka. Lamulo lotsatila la SQL lingagwiritsidwe ntchito mwamsanga kugwiritsa ntchito izi kwa antchito onse omwe amasungidwa mu database:

PHUNZITSANI munthu_info SET mphoto = malipiro * 1.03

Pamene wogwira ntchito watsopano Bart Simpson akuwonetsa ntchito pamwamba komanso kupitirira ntchito, oyang'anira akufuna kuwona zomwe akuchitazo ndi kukweza $ 5,000. Lembali LA WHERE lingagwiritsidwe ntchito pokonzekera Bart chifukwa cha izi:

PHUNZITSANI munthu_info SET mphotho = malipiro + $ 5000 PAMENE ntchito_id = 12345

TAYANI. Potsiriza, tiyang'ane lamulo la DELETE. Mudzapeza kuti mawu a lamuloli ali ofanana ndi malamulo ena a DML. Mwamwayi, lipoti lathu laposachedwa lapindula silinakwaniritse zoyembekeza ndipo Bart wosauka adachotsedwa. Lamulo LOCHOKERA ndi ndime WHERE lingagwiritsidwe ntchito kuchotsa mbiri yake pa tebulo la person_info:

TCHITANI KUYAMBA kuchokera ku person_info PAMENE ntchito_id = 12345

ZOYENERA

Tsopano popeza mwaphunzira zofunikira za SQL, ndi nthawi yopitilira ku chimodzi mwa mfundo zamphamvu zomwe chilankhulo chiyenera kupereka-ndondomeko ya JOIN. Mawu A JOIN amakulolani kuti muphatikize deta m'matawo angapo kuti mugwiritse ntchito bwino deta yambiri. Mawu awa ndi pamene mphamvu yeniyeni ya adiresi imakhala.

Kuti mufufuze kugwiritsa ntchito ntchito yofunika YOININ ntchito kuti muphatikize deta kuchokera pa matebulo awiri, pitirizani ndi chitsanzo pogwiritsira ntchito PERSONAL_INFO tebulo ndikuwonjezera tebulo yowonjezera. Ganizirani kuti muli ndi tebulo yotchedwa DISCIPLINARY_ACTION yomwe inalengedwa ndi mawu otsatirawa:

DZIWANI ZIWIRI chilango_action (action_id int osati null, employee_id int osati null, ndemanga char (500))

Gome ili liri ndi zotsatira za zochita zaulangizi pa antchito a kampani. Mudzazindikira kuti mulibe chidziwitso chilichonse chokhudza wogwira ntchito kupatula nambala ya antchito. N'zosavuta kulingalira zambiri zomwe mungafune kuphatikizapo mfundo kuchokera ku DISCIPLINARY_ACTION ndi PERSONAL_INFO matebulo.

Tangoganizani kuti mwakhala mukukonzekera malipoti omwe amalembetsa zoyenera kuchita kwa antchito onse omwe ali ndi malipiro oposa $ 40,000. Kugwiritsidwa ntchito kwa JOIN ntchito, mu nkhani iyi, ndi yowongoka. Titha kulandira uthengawu pogwiritsa ntchito lamulo ili:

SANKANI person_info.first_name, personal_info.last_name, disciplinary_action.comments FROM person_info, disciplinary_action WHERE personal_info.employee_id = disciplinary_action.employee_id NDI munthu_info.salary> 40000

Lembali limatchula ma tebulo awiri omwe tikufuna kuti tilowe nawo mu CHIYAMBO CHA CHIYAMBI ndikuphatikizapo ndemanga m'BUKU LA WHERE kuchepetsa zotsatira ku zolemba zomwe zogwirizana ndi malemba a ogwira ntchito ndikugwirizanitsa zofunikira za malipiro oposa $ 40,000.