Exemple de interogări SQL către baza de date MySQL. Instrucțiuni și oferte speciale

Cum pot afla numărul de modele de PC produse de un anumit furnizor? Cum se determină valoarea medie a prețului computerelor cu aceleași caracteristici tehnice? Acestea și multe alte întrebări legate de unele informații statistice se poate răspunde cu ajutorul lui funcții rezumative (agregate).. Standardul oferă următoarele funcții agregate:

Toate aceste funcții returnează o singură valoare. În același timp, funcțiile COUNT, MINși MAX se aplică oricărui tip de date, în timp ce SUMĂși AVG folosit numai pentru câmpurile numerice. Diferența între funcție NUMARA(*)și NUMARA(<имя поля>) este că al doilea nu ia în considerare valorile NULL la calcul.

Exemplu. Găsiți prețul minim și maxim pentru computerele personale:

Exemplu. Găsiți numărul disponibil de computere produse de producătorul A:

Exemplu. Dacă ne interesează numărul de modele diferite produse de producătorul A, atunci interogarea poate fi formulată după cum urmează (folosind faptul că fiecare model este înregistrat o dată în tabelul Produs):

Exemplu. Găsiți numărul de modele diferite disponibile produse de producătorul A. Interogarea este similară cu cea anterioară, în care era necesar să se determine numărul total de modele produse de producătorul A. Aici este necesar să găsiți numărul de modele diferite. în tabelul PC (adică, disponibil pentru vânzare).

Pentru a ne asigura că la obținerea indicatorilor statistici, când sunt utilizate numai valori unice argumentul funcției agregate poate fi utilizat parametru DISTINCT. O alta parametrul ALL este implicit și se așteaptă să numere toate valorile returnate în coloană. Operator,

Dacă trebuie să obținem numărul de modele de PC produse de fiecare producător, va trebui să utilizați GROUP BY oferta, urmând sintactic după clauze WHERE.

GROUP BY oferta

Dacă ar exista o coloană cu o dată în SELECT, atunci ar fi posibil să se calculeze acești indicatori pentru fiecare dată specifică. Pentru a face acest lucru, trebuie să adăugați data ca coloană de grupare, iar apoi funcțiile agregate vor fi calculate pentru fiecare combinație de valori (model-data).

Sunt mai multe specifice reguli pentru executarea funcţiilor agregate:

• Dacă în urma cererii nici un rând primit(sau mai mult de un rând pentru acest grup), atunci nu există date inițiale pentru calcularea vreuneia dintre funcțiile agregate. În acest caz, rezultatul execuției funcțiilor COUNT va fi zero, iar rezultatul tuturor celorlalte funcții va fi NULL.
• Argument functie de agregat nu poate conține în sine funcții agregate(funcție din funcție). Acestea. într-o singură solicitare este imposibil, să zicem, să primim un maxim de valori medii.
• Rezultatul executării funcției COUNT este întreg(ÎNTREG). Alte funcții agregate moștenesc tipurile de date ale valorilor procesate.
• Dacă la executarea funcției SUM s-a obținut un rezultat care a depășit valoarea maximă a tipului de date utilizat, a greşeală.

Deci, dacă cererea nu conține Oferte GROUP BY, apoi funcții agregate inclus în clauza SELECT, sunt executate pe toate șirurile de interogare rezultate. Dacă cererea conţine GROUP BY oferta, fiecare set de rânduri care are aceleași valori ale coloanei sau grupului de coloane specificat în oferta GROUP BY, constituie un grup, și funcții agregate efectuate pentru fiecare grupă separat.

AVÂND oferta

În cazul în care un clauza WHERE definește un predicat pentru filtrarea șirurilor, atunci clauza HAVING aplicat după grupare pentru a defini un predicat similar filtrarea grupurilor după valori funcții agregate. Această clauză este necesară pentru a valida valorile cu care se obțin functie de agregat nu din rânduri separate ale sursei de înregistrare definită în clauza FROM, și de la grupuri de astfel de linii. Prin urmare, un astfel de control nu poate fi inclus în clauza WHERE.

Fiecare dezvoltator web trebuie să cunoască SQL pentru a scrie interogări de baze de date. Și, deși phpMyAdmin nu a fost anulat, este adesea necesar să vă murdărești mâinile pentru a scrie SQL de nivel scăzut.

De aceea am pregătit un scurt tur al elementelor de bază ale SQL. Să începem!

1. Creați un tabel

Instrucțiunea CREATE TABLE este folosită pentru a crea tabele. Argumentele trebuie să fie numele coloanelor, precum și tipurile de date ale acestora.

Să creăm un tabel simplu numit lună. Este format din 3 coloane:

• id– Numărul lunii din anul calendaristic (întreg).
• Nume– Numele lunii (șir, maximum 10 caractere).
• zile– Numărul de zile din această lună (întreg).

Iată cum ar arăta interogarea SQL corespunzătoare:

CREATE TABLE luni (id int, nume varchar(10), zile int);

De asemenea, la crearea tabelelor, este recomandabil să adăugați o cheie primară pentru una dintre coloane. Acest lucru va păstra înregistrările unice și va accelera interogările selectate. Fie ca în cazul nostru numele lunii să fie unic (coloana Nume)

CREATE TABLE luni (id int, name varchar(10), days int, PRIMARY KEY (nume));

2. Introduceți rânduri

Acum să completăm tabelul nostru luni Informatii utile. Adăugarea înregistrărilor la un tabel se face prin instrucțiunea INSERT. Există două moduri de a scrie această instrucțiune.

