Zabi bayani (SQL)

Transfer SQL raguwa kanta (harshe ne ginannun queries) keyi da cewa shi yana bukatar - mafi yawan amfani da kashi a SQL. Zabi layuka, ta atomatik kashe m data, skip ko sake tsarawa ginshikan taimaka Zabi (SQL) - sanarwa dauke da wani nuni da database to kawo wasu bayanai.

ginin kalma

Don kamata amfani da wani ma'aikaci dole ne farko familiarize kanka tare da ginin kalma da shirye-shirye da harshen karkashin shawara. Lokacin magana musamman game da SQL harshe, Zabi (sadarwarka) yana da wadannan ginin kalma:

zabi	Yana gaya wa database cewa mun wuce a kan request. Wannan keyword.
Daya, biyu, uku ...	Jerin nuna ginshikan
daga	Yana nuna sunan tebur daga wanda data za a zaba. Shi ne kuma m keyword.

Wannan ake kira "short" sadarwarka ginin kalma, amma shi ya nuna mana da cewa ba tare da keywords Zabi kuma daga database ba cika tare da mu request.

Cikakken bayani ginin kalma da aka nuna a cikin wadannan adadi:

Ga ina magana ba ka damar tata your search da tantancewa yanayi.

Domin da dabi'u na ra'ayoyi da kuma aikace-aikace don su na tara aiki amfani da tayin Group ta, kuma mu bayyana sakamakon bayan ra'ayoyi amfani Samun shawara.

Domin ta raba zai taimaka dabi'u na zaba shafi a hawa ko saukowa domin.

с информацией: Don ganin mafi fili tare da wani Zabi sanarwa, ɗauka cewa mu database yana da wadannan tebur Cats bayanai:

Id	asali	sunan	birthday	launi
1	Bobtail	lord	04/01/2017	Gray
2	curl	feint	03/16/2017	White
3	mau	damisa	03/30/2017	Black
4	Bobtail	Tyson	02/23/2017	Gray
5	Burmilla	Athena	08/01/2017	Black

Kowane jere ƙunshi musamman dakin kyanwa, ta irin, sunan barkwanci, ranar haihuwa da kuma canza launi. Za mu kuma tattauna yadda za a sadarwarka Zabi (SQL), an riga an dogara ne a kan data daga wannan tebur.

Kamar yadda bayanai da aka dauka samfur daga tebur

Kamar yadda aka tattauna a sama, ga wani samfurin na bayanai da kuke bukata daga tebur za mu amfani da keywords.

A keyword ƙayyade Zabi ginshikan nuna. Za ka iya yin jerin da ake bukata ginshikan, rabu da wakafi, sa'an nan dukan tsarin zai yi kama da wannan:

Zabi launi, irin, sunan daga Cats

Kamar yadda ka gani, za mu iya gabatar da ginshiƙai a cikin tsari a cikin abin da suka bukatar mu. Bugu da kari, za mu iya kacokan kawai dole ginshikan mana.

Akwai kuma wani shorthand ga duk ginshiƙai a cikin wani tebur view. Don yin wannan, bayan da Zabi nuna wani alama (*) da sarari. A dukan tsarin zai yi kama da wannan:

zabi * daga Cats

A sakamakon na sama tambaya - dukan tebur Cats, gabatar a cikin nau'i a cikin abin da aka dauke a karshen na karshe sashe.

Mutane da yawa mamaki yadda wurin na yi a SQL Zabi a jere. Mafi sau da yawa wannan ake bukata a lokacin da kana so ka hada da larabci ba, sunan da kuma patronymic na mutum sanya shi a cikin rabu ginshikan.

A wannan yanayin, muna hada breeds da launuka na Cats Cats tebur. Nuance ne cewa daban-daban databases amfani ga kirtani concatenation na daban-daban haruffa. A wasu lokuta yana da kawai da (+), a cikin wasu - wani biyu line alama (||) ko wani ampersand (&), wani lokacin amfani da operand Concat. Saboda haka, kafin hada da bukatar karanta annotation zuwa takamaiman database da wanda kake aiki.

Zabi irin || ',' || launi daga Cats

A sakamakon da muka samu da wadannan:

Jinsi, Color

Bobtail, Gray

Curl, White

Mau, Black

Bobtail, Gray

Burmilla, Black

Kawar da m data

Wadansu - Aiki Zabi (SQL), wanda damar don kauce wa kwafi na da cikakken m kirtani na samfurin sakamakon.

Alal misali, za mu so mu san abin da cat breeds da a cikin tebur. Idan kana amfani da wani sauki tambaya:

Zabi irin daga Cats

Sa'an nan kuma muka samu wani sa ran sakamakon:

asali

Bobtail

curl

mau

Bobtail

Burmilla

