Infoga, ta bort, uppdatera poster i databasen. Hur man skickar en fråga till en databas med VBA Access Skapa sql-frågor i åtkomstnamn

Laboratoriearbete nr 1

SQL: DATAEXTRAKT - kommandoVÄLJ

Målet med arbetet:

• bli bekant med SQL-satser;
• lär dig hur du skapar enkla SQL-frågor i Access med hjälp av kommandot SELECT;

· användning av operatorer IN, MELLAN, GILLA, ÄR NULL.

Träning№1. Skapa en fråga för att välja i SQL-läge alla värden i fälten FÖRNAMN och EFTERNAMN från tabellen STUDENTER.

VÄLJ FÖRNAMN, EFTERNAMN

FRÅN ELEVER;

Träning№2 . Skapa en fråga för att välja i SQL-läge alla kolumner i tabellen STUDENTS.

VÄLJ *

FRÅN ELEVER;

Uppgift nr 3. Skapa en fråga för att välja i SQL-läge namnen på städer där eleverna bor, information om vilka finns i tabellen PERSONUPPGIFTER.

VÄLJ DISTINKT STAD

FRÅN [PERSONUPPGIFTER];

Uppgift nr 4. Skapa en urvalsfråga i SQL-läge som hämtar namnen på alla elever med efternamnet Ivanov, information om vilken finns i STUDENTS-tabellen.

VÄLJ EFTERNAMN, FÖRNAMN

FRÅN ELEVER

WHERE LAST NAME="Ivanov";

Uppgift nr 5. Skapa en urvalsfråga i SQL-läge för att få för- och efternamn på studenter som studerar i UIT-22-gruppen på en budgetform av utbildning.

VÄLJ EFTERNAMN, FÖRNAMN

FRÅN ELEVER

WHERE GROUP="UIT-22" OCH BUDGET=true;

Uppgift nr 6. Skapa en fråga i SQL-läge. för ett urval från EXAMINATIONStabellen, information om elever som endast har betyg 4 och 5.

VÄLJ *

FRÅN [FÖRÄNDRAEXAMEN]

VARKVALITETIN(4,5);

Uppgift nr 7. Skapa ett zanpoc- och SQL-läge för att välja information om elever som har provbetyget 3 i ämnet IOSU.

VÄLJ *

FRÅN [FÖRÄNDRAEXAMEN]

VARARTIKEL=" IOSU"OchKVALITETInte i (4,5);

Uppgift nr 8. Skapa en fråga i SQL-läge för att välja poster för objekt vars timmar är mellan 100 och 130.

VÄLJ *

FRÅNFÖREMÅL

VARKOLLA PÅMELLAN 100 OCH 130;

Uppgift nr 9. Skapa en fråga i SQL-läge för att välja information från STUDENTS-tabellen om elever vars efternamn börjar, till exempel med bokstaven "C".

VÄLJ *

FRÅNSTUDENTER

VAREFTERNAMNTYCKA OM"MED*";

Slutsats: Under laboratoriearbete bekantade sig med SQL-instruktioner, lärde sig att skapa enkla SQL-frågor i Access med hjälp av kommandot SELECT med IN, BETWEEN, LIKE-operatorerna.

Denna lektion är tillägnad SQL-frågor till databasen på VBA-åtkomst. Vi kommer att titta på hur INSERT, UPDATE, DELETE-frågor görs till databasen i VBA, och vi kommer också att lära oss hur man får ett specifikt värde från en SELECT-fråga.

De som programmerar in VBA-åtkomst när du arbetar med databasen SQL-server, mycket ofta ställs de inför en så enkel och nödvändig uppgift som att skicka en SQL-fråga till en databas, vare sig det är INSERT, UPDATE eller en enkel SQL SELECT-fråga. Och eftersom vi är novisa programmerare borde vi också kunna göra detta, så idag kommer vi att göra just det.

Vi har redan berört ämnet att hämta data från en SQL-server, där vi skrev kod i VBA för att erhålla denna data, till exempel i artikeln om att ladda upp data till en textfil från MSSql 2008, eller så berörde vi det också en lite i materialet Ladda upp data från Access till en Word- och Excel-mall, men på ett eller annat sätt har vi tittat på detta ytligt och idag föreslår jag att vi ska prata om detta lite mer detaljerat.

Notera! Alla exemplen nedan anses använda Access 2003 ADP-projektet och MSSql 2008-databasen. Om du inte vet vad ett ADP-projekt är, så tittade vi på detta i materialet Hur man skapar och konfigurerar ett Access ADP-projekt

Källdata för exempel

Låt oss säga att vi har en tabell test_table, som kommer att innehålla numren och namnen på årets månader (förfrågningar exekveras med Management Studio)

SKAPA TABELL .( INTE NULL, (50) NULL) PÅ GÅR

Som jag redan har sagt kommer vi att använda ett ADP-projekt konfigurerat för att fungera med MS SQL 2008, där jag skapade ett testformulär och lade till en startknapp med en signatur "Springa", som vi kommer att behöva för att testa vår kod, dvs. Vi kommer att skriva all kod i händelsehanteraren " Knapptryckning».

Frågor till databasen INSERT, UPDATE, DELETE i VBA

För att inte försena för länge, låt oss börja direkt, låt oss säga att vi måste lägga till en rad i vår testtabell ( kod kommenterade)/

Private Sub start_Click() "Deklarera en variabel för att lagra frågesträngen Dim sql_query As String "Skriv in frågan vi behöver i den sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "juni")" "Execute it DoCmd. RunSQL sql_query End Sub

I det här fallet exekveras frågan med de aktuella databasanslutningsparametrarna. Vi kan kontrollera om uppgifterna har lagts till eller inte.

Som du kan se har uppgifterna infogats.

För att radera en rad skriver vi följande kod.

Private Sub start_Click() "Deklarera en variabel för att lagra frågesträngen Dim sql_query As String "Skriv en raderingsfråga i den sql_query = "DELETE test_table WHERE id = 6" "Kör den DoCmd.RunSQL sql_query End Sub

Om vi ​​kontrollerar kommer vi att se att den önskade raden har raderats.

För att uppdatera data, skriv till variabeln sql_query uppdateringsbegäran, jag hoppas att innebörden är klar.

SELECT-fråga till en databas i VBA

Här är saker lite mer intressanta än med andra SQL-konstruktioner.

Först, låt oss säga att vi behöver få all data från tabellen, och till exempel kommer vi att bearbeta den och visa den i ett meddelande, och du kan naturligtvis använda den för andra ändamål, för detta skriver vi följande koda

Private Sub start_Click() "Deklarera variabler "För en uppsättning poster från databasen Dim RS As ADODB.Recordset "Frågesträng Dim sql_query As String "Sträng för att visa sammanfattande data i ett meddelande Dim str As String "Skapa ett nytt objekt för poster set RS = New ADODB .Recordset "Query line sql_query = "SELECT id, name_mon FROM test_table" "Kör frågan med de aktuella projektanslutningsinställningarna RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Loop through the records While Not ( RS.EOF) "Fyll variabeln för att visa meddelandet str = str & RS.Fields("id") & "-" & RS.Fields("name_mon") & vbnewline "gå till nästa post RS.MoveNext Wend " Skriv ut meddelandet msgbox str End Sub

Här använder vi redan VBA Access-loopar för att iterera genom alla värden i vårt rekorduppsättning.

Men ganska ofta är det nödvändigt att inte få alla värden från en uppsättning poster, utan bara en, till exempel månadens namn genom dess kod. Och för att göra detta är det ganska dyrt att använda en loop, så vi kan helt enkelt skriva en fråga som bara returnerar ett värde och komma åt det, till exempel får vi månadens namn med kod 5

Private Sub start_Click() "Deklarera variabler" För en uppsättning poster från databasen Dim RS As ADODB.Recordset "Frågesträng Dim sql_query As String "String för att visa slutvärdet Dim str As String "Skapa ett nytt objekt för postuppsättningen RS = Ny ADODB.Recordset "Query line sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "Kör frågan med de aktuella projektanslutningsinställningarna RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Hämta vårt värde str = RS. Fields(0) msgbox str End Sub

För universalitet, här har vi redan adresserat inte med cellnamnet, utan med dess index, d.v.s. 0, och detta är det allra första värdet i Rekorduppsättning, till slut fick vi värdet "Maj".

Som du kan se är allt ganska enkelt. Om du ofta behöver få ett specifikt värde från databasen ( som i det sista exemplet), så rekommenderar jag att mata ut all kod till en separat funktion (Hur man skriver en funktion i VBA Access 2003) med en ingångsparameter, till exempel månadskoden ( om vi betraktar vårt exempel) och helt enkelt, där det är nödvändigt att visa detta värde, ring funktionen vi behöver med den önskade parametern och det är det, genom att göra detta kommer vi att avsevärt minska VBA-koden och förbättra uppfattningen av vårt program.

Det är allt för idag. Lycka till!

Beskrivning av utbildningsprojektet "Shop"

Tabelllänkdiagram

Beskrivning av tabeller

m_category - produktkategorier

m_inkomst - varumottagning

m_outcome - konsumtion av varor

m_product - katalog, produktbeskrivningar

m_supplier - katalog; leverantörsinformation

m_unit - katalog; enheter

För att praktiskt testa exemplen som ges i detta utbildningsmaterial måste du ha följande programvara:

Microsoft Access 2003 eller nyare.

SQL-fråga i MS Access. Start

För att se innehållet i tabellen, dubbelklicka på tabellnamnet i den vänstra panelen:

Klicka på för att växla till redigeringsläge för tabellfält topppanel välj designläge:

För att visa resultatet av en SQL-fråga, dubbelklicka på frågenamnet i den vänstra rutan:

För att växla till redigeringsläge för SQL-frågor, välj SQL-läge i den övre panelen:

SQL-fråga. Exempel i MS Access. VÄLJ: 1-10

I en SQL-fråga används SELECT-satsen för att välja från databastabeller.

SQL-fråga Q001. Exempel på SQL-fråga för att få endast de obligatoriska fälten i önskad sekvens:

SELECT dt, product_id, summa

FRÅN m_inkomst;

SQL-fråga Q002. I det här exemplet på SQL-frågan används asterisken (*) för att lista alla kolumner i tabellen m_product, med andra ord för att få alla fält i m_product-relationen:

VÄLJ *
FRÅN m_produkt;

BegäranSQL Q003. DISTINCT-satsen används för att eliminera dubbla poster och få flera unika poster:

VÄLJ DISTINCT produkt-id

FRÅN m_inkomst;

SQL-fråga Q004. ORDER BY-satsen används för att sortera (ordna) poster efter värdena i ett specifikt fält. Fältnamnet anges efter ORDER BY-satsen:

VÄLJ *
FRÅN m_inkomst

BESTÄLL EFTER pris;

SQL-fråga Q005. ASC-satsen används som ett komplement till ORDER BY-satsen och tjänar till att specificera stigande sortering. DESC-satsen används utöver ORDER BY-satsen och används för att specificera fallande sortering. I det fall där varken ASC eller DESC anges, antas närvaron av ASC (standard):

VÄLJ *
FRÅN m_inkomst

BESTÄLL AV dt DESC , pris;

SQL-fråga Q006. För att välja nödvändiga poster från tabellen används olika logiska uttryck som uttrycker urvalsvillkoret. Det booleska uttrycket visas efter WHERE-satsen. Ett exempel på att hämta alla poster från tabellen m_inkomst där beloppsvärdet är större än 200:

VÄLJ *
FRÅN m_inkomst

VAR belopp>200;

SQL-fråga Q007. För uttryck svåra förhållanden använd de logiska operatorerna AND (konjunktion), OR (disjunktion) och NOT (logisk negation). Ett exempel på att få från tabellen m_outcome alla poster för vilka beloppsvärdet är 20 och prisvärdet är större än eller lika med 10:

Pris

FRÅN m_outcome
WHERE summa=20 OCH pris>=10;

SQL-fråga Q008. För att sammanfoga data från två eller flera tabeller, använd INNER JOIN, LEFT JOIN, RIGHT JOIN instruktionerna. Följande exempel hämtar fälten dt, product_id, amount, price från tabellen m_inkomst och titelfältet från tabellen m_product. M_income-tabellposten kopplas till m_product-tabellposten när värdet av m_income.product_id är lika med värdet av m_product.id:

PÅ m_income.product_id=m_product.id;

SQL-fråga Q009. Det finns två saker att notera i den här SQL-frågan: 1) texten du letar efter är innesluten i enstaka citat("); 2) datumet anges i formatet #Månad/Dag/År#, vilket är sant för MS Access. I andra system kan formatet för att skriva datum vara annorlunda. Ett exempel på att visa information om kvittot mjölk den 12 juni 2011. Observera datumformat #6/12/2011#:

SELECT dt, product_id, title, summa, price

FRÅN m_inkomst INNER JOIN m_product

WHERE title="Mjölk" And dt=#6/12/2011#; !}

SQL-fråga Q010. BETWEEN-instruktionen används för att testa om ett värde tillhör ett visst område. Ett exempel på SQL-fråga som visar information om produkter som tagits emot mellan 1 juni och 30 juni 2011:

VÄLJ *
FRÅN m_inkomst INNER JOIN m_product

PÅ m_income.product_id=m_product.id
VAR dt MELLAN #6/1/2011# Och #6/30/2011#;

SQL-fråga. Exempel i MS Access. VÄLJ: 11-20

En SQL-fråga kan kapslas i en annan. En underfråga är inget annat än en fråga i en fråga. Vanligtvis används en underfråga i WHERE-satsen. Men det finns andra sätt att använda underfrågor.

Fråga Q011. Information om produkter från tabellen m_product visas, vars koder också finns i tabellen m_inkomst:

VÄLJ *
FRÅN m_produkt

WHERE id IN (VÄLJ product_id FROM m_income);

Fråga Q012. En lista över produkter från tabellen m_product visas, vars koder inte finns i tabellen m_outcome:

VÄLJ *
FRÅN m_produkt

WHERE id NOT IN (SELECT product_id FROM m_outcome);

Begär Q013. Den här SQL-frågan visar en unik lista med produktkoder och namn som finns i tabellen m_inkomst men inte i tabellen m_utfall:

SELECT DISTINCT product_id, title

FRÅN m_inkomst INNER JOIN m_product
PÅ m_income.product_id=m_product.id
WHERE product_id NOT IN (SELECT product_id FROM m_outcome);

Fråga Q014. En unik lista över kategorier vars namn börjar med bokstaven M visas från tabellen m_category:

VÄLJ DISTINKT titel

FRÅN m_produkt
WHERE titel SOM "M*";

Fråga Q015. Ett exempel på att utföra aritmetiska operationer på fält i en fråga och byta namn på fält i en fråga (alias). Det här exemplet beräknar kostnad = kvantitet*pris och vinst för varje postutgiftspost, förutsatt att vinsten är 7 procent av försäljningen:

belopp*pris/100*7 AS-vinst
FROM m_outcome;

Fråga Q016. Genom att analysera och förenkla aritmetiska operationer kan du öka hastigheten för utförande av frågor:

SELECT dt, product_id, amount, price, amount*price AS outcome_sum,

utfallssumma*0,07 AS vinst
FROM m_outcome;

Begär Q017. Du kan använda INNER JOIN-satsen för att sammanfoga data från flera tabeller. I följande exempel, beroende på värdet av ctgry_id, matchas varje post i tabellen m_inkomst med namnet på kategorin från tabellen m_category som produkten tillhör:

VÄLJ c.titel, b.titel, dt, summa, pris, summa*pris AS inkomst_summa

FROM (m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id)
INNER JOIN m_category AS c ON b.ctgry_id=c.id
BESTÄLLNING AV c.title, b.title;

Begär Q018. Funktioner som SUMMA - summa, COUNT - kvantitet, AVG - aritmetiskt medelvärde, MAX - maxvärde, MIN - minimivärde kallas aggregerade funktioner. De accepterar många värden och efter bearbetning returnerar de ett enda värde. Ett exempel på att beräkna summan av produkten av belopps- och prisfälten med hjälp av aggregatets SUMMA:

VÄLJ SUMMA(belopp*pris) SOM Total_Summa

FRÅN m_inkomst;

Fråga Q019. Ett exempel på användning av flera aggregerade funktioner:

SELECT Sum(amount) AS Amount_Sum, AVG(amount) AS Amount_AVG,

MAX(amount) AS Amount_Max, Min(amount) AS Amount_Min,
Count(*) AS Total_Number
FRÅN m_inkomst;

Begär Q020. I det här exemplet beräknas mängden för alla varor med kod 1, aktiverade i juni 2011:

VÄLJ Summa(belopp*pris) SOM inkomst_summa

FRÅN m_inkomst
WHERE product_id=1 OCH dt MELLAN #6/1/2011# OCH #6/30/2011#;.

Fråga Q021. Följande SQL-fråga beräknar mängden försäljning av varor med kod 4 eller 6:

VÄLJ Summa(belopp*pris) som utfallssumma

FRÅN m_outcome
WHERE product_id=4 ELLER product_id=6;

Fråga Q022. Det är beräknat hur mycket varor med kod 4 eller 6 som såldes den 12 juni 2011:

SELECT Sum(belopp*pris) AS outcome_sum

FRÅN m_outcome
WHERE (product_id=4 OR product_id=6) OCH dt=#6/12/2011#;

Fråga Q023. Uppgiften är denna. Beräkna den totala mängden varor i kategorin "Bageriprodukter" som aktiverades.

För att lösa detta problem måste du arbeta med tre tabeller: m_inkomst, m_produkt och m_kategori, eftersom:

- kvantiteten och priset på aktiverade varor lagras i m_inkomsttabellen;
- kategorikoden för varje produkt lagras i tabellen m_product;
- namnet på titelkategorin lagras i tabellen m_category.

För att lösa detta problem kommer vi att använda följande algoritm:

- bestämma kategorikoden "Bageriprodukter" från tabellen m_category med hjälp av en underfråga;
- sammankoppling av tabellerna m_inkomst och m_product för att bestämma kategorin för varje köpt produkt;
- beräkning av kvittobeloppet (= kvantitet*pris) för varor vars kategorikod är lika med koden definierad av ovanstående underfråga.
VÄLJ
FROM m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id
WHERE ctgry_id = (SELECT id FROM m_category WHERE title="Bageriprodukter"); !}

