SQL - Lecția 4. Selectarea datelor - instrucțiunea SELECT

Deci, în baza noastră de date pe forum există trei tabele: utilizatori (utilizatori), subiecte (subiecte) și postări (mesaje). Și vrem să vedem ce date conțin. Pentru a face acest lucru, există un operator în SQL SELECTAȚI. Sintaxa de utilizare a acestuia este următoarea:

SELECT selectează_ce FROM select_from;

În loc de „ce_se_selectăm”, trebuie să specificăm fie numele coloanei ale cărei valori dorim să le vedem, fie numele mai multor coloane separate prin virgule, fie caracterul asterisc (*), care înseamnă selecția tuturor coloanelor din masa. În loc de „from_choose” ar trebui să specificați numele tabelului.

Să ne uităm mai întâi la toate coloanele din tabelul utilizatori:

SELECT * FROM utilizatori;

Acestea sunt toate datele noastre pe care le-am introdus în acest tabel. Dar să presupunem că vrem să ne uităm doar la coloana id_user (de exemplu, în ultima lecție, trebuia să știm ce id_users sunt în tabelul utilizatori pentru a popula tabelul cu subiecte). Pentru a face acest lucru, vom specifica numele acestei coloane în interogare:

SELECTează id_user FROM utilizatori;

Ei bine, dacă vrem să vedem, de exemplu, numele și e-mailurile utilizatorilor noștri, atunci vom enumera coloanele de interes separate prin virgule:

SELECT numele, e-mailul de la utilizatori;

În mod similar, puteți vedea ce date conțin celelalte tabele ale noastre. Să vedem ce subiecte avem:

SELECT * FROM subiecte;

Acum avem doar 4 subiecte, și dacă sunt 100? Aș dori să fie afișate, de exemplu, în ordine alfabetică. Există un cuvânt cheie pentru aceasta în SQL. COMANDA PENTRU urmat de numele coloanei după care va avea loc sortarea. Sintaxa este următoarea:

SELECT coloană_nume FROM table_name ORDER BY sort_column_name;

Sortarea implicită este crescătoare, dar aceasta poate fi modificată prin adăugarea cuvântului cheie DESC

Acum datele noastre sunt sortate în ordine descrescătoare.

Puteți sorta după mai multe coloane simultan. De exemplu, următoarea interogare va sorta datele după coloana topic_name, iar dacă există mai multe rânduri identice în această coloană, atunci coloana id_author va fi sortată în ordine descrescătoare:

Comparați rezultatul cu rezultatul interogării anterioare.

De foarte multe ori nu avem nevoie de toate informațiile din tabel. De exemplu, vrem să știm ce subiecte au fost create de utilizatorul sveta (id=4). Există un cuvânt cheie pentru aceasta în SQL. UNDE, sintaxa pentru o astfel de solicitare este următoarea:

Pentru exemplul nostru, condiția este ID-ul utilizatorului, adică. vrem doar rânduri care au 4 în coloana id_author (ID utilizator sveta):

Sau vrem să știm cine a creat tema „biciclete”:

Desigur, ar fi mai convenabil să afișați numele autorului în loc de id-ul autorului, dar numele sunt stocate într-un alt tabel. În lecțiile ulterioare, vom învăța cum să selectăm date din mai multe tabele. Între timp, să aflăm ce condiții pot fi specificate folosind cuvântul cheie WHERE.

