Vestavěné funkce SQL navrženy pro usnadnění a urychlení zpracování dat. Zvláštností je, že je lze specifikovat přímo ve výrazu. Všechny vestavěné funkce lze podmíněně rozdělit do skupin.

Matematické funkce:

břišní svaly(význam) - vrátí absolutní hodnotu čísla;

Kolo(hodnota, přesnost) - vrátí číselnou hodnotu zaokrouhlenou nahoru na zadaný argument přesnost počet desetinných míst;

PODEPSAT(význam) - vrátí mínus, pokud je číslo záporné, a plus, jinak;

NAPÁJENÍ(hodnota, stupeň) – umocní číslo;

SQRT(význam) - extrahuje druhou odmocninu čísla;

STROP(význam)- vrátí nejbližší celé číslo větší nebo rovné hodnotě;

- PODLAHA(význam) Vrátí nejbližší celé číslo menší nebo rovné hodnotě.

Funkce řetězců:

ASCII(čára) - vrací ASCII kód prvního znaku řetězce;

CHAR(číslo) – vrátí znak podle ASCII kód;

LEN (čára) – vrací délku řetězce ve znacích, bez mezer na konci;

LTRIM(čára)/ RTRIM(čára)- odstraní mezery na začátku/konci řetězce;

VLEVO, ODJET(řetězec, číslo)/ RSVĚTLO(řetězec, číslo)- vrátí zadaný argument číslo počet znaků v řetězci od levého/pravého okraje;

SUBSTRING(čára, pozice, délka) - vrátí podřetězec zadané délky z řetězce počínaje zadanou pozicí;

DOLNÍ(řádek) /HORNÍ(čára) - vrátí řetězec převedený na nižší / velká písmena atd.

Funkce pro práci s daty:

GETDATE() - Vrátí hodnotu, která obsahuje datum a čas počítače, na kterém běží instance SQL Server;

DEN(datum_hodnoty)– vrátí číslo ze zadaného data;

MĚSÍC(datum_hodnoty)- vrátí číslo měsíce od zadaného data;

ROK(datum_hodnoty)- vrátí hodnotu roku od zadaného data;

DATENANE( část, datum_hodnoty) - vrací znakový řetězec, představující zadanou část ( den, Měsíc, Hodinaatd.) od uvedeného data;

DATEPART( část, datum_hodnoty) - vrátí celé číslo představující zadanou část ( den, Měsíc, Hodinaatd.) od daného data.

Funkce převodu datových typů

OBSAZENÍ (význam TAK JAKO datový typ)

KONVERTOVAT(datový typ, význam)

Argument význam ve funkcích určuje hodnotu, která má být převedena.

7.3. Příkazy jazyka definice dat

Jazyk definice dat obsahuje příkazy pro vytváření, úpravy a mazání databáze a jejích objektů.

Vytvořte tabulku

Vytvoření nové tabulky se provádí příkazem VYTVOŘIT TABULKU. Příkaz popisuje strukturu tabulky, každý sloupec tabulky a omezení integrity, která musí být pro tabulku nastavena.

Syntaxe příkazu:

VYTVOŘIT TABULKU název_tabulky (( popis_sloupce |vypočítaný_název_sloupce TAK JAKO výraz | table_level_integrity_constraints) [, ...])

Název tabulky je identifikátor s maximální délkou 128 znaků.

Tabulka může obsahovat počítaný sloupec, v tomto případě je hodnota sloupce určena výrazem uloženým ve struktuře tabulky. Vypočítaný sloupec nelze upravit, takže nemůže mít hodnotu NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY nebo DEFAULT.

Syntaxe pro popis sloupce tabulky je:

název_sloupce datový typ[(velikost)]

[(VÝCHOZÍ výchozí_hodnota | IDENTITY [(hodnota, krok)]}]

[column_level_integrity_constraints]

VÝCHOZÍ - umožňuje zadat hodnotu přiřazenou sloupci v nově přidaném záznamu.

IDENTITA Označuje, že se vytváří sloupec s povoleným automatickým číslováním (sloupec čítače). V tabulce lze definovat pouze jeden sloupec čítače. Parametr value určuje počáteční hodnotu čítače a parametr step určuje krok přírůstku. Pokud tyto parametry nejsou nastaveny, mají hodnotu 1. IDENTITY lze nastavit pouze pro sloupce, které mají celočíselný nebo desítkový typ. Vkládání hodnot do sloupce IDENTITY není povoleno.

Existují dvě skupiny omezení integrity, které zpracovává DBMS:

Deklarativní omezení integrity, která jsou deklarována při vytvoření nebo úpravě tabulky;

Omezení integrity procedury, která jsou zpracována spouštěči.

Deklarativní omezení integrity mohou být omezení na úrovni tabulky a omezení na úrovni tabulky. Omezení na úrovni sloupce se vztahuje pouze na jeden sloupec. Každé deklarativní omezení integrity lze pojmenovat.

Popis omezení na úrovni sloupců má následující syntaxi:

((PRIMÁRNÍ KLÍČ | UNIKÁTNÍ | NOT NULL) | REFERENCE CIZÍHO KLÍČE název_tabulky( název_sloupce)

|ZKONTROLUJTE booleovský_výraz)

Název omezení integrity dat musí být v rámci databáze jedinečný. Zvažte omezení, která lze definovat na úrovni sloupců:

Omezení PRIMÁRNÍ KLÍČ. Všechny hodnoty primárního klíče tabulky musí být jedinečné a nesmí mít hodnotu null. Tabulka může mít pouze jeden primární klíč. Pokud je složený, pak jsou omezení integrity primárního klíče nastavena na úrovni tabulky;

Omezení jedinečnosti hodnoty sloupce UNIQUE. To znamená, že tabulka nemůže mít dva záznamy, které mají v daném sloupci stejnou hodnotu;

Omezení NOT NULL, které brání sloupci v ukládání hodnoty NULL;

Omezení cizího klíče FOREIGN KEY (omezení referenční integrity). Pro sloupec, který je cizím klíčem, zadejte pomocí REFERENCE název tabulky, na kterou se vytváří odkaz, a název sloupce této tabulky, na který bude odkazováno. Taková tabulka je hlavní (nadřazenou) tabulkou ve vztahu k vytvořené tabulce. Sloupec v hlavní tabulce, jehož hodnoty se propojují, musí mít omezení PRIMARY KEY.

Pokud se cizí klíč tabulky skládá z více polí, musí být omezení FOREIGN KEY specifikováno na úrovni tabulky. V tomto případě byste měli vypsat všechny sloupce obsažené v cizím klíči, uvést název hlavní tabulky a názvy sloupců hlavní tabulky, na které cizí klíč odkazuje.

Referenční integrita stanoví pravidla pro přidávání a úpravu dat v tabulce pomocí cizího klíče a jeho odpovídajícího omezení primárního klíče. Klauzule ON UPDATE a ON DELETE pro cizí klíč definují následující pravidla pro úpravu souvisejících dat:

NO ACTION - umožňuje změnit (smazat) pouze ty hodnoty v hlavní tabulce, které nemají odpovídající hodnoty cizího klíče v podřízené tabulce. Toto pravidlo je ve výchozím nastavení platné;

CASCADE znamená, že každá hodnota cizího klíče podřízené tabulky bude automaticky změněna (smazána) při změně hodnoty primárního klíče nadřazené tabulky;

SET NULL znamená, že v případě změny (smazání) primárního klíče nadřazené tabulky budou ve všech odkazujících řádcích podřízené tabulky hodnotám cizího klíče automaticky přiřazeny hodnoty NULL;

SET DEFAULT znamená, že v případě změny (smazání) primárního klíče nadřazené tabulky budou ve všech odkazujících řádcích podřízené tabulky hodnotám cizího klíče automaticky přiřazeny výchozí hodnoty.

Dokončeme příklad tréninkové databáze „Univerzita“, jejíž návrh byl zvažován v kap. 4.3 tabulky DISCIPLÍNA a VŠEOBECNÉ PROHLÁŠENÍ. Tabulky 6 a 7 popisují logickou strukturu tabulek.

Tabulka 6

Logická struktura informačního objektu DISCIPLINE

Tabulka 7

Logická struktura informačního objektu OBECNÉ PROHLÁŠENÍ

		Klíčové znamení	Formát pole
	název				Přesnost
Číslo knihy záznamů	Číslo zapsané žákovské knížky		text
Kód disciplíny	Kód disciplíny		Číselné	dlouhé celé číslo
			číselné

Uveďme požadavky na vytvoření tabulek v souladu s tím, který je znázorněn na obr. 35 infoologický databázový model.

Rýže. 35. Schéma databáze "Univerzita"

Jak je vidět z databázového schématu, tabulka FAKULTA je nezávislá tabulka, proto se vytváří jako první. Požadavek na vytvoření tabulky s přihlédnutím k popisu logické struktury v tabulce. 4 (str. 61) bude vypadat takto:

CREATE TABLE fakulta

([číslo oddělení] tinyint PRIMARY KEY , [název oddělení] char(50))

Tabulka SPECIALTY je také nezávislá, vytváříme ji jako druhou. Při vytváření dotazu používá popis logické struktury v tabulce. 5 (str. 62).

VYTVOŘIT TABULKU [specialita] (

[speciální číslo] int PRIMÁRNÍ KLÍČ,

[název specializace] char (60),

[náklady na vzdělání])

Tabulka GROUP je závislá na tabulce FAKULTA a SPECIALITA. Při vytváření dotazu používáme tabulku 3 (str. 61) a bereme v úvahu, že sloupce číslo fakulty a speciální číslo jsou cizí klíče:

VYTVOŘIT TABULKU [skupina] (

[číslo skupiny] smallint PRIMÁRNÍ KLÍČ,

[číslo speciality] int ZAHRANIČNÍ KLÍČOVÉ REFERENCE speciality( pokoj, místnost speciální- zprávy)ON DELETE CASCADE ON UPDADE CASCADE,

[číslo fakulty] tinyint ZAHRANIČNÍ KLÍČOVÉ REFERENCE fakulta( pokoj, místnost fakulta) ON DELETE CASCADE ON UPDADE CASCADE, [číslo kurzu] tinyint)

