Image
12.4.2016 0 Comments

Databázy / 7. časť

Späť na úvod >> Späť na seriál

V predošlej časti seriálu sme si ukázali, ako vytvoríme súbor zaner.sql. Vieme, že tento súbor obsahuje SQL príkazy na vytvorenie a zároveň naplnenie požadovanej tabuľky.

Source (\.) programu MySQL

Môžeme povedať, že súbor zaner.sql je akýsi dávkový súbor SQL príkazov. Hovoríme mu aj SQL skript (z angl. script). No dobre, ale ako najjednoduchšie tieto príkazy vykonať bez toho, aby sme ich prácne preťukávali zo súboru do klávesnice? No predsa jednoduchým spustením tohto skriptu. Náš starý známy monitor MySQL má jeden parameter, ktorý umožňuje vykonať súbor s SQL skriptom automaticky tak, ako keby boli jednotlivé príkazy zadávané z klávesnice. Tento parameter sa nazýva source (zdroj), a označuje sa aj skrátene „\.” (pozri help). Ak teda zadáme:
mysql> source zaner.sql;

dostaneme požadovaný výsledok. Výpis č. 1 ukazuje, že boli vykonané jednotlivé príkazy zo súboru zaner.sql:

Veľmi často potrebujeme v databázach  okrem textových a číselných hodnôt uchovávať aj údaje o čase a dátume. Tie zaznamenávajú rôzne udalosti v konkrétnom okamihu. Asi najdôležitejším údajom býva dátum. Ako príklad môže poslúžiť prípad, keď sa v každej knižnici  zaznamenáva dátum zápožičky a dátum vrátenia kníh. Aj o každom úradnom úkone sa zaznamenáva dátum jeho vykonania. Preto si teraz povieme niečo o uchovávaní dátumu a času a ukážeme si aj operácie s týmito údajmi.

Všetko to, čo sa týka dátumu a času, môžeme v MySQL rozdeliť do dvoch skupín:

-      dátumové a časové typy
-      dátumové a časové funkcie

Najdôležitejšie dátové typy už poznáme. Sú to buď číselné typy (napr. INT...), alebo textové typy (CHAR, VARCHAR...). Teraz k nim pridáme dátumové a časové typy.

Dátumové a časové typy
MySQL podporuje tieto časové a dátumové typy:
TIME – uloženie časových údajov
DATE – uloženie dátumu
DATETIME – združené údaje dátumu a času
YEAR – uloženie roku
TIMESTAMP – tzv. časová pečiatka

Formát jednotlivých typov sa líši, preto si teraz tieto typy preberieme.

DATETIME
Tento typ sa používa, keď potrebujeme hodnotu, ktorá obsahuje obidve informácie – dátumovú aj časovú.
Formát: ‘RRRR-MM-DD HH:MM:SS’
Podporovaný rozsah: od ‘1000-01-01 00:00:00’ do ‘9999-12-31 23:59:59’
Vidíme, že podporovaný rozsah je veľmi veľký, vyhovie aj veľmi náročným aplikáciám.

DATE
Tento typ použijeme, keď potrebujeme iba dátumovú informáciu, bez časovej časti.
Formát: ‘RRRR-MM-DD’
Podporovaný rozsah: od ‘1000-01-01’ do ‘9999-12-31’

TIMESTAMP
Tento typ je veľmi zvláštny. Pomenujeme si ho časová pečiatka. Skutočne, ak máme v tabuľke nadefinovaný určitý stĺpec typu TIMESTAMP, vždy a automaticky sa pri operácii INSERT alebo UPDATE  na stanovenom riadku zároveň do tejto položky uloží aktuálny časový údaj v stanovenom formáte.
Formát: ‘RRRRMMDDHHMMSS’
Podporovaný rozsah: od ‘19700101000000’ do ‘20371231235959’
Všimnime si podporovaný rozsah. Začína sa počiatkom roku 1970 a končí poslednou sekundou roku 2037. No myslím, že aj tento rozsah postačuje našej práci.
Tento typ môže mať aj zmenený rozsah. Stačí, ak pri jeho definovaní stanovíme parametrom príslušnú veľkosť zaznamenávaného poľa, tak ako je to v tab. č. 1:

Parameter

Zobrazovaný formát

TIMESTAMP(14)

RRRRMMDDHHMMSS

TIMESTAMP(12)

RRMMDDHHMMSS

TIMESTAMP(10)

