Hem › FÖRBI › Kapslade och länkade underfrågor i SQL, EXISTS predikat. Använda EXISTS-operatorn Frågar med hjälp av exists-funktionen

Kapslade och länkade underfrågor i SQL, EXISTS predikat. Använda EXISTS-operatorn Frågar med hjälp av exists-funktionen

VAR FINNS

Underfrågan kontrolleras för närvaron av en eller flera rader. Om minst en rad matchar frågan returneras det booleska värdet TRUE. När det valfria nyckelordet NOT anges returneras det booleska värdet TRUE om underfrågan inte returnerar några matchande rader.

underfråga

Baserat på den fullständiga underfrågan hämtas den resulterande datamängden.

Generella regler

Operatören EXISTS testar förekomsten av en eller flera rader i en underfråga till en överordnad fråga.

VÄLJ * FRÅN jobb DÄR INTE FINNS (VÄLJ * FRÅN anställd WHERE jobs.job_id=employye.job_id);

Det här exemplet kontrollerar postunderfrågan med det extra nyckelordet NOT. Följande exempel söker efter specifika poster i en underfråga för att hämta huvudresultatuppsättningen.

VÄLJ au_lname FRÅN författare WHERE FINNS (VÄLJ * FRÅN förlag WHERE authors.city=publishers.city);

Den här frågan returnerar efternamnen på författare (au_lname) som bor i samma stad som förlagen. Observera att du kan använda en asterisk i underfrågan eftersom underfrågan bara måste returnera en post med det booleska värdet TRUE. I sådana fall spelar kolumner ingen roll. Nyckelpunkten är strängens existens.

I många frågor utför operatorn EXISTS samma funktion som ALLA. Operatören EXISTS är vanligtvis mest effektiv när den används med korrelerade frågor.

Operatorn EXISTS är semantiskt likvärdig med operatorn ALLA.

En underfråga i en EXISTS-sats utför vanligtvis en av två typer av sökningar. Det första alternativet är att använda ett jokertecken - en asterisk (till exempel VÄLJ * FRÅN...), i vilket fall du inte hämtar någon specifik kolumn eller värde. Asterisken här betyder "valfri kolumn". Det andra alternativet är att endast välja en specifik kolumn i underfrågan (till exempel SELECT aujd FROM). Vissa individuella plattformar tillåter undersökningar på flera kolumner (t.ex. SELECT aujd, aujname FROM...). Den här funktionen är dock sällsynt och bör undvikas i kod som måste porteras till andra plattformar.

Skillnader mellan plattformar

Alla plattformar stödjer EXISTS-operatören i den form vi beskrev ovan.

"Det var lättare förr" - tänkte jag när jag satte mig ner för att optimera nästa fråga i SQL management studio. När jag skrev under MySQL var allt verkligen enklare - antingen fungerar det eller så fungerar det inte. Antingen saktar det ner eller så gör det inte. Förklara löste alla mina problem, inget mer behövdes. Nu har jag en kraftfull miljö för att utveckla, felsöka och optimera frågor och procedurer/funktioner, och allt det här röran skapar bara fler problem enligt mig. Och varför alla? Eftersom den inbyggda frågeoptimeraren är ond. Om jag skriver i MySQL och PostgreSQL

Välj * från a, b, c där a.id = b.id, b.id = c.id

och var och en av tabletterna kommer att ha minst 5k rader - allt kommer att frysa. Och tack gud! För annars utvecklar utvecklaren i bästa fall lättja för att skriva rätt, och i värsta fall förstår han inte alls vad han gör! Trots allt kommer samma fråga i MSSQL att fungera på samma sätt

Välj * från a join b på a.id = b.id join c på b.id = c.id

Den inbyggda optimeraren kommer att kamma den redundanta begäran och allt kommer att bli okej.

Han kommer också själv att bestämma vad som är bättre att göra - existera eller gå med och mycket mer. Och allt kommer att fungera så optimalt som möjligt.

Det finns bara ett MEN. Vid ett tillfälle kommer optimeraren att snubbla över komplex fråga och passerar, och då får du ett jätteproblem. Och du kanske inte får det direkt, utan när vikten på borden når kritisk massa.

Så här kommer till punkten med artikeln. finns och i är mycket tunga operationer. Detta är faktiskt en separat underfråga för varje resultatrader. Och om det också finns häckande, så är det i allmänhet att släcka lamporna. Allt kommer att vara ok när 1, 10, 50 rader returneras. Du kommer inte att känna skillnaden, och anslutningen kanske går ännu långsammare. Men när 500 dras ut börjar problemen. 500 underfrågor inom en begäran är allvarligt.

Även om det är bättre ur mänsklig förståelsesynpunkt och existerar, men ur synvinkeln av tidskostnader för frågor som returnerar 50+ rader är de inte acceptabla.

Det är nödvändigt att reservera att om det minskar någonstans måste det naturligtvis komma någonstans. Ja, join är mer minneskrävande, eftersom att hålla hela värdetabellen på en gång och arbeta med den är dyrare än att köra underfrågor för varje rad, vilket snabbt frigör minne. Du måste titta specifikt på förfrågan och mäta om användningen av extra minne för tidens skull kommer att vara kritisk eller inte.

Jag kommer att ge exempel på fullständiga analogier. Generellt sett har jag ännu inte stött på frågor av en sådan grad av komplexitet som inte kunde utökas till en kaskad av sammanfogningar. Det kan ta en dag, men allt kan avslöjas.

Välj * från a där a.id in (välj id från b) välj * från a där existerar (välj topp 1 1 från b där b.id = a.id) välj * från a join b på a.id = b. id välj * från a där a.id inte finns (välj id från b) välj * från a där det inte finns (välj topp 1 1 från b där b.id = a.id) välj * från en vänster sammanfogning b på a. id = b.id där b.id är null

Jag upprepar - MSSQL-optimeraren optimerar dessa exempel för maximal prestanda och det kommer aldrig att finnas dumma människor med så enkla förfrågningar.

Låt oss nu överväga ett exempel på en riktig fråga som måste skrivas om eftersom den helt enkelt frös på vissa prover (strukturen är mycket förenklad och begreppen har ersatts, det finns ingen anledning att vara rädd för någon icke-optimalitet i databasstrukturen ).

Du måste dra ut alla dubbletter av "produkter" på olika konton, med fokus på parametrarna för produkten, dess grupp och överordnade grupp, om det finns en.