Prima modalitate nu este de a specifica numele coloanelor în care vor fi inserate datele, ci de a specifica doar valorile.

Această notație este simplă, dar nesigură, deoarece nu există nicio garanție că, pe măsură ce proiectul se extinde și tabelul este editat, coloanele vor fi în aceeași ordine ca înainte. Un mod mai sigur (și în același timp mai greoi) de a scrie o instrucțiune INSERT necesită specificarea atât a valorilor, cât și a ordinii coloanelor:

Iată prima valoare din listă VALORI se potrivește cu primul nume de coloană specificat și așa mai departe.

3. Extragerea datelor din tabele

Instrucțiunea SELECT este cel mai bun prieten al nostru atunci când dorim să extragem date dintr-o bază de date. Este folosit foarte des, așa că vă rugăm să citiți cu atenție această secțiune.

Cea mai simplă utilizare a instrucțiunii SELECT este o interogare care returnează toate coloanele și rândurile dintr-un tabel (de exemplu, un tabel numit personaje):

SELECTAȚI * DIN „personaje”

Caracterul asterisc (*) înseamnă că dorim să obținem date din toate coloanele. Deoarece bazele de date SQL constau de obicei din mai mult de un tabel, este necesar cuvântul cheie FROM, urmat de un spațiu urmat de numele tabelului.

Uneori nu dorim să obținem date de la toate coloanele dintr-un tabel. Pentru a face acest lucru, în loc de asterisc (*), trebuie să scriem numele coloanelor dorite separate prin virgule.

SELECT ID, numele FROM luna

De asemenea, în multe cazuri dorim ca rezultatele să fie sortate într-o anumită ordine. În SQL, facem acest lucru cu ORDER BY. Poate fi nevoie de un modificator opțional - ASC (implicit) pentru a sorta în ordine crescătoare sau DESC pentru a sorta în ordine descrescătoare:

SELECT ID, nume FROM luna ORDENARE DUPA nume DESC

Când utilizați ORDER BY, asigurați-vă că este ultimul în instrucțiunea SELECT. În caz contrar, va fi emis un mesaj de eroare.

4. Filtrarea datelor

Ați învățat cum să selectați anumite coloane dintr-o bază de date folosind o interogare SQL, dar ce se întâmplă dacă vrem să recuperăm și anumite rânduri? Clauza WHERE vine în ajutor aici, permițându-ne să filtram datele în funcție de o condiție.

În această interogare, selectăm doar acele luni din tabel lună care sunt mai mari de 30 de zile folosind operatorul mai mare decât (>).

SELECT ID, nume FROM luna WHERE zile > 30

5. Filtrare avansată a datelor. operatori AND și SAU

Anterior, filtram datele folosind un singur criteriu. Pentru o filtrare mai complexă a datelor, puteți utiliza operatorii AND și SAU și operatorii de comparație (=,<,>,<=,>=,<>).

Aici avem un tabel care conține cele mai bine vândute patru albume din toate timpurile. Să le alegem pe cele care sunt clasificate ca rock și au mai puțin de 50 de milioane de exemplare vândute. Acest lucru se poate face cu ușurință prin plasarea unui operator AND între aceste două condiții.

6. În/Între/Like

WHERE acceptă, de asemenea, mai multe comenzi speciale, permițându-vă să verificați rapid interogările cele mai frecvent utilizate. Aici sunt ei:

• IN - folosit pentru a specifica o serie de condiții, oricare dintre acestea poate fi îndeplinită
• BETWEEN - Verifică dacă valoarea se află în intervalul specificat
• LIKE - caută anumite modele

De exemplu, dacă vrem să selectăm albume cu popși suflet muzica, putem folosi IN("value1","value2") .

SELECTAȚI * DIN albumele WHERE genre IN ("pop","soul");

Dacă vrem să obținem toate albumele lansate între 1975 și 1985, am scrie:

SELECTAȚI * DIN albumele WHERE lansate ÎNTRE 1975 ȘI 1985;

7. Funcții

SQL este plin de funcții care fac tot felul de lucruri utile. Iată câteva dintre cele mai frecvent utilizate:

• COUNT() - returnează numărul de rânduri
• SUM() - returnează suma totală a unei coloane numerice
• AVG() - returnează valoarea medie dintr-un set de valori
• MIN() / MAX() - Obține valoarea minimă / maximă dintr-o coloană

Pentru a obține cel mai recent an în tabelul nostru, trebuie să scriem următoarea interogare SQL:

SELECT MAX (lansat) FROM albume;

8. Subinterogări

În paragraful anterior, am învățat cum să facem calcule simple cu date. Dacă dorim să folosim rezultatul din aceste calcule, nu ne putem lipsi de interogări imbricate. Să presupunem că vrem să ieșim artist, albumși anul lansării pentru cel mai vechi album din tabel.

Știm cum să obținem aceste coloane specifice:

SELECT artist, album, lansat DIN albume;

De asemenea, știm cum să obținem cel mai devreme an:

SELECT MIN (lansat) FROM album;

Tot ce trebuie acum este să combinați cele două interogări cu un WHERE:

SELECT artist,album,released FROM albums WHERE lansat = (SELECT MIN(released) FROM albums);

9. Îmbinarea tabelelor

În bazele de date mai complexe, există mai multe tabele care sunt legate între ele. De exemplu, mai jos sunt două tabele despre jocuri video ( jocuri video) și dezvoltatori de jocuri video ( game_developers).