Tabulka STUDENT je tabulka závislá na GROUP. Na základě údajů v tabulce 2 (str. 60) provedeme dotaz. Také si všimneme, že sloupec číslo skupiny jsou cizí klíče:

VYTVOŘIT TABULKU [student] (

[číslo skupiny] smallint NOT NULL CIZÍ KLÍČOVÉ REFERENCE group( pokoj, místnost skupiny) ,

[příjmení] char(15) NOT NULL ,

[datum narození] datum a čas NOT NULL ,

[komerční] bit NOT NULL ,

[registrační jméno] char(9))

Údaje v tabulce GENERAL RECORD závisí na tabulkách STUDENT a DISCIPLINE. V této tabulce je primární klíč složený a každý ze sloupců primárního klíče je cizí klíč (viz Tabulka 7 a Obrázek 35).

Použijme popis logické struktury tabulky disciplín uvedený v tabulce 6 a položme dotaz:

VYTVOŘIT TABULKU [disciplína] (

[kód disciplíny] int PRIMÁRNÍ KLÍČ,

[název disciplíny] char(50))

Nyní můžete vytvořit dotaz pro vytvoření tabulky obecných příkazů. Protože primární klíč tabulky je složený klíč, musí být omezení PRIMARY KEY nastaveno na úrovni tabulky. Nastavme například omezení FOREIGN KEY také na úrovni tabulky. Žádost bude vypadat takto:

VYTVOŘIT TABULKU [obecný list] (

[disciplinární řád] int,

[číslo v klasifikační knize] char(8),

[stupeň] NENÍ NULL , PRIMÁRNÍ KLÍČ ([kód disciplíny],[číslo třídní knihy]), CIZÍ KLÍČ ([kód disciplíny]) ODKAZY [disciplína] ([kód disciplíny]), CIZÍ KLÍČ ([číslo třídní knihy]) REFERENCE [student ] ([číslo klasifikační knihy]))

Změna struktury tabulky

Změna struktury tabulky se provádí příkazem ALTER TABLE. Pomocí příkazu můžete měnit vlastnosti existujících sloupců, mazat je nebo přidávat do tabulky nové sloupce, spravovat omezení integrity, a to jak na úrovni sloupců, tak na úrovni tabulky. Přiřazení mnoha parametrů a klíčových slov je podobné jako přiřazení odpovídajících parametrů a klíčových slov příkazu CREATE TABLE.

Smazání tabulky

Zrušení tabulky se provádí pomocí příkazu DROP TABLE. Syntaxe příkazu:

DOP STŮL stůl

Například dotaz na odstranění tabulky STUDENT vypadá takto:

DROP TABLE Student

Odstranění tabulky BY MĚLO brát v úvahu vztahy vytvořené v databázi mezi tabulkami. Pokud jiná tabulka odkazuje na tabulku, která je odstraňována pomocí omezení integrity FOREIGN KEY, pak DBMS nepovolí její odstranění.

Vytvořte index

Indexy se používají k urychlení přístupu ke konkrétním datům v databázové tabulce. Index je struktura, která seřadí hodnoty v jednom nebo více sloupcích databázové tabulky, jako je například sloupec Příjmení tabulky STUDENT. Pokud hledáte konkrétního studenta podle příjmení, rejstřík vám pomůže získat informace, které potřebujete, rychleji než prohledávat všechny řádky v tabulce.

Index poskytuje ukazatele na datové hodnoty uložené v konkrétních sloupcích tabulky a uspořádává tyto ukazatele podle určeného pořadí řazení. Vyhledávání dat v tabulce pomocí rejstříku je podobné vyhledávání v rejstříku v knize. Nejprve se vyhledá konkrétní hodnota v indexu a poté se provede odpovídající skok přes ukazatel na řádek obsahující tuto hodnotu.

Index se vytvoří pomocí příkazu CREATE INDEX:

VYTVOŘIT INDEX

název_ index ON jméno _tabulky(sloupec [,…])

kde UNIQUE označuje, že index by měl ukládat pouze jedinečné hodnoty.

Index lze vytvořit na jednom nebo více sloupcích (složený index). Složené indexy umožňují rozlišovat mezi záznamy, které mají stejnou hodnotu ve stejném sloupci.

Příklad: Vytvořte složený index v tabulce STUDENT pro pole Příjmení a Datum narození

VYTVOŘIT INDEX Ind_Fam ZAPNUTO

Student(Příjmení, [Datum narození] DESC)

Index na tabulce byste měli vytvořit pouze v případě, že máte v úmyslu často dotazovat data v indexovaných sloupcích. Indexy zabírají místo na disku a zpomalují přidávání, mazání a aktualizace řádků.

Odstranění indexu tabulky

Příkaz DROP odstraní index z tabulky. Syntaxe příkazu DROP pro zrušení indexu je:

DROP INDEX index NA stůl

