SQL Zvinotarisirwa

Dzidza nezveDDL, DML neJOINs

The Structured Query Language ndechimwe chezvidzidzo zvinokosha zvekuvakwa kwemazuva ano. SQL inotsanangura nzira dzinoshandiswa kugadzira nekushandura zvinyorwa zvehukama pamazamu makuru makuru. Pakutanga, mutauro unogona kuita seunotyisa uye hwakaoma, asi hazvisi zvose zvakaoma.

Iri sumo yezvinokosha shure kweSQL rinotarisa pfupi mamwe emirairo mikuru inoshandiswa kugadzira nekugadzirisa databases.

Nezve SQL

Kutaura kwakarurama kweSQL inhau yakakavadzana mukati memharaunda ye database. Muchikamu chayo cheSQL, American National Standards Institute yakaratidza kuti kududzira kunoshandiswa kwemashoko ndeye "mutezo". Zvisinei, vazhinji vashanduri vezvenhoroondo vakatora shoko rekutaura slang "sequel." Sarudzo ndeyako.

SQL inouya mune zvakawanda zvinofadza. Oracle databases anoshandisa PL / SQL yaro. Microsoft SQL Server inoshandisa Transact-SQL. Zvose zvakasiyana-siyana zvakavakirwa pamhizha yeAnSI SQL. Iri sumo rinoshandisa ANSI-rinogamuchirika SQL mirairo inoshanda pane ipi zvayo yemazuva ano yekodhidhi yekodhidhi.

DDL uye DML

SQL mirayiridzo inogona kugoverwa kuva mbiri huru-mitauro. Iyo Deta Definition Mutauro (DDL) ine mirairo inoshandiswa kugadzira uye kuparadza zvinyorwa uye zvinyorwa zvezvinhu. Mushure mokunge sangano re database richirondedzerwa neDDL, vatungamiri vepa database uye vashandisi vanogona kushandisa Dhigirizhonga Dhimiro (DML) kuisa, kuwana uye kugadzirisa dhesi iri mukati maro.

Dhiyabhorosi Dhairimi Mutauro Wemitemo

Iyo Deta Definition Mutauro inoshandiswa kugadzira uye kuparadza zvinyorwa uye zvinyorwa zvezvinhu. Iyi mirairo inonyanya kushandiswa nevashandi vepa database pakusarudza nekugadzirisa zvikamu zveprojekti ye database. Pano tarisa chitarisiko uye kushandiswa kwemashoko mana eDDL anokosha:

DZENGA. Kuisa database yekugadzirisa pakombiyuta kunokubvumira kuti iwe ugadzire uye utungamirire huwandu hwemashoko akazvimirira. Semuenzaniso, iwe unogona kuchengeta database yevatengi vekutengesa kune dhipatimendi rako rekutengesa uye nheyo dzevashandi vehurumende yako HR. Chirevo che CREATE chinoshandiswa kugadzirisa chimwe chezviziviso zvepa platform yako. Somuenzaniso, murairo:

VADZIDZA VARI VANHU

inoita database isina chinhu inonzi "vashandi" pane DBMS yako. Mushure mokusika database, danho rinotevera ndokuumba matafura ane data. Imwe mhando yakasiyana ye CREATE command inogona kushandiswa nekuda kwechinangwa ichi. Murairo:

DENGA TABLE person_info (first_name char (20) kwete null, last_name char (20) kwete null, employee_id int kwete null)

inogadzira tafura ine zita rokuti "personal_info" mune databasi ino. Mumuenzaniso, tafura ine zvinhu zvitatu zvekutanga: zita_name, last_name uye employee_id pamwe nehumwe humwe ruzivo.

Shandisa. Mutemo weUSE unokubvumira kutsanangura dhesi rekuti iwe unoda kushanda nayo mukati meDBMS yako. Semuenzaniso, kana iwe ikozvino uri kushanda munyaya dzekutengesa uye uchida kubudisa mimwe mirairo inobata mazita evhareji yevashandi, taurirai neyotevera SQL murairo:

Shandisa vashandi