Operator	Descriere
= (egal)	Valorile selectate sunt egale cu cele specificate Exemplu: SELECT * FROM subiecte WHERE id_author=4; Rezultat:
> (mai mult)	Sunt selectate valori mai mari decât cele specificate Exemplu: SELECT * FROM subiecte WHERE id_author>2; Rezultat:
< (меньше)	Sunt selectate valori mai mici decât cele specificate Exemplu: SELECT * FROM subiecte WHERE id_author Rezultat:
>= (mai mare sau egal cu)	Sunt selectate valori mai mari sau egale cu valoarea specificată. Exemplu: SELECT * FROM subiecte WHERE id_author>=2; Rezultat:
<= (меньше или равно)	Sunt selectate valori mai mici sau egale cu valoarea specificată. Exemplu: SELECT * FROM subiecte WHERE id_author Rezultat:
!= (nu este egal)	Sunt selectate valori care nu sunt egale cu cele specificate Exemplu: SELECT * FROM subiecte WHERE id_author!=1; Rezultat:
NU ESTE NUL	Sunt selectate rândurile care au valori în câmpul specificat Exemplu: SELECT * FROM subiecte WHERE id_author IS NOT NULL; Rezultat:
ESTE NUL	Sunt selectate rândurile care nu au o valoare în câmpul specificat Exemplu: SELECT * FROM subiecte WHERE id_author IS NULL; Rezultat: Set gol - nu există astfel de șiruri.
ÎNTRE (între)	Sunt selectate valorile dintre valorile specificate. Exemplu: SELECT * FROM subiecte WHERE id_author INTRE 1 SI 3; Rezultat:
IN (valoare conținută)	Valorile corespunzătoare celor specificate Exemplu: SELECT * FROM subiecte WHERE id_author IN (1, 4); Rezultat:
NOT IN (valoarea nu este inclusă)	Valori selectate, altele decât cele specificate Exemplu: SELECT * FROM subiecte WHERE id_author NOT IN (1, 4); Rezultat:
LIKE (potrivire)	Sunt selectate valorile eșantionului Exemplu: SELECT * FROM subiecte WHERE nume_subiect LIKE "vel%"; Rezultat: Posibilele metacaractere ale operatorului LIKE vor fi discutate mai jos.
NU CA	Sunt selectate valori care nu se potrivesc cu eșantionul Exemplu: SELECT * FROM subiecte WHERE nume_subiect NU LIKE „vel%”; Rezultat:

Metacaracterele operatorului LIKE

Căutările metacaracterelor pot fi efectuate numai în câmpurile de text.

Cel mai comun metacaracter este % . Înseamnă orice caracter. De exemplu, dacă dorim să găsim cuvinte care încep cu literele „vel”, atunci vom scrie LIKE „vel%”, iar dacă dorim să găsim cuvinte care conțin caracterele „club”, atunci vom scrie LIKE „% club%". De exemplu:

Un alt metacaracter folosit frecvent este _ . Spre deosebire de %, care denotă puține sau deloc caractere, sublinierea denotă exact un caracter. De exemplu:

Atenție la spațiul dintre metacaracter și „pește”, dacă îl săriți, cererea nu va funcționa, deoarece metacaracter _ reprezintă exact un caracter, iar un spațiu este, de asemenea, un caracter.

Este suficient pentru azi. În lecția următoare, vom învăța cum să interogăm două sau mai multe tabele. Între timp, încercați să faceți propriile interogări în tabelul de postări (mesaje).

Această lecție este despre interogări SQL la baza de date pe Acces VBA. Ne vom uita la modul în care interogările VBA INSERT, UPDATE, DELETE sunt efectuate în baza de date și vom învăța, de asemenea, cum să obținem o anumită valoare dintr-o interogare SELECT.

Cei care programează Acces VBAși în timp ce lucrează cu o bază de date SQL server, ei se confruntă adesea cu o sarcină atât de simplă și necesară, cum ar fi trimiterea unei interogări SQL către baza de date, fie că este vorba de INSERT, UPDATE sau o simplă interogare SQL SELECT. Și din moment ce suntem programatori începători, ar trebui să putem face acest lucru, așa că astăzi vom face exact asta.

Am atins deja subiectul obținerii de date de la un server SQL, unde am scris cod în VBA pentru a obține aceste date, de exemplu, într-un articol despre Încărcarea datelor într-un fișier text din MSSql 2008 sau am atins și puțin în material Încărcarea datelor din Access într-un șablon Word și Excel.dar într-un fel sau altul acolo l-am considerat superficial, iar astăzi îmi propun să vorbim puțin mai detaliat despre asta.

Notă! Toate exemplele de mai jos sunt discutate folosind un proiect Access 2003 ADP și o bază de date MSSql 2008.

Date inițiale pentru exemple

Să presupunem că avem un tabel test_table care va conține numerele și numele lunilor dintr-un an (interogările se fac folosind studio de management)

CREATE TABLE .( NOT NULL, (50) NULL) ON GO

