Access DBVS naudoja dviejų tipų užklausas: QBE – užklausa pagal pavyzdį ir SQL(Structured Query Language) – struktūrinės užklausos kalba. Užklausos pavyzdys sugeneruojamas užpildžius specialią užklausos formą lange „Užklausų kūrimo priemonė“. SQL – užklausas kuria programuotojai iš SQL sekos – nurodymus. SQL paprastai generuoja programuotojai naudodami užklausos formą, kurią atidaro komanda „Query Builder“ skirtuke „Kūrimas“, o meniu Rodinys pasirenkamas „SQL režimas“. SQL kalba skirta dirbti su duomenimis, t.y. Duomenims reliacinėse duomenų bazėse kurti, keisti ir valdyti.
Reikėtų pažymėti, kad yra keletas SQL užklausų režimų (ANSI-89 SQL ir ANSI-92 SQL režimo užklausos), kurie atitinka ANSI-89 SQL ir ANSI-92 SQL standartus.
Instrukcijose yra duomenų rinkinio SQL aprašymas. SQL teiginiai susideda iš sakinių (SELECT, FROM, WHERE ir kt.). Pasiūlymai SQL kalba susideda iš terminų (operatorių arba komandų, identifikatorių, konstantų ir kt.). Teiginys prasideda sakiniu (viena iš komandų SELECT, CREATE, INSERT, UPDATE, DELETE ir kt.) ir baigiasi kabliataškiu. Pagrindiniai SQL sakiniai: SELECT, FROM ir WHERE.
Pavyzdžiui, SQL sakinys:
SELECT Students.StudentCode
IŠ Studentų;
susideda iš sakinio „SELECT Students.StudentCode“ ir „FROM Students“.
SELECT sąlygoje yra operatorius PASIRINKTI ir identifikatorius„Studentai.Studento kodas“. Čia prieš pilną lauko „Studento kodas“ pavadinimą rašomas duomenų bazės lentelės „Studentai“ pavadinimas. PASIRINKTI – nurodo lauką, kuriame yra reikalingi duomenys. FROM sąlygą sudaro FROM sąlyga ir identifikatorius „Students“. FROM – apibrėžia lentelę, kurioje yra SELECT sąlygoje nurodyti laukai.
Reikėtų pažymėti, kad kurdami užklausą SQL, turite atsižvelgti į jos sintaksę. Nepaisant to, kad SQL kalbos sintaksė yra pagrįsta anglų kalbos sintaksė, SQL kalbos versijų sintaksė skirtingose DBVS gali skirtis.
Yra keletas užklausų tipų: įrašų pasirinkimas, atnaujinimas, pridėjimas ir trynimas, kryžminės užklausos, lentelių kūrimas ir trynimas, lentelių sujungimas ir kt. Dažniausiai yra prašymo pavyzdys. Pasirinkimo užklausos naudojamos norint pasirinkti vartotojui reikalingą informaciją, esančią lentelėse. Jie sukurti tik susijusioms lentelėms.
Norėdami peržiūrėti SQL užklausų užklausas Access 2003 arba 2007 DBVS, turite vykdyti komandą SQL View/Mode aktyviame lange pavyzdinei užklausai kurti (1 pav.).
Ryžiai. 1.
Gaukime SQL sakinį (SELECT), kad paimtų duomenis iš Access 2003 duomenų bazės pagal mokinio veiklos kriterijų „Grade=5“ (2 pav.).
Ryžiai. 2.
Kaip matyti iš SELECT sakinio (1 pav.), jis aprašo duomenų rinkinį SQL kalba: SELECT – apibrėžia laukų pavadinimus, prieš kuriuos pateikiami lentelių, kuriose yra duomenys, pavadinimai; FROM - apibrėžia lenteles ir jų ryšius per lentelių raktinius laukus (tam naudojama konstrukcija INNER JOIN ... ON), kurių pagrindu parenkami duomenys; WHREME – apibrėžia laukų pasirinkimo sąlygas; ORDER BY – nustato, kaip rūšiuoti didėjančia tvarka (pagal numatytuosius nustatymus vykdoma didėjimo tvarka) lentelės „Studentai“ lauko „Pavardė“ reikšmes.
Kaip matyti iš duomenų gavimo iš duomenų bazės instrukcijų, SQL kalba aprašo, ką reikia gauti iš duomenų bazės, o vykdymas patikėtas DBVS, nes SQL kalba neturi savo priemonių programos vykdymui valdyti.
Ankstesniuose straipsniuose buvo aptartos problemos. Nagrinėjama duomenų bazių lentelių „sql_training_st.mdb“ struktūros kūrimo technologija pagal SQL užklausas. Be to, naudojant SQL užklausas, buvo užpildytos ACCESS DBMS lentelės „sql_training_st.mdb“.
Yra žinoma, kad reliacinėse duomenų bazėse SQL kalba skirta manipuliuoti duomenimis, apibrėžti duomenų bazės ir jos komponentų struktūrą, kontroliuoti vartotojų prieigą prie duomenų bazės ir valdyti sandorius arba valdyti duomenų bazės pakeitimus.
SQL kalba susideda iš keturių grupių:
- duomenų apdorojimo kalba DML;
- DDL duomenų apibrėžimo kalba;
- duomenų valdymo kalba DCL;
- TCL operacijų valdymo kalba.
DML grupę sudaro keturi pagrindiniai SQL užklausų tipai:
- INSERT – skirtas pridėti vieną ar daugiau įrašų į lentelės pabaigą;
- ATNAUJINIMAS – skirtas keisti esamus įrašus lentelės stulpeliuose arba modifikuoti duomenis lentelėje;
- DELETE – skirta ištrinti įrašus iš lentelės;
- SELECT – skirtas duomenims iš lentelių parinkti.
Puslapyje buvo aptarti pirmieji trys SQL užklausų tipai (INSERT, UPDATE, DELETE), kurios yra susijusios su korekcinėmis duomenų bazės užklausomis.
Šiame straipsnyje apžvelgsime duomenų gavimo iš „Access“ duomenų bazės lentelių užklausas.
Norėdami gauti informaciją, saugomą Access 2003 arba 2007 duomenų bazėje, galite naudoti SELECT užklausą, kad pasirinktumėte duomenis iš lentelių.
Norėdami tai padaryti, sukurkime šią SQL užklausą (SQL sakinį), vykdydami komandą View/SQL Mode, pasirinkite SQL režimą. Klaviatūra įveskite šį SQL sakinį:
PASIRINKTI*
IŠ Studentų;
Šį teiginį sudaro du punktai „SELECT *“ ir „FROM Students“. Pirmajame sakinyje yra SELECT sakinys ir identifikatorius * ("identifikatorius *" reiškia visų lentelės stulpelių sąrašą). Antrame sakinyje yra FROM sakinys ir identifikatorius „Students“.
FROM – apibrėžia mokinių lentelę, kurioje yra laukeliai, nurodyti SELECT sąlygoje. Reikėtų pažymėti, kad pasirinkimo užklausoje visada yra du teiginiai: SELECT ir FROM. Atsižvelgiant į atrankos sąlygas, atrankos užklausoje gali būti kitų operatorių. 1 paveiksle parodyta užklausos gauti duomenis ekrano kopija.
Ryžiai. 1. SQL SELECT užklausa duomenims pasirinkti
Šiame pavyzdyje duomenų pavyzdys generuojamas iš visų lentelės Studentai stulpelių.
Išsaugokite užklausą pavadinimu „Students-query1“. Įvykdžius komandą „Išsaugoti“, „Naršymo srityje“ atsiras objektas - „Užklausos: Studentai-užklausa1“.
Išsaugoję pasirinkimo užklausą, turite ją įvykdyti spustelėdami piktogramą „Vykdyti“. Komandos „Vykdyti“ rezultatai parodyti pav. 2.
Ryžiai. 2. Duomenų parinkimas iš visų Mokinių lentelės stulpelių
Laboratorinis darbas Nr.1
SQL: DATA EXTRACT – komandaPASIRINKTI
Darbo tikslas:
- susipažinti su SQL sakiniais;
- išmokti kurti paprastas SQL užklausas programoje Access naudojant komandą SELECT;
· operatorių IN, BETWEEN, LIKE naudojimas IS NULL.
Pratimas№1. Sukurkite užklausą, kad SQL režimu pasirinktumėte visas lentelės STUDENTAI laukų VARDAS ir PAVARDĖ reikšmes.
PASIRINKITE VARDĄ, PAVARDĘ
IŠ STUDENTŲ;
Pratimas№2 . Sukurkite užklausą, kad SQL režimu pasirinktumėte visus lentelės STUDENTAI stulpelius.
PASIRINKTI*
IŠ STUDENTŲ;
Užduotis Nr.3. Sukurkite užklausą, kad SQL režimu pasirinktumėte miestų, kuriuose gyvena studentai, pavadinimus, apie kuriuos informacija yra lentelėje ASMENINIAI DUOMENYS.
PASIRINKITE ATSKIRĮ MIESTĄ
IŠ [ASMENS DUOMENŲ];
4 užduotis. Sukurkite pasirinkimo užklausą SQL režimu, kuri nuskaito visų mokinių vardus pavarde Ivanov, informacija apie tai yra lentelėje STUDENTAI.
PASIRINKITE PAVARDĘ, VARDĄ
IŠ STUDENTŲ
WHERE LAST NAME="Ivanovas";
Užduotis Nr.5. Sukurkite pasirinkimo užklausą SQL režimu, kad gautumėte studentų, kurie mokosi UIT-22 grupėje pagal biudžetinę formą, vardus ir pavardes.
PASIRINKITE PAVARDĘ, VARDĄ
IŠ STUDENTŲ
WHERE GROUP="UIT-22" AND BIUDŽETAS=true;
6 užduotis. Sukurkite užklausą SQL režimu. pavyzdžiu iš lentelės EGZAMINAS – informacija apie mokinius, kurie turi tik 4 ir 5 pažymius.
PASIRINKTI*
NUO [PAKEISTIEGZAMINAI]
KURGRADEIN(4,5);
Užduotis Nr.7. Sukurkite zanpoc ir SQL režimą, kad pasirinktumėte informaciją apie studentus, kurių IOSU dalyko egzamino įvertinimas yra 3.
PASIRINKTI*
NUO [PAKEISTIEGZAMINAI]
KURITEM=" IOSU"IrGRADENe (4,5);
8 užduotis. Sukurkite užklausą SQL režimu, kad pasirinktumėte įrašus elementams, kurių valandos yra nuo 100 iki 130.
PASIRINKTI*
NUOPREKĖS
KURŽIŪRĖTINUO 100 IKI 130;
Užduotis Nr.9. Sukurkite užklausą SQL režimu ir iš lentelės STUDENTAI pasirinkite informaciją apie mokinius, kurių pavardės prasideda, pavyzdžiui, raide „C“.
PASIRINKTI*
NUOSTUDENTAI
KURPAVARDĖKAIP"SU*";
Išvada: Laboratorinių darbų metu susipažinome su SQL instrukcijomis, išmokome kurti paprastas SQL užklausas programoje Access naudojant komandą SELECT naudojant IN, BETWEEN, LIKE operatorius.
Edukacinio projekto „Parduotuvė“ aprašymasLentelės nuorodų diagrama
Lentelių aprašymas
m_category – prekių kategorijos
m_pajamos – prekių gavimas
m_outcome – prekių suvartojimas
m_product – katalogas, prekių aprašymai
m_tiekėjas – katalogas; tiekėjo informacija
m_unit - katalogas; vienetų
Norėdami praktiškai išbandyti šioje mokymo medžiagoje pateiktus pavyzdžius, turite turėti šią programinę įrangą:
Microsoft Access 2003 arba naujesnė versija.
SQL užklausa MS Access. Pradėti
Norėdami pamatyti lentelės turinį, dukart spustelėkite lentelės pavadinimą kairiajame skydelyje:
Norėdami perjungti į lentelės lauko redagavimo režimą, viršutiniame skydelyje pasirinkite Dizaino režimas:
Norėdami pamatyti SQL užklausos rezultatą, dukart spustelėkite užklausos pavadinimą kairiojoje srityje:
Norėdami perjungti į SQL užklausos redagavimo režimą, viršutiniame skydelyje pasirinkite SQL režimą:
SQL užklausa. MS Access pavyzdžiai. PASIRINKTI: 1-10
SQL užklausoje SELECT sakinys naudojamas pasirinkti iš duomenų bazės lentelių.SQL užklausa Q001. SQL užklausos pavyzdys, norint gauti tik reikiamus laukus norima seka:
PASIRINKITE dt, produkto_id, sumą
IŠ m_pajamų;
SQL užklausa Q002.Šiame SQL užklausos pavyzdyje žvaigždutė (*) naudojama visiems m_product lentelės stulpeliams išvardyti, kitaip tariant, norint gauti visus m_product ryšio laukus:
PASIRINKTI*
IŠ m_produktas;
PrašymasSQL Q003. DISTINCT teiginys naudojamas norint pašalinti pasikartojančius įrašus ir gauti kelis unikalius įrašus:
PASIRINKTI ATSKIRTI produkto_id
IŠ m_pajamų;
SQL užklausa Q004. Teiginys ORDER BY naudojamas įrašams rūšiuoti (sutvarkyti) pagal konkretaus lauko reikšmes. Lauko pavadinimas nurodomas po ORDER BY sakinio:
PASIRINKTI*
NUO m_pajamų
UŽSAKYTI PAGAL kainą;
SQL užklausa Q005. ASC sakinys naudojamas kaip ORDER BY sakinio papildymas ir skirtas rūšiavimui didėjimo tvarka nurodyti. Teiginys DESC naudojamas kartu su sakiniu ORDER BY ir naudojamas rūšiavimui mažėjančia tvarka nurodyti. Tuo atveju, kai nenurodytas nei ASC, nei DESC, daroma prielaida, kad yra ASC (numatytasis):
PASIRINKTI*
NUO m_pajamų
UŽSAKYTI PAGAL dt DESC , kaina;
SQL užklausa Q006. Norint pasirinkti reikiamus įrašus iš lentelės, naudojamos įvairios loginės išraiškos, išreiškiančios pasirinkimo sąlygą. Būlio išraiška atsiranda po WHERE teiginio. Pavyzdys, kaip gauti visus įrašus iš lentelės m_income, kurių sumos reikšmė didesnė nei 200:
PASIRINKTI*
NUO m_pajamų
KUR suma>200;
SQL užklausa Q007. Norint išreikšti sudėtingas sąlygas, naudojami loginiai operatoriai AND (junginys), OR (disjunkcija) ir NOT (loginis neigimas). Pavyzdys, kaip gauti iš lentelės m_outcome visus įrašus, kurių sumos reikšmė yra 20, o kainos vertė yra didesnė arba lygi 10:
Kaina
IŠ m_outcome
KUR suma=20 IR kaina>=10;
SQL užklausa Q008. Norėdami sujungti duomenis iš dviejų ar daugiau lentelių, naudokite instrukcijas INNER JOIN, LEFT JOIN, RIGHT JOIN. Toliau pateiktame pavyzdyje pateikiami dt, produkto_id, suma, kainos laukai iš lentelės m_pajamos ir pavadinimo laukai iš lentelės m_produktas. Lentelės m_pajamos įrašas sujungiamas su m_produkto lentelės įrašu, kai m_pajamos.produkto_id reikšmė yra lygi m_produkto.id reikšmei:
ĮJUNGTA m_pajamos.produkto_id=m_produkto.id;
SQL užklausa Q009.Šioje SQL užklausoje reikia atkreipti dėmesį į du dalykus: 1) paieškos tekstas yra kabutėse ("); 2) data yra formatu #Month/Day/Year#, kuris tinka MS Access. sistemose, datos rašymo formatas gali skirtis Informacijos apie pieno gavimą 2011 m. birželio 12 d. rodymo pavyzdys. Atkreipkite dėmesį į datos formatą #6/12/2011#:
PASIRINKITE dt, produkto_id, pavadinimą, sumą, kainą
IŠ „m_income“ VIDINIO PRISIJUNGTI „m_product“.
WHERE title="Pienas" And dt=#6/12/2011#; !}
SQL užklausa Q010. Instrukcija BETWEEN naudojama norint patikrinti, ar reikšmė priklauso tam tikram diapazonui. SQL užklausos, kurioje pateikiama informacija apie produktus, gautus nuo 2011 m. birželio 1 d. iki birželio 30 d., pavyzdys:
PASIRINKTI*
IŠ „m_income“ VIDINIO PRISIJUNGTI „m_product“.
ON m_income.product_id=m_product.id
KUR dt TARP 2011-06-01 # IR 2011-06-30 #;
SQL užklausa. MS Access pavyzdžiai. PASIRINKTI: 11-20
Viena SQL užklausa gali būti įdėta į kitą. Antrinė užklausa yra ne kas kita, kaip užklausa užklausoje. Paprastai WHERE sąlygoje naudojama antrinė užklausa. Tačiau yra ir kitų būdų, kaip naudoti antrines užklausas.Klausimas Q011. Rodoma informacija apie produktus iš lentelės m_product, kurių kodai taip pat yra lentelėje m_pajamos:
PASIRINKTI*
IŠ m_produkto
WHERE id IN (SELECT product_id FROM m_income);
Prašymas Q012. Rodomas produktų sąrašas iš lentelės m_product, kurių kodų nėra lentelėje m_outcome:
PASIRINKTI*
IŠ m_produkto
WHERE id NOT IN (SELECT product_id FROM m_outcome);
Prašymas Q013.Ši SQL užklausa rodo unikalų produktų kodų ir pavadinimų sąrašą, kurie yra lentelėje m_income, bet nėra m_outcome lentelėje:
PASIRINKITE DISTINCT produkto_id, pavadinimą
IŠ „m_income“ VIDINIO PRISIJUNGTI „m_product“.
ON m_income.product_id=m_product.id
WHERE product_id NOT IN (SELECT product_id FROM m_outcome);
Klausimas Q014. Unikalus kategorijų, kurių pavadinimai prasideda raide M, sąrašas rodomas lentelėje m_category:
PASIRINKTI ATSKIRIMĄ pavadinimą
IŠ m_produkto
KUR pavadinimas LIKE "M*";
Klausimas Q015. Aritmetinių operacijų atlikimo su laukais užklausoje ir laukų pervadinimo užklausoje (alias) pavyzdys. Šiame pavyzdyje apskaičiuojamos išlaidos = kiekis*kaina ir pelnas kiekvienam prekės išlaidų įrašui, darant prielaidą, kad pelnas yra 7 procentai pardavimo:
suma*kaina/100*7 AS pelnas
FROM m_outcome;
Klausimas Q016. Analizuodami ir supaprastindami aritmetines operacijas, galite padidinti užklausos vykdymo greitį:
SELECT dt, produkto_id, suma, kaina, suma*kaina AS rezultato_suma,
rezultatas_suma*0,07 AS pelnas
FROM m_outcome;
Klausimas Q017. Galite naudoti teiginį INNER JOIN norėdami sujungti duomenis iš kelių lentelių. Toliau pateiktame pavyzdyje, atsižvelgiant į ctgry_id reikšmę, kiekvienas įrašas lentelėje m_pajamos suderinamas su kategorijos pavadinimu iš lentelės m_category, kuriai priklauso produktas:
PASIRINKITE c.title, b.title, dt, suma, kaina, suma*kaina AS pajamų_suma
FROM (m_pajamos KAIP VIDINIS JOIN m_product AS b ON a.product_id=b.id)
VIDINIS JOIN m_category AS c ON b.ctgry_id=c.id
UŽSAKYTI PAGAL c.title, b.title;
Prašymas Q018. Tokios funkcijos kaip SUM – suma, COUNT – kiekis, AVG – aritmetinis vidurkis, MAX – didžiausia reikšmė, MIN – mažiausia reikšmė vadinamos agregacinėmis funkcijomis. Jie priima daugybę verčių ir jas apdoroję grąžina vieną reikšmę. Sumos ir kainos laukų sandaugos sumos apskaičiavimo naudojant SUM agregate funkciją pavyzdys:
SELECT SUM(suma*kaina) AS Total_Sum
IŠ m_pajamų;
Klausimas Q019. Kelių suvestinių funkcijų naudojimo pavyzdys:
SELECT Suma(suma) AS Suma_Suma, AVG(suma) AS Suma_AVG,
MAX(suma) AS Suma_Max, Min(summa) AS Suma_Min,
Skaičius (*) AS Total_Skaičius
IŠ m_pajamų;
Užklausa Q020.Šiame pavyzdyje apskaičiuojama visų prekių, kurių kodas 1, 2011 m. birželio mėn. didžiosiomis raidėmis, kiekis:
SELECT Suma(suma*kaina) AS pajamų_suma
NUO m_pajamų
KUR produkto_id=1 IR dt TARP #6/1/2011# IR #6/30/2011#;.
Klausimas Q021.Ši SQL užklausa apskaičiuoja prekių, kurių kodas 4 arba 6, pardavimo sumą:
SELECT Sum(summa*kaina) kaip rezultato_suma
IŠ m_outcome
WHERE produkto_id=4 OR produkto_id=6;
Klausimas Q022. Apskaičiuota, kiek 2011-06-12 parduota prekių, kurių kodas 4 arba 6:
SELECT Suma(suma*kaina) AS rezultato_suma
IŠ m_outcome
KUR (produkto_id=4 OR produkto_id=6) IR dt=#6/12/2011#;
Klausimas Q023. Užduotis tokia. Apskaičiuokite bendrą prekių kiekį kategorijoje „Kepos gaminiai“, kurios buvo parašytos didžiąja raide.
Norėdami išspręsti šią problemą, turite dirbti su trimis lentelėmis: m_pajamos, m_produktas ir m_kategorija, nes:
- lentelėje m_pajamos saugomi kapitalizuotų prekių kiekis ir kaina;
- kiekvienos prekės kategorijos kodas saugomas m_produkto lentelėje;
- antraštės kategorijos pavadinimas saugomas lentelėje m_kategorija.
Norėdami išspręsti šią problemą, naudosime šį algoritmą:
- kategorijos kodo „Kepos gaminiai“ nustatymas iš lentelės m_kategorija naudojant antrinę užklausą;
- lentelių m_pajamos ir m_produktas sujungimas, siekiant nustatyti kiekvienos perkamos prekės kategoriją;
- gavimo sumos (= kiekis*kaina) apskaičiavimas prekėms, kurių kategorijos kodas yra lygus pirmiau pateiktoje papildomoje užklausoje apibrėžtam kodui.
PASIRINKTI
IŠ „m_product“ KAIP VIDINIO PRISIJUNGIMO m_pajamos AS „b“ ON a.id=b.product_id
WHERE ctgry_id = (SELECT id FROM m_category WHERE title="Kepos gaminiai"); !}
Klausimas Q024. Bendro kapitalizuotų prekių kiekio apskaičiavimo kategorijoje „Kepos gaminiai“ išspręsime naudodami šį algoritmą:
- kiekvienam įrašui lentelėje m_pajamos, priklausomai nuo jo produkto_id reikšmės, iš lentelės m_kategorija, suderinkite kategorijos pavadinimą;
- pasirinkti įrašus, kurių kategorija yra „Kepos gaminiai“;
- apskaičiuokite kvito sumą = kiekis*kaina.
FROM (m_produktas KAIP VIDINIS JOIN m_pajamos AS b ON a.id=b.product_id)
WHERE c.title="Kepos gaminiai";
!}
Klausimas Q025.Šiame pavyzdyje apskaičiuojama, kiek prekių buvo sunaudota:
PASIRINKITE COUNT(produkto_id) AS produkto_cnt
FROM (SELECT DISTINCT product_id FROM m_outcome) AS t;
Klausimas Q026. Teiginys GROUP BY naudojamas įrašams grupuoti. Paprastai įrašai grupuojami pagal vieno ar kelių laukų reikšmę ir kiekvienai grupei taikoma tam tikra agregavimo operacija. Pavyzdžiui, ši užklausa generuoja prekių pardavimo ataskaitą. Tai yra, sugeneruojama lentelė su prekių pavadinimais ir suma, už kurią jos buvo parduotos:
PASIRINKITE pavadinimą, SUM(suma*kaina) AS rezultato_suma
IŠ „m_product“ KAIP VIDINIO PRISIJUNGIMO „m_outcome AS“ b
ON a.id=b.product_id
GROUP BY pavadinimas;
Prašymas Q027. Pardavimo ataskaita pagal kategorijas. Tai yra, sugeneruojama lentelė, kurioje pateikiami prekių kategorijų pavadinimai, bendra suma, už kurią parduota šių kategorijų produktų, ir vidutinė pardavimo suma. Funkcija ROUND naudojama vidutinei vertei suapvalinti iki artimiausios šimtosios dalies (antrasis skaitmuo po dešimtainio skyriklio):
SELECT c.title, SUM(suma*kaina) AS rezultato_suma,
ROUND(AVG(suma*kaina),2) AS rezultatas_suma_vid
FROM (m_produktas KAIP VIDINIS JOIN m_outcome AS b ON a.id=b.product_id)
VIDINIS JOIN m_category AS c ON a.ctgry_id=c.id
GROUP BY c.pavadinimas;
Klausimas Q028. Bendras ir vidutinis jos kvitų skaičius apskaičiuojamas kiekvienai prekei ir pateikiama informacija apie produktus, kurių bendras kvitų skaičius yra ne mažesnis kaip 500:
PASIRINKITE produkto_id, SUM(suma) AS summa_sum,
Apvalus(Vid.(suma),2) AS suma_vid
NUO m_pajamų
GROUP BY produkto_id
TURIMAS Suma(suma)>=500;
Klausimas Q029.Šioje užklausoje apskaičiuojama kiekvieno produkto įplaukų suma ir vidurkis, gautas 2011 m. antrąjį ketvirtį. Jei bendra prekės kvito suma yra ne mažesnė kaip 1000, tada rodoma informacija apie šią prekę:
PASIRINKITE pavadinimą, SUM(suma*kaina) AS pajamų_suma
NUO m_pajamų a VIDINIO PRISIJUNGIMO m_produktas b Į a.product_id=b.id
KUR dt TARP #4/1/2011# IR #6/30/2011#
GROUP BY pavadinimą
TURIMAS SUMMA(suma*kaina)>=1000;
Klausimas Q030. Kai kuriais atvejais reikia suderinti kiekvieną vienos lentelės įrašą su kiekvienu kitos lentelės įrašu; kuris vadinamas Dekarto sandauga. Lentelė, atsirandanti dėl tokio ryšio, vadinama Dekarto lentele. Pavyzdžiui, jei kurioje nors lentelėje A yra 100 įrašų, o lentelėje B yra 15 įrašų, tada jų Dekarto lentelę sudarys 100*15=150 įrašų. Ši užklausa sujungia kiekvieną m_income lentelės įrašą su kiekvienu įrašu m_outcome lentelėje:
IŠ m_pajamų, m_rezultatų;
Klausimas Q031.Įrašų grupavimo pagal du laukus pavyzdys. Ši SQL užklausa kiekvienam tiekėjui apskaičiuoja iš jo gautų prekių kiekį ir kiekį:
SUM(suma*kaina) AS pajamų_suma
Prašymas Q032.Įrašų grupavimo pagal du laukus pavyzdys. Šia užklausa kiekvienam tiekėjui apskaičiuojamas mūsų parduodamų produktų kiekis ir kiekis:
PASIRINKITE tiekėjo_id, produkto_id, SUM(suma) AS sumos_suma,
GROUP BY tiekėjo_id, produkto_id;
Klausimas Q033.Šiame pavyzdyje dvi aukščiau pateiktos užklausos (q031 ir q032) naudojamos kaip antrinės užklausos. Šių užklausų rezultatai naudojant LEFT JOIN metodą sujungiami į vieną ataskaitą. Šioje užklausoje pateikiama kiekvieno tiekėjo gautų ir parduotų produktų kiekio ir kiekio ataskaita. Atkreipkite dėmesį, kad jei kuri nors prekė jau gauta, bet dar neparduota, šio įrašo langelis result_sum bus tuščias. kad ši užklausa yra tik palyginti sudėtingų užklausų naudojimo kaip antrinės užklausos pavyzdys. Šios SQL užklausos su dideliu duomenų kiekiu našumas yra abejotinas:
PASIRINKTI*
NUO
SUM(suma*kaina) AS pajamų_suma
ON a.product_id=b.id GRUPĖ PAGAL tiekėjo_id, produkto_id) AS a
KAIRĖ PRISIJUNGTI
(Pasirinkite tiekėjo_id, produkto_id, SUM(suma) AS sumos_suma,
SUM(suma*kaina) AS rezultato_suma
IŠ „m_outcome“ KAIP VIDINIO PRISIJUNGIMO „m_product AS“ b
ON a.product_id=b.id GROUP BY BY tiekėjo_id, produkto_id) AS b
ĮJUNGTA (a.product_id=b.product_id) IR (a.tiekėjo_id=b.tiekėjo_id);
Klausimas Q034.Šiame pavyzdyje dvi aukščiau pateiktos užklausos (q031 ir q032) naudojamos kaip antrinės užklausos. Šių užklausų rezultatai naudojant RIGTH JOIN metodą sujungiami į vieną ataskaitą. Toliau pateiktoje užklausoje pateikiama kiekvieno kliento mokėjimų sumos ataskaita pagal jo naudojamas mokėjimo sistemas ir investicijų sumą. Šioje užklausoje pateikiama kiekvieno tiekėjo gautų ir parduotų produktų kiekio ir kiekio ataskaita. Atkreipkite dėmesį, kad jei kuri nors prekė jau parduota, bet dar neatvežta, šio įrašo langelis pajamų_suma bus tuščias. Tokių tuščių langelių buvimas yra pardavimo apskaitos klaidos rodiklis, nes prieš parduodant pirmiausia reikia, kad atitinkamas produktas būtų pristatytas:
PASIRINKTI*
NUO
(Pasirinkite tiekėjo_id, produkto_id, SUM(suma) AS sumos_suma,
SUM(suma*kaina) AS pajamų_suma
IŠ „m_pajamų“ KAIP VIDINIO PRISIJUNGIMO „m_product AS b“ ON a.product_id=b.id
GROUP BY tiekėjo_id, produkto_id) AS a
TEISINGAS PRISIJUNGTI
(Pasirinkite tiekėjo_id, produkto_id, SUM(suma) AS sumos_suma,
SUM(suma*kaina) AS rezultato_suma
IŠ „m_outcome“ KAIP VIDINIO PRISIJUNGIMO „m_product AS b“ ON a.product_id=b.id
GRUPĖ PAGAL tiekėjo_id, produkto_id) AS b
ĮJUNGTA (a.tiekėjo_id=b.tiekėjo_id) IR (a.product_id=b.product_id);
Klausimas Q035. Rodoma ataskaita, kurioje nurodoma pajamų ir išlaidų suma pagal gaminius. Tam pagal lenteles m_income ir m_outcome sukuriamas produktų sąrašas, tada kiekvienai prekei iš šio sąrašo apskaičiuojama jos pajamų suma pagal lentelę m_pajamos ir išlaidų suma pagal lentelę m_outcome:
PASIRINKITE produkto_id, SUM(suma) AS pajamų_suma,
SUM(iš_suma) AS rezultato_suma
NUO
(PASIRINKITE produkto_id, suma AS in_suma, 0 AS out_amount
NUO m_pajamų
SĄJUNGOS VISI
PASIRINKITE produkto_id, 0 AS suma, suma AS out_amount
IŠ m_rezultato) AS t
GROUP BY produkto_id;
Klausimas Q036. Funkcija EXISTS grąžina TRUE, jei jai perduota rinkinyje yra elementų. Funkcija EXISTS grąžina FALSE, jei jai perduotas rinkinys yra tuščias, ty joje nėra elementų. Šioje užklausoje rodomi produktų kodai, esantys ir m_income, ir m_outcome lentelėse:
PASIRINKTI ATSKIRTI produkto_id
IŠ m_pajamų AS a
WHERE EXISTS(SELECT produkto_id FROM m_outcome AS b
Klausimas Q037. Rodomi produktų kodai, esantys ir m_income, ir m_outcome lentelėse:
PASIRINKTI ATSKIRTI produkto_id
IŠ m_pajamų AS a
WHERE product_id IN (SELECT product_id FROM m_outcome)
Klausimas Q038. Rodomi produktų kodai, kurie yra m_income lentelėje, bet nėra m_outcome lentelėje:
PASIRINKTI ATSKIRTI produkto_id
IŠ m_pajamų AS a
WHERE NOT EXISTS(SELECT product_id FROM m_outcome AS b
WHERE b.product_id=a.product_id);
Klausimas Q039. Rodomas produktų su maksimaliu pardavimo kiekiu sąrašas. Algoritmas yra toks. Kiekvienai prekei skaičiuojamas jos pardavimų dydis. Tada nustatomas didžiausias šių sumų dydis. Tada kiekvienai prekei dar kartą apskaičiuojama jos pardavimų suma ir rodomas kodas bei prekių, kurių pardavimo suma lygi maksimaliai, pardavimo suma:
PASIRINKITE produkto_id, SUM(suma*kaina) AS suma_suma
IŠ m_outcome
GROUP BY produkto_id
TURIMAS SUMMA(suma*kaina) = (PASIRINKITE MAX(s_suma)
FROM (SELECT SUM(suma*kaina) AS s_suma FROM m_outcome GROUP BY product_id));
Klausimas Q040. Rezervuotas žodis IIF (sąlyginis operatorius) naudojamas loginei išraiškai įvertinti ir veiksmui atlikti priklausomai nuo rezultato (TRUE arba FALSE). Toliau pateiktame pavyzdyje prekės pristatymas laikomas "mažu", jei kiekis yra mažesnis nei 500. Priešingu atveju, ty gavimo kiekis yra didesnis arba lygus 500, pristatymas laikomas "dideliu":
PASIRINKITE dt, produkto_id, sumą,
IIF(suma IŠ m_pajamų;
SQL užklausa Q041. Tuo atveju, kai IIF operatorius naudojamas kelis kartus, patogiau jį pakeisti SWITCH operatoriumi. SWITCH operatorius (keleto pasirinkimo operatorius) naudojamas loginei išraiškai įvertinti ir veiksmui atlikti priklausomai nuo rezultato. Toliau pateiktame pavyzdyje pristatyta partija laikoma "maža", jei prekių kiekis partijoje yra mažesnis nei 500. Priešingu atveju, jei prekių kiekis yra didesnis arba lygus 500, partija laikoma "didelė" ":
PASIRINKITE dt, produkto_id, sumą,
SWITCH(suma =500,"didelis") AS ženklas
IŠ m_pajamų;
Klausimas Q042. Sekančioje užklausoje, jei prekių kiekis gautoje partijoje yra mažesnis nei 300, tai partija laikoma „maža“. Kitu atveju, jei sąlygos suma SELECT dt, produkto_id, suma,
IIF(suma IIF(suma IŠ m_pajamų;
SQL užklausa Q043. Sekančioje užklausoje, jei prekių kiekis gautoje partijoje yra mažesnis nei 300, tai partija laikoma „maža“. Kitu atveju, jei sąlygos suma SELECT dt, produkto_id, suma,
SWITCH(sumos suma >=1000,"didelis") AS ženklas
IŠ m_pajamų;
SQL užklausa Q044.Šioje užklausoje pardavimai skirstomi į tris grupes: maži (iki 150), vidutiniai (nuo 150 iki 300), dideli (300 ir daugiau). Toliau apskaičiuojama bendra suma kiekvienai grupei:
SELECT Category, SUM(outcome_sum) AS Ctgry_Total
FROM (SELECT suma*kaina AS rezultato_suma,
IIf(suma*kaina IIf(suma*kaina NUO m_rezultato) AS t
GROUP BY Kategorija;
SQL užklausa Q045. Funkcija DateAdd naudojama dienoms, mėnesiams ar metams pridėti prie nurodytos datos ir gauti naują datą. Kitas prašymas:
1) prie datos prideda 30 dienų nuo dt lauko ir parodo naują datą lauke dt_plus_30d;
2) prideda 1 mėnesį prie datos iš dt lauko ir rodo naują datą lauke dt_plus_1m:
SELECT dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m
IŠ m_pajamų;
SQL užklausa Q046. Funkcija DateDiff skirta apskaičiuoti skirtumą tarp dviejų datų skirtingais vienetais (dienomis, mėnesiais arba metais). Ši užklausa apskaičiuoja skirtumą tarp datos lauke dt ir dabartinės datos dienomis, mėnesiais ir metais:
SELECT dt, DateDiff("d", dt,Data()) AS paskutinę_dieną,
DateDiff("m",dt,Date()) AS paskutiniai_mėnesiai,
DateDiff("yyyy",dt,Data()) AS paskutiniai_metai
IŠ m_pajamų;
SQL užklausa Q047. Dienų skaičius nuo prekės gavimo datos (lentelė m_pajamos) iki dabartinės datos apskaičiuojamas naudojant DateDiff funkciją ir palyginama galiojimo data (lentelė m_produktas):
DateDiff("d",dt,Date()) AS paskutinės_dienos
IŠ „m_income“ KAIP VIDINIO PRISIJUNGIMO „m_product AS“ b
ON a.product_id=b.id;
SQL užklausa Q048. Skaičiuojamas dienų skaičius nuo prekės gavimo datos iki einamosios datos, tada tikrinama, ar šis kiekis neviršija galiojimo termino:
PASIRINKITE a.id, produkto_id, dt, gyvenimo dienas,
DateDiff("d",dt,Date()) AS paskutinės_dienos, IIf(paskutinės_dienos>gyvenimo dienos,"Taip","Ne") AS data_expire
FROM m_income a VIDINIS JOIN m_product b
ON a.product_id=b.id;
SQL užklausa Q049. Skaičiuojamas mėnesių skaičius nuo prekės gavimo dienos iki einamosios datos. Stulpelyje month_last1 apskaičiuojamas absoliutus mėnesių skaičius, stulpelyje month_last2 apskaičiuojamas visų mėnesių skaičius:
SELECT dt, DateDiff("m", dt,Data()) AS mėnesis_paskutinis,
DateDiff("m",dt,Data())-iif(day(dt)>day(data()),1,0) AS mėnesis_paskutinysis2
IŠ m_pajamų;
SQL užklausa Q050. Rodoma ketvirtinė ataskaita apie įsigytų prekių kiekį ir kiekį už 2011 m.
SELECT kvartal, SUM(outcome_sum) AS Iš viso
FROM (SELECT suma*kaina AS rezultato_suma, mėnuo(dt) AS m,
SWITCH(m =10,4) AS kvartalas
IŠ m_pajamų WHERE metai(dt)=2011) AS t
GROUP BY ketvirtį;
Klausimas Q051.Ši užklausa padeda išsiaiškinti, ar vartotojai galėjo į sistemą įvesti informaciją apie prekių suvartojimą, didesnį nei gautas prekių kiekis:
PASIRINKITE produkto_id, SUM(in_sum) AS pajamų_suma, SUM(iš_suma) AS rezultatų_suma
FROM (PASIRINKITE produkto_id, suma*kaina kaip in_sum, 0 kaip out_sum
nuo m_pajamų
SĄJUNGOS VISI
PASIRINKITE produkto_id, 0 kaip in_sum, suma*kaina kaip out_sum
iš m_rezultato) AS t
GROUP BY produkto_id
TURI SUMĄ (sumoje)
Prašymas Q052. Užklausos grąžintų eilučių numeravimas įgyvendinamas įvairiais būdais. Pavyzdžiui, MS Access parengtos ataskaitos eilutes galite pernumeruoti naudodami pačią MS Access. Taip pat galite pernumeruoti naudodami programavimo kalbas, pavyzdžiui, VBA arba PHP. Tačiau kartais tai reikia padaryti pačioje SQL užklausoje. Taigi, ši užklausa sunumeruos m_income lentelės eilutes pagal ID lauko reikšmių didėjimo tvarką:
PASIRINKITE SKAIČIUS (*) kaip N, b.id, b.product_id, b.amount, b.price
NUO m_pajamų a VIDINIO PRISIJUNGIMO m_pajamos b PRIE a.id GRUPĖS BY b.id, b.product_id, b.summa, b.price;
Klausimas Q053. Rodomi penki geriausi produktai tarp produktų pagal pardavimų kiekį. Pirmieji penki įrašai spausdinami naudojant TOP instrukciją:
PASIRINKITE TOP 5, produkto_id, suma(suma*kaina) AS suma
IŠ m_outcome
GROUP BY produkto_id
UŽSAKYTI pagal sumą(suma*kaina) DESC;
Klausimas Q054. Rodomi penki geriausi produktai tarp produktų pagal pardavimą, o eilutės sunumeruojamos:
SELECT COUNT(*) AS N, b.product_id, b.summa
NUO
FROM m_outcome GROUP BY product_id) AS a
VIDINIS PRISIJUNGIMAS
(SELECT product_id, suma(suma*kaina) AS suma,
suma*10000000+product_id AS id
FROM m_outcome GROUP BY product_id) AS b
ON a.id>=b.id
GROUP BY b.product_id, b.summa
HAING COUNT(*)ORDER BY COUNT(*);
Klausimas Q055.Ši SQL užklausa rodo matematinių funkcijų COS, SIN, TAN, SQRT, ^ ir ABS naudojimą MS Access SQL:
SELECT (pasirinkite skaičių (*) iš m_income) kaip N, 3.1415926 kaip pi, k,
2*pi*(k-1)/N kaip x, COS(x) kaip COS_, SIN(x) kaip SIN_, TAN(x) kaip TAN_,
SQR(x) kaip SQRT_, x^3 kaip "x^3", ABS(x) kaip ABS_
FROM (SELECT COUNT(*) AS k
IŠ m_pajamų KAIP VIDINIO PRISIJUNGIMO m_pajamos AS b ON a.idGROUP BY b.id) t;
SQL užklausa. MS Access pavyzdžiai. ATNAUJINIMAS: 1-10 d
Užklausa U001.Ši SQL keitimo užklausa padidina prekių, kurių kodas 3 m_income lentelėje, kainas 10 %:ATNAUJINTAS m_pajamų NUSTATYMAS kaina = kaina*1.1
WHERE produkto_id=3;
Užklausa U002.Ši SQL naujinimo užklausa padidina visų produktų kiekį lentelėje m_income 22 vienetais, kurių pavadinimai prasideda žodžiu „Alyva“:
ATNAUJINTI m_pajamų NUSTATYTA suma = suma+22
WHERE produkto_id IN (SELECT id FROM m_product WHERE pavadinimas LIKE "Aliejus*");
Prašymas U003.Ši SQL užklausa dėl lentelės m_outcome pakeitimo sumažina visų Sladkoe LLC gaminamų prekių kainas 2 procentais:
UPDATE m_outcome SET kaina = kaina*0,98
WHERE product_id IN
(PASIRINKITE a.id IŠ „m_product“ a VIDINIO PRISIJUNGIMO m_tiekėjo b
ON a.supplier_id=b.id WHERE b.title="LLC"Сладкое"");. !}
Užklausos programoje „Access“ yra pagrindinis įrankis duomenims duomenų bazių lentelėse pasirinkti, atnaujinti ir apdoroti. „Access“, vadovaudamasi reliacinių duomenų bazių koncepcija, užklausoms vykdyti naudoja SQL (struktūrinę užklausų kalbą). Naudojant SQL kalbos instrukcijas, bet kokia „Access“ užklausa įgyvendinama.
Pagrindinis užklausos tipas yra atrankos užklausa. Šios užklausos rezultatas yra nauja lentelė, kuri egzistuoja iki užklausos uždarymo. Įrašai formuojami sujungiant lentelių, kuriomis grindžiama užklausa, įrašus. Lentelės įrašų sujungimo būdas nurodomas apibrėžiant jų ryšį duomenų schemoje arba kuriant užklausą. Užklausoje suformuluotos atrankos sąlygos leidžia filtruoti įrašus, kurie sudaro lentelių sujungimo rezultatą.
Programoje „Access“ galima sukurti kelių tipų užklausas:
- prašymo pavyzdys- parenka duomenis iš vienos lentelės ar užklausos arba kelių tarpusavyje susijusių lentelių ir kitų užklausų. Rezultatas yra lentelė, kuri egzistuoja iki užklausos uždarymo. Rezultatų lentelės įrašai generuojami pagal nurodytas atrankos sąlygas ir naudojant kelias lenteles sujungiant jų įrašus;
- prašymas sukurti lentelę- parenka duomenis iš tarpusavyje susijusių lentelių ir kitų užklausų, tačiau, skirtingai nei pasirinkimo užklausa, išsaugo rezultatą naujoje nuolatinėje lentelėje;
- prašymai atnaujinti, papildyti, ištrinti- yra veiksmų užklausos, dėl kurių keičiami duomenys lentelėse.
Užklausos programoje Access projektavimo režimu turi duomenų schemą, kurioje rodomos naudojamos lentelės, ir užklausos forma, kurioje projektuojama užklausos lentelės struktūra ir įrašų parinkimo sąlygos (4.1 pav.).
Naudodami užklausą galite atlikti šių tipų duomenų apdorojimą:
- įtraukti vartotojo pasirinktus lentelės laukus į užklausos lentelę;
- atlikti skaičiavimus kiekviename iš gautų įrašų;
- atrinkti įrašus, atitinkančius atrankos sąlygas;
- sukurti naują virtualią lentelę, pagrįstą tarpusavyje susijusių lentelių įrašų derinimu;
- grupiniai įrašai, kurių viename ar keliuose laukuose yra vienodos reikšmės, vienu metu atlieka statistines funkcijas kituose grupės laukuose ir dėl to kiekvienai grupei įtraukia po vieną įrašą;
- sukurti naują duomenų bazės lentelę naudojant duomenis iš esamų lentelių;
- atnaujinti laukus pasirinktame įrašų pogrupyje;
- ištrinti pasirinktą įrašų poaibį iš duomenų bazės lentelės;
- įtraukti pasirinktą įrašų poaibį į kitą lentelę.
„Access“ užklausos yra kitų užklausų, formų ir ataskaitų įrašų šaltiniai. Naudodami užklausą galite surinkti visą informaciją tam, kad iš kelių lentelių sugeneruotumėte tam tikros temos dokumentą, o tada pagal ją sukurtumėte formą – elektroninį šio dokumento atvaizdą. Jei vedlys sukuria formą arba ataskaitą, remdamasis keliomis tarpusavyje susijusiomis lentelėmis, užklausa jiems automatiškai sugeneruojama kaip įrašų šaltinis.
Norėdami tai sustiprinti, žiūrėkite vaizdo įrašą.