Než bude možné index z tabulky nebo tabulky samotné zrušit, musí být uzavřen.

Příklad: Vymažte index Ind_Fam z tabulky STUDENT

DROP INDEX Ind_Fam ON Student

Naučme se sčítat. Ne, toto nejsou výsledky učení SQL, ale výsledky hodnot sloupců databázových tabulek. Agregační funkce SQL pracují s hodnotami sloupce a vytvářejí jednu výslednou hodnotu. Nejčastěji používané agregační funkce SQL jsou SUM, MIN, MAX, AVG a COUNT. Existují dva případy, kdy by se měly použít agregační funkce. Za prvé, agregační funkce se používají samy o sobě a vracejí jedinou výslednou hodnotu. Za druhé, agregační funkce se používají s klauzulí SQL GROUP BY, to znamená se seskupováním podle polí (sloupců), aby se získaly výsledné hodnoty v každé skupině. Nejprve zvažte případy použití agregačních funkcí bez seskupování.

Funkce SQL SUM

Funkce SQL SUM vrací součet hodnot sloupce v databázové tabulce. Lze jej použít pouze na sloupce, jejichž hodnoty jsou čísla. Dotazy SQL pro získání výsledného součtu začínají takto:

SELECT SUM (COLUMNAME)...

Za tímto výrazem následuje FROM (TABLE_NAME) a ​​poté lze zadat podmínku pomocí klauzule WHERE. Kromě toho lze DISTINCT přidat před název sloupce, což znamená, že budou brány v úvahu pouze jedinečné hodnoty. Ve výchozím nastavení se berou v úvahu všechny hodnoty (pro to můžete konkrétně zadat ne DISTINCT, ale ALL, ale slovo ALL je volitelné).

Příklad 1 Existuje databáze společnosti s údaji o jejích odděleních a zaměstnancích. Tabulka Zaměstnanci má také sloupec s údaji o mzdách zaměstnanců. Výběr z tabulky má následující podobu (pro zvětšení obrázku na něj klikněte levým tlačítkem myši):

Chcete-li získat součet všech platů, použijte následující dotaz:

VYBERTE SOUČTU (plat) ZE zaměstnanců

Tento dotaz vrátí hodnotu 287664,63.

A teď . Ve cvičeních již začínáme úkoly komplikovat, přibližovat je těm, se kterými se setkáváme v praxi.

Funkce SQL MIN

Funkce SQL MIN také funguje na sloupcích, jejichž hodnoty jsou čísla a vrací minimum ze všech hodnot ve sloupci. Tato funkce má podobnou syntaxi jako funkce SUM.

Příklad 3 Databáze a tabulka jsou stejné jako v příkladu 1.

Musíte znát minimum mzdy zaměstnanci oddělení číslo 42. K tomu napište následující požadavek:

Dotaz vrátí hodnotu 10505,90.

A znovu cvičení pro nezávislé řešení . V tomto a některých dalších cvičeních budete potřebovat nejen tabulku Staff, ale také tabulku Org obsahující údaje o divizích společnosti:

Příklad 4 Do tabulky Zaměstnanci je přidána tabulka Org, která obsahuje údaje o divizích společnosti. Výstup minimální počet let, které jeden zaměstnanec odpracoval v oddělení se sídlem v Bostonu.

Funkce SQL MAX

Funkce SQL MAX funguje podobně a má stejnou syntaxi, která se používá, když chcete určit maximální hodnotu mezi všemi hodnotami sloupce.

Příklad 5

Je nutné zjistit maximální plat zaměstnanců oddělení číslo 42. K tomu napište následující dotaz:

Dotaz vrátí hodnotu 18352,80

Nyní je čas cvičení pro sebeurčení.

Příklad 6 Opět pracujeme se dvěma tabulkami – Staff a Org. Zobrazte název oddělení a maximální výši přijatých provizí jedním zaměstnancem v oddělení, které patří do skupiny oddělení (Divize) Východ. Použití JOIN (připojení ke stolům) .

Funkce SQL AVG

To, co bylo řečeno o syntaxi pro předchozí popsané funkce, platí i o funkci SQL AVG. Tato funkce vrací průměr všech hodnot ve sloupci.

Příklad 7 Databáze a tabulka jsou stejné jako v předchozích příkladech.

Nechť je požadováno zjištění průměrné délky služby zaměstnanců oddělení číslo 42. K tomu napíšeme následující dotaz:

Výsledek bude 6,33

Příklad 8 Pracujeme s jedním stolem – Staff. Zobrazte průměrnou mzdu zaměstnanců s praxí od 4 do 6 let.

Funkce SQL COUNT

Funkce SQL COUNT vrací počet záznamů v databázové tabulce. Pokud v dotazu zadáte SELECT COUNT(COLUMNAME) ..., bude výsledkem počet záznamů bez zohlednění těch záznamů, ve kterých je hodnota sloupce NULL (nedefinováno). Pokud jako argument použijete hvězdičku a spustíte dotaz SELECT COUNT(*) ..., výsledkem bude počet všech záznamů (řádků) v tabulce.

