Basic SQL Mashoko

SQL mureza rakagamuchirwa muna 1992 uye vachiri nhasi kushandiswa. Kuti iye ave chipimo vazhinji Database utariri enyika. Chokwadi, vamwe vagadziri vanoshandisa dudziro mureza yavo. Asi chero ezvinhu, kuchine huru zvinoriumba - SQL mashoko.

ziviso

Kushandisa SQL mashoko ari Database kunoitika tsika utariri matafura uye kugadzirira kwavo kumwe kuongorora uye kuratidza. Ndivo chine Keywords, ayo hurongwa anoziva zvokuita pamwe mashoko.

Dudzira Mapoka anoverengeka SQL mashoko:

• tsanangudzo Database zvinhu;
• vachikorovhera tsika;
• kudzivirirwa uye kutarisirwa;
• pachirongwa parameters;
• Information pamusoro pangoro;
• kunofambira mberi SQL;
• simba SQL.

SQL mashoko nokuti mashoko evanhu

muchikwata ichi zvinosanganisira mazvi izvo unogona kudzora placement tsika dziri Database.

Pinza. Inoisa panhurikidzwa imwe huripo tafura. Rinogona kushandiswa rimwe ukoshi kana kakawanda, kunorondedzerwa imwe ezvinhu. Somuenzaniso:

pinza PINDA

tafura zita (mbiru 1 zita, zita mbiru 2)

Tsika (ukoshi 1 ukoshi 2).

Kushandisa opareta SQL mashoko dzinoisa multiple tsika, shandisa inotevera nemarongerwo:

pinza PINDA

1 tafura zita (zita mbiru 1, Mbiru zita 2)

Sarudza mbiru zita 1, zita mbiru 2

FROM tafura zita 2

KUPI zita 2.imya tafura Mbiru 1> 2

mubvunzo uyu anosarudza ose mashoko ari Table 2, rinopfuura 2 1 Mbiru namatidza muvira wokutanga.

UPDATE. Sezvo zita rinoreva, mashoko aya inogadziridza SQL mubvunzo Data muimba huripo netafura imwe hwaro.

muenzaniso:

UPDATE tafura zita 1

Isai sa zita = 2 "Basil"

KUPI tafura zita 1.imya Mbiru 1 = 1

Chimiro ukaguta ukoshi Basil misara zvose izvo tichingamidze nhamba 1 pakutanga mbiru.

Kudzima. Deletes humboo kubva patafura. Unogona tsanangurai chero mamiriro kana kubvisa zvose mechidziro.

Kudzima FROM tafura zita

KUPI zita tablitsy.imya Mbiru 1 = 1

The pamusoro mubvunzo kuchaita zvinomuomera kuikanganwa ose mashoko kubva Database pamwe kukosha imwe yokutanga mbiru. Heunoi sei unogona kubvisa yose tafura:

Kudzima FROM tafura zita.

Next zvakafanira kuudza pamusoro sarudza mashoko. Ndiye mumwe anokosha, saka iye achava kupedzera munhu yoga chitsauko.

Sarudza mashoko

Chinangwa chikuru sarudza - vakasarudzwa mashoko maererano zvimwe zvinhu. Mugumisiro basa rake nguva dzose mutsva tafura vakasarudzwa mashoko. MS opareta SQL sarudza anogona kushandiswa uremu zvikumbiro siyana. Saka, pamwe chete nawo, unogona kufunga zvimwe Related keywords.

Kuti usarudze yose date chiratidzo "*" rinoshandiswa kubva imwe tafura.

sarudza *

FROM tafura zita 1

Mugumisiro mubvunzo uyu achava chaiyo kopi tafura 1.

Uye pano pane Hadzisi nokuda KUPI ezvinhu izvo kuminyuka kubva Table 1, zvose zvinhu kupfuura 2 chomupepanhau 1.

sarudza *

FROM tafura zita 1

KUPI zita 1.imya tafura Mbiru 1> 2

Unogona kutaridzwa wokusarudza kuti chete vamwe mbiru inodiwa.

Sarudza tafura zita 1.imya chomupepanhau 1

FROM tafura zita 1

