Select hadal (SQL)

gaabiyay SQL Transfer laftiisa (luqada waa weydiimo habeysan) ka tarjumaya xaqiiqda ah in waxa loo baahan yahay - element inta badan la isticmaalo ee SQL. Dooro saf ah, si toos ah u baabi'iyo xogta siyaado ah, ka gudub ama reorder columns caawin Select (SQL) - hadal kooban muujinaysa of database si ay u keenaan macluumaad gaar ah.

Saan

Si aad si sax ah u isticmaasho shaqaale kasta waa in marka hore naftaada baro Saan luqadda barnaamijyada hoos tixgelin. Marka si gaar ah hadalka ku saabsan luqadda SQL ah, Select (operator) ayaa Saan soo socda:

Dooro	Waxaa sheegayaa database ah in aan ku gudbin codsiga. keyword Tani.
Mid ka mid ah, laba, saddex ...	Qor in uu soo bandhigo columns
From	Waxay muujinaysaa magaca miiska ka kaas oo xogta lagu dooranayo. Sidoo kale waa keyword khasab ah.

Tani waxa loogu yeero Saan operator "gaaban", laakiin waxa ay noo tilmaamay in ay xaqiiqda ah in aan keywords Select oo ka database aanu u hoggaansamaan codsigeena ma.

Saan war Full lagu muujiyay shaxanka soo socda:

Halkan Halkee lagu burburin kuu ogolaanaya inaad si loo tijaabiyo raadinta by xaaladaha la tilmaamayo.

Waayo, qiyamka isutagga iyo codsiga iyaga ka mid ah shaqo loo isticmaalo wadar ahaan labada lug dalab Group by, iyo in la caddeeyo natiijada ka dib isutagga isticmaalaa Lahaanshaha soo jeedinta.

Order by sort awood u siin doona qiimaha column ee la doortay ee kor u kacaya ama amarka soo degaya.

с информацией: Si aad u aragto si cad qoraal Select, ka fikirno in our database leeyahay macluumaadka soo socda miiska Cats:

Id	nooc	magaca	Dhalashada	color
1	Bobtail	sayidkaygiiyow	04/01/2017	Gray
2	Laabid	taatiko	03/16/2017	White
3	Mau	haramcad	03/30/2017	Black
4	Bobtail	Tyson	02/23/2017	Gray
5	Burmilla	Athena	08/01/2017	Black

isku xigta kasta waxa ku jira jeebka qol u gaar ah, oo ay sirdoonka, naaneesta, taariikhda dhalashada iyo midab. Waxa kale oo aanu ka fiirsan doonaa sida wadaha Select (SQL), waxa ay mar horeba ku salaysan xogta miiska ka.

Sida xogta la waraystay miiska ka

Sida kor looga hadlay, tusaale ahaan macluumaadka aad miiska ka u baahan in aan u isticmaalno doonaa keywords ah.

keyword The qeexan Select columns in uu soo bandhigo. Waxaad samayn kartaa liiska tiirar looga baahan yahay, kala hakadka, ka dibna dhismeedka dhan eegi doonaa sida tan:

midabka Select, sirdoonka, magaca From Cats

Sida aad arki karto, waxaanu kuu qabanqaabin karaa tiirar si ay na u baahan tahay. Intaa waxaa dheer, waxaan noo lean karaa oo kaliya tiirar loo baahdo.

Waxa kale oo jira soo saarista ah oo dhan tiirarkii in view miis. Si arrintan loo sameeyo, ka dib markii Select ayaa tilmaamaya calaamad ah (*) by meel. Dhismaha oo dhan eegi doonaa sida tan:

Dooro * From Cats

Natiijada su'aasha kor ku xusan - ka Cats miiska oo dhan, soo bandhigay qaab kuwaas oo waxaa ku jira dhamaadka qaybta ugu dambaysa.

Dad badan ayaa la yaabi meesha waxqabadka sida in SQL Select oo isku xigta. Inta badan waxaa loo baahan yahay marka aad rabto in aad isu geeyo la odhan, magaca iyo patronymic qofka lagu meeleeyay columns soocay.

Xaaladdan oo kale, waxaan isu geeyo ay eyda iyo bisadaha midabada miiska Cats. Midka wanaagsan waa in database kala duwan loo isticmaalo concatenation string jilayaasha kala duwan. Xaaladaha qaarkood waxa kaliya oo lagu daray ah (+), in kale oo ka - feature a line double (||) ama ampersand ah (&), oo mararka qaarkood loo isticmaalo, iyo operand Concat. Sidaa darteed, ka hor inta aan isku baahida loo qabo in la akhriyo annotation si database gaar ah oo aad shaqaynayso.

Select sirdoonka || ',' || midabka From bisadaha

Natiijadii aan ka heli soo socda:

Nooc, Color

Bobtail, Gray

Laabid, White

Mau, Black

Bobtail, Gray

Burmilla, Black

Tirtiridda macluumaadka siyaado ah

Oo kala duwan - Function Select (SQL), kaas oo u ogolaanaya in ay ka fogaadaan yaac iyo xadhig gabi ahaanba isku natiijada tijaabada.

Tusaale ahaan, waxaan rabnaa in la ogaado waxa ay eyda cat leeyihiin in miiska our. Haddii aad isticmaalayso su'aal fudud:

Select sirdoonka From Cats

Haddaba, waxaan u hesho natiijo aan filayaa,

nooc

Bobtail

Laabid

Mau

Bobtail

Burmilla

Sida aad arki karto, Bobtail sirdoonka waa labalaabka laba jeer. dood oo kala duwan si looga fogaado in hal hawl ah, waxaan u baahan nahay oo kaliya in ay ku dar weydiinta:

Dooro sirdoonka oo kala duwan From Cats

codsi yeelidda

Sida xaqiiqda ah, gebi ahaanba ma jiro codsi ma saarka xogta sida go'an dhamaystiran oo saf ee miiska. Tixgeli soo jeedin ah in Select (SQL) ogolaan doonaa si ay u qeexaan shuruudaha xulashada kaliya khadadka midig.

Sidaa darteed hantida tani waa xaggee. Hindisaha ayaa waxay khusaysaa la saadaalin ah - muujinta shuruudaysan, taas oo ku siinaysaa qiimaha wax soo saarka waa "run" ama "been ah." Select war Siibnaa kaliya xogta miiska ka kaas oo hadal shuruud waxay noqon lahayd inaad Run, ama "runta".

Waxay kaa caawinaysaa in heshiis la this dhismaha, muunad fudud. Ka soo qaad waxaan rabnaa in la ogaado oo dhan ku saabsan ee bisadaha ee midabka madow.

Dooro * From bisadaha Halkee color = 'Black'

Natiijada su'aal tani waxay noqon doontaa oo isku xigta ku xiga ee miiska:

3	Mau	haramcad	03/30/2017	Black
5	Burmilla	Athena	08/01/2017	Black

Waxa kale oo aad isku dari kartaa xaaladaha la isticmaalayo shaqeeya macquul Oo Or, ma aha.

Sii Group by

Sii Group by, loo isticmaalo in Select (SQL), aad u ogolaanaya in ay group weydiinta by qiimaha column gaar ah (ama columns), ka dibna iyaga ka codsan in ay shaqo wadar ahaan.

Waayo, hawlaha wadar ahaan ka mid ah:

• Tiri - xisaabi tirada saf xulay weydiinta.
• Sum - wadarta xisaabta oo dhan column ee la doortay.
• Min - saarka yar ee column ee la doortay.
• Max - siday u kala horreeyaan, qiimaha ugu badan ee column ee la doortay ee.
• Avg - qiimaha celceliska.

Qorshaha soo jeedinta waxaa sida ugu wanaagsan fahamsan yahay by hab oo tusaale ahaan. Ka soo qaad waxaan rabnaa in aan aad u ogaato sida inta badan kasta kittens sirdoonka waxaan leenahay. Si arrintan loo sameeyo, la abuuro su'aal soo socda fudud:

Select sirdoonka, count (*) From bisadaha Group by sirdoonka

Natiijada doonista in miiska soo socda:

nooc	Tiri
Bobtail	2
Laabid	1
Mau	1
Burmilla	1

Sida aad arki karto, bisadaha bobtail nooc waxaan leenahay labo, dadka kale sida kaliya hal. no - At codsiga dhaqanka noocan oo kale ah, ku salaysan miiska dul saarnaado, breeder ah fahmi karo waxa bisadaha eyda waa in baahida ka mid ah macaamiisha, iyo waxa.

Waxay u badan tahay in sababta oo ah tirada badan ee entries in miiska dhabta ah weli doonayaa in aan caddeeyo codsiga iyo muujiyaan kittens kuwa, kaas oo aan ka badnayn ahaayeen, tusaale ahaan, toban kaliya. Si loo caddeeyo ama kooxo filter waxaa loo isticmaalaa Lahaanshaha soo jeedinta. Waxaa noo ogolaadaa in ay iska tuur kooxaha qaarkood, si la mid ah lagu burburin karo Xaggee ka, kaas oo iska xoorayaa khadadka qaar. Xaaladda waxaa lagu qeexaa by shaqo wadar ahaan labada lug. Buuxi codsiga:

Select sirdoonka, count (*) From bisadaha Group by sirdoonka Lahaanshaha count (*) <= 10

Tan iyo xaalad aan la weydiiyey "tirada bisadaha idaad kasta waa in aan ka badnayn 10", natiijada aan helno waa isku mid sida in tusaale ahaan, iyada oo la tilmaamayo. Laakiin halkan waxaa muhiim ah in la fahmo nidaamka laftiisa shaqeeyaa Lahaanshaha soo jeedinta. Laakiin haddii aad beddesho xaaladda macquulka ah ee Lahaanshaha count (*) = 1, ka dibna natiijada la dhimi doonaa saddex lines iyo bandhigayaa kittens, taas oo kaliya hal tagay.

kala sooca

Bartaan Amarka by - dalab Select (SQL) operator u ogolaanaya inay kala soocaan saf saarka ee kor u kacaya ama amarka soo degaya in mid ama ka badan tiirar.

Waxaa muhiim ah in la xasuusto in Amarka by - waa dalab ugu dambeeya oo dhan dhismeedka war Select. Waxaa la dhigayaa ka dib markii Select ah, From, Xaggee, Group by, Lahaanshaha.

Waxaa jira saddex dhibcood oo muhiim ah inta lagu guda jiro kalabuxu waxay ka:

1) Waxaad kala dooran kartaa tiro ka mid ah tiirar, kuwaasoo mid walba uu si gaar ah loo kala soocaa karaa in kor (ASC), ama (dood) degaya.

2) Dhammaan shaxda kor ku xusan in Amarka by xukunka waa in ay ahaataa mid ah tiirar xulay in Select xaadirka ah.

3) magacyada gaarka ah liiska Ikhtiyaar u xalliso tiirarkii, aad si fudud u tilmaami kartaa lambarada hoos jirta oo ay u tagaan si ay war Select.

Waxaan rajaynayaa in this article waxaad heli doontaa aqoonta aasaasiga ah ee ku saabsan sida loo isticmaalo weydiimo SQL iyo hadda dooro macluumaadka lagama maarmaanka ah ka database si fudud.