Příklad 9 Databáze a tabulka jsou stejné jako v předchozích příkladech.

Chcete znát počet všech zaměstnanců, kteří dostávají provize. Počet zaměstnanců, jejichž hodnoty sloupce Comm nejsou NULL, vrátí následující dotaz:

VYBERTE POČET (Komunikace) OD personálu

Výsledkem bude hodnota 11.

Příklad 10 Databáze a tabulka jsou stejné jako v předchozích příkladech.

Pokud chcete znát celkový počet záznamů v tabulce, použijte dotaz s hvězdičkou jako argument funkce COUNT:

VYBERTE POČET (*) OD zaměstnanců

Výsledkem bude hodnota 17.

další cvičení pro sebeurčení musíte použít poddotaz.

Příklad 11. Pracujeme s jedním stolem – Staff. Zobrazte počet zaměstnanců v oddělení Plains.

Agregační funkce s SQL GROUP BY

Nyní se podívejme na použití agregačních funkcí spolu s klauzulí SQL GROUP BY. Klauzule SQL GROUP BY se používá k seskupení výsledných hodnot podle sloupců v databázové tabulce. Stránka má lekce věnovaná tomuto operátorovi samostatně .

Příklad 12. Existuje databázový portál reklam. Obsahuje tabulku reklam, která obsahuje údaje o reklamách odeslaných za daný týden. Sloupec Kategorie obsahuje údaje o velkých kategoriích inzerátů (například Nemovitosti) a sloupec Části obsahuje údaje o menších částech, které jsou zahrnuty do kategorie (například části Byty a vily jsou součástí kategorie Nemovitosti). Sloupec Jednotky obsahuje údaje o počtu odeslaných inzerátů a sloupec Peníze obsahuje množství peněz vydělaných za odeslání inzerátů.

Kategorie	část	Jednotky	Peníze
Doprava	motorová vozidla	110	17600
Nemovitost	Byty	89	18690
Nemovitost	chaty	57	11970
Doprava	Motocykly	131	20960
stavební materiál	Desky	68	7140
elektrotechnika	televizory	127	8255
elektrotechnika	Ledničky	137	8905
stavební materiál	Regips	112	11760
Volný čas	knihy	96	6240
Nemovitost	Domy	47	9870
Volný čas	Hudba	117	7605
Volný čas	Hry	41	2665

Pomocí klauzule SQL GROUP BY zjistěte částku peněz vygenerovanou odesláním reklam v každé kategorii. Píšeme následující dotaz:

VYBERTE kategorii, SOUČET (Peníze) JAKO PENÍZE Z Reklamy GROUP BY Category

Příklad 13 Databáze a tabulka jsou stejné jako v předchozím příkladu.

Pomocí klauzule SQL GROUP BY zjistěte, která část každé kategorie měla nejvíce reklam. Píšeme následující dotaz:

VYBERTE kategorii, část, MAX (jednotky) JAKO maximum FROM Ads GROUP BY Category

Výsledkem bude následující tabulka:

Lze získat celkové a jednotlivé hodnoty v jedné tabulce kombinování výsledků dotazu pomocí operátoru UNION .

Relační databáze a jazyk SQL

Jako většina programovacích jazyků má SQL funkce pro manipulaci s daty. Stojí za zmínku, že na rozdíl od příkazů SQL nejsou funkce standardizovány pro všechny typy DBMS, to znamená, že pro provádění stejných operací s daty mají různé DBMS své vlastní názvy funkcí. To znamená, že kód dotazu napsaný v jednom DBMS nemusí fungovat v jiném, a s tím je třeba v budoucnu počítat. Především se jedná o funkce pro zpracování textových hodnot, převod datových typů a manipulaci s daty.

Obvykle je podporován DBMS standardní sada typy funkcí, jmenovitě:

• Textové funkce, které se používají pro zpracování textu (výběr části znaků v textu, určení délky textu, převod znaků na velká nebo malá písmena ...)
• Číselné funkce. Používá se k provedení matematické operace přes čísla
• Funkce data a času (provádění manipulace s datem a časem, výpočet období mezi daty, kontrola správnosti dat atd.)
• Statistické funkce (pro výpočet maxima / minimální hodnoty, průměry, počítání množství a součtů...)
• Systémové funkce (poskytují různé servisní informace o DBMS, uživateli atd.).

1. SQL funkce pro zpracování textu

Implementace SQL v Access DBMS má následující funkce pro zpracování textu:

Převedeme názvy produktů na velká písmena pomocí funkce UCase():

VYBRAT Product, UCase(Product) AS Product_UCase FROM Sumproduct

Pomocí funkce vyberte první tři znaky v textu VLEVO, ODJET():

VYBRAT Produkt, VLEVO(Produkt, 3) JAKO Produkt_VLEVO Z Sumproduktu

2. SQL funkce pro zpracování čísel

Funkce zpracování čísel jsou navrženy k provádění matematických operací s číselnými daty. Tyto funkce jsou určeny pro algebraické a geometrické výpočty, takže se používají mnohem méně často než funkce pro zpracování data a času. Číselné funkce jsou však nejvíce standardizované ve všech verzích SQL. Podívejme se na seznam numerických funkcí:

Uvedli jsme pouze několik hlavních funkcí, ale vždy se můžete podívat do dokumentace vaší databáze, kde najdete úplný seznam podporovaných funkcí a jejich podrobný popis.

Napišme například dotaz, abychom získali druhou odmocninu čísel ve sloupci Množství pomocí funkce SQR():

VYBRAT Částka, SQR(Částka) JAKO Částka_SQR Z Součtu

3. SQL funkce pro zpracování dat a časů

Funkce pro manipulaci s datem a časem jsou jednou z nejdůležitějších a často používaných funkcí SQL. Databáze ukládají hodnoty data a času ve speciálním formátu, takže bez nich nelze přímo používat dodatečné zpracování. Každý DBMS má vlastní sadu funkcí pro zpracování dat, která bohužel neumožňuje jejich přenos na jiné platformy a SQL implementace.

Seznam některých funkcí pro zpracování data a času Přístup do DBMS:

Podívejme se na příklad, jak funkce funguje DatePart():

VYBRAT Datum1, DatumČást("m", Datum1) JAKO Měsíc1 OD Souhrnný produkt

Funkce DatePart() Má to doplňkový parametr, což nám umožňuje zobrazit potřebnou část data. V příkladu jsme použili parametr "m", který zobrazuje číslo měsíce (stejným způsobem můžeme zobrazit rok - "yyyy", čtvrťák - "q", den - "d", týden - "w", hodina - "h", minut - "n", sekund - "s" atd.).

4. SQL statistické funkce

Statistické funkce nám pomáhají získat hotová data bez vzorkování. SQL dotazy s těmito funkcemi se často používají k analýze a vytváření různých sestav. Příkladem takového výběru může být: určení počtu řádků v tabulce, získání součtu hodnot pro určité pole, hledání největší / nejmenší nebo průměrné hodnoty v určeném sloupci tabulky. Všimněte si také, že statistické funkce jsou podporovány všemi DBMS bez velkých změn v pravopisu.

Seznam statistických funkcí v Přístup do DBMS:

POČET():

VYBRAT POČET(*) JAKO Počet1 OD Součet- vrátí počet všech řádků v tabulce

VYBRAT POČET (Produkt) JAKO Počet2 OD Součtu součinu- vrátí počet všech neprázdných řádků v poli produkt

Záměrně jsme odstranili jednu hodnotu ve sloupci produkt ukázat rozdíl v tom, jak tyto dva dotazy fungují.

Příklady použití funkcí SOUČET():

VYBRAT SUM(množství) JAKO Suma1 OD Součet součin WHERE Měsíc = "duben "

Tímto požadavkem jsme zohlednili celkový počet prodaného zboží v dubnu.

VYBRAT SUM(Množství*Částka) JAKO Součet2 OD Součet

Jak vidíte, v agregačních funkcích můžeme provádět výpočty na více sloupcích pomocí standardních matematických operátorů.

Příklad použití funkce MIN():

VYBRAT MIN(Částka) JAKO Min.1 Z Souč.součinu

Příklad použití funkce MAX():

VYBRAT MAX(Částka) JAKO Max1 OD Součtu součinu

Příklad použití funkce AVG():

VYBRAT AVG(Částka) AS Prům.1 OD Součet

Funkce jsou speciálním typem příkazů v sadě příkazů SQL a každý dialekt má svou vlastní implementaci sady příkazů. Ve výsledku můžeme říci, že funkce jsou příkazy skládající se z jednoho slova a vracející jedinou hodnotu. Hodnota funkce může záviset na vstupních parametrech, jako například v případě funkce, která vypočítává průměr seznamu hodnot v databázi. Mnoho funkcí však nebere žádné vstupní parametry, například funkce, která vrací aktuální systémový čas, je CURRENTJ1ME.

Standard ANSI podporuje několik užitečné funkce. Tato kapitola obsahuje popis těchto funkcí a také Detailní popis a příklady pro každou platformu. Každá platforma má navíc dlouhý seznam vlastních, interních funkcí, které přesahují standard SQL. Tato kapitola poskytuje parametry a popisy všech vnitřních funkcí každé z platforem.

Většina platforem má navíc možnost vytvářet vlastní funkce. Za dodatečné informace pro uživatelem definované funkce viz "CREATE/ALTER FUNCTION/PROCEDURE Statements"

Typy funkcí

Existovat různé způsoby klasifikace funkcí. Následující podčásti popisují důležité rozdíly, které vám pomohou pochopit, jak funkce fungují.

Deterministické a nedeterministické funkce

Funkce mohou být deterministické nebo nedeterministické. Deterministická funkce vždy vrací stejný výsledek pro stejnou sadu vstupních hodnot. Nedeterministické funkce mohou při různých voláních vracet různé výsledky, i když mají stejné vstupní hodnoty.

