Velg (SQL) - Select (SQL)

Den SQL SELECT -setning returnerer et resultat sett med poster, fra en eller flere tabeller .

En SELECT-setning henter null eller flere rader fra en eller flere databasetabeller eller database utsikt . I de fleste applikasjoner SELECTer den mest brukte kommandoen data manipulering språk (DML). Siden SQL er et deklarativt programmeringsspråk , SELECTangir spørringer et resultatsett, men angir ikke hvordan det skal beregnes. Databasen oversetter spørringen til en " spørringsplan " som kan variere mellom henrettelser, databaseversjoner og databaseprogramvare. Denne funksjonaliteten kalles " spørringsoptimereren ", ettersom den er ansvarlig for å finne den best mulige utførelsesplanen for spørringen, innenfor gjeldende begrensninger.

SELECT -setningen har mange valgfrie ledd:

• SELECTledd er listen over kolonner eller SQL -uttrykk som må returneres av spørringen. Dette er omtrent den relasjons algebra projeksjon drift.
• ASgir eventuelt et alias for hver kolonne eller uttrykk i SELECTleddet. Dette er den relasjonelle algebra -omdøpsoperasjonen .
• FROM angir fra hvilken tabell dataene skal hentes.
• WHEREangir hvilke rader som skal hentes. Dette er omtrent på relasjonsalgebra utvalget drift.
• GROUP BYgrupper rader som deler en eiendom, slik at en samlet funksjon kan brukes på hver gruppe.
• HAVING velger blant gruppene definert av GROUP BY -klausulen.
• ORDER BY angir hvordan du skal bestille de returnerte radene.

Oversikt

SELECTer den vanligste operasjonen i SQL, kalt "spørringen". SELECThenter data fra en eller flere tabeller eller uttrykk. Standard SELECTuttalelser har ingen vedvarende effekter på databasen. Noen ikke-standardiserte implementeringer av SELECTkan ha vedvarende effekter, for eksempel SELECT INTOsyntaksen i noen databaser.

Forespørsler lar brukeren beskrive ønskede data, slik at databasesystemet (DBMS) kan utføre planlegging , optimalisering og utførelse av de fysiske operasjonene som er nødvendige for å produsere det resultatet etter eget valg.

En spørring inneholder en liste over kolonner som skal inkluderes i det endelige resultatet, normalt umiddelbart etter SELECTsøkeordet. En stjerne (" *") kan brukes til å angi at spørringen skal returnere alle kolonnene i tabellene du spør. SELECTer den mest komplekse setningen i SQL, med valgfrie søkeord og setninger som inkluderer:

• Det FROMpunkt, noe som indikerer tabellen (e) for å hente data fra. Den FROMklausulen kan inneholde valgfrie JOINunderavsnitt for å angi reglene for å bli med tabeller.
• Den WHEREbestemmelsen inkluderer predikat en sammenligning, noe som begrenser de rader som returneres av spørringen. Den WHEREklausulen eliminerer alle rader fra resultatsettet der sammenligningen predikatet ikke evaluere til True.
• Den GROUP BYklausulen prosjekter rekker å ha felles verdier i et mindre sett med rader. GROUP BYbrukes ofte sammen med SQL -aggregeringsfunksjoner eller for å eliminere dupliserte rader fra et resultatsett. Den WHEREleddet påføres før GROUP BYleddet.
• Den HAVINGbestemmelsen inkluderer predikat en brukes til å filtrere rader som følge av GROUP BYleddet. Fordi det virker på resultatene av GROUP BYklausulen, kan aggregeringsfunksjoner brukes i HAVINGklausulpredikatet.
• De ORDER BYPunkt identifiserer hvilken kolonne [s] for å bruke til å sortere de resulterende data, og i hvilken retning for å sortere dem (stigende eller synkende). Uten en ORDER BYklausul er rekkefølgen på radene som returneres av en SQL -spørring udefinert.
• De DISTINCTsøkeord eliminerer duplisere data.

