SQL – õppetund 4. Andmete valimine – SELECT-lause

Seega on meie foorumi andmebaasis kolm tabelit: kasutajad (kasutajad), teemad (teemad) ja postitused (sõnumid). Ja me tahame näha, milliseid andmeid need sisaldavad. Selleks on SQL-is operaator VALI. Selle kasutamise süntaks on järgmine:

SELECT vali_mida FROM vali_kellest;

"what_to_select" asemel peame määrama kas veeru nime, mille väärtusi tahame näha, või mitme komadega eraldatud veeru nime või tärni (*), mis tähendab kõigi veergude valikut. laud. "from_choose" asemel peaksite määrama tabeli nime.

Vaatame esmalt kõiki kasutajate tabeli veerge:

SELECT * FROM kasutajad;

Need on kõik meie andmed, mille me sellesse tabelisse sisestasime. Kuid oletame, et tahame vaadata ainult veergu id_user (näiteks viimases õppetükis pidime teematabeli täitmiseks teadma, millised id_users on kasutajate tabelis). Selleks määrame päringus selle veeru nime:

SELECT id_user FROM kasutajatest;

Noh, kui tahame näha näiteks oma kasutajate nimesid ja e-maile, siis loetleme huvipakkuvad veerud komadega eraldatuna:

VALI kasutajate nimi, e-post;

Samamoodi näete, milliseid andmeid meie teised tabelid sisaldavad. Vaatame, mis teemad meil on:

SELECT * FROM teemadest;

Nüüd on meil ainult 4 teemat ja kui neid on 100? Tahaks, et need oleksid kuvatud näiteks tähestikulises järjekorras. SQL-is on selle jaoks märksõna. TELLI millele järgneb veeru nimi, mille järgi sortimine toimub. Süntaks on järgmine:

SELECT veeru_nimi FROM tabeli_nimi ORDER BY sorti_veeru_nimi;

Vaikimisi sorteerimine on tõusev, kuid seda saab muuta märksõna lisamisega DESC

Nüüd on meie andmed sorteeritud kahanevas järjekorras.

Saate sortida korraga mitme veeru järgi. Näiteks järgmine päring sorteerib andmed veeru teema_nimi järgi ja kui selles veerus on mitu identset rida, sorteeritakse veerg id_author kahanevas järjekorras:

Võrrelge tulemust eelmise päringu tulemusega.

Väga sageli ei vaja me tabelist kogu teavet. Näiteks tahame teada, millised teemad on loonud kasutaja sveta (id=4). SQL-is on selle jaoks märksõna. KUS, on sellise päringu süntaks järgmine:

Meie näite puhul on tingimuseks kasutaja ID, st. soovime ainult ridu, mille veerus id_author on 4 (kasutaja ID sveta):

Või tahame teada, kes lõi teema "jalgrattad":

Muidugi oleks mugavam kuvada autori id asemel autori nimi, kuid nimed salvestatakse teise tabelisse. Hilisemates tundides õpime, kuidas valida andmeid mitmest tabelist. Vahepeal uurime, milliseid tingimusi saab WHERE-i märksõnaga määrata.

Operaator	Kirjeldus
= (võrdne)	Valitud väärtused on võrdsed määratud väärtustega Näide: SELECT * FROM teemadest WHERE id_author=4; Tulemus:
> (veel)	Valitakse määratust suuremad väärtused Näide: SELECT * FROM teemadest WHERE id_author>2; Tulemus:
< (меньше)	Valitakse määratud väärtusest väiksemad väärtused Näide: SELECT * FROM teemadest WHERE id_author Tulemus:
>= (suurem või võrdne)	Valitakse väärtused, mis on määratud väärtusest suuremad või sellega võrdsed. Näide: SELECT * FROM teemadest WHERE id_autor>=2; Tulemus:
<= (меньше или равно)	Valitakse väärtused, mis on määratud väärtusest väiksemad või sellega võrdsed. Näide: SELECT * FROM teemadest WHERE id_author Tulemus:
!= (ei ole võrdne)	Valitakse väärtused, mis ei ole määratud määratud Näide: SELECT * FROM teemadest WHERE id_autor!=1; Tulemus:
EI OLE NULL	Valitakse read, millel on määratud väljal väärtused Näide: SELECT * FROM teemadest WHERE id_author EI OLE NULL; Tulemus:
ON NULL	Valitakse read, millel pole määratud väljal väärtust Näide: SELECT * FROM teemadest WHERE id_author IS NULL; Tulemus: Tühi komplekt – selliseid stringe pole.
VAHEL (vahel)	Valitakse määratud väärtuste vahel olevad väärtused. Näide: SELECT * FROM teemadest WHERE id_author BETWEEN 1 AND 3; Tulemus:
IN (väärtus sisaldub)	Määratud väärtustele vastavad väärtused Näide: SELECT * FROM teemadest WHERE id_author IN (1, 4); Tulemus:
NOT IN (väärtus ei sisaldu)	Valitud väärtused, mis pole määratud Näide: SELECT * FROM teemadest WHERE id_author NOT IN (1, 4); Tulemus:
LIKE (matš)	Valitakse näidisväärtused Näide: SELECT * FROM teemadest WHERE teema_nimi LIKE "vel%"; Tulemus: Operaatori LIKE võimalikke metamärke käsitletakse allpool.
MITTE NAGU	Valitakse väärtused, mis ei vasta proovile Näide: SELECT * FROM teemadest WHERE teema_nimi EI MEELDI "vel%"; Tulemus:

LIKE operaatori metamärgid

Metamärkide otsinguid saab teha ainult tekstiväljadel.

Kõige tavalisem metamärk on % . See tähendab mis tahes tegelasi. Näiteks kui tahame leida sõnu, mis algavad tähtedega "vel", siis kirjutame LIKE "vel%" ja kui tahame leida sõnu, mis sisaldavad märke "klubi", siis kirjutame LIKE "% klubi%". Näiteks:

