SQL - Lekce 4. Načítání dat - příkaz SELECT

V naší databázi fóra jsou tedy tři tabulky: uživatelé (uživatelé), témata (témata) a příspěvky (zprávy). A my chceme vidět, jaká data obsahují. V SQL na to existuje operátor VYBRAT. Syntaxe pro jeho použití je následující:

SELECT what_select FROM where_select;

Místo „what_select“ musíme zadat buď název sloupce, jehož hodnoty chceme vidět, nebo názvy několika sloupců oddělených čárkami, nebo symbol hvězdičky (*), což znamená výběr všech sloupců tabulky. . Místo "from_select" byste měli zadat název tabulky.

Podívejme se nejprve na všechny sloupce z tabulky uživatelů:

SELECT * FROM uživatelů;

To jsou všechna naše data, která jsme zadali do této tabulky. Předpokládejme ale, že se chceme podívat pouze na sloupec id_user (například v minulé lekci jsme k naplnění tabulky témat potřebovali vědět, kteří id_users jsou v tabulce uživatelů). Za tímto účelem uvedeme v požadavku název tohoto sloupce:

SELECT id_user FROM uživatelů;

No a pokud chceme vidět například jména a e-maily našich uživatelů, tak uvedeme sloupce zájmu oddělené čárkami:

SELECT jméno, email FROM uživatelů;

Podobně můžete vidět, jaká data obsahují naše další tabulky. Podívejme se, jaká témata máme:

VYBRAT * Z témat;

Teď máme jen 4 témata, ale co když jich je 100? Přál bych si, aby byly zobrazeny například v abecedním pořadí. V SQL pro to existuje klíčové slovo SEŘADIT PODLE následovaný názvem sloupce, podle kterého proběhne řazení. Syntaxe je následující:

SELECT název_sloupce FROM název_tabulky ORDER BY název_sloupce_třídění;

Ve výchozím nastavení je řazení ve vzestupném pořadí, ale to lze změnit přidáním klíčového slova DESC

Nyní jsou naše data seřazeny sestupně.

Řazení lze provádět podle několika sloupců najednou. Například následující dotaz seřadí data podle sloupce topic_name, a pokud je v tomto sloupci více identických řádků, bude sloupec id_author seřazen v sestupném pořadí:

Porovnejte výsledek s výsledkem předchozího dotazu.

Velmi často nepotřebujeme všechny informace z tabulky. Chceme například zjistit, která témata vytvořil uživatel sveta (id=4). V SQL pro to existuje klíčové slovo KDE, syntaxe takového požadavku je následující:

Pro náš příklad je podmínkou ID uživatele, tzn. Potřebujeme pouze ty řádky, jejichž sloupec id_author obsahuje 4 (ID uživatele sveta):

Nebo nás zajímá, kdo vytvořil téma „kola“:

Samozřejmě by bylo pohodlnější, kdyby se místo id autora zobrazilo jeho jméno, ale jména jsou uložena v jiné tabulce. V následujících lekcích se naučíme, jak vybírat data z více tabulek. Mezitím zjistíme, jaké podmínky lze nastavit pomocí klíčového slova WHERE.

Operátor	Popis
= (rovná se)	Jsou vybrány hodnoty rovné zadané hodnotě Příklad: SELECT * FROM témat WHERE id_author=4; Výsledek:
> (více)	Jsou vybrány hodnoty vyšší, než je uvedeno Příklad: SELECT * FROM témat WHERE id_author>2; Výsledek:
< (меньше)	Jsou vybrány hodnoty menší než zadané Příklad: SELECT * FROM topics WHERE id_author Výsledek:
>= (větší nebo rovno)	Jsou vybrány hodnoty větší a rovné zadané hodnotě Příklad: SELECT * FROM topics WHERE id_author>=2; Výsledek:
<= (меньше или равно)	Jsou vybrány hodnoty menší a rovné zadané hodnotě Příklad: SELECT * FROM topics WHERE id_author Výsledek:
!= (není rovno)	Jsou vybrány hodnoty, které se nerovnají zadané hodnotě Příklad: SELECT * FROM topics WHERE id_author!=1; Výsledek:
NENÍ NULL	Vybere řádky, které mají hodnoty v zadaném poli Příklad: SELECT * FROM témat WHERE id_author NENÍ NULL; Výsledek:
JE NULL	Vybere řádky, které nemají v zadaném poli žádnou hodnotu Příklad: SELECT * FROM témat WHERE id_author JE NULL; Výsledek: Prázdná sada – žádné takové řádky nejsou.
BETWEEN (mezi)	Vyberou se hodnoty mezi zadanými hodnotami Příklad: SELECT * FROM topics WHERE id_author BETWEEN 1 AND 3; Výsledek:
IN (hodnota obsažena)	Vyberou se hodnoty odpovídající zadaným hodnotám Příklad: SELECT * FROM topics WHERE id_author IN (1, 4); Výsledek:
NOT IN (hodnota není obsažena)	Jsou vybrány jiné hodnoty, než jsou zadané Příklad: SELECT * FROM topics WHERE id_author NOT IN (1, 4); Výsledek:
LIKE (shoda)	Vyberou se hodnoty, které odpovídají vzoru Příklad: SELECT * FROM topics WHERE topic_name LIKE "led%"; Výsledek: Možné metaznaky pro operátor LIKE budou diskutovány níže.
NELÍBÍ SE (neodpovídá)	Jsou vybrány hodnoty, které neodpovídají vzoru Příklad: SELECT * FROM topics WHERE topic_name NOT LIKE "led%"; Výsledek:

Metaznaky operátoru LIKE

Vyhledávání pomocí metaznaků lze provádět pouze v textových polích.