Følgende eksempel på en SELECTforespørsel returnerer en liste over dyre bøker. Spørringen henter alle rader fra Book bord der prisen kolonnen inneholder en verdi som er større enn 100,00. Resultatet er sortert i stigende rekkefølge etter tittel . Stjernen (*) i valglisten indikerer at alle kolonnene i Bok tabellen bør inkluderes i resultatsettet.

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

Eksemplet nedenfor viser en forespørsel om flere tabeller, gruppering og aggregering, ved å returnere en liste over bøker og antall forfattere som er knyttet til hver bok.

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

Eksempelutgang kan ligne på følgende:

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

Under forutsetning at ISBN er den eneste felles kolonne navnet på de to tabellene og at en kolonne som heter tittelen bare eksisterer i Bok bordet, kan man omskrive spørsmålet ovenfor i følgende form:

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

Imidlertid støtter mange leverandører enten ikke denne tilnærmingen, eller de krever visse konvensjoner om navngivning av kolonner for at naturlige sammenføyninger skal fungere effektivt.

SQL inkluderer operatører og funksjoner for beregning av verdier på lagrede verdier. SQL tillater bruk av uttrykk i valglisten til prosjektdata, som i følgende eksempel, som returnerer en liste over bøker som koster mer enn 100.00 med en ekstra sales_tax kolonne som inneholder en omsetningsavgift tallet beregnet til 6% av prisen .

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

Underforespørsler

Spørringer kan nestes slik at resultatene av en spørring kan brukes i en annen spørring via en relasjonsoperator eller aggregeringsfunksjon. En nestet forespørsel er også kjent som en undersøkelse . Mens joins og andre bordoperasjoner gir beregningsmessig overlegne (dvs. raskere) alternativer i mange tilfeller, introduserer bruk av undersøkelser et hierarki i utførelsen som kan være nyttig eller nødvendig. I følgende eksempel AVGmottar aggregeringsfunksjonen resultatet av en undersøkelse som input:

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

En undersøkelse kan bruke verdier fra den ytre spørringen, i så fall er den kjent som en korrelert undersøkelse .

Siden 1999 tillater SQL -standarden navngitte underforespørsler kalt vanlige tabelluttrykk (navngitt og designet etter implementering av IBM DB2 versjon 2; Oracle kaller disse subquery factoring ). CTE kan også være rekursive ved å referere til seg selv; den resulterende mekanismen tillater tre- eller grafoverganger (når det er representert som relasjoner), og mer generelt fixpoint -beregninger.

Avledet bord

En avledet tabell er bruken av å referere til en SQL -undersøkelse i en FROM -klausul. I hovedsak er den avledede tabellen en undersøkelse som kan velges fra eller kobles til. Avledet tabellfunksjonalitet lar brukeren referere til undersøkelsen som en tabell. Den avledede tabellen blir også referert til som en innebygd visning eller en velg fra listen .

I det følgende eksemplet innebærer SQL -setningen en sammenføyning fra den første boktabellen til den avledede tabellen "Salg". Denne avledede tabellen fanger tilhørende boksalgsinformasjon ved hjelp av ISBN for å bli med i bøkertabellen. Som et resultat, gir den avledede tabellen resultatsettet med flere kolonner (antall solgte varer og selskapet som solgte bøkene):

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

Gitt en tabell T, vil spørringen resultere i at alle elementene i alle radene i tabellen vises. SELECT * FROM T

Med den samme tabellen vil spørringen resultere i at elementene fra kolonnen C1 i alle radene i tabellen vises. Dette ligner på en projeksjon i relasjonsalgebra , bortsett fra at i det generelle tilfellet kan resultatet inneholde dupliserte rader. Dette er også kjent som en vertikal partisjon i noen databasetermer, som begrenser utdata fra spørringen til å vise bare angitte felt eller kolonner. SELECT C1 FROM T

Med den samme tabellen vil spørringen resultere i at alle elementene i alle radene der verdien i kolonne C1 er '1' vises - i relasjonsalgebra -termer vil et valg bli utført på grunn av WHERE -leddet. Dette er også kjent som en horisontal partisjon, som begrenser rader som sendes ut av en spørring i henhold til angitte betingelser. SELECT * FROM T WHERE C1 = 1

