Vælg (SQL) - Select (SQL)

Den SQL SELECT- sætning returnerer et resultat sæt af poster fra en eller flere tabeller .

En SELECT-sætning henter nul eller flere rækker fra en eller flere databasetabeller eller database synspunkter . I de fleste applikationer SELECTer den mest almindeligt anvendte kommando for databehandlingssprog (DML). Da SQL er et deklarativt programmeringssprog , SELECTangiver forespørgsler et resultatsæt, men angiver ikke, hvordan det skal beregnes. Databasen oversætter forespørgslen til en " forespørgselsplan ", som kan variere mellem henrettelser, databaseversioner og databasesoftware. Denne funktionalitet kaldes " forespørgselsoptimering ", da den er ansvarlig for at finde den bedst mulige udførelsesplan for forespørgslen inden for gældende begrænsninger.

SELECT -sætningen har mange valgfrie klausuler:

• SELECTklausul er listen over kolonner eller SQL -udtryk, der skal returneres af forespørgslen. Dette er omtrent den relationelle algebra projektionsoperation .
• ASgiver eventuelt et alias for hver kolonne eller udtryk i SELECTklausulen. Dette er den relationelle algebra -omdøbning .
• FROM angiver fra hvilken tabel dataene skal hentes.
• WHEREangiver, hvilke rækker der skal hentes. Dette er omtrent den relationelle valg af algebra .
• GROUP BYgrupperækker, der deler en ejendom, så en samlet funktion kan anvendes på hver gruppe.
• HAVING vælger blandt de grupper, der er defineret af GROUP BY -klausulen.
• ORDER BY angiver, hvordan de returnerede rækker skal bestilles.

Oversigt

SELECTer den mest almindelige operation i SQL, kaldet "forespørgslen". SELECThenter data fra en eller flere tabeller eller udtryk. Standardudsagn SELECThar ingen vedvarende virkninger på databasen. Nogle ikke-standardiserede implementeringer af SELECTkan have vedvarende effekter, f.eks. SELECT INTOSyntaksen i nogle databaser.

Forespørgsler giver brugeren mulighed for at beskrive ønskede data, så databasestyringssystemet (DBMS) kan udføre planlægning , optimering og udførelse af de fysiske operationer, der er nødvendige for at producere det resultat, som det vælger.

En forespørgsel indeholder en liste over kolonner, der skal medtages i det endelige resultat, normalt umiddelbart efter SELECTsøgeordet. En stjerne (" *") kan bruges til at angive, at forespørgslen skal returnere alle kolonner i de forespurgte tabeller. SELECTer den mest komplekse erklæring i SQL med valgfri søgeord og klausuler, der omfatter:

• Den FROMbestemmelse, der angiver tabel (ler) til at hente data fra. Den FROMklausul kan indeholde valgfrie JOINunderpunkter at præcisere reglerne for at blive medlem tabeller.
• Den WHEREbestemmelse indbefatter en sammenligning prædikat, hvilket begrænser rækkerne returneres af forespørgslen. Den WHEREklausul fjerner alle rækker fra resultatet sæt, hvor sammenligningen prædikat ikke vurdere til True.
• De GROUP BYklausul projekter rækker har fælles værdier i et mindre sæt rækker. GROUP BYbruges ofte sammen med SQL -aggregeringsfunktioner eller til at eliminere dublerede rækker fra et resultatsæt. Den WHEREklausul påføres før GROUP BYklausul.
• Den HAVINGbestemmelse indbefatter et prædikat bruges til at filtrere rækker som følge af GROUP BYklausulen. Fordi det virker på resultaterne af GROUP BYklausulen, kan aggregeringsfunktioner bruges i HAVINGklausulprædikatet.
• De ORDER BYclause identificerer hvilken kolonne [s] for at bruge til at sortere de resulterende data, og i hvilken retning at sortere dem (stigende eller faldende). Uden en ORDER BYklausul er rækkefølgen af ​​rækker, der returneres af en SQL -forespørgsel, udefineret.
• De DISTINCTsøgeord eliminerer duplikere data.

Følgende eksempel på en SELECTforespørgsel returnerer en liste over dyre bøger. Forespørgslen henter alle rækker fra Bog tabellen, hvor prisen kolonne indeholder en værdi større end 100,00. Resultatet sorteres i stigende rækkefølge efter titel . Stjernen (*) i select-listen viser, at alle kolonner i bog tabellen bør indgå i resultatet sæt.

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

