Access DBMS folosește două tipuri de interogări: QBE – interogare după eșantion și SQL(Structured Query Language) - limbaj structurat de interogare. Un exemplu de solicitare este generat prin completarea unui formular special de solicitare în fereastra „Query Builder”. SQL – interogările sunt create de programatori dintr-o secvență de SQL – instrucțiuni. SQL este generat, de regulă, de către programatori pe un formular de interogare, care este deschis de comanda „Query Builder” din fila „Creation” și „SQL Mode” este selectat din meniul View. Limbajul SQL este conceput pentru a lucra cu date, de exemplu. pentru crearea, modificarea și gestionarea datelor în baze de date relaționale.
Trebuie remarcat faptul că există mai multe moduri de interogare SQL (interogări în mod ANSI-89 SQL și ANSI-92 SQL) care respectă standardele ANSI-89 SQL și ANSI-92 SQL.
Instrucțiunile conțin o descriere a setului de date în SQL. Instrucțiuni SQL constau din clauze (SELECT, FROM, WHERE etc.). promoțiiîn limbajul SQL constau din termeni (operatori sau comenzi, identificatori, constante etc.). Instrucțiunea începe cu o instrucțiune (una dintre comenzile SELECT, CREATE, INSERT, UPDATE, DELETE etc.) și se termină cu punct și virgulă. Instrucțiuni SQL de bază: SELECT, FROM și WHERE.
De exemplu, instrucțiunea SQL:
SELECT Students.StudentCode
DE LA Studenți;
constă din clauza „SELECT Students.StudentCode” și clauza „FROM Students”.
Clauza SELECT conține operator SELECT și identificator„Elevi.Codul elevului”. Aici numele complet al câmpului „StudentCode” este precedat de numele tabelului „Students” al bazei de date. SELECT - specifică câmpul care conține datele necesare. Clauza FROM constă dintr-o clauză FROM și identificatorul „Students”. FROM - definește un tabel care conține câmpurile specificate în clauza SELECT.
Trebuie remarcat faptul că atunci când creați o interogare în SQL, trebuie să luați în considerare sintaxa acesteia. În ciuda faptului că sintaxa limbajului SQL se bazează pe sintaxa limbii engleze, sintaxa versiunilor limbajului SQL poate diferi pentru diferite SGBD.
Există mai multe tipuri de interogări: selectarea, actualizarea, adăugarea și ștergerea înregistrărilor, interogare încrucișată, crearea și ștergerea tabelelor, alăturarea tabelelor etc. Cea mai comună este o cerere de eșantion. Interogările de selecție sunt folosite pentru a selecta informațiile conținute în tabelele de care utilizatorul are nevoie. Sunt create numai pentru tabelele înrudite.
Pentru a vizualiza interogări de interogare SQL în DBMS Access 2003 sau 2007, trebuie să executați comanda SQL View/Mode în fereastra activă pentru proiectarea unui exemplu de interogare (Fig. 1).
Orez. 1.
Să obținem o instrucțiune SQL (SELECT) pentru a eșantiona date din baza de date Access 2003 în conformitate cu criteriul de performanță al elevilor „Grade=5” (Fig. 2).
Orez. 2.
După cum urmează din instrucțiunea SELECT (Fig. 1), aceasta descrie un set de date în limbaj SQL: SELECT - definește numele câmpurilor precedate de numele tabelelor care conțin datele; FROM - definește tabele și relațiile acestora prin câmpurile cheie ale tabelelor (pentru aceasta se folosește construcția INNER JOIN ... ON), pe baza cărora se selectează datele; WHREME - definește condițiile de selectare a câmpurilor; ORDER BY - determină modul în care se sortează în ordine crescătoare (în mod implicit se realizează ordine crescătoare) valorile câmpului „Nume” din tabelul „Studenți”.
După cum rezultă din instrucțiunile pentru preluarea datelor din baza de date, limbajul SQL descrie ceea ce trebuie obținut din baza de date, în timp ce execuția este încredințată SGBD, deoarece limbajul SQL nu are mijloace proprii de control al execuției programului.
Articolele anterioare au discutat problemele. Se are în vedere tehnologia de creare a structurii tabelelor bazei de date „sql_training_st.mdb” pe baza interogărilor SQL. În plus, folosind interogări SQL, au fost completate tabelele ACCESS DBMS „sql_training_st.mdb”.
Se știe că în bazele de date relaționale, limbajul SQL este conceput pentru a manipula datele, a defini structura bazei de date și a componentelor acesteia, pentru a controla accesul utilizatorilor la baza de date și pentru a gestiona tranzacțiile sau a gestiona modificările din baza de date.
Limbajul SQL este format din patru grupuri:
- limbaj de manipulare a datelor DML;
- Limbajul de definire a datelor DDL;
- limbaj de management al datelor DCL;
- Limbajul de control al tranzacțiilor TCL.
Grupul DML include patru tipuri principale de interogări SQL:
- INSERT - conceput pentru a adăuga una sau mai multe înregistrări la sfârșitul tabelului;
- UPDATE - conceput pentru a modifica înregistrările existente în coloanele tabelului sau pentru a modifica datele din tabel;
- DELETE - conceput pentru a șterge înregistrările din tabel;
- SELECT - conceput pentru a selecta date din tabele.
Primele trei tipuri de interogări SQL (INSERT, UPDATE, DELETE), care se referă la interogări corective la baza de date, au fost discutate în pagină.
În acest articol ne vom uita la interogări pentru preluarea datelor din tabelele bazei de date Access.
Pentru a prelua informațiile stocate într-o bază de date Access 2003 sau 2007, puteți utiliza o interogare SELECT pentru a selecta date din tabele.
Să compunem următoarea interogare SQL (instrucțiune SQL) pentru eșantionare, selectați modul SQL executând comanda View/SQL Mode. Introduceți următoarea instrucțiune SQL de la tastatură:
SELECTAȚI *
DE LA Studenți;
Această declarație constă din două clauze „SELECT *” și „FROM Students”. Prima clauză conține o instrucțiune SELECT și un identificator * ("identificator *" înseamnă listarea tuturor coloanelor tabelului). A doua clauză conține instrucțiunea FROM și identificatorul „Students”.
FROM - Definește tabelul Students, care conține câmpurile specificate în clauza SELECT. Trebuie remarcat faptul că o interogare de selecție conține întotdeauna două instrucțiuni: SELECT și FROM. În funcție de condițiile de selecție, în cererea de selecție pot fi prezenți și alți operatori. Figura 1 prezintă o captură de ecran a unei cereri de preluare a datelor.
Orez. 1. Interogare SQL SELECT pentru a selecta datele
În acest exemplu, un eșantion de date este generat din toate coloanele din tabelul Studenți.
Salvați cererea cu numele „Students-query1”. Ca urmare a executării comenzii „Salvare”, în „Zona de navigare” va apărea obiectul „Solicite: Students-request1”.
După salvarea cererii de selecție, trebuie să executați această solicitare făcând clic pe pictograma „Run”. Rezultatele comenzii „Run” sunt prezentate în Fig. 2.
Orez. 2. Selectarea datelor din toate coloanele tabelului Studenți
Lucrare de laborator nr 1
SQL: DATA EXTRACT - comandaSELECTAȚI
Scopul lucrării:
- familiarizați-vă cu instrucțiunile SQL;
- învață cum să creezi interogări SQL simple în Access folosind comanda SELECT;
· utilizarea operatorilor IN, BETWEEN, LIKE, IS NULL.
Exercițiu№1. Creați o interogare pentru a selecta în modul SQL toate valorile câmpurilor PRENUME și NUME din tabelul STUDENTI.
SELECTAȚI PRENUMELE, NUMELE
DE LA STUDENTI;
Exercițiu№2 . Creați o interogare pentru a selecta în modul SQL toate coloanele din tabelul STUDENTI.
SELECTAȚI *
DE LA STUDENTI;
Sarcina nr. 3. Creați o interogare pentru a selecta în modul SQL numele orașelor în care locuiesc elevii, informații despre care se află în tabelul DATE PERSONALE.
SELECTAȚI ORAȘUL DISTINCT
DIN [DATE PERSONALE];
Sarcina nr. 4. Creați o interogare de selecție în modul SQL care preia numele tuturor elevilor cu numele de familie Ivanov, informații despre care se află în tabelul STUDENTI.
SELECTAȚI NUMELE, PRENUMELE
DE LA STUDENTI
UNDE NUMELE="Ivanov";
Sarcina nr. 5. Creați o interogare de selecție în modul SQL pentru a obține numele și prenumele studenților care studiază în grupa UIT-22 pe o formă de educație bugetară.
SELECTAȚI NUMELE, PRENUMELE
DE LA STUDENTI
WHERE GROUP="UIT-22" AND BUDGET=true;
Sarcina nr. 6. Creați o interogare în modul SQL. pentru o mostră din tabelul de EXAMEN, informații despre elevii care au doar note 4 și 5.
SELECTAȚI *
DE LA [SCHIMBAREEXAMENE]
UNDENOTAIN(4,5);
Sarcina nr. 7. Creați un mod zanpoc și SQL pentru a selecta informații despre studenții care au nota 3 la examen la materia IOSU.
SELECTAȚI *
DE LA [SCHIMBAREEXAMENE]
UNDEARTICOL=" IOSU"ȘiNOTANu în (4,5);
Sarcina nr. 8. Creați o interogare în modul SQL pentru a selecta înregistrări pentru articolele ale căror ore sunt între 100 și 130.
SELECTAȚI *
DINARTICOLE
UNDECEASINTRE 100 SI 130;
Sarcina nr. 9. Creați o interogare în modul SQL pentru a selecta din tabelul STUDENTI informații despre elevii ale căror nume de familie încep, de exemplu, cu litera „C”.
SELECTAȚI *
DINELEVI
UNDENUME DE FAMILIECA"CU*";
Concluzie:În timpul lucrărilor de laborator, ne-am familiarizat cu instrucțiunile SQL, am învățat cum să creăm interogări SQL simple în Access folosind comanda SELECT folosind operatorii IN, BETWEEN, LIKE.
Descrierea proiectului educațional „Magazin”Diagrama legăturii tabelului
Descrierea tabelelor
m_category - categorii de produse
m_venit - primire marfa
m_outcome - consumul de bunuri
m_product - director, descrieri de produse
m_furnizor - director; informatii despre furnizor
m_unit - director; unitati
Pentru a testa practic exemplele date în acest material de instruire, trebuie să aveți următorul software:
Microsoft Access 2003 sau o versiune ulterioară.
Interogare SQL în MS Access. start
Pentru a vedea conținutul tabelului, faceți dublu clic pe numele tabelului din panoul din stânga:
Pentru a comuta la modul de editare a câmpurilor de tabel, selectați modul Design din panoul superior:
Pentru a afișa rezultatul unei interogări SQL, faceți dublu clic pe numele interogării din panoul din stânga:
Pentru a comuta la modul de editare a interogărilor SQL, selectați modul SQL din panoul de sus:
Interogare SQL. Exemple în MS Access. SELECTARE: 1-10
Într-o interogare SQL, instrucțiunea SELECT este utilizată pentru a selecta din tabelele bazei de date.Interogare SQL Q001. Exemplu de interogare SQL pentru a obține numai câmpurile necesare în secvența dorită:
SELECTAȚI dt, product_id, amount
DIN m_venit;
Interogare SQL Q002.În acest exemplu de interogare SQL, caracterul asterisc (*) este folosit pentru a lista toate coloanele din tabelul m_product, cu alte cuvinte, pentru a obține toate câmpurile relației m_product:
SELECTAȚI *
FROM m_product;
CerereSQL Q003. Declarația DISTINCT este folosită pentru a elimina intrările duplicate și pentru a obține mai multe intrări unice:
SELECTAȚI DISTINCT ID_produs
DIN m_venit;
Interogare SQL Q004. Instrucțiunea ORDER BY este folosită pentru a sorta (ordona) înregistrările după valorile unui anumit câmp. Numele câmpului este specificat după instrucțiunea ORDER BY:
SELECTAȚI *
DIN m_venit
COMANDA DUPA pret;
Interogare SQL Q005. Instrucțiunea ASC este folosită ca o completare a instrucțiunii ORDER BY și servește la specificarea sortării crescătoare. Instrucțiunea DESC este utilizată în plus față de instrucțiunea ORDER BY și este folosită pentru a specifica sortarea descendentă. În cazul în care nu sunt specificate nici ASC, nici DESC, se presupune prezența ASC (implicit):
SELECTAȚI *
DIN m_venit
COMANDA PRIN dt DESC , pret;
Interogare SQL Q006. Pentru a selecta înregistrările necesare din tabel, se folosesc diverse expresii logice care exprimă condiția de selecție. Expresia booleană apare după instrucțiunea WHERE. Un exemplu de obținere a tuturor înregistrărilor din tabelul m_income pentru care valoarea sumei este mai mare de 200:
SELECTAȚI *
DIN m_venit
UNDE suma>200;
Interogare SQL Q007. Pentru a exprima condiții complexe se folosesc operatorii logici AND (conjuncție), OR (disjuncție) și NOT (negație logică). Un exemplu de obținere din tabelul m_outcome a tuturor înregistrărilor pentru care valoarea sumei este 20 și valoarea prețului este mai mare sau egală cu 10:
Preț
DIN m_rezultat
WHERE suma=20 ȘI preț>=10;
Interogare SQL Q008. Pentru a uni date din două sau mai multe tabele, utilizați instrucțiunile INNER JOIN, LEFT JOIN, RIGHT JOIN. Următorul exemplu preia câmpurile dt, product_id, amount, price din tabelul m_income și câmpul titlu din tabelul m_product. Înregistrarea tabelului m_income este asociată cu înregistrarea tabelului m_product atunci când valoarea m_income.product_id este egală cu valoarea m_product.id:
ON m_income.product_id=m_product.id;
Interogare SQL Q009. Există două lucruri de remarcat în această interogare SQL: 1) textul de căutare este inclus între ghilimele simple ("); 2) data este în formatul #Lună/Ziu/An#, ceea ce este valabil pentru MS Access. sisteme, formatul de scriere a datei poate fi diferit Un exemplu de afișare a informațiilor despre primirea laptelui pe 12 iunie 2011. Vă rugăm să rețineți formatul de dată #6/12/2011#:
SELECT dt, product_id, title, amount, price
FROM m_income INNER JOIN m_product
WHERE title="Lapte" And dt=#6/12/2011#; !}
Interogare SQL Q010. Instrucțiunea BETWEEN este utilizată pentru a testa dacă o valoare aparține unui anumit interval. Un exemplu de interogare SQL care afișează informații despre produsele primite între 1 iunie și 30 iunie 2011:
SELECTAȚI *
FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
UNDE dt ÎNTRE #6/1/2011# Și #6/30/2011#;
Interogare SQL. Exemple în MS Access. SELECT: 11-20
O interogare SQL poate fi imbricată în alta. O subinterogare nu este altceva decât o interogare în cadrul unei interogări. De obicei, o subinterogare este utilizată în clauza WHERE. Dar există și alte moduri de a folosi subinterogări.Interogarea Q011. Sunt afișate informații despre produsele din tabelul m_product, ale căror coduri se află și în tabelul m_income:
SELECTAȚI *
DE LA m_produs
WHERE id IN (SELECTARE ID_produs FROM m_venit);
Solicitați Q012. Este afișată o listă de produse din tabelul m_product, ale căror coduri nu sunt în tabelul m_outcome:
SELECTAȚI *
DE LA m_produs
WHERE id NOT IN (SELECT Product_id FROM m_outcome);
Solicitați Q013. Această interogare SQL afișează o listă unică de coduri și nume de produse care se află în tabelul m_income, dar nu în tabelul m_outcome:
SELECTAȚI DISTINCT produs_id, titlu
FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
WHERE product_id NOT IN (SELECTARE product_id FROM m_outcome);
Interogarea Q014. O listă unică de categorii ale căror nume încep cu litera M este afișată din tabelul m_category:
SELECTAȚI un titlu DISTINCT
DE LA m_produs
UNDE titlul LIKE „M*”;
Interogarea Q015. Un exemplu de efectuare a operațiunilor aritmetice asupra câmpurilor dintr-o interogare și redenumirea câmpurilor dintr-o interogare (alias). Acest exemplu calculează cheltuială = cantitate*preț și profit pentru fiecare intrare de cheltuială de articol, presupunând că profitul este de 7% din vânzări:
suma*pret/100*7 AS profit
FROM m_rezultat;
Interogarea Q016. Prin analiza și simplificarea operațiilor aritmetice, puteți crește viteza de execuție a interogărilor:
SELECT dt, product_id, amount, price, amount*price AS outcome_sum,
sumă_rezultat*0,07 AS profit
FROM m_rezultat;
Interogarea Q017. Puteți utiliza instrucțiunea INNER JOIN pentru a uni date din mai multe tabele. În exemplul următor, în funcție de valoarea ctgry_id, fiecare intrare din tabelul m_income este asociată cu numele categoriei din tabelul m_category căreia îi aparține produsul:
SELECTAȚI c.titlu, b.titlu, dt, sumă, preț, sumă*preț AS income_sum
FROM (m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id)
INNER JOIN m_category AS c ON b.ctgry_id=c.id
ORDENAREA PENTRU c.titlu, b.titlu;
Solicitați Q018. Funcții precum SUM - sumă, COUNT - cantitate, AVG - medie aritmetică, MAX - valoare maximă, MIN - valoare minimă sunt numite funcții agregate. Acceptă multe valori și după procesare returnează o singură valoare. Un exemplu de calcul al sumei produsului dintre câmpurile sumă și preț folosind funcția de agregare SUM:
SELECT SUM(suma*preț) AS Total_Sum
DIN m_venit;
Interogarea Q019. Un exemplu de utilizare a mai multor funcții agregate:
SELECT Sum(suma) AS Amount_Sum, AVG(amount) AS Amount_AVG,
MAX(amount) AS Amount_Max, Min(amount) AS Amount_Min,
Count(*) AS Total_Number
DIN m_venit;
Solicitați Q020.În acest exemplu, se calculează valoarea tuturor bunurilor cu codul 1, capitalizate în iunie 2011:
SELECT Sum(suma*preț) AS income_sum
DIN m_venit
WHERE product_id=1 ȘI dt ÎNTRE #6/1/2011# ȘI #6/30/2011#;.
Interogarea Q021. Următoarea interogare SQL calculează valoarea vânzărilor de articole cu codul 4 sau 6:
SELECTAȚI Suma(amount*price) ca rezultat_sum
DIN m_rezultat
WHERE product_id=4 SAU product_id=6;
Interogarea Q022. Se calculează câte bunuri cu codul 4 sau 6 au fost vândute la 12 iunie 2011:
SELECT Sum(suma*preț) AS rezultat_sum
DIN m_rezultat
WHERE (product_id=4 SAU product_id=6) ȘI dt=#6/12/2011#;
Interogarea Q023. Sarcina este aceasta. Calculați cantitatea totală de mărfuri din categoria „Produse de panificație” care au fost valorificate.
Pentru a rezolva această problemă, trebuie să operați cu trei tabele: m_income, m_product și m_category, deoarece:
- cantitatea si pretul bunurilor capitalizate sunt stocate in tabelul m_income;
- codul categoriei fiecărui produs este stocat în tabelul m_product;
- numele categoriei de titlu este stocat în tabelul m_category.
Pentru a rezolva această problemă vom folosi următorul algoritm:
- determinarea codului categoriei „Produse de panificatie” din tabelul m_category folosind o subinterogare;
- conectarea tabelelor m_income și m_product pentru a determina categoria fiecărui produs achiziționat;
- calculul sumei de primire (= cantitate*pret) pentru marfa al caror cod de categorie este egal cu codul definit de subinterogarea de mai sus.
SELECTAȚI
FROM m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id
WHERE ctgry_id = (SELECT ID FROM m_category WHERE title="Produse de panificație"); !}
Interogarea Q024. Vom rezolva problema calculării cantității totale de bunuri valorificate din categoria „Produse de panificație” folosind următorul algoritm:
- pentru fiecare intrare din tabelul m_income, în funcție de valoarea product_id-ului acestuia, din tabelul m_category, potriviți numele categoriei;
- selectați înregistrări pentru care categoria este „Produse de panificație”;
- se calculeaza suma chitantei = cantitate * pret.
FROM (m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id)
WHERE c.title="Produse de panificație";
!}
Interogarea Q025. Acest exemplu calculează câte bunuri au fost consumate:
SELECTAȚI COUNT(product_id) AS product_cnt
FROM (SELECT DISTINCT product_id FROM m_outcome) AS t;
Interogarea Q026. Instrucțiunea GROUP BY este utilizată pentru a grupa înregistrările. În mod obișnuit, înregistrările sunt grupate după valoarea unuia sau mai multor câmpuri și se aplică o operațiune agregată fiecărui grup. De exemplu, următoarea interogare generează un raport privind vânzarea mărfurilor. Adică, se generează un tabel care conține numele bunurilor și suma pentru care au fost vândute:
SELECTează titlul, SUM(suma*preț) AS rezultatul_sumă
FROM m_product CA o INNER JOIN m_outcome AS b
ON a.id=b.product_id
GROUP BY titlu;
Cerere Q027. Raport de vânzări pe categorii. Adică, este generat un tabel care conține numele categoriilor de produse, suma totală pentru care au fost vândute produsele din aceste categorii și valoarea medie a vânzărilor. Funcția ROUND este utilizată pentru a rotunji valoarea medie la cea mai apropiată sutime (a doua cifră după separatorul zecimal):
SELECTAȚI titlul c., SUM(amount*price) AS rezultat_sum,
ROUND(AVG(cantitate*preț),2) AS outcome_sum_avg
FROM (m_product AS a INNER JOIN m_outcome AS b ON a.id=b.product_id)
INNER JOIN m_category AS c ON a.ctgry_id=c.id
GROUP BY c.titlu;
Interogarea Q028. Numărul total și mediu al încasărilor sale este calculat pentru fiecare produs și afișează informații despre produsele ale căror încasări totale este de cel puțin 500:
SELECT produs_id, SUM(amount) AS amount_sum,
Rotunjite(Avg(amount),2) AS amount_avg
DIN m_venit
GROUP BY product_id
AVÂND Sumă(suma)>=500;
Interogarea Q029. Această interogare calculează pentru fiecare produs suma și media încasărilor efectuate în al doilea trimestru al anului 2011. Dacă suma totală a chitanței produsului este de cel puțin 1000, atunci sunt afișate informații despre acest produs:
SELECTează titlul, SUM(suma*preț) AS income_sum
FROM m_income a INNER JOIN m_product b ON a.product_id=b.id
UNDE dt INTRE #4/1/2011# SI #6/30/2011#
GROUP BY titlu
AVÂND SUMA(suma*preț)>=1000;
Interogarea Q030.În unele cazuri, trebuie să potriviți fiecare înregistrare a unui tabel cu fiecare înregistrare a altui tabel; care se numeşte produsul cartezian. Tabelul rezultat dintr-o astfel de conexiune se numeste masa lui Descartes. De exemplu, dacă un tabel A are 100 de înregistrări și tabelul B are 15 înregistrări, atunci tabelul lor Descartes va fi format din 100*15=150 de înregistrări. Următoarea interogare unește fiecare înregistrare din tabelul m_income cu fiecare înregistrare din tabelul m_outcome:
DIN m_venit, m_rezultat;
Interogarea Q031. Un exemplu de grupare a înregistrărilor după două câmpuri. Următoarea interogare SQL calculează pentru fiecare furnizor cantitatea și cantitatea de mărfuri primite de la acesta:
SUM(suma*preț) AS income_sum
Interogarea Q032. Un exemplu de grupare a înregistrărilor după două câmpuri. Următoarea interogare calculează pentru fiecare furnizor cantitatea și cantitatea produselor sale vândute de noi:
SELECT furnizor_id, product_id, SUM(amount) AS amount_sum,
GROUP BY furnizor_id, produs_id;
Interogarea Q033.În acest exemplu, cele două interogări de mai sus (q031 și q032) sunt utilizate ca subinterogări. Rezultatele acestor interogări folosind metoda LEFT JOIN sunt combinate într-un singur raport. Următoarea interogare afișează un raport privind cantitatea și cantitatea de produse primite și vândute pentru fiecare furnizor. Vă rugăm să rețineți că, dacă un produs a fost deja primit, dar nu a fost încă vândut, atunci celula rezultat_sum pentru această intrare va fi goală. că această interogare este doar un exemplu de utilizare a interogărilor relativ complexe ca subinterogare. Performanța acestei interogări SQL cu o cantitate mare de date este discutabilă:
SELECTAȚI *
DIN
SUM(suma*preț) AS income_sum
ON a.product_id=b.id GROUP BY furnizor_id, product_id) AS a
LEFT JOIN
(SELECTAȚI ID-ul furnizorului, ID-ul produsului, SUM(amount) AS amount_sum,
SUM(sumă*preț) AS rezultat_sumă
FROM m_outcome CA un INNER JOIN m_product AS b
ON a.product_id=b.id GROUP BY furnizor_id, product_id) AS b
ON (a.product_id=b.product_id) ȘI (a.supplier_id=b.supplier_id);
Interogarea Q034.În acest exemplu, cele două interogări de mai sus (q031 și q032) sunt utilizate ca subinterogări. Rezultatele acestor interogări folosind metoda RIGTH JOIN sunt combinate într-un singur raport. Următoarea interogare afișează un raport privind valoarea plăților fiecărui client în funcție de sistemele de plată pe care le-a folosit și de valoarea investițiilor pe care le-a făcut. Următoarea interogare afișează un raport privind cantitatea și cantitatea de produse primite și vândute pentru fiecare furnizor. Vă rugăm să rețineți că, dacă un produs a fost deja vândut, dar nu a sosit încă, atunci celula income_sum pentru această intrare va fi goală. Prezența unor astfel de celule goale este un indicator al unei erori în contabilitatea vânzărilor, deoarece înainte de vânzare este mai întâi necesar ca produsul corespunzător să sosească:
SELECTAȚI *
DIN
(SELECTAȚI ID-ul furnizorului, ID-ul produsului, SUM(amount) AS amount_sum,
SUM(suma*preț) AS income_sum
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY furnizor_id, produs_id) AS a
ÎNSCRIEȚI DREPT
(SELECTAȚI ID-ul furnizorului, ID-ul produsului, SUM(amount) AS amount_sum,
SUM(sumă*preț) AS rezultat_sumă
FROM m_outcome AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY furnizor_id, produs_id) AS b
ON (a.supplier_id=b.supplier_id) ȘI (a.product_id=b.product_id);
Interogarea Q035. Este afișat un raport care arată valoarea veniturilor și cheltuielilor pe produs. Pentru a face acest lucru, se creează o listă de produse conform tabelelor m_income și m_outcome, apoi pentru fiecare produs din această listă se calculează suma veniturilor sale conform tabelului m_income și suma cheltuielilor sale conform tabelului m_outcome:
SELECT produs_id, SUM(in_amount) AS income_amount,
SUM(out_amount) AS outcome_amount
DIN
(SELECTARE ID_produs, suma AS in_amount, 0 AS Out_amount
DIN m_venit
UNIREA TOȚI
SELECT produs_id, 0 AS in_amount, suma AS out_amount
DIN m_rezultat) AS t
GROUP BY product_id;
Interogarea Q036. Funcția EXISTS returnează TRUE dacă setul transmis conține elemente. Funcția EXISTS returnează FALSE dacă setul care i-a fost transmis este gol, adică nu conține niciun element. Următoarea interogare afișează codurile de produs care sunt conținute atât în tabelele m_income, cât și în tabelele m_outcome:
SELECTAȚI DISTINCT ID_produs
FROM m_income AS a
WHERE EXISTS(SELECT product_id FROM m_outcome AS b
Interogarea Q037. Sunt afișate codurile de produs care sunt conținute atât în tabelele m_income, cât și în tabelul m_outcome:
SELECTAȚI DISTINCT ID_produs
FROM m_income AS a
WHERE product_id IN (SELECTARE product_id FROM m_outcome)
Interogarea Q038. Sunt afișate codurile de produs care sunt conținute în tabelul m_income, dar nu sunt incluse în tabelul m_outcome:
SELECTAȚI DISTINCT ID_produs
FROM m_income AS a
WHERE NU EXISTIS(SELECT product_id FROM m_outcome AS b
UNDE b.product_id=a.product_id);
Interogarea Q039. Este afișată o listă de produse cu valoarea maximă a vânzărilor. Algoritmul este după cum urmează. Pentru fiecare produs se calculează valoarea vânzărilor sale. Apoi, se determină maximul acestor sume. Apoi, pentru fiecare produs, se calculează din nou suma vânzărilor sale și se afișează codul și suma vânzărilor de mărfuri a căror sumă a vânzărilor este egală cu maximul:
SELECTează cod_produs, SUM(cantitate*preț) AS suma_sumă
DIN m_rezultat
GROUP BY product_id
AVÂND SUM(sumă*preț) = (SELECT MAX(s_amount)
FROM (SELECT SUM(suma*preț) AS s_amount FROM m_rezultat GROUP BY product_id));
Interogarea Q040. Cuvântul rezervat IIF (operator condiționat) este folosit pentru a evalua o expresie logică și pentru a efectua o acțiune în funcție de rezultat (adevărat sau fals). În exemplul următor, livrarea articolului este considerată „mică” dacă cantitatea este mai mică de 500. În caz contrar, adică cantitatea de primire este mai mare sau egală cu 500, livrarea este considerată „mare”:
SELECT dt, product_id, amount,
IIF(suma DIN m_venit;
Interogare SQL Q041.În cazul în care operatorul IIF este folosit de mai multe ori, este mai convenabil să îl înlocuiți cu operatorul SWITCH. Operatorul SWITCH (operator de selecție multiplă) este folosit pentru a evalua o expresie logică și a efectua o acțiune în funcție de rezultat. În exemplul următor, lotul livrat este considerat „mic” dacă cantitatea de mărfuri din lot este mai mică de 500. În caz contrar, adică dacă cantitatea de mărfuri este mai mare sau egală cu 500, lotul este considerat „mare ":
SELECT dt, product_id, amount,
SWITCH(cantitate =500,„mare”) marca AS
DIN m_venit;
Interogarea Q042.În următoarea cerere, dacă cantitatea de mărfuri din lotul primit este mai mică de 300, atunci lotul este considerat „mic”. În caz contrar, adică dacă valoarea condiției SELECT dt, product_id, amount,
IIF(suma IIF(suma DIN m_venit;
Interogare SQL Q043.În următoarea cerere, dacă cantitatea de mărfuri din lotul primit este mai mică de 300, atunci lotul este considerat „mic”. În caz contrar, adică dacă valoarea condiției SELECT dt, product_id, amount,
SWITCH(suma sumă suma>=1000,„mare”) marca AS
DIN m_venit;
Interogare SQL Q044.În următoarea interogare, vânzările sunt împărțite în trei grupuri: mici (până la 150), medii (de la 150 la 300), mari (300 sau mai mult). În continuare, se calculează suma totală pentru fiecare grup:
SELECTARE Categoria, SUM(suma_rezultat) AS Ctgry_Total
FROM (SELECT suma*preț ca rezultat_sumă,
IIf(suma*preț IIf(suma*preț FROM m_rezultat) AS t
GROUP BY Categorie;
Interogare SQL Q045. Funcția DateAdd este folosită pentru a adăuga zile, luni sau ani la o dată dată și pentru a obține o nouă dată. Următoarea cerere:
1) adaugă 30 de zile la data din câmpul dt și afișează noua dată în câmpul dt_plus_30d;
2) adaugă 1 lună la data din câmpul dt și afișează noua dată în câmpul dt_plus_1m:
SELECT dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m
DIN m_venit;
Interogare SQL Q046. Funcția DateDiff este concepută pentru a calcula diferența dintre două date în unități diferite (zile, luni sau ani). Următoarea interogare calculează diferența dintre data din câmpul dt și data curentă în zile, luni și ani:
SELECT dt, DateDiff("d",dt,Date()) AS ultima_zi,
DateDiff("m",dt,Date()) AS last_months,
DateDiff("aaaa", dt,Data()) AS anii_ultim
DIN m_venit;
Interogare SQL Q047. Numărul de zile de la data primirii mărfurilor (tabelul m_venit) până la data curentă se calculează utilizând funcția DateDiff și se compară data de expirare (tabelul m_product):
DateDiff("d",dt,Date()) AS ultimele_zile
FROM m_income CA un INNER JOIN m_product AS b
ON a.product_id=b.id;
Interogare SQL Q048. Se calculează numărul de zile de la data primirii mărfurilor până la data curentă, apoi se verifică dacă această cantitate depășește data de expirare:
SELECT a.id, product_id, dt, lifedays,
DateDiff("d",dt,Date()) AS ultimele_zile, IIf(last_days>lifedays,"Yes","Nu") AS data_expire
FROM m_income a INNER JOIN m_product b
ON a.product_id=b.id;
Interogare SQL Q049. Se calculează numărul de luni de la data primirii mărfurilor până la data curentă. Coloana month_last1 calculează numărul absolut de luni, coloana month_last2 calculează numărul de luni întregi:
SELECT dt, DateDiff("m",dt,Date()) AS luna_ultima1,
DateDiff("l",dt,Date())-iif(day(dt)>day(date()),1,0) AS month_last2
DIN m_venit;
Interogare SQL Q050. Este afișat un raport trimestrial privind cantitatea și cantitatea de bunuri achiziționate pentru anul 2011:
SELECT kvartal, SUM(outcome_sum) AS Total
FROM (SELECT suma*preț AS rezultat_sum, luna(dt) AS m,
SWITCH(m =10,4) AS kvartal
DIN m_venit WHERE an(dt)=2011) AS t
GRUPĂ PE trimestru;
Interogarea Q051. Următoarea interogare vă ajută să aflați dacă utilizatorii au putut introduce în sistem informații despre consumul de bunuri într-o cantitate mai mare decât cantitatea de bunuri primite:
SELECT produs_id, SUM(in_sum) AS income_sum, SUM(out_sum) AS outcome_sum
FROM (SELECT ID_produs, suma*preț ca în_sum, 0 ca out_sum
din m_income
UNIREA TOȚI
SELECTAȚI ID-ul_produsului, 0 ca sumă_intră, sumă*preț ca sumă_sumă
din m_outcome) AS t
GROUP BY product_id
AVÂND SUM(în_sumă)
Interogarea Q052. Numerotarea rândurilor returnate de o interogare este implementată în moduri diferite. De exemplu, puteți renumerota rândurile unui raport pregătit în MS Access folosind MS Access însuși. De asemenea, puteți renumerota folosind limbaje de programare, de exemplu, VBA sau PHP. Cu toate acestea, uneori, acest lucru trebuie făcut în interogarea SQL în sine. Deci, următoarea interogare va numerota rândurile tabelului m_income în funcție de ordinea crescătoare a valorilor câmpului ID:
SELECTAȚI COUNT(*) ca N, b.id, b.product_id, b.amount, b.price
FROM m_income a INNER JOIN m_income b ON a.id GROUP BY b.id, b.product_id, b.amount, b.price;
Interogarea Q053. Sunt afișate primele cinci produse dintre produsele după valoarea vânzărilor. Primele cinci înregistrări sunt tipărite folosind instrucțiunea TOP:
SELECTARE TOP 5, product_id, sum(amount*price) AS summa
DIN m_rezultat
GROUP BY product_id
COMANDA PENTRU suma(suma*pret) DESC;
Interogarea Q054. Sunt afișate primele cinci produse dintre produsele după valoarea vânzărilor, iar rândurile sunt numerotate ca rezultat:
SELECTARE COUNT(*) AS N, b.product_id, b.summa
DIN
FROM m_outcome GROUP BY product_id) AS a
INNER JOIN
(SELECTARE ID_produs, sum(cantitate*preț) AS summa,
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
AVÂND NUMĂRARE(*)ORDINARE PENTRU NUMĂRARE(*);
Interogarea Q055. Următoarea interogare SQL arată utilizarea funcțiilor matematice COS, SIN, TAN, SQRT, ^ și ABS în MS Access SQL:
SELECT (selectați count(*) din m_income) ca N, 3,1415926 ca pi, k,
2*pi*(k-1)/N ca x, COS(x) ca COS_, SIN(x) ca SIN_, TAN(x) ca TAN_,
SQR(x) ca SQRT_, x^3 ca „x^3”, ABS(x) ca ABS_
FROM (SELECT COUNT (*)) AS k
FROM m_income AS a INNER JOIN m_income AS b ON a.idGROUP BY b.id) t;
Interogare SQL. Exemple în MS Access. UPDATE: 1-10
Solicitare U001. Următoarea interogare de modificare SQL crește prețurile bunurilor cu codul 3 din tabelul m_income cu 10%:UPDATE m_income SET preț = preț*1.1
WHERE product_id=3;
Cerere U002. Următoarea interogare de actualizare SQL mărește cantitatea tuturor produselor din tabelul m_income cu 22 de unități ale căror nume încep cu cuvântul „Ulei”:
UPDATE m_income SET suma = suma+22
WHERE product_id IN (SELECT ID FROM m_product WHERE titlul LIKE "Oil*");
Cerere U003. Următoarea interogare SQL pentru o modificare a tabelului m_outcome reduce prețurile tuturor bunurilor fabricate de Sladkoe LLC cu 2 procente:
UPDATE m_outcome SET preț = preț*0,98
WHERE product_id IN
(SELECT a.id FROM m_product a INNER JOIN m_supplier b
ON a.supplier_id=b.id WHERE b.title="LLC"Сладкое"");. !}
Interogările din Access sunt instrumentul principal pentru selectarea, actualizarea și procesarea datelor în tabelele bazei de date. Access, în conformitate cu conceptul de baze de date relaționale, folosește SQL (Structured Query Language) pentru a executa interogări. Folosind instrucțiunile limbajului SQL, orice interogare în Access este implementată.
Principalul tip de cerere este o cerere de selecție. Rezultatul acestei interogări este un tabel nou care există până când interogarea este închisă. Înregistrările sunt formate prin combinarea înregistrărilor tabelelor pe care se bazează interogarea. Metoda de combinare a înregistrărilor de tabel este specificată la definirea relației lor în schema de date sau la crearea unei interogări. Condițiile de selecție formulate în interogare vă permit să filtrați înregistrările care alcătuiesc rezultatul îmbinării tabelelor.
În Access pot fi create mai multe tipuri de interogări:
- cerere de mostra- selectează datele dintr-un tabel sau interogare sau mai multe tabele interdependente și alte interogări. Rezultatul este un tabel care există până când interogarea este închisă. Înregistrările tabelului de rezultate sunt generate în conformitate cu condițiile de selecție specificate și atunci când se utilizează mai multe tabele prin combinarea înregistrărilor acestora;
- cerere de creare a unui tabel- selectează datele din tabele interconectate și din alte interogări, dar, spre deosebire de o interogare select, stochează rezultatul într-un nou tabel permanent;
- solicitări de actualizare, adăugare, ștergere- sunt cereri de acțiune, în urma cărora datele din tabele sunt modificate.
Interogările din Access în modul proiectare conțin o schemă de date care afișează tabelele utilizate și un formular de interogare în care sunt proiectate structura tabelului de interogare și condițiile de selectare a înregistrărilor (Fig. 4.1).
Folosind o interogare, puteți efectua următoarele tipuri de prelucrare a datelor:
- includeți câmpuri de tabel selectate de utilizator în tabelul de interogări;
- efectuați calcule în fiecare dintre înregistrările primite;
- selectați înregistrări care îndeplinesc condițiile de selecție;
- creați un nou tabel virtual bazat pe combinarea înregistrărilor de tabele interconectate;
- înregistrările grupului care au aceleași valori în unul sau mai multe câmpuri, îndeplinesc simultan funcții statistice pe alte câmpuri ale grupului și, ca urmare, includ o înregistrare pentru fiecare grup;
- creați un nou tabel de bază de date folosind date din tabelele existente;
- actualizați câmpurile dintr-un subset selectat de înregistrări;
- ștergeți un subset selectat de înregistrări dintr-un tabel al bazei de date;
- adăugați un subset selectat de înregistrări la un alt tabel.
Interogările din Access servesc ca surse de înregistrare pentru alte interogări, formulare și rapoarte. Folosind o interogare, puteți colecta informații complete pentru a genera un anumit document de domeniu din mai multe tabele, apoi îl puteți utiliza pentru a crea un formular - o reprezentare electronică a acestui document. Dacă un formular sau un raport este creat de către vrăjitor pe baza mai multor tabele interconectate, atunci o interogare este generată automat pentru acestea ca sursă de înregistrări.
Pentru a consolida acest lucru, urmăriți tutorialul video.