Nestede og koblede underspørringer i SQL, EKSISTERER predikat. Bruke EXISTS-operatoren Spørringer ved hjelp av exists-funksjonen
HVOR FINNES
Underspørringen kontrolleres for tilstedeværelse av én eller flere rader. Hvis minst én rad samsvarer med søket, returneres den boolske verdien TRUE. Når det valgfrie NOT-nøkkelordet er spesifisert, returneres den boolske verdien TRUE hvis underspørringen ikke returnerer noen samsvarende rader.
underspørring
Basert på den fullstendige underspørringen, hentes det resulterende datasettet.
Generelle regler
EXISTS-operatoren tester eksistensen av én eller flere rader i en underspørring til en overordnet spørring.
VELG * FRA jobber HVOR IKKE FINNES (VELG * FRA ansatt WHERE jobs.job_id=employye.job_id);
Dette eksemplet sjekker postunderspørringen ved å bruke det ekstra NOT-nøkkelordet. Følgende eksempel søker etter spesifikke poster i en underspørring for å hente hovedresultatsettet.
VELG au_lname FRA forfattere HVOR FINNES (VELG * FRA utgivere WHERE forfattere.by=utgivere.by);
Denne spørringen returnerer etternavnene til forfattere (au_lname) som bor i samme by som utgiverne. Merk at du kan bruke en stjerne i underspørringen fordi underspørringen må returnere bare én post med den boolske verdien TRUE. I slike tilfeller spiller ikke kolonner noen rolle. Nøkkelpunktet er eksistensen av strengen.
I mange spørringer utfører EXISTS-operatøren samme funksjon som ALLE. EXISTS-operatoren er vanligvis mest effektiv når den brukes med korrelerte spørringer.
EXISTS-operatoren er semantisk ekvivalent med ALLE-operatoren.
En underspørring i en EXISTS-setning utfører vanligvis én av to typer søk. Det første alternativet er å bruke et jokertegn - en stjerne (for eksempel SELECT * FROM...), i så fall henter du ikke noen spesifikk kolonne eller verdi. Stjernen betyr her "en hvilken som helst kolonne". Det andre alternativet er å velge bare én spesifikk kolonne i underspørringen (for eksempel SELECT aujd FROM). Noen individuelle plattformer tillater underspørringer på flere kolonner (f.eks. SELECT aujd, aujname FROM...). Denne funksjonen er imidlertid sjelden og bør unngås i kode som må porteres til andre plattformer.
Forskjeller mellom plattformer
Alle plattformer støtter EXISTS-operatøren i formen vi beskrev ovenfor.
"Det var enklere før" - tenkte jeg da jeg satte meg ned for å optimalisere neste spørring i SQL ledelsesstudio. Da jeg skrev under MySQL, var alt veldig enklere - enten fungerer det eller ikke. Enten bremser den ned eller så gjør den det ikke. Forklar løste alle problemene mine, ingenting mer var nødvendig. Nå har jeg et kraftig miljø for å utvikle, feilsøke og optimalisere spørringer og prosedyrer/funksjoner, og alt dette rotet skaper bare flere problemer etter min mening. Og hvorfor alle? Fordi den innebygde spørringsoptimereren er ond. Hvis jeg skriver i MySQL og PostgreSQL
Velg * fra a, b, c der a.id = b.id, b.id = c.id
og hver av nettbrettene vil ha minst 5k linjer - alt vil fryse. Og takk Gud! For ellers utvikler utvikleren i beste fall latskap til å skrive riktig, og i verste fall forstår han ikke hva han gjør i det hele tatt! Tross alt vil den samme spørringen i MSSQL fungere på samme måte
Velg * fra a join b on a.id = b.id join c on b.id = c.id
Den innebygde optimizeren vil finkjemme den overflødige forespørselen og alt vil være i orden.
Han vil også selv bestemme hva som er bedre å gjøre - eksistere eller bli med og mye mer. Og alt vil fungere så optimalt som mulig.
Det er bare ett MEN. På et tidspunkt vil optimizeren snuble over komplekst søk og passerer, og da får du et kjempeproblem. Og du får det kanskje ikke med en gang, men når vekten på bordene når kritisk masse.
Så her er til poenget med artikkelen. eksisterer og i er svært tunge operasjoner. Dette er faktisk en egen underspørring for hver resultatlinjer. Og hvis det også er hekking, er det vanligvis å slukke lysene. Alt vil være i orden når 1, 10, 50 linjer er returnert. Du vil ikke føle forskjellen, og kanskje blir det enda tregere. Men når 500 er trukket ut, begynner problemene. 500 underspørringer i én forespørsel er alvorlig.
Selv om fra et synspunkt av menneskelig forståelse, i og eksisterer er bedre, men fra et synspunkt av tidskostnader for spørringer returnerer 50+ rader, er de ikke akseptable.
Det er nødvendig å ta forbehold om at hvis det minker et sted, må det naturlig nok komme et sted. Ja, join er mer minnekrevende, fordi å holde hele verditabellen samtidig og operere med den er dyrere enn å kjøre underspørringer for hver rad, noe som raskt frigjør minne. Du må se spesifikt på forespørselen og måle om bruken av ekstra minne for tidens skyld vil være kritisk eller ikke.
Jeg vil gi eksempler på komplette analogier. Generelt sett har jeg ennå ikke støtt på spørsmål av en slik grad av kompleksitet som ikke kunne utvides til en kaskade av sammenføyninger. Det kan ta en dag, men alt kan avsløres.
Velg * fra a hvor a.id in (velg id fra b) velg * fra a hvor eksisterer (velg topp 1 1 fra b hvor b.id = a.id) velg * fra a join b på a.id = b. id velg * fra a hvor a.id ikke er i (velg id fra b) velg * fra a hvor ikke eksisterer (velg topp 1 1 fra b hvor b.id = a.id) velg * fra venstre sammenføyning b på a. id = b.id der b.id er null
Jeg gjentar - MSSQL-optimalisatoren optimaliserer disse eksemplene for maksimal ytelse og det vil aldri være dumme mennesker med så enkle forespørsler.
La oss nå vurdere et eksempel på en ekte spørring som måtte skrives om fordi den rett og slett frøs på noen prøver (strukturen er veldig forenklet og konseptene er erstattet, det er ingen grunn til å være redd for noe uoptimalitet i databasestrukturen ).
Du må trekke ut alle dupliserte "produkter" i forskjellige kontoer, med fokus på parametrene til produktet, dets gruppe og overordnede gruppe, hvis det er en.
Velg d.PRODUCT_ID fra PRODUCT s, PRODUCT_GROUP sg venstre bli med M_PG_DEPENDENCY sd on (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP dg left join M_PG_DEPENDENCY dd on (dg.CY_GROUP_DE) PRODUCT_GROUP_ID=sg .PRODUCT_GROUP_ID og d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID og sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC og sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME og s.PRODUCT_NAME=d.PRODUCT_NAME og s.PRODUCT_PRODUCT_TYPE=d.CU_IS_TYPE=d.CU_IS_TYPE=d.CU_IS_TYPE=d. og s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT og dg.PRODUCT_GROUP_IS_TMPL=0 og ((sd.M_PG_DEPENDENCY_CHILD_ID er null og dd.M_PG_DEPENDENCY_CHILD_ID er null) eller eksisterer (velg 1 fra PRODUCT_GROUP sg.pg.END_Group sg.END_Group s.END_Group sg.END_Group, PRODUCT_GROUP sg.END ID = sg1.PRODUCT_GROUP_ID og dd .M_PG_DEPENDENCY_PARENT_ID = dg1.PRODUCT_GROUP_ID og sg1.PRODUCT_GROUP_PERSPEC=dg1.PRODUCT_GROUP_PERSPEC og sg1.PRODUCT_GROUP_NAME=dg1.PRODUCT_GROUP_NAME og))
Så dette er tilfelle når optimizeren ga opp. Og for hver linje ble det henrettet en tung eksisterer, som drepte databasen.
Velg d.PRODUCT_ID fra PRODUCT s bli med i PRODUCT d på s.PRODUCT_TYPE=d.PRODUCT_TYPE og s.PRODUCT_NAME=d.PRODUCT_NAME og s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE og s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT bli med i PRODUCT_GROUP_GROUP_ID. sg.PRODUCT_GROUP_ID bli med i PRODUCT_GROUP dg på d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID og sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME og sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC forlot bli med i M_PG_DEPEND ENCY_PG_CYP_EN LD_ID venstre bli med M_PG_DEPENDENCY dd på dg. PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID igjen bli med i PRODUCT_GROUP sgp på sgp.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID igjen bli med i PRODUCT_GROUP dgp på dgp.PRODUCT_GROUP_ID = dd_END_CY_PG_DEGROUP og DUPARCITY_Group p.PRODUCT_GROUP_NAME og isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull( dgp. PRODUCT_GROUP_IS_TMPL, 0) hvor (sd.M_PG_DEPENDENCY_CHILD_ID er null og dd.M_PG_DEPENDENCY_CHILD_ID er null) eller (sgp.PRODUCT_GROUP_NAME er ikke null og dgp.PRODUCT_GROUP_NAME er ikke null) go
Etter disse transformasjonene økte ytelsen til visningen eksponentielt med antallet produkter som ble funnet. Eller rettere sagt, søketiden forble praktisk talt uavhengig av antall treff og var alltid veldig liten. Som det skal være.
Dette er et tydelig eksempel på hvordan det å stole på MSSQL-optimalisatoren kan spille en grusom spøk. Ikke stol på ham, ikke vær lat, bli med manuelt, tenk hver gang hva som er bedre i en gitt situasjon - eksisterer, er med eller blir med.
SQL-språkpredikatet EKSISTERER utfører en logisk oppgave. I SQL-spørringer dette predikatet brukes i uttrykk for formen
EKSISTERER (VELG * FRA TABLE_NAME ...).
Dette uttrykket returnerer sant når spørringen finner én eller flere rader som samsvarer med betingelsen, og returnerer usann når ingen rader blir funnet.
For IKKE FINNES det er omvendt. Uttrykk
FINNES IKKE (VELG * FRA TABLE_NAME ...)
returnerer sann når ingen rader er funnet i spørringen, og usann når minst én rad er funnet.
De enkleste spørringene med SQL EXISTS-predikatet
I eksemplene jobber vi med bibliotekdatabasen og dens "Bok i bruk" (BOOKINUSE) og "Bruker" (BRUKER)-tabeller. Foreløpig trenger vi bare "Bok i bruk"-tabellen (BOOKINUSE).
| Forfatter | Tittel | Pubår | Inv_No | Bruker-ID |
| Tolstoj | Krig og fred | 2005 | 28 | 65 |
| Tsjekhov | Kirsebærhagen | 2000 | 17 | 31 |
| Tsjekhov | Utvalgte historier | 2011 | 19 | 120 |
| Tsjekhov | Kirsebærhagen | 1991 | 5 | 65 |
| Ilf og Petrov | De tolv stolene | 1985 | 3 | 31 |
| Majakovskij | Dikt | 1983 | 2 | 120 |
| Pastinakk | Doktor Zhivago | 2006 | 69 | 120 |
| Tolstoj | søndag | 2006 | 77 | 47 |
| Tolstoj | Anna Karenina | 1989 | 7 | 205 |
| Pushkin | Kapteinens datter | 2004 | 25 | 47 |
| Gogol | Spiller | 2007 | 81 | 47 |
| Tsjekhov | Utvalgte historier | 1987 | 4 | 205 |
| Pastinakk | Favoritter | 2000 | 137 | 18 |
Eksempel 1. Bestem ID-ene til brukere som fikk Tolstojs bøker og som også fikk Tsjekhovs bøker. Den ytre spørringen velger data om brukere som fikk Tolstojs bøker, og EXISTS-predikatet spesifiserer en tilleggsbetingelse som sjekkes i den indre spørringen – brukere som har fått Tsjekhovs bøker. En tilleggsbetingelse i den interne forespørselen er at bruker-IDene fra de eksterne og interne forespørselene samsvarer med: User_ID=tols_user.user_id. Forespørselen vil være som følger:
Denne spørringen vil returnere følgende resultat:
Forskjeller mellom EXISTS og IN-predikater
Ved første øyekast på spørsmål med EXISTS-predikatet kan du få inntrykk av at det er identisk predikat IN. Dette er feil. Selv om de er veldig like. IN-predikatet søker etter verdier fra området spesifisert i argumentet, og hvis det er slike verdier, velges alle rader som tilsvarer dette området. Resultatet av EXISTS-predikatet er et "ja" eller "nei" svar på spørsmålet om det er noen verdier i det hele tatt som tilsvarer de som er spesifisert i argumentet. I tillegg innledes IN-predikatet av navnet på kolonnen for å søke etter rader som samsvarer med verdiene i området. La oss se på et eksempel som viser forskjellen mellom EXISTS-predikatet og IN-predikatet, og problemet løst ved å bruke IN-predikatet.
Eksempel 4. Bestem ID-ene til brukere som har fått utstedt bøker av forfattere hvis bøker har blitt utstedt til brukeren med ID 31. Forespørselen vil være som følger:
| Bruker-ID |
| 120 |
| 65 |
| 205 |
En intern spørring (etter IN) velger forfattere: Tsjekhov; Ilf og Petrov. Den eksterne spørringen velger alle brukere som har fått utstedt bøker av disse forfatterne. Vi ser at, i motsetning til EXISTS-predikatet, innledes IN-predikatet av navnet på kolonnen, i dette tilfellet - Forfatter.
Forespørsler med EXISTS-predikatet og tilleggsbetingelser
Hvis du i tillegg til EXISTS-predikatet i spørringen bruker minst én tilleggsbetingelse, for eksempel spesifisert ved å bruke aggregerte funksjoner, så kan slike spørringer tjene til enkel dataanalyse. La oss demonstrere dette med følgende eksempel.
Eksempel 5. Bestem ID-ene til brukere som har fått utstedt minst én bok av Pasternak, og som har fått utstedt mer enn 2 bøker. Vi skriver følgende spørring, der den første betingelsen er spesifisert av EXISTS-predikatet med en nestet spørring, og den andre betingelsen med HAVING-operatoren må alltid komme etter den nestede spørringen:
Resultat av forespørselen:
| Bruker-ID |
| 120 |
Som det fremgår av BOKINUS-tabellen, ble Pasternaks bok også utstedt til brukeren med ID 18, men han fikk bare én bok og er ikke inkludert i prøven. Hvis du bruker COUNT-funksjonen på en lignende spørring igjen, men denne gangen for å telle de valgte radene (øv på dette selv), kan du få informasjon om hvor mange brukere som leser Pasternaks bøker som også leser bøker av andre forfattere. Dette er allerede fra feltet dataanalyse.
Spørringer med EXISTS-predikat på to tabeller
Forespørsler med EXISTS-predikatet kan hente data fra mer enn én tabell. Mange problemer kan løses med samme resultat ved å bruke BLI MED operatør, men i noen tilfeller lar bruk av EXISTS deg lage et mindre tungvint søk. Det er å foretrekke å bruke EXISTS i tilfeller der den resulterende tabellen vil inneholde kolonner fra kun én tabell.
I det følgende eksempelet, fra samme database, vil du i tillegg til BOKINUSE-tabellen også trenge en BRUKER-tabell.
Resultatet av spørringen vil være følgende tabell:
| Forfatter |
| Tsjekhov |
| Majakovskij |
| Pastinakk |
Som med bruk av JOIN-operatoren, i tilfeller der det er mer enn én tabell, bør du bruke tabellaliaser for å sjekke at verdiene til nøklene som forbinder tabellene stemmer overens. I vårt eksempel er tabellaliasene bk og us, og nøkkelen som forbinder tabellene er User_ID.
EKSISTERER predikat i sammenføyninger av mer enn to tabeller
Nå skal vi se mer detaljert hvorfor det er å foretrekke å bruke EXISTS i tilfeller der den resulterende tabellen vil inneholde kolonner fra kun én tabell.
Vi jobber med databasen "Eiendom". Avtaletabellen inneholder data om avtaler. For våre oppgaver vil Type-kolonnen med data om transaksjonstype - salg eller leie - være viktig i denne tabellen. Objekttabellen inneholder data om objekter. I denne tabellen trenger vi verdiene til Rom (antall rom) og LogBalc-kolonnene, som inneholder data om tilstedeværelsen av en loggia eller balkong i boolsk format: 1 (ja) eller 0 (nei). Tabellene Kunde, Forvalter og Eier inneholder data om henholdsvis kunder, bedriftsledere og eiendomseiere. I disse tabellene er FName og LName henholdsvis for- og etternavn.
Eksempel 7. Identifiser kunder som har kjøpt eller leid eiendommer som ikke har loggia eller balkong. Vi skriver følgende spørring, der EXISTS-predikatet spesifiserer en tilgang til resultatet av å slå sammen to tabeller:
Siden kolonner velges fra klienttabellen ved hjelp av stjerneoperatoren, vil alle kolonnene i denne tabellen vises, som vil ha like mange rader som det er klienter som samsvarer med betingelsen spesifisert av EXISTS-predikatet. Vi trenger ikke å sende ut noen kolonner fra tabellene hvis sammenføyning er tilgjengelig av underspørringen. Derfor, for å spare maskintid, hentes kun én kolonne. For å gjøre dette skrives en enhet etter ordet SELECT. Den samme teknikken brukes i spørringene i de følgende eksemplene.
Skriv en SQL-spørring med EXISTS-predikatet selv, og se deretter på løsningen
Vi fortsetter å skrive SQL-spørringer sammen med EXISTS-predikatet
Eksempel 9. Bestem eierne av objektene som ble leid. Vi skriver følgende spørring, der EXISTS-predikatet også spesifiserer en tilgang til resultatet av å slå sammen to tabeller:
Som i forrige eksempel vil alle felt fra tabellen som den eksterne spørringen har tilgang til, returneres.
Eksempel 10. Bestem antall eiere hvis eiendommer ble håndtert av manager Savelyev. Vi skriver en spørring der den ytre spørringen får tilgang til en sammenføyning av tre tabeller, og EXISTS-predikatet spesifiserer tilgang til bare én tabell:
Alle spørringer sjekkes mot en eksisterende database. Vellykket bruk!
Relasjonsdatabaser og SQL-språk
Novosibirsk State Academy of Economics and Management
LABORATORIEPRAKTIKK OM DISIPLINER
"DATABASE"
Laboratoriearbeid nr. 7
"Språket til baser SQL-data: datamanipulasjonskommandoer»
NOVOSIBIRSK 2000
SQL er en forkortelse for Structured Query Language. Fra navnet på språket er det klart at hovedformålet er å generere spørringer for å få informasjon fra en database. Kommandoer for å hente data danner grunnlaget for datamanipuleringsspråket DML – en integrert del av SQL-språket. DML består imidlertid av mer enn bare kommandoer for å hente data fra en database. Det er også kommandoer for datamodifisering, dataadministrasjon og andre.
Laboratoriearbeidet undersøker de grunnleggende verktøyene i DML-språket. I prosess laboratoriearbeid vi vil holde oss til SQL2-standarden.
På grunn av det faktum at SQL er et stort språk, vil vi kun vurdere de grunnleggende kommandoene. Ulike spesifikke SQL-verktøy dekkes i påfølgende laboratorier.
For å utføre laboratoriearbeid kreves kunnskap om det grunnleggende om relasjonsdatamodellen, det grunnleggende om relasjonsalgebra og relasjonsregning, og prinsippene for arbeid med MS SQL Server DBMS.
Som et resultat av å fullføre laboratoriearbeidet, vil du mestre metodene for å manipulere data ved hjelp av SQL-språkkommandoer, vurdere dialekten til språket implementert i MS SQL Server DBMS.
INTRODUKSJON
SQL inneholder et bredt spekter av datamanipuleringsmuligheter, både for å lage spørringer og for å oppdatere databasen. Disse egenskapene er bare avhengige av den logiske strukturen til databasen, ikke på dens fysiske struktur, som er i samsvar med kravene til relasjonsmodellen.
Den opprinnelige strukturen til SQL-syntaksen var (eller så i det minste ut til å være) basert på Codds relasjonskalkulus. Den eneste støttede operasjonen i relasjonsalgebra var union.
I tillegg til den relasjonskalkuluslignende syntaksen utviklet i den forrige standarden, implementerer SQL2 direkte operasjonsunionen, skjæringspunktet, forskjellen og sammenføyningen. Utvalgs-, prosjekt- og produktdriften ble (og blir fortsatt) støttet nesten direkte, mens divisjons- og oppdragsoperasjonen støttes i en mer tungvint form.
Vi vil først beskrive SQL-spørringsspråket og deretter dets datainntasting og modifikasjonsoperasjoner. Datamodifikasjonsoperasjoner vil bli beskrevet sist, siden deres struktur til en viss grad er avhengig av strukturen til spørringsspråket.
Enkle spørsmål
For oss enkel forespørsel det vil være en spørring som bare får tilgang til én tabell i databasen. Enkle spørringer vil hjelpe oss å illustrere den grunnleggende strukturen til SQL.
Enkel forespørsel. En spørring som bare får tilgang til én databasetabell.
Be om: Hvem jobber som plasterere?
WHERE SKILL_TYPE = "Gipser"
Resultat:
G.Rickover
Denne spørringen illustrerer de tre vanligste fraser SQL: SELECT, FROM og WHERE. Selv om vi i vårt eksempel plasserte dem på forskjellige linjer, kan de alle vises på samme linje. De kan også rykkes forskjellig, og ord i fraser kan skilles med et vilkårlig antall mellomrom. La oss se på egenskapene til hver setning.
Plukke ut. SELECT-leddet viser kolonnene som skal vises i den resulterende tabellen. Dette er alltid kolonner i en eller annen relasjonstabell. I vårt eksempel består den resulterende tabellen av én kolonne (NAME), men generelt kan den inneholde flere kolonner; den kan også inneholde beregnede verdier eller konstanter. Vi vil gi eksempler på hvert av disse alternativene. Hvis den resulterende tabellen må inneholde mer enn én kolonne, er alle de nødvendige kolonnene oppført etter SELECT kommandoer separert av kommaer. For eksempel vil uttrykket SELECT WORKER_ID, NAME resultere i en tabell som består av WORKER_ID og NAME kolonnene.
SELECT-klausul. Angir kolonnene i den resulterende tabellen.
Fra. FROM-leddet spesifiserer én eller flere tabeller som søket får tilgang til. Alle kolonnene som er oppført i SELECT- og WHERE-klausulene må eksistere i en av tabellene som er oppført i FROM-kommandoen. I SQL2 kan disse tabellene defineres direkte i skjemaet som basistabeller eller datavisninger, eller de kan i seg selv være navnløse tabeller som er et resultat av SQL-spørringer. I sistnevnte tilfelle er forespørselen eksplisitt gitt i FROM-kommandoen.
FRA-setningen. Spesifiserer de eksisterende tabellene som søket får tilgang til.
Hvor. WHERE-klausulen inneholder en betingelse. på grunnlag av hvilke radene i tabellen(e) velges. I vårt eksempel er betingelsen at SKILL_TYPE-kolonnen må inneholde konstanten "Plasterer" omsluttet av apostrof, slik det alltid gjøres med tekstkonstanter i SQL. WHERE-leddet er den mest flyktige SQL-kommandoen; den kan inneholde mange forskjellige forhold. Mye av diskusjonen vår vil bli viet til å illustrere de forskjellige konstruksjonene som er tillatt i WHERE-kommandoen.
WHERE-klausul. Angir betingelsen basert på hvilke rader som velges fra de angitte tabellene.
SQL-spørringen ovenfor behandles av systemet i følgende rekkefølge: FROM, WHERE, SELECT. Det vil si at radene i tabellen spesifisert i FROM-kommandoen plasseres i arbeidsområdet for behandling. WHERE-leddet brukes deretter på hver rad i rekkefølge. Alle rader som ikke tilfredsstiller WHERE-betingelsen er ekskludert fra vurdering. Deretter blir de radene som tilfredsstiller WHERE-betingelsen behandlet av SELECT-setningen. I vårt eksempel er NAME valgt fra hver slik rad, og alle valgte verdier sendes ut som søkeresultater.
Be om: Gi all informasjon om kontorbygg.
WHERE TYPE = "Kontor"
Resultat:
BLDG IDADRESSTYPEQLTYNIVÅSTATUS
312 Elm St., 123 Office 2 2
210 Berezovaya st. 1011 Office Z 1
111 Osinovaya st. 1213 Office 4 1
En stjerne (*) i en SELECT-kommando betyr "hele raden." Dette er en praktisk stenografi som vi vil bruke ofte.
Be om: Hva er ukelønnen for hver elektriker?
VELG NAVN, "Ukelønn = ", 40 * HRLY_RATE
WHERE SKILL_TYPE = "Elektriker"
Resultat:
M. Faraday Ukelønn = 500,00
H.Columbus Ukelønn = 620,00
Denne spørringen illustrerer bruken av både tegnkonstanter (i vårt eksempel "Ukelønn = ") og beregninger i SELECT-kommandoen. I SELECT-setningen kan du utføre beregninger som bruker numeriske kolonner og numeriske konstanter, samt standard aritmetiske operatorer ( +, -, *, /), gruppert etter behov ved hjelp av parenteser. Vi har også tatt med en ny ORDER kommando BY, som sorterer søkeresultatet i stigende alfanumerisk rekkefølge etter den angitte kolonnen. Hvis du vil sortere resultatene i synkende rekkefølge, må du legge til DESC i kommandoen. ORDER BY-leddet kan sortere resultatene etter flere kolonner, noen i stigende rekkefølge og andre i synkende rekkefølge. Sorteringens primærnøkkelkolonne er oppført først.
Karakterkonstant. En konstant som består av bokstaver, tall og "spesielle" tegn.
Be om: Hvem har en timepris på $10 til $12?
HVOR HRLY_RATE > = 10 OG HRLY_RATE< - 12
Resultat:
ARBEIDERS ID NAME HRLY_RATE SKILL_TYPE SUPV_ID
Denne spørringen illustrerer noen av tilleggsfunksjonene til WHERE-setningen: sammenligningsoperatorer og den boolske AND-operatoren. Seks sammenligningsoperatorer (=,<>(ikke lik),<, >, <=, >=). De boolske operatorene AND, OR og NOT kan brukes til å lage sammensatte betingelser eller for å negere en betingelse. Parenteser kan brukes til å gruppere forhold, slik det er vanlig i programmeringsspråk.
Sammenligningsoperatorer =,<>, <, >, <=, >=.
boolske operasjoner OG (OG), ELLER (ELLER) og IKKE (HAN) .
Du kan også bruke BETWEEN (between) operatoren for å formulere denne spørringen:
WHERE HRLY_RATE MELLOM 10 OG 12
BETWEEN kan brukes til å sammenligne en mengde med to andre mengder, hvorav den første er mindre enn den andre, hvis den sammenlignede mengden kan være lik hver av disse mengdene eller en hvilken som helst verdi i mellom.
Forespørsel: Liste gipsere, taktekkere og elektrikere.
WHERE SKILL_TYPE IN ("gipser", "Tekker", "elektriker")
Resultat:
WORKER_ID NAME HRLY_RATE SKILL_TYPE SUPV_ID
1412 K.Nemo 13,75 Gipser 1520
2920 R. Garrett 10,00 Taktekker 2920
1520 G. Rickover 11,75 Gipser 1520
Denne spørringen forklarer bruken av sammenligningsoperatoren IN (B). WHERE-betingelsen anses som sann hvis radens spesialitetstype er plassert inne i settet spesifisert i parentes, det vil si hvis spesialitetstypen er gipser, taktekker eller elektriker. Vi vil se IN-operatøren igjen i underspørringer.
La oss anta at vi ikke kan huske nøyaktig stavemåten til spesialiteten vår: "elektriker" eller "elektronikkingeniør" eller noe annet. Jokertegn, som erstatter udefinerte tegnstrenger, gjør det lettere å finne unøyaktige stavemåter i en spørring.
Mønstersymboler. Tegn som erstatter udefinerte tegnstrenger.
Be om: List opp de ansatte hvis spesialitetstype begynner med "Elek".
WHERE SKILL_TYPE LIKE ("Velg%")
Resultat:
WORKER ID NAME HRLY_RATE SKILL_TYPE SUPV_ID
1235 M. Faraday 12.50 Elektriker 1311
1311 H. Columbus 15,50 Elektrisk 1311
SQL har to jokertegn: % (prosent) og _ (understrek). Understrekingen erstatter nøyaktig ett udefinert tegn. Prosentandelen erstatter et vilkårlig antall tegn, som starter med null. Når jokertegn brukes, kreves det en LIKE-operator for å sammenligne tegnvariabler med konstanter. Andre eksempler:
NAME LIKE "__Columbus"
NAME LIKE "__K%"
Betingelsen i det første eksemplet er sann hvis NAVN består av to tegn etterfulgt av "Columbus". I WORKER-tabellen begynner alle navn med en første initial og et punktum. Dermed bruker vi denne betingelsen. La oss finne alle ansatte med etternavnet "Columbus". Tilstanden til det andre eksemplet lar oss finne alle ansatte hvis etternavn begynner med bokstaven "K".
Be om: Finn alle jobber som starter i løpet av de neste to ukene.
HVOR START_DATE MELLOM CURRENT_DATE OG
Resultat:(Anta at gjeldende dato er AKTUELL DATO = 10.10)
WORKER_ID BLDG_ID START_DATE NUM_DAYS
1235 312 10.10 5
1235 515 17.10 22
3231 111 10.10 8
1412 435 15.10 15
3231 312 24.10 20
1311 460 23.10 24
Denne spørringen illustrerer bruken av BETWEEN-operatoren med dato- og intervallverdier. CURRENT_DATE er en funksjon som alltid returnerer dagens dato. Uttrykk
CURRENT_DATE + INTERVAL "14" DAG
legger til en to-ukers periode til gjeldende dato. Dermed velges ASSIGNMENT (forutsatt at i dag er 10/10) hvis START_DATE-kolonneverdien er mellom 10/10 og 24/10. Fra dette kan vi se at vi kan legge til intervallverdier til datofelt. Dessuten kan vi multiplisere verdiene til intervallene med heltallsverdier. Anta for eksempel at vi ønsker å finne ut hvilket tall som vil være i løpet av et visst antall uker (angitt med variabelen NUM_WEEKS). Vi kan gjøre det slik:
CURRENT_DATE + INTERVAL "7" DAG * NUM_WEEKS
2. Spørringer med flere tabeller
Evnen til å relatere dataelementer på tvers av grensene til en enkelt tabell er viktig for ethvert databasespråk. I relasjonsalgebra utføres denne funksjonen av sammenføyningsoperasjonen. Selv om mye av SQL er basert direkte på relasjonskalkulus, kobler SQL data fra forskjellige tabeller på en lignende måte som sammenføyningsoperasjonen til relasjonsalgebra. Nå skal vi vise hvordan dette gjøres. Vurder forespørselen:
Be om:
Dataene som trengs for svaret er i to tabeller: ARBEIDER og OPPDRAG. SQL-løsningen krever en liste over begge tabellene i FROM-kommandoen og spesifisere en spesiell type WHERE-ledd:
VELG SKILL_TYPE
FRA ARBEIDER, OPPDRAG
WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID
OG BLDG_ID = 435
Hva foregår her? Vi må vurdere to stadier i hvordan systemet behandler denne forespørselen.
1. Som vanlig behandles FROM-klausulen først. Men i dette tilfellet, fordi kommandoen spesifiserer to tabeller, oppretter systemet et kartesisk produkt av radene i disse tabellene. Dette betyr at det opprettes én stor tabell (logisk) bestående av kolonner fra begge tabellene, med hver rad i en tabell paret med hver rad i den andre tabellen. I vårt eksempel, fordi WORKER-tabellen har fem kolonner og ASSIGNMENT-tabellen har fire kolonner, vil det kartesiske produktet produsert av FROM-kommandoen ha ni kolonner. Det totale antallet rader i det kartesiske produktet er lik m * n, der m er antall rader i WORKER-tabellen; og n er antall rader i OPPDRAG-tabellen. Siden WORKER-tabellen har 7 rader og ASSIGNMENT-tabellen har 19 rader, vil det kartesiske produktet inneholde 7x19 eller 133 rader. Hvis FROM-kommandoen viser mer enn to tabeller, opprettes et kartesisk produkt av alle tabellene spesifisert i kommandoen.
Kartesisk produkt. Resultatet av å slå sammen hver rad i en tabell med Hver en rad fra et annet bord.
2. Etter å ha opprettet den gigantiske relasjonstabellen, bruker systemet WHERE-kommandoen som før. Hver rad i tabellen opprettet av FROM-kommandoen. kontrolleres for å se om WHERE-betingelsen er oppfylt. Rader som ikke oppfyller vilkåret er unntatt fra vurdering. SELECT-leddet blir deretter brukt på de gjenværende radene.
WHERE-klausulen i søket vårt inneholder to betingelser:
1. ARBEIDER. WORKER_ID = OPPDRAG.ARBEIDS-ID
2. BLDG_ID = 435
Den første av disse betingelsene er sammenføyningsbetingelsen. Merk at siden både WORKER- og ASSIGNMENT-tabellene inneholder en kolonne kalt WORKER_ID, vil deres kartesiske produkt inneholde to kolonner med det navnet. For å skille mellom dem, går vi foran kolonnenavnet med navnet på kildetabellen, atskilt med en prikk.
Den første betingelsen betyr at verdien til WORKER_ID-kolonnen fra WORKER-tabellen i en hvilken som helst rad som er valgt, må samsvare med verdien til WORKER_ID-kolonnen fra ASSIGNMENT-tabellen. I virkeligheten slår vi sammen to tabeller med WORKER_ID. Alle rader der verdiene til disse to kolonnene ikke er like er ekskludert fra produkttabellen. Nøyaktig det samme skjer når du utfører den naturlige sammenføyningsoperasjonen til relasjonsalgebra. (Men det er fortsatt en viss forskjell fra en naturlig sammenføyning: SQL fjerner ikke automatisk den ekstra WORKER_ID-kolonnen). Den komplette sammenføyningen av disse to tabellene med tilleggsbetingelsen BLDG_ID = 435 er vist i fig. 1. Bruk av SELECT-kommandoen vil til slutt gi følgende spørringsresultat:
FERDIGHETSTYPE
Gipser
Taktekker
Elektriker
Ris. 1. Bli med i ARBEider- og OPPDRAG-tabellene
Nå skal vi vise hvordan du kobler en tabell til seg selv i SQL.
Be om: List de ansatte, og angi navnene på deres ledere.
VELG A.WORKER_NAME, B.WORKER_NAME
FRA ARBEIDER A, ARBEIDER B
WHERE B.WORKER_ID = A.SUPV_ID
FROM-leddet i dette eksemplet lager to "kopier" av WORKER-tabellen, og gir dem aliasene A og B. Et alias er et alternativt navn gitt til tabellen. Deretter kobles kopiene A og B av WORKER-tabellen sammen med WHERE-kommandoen basert på likhetsbetingelsen til WORKER_ID i B og SUPV_ID i A. Dermed blir hver rad fra A koblet til rad B, som inneholder informasjon om lederen av rad A. (Fig. 2).
Ris. 2. Sammenføyning av to kopier av WORKER-bordet
Ved å velge to medarbeidernavn fra hver linje, får vi den nødvendige listen:
A.NAMEB.NAME
M. Faraday H. Columbus
K.Nemo G.Rickover R.Garrett R.Garrett
P. Mason P. Mason G. Rickover G. Rickover H. Columbus H. Columbus J. Barrister P. Mason
Kallenavn. Et alternativt navn gitt til tabellen.
A.WORKER_NAME representerer arbeideren og B.WORKER_NAME representerer lederen. Vær oppmerksom på at noen arbeidere er sine egne ledere, noe som følger av likhets WORKER_ID - SUPV_ID på linjene deres.
I SQL kan du koble mer enn to tabeller om gangen:
Be om
VELG WORKER_NAME
FRA ARBEIDER, OPPDRAG, BYGG
WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID OG ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID OG
TYPE = "Kontor"
Resultat:
M. Faraday
G.Rickover
J. Barrister
Merk at hvis et kolonnenavn (for eksempel WORKER_ID eller BLDG_ID) vises i mer enn én tabell, må vi for å unngå tvetydighet sette navnet på den opprinnelige tabellen foran kolonnenavnet. Men hvis kolonnenavnet forekommer i bare én tabell, som TYPE i vårt eksempel, er det ingen tvetydighet, så tabellnavnet trenger ikke spesifiseres.
SQL-kommandoene i denne spørringen oppretter én tabell fra tre relasjonsdatabasetabeller. De to første tabellene kobles sammen av WORKER_ID, hvoretter den tredje tabellen blir koblet sammen med BLDG_ID til den resulterende tabellen. Betingelse
TYPE = "Kontor"
WHERE-klausulen fører til at alle rader ekskluderes bortsett fra de for kontorbygg. Dette oppfyller kravene i forespørselen.
3. Underspørringer
Undersøk. Spørring i en spørring
En underspørring kan plasseres i WHERE-leddet til en spørring, og dermed utvide mulighetene til WHERE-leddet. La oss se på et eksempel.
Be om: Hva er spesialitetene til arbeiderne som er tildelt bygning 435?
VELG SKTLL_TYPE
FRA WORKER HVOR WORKER_ID IN
(VELG WORKER_ID
HVOR BLDG_ID = 435)
Underspørring i dette eksemplet
(VELG WORKER_ID
HVOR BLDG_ID = 435)
En spørring som inneholder en underspørring kalles ekstern forespørsel eller hovedforespørsel. Underspørringen resulterer i opprettelsen av følgende sett med ansatt-IDer:
ARBEIDERS ID
Ekstern forespørsel. Hovedspørringen, som inneholder alle underspørringene.
Dette settet med ID-er tar deretter plassen til en underspørring i den ytre spørringen. Fra dette tidspunktet utføres den ytre spørringen ved å bruke settet som er opprettet av underspørringen. Den ytre spørringen behandler hver rad i WORKER-tabellen i henhold til WHERE-leddet. Hvis WORKER_ID for en rad ligger i (IN)-settet opprettet av underspørringen, blir SKILL_TYPE for raden valgt og vist i den resulterende tabellen:
FERDIGHETSTYPE
Gipser
Taktekker
Elektriker
Det er veldig viktig at SELECT-leddet i underspørringen inneholder WORKER_ID og bare WORKER_ID. Ellers ville WHERE-klausulen i den ytre spørringen, som betyr at WORKER_ID er i settet med arbeider-ID-er, ikke ha noen betydning.
Merk at en underspørring logisk kan utføres før minst én rad vurderes av hovedspørringen. På en måte er en underspørring uavhengig av hovedspørringen. Den kan utføres som en fullstendig spørring. Vi sier at en slik underspørring ikke er korrelert med hovedspørringen. Som vi snart skal se, kan underspørringer korreleres.
Ukorrelert underspørring. En underspørring hvis verdi er uavhengig av enhver ytre spørring.
Her er et eksempel på en underspørring i en underspørring.
Be om: Liste de ansatte som er tildelt kontorbyggene.
Igjen ser vi på spørringen som vi undersøkte sammenhengen med.
VELG WORKER_MAME
WHERE WORKER_ID IN
(VELG WORKER_ID
HVOR BLDG_ID IN
WHERE TYPE = "Kontor"))
Resultat:
M. Faraday
G.Rickover
J. Barrister
Merk at vi ikke trenger å prefiksere kolonnenavnene med tabellnavn noe sted, siden hver underspørring behandler én og bare én tabell, så det kan ikke oppstå uklarheter.
Forespørselsutførelse skjer i en inn- og ut-rekkefølge. Det vil si at den innerste spørringen (eller "nederst") utføres først, deretter utføres underspørringen som inneholder den, og deretter den ytre spørringen.
Korrelerte underspørringer. Alle underspørringene omtalt ovenfor var uavhengige av hovedspørringene de ble brukt i. Med uavhengig mener vi at underspørringer kan utføres på egen hånd som fullstendige spørringer. Vi går nå videre til å vurdere en klasse med underspørringer hvis utførelsesresultater kan avhenge av raden som vurderes av hovedspørringen. Slike underspørringer kalles korrelerte underspørringer.
Korrelert underspørring. En underspørring hvis resultat avhenger av raden som vurderes av hovedspørringen.
Be om: List opp de ansatte som har høyere timepriser enn lederne deres.
VELG WORKER_NAME
WHERE A.HRLY_RATE >
(VELG B.HRLY_RATE
WHERE B.WORKER_ID = A.SUPV_ID)
Resultat:
De logiske trinnene for å utføre denne forespørselen er:
1. Systemet lager to kopier av WORKER-tabellen: kopi A og kopi B. I henhold til måten vi definerte dem, refererer A til den ansatte, B refererer til lederen.
2. Systemet vurderer deretter hver rad A. En gitt rad velges hvis den tilfredsstiller WHERE-betingelsen. Denne betingelsen betyr at en rad vil bli valgt hvis dens HRLY_RATE-verdi er større enn HRLY_RATE generert av underspørringen.
3. Underspørringen velger HRLY_RATE-verdien fra rad B, hvis WORKER_ID er lik SUPV_ID i rad A, i dette øyeblikket behandlet av hovedforespørselen. Dette er HRLY_RATE for manageren.
Merk at siden A.HRLY_RATE bare kan sammenlignes med én verdi, må underspørringen bare returnere én verdi. Denne verdien endres avhengig av hvilken rad A som vurderes. Dermed er underspørringen korrelert med hovedspørringen. Vi vil se flere eksempler på korrelerte underspørringer senere når vi studerer innebygde funksjoner.
EXISTS og NOT EXISTS operators
Anta at vi ønsker å identifisere arbeidere som ikke er satt til å arbeide i en bestemt bygning. På overflaten ser det ut til at en slik forespørsel lett kan tilfredsstilles ved ganske enkelt å avvise den bekreftende versjonen av forespørselen. Anta for eksempel at vi er interessert i en bygning med BLDG_ID 435. Vurder forespørselen:
VELG WORKER_ID
HVOR BLDG_ID IKKE 435
Dessverre er dette en feil formulering av løsningen. Forespørselen vil ganske enkelt gi oss ID-ene til arbeidere som jobber i andre bygninger. Selvfølgelig kan noen av dem også tilordnes bygning 435.
En riktig formulert løsning bruker NOT EXISTS-operatoren:
VELG WORKER_ID
HVOR IKKE FINNES
WHERE ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID OG
Resultat:
WORKER_ID
Operatorene EXISTS og NOT EXISTS plasseres alltid foran underspørringen. EXISTS evalueres til sann hvis settet generert av underspørringen ikke er tomt. Hvis settet generert av underspørringen er tomt, tar EXISTS verdien "false". Operatøren IKKE FINNES, selvfølgelig, fungerer akkurat motsatt. Det er sant hvis resultatet av underspørringen er tomt, og ellers usant.
FINNES operatør. Returnerer sann hvis resultatsettet ikke er tomt.
FINNES IKKE operatør. Returnerer sann hvis resultatsettet er tomt.
I dette eksemplet brukte vi NOT EXISTS-operatoren. Underspørringen velger alle rader i ASSIGNMENT-tabellen der WORKER_ID har samme verdi som raden vurdert av hovedspørringen, og BLDG_ID er lik 435. Hvis dette settet er tomt, er arbeiderraden vurdert av hovedspørringen. valgt, siden dette betyr at Denne ansatte ikke jobber i bygg 435.
I løsningen vi ga, brukte vi en korrelert underspørring. Hvis vi bruker IN-operatoren i stedet for NOT EXISTS, kan vi klare oss med en ukorrelert underspørring:
VELG WORKER_ID
WHERE WORKER_ID IKKE INN
(VELG WORKER_ID
WHERE BLDG_ID = 435)
Denne løsningen er enklere enn løsningen med NOT EXISTS-operatøren. Et naturlig spørsmål dukker opp: hvorfor trenger vi EKSISTERER og FINNES IKKE i det hele tatt? Svaret er at IKKE FINNES er den eneste måten å løse spørsmål som inneholder ordet "hver" i betingelsen. Slike spørringer løses i relasjonsalgebra ved å bruke divisjonsoperasjonen, og i relasjonskalkulus ved å bruke den universelle kvantifikatoren. Her er et eksempel på et søk med ordet "hver" i sin tilstand:
Be om: List opp de ansatte som er tildelt hver bygning.
Dette spørsmålet kan implementeres i SQL ved hjelp av doble negasjoner. Vi omformulerer spørringen til å inkludere en dobbel negativ:
Be om: List opp slike ansatte for hvem Ikke det er en bygning som de ikke er tildelt.
Vi fremhevet det doble negative. Det er klart at denne forespørselen logisk sett tilsvarer den forrige.
Nå ønsker vi å formulere løsningen i SQL. For å gjøre den endelige løsningen lettere å forstå, gir vi først en løsning på et foreløpig problem: problemet med å identifisere alle bygningene som en hypotetisk arbeider for, "1234" Ikke utnevnt.
(I) VELG BLDG_ID
HVOR IKKE FINNES
ASSIGNMENT.WORKER_ID = 1234)
Vi har merket denne spørringen (I) fordi vi vil referere til den senere. Hvis det ikke er noen bygning som tilfredsstiller denne forespørselen, blir arbeider 1234 tildelt hver bygning og tilfredsstiller derfor betingelsene i den opprinnelige forespørselen. For å få en løsning på den opprinnelige spørringen, må vi generalisere spørringen (I) fra en spesifikk arbeider 1234 til WORKER_ID-variabelen og gjøre denne modifiserte spørringen til en underspørring av den større spørringen. Her er løsningen:
(II) VELG WORKER_ID
HVOR IKKE FINNES
HVOR IKKE FINNES
HVOR ASSIGNMENT.BLDG_ID = BYGNING.BLDG_ID OG
ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID)
Resultat:
ARBEIDERS ID
Merk at underspørringen som starter på den fjerde linjen i spørringen (II) er identisk med spørringen (I), med "1234" erstattet av WORKER.WORKER_ID. Spørsmål (II) kan leses som følger:
Velg WORKER_ID fra WORKER hvis det ikke er noen bygning som WORKER_ID ikke er tilordnet.
Dette samsvarer med betingelsene i den opprinnelige forespørselen.
Vi ser at NOT EXISTS-operatoren kan brukes til å formulere de spørringene som krevde en divisjonsoperasjon i relasjonsalgebra, og en universell kvantifier i relasjonskalkulus. Fra et brukervennlig synspunkt gir NOT EXISTS-operatoren ingen spesiell fordel, noe som betyr at SQL-spørringer som bruker NOT EXISTS to ganger ikke er lettere å forstå enn relasjonsalgebraløsninger med divisjon eller relasjonskalkulusløsninger med universelle kvantifiserere. Mer forskning vil være nødvendig for å lage språkkonstruksjoner som gjør at slike spørsmål kan løses mer naturlig.
Innebygde funksjoner
La oss vurdere spørsmål av denne typen:
Hva er høyeste og minste timepris? Hva er gjennomsnittlig antall dager ansatte jobber i bygning 435? Hva er det totale antall dager avsatt til pussarbeid på bygg 312? Hvor mange forskjellige spesialiteter er det?
Å svare på disse spørsmålene krever statistiske funksjoner som ser på mange rader i en tabell og returnerer én enkelt verdi. Det er fem slike funksjoner i SQL, kalt innebygde funksjoner eller settfunksjoner. Disse funksjonene er SUM (sum), AVG (gjennomsnitt), COUNT (antall), MAX (maksimum) og MIN (minimum).
Innebygd funksjon (sett funksjon). En statistisk funksjon som opererer på flere rader: SUM (sum), AVG (gjennomsnitt), COUNT (antall), MAX (maksimum), MIN (minimum).
Be om: Hva er høyeste og minste timepris?
VELG MAX(HRLY_RATE), MIN(HRLY_RATE)
Resultat: 17.40, 8.20
MAX funksjoner og MIN opererer på én tabellkolonne. De velger henholdsvis maksimums- eller minimumsverdien fra denne kolonnen. Vår spørringsformulering inneholder ikke en WHERE-klausul. For de fleste søk er dette kanskje ikke tilfelle, som vårt neste eksempel viser.
Be om: Hva er gjennomsnittlig antall dager ansatte jobber i bygning 435?
VELG AVG(NUM_DAYS)
HVOR BLDG_ID =435
Resultat: 12.33
Be om: Hva er det totale antall dager avsatt til pussarbeid på bygg 312?
VELG SUM(NUM_DAYS)
FRA OPPDRAG, ARBEIDER
WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID OG
SKILL_TYPE = "Gipser" OG
Resultat: 27
Løsningen bruker en sammenføyning mellom ASSIGNMENT- og WORKER-tabellene. Dette er nødvendig fordi SKILL_TYPE er i WORKER-tabellen og BLDG_ID er i ASSIGNMENT-tabellen.
Be om: Hvor mange forskjellige spesialiteter er det?
VELG ANTALL (DISTINCT SKILL_TYPE)
Resultat: 4
Fordi den samme spesialiteten kan vises i flere forskjellige rader, må du bruke DISTINCT-nøkkelordet i denne spørringen for å forhindre at systemet teller samme spesialitetstype mer enn én gang. DISTINCT-operatøren kan brukes med alle de innebygde funksjonene, selv om den selvfølgelig er overflødig med MAX- og MIN-funksjonene.
DISTINKT. En operatør som eliminerer dupliserte linjer.
SUM- og AVG-funksjonene skal bare brukes med numeriske kolonner. Andre funksjoner kan brukes med både numeriske data og tegndata. Alle funksjoner unntatt COUNT kan brukes med beregnede uttrykk. For eksempel:
Be om: Hva er gjennomsnittlig ukelønn?
VELG AVG (40 * HRLY_RATE)
Resultat: 509.14
COUNT kan referere til en hel rad i stedet for en individuell kolonne :
Be om: Hvor mange bygninger har kvalitetsnivå 3?
VELG ANTALL (*)
FRA BYGG HVOR
Resultat: 3
Som alle disse eksemplene viser, hvis en SELECT-kommando inneholder en innebygd funksjon, kan ingenting annet vises i den SELECT-kommandoen. Det eneste unntaket fra denne regelen er GROUP BY-klausulen, som vi skal se på nå.
GROUP BY og HAVING klausuler
I ledelsen kreves det ofte statistisk informasjon om hver gruppe i mange grupper. Tenk for eksempel på følgende spørring:
Be om: For hver leder, finn ut den maksimale timeprisen blant hans underordnede.
For å løse dette problemet må vi dele arbeiderne inn i grupper etter deres ledere. Vi vil da fastsette maksimumsbudet innenfor hver gruppe. I SQL gjøres dette på denne måten:
GRUPPER ETTER SUPV_ID
Resultat:
SUPV_IDMAX(HRLY RATE)
Når du behandler denne spørringen, deler systemet først radene i WORKER-tabellen i grupper ved å bruke følgende regel. Rader plasseres i samme gruppe hvis og bare hvis de har samme SUPV_ID. SELECT-leddet blir deretter brukt på hver gruppe. Siden det bare er én SUPV_ID-verdi i denne gruppen, er det ingen SUPV_ID-usikkerhet i gruppen. For hver gruppe gir SELECT-leddet ut SUPV_ID og beregner og sender også ut MAX(HRLY_RATE)-verdien. Resultatet er presentert ovenfor.
I en SELECT-kommando med innebygde funksjoner kan bare de kolonnene som er inkludert i GROUP BY-leddet vises. Merk at SUPV_ID kan brukes i en SELECT-kommando fordi den er inkludert i GROUP BY-leddet.
GROUP BY klausul. Indikerer at rader skal deles inn i grupper med vanlige verdier for de angitte kolonnene.
GROUP BY-leddet lar deg utføre visse komplekse beregninger. For eksempel vil vi kanskje finne ut gjennomsnittet av disse maksimumsbudene. Imidlertid er beregning med innebygde funksjoner begrenset i den forstand at den ikke tillater bruk av innebygde funksjoner inne i andre innebygde funksjoner. Altså et uttrykk som
AVG(MAX(HRLY_RATE))
forbudt. Gjennomføringen av en slik forespørsel vil bestå av to trinn. Vi må først legge inn maksimumsbudene i en ny tabell og i et andre trinn beregne gjennomsnittet.
Du kan bruke WHERE-leddet med GROUP BY-kommandoen:
Be om: Finn ut for hver type bygning gjennomsnittlig nivå kvalitet blant bygninger med status 1.
VELG TYPE, AVG(QLTY_LEVEL)
HVOR STATUS = 1
Resultat:
TYPEAVG(QLTY_LEVEL)
Butikk 1
Boligbygg 3
WHERE-leddet utføres før GROUP BY-setningen. Dermed kan ingen gruppe inneholde en rad som har en annen status enn 1. Status 1-radene er gruppert etter TYPE-verdi, og deretter brukes en SELECT-klausul på hver gruppe.
HAR setning. Setter vilkår på grupper.
Vi kan også bruke vilkår for grupper opprettet av GROUP BY-klausulen. Dette gjøres ved å bruke HAVING-frasen. Anta for eksempel at vi bestemte oss for å gjøre en av de tidligere spørringene mer spesifikke:
Be om: For hver leder som har mer enn én underordnet, finn ut maksimal timepris blant hans underordnede.
Vi kan gjenspeile denne tilstanden med den riktige HAVING-kommandoen:
VELG SUPV_ID, MAX(HRLY_RATE)
FRA ARBEIDSGRUPPEN AV SUPV_ID
HAR ANTALL(*) > 1
Resultat:
SUPV_ID MAX(HRLY_RATE)
Forskjellen mellom WHERE og HAVING klausuler er at WHERE gjelder rader, mens HAVING gjelder grupper.
En spørring kan inneholde både en WHERE- og en HAVING-klausul. I dette tilfellet kjøres WHERE-leddet først fordi det kjøres før grupperingen. Vurder for eksempel følgende modifikasjon av den tidligere spørringen:
Be om: For hver type bygning, finn ut det gjennomsnittlige kvalitetsnivået blant bygninger med status 1. Vurder bare de typer bygninger hvis maksimale kvalitetsnivå ikke overstiger 3.
VELG TYPE, AVG (QLTY_JLEVEL)
HVOR STATUS = 1
HAR MAX(QLTY_LEVEL)<= 3
Resultat:
TYPE AVG(QLTY_LEVEL)
Butikk 1
Boligbygg 3
Merk at fra og med FROM-leddet, utføres leddene i rekkefølge, og deretter brukes SELECT-leddet. Dermed blir WHERE-leddet brukt på BUILDING-tabellen, og alle rader der STATUS er forskjellig fra 1 blir slettet. De resterende radene er gruppert etter TYPE; alle rader med samme TYPE-verdi havner i samme gruppe. Dermed opprettes flere grupper, en for hver TYPE-verdi. HAVING-klausulen brukes deretter på hver gruppe, og de gruppene hvis maksimale kvalitetsnivåverdi overstiger 3, fjernes. Til slutt blir SELECT-leddet brukt på de gjenværende gruppene.
7. Innebygde funksjoner og underspørringer
Innebygde funksjoner kan bare brukes i en SELECT-klausul eller HAVING-kommando. En SELECT-klausul som inneholder en innebygd funksjon kan imidlertid være en del av en underspørring. La oss se på et eksempel på en slik underspørring:
Be om: Hvilke arbeidere har høyere timepris enn gjennomsnittet?
VELG WORKER_NAME
WHERE HRLY_RATE >
(VELG AVG(HRLY_RATE)
Resultat:
H. Columbus
Merk at underspørringen ikke er korrelert med hovedspørringen. Underspørringen returnerer nøyaktig én verdi - gjennomsnittlig timepris. Hovedspørringen velger en arbeider bare hvis raten hans er høyere enn det beregnede gjennomsnittet.
Korrelerte søk kan også bruke innebygde funksjoner:
Spørsmål: Hvilken ansatt har en timepris som er høyere enn gjennomsnittlig timepris blant samme leders underordnede?
I dette tilfellet, i stedet for å beregne én gjennomsnittlig timepris for alle arbeidere, må vi beregne gjennomsnittsprisen for hver gruppe arbeidere som rapporterer til samme leder. Dessuten må vår beregning gjøres på nytt for hver arbeider som vurderes av hovedspørringen:
VELG A. WORKER_NAME
SQL lar deg neste spørringer i hverandre. Vanligvis returnerer en underspørring en enkelt verdi, som sjekkes for å se om predikatet er sant.
Typer søkeord:
. Sammenligning med resultatet av en underspørring (=, >=)
. Sjekker for tilhørighet til resultatene av en underspørring (IN)
. Eksistenssjekk (EXISTS)
. Multippel (kvantitativ) sammenligning (ALLE, ALLE)
Merknader om nestede søk:
. En underspørring må bare velge én kolonne (bortsett fra en underspørring med et EXISTS-predikat), og resultatdatatypen må samsvare med datatypen til verdien spesifisert i predikatet.
. I noen tilfeller kan du bruke nøkkelordet DISTINCT for å sikre at én enkelt verdi returneres.
. Du kan ikke inkludere en ORDER BY- eller UNION-klausul i en underspørring.
. Underspørringen kan være plassert enten til venstre eller til høyre for søkebetingelsen.
. Underspørringer kan bruke aggregeringsfunksjoner uten et GROUP BY-ledd, som automatisk returnerer en spesiell verdi for et hvilket som helst antall rader, et spesielt IN-predikat og kolonnebaserte uttrykk.
. Når det er mulig, bør du bruke JOIN-tabellsammenføyninger i stedet for underspørringer.
Eksempler på nestede søk:
VELG * FRA bestillinger WHERE SNum=(VELG SNum FRA selgere WHERE SName='Motika')
VELG * FRA bestillinger HVOR SNum IN (VELG SNum FRA selgere WHERE City='London')
VELG * FRA bestillinger WHERE SNum=(VELG DISTINCT SNum FROM Orders WHERE CNum=2001)
VELG * FRA bestillinger WHERE Amt>(VELG AVG(Amt) FRA bestillinger WHERE Odate=10/04/1990)
VELG * FRA Kunde WHERE CNum=(VELG SNum+1000 FRA selgere WHERE SName=’Serres’)
2) Relaterte underspørringer
I SQL kan du opprette underspørringer som refererer til en tabell fra en ytre spørring. I dette tilfellet utføres underspørringen flere ganger, én gang for hver tabellrad fra den ytre spørringen. Derfor er det viktig at underspørringen bruker indeksen. En underspørring kan få tilgang til samme tabell som en ytre. Hvis den ytre spørringen returnerer et relativt lite antall rader, vil den tilknyttede underspørringen være raskere enn den frakoblede. Hvis en underspørring returnerer et lite antall rader, vil den relaterte spørringen være tregere enn den ikke-relaterte spørringen.
Eksempler på relaterte underspørringer:
SELECT * FROM SalesPeople Main WHERE 1(SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //returnerer alle bestillinger hvis verdi overstiger gjennomsnittlig ordreverdi for en gitt kunde
3) Predikat FINNES
Syntaktisk form: FINNES ()
Predikatet tar en underspørring som et argument og evalueres til sann hvis underspørringen har utdata og ellers usann. Underspørringen utføres én gang og kan inneholde flere kolonner, siden verdiene deres ikke er sjekket, men resultatet av tilstedeværelsen av rader registreres ganske enkelt.
Merknader om EXISTS-predikatet:
. EXISTS er et predikat som returnerer TRUE eller FALSE og kan brukes alene eller med andre boolske uttrykk.
. EXISTS kan ikke bruke aggregeringsfunksjoner i underspørringen.
. I korrelerte underspørringer blir EXISTS-predikatet utført for hver rad i den ytre tabellen.
. Du kan kombinere EXISTS-predikatet med tabellsammenføyninger.
Eksempler for EXISTS-predikatet:
SELECT * FROM Customer WHERE EXISTS(SELECT * FROM Customer WHERE City='San Jose') – returnerer alle kunder hvis noen av dem bor i San Jose.
VELG DISTINCT SNum FROM Customer First WHERE NOT EXISTS (VELG * FROM Customer Send WHERE Send.SNum=First.SNum OG Send.CNumFirst.CNum) – returnerer antall selgere som bare betjente én kunde.
VELG DISTINKT F.SNum, SName, F.City FRA selgere F, Customer S WHERE EXISTS (VELG * FRA Customer T WHERE S.SNum=T.SNum AND S.CNumT.CNum AND F.SNum=S.SNum) – returnerer nummer, navn og bosted for alle selgere som betjente flere kunder.
VELG * FRA SalesPeople Frst HVOR FINNES (VELG * FRA Kunde Send WHERE Frst.SNum=Send.SNum OG 1
4) Predikater for kvantitativ sammenligning
Syntaktisk form: (=|>|=|) ALLE|ALLE ()
Disse predikatene bruker en underspørring som argument, men sammenlignet med EXISTS-predikatet brukes de sammen med relasjonspredikater (=,>=). I denne forstand ligner de på IN-predikatet, men brukes bare med underspørringer. Standarden tillater at SOME-nøkkelordet brukes i stedet for ALLE, men ikke alle DBMS-er støtter det.
Merknader om sammenligningspredikater:
. ALL-predikatet evalueres til TRUE hvis hver verdi valgt under utførelsen av underspørringen tilfredsstiller betingelsen spesifisert i det ytre spørringspredikatet. Det brukes oftest med ulikheter.
. ANY-predikatet evalueres til TRUE hvis minst én verdi valgt under utførelsen av underspørringen tilfredsstiller betingelsen spesifisert i det ytre spørringspredikatet. Det brukes oftest med ulikheter.
. Hvis underspørringen ikke returnerer noen rader, tar ALL automatisk verdien TRUE (det anses at sammenligningsbetingelsen er oppfylt), og for ALLE tar den verdien FALSE.
. Hvis sammenligningen er TRUE for ingen rader og det er én eller flere rader med en NULL-verdi, returnerer ANY UKJENT.
. Hvis sammenligningen er FALSE for ingen rader og det er en eller flere rader med en NULL-verdi, returnerer ALL UKJENT.
Eksempler på predikatet for kvantitativ sammenligning:
VELG * FRA selgere HVOR By=NOT (VELG by FRA kunde)
VELG * FRA bestillinger WHERE Amt ALL(VELG vurdering FRA Customer WHERE City='Roma')
5) Unikitetspredikat
UNIK|DISTINKT ()
Predikatet brukes til å sjekke unikheten (fravær av duplikater) i utdataene til underspørringen. Dessuten, i UNIQUT-predikatet, regnes strenger med NULL-verdier som unike, og i DISTINCT-predikatet anses to udefinerte verdier like med hverandre.
6) Match predikat
KAMP ()
MATCH-predikatet tester om verdien til en spørringsstreng vil samsvare med verdien til en streng som er et resultat av underspørringen. Denne underspørringen skiller seg fra IN AND ANY-predikatene ved at den tillater behandling av "delvise" (DELVIS) treff som kan forekomme blant rader som har noen NULL-verdier.
7) Spørsmål i FRA-delen
Faktisk er det lovlig å bruke en underspørring der en tabellreferanse er tillatt.
SELECT CName, Tot_Amt FROM Customer, (SELECT CNum, SUM(Amt) AS Tot_Amt FROM Orders GROUP BY CNum) WHERE City=’London’ AND Customer.CNum=Orders.CNum
//subquery returnerer det totale antallet bestillinger plassert av hver kunde fra London.
8) Rekursive spørringer
MED REKURSIV
Q1 SOM VELG … FRA … HVOR …
Q2 SOM VELG … FRA … HVOR …