După cum am spus, vom folosi un proiect ADP configurat să funcționeze cu MS SQL 2008, în care am creat un formular de testare și am adăugat un buton de pornire cu o legendă. "Alerga", de care va trebui să ne testăm codul, de exemplu. vom scrie tot codul în handlerul de evenimente " Apăsați butonul».

Interogări de bază de date INSERT, UPDATE, DELETE în VBA

Pentru a nu-l trage mult timp, să începem, să presupunem că trebuie să adăugăm un rând la tabelul nostru de testare ( codul este comentat)/

Private Sub start_Click() „Declară o variabilă pentru a stoca șirul de interogare Dim sql_query As String „Scrie interogarea de care avem nevoie în ea sql_query = „INSERT INTO test_table (id, name_mon) VALUES (“6”, „June”)” „Execute cu DoCmd.RunSQL sql_query End Sub

În acest caz, interogarea este executată utilizând setările curente de conexiune la baza de date. Putem verifica dacă datele au fost adăugate sau nu.

După cum puteți vedea, datele au fost introduse.

Pentru a șterge o linie, scriem următorul cod.

Private Sub start_Click() „Declară o variabilă care să dețină șirul de interogare Dim sql_query As String „Scrieți o interogare de ștergere în ea sql_query = „DELETE test_table WHERE id = 6” „Execută-l DoCmd.RunSQL sql_query End Sub

Dacă verificăm, vom vedea că linia dorită a fost ștearsă.

Pentru a actualiza datele, scriem o interogare de actualizare pentru variabila sql_query, sper că sensul este clar.

SELECT interogare la baza de date în VBA

Aici lucrurile sunt puțin mai interesante decât cu alte constructe SQL.

În primul rând, să presupunem că trebuie să obținem toate datele din tabel și, de exemplu, le vom procesa și le vom afișa într-un mesaj și, desigur, le puteți folosi în alte scopuri, pentru aceasta scriem următoarele cod

Private Sub start_Click() „Declararea variabilelor „Pentru un set de înregistrări din baza de date Dim RS As ADODB.Recordset „Șir de interogare Dim sql_query As String „Șir pentru afișarea datelor totale în mesajul Dim str As String „Crearea unui nou obiect pentru setul de înregistrări RS = Nou ADODB .Recordset „Șir de interogare sql_query = „SELECT id, name_mon FROM test_table” „Execută interogarea folosind setările curente de conexiune la proiect pentru a afișa un mesaj str = str & RS.Fields(„id”) & „-” & RS. Câmpuri ("name_mon") și vbnewline "mergi la înregistrarea următoare RS.MoveNext Wend "Output message msgbox str End Sub

Aici folosim deja VBA Access Loops pentru a parcurge toate valorile din setul nostru de înregistrări.

Dar, destul de des, este necesar să obțineți nu toate valorile dintr-un set de înregistrări, ci doar una, de exemplu, numele lunii prin codul său. Și pentru aceasta, folosirea unei bucle este oarecum costisitoare, așa că putem scrie pur și simplu o interogare care va returna o singură valoare și se va referi la ea, de exemplu, vom obține numele lunii prin codul 5

Private Sub start_Click() „Declararea variabilelor „Pentru un set de înregistrări din baza de date Dim RS As ADODB.Recordset „Șir de interogare Dim sql_query As String „Șir pentru afișarea valorii finale Dim str As String „Crearea unui nou obiect pentru setul de înregistrări RS = Nou ADODB.Recordset „Șir de interogare sql_query = „SELECT name_mon FROM test_table WHERE id = 5” „Execută interogarea folosind setările curente de conexiune la proiect RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic „Obțineți valoarea noastră str = RS0Field. ) msgbox str end sub

Pentru universalitate, aici ne-am adresat deja nu prin numele celulei, ci prin indexul acesteia, i.e. 0, care este prima valoare în Set de înregistrări, pana la urma am obtinut valoarea "Mai".

După cum puteți vedea, totul este destul de simplu. Dacă deseori trebuie să obțineți o anumită valoare din baza de date ( ca în ultimul exemplu), apoi vă recomand să scoateți tot codul într-o funcție separată (Cum se scrie o funcție în VBA Access 2003) cu un parametru de intrare, de exemplu, codul lunii ( luând în considerare exemplul nostru) și pur și simplu, acolo unde este necesar să afișăm această valoare, apelăm funcția de care avem nevoie cu parametrul necesar și asta este, vom reduce semnificativ codul VBA și vom îmbunătăți percepția programului nostru.

