Přístupové dotazy jsou primárním nástrojem pro výběr, aktualizaci a zpracování dat v databázových tabulkách. Access v souladu s koncepcí relačních databází používá k provádění dotazů SQL (Structured Query Language). Pomocí instrukcí jazyka SQL je implementován jakýkoli dotaz v Accessu.

Hlavním typem požadavku je výběrový požadavek. Výsledkem tohoto dotazu je nová tabulka, která existuje, dokud není dotaz uzavřen. Záznamy jsou tvořeny kombinací záznamů tabulek, na kterých je dotaz založen. Metoda pro kombinování záznamů tabulky se zadává při definování jejich vztahu v datovém schématu nebo při vytváření dotazu. Výběrové podmínky formulované v dotazu umožňují filtrovat záznamy, které tvoří výsledek spojování tabulek.

V Accessu lze vytvořit několik typů dotazů:

• vzorová žádost- vybírá data z jedné tabulky nebo dotazu nebo několika vzájemně souvisejících tabulek a dalších dotazů. Výsledkem je tabulka, která existuje, dokud není dotaz uzavřen. Záznamy výsledkových tabulek jsou generovány v souladu se zadanými podmínkami výběru a při použití více tabulek kombinací jejich záznamů;
• požadavek na vytvoření tabulky- vybírá data z propojených tabulek a dalších dotazů, ale na rozdíl od výběrového dotazu uloží výsledek do nové trvalé tabulky;
• požadavky na aktualizaci, doplnění, smazání- jsou akční požadavky, v důsledku kterých se mění údaje v tabulkách.

Dotazy v Accessu v návrhovém režimu obsahují datové schéma, které zobrazuje použité tabulky, a dotazovací formulář, ve kterém je navržena struktura tabulky dotazu a podmínky pro výběr záznamů (obr. 4.1).

Pomocí dotazu můžete provádět následující typy zpracování dat:

• zahrnout uživatelem vybraná pole tabulky do tabulky dotazu;
• provádět výpočty v každém z přijatých záznamů;
• vybrat záznamy, které splňují podmínky výběru;
• vytvořit novou virtuální tabulku založenou na kombinování záznamů vzájemně souvisejících tabulek;
• skupinové záznamy, které mají stejné hodnoty v jednom nebo více polích, současně provádějí statistické funkce na jiných polích skupiny a v důsledku toho obsahují jeden záznam pro každou skupinu;
• vytvořit novou databázovou tabulku pomocí dat z existujících tabulek;
• aktualizovat pole ve vybrané podmnožině záznamů;
• odstranit vybranou podmnožinu záznamů z databázové tabulky;
• přidat vybranou podmnožinu záznamů do jiné tabulky.

Dotazy v Accessu slouží jako zdroje záznamů pro další dotazy, formuláře a sestavy. Pomocí dotazu můžete shromáždit kompletní informace pro generování dokumentu předmětová oblast z několika tabulek, pak pomocí něj vytvořte formulář - elektronickou reprezentaci tohoto dokumentu. Pokud průvodce vytvoří formulář nebo sestavu na základě několika vzájemně souvisejících tabulek, automaticky se pro ně vygeneruje dotaz jako zdroj záznamů.
Chcete-li to posílit, podívejte se na video tutoriál.

Access DBMS používá dva typy dotazů: QBE – dotaz podle vzorku a SQL(Structured Query Language) - strukturovaný dotazovací jazyk. Vzorový požadavek se vygeneruje vyplněním speciálního formuláře požadavku v okně „Tvůrce dotazů“. SQL – dotazy jsou vytvářeny programátory ze sekvence SQL – instrukce. SQL je generováno zpravidla programátory na formuláři dotazu, který se otevře příkazem „Query Builder“ na záložce „Vytvoření“ a z nabídky Zobrazit je vybrán „Režim SQL“. Jazyk SQL je navržen pro práci s daty, tzn. pro vytváření, úpravu a správu dat v relačních databázích.

Je třeba poznamenat, že existuje několik režimů dotazů SQL (dotazy režimu SQL ANSI-89 a ANSI-92 SQL), které vyhovují standardům ANSI-89 SQL a ANSI-92 SQL.

Instrukce obsahují popis datové sady v SQL. SQL příkazy sestávají z klauzulí (SELECT, FROM, WHERE atd.). Nabídky v jazyce SQL se skládají z pojmů (operátory nebo příkazy, identifikátory, konstanty atd.). Příkaz začíná příkazem (jeden z příkazů SELECT, CREATE, INSERT, UPDATE, DELETE atd.) a končí středníkem. Základní SQL příkazy: SELECT, FROM a WHERE.

