Vstavané funkcie SQL navrhnuté tak, aby uľahčili a urýchlili spracovanie údajov. Zvláštnosťou je, že sa dajú špecifikovať priamo vo výraze. Všetky vstavané funkcie je možné podmienečne rozdeliť do skupín.

Matematické funkcie:

    ABS(význam) - vráti absolútnu hodnotu čísla;

    Okrúhly(hodnota, presnosť) - vráti číselnú hodnotu zaokrúhlenú nahor na zadaný argument presnosť počet desatinných miest;

    SIGN(význam) - vráti mínus, ak je číslo záporné, a v opačnom prípade plus;

    MOC(hodnota, stupeň) – zvýši číslo na mocninu;

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

    STROP(význam)- vráti najbližšie celé číslo väčšie alebo rovné hodnote;

    - PODLAHA(význam) Vráti najbližšie celé číslo menšie alebo rovné hodnote.

Funkcie reťazca:

    ASCII(riadok) - vracia ASCII kód prvého znaku reťazca;

    CHAR(číslo) – vráti znak podľa ASCII kód;

    LEN (riadok) – vráti dĺžku reťazca v znakoch, bez medzier na konci;

    LTRIM(riadok)/ RTRIM(riadok)- odstraňuje medzery na začiatku/konci reťazca;

    LEFT(reťazec, číslo)/ RIGHT(reťazec, číslo)- vráti zadaný argument číslo počet znakov v reťazci, začínajúc od ľavého/pravého okraja;

    SUBSTRING(linka, pozícia, dĺžka) - vráti podreťazec zadanej dĺžky z reťazca, začínajúc od zadanej pozície;

    NIŽŠIE(riadok) /HORNÝ(riadok) - vráti reťazec skonvertovaný na nižší / veľké písmená atď.

Funkcie pre prácu s dátumami:

    GETDATE() - Vráti hodnotu, ktorá obsahuje dátum a čas počítača, na ktorom beží inštancia SQL Server;

    DAY(dátum_hodnoty)– vráti číslo zo zadaného dátumu;

    MESIAC(dátum_hodnoty)- vráti číslo mesiaca od zadaného dátumu;

    ROK(dátum_hodnoty)- vráti hodnotu roka od zadaného dátumu;

    DATENANE( časť, dátum_hodnoty) - vracia znakový reťazec, predstavujúce špecifikovanú časť ( deň, mesiac, hodinaatď.) od určeného dátumu;

    DATEPART( časť, dátum_hodnoty) - vráti celé číslo predstavujúce zadanú časť ( deň, mesiac, hodinaatď.) od daného dátumu.

Funkcie prevodu dátových typov

    CAST (význam AS Dátový typ)

    KONVERTOVAŤ(Dátový typ, význam)

Argument význam vo funkciách určuje hodnotu, ktorá sa má previesť.

7.3. Príkazy jazyka definície údajov

Jazyk definície údajov obsahuje príkazy na vytváranie, úpravu a mazanie databázy a jej objektov.

Vytvorte tabuľku

Vytvorenie novej tabuľky sa vykoná príkazom VYTVORIŤ TABUĽKU. Príkaz popisuje štruktúru tabuľky, každý stĺpec tabuľky a obmedzenia integrity, ktoré musia byť pre tabuľku nastavené.

Syntax príkazu:

VYTVORIŤ TABUĽKU názov_tabuľky ((popis_stĺpca |vypočítaný_názov_stĺpca AS výraz | table_level_integrity_constraints) [, ...])

Názov tabuľky je identifikátor s maximálnou dĺžkou 128 znakov.

Tabuľka môže obsahovať vypočítaný stĺpec, v tomto prípade je hodnota stĺpca určená výrazom uloženým v štruktúre tabuľky. Vypočítaný stĺpec nemožno upraviť, takže nemôže byť NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY alebo DEFAULT.

Syntax na popis stĺpca tabuľky je:

názov_stĺpca Dátový typ[(veľkosť)]

[(DEFAULT predvolená_hodnota | IDENTITY [(hodnota, krok)]}]

[column_level_integrity_constraints]

DEFAULT - umožňuje určiť hodnotu priradenú stĺpcu v novo pridanom zázname.

IDENTITY Označuje, že sa vytvára stĺpec s povoleným automatickým číslovaním (stĺpec počítadla). V tabuľke môže byť definovaný iba jeden stĺpec počítadla. Parameter value určuje počiatočnú hodnotu počítadla a parameter step určuje krok prírastku. Ak tieto parametre nie sú nastavené, majú hodnotu 1. IDENTITY možno nastaviť len pre stĺpce, ktoré majú celočíselný alebo desiatkový typ. Vkladanie hodnôt do stĺpca IDENTITY nie je povolené.

DBMS spravuje dve skupiny obmedzení integrity:

Obmedzenia deklaratívnej integrity, ktoré sa deklarujú pri vytváraní alebo úprave tabuľky;

Obmedzenia procedurálnej integrity, ktoré sú ovládané spúšťačmi.

Deklaratívne obmedzenia integrity môžu byť obmedzenia na úrovni tabuľky a obmedzenia na úrovni tabuľky. Obmedzenie na úrovni stĺpca sa vzťahuje len na jeden stĺpec. Každé deklaratívne obmedzenie integrity možno pomenovať.

Opis obmedzení na úrovni stĺpcov má nasledujúcu syntax:

((PRIMÁRNY KĽÚČ | UNIKÁTNY | NIE JE NULL) | REFERENCIE CUDZIEHO KĽÚČA názov_tabuľky( názov_stĺpca)

|SKONTROLOVAŤ booleovský_výraz)

Názov obmedzenia integrity údajov musí byť v rámci databázy jedinečný. Zvážte obmedzenia, ktoré možno definovať na úrovni stĺpca:

Obmedzenie PRIMÁRNY KĽÚČ. Všetky hodnoty primárneho kľúča tabuľky musia byť jedinečné a nesmú byť nulové. Tabuľka môže mať iba jeden primárny kľúč. Ak je zložený, potom sa obmedzenia integrity primárneho kľúča nastavia na úrovni tabuľky;

Obmedzenia jedinečnosti hodnoty stĺpca UNIQUE. To znamená, že tabuľka nemôže mať dva záznamy, ktoré majú v tomto stĺpci rovnakú hodnotu;

Obmedzenie NOT NULL, ktoré bráni stĺpcu uložiť hodnotu NULL;

Obmedzenie cudzieho kľúča FOREIGN KEY (obmedzenie referenčnej integrity). Pre stĺpec, ktorý je cudzím kľúčom, použite REFERENCES na zadanie názvu tabuľky, ku ktorej sa vytvára prepojenie, a názvu stĺpca tejto tabuľky, s ktorým bude prepojené. Takáto tabuľka je hlavnou (nadradenou) tabuľkou vo vzťahu k vytvorenej tabuľke. Stĺpec v hlavnej tabuľke, ktorého hodnoty sa prepájajú, musí mať obmedzenie PRIMARY KEY.

Ak kľúč cudzej tabuľky pozostáva z viacerých polí, potom obmedzenie FOREIGN KEY musí byť špecifikované na úrovni tabuľky. V tomto prípade by ste mali uviesť zoznam všetkých stĺpcov zahrnutých v cudzom kľúči, zadať názov hlavnej tabuľky a názvy stĺpcov hlavnej tabuľky, na ktoré sa cudzí kľúč vzťahuje.

Referenčná integrita stanovuje pravidlá na pridávanie a úpravu údajov v tabuľke pomocou cudzieho kľúča a jeho zodpovedajúceho obmedzenia primárneho kľúča. Klauzuly ON UPDATE a ON DELETE pre cudzí kľúč definujú nasledujúce pravidlá na úpravu súvisiacich údajov:

NO ACTION - umožňuje zmeniť (vymazať) iba tie hodnoty v hlavnej tabuľke, ktoré nemajú zodpovedajúce hodnoty cudzieho kľúča v podradenej tabuľke. Toto pravidlo je štandardne účinné;

CASCADE znamená, že každá hodnota cudzieho kľúča podradenej tabuľky sa automaticky zmení (vymaže) pri zmene hodnoty primárneho kľúča nadradenej tabuľky;

SET NULL znamená, že v prípade zmeny (vymazania) primárneho kľúča nadradenej tabuľky budú vo všetkých odkazujúcich riadkoch podradenej tabuľky hodnotám cudzieho kľúča automaticky priradené hodnoty NULL;

SET DEFAULT znamená, že ak sa zmení (odstráni) primárny kľúč nadradenej tabuľky, vo všetkých odkazujúcich riadkoch podradenej tabuľky sa hodnotám cudzieho kľúča automaticky priradia predvolené hodnoty.

Doplňme príklad tréningovej databázy „Univerzita“, ktorej dizajn bol uvažovaný v kap. 4.3 tabuľky DISCIPLÍNA a VŠEOBECNÉ VYHLÁSENIE. Tabuľky 6 a 7 popisujú logickú štruktúru tabuliek.

Tabuľka 6

Logická štruktúra informačného objektu DISCIPLINE

Tabuľka 7

Logická štruktúra informačného objektu VŠEOBECNÉ VYHLÁSENIE

Kľúčový znak

Formát poľa

názov

Presnosť

Číslo knihy záznamov

Číslo zapísanej žiackej knižky

text

Kód disciplíny

Kód disciplíny

Číselné

dlhé celé číslo

číselné

Uveďme požiadavky na vytvorenie tabuliek v súlade s tým, ktorý je znázornený na obr. 35 infoologický databázový model.

Ryža. 35. Schéma databázy "Univerzita"

Ako môžete vidieť zo schémy databázy, tabuľka FAKULTA je nezávislá tabuľka, preto sa vytvára ako prvá. Požiadavka na vytvorenie tabuľky, berúc do úvahy popis logickej štruktúry v tabuľke. 4 (s. 61) bude vyzerať takto:

fakulty VYTVORIŤ TABUĽKU

([číslo oddelenia] tinyint PRIMARY KEY , [názov oddelenia] char(50))

Tabuľka SPECIALTY je tiež nezávislá, vytvárame ju ako druhú. Pri vytváraní dotazu používa popis logickej štruktúry v tabuľke. 5 (str. 62).

VYTVORIŤ TABUĽKU [špecialita] (

[špeciálne číslo] int PRIMARY KEY,

[názov špecializácie] char (60),

[náklady na vzdelanie])

Tabuľka SKUPINA závisí od tabuľky FAKULTA a ŠPECIALITA. Pri vytváraní dotazu používame tabuľku 3 (s. 61) a berieme do úvahy, že stĺpce číslo fakulty a špeciálne číslo sú cudzie kľúče:

VYTVORIŤ TABUĽKU [skupina] (

[číslo skupiny] smallint PRIMÁRNY KĽÚČ,

[číslo špeciality] int ZAHRANIČNÉ KĽÚČOVÉ REFERENCIE špecialita( miestnosť špeciálne- správy)ON DELETE CASCADE ON UPDADE CASCADE,

[číslo fakulty] tinyint KĽÚČOVÉ ZAHRANIČNÉ REFERENCIE fakulta( miestnosť fakulty) ON DELETE CASCADE ON UPDADE CASCADE, [číslo kurzu] tinyint)

Tabuľka STUDENT je tabuľka závislá od GROUP. Na základe údajov v tabuľke 2 (str. 60) vytvoríme dotaz. Upozorňujeme tiež, že stĺpec číslo skupiny sú cudzie kľúče:

VYTVORIŤ TABUĽKU [študent] (

[číslo skupiny] smallint NOT NULL CUDZIE KĽÚČOVÉ REFERENCIE group( miestnosť skupiny) ,

[priezvisko] char(15) NOT NULL ,

[dátum narodenia] dátum a čas NIE JE NULL ,

[komerčný] bit NOT NULL ,

[registračný názov] char(9))

Údaje v tabuľke GENERAL RECORD závisia od tabuliek ŠTUDENT a DISCIPLÍNA. V tejto tabuľke je primárny kľúč zložený a každý zo stĺpcov primárneho kľúča je cudzí kľúč (pozri tabuľku 7 a obrázok 35).

Využime popis logickej štruktúry tabuľky disciplín uvedený v tabuľke 6 a urobme dotaz:

VYTVORIŤ TABUĽKU [disciplína] (

[kód disciplíny] int PRIMÁRNY KĽÚČ,

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

Teraz môžete vytvoriť dotaz na vytvorenie tabuľky všeobecných príkazov. Pretože primárny kľúč tabuľky je zložený kľúč, obmedzenie PRIMARY KEY musí byť nastavené na úrovni tabuľky. Napríklad, nastavme obmedzenia FOREIGN KEY aj na úrovni tabuľky. Žiadosť bude vyzerať takto:

VYTVORIŤ TABUĽKU [všeobecný hárok] (

[disciplinárny kód] int,

[číslo v klasifikačnej knihe] char(8),

[známka] NIE JE NULL , PRIMÁRNY KĽÚČ ([kód disciplíny],[číslo triednej knihy]), CUDZÍ KĽÚČ ([kód disciplíny]) ODKAZY [disciplína] ([kód disciplíny]), CUDZÍ KĽÚČ ([číslo triednej knihy]) REFERENCIE [študent ] ([číslo knihy známok]))

Zmena štruktúry tabuľky

Zmena štruktúry tabuľky sa vykonáva pomocou príkazu ALTER TABLE. Pomocou príkazu môžete zmeniť vlastnosti existujúcich stĺpcov, odstrániť ich alebo pridať nové stĺpce do tabuľky, spravovať obmedzenia integrity, a to na úrovni stĺpcov aj na úrovni tabuľky. Priradenie mnohých parametrov a kľúčových slov je podobné priradeniu zodpovedajúcich parametrov a kľúčových slov príkazu CREATE TABLE.

Odstránenie tabuľky

Zrušenie tabuľky sa vykonáva pomocou príkazu DROP TABLE. Syntax príkazu:

DROP TABLE tabuľky

Napríklad dotaz na vymazanie tabuľky STUDENT vyzerá takto:

DROP TABLE Študent

Odstránenie tabuľky BY MALO brať do úvahy vzťahy vytvorené v databáze medzi tabuľkami. Ak iná tabuľka odkazuje na tabuľku, ktorá sa odstraňuje pomocou obmedzenia integrity FOREIGN KEY, potom DBMS nepovolí jej vymazanie.

Vytvorte index

Indexy sa používajú na urýchlenie prístupu ku konkrétnym údajom v databázovej tabuľke. Index je štruktúra, ktorá zoraďuje hodnoty v jednom alebo viacerých stĺpcoch databázovej tabuľky, ako je napríklad stĺpec Priezvisko tabuľky STUDENT. Ak hľadáte konkrétneho študenta podľa priezviska, index vám pomôže získať potrebné informácie rýchlejšie ako prehľadávanie všetkých riadkov v tabuľke.

Index poskytuje ukazovatele na hodnoty údajov uložené v konkrétnych stĺpcoch tabuľky a usporiada tieto ukazovatele podľa určeného poradia zoradenia. Vyhľadávanie údajov v tabuľke pomocou indexu je podobné ako vyhľadávanie v indexe v knihe. Najprv sa vyhľadá konkrétna hodnota v indexe a potom sa vykoná zodpovedajúci skok cez ukazovateľ na riadok obsahujúci túto hodnotu.

Index sa vytvorí pomocou príkazu CREATE INDEX:

VYTVORIŤ INDEX

názov_ index ON meno _tables(stĺpec [,…])

kde UNIQUE označuje, že index by mal uchovávať iba jedinečné hodnoty.

Index môže byť vytvorený na jednom alebo viacerých stĺpcoch (zložený index). Zložené indexy vám umožňujú rozlišovať medzi záznamami, ktoré majú rovnakú hodnotu v rovnakom stĺpci.

Príklad: Vytvorte zložený index v tabuľke STUDENT pre polia Priezvisko a Dátum narodenia

VYTVORIŤ INDEX Ind_Fam ON

Študent(priezvisko, [dátum narodenia] DESC)

Index na tabuľke by ste mali vytvoriť iba vtedy, ak máte v úmysle často dotazovať údaje v indexovaných stĺpcoch. Indexy zaberajú miesto na disku a spomaľujú pridávanie, mazanie a aktualizácie riadkov.

Odstránenie indexu tabuľky

Príkaz DROP odstráni index z tabuľky. Syntax príkazu DROP na zrušenie indexu je:

DROP INDEX index ON tabuľky

Pred odstránením indexu z tabuľky alebo zo samotnej tabuľky musí byť zatvorený.

Príklad: Vymažte index Ind_Fam z tabuľky STUDENT

DROP INDEX Ind_Fam ON Student

Naučme sa sumarizovať. Nie, toto nie sú výsledky učenia sa SQL, ale výsledky hodnôt stĺpcov databázových tabuliek. Agregačné funkcie SQL pracujú s hodnotami stĺpca a vytvárajú jednu výslednú hodnotu. Najčastejšie používané agregačné funkcie SQL sú SUM, MIN, MAX, AVG a COUNT. Existujú dva prípady, v ktorých by sa mali použiť agregačné funkcie. Po prvé, súhrnné funkcie sa používajú samy osebe a vracajú jedinú výslednú hodnotu. Po druhé, agregačné funkcie sa používajú s klauzulou SQL GROUP BY, to znamená so zoskupením podľa polí (stĺpcov), aby sa získali výsledné hodnoty v každej skupine. Najprv zvážte prípady použitia agregačných funkcií bez zoskupovania.

Funkcia SQL SUM

Funkcia SQL SUM vracia súčet hodnôt stĺpca v databázovej tabuľke. Dá sa použiť iba na stĺpce, ktorých hodnoty sú čísla. SQL dotazy na získanie výsledného súčtu začínajú takto:

SELECT SUM (COLUMNAME)...

Za týmto výrazom nasleduje FROM (TABLE_NAME) a ​​potom je možné zadať podmienku pomocou klauzuly WHERE. Okrem toho môže byť pred názvom stĺpca predpona DISTINCT, čo znamená, že sa budú brať do úvahy iba jedinečné hodnoty. V predvolenom nastavení sa berú do úvahy všetky hodnoty (na tento účel môžete konkrétne určiť nie DISTINCT, ale ALL, ale slovo ALL je voliteľné).

Príklad 1 Existuje databáza spoločnosti s údajmi o jej oddeleniach a zamestnancoch. Tabuľka Zamestnanci má tiež stĺpec s údajmi o mzdách zamestnancov. Výber z tabuľky má nasledovnú formu (pre zväčšenie obrázku kliknite naň ľavým tlačidlom myši):

Ak chcete získať súčet všetkých platov, použite nasledujúci dotaz:

VYBERTE SI SUM (Plat) OD zamestnancov

Tento dotaz vráti hodnotu 287664,63.

A teraz . V cvičeniach už začíname úlohy komplikovať, približovať ich tým, s ktorými sa stretávame v praxi.

Funkcia SQL MIN

Funkcia SQL MIN funguje aj so stĺpcami, ktorých hodnoty sú čísla a vracia minimum zo všetkých hodnôt v stĺpci. Táto funkcia má podobnú syntax ako funkcia SUM.

Príklad 3 Databáza a tabuľka sú rovnaké ako v príklade 1.

Musíte vedieť minimum mzdy zamestnanci oddelenia číslo 42. K tomu napíšte nasledujúcu žiadosť:

Dopyt vráti hodnotu 10505,90.

A znova cvičenie pre nezávislé riešenie . V tomto a niektorých ďalších cvičeniach budete potrebovať nielen tabuľku Zamestnanci, ale aj tabuľku Org obsahujúcu údaje o divíziách spoločnosti:


Príklad 4 Do tabuľky Zamestnanci sa pridá tabuľka Org, ktorá obsahuje údaje o divíziách spoločnosti. Výstup je minimálny počet rokov, ktoré jeden zamestnanec odpracoval v oddelení v Bostone.

Funkcia SQL MAX

Funkcia SQL MAX funguje podobne a má podobnú syntax, ktorá sa používa, keď chcete určiť maximálnu hodnotu medzi všetkými hodnotami stĺpca.

Príklad 5

Je potrebné zistiť maximálny plat zamestnancov oddelenia číslo 42. Za týmto účelom napíšte nasledujúci dotaz:

Dopyt vráti hodnotu 18352,80

Teraz je čas cvičenia na sebaurčenie.

Príklad 6 Opäť pracujeme s dvoma tabuľkami – Staff a Org. Zobrazte názov oddelenia a maximálnu výšku provízií prijatých jedným zamestnancom na oddelení, ktoré patrí do skupiny oddelení (Divízia) Východ. Použite JOIN (pripojenie k stolom) .

Funkcia SQL AVG

To, čo bolo povedané o syntaxi pre predchádzajúce opísané funkcie, platí aj pre funkciu SQL AVG. Táto funkcia vráti priemer všetkých hodnôt v stĺpci.

Príklad 7 Databáza a tabuľka sú rovnaké ako v predchádzajúcich príkladoch.

Nech je potrebné zistiť priemernú dĺžku služby zamestnancov oddelenia číslo 42. Za týmto účelom napíšeme nasledujúci dotaz:

Výsledok bude 6,33

Príklad 8 Pracujeme s jedným stolom – Staff. Zobrazte priemernú mzdu zamestnancov s praxou od 4 do 6 rokov.

Funkcia SQL COUNT

Funkcia SQL COUNT vráti počet záznamov v databázovej tabuľke. Ak v dotaze zadáte SELECT COUNT(COLUMNAME) ..., výsledkom bude počet záznamov bez zohľadnenia tých záznamov, v ktorých je hodnota stĺpca NULL (nedefinovaná). Ak ako argument použijete hviezdičku a spustíte dotaz SELECT COUNT(*) ..., výsledkom bude počet všetkých záznamov (riadkov) v tabuľke.

Príklad 9 Databáza a tabuľka sú rovnaké ako v predchádzajúcich príkladoch.

Chcete vedieť počet všetkých zamestnancov, ktorí dostávajú provízie. Počet zamestnancov, ktorých hodnoty stĺpca Comm nie sú NULL, vráti nasledujúci dotaz:

VYBERTE POČET (Komunikácia) OD personálu

Výsledkom bude hodnota 11.

Príklad 10 Databáza a tabuľka sú rovnaké ako v predchádzajúcich príkladoch.

Ak chcete zistiť celkový počet záznamov v tabuľke, použite dotaz s hviezdičkou ako argument funkcie COUNT:

VYBERTE POČET (*) OD personálu

Výsledkom bude hodnota 17.

Ďalšie cvičenie na sebaurčenie musíte použiť poddotaz.

Príklad 11. Pracujeme s jedným stolom – Staff. Zobrazte počet zamestnancov v oddelení Plains.

Agregačné funkcie s SQL GROUP BY

Teraz sa pozrime na používanie agregačných funkcií spolu s klauzulou SQL GROUP BY. Klauzula SQL GROUP BY sa používa na zoskupenie výsledných hodnôt podľa stĺpcov v databázovej tabuľke. Stránka má lekciu venovanú tomuto operátorovi samostatne .

Príklad 12. K dispozícii je databáza inzerátov portálu. Obsahuje tabuľku reklám, ktorá obsahuje údaje o reklamách, ktoré boli odoslané za daný týždeň. Stĺpec Kategória obsahuje údaje o veľkých kategóriách reklám (napríklad Nehnuteľnosti) a Stĺpec Časti obsahuje údaje o menších častiach zaradených do kategórií (napríklad časti Byty a vily sú súčasťou kategórie Nehnuteľnosti). Stĺpec Jednotky obsahuje údaje o počte odoslaných inzerátov a stĺpec Peniaze obsahuje množstvo peňazí zarobených za odoslanie inzerátov.

KategóriačasťJednotkyPeniaze
DopravaMotorové vozidlá110 17600
NehnuteľnosťApartmány89 18690
NehnuteľnosťDače57 11970
DopravaMotocykle131 20960
stavebné materiálydosky68 7140
elektrotechnikatelevízory127 8255
elektrotechnikaChladničky137 8905
stavebné materiályRegips112 11760
Voľný časknihy96 6240
NehnuteľnosťDomy47 9870
Voľný časHudba117 7605
Voľný časHry41 2665

Pomocou klauzuly SQL GROUP BY nájdite množstvo peňazí vygenerovaných odoslaním reklám v každej kategórii. Píšeme nasledujúci dotaz:

VYBERTE kategóriu, SÚČET (Peniaze) AKO PENIAZE Z REKLAMY GROUP BY Category

Príklad 13 Databáza a tabuľka sú rovnaké ako v predchádzajúcom príklade.

Pomocou klauzuly SQL GROUP BY zistite, ktorá časť každej kategórie mala najviac reklám. Píšeme nasledujúci dotaz:

VYBERTE kategóriu, časť, MAX (jednotky) AKO maximum FROM Ads GROUP BY Category

Výsledkom bude nasledujúca tabuľka:

Je možné získať celkové a jednotlivé hodnoty v jednej tabuľke kombinovanie výsledkov dotazu pomocou operátora UNION .

Relačné databázy a jazyk SQL

Ako väčšina programovacích jazykov, aj SQL má funkcie na spracovanie údajov. Stojí za zmienku, že na rozdiel od príkazov SQL nie sú funkcie štandardizované pre všetky typy DBMS, to znamená, že na vykonávanie rovnakých operácií s údajmi majú rôzne DBMS svoje vlastné názvy funkcií. To znamená, že kód dotazu napísaný v jednom DBMS nemusí fungovať v inom a s tým treba v budúcnosti počítať. Predovšetkým sa to týka funkcií na spracovanie textových hodnôt, konverziu dátových typov a manipuláciu s dátumami.

Zvyčajne je podporovaný DBMS štandardná sada typy funkcií, konkrétne:

  • Textové funkcie, ktoré sa používajú na spracovanie textu (výber časti znakov v texte, určenie dĺžky textu, prevod znakov na veľké alebo malé písmená ...)
  • Číselné funkcie. Používa sa na vykonávanie matematické operácie nad číslami
  • Funkcie dátumu a času (vykonávanie manipulácie s dátumom a časom, výpočet obdobia medzi dátumami, kontrola správnosti dátumov atď.)
  • Štatistické funkcie (na výpočet maxima / minimálne hodnoty, priemery, počítanie množstiev a súčtov...)
  • Systémové funkcie (poskytujú rôzne servisné informácie o DBMS, používateľovi atď.).

1. Funkcie SQL pre spracovanie textu

Implementácia SQL v Access DBMS má nasledujúce funkcie na spracovanie textu:

Pomocou funkcie prevedieme názvy produktov na veľké písmená UCase():

VYBRAŤ Produkt, UCase(Product) AS Product_UCase FROM Sumproduct

Pomocou funkcie vyberte prvé tri znaky v texte LEFT():

VYBRAŤ Produkt, LEFT(Produkt, 3) AS Product_LEFT FROM Sumproduct

2. SQL funkcie na spracovanie čísel

Funkcie spracovania čísel sú určené na vykonávanie matematických operácií s číselnými údajmi. Tieto funkcie sú určené na algebraické a geometrické výpočty, preto sa používajú oveľa menej často ako funkcie na spracovanie dátumu a času. Číselné funkcie sú však najviac štandardizované vo všetkých verziách SQL. Poďme sa pozrieť na zoznam numerických funkcií:

Uviedli sme len niekoľko hlavných funkcií, ale vždy si môžete pozrieť dokumentáciu vašej databázy, kde nájdete úplný zoznam podporovaných funkcií a ich podrobný popis.

Napríklad napíšme dotaz na získanie druhej odmocniny čísel v stĺpci Suma pomocou funkcie SQR():

VYBRAŤ Suma, SQR(Suma) AS Suma_SQR FROM Sumproduct

3. SQL funkcie na spracovanie dátumov a časov

Funkcie manipulácie s dátumom a časom sú jednou z najdôležitejších a najčastejšie používaných funkcií SQL. Databázy ukladajú hodnoty dátumu a času v špeciálnom formáte, takže bez nich nie je možné ich priamo použiť dodatočné spracovanie. Každý DBMS má vlastnú sadu funkcií na spracovanie dátumov, čo, žiaľ, neumožňuje ich prenos na iné platformy a implementácie SQL.

Zoznam niektorých funkcií na spracovanie dátumu a času DBMS prístup:

Pozrime sa na príklad, ako funkcia funguje DatePart():

VYBRAŤ Date1, DatePart("m", Date1) AS 1. mesiac OD Sumproduct

Funkcia DatePart() Má dodatočný parameter, ktorý nám umožňuje zobraziť potrebnú časť dátumu. V príklade sme použili parameter "m", ktorý zobrazuje číslo mesiaca (rovnakým spôsobom môžeme zobraziť rok - "yyyy", štvrťrok - "q", deň - "d", týždeň - "w", hodina - "h", minúty - "n", sekundy - "s" atď.).

4. Štatistické funkcie SQL

Štatistické funkcie nám pomáhajú získať hotové údaje bez vzorkovania. SQL dotazy s týmito funkciami sa často používajú na analýzu a vytváranie rôznych zostáv. Príkladom takýchto výberov môže byť: určenie počtu riadkov v tabuľke, získanie súčtu hodnôt pre určité pole, hľadanie najväčšej / najmenšej alebo priemernej hodnoty v určenom stĺpci tabuľky. Všimnite si tiež, že štatistické funkcie sú podporované všetkými DBMS bez výrazných zmien v pravopise.

Zoznam štatistických funkcií v DBMS prístup:

COUNT():

VYBRAŤ COUNT(*) AKO Počet1 OD Sumárny súčin- vráti počet všetkých riadkov v tabuľke

VYBRAŤ POČET (Produkt) AKO Počet2 OD Sumproduktu- vráti počet všetkých neprázdnych riadkov v poli Produkt

Zámerne sme odstránili jednu hodnotu v stĺpci Produkt ukázať rozdiel v tom, ako tieto dva dotazy fungujú.

Príklady použitia funkcií SUM():

VYBRAŤ SUM(množstvo) AKO súčet1 OD Sumárny súčin WHERE mesiac = "apríl "

Touto požiadavkou sme premietli celkový počet predaných tovarov v apríli.

VYBRAŤ SUM(Množstvo*Suma) AKO Suma2 Z Sumproduktu

Ako vidíte, v agregačných funkciách môžeme vykonávať výpočty na viacerých stĺpcoch pomocou štandardných matematických operátorov.

Príklad použitia funkcie MIN():

VYBRAŤ MIN(Suma) AS Min1 OD Sumproduktu

Príklad použitia funkcie MAX():

VYBRAŤ MAX(Suma) AS Max1 OD súčtu

Príklad použitia funkcie AVG():

VYBRAŤ AVG(Suma) AS Avg1 FROM Sumproduct

Funkcie sú špeciálnym typom príkazov v sade príkazov SQL a každý dialekt má svoju vlastnú implementáciu sady príkazov. V dôsledku toho môžeme povedať, že funkcie sú príkazy pozostávajúce z jedného slova a vracajúce jednu hodnotu. Hodnota funkcie môže závisieť od vstupných parametrov, ako napríklad v prípade funkcie, ktorá vypočítava priemer zoznamu hodnôt v databáze. Mnohé funkcie však neberú žiadne vstupné parametre, napríklad funkcia, ktorá vracia aktuálny systémový čas, je CURRENTJ1ME.

Štandard ANSI podporuje niekoľko užitočné funkcie. Táto kapitola obsahuje popis týchto funkcií, ako aj Detailný popis a príklady pre každú platformu. Každá platforma má navyše dlhý zoznam vlastných interných funkcií, ktoré presahujú štandard SQL. Táto kapitola poskytuje parametre a popisy všetkých interných funkcií každej z platforiem.

Väčšina platforiem má navyše možnosť vytvárať vlastné funkcie. Za Ďalšie informácie pre užívateľom definované funkcie si pozrite "VYTVORIŤ/ZMENIŤ FUNKCIU/POSTUP"

Typy funkcií

Existovať rôzne cesty klasifikácie funkcií. Nasledujúce podsekcie popisujú dôležité rozdiely, ktoré vám pomôžu pochopiť, ako funkcie fungujú.

Deterministické a nedeterministické funkcie

Funkcie môžu byť deterministické alebo nedeterministické. Deterministická funkcia vždy vráti rovnaký výsledok pre rovnakú množinu vstupných hodnôt. Nedeterministické funkcie môžu pri rôznych volaniach vracať rôzne výsledky, aj keď majú rovnaké vstupné hodnoty.

Prečo je také dôležité, že s rovnakým vstupné parametre mali rovnaké výsledky? Je to dôležité, pretože definuje spôsob, akým sa funkcie používajú v zobrazeniach, užívateľom definovaných funkciách a uložených procedúrach. Obmedzenia zapnuté rôzne platformy môžu byť rôzne, ale niekedy možno v týchto objektoch použiť iba deterministické funkcie. Napríklad SQL Server môže vytvoriť index na výraze v stĺpci, pokiaľ výraz neobsahuje nedeterministické funkcie. Pravidlá a obmedzenia sa líšia od platformy k platforme, preto sa pri používaní funkcií riaďte dokumentáciou výrobcu.

Agregátne a skalárne funkcie

Ďalším spôsobom kategorizácie funkcií je ich schopnosť pracovať iba s jedným reťazcom alebo s kolekciou hodnôt alebo so súbormi reťazcov. Agregátne funkcie fungujú na kolekcii hodnôt a vracajú jednu celkovú hodnotu. Skalárne funkcie vracajú jednu hodnotu v závislosti od skalárnych vstupných argumentov. Niektoré skalárne funkcie, ako napríklad CURRENTJTIME, nevyžadujú žiadne argumenty.

Funkcie okien

Funkcie okien možno považovať za podobné agregovaným funkciám v tom, že môžu pracovať na viacerých riadkoch naraz. Rozdiel je v tom, ako sú tieto riadky špecifikované. Agregačné funkcie fungujú na množinách riadkov špecifikovaných v klauzule GROUP BY. V prípade funkcií okna je množina riadkov špecifikovaná pri každom volaní funkcie a rôzne volania funkcií v rámci toho istého dotazu môžu fungovať na rôznych množinách riadkov.

Kroky tejto fázy učenia sa SQL dotazov sú navrhnuté tak, aby demonštrovali skutočnosť, že SQL dokáže nielen robiť komplexné výbery a triediť dáta, ale aj počítať výsledky matematických funkcií, vykonávať transformáciu textu, skupinové záznamy atď. Presnejšie povedané, toto všetko nedokáže SQL, ale tí, ktorí to podporujú. SQL so svojimi štandardmi formuluje iba požiadavky pre tie isté DBMS.

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

Tento krok vám ukáže, ako používať najjednoduchšie funkcie v SQL, ako sú súčet, min, max, priemer atď. Začnime hneď príkladom odvodenia priemernej dĺžky odpracovaných rokov všetkých zamestnancov.

VYBERTE AVG(D_STAFF.S_EXPERIENCE) AKO [PREMERNÁ SKÚSENOSŤ ZAMESTNANCOV] Z D_STAFF

SQL funkcia AVG.

Podobne môžete vypočítať minimálne a maximálne hodnoty (MIN, MAX), celkovú sumu (SUM) atď. Odporúčam vám to vyskúšať pomocou návodu. Stojí za to pokúsiť sa definovať dodatočné kritériá na výber záznamov, ktoré sa podieľajú na určovaní konečnej hodnoty funkcie pomocou klauzuly WHERE.

Vyššie uvedené funkcie používajú na určenie svojej hodnoty celý výsledok dotazu. Takéto funkcie sú tzv agregát . Existuje tiež množstvo funkcií, ktorých argumenty nie sú všetky hodnoty stĺpca definovaného v dotaze, ale každá jednotlivá hodnota každého jednotlivého riadka výsledku. Príkladom takejto funkcie je funkcia SQL na výpočet dĺžky textové pole LEN:

VYBERTE S_NAME, LEN(D_STAFF.S_NAME) AKO [LENGTH] OD D_STAFF


Môže byť použité superpozícia SQL funkcií, ako je uvedené nižšie, a vypočítajte hodnotu maximálnej dĺžky poľa S_NAME.

VYBERTE MAX (LEN(D_STAFF.S_NAME)) AKO [MAX LENGTH] OD D_STAFF


Funkcia SQL MAX.

No a na záver všetko spolu.

VYBERTE SÚČET (D_STAFF.S_EXPERIENCE) AKO [SUM], AVG (D_STAFF.S_EXPERIENCE) AKO [PREMER], MIN (D_STAFF.S_EXPERIENCE) AKO [MINIMUM], MAX (D_STAFF.S_EXPERIENCE) AKO [MAXIMUM], COUNT(*) AKO [POČET ZÁZNAMOV], MAX (LEN(D_STAFF.S_NAME)) AKO [MAXIMÁLNA DĹŽKA] OD D_STAFF


Príklad použitia agregovaných funkcií SQL.

Všimnite si argument funkcie COUNT. Zadal som (*) ako argument, pretože sa chcem presne dostať celkový počet záznamy. Ak zadáte napríklad COUNT(S_NAME), výsledkom bude počet neprázdnych hodnôt S_NAME ​​(S_NAME IS NOT NULL). Bolo by možné zapísať COUNT(DISTINCT S_NAME) a ​​získať počet jedinečných hodnôt S_NAME, ale MS Access, žiaľ, túto možnosť nepodporuje. V našom príklade COUNT(S_NAME) a ​​COUNT(*) dávajú presne rovnaký výsledok.

Krok 16 Transformácia textu

často textové hodnoty vyplnené používateľmi softvér rôznymi spôsobmi: kto napíše celé meno. veľkými písmenami, kto nie; niekto píše všetko veľké písmená. Mnohé ohlasovacie formuláre si vyžadujú jednotný prístup, nielen ohlasovacie formuláre. Na vyriešenie tohto problému má SQL dve funkcie UCASE a LCASE. Príklad žiadosti a výsledok jej spracovania sú uvedené nižšie:

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


SQL funkcie UCASE a LCASE.

Krok 17: SQL a manipulácia s reťazcami

Existuje aj taká úžasná funkcia MID, ktorá vám pomôže vyriešiť problém extrakcie časti reťazca z celej hodnoty textového poľa. Aj tu by bol najlepší komentár príklad – príklad „zosmiešňovania“ mien používateľských profilov.

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


Superpozícia SQL funkcií UCASE a MID.

„Vystrihli sme“ 5 znakov z hodnôt názvov profilov, počnúc od 3. a dostali sme veľa opakujúcich sa „odpadkov“. Aby sme ponechali iba jedinečné hodnoty, použijeme kľúčové slovo ODLIŠNÝ.

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


Výber jedinečných hodnôt súhrnnej funkcie.

Niekedy je potrebné použiť výrazy s funkciou LEN ako argumenty funkcie MID. V nasledujúcom príklade už zobrazujeme posledných 5 znakov v názvoch profilov.

SELECT UCASE(MID(P_NAME,LEN(P_NAME)-4,5)) FROM D_PROFILE


Pomocou funkcie SQL LEN.

Krok 18. Použitie SQL funkcií v kritériách pre výber záznamov. HAVING vyhlásenie

Keď sme sa zaoberali funkciami, takmer okamžite vyvstáva otázka, ako sa dajú použiť v kritériách výberu záznamov? Niektoré funkcie, najmä tie, ktoré nie sú agregátmi, sa ľahko používajú. Tu je napríklad zoznam zamestnancov, ktorých celé meno viac ako 25 znakov.

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


Použitie neagregovanej funkcie LEN v podmienkach dotazu SQL.

Ak napríklad potrebujete zobraziť identifikátory všetkých pozícií, ktoré sú vo firme obsadené viac ako jedným zamestnancom, tento prístup nebude fungovať. Chcem tým povedať, že nasledujúci dotaz nemusí dávať zmysel, ale je nesprávny z pohľadu štruktúrovaného dotazu. Je to spôsobené tým, že na správne spracovanie takýchto SQL dotazov pomocou agregačných funkcií nebude stačiť jeden lineárny prechod cez záznamy zamestnancov.

SELECT S_POSITION FROM D_STAFF WHERE COUNT(S_POSITION)>1

Pre takéto prípady bolo v SQL zavedené kľúčové slovo HAVING, ktoré nám pomôže vyriešiť problém s pozíciami a zamestnancami.

VYBERTE S_POSITION ZO SKUPINY D_STAFF BY S_POSITION S COUNT(S_POSITION)>1


Používanie agregačných funkcií v podmienkach dotazu SQL.

Krok 19. Zoskupenie údajov vo výsledkoch dotazu SQL pomocou operátora GROUP BY

Klauzula GROUP BY je potrebná na zoskupenie hodnôt agregovaných funkcií podľa hodnôt ich priradených polí. Je to potrebné, keď chceme použiť hodnotu agregovanej funkcie v kritériách výberu záznamu (predchádzajúci krok). Je tiež potrebné, keď chceme do výsledku dotazu zahrnúť hodnotu agregovanej funkcie. Ale vo veľmi jednoduchá verzia zoskupovanie je ekvivalentné zvýrazneniu jedinečných hodnôt stĺpcov. Pozrime sa na príklad žiadosti.

VYBERTE S_POSITION OD D_STAFF


A to sú dve možnosti, ktoré umožňujú zobraziť iba jedinečné hodnoty S_POSITION.

VYBERTE S_POSITION ZO SKUPINY D_STAFF PODĽA S_POSITION

VYBERTE DISTINCT S_POSITION OD D_STAFF


Teraz sa vráťme k zoskupovaniu funkčných hodnôt podľa hodnôt polí, ktoré sú s nimi spojené. Pre každý užívateľský profil zobrazme počet záznamov, ktoré sú s ním spojené v tabuľke D_STAFF_PROFILE.

SELECT PROFILE_ID AS , COUNT(PROFILE_ID) AS [NUMBER OF RECORDS] ZO SKUPINY D_STAFF_PROFILE BY PROFILE_ID


Použitie agregovanej funkcie SQL so zoskupovaním.

Operátor GROUP BY vám tiež umožňuje zoskupiť výsledok dopytu podľa viacerých polí tak, že ich uvediete oddelené čiarkami. Dúfam, že po všetkom vyššie uvedenom nie sú potrebné ďalšie komentáre k výsledku posledného dotazu.

SELECT S.S_POSITION AS , S.S_NAME AKO [ZAMESTNANEC], COUNT(SP.STAFF_ID) AKO [POČET ZÁZNAMOV V TABUĽKE D_STAFF_PROFILE] OD D_STAFF S, D_STAFF_PROFILE SP WHERE S.XD_IID=SP.STAFF_ID_POS GROUP S, S. S_NAME


Zoskupenie riadkov výsledkov dotazu SQL podľa viacerých polí.