Mugumisiro mubvunzo uyu vachatitaka zvose mitsetse vane tsika Mbiru 1. Uchishandisa MS SQL dzaishandisa anogona kusika yayo tafura, mukufamba kutsiva, pakuverenga uye dzinomiririra zvimwe tsika.

sarudza

tafura zita 1.imya chomupepanhau 1

tafura zita 1.imya chomupepanhau 2

tafura zita 1.imya chomupepanhau 3

«=» AS EQ

tafura zita 1.imya mbiru 2 petafura zita * 1.imya Mbiru 3 AS SUMMA

FROM tafura zita 1

Izvi mubvunzo sokuti kunzwisisa fetches zvose zvinokosheswa Table 1, uye ipapo pave itsva mbiru uye EQ SUMMA. First anopinda "+" chiratidzo, yechipiri umbowo mbiru chigadzirwa 2 3. mugumisiro Izvi zvinogona achimiririrwa tafura, kunzwisisa sei akunoita:

mbiru 1	mbiru 2	mbiru 3	EQ	SUMMA
Product Name 1	10	50	+	500
Product Name 2	15	100	+	1500

Kana uri kushandisa yevashoma mashoko, unogona pakarepo kupedza achirayira mashoko chero chikonzero. Inoshandisa shoko KUTI BY.

sarudza

tafura zita 1.imya chomupepanhau 1

tafura zita 1.imya chomupepanhau 2

tafura zita 1.imya chomupepanhau 3

FROM tafura zita 1

KUTI BY sa zita 2

The zvichiguma tafura vachatarira seizvi:

mbiru 1	mbiru 2	mbiru 3
1	1	54
3	2	12
7	3	100
2	5	1

Kuti, zvose pemitsara dzave kuiswa nenzira yakadaro kuti mbiru 2 tsika vaikwira.

Data zvinogona kuwanikwa multiple pamatafura. Kuti zvijeke, unofanira kutanga kufungidzira kuti vane mbiri, vamwe vari Database:

Table "Vashandi"

nhamba	zita	zita rechipiri
1	Vasya	Vasina
2	Petya	Petin

Table "muhoro"

nhamba	mutengo	ndiye
1	1	10000
2	0.5	3500

Zvino unofanira, akadai dzaibatanidza mabwendefa maviri kuti tsika dzakafanana. Kushandisa zvinhu SQL mashoko inogona kuitwa sezvinotevera:

sarudza

Sotrudniki.Nomer

Sotrudniki.Imya

Zarplata.Stavka

Zarplata.Nachisleno

FROM Vashandi muhoro

KUPI Sotrudniki.Nomer = Zarplata.Nomer

Pane vakatorapo zvakasiyana matafura maviri tsika vakabatanidzwa nhamba. Zvinoguma zvinotevera akaisawo date:

nhamba	zita	mutengo	ndiye
1	Vasya	1	10000
2	Petya	0.5	3500

Zvishoma pamusoro sarudza. Kushandisa uwandu hwezvinhu zvose zvabatanidzwa. mabasa

Chimwe chedzidziso SQL sarudza mashoko anogona kubereka dzimwe Masvomhu mu mharidzo. Kuti aite izvi, iye anoshandisa vamwe uye mabasa Masvomhu.

Somuenzaniso, kuti nhamba zvinyorwa kubva patafura "Vashandi", unofanira kushandisa mubvunzo:

Sarudza Count (*) AS N

FROM Vashandi

The Zvichaguma tafura pamwe chete mbiru uye kukosha.

N

2

basa rinogona kushandiswa queries kuti overenga pachiyero, kunonyanyisa uye shoma tsika, uye paavhareji. Kuti aite izvi, mazvi anoshandiswa Muchidimbu, MAX, Min, Avg.

Somuenzaniso, zvakafanira kuti abate nzwisa zvakatoitika kuzivikanwa "muhoro" tafura:

nhamba	mutengo	ndiye
1	1	10000
2	0.5	3500

Unogona kushandisa rimwe chikumbiro chakadaro uye uone zvinoitika:

sarudza

Verengai (Zarplata.Nachisleno) AS SUMMA