Asta e tot pentru azi. Noroc!

Descrierea proiectului educațional „Magazin”

Schema de legături la tabel

Descrierea tabelelor

m_category - categorii de produse

m_venit - primire de bunuri

m_outcome - consumul de bunuri

m_product - director, descriere produs

m_furnizor - director; informatii despre furnizor

m_unit - director; unitati

Pentru a testa practic exemplele oferite în acest tutorial, trebuie să aveți la dispoziție următorul software:

Microsoft Access 2003 sau o versiune ulterioară.

Interogare SQL în MS Access. start

Pentru a vedea conținutul unui tabel, faceți dublu clic pe numele tabelului din panoul din stânga:

Pentru a comuta la modul de editare a câmpului tabelului, 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 superior:

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. Un exemplu de interogare SQL pentru a obține numai câmpurile necesare în secvența dorită:

SELECT dt, product_id, amount

DIN m_venit;

Interogarea SQL Q002.În acest exemplu de interogare SQL, caracterul asterisc (*) este folosit pentru a afișa 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;

CerereSQLQ003. Declarația DISTINCT este folosită pentru a elimina înregistrările duplicate și pentru a obține multe înregistrări unice:

SELECTAȚI DISTINCT ID_produs

DIN m_venit;

Interogarea SQL Q004. Instrucțiunea ORDER BY este folosită pentru a sorta (ordona) înregistrările după valorile unui anumit câmp. Numele câmpului urmează clauza ORDER BY:

SELECTAȚI *
DIN m_venit

COMANDA DUPA pret;

Interogarea SQL Q005. Instrucțiunea ASC este utilizată în plus față de instrucțiunea ORDER BY și este folosită pentru a defini o sortare ascendentă. Instrucțiunea DESC este folosită în plus față de instrucțiunea ORDER BY și este folosită pentru a defini o sortare 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;

Interogarea SQL Q006. Pentru a selecta înregistrările necesare din tabel, se folosesc diverse expresii logice care exprimă condiția de selecție. Expresia booleană vine după clauza WHERE. Un exemplu de obținere din tabelul m_income a tuturor înregistrărilor 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 operațiile logice 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 conectată la î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;

Interogarea SQL Q009. Există două lucruri la care trebuie să acordați atenție în această interogare SQL: 1) textul de căutare este inclus între ghilimele simple ("); 2) data este în formatul #Lună/Zi/An#, care este corect pentru MS Acces. În alte sisteme, formatul de dată poate fi diferit. Un exemplu de afișare a informațiilor despre primirea laptelui pe 12 iunie 2011. Atenție la formatul de dată #6/12/2011#:

SELECT dt, product_id, title, amount, price

FROM m_income INNER JOIN m_product

WHERE title="(!LANG:Lapte" And dt=#6/12/2011#; !}

Interogare SQL Q010. Instrucțiunea BETWEEN este utilizată pentru a testa dacă o gamă de valori îi aparține. Un exemplu de interogare SQL care afișează informații despre bunurile 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ă într-o clauză WHERE. Dar există și alte moduri de a folosi subinterogări.

Solicitați Q011. Afișează informații despre produse 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 se află în tabelul m_outcome:

SELECTAȚI *
DE LA m_produs

WHERE id NOT IN (SELECTARE product_id FROM m_outcome);

Solicitați Q013. Această interogare SQL returnează o listă unică de coduri și nume de produse care au coduri în tabelul m_income, dar nu în tabelul m_outcome:

SELECTAȚI DISTINCT product_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);

Solicitați Q014. O listă unică de categorii este afișată din tabelul m_category, ale căror nume încep cu litera M:

SELECTAȚI un titlu DISTINCT

DE LA m_produs
UNDE titlul LIKE „M*”;

Solicitați 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ă cheltuieli = cantitate*preț și profit pentru fiecare înregistrare de consum de articol, presupunând că profitul este de 7% din vânzări:

suma*pret/100*7 AS profit
DIN m_rezultat;

