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

data si ora

Tip de date	Descriere
DATA	Valori date
DATETIME	Valori date și ore cu precizie minute
TIMP	Valori de timp

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. O modalitate mai sigură (și în același timp mai greoaie) 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.

SELECT * FROM albume WHERE gen = „rock” AND sales_in_millions<= 50 ORDER BY released

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!

tv_series date table UPDATE tv_series SET gen = "drama" WHERE id = 2;

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 multe, dar ceea ce știți deja ar trebui să fie suficient pentru a vă oferi niște abilități practice în cariera dvs. web.

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

GROUP BY oferta este folosit pentru a defini grupuri de linii de ieșire cărora li se pot aplica funcții agregate (COUNT, MIN, MAX, AVG și SUM). Dacă această clauză lipsește și sunt utilizate funcții de agregare, atunci toate coloanele cu numele menționate în SELECTAȚI, ar trebui incluse în funcții agregate, iar aceste funcții se vor aplica întregului set de rânduri care satisfac predicatul de interogare. În caz contrar, toate coloanele din lista SELECT, nu este inclusîn funcții agregate, trebuie specificate în clauza GROUP BY. Ca rezultat, toate rândurile de ieșire ale interogării sunt împărțite în grupuri caracterizate prin aceleași combinații de valori în aceste coloane. După aceea, se vor aplica funcții agregate fiecărui grup. Rețineți că pentru GROUP BY, toate valorile NULL sunt tratate ca egale, adică. la gruparea după un câmp care conține valori NULL, toate aceste rânduri se vor încadra într-un singur grup.
În cazul în care un dacă există o clauză GROUP BY, în clauza SELECT fără funcții agregate, atunci interogarea va returna pur și simplu un rând din fiecare grup. Această caracteristică, împreună cu cuvântul cheie DISTINCT, poate fi utilizată pentru a elimina rândurile duplicate dintr-un set de rezultate.
Luați în considerare un exemplu simplu:

SELECT model, COUNT(model) AS Cantitate_model, AVG(preț) AS Avg_price DE LA PC GROUP BY model;

În această interogare, pentru fiecare model de PC, sunt determinate numărul și costul mediu al acestora. Toate rândurile cu aceeași valoare de model (număr de model) formează un grup, iar ieșirea SELECT calculează numărul de valori și valorile medii ale prețului pentru fiecare grup. Rezultatul interogării va fi următorul tabel:

model	Cantitate_model	Avg_price
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

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.

Sintaxă:

* Unde câmpuri 1- câmpuri pentru selecție separate prin virgule, puteți specifica și toate câmpurile cu *; masa- numele tabelului din care scoatem datele; conditii— condiții de prelevare; câmpuri 2— câmp sau câmpuri, separate prin virgule, după care să sortați; numara— numărul de rânduri de descărcat.
* interogarea între paranteze drepte este opțională pentru preluarea datelor.

Cazuri de utilizare simple pentru selectare

1. Eșantionarea normală a datelor:

> SELECTAȚI * FROM utilizatori

2. Selectarea datelor cu unirea a două tabele (JOIN):

SELECTAȚI u.name, r.* FROM users u JOIN users_rights r ON r.user_id=u.id

* în acest exemplu, datele sunt selectate cu tabele de unire utilizatoriiși drepturile_utilizatorilor. Sunt grupate pe câmpuri. ID-ul de utilizator(în tabelul user_rights) și id(utilizatori). Câmpul de nume este preluat din primul tabel și toate câmpurile din al doilea.

3. Eșantionarea cu un interval de timp și/sau dată

a) se cunosc punctul de plecare și un anumit interval de timp:

* vor fi selectate datele pentru ultima oră (câmp Data).

b) se cunosc data de început și data de încheiere:

25.10.2017 și 25.11.2017 .

c) se cunosc datele de început și de sfârșit + ora:

* selectați datele între 25.03.2018 0 ore 15 minuteși 25.04.2018 15 ore 33 minute și 9 secunde.

d) extrageți date pentru o anumită lună și an:

* extrageți datele unde se află în câmp Data există valori pentru aprilie 2018 al anului.

4. Eșantionarea valorii maxime, minime și medii:

> SELECTAȚI max(zonă), min(zonă), medie(zonă) FROM țară

* max- valoare maximă; min- minim; medie- media.

5. Folosind lungimea șirului:

* această interogare ar trebui să arate toți utilizatorii al căror nume este format din 5 caractere.

Exemple de interogări mai complexe sau utilizate rar

1. Îmbinarea cu gruparea datelor selectate într-o singură linie (GROUP_CONCAT):

* de la masă utilizatorii preluarea datelor după câmp id, toate se încadrează pe o singură linie, valorile sunt separate virgule.

2. Gruparea datelor după două sau mai multe câmpuri:

> SELECTAȚI * FROM utilizatori GROUP BY CONCAT(titlu, „::”, naștere)

* total, în acest exemplu, vom descărca datele din tabelul utilizatori și le vom grupa pe câmpuri titluși naștere. Înainte de grupare, îmbinăm câmpurile într-o singură linie cu un separator :: .

3. Combinarea rezultatelor din două tabele (UNION):

> (SELECT ID, fio, adresa, „Utilizatori” ca tip FROM utilizatori)
UNIUNE
(SELECT ID, fio, adresa, „Clienți” ca tip FROM clienți)

* în acest exemplu, datele sunt preluate din tabele utilizatoriiși Clienți.

4. Un eșantion de medii grupate pentru fiecare oră:

SELECTAȚI avg(temperature), DATE_FORMAT(datetimeupdate, "%Y-%m-%d %H") ca hour_datetime DIN arhiva GROUP BY DATE_FORMAT(datetimeupdate, "%Y-%m-%d %H")

* aici extragem valoarea medie a câmpului temperatura de la masă Arhivași grupați după domeniu actualizare datetime(cu împărțire în timp pentru fiecare oră).

Inserare (INSERT)

Sintaxa 1:

> INSERT INTO

() VALORI ( )

Sintaxa 2:

> INSERT INTO

VALORI ( )

* Unde masa- numele tabelului în care introducem datele; câmpuri- enumerarea câmpurilor separate prin virgule;valorile— enumerarea valorilor separate prin virgule.
* prima opțiune vă va permite să introduceți doar pe câmpurile listate - restul vor primi valori implicite. A doua opțiune va necesita inserarea pentru toate câmpurile.

inserați exemple

1. Inserați mai multe rânduri cu o singură interogare:

> INSEREȚI ÎN orașe („nume”, „țară”) VALORI („Moscova”, „Rusia”), („Paris”, „Franța”), („Funafuti”, „Tuvalu”);

* În acest exemplu, vom adăuga 3 înregistrări cu o interogare SQL.

2. Inserați dintr-un alt tabel (copierând rânduri, INSERT + SELECT):

* extrageți toate înregistrările din tabel orase, ale căror nume încep cu „M” și intră în tabel orase-nou.

Actualizare (UPDATE)

Sintaxă:

* Unde masa- numele tabelului; camp- câmpul pentru care vom modifica valoarea; valoare— valoare nouă; conditii- o condiție (este periculos să faci o actualizare fără ea - poți înlocui toate datele din întregul tabel).

Actualizați folosind înlocuirea (REPLACE):

ACTUALIZAȚI

A STABILIT = ÎNLOCUIRE( , "<что меняем>", "<на что>");

UPDATE orașe SET nume = REPLACE(nume, „Maskva”, „Moscova”);

Dacă vrem să fim în siguranță, rezultatul înlocuirii poate fi verificat mai întâi cu SELECT:

Ștergere (DELETE)

Sintaxă:

* Unde masa- numele tabelului; conditii- o condiție (ca și în cazul UPDATE, este periculos să folosiți DELETE fără o condiție - DBMS nu va cere confirmare, ci pur și simplu șterge toate datele).

Creați un tabel

Sintaxă:

> CREAȚI TABEL

( , )