Masa jocuri video există o coloană pentru dezvoltatori ( developer_id), dar conține un număr întreg, nu numele dezvoltatorului. Acest număr este un identificator ( id) al dezvoltatorului corespunzător din tabelul dezvoltatorilor de jocuri ( game_developers) legând cele două liste în mod logic, permițându-ne să folosim informațiile stocate în ambele în același timp.

Dacă vrem să creăm o interogare care să returneze tot ce trebuie să știm despre jocuri, putem folosi un INNER JOIN pentru a lega coloanele din ambele tabele.

SELECT video_games.name, video_games.genre, game_developers.name, game_developers.country FROM video_games INNER JOIN game_developers ON video_games.developer_id = game_developers.id;

Acesta este cel mai simplu și mai comun tip JOIN. Există mai multe alte opțiuni, dar se aplică cazurilor mai puțin frecvente.

10. Aliasuri

Dacă te uiți la exemplul anterior, vei observa că există două coloane numite Nume. Acest lucru este confuz, așa că să setăm un alias la una dintre coloanele repetate, de exemplu, Nume de la masă game_developers va fi chemat dezvoltator.

De asemenea, putem scurta interogarea prin aliasarea numelor tabelelor: jocuri video Hai sa sunăm jocuri, game_developers - dezvoltatori:

SELECT games.name, games.genre, devs.name AS dezvoltator, devs.country FROM video_games AS jocuri INNER JOIN game_developers AS devs ON games.developer_id = devs.id;

11. Actualizarea datelor

Adesea trebuie să schimbăm datele pe unele rânduri. În SQL, acest lucru se face cu instrucțiunea UPDATE. Declarația UPDATE constă în:

• Tabelul care conține valoarea de înlocuire;
• Numele coloanelor și noile lor valori;
• Rândurile selectate cu WHERE pe care dorim să le actualizăm. Dacă acest lucru nu se face, atunci toate rândurile din tabel se vor schimba.

Mai jos este tabelul seriale TV cu serii cu ratingul lor. Cu toate acestea, o mică eroare s-a strecurat în tabel: deși seria Urzeala tronurilorși este descrisă ca o comedie, chiar nu este. Să reparăm asta!

12. Ștergerea datelor

Ștergerea unui rând de tabel cu SQL este un proces foarte simplu. Tot ce trebuie să faceți este să selectați tabelul și rândul pe care doriți să le ștergeți. Să eliminăm ultimul rând din tabel din exemplul anterior seriale TV. Acest lucru se face folosind instrucțiunea >DELETE.

DELETE FROM tv_series WHERE id = 4

Aveți grijă când scrieți instrucțiunea DELETE și asigurați-vă că este prezentă clauza WHERE, altfel toate rândurile tabelului vor fi șterse!

13. Ștergerea unui tabel

Dacă vrem să eliminăm toate rândurile, dar să părăsim tabelul în sine, atunci folosiți comanda TRUNCATE:

TRUNCATE TABLE nume_tabel;

În cazul în care dorim cu adevărat să ștergem atât datele, cât și tabelul în sine, atunci comanda DROP va fi utilă:

DROP TABLE nume_tabel;

Fii foarte atent cu aceste comenzi. Nu pot fi anulate!/p>

Aceasta încheie tutorialul nostru SQL! Nu am acoperit prea multe, dar ceea ce știți deja ar trebui să fie suficient pentru a vă oferi câteva abilități practice în cariera dvs. web.

Interogările sunt scrise fără ghilimele de escape, deoarece MySQL, MS SQLși PostGree sunt diferite.

Interogare SQL: obținerea câmpurilor specificate (obligatorii) din tabel

Primim o listă de înregistrări: TOATE țările și populația lor. Numele câmpurilor obligatorii sunt separate prin virgule.

SELECT * FROM table_name

* denotă toate câmpurile. Adică vor fi afișaje TOT câmpuri de date.

Interogare SQL: afișarea înregistrărilor dintr-un tabel, excluzând duplicatele

Primim o listă de înregistrări: țările în care se află utilizatorii noștri. Pot exista mulți utilizatori dintr-o singură țară. În acest caz, aceasta este cererea dvs.

Interogare SQL: afișarea înregistrărilor dintr-un tabel conform unei anumite condiții

Primim o listă de înregistrări: țări în care numărul de persoane este mai mare de 100.000.000.

Interogare SQL: afișarea înregistrărilor dintr-un tabel cu ordonare

Primim o listă de înregistrări: orașe în ordine alfabetică. A la început, Z la sfârșit.

SELECT ID, city_title FROM table_name ORDER BY city_title DESC

Obținem o listă de înregistrări: orașe în sens invers ( DESC) e bine. La început I, la sfârșit A.

Interogare SQL: numărarea numărului de înregistrări

Obținem numărul (numărul) de înregistrări din tabel. În acest caz, NU există o listă de intrări.

Interogare SQL: afișarea intervalului dorit de înregistrări

Obținem 2 (a doua) și 3 (a treia) înregistrări din tabel. Interogarea este utilă atunci când se creează navigarea pe pagini WEB.

Interogări SQL cu condiții

Afișarea înregistrărilor dintr-un tabel în funcție de o condiție dată folosind operatori logici.

Interogare SQL: construcție ȘI (ȘI)

Obținerea unei liste de înregistrări: orașe din Rusia Și au acces la petrol. Când se folosește operatorul? ȘI, atunci ambele condiții trebuie să se potrivească.

Interogare SQL: construcție SAU (OR)

Obțineți o listă de înregistrări: toate orașele din Rusia SAU STATELE UNITE ALE AMERICII. Când se folosește operatorul? SAU, atunci cel puțin o condiție trebuie să se potrivească.