Välj d.PRODUCT_ID från PRODUCT s, PRODUCT_GROUP sg vänster gå med M_PG_DEPENDENCY sd på (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP dg vänster gå med M_PG_DEPENDENCY dd på (dg.CY_GROUP_DEPENDENCY CHILD_DE) dd på (dg.CYM_GROUP_DEPENDENCY) PRODUCT_GROUP_ID=sg .PRODUCT_GROUP_ID och d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID och sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC och sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME och s.PRODUCT_NAME=d.PRODUCT_NAME och s.PRODUCT_PRODUCT_TYPE_d.CURE_TYPE=d.CURE_TYPE=d.CU och s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT och dg.PRODUCT_GROUP_IS_TMPL=0 och ((sd.M_PG_DEPENDENCY_CHILD_ID är null och dd.M_PG_DEPENDENCY_CHILD_ID är null) eller existerar (välj 1 från PRODUCT_GROUP sgDE, PAR_GROUP sgDE, PAR_GROUP sgDE, PAREND_GROUP sgDE,END_Group sgDE, PRODUCT_GROUP sgDE, PAR ID = sg1.PRODUCT_GROUP_ID och dd .M_PG_DEPENDENCY_PARENT_ID = dg1.PRODUCT_GROUP_ID och sg1.PRODUCT_GROUP_PERSPEC=dg1.PRODUCT_GROUP_PERSPEC och sg1.PRODUCT_GROUP_NAME=dg1.PRODUCT_GROUP_NAME och))

Så detta är fallet när optimeraren gav upp. Och för varje rad exekverades en tung existens, vilket dödade databasen.

Välj d.PRODUCT_ID från PRODUCT s gå med i PRODUCT d på s.PRODUCT_TYPE=d.PRODUCT_TYPE och s.PRODUCT_NAME=d.PRODUCT_NAME och s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE och s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT gå med i PRODUCT_GROUP sg på PRODUCT_GROUP_PRODUCT_ID. sg.PRODUCT_GROUP_ID gå med i PRODUCT_GROUP dg på d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID och sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME och sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC kvar gå med i M_PG_DEPEND_GROUP_PERSPECs på sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME och sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC vänster gå med i M_PG_DEPEND_GROUP_GIDs. LD_ID vänster gå med i M_PG_DEPENDENCY dd på dg. PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID kvar gå med i PRODUCT_GROUP sgp på sgp.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID vänster gå med i PRODUCT_GROUP dgp på dgp.PRODUCT_GROUP_ID = dd.M_CY_PG_DEGROUP och DUPARENDENCY_PARENT_DEC p.PRODUCT_GROUP_NAME och isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull( dgp. PRODUCT_GROUP_IS_TMPL, 0) där (sd.M_PG_DEPENDENCY_CHILD_ID är null och dd.M_PG_DEPENDENCY_CHILD_ID är null) eller (sgp.PRODUCT_GROUP_NAME är inte null och dgp.PRODUCT_GROUP_NAME är inte null) go

Efter dessa omvandlingar ökade vyns prestanda exponentiellt med antalet hittade produkter. Eller rättare sagt, söktiden förblev praktiskt taget oberoende av antalet matchningar och var alltid mycket liten. Som det ska vara.

Det här är ett tydligt exempel på hur att lita på MSSQL-optimeraren kan spela ett grymt skämt. Lita inte på honom, var inte lat, gå med manuellt, tänk varje gång vad som är bättre i en given situation - finns, in eller gå med.

SQL-språkpredikatet FINNS utför en logisk uppgift. I SQL-frågor detta predikat används i uttryck för formen

FINNS (VÄLJ * FRÅN TABLE_NAME ...).

Detta uttryck returnerar sant när frågan hittar en eller flera rader som matchar villkoret, och returnerar falskt när inga rader hittas.

För INTE FINNS det är tvärtom. Uttryck

FINNS INTE (VÄLJ * FRÅN TABLE_NAME ...)

returnerar sant när inga rader hittas i frågan, och falskt när minst en rad hittas.

De enklaste frågorna med SQL EXISTS-predikatet

I exemplen arbetar vi med biblioteksdatabasen och dess "Book in Use" (BOOKINUSE) och "User" (USER) tabeller. För närvarande behöver vi bara tabellen "Boka i bruk" (BOOKINUSE).

Författare	Titel	Pubår	Inv_No	Användar ID
Tolstoj	Krig och fred	2005	28	65
Tjechov	Körsbärsträdgården	2000	17	31
Tjechov	Utvalda berättelser	2011	19	120
Tjechov	Körsbärsträdgården	1991	5	65
Ilf och Petrov	De tolv stolarna	1985	3	31
Majakovskij	Dikter	1983	2	120
Palsternacka	Doktor Zhivago	2006	69	120
Tolstoj	söndag	2006	77	47
Tolstoj	Anna Karenina	1989	7	205
Pusjkin	Kaptens dotter	2004	25	47
Gogol	Pjäser	2007	81	47
Tjechov	Utvalda berättelser	1987	4	205
Palsternacka	Favoriter	2000	137	18

Exempel 1. Bestäm ID:n för användare som fick Tolstojs böcker och som också fick Tjechovs böcker. Den yttre frågan väljer data om användare som fick Tolstojs böcker, och EXISTS-predikatet specificerar ett ytterligare villkor som kontrolleras i den inre frågan - användare som fick Tjechovs böcker. Ett ytterligare villkor i den interna begäran är att användar-ID:n från de externa och interna förfrågningarna matchar: User_ID=tols_user.user_id. Begäran kommer att vara följande:

Denna fråga kommer att returnera följande resultat:

Skillnader mellan EXISTS och IN-predikat

Vid en första anblick på frågor med predikatet EXISTS kan du få intrycket att det är identiskt predikat IN. Detta är fel. Även om de är väldigt lika. IN-predikatet söker efter värden från det intervall som anges i dess argument, och om det finns sådana värden väljs alla rader som motsvarar detta intervall. Resultatet av EXISTS-predikatet är ett "ja" eller "nej" svar på frågan om det finns några värden alls som motsvarar de som anges i argumentet. Dessutom föregås IN-predikatet av namnet på kolumnen för att söka efter rader som matchar värdena i intervallet. Låt oss titta på ett exempel som visar skillnaden mellan EXISTS-predikatet och IN-predikatet, och problemet löst med IN-predikatet.

Exempel 4. Bestäm ID:n för användare som har fått böcker av författare vars böcker har utfärdats till användaren med ID 31. Begäran kommer att vara enligt följande:

Användar ID

120

205

En intern fråga (efter IN) väljer författare: Tjechov; Ilf och Petrov. Den externa frågan väljer alla användare som har fått böcker av dessa författare. Vi ser att, till skillnad från EXISTS-predikatet, föregås IN-predikatet av namnet på kolumnen, i det här fallet - Author.

Frågor med EXISTS-predikat och ytterligare villkor

Om du, förutom EXISTS-predikatet i frågan, tillämpar minst ett ytterligare villkor, till exempel specificerat med aggregerade funktioner, då kan sådana frågor tjäna för enkel dataanalys. Låt oss visa detta med följande exempel.

Exempel 5. Bestäm ID:n för användare som har fått minst en bok av Pasternak och som har fått mer än 2 böcker. Vi skriver följande fråga, där det första villkoret specificeras av EXISTS-predikatet med en kapslad fråga, och det andra villkoret med HAVING-operatorn måste alltid komma efter den kapslade frågan:

Resultat av begäran:

Användar ID

120

Som framgår av BOKINUSE-tabellen utfärdades även Pasternaks bok till användaren med ID 18, men han fick bara en bok och ingår inte i provet. Om du använder COUNT-funktionen på en liknande fråga igen, men den här gången för att räkna de valda raderna (öva på detta själv), kan du få information om hur många användare som läser Pasternaks böcker som också läser böcker av andra författare. Detta är redan från området för dataanalys.

Frågor med EXISTS-predikat på två tabeller

Frågor med predikatet EXISTS kan hämta data från mer än en tabell. Många problem kan lösas med samma resultat med hjälp av JOIN-operatör, men i vissa fall kan du genom att använda EXISTS skapa en mindre krånglig fråga. Det är att föredra att använda EXISTS i de fall där den resulterande tabellen kommer att innehålla kolumner från endast en tabell.

I följande exempel, från samma databas, behöver du förutom BOKINUSE-tabellen också en USER-tabell.

Resultatet av frågan blir följande tabell:

Författare

Tjechov

Majakovskij

Palsternacka

Som med att använda JOIN-operatorn, i fall där det finns mer än en tabell, bör du använda tabellalias för att kontrollera att värdena på nycklarna som kopplar samman tabellerna matchar. I vårt exempel är tabellaliasen bk och us, och nyckeln som förbinder tabellerna är User_ID.

FINNS predikat i sammanfogningar av fler än två tabeller

Nu kommer vi att se mer i detalj varför det är att föredra att använda EXISTS i de fall där den resulterande tabellen kommer att innehålla kolumner från endast en tabell.

Vi arbetar med databasen "Fastigheter". Tabellen Deal innehåller data om erbjudanden. För våra uppgifter kommer kolumnen Typ med uppgifter om typen av transaktion - försäljning eller leasing - att vara viktig i denna tabell. Objekttabellen innehåller data om objekt. I den här tabellen kommer vi att behöva värdena för kolumnerna Rum (antal rum) och LogBalc, som innehåller data om närvaron av en loggia eller balkong i booleskt format: 1 (ja) eller 0 (nej). Tabellerna Kund, Förvaltare och Ägare innehåller uppgifter om kunder, företagsledare respektive fastighetsägare. I dessa tabeller är FName och LName för- respektive efternamn.

Exempel 7. Identifiera kunder som har köpt eller hyrt fastigheter som inte har en loggia eller balkong. Vi skriver följande fråga, där EXISTS-predikatet anger en åtkomst till resultatet av att sammanfoga två tabeller:

Eftersom kolumner väljs från klienttabellen med asteriskoperatorn, kommer alla kolumner i denna tabell att visas, som kommer att ha lika många rader som det finns klienter som matchar villkoret som anges av EXISTS-predikatet. Vi behöver inte mata ut några kolumner från tabellerna vars koppling nås av underfrågan. Därför, för att spara maskintid, hämtas endast en kolumn. För att göra detta skrivs en enhet efter ordet SELECT. Samma teknik används i frågorna i följande exempel.

Skriv en SQL-fråga med predikatet EXISTS själv och titta sedan på lösningen

Vi fortsätter att skriva SQL-frågor tillsammans med EXISTS-predikatet

Exempel 9. Bestäm ägarna till de objekt som hyrs ut. Vi skriver följande fråga, där EXISTS-predikatet också anger en åtkomst till resultatet av att sammanfoga två tabeller:

Som i föregående exempel kommer alla fält från tabellen som den externa frågan har åtkomst till att returneras.

Exempel 10. Bestäm antalet ägare vars fastigheter hanterades av chefen Savelyev. Vi skriver en fråga där den yttre frågan får åtkomst till en koppling av tre tabeller, och EXISTS-predikatet anger åtkomst till endast en tabell:

Alla frågor kontrolleras mot en befintlig databas. Framgångsrik användning!

Relationsdatabaser och SQL-språk

Novosibirsk State Academy of Economics and Management

LABORATORIEPRAKTIK OM DISCIPLINER

"DATABAS"

Laboratoriearbete nr 7

"Basernas språk SQL-data: datamanipuleringskommandon»

NOVOSIBIRSK 2000

SQL är en förkortning för Structured Query Language. Av namnet på språket är det tydligt att dess huvudsakliga syfte är att generera frågor för att få information från en databas. Kommandon för att hämta data ligger till grund för datamanipuleringsspråket DML - en integrerad del av SQL-språket. DML består dock av mer än bara kommandon för att hämta data från en databas. Det finns också kommandon för datamodifiering, datahantering och annat.

Laboratoriearbetet undersöker de grundläggande verktygen i DML-språket. Pågående laboratoriearbete vi kommer att hålla oss till SQL2-standarden.

På grund av det faktum att SQL är ett stort språk kommer vi bara att överväga de grundläggande kommandona. Olika specifika SQL-verktyg behandlas i efterföljande laborationer.

För att utföra laborationer krävs kunskap om grunderna i relationsdatamodellen, grunderna i relationalgebra och relationskalkyl samt principerna för att arbeta med MS SQL Server DBMS.

Som ett resultat av att slutföra laboratoriearbetet kommer du att behärska metoderna för att manipulera data med hjälp av SQL-språkkommandon, överväga dialekten för språket implementerat i MS SQL Server DBMS.

INTRODUKTION

SQL innehåller ett brett utbud av datamanipuleringsmöjligheter, både för att skapa frågor och uppdatera databasen. Dessa möjligheter förlitar sig endast på databasens logiska struktur, inte på dess fysiska struktur, vilket är förenligt med kraven i relationsmodellen.

Den ursprungliga strukturen för SQL-syntax var (eller tycktes åtminstone vara) baserad på Codds relationskalkyl. Den enda stödda operationen i relationalgebra var union.

Förutom den relationskalkylliknande syntaxen som utvecklats i den tidigare standarden, implementerar SQL2 direkt operationerna union, intersection, difference och join. Select-, projekt- och produktverksamheten stöddes (och fortsätter att stödjas) nästan direkt, medan divisions- och uppdragsverksamheten stöds i en mer krånglig form.

Vi kommer först att beskriva SQL-frågespråket och sedan dess datainmatning och modifieringsoperationer. Datamodifieringsoperationer kommer att beskrivas sist, eftersom deras struktur till viss del beror på strukturen hos frågespråket.

Enkla frågor

För oss enkel begäran det kommer att finnas en fråga som endast kommer åt en tabell i databasen. Enkla frågor hjälper oss att illustrera den grundläggande strukturen i SQL.

Enkel begäran. En fråga som endast har åtkomst till en databastabell.

Begäran: Vem jobbar som putsare?

WHERE SKILL_TYPE = "Plåsterare"

Resultat:

G.Rickover

Denna fråga illustrerar de tre vanligaste fraser SQL: SELECT, FROM och WHERE. Även om vi i vårt exempel placerade dem på olika rader, kan de alla visas på samma rad. De kan också dras in på olika sätt, och ord i fraser kan separeras med ett godtyckligt antal mellanslag. Låt oss titta på egenskaperna hos varje fras.

Välj. SELECT-satsen listar kolumnerna som ska visas i den resulterande tabellen. Dessa är alltid kolumner i någon relationstabell. I vårt exempel består den resulterande tabellen av en kolumn (NAME), men i allmänhet kan den innehålla flera kolumner; den kan också innehålla beräknade värden eller konstanter. Vi kommer att ge exempel på vart och ett av dessa alternativ. Om den resulterande tabellen måste innehålla mer än en kolumn, listas alla obligatoriska kolumner efter SELECT-kommandon separerade av kommatecken. Till exempel kommer frasen SELECT WORKER_ID, NAME att resultera i en tabell som består av kolumnerna WORKER_ID och NAME.

SELECT-sats. Anger kolumnerna i den resulterande tabellen.

Från. FROM-satsen specificerar en eller flera tabeller som är åtkomliga av frågan. Alla kolumner listade i SELECT- och WHERE-satserna måste finnas i en av tabellerna som listas i FROM-kommandot. I SQL2 kan dessa tabeller definieras direkt i schemat som bastabeller eller datavyer, eller så kan de själva vara namnlösa tabeller som härrör från SQL-frågor. I det senare fallet ges begäran uttryckligen i FROM-kommandot.

FRÅN-frasen. Anger de befintliga tabellerna som är åtkomliga av frågan.

Var. WHERE-satsen innehåller ett villkor. på basis av vilken raderna i tabellen/tabellerna väljs. I vårt exempel är villkoret att SKILL_TYPE-kolumnen måste innehålla konstanten "Plasterer" innesluten i apostrof, som alltid görs med textkonstanter i SQL. WHERE-satsen är det mest flyktiga SQL-kommandot; det kan innehålla många olika villkor. Mycket av vår diskussion kommer att ägnas åt att illustrera de olika konstruktionerna som tillåts i WHERE-kommandot.

WHERE klausul. Anger villkoret baserat på vilka rader som väljs från de angivna tabellerna.

Ovanstående SQL-fråga bearbetas av systemet i följande ordning: FROM, WHERE, SELECT. Det vill säga raderna i tabellen som anges i FROM-kommandot placeras i arbetsområdet för bearbetning. WHERE-satsen tillämpas sedan på varje rad i följd. Alla rader som inte uppfyller WHERE-villkoret exkluderas från behandling. Sedan bearbetas de rader som uppfyller WHERE-villkoret av SELECT-satsen. I vårt exempel väljs NAME från varje sådan rad, och alla valda värden matas ut som frågeresultat.

Begäran: Ge all information om kontorsbyggnader.

WHERE TYPE = "Kontor"

Resultat:

BLDG IDADDRESSTYPEQLTY NIVÅSTATUS

312 Elm St., 123 Office 2 2

210 Berezovaya st. 1011 Office Z 1

111 Osinovaya st. 1213 Office 4 1

En asterisk (*) i ett SELECT-kommando betyder "hela raden." Detta är en bekväm stenografi som vi kommer att använda ofta.

Begäran: Vad är veckolönen för varje elektriker?

VÄLJ NAMN, "Veckolön = ", 40 * HRLY_RATE

WHERE SKILL_TYPE = "Elektriker"

Resultat:

M. Faraday Veckolön = 500,00

H.Columbus Veckolön = 620,00

Den här frågan illustrerar användningen av både teckenkonstanter (i vårt exempel "Veckolön = ") och beräkningar i SELECT-kommandot. Inom SELECT-satsen kan du utföra beräkningar som använder numeriska kolumner och numeriska konstanter, såväl som vanliga aritmetiska operatorer ( +, -, *, /), grupperade efter behov med parenteser. Vi har även tagit med en ny ORDER kommando BY, som sorterar frågeresultatet i stigande alfanumerisk ordning efter den angivna kolumnen. Om du vill sortera resultaten i fallande ordning måste du lägga till DESC i kommandot. ORDER BY-satsen kan sortera resultaten efter flera kolumner, några i stigande ordning och andra i fallande ordning. Sorteringens primärnyckelkolumn listas först.

Teckenkonstant. En konstant bestående av bokstäver, siffror och "special" tecken.

Begäran: Vem har ett timpris på $10 till $12?

WHERE HRLY_RATE > = 10 OCH HRLY_RATE< - 12

Resultat:

ARBETARID NAMN HRLY_RATE SKILL_TYPE SUPV_ID

Den här frågan illustrerar några av de ytterligare funktionerna i WHERE-satsen: jämförelseoperatorer och den booleska AND-operatorn. Sex jämförelseoperatorer (=,<>(inte jämnlikt),<, >, <=, >=). De booleska operatorerna AND, OR och NOT kan användas för att skapa sammansatta villkor eller för att negera ett villkor. Parenteser kan användas för att gruppera villkor, vilket är vanligt i programmeringsspråk.

Jämförelseoperatorer =,<>, <, >, <=, >=.

booleska operationer OCH (OCH), ELLER (ELLER) och INTE (HAN) .

Du kan också använda operatorn BETWEEN (between) för att formulera denna fråga:

WHERE HRLY_RATE MELLAN 10 OCH 12

BETWEEN kan användas för att jämföra en kvantitet med två andra kvantiteter, varav den första är mindre än den andra, om den jämförda kvantiteten kan vara lika med var och en av dessa kvantiteter eller något däremellan värde.

Begäran: Lista putsare, takläggare och elektriker.

WHERE SKILL_TYPE IN ("putsare", "takläggare", "elektriker")

Resultat:

WORKER_ID NAMN HRLY_RATE SKILL_TYPE SUPV_ID

1412 K.Nemo 13.75 Putsare 1520

2920 R. Garrett 10.00 Takläggare 2920

1520 G. Rickover 11,75 Putsare 1520

Denna fråga förklarar användningen av jämförelseoperatorn IN (B). WHERE-villkoret anses vara sant om radens specialtyp är placerad inuti den uppsättning som anges inom parentes, det vill säga om specialtypen är putsare, takläggare eller elektriker. Vi kommer att se IN-operatorn igen i underfrågor.

Låt oss anta att vi inte kan komma ihåg exakt stavningen av vår specialitet: "elektriker" eller "elektronikingenjör" eller något annat. Jokertecken, som ersätter odefinierade teckensträngar, gör det lättare att hitta felaktiga stavningar i en fråga.

Mönstersymboler. Tecken som ersätter odefinierade teckensträngar.

Begäran: Lista de anställda vars specialitetstyp börjar med "Elek".

WHERE SKILL_TYPE LIKE ("Elect%")

Resultat:

ARBETAR-ID NAMN HRLY_RATE SKILL_TYPE SUPV_ID

1235 M. Faraday 12.50 Elektriker 1311

1311 H. Columbus 15,50 Electric 1311

SQL har två jokertecken: % (procent) och _ (understreck). Understrecket ersätter exakt ett odefinierat tecken. Procentsatsen ersätter ett godtyckligt antal tecken, som börjar med noll. När jokertecken används krävs en LIKE-operator för att jämföra teckenvariabler med konstanter. Andra exempel:

NAMN SOM "__Columbus"

NAMN SOM "__K%"

Villkoret i det första exemplet är sant om NAMN består av två tecken följt av "Columbus". I WORKER-tabellen börjar alla namn med en första initial och en punkt. Således använder vi detta villkor. Låt oss hitta alla anställda med efternamnet "Columbus". Villkoret för det andra exemplet tillåter oss att hitta alla anställda vars efternamn börjar med bokstaven "K".

Begäran: Hitta alla jobb som börjar inom de närmaste två veckorna.

VAR START_DATE MELLAN CURRENT_DATE OCH

Resultat:(Anta att det aktuella datumet är AKTUELLT DATUM = 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

Den här frågan illustrerar användningen av operatorn BETWEEN med datum- och intervallvärden. CURRENT_DATE är en funktion som alltid returnerar dagens datum. Uttryck

CURRENT_DATE + INTERVAL "14" DAG

lägger till en tvåveckorsperiod till det aktuella datumet. Således väljs UPPDRAG (förutsatt att idag är 10/10) om dess START_DATE kolumnvärde är mellan 10/10 och 10/24. Av detta kan vi se att vi kan lägga till intervallvärden till datumfält. Dessutom kan vi multiplicera värdena för intervallen med heltalsvärden. Anta till exempel att vi vill ta reda på vilket antal som kommer att vara inom ett visst antal veckor (betecknas med variabeln NUM_WEEKS). Vi kan göra så här:

CURRENT_DATE + INTERVAL "7" DAG * NUM_VECKOR

2. Flerbordsfrågor

Möjligheten att relatera dataelement över gränserna för en enskild tabell är viktig för alla databasspråk. I relationalgebra utförs denna funktion av joinoperationen. Även om mycket av SQL är direkt baserat på relationskalkyl, kopplar SQL samman data från olika tabeller på ett liknande sätt som för sammankopplingen av relationalgebra. Nu ska vi visa hur detta går till. Överväg begäran:

Begäran:

Uppgifterna som behövs för svaret finns i två tabeller: ARBETARE och UPPDRAG. SQL-lösningen kräver att båda tabellerna anges i FROM-kommandot och att en speciell typ av WHERE-sats anges:

VÄLJ SKILL_TYPE

FRÅN ARBETARE, UPPDRAG

WHERE WORKER.WORKER_ID = UPPDRAG.ARBETARE_ID

OCH BLDG_ID = 435

Vad händer här? Vi måste överväga två steg i hur systemet behandlar denna begäran.

1. Som vanligt behandlas FROM-satsen först. Men i det här fallet, eftersom kommandot specificerar två tabeller, skapar systemet en kartesisk produkt av raderna i dessa tabeller. Detta innebär att en stor tabell skapas (logiskt) bestående av kolumner från båda tabellerna, med varje rad i en tabell parad med varje rad i den andra tabellen. I vårt exempel, eftersom WORKER-tabellen har fem kolumner och ASSIGNMENT-tabellen har fyra kolumner, kommer den kartesiska produkten som produceras av FROM-kommandot att ha nio kolumner. Det totala antalet rader i den kartesiska produkten är lika med m * n, där m är antalet rader i WORKER-tabellen; och n är antalet rader i tabellen UPPDRAG. Eftersom WORKER-tabellen har 7 rader och ASSIGNMENT-tabellen har 19 rader, kommer den kartesiska produkten att innehålla 7x19 eller 133 rader. Om kommandot FROM listar fler än två tabeller skapas en kartesisk produkt av alla tabeller som anges i kommandot.

kartesisk produkt. Resultatet av att sammanfoga varje rad i en tabell med varje en rad från ett annat bord.

2. Efter att ha skapat den gigantiska relationstabellen använder systemet WHERE-kommandot som tidigare. Varje rad i tabellen skapas av kommandot FROM. kontrolleras för att se om WHERE-villkoret är uppfyllt. Rader som inte uppfyller villkoret undantas från övervägande. SELECT-satsen tillämpas sedan på de återstående raderna.

WHERE-satsen i vår fråga innehåller två villkor:

1. ARBETARE. WORKER_ID = UPPDRAG.ARBETARE_ID

2. BLDG_ID = 435

Det första av dessa villkor är sammanfogningsvillkoret. Observera att eftersom både WORKER- och ASSIGNMENT-tabellerna innehåller en kolumn med namnet WORKER_ID, kommer deras kartesiska produkt att innehålla två kolumner med det namnet. För att skilja mellan dem, föregår vi kolumnnamnet med namnet på källtabellen, separerade med en punkt.

Det första villkoret innebär att i valfri rad måste värdet för kolumnen WORKER_ID från tabellen WORKER matcha värdet på kolumnen WORKER_ID från tabellen ASSIGNMENT. I verkligheten sammanfogar vi två tabeller av WORKER_ID. Alla rader där värdena för dessa två kolumner inte är lika exkluderas från produkttabellen. Exakt samma sak händer när man utför den naturliga sammanfogningen av relationalgebra. (Det finns dock fortfarande en viss skillnad från en naturlig koppling: SQL tar inte automatiskt bort den extra WORKER_ID-kolumnen). Den fullständiga sammanfogningen av dessa två tabeller med tilläggsvillkoret BLDG_ID = 435 visas i fig. 1. Användning av kommandot SELECT kommer slutligen att ge följande frågeresultat:

FÄRDIGHETSTYP

Murare

Taktäckare

Elektriker

Ris. 1. Gå med i ARBETARNA och UPPDRAG-tabellerna

Nu kommer vi att visa hur man kopplar en tabell till sig själv i SQL.

Begäran: Lista de anställda och ange namnen på deras chefer.

VÄLJ A.WORKER_NAME, B.WORKER_NAME

FRÅN ARBETARE A, ARBETARE B

WHERE B.WORKER_ID = A.SUPV_ID

FROM-satsen i detta exempel skapar två "kopior" av WORKER-tabellen och ger dem aliasen A och B. Ett alias är ett alternativt namn som ges till tabellen. Därefter sammanfogas kopiorna A och B av WORKER-tabellen av WHERE-kommandot baserat på likhetsvillkoret för WORKER_ID i B och SUPV_ID i A. Således kopplas varje rad från A till rad B, som innehåller information om chefen för rad A. (Fig. 2).

Ris. 2. Sammanfoga två exemplar av WORKER-bordet

Genom att välja två anställdas namn från varje rad får vi den obligatoriska listan:

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

Smeknamn. Ett alternativt namn som ges till tabellen.

A.WORKER_NAME representerar arbetaren och B.WORKER_NAME representerar chefen. Observera att vissa arbetare är sina egna chefer, vilket följer av jämlikhet WORKER_ID - SUPV_ID i deras rader.

I SQL kan du länka mer än två tabeller åt gången:

Begäran

VÄLJ WORKER_NAME

FRÅN ARBETARE, UPPDRAG, BYGGNAD

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID AND ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID OCH

TYPE = "Kontor"

Resultat:

M. Faraday

G.Rickover

J. Barrister

Observera att om ett kolumnnamn (som WORKER_ID eller BLDG_ID) förekommer i mer än en tabell, måste vi, för att undvika oklarheter, lägga till kolumnnamnet med namnet på den ursprungliga tabellen. Men om kolumnnamnet bara förekommer i en tabell, som TYPE i vårt exempel, finns det ingen tvetydighet, så tabellnamnet behöver inte anges.

SQL-kommandona i den här frågan skapar en tabell från tre relationsdatabastabeller. De två första tabellerna sammanfogas av WORKER_ID, varefter den tredje tabellen sammanfogas av BLDG_ID till den resulterande tabellen. Skick

TYPE = "Kontor"

WHERE-satsen gör att alla rader exkluderas utom de för kontorsbyggnader. Detta uppfyller kraven i begäran.

3. Underfrågor

Underfråga. Fråga i en fråga

En underfråga kan placeras i WHERE-satsdelen i en fråga, vilket utökar WHERE-satsens möjligheter. Låt oss titta på ett exempel.

Begäran: Vilka är specialiteterna för arbetarna som tilldelats byggnad 435?

VÄLJ SKTLL_TYPE

FRÅN ARBETARE DÄR WORKER_ID IN

(VÄLJ WORKER_ID

WHERE BLDG_ID = 435)

Underfråga i detta exempel

(VÄLJ WORKER_ID

WHERE BLDG_ID = 435)

En fråga som innehåller en underfråga anropas extern begäran eller huvudförfrågan. Underfrågan resulterar i skapandet av följande uppsättning medarbetar-ID:n:

ARBETARID

Extern begäran. Huvudfrågan, som innehåller alla underfrågor.

Denna uppsättning ID ersätter sedan en underfråga i den yttre frågan. Från och med denna tidpunkt exekveras den yttre frågan med den uppsättning som skapats av underfrågan. Den yttre frågan bearbetar varje rad i WORKER-tabellen enligt WHERE-satsen. Om WORKER_ID för en rad ligger i (IN)-uppsättningen som skapats av underfrågan, väljs SKILL_TYPE för raden och visas i den resulterande tabellen:

FÄRDIGHETSTYP

Murare

Taktäckare

Elektriker

Det är mycket viktigt att SELECT-satsen i underfrågan innehåller WORKER_ID och endast WORKER_ID. Annars skulle WHERE-satsen i den yttre frågan, vilket betyder att WORKER_ID finns i uppsättningen av arbetar-ID:n, inte ha någon betydelse.

Observera att en underfråga logiskt kan exekveras innan minst en rad beaktas av huvudfrågan. På sätt och vis är en underfråga oberoende av huvudfrågan. Den kan köras som en fullständig fråga. Vi säger att en sådan underfråga inte är korrelerad med huvudfrågan. Som vi snart kommer att se kan underfrågor korreleras.

Okorrelerad underfråga. En underfråga vars värde är oberoende av någon yttre fråga.

Här är ett exempel på en underfråga i en underfråga.

Begäran: Lista de anställda som tilldelats kontorsbyggnaderna.

Återigen tittar vi på frågan med vilken vi undersökte sambandet.

VÄLJ WORKER_MAME

WHERE WORKER_ID IN

(VÄLJ WORKER_ID

VAR BLDG_ID IN

WHERE TYPE = "Kontor"))

Resultat:

M. Faraday

G.Rickover

J. Barrister

Observera att vi inte behöver prefixa kolumnnamnen med tabellnamn någonstans, eftersom varje underfråga bearbetar en och endast en tabell, så inga oklarheter kan uppstå.

Frågekörning sker i en ordning inifrån och ut. Det vill säga, den innersta frågan (eller "undertill") exekveras först, sedan exekveras underfrågan som innehåller den, och sedan den yttre frågan.

Korrelerade delfrågor. Alla underfrågor som diskuterades ovan var oberoende av huvudfrågorna där de användes. Med oberoende menar vi att delfrågor kan köras på egen hand som fullständiga frågor. Vi går nu vidare för att överväga en klass av underfrågor vars exekveringsresultat kan bero på raden som anses av huvudfrågan. Sådana underfrågor kallas korrelerade underfrågor.

Korrelerad underfråga. En underfråga vars resultat beror på raden som beaktas av huvudfrågan.

Begäran: Lista de anställda vars timpriser är högre än deras chefers.

VÄLJ WORKER_NAME

WHERE A.HRLY_RATE >

(VÄLJ B.HRLY_RATE

WHERE B.WORKER_ID = A.SUPV_ID)

Resultat:

De logiska stegen för att utföra denna begäran är:

1. Systemet skapar två kopior av ARBETERARtabellen: kopia A och kopia B. Enligt hur vi definierade dem hänvisar A till medarbetaren, B hänvisar till chefen.

2. Systemet tar sedan hänsyn till varje rad A. En given rad väljs om den uppfyller WHERE-villkoret. Detta villkor innebär att en rad kommer att väljas om dess HRLY_RATE-värde är större än HRLY_RATE som genereras av underfrågan.

3. Underfrågan väljer värdet HRLY_RATE från rad B, vars WORKER_ID är lika med SUPV_ID på rad A, i det här ögonblicket beaktas av huvudförfrågan. Detta är chefens HRLY_RATE.

Observera att eftersom A.HRLY_RATE bara kan jämföras med ett värde, måste underfrågan endast returnera ett värde. Detta värde ändras beroende på vilken rad A som övervägs. Således är underfrågan korrelerad med huvudfrågan. Vi kommer att se fler exempel på korrelerade delfrågor senare när vi studerar inbyggda funktioner.

FINNS och FINNS INTE operatörer

Anta att vi vill identifiera arbetare som inte är tilldelade att arbeta i en viss byggnad. På ytan verkar det som att en sådan begäran lätt kan tillgodoses genom att helt enkelt förneka den bekräftande versionen av begäran. Anta till exempel att vi är intresserade av en byggnad med BLDG_ID 435. Tänk på begäran:

VÄLJ WORKER_ID

WHERE BLDG_ID INTE 435

Tyvärr är detta en felaktig formulering av lösningen. Förfrågan ger oss helt enkelt ID:n för arbetare som arbetar i andra byggnader. Uppenbarligen kan några av dem också tilldelas byggnad 435.

En korrekt formulerad lösning använder operatorn INTE FINNS:

VÄLJ WORKER_ID

DÄR INTE FINNS

WHERE ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID AND

Resultat:

WORKER_ID

Operatörerna EXISTS och NOT EXISTS placeras alltid före underfrågan. EXISTS utvärderas till sant om uppsättningen som genereras av underfrågan inte är tom. Om uppsättningen som genereras av underfrågan är tom tar EXISTS värdet "false". Operatören INTE FINNS fungerar naturligtvis precis tvärtom. Det är sant om resultatet av underfrågan är tomt och annars falskt.

FINNS operatör. Returnerar sant om resultatuppsättningen inte är tom.

FINNS INTE operatör. Returnerar sant om resultatuppsättningen är tom.

I det här exemplet använde vi operatorn NOT EXISTS. Underfrågan väljer alla rader i tabellen ASSIGNMENT där WORKER_ID har samma värde som raden som anses av huvudfrågan, och BLDG_ID är lika med 435. Om denna uppsättning är tom, är arbetarraden som anses av huvudfrågan. valt, eftersom detta innebär att Den här anställde inte arbetar i byggnad 435.

I lösningen vi tillhandahöll använde vi en korrelerad underfråga. Om vi använder IN-operatorn istället för INTE FINNS, kan vi klara oss med en okorrelerad underfråga:

VÄLJ WORKER_ID

WHERE WORKER_ID INTE IN

(VÄLJ WORKER_ID

WHERE BLDG_ID = 435)

Denna lösning är enklare än lösningen med operatören INTE FINNS. En naturlig fråga uppstår: varför behöver vi FINNS och FINNS INTE alls? Svaret är att INTE FINNS är det enda sättet att lösa frågor som innehåller ordet "varje" i villkoret. Sådana frågor löses i relationalgebra med hjälp av divisionsoperationen och i relationskalkyl med den universella kvantifieraren. Här är ett exempel på en fråga med ordet "varje" i sitt skick:

Begäran: Lista de anställda som är tilldelade varje byggnad.

Denna fråga kan implementeras i SQL med hjälp av dubbla negationer. Vi omformulerar frågan så att den inkluderar ett dubbelt negativt:

Begäran: Lista sådana anställda för vem Inte det finns en byggnad som de inte är anvisade till.

Vi lyfte fram det dubbla negativa. Det är tydligt att denna begäran logiskt sett motsvarar den föregående.

Nu vill vi formulera lösningen i SQL. För att göra den slutliga lösningen lättare att förstå ger vi först en lösning på ett preliminärt problem: problemet med att identifiera alla byggnader för vilka en hypotetisk arbetare, "1234" Inte utsedd.

(I) VÄLJ BLDG_ID

DÄR INTE FINNS

ASSIGNMENT.WORKER_ID = 1234)