Například příkaz SQL:
SELECT Students.StudentCode
OD studentů;
se skládá z doložky „SELECT Students.StudentCode“ a doložky „FROM Students“.

Klauzule SELECT obsahuje operátor VYBERTE a identifikátor"Students.Student Code". Zde před úplným názvem pole "StudentCode" je uveden název tabulky "Students" databáze. SELECT - určuje pole, které obsahuje požadovaná data. Klauzule FROM se skládá z klauzule FROM a identifikátoru "Students". FROM - definuje tabulku, která obsahuje pole specifikovaná v klauzuli SELECT.

Je třeba poznamenat, že při vytváření dotazu v SQL musíte vzít v úvahu jeho syntaxi. Přestože syntaxe SQL je založena na syntaxi v angličtině, ale pro různé DBMS se syntaxe jazykových verzí SQL může lišit.

Existuje několik typů dotazů: výběr, aktualizace, přidávání a mazání záznamů, křížový dotaz, vytváření a mazání tabulek, spojování tabulek atd. Nejběžnější je vzorová žádost. Výběrové dotazy slouží k výběru požadované uživatelem informace obsažené v tabulkách. Jsou vytvořeny pouze pro související tabulky.

Chcete-li zobrazit výběrové dotazy SQL v Access 2003 nebo 2007 DBMS, musíte aktivní okno při návrhu dotazu podle příkladu (obr. 1) spusťte příkaz SQL View/Mode.

Rýže. 1.

Pořiďme si SQL příkaz (SELECT) pro vzorkování dat z databáze Access 2003 podle výkonnostního kritéria studenta "Stupeň=5" (obr. 2).

Rýže. 2.

Jak vyplývá z příkazu SELECT (obr. 1), popisuje datovou sadu v jazyce SQL: SELECT - definuje názvy polí, kterým předcházejí názvy tabulek, které data obsahují; FROM - definuje tabulky a jejich vztahy přes klíčová pole tabulek (k tomu slouží konstrukce INNER JOIN ... ON), na základě kterých se vybírají data; WHREME - definuje podmínky pro výběr oborů; ORDER BY - určuje, jak seřadit vzestupně (ve výchozím nastavení se provádí vzestupně) hodnoty pole "Příjmení" tabulky "Studenti".

Jak vyplývá z pokynů pro načítání dat z databáze, jazyk SQL popisuje, co je třeba získat z databáze, zatímco provádění je svěřeno DBMS, protože jazyk SQL nemá vlastní prostředky pro řízení provádění programu.

Laboratorní práce č.1

SQL: DATA EXTRACT - příkazVYBRAT

Cíl práce:

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

· 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, informace o nich jsou 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 seznámili se s instrukcemi SQL, naučili se vytvářet jednoduché SQL dotazy v Accessu pomocí příkazu SELECT pomocí operátorů IN, BETWEEN, LIKE.

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. Výstupem tohoto SQL dotazu unikátní seznam kódy a názvy zboží, jehož kódy jsou v tabulce m_income, ale které nejsou 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:

Cena, částka*cena AS výsledek_součet,
částka*cena/100*7 AS zisk
FROM m_outcome;

Dotaz Q016. Po analýze a zjednodušení aritmetické operace, můžete zvýšit rychlost provádění dotazu:

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ývá agregační funkce. Přijímají mnoho hodnot a po jejich zpracování vrátí jednu hodnotu. Příklad výpočtu součtu součinu polí množství a ceny pomocí agregační funkce SOUČET:

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;

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

VYBRAT
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:

VYBRAT
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. Spočítejte si celkové množství 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.

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é částky aktivovaného zboží v kategorii „Pekařské výrobky“ vyřešíme pomocí následujícího algoritmu:
- přiřaďte každý záznam v tabulce m_income v závislosti na hodnotě jeho product_id z tabulky m_category s názvem kategorie;
- vyberte záznamy, pro které je kategorie „Pekárenské výrobky“;
- vypočítat částku příjmu = množství*cena.

SELECT Suma(částka*cena) JAKO součet_příjmů
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 použije 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ýsledek_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
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 má například některá tabulka A 100 záznamů a tabulka B 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:

SELECT *FROM m_příjem, m_výsledek;

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ů