MAX (Zarplata.Nachisleno) AS MAX

Min (Zarplata.Nachisleno) AS Min

Avg (Zarplata.Nachisleno) AS SRED

FROM muhoro

Kwokupedzisira tafura achava sezvinotevera:

SUMMA	MAX	Min	SRED
13500	10000	3500	6750

Ndiyo nzira, unogona kusarudza kubva Database chakakodzera tsika dziri nhunzi kuita kuverenga kuti akasiyana-siyana.

Union, mharadzano uye musiyano

Sangana queries akawanda mu SQL

Select Sotrudniki.Imya

FROM Vashandi

KUPI Sotrudniki.Nomer = 1

UNION

Select Sotrudniki.Imya

FROM Vashandi muhoro

KUPI Zarplata.Nomer = 1

Zvinofanira kurangarirwa kuti tafura iyi kushamwaridzana anofanira kuva enderana. Kureva vane uwandu pembiru.

The nemarongerwo vakasarudzwa mashoko uye nzira kuti kugadzira

Chinhu chokutanga Sarudza anosarudza munzvimbo kubva iyo Achatora mashoko. Kuti aite izvi, kushandisa guru FROM. Kana kusingajekeswi, chii kusarudza.

Unogona kupa SQL mashoko KUPI. Achibatsirwa sarudza anomhanya kuburikidza nerimwe musara patafura uye achaongorora kuti mashoko nokuda zvaidiwa mamiriro.

Kana mubvunzo ane BOKA BY, ipapo pane vanhu muzvikwata zvisina tsarukano ose netsika parameters izvi.

Operators kuenzanisa mashoko

Vane mhando dzakawanda. In SQL Kuenzanisa dzaishandisa anogona kuongorora marudzi akasiyana tsika.

• "=". Rinoreva, sezvo imi kuti fembera, wakaenzana maviri mashoko. Somuenzaniso, kare kushandiswa mienzaniso kumusoro - KUPI Zarplata.Nomer = 1.

• ">". Greater-pane chiratidzo. Kana kukosha kurutivi okuti kuruboshwe mukuru zvino musoro KWECHOKWADI iri akadzoka uye mamiriro ezvinhu akasangana.

• "<". Asingasviki chiratidzo. Harungashandurwi yapfuura opareta.

• Zviratidzo "<=" uye "> =". Zvakasiyana nyore dzaishandisa mamwe uye zvishoma ndechokuti zvakaenzana operands ezvinhu ndezvechokwadiwo.

• "<>". Handina basa. Mamiriro anonzi DZECHOKWADI, kana operand mumwe chete haana kuenzana mumwe. Iye ane zvakawanda dudziro - "! =".

SEZVAIITA

Shandura guru iri rinogona kuva "yakafanana." KUFANANA opareta muna SQL rinoshandiswa anenge ari ichocho - anomhanya mumwe mubvunzo kubva template. Kureva izvozvo kunoita kuti akudziridze mharidzo Data kubva Database uchishandisa nguva mashoko.

Somuenzaniso, uine basa akadai: kubva kare anozivikanwa nechigadziko "mudonzvo" kuti vose vanhu vane zita rinoguma "I". Ipapo chikumbiro inogona rinoumbwa sezvinotevera:

sarudza *

FROM Vashandi

KUPI Name KUFANANA `% I`ll

Muzana wemasaini iyi zvinoreva chifukidzo, kureva, chero hunhu, uye nokuwanda. Zvino tsamba, "Ini» SQL anosarudza kuti pakupedzisira unhu saizvozvo.

CASE

Mashoko aya SQL Server ane Implementation siyana sarudzo. It chakafanana chinja mashoko mumitauro yakawanda zvirongwa. CASE mashoko SQL anoita chiito nezvirango akawanda.

Somuenzaniso, iwe sarudza kubva patafura "muhoro" kunonyanyisa uye pasipasi tsika.

nhamba	mutengo	ndiye
1	1	10000
2	0.5	3500

Ipapo chikumbiro inogona rinoumbwa sezvinotevera:

sarudza *

FROM muhoro

