Selectați (SQL) - Select (SQL)

Instrucțiunea SQL SELECT returnează un set de rezultate de înregistrări, de la unul sau mai multe tabele .

O instrucțiune SELECT recuperează zero sau mai multe rânduri de la una sau mai multe tabele de baze de date sau vizualizări ale bazei de date . În majoritatea aplicațiilor, SELECTeste cea mai frecvent utilizată comandă de limbaj de manipulare a datelor (DML). Deoarece SQL este un limbaj de programare declarativ , SELECTinterogările specifică un set de rezultate, dar nu specifică cum să se calculeze. Baza de date traduce interogarea într-un „ plan de interogare ” care poate varia între execuții, versiunile bazei de date și software-ul bazei de date. Această funcționalitate se numește „ optimizator de interogare ”, deoarece este responsabilă pentru găsirea celui mai bun plan de execuție posibil pentru interogare, în limitele aplicabile.

Instrucțiunea SELECT are multe clauze opționale:

• SELECTclauza este lista de coloane sau expresii SQL care trebuie returnate de interogare. Aceasta este aproximativ operația de proiecție algebră relațională .
• ASoferă opțional un alias pentru fiecare coloană sau expresie din SELECTclauză. Aceasta este operația de redenumire a algebrei relaționale .
• FROM specifică din ce tabel să obțineți datele.
• WHEREspecifică ce rânduri să recupereze. Aceasta este aproximativ operația de selectare a algebrei relaționale .
• GROUP BYgrupează rânduri care partajează o proprietate, astfel încât o funcție agregată să poată fi aplicată fiecărui grup.
• HAVING selectează dintre grupurile definite de clauza GROUP BY.
• ORDER BY specifică modul de comandare a rândurilor returnate.

Prezentare generală

SELECTeste cea mai comună operație în SQL, numită „interogarea”. SELECTpreia date de pe unul sau mai multe tabele sau expresii. SELECTDeclarațiile standard nu au efecte persistente asupra bazei de date. Unele implementări non-standard ale SELECTpot avea efecte persistente, cum ar fi SELECT INTOsintaxa furnizată în unele baze de date.

Interogările permit utilizatorului să descrie datele dorite, lăsând sistemul de gestionare a bazelor de date (SGBD) să efectueze planificarea , optimizarea și efectuarea operațiunilor fizice necesare pentru a produce acel rezultat după cum alege.

O interogare include o listă de coloane de inclus în rezultatul final, în mod normal imediat după SELECTcuvântul cheie. Un asterisc (" *") poate fi utilizat pentru a specifica că interogarea ar trebui să returneze toate coloanele tabelelor interogate. SELECTeste cea mai complexă afirmație din SQL, cu cuvinte cheie și clauze opționale care includ:

• FROMClauza, care indică masa (e) pentru a prelua date de la. FROMClauza poate include opțional JOINsubclauze pentru a specifica regulile de aderare la tabele.
• WHEREClauza include un predicat comparație, care limitează rândurile returnate de interogare. WHEREClauza elimină toate rândurile din setul de rezultate în cazul în care predicatul comparație nu se evaluează la True.
• Cele GROUP BYproiecte Clauza de rânduri având valori comune într - un set mai mic de rânduri. GROUP BYeste adesea utilizat împreună cu funcțiile de agregare SQL sau pentru a elimina rândurile duplicate dintr-un set de rezultate. WHEREClauza se aplică înainte de GROUP BYclauza.
• HAVINGClauza include un predicat utilizat pentru rândurile de filtrare care rezultă din GROUP BYclauza. Deoarece acționează asupra rezultatelor GROUP BYclauzei, funcțiile de agregare pot fi utilizate în HAVINGpredicatul clauzei.
• Cele ORDER BYClauza care identifică o coloană [s] pentru a utiliza pentru a sorta datele rezultate, și în care direcția lor (crescătoare sau descrescătoare) sortați. Fără o ORDER BYclauză, ordinea rândurilor returnate de o interogare SQL este nedefinită.
• Cuvântul DISTINCTcheie elimină datele duplicate.