RRMMDDHHMM

TIMESTAMP(8)

RRRRMMDD

TIMESTAMP(6)

RRMMDD

TIMESTAMP(4)

RRMM

TIMESTAMP(2)

RR

Ak si pozorne prezrieme túto tabuľku, základný formát sa neskracuje sprava, ale mení sa formát zobrazovania roka zo štvorciferného na dvojciferný. Treba pripomenúť, že ak zadáme typ TIMESTAMP bez parametra, defaultne (štandardne) sa uvažuje úplný 14-miestny formát.

Poznámka:
Aj keď sú DATE, DATETIME a TIMESTAMP veľmi príbuzné, nemajú rovnaký rozsah platnosti. Zatiaľ čo dátum 20-11-1963 môže byť uložený v DATE alebo DATETIME, pre TIMESTAMP je neplatný a bude konvertovaný na nuly.
Ako by sme mohli túto zaujímavú funkciu využiť? To si ukážeme ďalej.

TIME
Tento typ je zobrazovaný vo formáte HH:MM:SS, (alebo HHH:MM:SS pre rozšírenú hodnotu) a môže dosahovať rozsah hodnôt od -838:59:59 do 838:59:59. Nie, nie je to preklep, v tomto type môžeme ukladať aj časový údaj väčší ako 24 hodín, dokonca môže nadobúdať aj záporné hodnoty.

TIME’ hodnoty môžeme zadávať v týchto formátoch:
a)    ako reťazec v ‘HH:MM:SS’ formáte
Sú možné aj príbuzné syntaxe, teda ‘10:24:56’ je rovnaký ako ‘10.24.56’

b)    ako reťazec bez dvojbodiek, teda vo formáte ‘HHMMSS’
Teda ‘101112’ je chápaný ako ‘10:11:12’. Ale zápis ‘109745’ je neplatný, lebo 97 minút neexistuje

c)    ako číslo vo HHMMSS formáte (teda bez apostrofov)
Číslom 101112 sa rozumie čas 10:11:12

d)    ako výsledok funkcie, ktorá vráti hodnotu, akceptovateľnú TIME kontextom, napr. Current_Time

Pre TIME hodnoty, prezentované ako reťazec, netreba používať dve cifry v prezentácii hodín, minút a sekúnd, ktoré sú menšie ako číslo 10. Teda zápis ‘8:3:2’ je to isté ako ‘08:03:02’.

Pozor!
Buďme opatrní pri zadávaní neúplných údajov! MySQL „počíta“ číslice sprava. Predpokladá, že dve pravé číslice reprezentujú sekundy. Takže  zápis ‘11:12’, ‘1112’ a 1112 (spomeňme si, že v apostrofoch sú reťazce – strings, bez apostrofov čísla – numeric) reprezentuje 00:11:12, teda 11 minút a 12 sekúnd, a nie 11 hodín a 12 minút! Jednoducho ‘12’ prezentuje 12 sekúnd.
Ak by sme chceli zadať údaj väčší ako povolený rozsah, napr. 850:00:00, bude aproximovaný na maximálny rozsah, teda 838:59:59. Neplatné časové údaje sú konvertované na 00:00:00, takže je veľmi ťažké rozhodnúť, či sa do programu uložil čas polnoci, alebo niekto zadal zlý časový údaj, ktorý bol naň konvertovaný.

YEAR
YEAR
je jednobajtový typ, ktorý reprezentuje roky. MySQL vracia a zobrazuje YEAR hodnoty vo formáte ‘YYYY’. Rozsah je od ‘1901’ ‘2155’.
YEAR hodnoty môžeme zadávať v týchto formátoch:
a)    ako 4-miestny reťazec v rozsahu od ‘1901’ do ‘2155’
b)    ako 4-miestne číslo v rozsahu od 1901 do 2155
c)    ako 2-miestny reťazec v rozsahu od ‘00’ do ‘99’

Hodnoty v rozsahu ‘00’ až ‘69’ sú konvertované na hodnoty od ‘2000’ do ‘2069’, hodnoty v rozsahu ‘70’ až ‘99’ sú konvertované na hodnoty od ‘1970’ do ‘1999’
d)    ako 2-miestne číslo v rozsahu 1 až 99