KUPI CASE APO Select MAX (Kubhadhara) teverei Maximum

KANA Select Min (Kubhadhara) teverei Minimum

MUGUMO mugumisiro

mu "Kubhadhara" Mbiru muchirevo chechinyorwa chino, zviri hurongwa anotsvaka kufadza uye pasipasi ukoshi. Zvadaro, muchishandisa MUGUMO munda akasikwa "nhamba", chichakurukurwa Logged mu "kunonyanyisa" kana "zvishoma" zvichienderana mugumisiro ezvinhu.

By panzira, SQL pane zvakawanda tsindirana muchimiro CASE - COALESCE.

Data tsananguro

maonero Izvi zvinobvumira kuti vaite zvakasiyana-siyana kuchinja matafura - kusika, zvinomuomera kuikanganwa, kuchinja, uye kushanda maindex.

Wokutanga, izvo zvinokosha kufunga - Create ZVIRI. Anoita hapana chimwe pane kutanga tafura. Kana uchingoda kutaipa kusika ZVIRI mubvunzo, hapana chichaitika, nekuti unofanira tsanangurai vakawanda parameters.

Somuenzaniso, kuti kusika yaizivikanwa tafura "Vashandi" kuda kushandisa murayiro:

Create ZVIRI Vashandi

(ID nhamba (10) NOT null

Name varchar (50) NOT null

Last Name varchar (50) NOT null)

In chikumbiro ichi, muzvikomberedzo pakarepo kunorondedzerwa munda mazita avo nemifananidzo, uyewo kana zvinogona null.

DONHERA ZVIRI

Anoita nyore basa - achibvisa akati tafura. It ane mamwe chisarudzo IF ariko. It absorbs kukanganisa kana deleting, kana runodiwa tafura haako. Muenzaniso kushandiswa:

DONHERA ZVIRI Vashandi KANA ariko.

Create INDEX

In SQL, pane hurongwa indices runovabvumira nokukurumidza kuwana mashoko. Kazhinji, ndiko kubatana anotaura imwe mbiru. Pave indekisi kunogona chikumbiro nyore:

Create INDEX nazvanie_indeksa

ON nazvanie_tablitsy (nazvanie_stolbtsa)

Shandisa mashoko aya muna T-SQL, Oracle, PL SQL uye vazhinji nevamwe mhirikiro ruzivo.

Alter ZVIRI

Zvikuru dzichipfekeka opareta pamwe vakawanda mikana. Kazhinji, kuchinja unobereka chivakwa tsananguro uye yavaive pamatafura. Operator inoshandiswa Oracle SQL, Postgres, uye vamwe vakawanda.

Reference zvino chapiwa pamwe mikana yakasiyana-siyana achishandisa Alter ZVIRI.

• Wedzera. Anoita kuwedzera wechikamu ari tafura. Nemarongerwo iye ichi: kuchinja ZVIRI kuwedzera nazvanie_tablitsy nazvanie_stolbtsa tip_hranimyh_dannyh. Anogona kuva parameter KANA USINA ariko, izvo vanodzivisa chikanganiso kana wechikamu kare ndiye;

• DONHERA. Kunobvisa mbiru. KANA aripo zvakare yapera, izvo zvichaita vaunze chikanganiso achiti zvakumbirwa mbiru hakuwaniki;

• CHANGE. Kushandiswa rename munda zita kutaurwa. Muenzaniso kushandiswa: kuchinja ZVIRI KUSARUDZA nazvanie_tablitsy OldName new_name;

• Nadzurudza. murayiro uyu achachinja mhando imwe mbiru uye yokuwedzera hunhu. Uye rinoshandiswa sezvizvi: kuchinja ZVIRI yokugadziridza nazvanie_tablitsy nazvanie_stolbtsa datatype anoti;

Create MAONERO

In SQL, pane zvakadai chinhu ive pfungwa. Muchidimbu, ndiwo rudzi chaivo tafura chete mashoko. It achiumbwa somugumisiro mharidzo kushandisa mutauro SQL sarudza mashoko. Views ichigona kudzora kuwana Database kuvanza kwavari, kutsiva chaiye mbiru mazita.

