How kunyora SQL-queries - detailed mienzaniso

Mumwe nomumwe wedu anogara anotarisana uye anoshandisa zvakasiyana siyana Databases. Kana tikasarudza e-mail, tiri kushanda pamwe Database. Database kushandisa rubatsiro kutsvaka, mabhengi kuchengetera mutengi data, etc.

Asi pasinei anoramba kushandiswa Database, kunyange vazhinji-software gadziriro Developers zvakawanda "mavara machena" nokuda kududzira siyana zvakafanana mazwi. Tinopa muchidimbu tsanangudzo huru mashoko kune Databases pamusoro SQL kufungisiswa. Izvozvo.

Database - faira kana wakaiswa kuti mafaira kuchengeta akarayira date zvivako uye dzavo ukama. Kakawanda, mashoko Database inonzi Database utariri gadziriro (DBMS). Database - chiri dzigare mairi mashoko ari chaiwo pamanyorerwo uye vanogona kushanda pamwe Databases zvakasiyana.

Table - fungidzira folder umo magwaro akachengetwa, dzikarongwa maererano zvimwe parameters, dzakadai pamazita maodha pamwedzi wokupedzisira. Iri ndiro tafura riri kombiyuta Database. A yoga tafura ane zita yayo rakasiyana.

Data Type - rudzi mashoko akabvumira kuti zvikachenge- rimwe mbiru kana mutsara. Inogona kuva chaizvo nhamba kana chinyorwa format.

Column uye mutsara - isu tose vakashanda pamwe Spreadsheets, zviri varipowo pane mechidziro neshongwe. Chero ndeyeukama Database anoshanda matafura saizvozvo. dzimwe nguva hungiso vanonzi zvinyorwa.

A kiyi chikuru - pamusara mumwe ave mumwe kana kupfuura mbiru dzayo akasiyana kuzivikanwa. No kiyi chikuru Zvakaoma chaizvo kuti dzifambirane, kuchinja uye zvinomuomera kuikanganwa akakodzera misara.

Chii SQL?

Mubvunzo mutauro SQL (Chirungu muronga Query Language -. Muronga Query Language) yakagadzirirwa chete kushanda Databases uye pari mureza vose dzakakurumbira Databases. The mutauro nemarongerwo rinoumbwa vashoma dzaishandisa uye nyore kudzidza. Asi pasinei pachena nyore, hunobvumira pakasikwa SQL queries kuti kunzwisisa mashandiro pamwe Database upi saizi.

Kubvira muna 1992, pane chakajairika mwero, rinonzi ANSI SQL. It inorondedzera chikuru nemarongerwo uye mashandiro anoita dzaishandisa uye chinotsigirwa zvose DBMS pamusika vatungamiri, akadai tsvene iri Microsoft ari SQL Server. Funga zvose zvaidiwa mutauro pfupi nyaya hazvibviri, saka tichati ongororei chete inokosha SQL queries. Mienzaniso vanoratidza pakururama uye zvaidiwa mutauro:

• kusika Databases nematafura;
• date sarudzo;
• Achiwedzera Entries;
• nadzurudzo uye deletion mashoko.

SQL Data Types

mbiru dzose ari tafura Database achengetere chete mashoko mhando. mu SQL date mhando zvakafanana mune mimwe mitauro zvirongwa.

date mhando	tsananguro
Int	integers
REAL	Zvinoyangarara pfungwa nhamba
TEXT	Character tambo shanduka urefu
DATE	SQL mubvunzo "Musi" nenzira dzakasiyana-siyana zvinogona
TIME	nguva
kwaIsakari	Text hungiso kuti wakasimba urefu

Gadzira matafura uye Databases

Gadzira Databases itsva, matafura, uye dzimwe queries muna SQL Pane nzira mbiri:

• SQL mashoko kuburikidza Database kunyaradzana
• Kushandisa dyidzana zvishandiso Administration, rutivi Database Server.

A itsva Database opareta Create Database ; . Sezvaunogona kuona, kuti nemarongerwo riri nyore uye muchidimbu.

Matafura mukati Database kusika kusika ZVIRI okuti pamwe anotevera parameters:

• tafura zita
• mazita uye date mhando pembiru

Somuenzaniso, kuumba chinokosha tafura anotevera mbiru:

