Whakahau SQL-pātai

SQL - tetahi o nga reo hōtaka tino noa ki te waihanga me te whakahaere i te pātengi raraunga, me te mo te momo o ngā mahi ki te raraunga iho.

Ka rite ki whakaaturanga mahi, ko reira rawa ngāwari ki te whakamahi, me te hanga i te nuinga o te kupu paerewa o te reo Ingarihi. Ka rite ki tetahi atu reo hōtaka, he ona ake arorau me te wetereo, te huinga o whakahau taketake me ngā ture mo ratou te whakamahi i te SQL.

Whakarōpū o whakahau SQL

Ka taea te whakaaro whakahau SQL paerewa katoa e hāngai ana i runga i to ratou ūnga. Ka rite ki te pūtake mō te whakarōpū vneglasnoy taea te tango taua huinga rite:

1. ki te uiui whakahau.

2. Teams ngā tukanga me ngā mahi i roto i te hanga-.

3. pāngia e me tepu pūnaha whakahau.

4. Ka tautuhi o ngā ki te mahi ki te rā, me te aho taurangi.

5. Whakahau hoki te mahi ki te raraunga, me te tepu.

Ko te mutunga tenei whakarōpū, engari kua hanga nga huinga whakahau reo SQL taketake mea ïa no te mea o enei momo.

Whakaaro i te whakarōpū o te reo, e kore e ki te whakahua i taua ko reira ao, pera kitea e te hōkai o tona whakamahi. E whakamahia ana tēnei reo hōtaka me ona rerekē kore anake i roto i te taiao paerewa engari ano i roto i te tahi atu hōtaka, e, tetahi ara ranei tetahi, kua whakamahia e koe.

Ka taea te titiro te hōkai o te whakamahi o SQL i te tirohanga o te pūmanawa tari, ara MicrosoftAccess. Tenei reo, kaua ranei, ona momo - MySQL, ka taea e koe te whakahaere i te pātengi raraunga Ipurangi. Ahakoa taiao whanaketanga Oracle i runga i to ratou whakamahi i roto i whakahau SQL o uiui.

Mā te SQL i roto i MicrosoftAccess

Ko tētahi o nga whakamahinga reo tino noa mō te mōkihi pūmanawa hōtaka pātengi raraunga whakaaro te MicrosoftOffice. Ko te ako o tenei pūmanawa whakarato te akoranga kura o pūtaiao rorohiko, me te tekau ma tahi i te kōeke whakaaro te he pūnaha whakahaere pātengi raraunga MicrosoftAccess.

Ko reira te ako o te tono kia mohio te ākonga ki te whanaketanga reo o pātengi raraunga , me te whiwhi i te māramatanga taketake o te katoa ngā reira. kapa SQL Access pai tahito, o te akoranga, ki te whakaaro tatou ratou i roto i te tikanga ngaio. Ko te tino māmā rere taua whakahau, a kua urutau ratou i roto i te kaiwhakatika waehere.

Fakakaukau angé ki he tauira motuhake:

Pe_SurName Tīpakohia

MEI Pherson

HEA Pe_Name = 'Meri';

I runga i te wetereo whakahau, ka taea e matau koe e hoki reira te ingoa kaiwhakamahi o te tangata, i roto i tenei take, he wahine ko Meri, e te rongoa i roto i te ripanga Hoapā pātengi raraunga.

Ahakoa iti te whakamahi o SQL i Access te, i ētahi wā e taea nui taua uiui ohie faaohie te whakatinanatanga o te mahi i faataahia.

Mā te tauākī SQL i Oracle

Oracle - te reira pea te anake Microsoft SQL Tūmau he whakataetae nui. Ko tenei taiao whanaketanga me te whakahaere o te pātengi raraunga , ko te tonu arata'i ki te whakapai ake o te āhuatanga kamupene pūmanawa hua Microsoft rite te whakataetae - ko te pūkaha o te ahunga whakamua. Ahakoa te totohe tamau, korerotia kapa SQL Oracle SQL. Me kï reira e ahakoa whakaaro Oracle me te tata te kape tonu o te SQL, te arorau o tenei pūnaha, me te reo e te tikanga whakaaro māmā.