Muitiro rakasikwa kunoitika nenzira iri nyore mubvunzo:

Gadzira NEZVEMAONERO maonero zita AS sarudza FROM * tafura zita

Zvakabudiswa anogona kutora nzvimbo yose Database seboka, uye mune imwe ezvinhu.

A zvishoma pamusoro zvinhu

In SQL queries vanowanzoshandisa akasiyana pakuvara mabasa anobvumira kuti kushamwaridzana humboo uye exchange navo nhunzi. Zvakakodzera kufunga navo, sezvo ivo vari chinokosha mutauro muronga.

• Count. Unobereka tichiverenga misara kana zvawanyora imwe tafura. Somushonga, unogona kutaridzwa mbiru zita, ipapo Data vachatorwa kubva kwaari. Sarudza Count * FROM Vashandi;

• Avg. Nyaya iyi inotaura nezve mbiru pamwe Numeric mashoko. Its mugumisiro ndiyo vakatsunga svomhu zvinoreva tsika dzose;

• Min uye MAX. Mabasa aya zvave munyaya ino. Ivo kutsanangura kufadza uye shoma tsika akati chomupepanhau;

• Verengai. Ndicho nyore - mashandiro anoti mubatanidzwa tsika iri shongwe. Rinoshandiswa chete nokuda Numeric date mhando. VaGumbo chikumbiro parameter vakasiyana, zvichawedzerwa chete nemamwe tsika;

• Round. Basa rounding grade fractional nhamba. The nemarongerwo aishandiswa mbiru zita uye uwandu grade nzvimbo;

• Len. A nyore basa kuti anoti Kureba mbiru. The Zvichaguma chitsva tafura kuratidza kuwanda kutaurwa;

• NOW. guru iri rinoshandiswa kuverenga kuitika zuva uye nguva.

mamwe dzaishandisa

Mienzaniso yakawanda SQL ndezvipi mazvi kuti mabasa diki, asi zvisinei nyore zvikuru mharidzo kana Database basa.

• AS. Rinoshandiswa kana uchida nemaziso kuronga mugumisiro mabasa ari akarongwa zita kuti tafura kuwana.

• PAKATI. A batsira chaizvo chishandiso zvakabudiswa. Inoratidza kuwanda tsika, kusanganisira kudiwa vawane mashoko. The chiyamuro parameter anogamuchira kubva uye kuti nhamba siyana inoshandiswa.

• NOT. Mutekenyi anopa kunopesana rokuti.

• TRUNCATE. Anobvisa mashoko kubva ati chawo mugove. Yakasiyana avo dzaishandisa kuti apore mashoko pashure kushandiswa kwayo hazviiti. It kusvetuka kuti Implementation munhu akapiwa guru akasiyana SQL zvinogona kududzira siyana. Saka usati edza kushandisa TRUNCATE, zviri nani necheseri mashoko.

• Muganhu. Anogadza uwandu goho mitsetse. The peculiarity pamusoro opareta ndechokuti nguva dzose riri pamagumo. Zvinotora chimwe fanira uye mumwe hako parameter. Wokutanga rinoratidza sei mitsara yakawanda pamwe Data vakasarudzwa kuratidza. Uye kana wechipiri, ipapo opareta unoshanda zvose zvakawanda tsika.

• UNION. Very mushandisi-ushamwari opareta chokubatanidza vakawanda queries. Akatoratidza akasangana pakati mienzaniso iyi munyaya ino. Unogona kuratidza mechidziro multiple matafura, UNION kuabatanidza nokuda yamunenge kushandiswa. Nemarongerwo iye ichi: Sarudza column_name FROM tafura UNION sarudza FROM tafura imya_drugogo_stolbtsa imya_drugoy. Zvinoguma tafura kuUnited zvinodiwa pfupiso.

• PRIMARY PFUNGWA. Rinoshandurwa kuti "chikuru kiyi." Chaizvoizvo, kuti mazwi kwakadaro kureva zvinhu. Ndiye rakasiyana identifier nokuda mutsara. Rinoshandiswa, sezvo mutemo, paaisika tafura kuratidza munda ichange iine nayo.