Interogare SQL: AND NOT construit (ȘI NU)

Obțineți o listă de înregistrări: toți utilizatorii din Rusia Și făcut NU MAI PUȚIN 7 comentarii.

Interogare SQL: construcție IN (B)

Primim o listă de înregistrări: toți utilizatorii care locuiesc în ( ÎN) (Rusia, sau Bulgaria sau China)

Interogare SQL: construcție NOT IN (NU IN)

Primim o listă de înregistrări: toți utilizatorii care nu locuiesc în ( NU ÎN) (Rusia sau China).

Interogare SQL: construcție IS NULL (valori goale sau NOT goale)

Primim o listă de înregistrări: toți utilizatorii pentru care starea nu este definită. NULL este un subiect separat și, prin urmare, este verificat separat.

SELECT ID, user_login FROM table_name WHERE starea NU ESTE NULL

Obținem o listă de înregistrări: toți utilizatorii în care starea este definită (NU ZERO).

Interogare SQL: construct LIKE

Primim o listă de înregistrări: utilizatori al căror nume de familie începe cu combinația „Ivan”. Semnul % înseamnă ORICE număr de ORICE caractere. Pentru a găsi semnul %, trebuie să utilizați scăparea „Ivan\%”.

Interogare SQL: BETWEEN construct

Primim o listă de înregistrări: utilizatori care primesc un salariu de la 25.000 la 50.000 inclusiv.

Există FOARTE mulți operatori logici, așa că studiați documentația serverului SQL în detaliu.

Interogări SQL complexe

Interogare SQL: uniți mai multe interogări

Primim o listă de intrări: utilizatori care sunt înregistrați în sistem, precum și acei utilizatori care sunt înregistrați separat pe forum. Operatorul UNION poate combina mai multe interogări. UNION acționează ca SELECT DISTINCT, adică elimină valorile duplicat. Pentru a obține absolut toate înregistrările, trebuie să utilizați operatorul UNION ALL.

Interogare SQL: calculul valorilor câmpului MAX, MIN, SUM, AVG, COUNT

Ieșirea unuia, valoarea maximă a contorului din tabel:

Ieșirea unuia, valoarea minimă a contorului din tabel:

Afișarea sumei tuturor valorilor contorului în tabel:

Afișarea valorii medii a contorului în tabel:

Afișarea numărului de contoare din tabel:

Concluzia numărului de contoare din magazinul nr. 1, în tabel:

Acestea sunt cele mai populare comenzi. Se recomandă, acolo unde este posibil, utilizarea interogărilor SQL de acest fel pentru calcul, deoarece niciun mediu de programare nu poate fi comparat în viteza de procesare a datelor decât serverul SQL însuși atunci când procesează propriile date.

Interogare SQL: gruparea înregistrărilor

Primim o listă de înregistrări: cu numele continentului și cu suma zonelor tuturor țărilor lor. Adică, dacă există un director cu țările în care fiecare țară are aria sa înregistrată, atunci folosind construcția GROUP BY, puteți afla dimensiunea fiecărui continent (pe baza grupării pe continente).

Interogare SQL: utilizarea mai multor tabele prin alias (alias)

Primim o listă de înregistrări: comenzi de la cumpărători care locuiesc numai în Tyumen.

De fapt, cu o bază de date proiectată corespunzător de acest tip, interogarea este cea mai frecventă, așa că MySQL a introdus un operator special care funcționează de multe ori mai rapid decât codul scris mai sus.

SELECT o.order_no, o.amount_paid, z.company FROM comenzi AS o LEFT JOIN client AS z ON (z.custno=o.custno)

Subinterogări imbricate

Obținem o singură înregistrare: informații despre utilizatorul cu salariul maxim.

Atenţie! Subinterogările imbricate sunt unul dintre blocajele din serverele SQL. Împreună cu flexibilitatea și puterea lor, ele măresc semnificativ încărcarea serverului. Ceea ce duce la o încetinire catastrofală pentru alți utilizatori. Există cazuri foarte frecvente de apeluri recursive cu interogări imbricate. Prin urmare, recomand insistent să NU folosiți interogări imbricate, ci să le împărțiți în altele mai mici. Sau utilizați combinația LEFT JOIN descrisă mai sus. În plus față de acest tip de solicitări, acestea reprezintă un accent sporit al breșelor de securitate. Dacă decideți să utilizați subinterogări imbricate, atunci trebuie să le proiectați foarte atent și să faceți rulări inițiale pe copiile bazei de date (baze de date de testare).

Interogările SQL modifică datele

Interogare SQL: INSERT

Instruire INTRODUCE vă permit să introduceți înregistrări în tabel. Cu cuvinte simple, creați o linie cu date într-un tabel.

Opțiunea numărul 1. Instrucțiuni des folosite:

In masa " table_name» va insera 2 (doi) utilizatori simultan.

Opțiunea numărul 2. Este mai convenabil să folosești stilul:

Acest lucru are avantajele și dezavantajele sale.

Principalele dezavantaje:

• Multe interogări SQL mici sunt puțin mai lente decât o interogare SQL mare, dar alte interogări vor fi puse în coadă pentru service. Adică, dacă o interogare SQL mare rulează timp de 30 de minute, atunci în tot acest timp restul interogărilor vor fuma bambus și vor aștepta rândul lor.
• Solicitarea este mai masivă decât versiunea anterioară.

Principalele avantaje:

• În timpul interogărilor SQL mici, alte interogări SQL nu sunt blocate.
• Ușurință de citit.
• Flexibilitate. În această opțiune, nu puteți urmări structura, ci puteți adăuga doar datele necesare.
• Atunci când creați arhive în acest fel, puteți copia cu ușurință o linie și o puteți rula prin linia de comandă (consola), astfel încât să nu restabiliți întreaga ARHIVĂ.
• Stilul de scriere este similar cu declarația UPDATE, care este mai ușor de reținut.

Interogare SQL: UPDATE

In masa " table_name» în înregistrarea cu numărul id=1, valorile câmpurilor user_login și user_surname vor fi modificate la valorile specificate.

Interogare SQL: DELETE

Înregistrarea cu numărul de identificare 3 va fi ștearsă din tabelul table_name.

1. Se recomandă ca toate numele câmpurilor să fie scrise cu litere mici și, dacă este necesar, separate printr-un spațiu forțat „_” pentru compatibilitate cu diferite limbaje de programare precum Delphi, Perl, Python și Ruby.
2. Comenzile SQL sunt scrise cu majuscule pentru a fi lizibile. Amintiți-vă întotdeauna că alte persoane pot citi codul după tine, dar cel mai probabil tu însuți după N perioadă de timp.
3. Numiți câmpurile mai întâi cu un substantiv și apoi cu o acțiune. De exemplu: city_status, user_login, user_name.
4. Încercați să evitați cuvintele rezervate în diferite limbi care pot cauza probleme în SQL, PHP sau Perl, cum ar fi (nume, număr, link). De exemplu: linkul poate fi folosit în MS SQL, dar este rezervat în MySQL.

Acest material este o referință scurtă pentru munca de zi cu zi și nu pretinde a fi o sursă super mega autorizată, care este sursa primară de interogări SQL ale unei anumite baze de date.

• Traducere
• tutorial

Ar trebui să fie „ SELECT * WHERE a=b FROM c ” sau „ SELECT WHERE a=b FROM c ON * ”?

Dacă sunteți ca mine, veți fi de acord: SQL este unul dintre acele lucruri care par ușor la prima vedere (se citește ca în engleză!), dar cumva trebuie să căutați pe google fiecare interogare simplă pentru a găsi sintaxa corectă.

Și apoi încep alăturarea, agregarea, subinterogările și se dovedește a fi complet un gunoi. Ca aceasta:

Bue! Acest lucru va speria orice începător sau chiar un dezvoltator mediu dacă vede SQL pentru prima dată. Dar nu este chiar atât de rău.

Este ușor să ne amintim ce este intuitiv și, cu acest ghid, sper să scad bariera de intrare în SQL pentru începători și să ofer SQL experimentat un nou aspect.

Deși sintaxa SQL este aproape aceeași în diferite baze de date, acest articol folosește PostgreSQL pentru interogări. Câteva exemple vor funcționa în MySQL și alte baze de date.

1. Trei cuvinte magice

Există multe cuvinte cheie în SQL, dar SELECT , FROM și WHERE sunt prezente în aproape fiecare interogare. Puțin mai târziu, îți vei da seama că aceste trei cuvinte reprezintă cele mai fundamentale aspecte ale construirii interogărilor bazei de date, iar alte interogări, mai complexe, sunt doar suplimente pe deasupra lor.

2. Baza noastră

Să aruncăm o privire la baza de date pe care o vom folosi ca exemplu în acest articol:

Avem o bibliotecă de cărți și oameni. Există, de asemenea, un tabel special pentru cărțile emise de contabilitate.

• Tabelul „cărți” stochează informații despre titlu, autor, data publicării și disponibilitatea cărții. Totul este simplu.
• În tabelul „membri” - numele și prenumele tuturor persoanelor care s-au înscris la bibliotecă.
• Tabelul „împrumuturi” stochează informații despre cărțile împrumutate de la bibliotecă. Coloana bookid se referă la id-ul cărții împrumutate din tabelul „cărți”, iar coloana memberid se referă la persoana corespunzătoare din tabelul „membri”. Avem și o dată de emitere și o dată la care trebuie returnată cartea.

3. Cerere simplă

Să începem cu o cerere simplă: avem nevoie numeși identificatori(id) toate cărțile scrise de autorul „Dan Brown”

Cererea va fi astfel:

Iar rezultatul este cam asa:

Destul de simplu. Să defalcăm cererea pentru a înțelege ce se întâmplă.

3.1 FROM - de unde obținem datele

Acest lucru poate părea evident acum, dar FROM va fi foarte important mai târziu, când ajungem la alăturari și subinterogări.

FROM puncte către tabel pentru a interoga. Acesta poate fi un tabel deja existent (ca în exemplul de mai sus) sau un tabel creat din mers prin îmbinări sau subinterogări.

3.2 UNDE - ce date trebuie afișate

WHERE pur și simplu se comportă ca un filtru linii pe care vrem să le afișăm. În cazul nostru, vrem să vedem doar rânduri în care valoarea din coloana autor este „Dan Brown”.

3.3 SELECT - cum se afișează datele

Acum că avem toate coloanele de care avem nevoie din tabelul de care avem nevoie, trebuie să decidem cum să arătăm exact aceste date. În cazul nostru, avem nevoie doar de titluri și ID-uri de cărți, așa că asta facem. alege cu SELECT . În același timp, puteți redenumi coloana folosind AS .

Întreaga interogare poate fi vizualizată cu o diagramă simplă:

4. Conexiuni (uniuni)

Acum vrem să vedem titlurile (nu neapărat unice) ale tuturor cărților lui Dan Brown care au fost împrumutate de la bibliotecă și când acele cărți trebuie returnate:

Rezultat:

În cea mai mare parte, cererea este similară cu cea anterioară. cu exceptia DIN secțiunea. Înseamnă că interogăm date dintr-un alt tabel. Nu accesăm nici tabelul „cărți”, nici tabelul „împrumuturi”. În schimb, ne întoarcem la masa noua, care a fost creat prin unirea acestor două tabele.

împrumuturi JOIN books ON borrowings.bookid=books.bookid este un tabel nou care a fost format prin combinarea tuturor înregistrărilor din tabelele „cărți” și „împrumuturi” în care se potrivesc valorile bookid. Rezultatul unei astfel de fuziuni va fi:

Și apoi facem o interogare la acest tabel în același mod ca în exemplul de mai sus. Aceasta înseamnă că atunci când vă alăturați meselor, trebuie să vă faceți griji doar despre cum să efectuați această alăturare. Și atunci cererea devine la fel de clară ca în cazul „cererii simple” de la punctul 3.

Să încercăm o îmbinare puțin mai complexă cu două tabele.

Acum vrem să obținem numele și prenumele celor care au luat din bibliotecă cărțile autorului „Dan Brown”.

De data aceasta, să mergem de jos în sus:

Pasul 1- de unde luăm datele? Pentru a obține rezultatul pe care îl dorim, trebuie să unim tabelele „membru” și „cărți” cu tabelul „împrumuturi”. Secțiunea JOIN va arăta astfel:

Rezultatul conexiunii poate fi văzut la link.

Pasul 2 Ce date arătăm? Ne interesează doar datele în care autorul cărții este „Dan Brown”

Pasul 3 Cum afișăm datele? Acum că datele au fost primite, trebuie doar să afișați numele și prenumele celor care au luat cărțile:

Super! Rămâne doar să combinăm cele trei componente și să facem cererea de care avem nevoie:

Ce ne va oferi:

Amenda! Dar numele se repetă (nu sunt unice). Vom rezolva acest lucru în curând.

5. Agregarea

Aproximativ vorbind, agregările sunt necesare pentru a converti mai multe șiruri într-unul singur. În același timp, în timpul agregării, este utilizată o logică diferită pentru diferite coloane.

Să continuăm cu exemplul nostru în care apar nume duplicate. Puteți vedea că Ellen Horton a împrumutat mai multe cărți, dar aceasta nu este cea mai bună modalitate de a arăta aceste informații. Puteți face o altă cerere:

Ceea ce ne va da rezultatul dorit:

Aproape toate agregările vin cu o clauză GROUP BY. Acest lucru transformă un tabel care ar putea fi obținut printr-o interogare în grupuri de tabele. Fiecare grup corespunde unei valori unice (sau unui grup de valori) a coloanei pe care am specificat-o în GROUP BY . În exemplul nostru, convertim rezultatul exercițiului anterior într-un grup de șiruri. Facem și o agregare cu count , care convertește mai multe rânduri într-o valoare întreagă (în cazul nostru, numărul de rânduri). Această valoare este apoi atribuită fiecărui grup.

Fiecare rând din rezultat este rezultatul agregării fiecărui grup.

Puteți ajunge la concluzia logică că toate câmpurile din rezultat trebuie fie specificate în GROUP BY, fie agregarea trebuie efectuată asupra lor. Deoarece toate celelalte câmpuri pot diferi unele de altele în rânduri diferite, iar dacă le selectați cu SELECT "th, nu este clar care dintre valorile posibile ar trebui luată.

În exemplul de mai sus, funcția de numărare a procesat toate rândurile (din moment ce număram numărul de rânduri). Alte funcții precum suma sau max procesează numai rândurile specificate. De exemplu, dacă vrem să știm numărul de cărți scrise de fiecare autor, atunci avem nevoie de această interogare:

Rezultat:

Aici funcția sumă procesează doar coloana stoc și calculează suma tuturor valorilor din fiecare grup.

6. Subinterogări

Subinterogările sunt interogări SQL normale încorporate în interogări mai mari. Acestea sunt împărțite în trei tipuri în funcție de tipul rezultatului returnat.

6.1 Tabel bidimensional

Există interogări care returnează mai multe coloane. Un bun exemplu este interogarea din exercițiul anterior de agregare. Fiind o subinterogare, va returna pur și simplu un alt tabel pe care se pot face noi interogări. Continuând exercițiul anterior, dacă dorim să aflăm numărul de cărți scrise de autorul „Robin Sharma”, atunci una dintre modalitățile posibile este utilizarea subinterogărilor:

Rezultat:

Poate fi scris ca: ["Robin Sharma", "Dan Brown"]

2. Acum utilizați acest rezultat într-o nouă interogare:

Rezultat:

Este la fel ca:

6.3 Valori individuale

Există interogări care au ca rezultat un singur rând și o coloană. Ele pot fi tratate ca valori constante și pot fi folosite oriunde sunt utilizate valori, cum ar fi în operatorii de comparație. Ele pot fi, de asemenea, folosite ca tabele bidimensionale sau matrice cu un singur element.

Să obținem, de exemplu, informații despre toate cărțile, al căror număr din bibliotecă depășește valoarea medie în acest moment.

Media se poate obține astfel:

Ce ne oferă:

7. Operații de scriere

Majoritatea operațiunilor de scriere a bazei de date sunt destul de simple în comparație cu operațiunile de citire mai complexe.

7.1 Actualizare

Sintaxa unei cereri UPDATE este semantic aceeași cu o solicitare de citire. Singura diferență este că, în loc să selectăm coloanele cu SELECT „th, setăm cunoștințele cu SET „th.