pūnaha Oracle te whakamahi i te huinga motuhake o whakahau kore ko te taua hanganga uaua. Ki te whakaaro tatou ki nga kaha raraunga o ngā taiao whanaketanga pātengi raraunga, e kore e Oracle i te hanganga matatini o pātai ohanga e.

Tenei rerekētanga taea maha nga wa ki te tere ake te mahi ki te raraunga, engari, i roto i te rerekē, arata'i ki te whakamahi hauarea o mahara, i roto i ētahi wā takitahi. hanganga Oracle nuinga hanga i runga i nga papa rangitahi me ratou whakamahi. Ka rite ki te tauira: hāngai ana whakahau SQL i roto i te pūnaha e runga i te faaauraa ki paerewa te reo SQL ano, ahakoa kahore tino rerekē i reira.

SELECTCONCAT (CONCAT (CONCAT ( 'Kaimahi', a maua), CONCAT (SUBSTR (fname, 0, 1), SUBSTR (otch, 0, 1))), CONCAT ( 'prinyatnarabotu', acceptdate)) MEI kaimahi HEA acceptdate> TO_DATE ('01 .01.80 ',' dd.mm.yyyy ');

Ka hoki tēnei uiui raraunga e pā ana ki ngā kaimahi, e mahi hoki i te tetahi wā o te wā. Ahakoa he rerekē i te hanganga rapu Tūmau SQL Microsoft, whakahau te mahia o SQL i roto i enei pūnaha he rite, ki te kahore hoki taipitopito iti.

Mā te SQL i runga i te Ipurangi

Ki te taeraa mai o te Tukutuku Ao Whānui, arā te Internet, kei te whakawhānui i te reo SQL te hōkai o te whakamahi. Ka rite ki mohiotia te, kei te rongoa te whatunga he rota o ngā mōhiohio, engari ehara i te mea huru, ka whakairi ki runga ki te tukutuku me te tūmau rite ki etahi paearu.

No te rokiroki mōhiohio i runga i te Ipurangi, me te i roto i era atu wahi, he haepapa tika ki te pātengi raraunga, me te pae he pūnaha mana. Tikanga, E whakaritea ngā pae me te waehere i roto i ngā reo hōtaka rerekē, engari e hāngai ana te pātengi raraunga i runga i te momo o te SQL, a ko reira te reo o te hanganga pātengi raraunga, hāngai i te atanga tukutuku MySQL.

Ko te wetereo me te huinga taketake o whakahau e te reo tino tārua te SQL maheni katoa, ki etahi o ana tāpiritanga, e hoatu ki a ia i rite ki tSQL Tūmau Microsoft.

faaue tino rite SQL wetereo kore anake, engari ano hoki i te huinga paerewa o kupu mahi. Ko te rerekētanga anake i roto i te tono karanga, me te hanganga. Hei tauira, whakaaro he tono ki te waihanga i tētahi ripanga hou, ko ia te tuatahi te mea ratou whakaako tamariki i roto i ngā kura i runga i te rorohiko:

$ Hono = mysqli_connect ( 'localhost', "pakiaka", '', 'hei whakamatau');

ki te (! $ hono) mate ( "Hapa");

$ Uiui = 'waihanga kaiwhakamahi ripanga (

takiuru VARCHAR (20)

VARCHAR kupuhipa (20)

');

ki te (mysqli_query ($ hono, $ rapu)) paoro "hanga Ripanga.";

elseecho "kore i hanga te tepu:" .mysqli_error ();

mysqli_close ($ hono);

Ka rite ki te hua o te tono pera, ka taea e te tiki e koe i te tepu hou "kaiwhakamahi", i roto i nei i reira e kia rua nga mara: ingoa kaiwhakamahi me te kupuhipa.

ke wetereo raro i te Tukutuku, engari kua hāngai ki runga ki kapa MicrosoftSQLServer.

Building Uiui MicrosoftSQLServer

Ko te tauira o te huinga ngā o tepu raraunga ko tetahi o nga mahi matua o te SQL. Hoki taua ngā mahi, ngā te whakahau tīpako i SQL. Ko te pā ana ki kōrero i te reira i raro.

