Operator SQL ciki JOIN: misalai na ginin kalma da kuma siffofin

Developing wani database ya hada da ba wai kawai da halittar kuma da cika teburorin da daban-daban bayani, amma kuma kara aiki tare da bayanai. Don daidai yi wani iri-iri ayyuka a zabi na data daga allunan da bayar da rahoto, amfani da daidaitattun Zabi zane.

Dibo data daga allunan

Idan muka yi la'akari da matsalar da zabi na data ko gina wani rahoto, za ka iya sanin ko matakin da wuya daga cikin aiki. Kamar yadda mai mulkin, lokacin da akayi tare da tsanani (bisa ga girma) databases, wanda aka kafa, misali, a cikin online Stores, ko manyan kamfanoni, data selection ba a iyakance ga guda tebur. Matsayin mai mulkin, da samfurin iya zama daga wani fairly manyan yawan ba kawai nasaba Tables, amma kuma nested queries / sub-queries, abin da ya sa cikin shirye-shiryen, dangane da aiki. Ga wani samfurin na daya tebur iya amfani da sauki tsarin:

inda mutum - sunan tebur daga abin da kuke so yin data gyara.

Idan, duk da haka, za ka bukatar ka zabi data daga mahara Tables, daya daga cikin misali kayayyaki za a iya amfani da su shiga mahara Tables.

a haɗa ƙarin alluna Hanyar

Idan muka yi la'akari da yin amfani da irin wannan Tsarin a firamare, za mu iya bambanta da wadannan sunadaran don haɗa ake bukata da dama ga samfurin allunan, wato:

1. Operator Inner Join.
2. Hagu Join ko, shi ne na biyu rikodi hanya, Hagu matsanancin Join.
3. Cross Join.
4. Cikakken Join.

Amfani da aiki da su shiga Tables a yi, za ka iya koyi da la'akari da amfani da SQL sanarwa - Inner Join. Wani misali na yin amfani zai zama kamar haka:

Harshe SQL da Join Inner Join sadarwarka za a iya amfani da su kawai domin hada biyu ko fiye da alluna, amma kuma a haɗa shi da sauran sub-queries, wanda ƙwarai facilitates aikin na database ma'aikata, da kuma, kamar yadda mai mulkin, zai iya muhimmanci hanzarta aiwatar da takamaiman, rikitarwa tambaya Tsarin.

Hada data a allunan line ta line

Idan muka yi la'akari da dangane da wani babban yawan sub-queries, da kuma data tattara a cikin wani aure tebur line ta line, ka kuma iya amfani da aiki da kungiyar tarayyar da kuma kungiyar tarayyar All.

A amfani da wadannan Tsarin zai dogara ne a kan developer zuwa kafa manufofin da sakamakon abin da ya so ya cimma a karshen.

Description Inner Join sadarwarka

A mafi yawan lokuta, to hada mahara Tables a cikin SQL harshen amfani da Inner Join sadarwarka. Description Inner Join a SQL ne quite sauki fahimta da talakawan shirye-shiryen wanda aka kawai fara fahimta da bayanai. Idan muka yi la'akari da bayanin da ginshikai na aikin da wannan tsarin, za mu samu wadannan hoto. A dabaru sadarwarka matsayin dukan da aka gina a kan mararraba da kuma yiwuwar Samfur kawai data cewa shi ne a cikin kowane daga cikin alluna kunshe a cikin tambaya.

Idan muka dubi wannan irin aiki cikin sharuddan hoto fassarar, mun samu SQL Inner Join sadarwarka tsarin, misali na wanda za a iya nuna by da wadannan makirci:

Alal misali, muna da alluna biyu, aka nuna schematically a cikin adadi. Su, bi da bi, da wata lambar daban na shigarwar. A kowane daga cikin alluna da filayen cewa suna da alaka. Idan muka yi kokarin bayyana aikin sadarwarka a kan tushen da zane, da koma sakamakon zai zama mai sa na records daga alluna biyu, inda da dakuna ne juna filayen daidaita. Kawai sa, tambayar ya koma kawai ne ga waɗanda records (a teburin lambar biyu), wanda data ne a lamba daya tebur.

A ginin kalma daga cikin Inner Join

Kamar yadda aka ambata a baya, da sadarwarka Inner Join, wato, ta ginin kalma, unusually sauki. Domin kungiyar na dangantaka tsakanin allunan cikin guda samfurin zai isar wa tuna da amfani da wadannan Schematic zane na yi na afareta, wanda aka wajabta a guda line na software SQL-code, wato:

• Inner Join [Table Name] a [a key filin daga cikin tebur zuwa wanda connect] = [pluggable key filin daga cikin tebur].

Don sadarwa a cikin wannan bayani ne yafi amfani da allunan keys. Matsayin mai mulkin, a cikin rukuni na alluna da adana bayanai game da ma'aikata, baya aka bayyana Mutum reshe kuma da a kalla daya kama rikodin. Saboda haka, dauki kusa duba da sadarwarka SQL Inner Join, wani misali na wanda aka nuna kadan a baya.

MISALI bayanin da dangane da audio samfurin tebur

Muna da mutum tebur, wanda Stores bayani game da duk ma'aikata aiki a cikin kamfanin. Kamar lura cewa master key na tebur ne a filin - Pe_ID. Just a kan shi da kuma za su tafi wani cuta.

Na biyu tebur reshe zai adana bayanai game da ofisoshin a cikin abin da ma'aikata aiki. Ta, bi da bi, an haɗa via Su_Person filin Mutum tebur. Menene wannan yake nufi? Bisa ga bayanan tsari za a iya ce cewa a cikin tebur raka'a ga kowane rikodin a tebur "Ma'aikata" zai zama bayani game da sashen a cikin abin da suka yi aiki. Shi ne wannan sadarwar da zai yi aiki sadarwarka Inner Join.

Domin a more ilhama amfani da la'akari da sadarwarka SQL Inner Join (misalai na ta yin amfani for daya ko alluna biyu). Idan muka yi la'akari da misali daga cikin wannan tebur, to, duk da quite sauki:

MISALI gama alluna biyu da subquery

Operator SQL Inner Join, misalai daga waxanda suke amfani da su mai da data daga mahara alluna za a iya shirya a sama hanya, shi ne aiki a kan kadan wahala manufa. Don wahalad da aiki na alluna biyu. Alal misali, dole mu tafi tebur cewa Stores bayani game da duk sassan a kowane daga cikin ƙungiyoyin ba. A wannan tebur rubuta division lambar kuma ma'aikaci lambar, kuma dole ne a supplemented da sunan samfurin data na kowane sashen. Neman gaba, yana da daraja yana mai cewa ba za ka iya amfani da hanyoyi guda biyu da su warware wannan matsala.

A farko hanya ne zuwa connect da sashen tebur zuwa samfurin. Tsara request a cikin wannan harka iya zama kamar haka:

Na biyu Hanyar magance matsalar - shi ne don amfani mai subquery a cikin abin da ba duk data za a zaba daga cikin sashen tebur, amma kawai ya cancanta. Wannan shi ne akasi da farko hanya, shi zai rage lokacin da request.

Shi ne ya kamata a lura da cewa irin wannan tsari ba zai iya ko da yaushe bugun sama da aikin request. Wani lokaci akwai lokuta idan ya zama dole don amfani da wani ƙarin samfurin data cikin wani wucin gadi tebur (idan ƙarfi ne ma manyan), sa'an nan an hade tare da babban samfurin.

Misali na yin amfani da Inner Join sadarwarka don samfurori daga babban adadin alluna

A yi ya shafi yin amfani da hadaddun queries ga samfurin gagarumin yawa na data alluna da kuma subqueries juna. Wadannan da bukatun iya gamsar da SQL Inner Join cakude. Misalai na yin amfani da afareta, a cikin wannan harka za a iya wuya ba kawai samfurori da yawa ajiya wurare, amma kuma tare da babban yawan nested subqueries. Domin a kankare misali, za ka iya yi bayanan daga wani samfurin na tsarin alluna (Inner Join SQL sanarwa). Misali - 3 daga cikin tebur - a cikin wannan harka za su yi wajen hadaddun tsarin.

A wannan yanayin da alaka (da primary tebur) da uku kara gabatar da dama data selection yanayi.

Lokacin amfani da Inner Join sadarwarka ya kamata su tuna cewa mafi hadaddun tambayar, da ya fi tsayi za a aiwatar, saboda haka ya kamata ka duba hanyoyin da za a fi sauri yi da kuma warware matsalar.

ƙarshe

A ƙarshe, ina so in ce abu daya: aiki tare da bayanai - wannan ba shi ne abu mafi wuya a shirye-shirye, don haka idan ka so kowane guda mutum zai iya neman ilimi don gina wani database, da kuma a kan lokaci, su sami kwarewa, ka samu aiki tare da su a kan wani kwararren matakin .