Următorul exemplu de SELECTinterogare returnează o listă de cărți scumpe. Interogarea preia toate rândurile din tabelul Carte în care coloana preț conține o valoare mai mare de 100,00. Rezultatul este sortat în ordine crescătoare după titlu . Asteriscul (*) din lista de selectare indică faptul că toate coloanele din tabelul Carte ar trebui incluse în setul de rezultate.

SELECT *
 FROM  Book
 WHERE price > 100.00
 ORDER BY title;

Exemplul de mai jos demonstrează o interogare a mai multor tabele, grupare și agregare, prin returnarea unei liste de cărți și a numărului de autori asociați cu fiecare carte.

SELECT Book.title AS Title,
       count(*) AS Authors
 FROM  Book
 JOIN  Book_author
   ON  Book.isbn = Book_author.isbn
 GROUP BY Book.title;

Exemplul de ieșire seamănă cu următorul:

Title                  Authors
---------------------- -------
SQL Examples and Guide 4
The Joy of SQL         1
An Introduction to SQL 2
Pitfalls of SQL        1

În condiția prealabilă, isbn este singurul nume de coloană comun al celor două tabele și că o coloană numită titlu există doar în tabelul Book , s-ar putea rescrie interogarea de mai sus în următoarea formă:

SELECT title,
       count(*) AS Authors
 FROM  Book
 NATURAL JOIN Book_author
 GROUP BY title;

Cu toate acestea, mulți furnizori fie nu acceptă această abordare, fie necesită anumite convenții de denumire a coloanelor pentru ca îmbinările naturale să funcționeze eficient.

SQL include operatori și funcții pentru calcularea valorilor pe valorile stocate. SQL permite utilizarea expresiilor din lista de selectare pentru a proiecta date, ca în exemplul următor, care returnează o listă de cărți care costă mai mult de 100,00 cu o coloană suplimentară sales_tax care conține o cifră a impozitului pe vânzări calculată la 6% din preț .

SELECT isbn,
       title,
       price,
       price * 0.06 AS sales_tax
 FROM  Book
 WHERE price > 100.00
 ORDER BY title;

Subcercări

Interogările pot fi imbricate astfel încât rezultatele unei interogări să poată fi utilizate într-o altă interogare printr-un operator relațional sau o funcție de agregare. O interogare imbricată este cunoscută și ca subinterogare . În timp ce îmbinările și alte operații de tabel oferă alternative superioare din punct de vedere al calculului (adică mai rapide) în multe cazuri, utilizarea subconsultărilor introduce o ierarhie în execuție care poate fi utilă sau necesară. În exemplul următor, funcția de agregare AVGprimește ca intrare rezultatul unei interogări:

SELECT isbn,
       title,
       price
 FROM  Book
 WHERE price < (SELECT AVG(price) FROM Book)
 ORDER BY title;

O subinterogare poate utiliza valori din interogarea externă, caz în care este cunoscută ca subinterogare corelată .

Din 1999 standardul SQL permite subinterogări denumite expresii de tabel comune (denumite și proiectate după implementarea IBM DB2 versiunea 2; Oracle numește aceste subquery factoring ). CTE-urile pot fi, de asemenea, recursive referindu-se la ele însele; mecanismul rezultat permite traversarea arborelui sau a graficului (atunci când este reprezentată ca relații) și, în general, calcule punct fix .

Masă derivată

Un tabel derivat este utilizarea referințării unei subinterogări SQL într-o clauză FROM. În esență, tabelul derivat este o subinterogare care poate fi selectată din sau alăturată. Funcționalitatea tabelului derivat permite utilizatorului să facă referire la subinterogare ca tabel. Tabelul derivat este, de asemenea, denumit vizualizare inline sau selectare din listă .

În exemplul următor, instrucțiunea SQL implică o asociere de la tabelul inițial Cărți la tabelul derivat „Vânzări”. Acest tabel derivat captează informații despre vânzările de cărți asociate folosind codul ISBN pentru a se alătura tabelului Cărți. Ca rezultat, tabelul derivat oferă setul de rezultate cu coloane suplimentare (numărul de articole vândute și compania care a vândut cărțile):

