SQL-queries murayiro

SQL - mumwe inonyanya zvirongwa mitauro rokusika uye kutarisira Database, uyewo zvakasiyana siyana nezviito chete mashoko pachayo.

Sezvo tsika inoratidza zviri zvikuru nyore kushandisa uye inoita zvikuru vomureza Mutauro kuti mutauro Chirungu. Kufanana mimwe mutauro zvirongwa, iri SQL ine yayo kufunga nemarongerwo, boka mirayiro chikuru uye mitemo yavo kushandiswa.

Kupatsanura pakati SQL mirayiro

mirayiro SQL All mureza anogona kunzi yakavakirwa kwavaienda. Sehwaro kupatsanura vneglasnoy kunogona kutora nemhando akadai:

1. kuti mubvunzo mirayiro.

2. Zvikwata pakuvara maitirwo uye mabasa.

3. ude uye maitiro pamatafura kuraira.

4. Anoita anofambidzana kushanda pamwe zuva uye tambo variables.

5. Nokuda kushanda mashoko uye pamatafura.

kupatsanura Izvi kusingaperi, asi chikuru SQL mutauro murayiro rinovira dzinovakwa chaiyo nokuda pakubhadhara izvi.

Tichitarisa kupatsanura mutauro, tisingatauri kuti inoshanda, sezvinoratidzwa kutsanangurwa kushandiswa kwayo. Izvi mutauro zvirongwa zvayo dzomutauro dziri kushandiswa kwete chete mureza ezvinhu asiwo dzimwe purogiramu, iyo, mumwe nzira kana mumwe, iwe vakashandisa.

The yezvinoreva kushandisa SQL anogona kuonekwa nemaonero muhofisi software, kureva MicrosoftAccess. mutauro uyu, kana kuti, mhando yawo - MySQL, unogona kutarisira Internet Database. Kunyange Oracle budiriro ezvinhu kwakavakirwa kwavo kushandiswa SQL mirairo queries.

Kushandisa SQL muna MicrosoftAccess

Chimwe chezvinhu zvakafanana pevara mutauro Database zvirongwa yekombiyuta anonzi MicrosoftOffice. Kudzidza-software iyi inopa nzira kombiyuta sayenzi chikoro, uye mugiredhi regumi anonzi ari Database kutarisirwa hurongwa MicrosoftAccess.

Ndicho kudzidza chikumbiro vadzidzi tinyatsoziva mutauro kukura kwakaita Databases uye kuwana nzwisiso zvose izvozvo yaisanganisira. SQL Access Chikwata nani chinyakare Chokwadi, kana tikafunga navo nyanzvi nenzira. Kumhanya mirayiro akadaro ari nyore chaizvo, uye vari chakatorwa mu bumbiro mupepeti.

Funga muenzaniso chaiwo:

Sarudza Pe_SurName

FROM Pherson

KUPI Pe_Name = 'Maria';

Zvichienderana nemurairo nemarongerwo, unogona kunzwisisa kuti anodzoka inodzidzisa zita munhu, muchiitiko ichi, mumwe mukadzi ainzi Mariya, uyo anochengeterwa tafura Contacts Database.

Kunyange kushandisa SQL muna Access iri shoma, dzimwe nguva queries nyore zvakadaro kunogona nyore zvikuru Implementation ari mabasa.

Kushandisa SQL mitsara Oracle

Pangataura - zvimwe chete Microsoft SQL Server rakakomba mukwikwidzi. Ndiwo uyu kukura zvakatipoteredza uye kurapa ari Database achigara zvinotungamirira kuvandudzika usashandisa kambani Microsoft chigadzirwa zvinhu sezvo makwikwi - ndiwo injini mberi. Pasinei chenguva tsere, SQL Oracle chikwata yakadzokorora SQL. Zvinofanira kucherechedzwa kuti kunyange Oracle uye anonzi anenge kopi izere SQL, nomusoro ino, uye mutauro wacho kazhinji vaiona nyore.