Solicitați 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
DIN m_rezultat;

Solicitați Q017. Folosind instrucțiunea INNER JOIN, puteți combina date din mai multe tabele. În exemplul următor, în funcție de valoarea lui 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
ORDINARE 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. Ele iau mai multe valori și returnează o singură valoare atunci când sunt procesate. Un exemplu de calculare a sumei produsului dintre câmpurile cantitate și preț folosind funcția de agregare SUM:

SELECTAȚI SUMA(suma*preț) AS Total_Sum

DIN m_venit;

Solicitați 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ă suma tuturor articolelor cu codul 1 primite î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#;.

Solicitați Q021. Următoarea interogare SQL calculează pentru cât s-au vândut bunurile cu codul 4 sau 6:

SELECTAȚI Suma(amount*price) ca rezultat_sum

DIN m_rezultat
WHERE product_id=4 SAU product_id=6;

Solicitați Q022. Se calculează pentru ce sumă a fost vândută la 12 iunie 2011 de bunuri cu codul 4 sau 6:

SELECTAȚI Suma(suma*preț) AS rezultatul_sumă

DIN m_rezultat
WHERE (product_id=4 SAU product_id=6) ȘI dt=#6/12/2011#;

Solicitați Q023. Sarcina este aceasta. Calculați suma totală pentru care au fost creditate mărfurile din categoria „Produse de copt”.

Pentru a rezolva această problemă, trebuie să operați pe trei tabele: m_income, m_product și m_category, deoarece:

- cantitatea si pretul bunurilor creditate sunt stocate in tabelul m_income;
- codul categoriei fiecărui produs este stocat în tabelul m_product;
- numele titlului categoriei este stocat în tabelul m_category.

Pentru a rezolva această problemă, folosim următorul algoritm:

- determinarea codului categoriei „Produse panificate” din tabelul m_category prin intermediul unei subinterogare;
- alăturarea tabelelor m_income și m_product pentru a determina categoria fiecărui produs creditat;
- calculul sumei de primire (= cantitate * preț) pentru mărfuri, al căror 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="(!LANG:Produse de panificație)"); !}

Solicitați Q024. Problema calculării cantității totale de mărfuri creditate din categoria „Produse coapte” va fi rezolvată prin următorul algoritm:
- fiecare înregistrare a tabelului m_income, în funcție de valoarea product_id-ului său, din tabelul m_category, se potrivește cu numele categoriei;
- selectați înregistrări pentru care categoria este egală cu „Produse de copt”;
- se calculează suma venitului = cantitate * preț.

FROM (m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id)

WHERE c.title="(!LANG:Produse de patiserie"; !}

Cerere Q025. Acest exemplu calculează câte articole au fost consumate:

SELECTAȚI COUNT(product_id) AS product_cnt

FROM (SELECT DISTINCT product_id FROM m_outcome) AS t;

Cerere Q026. Clauza GROUP BY este utilizată pentru gruparea înregistrărilor. În mod obișnuit, înregistrările sunt grupate după valoarea unuia sau mai multor câmpuri și fiecărei grupe i se aplică o operație de agregare. De exemplu, următoarea interogare generează un raport privind vânzarea mărfurilor. Adică, se generează un tabel care va conține numele bunurilor și suma pentru care sunt 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ă, se generează un tabel care va conține numele categoriilor de produse, suma totală pentru care sunt vândute mărfurile 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 zecimală după separatorul zecimal):

SELECTAȚI titlul c., SUM(suma*preț) AS rezultatul_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;

Solicitați Q028. Pentru fiecare produs, se calculează numărul total și mediu al încasărilor sale și se afișează informații despre mărfuri, al căror număr total de chitanțe 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;

Solicitați Q029. Această interogare calculează pentru fiecare articol suma și media încasărilor sale efectuate în al doilea trimestru al anului 2011. Dacă suma totală de primire a mărfurilor nu este mai mică de 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 SUM(suma*preț)>=1000;