Dacă toate cărțile lui Dan Brown sunt pierdute, atunci trebuie să resetați valoarea cantității. Interogarea pentru aceasta ar fi:

WHERE face același lucru ca înainte: selectează rândurile. În loc de SELECT , care a fost folosit la citire, acum folosim SET . Cu toate acestea, acum trebuie să specificați nu numai numele coloanei, ci și noua valoare pentru această coloană în rândurile selectate.

7.2 Ștergeți

O interogare DELETE este doar o interogare SELECT sau UPDATE fără nume de coloană. Serios. Ca și în cazul SELECT și UPDATE , clauza WHERE rămâne aceeași: selectează rândurile de șters. Operația de ștergere distruge întregul rând, așa că nu are sens să specificați coloane individuale. Deci, dacă decidem să nu resetam numărul de cărți ale lui Dan Brown, ci să ștergem toate intrările în general, atunci putem face o cerere ca aceasta:

7.3 Inserare

Poate singurul lucru care diferă de alte tipuri de interogări este INSERT. Formatul este:

Unde a, b, c sunt numele coloanelor și x, y și z sunt valorile care trebuie inserate în acele coloane, în această ordine. Practic asta este.

Să ne uităm la un exemplu concret. Iată o interogare INSERT care populează întregul tabel „cărți”:

8. Verificare

Am ajuns la final, vă propun un mic test. Priviți această solicitare chiar la începutul articolului. Poți să-ți dai seama? Încercați să o împărțiți în secțiuni SELECT , FROM , WHERE , GROUP BY și uitați-vă la componentele individuale ale subinterogării.

Iată-l într-o formă mai lizibilă:

Această interogare returnează o listă a persoanelor care au împrumutat o carte din bibliotecă care are un total peste medie.

Rezultat:

Sper că ai reușit să-ți dai seama fără probleme. Dar dacă nu, atunci mă voi bucura de comentariile și feedback-ul dumneavoastră, astfel încât să pot îmbunătăți această postare.

Etichete: Adăugați etichete

Expresii de tabel numite subinterogări, care sunt folosite acolo unde se așteaptă un tabel. Există două tipuri de expresii de tabel:

tabele derivate;

expresii de tabel generalizate.

Aceste două forme de expresii de tabel sunt discutate în următoarele subsecțiuni.

Tabele derivate

Tabel derivat este o expresie de tabel inclusă în clauza FROM a unei interogări. Tabelele derivate pot fi utilizate atunci când nu este posibilă utilizarea aliasurilor de coloană deoarece traducătorul SQL procesează o altă instrucțiune înainte ca aliasul să fie cunoscut. Exemplul de mai jos arată o încercare de a utiliza un alias de coloană într-o situație în care o altă clauză este procesată înainte ca aliasul să fie cunoscut:

UTILIZAȚI SampleDb; SELECTARE MONTH(EnterDate) ca enter_month FROM Works_on GROUP BY enter_month;

Încercarea de a executa această interogare va returna următorul mesaj de eroare:

Mesajul 207, Nivel 16, Stare 1, Rând 5 Nume de coloană invalid „enter_month”. (Mesajul 207: Nivelul 16, Stare 1, Linia 5 Nume de coloană nevalid enter_month)

Motivul erorii este că clauza GROUP BY este procesată înainte ca lista corespunzătoare a instrucțiunii SELECT să fie procesată, iar alias-ul coloanei enter_month nu este cunoscut când grupul este procesat.

Această problemă poate fi rezolvată folosind o vizualizare care conține interogarea anterioară (fără clauza GROUP BY), deoarece clauza FROM este executată înaintea clauzei GROUP BY:

UTILIZAȚI SampleDb; SELECT enter_month FROM (SELECT MONTH(EnterDate) as enter_month FROM Works_on) AS m GROUP BY enter_month;

Rezultatul acestei interogări va fi astfel:

De obicei, o expresie de tabel poate fi plasată oriunde într-o instrucțiune SELECT unde ar putea apărea numele tabelului. (Rezultatul unei expresii de tabel este întotdeauna un tabel sau, în cazuri speciale, o expresie.) Următorul exemplu arată utilizarea unei expresii de tabel în lista select a unei instrucțiuni SELECT:

Rezultatul acestei interogări este:

Expresii de tabel generice

Expresie comună de tabel (OTB) (CTE pe scurt) este o expresie de tabel cu nume acceptată de limbajul Transact-SQL. Expresiile comune de tabel sunt utilizate în următoarele două tipuri de interogări:

nerecursiv;

recursiv.

Aceste două tipuri de solicitări sunt discutate în secțiunile următoare.

OTB și interogări nerecursive

Forma nerecursivă a OTB poate fi utilizată ca alternativă la tabelele și vizualizările derivate. De obicei, OTB este definit de CU clauzeși o interogare suplimentară care se referă la numele folosit în clauza WITH. În Transact-SQL, semnificația cuvântului cheie WITH este ambiguă. Pentru a evita ambiguitatea, instrucțiunea care precede instrucțiunea WITH trebuie terminată cu punct și virgulă.

UTILIZAȚI AdventureWorks2012; SELECTează SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") AND Freight > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader "Order05YEAR" =(OrderDate05) ")/2,5;