Nejběžnějším metaznakem je % . To znamená jakékoli symboly. Chceme-li například najít slova začínající písmeny "vel", pak napíšeme LIKE "vel%" a pokud chceme najít slova obsahující znaky "klub", pak napíšeme LIKE "% klub%". Například:

Dalším běžně používaným metaznakem je _ . Na rozdíl od %, které označuje málo nebo žádné znaky, podtržítko označuje právě jeden znak. Například:

Věnujte pozornost prostoru mezi metaznakem a „rybou“, pokud jej přeskočíte, požadavek nebude fungovat, protože metaznak _ znamená přesně jeden znak a mezera je také znak.

Pro dnešek stačí. V další lekci se naučíme psát dotazy na dvě nebo více tabulek. Mezitím si zkuste vytvořit vlastní dotazy podle tabulky příspěvků.

Tato lekce je věnována SQL dotazy do databáze na Přístup VBA. Podíváme se na to, jak se ve VBA provádějí dotazy INSERT, UPDATE, DELETE do databáze a také se naučíme, jak získat konkrétní hodnotu z SELECT dotazu.

Ti, kteří programují Přístup VBA a při práci s databází SQL serveru jsou velmi často postaveni před tak jednoduchý a nezbytný úkol, jako je odeslání SQL dotazu do databáze, ať už jde o INSERT, UPDATE nebo jednoduchý SQL SELECT dotaz. A protože jsme začínající programátoři, měli bychom to také umět, takže dnes to uděláme.

Tématu získávání dat z SQL serveru, kde jsme psali kód pro získání těchto dat ve VBA, jsme se již dotkli např. v článku Nahrávání dat do textového souboru z MSSql 2008, nebo jsme se také dotkli málo v materiálu Nahrávání dat z Accessu do šablony Word a Excel, ale tak či onak jsme se na to podívali povrchně a dnes o tom navrhuji mluvit trochu podrobněji.

Poznámka! Všechny níže uvedené příklady jsou považovány za použití projektu Access 2003 ADP a databáze MSSql 2008 Pokud nevíte, co je projekt ADP, pak jsme se na to podívali v materiálu Jak vytvořit a nakonfigurovat projekt Access ADP.

Zdrojová data pro příklady

Řekněme, že máme tabulku test_table, která bude obsahovat čísla a názvy měsíců v roce (dotazy se provádějí pomocí Management Studio)

VYTVOŘIT TABULKU .( NOT NULL, (50) NULL) ON GO

Jak jsem již řekl, použijeme projekt ADP nakonfigurovaný pro práci s MS SQL 2008, ve kterém jsem vytvořil testovací formulář a přidal tlačítko start s podpisem "Běh", který budeme potřebovat k otestování našeho kódu, tzn. Celý kód zapíšeme do obsluhy události " Stisk tlačítka».

Databázové dotazy INSERT, UPDATE, DELETE ve VBA

Abychom se příliš nezdržovali, začněme hned, řekněme, že potřebujeme přidat řádek do naší testovací tabulky ( kód okomentován)/

Private Sub start_Click() "Deklarujte proměnnou pro uložení řetězce dotazu Dim sql_query As String "Zapište do ní dotaz, který potřebujeme sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "Červen")" "Spustit to DoCmd RunSQL sql_query End Sub

V tomto případě se dotaz provede pomocí aktuálních parametrů připojení k databázi. Můžeme zkontrolovat, zda byla data přidána nebo ne.

Jak vidíte, data byla vložena.

Abychom odstranili jeden řádek, napíšeme následující kód.

Private Sub start_Click() "Deklarujte proměnnou pro uložení řetězce dotazu Dim sql_query As String "Zapište do ní mazací dotaz sql_query = "DELETE test_table WHERE id = 6" "Spusťte DoCmd.RunSQL sql_query End Sub

Pokud zaškrtneme, uvidíme, že požadovaný řádek byl smazán.

Chcete-li aktualizovat data, napište požadavek na aktualizaci do proměnné sql_query, doufám, že význam je jasný.

SELECT dotaz do databáze ve VBA

Zde jsou věci o něco zajímavější než u jiných konstrukcí SQL.

Nejprve řekněme, že potřebujeme získat všechna data z tabulky a například je zpracujeme a zobrazíme ve zprávě a vy je samozřejmě můžete použít pro jiné účely, k tomu píšeme následující kód

Private Sub start_Click() "Deklarovat proměnné "Pro sadu záznamů z databáze Dim RS As ADODB.Recordset "Řetězec dotazu Dim sql_query As String "Řetězec pro zobrazení souhrnných dat ve zprávě Dim str As String "Vytvořit nový objekt pro záznamy set RS = New ADODB .Recordset "Query line sql_query = "SELECT id, name_mon FROM test_table" "Spusťte dotaz pomocí aktuálního nastavení připojení projektu RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Procházet záznamy, zatímco ne ( RS.EOF) "Vyplňte proměnnou, aby se zobrazila zpráva str = str & RS.Fields("id") & "-" & RS.Fields("name_mon") & vbnewline "přejít na další záznam RS.MoveNext Wend " Výstup zprávy msgbox str End Sub

Zde již používáme smyčky VBA Access k iteraci všech hodnot v naší sadě záznamů.

Poměrně často je však nutné získat ne všechny hodnoty ze sady záznamů, ale pouze jednu, například název měsíce podle jeho kódu. A k tomu je nějak drahé používat smyčku, takže můžeme jednoduše napsat dotaz, který vrátí jen jednu hodnotu a přistupovat k ní, například získáme název měsíce pomocí kódu 5