> CREAȚI TABEL DACĂ NU EXISTĂ `users_rights` (
`id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`drepturi` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

* Unde masa- numele tabelului (în exemplu drepturile_utilizatorilor); câmpul 1, câmpul 2— numele câmpurilor (în exemplu, sunt create 3 câmpuri — id, user_id, drepturi); opțiuni1, opțiuni2— parametri de câmp (în exemplu int(10) unsigned NOT NULL); opțiunile de masă— parametri generali ai tabelului (în exemplu ENGINE=InnoDB DEFAULT CHARSET=utf8).

Utilizarea cererilor în PHP

Conectarea la baza de date:

mysql_connect ("localhost", "login", "parolă") sau die ("Eroare de conectare MySQL");
mysql_select_db("nume_db");
mysql_query("SETĂ NUMELE "utf8"");

* unde se face conexiunea la baza de date de pe serverul local ( gazdă locală); acreditări de conectare - Autentificareși parola(respectiv, autentificare și parolă); folosit ca bază db_name; codificarea folosită UTF-8.

De asemenea, puteți crea o conexiune persistentă:

mysql_pconnect ("localhost", "login", "parola") sau die ("Eroare de conectare MySQL");

*cu toate acestea, este posibil să atingeți limita maximă permisă de găzduire. Această metodă ar trebui folosită pe propriile noastre servere, unde noi înșine putem controla situația.

Terminați conexiunea:

* se face automat în PHP, cu excepția conexiunilor persistente (mysql_pconnect).

O interogare către MySQL (Mariadb) în PHP este efectuată de funcția mysql_query(), iar preluarea datelor dintr-o interogare este realizată de mysql_fetch_array():

$rezultat = mysql_query("SELECT * FROM utilizatori");
în timp ce ($mass = mysql_fetch_array($rezultat)) (
ecou $masa . "
";
}

* în acest exemplu, a fost făcută o interogare la tabel utilizatorii. Rezultatul interogării este plasat într-o variabilă $rezultat. Se utilizează următorul ciclu in timp ce, a cărui iterație extrage o matrice de date și o plasează într-o variabilă $masa- la fiecare iterație, lucrăm cu un rând al bazei de date.

Funcția mysql_fetch_array() folosită returnează un tablou asociativ, cu care este convenabil să lucrați, dar există o altă alternativă - mysql_fetch_row(), care returnează o matrice obișnuită numerotată.

Ecranarea

Dacă trebuie să includeți un caracter special în șirul de interogare, de exemplu, %, trebuie să utilizați evadarea cu caracterul backslash - \

De exemplu:

* dacă o astfel de interogare este executată fără evadare, semnul % va fi interpretat ca orice număr de caractere după 100.

Asta e tot. Dacă aveți nevoie de ajutor cu o solicitare, vă rugăm să-mi trimiteți un e-mail.

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 utilizarea aliasurilor de coloană nu este posibilă 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" =(OrderDate) ")/2,5;

Interogarea din acest exemplu selectează comenzi ale căror taxe totale (TotalDue) sunt mai mari decât media tuturor taxelor și al căror transport este mai mare 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 oarecum 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 care s-au acumulat 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.

SQL este folosit pentru a prelua date din baza de date. SQL este un limbaj de programare care seamănă foarte mult cu engleza, dar este destinat programelor de gestionare a bazelor de date. SQL este folosit în fiecare interogare din Access.

Înțelegerea modului în care funcționează SQL vă ajută să creați interogări mai precise și facilitează remedierea interogărilor care returnează rezultate incorecte.

Acest articol face parte din seria de articole SQL for Access. Descrie elementele de bază ale utilizării SQL pentru a prelua date și oferă exemple de sintaxă SQL.

În acest articol

Ce este SQL?

SQL este un limbaj de programare conceput pentru a lucra cu seturi de fapte și relațiile dintre ele. Programele de baze de date relaționale, cum ar fi Microsoft Office Access, folosesc SQL pentru a manipula datele. Spre deosebire de multe limbaje de programare, SQL este ușor de citit și de înțeles chiar și pentru începători. La fel ca multe limbaje de programare, SQL este un standard internațional recunoscut de comitetele de standarde precum ISO și ANSI.

Seturile de date sunt descrise în SQL pentru a ajuta la răspunsul la întrebări. Când utilizați SQL, trebuie utilizată sintaxa corectă. Sintaxa este un set de reguli care permit combinarea corectă a elementelor unei limbi. Sintaxa SQL se bazează pe sintaxa engleză și partajează multe elemente cu sintaxa limbajului Visual Basic pentru aplicații (VBA).

De exemplu, o instrucțiune SQL simplă care preia o listă cu numele de familie ale persoanelor de contact numite Mary ar putea arăta astfel:

SELECTAȚI Nume
DIN Contacte
WHERE Prenume = „Maria”;

Notă: Limbajul SQL este folosit nu numai pentru a efectua operații asupra datelor, ci și pentru a crea și modifica structura obiectelor bazei de date, cum ar fi tabelele. Partea din SQL care este folosită pentru a crea și modifica obiectele bazei de date se numește DDL. DDL nu este acoperit în acest articol. Pentru mai multe informații, consultați Crearea și modificarea tabelelor sau a indecșilor folosind o interogare de definire a datelor.

Instrucțiuni SELECT

Instrucțiunea SELECT este folosită pentru a descrie un set de date în SQL. Conține o descriere completă a setului de date care urmează să fie preluate din baza de date, inclusiv următoarele:

tabele care conțin date;

legături între date din diferite surse;

câmpuri sau calcule pe baza cărora sunt selectate datele;

condiții de selecție care trebuie îndeplinite de datele incluse în rezultatul interogării;

necesitatea si metoda de sortare.

Instrucțiuni SQL

O instrucțiune SQL constă din mai multe părți numite clauze. Fiecare clauză dintr-o instrucțiune SQL are un scop. Unele oferte sunt obligatorii. Tabelul de mai jos listează instrucțiunile SQL cele mai frecvent utilizate.

Clauza SQL	Descriere	Obligatoriu
	Definește câmpurile care conțin datele dorite.
	Definește tabelele care conțin câmpurile specificate în clauza SELECT.
	Definește criteriile de selecție a câmpurilor pe care trebuie să le îndeplinească toate înregistrările incluse în rezultate.
	Specifică ordinea de sortare a rezultatelor.
	Într-o instrucțiune SQL care conține funcții agregate, specifică câmpurile pentru care nu este calculată nicio valoare rezumativă în clauza SELECT.	Doar dacă există astfel de câmpuri
	Într-o instrucțiune SQL care conține funcții agregate, definește condițiile aplicate câmpurilor pentru care se calculează valoarea sumar în clauza SELECT.

termeni SQL

Fiecare propoziție SQL este alcătuită din termeni care pot fi comparați cu părți de vorbire. Tabelul de mai jos listează tipurile de termeni SQL.

Termenul SQL	Parte comparabilă de vorbire	Definiție	Exemplu
identificator	substantiv	Un nume folosit pentru a identifica un obiect de bază de date, cum ar fi un nume de câmp.	Clienți.[PhoneNumber]
operator	verb sau adverb	Un cuvânt cheie care reprezintă sau modifică o acțiune.
constant	substantiv	O valoare care nu se modifică, cum ar fi un număr sau NULL.
expresie	adjectiv	O combinație de identificatori, operatori, constante și funcții care evaluează la o singură valoare.	>= Bunuri.[Preț]

Clauze SQL de bază: SELECT, FROM și WHERE

Formatul general al instrucțiunilor SQL este:

SELECTează câmpul_1
DIN tabelul_1
UNDE criteriul_1
;

Note:

Accesul nu respectă întreruperile de linie în instrucțiunea SQL. În ciuda acestui fapt, este recomandat să începeți fiecare propoziție pe o linie nouă, astfel încât instrucțiunea SQL să fie ușor de citit atât pentru persoana care a scris-o, cât și pentru toți ceilalți.

Fiecare instrucțiune SELECT se termină cu punct și virgulă (;). Punctul și virgulă poate fi fie la sfârșitul ultimei propoziții, fie pe o linie separată la sfârșitul instrucțiunii SQL.

Exemplu în Access

Următorul exemplu arată cum ar putea arăta o instrucțiune SQL în Access pentru o interogare de selectare simplă.

1. Clauza SELECT

2. clauza FROM

3. clauza WHERE

Să descompunem exemplul pe propoziții pentru a înțelege cum funcționează sintaxa SQL.

clauza SELECT

SELECT , Companie

Aceasta este o clauză SELECT. Conține o instrucțiune (SELECT) urmată de doi identificatori ("[Adresa de e-mail]" și "Companie").

Dacă identificatorul conține spații sau caractere speciale (de exemplu, „Adresă de e-mail”), acesta trebuie inclus între paranteze drepte.

În clauza SELECT, nu trebuie să specificați tabelele care conțin câmpurile și nu puteți specifica condițiile de selecție care trebuie îndeplinite de datele pentru a fi incluse în rezultate.

Într-o instrucțiune SELECT, clauza SELECT vine întotdeauna înaintea clauzei FROM.

clauza FROM

DIN Contacte

Aceasta este clauza FROM. Conține o declarație (FROM) urmată de un identificator (Contacte).

Clauza FROM nu specifică câmpurile de selectat.

clauza WHERE

WHERE City="Seattle"

Aceasta este clauza WHERE. Conține un operator (UNDE) urmat de o expresie (City="Rostov").

Puteți face multe lucruri cu clauzele SELECT, FROM și WHERE. Pentru mai multe informații despre cum să utilizați aceste oferte, consultați următoarele articole:

Sortați rezultatele: COMANDĂ PENTRU

La fel ca Microsoft Excel, Access vă permite să sortați rezultatele unei interogări într-un tabel. Folosind clauza ORDER BY, puteți specifica, de asemenea, cum vor fi sortate rezultatele atunci când interogarea este executată. Dacă se folosește o clauză ORDER BY, aceasta trebuie să fie la sfârșitul instrucțiunii SQL.

Clauza ORDER BY conține o listă de câmpuri de sortat, în aceeași ordine în care va fi aplicată sortarea.

De exemplu, să presupunem că doriți să sortați mai întâi rezultatele după câmpul Companie în ordine descrescătoare și apoi, dacă există înregistrări cu aceeași valoare a câmpului Companie, sortați-le după câmpul Adresă de e-mail în ordine crescătoare. Clauza ORDER BY ar arăta astfel:

COMANDA PENTRU COMPANIE DESC,

Notă:În mod implicit, Access sortează valorile în ordine crescătoare (de la A la Z, de la cel mai mic la cel mai mare). Pentru a sorta valorile în ordine descrescătoare, trebuie să specificați cuvântul cheie DESC.

Pentru mai multe informații despre clauza ORDER BY, consultați clauza ORDER BY.

Lucrul cu date rezumative: clauze GROUP BY și HAVING

Uneori trebuie să lucrați cu date rezumative, cum ar fi vânzările lunare totale sau cele mai scumpe articole din stoc. Pentru a face acest lucru, clauza SELECT aplică o funcție de agregare câmpului. De exemplu, dacă rezultatul unei interogări este de a returna numărul de adrese de e-mail pentru fiecare companie, clauza SELECT ar putea arăta astfel:

Capacitatea de a utiliza o anumită funcție de agregare depinde de tipul de date din câmp și de expresia dorită. Pentru mai multe informații despre funcțiile de agregare disponibile, consultați articolul Funcții de agregare SQL.

Specificarea câmpurilor neutilizate într-o funcție de agregare: clauza GROUP BY

Când utilizați funcții de agregare, de obicei trebuie să creați o clauză GROUP BY. Clauza GROUP BY specifică toate câmpurile cărora nu se aplică funcția de agregare. Dacă funcțiile de agregare se aplică tuturor câmpurilor dintr-o interogare, nu trebuie să creați o clauză GROUP BY.

Clauza GROUP BY trebuie să urmeze imediat clauza WHERE sau FROM dacă nu există nicio clauză WHERE. În clauza GROUP BY, câmpurile sunt specificate în aceeași ordine ca și în clauza SELECT.

Să continuăm exemplul anterior. Dacă clauza SELECT aplică numai funcția de agregare câmpului [Adresă de e-mail], atunci clauza GROUP BY ar arăta astfel:

GRUP DE COMPANIE

Pentru mai multe informații despre clauza GROUP BY, consultați clauza GROUP BY.

Constrângerea valorilor agregate cu condiții de grupare: clauza HAVING

Dacă doriți să specificați condiții pentru a restricționa rezultatele, dar câmpul în care doriți să le aplicați este utilizat într-o funcție de agregare, nu puteți utiliza o clauză WHERE. Ar trebui folosită în schimb clauza HAVING. Clauza HAVING funcționează la fel ca și clauza WHERE, dar este folosită pentru datele agregate.

Să presupunem, de exemplu, că funcția AVG (care calculează valoarea medie) este aplicată primului câmp din clauza SELECT:

SELECT COUNT(), Companie

Dacă doriți să restricționați rezultatele interogării pe baza valorii funcției COUNT, nu puteți aplica o condiție de filtru acestui câmp în clauza WHERE. În schimb, condiția ar trebui plasată într-o clauză HAVING. De exemplu, dacă doriți ca interogarea să returneze rânduri numai dacă compania are mai multe adrese de e-mail, puteți utiliza următoarea clauză HAVING:

AVÂND COUNT()>1

Notă: O interogare poate include atât o clauză WHERE, cât și o clauză HAVING, cu criteriile pentru câmpurile care nu sunt utilizate în funcțiile agregate specificate în clauza WHERE și condițiile pentru câmpurile care sunt utilizate în funcțiile agregate sunt specificate în clauza HAVING .

Pentru mai multe informații despre clauza HAVING, consultați clauza HAVING.

Combinarea rezultatelor interogării: Operatorul UNION

Operatorul UNION este utilizat pentru a vizualiza simultan toate datele returnate de mai multe interogări de selecție similare ca un set combinat.

Operatorul UNION vă permite să combinați două instrucțiuni SELECT într-una singură. Instrucțiunile SELECT combinate trebuie să aibă același număr și aceeași ordine de câmpuri de ieșire cu aceleași tipuri de date sau compatibile. Când se execută o interogare, datele din fiecare set de câmpuri care se potrivesc sunt combinate într-un singur câmp de ieșire, astfel încât rezultatul interogării are atâtea câmpuri câte fiecare instrucțiune SELECT individual.

Notă:În interogările union, tipurile de date numerice și text sunt compatibile.

Folosind operatorul UNION, puteți specifica dacă rândurile duplicate, dacă există, trebuie incluse în rezultatele interogării. Pentru a face acest lucru, utilizați cuvântul cheie ALL.

O interogare pentru a uni două instrucțiuni SELECT are următoarea sintaxă de bază:

SELECTează câmpul_1
DIN tabelul_1
UNIUNE
SELECTează câmpul_a
DIN tabel_a
;

Să presupunem, de exemplu, că există două tabele numite „Produse” și „Servicii”. Ambele tabele conțin câmpuri cu numele produsului sau serviciului, informații despre preț și garanție, precum și un câmp care indică exclusivitatea produsului sau serviciului oferit. Deși tabelele Produse și Servicii au diferite tipuri de garanții, informațiile de bază sunt aceleași (dacă se oferă o garanție de calitate pentru produse sau servicii individuale). Pentru a uni patru câmpuri din două tabele, puteți utiliza următoarea interogare de alăturare:

SELECTAȚI numele, prețul, warranty_available, exclusive_offer
DIN Produse
UNIREA TOȚI
SELECTAȚI numele, prețul, garanția_disponibilă, oferta_exclusivă
DE LA Servicii
;

Pentru mai multe informații despre combinarea instrucțiunilor SELECT folosind operatorul UNION, consultați articolul