Oracle ezvinhu achishandisa imwe vanochengeta mirayiro haisi zvakaoma kunzwisisa zvakadaro. Kana tikafunga mashoko nezvaanogona ose Database kukura runyararo, Oracle asina kunzwisisa maumbirwo nested queries.

musiyano Izvi zvinobvumira kakawanda kuti rikurumidze basa pamwe data, asi, kusiyana, inotungamirira va neudhadha kushandisa ndangariro, mune zvimwe nomumwe zviitiko. Oracle mamiriro zvikurukuru kuvakwa pamusoro pfupi matafura avo kushandiswa. Somuenzaniso: SQL mirayiro munyika inotorwa ringanidzo mutauro SQL pacharo mitemo, kunyange asina yekudyidzana kwakasiyana pairi.

SELECTCONCAT (CONCAT (CONCAT ( 'Employee', sname), CONCAT (SUBSTR (fname, 0, 1), SUBSTR (otch, 0, 1))), CONCAT ( 'prinyatnarabotu', acceptdate)) KUBVA vashandi KUPI acceptdate> to_date ('01 .01.80 ',' dd.mm.yyyy ');

mubvunzo uyu achadzoka mashoko pamusoro vashandi, avo vanoshanda kuti imwe nguva. Kunyange zvazvo mubvunzo mamiriro rakasiyana Microsoft SQL Server, kuurayiwa SQL rinorayira mu izvi hurongwa akafanana, kunze zviduku zvakawanda.

Kushandisa SQL paIndaneti

With veMubatanidzwa World Wide Web, kureva Indaneti, mutauro SQL iri kuwedzera kukura kwebasa kushandiswa. Sezvo chikazozikamwa, nemambure inochengeterwa zvakawanda, asi hazvina runobva, ndokuisa pamusoro Websites uye servers maererano zvimwe zvinodiwa.

Kuchengeta mashoko paIndaneti, uyewo dzimwe nzvimbo, vane basa zvakananga Database, uye site kuzvidzora enyika. Kazhinji, nzvimbo uye bumbiro remitemo Vakarongwa mapurogiramu mitauro yakasiyana, asi Database kunobva mufananidzo SQL, uye ndicho mutauro Database yokusika, nezvatinovavarira panguva MySQL padandemutande interfaces.

The nemarongerwo uye inokosha akagadzirwa mirayiro mutauro chose kutevedzera zvose vanoziva SQL, navamwe kuwedzeredza, iyo umupe kusafanana Microsoft tSQL Server.

SQL rinorayira zvachose zvakafanana kwete chete nemarongerwo asiwo mureza iine basa mashoko. Musiyano chete ari kudaidzwa chikumbiro uye yokuronga patsva. Somuenzaniso, funga chikumbiro kuumba tafura idzva, iye ndiye wokutanga pezvavanodzidzisa vana muzvikoro pakombiyuta:

$ Link = mysqli_connect ( 'localhost', "mudzi", '', 'tester');

kana (! $ linked) kufa ( "Error");

$ Query = 'kusika tafura vanoshandisa (

login VARCHAR (20)

pasiwedhi VARCHAR (20)

');

kana (mysqli_query ($ kubatana, $ mubvunzo)) ungira "Table zvakasikwa.";

elseecho "Tafura haina kusikwa:" .mysqli_error ();

mysqli_close ($ linked);

Somugumisiro chikumbiro yakadaro, unogona kuwana mutsva tafura "vanoshandisa", umo pachava minda maviri: Username uye password.

Nemarongerwo zvakachinja pasi Web, asi kunobva MicrosoftSQLServer chikwata.

Building queries MicrosoftSQLServer

The muenzaniso imwe akagadzirwa date pamatafura ndeimwe chikuru mabasa ose SQL. For kwacho, akapa vakasarudzwa murayiro muna SQL. Ndizvo nezvazvo inokurukurwa pazasi.