Private Sub start_Click() "Deklarovat proměnné" Pro sadu záznamů z databáze Dim RS As ADODB.Recordset "Řetězec dotazu Dim sql_query As String "Řetězec pro zobrazení konečné hodnoty Dim str As String "Vytvořit nový objekt pro sadu záznamů RS = New ADODB.Recordset "Řádek dotazu sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "Spusťte dotaz pomocí aktuálního nastavení připojení projektu RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Získejte naši hodnotu str = RS. Pole(0) msgbox str End Sub

Pro univerzálnost jsme zde již řešili nikoli názvem buňky, ale jejím indexem, tzn. 0 a toto je úplně první hodnota v Sada záznamů, nakonec jsme dostali hodnotu "Smět".

Jak vidíte, vše je docela jednoduché. Pokud často potřebujete získat konkrétní hodnotu z databáze ( jako v posledním příkladu), pak doporučuji vypsat celý kód do samostatné funkce (Jak napsat funkci ve VBA Access 2003) s jedním vstupním parametrem, například kód měsíce ( vezmeme-li v úvahu náš příklad) a jednoduše tam, kde je potřeba tuto hodnotu zobrazit, zavoláme potřebnou funkci s požadovaným parametrem a je to, tím výrazně zredukujeme VBA kód a zlepšíme vnímání našeho programu.

To je pro dnešek vše. Hodně štěstí!

Popis vzdělávacího projektu "Obchod"

Schéma propojení tabulky

Popis tabulek

m_category - kategorie produktů

m_income - příjem zboží

m_outcome - spotřeba zboží

m_product - adresář, popisy produktů

m_supplier - adresář; informace o dodavateli

m_unit - adresář; Jednotky

Chcete-li prakticky vyzkoušet příklady uvedené v tomto školicím materiálu, musíte mít následující software:

Microsoft Access 2003 nebo novější.

SQL dotaz v MS Access. Start

Chcete-li zobrazit obsah tabulky, dvakrát klikněte na název tabulky v levém panelu:

Chcete-li přepnout do režimu úprav pole tabulky, vyberte režim návrhu na horním panelu:

Chcete-li zobrazit výsledek dotazu SQL, poklepejte na název dotazu v levém podokně:

Chcete-li přepnout do režimu úprav dotazu SQL, vyberte režim SQL v horním panelu:

SQL dotaz. Příklady v MS Access. VÝBĚR: 1-10

V dotazu SQL se příkaz SELECT používá k výběru z databázových tabulek.

SQL dotaz Q001. Příklad SQL dotazu pro získání pouze požadovaných polí v požadovaném pořadí:

SELECT dt, product_id, množství

Z m_příjmu;

SQL dotaz Q002. V tomto příkladu SQL dotazu je znak hvězdičky (*) použit k vypsání všech sloupců tabulky m_product, jinými slovy, k získání všech polí vztahu m_product:

VYBRAT *
FROM m_product;

ŽádostSQL Q003. Příkaz DISTINCT se používá k odstranění duplicitních záznamů a získání více jedinečných záznamů:

SELECT DISTINCT product_id

Z m_příjmu;

SQL dotaz Q004. Příkaz ORDER BY se používá k řazení (řazení) záznamů podle hodnot konkrétního pole. Název pole je uveden za příkazem ORDER BY:

VYBRAT *
Z m_příjmu

OBJEDNAT PODLE ceny;

SQL dotaz Q005. Příkaz ASC se používá jako doplněk k příkazu ORDER BY a slouží k určení vzestupného řazení. Příkaz DESC se používá jako doplněk k příkazu ORDER BY a používá se k určení sestupného řazení. V případě, že není specifikováno ASC ani DESC, předpokládá se přítomnost ASC (výchozí):

VYBRAT *
Z m_příjmu

OBJEDNAT OD dt DESC , cena;

SQL dotaz Q006. Pro výběr potřebných záznamů z tabulky se používají různé logické výrazy, které vyjadřují podmínku výběru. Booleovský výraz se objeví za příkazem WHERE. Příklad získání všech záznamů z tabulky m_income, pro které je hodnota částky větší než 200:

VYBRAT *
Z m_příjmu

WHERE částka>200;

SQL dotaz Q007. Pro vyjádření složitých podmínek se používají logické operátory AND (konjunkce), OR (disjunkce) a NOT (logická negace). Příklad získání všech záznamů z tabulky m_outcome, pro které je hodnota částky 20 a hodnota ceny je větší nebo rovna 10:

Cena

Z m_outcome
WHERE částka=20 A cena>=10;

SQL dotaz Q008. Chcete-li spojit data ze dvou nebo více tabulek, použijte instrukce INNER JOIN, LEFT JOIN, RIGHT JOIN. Následující příklad načte pole dt, product_id, částka, cena z tabulky m_income a pole title z tabulky m_product. Záznam tabulky m_income se připojí k záznamu tabulky m_product, když se hodnota m_income.product_id rovná hodnotě m_product.id:

ON m_income.product_id=m_product.id;