Med mer enn én tabell vil resultatsettet være hver kombinasjon av rader. Så hvis to tabeller er T1 og T2, vil det resultere i hver kombinasjon av T1 -rader med hver T2 -rad. For eksempel, hvis T1 har 3 rader og T2 har 5 rader, vil det resultere i 15 rader. SELECT * FROM T1, T2

Selv om det ikke er i standard, tillater de fleste DBMS å bruke en valgt klausul uten en tabell ved å late som om en imaginær tabell med en rad brukes. Dette brukes hovedsakelig til å utføre beregninger der en tabell ikke er nødvendig.

SELECT -setningen spesifiserer en liste over egenskaper (kolonner) etter navn, eller jokertegnet ("*") som betyr "alle egenskaper".

Begrensning av resultatrader

Ofte er det praktisk å angi et maksimalt antall rader som returneres. Dette kan brukes til testing eller for å forhindre bruk av for store ressurser hvis spørringen returnerer mer informasjon enn forventet. Tilnærmingen for å gjøre dette varierer ofte per leverandør.

I ISO SQL: 2003 kan resultatsett begrenses ved bruk

• markører , eller
• ved å legge til en SQL-vindusfunksjon i SELECT-setningen

ISO SQL: 2008 introduserte FETCH FIRSTklausulen.

I følge PostgreSQL v.9 -dokumentasjonen utfører en SQL -vindusfunksjon "en beregning på tvers av et sett med tabellrader som på en eller annen måte er relatert til den nåværende raden", på en måte som ligner på aggregerte funksjoner. Navnet minnes signalprosesseringsvindusfunksjoner . Et vindusfunksjonsanrop inneholder alltid en OVER -klausul.

ROW_NUMBER () vindusfunksjon

ROW_NUMBER() OVERkan brukes til en enkel tabell på de returnerte radene, f.eks. for å returnere ikke mer enn ti rader:

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, hver gang du kjører spørringen, er det mulig å få forskjellige radnummer tilordnet alle rader der sort_key er den samme. Når sort_key er unik, vil hver rad alltid få et unikt radnummer.

RANK () vindusfunksjon

Den RANK() OVERvindusfunksjon fungerer som ROW_NUMBER, men kan komme tilbake mer eller mindre enn n rader i tilfelle av tie betingelser, for eksempel for å returnere de øverste 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

Koden ovenfor kan returnere mer enn ti rader, f.eks. Hvis det er to personer på samme alder, kan den returnere elleve rader.

FETCH FIRST -klausul

Siden ISO SQL: 2008 -resultatgrenser kan spesifiseres som i følgende eksempel ved å bruke FETCH FIRSTklausulen.

SELECT * FROM T 
FETCH FIRST 10 ROWS ONLY

Denne klausulen støttes for tiden av CA DATACOM/DB 11, IBM DB2, SAP SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB versjon 2.0, Oracle 12c og Mimer SQL .

Microsoft SQL Server 2008 og høyere støtterFETCH FIRST , men det regnes som en del av ORDER BYklausulen. Alle ORDER BY, OFFSETog FETCH FIRSTklausuler kreves for denne bruken.

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

Ikke-standard syntaks

Noen DBMS tilbyr ikke-standard syntaks enten i stedet for eller i tillegg til SQL standardsyntaks. Nedenfor er varianter av den enkle grensespørringen for forskjellige DBMSer listet opp:

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

Rader paginering

Rader paginering er en tilnærming som brukes til å begrense og vise bare en del av de totale dataene til et søk i databasen. I stedet for å vise hundrevis eller tusenvis av rader samtidig, blir serveren bedt om bare én side (et begrenset sett med rader, for eksempel bare 10 rader), og brukeren begynner å navigere ved å be om den neste siden, og deretter den neste , og så videre. Det er veldig nyttig, spesielt i nettsystemer, der det ikke er noen dedikert forbindelse mellom klienten og serveren, så klienten trenger ikke å vente med å lese og vise alle radene på serveren.