Solicitați Q030.În unele cazuri este necesar să se potrivească fiecare înregistrare a unui tabel cu fiecare înregistrare a altui tabel; ceea ce se numeşte produs cartezian. Tabelul rezultat dintr-o astfel de îmbinare se numește tabel Descartes. De exemplu, dacă un tabel A are 100 de intrări și tabelul B are 15 intrări, atunci tabelul lor cartezian va fi format din 100*15=150 de intrări. Următoarea interogare unește fiecare intrare din tabelul m_income cu fiecare intrare din tabelul m_outcome:
FROM m_venit, m_rezultat;

Cerere 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

Cerere 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;

Cerere Q033.În acest exemplu, cele două interogări de mai sus (q031 și q032) sunt folosite ca subinterogări. Rezultatele acestor interogări sunt îmbinate într-un singur raport utilizând metoda LEFT JOIN. Următoarea interogare afișează un raport privind numărul și cantitatea de produse primite și vândute pentru fiecare furnizor. Ar trebui să acordați atenție faptului că, dacă un produs a sosit deja, dar nu a fost încă vândut, atunci celula rezultat_sum pentru această înregistrare 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);

Cerere Q034.În acest exemplu, cele două interogări de mai sus (q031 și q032) sunt folosite ca subinterogări. Rezultatele acestor interogări sunt combinate într-un singur raport folosind metoda RIGTH JOIN. Următoarea interogare tipărește un raport privind valoarea plăților fiecărui client pentru sistemele de plată pe care le-a folosit și valoarea investițiilor pe care le-a făcut. Următoarea interogare afișează un raport privind numărul și cantitatea de produse primite și vândute pentru fiecare furnizor. Rețineți că, dacă un produs a fost deja vândut, dar nu a fost încă primit, atunci celula income_sum pentru această intrare va fi goală. Prezența unor astfel de celule goale este un indicator al unei erori în contabilizarea vânzărilor, deoarece înainte de vânzare, este necesar mai întâi să sosească produsul corespunzător:

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);

Cerere Q035. Este afișat un raport privind 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 încasărilor 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;

Cerere 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 are elemente. Următoarea interogare returnează codurile de produs care sunt conținute atât în ​​tabelul m_income, cât și în tabelul m_outcome:

SELECTAȚI DISTINCT ID_produs

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

Cerere Q037. Sunt afișate codurile de produs care sunt conținute atât în ​​tabelul 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)

Cerere Q038. Sunt afișate coduri de produs care sunt conținute ca în tabelul m_income, dar nu sunt conținute în tabelul m_outcome:

SELECTAȚI DISTINCT ID_produs

FROM m_income AS a
WHERE NU EXISTS(SELECT product_id FROM m_outcome AS b
UNDE b.product_id=a.product_id);

Cerere Q039. Este afișată o listă de produse cu cea mai mare sumă de vânzări. Algoritmul este acesta. Pentru fiecare produs se calculează suma 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:

SELECTAȚI ID-ul_produsului, SUM(suma*preț) AS suma_sumă

DIN m_rezultat
GROUP BY product_id
AVÂND SUMA(suma*preț) = (SELECT MAX(s_amount)
FROM (SELECT SUM(suma*preț) AS s_amount FROM m_rezultat GROUP BY product_id));

Cerere 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 unui articol 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 instrucțiunea IIF este folosită de mai multe ori, este mai convenabil să o înlocuiți cu instrucțiunea SWITCH. Operatorul SWITCH (operator cu alegere 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;

Cerere Q042.În următoarea interogare, 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 interogare, 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(cantitate suma 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 și mai mult). În continuare, pentru fiecare grup, se calculează suma totală:

SELECT Categorie, SUM(suma_rezultat) AS Ctgry_Total

FROM (SELECT suma*preț AS 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) adăugați 1 lună la data din câmpul dt și afișați 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_income) până la data curentă sunt calculate 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ă acest număr 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 month_last1,

DateDiff("l",dt,Date())-iif(day(dt)>day(date()),1,0) AS month_last2
DIN m_venit;

Interogare SQL Q050. Se afișează un raport trimestrial privind cantitatea și cantitatea de mărfuri primite pentru anul 2011:

SELECT kvartal, SUM(suma_rezultat) 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
bloc GROUP BY;

Cerere Q051. Următoarea interogare vă ajută să aflați dacă utilizatorii au reușit să introducă în sistem informații despre consumul de bunuri pentru o sumă mai mare decât suma de primire a mărfurilor:

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ă)
Cerere Q052. Numerotarea liniilor returnate de 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 trebuie făcută în interogarea SQL în sine. Deci, următoarea interogare va numerota rândurile tabelului m_income în conformitate cu 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;

