Ligzdoti vaicājumi. Piemēri. Sarežģīti SQL vaicājumi. Ligzdoti vaicājumi, pievienošanās, skati
SQL ļauj ligzdot vaicājumus savā starpā. Parasti apakšvaicājums atgriež vienu vērtību, kas tiek pārbaudīta, lai noskaidrotu, vai predikāts ir patiess.
Meklēšanas vienumu veidi:
. Salīdzinājums ar apakšvaicājuma rezultātu (=, >=)
. Pārbauda piederību apakšvaicājuma (IN) rezultātiem
. Esamības pārbaude (EXISTS)
. Vairāki (kvantitatīvi) salīdzinājumi (JEBKURS, VISI)
Piezīmes par ligzdotiem vaicājumiem:
. Apakšvaicājumā ir jāatlasa tikai viena kolonna (izņemot apakšvaicājumu ar predikātu EXISTS), un tā rezultāta datu tipam ir jāatbilst predikātā norādītās vērtības datu tipam.
. Dažos gadījumos varat izmantot DISTINCT atslēgvārdu, lai nodrošinātu vienas vērtības atgriešanu.
. Apakšvaicājumā nevar iekļaut klauzulu ORDER BY vai UNION.
. Apakšvaicājums var atrasties vai nu pa kreisi vai pa labi no meklēšanas nosacījuma.
. Apakšvaicājumos var izmantot apkopošanas funkcijas bez GROUP BY klauzulas, kas automātiski atgriež īpašu vērtību jebkuram rindu skaitam, īpašu IN predikātu un kolonnu izteiksmēm.
. Kad vien iespējams, apakšvaicājumu vietā izmantojiet tabulas JOIN savienojumus.
Ligzdoto vaicājumu piemēri:
SELECT * FROM Pasūtījumi WHERE SNum=(SELECT SNum FROM SalesPeople WHERE SName=’Motika’)
SELECT * FROM Pasūtījumi WHERE SNum IN (SELECT SNum FROM SalesPeople WHERE City=’London’)
SELECT * FROM Pasūtījumi WHERE SNum=(SELECT DISTINCT SNum FROM Orders WHERE CNum=2001)
SELECT * FROM Pasūtījumi WHERE Amt> (SELECT AVG(Amt) FROM Orders WHERE Odate=10/04/1990)
SELECT * FROM Customer WHERE CNum=(SELECT SNum+1000 FROM SalesPeople WHERE SName=’Serres’)
2) Saistītie apakšvaicājumi
SQL varat izveidot apakšvaicājumus, kas atsaucas uz tabulu no ārējā vaicājuma. Šajā gadījumā apakšvaicājums tiek izpildīts vairākas reizes, vienu reizi katrai tabulas rindai no ārējā vaicājuma. Tāpēc ir svarīgi, lai apakšvaicājumā tiktu izmantots indekss. Apakšvaicājums var piekļūt tai pašai tabulai kā ārējam. Ja ārējais vaicājums atgriež salīdzinoši nelielu rindu skaitu, saistītais apakšvaicājums būs ātrāks nekā nesaistītais. Ja apakšvaicājums atgriež nelielu skaitu rindu, saistītais vaicājums būs lēnāks nekā nesaistītais vaicājums.
Saistīto apakšvaicājumu piemēri:
SELECT * FROM SalesPeople Galvenā WHERE 1(SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //atgriež visus pasūtījumus, kuru vērtība pārsniedz konkrētā klienta vidējo pasūtījuma vērtību
3) Predikāts PASTĀV
Sintaktiskā forma: PASTĀV ()
Predikāts izmanto apakšvaicājumu kā argumentu un novērtē kā patiesu, ja apakšvaicājumā ir izvade, un false citādi. Apakšvaicājums tiek izpildīts vienreiz un var saturēt vairākas kolonnas, jo to vērtības netiek pārbaudītas, bet rindu klātbūtnes rezultāts tiek vienkārši reģistrēts.
Piezīmes par predikātu EXISTS:
. EXISTS ir predikāts, kas atgriež TRUE vai FALSE, un to var izmantot atsevišķi vai kopā ar citām Būla izteiksmēm.
. EXISTS savā apakšvaicājumā nevar izmantot apkopošanas funkcijas.
. Korelētajos apakšvaicājumos predikāts EXISTS tiek izpildīts katrai ārējās tabulas rindai.
. Predikātu EXISTS var apvienot ar tabulu savienojumiem.
Predikāta EXISTS piemēri:
SELECT * FROM Customer WHERE EXISTS (SELECT * FROM Customer WHERE City=’Sanhosē’) — atgriež visus klientus, ja kāds no viņiem dzīvo Sanhosē.
SELECT DISTINCT SNum FROM Customer First WHERE NOT EXISTS (SELECT * FROM Customer Send WHERE Send.SNum=First.SNum AND Send.CNumFirst.CNum) — atgriež to pārdevēju skaitu, kuri apkalpojuši tikai vienu klientu.
SELECT DISTINCT F.SNum, SNum, F. City FROM SalesPeople F, Customer S WHERE EXISTS (SELECT * FROM Customer T WHERE S.SNum=T.SNum AND S.CNumT.CNum UN F.SNum=S.SNum) — atgriež visu pārdevēju, kuri apkalpoja vairākus klientus, numuri, vārdi un dzīvesvietas pilsētas.
SELECT * FROM SalesPeople Frst WHERE EXISTS (SELECT * FROM Klients Sūtīt WHERE Frst.SNum=Send.SNum UN 1
4) Kvantitatīvās salīdzināšanas predikāti
Sintaktiskā forma: (=|>|=|) JEBKĀRĀ|VISI ()
Šie predikāti kā argumentu izmanto apakšvaicājumu, tomēr, salīdzinot ar predikātu EXISTS, tie tiek lietoti kopā ar relāciju predikātiem (=,>=). Šajā ziņā tie ir līdzīgi IN predikātam, bet tiek izmantoti tikai ar apakšvaicājumiem. Standarts ļauj izmantot DAŽU atslēgvārdu, nevis JEBKURU, taču ne visas DBVS to atbalsta.
Piezīmes par salīdzināšanas predikātiem:
. Predikāts ALL tiek novērtēts kā TRUE, ja katra apakšvaicājuma izpildes laikā atlasītā vērtība atbilst nosacījumam, kas norādīts ārējā vaicājuma predikātā. To visbiežāk izmanto ar nevienlīdzību.
. Predikāts ANY tiek novērtēts kā TRUE, ja vismaz viena apakšvaicājuma izpildes laikā atlasītā vērtība atbilst nosacījumam, kas norādīts ārējā vaicājuma predikātā. To visbiežāk izmanto ar nevienlīdzību.
. Ja apakšvaicājums neatgriež nevienu rindu, tad VISI automātiski iegūst vērtību TRUE (tiek uzskatīts, ka salīdzināšanas nosacījums ir izpildīts), bet JEBKURAM tā iegūst vērtību FALSE.
. Ja salīdzinājums ir TRUE nevienai rindai un ir viena vai vairākas rindas ar NULL vērtību, tad ANY atgriež NEZINĀMS.
. Ja salīdzinājums ir FALSE nevienai rindai un ir viena vai vairākas rindas ar NULL vērtību, ALL atgriež NEZINĀMS.
Kvantitatīvā salīdzinājuma predikāta piemēri:
SELECT * FROM SalesPeople WHERE Pilsēta = ANY (IZVĒLIES Pilsētu NO klienta)
SELECT * FROM Pasūtījumi WHERE Amt ALL (SELECT Rating FROM Customer WHERE City='Rome')
5) Unikalitātes predikāts
UNIKĀLS|ATŠĶIRĪGS ()
Predikāts tiek izmantots, lai pārbaudītu apakšvaicājuma izejas datu unikalitāti (dublikātu neesamību). Turklāt UNIQUT predikātā virknes ar NULL vērtībām tiek uzskatītas par unikālām, un predikātā DISTINCT divas nenoteiktas vērtības tiek uzskatītas par vienādām viena ar otru.
6) Predikātu atbilstības
MATCH ()
Predikāts MATCH pārbauda, vai vaicājuma virknes vērtība atbilst jebkuras apakšvaicājuma virknes vērtībai. Šis apakšvaicājums atšķiras no IN AND ANY predikātiem ar to, ka tas ļauj apstrādāt “daļējas” (PARTIAL) atbilstības, kas var rasties rindās, kurām ir dažas NULL vērtības.
7) Vaicājumi sadaļā NO
Faktiski ir likumīgi izmantot apakšvaicājumu visur, kur ir atļauta tabulas atsauce.
SELECT Cname, Tot_Amt FROM Customer, (SELECT CNum, SUM(Amt) AS Tot_Amt FROM Orders GROUP BY CNum) WHERE City='London' AND Customer.CNum=Orders.CNum
//subquery atgriež katra klienta no Londonas veikto pasūtījumu kopējo summu.
8) Rekursīvie vaicājumi
AR REKURSĪVU
Q1 KĀ IZVĒLĒTIES … NO … KUR …
Q2 KĀ IZVĒLĒTIES … NO … KUR …
- Tulkošana
- Apmācība
Vai jums ir nepieciešams “SELECT * WHERE a=b FROM c” vai “SELECT WHERE a=b FROM c ON *”?
Ja esat tāds pats kā es, jūs piekrītat: SQL ir viena no tām lietām, kas sākotnēji šķiet vienkārša (lasāma kā angļu valoda!), bet kaut kā jums ir jāmeklē Google katrs vienkāršais vaicājums, lai atrastu pareizo sintaksi.
Un tad sākas pievienošanās, apkopošana, apakšvaicājumi, un izrādās, ka tā ir pilnīga muļķība. Kaut kas tamlīdzīgs:
ATLASĪT dalībniekus.vārds || " " || members.lastname AS "Pilns vārds" NO aizņēmumiem IEKŠĒJS JOIN dalībnieki ON members.memberid=borrowings.memberid IEKŠĒJS JOIN grāmatas ON books.bookid=borrowings.bookid WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock>(SELECT avg(stock) ) NO grāmatām)) GROUP BY dalībnieki.vārds, biedri.uzvārds;
Bue! Tas atbaidīs jebkuru iesācēju vai pat vidēja līmeņa izstrādātāju, ja viņš pirmo reizi redz SQL. Bet tas viss nav tik slikti.
Ir viegli atcerēties, kas ir intuitīvs, un ar šo ceļvedi es ceru samazināt barjeru, kas iesācējiem iekļūs SQL, un piedāvāt tiem, kas jau ir pieredzējuši, jaunu veidu, kā aplūkot SQL.
Lai gan SQL sintakse dažādās datu bāzēs ir gandrīz vienāda, šajā rakstā vaicājumiem tiek izmantots PostgreSQL. Daži piemēri darbosies MySQL un citās datu bāzēs.
1. Trīs burvju vārdi
SQL ir daudz atslēgvārdu, bet SELECT, FROM un WHERE ir gandrīz katrā vaicājumā. Nedaudz vēlāk jūs sapratīsit, ka šie trīs vārdi ir vissvarīgākie datu bāzes vaicājumu veidošanas aspekti, bet citi, sarežģītāki vaicājumi ir tikai papildinājumi tiem.
2. Mūsu bāze
Apskatīsim datubāzi, kuru izmantosim kā piemēru šajā rakstā:
Mums ir grāmatu bibliotēka un cilvēki. Ir arī speciāla tabula izdoto grāmatu ierakstīšanai.
- Tabulā "Grāmatas" tiek glabāta informācija par grāmatas nosaukumu, autoru, izdošanas datumu un pieejamību. Tas ir vienkārši.
- Tabulā “Dalībnieki” - visu bibliotēkā reģistrējušos cilvēku vārdi un uzvārdi.
- Tabulā “Aizņēmumi” tiek glabāta informācija par bibliotēkā paņemtajām grāmatām. Grāmatvedības sleja attiecas uz tabulā “grāmatas” uzņemtās grāmatas ID, bet sleja “Biedru identifikators” attiecas uz atbilstošo personu no tabulas “Dalībnieki”. Mums ir arī izdošanas datums un datums, kad grāmata ir jāatdod.
3. Vienkāršs pieprasījums
Sāksim ar vienkāršu pieprasījumu: mums vajag vārdus Un identifikatori(id) no visām grāmatām, kuras sarakstījis autors “Dan Brown”
Pieprasījums būs šāds:
SELECT bookid AS "id", nosaukums FROM books WHERE autors = Dens Brauns;
Un rezultāts ir šāds:
| id | virsraksts |
|---|---|
| 2 | Pazudušais simbols |
| 4 | Inferno |
Pavisam vienkārši. Apskatīsim pieprasījumu, lai saprastu, kas notiek.
3.1 FROM — no kurienes mēs iegūstam datus
Šobrīd tas var šķist pašsaprotami, taču FROM būs ļoti svarīgi vēlāk, kad nonāksim pie pievienošanās un apakšvaicājumiem.
FROM punkti uz tabulu, lai vaicātu. Tā var būt esoša tabula (kā iepriekš minētajā piemērā) vai tabula, kas izveidota lidojuma laikā, izmantojot savienojumus vai apakšvaicājumus.
3.2 KUR - kādi dati tiek parādīti
WHERE vienkārši uzvedas kā filtrs līnijas, kuru mēs vēlamies izvadīt. Mūsu gadījumā mēs vēlamies redzēt tikai rindas, kurās autora kolonnas vērtība ir “Dan Brown”.
3.3 SELECT - kā parādīt datus
Tagad, kad mums ir visas vajadzīgās tabulas kolonnas, mums ir jāizlemj, kā tieši parādīt šos datus. Mūsu gadījumā mums ir nepieciešami tikai grāmatu nosaukumi un ID, tāpēc mēs to darām. izvēlamies izmantojot SELECT. Tajā pašā laikā kolonnu var pārdēvēt, izmantojot AS.
Visu vaicājumu var vizualizēt, izmantojot vienkāršu diagrammu:
4. Savienojumi (savienojumi)
Tagad mēs vēlamies redzēt visu to Dena Brauna grāmatu nosaukumus (ne vienmēr unikālos), kuras ir izņemtas no bibliotēkas, un to, kad šīs grāmatas ir jāatdod:
SELECT books.title AS "Nosaukums", borrowings.returndate AS "Atgriešanas datums" NO aizņēmumiem PIEVIENOTIES grāmatām ON borrowings.bookid=books.bookid WHERE books.author="Dan Brown";
Rezultāts:
| Nosaukums | Atgriešanās datums |
|---|---|
| Pazudušais simbols | 2016-03-23 00:00:00 |
| Inferno | 2016-04-13 00:00:00 |
| Pazudušais simbols | 2016-04-19 00:00:00 |
Lielākoties pieprasījums ir līdzīgs iepriekšējam izņemot NO sadaļām. Tas nozīmē, ka mēs pieprasām datus no citas tabulas. Mēs nepiekļūstam ne “grāmatu” tabulai, ne “aizņēmumu” tabulai. Tā vietā mēs vēršamies pie jauns galds, kas tika izveidots, savienojot šīs divas tabulas.
aizņēmumi PIEVIENOJIETIES grāmatām ON aizņēmumi.bookid=books.bookid — uzskatiet šo par jaunu tabulu, kas tika izveidota, apvienojot visus ierakstus no tabulām "grāmatas" un "aizņēmumi", kurās grāmatas ID vērtības ir vienādas. Šādas apvienošanas rezultāts būs:
Un tad mēs vaicājam šo tabulu tādā pašā veidā kā iepriekš minētajā piemērā. Tas nozīmē, ka, savienojot galdus, jums tikai jāuztraucas par to, kā izveidot savienojumu. Un tad pieprasījums kļūst tikpat skaidrs kā “vienkāršā pieprasījuma” gadījumā no 3. punkta.
Izmēģināsim nedaudz sarežģītāku savienojumu ar divām tabulām.
Tagad mēs vēlamies uzzināt to cilvēku vārdus un uzvārdus, kuri no bibliotēkas paņēma autora “Dena Brauna” grāmatas.
Šoreiz ejam no apakšas uz augšu:
1. darbība- kur mēs iegūstam datus? Lai iegūtu vajadzīgo rezultātu, jāsavieno tabulas “biedrs” un “grāmatas” ar tabulu “aizņēmumi”. Sadaļa JOIN izskatīsies šādi:
aizņēmumi PIEVIENOTIES grāmatām ON borrowings.bookid=books.bookid PIEVIENOTIES biedriem ON members.memberid=borrowings.memberid
Savienojuma rezultātu var redzēt saitē.
2. darbība- kādus datus mēs rādām? Mūs interesē tikai dati, kur grāmatas autors ir “Dens Brauns”
KUR books.author="Den Brown"
3. darbība- kā mēs parādām datus? Tagad, kad dati ir saņemti, jums tikai jāparāda to personu vārdi, kuri paņēma grāmatas:
ATLASĪT dalībniekus.vārds AS "Vārds", dalībnieki.uzvārds AS "Uzvārds"
Super! Atliek tikai apvienot trīs komponentus un iesniegt mums nepieciešamo pieprasījumu:
ATLASĪT biedrus.vārds AS "Vārds", biedri.uzvārds AS "Uzvārds" NO aizņēmumiem PIEVIENOTIES grāmatām ON aizņēmumiem.bookid=books.bookid PIEVIENOTIES biedriem ON members.memberid=borrowings.memberid WHERE books.author="Dan Brown";
Ko mums dos:
| Vārds | Uzvārds |
|---|---|
| Maiks | Viliss |
| Elena | Hortons |
| Elena | Hortons |
Lieliski! Bet nosaukumi atkārtojas (tie nav unikāli). Mēs to drīz izlabosim.
5. Apkopošana
Rupji runajot, Apkopojumi ir nepieciešami, lai vairākas rindas pārvērstu vienā. Tajā pašā laikā apkopošanas laikā dažādām kolonnām tiek izmantota atšķirīga loģika.
Turpināsim ar mūsu piemēru, kur parādās dublēti vārdi. Ir skaidrs, ka Elena Hortone paņēma vairāk nekā vienu grāmatu, taču tas nav labākais veids, kā parādīt šo informāciju. Varat iesniegt citu pieprasījumu:
IZVĒLĒTIES biedri.vārds AS "Vārds", biedri.uzvārds AS "Uzvārds", skaits(*) AS "Aizņemto grāmatu skaits" NO aizņēmumiem PIEVIENOTIES grāmatām ON aizņēmumi.bookid=books.bookid PIEVIENOTIES biedriem ON dalībnieki.memberid=aizņēmumi .memberid WHERE grāmatas.author="Dan Brown" GROUP BY dalībnieki.vārds, dalībnieki.uzvārds;
Kas dos mums vēlamo rezultātu:
| Vārds | Uzvārds | Aizņemto grāmatu skaits |
|---|---|---|
| Maiks | Viliss | 1 |
| Elena | Hortons | 2 |
Gandrīz visos apkopojumos ir klauzula GROUP BY. Šī lieta pārvērš tabulu, ko var izgūt ar vaicājumu, tabulu grupās. Katra grupa atbilst unikālai kolonnas vērtībai (vai vērtību grupai), ko norādījām GROUP BY . Mūsu piemērā mēs pārvēršam iepriekšējā uzdevuma rezultātu rindu grupā. Mēs veicam arī apkopošanu ar count , kas vairākas rindas pārvērš veselā skaitļā (mūsu gadījumā rindu skaitā). Pēc tam šī nozīme tiek piešķirta katrai grupai.
Katra rezultāta rinda atspoguļo katras grupas apkopojuma rezultātu.
Var nonākt pie loģiska secinājuma, ka visi rezultāta lauki ir vai nu jānorāda GROUP BY , vai arī tiem jāveic agregācija. Tā kā visi pārējie lauki var atšķirties viens no otra dažādās rindās, un, ja tos atlasāt ar SELECT, nav skaidrs, kura no iespējamām vērtībām būtu jāņem.
Iepriekš minētajā piemērā skaitīšanas funkcija apstrādāja visas rindas (jo mēs skaitījām rindu skaitu). Citas funkcijas, piemēram, summa vai max, apstrādā tikai norādītās rindas. Piemēram, ja mēs vēlamies uzzināt katra autora sarakstīto grāmatu skaitu, mums ir nepieciešams šāds vaicājums:
SELECT autors, summa(krājums) FROM grāmatas GROUP BY autors;
Rezultāts:
| autors | summa |
|---|---|
| Robins Šarma | 4 |
| Dens Brauns | 6 |
| Džons Grīns | 3 |
| Amish Tripathi | 2 |
Šeit summas funkcija apstrādā tikai krājumu kolonnu un aprēķina visu vērtību summu katrā grupā.
6. Apakšvaicājumi
Apakšvaicājumi ir parasti SQL vaicājumi, kas iegulti lielākos vaicājumos. Tie ir sadalīti trīs veidos atkarībā no atgrieztā rezultāta veida.
6.1 Divdimensiju tabula
Ir vaicājumi, kas atgriež vairākas kolonnas. Labs piemērs ir vaicājums no iepriekšējā apkopošanas uzdevuma. Tā kā tas ir apakšvaicājums, tas vienkārši atgriezīs citu tabulu, kurā var veikt jaunus vaicājumus. Turpinot iepriekšējo uzdevumu, ja vēlamies noskaidrot autora “Robina Šarma” sarakstīto grāmatu skaitu, tad viens no iespējamajiem veidiem ir izmantot apakšvaicājumus:
SELECT * FROM (SELECT autors, summa( krājums) FROM grāmatas GROUP BY author) AS rezultāti WHERE author="Robin Sharma";
Rezultāts:
Var rakstīt kā: ["Robins Šarma", "Dens Brauns"]
2. Tagad mēs izmantojam šo rezultātu jaunā vaicājumā:
SELECT virsraksts, bookid FROM books WHERE autors IN (SELECT autors FROM (SELECT autors, summa( krājums) FROM books GROUP BY author) AS rezultāti WHERE summa > 3);
Rezultāts:
| virsraksts | bookid |
|---|---|
| Pazudušais simbols | 2 |
| Kurš raudās, kad tu mirsi? | 3 |
| Inferno | 4 |
Tas ir tāds pats kā:
IZVĒLĒTIES virsrakstu, grāmatiņu FROM books WHERE autors ("Robin Sharma", "Dan Brown");
6.3. Individuālās vērtības
Ir vaicājumi, kuru rezultāts ir tikai viena rinda un viena kolonna. Tās var uzskatīt par nemainīgām vērtībām, un tās var izmantot visur, kur tiek izmantotas vērtības, piemēram, salīdzināšanas operatoros. Tos var izmantot arī kā divdimensiju tabulas vai viena elementa masīvus.
Iegūsim, piemēram, informāciju par visām grāmatām, kuru skaits bibliotēkā pārsniedz pašreizējo vidējo.
Vidējo daudzumu var iegūt šādi:
atlasīt avg(stock) no grāmatām;
Ko mums dod:
7. Rakstīt operācijas
Lielākā daļa datu bāzes rakstīšanas operāciju ir diezgan vienkāršas, salīdzinot ar sarežģītākām lasīšanas darbībām.
7.1 Atjauninājums
UPDATE pieprasījuma sintakse semantiski ir tāda pati kā lasīšanas pieprasījumam. Vienīgā atšķirība ir tā, ka tā vietā, lai atlasītu kolonnas ar SELECT, mēs iestatām zināšanas ar SET.
Ja visas Dena Brauna grāmatas ir pazaudētas, jums ir jāatiestata daudzuma vērtība. Vaicājums par to būtu šāds:
UPDATE grāmatas SET stock=0 WHERE autors = Dens Brauns;
WHERE dara to pašu, ko iepriekš: atlasa rindas. SELECT vietā, ko izmantojām lasīšanas laikā, tagad mēs izmantojam SET. Taču tagad atlasītajās rindās jānorāda ne tikai kolonnas nosaukums, bet arī jauna šīs kolonnas vērtība.
7.2 Dzēst
DELETE vaicājums ir vienkārši vaicājums SELECT vai UPDATE bez kolonnu nosaukumiem. Nopietni. Tāpat kā ar SELECT un UPDATE , WHERE bloks paliek nemainīgs: tas atlasa dzēšamās rindas. Dzēšanas darbība iznīcina visu rindu, tāpēc nav jēgas norādīt atsevišķas kolonnas. Tātad, ja mēs nolemjam neatiestatīt Dena Brauna grāmatu skaitu, bet dzēst visus ierakstus pavisam, mēs varam iesniegt šādu pieprasījumu:
dzēst no grāmatām WHERE autors = Dens Brauns;
7.3. Ievietot
Varbūt vienīgais, kas atšķiras no cita veida vaicājumiem, ir INSERT. Formāts ir:
INSERT INTO x (a,b,c) VALUES (x, y, z);
Kur a , b , c ir kolonnu nosaukumi un x , y un z ir vērtības, kas jāievieto šajās kolonnās tādā pašā secībā. Tas būtībā arī viss.
Apskatīsim konkrētu piemēru. Šeit ir INSERT vaicājums, kas aizpilda visu "grāmatu" tabulu:
INSERT INTO grāmatas (grāmatas nosaukums, nosaukums, autors, publicēts, krājums) VĒRTĪBAS (1 "Scion of Ikshvaku", "Amish Tripathi","06-22-2015",2), (2 "The Lost Symbol"," Dens Brauns","07-22-2010",3), (3"Who Will Cry When You Die?","Robin Sharma","06-15-2006",4), (4"Inferno" "Dens Brauns","05-05-2014",3), (5 "The Fault in our Stars","Džons Grīns","01-03-2015",3);
8. Pārbaudiet
Esam nonākuši pie beigām, es ierosinu veikt nelielu pārbaudi. Apskatiet šo pieprasījumu pašā raksta sākumā. Vai varat to izdomāt? Mēģiniet to sadalīt sadaļās SELECT , FROM , WHERE , GROUP BY un apskatiet atsevišķus apakšvaicājumu komponentus.
Šeit tas ir lasāmākā formā:
ATLASĪT dalībniekus.vārds || " " || members.lastname AS "Pilns vārds" NO aizņēmumiem IEKŠĒJS JOIN dalībnieki ON members.memberid=borrowings.memberid IEKŠĒJS JOIN grāmatas ON books.bookid=borrowings.bookid WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock> (SELECT avg(stock) ) NO grāmatām)) GROUP BY dalībnieki.vārds, biedri.uzvārds;
Šis vaicājums atgriež sarakstu ar cilvēkiem, kuri ir izņēmuši grāmatu no bibliotēkas, kuru kopējais skaits pārsniedz vidējo.
Rezultāts:
| Pilnais vārds |
|---|
| Lida Tailere |
Es ceru, ka jums izdevās to izdomāt bez problēmām. Bet, ja nē, es priecāšos par jūsu komentāriem un atsauksmēm, lai es varētu uzlabot šo ziņu.
Birkas: pievienojiet atzīmes
Apakšvaicājums attiecas uz īpašiem objektiem programmā 1C. Tās funkcionalitāte tiek izmantota, ja programmā nepieciešams ģenerēt un izpildīt īpaša veida vaicājumus datu bāzes tabulām. Lai iegūtu pieprasījuma rezultātu, vispirms pareizi jāsastāda pieprasījuma teksts, kurā tiks uzkrāta aktuālā informācija par datu iegūšanas avotiem. Tās var būt tabulas, īpaši lauki, unikālas grupas.
Mūsdienīga vaicājumu valoda programmai 1C
Ligzdotie vaicājumi ir rakstītas, izmantojot valodu, kurai ir savas īpašības un kas tajā pašā laikā ir līdzīga citām īpašām SQL valodām, piemēram, pēc savas sintakses. Galvenās atšķirīgās iezīmes vaicājuma valoda 1C tie ir:
- Atsauces lauku atcelšana;
- Netipisku pieprasījumu iespēja;
- Sakārtošana pēc primitīvajiem veidiem;
- Izmantojot konstrukciju "View";
- Datu paraugu ņemšana pēc veidnes;
- Grupu un kopsummu koplietošana;
- Būvniecība ar nosacījumu “Kur”.
1C vaicājumu valodai ir arī citas atšķirīgas iezīmes, kas ļauj tai būt efektīvai.
Kas jums jāzina par ligzdotajiem vaicājumiem un vaicājumu valodu?
Profesionāla vaicājumu valodas dizaina izpēte ļauj ne tikai saprast, kam tā ir paredzēta vaicājuma valoda, bet arī iemācīties attēlot datu bāzes objektu kopu 2-dimensiju tabulu formātā, apstrādāt vaicājumu konsoli, veidot failus vaicājumu sarakstu glabāšanai un iepazīties ar specifiskajiem tabulu laukiem, kuriem ir atsauces tips. Un tas viss būs tikai pamatzināšanas par sistēmu ligzdotie vaicājumi 1C. Ir svarīgi apgūt grupas darbības, kas saistītas ar vaicājuma valodu, un iemācīties veikt pareizu atlasi no vairākiem datu avotiem.
Ligzdota vaicājuma piemērs
Apskatīsim vienu no iespējām ligzdotajam vaicājumam, kura pamatā ir vaicājumu apvienošana. Pieņemsim, ka ir dokumenti par ieņēmumiem un izdevumiem, un viena un tā pati juridiskā persona darbojas gan kā pārdevējs, gan kā pircējs. Mums ir jānoskaidro darījuma partnera kopējais parāds. Lai to izdarītu, mēs izmantojam efektīvo konstrukciju “UNITE ALLYTHY”.
Tabula "ARRIAGE"
Tabula "PATĒRIŅŠ"
Vispirms nosakām visus izdevumus, tad ienākumus pa juridiskajām personām. Otro pieprasījumu ievietojām ar “-” zīmi, tas ļaus pareizi sakļaut datus.
Darbības rezultāts:
Bet mums ir nepieciešams sabrukts rezultāts, ir nepieciešama grupēšana pēc juridiskām personām.
Tad ziņojuma rezultāts būs:
Runājot par izdevumu un ienākumu tabulām, piemēram, saskaņā ar Nomenklatūras direktoriju, ir jānovērš dublēšanās. Taču jūs nevarēsit apvienot katru pieprasījumu atsevišķi. Tāpēc jums ir jādara šādi:
Šajā gadījumā vaicājums tiek ievietots iekavās, to sauc par ligzdotu vaicājumu. Tas ļauj ne tikai grupēt mums būtiskos ierakstus, bet arī izslēgt līdzīgus elementus no diviem izmantotajiem apakšvaicājumiem.
Apvienojot divus vaicājumus, jāievēro noteiktas prasības. Pirmkārt, tas attiecas uz lauku skaitu. Ir nepieciešams, lai to skaits būtu identisks. Piemēram, ja datu tabulā “Izdevumi” bija norādīta Atlaide, bet Kvīts datos šāda elementa nebija, tad jāizmanto šāda konstrukcija:
Turpmāk būs jāpielāgo atlaides apmērs un jāveic nepieciešamā grupēšana.
Otrs obligātais nosacījums ir kārtība. Fakts ir tāds, ka lauku apvienošanas procedūra notiek stingri saskaņā ar to secību, tas ir, kā tie tiek apzīmēti katrā secīgajā sadaļā. Lai mainītu pasūtījumu, mēs izmantojam šādu konstrukciju:
Starp citu, izmantojot Savienības konstrukciju, tas ir jānošķir no vairāku vaicājumu apvienošanas jēdziena. Apvienošana ļauj vertikāli savienot parauga rezultātus pa vienam no pirmā un pēc tam pāriet uz otro. Tādā veidā dati tiek iegūti divu tabulu informācijas apvienošanas dizaina rezultātā.
Pēdējā nodarbībā saskārāmies ar vienu neērtību. Kad vēlējāmies uzzināt, kurš veidoja tēmu “velosipēdi”, mēs iesniedzām attiecīgu pieprasījumu:
Autora vārda vietā saņēmām viņa identifikatoru. Tas ir saprotams, jo mēs veicām vaicājumu vienā tabulā - Tēmas, un tēmu autoru vārdi tiek glabāti citā tabulā - Lietotāji. Tāpēc, uzzinot tēmas autora identifikatoru, mums ir jāveic vēl viens vaicājums - tabulā Lietotāji, lai uzzinātu viņa vārdu:
SQL nodrošina iespēju apvienot šādus vaicājumus vienā, pārvēršot vienu no tiem par apakšvaicājumu (ligzdoto vaicājumu). Tātad, lai uzzinātu, kurš ir izveidojis tēmu "velosipēdi", mēs veiksim šādu vaicājumu:
Tas ir, pēc atslēgvārda KUR, nosacījumā ierakstām citu pieprasījumu. MySQL vispirms apstrādā apakšvaicājumu, atgriež id_author=2, un šī vērtība tiek nodota klauzulai KURārējs pieprasījums.
Vienā vaicājumā var būt vairāki apakšvaicājumi, šāda vaicājuma sintakse ir šāda: Ņemiet vērā, ka apakšvaicājumi var atlasīt tikai vienu kolonnu, kuras vērtības tie atgriezīsies ārējā vaicājumā. Mēģinot atlasīt vairākas kolonnas, tiks parādīta kļūda.
Lai to nostiprinātu, iesniegsim vēl vienu pieprasījumu un noskaidrosim, kādus ziņojumus tēmas “velosipēdi” autors atstāja forumā:
Tagad sarežģīsim uzdevumu, noskaidrosim, kurās tēmās tēmas “velosipēdi” autors atstāja ziņojumus:
Izdomāsim, kā tas darbojas.
- MySQL vispirms izpildīs dziļāko vaicājumu:
- Rezultātā iegūtais rezultāts (id_author=2) tiks nodots ārējam pieprasījumam, kura forma būs šāda:
- Rezultātā iegūtais rezultāts (id_topic:4,1) tiks nosūtīts ārējam pieprasījumam, kura forma būs šāda:
- Un tas dos gala rezultātu (topic_name: par makšķerēšanu, par makšķerēšanu). Tie. tēmas "velosipēdi" autors atstāja ziņas Sergeja veidotajā tēmā "Par makšķerēšanu" (id=1) un Svetas veidotajā tēmā "Par makšķerēšanu" (id=4).
- Nav ieteicams izveidot vaicājumus, kuru ligzdošanas pakāpe ir lielāka par trim. Tas palielina izpildes laiku un apgrūtina koda izpratni.
- Dotā sintakse ligzdotajiem vaicājumiem, iespējams, ir visizplatītākā, bet ne vienīgā. Piemēram, tā vietā, lai jautātu
rakstīt
Tie. mēs varam izmantot jebkurus operatorus, kas tiek izmantoti ar WHERE atslēgvārdu (mēs tos pētījām pēdējā nodarbībā).
19. Ligzdotu sql vaicājumu izveide. Īstenošanas piemērs.
Izmantojot SQL, vaicājumus var ligzdot viens otrā. Parasti iekšējais vaicājums ģenerē vērtību, kas tiek pārbaudīta ārējā vaicājuma predikātā (klauzulā WHERE vai HAVING), lai noteiktu, vai tā ir patiesa vai nepatiesa. Saistībā ar apakšvaicājumu varat izmantot predikātu EXISTS, kas atgriež patieso vērtību, ja apakšvaicājuma izvade nav tukša.
Apvienojot to ar citām atlasītā operatora funkcijām, piemēram, grupēšanu, apakšvaicājums ir spēcīgs instruments vēlamā rezultāta sasniegšanai. Priekšraksta SELECT daļā FROM ir atļauts lietot sinonīmus tabulu nosaukumiem, ja, veidojot vaicājumu, mums ir nepieciešams vairāk nekā viens noteiktas attiecības gadījums. Sinonīmi tiek norādīti, izmantojot AS atslēgvārdu, kuru var pilnībā izlaist. Tātad daļa no FROM varētu izskatīties šādi:
NO Rl AS A, Rl AS B
NO Rl A. Rl B:
abas izteiksmes ir līdzvērtīgas un tiek uzskatītas par priekšraksta SELECT lietojumiem diviem tabulas R1 gadījumiem.
Piemēram, parādīsim, kā daži datubāzes “Session” vaicājumi izskatās SQL:
To personu saraksts, kuri nokārtoja visus nepieciešamos eksāmenus.
KUR Rādītājs > 2
IR SKAITS(*) = (ATLASĪT SKAITS(*)
WHERE R2.Group=R3.Group UN pilns nosaukumsa.pilns nosaukums)
Šeit iebūvētais vaicājums nosaka kopējo eksāmenu skaitu, kas jākārto katram skolēnam skolēna klasē, un salīdzina šo skaitu ar skolēna kārtoto eksāmenu skaitu.
Saraksts ar tiem, kuriem vajadzēja kārtot DB eksāmenu, bet vēl nav to nokārtojuši.
SELEST Pilns vārds
KUR R2.Fpynna=R3.Grupa UN Disciplīna = "DB" UN NAV
(ATLASĪT pilnu vārdu FROM Rl WHERE pilns vārds=a.pilns vārds UN disciplīna = "DB")
Predikāts EXISTS (apakšvaicājums) ir patiess, ja apakšvaicājuma apakšvaicājums nav tukšs, tas ir, tajā ir vismaz viens kortežs, pretējā gadījumā predikāts EXISTS ir nepatiess.
Predikāts NOT EXISTS ir patiess tikai tad, ja apakšvaicājums ir tukšs.
Ievērojiet, kā NOT EXISTS ar ligzdotu vaicājumu ļauj izvairīties no attiecību atšķirības darbības. Piemēram, vaicājuma formulēšana ar vārdu “viss” var tikt veikta it kā ar dubultu negatīvu. Aplūkosim piemēru datubāzei, kas modelē atsevišķu piegādātāju atsevišķu detaļu piegādi, to attēlo viena SP attiecība “Piegādātāji-detaļas” ar diagrammu
SP (Supplier_number. Part_number) P (Part_number. Name)
Šādi tiek formulēta atbilde uz pieprasījumu: "Atrodiet piegādātājus, kas piegādā visas detaļas."
IZVĒLĒTIES ATŠĶIRĪGU VENDOR_NUMBER NO SP SP1, KUR NEPASTĀV
(ATLASĪT daļas_numuru
NO P, KUR NAV
(ATLASES * NO SP SP2
WHERE SP2.supplier_number=SP1.supplier_number UN
sp2.daļas_numurs = P.daļas_numurs)):
Faktiski mēs pārformulējām šo pieprasījumu šādi: "Atrodiet tādus piegādātājus, lai nebūtu nevienas daļas, ko viņi nepiegādā." Jāņem vērā, ka šo vaicājumu var ieviest arī, izmantojot apkopošanas funkcijas ar apakšvaicājumu:
ATLASĪT ATŠĶIRĪGU Pārdevēja_numurs
GROUP BY Vendor_number
AR IZSKAITĪJUMU daļas_numurs) =
(ATLASĪT Skaits(daļas_numurs)
SQL92 standarts paplašina salīdzināšanas operatorus uz vairākiem salīdzinājumiem, izmantojot atslēgvārdus ANY un ALL. Šis paplašinājums tiek izmantots, salīdzinot noteiktas kolonnas vērtību ar apakšvaicājuma atgriezto datu kolonnu.
Jebkurš atslēgvārds, kas ievietots jebkurā salīdzināšanas predikātā, nozīmē, ka predikāts būs patiess, ja vismaz vienai apakšvaicājuma vērtībai salīdzināšanas predikāts ir patiess. Atslēgvārdam ALL ir nepieciešams, lai salīdzināšanas predikāts būtu patiess, salīdzinot ar visām apakšvaicājuma rindām.
Piemēram, atradīsim skolēnus, kuri nokārtoja visus eksāmenus ar atzīmi, kas nav zemāka par “labi”. Strādājam ar to pašu “Session” datu bāzi, bet pievienojam tai vēl vienu relāciju R4, kas raksturo laboratorijas darbu izpildi semestra laikā:
R 1 = (vārds, disciplīna, pakāpe);
R 2 = (pilns vārds, grupa);
R 3 = (grupas, disciplīna)
R 4 = (vārds, disciplīna, laboratorijas_darba numurs, pakāpe);
Atlasiet R1.Pilns vārds no R1 Kur 4 > = Visi (atlasiet Rl.Rating
Kur R1.Pilns vārds = R11.Pilns vārds)
Apskatīsim citu piemēru:
Atlasiet skolēnus, kuru eksāmena atzīme šajā disciplīnā nokārtotajā laboratorijas darbā ir ne mazāka par vienu atzīmi:
Izvēlieties R1.Name
No R1 Kur R1.Vērtējums >= JEBKURS (atlasiet R4.Rating
Kur Rl.Disciplīna = R4. Disciplīna UN R1. Pilns vārds = R4. Pilns vārds)