Dotaz 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,
SUM(částka*cena) JAKO výsledek_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á. Je třeba také poznamenat, že tento dotaz je pouze příkladem použití poměrně složitých dotazů jako poddotazu. Výkon tohoto SQL dotaz na velký objemúdaje jsou pochybné:

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 (VYBRAT ID_produktu Z 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í booleovské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<500,"малая","большая") AS mark
Z 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í,
PŘEPNOUT(částka<500,"малая",amount>=500 "velký") AS značka
Z m_příjmu;

Dotaz Q042. <300 не выполняется, то проверяется является ли количество товаров в партии меньше 500. Если размер партии меньше 500, то она считается «средней». В противном случае партия считается «большой»:

SELECT dt, product_id, množství,
IIF(částka<300,"малая",
IIF(částka<1000,"средняя","большая")) AS mark
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 částka podmínkou<300 не выполняется, то проверяется является ли количество товаров в партии меньше 500. Если размер партии меньше 500, то она считается «средней». В противном случае партия считается «большой»:

SELECT dt, product_id, množství,
PŘEPNOUT(částka<300,"малая",
množství<1000,"средняя",
částka>=1000,"velká") značka AS
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<150,"малая",
IIf(částka*cena<300,"средняя","большая")) AS Category
OD m_outcome) AS t
GROUP BY Category;

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,
PŘEPNOUT(m<4,1,m<7,2,m<10,3,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)

Žádost 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_příjmu a VNITŘNÍ PŘIPOJENÍ m_příjmu b NA a.id<= b.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 (*)<=5
ORDER 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říjmu JAKO a VNITŘNÍ PŘIPOJENÍ m_příjmu JAKO b NA a.id<=b.id
GROUP BY b.id) t;

SQL je jedním z nejběžnějších programovacích jazyků pro vytváření a správu databáze a také pro provádění různých akcí se samotnými daty.

Jak ukazuje praxe, je docela snadné se naučit a maximálně využívá standardní anglickou slovní zásobu. Jako každý jiný programovací jazyk má SQL svou vlastní logiku a syntaxi, soubor základních příkazů a pravidel pro jejich použití.

Klasifikace SQL příkazů

Všechny standardní lze zvážit na základě jejich účelu. Následující soubory mohou být brány jako základ pro neformální klasifikaci:

Příkazy pro vytváření dotazů.

Příkazy vestavěných procedur a funkcí.

Příkazy pro triggery a systémové tabulky.

Sady kombinací pro práci s datovými a řetězcovými proměnnými.

Příkazy pro práci s daty a tabulkami.

V této klasifikaci lze pokračovat donekonečna, ale hlavní sady příkazů SQL budou sestaveny přesně na základě těchto typů.

Při úvahách o klasifikaci jazyka nelze nezmínit, že je univerzální, o čemž svědčí rozsah jeho použití. Tento programovací jazyk a jeho varianty se používají nejen ve standardním prostředí, ale i v jiných programech, které jste tak či onak používali.

Na rozsah použití SQL lze nahlížet z pohledu kancelářského softwaru, konkrétně MicrosoftAccess. Tento jazyk, respektive jeho verze - MySQL, umožňuje spravovat databáze na internetu. Dokonce i vývojové prostředí Oracle používá v jádru svých dotazů příkazy SQL.

Použití SQL v MicrosoftAccess

Jedním z nejjednodušších příkladů použití jazyka pro programování databází je softwarový balík Microsoft Office. Studium tohoto softwarového produktu je součástí školního předmětu informatika a v jedenáctém ročníku se probírá systém správy databází MicrosoftAccess.

Právě učením této aplikace se studenti seznámí s jazykem pro vývoj databází a získají základní porozumění všemu, co s tím souvisí. Příkazy Access SQL jsou samozřejmě docela primitivní, pokud je vezmete v úvahu na profesionální úrovni. Provádění takových příkazů je velmi jednoduché a jsou vytvářeny v editoru vlastního kódu.

Podívejme se na konkrétní příklad:

SELECT Pe_Příjmení

WHERE Pe_Name = "Marie";

Na základě syntaxe příkazu můžete pochopit, že vrátí uživateli příjmení osoby, v tomto případě ženy jménem Marie, které je uloženo v tabulce databáze Kontakty.

Ačkoli je použití SQL v Accessu omezené, někdy mohou takové jednoduché dotazy značně zjednodušit daný úkol.

Použití SQL příkazů v Oracle

