SQL-mafunso lamulo

SQL - chimodzi mwa zinenero zambiri mapulogalamu kulenga ndi kusamalira Nawonso achichepere, komanso zosiyanasiyana zochita ndi deta yokha.

Monga mmene kumapeto, n'zosavuta kwambiri kuti ntchito ndipo zimapangitsa ambiri mawu muyezo wa Chingerezi. Mofanana ndi chinenero mapulogalamu, ndi SQL ali mfundo yake ndi malembedwe, ndi malamulo zikuluzikulu ndi malamulo awo ntchito.

Gulu la malamulo SQL

Onse malamulo muyezo SQL tinganene kuti potengera kopita kwawo. Monga maziko gulu vneglasnoy akhoza kutenga waika monga:

1. QUERY malamulo.

2. Magulu anamanga-mu njira ndi ntchito.

3. akapsa ndi matebulo dongosolo malamulo anga.

4. Akanema wa ma ntchito ndi tsiku ndi chingwe zosintha.

5. Amalamula ntchito deta ndi magome.

Gawo ili zambiri, koma zikuluzikulu chinenero SQL adzabweretse lamulo zimamangidwa ndendende chifukwa cha mitundu.

Poganizira gulu la chinenero, osanenapo kuti kuli padziko lonse, umboni ndi kuchuluka kwa ntchito. chinenero mapulogalamu ndi mitundu yake zosungidwazo zimagwiritsidwa ntchito osati malo muyezo komanso mapulogalamu ena, amene mwanjira ina, mwagwiritsa ntchito.

Kuchuluka kwa ntchito SQL angaonedwe pa kaonedwe ka mapulogalamu ofesi, ndicho MicrosoftAccess. chinenero, kapena kani, mitundu yake - MySQL, mungathe kupereka kwa Nawonso achichepere Internet. Ngakhale Oracle chilengedwe chitukuko zochokera ntchito zawo malamulo SQL a mafunso.

Kugwiritsa SQL mu MicrosoftAccess

Mmodzi wa ambiri ntchito chinenero mapulogalamu Nawonso achichepere mapulogalamu phukusi imatengedwa MicrosoftOffice. Kuphunzira pulogalamu amapereka Maphunziro a sayansi kompyuta, ndiponso ali mu sitandade khumi imatengedwa ndi kasamalidwe Nawonso achichepere dongosolo MicrosoftAccess.

Ndi kuphunzira ntchito ophunzira adziwane bwino ndi chitukuko chinenero cha zinasokoneza makompyuta ndi kumvetsa zinthu zonse m'gulu. SQL Access timu m'malo wosazindikira, ndithudi, ngati tikambirana iwo m'njira akatswiri. Akuthamanga malamulowa ndi losavuta kwambiri ndipo ndinazolowera mu mkonzi code.

Taganizirani chitsanzo chindunji:

Sankhani Pe_SurName

KUCHOKERA Pherson

KUMENE Pe_Name = 'Mary;

Zochokera malembedwe lamulo, inu mukhoza kumvetsa kuti akubwerera wosuta dzina la munthu, mu nkhani iyi, mayi wina dzina lake Mary, amene kusungidwa mu tebulo Keyala Nawonso achichepere.

Ngakhale ntchito SQL mu Access okha, nthawizina ngati mafunso zosavuta kungathandize kwambiri wosalira kukhazikitsa ntchito yanu.

Ntchito mfundo SQL mu Oracle

Oracle - mwina yekha Microsoft SQL Server mpikisano kwambiri. Ndi izi chilengedwe chitukuko ndi kasamalidwe Nawonso achichepere ndi zonse kumabweretsa kusintha kwa mapulogalamu kampani mbali Microsoft mankhwala monga mpikisano - ndi injini ikuyendera. Ngakhale kupikisana zonse, SQL Oracle timu mobwerezabwereza SQL. Tikumbukenso kuti ngakhale Oracle ndi imatengedwa pafupifupi buku wodzala ndi SQL, nzeru za dongosolo lino, ndi chinenero anthu amaona mosavuta.