He tino māmā Ngā o hanga rōpū, ka hangaia rawa tīpako whakahau i roto i te SQL te e whai ake. Hei tauira, i reira he he tepu i roto i nei e wātea ana mō ngā kaimahi e, mo te tauira, te ingoa o te tangata i te raraunga. tū tatou i te raruraru e te tepu e hiahia ana koe ki te tīpako raraunga e pā ana ki ngā kaimahi, e te rā o te whanau - i roto i te wā i te tuatahi o Hānuere ki te tuatahi o Maehe o te tau o nāianei, urutomo. Hoki te tauira taua he e tika ana ki te mahia he whakahau SQL, i te mea he hoahoa paerewa e kore anake, engari ano hoki te huru kōwhiringa:

Tīpakohia * i Tangata

Hea P_BerthDay> = '01 / 01/2016 'a P_BerthDay <= '03 / 01/2016'

Tuhinga o tenei whakahau ka hoki mai raraunga katoa e pā ana ki ngā kaimahi, ko tona huritau kei roto i te wā, kua whakaturia nei e e koutou. kia ētahi wā kia whakawerohia koe ki te whakaatu i te ingoa whakamutunga anake, ingoa tuatahi me te patronymic o te kaimahi. Ki tenei mutunga, ki te tono hanga i te ara paku rerekē, hei tauira, e whai ake:

SelectP_Name - ingoa

P_SurName - ingoa

P_Patronimic - patronymic

i Tangata

Hea P_BerthDay> = '01 / 01/2016 'a P_BerthDay <= '03 / 01/2016'

Heoi, ko te kōwhiringa noa o tetahi mea tenei. Ko ia, i roto i te ngako, kore e pā tetahi, engari whakarato mōhiohio anake. Otiia ki te whakatau koe ki te tango i tino te reo SQL, ka whai koe ki te ako e founga ki te hanga huringa ki te pātengi raraunga, mai to ratou hanganga kahore reira e taea noa. Kia pehea te mahi te tenei ka kōrero reira i raro.

Basic SQL faaue ki te huri i ngā raraunga

hanga te wetereo te mo te e tohea ana e kore anake, engari ano mo te te whawhe raraunga. Fele, Kei te tuhi te kaihātepe mahi pātengi raraunga te hōtuhi mo nga tauira me ngā pūrongo, engari i ētahi wā e hiahia ana koe ki te hanga ngā huringa ki te tepu. SQL whakahau rārangi mō taua mahi he iti, me te ngā o ngā kapa matua e toru:

1. Kōkuhu (trans. Kōkuhu).

2. Whakahōu (trans. Whakahōu).

3. Mukua (trans. Tangohia).

Ko te whakaaro o enei kapa he ngāwari ki te whakatau, he mea nui ki te iriti i tika o ratou ingoa. He ngāwari ki te whakamahi, me te kore e whai i te hanga uaua o te kaupapa enei whakahau, engari ko reira utu te whakahua e etahi o ratou, ki te whakamahia hē, e taea meinga kino tūnguru ki te pātengi raraunga.

Ka rite ki te tikanga, i mua i te whakamahi i enei whakahau MSSQL hiahia ana koe ki te whakaaro me te tango ki te pūkete te mau hopearaa taea katoa o ratou whakatinanatanga.

I muri ako i enei whakahau, ka taea ki te tino tīmata mahi ki tepu pātengi raraunga, reira whakarerekē reira, ka hanga i etahi taurangi whakamarama hou i runga i ranei te tango tawhito koutou.

Kōkuhu kapa

Hei kōkuhu raraunga ki te ripanga whakamahi i te kapa haumaru - Kōkuhu. Ko taea tonu raraunga hē whakaurua ki te tango me te tāpiri ano ki te pātengi raraunga.

Kōkuhu whakahau ki kōkuhu raraunga hou ki te tepu, me te taea ki a koe te tāpiri i rite te huinga oti, a tīpakongia.

Hei tauira, whakaaro te kōkuhu he whakahau i roto i te Tangata tepu whakaahuatia i mua. I roto i te tikanga ki te hanga i te whakahau SQL me rere i roto i te tepu raraunga, e taea ai ki te kōkuhu i te raraunga katoa i roto i te tepu whakakiia reira tīpakongia ranei.

Kōkuhu ki te tangata