Terms of chikwata chivakwa vari nyore zvikuru, uye murayiro sarudzai kwazvo SQL inovakwa sezvinotevera. Somuenzaniso, pane imwe tafura umo mashoko anowanikwa vashandi avo Somuenzaniso, zita Yomunhu. We pozi dambudziko kuti tafura unofanira kusarudza mashoko pamusoro vashandi, iyo zuva rekuzvarwa - iri penguva kubva rokutanga January kusvika rokutanga Kurume gore ano, zvichibatanidzwa. Nokuti mharidzo yakadaro kunokosha kuuraya munhu SQL murayiro, zvisina chete mwero design, asi vasanangurwa ezvinhu:

Sarudza kubva Person

Where P_BerthDay> = '01 / 01/2016 'uye P_BerthDay <= '03 / 01/2016'

Achipa murayiro uyu achadzoka mashoko ose pamusoro vashandi, rakaberekwa iri nguva, izvo yakatogadzirwa nemi. Dzimwe nguva unogona akashora kuratidza chete yokupedzisira zita, wokutanga zita uye patronymic yacho mushandi. Kuti izvi zvidaro, chikumbiro kuvaka siyanei nzira Somuenzaniso, sezvinotevera:

SelectP_Name - zita

P_SurName - zita rechipiri

P_Patronimic - patronymic

kubva Person

Where P_BerthDay> = '01 / 01/2016 'uye P_BerthDay <= '03 / 01/2016'

Zvisinei, iyi inongova kusarudzwa chinhu. Iye, chaizvoizvo, asingadi kukanganisa chinhu, asi chete kunopa mashoko. Asi kana ukasarudza kufungisisa mutauro SQL, unofanira kudzidza nzira kuchinja kuti Database, sezvo kuvakwa dzavo pasina hazvitomboiti. Izvi zvinoitwa nayo ichakurukurwa pazasi.

Basic SQL anoraira kuchinja mashoko

The nemarongerwo inovakwa kwete chete nokuda querying, asiwo mashoko evanhu. Zvikurukuru, basa Database programmer ari kunyora manyorero nokuda ivhu nemishumo, asi dzimwe nguva unofanira kuchinja kuti tafura. SQL anorayira mazita yezviito yakadaro diki uye chinosanganisira chikuru zvikwata zvitatu:

1. Pinza (mhiri. Pinza).

2. Update (mhiri. Update).

3. Kudzima (mhiri. Bvisa).

Chinangwa zvikwata izvi zviri nyore kuti asarudze, zvakakwana chete kushandura zita ravo. Izvi mirayiro zviri nyore kushandisa uye havana yakaoma kuvaka zano, asi zvinokosha kutaura kuti vamwe vavo, kana ikashandiswa zvisina kufanira, anogona kukonzera irreparable kukuvadza Database.

Kazhinji, asati kushandisa mirayiro MSSQL unofanira kufunga uye kufunga zvose zvinobvira nemigumisiro Implementation kwavo.

Tadzidza mirayiro, uchakwanisa kutanga zvakazara kushanda Database matafura, nokudaro yokugadziridza nayo uye kuti mamwe matsva variables tsananguro iri kana kubvisa kare.

Insert chikwata

Kuti pinza date ive tafura uchishandisa zvikuru chikwata - Insert. Zvisiri akaisa date zvinogoneka nguva kubvisa uye kuwedzera zvakare kuti Database.

Pinza murayiro pinza umboo itsva patafura uye anobvumira kuti kuwedzera somunhu kwakakwana yakatarwa, uye zvokusarudza.

Somuenzaniso, funga kuisa murayiro iri kare anorondedzerwa tafura Person. Kuitira kuti SQL murayiro inofanira nenzira date tafura, izvo zvinoita kuti dzinoisa ose mashoko ari tafura kana kuzadza nayo zvokusarudza.