Fråga Q024. Vi kommer att lösa problemet med att beräkna den totala mängden aktiverade varor i kategorin "Bageriprodukter" med hjälp av följande algoritm:
- För varje post i tabellen m_inkomst, beroende på värdet på dess product_id, från tabellen m_category, matcha namnet på kategorin;
- välj poster för vilka kategorin är "bageriprodukter";
- beräkna mängden kvitto = kvantitet*pris.

FROM (m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id)

WHERE c.title="Bageriprodukter"; !}

Fråga Q025. Detta exempel beräknar hur många varor som förbrukades:

SELECT COUNT(product_id) AS product_cnt

FROM (SELECT DISTINCT product_id FROM m_outcome) AS t;

Fråga Q026. GROUP BY-satsen används för att gruppera poster. Vanligtvis grupperas poster efter värdet av ett eller flera fält, och någon sammanlagd operation tillämpas på varje grupp. Till exempel genererar följande fråga en rapport om försäljning av varor. Det vill säga, en tabell genereras som innehåller namnen på varorna och det belopp som de såldes för:

VÄLJ titel, SUM(belopp*pris) SOM utfallssumma

FROM m_product AS a INNER JOIN m_outcome AS b
PÅ a.id=b.product_id
GRUPPER EFTER titel;

Fråga Q027. Försäljningsrapport per kategori. Det vill säga att en tabell genereras som innehåller namnen på produktkategorier, det totala beloppet för vilka produkter av dessa kategorier såldes och det genomsnittliga försäljningsbeloppet. Funktionen ROUND används för att avrunda medelvärdet till närmaste hundradel (den andra siffran efter decimalavgränsaren):

SELECT c.title, SUM(amount*price) AS outcome_sum,

ROUND(AVG(belopp*pris),2) AS outcome_sum_gen
FROM (m_product AS a INNER JOIN m_outcome AS b ON a.id=b.product_id)
INNER JOIN m_category AS c PÅ a.ctgry_id=c.id
GROUP BY c.title;

Fråga Q028. Det totala och genomsnittliga antalet kvitton beräknas för varje produkt och visar information om produkter vars totala kvitton är minst 500:

SELECT product_id, SUM(amount) AS amount_sum,

Round(Avg(amount),2) AS amount_avg
FRÅN m_inkomst
GROUP BY product_id
HAR Sum(amount)>=500;

Fråga Q029. Denna fråga beräknar för varje produkt mängden och genomsnittet av dess inbetalningar som gjordes under andra kvartalet 2011. Om det totala beloppet på produktkvittot är minst 1000, visas information om denna produkt:

SELECT titel, SUM(belopp*pris) SOM inkomst_summa

FROM m_income a INNER JOIN m_product b ON a.product_id=b.id
VAR dt MELLAN #4/1/2011# OCH #6/30/2011#
GRUPP EFTER titel
HAR SUMMA(belopp*pris)>=1000;

Fråga Q030. I vissa fall måste du matcha varje post i en tabell med varje post i en annan tabell; som kallas den kartesiska produkten. Tabellen som blir resultatet av en sådan koppling kallas Descartes tabell. Till exempel, om någon tabell A har 100 poster och tabell B har 15 poster, kommer deras Descartes-tabell att bestå av 100*15=150 poster. Följande fråga förenar varje post i tabellen m_inkomst med varje post i tabellen m_outcome:
FRÅN m_inkomst, m_utfall;

Fråga Q031. Ett exempel på att gruppera poster efter två fält. Följande SQL-fråga beräknar för varje leverantör mängden och kvantiteten varor som tas emot från honom:

SUM(belopp*pris) SOM inkomst_summa

Fråga Q032. Ett exempel på att gruppera poster efter två fält. Följande fråga beräknar för varje leverantör mängden och kvantiteten av deras produkter som säljs av oss:

VÄLJ leverantörs-id, produkt-id, SUM(belopp) AS summa_summa,

GROUP BY supplier_id, product_id;

Fråga Q033. I det här exemplet används de två frågorna ovan (q031 och q032) som underfrågor. Resultaten av dessa frågor med metoden LEFT JOIN kombineras till en rapport. Följande fråga visar en rapport om kvantiteten och mängden produkter som tas emot och sålts för varje leverantör. Observera att om någon produkt redan har mottagits, men ännu inte sålts, kommer cellen utfallsumma för denna post att vara tom. att denna fråga bara är ett exempel på att använda relativt komplexa frågor som en underfråga. Prestandan för denna SQL-fråga med en stor mängd data är tveksam:

VÄLJ *
FRÅN

SUM(belopp*pris) SOM inkomst_summa

PÅ a.product_id=b.id GROUP BY supplier_id, product_id) AS a
VÄNSTER GÅ MED
(VÄLJ leverantörs-id, produkt-id, SUM(belopp) AS summa_summa,
SUM(belopp*pris) SOM utfall_summa
FROM m_outcome AS a INNER JOIN m_product AS b
PÅ a.product_id=b.id GROUP BY supplier_id, product_id) AS b
PÅ (a.product_id=b.product_id) AND (a.supplier_id=b.supplier_id);

Fråga Q034. I det här exemplet används de två frågorna ovan (q031 och q032) som underfrågor. Resultaten av dessa frågor med RIGTH JOIN-metoden kombineras till en rapport. Följande fråga visar en rapport om betalningsbeloppet för varje kund enligt de betalningssystem han använde och mängden investeringar han gjorde. Följande fråga visar en rapport om kvantiteten och mängden produkter som tas emot och sålts för varje leverantör. Observera att om någon produkt redan har sålts, men ännu inte har anlänt, kommer cellen inkomstsumma för denna post att vara tom. Förekomsten av sådana tomma celler är en indikator på ett fel i försäljningsredovisningen, eftersom innan en försäljning är det först nödvändigt att motsvarande produkt kommer fram:

VÄLJ *
FRÅN

