SQL:SELECT-spørringer - UiO

SQL: SELECT-spørringer

Evgenij Thorstensen

V18

Evgenij Thorstensen SQL: SELECT-spørringer V18 1 / 29

Relasjonsalgebra, recap

Tre hovedoperatorer i tillegg til mengdeoperatorer:

Seleksjon σθProjeksjon πAKartesisk produkt og θ-join ./θ

SQL-spørringer bygges i hovedsak via disse, men deklarativt.

DBMS-et bygger så et relalg-uttrykk fra spørringen, en såkalt queryplan.


SQL

Det intergalaktiske dataspeaket har flere del-språk:

Data Query Language: SELECT-spørringer

Data Definition Language: CREATE-spørringer, lager skjema

Data Control Language: GRANT/REVOKE, datatilgang ogbrukere

Data Manipulation Language: INSERT/UPDATE, skrivespørringer

Detaljer finnes her:https://www.postgresql.org/docs/9.2/static/sql.html


https://www.postgresql.org/docs/9.2/static/sql.html

Et eksempel

SELECT p.Navn, m.Id FROM Person p JOIN Movie m ONp.Id = m.ActorId

WHERE m.DirectorId = 1234;

Select-project-join-spørring: SELECT attributter FROM tabellWHERE betingelse.

[Person p JOIN Movie m ON p.Id = m.ActorId] er et tabelluttrykk.

Vi lager tabeller via joins, og betingelser via operatorer og logikk.


Joins

I SQL har vi NULL; det medfører at vi har noen ekstra join-typer.

JOIN

RIGHT JOIN

LEFT JOIN

FULL JOIN

JOIN er som i relasjonsalgebra; bare tupler som oppfyller betingelser.

[R LEFT JOIN S ON R.a = S.b] er en JOIN som beholder alle tuplerfra R. De som ikke matcher noe i S får NULL-verdier i alle S-kolonner.

Hva er RIGHT og OUTER?


Self-join

“Print navn på alle ansatte og hver ansattes sjef” gitt Ansatt(Id,Navn), Leder(ansId, sjefId).

--Feil!

SELECT a.Navn, a.Navn FROM Ansatt a JOIN Leder ONansId=Id AND sjefId=Id;

Åpenbart noe fryktelig galt i spørringen over. Vi trenger to forskjelligenavn!


Self-join

“Print navn på alle ansatte og hver ansattes sjef” gitt Ansatt(Id,Navn), Leder(ansId, sjefId).

Vi trenger en ekstra kopi av Ansatt:

--Riktig

SELECT a.Navn, s.Navn FROM Ansatt a

JOIN Leder ON ansId=a.Id

JOIN Ansatt s ON s.Id = sjefId;


Fallgruver joins

En veldig lite hjelpsom kortform for JOIN: FROM Tabell1, Tabell2,Tabell3 WHERE <masse betingelser>;

Veldig lett å rote seg bort. Skriv heller JOIN ON for hver tabell.

Self og andre joins: Husk at WHERE evalueres på enkeltrader itabellen laget i FROM.


Betingelser

Boolske operatorer på verdier, settes sammen via logiske operatorer.

I tillegg til kolonner/verdier kan man bruke scalar subqueries; dette eren spørring som returnerer en tabell med en rad og en kolonne.

Fallgruve med betingelser: NULL. [NULL = NULL] evaluerer til false.

https://www.postgresql.org/docs/9.2/static/

functions-comparison.html


https://www.postgresql.org/docs/9.2/static/functions-comparison.html

https://www.postgresql.org/docs/9.2/static/functions-comparison.html

NULL, en historie

C. A. R. Hoare sa i 2009:I call it my billion-dollar mistake. It was the invention ofthe null reference in 1965. At that time, I was designingthe first comprehensive type system for references in anobject oriented language (ALGOL W). My goal was toensure that all use of references should be absolutely safe,with checking performed automatically by the compiler.But I couldn’t resist the temptation to put in a nullreference, simply because it was so easy to implement.This has led to innumerable errors, vulnerabilities, andsystem crashes, which have probably caused a billiondollars of pain and damage in the last forty years.


NULL-problematikk

NULL som del av et aritmetisk uttrykk, gir NULL som svar

Sammenlikning av NULL med en verdi, gir unknown som svar.Unntak: is distinct from (som er omtrent som <>, mensammenlikner som om null var en verdi)

Det er ikke lov å bruke NULL eksplisitt som del av et uttrykk. Vikan spørre om resultatet av en beregning er NULL: [X IS NULL],[X IS NOT NULL]

Egne regler for gruppering med NULL senere.


NULL-håndtering

Noen nyttige constructs:

COALESCE(Expr1, Expr2, ...) returnerer første argument som ikke erNULL; NULL hvis alle er NULL.

Typisk bruk: COALESCE(Adresse, ’Ikke oppgitt’)

NULLIF(Expr1, Expr2) returnerer NULL hvis argumentene er like,ellers returneres Expr1.