SQL dotaz Q009. V tomto SQL dotazu je třeba věnovat pozornost dvěma věcem: 1) hledaný text je uzavřen v jednoduchých uvozovkách ("); 2) datum je ve formátu #Měsíc/Den/Rok#, což platí pro MS Access. V jiných systémech může být formát zápisu data jiný. Příklad zobrazení informace o příjmu mléka 12. června 2011. Vezměte prosím na vědomí formát data #6/12/2011#:

SELECT dt, product_id, title, number, price

OD m_income INNER JOIN m_product

WHERE title="Mléko" And dt=#6/12/2011#; !}

SQL dotaz Q010. Instrukce BETWEEN slouží k testování, zda hodnota patří do určitého rozsahu. Příklad SQL dotazu, který zobrazuje informace o produktech přijatých mezi 1. červnem a 30. červnem 2011:

VYBRAT *
OD m_income INNER JOIN m_product

ON m_income.product_id=m_product.id
KDE dt MEZI #6/1/2011# A #6/30/2011#;

SQL dotaz. Příklady v MS Access. VÝBĚR: 11-20

Jeden SQL dotaz může být vnořen do druhého. Poddotaz není nic jiného než dotaz v rámci dotazu. Obvykle se poddotaz používá v klauzuli WHERE. Existují ale i jiné způsoby použití poddotazů.

Dotaz Q011. Zobrazí se informace o produktech z tabulky m_produkt, jejichž kódy jsou také v tabulce m_příjmů:

VYBRAT *
OD m_product

WHERE id IN (SELECT product_id FROM m_income);

Žádost Q012. Zobrazí se seznam produktů z tabulky m_product, jejichž kódy nejsou v tabulce m_outcome:

VYBRAT *
OD m_product

WHERE id NOT IN (SELECT product_id FROM m_outcome);

Žádost Q013. Tento dotaz SQL zobrazí jedinečný seznam kódů a názvů produktů, které jsou v tabulce m_income, ale ne v tabulce m_outcome:

SELECT DISTINCT product_id, title

OD m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
WHERE product_id NOT IN (SELECT product_id FROM m_outcome);

Dotaz Q014. Jedinečný seznam kategorií, jejichž názvy začínají písmenem M, se zobrazí z tabulky m_category:

SELECT DISTINCT titul

OD m_product
WHERE titulek LIKE "M*";

Dotaz Q015. Příklad provádění aritmetických operací na polích v dotazu a přejmenování polí v dotazu (alias). Tento příklad vypočítává náklady = množství*cena a zisk pro každou položku nákladů, za předpokladu, že zisk je 7 procent z prodeje:

částka*cena/100*7 AS zisk
FROM m_outcome;

Dotaz Q016. Analýzou a zjednodušením aritmetických operací můžete zvýšit rychlost provádění dotazu:

SELECT dt, produkt_id, částka, cena, částka*cena AS výsledek_součet,

vysledek_soucet*0,07 AS zisk
FROM m_outcome;

Dotaz Q017. Ke spojení dat z více tabulek můžete použít příkaz INNER JOIN. V následujícím příkladu je v závislosti na hodnotě ctgry_id každá položka v tabulce m_income porovnána s názvem kategorie z tabulky m_category, do které produkt patří:

SELECT c.title, b.title, dt, částka, cena, částka*cena AS příjem_součet

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
ORDER BY c.title, b.title;

Žádost Q018. Funkce jako SUM - součet, COUNT - množství, AVG - aritmetický průměr, MAX - maximální hodnota, MIN - minimální hodnota se nazývají agregační funkce. Přijímají mnoho hodnot a po jejich zpracování vrátí jedinou hodnotu. Příklad výpočtu součtu součinu polí částka a cena pomocí agregační funkce SUM:

VYBERTE SOUČET(částka*cena) JAKO Součet_celkem

Z m_příjmu;

Dotaz Q019. Příklad použití několika agregačních funkcí:

SELECT Suma(částka) AS Částka_Součet, AVG(částka) AS Částka_AVG,

MAX(částka) AS Částka_Max, Min(částka) AS Částka_Min,
Počet(*) AS Total_Number
Z m_příjmu;

Žádost Q020. V tomto příkladu se vypočítá množství veškerého zboží s kódem 1, aktivované v červnu 2011:

SELECT Suma(částka*cena) JAKO součet_příjmů

Z m_příjmu
WHERE product_id=1 AND dt MEZI #6/1/2011# A #6/30/2011#;.

Dotaz Q021. Následující SQL dotaz vypočítá množství prodejů položek s kódem 4 nebo 6:

SELECT Suma(částka*cena) jako výsledek_součet

Z m_outcome
WHERE product_id=4 OR product_id=6;

Dotaz Q022. Vypočítá se, kolik zboží s kódem 4 nebo 6 bylo prodáno 12. června 2011:

SELECT Suma(částka*cena) AS výsledek_součet

Z m_outcome
WHERE (id_produktu=4 NEBO id_produktu=6) AND dt=#6/12/2011#;

Dotaz Q023.Úkol je tento. Vypočítejte celkové množství aktivovaného zboží v kategorii „Pekařské výrobky“.

Chcete-li tento problém vyřešit, musíte pracovat se třemi tabulkami: m_income, m_product a m_category, protože:

- množství a cena aktivovaného zboží jsou uloženy v tabulce m_income;
- kód kategorie každého produktu je uložen v tabulce m_product;
- název kategorie titulu je uložen v tabulce m_category.

K vyřešení tohoto problému použijeme následující algoritmus:

- určení kódu kategorie "Pekařské výrobky" z tabulky m_category pomocí dílčího dotazu;
- propojením tabulek m_income a m_product pro určení kategorie každého zakoupeného produktu;
- výpočet příjmové částky (= množství*cena) pro zboží, jehož kód kategorie se rovná kódu definovanému výše uvedeným poddotazem.
VYBRAT
Z m_product JAKO VNITŘNÍ PŘIPOJENÍ m_income AS b ON a.id=b.product_id
WHERE ctgry_id = (SELECT id FROM m_category WHERE title="Pekařské výrobky"); !}

Dotaz Q024. Problém výpočtu celkového množství aktivovaného zboží v kategorii „Pekařské výrobky“ řešíme pomocí následujícího algoritmu:
- pro každý záznam v tabulce m_income, v závislosti na hodnotě jeho product_id, z tabulky m_category, odpovídat názvu kategorie;
- vyberte záznamy, pro které je kategorie „Pekárenské výrobky“;
- vypočítat částku příjmu = množství * cena.

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

WHERE c.title="Pekařské výrobky"; !}