Nedenstående eksempel viser en forespørgsel efter flere tabeller, gruppering og aggregering ved at returnere en liste over bøger og antallet af forfattere, der er knyttet til hver bog.

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

Eksempeloutput ligner muligvis følgende:

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

Under forudsætning af, at isbn er det eneste fælles kolonne navn på de to tabeller, og at en kolonne med navnet titel kun eksisterer i Bog tabellen, kunne man omskrive forespørgslen ovenfor i følgende form:

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

Imidlertid understøtter mange leverandører enten ikke denne fremgangsmåde eller kræver visse konventioner om navngivning af kolonner for at naturlige sammenføjninger fungerer effektivt.

SQL inkluderer operatører og funktioner til beregning af værdier på lagrede værdier. SQL tillader brug af udtryk i SELECT-listen til projektdata, som i det følgende eksempel, som returnerer en liste over bøger, som koster mere end 100,00 med en ekstra sales_tax kolonne, der indeholder en omsætningsafgift tallet opgjort til 6% af prisen .

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

Underforespørgsler

Forespørgsler kan indlejres, så resultaterne af en forespørgsel kan bruges i en anden forespørgsel via en relationel operator eller aggregeringsfunktion. En indlejret forespørgsel er også kendt som en forespørgsel . Mens joins og andre bordoperationer giver beregningsmæssigt overlegne (dvs. hurtigere) alternativer i mange tilfælde, introducerer brugen af ​​underforespørgsler et hierarki i udførelsen, der kan være nyttigt eller nødvendigt. I det følgende eksempel modtager aggregeringsfunktionen AVGresultatet af en underforespørgsel som input:

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

En underforespørgsel kan bruge værdier fra den ydre forespørgsel, i hvilket tilfælde den er kendt som en korreleret underforespørgsel .

Siden 1999 tillader SQL -standarden navngivne underforespørgsler kaldet almindelige tabeludtryk (navngivet og designet efter implementeringen af ​​IBM DB2 version 2; Oracle kalder disse subquery factoring ). CTE'er kan også være rekursive ved at henvise til sig selv; den resulterende mekanisme tillader træ- eller grafoverfarter (når de er repræsenteret som relationer) og mere generelt fixpoint -beregninger .

Afledt bord

En afledt tabel er brugen af ​​at henvise til en SQL -underforespørgsel i en FROM -klausul. I det væsentlige er den afledte tabel en underforespørgsel, der kan vælges fra eller sluttes til. Afledt tabelfunktionalitet giver brugeren mulighed for at referere til forespørgslen som en tabel. Den afledte tabel kaldes også en inline -visning eller en vælg fra listen .

I det følgende eksempel involverer SQL -sætningen en sammenføjning fra den indledende bogtabel til den afledte tabel "Salg". Denne afledte tabel indsamler tilknyttede bogsalgsoplysninger ved hjælp af ISBN til at deltage i tabellen Bøger. Som et resultat heraf giver den afledte tabel resultatsættet med yderligere kolonner (antallet af solgte varer og det selskab, der solgte bøgerne):

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

Eksempler

Ved en tabel T vil forespørgslen resultere i, at alle elementerne i alle tabellernes rækker vises. SELECT * FROM T

Med den samme tabel vil forespørgslen resultere i, at elementerne fra kolonnen C1 i alle rækker i tabellen vises. Dette ligner en fremskrivning i relationel algebra , bortset fra at resultatet i almindelighed kan indeholde dubletter. Dette er også kendt som en lodret partition i nogle databasetermer, der begrænser forespørgselsoutput til kun at se specificerede felter eller kolonner. SELECT C1 FROM T

Med den samme tabel vil forespørgslen resultere i, at alle elementerne i alle de rækker, hvor værdien i kolonne C1 er '1', vises - i relationelle algebra -termer vil der blive udført et valg på grund af WHERE -klausulen. Dette er også kendt som en vandret partition, der begrænser rækker, der udsendes af en forespørgsel i henhold til angivne betingelser. SELECT * FROM T WHERE C1 = 1