SELECT b.isbn, b.title, b.price, sales.items_sold, sales.company_nm
FROM Book b
  JOIN (SELECT SUM(Items_Sold) Items_Sold, Company_Nm, ISBN
        FROM Book_Sales
        GROUP BY Company_Nm, ISBN) sales
  ON sales.isbn = b.isbn

Exemple

Având în vedere un tabel T, interogarea va avea ca rezultat afișarea tuturor elementelor din toate rândurile tabelului. SELECT * FROM T

Cu același tabel, interogarea va avea ca rezultat afișarea elementelor din coloana C1 a tuturor rândurilor din tabel. Acest lucru este similar cu o proiecție în algebră relațională , cu excepția faptului că, în cazul general, rezultatul poate conține rânduri duplicate. Acest lucru este, de asemenea, cunoscut sub numele de partiție verticală în unii termeni de bază de date, restricționând ieșirea interogării pentru a vizualiza doar câmpurile sau coloanele specificate. SELECT C1 FROM T

Cu același tabel, interogarea va duce la afișarea tuturor elementelor tuturor rândurilor în care valoarea coloanei C1 este „1” - în termeni de algebră relațională , se va efectua o selecție , din cauza clauzei WHERE. Aceasta este, de asemenea, cunoscută sub numele de partiție orizontală, restricționând rândurile de ieșire printr-o interogare în funcție de condițiile specificate. SELECT * FROM T WHERE C1 = 1

Cu mai multe tabele, setul de rezultate va fi fiecare combinație de rânduri. Deci, dacă două tabele sunt T1 și T2, va rezulta fiecare combinație de rânduri T1 cu fiecare rând T2. De exemplu, dacă T1 are 3 rânduri și T2 are 5 rânduri, atunci vor rezulta 15 rânduri. SELECT * FROM T1, T2

Deși nu este în standard, majoritatea SGBD permite utilizarea unei clauze select fără un tabel, pretinzând că se folosește un tabel imaginar cu un rând. Aceasta este utilizată în principal pentru a efectua calcule în cazul în care un tabel nu este necesar.

Clauza SELECT specifică o listă de proprietăți (coloane) după nume sau caracterul comod („*”) pentru a însemna „toate proprietățile”.

Limitarea rândurilor de rezultate

Adesea este convenabil să indicați un număr maxim de rânduri care sunt returnate. Aceasta poate fi utilizată pentru testare sau pentru a preveni consumul de resurse excesive dacă interogarea returnează mai multe informații decât se aștepta. Abordarea de a face acest lucru variază adesea în funcție de furnizor.

În ISO SQL: 2003 , seturile de rezultate pot fi limitate prin utilizarea

• cursoare sau
• prin adăugarea unei funcții de fereastră SQL la instrucțiunea SELECT

ISO SQL: 2008 a introdus FETCH FIRSTclauza.

Conform documentației PostgreSQL v.9, o funcție de fereastră SQL „efectuează un calcul pe un set de rânduri de tabele care sunt cumva legate de rândul curent”, într-un mod similar cu funcțiile agregate. Numele amintește funcțiile ferestrei de procesare a semnalului . Un apel funcție fereastră conține întotdeauna o clauză OVER .

Funcția fereastră ROW_NUMBER ()

ROW_NUMBER() OVERpoate fi folosit pentru un tabel simplu pe rândurile returnate, de exemplu pentru a returna cel mult zece rânduri:

SELECT * FROM
( SELECT
    ROW_NUMBER() OVER (ORDER BY sort_key ASC) AS row_number,
    columns
  FROM tablename
) AS foo
WHERE row_number <= 10

ROW_NUMBER poate fi nedeterminist : dacă sort_key nu este unic, de fiecare dată când executați interogarea este posibil să obțineți numere de rând diferite atribuite oricărui rând în care sort_key este același. Când sort_key este unic, fiecare rând va primi întotdeauna un număr unic de rând.

Funcția de fereastră RANK ()