mbiru	tsananguro
commodity_id	chigadzirwa ID
vendor_id	Vendor ID (Key Vendors zvekunze tafura)
commodity_name	zita chigadzirwa
commodity_price	mari
commodity_desc	tsananguro

Gadzira tafura:

Create ZVIRI chinokosha

(Commodity_id kwaIsakari (15) NOT null,

vendor_id kwaIsakari (15) NOT null,

commodity_name kwaIsakari (254) null,

commodity_price grade (8,2) null,

commodity_desc VARCHAR (1000) null);

The tafura ine mbiru shanu. Mushure zita riri dzakaparadzaniswa nezvitura befu mhando date pembiru. Mbiru ukoshi anogona kufungidzira null tsika (null) kana inofanira kuzadzwa mu (NOT null), uye akatsunga tafura kusika.

Mharidzo Data patafura

zvakabudiswa opareta date - kupfuura inowanzoshandiswa SQL queries. Kuti uwane mashoko unofanira tsanangurai kuti tinoda kusarudza kubva patafura. Pakutanga muenzaniso nyore:

Sarudza commodity_name FROM chinokosha

Mushure Sarudza mashoko anoratidza zita mbiru kuwana ruzivo, asi FROM anozivisa tafura.

asi mubvunzo tichava zvose tafura mitsara pamwe Commodity_name anokoshesa uchitanga yavakanga vakapinda Database i.e. asina Sorting. Kuronga zvabuda uchishandisa optioneel opareta KUTI BY.

Kuti mubvunzo pamusoro minda akawanda wozvinyora dzakaparadzaniswa nezvitura befu sezvo zvinotevera muenzaniso:

Sarudza commodity_id, commodity_name, commodity_price FROM chinokosha

Zvinokwanisika kuti sezvo pamusana mubvunzo tambo ukoshi pembiru zvose. Nokuti ichi chinangwa, chiratidzo "*":

Sarudza * FROM chinokosha

• Sarudza Uyezve inotsigira:
• Sort Data (KUTI BY mashoko)
• Selection maererano nemamiriro (KUPI)
• Muzvikwata zvisina tsarukano izwi (BOKA BY)

wedzera mutsetse

Kuti kuwedzera misara kuti tafura uchishandisa SQL mashoko dzinoisa mashoko. The Uyezve chinogona kuitwa nenzira nhatu:

• wedzera mutsetse itsva yose;
• of mutsetse;
• mubvunzo zvabuda.

Kuti kuwedzera mutsetse wakakwana, unofanira kutaridzwa tafura zita uye zvinokosheswa mbiru (minda) ane mutsetse itsva. Heuno muenzaniso:

Pinza KUKAVA chinokosha tsika ( '106' '50', 'Coca-cola', '1,68', 'No Alcogol,)

Example inowedzera chigadzirwa itsva patafura. Tsika dziri kutaurwa pashure netsika nechimwe mbiru. Kana pasina runoenzanirana ukoshi nokuda mbiru, zvakakodzera tsanangurai null. The mbiru vakazadzwa tsika uchitanga hwacho paaisika tafura.

Kana kuwedzera chete mugove tambo vanofanira zvakajeka kutaridzwa zita mbiru, sezvinoitwa Muenzaniso:

Pinza MU chinokosha (commodity_id, vendor_id, commodity_name)

Tsika ( '106' '50', 'Coca-cola', )

Hatina akasuma chigadzirwa identifiers chete, nokutengesa uye zita raro, uye navamwe munda akaisa parutivi chinhu.

Achiwedzera mubvunzo zvabuda

Zvikurukuru, pinza chinoshandiswa kuwedzera mumwe musara, asi inogona kushandiswa kuwedzera yevashoma mashoko zvabuda.

changed date

Kushandura mashoko muminda Database tafura vanofanira kushandisa imwe UPDATE mashoko. Mutekenyi inogona kushandiswa nenzira mbiri:

• Updated zvose mechidziro zviri patafura.
• Chete kuti mumwe mutsetse.

UPDATE chinosanganisira chikuru matatu zvinhu:

• tafura umo uchida kuchinja;
• munda mazita uye tsika itsva dzavo;
• Conditions nokuti mutsara kusarudza kuchinja.

Funga muenzaniso. Toti chigadzirwa pamwe ID = 106 akachinja mutengo, saka mutsetse uyu kuti dzifambirane. Nyora mashoko anotevera:

UPDATE COMMODITY Isai commodity_price = '3.2' KUPI commodity_id = ' 106'

Isu akanongedza tafura zita, muchiitiko ichi, kuti chinhu, izvo zvichashandiswa akagadziridzwa, uye shure akatarirwa - kukosha itsva mbiru uye kuwana zvaaida chinhu, chaiyo iri KUPI achida ukoshi ID.

Kushandura uwandu mbiru mushure Isai mashoko rinoti majoko wandei mbiru-ukoshi, akaparadzaniswa nevanhu nezvitura befu. Ona muenzaniso umo yoku- zita uye mutengo nhumbi:

UPDATE COMMODITY Isai commodity_name = 'Fanta' , commodity_price = '3.2' KUPI commodity_id = ' 106'

Kuti Kudzima mashoko ari mbiru, unogona kuisa kuti null, kana anobvumira nemamiriro tafura. Zvinofanira kurangarirwa kuti null - ichi ndicho "kwete" ukoshi, kwete razero sezvo murugwaro kana nhamba. Bvisa chinhu tsananguro:

UPDATE COMMODITY Isai commodity_desc = null KUPI commodity_id = ' 106'

Deleting misara

SQL zvikumbiro zvinomuomera kuikanganwa mitsara iri tafura anoteererwa Kudzima opareta. Pane nzira mbiri kushandisa:

• Table dzadzimwa vamwe pemitsara;
• akabvisa zvose mechidziro zviri patafura.

MUENZANISO dzichibvisa mumwe musara patafura:

Kudzima FROM COMMODITY KUPI commodity_id = '106'

Pashure Kudzima FROM kutaura patafura zita iri zvinomuomera kuikanganwa pemitsara. KUPI opareta zvinosanganisira chirwere pasi iyo mutsetse vachapiwa kusarudzwa nokuda deletion. In muenzaniso isu kubvisa chigadzirwa maererano ID = 106. KUPI kunoratidza kukosha chaizvo nokuti Zvino ichi opareta privedt kubvisa mitsara zvose zviri patafura. Izvi zvinoshandawo kushandura munda tsika.

The Delete statement haritauri mbiru mazita, uye Meta-vatambi. Iye chose anobvisa mutsetse uye zvinomuomera kuikanganwa imwe mbiru, haagoni.

Kushandisa SQL muna Microsoft Access

Microsoft Access ari yemanyorero kushandiswa interactively kusika munhu spreadsheet, Database, utariri kuchinja, ari kuongorora umboo ari Database uye kuti pave queries SQL Access Via iri nyore dyidzana mubvunzo muvaki (Query Designer), achishandisa iro unogona kuvaka nokukuitira SQL mashoko pakarepo chero kunzwisisa.

Also akatsigira nzira kuenda Server, sezvamakaropafadzwa Database Access inogona kushandiswa sezvo SQL-mubvunzo jenareta upi ODBC date kunobva. Nyaya iyi inoita chikumbira kukurukurirana Access Database chero format.

SQL Extensions

Nekuti SQL queries havana zvose Maitiro zvirongwa mutauro zvinhu zvakadai muzvishwe, branching, nezvimwewo, asi DBMS vagadziri kuva shanduro yavo SQL pamwe yemhando zvinhu. Chokutanga ndechokuti rutsigiro zvakachengetwa Maitiro uye mwero dzaishandisa Maitiro mitauro.

Vakawanda tumitauro mutauro:

• Oracle Database - PL / SQL
• Interbase, Firebird - PSQL
• Microsoft SQL Server - Panorama-SQL
• PostgreSQL - PL / pgSQL.

SQL Internet

MySQL Database asunungurwa pasi akasununguka rezinesi GNU General Public License. Pane hwezvokutengeserana rezinesi vachikwanisa kukudziridza tsika modules. Senzira inonyanya kufarirwa chikamu tinova Internet servers akadai XAMPP, WAMP rambi, uye ndiyo inonyanya kufarirwa Database chikumbiro chiitiko Internet.

Rakanga yakataurwa Sun Microsystems uye parizvino rinotsigirwa Oracle. Tinotsigira Database Kukura kusvika 64 terabytes, kuti SQL nemarongerwo: 2003, replicating Databases uye gore mabasa.