(VÄLJ leverantörs-id, produkt-id, SUM(belopp) AS summa_summa,
SUM(belopp*pris) SOM inkomst_summa
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS a
HÖGER GÅ MED
(VÄLJ leverantörs-id, produkt-id, SUM(belopp) AS summa_summa,
SUM(belopp*pris) SOM utfall_summa
FROM m_outcome AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS b
PÅ (a.supplier_id=b.supplier_id) AND (a.product_id=b.product_id);

Fråga Q035. En rapport visas som visar mängden intäkter och kostnader per produkt. För att göra detta skapas en lista över produkter enligt tabellerna m_income och m_outcome, sedan för varje produkt från denna lista beräknas summan av dess inkomst enligt m_income-tabellen och beloppet av dess utgifter enligt m_outcome-tabellen:

SELECT product_id, SUM(in_amount) AS income_amount,

SUM(out_amount) AS outcome_amount
FRÅN
(SELECT product_id, summa AS in_amount, 0 AS out_amount
FRÅN m_inkomst
UNION ALLA
SELECT product_id, 0 AS in_amount, summa AS out_amount
FROM m_outcome) AS t
GROUP BY product_id;

Fråga Q036. Funktionen FINNS returnerar TRUE om uppsättningen som skickas till den innehåller element. Funktionen FINNS returnerar FALSK om den mängd som skickas till den är tom, det vill säga den innehåller inga element. Följande fråga visar produktkoderna som finns i både m_income- och m_outcome-tabellerna:

VÄLJ DISTINCT produkt-id

FRÅN m_inkomst AS a
WHERE EXISTS(SELECT product_id FROM m_outcome AS b

Fråga Q037. Produktkoder som finns i både m_income- och m_outcome-tabellerna visas:

VÄLJ DISTINCT produkt-id

FRÅN m_inkomst AS a
WHERE product_id IN (SELECT product_id FROM m_outcome)

Fråga Q038. Produktkoder visas som finns i tabellen m_inkomst, men som inte finns i tabellen m_utfall:

VÄLJ DISTINCT produkt-id

FRÅN m_inkomst AS a
WHERE NOT EXISTS(SELECT product_id FROM m_outcome AS b
WHERE b.product_id=a.product_id);

Fråga Q039. En lista över produkter med det maximala försäljningsbeloppet visas. Algoritmen är som följer. För varje produkt beräknas storleken på dess försäljning. Därefter bestäms det högsta av dessa belopp. Sedan, för varje produkt, beräknas summan av dess försäljning igen, och koden och försäljningssumman för varor vars försäljningssumma är lika med maximum visas:

VÄLJ produkt-id, SUM(belopp*pris) SOM summa_summa

FRÅN m_outcome
GROUP BY product_id
HA SUMMA(belopp*pris) = (VÄLJ MAX(s_belopp)
FROM (VÄLJ SUMMA(belopp*pris) AS s_amount FROM m_outcome GROUP BY product_id));

Fråga Q040. Reserverat ord IIF ( villkorlig operatör) används för att utvärdera ett booleskt uttryck och utföra en åtgärd beroende på resultatet (TRUE eller FALSE). I följande exempel anses varuleveransen vara "liten" om kvantiteten är mindre än 500. I annat fall, det vill säga kvittokvantiteten är större än eller lika med 500, anses leveransen vara "stor":

SELECT dt, product_id, amount,

IIF(belopp FRÅN m_inkomst;

SQL-fråga Q041. I det fall där IIF-operatören används flera gånger är det bekvämare att ersätta den med SWITCH-operatören. SWITCH-operatorn (multipelvalsoperator) används för att utvärdera ett logiskt uttryck och utföra en åtgärd beroende på resultatet. I följande exempel anses det levererade partiet vara "litet" om mängden varor i partiet är mindre än 500. Annars, det vill säga om mängden varor är större än eller lika med 500, anses partiet vara "stort ":

SELECT dt, product_id, amount,

SWITCH(mängd =500,"stor") AS-märke
FRÅN m_inkomst;

Fråga Q042. I nästa begäran, om mängden varor i det mottagna partiet är mindre än 300, anses partiet vara "litet". Annars, det vill säga om villkorsbeloppet SELECT dt, product_id, amount,
IIF(belopp IIF(belopp FRÅN m_inkomst;

SQL-fråga Q043. I nästa begäran, om mängden varor i det mottagna partiet är mindre än 300, anses partiet vara "litet". Annars, det vill säga om villkorsbeloppet SELECT dt, product_id, amount,
SWITCH(belopp belopp belopp>=1000,"stor") AS-märke
FRÅN m_inkomst;

SQL-fråga Q044. I följande fråga delas försäljningen in i tre grupper: liten (upp till 150), medium (från 150 till 300), stor (300 eller fler). Därefter beräknas det totala beloppet för varje grupp:

SELECT Category, SUM(outcome_sum) AS Ctgry_Total

FRÅN (VÄLJ belopp*pris SOM utfallssumma,
IIf(belopp*pris IIf(belopp*pris FRÅN m_utfall) AS t
GRUPPER EFTER Kategori;

SQL-fråga Q045. Funktionen DateAdd används för att lägga till dagar, månader eller år till ett givet datum och få ett nytt datum. Nästa begäran:
1) lägger till 30 dagar till datumet från dt-fältet och visar det nya datumet i dt_plus_30d-fältet;
2) lägger till 1 månad till datumet från dt-fältet och visar det nya datumet i dt_plus_1m-fältet:

VÄLJ dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m

FRÅN m_inkomst;

SQL-fråga Q046. Funktionen DateDiff är utformad för att beräkna skillnaden mellan två datum i olika enheter (dagar, månader eller år). Följande fråga beräknar skillnaden mellan datumet i dt-fältet och det aktuella datumet i dagar, månader och år:

SELECT dt, DateDiff("d",dt,Date()) AS last_day,

DateDiff("m",dt,Date()) AS last_months,
DateDiff("åååå",dt,Datum()) AS last_years
FRÅN m_inkomst;

SQL-fråga Q047. Antalet dagar från datumet för mottagandet av varorna (tabell m_inkomst) till aktuellt datum beräknas med hjälp av DateDiff-funktionen och utgångsdatumet jämförs (tabell m_product):

DateDiff("d",dt,Date()) AS last_days
FROM m_income AS a INNER JOIN m_product AS b
PÅ a.product_id=b.id;

SQL-fråga Q048. Antalet dagar från dagen för mottagandet av varorna till det aktuella datumet beräknas, sedan kontrolleras om denna kvantitet överstiger utgångsdatumet:

SELECT a.id, product_id, dt, lifedays,

DateDiff("d",dt,Date()) AS last_days, IIf(last_days>lifedays,"Yes","No") AS date_expire
FROM m_inkomst a INNER JOIN m_product b
PÅ a.product_id=b.id;

SQL-fråga Q049. Antalet månader från dagen för mottagandet av varorna till det aktuella datumet beräknas. Kolumn month_last1 beräknar det absoluta antalet månader, kolumn month_last2 beräknar antalet hela månader:

SELECT dt, DateDiff("m",dt,Date()) AS month_last1,

DateDiff("m",dt,Date())-iif(day(dt)>day(date()),1,0) AS month_last2
FRÅN m_inkomst;

SQL-fråga Q050. En kvartalsrapport visas över kvantiteten och mängden varor som köpts för 2011:

SELECT kvartal, SUM(outcome_sum) AS Totalt

FRÅN (VÄLJ belopp*pris AS utfall_summa, månad(dt) AS m,
SWITCH(m =10,4) AS kvartal
FRÅN m_inkomst VAR år(dt)=2011) SOM t
GRUPPER EFTER kvartal;

Fråga Q051. Följande fråga hjälper till att ta reda på om användare kunde ange information om konsumtion av varor i systemet i en mängd som är större än mängden mottagna varor:

SELECT product_id, SUM(in_sum) AS income_sum, SUM(out_sum) AS outcome_sum

FRÅN (VÄLJ produkt-id, belopp*pris som in_summa, 0 som ut_summa
från m_inkomst
UNION ALLA
VÄLJ produkt-id, 0 som in_summa, belopp*pris som ut_summa
från m_utfall) AS t
GROUP BY product_id
HA SUMMA(i_summa)
Fråga Q052. Numreringen av rader som returneras av en fråga implementeras på olika sätt. Du kan t.ex. numrera om raderna i en rapport som upprättats i MS Access med hjälp av själva MS Access. Du kan också numrera om med hjälp av programmeringsspråk, till exempel VBA eller PHP. Men ibland måste detta göras i själva SQL-frågan. Så följande fråga kommer att numrera raderna i tabellen m_inkomst enligt stigande ordning för ID-fältvärdena:

VÄLJ ANTAL(*) som N, b.id, b.product_id, b.amount, b.price

FROM m_income a INNER JOIN m_income b ON a.id GROUP BY b.id, b.product_id, b.amount, b.price;

Fråga Q053. De fem bästa produkterna bland produkterna efter försäljningsbelopp visas. De första fem posterna skrivs ut med TOP-instruktionen:

VÄLJ TOP 5, produkt-id, summa(belopp*pris) SOM summa

FRÅN m_outcome
GROUP BY product_id
BESTÄLL EFTER summa(belopp*pris) DESC;

Fråga Q054. De fem bästa produkterna bland produkterna efter försäljningsbelopp visas, och raderna numreras som ett resultat:

VÄLJ ANTAL(*) SOM N, b.product_id, b.summa

FRÅN


FROM m_outcome GROUP BY product_id) AS a
INRE KOPPLING
(VÄLJ produkt-id, summa(belopp*pris) SOM summa,
summa*10000000+produkt-id AS-id
FROM m_outcome GROUP BY product_id) AS b
PÅ a.id>=b.id
GROUP BY b.product_id, b.summa
ATT HA ANTAL(*)ORDNING EFTER ANTAL(*);

Fråga Q055. Följande SQL-fråga visar användningen av de matematiska funktionerna COS, SIN, TAN, SQRT, ^ och ABS i MS Access SQL:

VÄLJ (välj count(*) från m_inkomst) som N, 3,1415926 som pi, k,

2*pi*(k-1)/N som x, COS(x) som COS_, SIN(x) som SIN_, TAN(x) som TAN_,
SQR(x) som SQRT_, x^3 som "x^3", ABS(x) som ABS_
FRÅN (VÄLJ ANTAL(*) SOM k
FROM m_income AS a INNER JOIN m_income AS b PÅ a.idGROUP BY b.id) t;

SQL-fråga. Exempel i MS Access. UPPDATERING: 1-10

Fråga U001. Följande SQL-ändringsfråga ökar priserna på varor med kod 3 i tabellen m_inkomst med 10 %:

UPPDATERA m_inkomst SET pris = pris*1.1

WHERE product_id=3;

Begär U002. Följande SQL-uppdateringsfråga ökar kvantiteten av alla produkter i tabellen m_income med 22 enheter vars namn börjar med ordet "Oil":

UPPDATERA m_inkomst SET belopp = belopp+22

WHERE product_id IN (SELECT id FROM m_product WHERE title LIKE "Oil*");

Begär U003. Följande SQL-fråga för en ändring i tabellen m_outcome minskar priserna på alla varor som tillverkas av Sladkoe LLC med 2 procent:

UPPDATERA m_outcome SET pris = pris*0,98

WHERE product_id IN
(VÄLJ a.id FROM m_product a INNER JOIN m_supplier b
PÅ a.supplier_id=b.id WHERE b.title="LLC"Сладкое"");. !}

Infoga, ta bort, uppdatera poster i en databas

Metoden ExecuteReader() hämtar ett dataläsarobjekt som låter dig se resultaten av SQL Select-satsen med hjälp av en skrivskyddad ström av information. Men om du behöver köra SQL-satser som modifierar datatabellen måste du anropa metoden ExecuteNonQuery() av detta objekt lag. Denna enda metod är utformad för att utföra infogning, ändringar och raderingar, beroende på formatet på kommandotexten.

Begrepp nonquery betyder en SQL-sats som inte returnerar en resultatuppsättning. Därav, Välj uttalandenär frågor, men Insert-, Update- och Delete-satser är det inte. Följaktligen returnerar metoden ExecuteNonQuery() en int som innehåller antalet rader som påverkas av dessa satser, snarare än en ny uppsättning poster.

För att visa hur man ändrar innehållet i en befintlig databas med enbart ExecuteNonQuery()-frågan, är nästa steg att skapa ditt eget dataåtkomstbibliotek som kapslar in AutoLot-databasprocessen.

I en riktig produktionsmiljö kommer din ADO.NET-logik nästan säkert att vara isolerad i en .NET .dll-sammansättning av en enkel anledning - kodåteranvändning! Detta gjordes inte i tidigare artiklar för att inte distrahera dig från uppgifterna. Men det skulle vara ett slöseri med tid att utveckla samma anslutningslogik, samma dataläsningslogik och samma kommandoexekveringslogik för varje applikation som behöver arbeta med AutoLot-databasen.

Genom att isolera dataåtkomstlogik i ett .NET-kodbibliotek kan olika applikationer med vilket användargränssnitt som helst (konsolliknande, skrivbordsstil, webbstil, etc.) komma åt det befintliga biblioteket, även oavsett språk. Och om du utvecklar ett dataåtkomstbibliotek i C#, kommer andra .NET-programmerare att kunna skapa sina egna användargränssnitt på vilket språk som helst (till exempel VB eller C++/CLI).

Vårt dataåtkomstbibliotek (AutoLotDAL.dll) kommer att innehålla ett enda namnområde (AutoLotConnectedLayer) som kommer att interagera med AutoLot-databasen med hjälp av ADO.NET-anslutna typer.

Börja med att skapa ett nytt C# Class Library-projekt som heter AutoLotDAL (förkortning av "AutoLot Data Access Layer") och ändra sedan det ursprungliga C#-kodfilnamnet till AutoLotConnDAL.cs.

Byt sedan namn på omfattningen av namnutrymmet till AutoLotConnectedLayer och ändra namnet på den ursprungliga klassen till InventoryDAL, eftersom denna klass kommer att definiera olika medlemmar för att interagera med Inventory-tabellen i AutoLot-databasen. Importera slutligen följande .NET-namnrymder:

Använda System; använder System.Collections.Generic; använder System.Text; använder System.Data; använder System.Data.SqlClient; namnutrymme AutoLotConnectedLayer ( offentlig klass InventoryDAL ( ) )

Lägger till anslutningslogik

Vår första uppgift är att definiera metoder som tillåter anropsprocessen att ansluta till och koppla från datakällan med hjälp av en giltig anslutningssträng. Eftersom vår AutoLotDAL.dll-sammansättning kommer att hårdkodas för att använda klasstyperna System.Data.SqlClient, definiera en privat SqlConnection-variabel som kommer att allokeras när InventoryDAL-objektet skapas.

Definiera dessutom en metod OpenConnection() och sedan en annan CloseConnection() som kommer att interagera med denna variabel:

Public class InventoryDAL ( privat SqlConnection connect = null; public void OpenConnection(string connectionString) ( connect = new SqlConnection(connectionString); connect.Open(); ) public void CloseConnection() ( connect.Close(); ) )

För korthetens skull kommer inte InventoryDAL-typen att leta efter alla möjliga undantag och kommer inte att skicka anpassade undantag när olika situationer uppstår (till exempel när anslutningssträngen är felaktig). Men om du byggde ett bibliotek för produktionsdataåtkomst, skulle du sannolikt behöva använda strukturerade undantagshanteringstekniker för att ta hänsyn till eventuella avvikelser som kan uppstå under körning.

Lägger till insättningslogik

Föra in ny ingång till Inventory-tabellen handlar det om att formatera SQL-satsen Föra in(beroende på användarinmatning) och anrop av metoden ExecuteNonQuery() med kommandoobjektet. För att göra detta, lägg till en offentlig InsertAuto()-metod till InventoryDAL-klassen som tar fyra parametrar som motsvarar de fyra kolumnerna i Inventory-tabellen (CarID, Color, Make och PetName). Baserat på dessa argument, generera en rad för att lägga till en ny post. Slutligen, kör SQL-satsen med SqlConnection-objektet:

Public void InsertAuto(int id, string color, string make, string petName) ( // SQL-sats string sql = string.Format("Infoga i inventering" + "(Bil-ID, Märke, Färg, PetName) Värden(@BilId, @Make, @Color, @PetName)"); använder (SqlCommand cmd = new SqlCommand(sql, this.connect)) ( // Lägg till parametrar cmd.Parameters.AddWithValue("@CarId", id); cmd.Parameters.AddWithValue("@Make", make); cmd. Parameters.AddWithValue("@Color", färg); cmd.Parameters.AddWithValue("@PetName", petName); cmd.ExecuteNonQuery(); ) )

Att definiera klasser som representerar poster i en relationsdatabas är ett vanligt sätt att skapa ett dataåtkomstbibliotek. Faktum är att ADO.NET Entity Framework genererar automatiskt starkt skrivna klasser som låter dig interagera med databasdata. Förresten, det fristående lagret av ADO.NET genererar starkt skrivna DataSet-objekt för att representera data från en given tabell i en relationsdatabas.

Att skapa en SQL-sats med strängsammansättning kan vara en säkerhetsrisk (tänk på SQL-insättningsattacker). Det är bättre att skapa kommandotexten med hjälp av en parametrerad fråga, som kommer att beskrivas lite senare.

Lägger till raderingslogik

Borttagning befintligt rekord inte svårare än att sätta in en ny skiva. Till skillnad från koden InsertAuto() kommer ett viktigt försök/fånga område att visas som hanterar den möjliga situationen där ett försök görs att ta bort en bil som någon redan har beställt från Kundtabellen. Lägg till följande metod till klassen InventoryDAL:

Public void DeleteCar(int id) ( string sql = string.Format("Delete from Inventory where CarID = "(0)"", id); using (SqlCommand cmd = new SqlCommand(sql, this.connect)) ( försök ( cmd.ExecuteNonQuery(); ) catch (SqlException ex) ( Exception error = new Exception("Tyvärr, den här maskinen är i restorder!", ex); kastfel; ) ) )

Lägger till förändringslogik

När det gäller att uppdatera en befintlig post i Inventory-tabellen uppstår den uppenbara frågan omedelbart: vad exakt kan anropsprocessen tillåtas ändra: bilens färg, det vänliga namnet, modellen eller alla tre? Ett sätt att maximera flexibiliteten är att definiera en metod som tar en parameter av typen string, som kan innehålla vilken SQL-sats som helst, men detta är minst sagt riskabelt.

Helst är det bättre att ha en uppsättning metoder som tillåter anropsprocessen att ändra poster olika sätt. Men för vårt enkla dataåtkomstbibliotek kommer vi att definiera en enda metod som gör att anropsprocessen kan ändra det vänliga namnet på den angivna bilen:

Public void UpdateCarPetName(int id, string newpetName) ( string sql = string.Format("Update Inventory Set PetName = "(0)" Där CarID = "(1)"", newpetName, id); using (SqlCommand cmd = new SqlCommand(sql, this.connect)) ( cmd.ExecuteNonQuery(); ) )

Lägger till samplingslogik

Nu måste vi lägga till en metod för att välja poster. Som visats tidigare låter en specifik dataleverantörs dataläsarobjekt dig välja poster med en skrivskyddad markör. Genom att anropa metoden Read() kan du bearbeta varje post en i taget. Det här är jättebra, men nu måste vi ta reda på hur vi returnerar dessa poster till det anropande applikationslagret.

Ett tillvägagångssätt skulle vara att hämta data med metoden Read() och sedan fylla i och returnera en flerdimensionell array (eller ett annat objekt som den generiska listan ).

Ett annat sätt är att returnera ett System.Data.DataTable-objekt, som faktiskt tillhör det fristående ADO.NET-lagret. DataTable är en klass som representerar ett tabellformat datablock (som ett papper eller ett kalkylblad).

Klassen DataTable innehåller data som en samling rader och kolumner. Dessa samlingar kan fyllas i programmatiskt, men DataTable-typen har en Load()-metod som kan fylla i dem automatiskt med ett dataläsarobjekt! Här är ett exempel där data från Inventory-tabellen returneras som en DataTable:

Public DataTable GetAllInventoryAsDataTable() ( DataTable inv = new DataTable(); string sql = "Select * From Inventory"; med (SqlCommand cmd = new SqlCommand(sql, this.connect)) ( SqlDataReader dr = cmd.ExecuteReader(); .Load(dr); dr.Close(); ) returnera inv; )

Arbeta med parametriserade kommandoobjekt

Hittills har vi använt hårdkodade strängliteraler för varje SQL-fråga i logiken för att infoga, uppdatera och ta bort för typen InventoryDAL. Du är förmodligen medveten om förekomsten av parametriserade frågor, som låter dig behandla SQL-parametrar som objekt snarare än bara en bit text.

Att arbeta med SQL-frågor på ett mer objektorienterat sätt hjälper inte bara till att minska stavfel (med starkt skrivna egenskaper), utan parametriserade frågor är vanligtvis mycket snabbare än sträng-litterala frågor eftersom de bara analyseras en gång (snarare än varje gång). CommandText-egenskapen är inställd på en SQL-sträng). Parameteriserade frågor skyddar dessutom mot SQL-injektionsattacker (ett välkänt säkerhetsproblem för dataåtkomst).

För att stödja parametriserade frågor upprätthåller ADO.NET-kommandoobjekt en samling individuella parameterobjekt. Som standard är denna samling tom, men du kan lägga till valfritt antal parameterobjekt som matchar platshållarparametrar i en SQL-fråga. Om du behöver associera en SQL-frågeparameter med en medlem av parametersamlingen för något kommandoobjekt, föregå SQL-parametern med symbolen @ (åtminstone när du arbetar med Microsoft SQL Server, även om inte alla DBMS stöder denna beteckning).

Inställning av parametrar med hjälp av typen DbParameter

Innan vi börjar skapa parameteriserade frågor, låt oss bekanta oss med typen DbParameter (basklassen för leverantörsparameterobjekt). Den här klassen har ett antal egenskaper som gör att du kan ange namn, storlek och typ av parametern, såväl som andra egenskaper, såsom visningsriktningen för parametern. Några viktiga egenskaper för DbParameter-typen ges nedan:

DbType

Hämtar eller ställer in datatypen från en parameter, representerad som en CLR-typ

Riktning

Returnerar eller ställer in typen av parameter: endast input, output-only, input och output, eller parameter för att returnera ett värde

Är Nullbar

Returnerar eller ställer in om en parameter kan acceptera tomma värden

Parameternamn

Hämtar eller ställer in DbParameter-namnet

Storlek

Problem eller installationer maximal storlek data för parametern (endast användbar för textdata)

Värde

Returnerar eller ställer in värdet på en parameter

För att demonstrera hur man fyller en samling kommandoobjekt med DBParameter-kompatibla objekt, låt oss skriva om metoden InsertAuto() så att den kommer att använda parameterobjekt (alla andra metoder kan göras om på samma sätt, men det aktuella exemplet räcker för oss):

Public void InsertAuto(int id, string color, string make, string petName) ( // SQL-sats string sql = string.Format("Insert Into Inventory" + "(CarID, Make, Color, PetName) Values("(0) ","(1)","(2)","(3)")", id, make, color, petName); // Parameteriserat kommando med (SqlCommand cmd = new SqlCommand(sql, this.connect)) ( SqlParameter param = new SqlParameter(); param.ParameterName = "@CarID"; param.Value = id; param.SqlDbType = SqlDbType.Int; cmd.Parameters.Add(param); param = new SqlParameter(); param. ParameterName = "@Make"; param.Value = make; param.SqlDbType = SqlDbType.Char; param.Size = 10; cmd.Parameters.Add(param); param = new SqlParameter(); param.ParameterName = "@Color "; param.Value = färg; param.SqlDbType = SqlDbType.Char; param.Size = 10; cmd.Parameters.Add(param); param = new SqlParameter(); param.ParameterName = "@PetName"; param.Value = petName; param.SqlDbType = SqlDbType.Char; param.Size = 10; cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); ) )

Observera att SQL-frågan här också innehåller fyra platshållartecken, var och en föregås av en @-symbol. Genom att använda egenskapen ParameterName på typen SqlParameter kan du beskriva var och en av dessa platshållare och specificera olika information (värde, datatyp, storlek, etc.) på ett starkt skrivet sätt. När alla parameterobjekt har förberetts läggs de till kommandoobjektsamlingen med anropet Add().

Olika egenskaper används här för att designa parameterobjekt. Observera dock att parameterobjekt stöder ett antal överbelastade konstruktorer som låter dig ställa in värden för olika egenskaper (vilket resulterar i en mer kompakt kodbas). Observera också att Visual Studio 2010 har olika grafiska designers som automatiskt genererar mycket av denna tråkiga parametermanipulerande kod åt dig.

Att skapa en parametriserad fråga resulterar ofta i mer kod, men resultatet är ett bekvämare sätt att programmera inställning av SQL-satser, samt bättre prestanda. Den här tekniken kan användas för alla SQL-frågor, även om parametriserade frågor är mest användbara om du behöver köra lagrade procedurer.

Regler hakparentes står för [valfri del] av en konstruktion. En vertikal stapel indikerar ett val mellan alternativ (var1|var2). Ellipsen innebär möjlig upprepning flera gånger - 1 gång, 2 gånger [, …]

SELECT-sats

Instruerar Microsoft Access-databasmotorn att returnera information från databasen som en uppsättning poster.

Syntax

VÄLJ [ predikat] { * | tabell.* | [tabell.]fält1

[, [tabell.]fält2 [, ...]]}
FRÅN tabelluttryck [, ...]

SELECT-satsen innehåller följande element.

Element	Beskrivning
Predikat	Ett av följande predikat: ALL, DISTINCT, DISTINCTROW eller TOP. Predikat används för att begränsa antalet poster som returneras. Om inget predikat anges är standardvärdet ALLA.
	Indikerar att alla fält är valda från den eller de angivna tabellerna
Tabell	Namnet på tabellen från vars fält posterna är valda
fält1, fält2	Namnen på fälten som innehåller data som ska hämtas. Om flera fält är specificerade kommer data att hämtas i den ordning deras namn är listade
smeknamn1, smeknamn2	Namn som används som kolumnrubriker istället för ursprungliga kolumnnamn tabeller
tabelluttryck	Ett eller flera tabellnamn som innehåller data som ska hämtas.
extern_databas	Namnet på databasen som innehåller de tabeller som anges i komponenten tabelluttryck om de inte finns i den aktuella databasen

Anteckningar

För att utföra den här operationen söker Microsoft Access-databasmotorn i den eller de angivna tabellerna, hämtar de önskade kolumnerna, väljer de rader som uppfyller de angivna villkoren och sorterar eller grupperar de resulterande raderna i angiven ordning.

SELECT-satser ändrar inte databasdata.

SELECT-satsen är vanligtvis det första ordet i SQL-satsen (SQL-sats (sträng). Uttryck som definierar SQL kommando, som SELECT, UPDATE eller DELETE, och inklusive satser som WHERE eller ORDER BY. SQL-satser/strängar används ofta i frågor och statistiska funktioner.) De flesta SQL-satser är antingen SELECT-satser eller SELECT...INTO-satser.

Minsta syntax för en SELECT-sats är följande:

VÄLJ fält FRÅN tabell

Du kan använda en asterisk (*) för att markera alla fält i en tabell. Följande exempel markerar alla fält i tabellen Anställda.

VÄLJ * FRÅN Anställda;

Om fältnamnet ingår i flera tabeller i FROM-satsen, föregå det med tabellnamnet och satsen «.» (punkt). I följande exempel finns fältet "Avdelning" i tabellerna "Anställda" och "Ledare". SQL-satsen väljer avdelningar från tabellen Anställda och chefsnamn från tabellen Arbetsledare.

VÄLJ medarbetare. Avdelningschefer. Befattningshavares namn FRÅN Anställda INNER JOIN Befattningshavare VAR Anställda. Avdelning = Chefer. Avdelning;

När du skapar ett RecordSet-objekt används tabellfältsnamnet av Microsoft Access-databasmotorn som namnet på "Field"-objektet i objektet Rekorduppsättning. Om fältnamnet behöver ändras eller inte tillhandahålls av uttrycket som genererar fältet, använd ett reserverat ord (Reserverat ord. Ett ord som är en del av ett språk, till exempel Visual Basic. Reserverade ord inkluderar namn på uttalanden, inbyggda funktioner och datatyper, metoder, operatorer och objekt.) AS. Följande exempel visar hur rubriken "Dag" används för att namnge det returnerade objektet Fält i det mottagna objektet Rekorduppsättning.

VÄLJ födelsedag SOM dag FRÅN anställda;

När du arbetar med aggregerade funktioner eller frågor som returnerar tvetydiga eller identiska objektnamn Fält, bör du använda AS-satsen för att skapa ett annat objektnamn Fält. I följande exempel, det returnerade objektet Fält i det mottagna objektet Rekorduppsättning får namnet "Census".

VÄLJ ANTAL(EmployeeCode) SOM Census FROM Anställda;

När du arbetar med en SELECT-sats kan du använda ytterligare satser för att ytterligare begränsa och organisera den data som hämtas. Mer information finns i hjälpämnet för erbjudandet du använder.

FRÅN klausul

Anger tabeller och frågor som innehåller fälten i SELECT-satsen.

Syntax

VÄLJ field_list
FRÅN tabelluttryck

En SELECT-sats som innehåller en FROM-sats innehåller följande element:

Element	Beskrivning
field_list
tabelluttryck	Ett uttryck som definierar en eller flera tabeller - datakällor. Uttrycket kan vara ett tabellnamn, ett lagrat frågenamn eller ett resultatuttryck konstruerat med operatorerna INNER JOIN, LEFT JOIN eller RIGHT JOIN
extern_databas	Den fullständiga sökvägen till den externa databasen som innehåller alla tabeller som anges i tabelluttryck

Anteckningar

Närvaron av en FROM-sats efter en SELECT-sats krävs.

I vilken ordning tabellerna är listade tabelluttryck spelar ingen roll.

Använda länkade tabeller (Länkad tabell. En tabell som sparas i en fil som inte är en del av den öppna databasen men är tillgänglig från Microsoft Access. Användaren kan lägga till, ta bort och ändra poster i den länkade tabellen, men kan inte ändra dess struktur .) istället för klausulen IN kan du göra processen att hämta data från en extern databas enklare och effektivare.

Exemplet nedan visar hur man hämtar data från tabellen Anställda.

VÄLJ Efternamn, Förnamn

FRÅN Anställda;

Indikerar de poster som valts för SQL-frågor ( SQL-språk(Structured Query Language). Ett strukturerat fråge- och databasprogrammeringsspråk som används ofta för att komma åt, fråga, uppdatera och hantera data i relationella DBMS.

Syntax

VÄLJ ]]
FRÅN tabell

SELECT-satsen som innehåller dessa predikat innehåller följande komponenter:

Komponent	Beskrivning
	Underförstått om inga predikat ingår. Microsoft Access-databasmotorn väljer alla poster som matchar villkoren för en SQL-sats (SQL-sats (sträng). Ett uttryck som definierar ett SQL-kommando, som SELECT, UPDATE eller DELETE, och inkluderar satser, som WHERE eller ORDER BY SQL-satser/strängar används vanligtvis i frågor och statistiska funktioner). Följande två identiska exempel visar hur man returnerar alla poster från tabellen Anställda. FRÅN Anställda BESTÄLLNING EFTER Anställdskod; FRÅN Anställda BESTÄLLNING EFTER Anställdskod;
	Utesluter poster som innehåller dubbletter av data i de valda fälten. Endast de unika värdena för vart och ett av fälten som anges i SELECT-satsen ingår i frågeresultaten. Till exempel kan vissa anställda som anges i tabellen Anställda ha samma efternamn. Om två poster innehåller efternamnet "Ivanov" i fältet Efternamn, returnerar följande SQL-sats endast en post som innehåller efternamnet "Ivanov". VÄLJ DISTINCT Efternamn Om DISTINCT-komponenten utelämnas, returnerar frågan båda posterna med efternamnet "Ivanov". Om SELECT-satsen innehåller flera fält, inkluderas kombinationen av alla fältvärden i frågeresultaten endast om den är unik för den posten. Resultaten av en fråga som använder DISTINCT-komponenten uppdateras inte för att återspegla efterföljande ändringar som gjorts av andra användare.
	Utesluter data från poster som upprepas i sin helhet istället för att innehålla enskilda fält med samma data. Låt oss anta att en fråga har skapats som kopplar samman tabellerna "Kunder" och "Beställningar" med hjälp av fältet "Kundkod". Tabellen Kunder innehåller inte dubbletter av kund-ID-fält, men de finns i tabellen Order eftersom varje kund kan ha flera order. Följande SQL-sats visar hur man använder DISTINCTROW-komponenten för att lista organisationer som har gjort minst en beställning, utan att nämna detaljerna för dessa beställningar. VÄLJ DISTINCTROW Titel FRÅN Kunder INNER JOIN Beställningar ON-klienter. CustomerId = Orders. Kundkod BESTÄLL EFTER Titel; Om DISTINCTROW-komponenten utelämnas resulterar frågan i flera rader för varje organisation som beställt flera gånger. DISTINCTROW-komponenten träder bara i kraft när man väljer fält från några av tabellerna som används i frågan. DISTINCTROW-komponenten ignoreras om frågan endast innehåller en tabell eller om fält hämtas från alla tabeller.
TOPP n	Returnerar det angivna antalet poster som är bland de första eller sista posterna i intervallet som anges av ORDER BY-satsen. Låt oss säga att du vill visa namnen på de 25 bästa eleverna från klassen 1994. Förnamn Efternamn WHERE Graduation Year = 2003 ORDER BY GradePointAverage DESC; Om du inte inkluderar ORDER BY-satsen, returnerar frågan en slumpmässig uppsättning av 25 poster från Students-tabellen som uppfyller WHERE-satsen. Predikatet TOP innebär inte ett val mellan lika värden. Om de 25:e och 26:e posterna i föregående exempel hade samma GPA, skulle frågan returnera 26 poster. Du kan också använda det reserverade ordet PERCENT för att hämta en viss procentandel av de första eller sista posterna i det intervall som anges av ORDER BY-satsen. Anta att du istället för de 25 bästa vill visa de lägsta 10 % av eleverna i avgångsklassen. VÄLJ TOP 10 PROCENT Förnamn Efternamn WHERE Graduation Year = 2003 ORDER BY GradePointAverage ASC; ASC-predikatet specificerar utmatningen av värden från den nedre delen av intervallet. Värdet som följer TOP-predikatet måste vara en heltalsdatatyp. Den grundläggande datatypen som används för att lagra heltalsvärden. En heltalsvariabel lagras som ett 64-bitars (8-byte) tal i intervallet -32768 till 32767. ) utan tecken . TOP-predikatet påverkar inte om frågan kan uppdateras.
tabell	Namnet på tabellen som poster hämtas från.

se även

SELECT-sats

FRÅN klausul

WHERE klausul

Bestämmer vilka poster från tabellerna som listas i FROM-satsen som bearbetas av SELECT-, UPDATE- eller DELETE-satser.

Syntax

VÄLJ field_list
FRÅN tabelluttryck
VAR urvalsvillkor

En SELECT-sats som innehåller en WHERE-sats innehåller följande delar.

Del	Beskrivning
field_list	Namnet på fältet eller fälten som hämtas tillsammans med eventuella alias (Alias ​​(SQL). Ett alternativt namn för en tabell eller ett fält i ett uttryck. Alias ​​används vanligtvis som kortare tabell- eller fältnamn för att underlätta efterföljande referens i program, för att förhindra tvetydiga referenser och för att erhålla mer beskrivande namn vid visning av frågeresultat.), predikat (ALL, DISTINCT, DISTINCTROW eller TOP), eller med någon annan parameter i SELECT-satsen.
tabelluttryck	Namnet på den eller de tabeller som data hämtas från.
urvalsvillkor	Uttryck (Uttryck. En kombination av matematiska och logiska operatorer, konstanter, funktioner, fältnamn, kontroller och egenskaper som resulterar i ett enda värde. Uttrycket kan utföra beräkningar, bearbeta text eller validera data.) som måste matcha de poster som ingår i frågeresultat.

Anteckningar

Microsoft Access-databasmotorn väljer poster som uppfyller villkoren i WHERE-satsen. Om WHERE-satsen inte anges returnerar frågan alla rader i tabellen. Om en fråga anger flera tabeller men inte anger en WHERE- eller JOIN-sats, producerar frågan en kartesisk produkt (kartesisk produkt. Är resultatet av exekvering av en SQL SELECT-sats som har en FROM-sats som refererar till två eller flera tabeller och ingen WHERE eller JOIN-sats som anger metod för sammanfogning.) tabeller.

WHERE-satsen krävs inte, men om den används måste den följa FROM-satsen. Du kan till exempel välja alla anställda från försäljningsavdelningen (WHERE Department = "Försäljning") eller alla kunder mellan 18 och 30 år (WHERE Age Between 18 And 30).

Om en JOIN-sats inte används för en SQL join-operation på flera tabeller, kommer det resulterande objektet Rekord satt det kommer att vara omöjligt att uppdatera.

WHERE-satsen liknar HAVING-satsen och specificerar de valda posterna. Efter att posterna har grupperats av GROUP BY-satsen bestämmer HAVING-satsen också posten som ska visas.

WHERE-satsen används för att utesluta poster som inte behöver grupperas med GROUP BY-satsen.

Använd olika uttryck för att avgöra vilka poster som returneras av SQL-satsen. Till exempel väljer följande SQL-sats alla anställda vars lön överstiger RUR.

VÄLJ Efternamn, Lön FRÅN Anställda VAR Lön > 21000;

WHERE-satsen kan innehålla upp till 40 uttryck kopplade av logiska operatorer (t.ex. OCH Och ELLER).

Om du anger ett fältnamn som innehåller mellanslag eller skiljetecken måste du ha det inom hakparenteser (). Till exempel kan en kundinformationstabell innehålla information om specifika kunder.

VÄLJ [Kundens favoritrestaurang]

Ange ett argument urvalsvillkor, date literals (Date literal. Alla teckensekvenser i ett giltigt format, inneslutna i siffertecken (#). Giltiga format är datumformatet som anges i språk- och standardinställningarna och Universal Date Format.) måste representeras i US-format. , även om icke-amerikanskt datumformat används. version av Microsoft Access-databasmotorn. Till exempel är datumet "10 maj 1996" skrivet som 10/5/96 i Storbritannien och som 05/10/1996 i Ryssland. Kom ihåg att bifoga bokstaver av datum i siffertecken (#), som visas i exemplen nedan.

För att hitta poster för den 10 maj 1996 i den brittiska databasen, använd följande SQL-sats:

VÄLJ * FRÅN beställningar VAR Leveransdatum = #10.05.1996#;

Du kan också använda funktionen DatumValue, erkänner internationella parametrar, installerat av Microsoft Windows®. Använd till exempel denna kod för Ryssland:

SELECT * FROM Orders WHERE Leveransdatum = DateValue("05/10/1996");

Och följande kod är för Storbritannien:

SELECT * FROM Orders WHERE Leveransdatum = DateValue("10/5/96");

Notera. Om kolumnen som anges i urvalskriterieraden är av typen GUID (Replica ID (GUID). Ett 16-byte fält i en Microsoft Access-databas som används för att unikt identifiera replikering. GUID:n används för att identifiera repliker, replikuppsättningar, tabeller, poster och andra objekt. I Microsoft Access-databaser kallas GUID-koder replikakoder.), använder urvalsvillkoren en något annorlunda syntax.

WHERE ReplicaID = (GUID (AB-CDEF0ABCDEF))

Se till att kapslade parenteser och bindestreck är korrekt placerade.

Källsida: http://office. /ru-ru/access/HA.aspx? pid=CH

GROUP BY klausul

Kombinerar poster med samma värden som finns i den angivna listan med fält till en post. Ett sammanfattningsvärde skapas för varje post om en SQL-aggregationsfunktion ingår i SELECT-satsen, t.ex Belopp eller Räkna.

Syntax

VÄLJ field_list
FRÅN tabell
VAR selection_condition

En SELECT-sats som innehåller en GROUP BY-sats innehåller följande element:

Element	Beskrivning
field_list	Namnen på fälten som hämtas tillsammans med eventuella alias (Alias ​​(SQL). Ett alternativt namn för en tabell eller ett fält i ett uttryck. Alias ​​används vanligtvis som kortare tabell- eller fältnamn för att underlätta efterföljande referens i program, för att förhindra tvetydiga referenser och för att få mer informativa namn vid visning av frågeresultat.) och statistiska uppgifter SQL-funktioner, predikat (ALL, DISTINCT, DISTINCTROW eller TOP), eller andra parametrar i SELECT-satsen
tabell
urvalsvillkor	Urvalsvillkor. Om uttalandet innehåller en WHERE-sats, kommer värdena att grupperas av Microsoft Access-databasmotorn efter att den har tillämpats på posterna.
group_field_list	group_field_list

Anteckningar

GROUP BY-satsen är valfri.

Om SQL-statistiska funktioner inte ingår i SELECT-satsen, beräknas inte summavärden.

GROUP BY-fältvärden som är Null (Null. Ett värde som kan anges i ett fält eller användas i uttryck och frågor för att indikera saknade eller okända data. I Visual Basic anger nyckelordet Null ett Null-värde. Vissa fält, t.ex. som primära nyckelfält, inte får innehålla Null-värden.), är grupperade och utelämnas inte. Dock värdena Null utvärderas inte av någon av SQL-statistikfunktionerna.

WHERE-satsen används för att utesluta rader som inte behöver grupperas. HAVING-satsen används för att filtrera poster efter gruppering.

Fält från fältlistan GROUP BY som inte innehåller Memo-datatyp (Memo Field-datatyp. En fältdatatyp i en Microsoft Access-databas. Ett MEMO-fält kan innehålla upp till 65535 tecken.) eller OLE Object (Fältdatatyp). OLE-objekt" En fältdatatyp som används för att spara objekt från andra applikationer som är länkade till eller inbäddade i en Microsoft Access-databas.) kan referera till vilket fält som helst i vilken tabell som helst som anges i FROM-satsen, även om fältet inte ingår i SELECT-satsen. För att göra detta räcker det att ha minst en SQL-statistisk funktion i SELECT-satsen. Microsoft Access-databasmotorn tillåter inte gruppering efter fält som innehåller MEMO-fält- eller OLE-objektdata.

Alla fält i SELECT-fältlistan måste antingen ingå i en GROUP BY-sats eller vara argument till en SQL-aggregationsfunktion.

se även

SELECT-sats

SELECT...INTO-sats

Predikater ALL, DISTINCT, DISTINCTROW, TOP

FRÅN klausul

HA erbjudande

ORDER BY klausul

WHERE klausul

SQL statistiska funktioner

Källsida: http://office. /ru-ru/access/HA.aspx? pid=CH

HA erbjudande

Definierar grupperade poster som ska visas i en SELECT-sats med en GROUP BY-sats. Efter att posterna har grupperats av GROUP BY-satsen kommer HAVING-satsen att visa de som uppfyller dess villkor.

Syntax

VÄLJ field_list
FRÅN tabell
VAR urvalsvillkor
GRUPP AV group_field_list

En SELECT-sats som innehåller en HAVING-sats innehåller följande element:

Element	Beskrivning
field_list	Namnen på fälten som laddas tillsammans med eventuella alias (Alias ​​(SQL). Ett alternativt namn för en tabell eller ett fält i ett uttryck. Alias ​​används vanligtvis som kortare tabell- eller fältnamn för att underlätta efterföljande referens i program, för att förhindra tvetydiga referenser och för att få mer informativa namn vid visning av frågeresultat.) och SQL-statistiska funktioner, predikat (ALL, DISTINCT, DISTINCTROW eller TOP) eller med andra parametrar i SELECT-satsen.
tabell	Namn på tabellen från vilken poster laddas
selection_condition	Urvalsvillkor. Om satsen innehåller en WHERE-sats, kommer Microsoft Access-databasmotorn att gruppera värdena efter att den applicerats på posterna.
group_field_list	Namn på fält (upp till 10) som används för att gruppera poster. Namnordningen i group_field_list bestämmer nivån på grupperingen - från högsta till lägsta
group_condition	Ett uttryck som anger vilka poster som ska visas

Anteckningar

HAVING-satsen är valfri.

HAVING-satsen liknar WHERE-satsen som bestämmer urvalet av poster. Efter att ha grupperat poster med GROUP BY-satsen bestämmer HAVING-satsen vilka poster som ska visas.

VÄLJ typkod,

Summa (I lager)

FRÅN produkter

GRUPPER EFTER Typkod

HA Summa(InStock) > 100 och som "TEL*";

HAVING-satsen kan innehålla upp till 40 uttryck länkade av logiska operatorer som t.ex Och Och Eller.

Källsida: http://office. /ru-ru/access/HA.aspx? pid=CH

ORDER BY klausul

Sorterar posterna som returneras av frågan i stigande eller fallande ordning av värdena i det eller de angivna fälten.

Syntax

VÄLJ field_list
FRÅN tabell
VAR selection_condition
[, fält2 ][, ...]]]

En SELECT-sats som innehåller en ORDER BY-sats innehåller följande element.

Element	Beskrivning
field_list	Namnen på fälten som hämtas tillsammans med eventuella alias (Alias ​​(SQL). Ett alternativt namn för en tabell eller ett fält i ett uttryck. Alias ​​används vanligtvis som kortare tabell- eller fältnamn för att underlätta efterföljande referens i program, för att förhindra tvetydiga referenser och för att få mer informativa namn vid visning av frågeresultat.) och SQL-statistiska funktioner, predikat (ALL, DISTINCT, DISTINCTROW eller TOP) eller med andra parametrar i SELECT-satsen.
tabell	Namn på tabellen från vilken poster hämtas
urvalsvillkor	Urvalsvillkor. Om uttalandet innehåller en WHERE-sats, kommer Microsoft Access-databasmotorn efter att ha tillämpats på posterna att beställa posternas värden
fält1, fält2	Namn på de fält som poster sorteras efter.

Anteckningar

ORDER BY-satsen är valfri. Den ska användas när du behöver visa data i sorterad form.

Standardsorteringsordningen är (Sorteringsordning. Ett sätt att ordna data baserat på dess värden och typ. Data kan sorteras alfabetiskt, efter numeriska värden eller efter datum. Sorteringsordningen kan vara stigande (0 till 100, A till Z) eller fallande (från 100 till 0, från Z till A).) stigande (från A till Ö, från 0 till 9). Exemplen nedan visar sortering av anställdas namn efter efternamn.

VÄLJ Efternamn, Förnamn

FRÅN Anställda

BESTÄLL EFTER Efternamn;

VÄLJ Efternamn, Förnamn

FRÅN Anställda

BESTÄLL EFTER Efternamn ASC;

För att sortera fält i fallande ordning (Z till A, 9 till 0), lägg till det reserverade ordet DESC till namnet på varje fält. Följande exempel visar sortering i fallande ordning baserat på anställdas löner.

VÄLJ Efternamn, Lön

FRÅN Anställda

BESTÄLLNING EFTER Lön DESC, Efternamn;

Om du anger fält i ORDER BY-satsen som innehåller data av typen MEMO Field (Memo Field-datatyp. En fältdatatyp i en Microsoft Access-databas. Ett MEMO-fält kan innehålla upp till 65 535 tecken.) eller OLE Object Field (OLE Object Field) Fältdatatyp "En fältdatatyp som används för att spara objekt från andra applikationer länkade till eller inbäddade i en Microsoft Access-databas.), kommer detta att generera ett fel. Microsoft Access-databasmotorn kan inte sortera dessa fälttyper.

ORDER BY-satsen är vanligtvis den sista satsen i en SQL-sats (SQL-sats (sträng). Ett uttryck som definierar ett SQL-kommando, såsom SELECT, UPDATE eller DELETE, och inkluderar satser, såsom WHERE eller ORDER BY. SQL-satser /strängar används ofta i frågor och statistiska funktioner.).

Du kan inkludera ytterligare fält i ORDER BY-satsen. Posterna sorteras först efter det fält som anges först i ORDER BY-satsen. Poster med samma värden i det första fältet sorteras sedan efter det fält som anges av det andra, och så vidare.
se även

SELECT-sats

SELECT...INTO-sats

Predikater ALL, DISTINCT, DISTINCTROW, TOP

FRÅN klausul

GROUP BY klausul

HA erbjudande

WHERE klausul

SQL statistiska funktioner

Källsida: http://office. /ru-ru/access/HA.aspx? pid=CH

INNER JOIN operation

Sammanfogar poster från två tabeller om de anslutande fälten i dessa tabeller innehåller samma värden.

Syntax

FRÅN bord 1 INRE KOPPLING Tabell 2 PÅ bord 1.fält1 jämförelseoperator tabell2.fält2

Operationen INNER JOIN består av följande delar:

Element	Beskrivning
bord 1, Tabell 2	Namn på tabellerna som innehåller de poster som ska sammanfogas
fält1, fält2	Namn på fälten som ska länkas. Icke-numeriska fält måste vara av samma datatyp (Data Type. En fältkarakteristik som definierar vilken typ av data som fältet kan innehålla. Datatyper inkluderar: Boolean, Heltal, Long, Currency, Single, Double, Date, String, och Variant (standard) och innehåller data av samma typ. Namnen på dessa fält kan dock vara annorlunda
comparison_operator	Vilken jämförelseoperator som helst: (=,<, >, <=, >= eller<>)