Vi har markerat denna fråga (I) eftersom vi kommer att hänvisa till den senare. Om det inte finns någon byggnad som uppfyller denna begäran, tilldelas arbetare 1234 till varje byggnad och uppfyller därför villkoren för den ursprungliga begäran. För att erhålla en lösning på den ursprungliga frågan måste vi generalisera frågan (I) från en specifik arbetare 1234 till variabeln WORKER_ID och förvandla denna modifierade fråga till en underfråga till den större frågan. Här är lösningen:

(II) VÄLJ ARBETARE_ID

DÄR INTE FINNS

WHERE ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID OCH

ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID)

Resultat:

ARBETARID

Observera att underfrågan som börjar på den fjärde raden i fråga (II) är identisk med fråga (I), med "1234" ersatt av WORKER.WORKER_ID. Fråga (II) kan läsas enligt följande:

Välj WORKER_ID från WORKER om det inte finns någon byggnad som WORKER_ID inte är tilldelad.

Detta matchar villkoren för den ursprungliga begäran.

Vi ser att operatorn NOT EXISTS kan användas för att formulera de frågor som krävde en divisionsoperation i relationalgebra och en universell kvantifierare i relationskalkyl. Ur ett användarvänligt perspektiv erbjuder NOT EXISTS-operatorn ingen speciell fördel, vilket innebär att SQL-frågor som använder NOT EXISTS två gånger inte är lättare att förstå än relationella algebralösningar med division eller relationskalkyllösningar med universella kvantifierare. Mer forskning kommer att behövas för att skapa språkkonstruktioner som gör att sådana frågor kan lösas mer naturligt.