Med mere end en tabel vil resultatsættet være hver kombination af rækker. Så hvis to tabeller er T1 og T2, vil det resultere i hver kombination af T1 -rækker med hver T2 -række. F.eks. Hvis T1 har 3 rækker og T2 har 5 rækker, resulterer der 15 rækker. SELECT * FROM T1, T2

Selvom det ikke er i standard, tillader de fleste DBMS at bruge en udvalgte klausul uden en tabel ved at foregive, at der bruges en imaginær tabel med en række. Dette bruges hovedsageligt til at udføre beregninger, hvor en tabel ikke er nødvendig.

SELECT -klausulen angiver en liste over egenskaber (kolonner) efter navn eller jokertegnet ("*") til at betyde "alle egenskaber".

Begrænsning af resultatrækker

Ofte er det praktisk at angive et maksimalt antal rækker, der returneres. Dette kan bruges til test eller til at forhindre forbrug af for store ressourcer, hvis forespørgslen returnerer flere oplysninger end forventet. Metoden til at gøre dette varierer ofte fra leverandør til leverandør.

I ISO SQL: 2003 kan resultatsæt begrænses ved at bruge

• markører eller
• ved at tilføje en SQL-vinduesfunktion til SELECT-sætningen

ISO SQL: 2008 introducerede FETCH FIRSTklausulen.

Ifølge PostgreSQL v.9 -dokumentation udfører en SQL -vinduesfunktion "en beregning på tværs af et sæt tabelrækker, der på en eller anden måde er relateret til den aktuelle række", på en måde, der ligner aggregerede funktioner. Navnet minder om signalbehandlingsvinduets funktioner . Et vinduesfunktionsopkald indeholder altid en OVER -klausul.

ROW_NUMBER () vinduesfunktion

ROW_NUMBER() OVERkan bruges til en simpel tabel på de returnerede rækker, f.eks. til ikke at returnere mere end ti rækker:

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 kan være ikke-deterministisk : Hvis sort_key ikke er unik, er det muligt hver gang du kører forespørgslen at få forskellige rækkenumre tildelt alle rækker, hvor sort_key er den samme. Når sort_key er unik, får hver række altid et unikt rækkenummer.

RANK () vinduesfunktion

Den RANK() OVERvinduesfunktionen fungerer som ROW_NUMBER, men kan returnere mere eller mindre end n rækker i tilfælde af slips forhold, fx at returnere top-10 yngste personer:

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

Ovenstående kode kunne returnere mere end ti rækker, f.eks. Hvis der er to personer i samme alder, kan den returnere elleve rækker.

FETCH FIRST -klausul

Da ISO SQL: 2008 -resultatgrænser kan specificeres som i følgende eksempel ved hjælp af FETCH FIRSTklausulen.

SELECT * FROM T 
FETCH FIRST 10 ROWS ONLY

Denne klausul understøttes i øjeblikket af CA DATACOM/DB 11, IBM DB2, SAP SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB version 2.0, Oracle 12c og Mimer SQL .

Microsoft SQL Server 2008 og højere understøtterFETCH FIRST , men det betragtes som en del af ORDER BYklausulen. De ORDER BY, OFFSETog FETCH FIRSTklausuler er alle nødvendige for denne brug.

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

Ikke-standard syntaks

Nogle DBMS'er tilbyder ikke-standard syntaks enten i stedet for eller ud over SQL standardsyntaks. Nedenfor er varianter af den simple grænseforespørgsel for forskellige DBMS'er opført:

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

Rækker pagination

Rækker pagination er en metode, der bruges til at begrænse og vise kun en del af de samlede data for en forespørgsel i databasen. I stedet for at vise hundredvis eller tusinder af rækker på samme tid, bliver serveren kun anmodet om en side (et begrænset sæt rækker pr. Eksempelvis kun 10 rækker), og brugeren begynder at navigere ved at anmode om den næste side og derefter den næste , og så videre. Det er meget nyttigt, specielt i websystemer, hvor der ikke er en dedikeret forbindelse mellem klienten og serveren, så klienten ikke behøver at vente med at læse og vise alle serverens rækker.

Data i pagineringstilgang

• {rows} = Antal rækker på en side
• {page_number} = Nummeret på den aktuelle side
• {begin_base_0} = Nummer på rækken - 1 hvor siden starter = (page_number -1) * rækker

Den enkleste metode (men meget ineffektiv)