Funcția de RANK() OVERfereastră acționează ca ROW_NUMBER, dar poate reveni mai mult sau mai puțin de n rânduri în caz de condiții de egalitate, de exemplu pentru a returna primii 10 tineri:

SELECT * FROM (
  SELECT
    RANK() OVER (ORDER BY age ASC) AS ranking,
    person_id,
    person_name,
    age
  FROM person
) AS foo
WHERE ranking <= 10

Codul de mai sus ar putea returna mai mult de zece rânduri, de exemplu, dacă există două persoane de aceeași vârstă, ar putea returna unsprezece rânduri.

Clauza FETCH FIRST

Întrucât limitele rezultatelor ISO SQL: 2008 pot fi specificate ca în exemplul următor folosind FETCH FIRSTclauza.

SELECT * FROM T 
FETCH FIRST 10 ROWS ONLY

Această clauză este acceptată în prezent de CA DATACOM / DB 11, IBM DB2, SAP SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB versiunea 2.0, Oracle 12c și Mimer SQL .

Microsoft SQL Server 2008 și versiuni superioare acceptăFETCH FIRST , dar este considerat parte a ORDER BYclauzei. De ORDER BY, OFFSETși FETCH FIRSTclauzele sunt toate necesare pentru această utilizare.

SELECT * FROM T 
ORDER BY acolumn DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY

Sintaxă nestandardizată

Unele SGBD oferă sintaxă non-standard fie în locul, fie în plus față de sintaxa standard SQL. Mai jos, sunt enumerate variantele interogării limită simple pentru diferite DBMS-uri:

SET ROWCOUNT 10
SELECT * FROM T

SELECT * FROM T 
LIMIT 10 OFFSET 20

SELECT * from T 
WHERE ROWNUM <= 10

SELECT * FROM T 
SAMPLE 10

SELECT * FROM T
ROWS 20 TO 30

SELECT * FROM T
WHERE ID_T > 10 FETCH FIRST 10 ROWS ONLY

SELECT * FROM T
WHERE ID_T > 20 FETCH FIRST 10 ROWS ONLY

Paginarea rândurilor

Paginarea rândurilor este o abordare utilizată pentru a limita și afișa doar o parte din datele totale ale unei interogări în baza de date. În loc să afișeze sute sau mii de rânduri în același timp, serverului i se solicită o singură pagină (un set limitat de rânduri, de exemplu doar 10 rânduri), iar utilizatorul începe să navigheze solicitând pagina următoare și apoi următoarea , si asa mai departe. Este foarte util, în special în sistemele web, unde nu există o conexiune dedicată între client și server, astfel încât clientul nu trebuie să aștepte să citească și să afișeze toate rândurile serverului.

Abordarea datelor în paginare

• {rows} = Numărul de rânduri dintr-o pagină
• {page_number} = Numărul paginii curente
• {begin_base_0} = Numărul rândului - 1 de unde începe pagina = (page_number-1) * rânduri

Cea mai simplă metodă (dar foarte ineficientă)

1. Selectați toate rândurile din baza de date
2. Citiți toate rândurile, dar trimiteți la afișare numai atunci când numărul_ rând al rândurilor citite este între {begin_base_0 + 1}și{begin_base_0 + rows}

Select * 
from {table} 
order by {unique_key}

Altă metodă simplă (puțin mai eficientă decât citirea tuturor rândurilor)

1. Selectați toate rândurile de la începutul tabelului până la ultimul rând de afișat ( {begin_base_0 + rows})
2. Citiți {begin_base_0 + rows}rândurile, dar trimiteți la afișare numai atunci când numărul_rând al rândurilor citite este mai mare decât{begin_base_0}

select *
from {table}
order by {unique_key}
FETCH FIRST {begin_base_0 + rows} ROWS ONLY

Select *
from {table}
order by {unique_key}
LIMIT {begin_base_0 + rows}

Select TOP {begin_base_0 + rows} * 
from {table} 
order by {unique_key}

SET ROWCOUNT {begin_base_0 + rows}
Select * 
from {table} 
order by {unique_key}
SET ROWCOUNT 0