Inbyggda funktioner

Låt oss överväga frågor av denna typ:

Vilka är de högsta och lägsta timpriserna? Vad är det genomsnittliga antalet dagar anställda arbetar i byggnad 435? Vad är det totala antalet dagar som avsatts för putsarbeten på byggnad 312? Hur många olika specialiteter finns det?

För att svara på dessa frågor krävs statistiska funktioner som tittar på många rader i en tabell och returnerar ett enda värde. Det finns fem sådana funktioner i SQL, kallade inbyggda funktioner eller inställda funktioner. Dessa funktioner är SUMMA (summa), AVG (genomsnitt), COUNT (kvantitet), MAX (max) och MIN (minimum).

Inbyggd funktion (inställningsfunktion). En statistisk funktion som fungerar på flera rader: SUM (summa), AVG (genomsnitt), COUNT (kvantitet), MAX (max), MIN (minimum).

Begäran: Vilka är de högsta och lägsta timpriserna?

VÄLJ MAX(HRLY_RATE), MIN(HRLY_RATE)

Resultat: 17.40, 8.20

MAX funktioner och MIN fungerar på en tabellkolumn. De väljer högsta respektive lägsta värde från den här kolumnen. Vår frågeformulering innehåller ingen WHERE-sats. För de flesta frågor är detta kanske inte fallet, vilket vårt nästa exempel visar.