Cerere Q053. Sunt afișate primele cinci dintre produse după volumul vânzărilor. Ieșirea primelor cinci înregistrări se realizează 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;

Cerere Q054. Sunt afișate primele cinci dintre produse după volumul 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(*);

Solicitați 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

Cerere U001. Următoarea interogare de modificare SQL crește prețurile articolelor 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 „Unt”:

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 de modificare SQL din tabelul m_outcome reduce prețurile tuturor bunurilor produse de OOO Sladkoe 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="(!LANG:OOO"Сладкое"");. !}

Laboratorul #1

SQL: RETRIEVE DATA - comandăSELECTAȚI

Scopul muncii:

• 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.

Sarcina№1. Creați o interogare pentru a selecta în modul SQL toate valorile câmpurilor NUME și NUME din tabelul STUDENTI.

SELECTAȚI PRENUMELE, NUMELE

DE LA STUDENTI;

Sarcina№2 . Creați o interogare de selectare în modul SQL pentru toate coloanele din tabelul STUDENTI.

SELECTAȚI *

DE LA STUDENTI;

Sarcina numărul 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 numărul 4. Creați o interogare de selectare în modul SQL care selectează numele tuturor elevilor cu numele de familie Ivanov, ale căror informații se află în tabelul STUDENTI.

SELECTAȚI NUMELE, NUMELE

DE LA STUDENTI

WHERE SURNAME="Ivanov";

Sarcina numărul 5. Creați o interogare pentru o selecție în modul SQL pentru a obține numele și prenumele studenților care studiază în grupa UIT-22 pe o formă de învățământ finanțată de buget.

SELECTAȚI NUMELE, NUMELE

DE LA STUDENTI

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

Sarcina numărul 6. Creați o interogare în modul SQL. pentru un eșantion din tabelul EXAMENUL DE APROBARE, informații despre elevii cu note doar 4 și 5.

SELECTAȚI *

DIN[SCHIMBAREEXAMENE]

UNDENOTAIN (4,5);

Sarcina numărul 7. Creați un mod zanpoc și SQL pentru un eșantion de informații despre studenții care au nota 3 la examen la disciplina IOSU.

SELECTAȚI *

DIN[SCHIMBAREEXAMENE]

UNDEARTICOL=" ISSU" ȘiNOTANu în (4,5);

Sarcina numărul 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 numărul 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 *

DINSTUDENȚI

UNDENUME DE FAMILIECA"Cu*";

Ieșire:În timpul lucrului 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.

Exemplele de interogări SQL pot fi folosite pentru a învăța și exersa scrierea interogărilor SQL în MS Access.

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ă într-o clauză WHERE. Dar există și alte moduri de a folosi subinterogări.

Solicitați Q011. Afișează informații despre produse 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 se află în tabelul m_outcome:

SELECTAȚI *
DE LA m_produs
WHERE id NOT IN (SELECTARE product_id FROM m_outcome);

Solicitați Q013. Această interogare SQL returnează o listă unică de coduri și nume de produse care au coduri în tabelul m_income, dar nu în tabelul m_outcome:

SELECTAȚI DISTINCT product_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);

Solicitați Q014. O listă unică de categorii este afișată din tabelul m_category, ale căror nume încep cu litera M:

SELECTAȚI un titlu DISTINCT
DE LA m_produs
UNDE titlul LIKE „M*”;

Solicitați 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ă cheltuieli = cantitate*preț și profit pentru fiecare înregistrare de consum de articol, presupunând că profitul este de 7% din vânzări:

suma*pret/100*7 AS profit
DIN m_rezultat;

Solicitați 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
DIN m_rezultat;

Solicitați Q017. Folosind instrucțiunea INNER JOIN, puteți combina date din mai multe tabele. În exemplul următor, în funcție de valoarea lui 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
ORDINARE 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. Ele iau mai multe valori și returnează o singură valoare atunci când sunt procesate. Un exemplu de calculare a sumei produsului dintre câmpurile cantitate și preț folosind funcția de agregare SUM.