Select *
    FROM (
        SELECT * 
        FROM {table} 
        ORDER BY {unique_key}
    ) a 
where rownum <= {begin_base_0 + rows}

Metoda cu poziționare

1. Selectați numai {rows}rândurile începând cu rândul următor pentru a afișa ( {begin_base_0 + 1})
2. Citiți și trimiteți pentru a afișa toate rândurile citite din baza de date

Select *
from {table}
order by {unique_key}
OFFSET {begin_base_0} ROWS
FETCH NEXT {rows} ROWS ONLY

Select *
from {table}
order by {unique_key}
LIMIT {rows} OFFSET {begin_base_0}

Select * 
from {table} 
order by {unique_key}
LIMIT {begin_base_0}, {rows}

Select TOP {begin_base_0 + rows}
       *,  _offset=identity(10) 
into #temp
from {table}
ORDER BY {unique_key} 
select * from #temp where _offset > {begin_base_0}
DROP TABLE #temp

SET ROWCOUNT {begin_base_0 + rows}
select *,  _offset=identity(10) 
into #temp
from {table}
ORDER BY {unique_key} 
select * from #temp where _offset > {begin_base_0}
DROP TABLE #temp
SET ROWCOUNT 0

select TOP {rows} * 
from (
      select *, ROW_NUMBER() over (order by {unique_key}) as _offset
      from {table}
) xx 
where _offset > {begin_base_0}

SET ROWCOUNT {begin_base_0 + rows}
select *,  _offset=identity(int,1,1) 
into #temp
from {table}
ORDER BY {unique-key}
select * from #temp where _offset > {begin_base_0}
DROP TABLE #temp
SET ROWCOUNT 0

SELECT * FROM (
    SELECT rownum-1 as _offset, a.* 
    FROM(
        SELECT * 
        FROM {table} 
        ORDER BY {unique_key}
    ) a 
    WHERE rownum <= {begin_base_0 + cant_regs}
)
WHERE _offset >= {begin_base_0}

Metoda cu filtru (este mai sofisticată, dar necesară pentru setul de date foarte mare)

1. Selectați numai apoi {rows}rândurile cu filtru:
   1. Prima pagină: selectați numai primele {rows}rânduri, în funcție de tipul bazei de date
   2. Pagina următoare: selectați numai primele {rows}rânduri, în funcție de tipul bazei de date, unde {unique_key}este mai mare decât {last_val}(valoarea {unique_key}ultimului rând din pagina curentă)
   3. Pagina anterioară: sortați datele în ordine inversă, selectați numai primele {rows}rânduri, unde {unique_key}este mai mic decât {first_val}(valoarea {unique_key}primului rând din pagina curentă) și sortați rezultatul în ordinea corectă
2. Citiți și trimiteți pentru a afișa toate rândurile citite din baza de date

select *
from {table} 
order by {unique_key}
FETCH FIRST {rows} ROWS ONLY

select * 
from {table} 
where {unique_key} > {last_val}
order by {unique_key}
FETCH FIRST {rows} ROWS ONLY

select * 
 from (
   select * 
   from {table} 
   where {unique_key} < {first_val}
   order by {unique_key} DESC
   FETCH FIRST {rows} ROWS ONLY
 ) a
 order by {unique_key}

select *
from {table}
order by {unique_key}
LIMIT {rows}

select * 
from {table} 
where {unique_key} > {last_val}
order by {unique_key}
LIMIT {rows}

select * 
 from (
   select * 
   from {table} 
   where {unique_key} < {first_val}
   order by {unique_key} DESC
   LIMIT {rows}
 ) a
 order by {unique_key}

select TOP {rows} * 
from {table} 
order by {unique_key}

select TOP {rows} * 
from {table} 
where {unique_key} > {last_val}
order by {unique_key}

select * 
 from (
   select TOP {rows} * 
   from {table} 
   where {unique_key} < {first_val}
   order by {unique_key} DESC
 ) a
 order by {unique_key}

SET ROWCOUNT {rows}
select *
from {table} 
order by {unique_key}
SET ROWCOUNT 0