Begäran: Vad är det genomsnittliga antalet dagar anställda arbetar i byggnad 435?

VÄLJ AVG(NUM_DAYS)

WHERE BLDG_ID =435

Resultat: 12.33

Begäran: Vad är det totala antalet dagar som avsatts för putsarbeten på byggnad 312?

VÄLJ SUMMA(NUM_DAYS)

FRÅN UPPDRAG, ARBETARE

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID AND

SKILL_TYPE = "Plåsterare" OCH

Resultat: 27

Lösningen använder en koppling mellan tabellerna ASSIGNMENT och WORKER. Detta är nödvändigt eftersom SKILL_TYPE finns i tabellen WORKER och BLDG_ID finns i tabellen ASSIGNMENT.

Begäran: Hur många olika specialiteter finns det?

VÄLJ ANTAL (DISTINCT SKILL_TYPE)

Resultat: 4

Eftersom samma specialitet kan visas i flera olika rader måste du använda nyckelordet DISTINCT i den här frågan för att förhindra att systemet räknar samma specialitetstyp mer än en gång. DISTINCT-operatorn kan användas med vilken som helst av de inbyggda funktionerna, även om den givetvis är redundant med MAX- och MIN-funktionerna.

DISTINKT. En operatör som eliminerar dubbla linjer.