Mer betingelser

EXISTS(Spørring) — boolsk

SOME/ANY(Spørring) og ALL(Spørring)

SELECT p.navn FROM Person p WHERE p.formue > ALL(SELECT pq.formue FROM Person pq WHERE pq.

alder = 30));

Kan brukes mot flere attributter via ROW: WHERE ROW(A, B) >ALL(her må vi ha to kolonner)

Viktig: ALL sammenlikner en og en verdi/rad med en hel tabell. Kanikke brukes til å sjekke flere rader samtidig mot en tabell.


La oss teste våre ferdigheter

Med det vi har sett sålangt kan vi gjøre en hel del.

Prosjekt(prnr, pnavn, kunde, pleder, startDato)Ansatt(anr, navn, tittel, fdato, pnr, ansDato)Timeliste(anr, dato, prnr, timer)Kunde(knr, knavn, adresse)

Finn navn og tittel på de ansatte som har arbeidet på minst ettprosjekt som er påbegynt etter 2014 og er bestilt av kunden ’ABC’.


Divide and conquer


--Alle prosjekter per betingelseWITH RiktigeProsjekter AS (SELECT p.prnr FROM Prosjekt p JOIN Kunde k ON p.kunde = k.knr WHERE

p.startDato>’2014-12-31’ AND k.knavn = ’ABC’), AnsattePaProsjekter AS (

--Hvem var pa hvilke prosjekterSELECT a.navn, a.tittel, t.prnr FROM Ansatt a JOIN Timeliste t ONt.anr = a.anr

)--Og sett de to sammenSELECT app.navn, app.tittel FROM AnsattePaProsjekter app JOIN

RiktigeProsjekter rp ON app.prnr = rp.prnr;


En vanskelig type oppgave

Samme skjema:


Finn navn og tittel på de ansatte som har arbeidet på alle prosjektersom er påbegynt etter 2014 og er bestilt av kunden ’ABC’.

Fristende å prøve seg med ALL; men funker ikke!

Triks: ∀xP(x) ≡ ¬∃x¬P(x). Vi vil ha de ansatte som IKKE har noeriktig prosjekt de IKKE har jobbet på.


Trenger fremdeles riktige prosjekter


--Alle prosjekter per betingelseCREATE VIEW RiktigeProsjekter AS (

SELECT p.prnr FROM Prosjekt p JOIN Kunde k ONp.kunde = k.knr WHERE p.startDato>’2014-12-31’ AND k.knavn = ’ABC’

);

Vi vil ha (Ansatt ./ Timeliste) div RiktigeProsjekter.


Løsning

--Ansatte som IKKE har noe (riktig prosjekt de

IKKE har jobbet pa)

SELECT a.navn, a.tittel FROM Ansatt a WHERE NOTEXISTS (

SELECT * FROM RiktigeProsjekter rp WHERE NOTEXISTS (

SELECT * FROM Timeliste t WHEREt.prnr = rp.prnr AND t.anr = a.anr));

Innerst select kjøres på nytt for hver ansatt og prosjekt. Kalleskorrelert spørring.

Kunne også ha brukt litt telling, eller en LEFT JOIN.


AggregeringCOUNT, SUM, etc. Et eksempel:

SELECT p.Navn, COUNT(m.id) AS ant FROM Person p

JOIN Movie m ON p.Id = m.ActorId

WHERE m.DirectorId = 1234

GROUP BY p.Id, p.Navn

HAVING ant>10;

Aggregater slår sammen mange tupler til ett tuppel. Hvilke tupler?

Først beregnes SELECT-FROM-WHERE

Så leses GROUP BY, og tabellen deles opp i en gruppe per verdiav det som er i GROUP BY

Så beregnes aggregater for hver gruppe → ny tabell, en rad pergruppe

Så filtreres denne på HAVING-betingelser.


Konsekvenser av aggregat-semantikk

Hvis jeg har ikke-aggregater i SELECT, så må jeg ha alle disse iGROUP BY (unntak fra og med pgSQL 9.1: Hvis PK er med iSELECT)

Aggregater kan ikke brukes i WHERE

Uten GROUP BY får man ut maks en rad; aggregerer heletabellen. Sjeldent riktig!

SELECT p.Navn, COUNT(m.id) AS ant FROM Person p JOIN Movie m ON p.Id = m.ActorId

WHERE m.DirectorId = 1234GROUP BY p.Id, p.NavnHAVING ant>10;


Gruppering-eksempelProsjekt(prnr, pnavn, kunde, pleder, startDato)Ansatt(anr, navn, tittel, fdato, pnr, ansDato)Timeliste(anr, dato, prnr, timer)Kunde(knr, knavn, adresse)