Teine sageli kasutatav metamärk on _ . Erinevalt %-st, mis tähistab vähe või üldse mitte ühtegi märki, tähistab alakriips täpselt ühte märki. Näiteks:

Pöörake tähelepanu metamärgi ja "kala" vahelisele tühikule, kui jätate selle vahele, siis päring ei tööta, sest metategelane _ tähistab täpselt ühte märki ja tühik on ka märk.

Tänaseks piisab. Järgmises õppetükis õpime, kuidas teha päringuid kahe või enama tabeli kohta. Vahepeal proovige teha oma päringuid postituste tabeli (sõnumite) vastu.

See õppetund räägib sellest SQL päringud andmebaasi sisse VBA juurdepääs. Vaatame, kuidas VBA päringuid INSERT, UPDATE, DELETE andmebaasi teostatakse, samuti õpime, kuidas saada SELECT päringust konkreetne väärtus.

Need, kes programmeerivad VBA juurdepääs ja SQL serveri andmebaasiga töötades seisavad nad sageli silmitsi sellise lihtsa ja vajaliku ülesandega nagu SQL päringu saatmine andmebaasi, olgu selleks siis INSERT, UPDATE või lihtne SQL SELECT päring. Ja kuna oleme algajad programmeerijad, peaksime ka sellega hakkama saama, nii et täna teeme just seda.

Oleme juba puudutanud SQL-serverist andmete hankimise teemat, kus kirjutasime VBA-s koodi nende andmete saamiseks, näiteks artiklis Andmete üleslaadimine tekstifaili MSSql 2008-st või puudutasime veidi ka materjalis. Andmete üleslaadimine Accessist Wordi ja Exceli malli, kuid nii või teisiti kaalusime seda pealiskaudselt ja täna teen ettepaneku sellest veidi üksikasjalikumalt rääkida.

Märge! Kõiki allpool toodud näiteid käsitletakse Access 2003 ADP projekti ja MSSql 2008 andmebaasi kasutades.

Algandmed näideteks

Oletame, et meil on tabel test_tabel, mis sisaldab kuude numbreid ja nimesid aastas (päringud tehakse kasutades juhtimisstuudio)

LOO TABEL .( MITTE NULL, (50) NULL) LÄHEB

Nagu ma ütlesin, kasutame MS SQL 2008-ga töötama konfigureeritud ADP projekti, milles lõin testvormi ja lisasin stardi nupu pealkirjaga "Jookse", mida vajame oma koodi testimiseks, st. kirjutame kogu koodi sündmuste käitlejasse " Nupu vajutamine».

Andmebaasipäringud INSERT, UPDATE, DELETE VBA-s

Et mitte kaua venitada, alustame, oletame, et peame oma testtabelisse rea lisama ( kood on kommenteeritud)/

Private Sub start_Click() "Deklareerige muutuja päringustringi salvestamiseks Dim sql_query Stringina "Kirjutage sinna vajalik päring sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "Juuni")" "Käivita see koos DoCmd-ga. RunSQL sql_query End Sub

Sel juhul teostatakse päring praeguste andmebaasiühenduse sätete abil. Saame kontrollida, kas andmed on lisatud või mitte.

Nagu näete, on andmed sisestatud.

Ühe rea kustutamiseks kirjutame järgmise koodi.

Private Sub start_Click() "Deklareerige muutuja päringustringi hoidmiseks Dim sql_query Stringina "Kirjutage sellesse kustutamispäring sql_query = "DELETE test_table WHERE id = 6" "Käivita see DoCmd.RunSQL sql_query End Sub

Kui kontrollime, näeme, et soovitud rida on kustutatud.

Andmete uuendamiseks kirjutame sql_query muutujale uuenduspäringu, loodan, et tähendus on selge.

SELECT päring VBA andmebaasi

Siin on asjad veidi huvitavamad kui teiste SQL-i konstruktsioonidega.

Esiteks oletame, et peame tabelist hankima kõik andmed ja näiteks töötleme neid ja kuvame need sõnumis ning loomulikult saate seda kasutada ka muudel eesmärkidel, selleks kirjutame järgmise kood

Private Sub start_Click() "Muutujate deklareerimine "Andmebaasist pärineva kirje jaoks Dim RS As ADODB.Recordset "Päringu string Dim sql_query As String "String koguandmete kuvamiseks sõnumis Dim str As String "Uue objekti loomine kirjete komplekti jaoks RS = Uus ADODB .Recordset "Query string sql_query = "SELECT id, name_mon FROM test_table" "Käivita päring, kasutades praeguseid projektiühenduse sätteid, et kuvada teade str = str & RS.Fields("id") & "-" & RS. Fields("name_mon") & vbnewline "mine järgmise kirje juurde RS.MoveNext Wend "Väljundsõnum msgbox str End Sub

Siin kasutame juba VBA Access Loope, et vaadata läbi kõik meie kirjekomplekti väärtused.

Kuid üsna sageli on vaja kirjete komplektist saada mitte kõiki väärtusi, vaid ainult ühte, näiteks kuu nime selle koodi järgi. Ja selleks on tsükli kasutamine kuidagi kallis, nii et saame lihtsalt kirjutada päringu, mis tagastab ainult ühe väärtuse ja viitab sellele, näiteks saame kuu nime koodiga 5

Private Sub start_Click() "Muutujate deklareerimine "Andmebaasist pärineva kirje jaoks Dim RS As ADODB.Recordset "Päringu string Dim sql_query As String "String lõppväärtuse kuvamiseks Dim str As String "Uue objekti loomine kirjete komplekti jaoks RS = Uus ADODB.Recordset "Query string sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "Käivita päring, kasutades praeguseid projektiühenduse sätteid RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic = "RS0.Fields" ) msgbox str end sub

Universaalsuse huvides oleme siin juba käsitlenud mitte lahtri nime, vaid selle indeksi, st. 0, mis on kõige esimene väärtus Kirjekomplekt, lõpuks saime väärtuse "mai".