• Default. Sezvinongoita rapfuura opareta, akashandiswa Implementation rakasikwa munhu mubvunzo. It inorondedzera default mutengo, izvo ichazara mumunda painenge zvakasikwa.

A Mazano shoma kutanga chikuva kuti kushanda SQL

1. Null. Beginners uye kwete programmers chete mukugadzirwa zvikumbiro kazhinji kukanganwa pamusoro Kuwanikwa null tsika. Somugumisiro, bumbiro remitemo kukanganisa Zvinokurumidza, iyo kwakaoma chamber ari debugging kwacho. Naizvozvo, kana kusika tafura, kana recalculated mharidzo tsika vanofanira kumbomira, uye kana null chiitiko chiri kucherechedzwa iri mubvunzo nzvimbo.

2. Memory. In neKwayedza takaendeswa akaratidzwa inoverengeka mashandiro, vanokwanisa kuita mamwe mabasa. In kukura goko kushanda pamwe Database, unogona 'kupfuura "dzemaitirwe nyore mashoko kusvika Database hurongwa. Mune zvimwe zviitiko, izvi zvinopa inokosha chaizvo vaita.

3. Kurambidzwa. Kana uchida kuwana kubva Database pamwe nezviuru misara miviri chete, zvakakodzera kushandisa dzaishandisa zvakadai muganhu kana TOP. No vanofanira dzosa mashoko kupfurikidza goko mutauro kukura.

4. Batanidza. Pamusoro kutambira mashoko kubva matafura wandei, programmers zhinji dzinotanga kuderedza pamwechete hamvuropu ndangariro zvinoreva. Asi nei? Pashure pezvose, unogona kugadzira chikumbiro umo zvose zvichava varipo. Haufaniri kunyora bumbiro kamwe uye kuchengetera mamwe ndangariro munyika.

5. Kufambira. Kana zvichibvira kushandisa mutemo ari chikumbiro, kureva mauto DBMS, zvakakodzera kurishandisa. Izvi achaponesa zvikuru zvinhu apo chirongwa kana basa.

6. Vazhinji zvikumbiro. Kana kuti pinza zvakawanda zvinyorwa zviri urongwa, ipapo Optimization vanofanira kufunga pamusoro packet Data akaiswa chikumbiro chimwe. Izvi zvichaita kunowedzerawo chokupika yose yezvinhu.

7. Kunyatsoverenga placement kuti mashoko. Usati vaigadzirira pechingoro mamiriro vanofanira kufunga sei uye kana zvakadaro dzinoverengeka matafura uye minda zvakakodzera. Pamwe pane nzira chokubatanidza navo, kana kurega vamwe. Kakawanda, programmers kushandisa kunyanyisa chitsama date kuti hapana uye kumboshandisa.

8. Types. Kuti aponese nzvimbo uye zvinhu vanofanira kufunga mhando mashoko akashandiswa. Kana unogona kushandisa munhu zvishoma "rinorema" nokuti mhando ndangariro, unofanira kushandisa zita rake. Somuenzaniso, kana inozivikanwa kuti mumunda uyu Numeric ukoshi haangaiti kupfuudza 255, nei kushandisa 4-byte Int, kana pane TINYINT 1 byte.

mhedziso

Mukugumisa, zvinofanira kucherechedzwa kuti mutauro uyu muronga SQL queries zvino kushandiswa zvinenge kwese - Websites, padandemutande mabasa, pakombiyuta Software, mobile mafomu. Saka, SQL ruzivo kuchabatsira sectors zvose okukura.

Zvisinei, dikidiki chizvarwa mutauro vomureza dzimwe nguva dzinosiyana mumwe. Somuenzaniso, PL SQL dzaishandisa vangava zvakasiyana nemarongerwo kupfuura SQL Server. Saka usati watanga kuva noruzivo ichi, zvinokosha kuti ndinozivana nayo mirayiridzo.

Mune ramangwana dzomuzana, izvo zvinogona kupfuura SQL functionality uye mabasa, hazvingaiti kuonekwa, saka kukura uyu zvikuru aizobudirira niche chipi programmer.