Dotaz Q025. Tento příklad vypočítá, kolik položek zboží bylo spotřebováno:

SELECT COUNT(product_id) AS product_cnt

FROM (SELECT DISTINCT product_id FROM m_outcome) AS t;

Dotaz Q026. Pro seskupování záznamů se používá příkaz GROUP BY. Typicky jsou záznamy seskupeny podle hodnoty jednoho nebo více polí a na každou skupinu se aplikuje nějaká agregační operace. Například následující dotaz vygeneruje sestavu o prodeji zboží. To znamená, že se vygeneruje tabulka obsahující názvy zboží a částku, za kterou bylo prodáno:

SELECT název, SUM(částka*cena) JAKO výsledek_součet

FROM m_product AS a INNER JOIN m_outcome AS b
ON a.id=b.product_id
GROUP BY titul;

Dotaz Q027. Zpráva o prodeji podle kategorií. To znamená, že se vygeneruje tabulka, která obsahuje názvy kategorií produktů, celkovou částku, za kterou byly produkty těchto kategorií prodány, a průměrnou částku prodeje. Funkce ROUND se používá k zaokrouhlení průměrné hodnoty na nejbližší setinu (druhá číslice za oddělovačem desetinných míst):

SELECT c.title, SUM(částka*cena) AS výsledný_součet,

ROUND(AVG(částka*cena),2) AS výsledek_součet_prům
FROM (m_product AS a INNER JOIN m_outcome AS b ON a.id=b.product_id)
INNER JOIN m_category AS c ON a.ctgry_id=c.id
GROUP BY c.titul;

Dotaz Q028. Celkový a průměrný počet jeho účtenek se vypočítá pro každý produkt a zobrazí informace o produktech, jejichž celkový počet účtenek je alespoň 500:

SELECT product_id, SUM(částka) AS částka_součet,

Zaokrouhlení(prům.(částka),2) AS prům. částka
Z m_příjmu
GROUP BY product_id
HAVING Součet(částka)>=500;

Dotaz Q029. Tento dotaz vypočítá pro každý produkt částku a průměr jeho příjmů uskutečněných ve druhém čtvrtletí roku 2011. Pokud je celková částka na účtence produktu alespoň 1000, zobrazí se informace o tomto produktu:

VYBERTE název, SUM(částka*cena) JAKO součet_příjmů

Z m_income a INNER JOIN m_product b ON a.product_id=b.id
KDE dt MEZI #4/1/2011# A #6/30/2011#
GROUP BY titulu
HAVING SUM(částka*cena)>=1000;

Dotaz Q030. V některých případech je potřeba porovnat každý záznam nějaké tabulky s každým záznamem jiné tabulky; který se nazývá kartézský součin. Tabulka vzniklá z takového spojení se nazývá Descartova tabulka. Pokud například některá tabulka A má 100 záznamů a tabulka B má 15 záznamů, bude jejich kartézská tabulka obsahovat 100*15=150 záznamů. Následující dotaz spojí každý záznam v tabulce m_income s každým záznamem v tabulce m_outcome:
Z m_příjmu, m_výsledku;

Dotaz Q031. Příklad seskupení záznamů podle dvou polí. Následující SQL dotaz vypočítá pro každého dodavatele množství a množství zboží, které od něj obdržel:

SUM(částka*cena) JAKO součet_příjmů

Žádost Q032. Příklad seskupení záznamů podle dvou polí. Následující dotaz vypočítá pro každého dodavatele množství a množství jejich produktů prodávaných námi:

SELECT dodavatel_id, product_id, SUM(částka) AS částka_součet,

GROUP BY dodavatel_id, produkt_id;

Dotaz Q033. V tomto příkladu jsou dva výše uvedené dotazy (q031 a q032) použity jako poddotazy. Výsledky těchto dotazů pomocí metody LEFT JOIN jsou sloučeny do jednoho reportu. Následující dotaz zobrazí zprávu o množství a množství produktů přijatých a prodaných pro každého dodavatele. Vezměte prosím na vědomí, že pokud byl nějaký produkt již přijat, ale ještě nebyl prodán, bude buňka result_sum pro tento záznam prázdná. že tento dotaz je pouze příkladem použití relativně složitých dotazů jako poddotazu. Výkon tohoto SQL dotazu s velkým množstvím dat je sporný:

VYBRAT *
Z

SUM(částka*cena) JAKO součet_příjmů

ON a.product_id=b.id GROUP BY dodavatel_id, product_id) AS a
PŘIPOJIT SE VLEVO
(SELECT dodavatel_id, product_id, SUM(částka) AS částka_součet,
SUM(částka*cena) JAKO výsledek_součet
OD m_outcome JAKO INNER JOIN m_product AS b
ON a.product_id=b.id GROUP BY dodavatel_id, product_id) AS b
ON (a.product_id=b.product_id) AND (a.supplier_id=b.supplier_id);

Dotaz Q034. V tomto příkladu jsou dva výše uvedené dotazy (q031 a q032) použity jako poddotazy. Výsledky těchto dotazů metodou RIGTH JOIN jsou sloučeny do jednoho reportu. Následující dotaz zobrazí přehled o výši plateb každého klienta podle platebních systémů, které používal, a výši investic, které provedl. Následující dotaz zobrazí zprávu o množství a množství produktů přijatých a prodaných pro každého dodavatele. Vezměte prosím na vědomí, že pokud byl nějaký produkt již prodán, ale ještě nedorazil, bude buňka příjem_součet pro tento záznam prázdná. Přítomnost takových prázdných buněk je indikátorem chyby v účetnictví prodeje, protože před prodejem je nejprve nutné, aby dorazil odpovídající produkt:

VYBRAT *
Z