SUM- och AVG-funktionerna ska endast användas med numeriska kolumner. Andra funktioner kan användas med både numeriska data och teckendata. Alla funktioner utom COUNT kan användas med beräknade uttryck. Till exempel:

Begäran: Vad är den genomsnittliga veckolönen?

VÄLJ AVG (40 * HRLY_RATE)

Resultat: 509.14

COUNT kan referera till en hel rad snarare än en enskild kolumn :

Begäran: Hur många byggnader har kvalitetsnivå 3?

VÄLJ ANTAL (*)

FRÅN BYGGNAD VAR

Resultat: 3

Som alla dessa exempel visar, om ett SELECT-kommando innehåller en inbyggd funktion, kan inget annat visas i det SELECT-kommandot. Det enda undantaget från denna regel är GROUP BY-satsen, som vi ska titta på nu.

GROUP BY och HAVING-satser

I förvaltningen krävs ofta statistisk information om varje grupp i många grupper. Tänk till exempel på följande fråga:

Begäran: För varje chef, ta reda på det maximala timpriset bland hans underordnade.

För att lösa detta problem måste vi dela in arbetarna i grupper efter deras chefer. Vi kommer sedan att fastställa maxbudet inom varje grupp. I SQL görs detta på följande sätt:

GRUPPER EFTER SUPV_ID