SET ROWCOUNT {rows}
select *
from {table} 
where {unique_key} > {last_val}
order by {unique_key}
SET ROWCOUNT 0

SET ROWCOUNT {rows}
 select *
 from (
   select * 
   from {table} 
   where {unique_key} < {first_val}
   order by {unique_key} DESC
 ) a
 order by {unique_key}
 SET ROWCOUNT 0

select *
from (
    select * 
    from {table} 
    order by {unique_key}
  ) a 
where rownum <= {rows}

select *
from (
  select * 
  from {table} 
  where {unique_key} > {last_val}
  order by {unique_key}
) a 
where rownum <= {rows}

select * 
 from (
   select *
   from (
     select * 
     from {table} 
     where {unique_key} < {first_val}
     order by {unique_key} DESC
   ) a1
   where rownum <= {rows}
 ) a2
 order by {unique_key}

Interogare ierarhică

Unele baze de date oferă sintaxă specializată pentru date ierarhice .

O funcție de fereastră în SQL: 2003 este o funcție agregată aplicată unei partiții a setului de rezultate.

De exemplu,

sum(population) OVER( PARTITION BY city )

calculează suma populațiilor tuturor rândurilor având aceeași valoare de oraș ca rândul curent.

Partițiile sunt specificate folosind clauza OVER care modifică agregatul. Sintaxă:

<OVER_CLAUSE> :: =
   OVER ( [ PARTITION BY <expr>, ... ]
          [ ORDER BY <expression> ] )

Clauza OVER poate partiționa și ordona setul de rezultate. Ordinea este utilizată pentru funcții relative la ordine, cum ar fi numărul_rând.

Evaluarea interogării ANSI

Procesarea unei instrucțiuni SELECT conform ANSI SQL ar fi următoarea:

Suport funcție fereastră de către furnizorii RDBMS

Implementarea caracteristicilor funcției de fereastră de către furnizorii de baze de date relaționale și motoare SQL diferă foarte mult. Majoritatea bazelor de date acceptă cel puțin o anumită aromă a funcțiilor ferestrei. Cu toate acestea, atunci când analizăm mai atent, devine clar că majoritatea furnizorilor implementează doar un subset al standardului. Să luăm ca exemplu clauza puternică RANGE. Numai Oracle, DB2, Spark / Hive și Google Big Query implementează pe deplin această caracteristică. Mai recent, furnizorii au adăugat noi extensii standardului, de ex. Funcții de agregare a matricei. Acestea sunt deosebit de utile în contextul executării SQL pe un sistem de fișiere distribuite (Hadoop, Spark, Google BigQuery) în care avem garanții de localitate a datelor mai slabe decât într-o bază de date relațională distribuită (MPP). Mai degrabă decât distribuirea uniformă a datelor pe toate nodurile, motoarele SQL care execută interogări împotriva unui sistem de fișiere distribuit pot obține garanții de co-localitate a datelor prin cuibarea datelor și evitând astfel îmbinările potențial costisitoare care implică o amestecare intensă în rețea. Funcțiile agregate definite de utilizator care pot fi utilizate în funcțiile ferestrei sunt o altă caracteristică extrem de puternică.

Generarea de date în T-SQL

Metodă de a genera date bazate pe unirea tuturor

select 1 a, 1 b union all
select 1, 2 union all
select 1, 3 union all
select 2, 1 union all
select 5, 1

SQL Server 2008 acceptă „constructorul de rânduri” specificat în standardul SQL3 („SQL: 1999”)

select *
from (values (1, 1), (1, 2), (1, 3), (2, 1), (5, 1)) as x(a, b)

Referințe

Surse

• Partiționare orizontală și verticală, cărți online Microsoft SQL Server 2000.

linkuri externe

• Funcția Windowed Tables și Window în SQL , Stefan Deßloch
• Sintaxa Oracle SELECT
• Sintaxa Firebird SELECT
• Sintaxa MySQL SELECT
• Sintaxa PostgreSQL SELECT
• Sintaxa SQLite SELECT