Nagu näete, on kõik üsna lihtne. Kui teil on sageli vaja saada andmebaasist konkreetne väärtus ( nagu viimases näites), siis soovitan väljastada kogu koodi eraldi funktsioonina (Kuidas VBA Access 2003-s funktsiooni kirjutada) ühe sisendparameetriga, näiteks kuukoodiga ( arvestades meie eeskuju) ja lihtsalt, kui on vaja seda väärtust kuvada, helistage vajalik funktsioon vajaliku parameetriga ja ongi kõik, vähendame oluliselt VBA koodi ja parandame oma programmi tajumist.

See on tänaseks kõik. Edu!

Haridusprojekti "Kauplus" kirjeldus

Tabeli linkide skeem

Tabelite kirjeldus

m_category – tootekategooriad

m_income - kauba kättesaamine

m_outcome - kaupade tarbimine

m_product – kataloog, tootekirjeldus

m_tarnija - kataloog; tarnija teave

m_üksus - kataloog; ühikut

Selles õpetuses toodud näidete praktiliseks testimiseks peab teil olema saadaval järgmine tarkvara:

Microsoft Access 2003 või uuem.

SQL-päring MS Accessis. Alusta

Tabeli sisu vaatamiseks topeltklõpsake vasakpoolsel paanil tabeli nimel:

Tabelivälja redigeerimisrežiimile lülitumiseks valige ülemisel paneelil Kujundusrežiim.

SQL-päringu tulemuse kuvamiseks topeltklõpsake päringu nimel vasakpoolsel paanil:

SQL-päringu redigeerimisrežiimile lülitumiseks valige ülemisel paneelil SQL-režiim:

SQL päring. Näited MS Accessis. VALI: 1-10

SQL-päringus kasutatakse andmebaasi tabelitest valimiseks käsku SELECT.

SQL päring Q001. SQL-päringu näide ainult nõutavate väljade saamiseks soovitud järjestuses:

SELECT dt, toote_id, summa

FROM m_income;

SQL päring Q002. Selles SQL-päringu näites kasutatakse tärni (*) tabeli m_product kõigi veergude kuvamiseks, teisisõnu seose m_product kõigi väljade hankimiseks:

VALI*
FROM m_product;

TaotlusSQLQ003. DISTINCT-lauset kasutatakse dubleerivate kirjete kõrvaldamiseks ja paljude ainulaadsete kirjete saamiseks:

VALI ERINEV toote_id

FROM m_income;

SQL päring Q004. Lauset ORDER BY kasutatakse kirjete sortimiseks (järjestamiseks) konkreetse välja väärtuste järgi. Välja nimi järgneb klauslile ORDER BY:

VALI*
M_sissetulekust

TELLI hinna järgi;

SQL päring Q005. ASC-lauset kasutatakse lisaks ORDER BY-lausele ja seda kasutatakse kasvava järjestuse määratlemiseks. Lauset DESC kasutatakse lisaks ORDER BY lausele ja seda kasutatakse kahaneva sortimise määratlemiseks. Kui ei ASC ega DESC pole määratud, eeldatakse ASC olemasolu (vaikimisi):

VALI*
M_sissetulekust

TELLIMINE dt DESC , hind;

SQL päring Q006. Tabelist vajalike kirjete valimiseks kasutatakse erinevaid loogilisi avaldisi, mis väljendavad valikutingimust. Tõeväärtus avaldis tuleb WHERE-klausli järel. Näide tabelist m_income kõigi kirjete hankimisest, mille summa väärtus on suurem kui 200:

VALI*
M_sissetulekust

KUS summa>200;

SQL päring Q007. Keeruliste tingimuste väljendamiseks kasutatakse loogikatehteid AND (konjunktsioon), VÕI (disjunktsioon) ja NOT (loogiline eitus). Näide tabelist m_outcome kõigi kirjete hankimisest, mille summa väärtus on 20 ja hinna väärtus on suurem kui 10 või sellega võrdne:

hind

FROM m_outcome
KUS summa=20 JA hind>=10;

SQL päring Q008. Kahe või enama tabeli andmete ühendamiseks kasutage lauseid INNER JOIN, LEFT JOIN, RIGHT JOIN. Järgmine näide toob väljad dt, product_id, summa, price tabelist m_income ja pealkirja väljad tabelist m_product. Tabeli m_sissetulek kirje ühendatakse tabeli m_toote kirjega, kui m_tulu.toote_id väärtus on võrdne m_toote.id väärtusega:

ON m_sissetulek.toote_id=m_toote.id;