Proč je tak důležité, že se stejným vstupní parametry máš stejné výsledky? To je důležité, protože definuje způsob použití funkcí v pohledech, uživatelsky definovaných funkcích a uložených procedurách. Omezení zapnuto různé platformy mohou být různé, ale někdy lze v těchto objektech použít pouze deterministické funkce. SQL Server může například vytvořit index na výrazu ve sloupci, pokud výraz neobsahuje nedeterministické funkce. Pravidla a omezení se liší platformu od platformy, proto se při používání funkcí řiďte dokumentací výrobce.

Agregátní a skalární funkce

Dalším způsobem kategorizace funkcí je jejich schopnost pracovat pouze s jedním řetězcem nebo s kolekcí hodnot nebo se sadami řetězců. Agregační funkce pracují se sbírkou hodnot a vracejí jedinou celkovou hodnotu. Skalární funkce vracejí jednu hodnotu v závislosti na skalárních vstupních argumentech. Některé skalární funkce, například CURRENTJTIME, nevyžadují žádné argumenty.

Funkce okna

Funkce okna lze považovat za podobné agregačním funkcím v tom, že mohou pracovat na více řádcích najednou. Rozdíl je v tom, jak jsou tyto řádky specifikovány. Agregační funkce fungují na sadách řádků zadaných v klauzuli GROUP BY. V případě funkcí okna je sada řádků určena s každým voláním funkce a volání různých funkcí v rámci stejného dotazu mohou pracovat s různými sadami řádků.

Kroky této fáze učení SQL dotazů jsou navrženy tak, aby demonstrovaly skutečnost, že SQL dokáže nejen provádět složité výběry a třídit data, ale také vypočítat výsledky matematických funkcí, provádět transformaci textu, skupinové záznamy atd. Přesněji řečeno, toto vše neumí SQL, ale ti, kteří to podporují. SQL se svými standardy pouze formuluje požadavky pro tyto stejné DBMS.

Krok 15 SUM, AVG, MIN, MAX, COUNT…

Tento krok vám ukáže, jak používat nejjednodušší funkce v SQL, jako je součet, min, max, průměr a tak dále. Začněme hned příkladem odvození průměrného odslouženého věku všech zaměstnanců.

VYBERTE AVG(D_STAFF.S_EXPERIENCE) JAKO [PRŮMĚRNÁ ZKUŠENOST ZAMĚSTNANCŮ] OD D_STAFF

SQL funkce AVG.

Podobně můžete vypočítat minimální a maximální hodnoty (MIN, MAX), celkovou částku (SUM) atd. Doporučuji vám to vyzkoušet pomocí tutoriálu. Stojí za to pokusit se definovat další kritéria pro výběr záznamů zapojených do určení konečné hodnoty funkce pomocí klauzule WHERE.

Výše uvedené funkce používají k určení své hodnoty celý výsledek dotazu. Takové funkce se nazývají agregát . Existuje také řada funkcí, jejichž argumenty nejsou všechny hodnoty sloupce definovaného v dotazu, ale každá jednotlivá hodnota každého jednotlivého řádku výsledku. Příkladem takové funkce je funkce SQL pro výpočet délky textové pole LEN:

VYBERTE S_NAME, LEN(D_STAFF.S_NAME) JAKO [DÉLKA] OD D_STAFF

Může být použito superpozice funkcí SQL, jak je uvedeno níže, a vypočítejte hodnotu maximální délky pole S_NAME.

VYBERTE MAX (LEN(D_STAFF.S_NAME)) JAKO [MAX DÉLKA] OD D_STAFF

Funkce SQL MAX.

No a na závěr vše dohromady.

VYBERTE SOUČET (D_STAFF.S_EXPERIENCE) JAKO [SOUČET], AVG (D_STAFF.S_EXPERIENCE) JAKO [PRŮMĚR], MIN (D_STAFF.S_EXPERIENCE) JAKO [MINIMUM], MAX (D_STAFF.S_EXPERIENCE) JAKO [MAXIMUM], COUNT(*) JAKO [POČET ZÁZNAMŮ], MAX (LEN(D_STAFF.S_NAME)) JAKO [MAXIMÁLNÍ DÉLKA] OD D_STAFF

Příklad použití agregačních funkcí SQL.

Všimněte si argumentu funkce COUNT. Zadal jsem (*) jako argument, protože se chci přesně dostat celkový počet evidence. Pokud zadáte například COUNT(S_NAME), výsledkem bude počet neprázdných hodnot S_NAME ​​(S_NAME IS NOT NULL). Bylo by možné napsat COUNT(DISTINCT S_NAME) a ​​získat počet jedinečných hodnot S_NAME, ale MS Access tuto možnost bohužel nepodporuje. V našem příkladu COUNT(S_NAME) a ​​COUNT(*) dávají přesně stejný výsledek.

Krok 16 Transformace textu

Často, textové hodnoty vyplněné uživateli software různými způsoby: kdo píše celé jméno. velkými písmeny, kdo není; někdo píše všechno velká písmena. Mnoho formulářů hlášení vyžaduje jednotný přístup, a to nejen formuláře hlášení. K vyřešení tohoto problému má SQL dvě funkce UCASE a LCASE. Příklad žádosti a výsledek jejího zpracování jsou uvedeny níže:

SELECT UCASE(D_STAFF.S_NAME) AS , LCASE(D_STAFF.S_NAME) AS FROM D_STAFF

SQL funkce UCASE a LCASE.

Krok 17: SQL a manipulace s řetězci

K dispozici je také taková úžasná funkce MID, která vám pomůže vyřešit problém extrahování části řetězce z celé hodnoty textového pole. I zde by byl nejlepší komentář příklad – příklad „zesměšňování“ jmen uživatelských profilů.

VYBERTE UCASE(MID(P_NAME;3;5)) Z D_PROFILU

Superpozice SQL funkcí UCASE a MID.

„Vystřihli jsme“ 5 znaků z hodnot názvů profilů, počínaje třetím, a dostali jsme spoustu opakujících se „odpadků“. Abychom ponechali pouze jedinečné hodnoty, použijeme klíčové slovo ODLIŠNÝ.

SELECT DISTINCT UCASE(MID(P_NAME;3;5)) AS FROM D_PROFILE

Výběr jedinečných hodnot agregované funkce.

Někdy je nutné použít výrazy s funkcí LEN jako argumenty funkce MID. V následujícím příkladu již zobrazujeme posledních 5 znaků v názvech profilů.

VYBERTE UCASE(MID(P_NAME,DÉLKA(P_NAME)-4,5)) Z D_PROFILU

Pomocí funkce SQL LEN.

Krok 18. Použití funkcí SQL v kritériích pro výběr záznamů. HAVING prohlášení

Když jsme se zabývali funkcemi, téměř okamžitě vyvstává otázka, jak je lze použít v kritériích pro výběr záznamů? Některé funkce, zejména ty, které nejsou agregáty, se snadno používají. Zde je například seznam zaměstnanců, jejichž celé jméno více než 25 znaků.

SELECT S_NAME FROM D_STAFF WHERE LEN(D_STAFF.S_NAME) > 25

Použití neagregační funkce LEN v podmínkách dotazu SQL.

Pokud například potřebujete zobrazit identifikátory všech pozic, které jsou obsazeny více než jedním zaměstnancem ve společnosti, tento přístup nebude fungovat. Chci říct, že následující dotaz nemusí dávat žádný smysl, ale je nesprávný z pohledu strukturovaného dotazu. Je to dáno tím, že pro správné zpracování takových SQL dotazů pomocí agregačních funkcí nebude stačit jeden lineární průchod záznamy zaměstnanců.

SELECT S_POSITION FROM D_STAFF WHERE COUNT(S_POSITION)>1

Pro takové případy bylo v SQL zavedeno klíčové slovo HAVING, které nám pomůže vyřešit problém s pozicemi a zaměstnanci.

VYBERTE S_POSITION ZE SKUPINY D_STAFF PODLE S_POSITION S POČETEM (S_POSITION)>1

Použití agregačních funkcí v podmínkách dotazu SQL.

Krok 19. Seskupení dat ve výsledcích dotazu SQL pomocí operátoru GROUP BY

Klauzule GROUP BY je nutná k seskupení hodnot agregačních funkcí podle hodnot jejich přidružených polí. Je potřeba, když chceme použít hodnotu agregované funkce v kritériích výběru záznamu (předchozí krok). Je také potřeba, když chceme do výsledku dotazu zahrnout hodnotu agregované funkce. Ale v samotném jednoduchá verze seskupení je ekvivalentní zvýraznění jedinečných hodnot sloupců. Podívejme se na příklad žádosti.

VYBERTE S_POSITION OD D_STAFF

A to jsou dvě možnosti, které umožňují zobrazit pouze jedinečné hodnoty S_POSITION.

VYBERTE S_POSITION ZE SKUPINY D_STAFF PODLE S_POSITION

VYBERTE DISTINCT S_POSITION OD D_STAFF

Nyní se vraťme k seskupení hodnot funkcí podle hodnot polí s nimi spojených. Zobrazme pro každý uživatelský profil počet záznamů s ním spojených v tabulce D_STAFF_PROFILE.

SELECT PROFILE_ID AS , COUNT(PROFILE_ID) AS [POČET ZÁZNAMŮ] ZE SKUPINY D_STAFF_PROFILE BY PROFILE_ID

Použití agregované funkce SQL se seskupováním.

Operátor GROUP BY také umožňuje seskupit výsledek dotazu podle více než jednoho pole tak, že je uvedete oddělenými čárkami. Doufám, že po všem výše uvedeném nejsou další komentáře k výsledku posledního dotazu potřeba.

SELECT S.S_POSITION AS , S.S_NAME AS [ZAMĚSTNANEC], COUNT(SP.STAFF_ID) JAKO [POČET ZÁZNAMŮ V TABULCE D_STAFF_PROFILE] OD D_STAFF S, D_STAFF_PROFILE SP WHERE S.XD_IID=SP.STAFF_ID_POS GROUP S, S. S_NAME

Seskupení řádků výsledků dotazu SQL podle více polí.