(SELECT dodavatel_id, product_id, SUM(částka) AS částka_součet,
SUM(částka*cena) JAKO součet_příjmů
Z m_income JAKO INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY id_dodavatele, id_produktu) AS a
SPRÁVNÉ PŘIPOJENÍ SE
(SELECT dodavatel_id, product_id, SUM(částka) AS částka_součet,
SUM(částka*cena) JAKO výsledek_součet
Z m_outcome JAKO INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY dodavatel_id, product_id) AS b
ON (a.id_dodavatele=b.id_dodavatele) AND (id_produktu=b.id_produktu);

Dotaz Q035. Zobrazí se zpráva zobrazující výši příjmů a výdajů podle produktů. K tomu se vytvoří seznam produktů podle tabulek m_income a m_outcome, následně se pro každý produkt z tohoto seznamu vypočte součet jeho příjmů podle tabulky m_income a výše jeho výdajů podle tabulky m_outcome:

SELECT product_id, SUM(in_amount) AS příjem_částka,

SUM(výsledná_částka) AS výsledná_částka
Z
(SELECT product_id, částka AS in_amount, 0 AS out_amount
Z m_příjmu
UNION VŠECHNY
SELECT product_id, 0 AS in_amount, částka AS out_amount
OD m_outcome) AS t
GROUP BY product_id;

Dotaz Q036. Funkce EXISTS vrátí hodnotu TRUE, pokud předaná množina obsahuje prvky. Funkce EXISTS vrátí FALSE, pokud je předaná množina prázdná, to znamená, že neobsahuje žádné prvky. Následující dotaz zobrazí kódy produktů, které jsou obsaženy v tabulkách m_income a m_outcome:

SELECT DISTINCT product_id