Pinza kupinda munhu

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

MS SQL Server murayiro chirongwa chakadai rwoga vakazadzwa vose masero petafura pamwe wainyatsotaura mashoko. Pane mamiriro ezvinhu apo mushandi asina kutanga pakati Somuenzaniso, akauya kuzoshanda Exchange kubva kuGermany. Panyaya iyi, mhanya zvinotevera murayiro inoisa data, izvo zvichaita kuuya tafura chete chii chinodiwa. The nemarongerwo murayiro uyu sezvinotevera:

Insertintoperson (P_Name, P_SurName, P_BerthDay)

Tsika ( 'David', 'Hook', '2/11/1986')

chikwata ichi anozadza chete yakanatsa muchitokisi, uye vamwe vose vachava null.

Murayiro kuchinja mashoko

Kushandura mashoko yose mutsetse, uye dzimwe masero vanoshandisa Update SQL murayiro. Kuita murayiro uyu inoda chete zvimwe zvinhu, kureva, kunyatsoreva anoratidza umo mutsara nhamba zvakakodzera kuchinja.

Update SQL murayiro ane nyore nemarongerwo. Kuti ave nechokwadi chokuti kushandisa, iwe tsanangurai iyo mashoko ari mbiru uye nhoroondo zvinofanira kushandurwa. Next, kuumba manyorero nokukuitira nayo. Funga muenzaniso. Tinofanira kushandura zuva rekuberekwa yaDhavhidhi Hook, iro rwuripowo tafura mushandi pa nhamba 5.

Update Person

Set P_BerthDay = '02 / 10/1986 'apo P_ID = 5

Condition (ari manyorero) hakuzochinji musi kuberekwa zvose tafura marekodzi, uye kunatsurudza chete anodiwa.

Zviri chikwata ichi programmers vanoshandisa kazhinji, nokuti kunoita kuti kuchinja umboo iri tafura pasina kukonzera chinokosha kuvadza ruzivo rwose.

Unoraira kushandisa pakuvara maitirwo uye mabasa

Tichibatsirwa mutauro SQL, haugoni chete kuvaka queries, asiwo kuti pave pakuvara zvigadziriswe kushanda mashoko. Kazhinji, pane nguva apo muchida kushandisa muviri mubvunzo yokuenzanisira inonyorerwa ambotaurwa.

Kutonga Zvinonzwisisika, ipapo unofanira kutevedzera namatidza mharidzo rugwaro panzvimbo chaiyo, asi iwe unogona kuita nyore kugadzirisa. Funga muenzaniso apo kushanda inowanikwa akaratidza bhatani kudhinda gwaro, somuenzaniso muna Excel. oparesheni Izvi zvichaita kuitwa sezvinodiwa. Zvinangwa zvakadaro, dzinovakwa-mu zvakachengetwa zvesangano. Mirayiro SQL queries, muchiitiko ichi, vari mubishi uye zvinokonzerwa munhu SQLExec chikwata.

Ngationei ngatiti kuti nzira kuti caffeine misi nokuberekwa vashandi pamwe kare anotsanangurwa Munhu tafura akasikwa. Muchiitiko chino, hapana chikonzero kunyora rose mubvunzo. Kuwana zvinhu zvakafanira mashoko yakakwana kuuraya murayiro Exec [nzira zita] uye zvakaitika zvakakodzera parameters kuti zvakabudiswa. Somuenzaniso tinogona kufunga nomazviri kwezvisikwa kwakadaro hunhu nzira:

CREATEPROCEDUREPrintPerson

@DB smalldatetime

@DE smalldatetime

AS

IVA NOCOUNT ON;

Sarudza kuna Person

FROM HumanResources.vEmployeeDepartmentHistory

KUPI P_BerthDay> = @DB uye P_BerthDay <= @DE

ANDEndDateISNULL;

GO

nzira iyi anodzoka mashoko ose pamusoro vashandi rakaberekwa achava pamamiriro nguva kumwedzi.

