Exempel på kapslade frågor med förklaring. Komplexa SQL-frågor
När man hämtar data är det ofta nödvändigt att kombinera information från flera relaterade tabeller. Detta kan göras med hjälp av kapslade frågor eller med hjälp av en join med SQL.
Kapslade frågor
För vårt exempel, låt oss säga att vi behövde ta reda på namnen på noder som besökte webbplatsen www.dom2.ru. Den nödvändiga informationen kan erhållas på begäran:
SELECT hst_name FROM hosts WHERE hst_pcode IN (VÄLJ vis_hstcode FROM besök, webbplatser WHERE (sit_pcode = vis_sitcode) OCH (sit_name LIKE "www.dom2.ru"));
Låt oss titta närmare på denna begäran. Den första SELECT-satsen behövs för att välja nodnamn. För att välja de namn vi behöver, innehåller begäran en WHERE-sektion, i vilken primärnyckel Tabellen "Nodes" (hst_pcode) kontrolleras för medlemskap i en uppsättning (IN-operator). Uppenbarligen bör uppsättningen för att kontrollera medlemskap returneras av den andra SELECT-satsen inom parentes. Låt oss titta på det separat:
VÄLJ vis_hstcode FRÅN besök, webbplatser WHERE (sit_pcode = vis_sitcode) OCH (sit_name LIKE "www.dom2.ru")
För innehållet i tabellerna i vårt exempel kommer underfrågan att returnera följande uppsättning värden
Ansluter med SQL
Som nämnts ovan är ett sätt att hämta data från flera tabeller att ansluta tabellerna med SQL. Huvudsyftet med en sådan koppling är att skapa en ny relation som kommer att innehålla data från två eller flera ursprungliga relationer.
Inre koppling
Låt oss titta på ett exempel:
VÄLJ hst_name, sit_name, vis_timestamp FROM hosts, visits, sites WHERE (hst_pcode = vis_hstcode) AND (vis_sitcode = sit_pcode)
Denna förfrågan kommer att returnera följande data
| hst_name | sit_name | vis_tidsstämpel |
| ws1 | www.dom2.ru | 2012-08-01 07:59:58.209028 |
| ws1 | www.vkontakte.ru | 2012-08-01 08:00:10.315083 |
| 1-1 | www.vkontakte.ru | 2012-08-01 08:00:20.025087 |
| 1-2 | www.opennet.ru | 2012-08-01 08:00:26.260159 |
I det här exemplet, från tre tabeller (värdar, besök, webbplatser), väljs och skapas ett fält nytt bord, som samlar in namnen på värdar, besökta webbplatser och tidpunkten för besök. Presentationen av de uppgifter som förenas styrs av villkoren i WHERE-klausulen. Du kan se att det finns två villkor som förbinder de tre tabellerna. Eftersom besökstabellen (besök) innehåller deras identifierare istället för värdnamnet och webbplatsens namn, lägger vi till ett villkor vid sammankoppling av tabellerna för att länka data med identifierare och sedan faller allt på plats. Om det av någon anledning, i motsats till referensintegriteten, finns poster i besökstabellen med identifieraren för en icke-existerande nod eller plats, kommer de inte att visas i resultatuppsättningen av frågan i det här exemplet.
Ovanstående exempel är lite förenklat och på grund av en liten förenkling försvinner klarheten. En mer visuell form av en fråga som sammanfogar flera tabeller och returnerar samma datamängd skulle se ut
SELECT hst_name, sit_name, vis_timestamp FROM hosts JOIN-besök PÅ (hst_pcode = vis_hstcode) JOIN-webbplatser PÅ (vis_sitcode = sit_pcode);
Begäran innehåller två JOIN… ON-operatörer. Eftersom "Gå med" kan översättas som "anslutning" eller "union", är detta exempel mer vältaligt. Om du försöker översätta texten i en SQL-fråga till ryska får du något liknande
SELECT (fält) hst_name, sit_name, vis_timestamp FROM (tabell) värdar JOINING (med tabell) besök BY (condition) (hst_pcode = vis_hstcode) JOINING (med tabell) webbplatser BY (condition) (vis_sitcode = sit_pcode);
Ryska ord inom parentes har lagts till för att göra frågan lättare att förstå. Du kan använda någon av ovanstående metoder för att skriva frågor.
Yttre sammanfogning
Metoderna som används ovan för att sammanfoga tabeller kallas inre koppling(inre koppling). Denna anslutningsmetod har nackdelar. Till exempel, om vi inte hade några besök på någon av webbplatserna, eller om en av noderna inte gjorde ett enda besök, kommer platsen eller noden inte att finnas i den resulterande datamängden. I exemplet ovan kan du se att webbplatsen www.yandex.ru saknas i data, liksom nod 1-3. Ibland är detta oönskat och i sådana fall använder de yttre sammanfogning(yttre sammanfogning). Den yttre sammanfogningen kan vara vänster(vänster gå med) och höger(höger gå med). Sammanfogningssidan (vänster eller höger) motsvarar tabellen från vilken hela data kommer att väljas. Således, när du använder en LEFT JOIN, kommer data från tabellen till vänster om JOIN-operatorn att väljas i sin helhet. Låt oss backa upp detta med ett exempel. Låt oss säga att vi måste välja ALLA noder och de besök som är kopplade till dem. Detta kan göras på begäran
SELECT hst_name, vis_timestamp FROM hosts LEFT JOIN besök PÅ (hst_pcode = vis_hstcode);
Var uppmärksam på de uppgifter som kommer att returneras som svar på begäran
| hst_name | vis_tidsstämpel |
| ws1 | 2012-08-01 07:59:58.209028 |
| ws1 | 2012-08-01 08:00:10.315083 |
| 1-1 | 2012-08-01 08:00:20.025087 |
| 1-2 | 2012-08-01 08:00:26.260159 |
| 1-3 |
Man kan se att nod 1-3 inte motsvarar ett enda besök, men den finns fortfarande i listan. RIGHT JOIN fungerar på liknande sätt. En fråga som returnerar samma datauppsättning kan skrivas med hjälp av RIGHT JOIN:
VÄLJ hst_name, vis_timestamp FROM besök RIGHT JOIN värdar PÅ (hst_pcode = vis_hstcode);
I det här fallet måste du ändra LEFT JOIN till en RIGHT JOIN och byta besöks- och värdtabellerna i frågan.
Använder UNION
Ibland behöver du få två listor med poster från tabeller som en. För detta ändamål kan den användas nyckelord UNION, som låter dig kombinera resultatuppsättningarna av två frågor till en datamängd. Låt oss säga att vi behöver få en lista som innehåller nätverksnoder och webbplatsnamn. Tabellerna är olika, och därför kommer frågorna att vara olika. Hur kombinerar man allt till en datamängd? Enkelt, men det finns vissa krav för sådan "limning" av frågor:
§ begäran måste innehålla samma antal fält;
§ Datatyperna för fälten i de sammanslagna frågorna måste också matcha.
För resten, använder UNIONär inte komplicerat. Till exempel, för att få en lista med värdnamn och webbplatsnamn som en uppsättning data, kör vi följande fråga:
SELECT hst_name AS name FROM hosts UNIONSELECT sit_name AS name FROM sites;
Detta tillvägagångssätt kan orsaka problem med att sortera poster. För att säkerställa att listan över platser kommer efter listan med noder kan du medvetet lägga till ett heltalsfält för att indikera numret som kommer att delta i sorteringen. Till exempel
SELECT 1 AS level, hst_name AS name FROM hosts UNIONSELECT 2 AS level, sit_name AS name FROM sitesORDER BY level, name;
FINNS och FINNS INTE villkor
Ibland är det nödvändigt att välja poster från en tabell som matchar (eller inte matchar) poster i andra tabeller. Låt oss säga att vi behöver en lista över webbplatser som inte har besökts. Du kan få en sådan lista genom att begära
SELECT sit_name FROM sites WHERE ((SELECT COUNT(*) FROM visits WHERE vis_sitcode = sit_pcode) = 0);
För vårt exempel blir listan kort:
| sit_name |
| www.yandex.ru |
Förfrågan fungerar så här:
§ Webbplatskoden och dess namn väljs från webbplatstabellen;
§ Webbplatskoden skickas till en kapslad fråga, som räknar poster med denna kod i besökstabellen;
§ COUNT(*)-funktionen kommer att räkna posterna och returnera deras antal, vilket kommer att skickas till villkoret;
§ om villkoret är sant (antalet poster är 0), läggs platsnamnet till i listan.
Om vissa personer tycker att den här frågan är förvirrande kan du uppnå samma resultat med en fråga som använder INTE FINNS:
SELECT sit_name FROM sites WHERE NOT EXISTS (SELECT vis_pcode FROM visits WHERE vis_sitcode = sit_pcode);
Uttrycket FINNS INTE (enligt min mening) ger ytterligare klarhet och är mer förståeligt. Uttrycket EXISTS fungerar på liknande sätt, vilket kontrollerar förekomsten av poster.
Visningar (VIEW)
Views (VIEW) används för att möjliggöra beständighet komplex fråga på servern under angivet namn. Låt oss säga att du ofta behöver begära data genom att skriva en stor fråga. Om du närmar dig problemet progressivt kan du skapa en idé. Detta är lätt att göra. Till exempel,
SKAPA VY show_dom2 ASSELECT hst_name FRÅN värdar WHERE hst_pcode IN (VÄLJ vis_hstcode FRÅN besök, webbplatser WHERE (sit_pcode = vis_sitcode) OCH (sit_name LIKE "www.dom2.ru"));
Det är faktiskt allt. En uppmärksam observatör kanske har märkt att du faktiskt kan ta emot en förfrågan och i början lägga till orden "SKAPA VY<имя>SOM". Det är på denna princip som vi kan rekommendera att skapa representationer. Skapa en begäran, se till att den fungerar och lägg sedan till allt som behövs för att spara denna begäran på servern som en vy. Den enda nackdelen med att använda vyer är att vissa särskilt avancerade frågeskrivtekniker kanske inte fungerar i vyer. Tyvärr finns det väldigt lite information om vyer i postgreSQL-dokumentationen, och du kan definitivt ta reda på vad som kan användas och vad som inte kan användas genom försök och misstag. Genom att spara begäran på servern som en vy kan du köra den så många gånger du vill, med en begäran som
VÄLJ * FRÅN show_dom2;
Det är viktigt att notera att när man kör en fråga som väljer data från en vy, väljs data från tabellerna genom en fråga som lagras i vyn. Vyn är helt dynamisk och den data som returneras av vyn kommer att vara aktuell när data i tabellerna uppdateras. Du kan ta bort en vy med en begäran som
DROP VIEW show_dom2;
Slutsats
beställning av datarapport
I denna kursarbete en databas "Stationery Warehouse" utvecklades, som innehåller alla nödvändig information om produkter, kunder, leverantörer och beställningar. Med hjälp av min databas kan du enkelt och utan specialkunskap upprätthålla en databas som låter dig utföra alla operationer med kunder, beställningar och tillverkare. Det vill säga lägg till, ändra, uppdatera, ta bort och visa alla tillgängliga och inmatade data. Frågor och rapporter sammanställdes utifrån databasen.
SLUTSATS
BIBLIOGRAFI
BILAGA A
APPENDIX B
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)
SELECT * FROM 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 underfrågan returnerar ett litet antal rader, då relaterad begäran kommer att köras långsammare än den obundna.
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: (=|>|=|) ALLA|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 ALLA, 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=NÅGON(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|DISTINKT ()
Predikatet används för att kontrollera unikheten (frånvaro av dubbletter) i underfrågans utdata. Dessutom, i UNIQUT predikat raderna med NULL-värden anses vara unika, och i predikatet DISTINCT 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.
8) Rekursiva frågor
MED REKURSIV
Q1 SOM VÄLJ … FRÅN … VAR …
Q2 SOM VAL … FRÅN … VAR …
En underfråga är en fråga som finns i WHERE nyckelordsuttrycket för en annan fråga för att ytterligare begränsa utdata. Underfrågor kallas också kapslade frågor. De används för att införa villkor för utdata. Underfrågor kan användas med SELECT-, INSERT-, UPDATE- eller DELETE-satser.
I vissa fall kan en underfråga användas istället för att länka tabeller, och därigenom länka tabelldata implicit. När du använder en underfråga i en fråga, exekveras underfrågan först, och först därefter exekveras frågan som innehåller den, med hänsyn tagen till villkoren för att utföra underfrågan. En underfråga kan användas i antingen nyckelordsuttrycket WHERE eller nyckelordsuttrycket HAVING i huvudfrågan. Logiska operationer och jämförelseoperationer som =, >,<, о, IN, NOT IN, AND, OR и т п. можно использовать в подзапросе. Все, что применимо к обычному запросу, применимо и к подзапросу.
När du skriver underfrågor måste du följa följande regler.
Underfrågan måste omges inom parentes.
En underfråga kan bara referera till en kolumn i dess SELECT-sökordsuttryck, om inte huvudfrågan använder en jämförelse med flera kolumner från underfrågan.
Nyckelordet ORDER BY kan inte användas i en underfråga, även om ORDER BY kan användas i huvudfrågan. Istället för ORDER BY kan du använda GROUP BY i en underfråga.
En underfråga som returnerar flera rader med data kan endast användas i operatorer som tillåter flera värden, som IN.
Operatorn BETWEEN kan inte användas på en underfråga, men den kan användas i själva underfrågan. Den grundläggande syntaxen för en subquery-sats är följande.
SELECT kolumnnamn FRÅN tabell WHERE kolumnnamn = (VÄLJ kolumnnamn FRÅN tabell WHERE-villkor);
Precis som en underfråga kan kapslas i en huvudfråga, kan en underfråga också kapslas in i en underfråga. I huvudfrågan exekveras underfrågan innan den huvudsakliga exekveras, och på samma sätt, i en underfråga, kommer underfrågan som är kapslad i den att exekveras först
Exempel. Låt det vara nödvändigt att bestämma antalet ämnen med ett betyg som överstiger medelbetyget för en student med identifierare 301:
SELECT COUNT (DISTINCT subj_id), marker FROM exam_marks GROUP BY mark HAVING mark > (SELECT AVG(mark) FROM exam_marks WHERE stud_id=301);
Att använda flera underfrågor i en sats ökar den tid som krävs för att bearbeta begäran och ökar sannolikheten för fel på grund av satsens komplexitet.
Länkade underfrågor är tillåtna i många SQL-implementationer. När du använder underfrågor i en inre fråga kan du referera till tabellen vars namn anges i FROM-satsen i den yttre frågan. Sådana underfrågor kallas länkade. Den associerade underfrågan exekveras en gång för varje rad i huvudfrågetabellen, enligt följande:
En rad väljs från tabellen vars namn anges i den yttre frågan;
Underfrågan exekveras och det resulterande värdet används för att analysera den raden i WHERE-satsen i den yttre frågan;
Baserat på resultatet av att utvärdera detta tillstånd, fattas ett beslut att inkludera/inte inkludera raden i utdata;
Proceduren upprepas för nästa rad externa frågetabeller.
GROUP BY-satsen låter dig gruppera poster som returneras av en SELECT-fråga med värdet av ett visst fält. Genom att använda HAVING-satsen kan du filtrera sådana grupper under utmatning. HAVING-satspredikatet utvärderas inte för varje resultatrad, utan för varje grupp av utdataposter som genereras GRUPP erbjudande BY av den yttre begäran.
SELECT exam_date, SUM(mark) FROM exam_marks a GROUP BY exam_date
HA 10< (SELECT COUNT(mark) FROM exam_marks b
WHERE a.exam_date=b.exam_date);
Det är nödvändigt, baserat på uppgifterna i exam_marks-tabellen, att bestämma summan av betyg som erhållits av studenter (markeringsfältsvärden), gruppera betygsvärdena efter tentamensdatum och exkludera de dagar då antalet studenter som tar prov under dagen var mindre än tio.
Sammanfattning: En underfråga är en fråga som körs i en annan fråga för ett jobb ytterligare villkor på utdata. En underfråga kan användas i nyckelordsuttryck WHERE och HAVING. Syntaxen för underfrågor skiljer sig praktiskt taget inte från syntaxen för en vanlig fråga. Det finns bara mindre begränsningar. En av dessa begränsningar är förbudet mot att använda nyckelordet ORDER BY i underfrågor, men du kan använda GROUP BY istället, vilket ger nästan samma effekt. Underfrågor används för att placera villkor i frågor där den exakta informationen inte är känd, vilket ökar kraften och flexibiliteten hos SQL.
19. Skapa kapslade sql-frågor. Exempel implementering.
Med SQL kan du kapsla frågor inuti varandra. Vanligtvis genererar den inre frågan ett värde som testas i den yttre frågans predikat (i WHERE- eller HAVING-satsen) för att avgöra om det är sant eller falskt. I samband med en underfråga kan du använda predikatet EXISTS, som returnerar sant om utdata från underfrågan inte är tom.
När den kombineras med de andra funktionerna hos select-operatorn, såsom gruppering, är en underfråga ett kraftfullt verktyg för att uppnå önskat resultat. I FRÅN-delen SELECT-sats Det är tillåtet att tillämpa synonymer på tabellnamn om vi behöver mer än en instans av en viss relation när vi skapar en fråga. Synonymer anges med nyckelordet AS, som kan utelämnas helt. Så FROM-delen kan se ut så här:
FRÅN Rl AS A, Rl AS B
FRÅN Rl A. Rl B:
båda uttrycken är ekvivalenta och anses vara tillämpningar av SELECT-satsen på två instanser av tabell R1.
Låt oss till exempel visa hur några frågor till databasen "Session" ser ut i SQL:
Lista över de som klarat alla obligatoriska prov.
WHERE Poäng > 2
HAR ANTAL(*) = (VÄLJ ANTAL(*)
WHERE R2.Group=R3.Group OCH fullständigt namna.fullständigt namn)
Här bestämmer den inbyggda frågan det totala antalet tentor som varje elev i elevens klass ska göra, och jämför detta antal med antalet tentor studenten har gjort.
En lista över de som skulle göra DB-provet, men som ännu inte har gjort det.
SELEST Fullständigt namn
VAR R2.Fpynna=R3.Grupp OCH Disciplin = "DB" OCH INTE FINNS
(VÄLJ fullständigt namn FROM Rl WHERE fullständigt namn=a.fullständigt namn OCH Disciplin = "DB")
Predikatet EXISTS (SubQuery) är sant när SubQuery-underfrågan inte är tom, det vill säga den innehåller minst en tupel, annars är EXISTS-predikatet falskt.
Predikatet INTE FINNS är sant endast när underfrågan är tom.
Lägg märke till hur INTE FINNS med en kapslad fråga gör att du kan undvika relationsskillnadsoperationen. Till exempel, att formulera en fråga med ordet "alla" kan göras som med ett dubbelnegativ. Låt oss titta på ett exempel på en databas som modellerar leverans enskilda delar enskilda leverantörer, representeras det av en SP-relation "Leverantörer-Delar" med schemat
SP (Supplier_number. Part_number) P (Art_number. Name)
Så här är svaret på förfrågan formulerat: "Hitta leverantörer som levererar alla delar."
VÄLJ DISTINKT VENDOR_NUMBER FRÅN SP SP1 DÄR INTE FINNS
(VÄLJ artikelnummer
FRÅN P DÄR INTE FINNS
(VÄLJ * FRÅN SP SP2
WHERE SP2.supplier_number=SP1.supplier_number OCH
sp2.part_number = P.part_number)):
Faktum är att vi omformulerade denna begäran på följande sätt: "Hitta leverantörer så att det inte finns någon del som de inte levererar." Det bör noteras att denna fråga också kan implementeras genom aggregerade funktioner med en underfråga:
VÄLJ DISTINKT Leverantörsnummer
GRUPPER EFTER Vendor_number
HAR KONTAKT DELnummer) =
(SELECT Count(part_number)
SQL92-standarden utökar jämförelseoperatorer till flera jämförelser med nyckelorden ANY och ALLA. Detta tillägg används när man jämför värdet för en specifik kolumn med datakolumnen som returneras av en underfråga.
Nyckelordet ANY placerat i ett jämförelsepredikat betyder att predikatet kommer att vara sant om jämförelsepredikatet är sant för minst ett värde från underfrågan. Nyckelordet ALL kräver att jämförelsepredikatet är sant när det jämförs med alla rader i underfrågan.
Låt oss till exempel hitta elever som klarade alla prov med betyget inte lägre än "bra". Vi arbetar med samma "Session"-databas, men lägger till ytterligare en relation R4, som kännetecknar leveransen av laborationer under terminen:
R1 = (namn, disciplin, betyg);
R2 = (fullständigt namn, grupp);
R 3 = (Grupper, Disciplin)
R4 = (Namn, Disciplin, Lab_work-nummer, Betyg);
Välj R1.Fullständigt namn Från R1 Där 4 > = Alla (Välj Rl.Rating
Där R1.Fullständigt namn = R11.Fullständigt namn)
Låt oss titta på ett annat exempel:
Välj studenter vars tentamensbetyg är minst ett betyg i det laborativa arbete de godkänt i denna disciplin:
Välj R1.Name
Från R1 Där R1.Rating >= ALLA (Välj R4.Rating
Där Rl.Disciplin = R4. Disciplin AND R1.Fullständigt namn = R4.Fullständigt namn)
Slå ihop tabeller.
En fråga kan kombinera data från en eller flera tabeller. Denna sammanfogning av tabeller kallas anslutning (bindande) tabeller. Skilja på inre och yttre anslutningar.
Inre En join är en join där resultatuppsättningen erhålls genom att lista de önskade fälten från olika tabeller efter ordet SELECT. I det här fallet måste tabellerna vara i en en-till-en relation.
Till exempel,
VÄLJ R.fam, R.birthday,A.Foto
FRÅN Rabotniki R, Advanced A
där tabellerna Rabotniki och Advanced innehåller huvud- och ytterligare information om företagets anställda. En-till-en kommunikation. Rabotniki-tabellen får aliaset R och Advanced-tabellen får aliaset A.
I föregående exempel föregås fältnamnet av tabellnamnet. Fält- och tabellnamn separeras med en punkt. Tabellnamnet måste anges om fältnamn upprepas för olika tabeller.
Om du tillämpar en inre koppling till en-till-många-tabeller kan resultatuppsättningen innehålla redundant information. Urvalskriterier används för att eliminera redundans.
Exempel 1. Givet en Sotrudniki-databas, bestående av två tabeller:
En inre kopplingsfråga för tabeller relaterade till en en-till-många-relation har formen
Antalet poster i den resulterande datamängden är lika med antalet poster i Sotrudniki-tabellen multiplicerat med antalet poster i Doljn-tabellen. Den resulterande datamängden ser ut som och innehåller redundant information:
Urvalskriterier används för att begränsa antalet poster i den resulterande datamängden. Begäran i det här fallet kommer att se ut
SELECT S_Fio,S_Birthday,D_Nazv FROM Sotrudniki, Doljn
WHERE S_Doljn=D_Code
Antalet poster i den resulterande datamängden kommer att vara lika med antalet poster i Sotrudniki-tabellen. sid
Exempel 2. Det krävs att du genererar en fråga för Sotrudniki-databasen som gör att du kan få en lista över anställda som har positionen "programmerare". Vi får
VÄLJ S_fio, S_födelsedag FRÅN sotrudniki, doljn
WHERE S_doljn=D_code och D_nazv="programmerare"
Självanslutning av tabeller är tillåten i SQL-frågor. I det här fallet får en tabell två alias.
Till exempel, För att hitta alla peers i Sotrudniki-tabellen kan du skriva en fråga:
VÄLJ s1.s_fio, s2.s_fio, s1.s_birthday
FRÅN Sotrudniki s1, Sotrudniki s2
VAR (EXTRACTER(ÅR
FROM s1.s_birthday)=EXTRACT(ÅR
FRÅN s2.s_birthday))
AND (s1.s_fio!=s2.s_fio) AND (s1.s_fio Det sista villkoret ordnar efternamn och eliminerar dubbelarbete av resultat. Med en inre koppling är alla tabeller vars fält anges i SQL-frågan lika. Det vill säga, för varje post i den första tabellen fanns det en motsvarande post i den andra tabellen. På extern förening (yttre sammanfogning) poster ingår i resultatuppsättningen oavsett om det finns ett motsvarande fält i den andra tabellen. Det finns tre typer av yttre fog. 1) VÄNSTER YTTRE JOIN … PÅ – den vänstra inkluderar i resultatet alla poster från det första bordet, även de som det inte finns någon match för i det andra. 2) HÖGER YTTRE JOIN … PÅ– höger, inkluderar i resultatet alla poster i den andra tabellen, även de för vilka det inte finns någon matchning i den första. 3) FULLSTÄNDIG YTTRE JOIN…PÅ– komplett, resultatet inkluderar en sammanslutning av poster från båda tabellerna, oavsett deras korrespondens. Med en yttre sammanfogning kan man prata om vilket bord som är det huvudsakliga. I det första fallet - vänster, i det andra - höger. Till exempel.
Låt det finnas efternamn i Sotrudniki-tabellen i Sotrudniki DB som har en position som inte är specificerad i Doljn-tabellen, och det finns positioner i Doljn-tabellen för vilka det inte finns något efternamn i Sotrudniki-tabellen. Sedan 1) VÄLJ * FRÅN Sotrudniki VÄNSTER YTTRE JOIN Doljn PÅ S_doljn=D_kod Resultatet kommer att inkludera alla Sotrudniki-fält och tabeller och Doljn-tabeller. Antalet rader motsvarar antalet poster i Sotrudniki-tabellen. I rader relaterade till poster som Doljn inte hittade en matchning för lämnas fälten i Doljn-tabellen tomma. 2) VÄLJ * FRÅN Sotrudniki HÖGER YTTRE JOIN Doljn PÅ S_doljn=D_kod Antalet rader motsvarar antalet poster i Doljn-tabellen. I rader relaterade till poster för vilka Sotrudniki inte hittade en matchning förblir fälten i Sotrudniki-tabellen tomma. 3) VÄLJ * FRÅN Sotrudniki FULL YTTRE JOIN Doljn ON Lade till rader relaterade till Sotrudniki-tabellen till rader relaterade till Doljn-tabellen, för vilka det inte finns någon matchning i Sotrudniki-tabellen. I en SQL-fråga kan du använda frågor kapslade i den första. Detta kan tillämpas på operatorer som returnerar aggregerade egenskaper såväl som på operatorer som returnerar flera värden. Till exempel, 1) Bestäm alla namne i tabellerna Sotrudniki och Sotrudniki1 som har samma struktur: VÄLJ * FRÅN Sotrudniki VAR S_fio IN (VÄLJ S_fio FRÅN Sotrudniki1) Den kapslade SELECT-satsen returnerar uppsättningen efternamn från Sotrudniki1-tabellen, och WHERE-satsen i huvud-SELECT-satsen väljer de poster i Sotrudniki-tabellen som finns i uppsättningen efternamn från Sotrudniki1-tabellen. 2) Mata in efternamnet på den yngsta medarbetaren från Sotrudniki-databasen: SELECT S_Fio, EXTRACT(YEAR FROM S_Birthday) WHERE EXTRACTION(YEAR FROM S_Birthday)= (SELECT max(EXTRACT(YEAR FROM S_Birthday)) FRÅN Sotrudniki) En kapslad SELECT-sats returnerar det maximala födelseåret, som används i WHERE-satsen i SELECT-huvudsatsen. 3) Utdata från Studentdatabasen alla betyg från en specifik student, till exempel Petrov: VÄLJ S_fam, P_nazv, E_mark FROM Examination, Predm, Studenter WHERE E_student=(VÄLJ S_kod FRÅN Studenter WHERE S_fam="Petrov") AND E_Predm=P_kod OCH E_Student=S_kod I den kapslade SELECT-konstruktionen bestäms koden för en elev som heter "Petrov", och de sista villkoren säkerställer att redundans elimineras under interna kopplingar av tabeller. Relaterade underfrågor. En inre fråga kan referera till en tabell vars namn anges i satsen FRÅN extern begäran. Sådan ansluten
Underfrågan exekveras en gång för varje rad i huvudfrågetabellen. Till exempel,
få information om de ämnen som provet hölls i vid ett visst datum, till exempel '01/14/2006': VÄLJ * FRÅN Predm PR VAR "01/14/2006" IN (VÄLJ E_datum FRÅN examen WHERE PR.P_code=E_predm) Samma problem kan lösas med tabellanslutningsoperationen: VÄLJ DISTINCT P_nazv FRÅN Predm, Examination WHERE P_code=E_predm OCH E_date= "01/14/2006" Exempel.
Visa efternamnet(en) på den elev som fick ett betyg över genomsnittet på tentamen SELECT DISTINCT S_fam FROM Students, Examination E_mark>(VÄLJ AVG(E_mark) FRÅN Examination) AND S_code=E_student Du kan använda nyckelord i WHERE-satsen när du arbetar med uppsättningar poster ALLT Och NÅGRA. ALLT- villkoret är uppfyllt för alla register, NÅGRA- villkoret är uppfyllt för minst en post. Till exempel,
1) visa efternamnen på anställda från Sotrudniki-tabellen som inte är äldre än någon anställd i Sotrudniki1-tabellen: WHERE S_birthday>= ALLA (VÄLJ S_födelsedag FRÅN Sotrudniki1) 2) visa efternamnen på anställda från Sotrudniki-tabellen som är yngre än minst en anställd i Sotrudniki1-tabellen: VÄLJ S_fio,S_födelsedag FRÅN Sotrudniki WHERE S_birthday> NÅGON (VÄLJ S_födelsedag FRÅN Sotrudniki1) Du kan använda nyckelordet i kapslade SELECT-satser EXISTERAR, vilket innebär att endast de poster väljs för vilka underfrågan returnerar ett eller flera värden. Till exempel,
VÄLJ S_fio,S_födelsedag FRÅN Sotrudniki S1 VAR FINNS (VÄLJ S_fio,S_födelsedag FRÅN Sotrudniki S2 VAR (S1.S_birthday=S2.S_birthday) OCH (S1.S_code!=S2.S_code)) Få en lista över anställda som har minst en kamrat.