Resultat:

SUPV_IDMAX(HRLY RATE)

När den här frågan bearbetas delar systemet först upp raderna i WORKER-tabellen i grupper med hjälp av följande regel. Rader placeras i samma grupp om och endast om de har samma SUPV_ID. SELECT-satsen tillämpas sedan på varje grupp. Eftersom det bara finns ett SUPV_ID-värde i denna grupp, finns det ingen SUPV_ID-osäkerhet i gruppen. För varje grupp matar SELECT-satsen ut SUPV_ID och beräknar och matar också ut MAX(HRLY_RATE)-värdet. Resultatet presenteras ovan.

I ett SELECT-kommando med inbyggda funktioner kan endast de kolumner som ingår i GROUP BY-satsen visas. Observera att SUPV_ID kan användas i ett SELECT-kommando eftersom det ingår i GROUP BY-satsen.

GROUP BY klausul. Indikerar att rader ska delas in i grupper med gemensamma värden för den eller de angivna kolumnerna.

GROUP BY-satsen låter dig utföra vissa komplexa beräkningar. Vi kanske till exempel vill ta reda på genomsnittet av dessa högsta bud. Beräkning med inbyggda funktioner är dock begränsad i den meningen att den inte tillåter att inbyggda funktioner används i andra inbyggda funktioner. Alltså ett uttryck som

AVG(MAX(HRLY_RATE))

förbjuden. Genomförandet av en sådan begäran kommer att bestå av två steg. Först måste vi lägga maxbuden i en ny tabell, och i det andra steget måste vi beräkna deras genomsnitt.

Du kan använda WHERE-satsen med kommandot GROUP BY:

Begäran: Ta reda på för varje typ av byggnad genomsnittlig nivå kvalitet bland byggnader med status 1.

VÄLJ TYP, AVG(QLTY_LEVEL)

VAR STATUS = 1

Resultat:

TYPEAVG(QLTY_LEVEL)

Butik 1

Bostadshus 3

WHERE-satsen exekveras före GROUP BY-satsen. Således kan ingen grupp innehålla en rad som har en annan status än 1. Status 1-raderna grupperas efter TYPE-värde och sedan tillämpas en SELECT-sats på varje grupp.

HA fras. Sätter villkor på grupper.

Vi kan även tillämpa villkor på grupper skapade av GROUP BY-satsen. Detta görs med hjälp av HAVING-frasen. Anta till exempel att vi bestämde oss för att göra en av de tidigare frågorna mer specifik:

Begäran: För varje chef som har mer än en underordnad, ta reda på det maximala timpriset bland hans underordnade.

Vi kan återspegla detta tillstånd med lämpligt HAVING-kommando:

VÄLJ SUPV_ID, MAX(HRLY_RATE)

FRÅN ARBETSGRUPP AV SUPV_ID

ATT HA ANTAL(*) > 1

Resultat:

SUPV_ID MAX(HRLY_RATE)

Skillnaden mellan WHERE- och HAVING-satser är att WHERE gäller rader, medan HAVING gäller grupper.

En fråga kan innehålla både en WHERE- och en HAVING-sats. I det här fallet exekveras WHERE-satsen först eftersom den exekveras före grupperingen. Överväg till exempel följande modifiering av den tidigare frågan:

Begäran: För varje typ av byggnad, ta reda på den genomsnittliga kvalitetsnivån bland byggnader med status 1. Tänk bara på de typer av byggnader vars högsta kvalitetsnivå inte överstiger 3.

VÄLJ TYP, AVG (QLTY_JLEVEL)

VAR STATUS = 1

HAR MAX(QLTY_LEVEL)<= 3

Resultat:

TYP AVG(QLTY_LEVEL)

Butik 1

Bostadshus 3

Observera att från och med FROM-satsen exekveras satserna i ordning, och sedan tillämpas SELECT-satsen. Således tillämpas WHERE-satsen på BUILDING-tabellen, och alla rader där STATUS skiljer sig från 1 raderas. De återstående raderna är grupperade efter TYPE; alla rader med samma TYPE-värde hamnar i samma grupp. Således skapas flera grupper, en för varje TYPE-värde. HAVING-satsen tillämpas sedan på varje grupp, och de grupper vars högsta kvalitetsnivåvärde överstiger 3 tas bort. Slutligen tillämpas SELECT-satsen på de återstående grupperna.

7. Inbyggda funktioner och underfrågor

Inbyggda funktioner kan endast användas i en SELECT-sats eller HAVING-kommando. En SELECT-sats som innehåller en inline-funktion kan dock vara en del av en underfråga. Låt oss titta på ett exempel på en sådan underfråga:

Begäran: Vilka arbetare har högre timpris än genomsnittet?

VÄLJ WORKER_NAME

WHERE HRLY_RATE >