Oracle je pravděpodobně jediným vážným konkurentem Microsoft SQL Server. Právě toto vývojové a řídící prostředí neustále vede ke zlepšování funkcí softwarového produktu Microsoftu, protože konkurence je motorem pokroku. Navzdory neustálé konkurenci SQL týmy Oracle replikují SQL. Stojí za zmínku, že ačkoli je Oracle považován za téměř úplnou kopii SQL, logika tohoto systému a jazyka jako celku je považována za jednodušší.

Systém Oracle při použití určité sady příkazů nemá tak složitou strukturu. S ohledem na možnosti těchto prostředí pro vývoj databází nemá Oracle složitou strukturu vnořených dotazů.

Tento rozdíl umožňuje mnohonásobně zrychlit práci s daty, ale naopak vede v některých individuálních případech k iracionálnímu využívání paměti. Struktura Oracle je postavena především na dočasných tabulkách a jejich použití. Jako příklad: SQL příkazy v tomto systému jsou konstruovány analogicky se standardy samotného jazyka SQL, i když se od něj mírně liší.

SELECTCONCAT(CONCAT(CONCAT('Zaměstnanec', jméno), CONCAT(SUBSTR(jméno, 0, 1), SUBSTR(otch, 0, 1)), CONCAT('přijato', přijato)) FROM zaměstnanců WHERE přijato > do_datum ( '01.01.80','dd.mm.yyyy');

Tento dotaz vrátí údaje o zaměstnancích, kteří byli přijati v určitém časovém období. Přestože je struktura dotazů odlišná, provádění příkazů SQL v těchto systémech je až na drobné detaily podobné.

Použití SQL na internetu

S příchodem World Wide Web, tedy internetu, se rozsah použití jazyka SQL rozšiřuje. Jak víte, mnoho informací je uloženo v síti, ale nejsou umístěny náhodně, ale jsou umístěny na stránkách a serverech podle určitých kritérií.

Databáze jsou přímo zodpovědné za ukládání informací na internetu, stejně jako jinde, a webové stránky jsou systémy správy. Webové stránky a jejich programový kód jsou zpravidla organizovány v různých programovacích jazycích, ale databáze jsou založeny na jedné z odrůd SQL, konkrétně na jazyku pro tvorbu databází, orientovaném na webová rozhraní MySQL.

Syntaxe a základní sada příkazů tohoto jazyka zcela kopírují známé SQL, ale s některými vlastními doplňky, které jej odlišují od Microsoft tSQL Server.

Příkazy SQL jsou si zcela podobné nejen syntaxí, ale i standardní sadou funkčních slov. Jediný rozdíl je ve volání a strukturování požadavku. Můžete například zvážit požadavek na vytvoření nové tabulky, toto je první věc, kterou se děti učí ve školách informatiky:

$link = mysqli_connect("localhost", "root", "", "tester");

if (!$link) die("Chyba");

$query = "vytvořit uživatele tabulky(

přihlásit se VARCHAR(20),

heslo VARCHAR(20)

if (mysqli_query($link, $dotaz)) echo "Tabulka byla vytvořena.";

elseecho "Tabulka nebyla vytvořena: ".mysqli_error();

mysqli_close($odkaz);

V důsledku provedení takového dotazu můžete získat novou tabulku „Uživatelé“, která bude mít dvě pole: přihlašovací jméno a heslo.

Syntaxe byla změněna pro web, ale je založena na příkazech MicrosoftSQLServer.

Vytváření dotazů na MicrosoftSQLServer

Výběr z tabulek konkrétní datové sady je jedním z hlavních úkolů SQL. Pro takové operace je k dispozici příkaz select v SQL. To je to, o čem bude řeč níže.

Pravidla pro konstrukci příkazu jsou velmi jednoduchá a samotný příkaz select v SQL je konstruován následovně. Existuje například tabulka, která obsahuje údaje o zaměstnanci, která má například název Osoba. Nastavme si úkol, že z tabulky je potřeba vybrat údaje o zaměstnancích, jejichž datum narození je v intervalu od 1. ledna do 1. března aktuálního roku včetně. Pro takový výběr je potřeba provést SQL příkaz, který bude obsahovat nejen standardní konstrukci, ale i podmínku výběru:

Vyberte * z osoby

Kde P_BerthDay >= „01/01/2016“ a P_BerthDay<= ‘03/01/2016’

Provedením takového příkazu se vrátí všechna data o zaměstnancích, jejichž narozeniny jsou ve vámi zadaném období. Někdy může být úkolem zobrazit pouze příjmení, jméno a patronymii zaměstnance. Chcete-li to provést, musíte požadavek sestavit trochu jinak, například takto:

SelectP_Name - jméno

P_SurName - příjmení

P_Patronimic - patronymic

Kde P_BerthDay >= „01/01/2016“ a P_BerthDay<= ‘03/01/2016’

To je však jen výběr něčeho. Ta v podstatě nic neovlivňuje, pouze poskytuje informace. Pokud se však rozhodnete brát jazyk SQL vážně, budete se muset naučit, jak provádět změny v databázích, protože bez toho je jejich sestavení prostě nemožné. Jak se to dělá, bude diskutováno níže.

Základní SQL příkazy pro změnu dat

Syntaxe jazyka je vytvořena nejen pro provádění dotazů, ale také pro manipulaci s daty. Hlavním úkolem databázového programátora je psát skripty pro výběry a sestavy, ale někdy je nutné provést změny v tabulkách. Seznam příkazů SQL pro takové akce je malý a skládá se ze tří hlavních příkazů:

Vložit (trans. Insert).

Aktualizace (překl. Aktualizace).

Smazat (překl. Smazat).

Účel těchto příkazů je snadné určit; vše, co musíte udělat, je přeložit jejich název. Tyto příkazy se snadno používají a mají jednoduchou strukturu, ale stojí za zmínku, že některé z nich mohou při nesprávném použití způsobit nenapravitelné poškození databáze.

Před použitím takových příkazů MSSQL je zpravidla nutné si je promyslet a vzít v úvahu všechny možné důsledky jejich provedení.

Po naučení těchto příkazů můžete naplno začít pracovat s databázovými tabulkami, a tím je upravovat a zavádět některé nové proměnné nebo mazat staré.

Vložit příkaz

Pro vložení dat do tabulky je nejbezpečnějším příkazem Vložit. Nesprávně vložená data lze vždy smazat a znovu vložit do databáze.

Příkaz Vložit je určen pro vkládání nových dat do tabulky a umožňuje přidat jak kompletní sadu, tak selektivně.

Zvažte například příkaz insert do dříve popsané tabulky Person. Abyste mohli zadat data do tabulky, musíte spustit SQL příkaz, který vám umožní vložit všechna data do tabulky nebo ji selektivně vyplnit.

Vložit do osoby

Vyberte ‚Grigoriev‘, ‚Vitalij‘, ‚Petrovich‘, ‚01/01/1988‘

Příkazy tohoto typu automaticky vyplní všechny buňky tabulky zadanými údaji. Jsou situace, kdy zaměstnanec nemá prostřední jméno, řekněme přišel pracovat z Německa jako výměnný student. V tomto případě je potřeba provést příkaz pro vložení dat, který do tabulky zanese pouze to, co je potřeba. Syntaxe takového příkazu bude následující:

Insertintoperson(P_Jméno, P_Příjmení,P_Den narození)

Hodnoty ('David', 'Hook', '02/11/1986')

Takový příkaz vyplní pouze zadané buňky a všechny ostatní budou mít hodnotu null.

Příkaz pro změnu dat

Chcete-li změnit data celého řádku i některých buněk, použijte příkaz Update SQL. Takový příkaz musíte provést pouze s určitou podmínkou, konkrétně uveďte přesně, který řádek podle čísla musíte provést změny.

Příkaz Update SQL má jednoduchou syntaxi. Pro správné použití musíte uvést, které údaje by měly být změněny ve kterém sloupci a ve kterém záznamu. Dále vytvořte skript a spusťte jej. Podívejme se na příklad. Potřebujeme změnit datum narození Davida Hooka, který je v tabulce zaměstnanců uveden jako číslo 5.

Nastavte P_BerthDay = '02/10/1986', kde P_ID = 5

Podmínka (v tomto skriptu) vám neumožní změnit datum narození ve všech záznamech tabulky, ale aktualizuje pouze ty potřebné.

Je to tento příkaz, který programátoři používají nejčastěji, protože vám umožňuje měnit data v tabulce, aniž by došlo k významnému poškození všech informací.

Příkazy pro použití vestavěných procedur a funkcí

Pomocí jazyka SQL můžete nejen vytvářet dotazy, ale také vytvářet vestavěné mechanismy pro práci s daty. Zpravidla existují chvíle, kdy potřebujete použít výběr, který byl zapsán dříve v těle jednoho požadavku.

Pokud soudíte logicky, pak je třeba zkopírovat text výběru a vložit jej na správné místo, ale vystačíte si s jednodušším řešením. Uvažujme příklad, kdy se na pracovním rozhraní zobrazí tlačítko pro tisk sestavy, řekněme v Excelu. Tato operace bude provedena podle potřeby. K takovým účelům se používají vestavěné uložené procedury. Příkazy jsou v tomto případě uzavřeny v proceduře a volány pomocí příkazu SQLExec.

Předpokládejme, že byl vytvořen postup pro zobrazení data narození zaměstnanců z dříve popsané tabulky Osoba. V tomto případě není potřeba psát celou žádost. Chcete-li získat potřebné informace, jednoduše spusťte příkaz Exec [název procedury] a předejte parametry nezbytné pro vzorkování. Jako příklad můžeme uvažovat mechanismus pro vytvoření procedury tohoto charakteru:

CREATEPROCEDUREPrintPerson

@DB smalldatetime

@DE smalldatetime

VYBERTE * z osoby

FROM HumanResources.vEmployeeDepartmentHistory

KDE P_BerthDay >= @DB a P_BerthDay<= @DE

ANDEndDateISNULL;

Tento postup vrátí všechny informace o zaměstnancích, jejichž narozeniny budou v daném časovém období.

Organizace integrity dat. Spouštěče

Některé příkazy MS SQL, dalo by se říci konstrukty, umožňují nejen organizovat manipulaci s daty, ale také zajistit jejich integritu. Pro takové účely jazyk obsahuje systémové konstrukce, které vytváří samotný programátor. Jedná se o takzvané spouštěče, které mohou zajistit kontrolu dat.

V tomto případě se k organizaci kontroly podmínek používají standardní příkazy SQL dotazu. Ve triggerech si můžete vytvořit spoustu podmínek a omezení pro práci s daty, které pomohou kontrolovat nejen přístup k informacím, ale také zakázat mazání, změnu či vkládání dat.

Neexistují žádná omezení pro typy příkazů SQL, které lze použít ve spouštěči. Podívejme se na příklad.

Pokud popíšeme mechanismus pro vytvoření triggeru, pak jsou zde typy SQL příkazů stejné jako při vytváření procedury. Algoritmus samotný bude popsán níže.

Nejprve musíme popsat servisní příkaz pro vytváření triggerů:

Uvádíme, pro jakou datovou operaci (v našem případě jde o operaci úpravy dat).

Dalším krokem je zadání tabulek a proměnných:

deklarovat @ID int. @Date smalldatetime @nID int. @nDatesmalldatetime

DEdeklarujte kurzor C1 pro výběr P_ID, P_BerthDay z Vloženo

DEdeklarujte kurzor C2 pro výběr P_ID, P_BerthDay z odstraněno

Nastavíme kroky výběru dat. Následně do těla kurzorů zapíšeme podmínku a reakci na ni:

pokud @ID = @nID a @nDatum = "01/01/2016"

sMasseges "Operaci nelze provést. Datum není vhodné"

Za zmínku stojí, že spoušť lze nejen vytvořit, ale také dočasně deaktivovat. Tuto manipulaci může provést pouze programátor provedením příkazů SQL SERVER:

altertablePERSONdisabletriggerall - pro deaktivaci všech spouštěčů vytvořených pro tuto tabulku, a tedy altertablePERSONenabletriggerall - pro jejich povolení.

Tyto základní SQL příkazy jsou nejčastěji používané, ale jejich kombinace se mohou měnit. SQL je velmi flexibilní programovací jazyk a dává vývojáři maximální možnosti.

Závěr

Ze všeho výše uvedeného můžeme vyvodit jediný závěr: znalost jazyka SQL je prostě nezbytná pro ty, kteří se chystají vážně zapojit do programování. Je základem všech operací prováděných jak na internetu, tak v domácích databázích. Proto musí budoucí programátor znát mnoho příkazů tohoto jazyka, neboť jen s jejich pomocí lze takříkajíc komunikovat s počítačem.

Samozřejmě existují nevýhody, jako u všeho na tomto světě, ale jsou tak nepodstatné, že v porovnání s výhodami prostě blednou. Mezi všemi programovacími jazyky je SQL prakticky jediný svého druhu, protože je univerzální a znalost psaní skriptů a kódů je základem téměř všech webů.

Za hlavní výhodu SQL lze bezesporu považovat jeho jednoduchost, protože koneckonců je součástí školního vzdělávacího programu. Zvládne to i začínající programátor, který jazykům moc nerozumí.