Tīpakohia 'Grigoriev' 'Vitali', 'Petrovich', '1/1/1988'

Kei te ki tonu aunoa MS SQL Tūmau whakahau te mahere penei ki nga pūtau katoa o te tepu ki te raraunga i whakaritea. He āhuatanga i reira te kaimahi e kore tuatahi waenganui, mo te tauira, ka haere ia ki te mahi i runga i te utu i Germany. I roto i tenei take, whakahaere i te raraunga whakahau fakahū e whai ake nei, e ka kawea ki te tepu anake te mea te mea e tika ana. Ko te wetereo o tenei whakahau ko e whai ake:

Insertintoperson (P_Name, P_SurName, P_BerthDay)

Uara ( 'Rawiri', 'Matau', '2/11/1986')

whakakiia ana tēnei kapa anake te pūtau tohua, me ētahi atu katoa ka whakakahore.

Whakahaua ki te huri i te raraunga

Hei huri i te raraunga rite te aho katoa, a ka whakamahi Whakahōu SQL whakahau etahi pūtau. Te mahi Me anake tenei whakahau ki etahi tikanga, ara, ïa tohu i ai rārangi o te tau e tika ana ki te hanga huringa.

Whakahōu SQL whakahau kua he wetereo ohie. Ki te whakarite te whakamahi tika, whakapūtā koutou e kia puta ke raraunga i roto i te tīwae me te record. Next, te hanga i tētahi hōtuhi ka mahia i te reira. Fakakaukau angé ki he tauira. E ti'a ia tatou ki te huri i te rā o te whanau o te Matau a Rawiri, ngā nei i roto i te tepu kaimahi i te tau 5.

Whakahōu Tangata

Whakatakotoria he P_BerthDay = '02 / 10/1986 'te wahi P_ID = 5

Here (i roto i te hōtuhi) e kore e huri i te rā o te whanau o ngā pūkete tepu katoa, ka whakahou i te tika anake.

Ko te whakamahi i tenei kapa o hötaka tino maha, no te mea taea reira ki a koe te huri i te raraunga i roto i te tepu ki waho mai meinga nui kino ki te mōhiohio katoa.

Faaue ki te whakamahi i ngā tukanga me ngā mahi hanga-i roto i

Ki te awhina o te SQL reo, e kore e taea e anake hanga e koe uiui, engari ki te waihanga hoki ngā tikanga hanga-i roto i hoki te mahi ki te raraunga. Ka rite ki te tikanga, i reira he wā ina e hiahia ana koe ki te whakamahi i roto i te tinana o te tauira rapu tuhituhia mua.

Whakawa arorau, ka hiahia koe ki te tārua me te whakapiri i te kuputuhi tauira i roto i te wahi e tika ana, engari ka taea e mahi koe i te otinga ohie. Fakakaukau angé ki he tauira i reira ki te atanga pātene mahi whakaaturia tā te pūrongo, hei tauira i roto i Excel. Ka kia whakamana tenei mahi rite hiahiatia. Hoki taua take, kua hanga-i roto i ngā tukanga rongoa. Tono pātai SQL, i roto i tenei take, kei roto i te tukanga, ā, e meinga e te rōpū SQLExec.

Kia tatou whakaaro e i hanga te tikanga mo ngā rā withdrawal o whanau o kaimahi ki te tepu Tangata whakaahuatia i mua. I roto i tenei take, kahore he hiahia ki te tuhituhi i te uiui katoa. Ki te whiwhi te mōhiohio e tika ana, ko te rawaka ki mahia i te Exec whakahau [ingoa tukanga] ka haere i te tawhā tika mō te tīpako. Ka rite ki te tauira e taea e tatou e whakaaro i te huarahi mo te hanganga o te āhua taua o te tikanga:

CREATEPROCEDUREPrintPerson

@DB smalldatetime

@DE smalldatetime

AS

Whakaritea NOCOUNT ON;

* Tīpakohia i Tangata

MEI HumanResources.vEmployeeDepartmentHistory

HEA P_BerthDay> = @DB me P_BerthDay <= @DE

ANDEndDateISNULL;

GO

Tenei tukanga hoki ngā mōhiohio katoa e pā ana ki ngā kaimahi nei huritau ka waiho i roto i te wā wa i homai.