Data i pagineringstilnærming

• {rows} = Antall rader på en side
• {page_number} = Nummer på gjeldende side
• {begin_base_0} = Nummeret på raden - 1 der siden starter = (sidenummer -1) * rader

Enkleste metode (men veldig ineffektiv)

1. Velg alle radene fra databasen
2. Les alle radene, men send til visning bare når radnummeret til radene som er lest er mellom {begin_base_0 + 1}og{begin_base_0 + rows}

Select * 
from {table} 
order by {unique_key}

En annen enkel metode (litt mer effektiv enn å lese alle radene)

1. Velg alle radene fra begynnelsen av tabellen til den siste raden for å vise ( {begin_base_0 + rows})
2. Les {begin_base_0 + rows}radene, men send til visning bare når radnummeret til radene som er lest er større enn{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 posisjonering

1. Velg bare {rows}rader som starter fra neste rad for å vise ( {begin_base_0 + 1})
2. Les og send for å vise alle radene som er lest 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 mer sofistikert, men nødvendig for veldig store datasett)

1. Velg bare {rows}rader med filter:
   1. Første side: Velg bare de første {rows}radene, avhengig av databasetype
   2. Neste side: Velg bare de første {rows}radene, avhengig av databasetypen, der den {unique_key}er større enn {last_val}(verdien {unique_key}av den siste raden på den gjeldende siden)
   3. Forrige side: sorter dataene i motsatt rekkefølge, velg bare de første {rows}radene, der verdien {unique_key}er mindre enn {first_val}(verdien {unique_key}til den første raden på gjeldende side), og sorter resultatet i riktig rekkefølge
2. Les og send for å vise alle radene som er lest 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 spørring

Noen databaser gir spesialisert syntaks for hierarkiske data .

En vindusfunksjon i SQL: 2003 er en samlet funksjon som brukes på en partisjon av resultatsettet.

For eksempel,

sum(population) OVER( PARTITION BY city )

beregner summen av populasjonene i alle radene som har samme sentrum verdi som den aktuelle rad.

Partisjoner er spesifisert ved bruk av OVER -klausulen som endrer aggregatet. Syntaks:

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

OVER -klausulen kan partisjonere og bestille resultatsettet. Bestilling brukes til ordrerelaterte funksjoner som radnummer.

Spørreevaluering ANSI

Behandlingen av en SELECT -setning i henhold til ANSI SQL vil være følgende:

Støtte for vindusfunksjoner av RDBMS -leverandører

Implementeringen av vindusfunksjoner fra leverandører av relasjonsdatabaser og SQL -motorer er veldig forskjellig. De fleste databaser støtter minst en smak av vindusfunksjoner. Når vi ser nærmere på det, blir det imidlertid klart at de fleste leverandører bare implementerer et delsett av standarden. La oss ta den kraftige RANGE -klausulen som et eksempel. Bare Oracle, DB2, Spark/Hive og Google Big Query implementerer denne funksjonen fullt ut. Mer nylig har leverandører lagt til nye utvidelser til standarden, f.eks. Array -aggregeringsfunksjoner. Disse er spesielt nyttige i sammenheng med å kjøre SQL mot et distribuert filsystem (Hadoop, Spark, Google BigQuery) der vi har svakere data-samlokalitetsgarantier enn på en distribuert relasjonsdatabase (MPP). I stedet for å fordele dataene jevnt på tvers av alle noder, kan SQL-motorer som kjører forespørsler mot et distribuert filsystem, oppnå data-samlokalitetsgarantier ved å hekke data og dermed unngå potensielt dyre sammenføyninger som involverer kraftig blanding over nettverket. Brukerdefinerte aggregerte funksjoner som kan brukes i vindusfunksjoner er en annen ekstremt kraftig funksjon.

Generere data i T-SQL

Metode for å generere data basert 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 støtter "radkonstruktøren" spesifisert i SQL3 -standarden ("SQL: 1999")

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

Referanser

Kilder

• Horisontal og vertikal partisjonering, Microsoft SQL Server 2000 Books Online.

Eksterne linker

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