Kamar yadda ka gani, Bobtail irin aka ninninkuwa sau biyu. Wadansu shawara don kauce wa kwafi, muna bukatar kawai don ƙara tambaya:

Zabi jinsin irin daga Cats

bayani request

A gaskiya, kusan babu wani request ya aikata ba fitarwa da data matsayin cikakken sa na layuka a cikin tebur. La'akari da wani tsari ga Zabi (SQL) zai ba da damar domin ayyana da sharudda ga zabin da kawai da hakkin Lines.

Saboda haka wannan dukiya ne ina. A tsari ya shafi cikin predicate - mai matukar magana, wanda ya bada fitarwa darajar ne "gaskiya" ko "ƙarya." Zabi sanarwa za su cire kawai data daga tebur domin wanda da matukar magana zai yi True, ko "gaskiya".

Ya taimaka da yawa tare da wannan shiri, mai sau samfurin. Misali muna so mu san duk game da Cats na baki launi.

zabi * daga Cats Ina color = 'Black'

A sakamakon wannan tambaya zai zama na gaba jere daga cikin tebur:

3	mau	damisa	03/30/2017	Black
5	Burmilla	Athena	08/01/2017	Black

Zaka kuma iya hada yanayi ta amfani da ma'ana aiki Kuma, Ko, Ba.

Bayar Group ta

Bayar Group ta, a yi amfani da Zabi (SQL), ba ka damar kungiyar tambayar da darajar da takamaiman shafi (ko ginshikan), sa'an nan amfani da su zuwa wani tara aiki.

Domin tara ayyuka sun hada da:

• Count - calculates da yawan layuka zaba ta hanyar tambaya.
• Sum - da ilmin lissafi Naira Miliyan Xari da duk aka zaba shafi.
• Min - m jimloli da aka zaba shafi.
• Max - da bi, matsakaicin dabi'u na zaba shafi.
• AVG - talakawan darajar.

Makirci da tsari da aka fi gane ta hanyar misali. Misali muna so mu gano yadda da yawa daga kowane irin kittens muke da shi. Don yin wannan, haifar da wadannan sauki tambaya:

Zabi irin, count (*) daga Cats Group da irin

A sakamakon nufin a cikin wadannan tebur:

asali	count
Bobtail	2
curl	1
mau	1
Burmilla	1

Kamar yadda ka gani, bobtail Cats asali da muke da biyu, wasu kamar guda kawai. A fatawar da irin wannan al'adar, dangane da mu tebur, da makiyayin iya fahimtar abin da Cats breeds ne a bukatar tsakanin abokan ciniki, da kuma abin da - babu.

Shi ne wata ila cewa saboda babbar dama shigarwar a cikin ainihin tebur har yanzu so bayyana bukatar da nuna sani kawai waɗanda kittens, wanda bai fi, misali, goma. Don bayyana ko tace kungiyoyin da ake amfani da Samun shawara. Yana yale mu mu jefar da wasu kungiyoyin, kama da ina magana, wanda discards wasu Lines. A yanayin da aka tsare a kusa da tara aiki. Cika a cikin request:

Zabi irin, count (*) daga Cats Group da irin Samun count (*) <= 10

Tun da yanayin mu aka tambayi "da yawan Cats na kowane irin ba fiye da 10", sakamakon da muka samu shi ne kamar a cikin misali, ba tare da tantancewa. Amma a nan yana da muhimmanci a fahimta makirci kanta ayyukan Samun shawara. Amma idan ka canza tunani yanayin a kan Samun count (*) = 1, sa'an nan sakamakon za a rage zuwa uku Lines da kuma nuna da kittens, wanda ya bar kawai daya.

kasawa

Get matsahi na saba da Order ta - tayin Zabi (SQL) sadarwarka da damar warware fitarwa layuka a hawa ko sauka domin a daya ko fiye ginshikan.

Yana da muhimmanci a tuna cewa Order ta - shi ne karshe tayin duk Zabi sanarwa tsarin. An sanya bayan da Zaɓa, Daga, ina, Group ta, Samun.

Akwai uku da muhimmanci da maki a lokacin da kasawa:

1) Za ka iya saka wani yawan ginshikan, kowanne daga abin da akayi daban-daban za a iya ana jerawa ko dai a hawa (ASC), ko saukowa (DESC).

2) Duk sama ginshiƙai a cikin Order da hukunci ya zama ba a cikin zaba ginshiƙai a cikin Zabi.

3) ZABI jerin takamaiman sunayen warware ginshikan, za ka iya kawai nuna su lambobi a karkashin abin da suke zuwa Zabi sanarwa.

Muna fatan cewa, ta hanyar wannan labarin, za ka samu asali ilmi game da yadda za a yi amfani da SQL queries kuma yanzu zabi zama dole bayanai daga database sauƙi.