1. Vælg alle rækker fra databasen
2. Læs alle rækker, men send kun til visning, når rækkenummeret for de læste rækker er mellem {begin_base_0 + 1}og{begin_base_0 + rows}

Select * 
from {table} 
order by {unique_key}

Anden enkel metode (lidt mere effektiv end at læse alle rækker)

1. Vælg alle rækker fra begyndelsen af ​​tabellen til den sidste række for at få vist ( {begin_base_0 + rows})
2. Læs {begin_base_0 + rows}rækkerne, men send dem kun til visning, når rækkenummeret for de læste rækker er større end{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}

Metode med positionering

1. Vælg kun {rows}rækker fra den næste række for at få vist ( {begin_base_0 + 1})
2. Læs og send for at få vist alle de læste rækker fra databasen

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}

Metode med filter (det er mere sofistikeret, men nødvendigt for meget store datasæt)

1. Vælg kun derefter {rows}rækker med filter:
   1. Første side: Vælg kun de første {rows}rækker afhængigt af databasetypen
   2. Næste side: Vælg kun de første {rows}rækker, afhængigt af typen af ​​database, hvor værdien {unique_key}er større end {last_val}(værdien {unique_key}af den sidste række på den aktuelle side)
   3. Forrige side: sorter dataene i omvendt rækkefølge, vælg kun de første {rows}rækker, hvor den {unique_key}er mindre end {first_val}(værdien {unique_key}af den første række på den aktuelle side), og sorter resultatet i den korrekte rækkefølge
2. Læs og send for at få vist alle de læste rækker fra databasen

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}

Hierarkisk forespørgsel

Nogle databaser giver specialiseret syntaks for hierarkiske data .

En vinduesfunktion i SQL: 2003 er en samlet funktion, der anvendes på en partition af resultatsættet.

For eksempel,

sum(population) OVER( PARTITION BY city )

beregner summen af befolkningerne i alle rækker, der har samme by værdi som den aktuelle række.

Partitioner specificeres ved hjælp af OVER -klausulen, der ændrer aggregatet. Syntaks:

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

OVER -klausulen kan opdele og bestille resultatsættet. Ordning bruges til ordre-relative funktioner, f.eks. Row_number.

Forespørgselsevaluering ANSI

Behandlingen af ​​en SELECT -sætning ifølge ANSI SQL ville være følgende:

Understøttelse af vinduesfunktioner fra RDBMS -leverandører

Implementering af vinduesfunktionsfunktioner af leverandører af relationsdatabaser og SQL -motorer adskiller sig voldsomt. De fleste databaser understøtter i det mindste en smag af vinduesfunktioner. Når vi ser nærmere på det, bliver det imidlertid klart, at de fleste leverandører kun implementerer et delsæt af standarden. Lad os tage den kraftfulde RANGE -klausul som et eksempel. Kun Oracle, DB2, Spark/Hive og Google Big Query implementerer denne funktion fuldt ud. For nylig har leverandører tilføjet nye udvidelser til standarden, f.eks. Array -aggregeringsfunktioner. Disse er især nyttige i forbindelse med at køre SQL mod et distribueret filsystem (Hadoop, Spark, Google BigQuery), hvor vi har svagere data-co-lokalitetsgarantier end på en distribueret relationsdatabase (MPP). I stedet for jævnt at distribuere dataene på tværs af alle noder, kan SQL-motorer, der kører forespørgsler mod et distribueret filsystem, opnå data-samlokalitetsgarantier ved at indlejre data og dermed undgå potentielt dyre joins, der involverer kraftig blanding på tværs af netværket. Brugerdefinerede samlede funktioner, der kan bruges i vinduesfunktioner, er en anden ekstremt kraftfuld funktion.

Generering af data i T-SQL

Metode til at generere data baseret på fagforeningen alle

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 understøtter den "rækkonstruktør", der er angivet i standarden SQL3 ("SQL: 1999")

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

Referencer

Kilder

• Vandret og lodret partitionering, Microsoft SQL Server 2000 Books Online.

eksterne links

• Vinduesborde og vinduesfunktion i SQL , Stefan Deßloch
• Oracle SELECT syntaks
• Firebird SELECT syntaks
• MySQL SELECT syntaks
• PostgreSQL SELECT syntaks
• SQLite SELECT syntaks