SQL päring Q009. Selle SQL-päringu puhul peate tähelepanu pöörama kahele asjale: 1) otsingutekst on ümbritsetud jutumärkidega ("); 2) kuupäev on vormingus #Month/Day/Year#, mis on MS jaoks õige Juurdepääs. Teistes süsteemides võib kuupäevavorming olla erinev. Näide piima kättesaamise teabe kuvamisest 12. juunil 2011. Pöörake tähelepanu kuupäeva vormingule #6/12/2011#:

SELECT dt, product_id, pealkiri, summa, hind

FROM m_income SISEMINE JOIN m_product

WHERE title="Piim" And dt=#6/12/2011#; !}

SQL päring Q010. Käsku BETWEEN kasutatakse selleks, et testida, kas väärtuste vahemik kuulub sellele. SQL-päringu näide, mis kuvab teavet 1. juunist 30. juunini 2011 vastu võetud kaupade kohta:

VALI*
FROM m_income SISEMINE JOIN m_product

ON m_income.product_id=m_product.id
KUS dt #6/1/2011# ja #6/30/2011#;

SQL päring. Näited MS Accessis. VALI: 11-20

Ühe SQL-päringu saab pesastada teise sisse. Alampäring pole midagi muud kui päring päringu sees. Tavaliselt kasutatakse alampäringut WHERE-klauslis. Kuid on ka teisi võimalusi alampäringute kasutamiseks.

Taotle Q011. Kuvab infot toodete kohta tabelist m_product, mille koodid on ka tabelis m_income:

VALI*
M_produktist

WHERE id IN (SELECT toote_id FROM m_tulu);

Taotle Q012. Kuvatakse toodete loend tabelist m_product, mille koode tabelis m_outcome pole:

VALI*
M_produktist

WHERE id NOT IN (SELECT product_id FROM m_outcome);

Taotle Q013. See SQL-päring tagastab kordumatu loendi koodidest ja tootenimedest, mille koodid on tabelis m_income, kuid mitte tabelis m_outcome:

VALI ERINEV toote_id, pealkiri

FROM m_income SISEMINE JOIN m_product
ON m_income.product_id=m_product.id
KUS toote_id EI OLE IN (SELECT toote_id FROM m_tulemus);

Taotle Q014. Tabelist m_category kuvatakse unikaalne kategooriate loend, mille nimed algavad tähega M:

VALI ERINEV pealkiri

M_produktist
KUS pealkiri LIKE "M*";

Taotle Q015. Näide päringu väljadel aritmeetiliste toimingute tegemisest ja päringu väljade ümbernimetamisest (alias). Selles näites arvutatakse iga kaubatarbimiskirje kulu = kogus*hind ja kasum, eeldades, et kasum moodustab 7 protsenti müügist:

summa*hind/100*7 AS kasum
FROM m_outcome;

Taotlus Q016. Aritmeetilisi toiminguid analüüsides ja lihtsustades saate päringu täitmise kiirust suurendada:

SELECT dt, toote_id, summa, hind, summa*hind AS tulemuse_summa,

tulemus_summa*0,07 AS-i kasum
FROM m_outcome;

Taotle Q017. Kasutades lauset INNER JOIN, saate kombineerida mitme tabeli andmeid. Järgmises näites, olenevalt ctgry_id väärtusest, vastendatakse iga kirje m_sissetulek tabelis selle kategooria nimega tabelist m_category, kuhu toode kuulub:

SELECT c.title, b.title, dt, summa, hind, summa*price AS tulu_summa

FROM (m_tulu KUI SISEMINE LIITUMINE m_toode AS b ON a.product_id=b.id)
SISEMINE JOIN m_category AS c ON b.ctgry_id=c.id
ORDER c.title, b.title;

Taotle Q018. Selliseid funktsioone nagu SUM – summa, COUNT – kogus, AVG – aritmeetiline keskmine, MAX – maksimaalne väärtus, MIN – minimaalne väärtus nimetatakse koondfunktsioonideks. Need võtavad mitu väärtust ja tagastavad töötlemisel ühe väärtuse. Näide väljade summa ja hinna korrutise summa arvutamisest koondfunktsiooni SUM abil:

SELECT SUM(summa*hind) AS Kogu_Summa

FROM m_income;

Taotle Q019. Näide mitme koondfunktsiooni kasutamisest:

SELECT Summa(summa) AS Summa_Summa, AVG(summa) AS Summa_AVG,

MAX(summa) AS Summa_Max, Min(summa) AS Summa_Min,
Count(*) AS Total_Arv
FROM m_income;

Taotlege Q020. Selles näites arvutatakse kõigi 2011. aasta juunis saadud koodiga 1 üksuste summa:

SELECT Sum(summa*hind) AS tulu_summa

M_sissetulekust
KUS toote_id=1 JA dt VAHEL #6/1/2011# JA #6/30/2011#;.

Taotle Q021. Järgmine SQL-päring arvutab, kui palju müüdi koodiga 4 või 6 kaupu:

SELECT Sum(summa*hind) kui tulemuse_summa

FROM m_outcome
KUS toote_id=4 VÕI toote_id=6;

Taotlege Q022. Arvutatakse, millise summa eest müüdi 12. juunil 2011 kaupa koodiga 4 või 6:

SELECT Sum(summa*hind) AS tulemuse_summa

FROM m_outcome
KUS (toote_id=4 VÕI toote_id=6) JA dt=#6/12/2011#;

Taotle Q023.Ülesanne on selline. Arvutage kogusumma, mille eest kategooria "Küpsetooted" kaupu krediteeriti.

Selle probleemi lahendamiseks peate kasutama kolme tabelit: m_income, m_product ja m_category, sest:

- krediteeritud kaupade kogus ja hind salvestatakse tabelisse m_income;
- iga toote kategooriakood salvestatakse tabelisse m_product;
- kategooria pealkirja nimi salvestatakse tabelisse m_category.

Selle probleemi lahendamiseks kasutame järgmist algoritmi:

- kategooria koodi "Küpsetooted" määramine tabelist m_kategooria alampäringu abil;
- m_income ja m_product tabelite ühendamine, et määrata iga krediteeritud toote kategooria;
- kauba laekumise summa (= kogus * hind) arvutamine, mille kategooria kood on võrdne ülaltoodud alampäringuga määratletud koodiga.
VALI
M_TOOTEST KUI SISEMINE LIITUMINE m_income AS b ON a.id=b.product_id
WHERE ctgry_id = (SELECT id FROM m_category WHERE title="küpsetised"); !}

Taotle Q024. Kategooria "Küpsetatud tooted" krediteeritud kaupade kogusumma arvutamise probleem lahendatakse järgmise algoritmiga:
- iga tabeli m_income kirje, olenevalt selle toote_id väärtusest tabelist m_category, ühtib kategooria nimega;
- valige kirjed, mille kategooria on võrdne "Küpsetatud tooted";
- arvuta sissetuleku suurus = kogus * hind.

FROM (m_toode KUI SISEMINE LIITUMINE m_income AS b ON a.id=b.product_id)

WHERE c.title="Küpsetised"; !}

Taotle Q025. See näide arvutab, kui palju tooteid tarbiti:

VALI COUNT(toote_id) AS toote_knt

FROM (SELECT DISTINCT product_id FROM m_outcome) AS t;