(VÄLJ AVG(HRLY_RATE)

Resultat:

H. Columbus

Observera att underfrågan inte är korrelerad med huvudfrågan. Underfrågan returnerar exakt ett värde - det genomsnittliga timpriset. Huvudfrågan väljer en arbetare endast om hans frekvens är högre än det beräknade genomsnittet.

Korrelerade frågor kan också använda inbyggda funktioner:

Fråga: Vilken anställd har ett timpris som är högre än det genomsnittliga timpriset bland samma chefs underordnade?

I det här fallet måste vi, istället för att beräkna ett genomsnittligt timpris för alla arbetare, beräkna det genomsnittliga priset för varje grupp av arbetare som rapporterar till samma chef. Dessutom måste vår beräkning göras på nytt för varje arbetare som beaktas av huvudfrågan:

VÄLJ A. WORKER_NAME

SQL låter dig kapsla frågor inom varandra. Vanligtvis returnerar en underfråga ett enda värde, som kontrolleras för att se om predikatet är sant.

Typer av söktermer:
. Jämförelse med resultatet av en underfråga (=, >=)
. Kontrollerar att de tillhör resultatet av en underfråga (IN)
. Existenskontroll (FINNS)
. Multipel (kvantitativ) jämförelse (ALLA, ALLA)

Anmärkningar om kapslade frågor:
. En underfråga måste endast välja en kolumn (förutom en underfråga med ett EXISTS-predikat), och dess resultatdatatyp måste matcha datatypen för värdet som anges i predikatet.
. I vissa fall kan du använda nyckelordet DISTINCT för att säkerställa att ett enda värde returneras.
. Du kan inte inkludera en ORDER BY- eller UNION-sats i en underfråga.
. Underfrågan kan placeras antingen till vänster eller till höger om sökvillkoret.
. Underfrågor kan använda aggregeringsfunktioner utan en GROUP BY-sats, som automatiskt returnerar ett specialvärde för valfritt antal rader, ett speciellt IN-predikat och kolumnbaserade uttryck.
. När det är möjligt bör du använda JOIN-tabellkopplingar istället för underfrågor.

Exempel på kapslade frågor:

SELECT * FROM Orders WHERE SNum=(SELECT SNum FROM Säljare WHERE SName=’Motika’)
VÄLJ * FRÅN Beställningar WHERE SNum IN (VÄLJ SNum FROM Säljare WHERE City=’London’)
SELECT * FROM Orders WHERE SNum=(SELECT DISTINCT SNum FROM Orders WHERE CNum=2001)
VÄLJ * FRÅN Orders WHERE Amt>(SELECT AVG(Amt) FROM Orders WHERE Odate=10/04/1990)
VÄLJ * FRÅN Kund WHERE CNum=(VÄLJ SNum+1000 FRÅN Säljare WHERE SName=’Serres’)

2) Relaterade underfrågor

I SQL kan du skapa underfrågor som refererar till en tabell från en yttre fråga. I det här fallet exekveras underfrågan flera gånger, en gång för varje tabellrad från den yttre frågan. Därför är det viktigt att underfrågan använder indexet. En underfråga kan komma åt samma tabell som en yttre. Om den yttre frågan returnerar ett relativt litet antal rader, kommer den länkade underfrågan att vara snabbare än den olänkade. Om en underfråga returnerar ett litet antal rader blir den relaterade frågan långsammare än den orelaterade frågan.

Exempel på relaterade underfrågor:

SELECT * FROM Säljare Main WHERE 1(SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //returerar alla order vars värde överstiger det genomsnittliga ordervärdet för en given kund

3) Predikat FINNS

Syntaktisk form: EXISTERAR ()

Predikatet tar en underfråga som ett argument och utvärderas till sant om underfrågan har utdata och falskt annars. Underfrågan exekveras en gång och kan innehålla flera kolumner, eftersom deras värden inte kontrolleras, men resultatet av närvaron av rader registreras helt enkelt.

Anteckningar om predikatet EXISTS:
. EXISTS är ett predikat som returnerar TRUE eller FALSE och kan användas ensamt eller med andra booleska uttryck.
. EXISTS kan inte använda aggregeringsfunktioner i sin underfråga.
. I korrelerade underfrågor exekveras EXISTS-predikatet för varje rad i den yttre tabellen.
. Du kan kombinera predikatet EXISTS med tabellkopplingar.

Exempel på predikatet EXISTS:

SELECT * FROM Customer WHERE EXISTS(SELECT * FROM Customer WHERE City='San Jose') – returnerar alla kunder om någon av dem bor i San Jose.
VÄLJ DISTINCT SNum FROM Customer First WHERE NOT EXISTS (VÄLJ * FROM Customer Send WHERE Send.SNum=First.SNum AND Send.CNumFirst.CNum) – returnerar antalet säljare som endast betjänade en kund.
VÄLJ DISTINKT F.SNum, SName, F.City FRÅN Säljare F, Customer S WHERE EXISTS (VÄLJ * FRÅN Customer T WHERE S.SNum=T.SNum AND S.CNumT.CNum AND F.SNum=S.SNum) – returnerar nummer, namn och hemort för alla säljare som betjänade flera kunder.
VÄLJ * FRÅN Säljare Frst VAR FINNS (VÄLJ * FRÅN Kund Skicka WHERE Frst.SNum=Skicka.SNum OCH 1

4) Predikat för kvantitativ jämförelse

Syntaktisk form: (=|>|=|) NÅGON|ALLA ()

Dessa predikat använder en underfråga som ett argument, men jämfört med EXISTS-predikatet används de tillsammans med relationspredikat (=,>=). I denna mening liknar de IN-predikatet, men används endast med underfrågor. Standarden tillåter att SOME-nyckelordet används istället för ALLT, men inte alla DBMS stödjer det.

Anmärkningar om jämförelsepredikat:
. ALL-predikatet utvärderas till TRUE om varje värde som valts under exekveringen av underfrågan uppfyller villkoret som specificerats i det yttre frågepredikatet. Det används oftast med ojämlikheter.
. Predikatet ANY utvärderas till TRUE om minst ett värde valt under exekveringen av delfrågan uppfyller villkoret som specificerats i det yttre frågepredikatet. Det används oftast med ojämlikheter.
. Om underfrågan inte returnerar några rader, tar ALL automatiskt värdet TRUE (det anses att jämförelsevillkoret är uppfyllt), och för ALLA tar det värdet FALSE.
. Om jämförelsen är TRUE för inga rader och det finns en eller flera rader med ett NULL-värde, returnerar ANY OKÄNT.
. Om jämförelsen är FALSK för inga rader och det finns en eller flera rader med ett NULL-värde, returnerar ALL OKÄNT.

Exempel på predikatet för kvantitativ jämförelse:

VÄLJ * FRÅN Säljare VAR stad=VÄLJ STAD FRÅN kund)
VÄLJ * FRÅN Beställningar WHERE Amt ALL(VÄLJ Betyg FRÅN Customer WHERE City=’Rom’)

5) Unikhetspredikat

UNIK|SÄRSKILD ()

Predikatet används för att kontrollera unikheten (frånvaro av dubbletter) i utdata från underfrågan. Dessutom, i UNIQUT-predikatet anses strängar med NULL-värden vara unika, och i DISTINCT-predikatet anses två odefinierade värden vara lika med varandra.

6) Matchpredikat

MATCH ()

MATCH-predikatet testar om värdet på en frågesträng matchar värdet på någon sträng som härrör från underfrågan. Den här underfrågan skiljer sig från IN AND ANY-predikaten genom att den tillåter bearbetning av "partiella" (PARTIAL) matchningar som kan förekomma bland rader som har vissa NULL-värden.

7) Frågor i avsnittet FRÅN

Faktum är att det är lagligt att använda en underfråga där en tabellreferens är tillåten.

SELECT CName, Tot_Amt FROM Customer, (SELECT CNum, SUM(Amt) AS Tot_Amt FROM Order GROUP BY CNum) WHERE City=’London’ AND Customer.CNum=Orders.CNum
//subquery returnerar det totala antalet beställningar som lagts av varje kund från London.