OD m_příjmu JAKO a
WHERE EXISTS(SELECT product_id FROM m_outcome AS b

Dotaz Q037. Zobrazí se kódy produktů, které jsou obsaženy v tabulkách m_income a m_outcome:

SELECT DISTINCT product_id

OD m_příjmu JAKO a
WHERE product_id IN (SELECT product_id FROM m_outcome)

Dotaz Q038. Zobrazí se kódy produktů, které jsou obsaženy v tabulce m_income, ale nejsou obsaženy v tabulce m_outcome:

SELECT DISTINCT product_id

OD m_příjmu JAKO a
WHERE NOT EXISTS (VYBERTE ID_produktu FROM m_outcome AS b
WHERE b.product_id=a.product_id);

Dotaz Q039. Zobrazí se seznam produktů s maximální prodejní částkou. Algoritmus je následující. U každého produktu se počítá výše jeho prodeje. Poté se určí maximální z těchto částek. Poté se pro každý produkt opět vypočítá součet jeho tržeb a zobrazí se kód a prodejní součet zboží, jehož prodejní součet je roven maximu:

SELECT product_id, SUM(částka*cena) AS částka_součet

Z m_outcome
GROUP BY product_id
HAVING SUM(částka*cena) = (VYBRAT MAX(s_částka)
FROM (SELECT SUM(částka*cena) AS s_částka FROM m_výsledek GROUP BY product_id));

Dotaz Q040. Vyhrazené slovo IIF (podmíněný operátor) se používá k vyhodnocení logického výrazu a provedení akce v závislosti na výsledku (TRUE nebo FALSE). V následujícím příkladu je dodávka položky považována za „malou“, pokud je množství menší než 500. V opačném případě, to znamená, že množství příjmu je větší nebo rovno 500, je dodávka považována za „velkou“:

SELECT dt, product_id, množství,

IIF(částka OD m_příjmu;

SQL dotaz Q041. V případě, že je operátor IIF použit vícekrát, je výhodnější jej nahradit operátorem SWITCH. Operátor SWITCH (operátor vícenásobného výběru) se používá k vyhodnocení logického výrazu a provedení akce v závislosti na výsledku. V následujícím příkladu je dodaná šarže považována za "malou", pokud je množství zboží v šarži menší než 500. V opačném případě, pokud je množství zboží větší nebo rovno 500, je šarže považována za "velkou". ":

SELECT dt, product_id, množství,

SWITCH(částka =500,"velký") AS značka
Z m_příjmu;

Dotaz Q042. V dalším požadavku, pokud je množství zboží v přijaté dávce menší než 300, je dávka považována za „malou“. V opačném případě, tedy pokud je podmínka částka SELECT dt, product_id, množství,
IIF(částka IIF(částka Z m_příjmu;

SQL dotaz Q043. V dalším požadavku, pokud je množství zboží v přijaté dávce menší než 300, je dávka považována za „malou“. V opačném případě, tedy pokud je podmínka částka SELECT dt, product_id, množství,
SWITCH(částka částka částka>=1000,"velká") AS značka
Z m_příjmu;

SQL dotaz Q044. V následujícím dotazu jsou prodeje rozděleny do tří skupin: malé (do 150), střední (od 150 do 300), velké (300 a více). Dále se pro každou skupinu vypočítá celková částka:

SELECT Kategorie, SUM(součet_výsledků) AS Ctgry_Total

FROM (VYBRAT částku*cenu JAKO výsledek_součet,
IIf(částka*cena IIf(částka*cena Z m_výsledku) AS t
GROUP BY Kategorie;

SQL dotaz Q045. Funkce DateAdd slouží k přidání dnů, měsíců nebo let k danému datu a získání nového data. Další požadavek:
1) přidá 30 dní k datu z pole dt a zobrazí nové datum v poli dt_plus_30d;
2) přidá 1 měsíc k datu z pole dt a zobrazí nové datum v poli dt_plus_1m:

SELECT dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m

Z m_příjmu;

SQL dotaz Q046. Funkce DateDiff je určena k výpočtu rozdílu mezi dvěma daty v různých jednotkách (dny, měsíce nebo roky). Následující dotaz vypočítá rozdíl mezi datem v poli dt a aktuálním datem ve dnech, měsících a letech:

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

DateDiff("m",dt,Date()) AS poslední_měsíce,
DateDiff("yyyy",dt,Date()) AS minulé_roky
Z m_příjmu;

SQL dotaz Q047. Počet dní od data přijetí zboží (tabulka m_income) do aktuálního data se vypočítá pomocí funkce DateDiff a porovná se datum expirace (tabulka m_product):

DateDiff("d",dt,Date()) AS last_days
OD m_příjmu JAKO VNITŘNÍ PŘIPOJENÍ m_produkt JAKO b
ON a.product_id=b.id;

SQL dotaz Q048. Počítá se počet dní od data přijetí zboží do aktuálního data, poté se kontroluje, zda toto množství nepřekračuje datum spotřeby:

SELECT a.id, product_id, dt, lifedays,

DateDiff("d",dt,Date()) AS last_days, IIf(last_days>lifedays,"Yes","No") AS date_expire
Z m_income a INNER JOIN m_product b
ON a.product_id=b.id;

SQL dotaz Q049. Počítá se počet měsíců od data převzetí zboží do aktuálního data. Sloupec month_last1 vypočítá absolutní počet měsíců, sloupec month_last2 vypočítá počet celých měsíců:

SELECT dt, DateDiff("m",dt,Date()) AS měsíc_poslední1,

DateDiff("m",dt,Date())-iif(day(dt)>day(date()),1,0) AS month_last2
Z m_příjmu;

SQL dotaz Q050. Zobrazí se čtvrtletní zpráva o množství a množství zakoupeného zboží za rok 2011:

SELECT kvartal, SUM(součet_výsledků) AS Celkem

FROM (SELECT částka*cena AS výsledek_součet, měsíc(dt) AS m,
SPÍNAČ(m =10,4) AS kvartal
OD m_příjmu KAM rok(dt)=2011) AS t
GROUP BY čtvrtletí;

Dotaz Q051. Následující dotaz pomáhá zjistit, zda uživatelé mohli do systému zadat informace o spotřebě zboží ve větším množství, než bylo přijaté zboží:

SELECT product_id, SUM(in_sum) AS příjem_součet, SUM(out_sum) AS result_sum

FROM (SELECT product_id, částka*cena jako in_sum, 0 jako out_sum
z m_příjmu
UNION VŠECHNY
SELECT product_id, 0 jako in_sum, částka*cena jako out_sum
z m_outcome) AS t
GROUP BY product_id
HAVING SUM(in_sum)
Dotaz Q052.Číslování řádků vrácených dotazem je implementováno různými způsoby. Řádky sestavy připravené v MS Access můžete například přečíslovat pomocí samotného MS Access. Přečíslovat můžete také pomocí programovacích jazyků, například VBA nebo PHP. Někdy je to však potřeba provést v samotném SQL dotazu. Následující dotaz tedy očísluje řádky tabulky m_income podle vzestupného pořadí hodnot pole ID:

SELECT COUNT(*) jako N, b.id, b.product_id, b.amount, b.price

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

Dotaz Q053. Zobrazí se pět nejlepších produktů mezi produkty podle objemu prodeje. Prvních pět záznamů se vytiskne pomocí instrukce TOP:

SELECT TOP 5, product_id, sum(částka*cena) AS summa

Z m_outcome
GROUP BY product_id
ORDER BY suma(částka*cena) DESC;

Dotaz Q054. Zobrazí se pět nejlepších produktů mezi produkty podle objemu prodeje a řádky jsou očíslovány:

SELECT COUNT(*) AS N, b.product_id, b.summa

Z


FROM m_outcome GROUP BY product_id) AS a
VNITŘNÍ SPOJENÍ
(SELECT product_id, sum(částka*cena) AS summa,
suma*10000000+ID_produktu AS id
FROM m_outcome GROUP BY product_id) AS b
ON a.id>=b.id
GROUP BY b.product_id, b.summa
HAVING COUNT(*)ORAD BY COUNT(*);

Dotaz Q055. Následující SQL dotaz ukazuje použití matematických funkcí COS, SIN, TAN, SQRT, ^ a ABS v MS Access SQL:

SELECT (vyberte počet(*) z m_income) jako N, 3,1415926 jako pi, k,

2*pi*(k-1)/N jako x, COS(x) jako COS_, SIN(x) jako SIN_, TAN(x) jako TAN_,
SQR(x) jako SQRT_, x^3 jako "x^3", ABS(x) jako ABS_
OD (SELECT COUNT(*) AS k
Z m_příjem JAKO a VNITŘNÍ JOIN m_příjem JAKO b NA a.idGROUP BY b.id) t;

SQL dotaz. Příklady v MS Access. AKTUALIZACE: 1-10

Žádost U001. Následující SQL dotaz na změnu zvýší ceny zboží s kódem 3 v tabulce m_income o 10%:

AKTUALIZACE m_income SET cena = cena*1.1

WHERE product_id=3;

Žádost U002. Následující aktualizační dotaz SQL zvýší množství všech produktů v tabulce m_income o 22 jednotek, jejichž názvy začínají slovem „Oil“:

AKTUALIZACE m_income SET částka = částka+22

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

Žádost U003. Následující SQL dotaz pro změnu v tabulce m_outcome snižuje ceny veškerého zboží vyráběného společností Sladkoe LLC o 2 procenta:

AKTUALIZACE m_outcome SET cena = cena*0,98

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

Laboratorní práce č.1

SQL: DATA EXTRACT - příkazVYBRAT

Cíl práce:

• seznámit se s příkazy SQL;
• naučit se vytvářet jednoduché SQL dotazy v Accessu pomocí příkazu SELECT;

· použití operátorů IN, BETWEEN, LIKE, IS NULL.

Cvičení№1. Vytvořte dotaz pro výběr všech hodnot polí FIRST NAME a LAST NAME z tabulky STUDENTS v režimu SQL.

VYBERTE JMÉNO, PŘÍJMENÍ

OD STUDENTŮ;

Cvičení№2 . Vytvořte dotaz pro výběr všech sloupců tabulky STUDENTS v režimu SQL.

VYBRAT *

OD STUDENTŮ;

Úkol č. 3. Vytvořte dotaz pro výběr v režimu SQL názvů měst, kde studenti bydlí, informace o nich jsou v tabulce OSOBNÍ ÚDAJE.

VYBERTE VZDÁLENÉ MĚSTO

OD [OSOBNÍ ÚDAJE];

Úkol č. 4. Vytvořte výběrový dotaz v režimu SQL, který načte jména všech studentů s příjmením Ivanov, o kterých jsou informace v tabulce STUDENTI.

VYBERTE PŘÍJMENÍ, JMÉNO

OD STUDENTŮ

WHERE LAST NAME="Ivanov";

Úkol č. 5. Vytvořte výběrový dotaz v režimu SQL pro získání jména a příjmení studentů studujících ve skupině UIT-22 na rozpočtové formě vzdělávání.

VYBERTE PŘÍJMENÍ, JMÉNO

OD STUDENTŮ

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

Úkol č. 6. Vytvořte dotaz v režimu SQL. pro ukázku z tabulky ZKOUŠKA informace o žácích, kteří mají pouze 4. a 5. ročník.

VYBRAT *

Z [ZMĚNAZKOUŠKY]

KDEŠKOLNÍ ZNÁMKAIN(4,5);

Úkol č. 7. Vytvořte režim zanpoc a SQL pro výběr informací o studentech, kteří mají ze zkoušky známku 3 z předmětu IOSU.

VYBRAT *

Z [ZMĚNAZKOUŠKY]

KDEPOLOŽKA=" IOSU"AŠKOLNÍ ZNÁMKANot In (4,5);

Úkol č. 8. Vytvořte dotaz v režimu SQL pro výběr záznamů pro položky, jejichž hodiny jsou mezi 100 a 130.

VYBRAT *

ZPOLOŽKY

KDEHODINKYMEZI 100 A 130;

Úkol č. 9. Vytvořte dotaz v režimu SQL pro výběr z tabulky STUDENTI informace o studentech, jejichž příjmení začíná např. písmenem „C“.

VYBRAT *

ZSTUDENTI

KDEPŘÍJMENÍJAKO"S*";

Závěr: Během laboratorní práce jsme se seznámili s SQL instrukcemi, naučili se vytvářet jednoduché SQL dotazy v Accessu pomocí příkazu SELECT pomocí operátorů IN, BETWEEN, LIKE.

Ukázkové SQL dotazy lze použít k učení a procvičování psaní SQL dotazů v MS Access.

Jeden SQL dotaz může být vnořen do druhého. Poddotaz není nic jiného než dotaz v rámci dotazu. Obvykle se poddotaz používá v klauzuli WHERE. Existují ale i jiné způsoby použití poddotazů.

Dotaz Q011. Zobrazí se informace o produktech z tabulky m_produkt, jejichž kódy jsou také v tabulce m_příjmů:

VYBRAT *
OD m_product
WHERE id IN (SELECT product_id FROM m_income);

Žádost Q012. Zobrazí se seznam produktů z tabulky m_product, jejichž kódy nejsou v tabulce m_outcome:

VYBRAT *
OD m_product
WHERE id NOT IN (SELECT product_id FROM m_outcome);

Žádost Q013. Tento dotaz SQL zobrazí jedinečný seznam kódů a názvů produktů, které jsou v tabulce m_income, ale ne v tabulce m_outcome:

SELECT DISTINCT product_id, title
OD m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
WHERE product_id NOT IN (SELECT product_id FROM m_outcome);

Dotaz Q014. Jedinečný seznam kategorií, jejichž názvy začínají písmenem M, se zobrazí z tabulky m_category:

SELECT DISTINCT titul
OD m_product
WHERE titulek LIKE "M*";

Dotaz Q015. Příklad provádění aritmetických operací na polích v dotazu a přejmenování polí v dotazu (alias). Tento příklad vypočítává náklady = množství*cena a zisk pro každou položku nákladů, za předpokladu, že zisk je 7 procent z prodeje:

částka*cena/100*7 AS zisk
FROM m_outcome;

Dotaz Q016. Analýzou a zjednodušením aritmetických operací můžete zvýšit rychlost provádění dotazu:

SELECT dt, produkt_id, částka, cena, částka*cena AS výsledek_součet,
vysledek_soucet*0,07 AS zisk
FROM m_outcome;

Dotaz Q017. Ke spojení dat z více tabulek můžete použít příkaz INNER JOIN. V následujícím příkladu je v závislosti na hodnotě ctgry_id každá položka v tabulce m_income porovnána s názvem kategorie z tabulky m_category, do které produkt patří:

SELECT c.title, b.title, dt, částka, cena, částka*cena AS příjem_součet
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
ORDER BY c.title, b.title;

Žádost Q018. Funkce jako SUM - součet, COUNT - množství, AVG - aritmetický průměr, MAX - maximální hodnota, MIN - minimální hodnota se nazývají agregační funkce. Přijímají mnoho hodnot a po jejich zpracování vrátí jedinou hodnotu. Příklad výpočtu součtu součinu polí částka a cena pomocí agregační funkce SUM.