Oracle dongosolo ntchito akonzedwa yeniyeni ya malamulo si chotero dongosolo zovuta. Ngati tikulingalira mphamvu deta ya mapangidwe Nawonso achichepere chitukuko, Oracle alibe kapangidwe zovuta za mafunso zinapeza malo okhala.

kusiyana Zimenezi zimathandiza nthawi zambiri mofulumira ntchito deta, koma, mosiyana, kumabweretsa ntchito inefficient kukumbukira nthawi zina payekha. Oracle dongosolo kwakukulukulu anamanga pa matebulo kanthawi ntchito zawo. Mwachitsanzo: malamulo SQL mu dongosolo zochokera kufanizira ndi mfundo za chinenero SQL yokha, ngakhale kwambiri amasiyana izo.

SELECTCONCAT (CONCAT (CONCAT ( 'Ntchito', sname), CONCAT (SUBSTR (fname, 0 1), SUBSTR (otch, 0 1))), CONCAT ( 'prinyatnarabotu, acceptdate)) KWA antchito KUMENE acceptdate> to_date ('01 .01.80 ',' dd.mm.yyyy ');

kafufuzidwe Izi adzabwerera deta za antchito, amene amagwira ntchito kwa nthawi winawake wa nthawi. Ngakhale dongosolo anuwa ndi wosiyana Microsoft SQL Server, kuphedwa kwa SQL akulamula mu kachitidwe aka ndi ofanana, kusiyapo mfundo zazing'ono.

Kugwiritsa SQL pa Intaneti

Mkubwela kwa World Lonse Web, mwachitsanzo Intaneti, chinenero SQL likukula kuchuluka kwa ntchito. Monga amadziwika, maukonde awasungira zambiri, koma si wachisokonezo, ndipo anaika pa Websites ndi maseva malinga ndi muyezo winawake.

Pakuti kusunga pa Intaneti, komanso mu malo ena, ali ndi udindo mwachindunji Nawonso achichepere, ndi malo ali kulamulira kachitidwe. Childs, malo ndi malamulo amachitika m'zinenero zosiyanasiyana mapulogalamu, koma Nawonso achichepere zachokera choyimira cha SQL, ndipo ndi chinenero cha chilengedwe Nawonso achichepere, zochokera pa MySQL polumikizira intaneti.

The malembedwe ndi kuika yaikulu ya malamulo kuti chinenero kwathunthu kutengera SQL zonse bwino, ndi zina zowonjezera wake, amene kumupatsa mosiyana Microsoft tSQL yawonongeka.

SQL akulamula ofanana kwathunthu osati malembedwe komanso muyezo mawu amenewa ntchito. Kusiyana komwe kulipo ndi chopempha kuitana ndi structuring. Mwachitsanzo, taganizirani kupempha kuti jambulani tebulo latsopano, iye ali woyamba zimene amaphunzitsa ana m'masukulu pa kompyuta:

$ Link = mysqli_connect ( 'localhost, "muzu",' ',' Tester ');

ngati (! $ kugwirizana) kufa ( "Mphulupulu");

$ Akafufuzidwe = 'alenge owerenga tebulo (

kulowa VARCHAR (20)

achinsinsi VARCHAR (20)

');

ngati (mysqli_query ($ kugwirizana, $ kafufuzidwe)) Echo "Table zinalengedwa.";

elseecho "tebulo The sichinayambe analenga:" .mysqli_error ();

mysqli_close ($ kugwirizana);

Monga Chifukwa pempho, ndipo mukhoza kupeza tebulo "ogwiritsa" latsopano m'menemo kudzakhala minda iwiri: lolowera achinsinsi.

Malembedwe anasintha pansi pa Web, koma zachokera MicrosoftSQLServer timu.

Kumanga Zikutsogola MicrosoftSQLServer

Chitsanzo cha makamaka ya matebulo deta ndi imodzi mwa ntchito zazikulu za SQL. Pakuti ntchito zimenezi, anapereka sankhani lamulo mu SQL. Ndicho za izo takambirana pansipa.

Terms of lomanga gulu ndi ophweka, ndipo sankhani kwambiri lamulo mu SQL inamangidwa motere. Mwachitsanzo, pali tebulo lomwe deta zilipo kwa ogwira ntchito amene Mwachitsanzo, dzina la munthu. Ife angabweretse vuto kuti tebulo muyenera kusankha deta za ogwira ntchito, zomwe tsiku lobadwa - mu imeneyi ku woyamba wa January woyamba wa March chaka panopa, kuphatikiza. Pakuti chitsanzo n'kofunika kudzapereka ndi SQL lamulo, limene si mamangidwe muyezo, komanso chikhalidwe kusankha:

Sankhani kuchokera Munthu

Kodi P_BerthDay> = '01 / 01/2016 'ndi P_BerthDay <= '03 / 01/2016'

Kuphedwa kwa lamulo limeneli adzabwerera deta zonse za antchito, amene anabadwanso patsiku ndi nthawi, amene lakhazikitsidwa mwa inu. Nthawi zina mukhoza kutsutsika kusonyeza yekha dzina lotsiriza, dzina loyamba ndi patronymic wantchito wa. Kuti zimenezi zitheke, pempho kumanga njira yosiyaniranapo Mwachitsanzo, motere:

SelectP_Name - dzina

P_SurName - labambo

P_Patronimic - patronymic

kwa Munthu

Kodi P_BerthDay> = '01 / 01/2016 'ndi P_BerthDay <= '03 / 01/2016'

Komabe, izi ziri kusankha chilichonse. Iye, kwenikweni, sikumangotikhudza chilichonse, koma likunena zambiri. Koma ngati mwaganiza mopepuka chinenero SQL, muyenera kuphunzira mmene kusintha Nawonso achichepere, chifukwa yomanga awo popanda sangathe. Kodi zimenezi izo ayankhidwa pansipa.

Kuweta SQL akulamula kusintha deta

malembedwe wamangidwa osati kwa querying, komanso mpheto deta. Kwenikweni, ntchito Nawonso achichepere mapulogalamu ndi kulemba script kwa zitsanzo ndi malipoti, koma nthawi zina muyenera kusintha kwa tebulo. SQL akulamula mndandanda zimenezi ang'ono ndi tichipeza magulu atatu akulu akulu:

1. Amaika (trans. Amaika).

2. Pezani (trans. Pezani).

3. Chotsani (trans. Chotsani).

Cholinga cha magulu awa ndi zosavuta kudziwa, ndi wokwanira kumasulira monga dzina lawo. Kulamula ndi yosavuta kugwiritsa ntchito ndipo alibe kumanga zovuta chiwembu, koma ndi ponena kuti ena a iwo, ngati ntchito molakwika, akhoza zoipa mpaka kalekale kwa Nawonso achichepere ofunika.

Monga ulamuliro, pamaso ntchito malamulo awa MSSQL muyenera kuganizira ndi kuganizira zotheka mavuto onse awo kukhazikitsa.

Ataphunzira malamulo amenewa, mudzatha kuti athe kuyamba ntchito ndi matebulo Nawonso achichepere, potero kusintha ndi ena atsopano zosintha ofotokozera kapena kuchotsa akale.

Ikani timu

Amaika deta mu tebulo ntchito gulu otetezeka - Ikani. Molakwa anaikapo deta nthawi zonse zotheka kuchotsa ndi kuwonjezera Nawonso achichepere kachiwiri.

Amaika lamulo amaika deta latsopano mu tebulo ndi limakupatsani kuwonjezera ngati lonse, ndi kusankha.

Mwachitsanzo, taganizirani kulowetsa lamulo mu m'mbuyomu anafotokoza Munthu tebulo. Pofuna kuti SQL lamulo zikuyenera mu tebulo deta, amene amalola amaika deta onse tebulo kapena lembani izo kusankha.

Amaika mu munthu

Sankhani 'Grigoriev' 'Vitali', 'Petrovich', '1/1/1988'

MS SQL Seva malangizo amenewa dongosolo ali basi wodzazidwa ndi maselo onse a tebulo ndi deta mwachindunji. Pali zinthu komwe wantchito alibe koyamba pakati, mwachitsanzo, adadza ku ntchito pa kuwombola ku Germany. Pankhaniyi, kuthamanga kutsatira lamulo oyika deta, umene udzabweretse gome okha zinthu zofunikira. The malembedwe lamulo ili:

Insertintoperson (P_Name, P_SurName, P_BerthDay)

Makhalidwe (Davide ',' mbedza, '2/11/1986')

gulu chimadzaza yekha mwachindunji khungu, ndi ena onse adzakhala null.

Lamulo kusintha deta

Kusintha deta monga mzere lonse, ndi ena maselo ntchito Pezani SQL lamulo. Kuchita zimene lamulo limeneli ayenera okha zinthu zina, ndicho ndendende zikusonyeza zimene mzere wa nambala zofunika kusintha.

Pezani SQL lamulo ali ndi malembedwe yosavuta. Kuonetsetsa ntchito moyenera, inu mwachindunji amene deta mu ndime ndi mbiri ayenera kusinthidwa. Kenako, kulenga script, motsatira izo. Tiyeni tione chitsanzo. Tiyenera kusintha tsiku lobadwa wa Davide mbedza, zomwe zili m'gulu la tebulo ntchito pa nambala 5.

Pezani Munthu

Ikani P_BerthDay = '02 / 10/1986 'kumene P_ID = 5

Chikhalidwe (mu script a) sadzasintha tsiku la kubadwa kwa zolembedwa zonse gome, ndi zosinthika yokha zofunika.

Ndi izi timu ya mapulogalamu ntchito zambiri, chifukwa amalola kusintha deta tebulo pasanabuke zinawonongeka kwambiri kuti mudziwe lonse.

Amalamula kugwiritsa ntchito njira ndi ntchito anamanga-

Mothandizidwa ndi chinenero SQL, simungathe amanga mafunso, komanso kulenga njira anamanga-mu ntchito deta. Monga ulamuliro, pali nthawi zina pamene mukufuna kugwiritsa ntchito mu thupi la chitsanzo kafufuzidwe olembedwa kale.

Kuweruza mwanzeru ndiye muyenera kukopera ndi muiike lemba nyemba mu malo oyenera, koma mungathe kuchita njira yachidule. Taganizirani chitsanzo kumene ntchito mawonekedwe anasonyeza batani kusindikiza lipoti Mwachitsanzo mu kupambana. Opaleshoni imeneyi adzachitidwa akamavutika. Zolinga, kodi anamanga-mu njira kusungidwa. Malamulo SQL mafunso, mu nkhani iyi, ndi mu ndondomeko komanso chifukwa cha gulu SQLExec.

Tiyeni tiyerekeze kuti ndondomeko achire masiku a kubadwa kwa ogwira ntchito ndi kale anafotokoza Munthu tebulo analengedwa. Pankhaniyi, palibe kulemba kafufuzidwe lonse. Pofuna kupeza zofunika akukwana kudzapereka lamulo Exec [ndondomeko dzina] ndi pochitika magawo zofunika zochepa chabe pamalangizo. Mwachitsanzo tingaganizire limagwirira kwa chilengedwe cha chikhalidwe ngati wochita:

CREATEPROCEDUREPrintPerson

@DB smalldatetime

@DE smalldatetime

AS

Khazikitsani NOCOUNT ON;

Sankhani kuchokera Munthu

KUCHOKERA HumanResources.vEmployeeDepartmentHistory

KUMENE P_BerthDay> = @DB ndi P_BerthDay <= @DE

ANDEndDateISNULL;

ATENGEDWA

njirayi akubwerera mfundo zonse za antchito amene anabadwanso patsiku adzakhala mu nthawi wopatsidwa nthawi.

Organization deta okhulupirika. akapsa

Ena MS SQL-lamulo, wina akhoza kunena, kapangidwe akhoza osati bungwe deta mpheto, komanso kuonetsetsa okhulupirika. Zolinga ngati m'chilankhulo cholinga dongosolo kamangidwe, amene amalenga mapulogalamu yekha. Awa amatchedwa akapsa, amene angathe kupereka ulamuliro kwa deta.

Pankhaniyi, kwa gulu la zinthu mayeso ntchito mfundo SQL-mafunso lamulo. Mu akapsa, inu mukhoza kulenga kwambiri a zinthu ndi zofooka kwa deta kuti zidzalamulira mwayi osati kuti mudziwe komanso limaletsa kuchotsa, kusinthidwa kapena kumanga deta.

Mitundu ya malamulo SQL kuti angagwiritsidwe ntchito choyambitsa, osati zochepa. Taganizirani chitsanzo chotsatirachi.

Ngati ife kufotokoza limagwirira polenga dziko choyambitsa, ndiye mitundu ya malamulo SQL ndi amodzi polenga ndondomeko a. aligorivimu chidzatambasulidwa pansipa.

Sitepe yoyamba ndi kufotokoza lamulo utumiki kulenga akapsa:

Pangani choyambitsa Person_Insert

mfundo ina kwa tebulo:

ONPerson

Limasonyeza chimene ntchito deta (mu nkhani iyi, deta kusintha ntchito).

Sitepe yotsatira ndi mwachindunji matebulo ndi zosintha:

kulengeza @ID Int. @Date smalldatetime @nID Int. @nDatesmalldatetime

Komanso kulengeza cursors kusankha matebulo deta kuchotsa ndi mayikidwe a deta:

Fotokozerani cholozera C1 kwa osankhidwa P_ID, P_BerthDay ku anaikapo

Fotokozerani cholozera C2 kwa osankhidwa P_ID, P_BerthDay ku zichotsedwa

KUFOTOKOZERA njira deta kusankha. Kamodzi mu thupi cursors mankhwala chikhalidwe ndi anachita izo:

ngati @ID = @nID ndi @nDate = '01 / 01/2016 '

ayamba

sMasseges 'Thamangani ntchito zosatheka. Tsiku si bwino '

kumapeto

Ndi ponena kuti choyambitsa silingakhoze kokha kulenga, koma zimitsani kwa kanthawi ofunika. mpheto amenewa amatha kusunga okha mapulogalamu kuchita SQL Seva lamulo:

altertablePERSONdisabletriggerall - kuletsa akapsa zonse zinalengedwa kwa gome, ndi moyenera, altertablePERSONenabletriggerall - mupezeke.

Izi SQL zofunika akulamula kwambiri amagwiritsidwa ntchito koma kuphatikiza awo akhoza kukhala yosiyanasiyana. SQL - wokonzeka kusintha kwambiri chinenero mapulogalamu ndi amapereka mapulogalamu munthu pazipita mwayi.

Pomaliza

Kuchokera pamwamba ife tikhoza kunena: SQL luso chinenero Madongosolo a anthu amene akufuna kuchita kwambiri mu dongosolo. Yankholo mtima wa zonse ntchito anachita mu Internet ndi Nawonso achichepere kunyumba. N'chifukwa chake mapulogalamu tsogolo ayenera kudziwa chiwerengero cha malamulo a chinenero, chifukwa chokha chimene iwo angakhoze kukhala, titero, kulankhula ndi kompyuta.

Kumene, pali zolakwa, monga chirichonse mu dziko lino, koma iwo ali ochepa chabe wotumbululuka pamaso nyota lapansi. Mwa chinenero onse mapulogalamu SQL pafupifupi yekhayo wa mtundu wake, chifukwa ndi chilengedwe, ndi kudziwa kulemba zolembedwa ndi zizindikiro ndi maziko a pafupifupi malo onse.

The mwayi waukulu SQL bezogovorchno tinganene kuti kuphweka, chifukwa, pambuyo pa zonse, iye ndiye anadzetsa maphunziro kusukulu. Popeza ndingathe kupirira ngakhale mapulogalamu novice, osati kwenikweni wodziwa zinenero.