Organization date kuvimbika. ude

Vamwe MS SQL-murayiro, mumwe vangatoti, magadzirirwo haagoni chete kuronga mashoko evanhu, asi kuti pave vakaperera. For zvinangwa zvakadaro mumutauro zvakagadzirirwa hurongwa design, iyo pave programmer pachake. Ava inonzi ude, izvo zvinogona kupa mashoko kudzora.

Panyaya iyi, nokuti sangano yokuedzwa mamiriro vachishandisa mureza SQL-queries murayiro. In ude, wagadzira yakawanda ezvinhu uye zvausingakwanisi kuti mashoko achaita kudzora kwete chete kuwana mashoko asiwo rambidza nokubviswa, nadzurudzo, kana pinza mashoko.

The Mhando SQL mirayiro anogona kushandiswa mune chikungiso, hakungoitwi. Chimbofunga nezvomuenzaniso unotevera.

Kana isu anotsanangura mashandiro kusika chikungiso, ipapo mhando mirayiro SQL vakafanana kana kutanga muitiro. algorithm ichatambanudzirwa kurondedzerwa pazasi.

Danho rokutanga kutsanangura basa murayiro chokusika ude:

Create rukungiso Person_Insert

Next nezve tafura:

ONPerson

Rinoratidza iyo mashoko akavhiyiwa (mu iyi, humboo kushandura oparesheni).

Danho iri kutaridzwa matafura uye variables:

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

Uyezve ndizivise cursors kusarudza mashoko pamatafura Kubviswa uye kupinza kuti mashoko:

Dudzirai cursor C1 kuti vakasarudzwa P_ID, P_BerthDay kubva akaiswa

Dudzirai cursor C2 kuti vakasarudzwa P_ID, P_BerthDay kubva dzadzimwa

Nokududza date kusarudza matanho. Kamwe mumuviri cursors varayire ezvinhu uye maitiro kwairi:

kana @ID = @nID uye @nDate = '01 / 01/2016 '

kutanga

sMasseges 'Run oparesheni hazviiti. Date hakusi kukodzera '

magumo

Zvinokosha kutaura kuti chikungiso haagoni chete kusika, asiwo kudzima kwekanguva. bata kwakadaro kunogona kuchengeta chete programmer achiita SQL Server murayiro:

altertablePERSONdisabletriggerall - chokukuvadza ude zvose zvakasikwa kuti tafura, uye, naizvozvovo, altertablePERSONenabletriggerall - nokuti usanganidzo.

Izvi zvinokosha SQL rinorayira anowanzodanwa kushandiswa, asi mubatanidzwa wavo kunogona siyana chaizvo. SQL - aida kuchinja chaizvo zvirongwa mutauro uye anopa yokuvaka vaifanira zvingangoitika.

mhedziso

Kubva apa tinogona kungogumisa: SQL mutauro unyanzvi kunofanira vaya vanoda kuita zvakakomba mune zvirongwa. It chiri pamwoyo remapazi zvose kuitwa Internet uye pamba Database. Ndokusaka ramangwana programmer anofanira kuziva uwandu mirayiro mutauro, nekuti chete vanogona kuva, saka kutaura, kutaura kombiyuta.

Chokwadi, pane zvausingakwanisi, sapamazuva zvose munyika muno, asi vari diki kuti anongoti hapana pamberi dhigirii. Pakati ose mutauro SQL zvirongwa iri rinenge chete nerudzi rwayo, nokuti nokudenga, uye ruzivo achinyora mumanyoro uye Codes ndivo hwaro anenge nzvimbo dzose.

The chikuru mukana SQL bezogovorchno anogona kunzi kwayo nyore, nokuti, pashure zvose, ndiye akatanga kupinda chikoro hweutungamiri. Sezvo kuita kunyange ruzivo programmer, kwete chaizvoizvo aiziva mitauro.