Zvakakosha kugara uchingwarira database yauri kushanda nayo usati watanga SQL mirairo iyo inoshandisa data.

ALTER. Kana wambosika tafura mukati me database, ungada kuchinja tsanangudzo yaro. Mutemo we ALTER unokubvumira kuita shanduko kune zvigadziri ta tafura pasina kuibvisa uye kuchidzorera zvakare. Cherechedzai murairo unotevera:

ALTER TABLE person_info ADD mari yemari pasina

Uyu muenzaniso unowedzera hutsva hutsva kune tafura yega_info-mushandi wemushandi. Nhauriro ye "mari" inotsanangura kuti mushandi wevashandi anochengetedzwa achishandisa madhora uye masentimita. Pakupedzisira, "null" keyword inotaurira dheseti kuti zvakakosha kuti munda uyu urege kuva nehuwandu kune chero munhu anopiwa.

DONHEDZA. Mutemo wokupedzisira weData Definition Mutauro, DROP, unotibvumira kuti tibvise zvinhu zvose zvebhuku kubva kuDBMS yedu. Somuenzaniso, kana tichida kubvisa zvachose tafura yega_info yatakagadzira, tingashandisa murairo unotevera:

DROP TABLE person_info

Saizvozvowo, murairo uri pasi apa unogona kushandiswa kubvisa tsvaga yese yevashandi:

DHOPOP DATABASE vashandi

Shandisa iri murairo nekufungisisa. Mutemo weDOPOP unobvisa zvikamu zvose zve data kubva kune database yako. Kana iwe uchida kubvisa zvinyorwa zvemunhu mumwe nomumwe, shandisa mutemo DELETE weLanguage Manipulation Language.

Dhigiridziro Dhiyabhorosi Mutauro Mutemo

Iyo Dhiyabhorosi Inoshandiswa Mutauro (DML) inoshandiswa kugadzirisa, kuisa nekugadzirisa ruzivo rwemashoko. Iyi mirairo inoshandiswa nevashandisi vese vekushandisa dhivhesi panguva yekushanda kweiyo database.

INSERT. Mutemo INSERT muSQL unoshandiswa kuwedzera zvinyorwa kune tafura iripo. Kudzokera kumunhu wega_info kubva muchikamu chakapfuura, fungidzira kuti dhipatimendi re HR yedu inoda kuwedzera mushandi mushandi kune database. Unogona kushandisa murairo wakafanana neyuyu:

DZIDZISA ZVIRI MUNYORI-maitiro ('bart', 'simpson', 12345, $ 45000)

Cherechedza kuti kune zvine maitiro anotsanangurwa kuti zvinyorwa. Izvi zvinowirirana nefurafura izvo zvinoenderana nehurongwa zvavakatsanangurwa: first_name, last_name, employee_id uye mubhadharo.

SELECT. Mutemo WOKUTEZA ndiyo mutemo unowanzoshandiswa muSQL. Iyo inobvumira vashandisi vepa database kuti vapezve ruzivo rwakananga rwavanoda kubva kune database yekushanda. Tarisa mimwe mienzaniso mishomanana, zvakare uchishandisa tafura yega_info kubva kune dhesi yevashandi.

Murairo unoratidzwa pasi apa unowana zvose zvese zviri mukati metafura yega_info. Cherechedza kuti asterisk inoshandiswa sa wildcard muSQL. Izvi zvinoreva kuti "Sarudza zvinhu zvose kubva patafura yega_info."

Sarudza * Kubva ku-person_info

Zvimwewo, vashandisi vangave vachida kugadzirisa maitiro anowanikwa kubva kune database. Semuenzaniso, dhipatimendi reHuman Resources ringada runyorwa rwemazita ekupedzisira evashandi vose mukambani. Inotevera SQL murairo inogona kutora mashoko aya chete:

SELECT last_name kubva ku-personal_info

Iyo WHERE clause inogona kushandiswa kugadzirisa zvinyorwa zvinodzorerwa kune avo vanosangana zvakatemwa. Mutungamiri mukuru angave anofarira kuongorora vashandi zvekodhi dzevashandi vanobhadhara zvikuru. Murairo unotevera unotora zvose data iri mukati memunhu wega_info kune marekodhi ane mubhadharo wepamusoro mukuru kupfuura $ 50,000:

Sarudza * Kubva pachake_info PANE MARI> $ 50000

FUNGA. Murairo we UPDATE unogona kushandiswa kugadzirisa ruzivo rwaive mukati me tafura, kana huwandu kana mumwe nomumwe. Funga kuti kambani inopa vashandi vose 3% mari-ye--kuwedzera kuwedzera muhomwe yavo gore negore. Iyi inotevera SQL murairo inogona kushandiswa kukurumidza kushandisa iyi kune vose vashandi vanochengetwa mu database:

FUNGA munhu oga_info SET mubhadharo = mushandi * 1.03

Apo mushandi mutsva Bart Simpson anoratidzira kushanda kwepamusoro uye kudarika kudanwa kwebasa, vatungamiri vanofarira kuziva stellar yake kubudirira ne $ 5,000 kusimudzwa. Iyo WHERE clause inogona kushandiswa kutora kunze Bart nokuda kwekusimudzirwa uku:

FUNGA munhu oga_info SET mubhadharo = mushandi + $ 5000 PANE mushandi_id = 12345

DELETE. Pakupedzisira, regai titarise mutemo weDELETE. Uchaona kuti chirevo chemurairo uyu chakafanana neyeimwe mirairo yeDML. Zvinosuruvarisa, mharidzo yedu yataipindira yemari yemakambani haina kumbosangana nemitarisiro uye varombo vakave vakasiyiwa. Murairo WAKADZISA neQUERE WHERE inogona kushandiswa kubvisa zvinyorwa zvake kubva patafura yega_info:

KUDA KUBVA KUBVA MUNYAYA_info PAIYE mushandi_id = 12345

KUITA

Iye zvino zvawakadzidza zvidzidzo zveSQL, inguva yekuenderera mberi kune rimwe remashoko ane simba zvikuru mutauro unofanirwa kupa-JOIN statement. A statement KUTAITA inokubvumira kuunganidza data mumatafura akawanda kuti uite zvakanaka kugadzira zvakawanda zve data. Aya mazwi ndeapi apo simba rechokwadi re database rinogara.

Kuti uongorore kushandiswa kwemashoko ekutanga JOIN chirongwa chekubatanidza data kubva pamatafura maviri, ramba uine muenzaniso uchishandisa PERSONAL_INFO tafura uye uwedzere imwe tafura pataikanganiswa. Funga kuti une tafura inonzi DISCIPLINARY_ACTION yakasikwa nemashoko anotevera:

DZENGA TABLE chirongwa_action (action_id int kwete null, employee_id int kwete, comments char (500))

Iyi tafura ine migumisiro yezviito zvekuranga kune vashandi vekambani. Iwe uchacherechedza kuti hauna humwe ruzivo pamusoro pemushandi kunze kweyo nhamba yevashandi. Zviri nyore kufungidzira mamiriro ezvinhu akawanda apo iwe ungada kusanganisa mashoko kubva ku DISCIPLINARY_ACTION uye PERSONAL_INFO mabheji.

Ita kuti iwe wakagadzwa nekugadzira mushumo unonyora maitiro ekuranga anotorwa kune vashandi vose vane mubayiro mukuru kupfuura $ 40,000. Kushandiswa kweJOIN operation, munyaya iyi, yakananga. Tinokwanisa kutora mashoko aya tichishandisa murairo unotevera:

Sarudza person_info.first_name, personal_info.last_name, chirango -action.com kubva ku-personal_info, disciplinary_action WHERE munhu_info.employee_id = chirango_action.employee_id uye person_info.salary> 40000

Iyo code inotsanangura mahwendefa maviri atinoda kubatanidza muCHITSA CHOKUTANGA uye zvino inosanganisira chirevo muQUERE WHERE kuvhara migumisiro kune zvinyorwa zvakafananidzwa neAIDS dzevashandi uye zvakasangana nemitemo yedu yemutero mukuru kupfuura $ 40,000.