--Alle prosjekter per betingelseWITH RiktigeProsjekter AS (SELECT p.prnr FROM Prosjekt p JOIN Kunde k ON p.kunde = k.knr WHERE

p.startDato>’2014-12-31’ AND k.knavn = ’ABC’), UnikeAnsattePaRiktigeProsjekter AS (--Hvem var pa riktige prosjekterSELECT DISTINCT a.anr, a.navn, a.tittel, t.prnr FROM Ansatt a JOIN

Timeliste t ON t.anr = a.anr JOIN RiktigeProsjekter rp ON rp.prnr = t.prnr

)--Og sa teller vi opp antall riktige prosjekter per ansattSELECT app.navn, app.tittel FROM UnikeAnsattePaRiktigeProsjekter appGROUP BY app.anr, app.navn, app.tittelHAVING COUNT(app.prnr) = (SELECT COUNT(*) FROM RiktigeProsjekter);


NULL-problematikk ved aggregering

NULL-verdier ignoreres ved aggregering — viktig med COUNT,unntak er COUNT(*).

De fleste aggregeringsfunksjoner returnerer NULL hvis de evalueresover ingen verdier.


SQL, generell form

SELECT [DISTINCT] <attributtliste>

FROM <tabelluttrykk>

[where <where-betingelser>][GROUP BY <grupperingsattributter>

[HAVING <aggregat-betingelser>] ]

[ORDER BY <attributt> [asc | desc][, <attributt> [asc | desc] ] ... ];


Mye vil ha mer: Arrays

SQL støtter arrays som datatype. Egentlig lister.

CREATE TABLE sal_emp (

name text,

pay_by_quarter integer[4],schedule text[][]

);

Til herligheten medfølger en haug operasjoner.

Arrays kan også brukes i spørringer, array(X, Y, 3) lager en array.


Array-operasjoner

Plukke ut et element: SELECT kodon[2] FROM genomsekvens ...

Konkatenering: g1.kodon || ’ACU’, g1.kodon || g2.kodon

Finne antall elementer: .. WHERE cardinality(kodon) > 100 ..

Sammenlikne eksakt innhold: g1.kodon = g2.kodon, g1.kodon <>g2. kodon

Sammenlikne med hvert element i en array: any, all. For eksempelWHERE kodon[3] = ANY(array[’GGU’, ’UGG’, ’UAA’])...

’Flate ut’ en array: SELECT kromosomnr, unnest(kodon) FROMgenomsekvens;

https://www.postgresql.org/docs/9.2/static/

functions-array.html


https://www.postgresql.org/docs/9.2/static/functions-array.html

https://www.postgresql.org/docs/9.2/static/functions-array.html

Rekursiv SQL

Ikke egentlig rekursiv. Egentlig fikspunkt-SQL (fixpoint).

Tillater oss å kjøre en spørring S på resultatet av S igjen og igjen.

Brukes til å nøste opp graf-aktige data.

WITH RECURSIVE <tabellnavn>(<attributtliste>) AS (

<ikke-rekursiv term>

union | union all -- velg en

<rekursiv term>

)

select * from <tabellnavn>;


Rekursiv SQL, eksempel

Gitt Fly(fraBy, tilBy, carrier, flightnr, pris), finn alle byer man kan nåfra NYC eller Chicago med maks 3 flybytter. Skriv ut antall bytter ogtotalpris.

WITH RECURSIVE Destinasjon(fra, til, antConn, totPris) AS (--Start-tabell gitt av denne sporringenSELECT f.fraBy, f.tilBy, 0, f.prisFROM fly fWHERE f.fraBy = ’New York’ or f.fraBy = ’Chicago’UNION ALL-- Rekursiv term, kjores pa Destinasjon og setter inn nye tupler

der!SELECT d.fra, g.tilBy, d.antconn + 1, d.totpris + g.prisFROM Destinasjon d JOIN Fly g ON d.til = g.fraByWHERE d.antconn < 3

)SELECT * FROM destinasjon;


Rekursiv SQL, semantikk

La Init og Rek være henholdsvis den ikke-rekursive og den rekursivedelen (begge er spørringer).

Først evalueres Init. Resultatet legges i tabellnavnet vi valge(Destinasjon).

Så evalueres Rek over Destinasjon. Hvis den returnerer tupler som ikkeallerede var i Destinasjon, legges de der, og Rek evalueres igjen. Ogigjen, og igjen, til den ikke finner noe nytt.


Rekursiv SQL med array

Array kommer til nytte i løkkehåndtering i rekursive spørringer.

WITH RECURSIVE Destinasjon(fra, til, byoversikt, antconn, totpris)AS (

SELECT f.fraBy, f.tilBy, array[f.fraBy, f.tilBy], 0, f.prisfrom fly fwhere f.fraBy = ’New York’ or f.fraBy = ’Chicago’UNION ALLselect d.fra, g.tilBy, d.byoversikt || g.tilBy, d.antconn + 1, d.

totpris + g.prisfrom destinasjon d, fly gwhere d.til = g.fraBy and g.tilBy <> all(byoversikt)

)SELECT fra, til, antconn, totpris FROM Destinasjon;


SQL:SELECT-spørringer - UiO

Documents

Transcript of SQL:SELECT-spørringer - UiO