Hodnoty v rozsahu 1 až 69 sú konvertované na hodnoty od 2001 do 2069, hodnoty v rozsahu 70 až 99 sú konvertované na hodnoty od 1970 do 1999. Všimnime si, že v tomto prípade nemôžeme zadefinovať ten magický rok 2000! Preto odporúčam používať rok v reťazcovom formáte
e)    ako výsledok funkcie, ktorá vracia hodnoty akceptovateľné v YEAR kontexte, napr. NOW()

Uvedomme si, že nekorektné hodnoty YEAR sú konvertované na hodnotu ‘0000’ .

Dátumové a časové funkcie
Zatiaľ čo dátumové a časové typy určovali štruktúru (formát), v akej sú ukladané dané hodnoty, dátumové a časové funkcie vracajú práve tie hodnoty. Tak ako ozajstné funkcie aj tieto spravidla obsahujú parameter, na základe ktorého vracajú požadovanú hodnotu.
Teraz si ukážeme najdôležitejšie funkcie, ktoré asi najčastejšie použijeme v našich projektoch.
A ako vyzískame tieto hodnoty? No predsa pomocou nám už veľmi známeho príkazu SQL – SELECT. Názvy jednotlivých funkcií sú odvedené od ich činností v anglickom jazyku. Pre lepšiu čitateľnosť ich budeme uvádzať s veľkými začiatočnými písmenami. Zadávať ich však môžeme rôzne. Presné použitie pri jednotlivých ukážkach je zrejmé z  príslušných výpisov.

DayOfWeek(dátum)
Vracia index dňa v týždni v zadanom dátume. Index dňa je číselná hodnota, kde
1 = Sunday, 2 = Monday, ...., 7 = Saturday.  Na výpise č. 2 vidíme, že 20. januára tohto roku bola naozaj sobota.

WeekDay(dátum)
Je veľmi podobný ako DayOfWeek. Vracia index dňa v týždni, ale index nadobúda tieto hodnoty: 0 = Monday, 1 = Tuesday, .... , 6 = Sunday.  Všimnime si, že zatiaľ čo v prvom prípade sme dátum zadali ako reťazec (string), v tomto prípade sme použili numerický zápis. To potvrdzuje spomenuté formáty ukladania časových a dátumových typov.

DayOfMonth(dátum)
Ak chceme z dátumu vyselektovať číslo dňa v danom mesiaci, použijeme DayOfMonth, tak ako na výpise č. 4.
Aj tu sme použili jeden z možných variantov zápisu dátumu:

DayOfYear(dátum)
Koľkýže to bude deň od začiatku roku, ten 3. máj 2001? No predsa stodvadsiaty tretí, tak ako na výpise č. 5:

DayName(dátum)
Ak nám nevyhovuje, že funkcie DayOfWeek alebo WeekDay vracajú index dňa v týždni a my by sme radšej prijali názov dňa (bohužiaľ len v angličtine), použijeme funkciu DayName, tak ako na výpise č. 6:

MonthName(dátum)
Obdobným spôsobom môžeme získať názov mesiaca z dátumu. Použijeme funkciu MonthName, tak ako na výpise č. 7:

Year(dátum)
Podobne ako sme selektovali deň v mesiaci, je možné selektovať aj rok. Príklad je na výpise č. 8:

Všimnime si, že sme rok zadali len v dvojmiestnom tvare, ale SQL server ho reprezentuje v úplnom štvormiestnom výpise. Pozor! Nemýľme si meno funkcie s názvom dátového typu!

Hour(čas)
Touto funkciou vyselektujeme hodinu zo zadaného časového údaja (výpis č. 9):

Minute(čas)
Aj minúty môžeme vyselektovať zo zadaného času (výpis č. 10):

Všimnime si formát zadávania času (výpis č. 11), ako aj v ďalších príkladoch:

Second(čas)
Obdobne je to so sekundami, ale teraz zadáme čas ako číslo (výpis č. 12):

Predstavme si, že nedopatrením zadáme nevhodný formát času, napr. 12 hodín 25 minút a 79 sekúnd. Čo sa stane? Odpoveď je na výpise č. 13:

Je to prázdna hodnota. (Pozor! Nie nulová!)

To_Days(dátum)
Táto funkcia vráti počet dní od roku 0 (nula). Príklad je na výpise č. 14:

Áno, je to presne 730 870 dní od počiatku letopočtu.

From_Days(n)
Naopak, ak vieme, koľko dní od počiatku letopočtu to bolo, a my chceme vedieť presný dátum, použijeme funkciu ako na výpise č. 15:

Nie, nepočítal som to, len som predchádzajúci príklad zväčšil o jeden deň. A funguje to!

Date_Format(dátum, formát)
Niektoré spomenuté selekčné funkcie sú integrované v tejto veľmi zaujímavej funkcii. Pomocou nej sme schopní vytvoriť rozmanité výpisy. Táto funkcia obsahuje dva parametre: dátum a formát. Parameter formát určuje, ako bude formulovaný výsledný výpis. Príklady najčastejšie používaných formátov sú v tab. č. 2:

Formát

Opis

%M

meno mesiaca, napr. January, ...December

%W

deň v týždni, napr. Sunday ...Saturday

%Y

rok, na 4 číslice

%y

rok, na 2 číslice

%a

skrátený deň v týždni, napr. Sun ...Sat

%b

skrátené meno mesiaca, napr. Jan... Dec

%d

deň v mesiaci, číselne 00...31

%e

deň v mesiaci, číselne 0...31

%m

mesiac, číselne 01...12

%c

mesiac, číselne 1...12

%j

deň v roku 001...366

%H

hodiny 00...23

%k

hodiny 0...23

%h

hodiny 01...12

%l

hodiny 1...12

%i

minúty 00...59

%S

sekundy 00...59

%r

čas 12-hodinový formát hh:mm:ss AM/PM

%T

čas 24-hodinový formát hh:mm:ss

Ak chceme zo zadaného dátumu a času vyformátovať zápis, kde bude názov dňa, názov mesiaca a rok, použijeme parametre %W, %M a %Y, tak ako na výpise č. 16:

Efektné, však?

Time_Format(čas, formát)
Toto je obdoba predchádzajúcej funkcie, ale so zameraním na časovú zložku. Preto môžeme používať len tie parametre, ktoré sa týkajú času. Ostatné budú poskytovať hodnotu NULL alebo 0 (nula).

CurDate(), Current_Date()
Táto funkcia bez parametrov vráti aktuálny dátum v operačnom systéme. Príklad je na výpise č. 17:

Keby sme potrebovali dostať výsledok v numerickom tvare, stačí, ak v príkaze SELECT pripočítame nulu, tak ako je to na výpise č. 18:

CurTime(), Current_Time()
Táto funkcia bez parametrov vráti aktuálny čas v operačnom systéme. Príklad je na výpise č. 19:

Keby sme potrebovali dostať výsledok v numerickom tvare, stačí, ak v príkaze SELECT pripočítame nulu, tak ako je to na výpise č. 20:

Now(), SysDate(), Current_TimeStamp()
Túto funkciu mám najradšej. Používam ju veľmi často. Prečo? Lebo spája obidve predchádzajúce funkcie. Výpis č. 21 ukazuje, čo je hodnotou tejto funkcie:

Aj tento výsledok môžeme pričítaním nuly previesť na numerický tvar (výpis č. 22):

Napadlo vás, ako by sa efektne dala táto funkcia využiť? No predsa môžeme ju použiť ako parameter dátum alebo čas v uvedených parametrických funkciách! Takisto môžeme použiť všetky bezparametrické funkcie. Presvedčme sa jedným príkladom na výpise č. 23:

Jednoduché, nie?
Túto funkciu často využijeme pri zadávaní časovej pečiatky, ako si ukážeme neskôr.

Sec_To_Time(sekundy)
Táto funkcia prevedie zadaný počet sekúnd na čas vo formáte HH:MM:SS, tak ako na výpise č. 24:

Ak neveríte, prepočítajte si to ako domácu úlohu.

Time_To_Sec(čas)
Táto funkcia je opakom predchádzajúcej. Zo zadaného času vypočíta dĺžku trvania v sekundách. Príklad je na výpise č. 25:

Ani v tomto príklade sme čas nezadali ako reťazec, ale sme využili bezparametrovú funkciu curtime() ako parameter parametrickej funkcie time_to_sec.

Použitie
Vidíme, že MySQL má mnoho funkcií na prácu s časom a dátumom. Ich využitie je veľmi široké. Asi najviac využijeme funkcie na získanie týchto údajov v základnom formáte. Ale keď budeme potrebovať vypočítať rozdiel časov alebo dátumov, použijeme funkcie na prevod na počet dní, tie potom odpočítame a výsledok prevedieme späť na konkrétny dátum.

Cvičenie
Aby sme si ujasnili, ako pracujú tieto funkcie pri vkladaní do tabuľky, vytvorme si cvičnú tabuľku s názvom CASOMIERA s týmito stĺpcami:

Položka

Typ položky

cas

TIME

datum

DATE

datcas

DATETIME

rok

YEAR

peciatka

TIMESTAMP

Teraz vložíme pomocou príkazu INSERT INTO postupne tieto hodnoty:
mysql> insert into casomiera values (curtime(), curdate(), now(), year(now()), null);
mysql> insert into casomiera values (‘’,’’,’’,’’,’’);
mysql> insert into casomiera values (null, null, null, null, null);
mysql> insert into casomiera(cas) values (curtime());

Takto sme naplnili tabuľku CASOMIERA, v ktorej sú štyri záznamy.
Teraz vykonáme príkaz:
mysql> select * from casomiera;

a dostaneme výsledok ako na výpise č. 26:

Pozrime sa bližšie na jednotlivé záznamy. Vieme, že jednotlivé riadky tabuľky CASOMIERA zodpovedajú postupnému zadávaniu uvedeného príkazu INSERT.

Všimnime si tieto dôležité skutočnosti:
-      Aj keď sme v 1. riadku zadali hodnotu null pre položku PECIATKA, predsa sa uložil aktuálny dátum a čas operačného systému. Ako keby SQL server opečiatkoval (dátumom a časom) to, že vykonal tento príkaz. Porovnajme, že sa obsah stĺpca PECIATKA zhoduje s údajmi v stĺpcoch CAS, DATUM a DATCAS.
-      Ak sme príkazom INSERT vkladali do všetkých položiek prázdne reťazce (‘’), SQL server skonvertoval  tieto položky vrátane časovej pečiatky TIMESTAMP na samé nuly.
-      Ak sme vložili hodnotu null (čo nie je nula ani prázdny reťazec, ale akési prázdno), všetky položky nadobudli hodnotu null okrem časovej pečiatky. Takto zase server opečiatkoval vykonanie tohto príkazu.
-      Nakoniec sme vložili iba hodnotu aktuálneho času systému (curtime()) do položky CAS. Ako vidíme, tá sa zapísala, ostatné položky nadobudli hodnotu null. A znova server opečiatkoval vykonanie tohto príkazu.

Je zrejmé, že z údajov časovej pečiatky sme schopní vyčítať, kedy bol daný záznam uložený do tabuľky. Pripomínam, že sa TIMESTAMP, t. j. pečiatka, mení nielen pri príkaze INSERT, ale aj pri príkaze UPDATE. Ostatné SQL príkazy nemajú na časovú pečiatku nijaký vplyv. Vyskúšajte!

Tááák, prebrali sme najdôležitejšie SQL príkazy a parametre príkazu SELECT. Ich počet je podstatne rozsiahlejší, ale pre našu prácu bude stačiť tento výpočet. Tentoraz mi nevyšiel priestor, aby sme si ukázali jednu z najdôležitejších činností SQL servera – spájanie tabuliek. Takže nabudúce.

 

 

Zobrazit Galériu

Nechajte si posielať prehľad najdôležitejších správ emailom

Mohlo by Vás zaujímať

Ako na to

Tipy a triky: Ako na snímku obrazovky na akomkoľvek počítači s Windows?

02.12.2016 00:13

Ak snímky obrazovky robíte často apotrebujete napríklad funkcie na posun stránok alebo snímanie zobrazenia pri vyššom rozlíšení displeja, zrejme používate nejakú špecializovanú aplikáciu. Väčšina použ ...

Ako na to 1

Tipy a triky: Ako aplikácii prednastaviť spúšťanie s administrátorskými právami?

30.11.2016 00:10

Väčšina aspoň trochu skúsenejších používateľov vie, že aj keď máte na operačnom systéme Windows vytvorený administrátorský účet, aplikácie pre bezpečnosť nefungujú vždy splnými administrátorskými práv ...

Ako na to 2

Tipy a triky: Ako vypnúť uzamykaciu obrazovku vo Windows 10?

29.11.2016 00:10

Rozčuľuje vás, že pred každým prihlásením doúčtu vášho počítača musíte prejsť uzamykacou obrazovkou? Windows 10 na tejto obrazovke ukazuje čas,dátum anejakú zaujímavú fotografiu zrôznych kútov sveta. ...

Žiadne komentáre

Vyhľadávanie

Kyocera - prve-zariadenia-formatu-a4-s-vykonom-a3

Najnovšie videá