Organization tapatahi raraunga. pāngia e

Ētahi MS SQL-whakahau, ara tetahi kia mea, e kore e taea e te hoahoa whakarite anake whawhe raraunga, engari ki te whakarite ano hoki ratou tapatahi. Hoki taua take i roto i te reo hangaia hoahoa pūnaha, e hanga he kaihātepe ia. He huaina pāngia e, e taea whakarato mana raraunga enei.

I roto i tenei take, mo te whakahaere o ngā āhuatanga whakamātautau mā te whakamahi i paerewa SQL-pātai whakahau. I roto i te pāngia e, ka taea e koe te hanga i te rota o ngā āhuatanga me ngā whāititanga mō raraunga e e whakahaere te uru kore anake ki ngā mōhiohio engari ano ki te ta'ofi te tango, whakarereke, ranei kōkuhu raraunga.

Ko nga momo o whakahau SQL e taea te whakamahi i roto i te keu, e kore e iti. A feruri i te hi'oraa e whai ake nei.

Ki te whakaahua tatou te huarahi mō te hanga i te keu, ka ko te taua rite ina hanga he tukanga nga momo o whakahau SQL. Ka hātepe ano e whakaahuatia i raro nei.

Ko te mahi tuatahi, ko te ki te whakaahua i te whakahau mahi ki te waihanga i pāngia e:

FA'U Keu Person_Insert

ira i muri ki te tepu:

ONPerson

Tohu hoki i ngā mahi raraunga (i roto i tenei take, nga raraunga huri mahi).

Ko te mahi i muri mai, ko te ki te tautuhi i te tepu, me taurangi:

whakaatu @ID int. @Date smalldatetime @nID int. @nDatesmalldatetime

whakaatu atu Pehu ki tīpako tepu raraunga tango me te whakaurunga o te raraunga:

Whakaatu pehu C1 mo tīpako P_ID, P_BerthDay i Whakaurutia

Whakaatu pehu C2 mo tīpako P_ID, P_BerthDay i mukua

Tautuhi i te kaupae kōwhiringa raraunga. Kia i roto i te tinana whakahau Pehu huru me te tauhohenga ki reira:

ki te @ID = @nID me @nDate = '01 / 01/2016 '

timata

sMasseges 'mahi Whakahaere taea. e kore te mea e tika ana Rā '

mutunga

Ko reira utu te whakahua e kore e taea e te keu waihanga anake, engari ano hoki tahuri atu hoki i te ia. Ka taea e taua whawhe mau anake te whakahau kaihātepe te raveraa i SQL Tūmau:

altertablePERSONdisabletriggerall - ki te mono pāngia e katoa i hanga mo te tepu, me, o fakatatau ki, altertablePERSONenabletriggerall - mo te whakauru.

whakahau enei SQL taketake whakamahia tino nuitia, engari ka taea e ratou huinga e tino kanorau. SQL - he reo hōtaka rawa ngāwari, me te homai i te kaiwhakawhanake i te mōrahi o taea.

mutunga

Mai i te runga ake e taea anake tatou e faaoti: pūkenga reo SQL he pau mo te hunga e hiahia ana ki te whai wāhi tino i roto i te hōtaka. takoto te reira i te ngakau o nga ngā mahi katoa i mahi i roto i te Internet me te i roto i te pātengi raraunga kāinga. Ko te aha me mohio te kaihātepe heke mai te maha o whakahau o te reo, no te mea anake e taea ratou e, pera ki te korero, ki te kōrero ki te rorohiko.

O te akoranga, i reira he ngaahi tōnounoú, rite i roto i te katoa i roto i tenei ao, engari ko ratou na iti e koma noa i mua i te mana. I roto i te reo katoa hōtaka SQL ko tata anake te tetahi o ona ahua, no te mea he reira te ao, me te matauranga i runga i te tuhituhi hōtuhi me waehere ko te pūtake o tata pae katoa.

Ka taea te whakaaro te painga matua o SQL bezogovorchno tona tapatahi, no te mea, i muri katoa, ko ia i te reira te tangata whakaurua ki te marautanga kura. Mai taea hapai i te reira ara he kaihātepe tauhou, e kore tino matau i roto i te reo.