Taotle Q026. Kirjete rühmitamiseks kasutatakse klauslit GROUP BY. Tavaliselt rühmitatakse kirjed ühe või mitme välja väärtuse järgi ja igale rühmale rakendatakse koondtoimingut. Näiteks järgmine päring genereerib kauba müügi aruande. See tähendab, et luuakse tabel, mis sisaldab kaupade nimetusi ja summat, mille eest neid müüakse:

SELECT pealkiri, SUM(summa*hind) AS tulemuse_summa

M_produktist KUI SISEMINE LIITUMINE m_outcome AS b
ON a.id=b.product_id
GROUP BY pealkirja järgi;

Taotlege Q027. Müügiaruanne kategooriate kaupa. See tähendab, et luuakse tabel, mis sisaldab tootekategooriate nimetusi, kogusummat, mille eest nende kategooriate kaupu müüakse, ja keskmist müügisummat. Funktsiooni ROUND kasutatakse keskmise väärtuse ümardamiseks lähima sajandikuni (teine ​​komakoht pärast kümnendkoha eraldajat):

SELECT c.title, SUM(summa*hind) AS result_sum,

ROUND(AVG(summa*hind),2) AS tulemus_summa_keskm
FROM (m_product KUI SISEMINE LIITUMINE m_tulemus AS b ON a.id=b.product_id)
SISEMINE LIITUMINE m_category AS c ON a.ctgry_id=c.id
GROUP BY c.title;

Taotle Q028. Iga toote kohta arvutatakse selle laekumiste koguarv ja keskmine arv ning kuvatakse teave kaupade kohta, mille laekumiste koguarv on vähemalt 500:

SELECT toote_id, SUM(summa) AS summa_summa,

Round(keskm.(summa),2) AS summa_keskm
M_sissetulekust
GROUP BY toote_id
HAVING Summa(summa)>=500;

Taotle Q029. See päring arvutab iga kauba kohta 2011. aasta teises kvartalis saadud laekumiste summa ja keskmise. Kui kauba kättesaamise kogusumma ei ole väiksem kui 1000, siis kuvatakse selle toote kohta teave:

SELECT pealkiri, SUM(summa*hind) AS tulu_summa

ALAST m_sissetulekust a SISEMINE LIITUMINE m_toode b ON a.toote_id=b.id
KUS dt 4/1/2011# JA #6/30/2011#
GROUP BY pealkirja järgi
HAVING SUM(summa*hind)>=1000;

Küsi Q030. Mõnel juhul on vaja sobitada mõne tabeli iga kirje teise tabeli iga kirjega; mida nimetatakse Descartes'i tooteks. Sellise ühenduse tulemusel saadud tabelit nimetatakse Descartes'i tabeliks. Näiteks kui mõnes tabelis A on 100 kirjet ja tabelis B on 15 kirjet, siis nende Descartes'i tabel koosneb 100*15=150 kirjest. Järgmine päring ühendab tabeli m_income iga kirje iga tabeli m_outcome kirjega:
FROM m_sissetulek, m_tulemus;

Taotlege Q031. Näide kirjete grupeerimisest kahe välja järgi. Järgmine SQL-päring arvutab iga tarnija kohta temalt saadud kauba koguse ja koguse:

SUM(summa*hind) AS tulu_summa

Taotlege Q032. Näide kirjete grupeerimisest kahe välja järgi. Järgmine päring arvutab iga tarnija kohta meie poolt müüdud toodete koguse ja koguse:

SELECT tarnija_id, toote_id, SUM(summa) AS summa_summa,

GROUP BY tarnija_id, toote_id;

Taotlege Q033. Selles näites kasutatakse kahte ülaltoodud päringut (q031 ja q032) alampäringutena. Nende päringute tulemused liidetakse üheks aruandeks, kasutades LEFT JOIN meetodit. Järgmine päring kuvab aruande iga tarnija kohta saadud ja müüdud toodete arvu ja koguse kohta. Tasub pöörata tähelepanu asjaolule, et kui mõni toode on juba saabunud, kuid pole veel müüdud, siis selle kirje result_sum lahter on tühi. et see päring on vaid näide suhteliselt keerukate päringute kasutamisest alampäringuna. Selle suure andmemahuga SQL-päringu jõudlus on küsitav:

VALI*
FROM

SUM(summa*hind) AS tulu_summa

ON a.product_id=b.id GROUP BY BY tarnija_id, toote_id) AS a
VASAKUID LIITUMINE
(VALI tarnija_id, toote_id, SUM(summa) AS summa_summa,
SUM(summa*hind) AS tulemuse_summa
FROM m_outcome KUI SISEMINE LIITUMINE m_product AS b
ON a.product_id=b.id GROUP BY BY tarnija_id, toote_id) AS b
ON (a.product_id=b.product_id) JA (a.supplier_id=b.supplier_id);

Taotlege Q034. Selles näites kasutatakse kahte ülaltoodud päringut (q031 ja q032) alampäringutena. Nende päringute tulemused kombineeritakse üheks aruandeks, kasutades meetodit RIGTH JOIN. Järgnev päring prindib välja aruande iga kliendi poolt kasutatud maksesüsteemide eest tehtud maksete ja tehtud investeeringute summa kohta. Järgmine päring kuvab aruande iga tarnija kohta saadud ja müüdud toodete arvu ja koguse kohta. Pange tähele, et kui toode on juba müüdud, kuid pole veel kätte saanud, on selle kirje tulu_summa lahter tühi. Selliste tühjade lahtrite olemasolu viitab veale müügiarvestuses, kuna enne müüki on vaja, et vastav toode kohale jõuaks:

VALI*
FROM

(VALI tarnija_id, toote_id, SUM(summa) AS summa_summa,
SUM(summa*hind) AS tulu_summa
FROM m_income KUI SISEMINE LIITUMINE m_product AS b ON a.product_id=b.id
GROUP BY BY tarnija_id, toote_id) AS a
ÕIGE LIITUMINE
(VALI tarnija_id, toote_id, SUM(summa) AS summa_summa,
SUM(summa*hind) AS tulemuse_summa
FROM m_outcome KUI SISEMINE LIITUMINE m_product AS b ON a.product_id=b.id
GROUP BY tarnija_id, toote_id) AS b
SEES (a.tarnija_id=b.tarnija_id) JA (a.toote_id=b.toote_id);