Interogarea din acest exemplu selectează comenzi ale căror taxe totale (TotalDue) sunt mai mari decât media tuturor taxelor și ale căror taxe de transport (Freight) sunt mai mari de 40% din taxele medii. Proprietatea principală a acestei interogări este volumul său, deoarece interogarea imbricată trebuie scrisă de două ori. O modalitate posibilă de a reduce domeniul de aplicare al constructului de interogare ar fi crearea unei vizualizări care conține o subinterogare. Dar această soluție este puțin complicată, deoarece necesită crearea vizualizării și apoi ștearsă după terminarea interogării. Cea mai bună abordare ar fi crearea unui OTB. Exemplul de mai jos arată utilizarea OTB non-recursivă, care scurtează definiția interogării de mai sus:

UTILIZAȚI AdventureWorks2012; WITH price_calc(year_2005) AS (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT year_2005 FROM price >_calc)_2 FROM price) (SELECT) /2,5;

Sintaxa clauzei WITH în interogările nerecursive este următoarea:

Parametrul cte_name este numele OTB care definește tabelul rezultat, iar parametrul column_list este lista de coloane din expresia tabelului. (În exemplul de mai sus, OTB este numit price_calc și are o coloană, year_2005.) Parametrul inner_query reprezintă o instrucțiune SELECT care specifică setul de rezultate al expresiei de tabel corespunzătoare. Expresia de tabel definită poate fi apoi utilizată în interogarea_exterioară. (Interogarea exterioară din exemplul de mai sus utilizează OTB price_calc și coloana sa year_2005 pentru a simplifica interogarea dublu imbricată.)

OTB și interogări recursive

Această secțiune prezintă material de complexitate crescută. Prin urmare, atunci când îl citiți pentru prima dată, este recomandat să îl săriți peste el și să reveniți la el mai târziu. OTB-urile pot fi recursive deoarece OTB-urile pot conține referințe la ei înșiși. Sintaxa de bază OTB pentru o interogare recursivă arată astfel:

Parametrii cte_name și column_list au aceeași semnificație ca în OTB pentru interogările nerecursive. Corpul clauzei WITH este format din două interogări unite prin instrucțiune UNIREA TOȚI. Prima interogare este apelată o singură dată și începe să acumuleze rezultatul recursiunii. Primul operand al operatorului UNION ALL nu se referă la un OTB. Această interogare se numește interogare de referință sau sursă.

A doua interogare conține un link către OTB și reprezintă partea sa recursivă. Din această cauză, se numește membru recursiv. În primul apel către partea recursivă, referința OTB reprezintă rezultatul interogării de referință. Membrul recursiv folosește rezultatul primului apel la interogare. După aceea, sistemul apelează din nou partea recursivă. Un apel către un membru recursiv se termină atunci când un apel anterior la acesta returnează un set de rezultate gol.

Operatorul UNION ALL se alătură rândurilor acumulate până acum, precum și rândurilor suplimentare adăugate de apelul curent membrului recursiv. (Prezența operatorului UNION ALL înseamnă că rândurile duplicate nu vor fi eliminate din rezultat.)

În cele din urmă, parametrul outer_query definește interogarea exterioară pe care OTB o folosește pentru a obține toate apelurile către uniunea ambilor membri.

Pentru a demonstra forma recursivă a OTB, folosim un tabel Avion definit și populat cu codul prezentat în exemplul de mai jos:

UTILIZAȚI SampleDb; CREATE TABLE Avion (ContainingAssembly VARCHAR(10), ContainedAssembly VARCHAR(10), QuantityContained INT, UnitCost DECIMAL(6,2)); INSERT INTO Airplane VALUES ("Avion", "Fuselaj", 1, 10); INSERT INTO Airplane VALUES ("Avion", "Aripi", 1, 11); INSERT INTO Airplane VALUES ("Avion", "Coada", 1, 12); INSERT INTO Airplane VALUES („Fuselaj”, „Salon”, 1, 13); INSERT INTO Airplane VALUES ("Fuselaj", "Cockpit", 1, 14); INSERT INTO Airplane VALUES („Fuselaj”, „Nas”, 1, 15); INSERT INTO Airplane VALUES(„Salon”, NULL, 1,13); INSERT INTO Airplane VALUES(„cabină”, NULL, 1, 14); INSERT INTO Airplane VALUES(„Nas”, NULL, 1, 15); INSERT INTO Airplane VALUES(„Aripi”, NULL,2, 11); INSERT INTO Airplane VALUES(„Coada”, NULL, 1, 12);

Tabelul Avion are patru coloane. Coloana ContainingAssembly definește ansamblul, iar coloana ContainedAssembly definește părțile (una câte una) care alcătuiesc ansamblul corespunzător. Figura de mai jos prezintă o ilustrare grafică a unui posibil tip de aeronavă și a părților sale constitutive:

Tabelul Avion este format din următoarele 11 rânduri:

Următorul exemplu utilizează clauza WITH pentru a defini o interogare care calculează costul total al fiecărei build:

UTILIZAȚI SampleDb; WITH list_of_parts(assembly1, amount, cost) AS (SELECT ContainingAssembly, QuantityContained, UnitCost FROM Airplane WHERE ContainedAssembly IS NULL UNION ALL SELECT a.ContainingAssembly, a.QuantityContained, CAST(l.cantity * l.cost AS DECIMAL) ) FROM list_of_pieces l, Avion a WHERE l.assembly1 = a.ContainedAssembly) SELECT assembly1 "Parte", cantitatea "Cantitate", cost "Preț" FROM list_of_pieces;

Clauza WITH definește o listă OTB numită list_of_parts, constând din trei coloane: asamblare1, cantitate și cost. Prima instrucțiune SELECT din exemplu este apelată o singură dată pentru a stoca rezultatele primului pas al procesului recursiv. Instrucțiunea SELECT de pe ultima linie a exemplului afișează următorul rezultat.