Taotle Q035. Kuvatakse tulude ja kulude aruanne toodete kaupa. Selleks koostatakse toodete loend vastavalt tabelitele m_income ja m_outcome, seejärel arvutatakse iga selle loendi toote kohta tema laekumiste summa m_tulu tabeli järgi ja kulude summa m_tulemus tabeli järgi:

SELECT toote_id, SUM(summas) AS tulu_summa,

SUM(välja_summa) AS tulemuse_summa
FROM
(VALI toote_id, summa AS summas, 0 AS välja_summa
M_sissetulekust
LIIT KÕIK
SELECT toote_id, 0 AS summas, summa AS välja_summa
FROM m_outcome) AS t
GROUP BY toote_id;

Taotlege Q036. Funktsioon EXISTS tagastab TRUE, kui sellele edastatud hulk sisaldab elemente. Funktsioon EXISTS tagastab FALSE, kui talle edastatud hulk on tühi, see tähendab, et sellel pole elemente. Järgmine päring tagastab tootekoodid, mis sisalduvad nii tabelis m_income kui ka tabelis m_outcome:

VALI ERINEV toote_id

FROM m_income AS a
WHERE EXISTS(SELECT toote_id FROM m_outcome AS b

Taotle Q037. Kuvatakse tootekoodid, mis sisalduvad nii tabelis m_income kui ka tabelis m_outcome:

VALI ERINEV toote_id

FROM m_income AS a
WHERE product_id IN (SELECT product_id FROM m_outcome)

Taotlege Q038. Kuvatakse tootekoodid, mis sisalduvad nagu tabelis m_income, kuid ei sisaldu tabelis m_outcome:

VALI ERINEV toote_id

FROM m_income AS a
WHERE NOT EXISTS(SELECT toote_id FROM m_outcome AS b
KUS b.toote_id=a.toote_id);

Taotle Q039. Kuvatakse kõige suurema müügisummaga toodete loend. Algoritm on selline. Iga toote kohta arvutatakse selle müügisumma. Seejärel määratakse nende summade maksimum. Seejärel arvutatakse iga toote jaoks uuesti selle müügisumma ning kuvatakse kood ja kauba müügisumma, mille müügisumma on võrdne maksimumiga:

VALI toote_id, SUM(summa*hind) AS summa_summa

FROM m_outcome
GROUP BY toote_id
HAVING SUM(summa*hind) = (VALI MAX(s_summa)
FROM (SELECT SUM(summa*hind) AS s_summa FROM m_tulemus GROUP BY product_id));

Küsi Q040. Reserveeritud sõna IIF (tingimuslik operaator) kasutatakse loogilise avaldise hindamiseks ja toimingu sooritamiseks sõltuvalt tulemusest (TRUE või FALSE). Järgmises näites loetakse kauba tarnimine "väikseks", kui kogus on väiksem kui 500. Vastasel juhul, st laekumise kogus on suurem või võrdne 500-ga, loetakse tarne "suureks":

SELECT dt, toote_id, summa,

IIF(summa M_tulu;

SQL päring Q041. Juhul, kui IIF-lauset kasutatakse rohkem kui üks kord, on mugavam asendada see SWITCH-lausega. Operaatorit SWITCH (mitme valikuga operaator) kasutatakse loogilise avaldise hindamiseks ja toimingu sooritamiseks sõltuvalt tulemusest. Järgmises näites loetakse tarnitud partii "väikeseks", kui kauba kogus partiis on alla 500. Vastasel juhul, st kui kauba kogus on suurem või võrdne 500, loetakse partii "suureks". ":

SELECT dt, toote_id, summa,

SWITCH(kogus =500,"suur") AS-märk
FROM m_income;

Taotlege Q042. Kui järgmises päringus on kauba kogus sissetulevas partiis alla 300, siis loetakse partii "väikeseks". Vastasel juhul, st kui tingimus summa SELECT dt, product_id, summa,
IIF(summa IIF(summa ALAST m_tulu;

SQL päring Q043. Kui järgmises päringus on kauba kogus sissetulevas partiis alla 300, siis loetakse partii "väikeseks". Vastasel juhul, st kui tingimus summa SELECT dt, product_id, summa,
SWITCH(summa summa summa>=1000,"suur") AS-märk
FROM m_income;

SQL päring Q044. Järgmises päringus jaguneb müük kolme rühma: väike (kuni 150), keskmine (150 kuni 300), suur (300 ja rohkem). Järgmisena arvutatakse iga rühma jaoks kogusumma:

SELECT kategooria, SUM(tulemuse_summa) AS Ctgry_Total

FROM (VALI summa*hind AS tulemuse_summa,
IIf(summa*hind IIf(summa*hind ALATES m_tulemus) AS t
GROUP BY Kategooria järgi;

SQL päring Q045. Funktsiooni DateAdd kasutatakse päevade, kuude või aastate lisamiseks antud kuupäevale ja uue kuupäeva hankimiseks. Järgmine taotlus:
1) lisab kuupäevale 30 päeva väljalt dt ja kuvab uue kuupäeva väljale dt_plus_30d;
2) lisage dt väljalt kuupäevale 1 kuu ja kuvage uus kuupäev väljale dt_plus_1m:

SELECT dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m

FROM m_income;

SQL päring Q046. Funktsioon DateDiff on loodud kahe kuupäeva erinevuse arvutamiseks erinevates ühikutes (päevad, kuud või aastad). Järgmine päring arvutab dt väljal oleva kuupäeva ja praeguse kuupäeva erinevuse päevades , kuudes ja aastates:

SELECT dt, DateDiff("d", dt, Kuupäev()) AS viimane_päev,

DateDiff("m",dt,Date()) AS viimased_kuud,
DateDiff("yyyy",dt,Date()) AS viimased_aastad
FROM m_income;

SQL päring Q047. Päevade arv alates kauba kättesaamise päevast (tabel m_income) kuni jooksva kuupäevani arvutatakse funktsiooni DateDiff abil ja võrreldakse aegumiskuupäeva (tabel m_product):

DateDiff("d",dt,Date()) AS viimased_päevad
FROM m_income KUI SISEMINE LIITUMINE m_product AS b
ON a.product_id=b.id;

SQL päring Q048. Arvestatakse päevade arv alates kauba kättesaamise kuupäevast kuni jooksva kuupäevani, seejärel kontrollitakse, kas see arv ületab aegumiskuupäeva:

SELECT a.id, product_id, dt, lifedays,

DateDiff("d",dt,Date()) AS viimased_päevad, IIf(viimased_päevad>elupäevad,"Jah","Ei") AS date_expire
FROM m_income a SISEMINE JOIN m_product b
ON a.product_id=b.id;

SQL päring Q049. Arvestatakse kuude arvu alates kauba kättesaamise kuupäevast kuni jooksva kuupäevani. Veerg month_last1 arvutab kuude absoluutarvu, veerg month_last2 arvutab täielike kuude arvu:

SELECT dt, DateDiff("m", dt, Kuupäev()) AS kuu_viimane1,

DateDiff("m",dt,Date())-iif(day(dt)>day(kuupäev()),1,0) AS kuu_viimane2
FROM m_income;

SQL päring Q050. Kuvatakse 2011. aasta kvartaalne aruanne saadud kauba koguse ja koguse kohta:

SELECT kvartal, SUM(tulemuse_summa) AS Kokku

FROM (SELECT summa*hind AS tulemus_summa, kuu(dt) AS m,
LÜLITI(m =10,4) AS kvartal
FROM m_sissetulek KUS aasta(dt)=2011) AS t
GROUP BY plokk;

Taotlege Q051. Järgmine päring aitab välja selgitada, kas kasutajatel õnnestus süsteemi sisestada teavet kaupade tarbimise kohta summas, mis on suurem kui kauba kättesaamise summa:

SELECT product_id, SUM(in_sum) AS tulu_summa, SUM(out_sum) AS result_sum

FROM (VALI toote_id, summa*hind kui in_sum, 0 kui out_sum
m_sissetulekust
LIIT KÕIK
VALI toote_id, 0 kui in_sum, summa*hind kui out_sum
alates m_tulemus) AS t
GROUP BY toote_id
HAVING SUM(in_sum)
Taotlege Q052. Päringuga tagastatud ridade nummerdamist rakendatakse erineval viisil. Näiteks saate MS Accessis koostatud aruande ridu ümber nummerdada, kasutades MS Accessi ennast. Samuti saate ümber nummerdada, kasutades programmeerimiskeeli, näiteks VBA või PHP. Kuid mõnikord tuleb seda teha SQL-päringus endas. Niisiis nummerdab järgmine päring tabeli m_income read vastavalt ID-välja väärtuste kasvavale järjestusele:

SELECT COUNT(*) kui N, b.id, b.product_id, b.amount, b.price

FROM m_income a SISEMINE LIITUMINE m_sissetulek b ON a.id GROUP BY BY b.id, b.product_id, b.summa, b.price;

Taotlege Q053. Välja on toodud müügimahu järgi toodete esiviisik. Esimese viie kirje väljund viiakse läbi TOP-juhise abil:

VALI TOP 5, toote_id, summa(summa*hind) AS summa

FROM m_outcome
GROUP BY toote_id
ORDER BY summa(summa*hind) DESC;

Taotlege Q054. Kuvatakse müügimahu järgi toodete seas esiviisik ja selle tulemusel nummerdatakse read:

SELECT COUNT(*) AS N, b.product_id, b.summa

FROM


FROM m_outcome GROUP BY product_id) AS a
SISEMINE LIITUMINE
(SELECT toote_id, summa(summa*hind) AS summa,
summa*10000000+toote_id AS id
FROM m_outcome GROUP BY product_id) AS b
ON a.id>=b.id
GROUP BY b.product_id, b.summa
HAVING COUNT(*)ORDER BY COUNT(*);

Taotlege Q055. Järgmine SQL-päring näitab matemaatiliste funktsioonide COS, SIN, TAN, SQRT, ^ ja ABS kasutamist MS Access SQL-is:

SELECT (valige arv(*) m_sissetulekust) kui N, 3,1415926 kui pi, k,

2*pi*(k-1)/N kui x, COS(x) kui COS_, SIN(x) kui SIN_, TAN(x) kui TAN_,
SQR(x) kui SQRT_, x^3 kui "x^3", ABS(x) kui ABS_
FROM (VALI COUNT(*) AS k
FROM m_sissetulek KUI SISEMINE JOIN m_sissetulek AS b ON a.idGROUP BY BY b.id) t;

SQL päring. Näited MS Accessis. VÄRSKENDUS: 1.–10

Taotlege U001. Järgmine SQL-i muudatuspäring tõstab tabelis m_income koodiga 3 kaupade hindu 10% võrra:

UPDATE m_income SET hind = hind*1.1

WHERE toote_id=3;

Taotlege U002. Järgmine SQL-i värskenduspäring suurendab kõigi tabelis m_income olevate toodete kogust 22 ühiku võrra, mille nimed algavad sõnaga "või":

VÄRSKENDA m_tulu SET summa = summa+22

WHERE toote_id IN (SELECT id FROM m_product WHERE pealkiri LIKE "Õli*");

Taotlege U003. Järgmine SQL-i muudatuste päring tabelis m_outcome vähendab kõigi OOO Sladkoe toodetud kaupade hindu 2 protsenti:

UPDATE m_outcome SET hind = hind*0,98

KUS toote_id IN
(VALIGE a.id FROM M_product a INNER JOIN m_supplier b
ON a.supplier_id=b.id WHERE b.title="OOO"Сладкое"");. !}

Labor nr 1

SQL: RETRIEVE DATA - käskVALI

Töö eesmärk:

• Tutvuge SQL-lausetega
• õppida, kuidas luua lihtsaid SQL-päringuid Accessis käsu SELECT abil;

· operaatorite IN, BETWEEN, LIKE, kasutamine ON NULL.

Harjutus№1. Looge päring, et valida SQL-režiimis tabelist ÕPILASED väljade EESnimi ja PERENIMI kõik väärtused.

VALI EESNIMI, PERENIMI

ÕPILASTELT;

Harjutus№2 . Looge SQL-režiimis valikupäring tabeli ÕPILASED kõigi veergude jaoks.

VALI*

ÕPILASTELT;

Ülesanne number 3. Loo päring, et valida SQL-režiimis õpilaste elukoha linnade nimed, mille kohta on info tabelis ISIKUANDMED.

VALI ERILINE LINN

FROM [ISIKUKAANDMED];

Ülesanne number 4. Loo SQL-režiimis valikupäring, mis valib kõigi perekonnanimega Ivanov õpilaste nimed, kelle andmed on tabelis ÕPILASED.

VALI PEREKONNANIMI, NIMI

ÕPILASTELT

WHERE SURNAME="Ivanov";

Ülesanne number 5. Looge SQL-režiimis valiku päring, et saada UIT-22 rühmas eelarvest rahastatava õppevormi raames õppivate õpilaste nimed ja perekonnanimed.

VALI PEREKONNANIMI, NIMI

ÕPILASTELT

KUS GROUP="HIT-22" AND BUDGET=true;

Ülesanne number 6. Looge päring SQL-režiimis. näidise jaoks tabelist EKSAMI SÖÖTAMINE teave õpilaste kohta, kelle hinded on ainult 4 ja 5.

VALI*

FROM[MUUTUSEKSAMID]

KUSHINNEIN (4,5);

Ülesanne number 7. Looge zanpoc- ja SQL-režiim teabe näidise jaoks õpilaste kohta, kellel on IOSU aine eksamihinne 3.

VALI*

FROM[MUUTUSEKSAMID]

KUSITEM=" ISSU"JaHINNENot In (4,5);

Ülesanne number 8. Looge SQL-režiimis päring, et valida kirjed üksuste jaoks, mille tundide arv on vahemikus 100 kuni 130.

VALI*

FROMASJAD

KUSVAATA100–130;

Ülesanne number 9. Looge SQL-režiimis päring, et valida tabelist ÕPILASED teavet õpilaste kohta, kelle perekonnanimed algavad näiteks tähega "C".

VALI*

FROMÕPILASED

KUSPEREKONNANIMILIKE"KOOS*";

Järeldus: Laboritöö käigus tutvusime SQL-lausetega, õppisime tegema Accessis lihtsaid SQL päringuid käsu SELECT abil operaatorite IN, BETWEEN, LIKE abil.

SQL-päringute näiteid saab kasutada SQL-päringute kirjutamise õppimiseks ja harjutamiseks MS Accessis.

Ühe SQL-päringu saab pesastada teise sisse. Alampäring pole midagi muud kui päring päringu sees. Tavaliselt kasutatakse alampäringut WHERE-klauslis. Kuid on ka teisi võimalusi alampäringute kasutamiseks.

Taotle Q011. Kuvab infot toodete kohta tabelist m_product, mille koodid on ka tabelis m_income:

VALI*
M_produktist
WHERE id IN (SELECT toote_id FROM m_tulu);

Taotle Q012. Kuvatakse toodete loend tabelist m_product, mille koode tabelis m_outcome pole:

VALI*
M_produktist
WHERE id NOT IN (SELECT product_id FROM m_outcome);

Taotle Q013. See SQL-päring tagastab kordumatu loendi koodidest ja tootenimedest, mille koodid on tabelis m_income, kuid mitte tabelis m_outcome:

VALI ERINEV toote_id, pealkiri
FROM m_income SISEMINE JOIN m_product
ON m_income.product_id=m_product.id
KUS toote_id EI OLE IN (SELECT toote_id FROM m_tulemus);

Taotle Q014. Tabelist m_category kuvatakse unikaalne kategooriate loend, mille nimed algavad tähega M:

VALI ERINEV pealkiri
M_produktist
KUS pealkiri LIKE "M*";

Taotle Q015. Näide päringu väljadel aritmeetiliste toimingute tegemisest ja päringu väljade ümbernimetamisest (alias). Selles näites arvutatakse iga kaubatarbimiskirje kulu = kogus*hind ja kasum, eeldades, et kasum moodustab 7 protsenti müügist:

summa*hind/100*7 AS kasum
FROM m_outcome;

Taotlus Q016. Aritmeetilisi toiminguid analüüsides ja lihtsustades saate päringu täitmise kiirust suurendada:

SELECT dt, toote_id, summa, hind, summa*hind AS tulemuse_summa,
tulemus_summa*0,07 AS-i kasum
FROM m_outcome;

Taotle Q017. Kasutades lauset INNER JOIN, saate kombineerida mitme tabeli andmeid. Järgmises näites, olenevalt ctgry_id väärtusest, vastendatakse iga kirje m_sissetulek tabelis selle kategooria nimega tabelist m_category, kuhu toode kuulub:

SELECT c.title, b.title, dt, summa, hind, summa*price AS tulu_summa
FROM (m_tulu KUI SISEMINE LIITUMINE m_toode AS b ON a.product_id=b.id)
SISEMINE JOIN m_category AS c ON b.ctgry_id=c.id
ORDER c.title, b.title;

Taotle Q018. Selliseid funktsioone nagu SUM – summa, COUNT – kogus, AVG – aritmeetiline keskmine, MAX – maksimaalne väärtus, MIN – minimaalne väärtus nimetatakse koondfunktsioonideks. Need võtavad mitu väärtust ja tagastavad töötlemisel ühe väärtuse. Näide väljade summa ja hinna korrutise summa arvutamisest koondfunktsiooni SUM abil.