SQL - vstavané funkcie: Rozdiel medzi revíziami

Z Kiwiki
Skočit na navigaci Skočit na vyhledávání
 
(5 medziľahlých úprav od rovnakého používateľa nie je zobrazených.)
Riadok 1: Riadok 1:
−
{{Skripta_dbs}} {{Draft}} Databázový systém mySQL podporuje obsahuje vstavané funkcie, často nazývané aj agregačné funkcie. Tieto funkcie môžeme rozdeliť do skupín podľa ich zamerania:  
+
{{Skripta_dbs}}
 +
Databázový systém MySQL obsahuje vstavané funkcie<ref>MySQL Useful Functions and Clauses - http://www.tutorialspoint.com/mysql/mysql-useful-functions.htm </ref>, ktoré môžeme rozdeliť do kategórií:
  
−
*Agregačné
+
*agregačné,
−
*Matematické
+
*matematické,
−
*Textové
+
*textové,
−
*Štatistické
+
*štatistické,
−
*Dátumové
+
*dátumové,
−
*Šifrovacie a komprimačné
+
*šifrovacie a komprimačné.
  
 
== Agregačné funkcie  ==
 
== Agregačné funkcie  ==
Riadok 16: Riadok 17:
 
Funkcia COUNT slúži na výpočet počtu záznamov v SQL dotaze.  
 
Funkcia COUNT slúži na výpočet počtu záznamov v SQL dotaze.  
  
−
'''Úloha:''' Zisti, koľko je v tabuľke city miest s kódom krajiny SVK (teda na Slovensku)  
+
'''Úloha:''' Zisti, koľko je v tabuľke ''city'' miest s kódom krajiny SVK (teda na Slovensku)  
  
 
<source lang="sql">
 
<source lang="sql">
Riadok 22: Riadok 23:
 
</source>  
 
</source>  
  
−
Výsledok: COUNT(*): 3
+
Výsledok:  
−
 
+
{| class=wikitable
−
Vylepšená verzia, pre prehľadnejšie čítanie výsledku: <source lang="sql">
+
|-
−
  SELECT COUNT(*) AS pocet FROM city WHERE CountryCode = "SVK"
+
!COUNT(*)
−
</source>
+
|-
−
 
+
|3
−
Výsledok: pocet: 3
+
|}
  
 
=== MAX() a MIN()  ===
 
=== MAX() a MIN()  ===
Riadok 51: Riadok 52:
 
  SELECT *  FROM city where Population=
 
  SELECT *  FROM city where Population=
 
     (SELECT MAX(Population) FROM city)
 
     (SELECT MAX(Population) FROM city)
−
</source> Výsledok:
+
</source>  
  
−
[[Image:Select max.png|center]]
+
Výsledok:  
 +
{| class=wikitable
 +
|-
 +
!ID
 +
!Name
 +
!CountryCode
 +
!District
 +
!Population
 +
|-
 +
|1024
 +
|Mumbai (Bombay)
 +
|IND
 +
|Maharashtra
 +
|10500000
 +
|}
  
 
=== AVG()  ===
 
=== AVG()  ===
  
−
Funkcia AVG() počíta aritmetický priemet množiny hodnôt.  
+
Funkcia AVG() počíta aritmetický priemer množiny hodnôt.  
  
  
Riadok 63: Riadok 78:
  
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT AVG(population) FROM city where CountryCode="SVK"
+
   SELECT AVG(population) FROM city WHERE CountryCode="SVK"
 
</source>  
 
</source>  
  
Riadok 69: Riadok 84:
 
{| class="wikitable"
 
{| class="wikitable"
 
|-
 
|-
−
! AVG(population))
+
! AVG(population)
 
|-
 
|-
 
| 261381.0000
 
| 261381.0000
Riadok 82: Riadok 97:
  
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT SUM(population) FROM city where CountryCode="SVK"  
+
   SELECT SUM(population) FROM city WHERE CountryCode="SVK"  
 
</source>  
 
</source>  
  
Riadok 102: Riadok 117:
  
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT STD(population) FROM city where CountryCode="CZE"  
+
   SELECT STD(population) FROM city WHERE CountryCode="CZE"  
 
</source>  
 
</source>  
  
Riadok 118: Riadok 133:
 
'''ABS(X)'''  
 
'''ABS(X)'''  
  
−
Funkcia ABS vypočíta absolútnu hodnotu z čísla X
+
Funkcia ABS vypočíta absolútnu hodnotu z čísla X.
  
 
Príklad:  
 
Príklad:  
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT ABS(-3);
+
   SELECT ABS(-3)
 
</source>  
 
</source>  
  
Riadok 140: Riadok 155:
 
Príklad:  
 
Príklad:  
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT SIN(PI()/2);
+
   SELECT SIN(PI()/2)
 
</source>  
 
</source>  
  
Riadok 158: Riadok 173:
 
Príklad:  
 
Príklad:  
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT COS(0);
+
   SELECT COS(0)
 
</source>  
 
</source>  
  
Riadok 174: Riadok 189:
  
 
Príklad: <source lang="sql">
 
Príklad: <source lang="sql">
−
   SELECT CEIL(7.55);
+
   SELECT CEIL(7.55)
 
</source>  
 
</source>  
  
Riadok 191: Riadok 206:
 
Príklad:  
 
Príklad:  
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT EXP(3);
+
   SELECT EXP(3)
 
</source>  
 
</source>  
  
Riadok 208: Riadok 223:
  
 
Príklad: <source lang="sql">
 
Príklad: <source lang="sql">
−
   SELECT FLOOR(7.55);
+
   SELECT FLOOR(7.55)
 
</source>  
 
</source>  
  
Riadok 223: Riadok 238:
 
'''FORMAT(X,N)'''  
 
'''FORMAT(X,N)'''  
  
−
FORMAT () funkcia sa používa na formátovanie čísla X v nasledujúcom formáte: ###,###,###.##. Pre číslo X sa použije práce N desatinných miest.
+
Funkcia FORMAT() sa používa na formátovanie čísla X. N je počet desatinných miest čísla X.
  
 
Príklad:  
 
Príklad:  
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT FORMAT(423423234.65434453,3);
+
   SELECT FORMAT(423423234.65434453,3)
 
</source>  
 
</source>  
  
Riadok 233: Riadok 248:
 
{| class="wikitable"
 
{| class="wikitable"
 
|-
 
|-
−
!FORMAT(423423234.65434453,3);
+
!FORMAT(423423234.65434453,3)
 
|-
 
|-
 
|423,423,234.654
 
|423,423,234.654
Riadok 241: Riadok 256:
 
'''LOG(X)'''  
 
'''LOG(X)'''  
  
−
Funkcia LOG vypočíta prirodzený logaritmus z čísla X. (Poznámka: prirodzený logaritmus má základ ''e'')
+
Funkcia LOG vypočíta prirodzený logaritmus z čísla X (Poznámka: prirodzený logaritmus má základ ''e'').
  
 
Príklad:  
 
Príklad:  
Riadok 259: Riadok 274:
 
'''LOG10(X)'''  
 
'''LOG10(X)'''  
  
−
Vypočíta desiatkový logaritmus z čísla X
+
Vypočíta desiatkový logaritmus z čísla X.
  
 
Príklad: <source lang="sql">
 
Príklad: <source lang="sql">
Riadok 279: Riadok 294:
 
Príklad:  
 
Príklad:  
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT PI();
+
   SELECT PI()
 
</source>  
 
</source>  
  
Riadok 291: Riadok 306:
  
  
−
'''POW( ''X, Y'' )'''  
+
'''POW(''X, Y'')'''  
  
 
Táto funkcia vracia hodnotu X umocnenú na Y. Je to alias k funkcii POWER.
 
Táto funkcia vracia hodnotu X umocnenú na Y. Je to alias k funkcii POWER.
  
 
Príklad: <source lang="sql">
 
Príklad: <source lang="sql">
−
   SELECT POWER(3,3);
+
   SELECT POWER(3,3)
 
</source>  
 
</source>  
  
Riadok 316: Riadok 331:
 
Príklad:
 
Príklad:
 
<source lang="sql">  
 
<source lang="sql">  
−
   SELECT ROUND(5.693893,2);
+
   SELECT ROUND(5.693893,2)
 
</source>
 
</source>
  
Riadok 323: Riadok 338:
 
{| class="wikitable"
 
{| class="wikitable"
 
|-
 
|-
−
!  ROUND(5.693893,2);
+
!  ROUND(5.693893,2)
 
|-
 
|-
 
| 5.69
 
| 5.69
Riadok 331: Riadok 346:
 
'''SQRT(''X'')'''  
 
'''SQRT(''X'')'''  
  
−
Funkcia SQRT určí druhú odmocninu čísla X. Ak je X záporné funkcia SQRT vráti ''NULL''.  
+
Funkcia SQRT vypočíta druhú odmocninu čísla X. Ak je X záporné funkcia SQRT vráti ''NULL''.  
  
 
Príklad:
 
Príklad:
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT SQRT(16);
+
   SELECT SQRT(16)
 
</source>  
 
</source>  
  
Riadok 347: Riadok 362:
  
  
−
'''TAN(A)'''
+
'''TAN(X)'''
  
−
Funkcia TAN - vypočíta tangens uhla X. Uhol A je v radiánoch.
+
Funkcia TAN - vypočíta tangens uhla X. Uhol X je v radiánoch.
  
−
Úloha: Aká je hodnota TAN(45)? V uhlovej miere je uhol 45° rovný PI/4
+
Príklad: Aká je hodnota TAN(45)?  
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT TAN(PI()/4);
+
   SELECT TAN(PI()/4)
 
</source>  
 
</source>  
  
Riadok 391: Riadok 406:
 
Funkcia CHAR() prevedie každý svoj argument X na znak podľa použitej znakovej tabuľky.
 
Funkcia CHAR() prevedie každý svoj argument X na znak podľa použitej znakovej tabuľky.
  
−
Príklad: V príklade budeme generovať 2 náhodné čísla.
 
 
<source lang="sql">
 
<source lang="sql">
 
   SELECT CHAR(77,121,83,81,76)
 
   SELECT CHAR(77,121,83,81,76)
Riadok 407: Riadok 421:
 
'''CONCAT'''  
 
'''CONCAT'''  
  
−
Funkcia CONCAT() sa používa na spojenie dvoch reťazcov do jedného reťazca
+
Funkcia CONCAT() sa používa na spojenie dvoch reťazcov do jedného reťazca.
  
−
Úloha: V tabuľke city spoj atribúty: name a CountryCode do jedného stĺpca s názvom ''Mesto''
+
Úloha: V tabuľke ''city'' spoj atribúty: ''name'' a ''CountryCode'' do jedného stĺpca s názvom ''Mesto''
  
 
<source lang="sql">
 
<source lang="sql">
Riadok 440: Riadok 454:
 
'''FIELD(str, str1, str2, str3, ...)'''  
 
'''FIELD(str, str1, str2, str3, ...)'''  
  
−
Funkcie FIELD vracia index (pozícia začínajúca na hodonte 1) reťazca str v reťazcoch  str1, str2, str3, ... . Ak nie je reťazec str nájdený, funkcia vráti 0.
+
Funkcie FIELD vracia index (pozícia začínajúca na hodonte 1) reťazca ''str'' v reťazcoch  ''str1, str2, str3'', ... . Ak nie je reťazec ''str'' nájdený, funkcia vráti 0.
  
 
Príklad:
 
Príklad:
Riadok 457: Riadok 471:
 
'''LOWER(str)'''  
 
'''LOWER(str)'''  
  
−
Funkcia LOWER() konvertuje reťazec ''str'' na malé písmená
+
Funkcia LOWER() konvertuje reťazec ''str'' na malé písmená.
  
−
Úloha: konvertuj code na malé písmená v tabuľke country.
+
Úloha: Konvertuj kód krahiny (''code'') na malé písmená v tabuľke ''country''.
  
 
<source lang="sql">
 
<source lang="sql">
Riadok 491: Riadok 505:
 
'''UPPER'''  
 
'''UPPER'''  
  
−
Funkcia UPPER() konvertuje reťazec na veľké písmená
+
Funkcia UPPER() konvertuje reťazec na veľké písmená.
  
  
−
'''Úloha:''' konvertuj name na veľké písmená v tabulke city
+
'''Úloha:''' konvertuj názov mesta (''name'') na veľké písmená v tabuľke ''city''.
  
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT upper(name) from city
+
   SELECT UPPER(name) FROM city
 
</source>  
 
</source>  
  
Riadok 503: Riadok 517:
 
{| class="wikitable"
 
{| class="wikitable"
 
|-
 
|-
−
!upper(name
+
!upper(name)
 
|-
 
|-
 
|MAZAR-E-SHARIF
 
|MAZAR-E-SHARIF
Riadok 535: Riadok 549:
 
|}
 
|}
  
−
<br> '''RTRIM(''str'')'''  
+
 
 +
'''RTRIM(''str'')'''  
  
 
Z konca reťazca ''str'' odstráni nečitateľné znaky (medzery).  
 
Z konca reťazca ''str'' odstráni nečitateľné znaky (medzery).  
Riadok 574: Riadok 589:
 
'''ADDDATE(date,INTERVAL days)'''  
 
'''ADDDATE(date,INTERVAL days)'''  
  
−
Funkcia ADDDATE pripočíta k zadanému dátumu ''date'' hodnotu ''days''. Parameter ''days'' môžeme zadávať pomocou funkcie INTERVAL, alebo ako celé číslo.
+
Funkcia ADDDATE pripočíta k zadanému dátumu ''date'' zadaný počet dní (hodnota ''days''). Parameter ''days'' môžeme zadávať pomocou funkcie INTERVAL, alebo ako celé číslo.
  
 
Príklad:  
 
Príklad:  
Riadok 599: Riadok 614:
  
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT ADDTIME('2010-12-20 10:00:0.0','1 1:1:1.000002');
+
   SELECT ADDTIME('2010-12-20 10:00:0.0','1 1:1:1.000002')
 
</source>  
 
</source>  
 
Výsledok:
 
Výsledok:
Riadok 617: Riadok 632:
 
Príklad:
 
Príklad:
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT CURDATE();
+
   SELECT CURDATE()
 
</source>  
 
</source>  
  
Riadok 636: Riadok 651:
 
Príklad:
 
Príklad:
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT CURTIME();
+
   SELECT CURTIME()
 
</source>  
 
</source>  
 
Výsledok:
 
Výsledok:
Riadok 673: Riadok 688:
 
Príklad:
 
Príklad:
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT DATE('2010-12-31 01:02:03');
+
   SELECT DATE('2010-12-31 01:02:03')
 
</source>
 
</source>
  
Riadok 688: Riadok 703:
  
 
DAY je synonymom pre DAYOFMONTH.
 
DAY je synonymom pre DAYOFMONTH.
 +
  
  
Riadok 697: Riadok 713:
 
Príklad:
 
Príklad:
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT DAYNAME('2010-12-16');
+
   SELECT DAYNAME('2010-12-16')
 
</source>
 
</source>
  
Riadok 712: Riadok 728:
 
'''DAYOFWEEK(D)'''  
 
'''DAYOFWEEK(D)'''  
  
−
Vracia deň v týždni index pre dáta (1 = nedeľa, 2 = pondelok., 7 = sobota). Tieto hodnoty indexu zodpovedajú štandardu ODBC.
+
Vráti číslo dňa v týždni (1 = nedeľa, 2 = pondelok, ... , 7 = sobota). Tieto hodnoty indexu zodpovedajú štandardu ODBC<ref>ODBC Scalar Functions - http://msdn.microsoft.com/en-us/library/bb630290.aspx</ref>.
  
 
Príklad:
 
Príklad:
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT DAYOFWEEK('2010-12-31 01:02:03');
+
   SELECT DAYOFWEEK('2010-12-31 01:02:03')
 
</source>
 
</source>
  
Riadok 722: Riadok 738:
 
{| class="wikitable"
 
{| class="wikitable"
 
|-
 
|-
−
! DAYOFWEEK('2010-12-31 01:02:03');
+
! DAYOFWEEK('2010-12-31 01:02:03')
 
|-
 
|-
 
| 6
 
| 6
Riadok 734: Riadok 750:
 
Príklad:
 
Príklad:
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT DAYOFMONTH('2010-12-31 01:02:03');
+
   SELECT DAYOFMONTH('2010-12-31 01:02:03')
 
</source>
 
</source>
  
Riadok 740: Riadok 756:
 
{| class="wikitable"
 
{| class="wikitable"
 
|-
 
|-
−
! DAYOFMONTH('2010-12-31 01:02:03');
+
! DAYOFMONTH('2010-12-31 01:02:03')
 
|-
 
|-
 
|31  
 
|31  
Riadok 748: Riadok 764:
 
'''DAYOFYEAR'''  
 
'''DAYOFYEAR'''  
  
−
Vracia deň v roku na dátum, v rozsahu 1 až 366.
+
Vracia poradové číslo dňa v roku v rozsahu 1 až 366.
  
 
Príklad:
 
Príklad:
Riadok 820: Riadok 836:
 
'''WEEK'''  
 
'''WEEK'''  
  
−
Z dátumu  určí číslo týždňa.
+
Z dátumu  určí číslo týždňa v intervale 1 až 52.
  
 
Príklad:
 
Príklad:
  
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT WEEK('2010-12-18');
+
   SELECT WEEK('2010-12-18')
 
</source>
 
</source>
  
Riadok 855: Riadok 871:
  
 
== Šifrovacie a komprimačné  ==
 
== Šifrovacie a komprimačné  ==
 +
Nasledujúce funkcie ponúkajú šifrovanie, dešifrovanie a hašovanie textových dát<ref>Encryption and Compression Functions - http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html</ref>.
 +
  
 
'''AES_ENCRYPT( ''nezasifrovany_text , kluc'' )'''  
 
'''AES_ENCRYPT( ''nezasifrovany_text , kluc'' )'''  
  
−
Funkcie AES_ENCRYPT() a AES_DECRYPT() šifrujú a dešifrujú dáta pomocou oficiálneho algoritmu [http://en.wikipedia.org/wiki/Advanced_Encryption_Standard AES](Advanced Encryption Standard), ktorý bol predtým známy ako "Rijndael." Dĺžka šifrovacieho kľúča je 128 bitov. Dĺžku kľúča sa môže predĺžiť až na 256 bitov. Vybrali sme si 128 kúskov, pretože to je oveľa rýchlejšie a je dostatočne zabezpečený pre väčšinu účelov.  
+
Funkcie AES_ENCRYPT() a AES_DECRYPT() šifrujú a dešifrujú dáta pomocou oficiálneho algoritmu [http://en.wikipedia.org/wiki/Advanced_Encryption_Standard AES](Advanced Encryption Standard). Dĺžka šifrovacieho kľúča je 128 bitov. Dĺžku kľúča sa môže predĺžiť až na 256 bitov.  
  
 
<source lang="sql">
 
<source lang="sql">
−
   insert into test (text,zasifrovane,heslo) values ("kiwiki",AES_ENCRYPT("kiwiki","123456789"),"1234567789")
+
   INSERT INTO test (text,zasifrovane,heslo) VALUES ("kiwiki",AES_ENCRYPT("kiwiki","123456789"),"1234567789")
−
</source> Výsledok: [[Image:Sql AES ENCRYPT.png|frame|center]]  
+
</source>
 +
 
 +
Výsledok:
 +
[[Image:Sql AES ENCRYPT.png|frame|center|Šifrovanie pomocou AES_ENCRYPT]]  
 +
 
  
 
'''AES_DECRYPT( ''zasifrovany_text , kluc'')'''  
 
'''AES_DECRYPT( ''zasifrovany_text , kluc'')'''  
  
 
Táto funkcia dešifruje dáta pomocou šifrovacieho algoritmus [http://en.wikipedia.org/wiki/Advanced_Encryption_Standard AES] (Advanced Encryption Standard) <source lang="sql">
 
Táto funkcia dešifruje dáta pomocou šifrovacieho algoritmus [http://en.wikipedia.org/wiki/Advanced_Encryption_Standard AES] (Advanced Encryption Standard) <source lang="sql">
−
   SELECT text, AES_DECRYPT(zasifrovane,"123456789") as rozsifrovane ,heslo from test
+
   SELECT text, AES_DECRYPT(zasifrovane,"123456789") AS rozsifrovane ,heslo FROM test
 
</source>  
 
</source>  
  
−
Výsledok: [[Image:Sql AES DECRYPT.png|frame|center]]  
+
Výsledok:
 +
[[Image:Sql AES DECRYPT.png|frame|center|Dešifrovanie pomocou AES_DECRYPT]]  
 +
 
  
 
'''COMPRESS( ''str'' ), UNCOMPRESS( ''str'' )'''  
 
'''COMPRESS( ''str'' ), UNCOMPRESS( ''str'' )'''  
  
−
Komprimuje/dekomprimuje reťazec a vráti výsledok ako binárny reťazec. Táto funkcia vyžaduje konfigur8ciu MySQL servara tak, aby boli zostavený s kompresnou knižnicu ako zlib. V opačnom prípade je návratová hodnota vždy NULL. Komprimovaný reťazec môže byť nekomprimované s UNCOMPRESS().  
+
Komprimuje/dekomprimuje reťazec a vráti výsledok ako binárny reťazec. Táto funkcia vyžaduje konfiguráciu MySQL servara tak, aby boli zostavený s kompresnou knižnicu ''zlib''. V opačnom prípade je návratová hodnota vždy NULL. Komprimovaný reťazec môže byť dekomprimovaný funkciou UNCOMPRESS().  
  
 
<source lang="sql">
 
<source lang="sql">
 
   INSERT INTO test (text,zasifrovane) VALUES ("kiwiki",COMPRESS("kiwiki"))
 
   INSERT INTO test (text,zasifrovane) VALUES ("kiwiki",COMPRESS("kiwiki"))
−
</source> Výsledok: [[Image:Sql compress.png|frame|center]]  
+
</source>
 +
 
 +
Výsledok: [[Image:Sql compress.png|frame|center|Výsledok komprimácie pomocou funkcie COMPRESS]]  
  
  
 
<source lang="sql">
 
<source lang="sql">
 
   SELECT id, text, UNCOMPRESS(zasifrovane) AS dekomprimovane FROM test
 
   SELECT id, text, UNCOMPRESS(zasifrovane) AS dekomprimovane FROM test
−
</source> Výsledok: [[Image:Sql uncompress.png|frame|center]]  
+
</source>  
 +
 
 +
Výsledok: [[Image:Sql uncompress.png|frame|center|Výsledok dekomprimácie pomocou funkcie UNCOMPRESS]]  
  
  
Riadok 892: Riadok 920:
 
Príklad:
 
Príklad:
 
<source lang="sql">
 
<source lang="sql">
−
  SELECT ENCODE("kiwiki", "heslo");
+
  SELECT ENCODE("kiwiki", "heslo")
 
</source>
 
</source>
−
 
  
 
Výsledok:
 
Výsledok:
−
[[Súbor:sql - encode.png|center]]
+
[[Súbor:sql - encode.png|center|frame|Zašifrovanie pomocou funkcie ENCODE]]
  
  
 
'''DECODE(''crypt_str,heslo'')'''  
 
'''DECODE(''crypt_str,heslo'')'''  
 
   
 
   
−
Funkcia DECODE je opak funkcie ENCODE. Dešifruje zašifrované dáta funkciou ENCODE pomocou vloženého hesla pri šifrovaní.
+
Funkcia DECODE je opak funkcie ENCODE. Dešifruje zašifrované dáta funkciou ENCODE pomocou vloženého hesla pri šifrovaní<ref>Decode Demo 1 - http://www.bhatipoglu.com/entry/41/decode-demo-1</ref>.
  
  
 
Príklad:
 
Príklad:
 
<source lang="sql">
 
<source lang="sql">
−
  SELECT DECODE( ENCODE("kiwiki","heslo"), "heslo");
+
  SELECT DECODE( ENCODE("kiwiki","heslo"), "heslo")
 
</source>
 
</source>
  
Riadok 914: Riadok 941:
 
{| class="wikitable"
 
{| class="wikitable"
 
|-
 
|-
−
! DECODE( ENCODE("kiwiki","heslo"), "heslo");
+
! DECODE( ENCODE("kiwiki","heslo"), "heslo")
 
|-
 
|-
 
| kiwiki
 
| kiwiki
Riadok 922: Riadok 949:
 
'''DES_DECRYPT( ''text, [kluc]''), DES_ENCRYPT(''text , kluc'')'''  
 
'''DES_DECRYPT( ''text, [kluc]''), DES_ENCRYPT(''text , kluc'')'''  
  
−
Funkcie DES_ENCRYPT() a DES_DECRYPT() šifrujú a dešifrujú dáta pomocou oficiálneho algoritmu [http://en.wikipedia.org/wiki/Data_Encryption_Standard DES]. Použitie je podobné ako pri funkciách aes_decript a aes_encrypt.  
+
Funkcie DES_ENCRYPT() a DES_DECRYPT() šifrujú a dešifrujú dáta pomocou oficiálneho algoritmu [http://en.wikipedia.org/wiki/Data_Encryption_Standard DES]. Použitie je podobné ako pri funkciách ''AES_DECRYPT'' a ''AES_ENCRYPT''.  
 +
 
  
  
Riadok 931: Riadok 959:
 
Príklad:
 
Príklad:
 
<source lang="sql">
 
<source lang="sql">
−
  SELECT MD5(password) FROM tabulka;
+
  SELECT MD5(password) FROM tabulka
 
</source>
 
</source>
  
Riadok 951: Riadok 979:
 
Príklad:
 
Príklad:
 
<source lang="sql">
 
<source lang="sql">
−
  SELECT SHA(password) FROM tabulka;
+
  SELECT SHA(password) FROM tabulka
−
  SELECT SHA1(password) FROM tabulka;
+
  SELECT SHA1(password) FROM tabulka
 
</source>
 
</source>
  
Riadok 967: Riadok 995:
 
'''PASSWORD()'''  
 
'''PASSWORD()'''  
  
−
Na základe vtupného textu vypočíta heslo. Táto funkcia je použitá na generovanie hesiel v databáze mysql (tabuľka users).  
+
Na základe vtupného textu vypočíta heslo. Táto funkcia je použitá na generovanie hesiel v databáze ''mysql'' (tabuľka ''users'').  
 
<source lang="sql">
 
<source lang="sql">
−
   SELECT PASSWORD('kiwiki');
+
   SELECT PASSWORD('kiwiki')
 
</source>  
 
</source>  
  
Riadok 980: Riadok 1 008:
 
|}
 
|}
 
== Zdroje a odkazy  ==
 
== Zdroje a odkazy  ==
−
 
+
<references/>
−
*http://www.tutorialspoint.com/mysql/mysql-useful-functions.htm
 
−
*http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html
 
−
*http://www.bhatipoglu.com/entry/41/decode-demo-1
 
−
*http://www.fsid.cvut.cz/cz/u12110/DZS/SQL.doc
 

Aktuálna revízia z 23:21, 17. január 2011

Databázové systémy

Materiály k cvičeniam

Entitno - Relačný Diagram

Jazyk SQL

>SQL - dátové typy
>SQL - select
>SQL - update, insert, delete
>SQL - DDL
>SQL - DCL
>SQL - vstavané funkcie
>SQL - pohľady
>SQL - procedúry
>SQL - triggery

MySQL a web

Databázové systémy - neriešené príklady

Databázový systém MySQL obsahuje vstavané funkcie[1], ktoré môžeme rozdeliť do kategórií:

  • agregačné,
  • matematické,
  • textové,
  • štatistické,
  • dátumové,
  • šifrovacie a komprimačné.

Agregačné funkcie

Agregačné funkcie vykonávajú nad stĺpcami tabuľky matematické operácie.

COUNT()

Funkcia COUNT slúži na výpočet počtu záznamov v SQL dotaze.

Úloha: Zisti, koľko je v tabuľke city miest s kódom krajiny SVK (teda na Slovensku) 

  SELECT COUNT(*) FROM city WHERE CountryCode = "SVK"

Výsledok:

COUNT(*)
3

MAX() a MIN()

Funkcia MAX(), resp. MIN() slúži na zistenie maximálnej, resp. minimálnej hodnoty z určitej množiny hodnôt.


Úloha: Zisti, ktoré mesto má najväčšiu populáciu.

Riešenie: Úlohu si rozdelíme na 2 časti:

  1. Zistenie maximálnej populácie z tabuľky miest
  2. Výber mesta s touto populáciou. Pre riešenie tejto úlohy použijeme vnorený príkaz SELECT

Podúloha 1: 

 SELECT MAX(Population) FROM city

Výsledok: 10500000

Podúloha 2: 

 SELECT *  FROM city where Population=
     (SELECT MAX(Population) FROM city)

Výsledok:

ID Name CountryCode District Population
1024 Mumbai (Bombay) IND Maharashtra 10500000

AVG()

Funkcia AVG() počíta aritmetický priemer množiny hodnôt.


Úloha: Aká je priemerná populácia na Slovensku? 

   SELECT AVG(population) FROM city WHERE CountryCode="SVK"

Výsledok:

AVG(population)
261381.0000

SUM()

Funkcia SUM() počíta súčet hodnôt danej množiny hodnôt.


Úloha: Aký je súčet obyvateľov miest na Slovensku? 

   SELECT SUM(population) FROM city WHERE CountryCode="SVK"

Výsledok:

SUM(population)
784143


STD()

Funkcia STD() počíta štandardnú odchýlku množiny hodnôt.


Úloha: Aká je štandardná odchýlka počtu obyvateľov v mestách Českej republiky? 

   SELECT STD(population) FROM city WHERE CountryCode="CZE"

Výsledok:

STD(population)
321564.4992


Matematické funkcie

ABS(X)

Funkcia ABS vypočíta absolútnu hodnotu z čísla X.

Príklad: 

   SELECT ABS(-3)

Výsledok:

ABS(-3)
3


SIN(X)

Funcia SIN vypočíta sínus uhla X. Uhol X je v radiánoch.

Príklad: 

   SELECT SIN(PI()/2)

Výsledok:

SIN(PI()/2)
1


COS

Funcia COS vypočíta kosínus uhla X. Uhol X je v radiánoch.

Príklad: 

   SELECT COS(0)

Výsledok:

COS(0)
1

CEIL(X)

Funkcia CEIL zaokrúhli číslo X smerom hore.

Príklad: 

  SELECT CEIL(7.55)

Výsledok:

CEIL(7.55)
8

EXP( X )

Táto funkcia vracia hodnotu e (základ prirodzeného logaritmu), umocnenú na mocninu X.

Príklad: 

   SELECT EXP(3)

Výsledok:

EXP(3)
20.0855369231877


FLOOR(X)

Funkcia FLOOR zaokrúhli číslo X smerom dole.

Príklad: 

  SELECT FLOOR(7.55)

Výsledok:

FLOOR(7.55)
7


FORMAT(X,N)

Funkcia FORMAT() sa používa na formátovanie čísla X. N je počet desatinných miest čísla X.

Príklad: 

   SELECT FORMAT(423423234.65434453,3)

Výsledok:

FORMAT(423423234.65434453,3)
423,423,234.654


LOG(X)

Funkcia LOG vypočíta prirodzený logaritmus z čísla X (Poznámka: prirodzený logaritmus má základ e).

Príklad: 

   SELECT LOG(10)


Výsledok:

LOG(10)
2.30258509299405

LOG10(X)

Vypočíta desiatkový logaritmus z čísla X.

Príklad: 

   SELECT LOG10(100)

Výsledok:

LOG10(100)
2

PI()

Táto funkcia vráti hodnotu Ludolfovho čísla pí.

Príklad: 

   SELECT PI()

Výsledok:

PI()
3.141593


POW(X, Y)

Táto funkcia vracia hodnotu X umocnenú na Y. Je to alias k funkcii POWER.

Príklad: 

   SELECT POWER(3,3)

Výsledok:

POWER(3,3)
27


ROUND (X,D)

Táto funkcia vracia X zaokrúhlené na celé číslo. Ak je použitý druhý argument(D) potom funkcia zaokrúhli X na D desatiných miest.


Príklad: 

 
   SELECT ROUND(5.693893,2)


Výsledok:

ROUND(5.693893,2)
5.69


SQRT(X)

Funkcia SQRT vypočíta druhú odmocninu čísla X. Ak je X záporné funkcia SQRT vráti NULL.

Príklad: 

   SELECT SQRT(16)

Výsledok:

SQRT(16)
4.000000


TAN(X)

Funkcia TAN - vypočíta tangens uhla X. Uhol X je v radiánoch.

Príklad: Aká je hodnota TAN(45)? 

   SELECT TAN(PI()/4)

Výsledok:

SELECT TAN(PI()/4)
1


RAND()

Funkcia RAND generuje náhodné číslo v intervale (0,1).


Príklad: V príklade budeme generovať 2 náhodné čísla. 

   SELECT RAND(),RAND()

Výsledok:

RAND() RAND()
0.516074677478592 0.469642581855545

Textové funkcie

CHAR(X,...)

Funkcia CHAR() prevedie každý svoj argument X na znak podľa použitej znakovej tabuľky. 

   SELECT CHAR(77,121,83,81,76)

Výsledok:

CHAR(77,121,83,81,76)
MySQL


CONCAT

Funkcia CONCAT() sa používa na spojenie dvoch reťazcov do jedného reťazca.

Úloha: V tabuľke city spoj atribúty: name a CountryCode do jedného stĺpca s názvom Mesto 

   SELECT concat(name, " - ", countrycode) AS Mesto FROM city

Výsledok:

Mesto
Košice - SVK
Prešov - SVK
Kabul - AFG
Qandahar - AFG
Herat - AFG
Mazar-e-Sharif - AFG
Amsterdam - NLD
Rotterdam - NLD


FIELD(str, str1, str2, str3, ...)

Funkcie FIELD vracia index (pozícia začínajúca na hodonte 1) reťazca str v reťazcoch str1, str2, str3, ... . Ak nie je reťazec str nájdený, funkcia vráti 0.

Príklad: 

   SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo')

Výsledok:

FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo')
2

LOWER(str)

Funkcia LOWER() konvertuje reťazec str na malé písmená.

Úloha: Konvertuj kód krahiny (code) na malé písmená v tabuľke country. 

   SELECT Name,LOWER(code) from country

Výsledok:

Name LOWER(code)
Afghanistan afg
Netherlands nld
Netherlands Antilles ant
Albania alb
Algeria dza
American Samoa asm
Andorra and
Angola ago


UPPER

Funkcia UPPER() konvertuje reťazec na veľké písmená.


Úloha: konvertuj názov mesta (name) na veľké písmená v tabuľke city. 

   SELECT UPPER(name) FROM city

Výsledok:

upper(name)
MAZAR-E-SHARIF
AMSTERDAM
HAAG
UTRECHT
EINDHOVEN
TILBURG

LTRIM(str)

Zo začiatku reťazca str odstráni nečitateľné znaky (medzery).

Príklad: 

   SELECT LTRIM('   ahoj DBS')


Výsledok:

LTRIM(' ahoj DBS')
ahoj DBS


RTRIM(str)

Z konca reťazca str odstráni nečitateľné znaky (medzery).

Príklad: 

   SELECT RTRIM('ahoj DBS   ')

Výsledok:

RTRIM('ahoj DBS ')
ahoj DBS


TRIM

Zo začiatku a konca reťazca str odstráni nečitateľné znaky (medzery).

Príklad: 

   SELECT TRIM('   ahoj DBS    ')


Výsledok:

TRIM(' ahoj DBS ')
ahoj DBS

Dátumové funkcie

ADDDATE(date,INTERVAL days)

Funkcia ADDDATE pripočíta k zadanému dátumu date zadaný počet dní (hodnota days). Parameter days môžeme zadávať pomocou funkcie INTERVAL, alebo ako celé číslo.

Príklad: 

SELECT ADDDATE('2010-12-20',10),
       ADDDATE('2010-12-20', INTERVAL 11 DAY)

Výsledok:

ADDDATE('2010-12-20',10) ADDDATE('2010-12-20', INTERVAL 11 DAY)
2010-12-30 2010-12-31


ADDTIME(T1,T2)

Funica ADDTIME pripočíta k času T1 čas T2. Výsledok je súčet týchto časov. 


   SELECT ADDTIME('2010-12-20 10:00:0.0','1 1:1:1.000002')

Výsledok:

ADDTIME('2010-12-20 10:00:0.0','1 1:1:1.000002')
2010-12-21 11:01:01.000002


CURRDATE

Vracia aktuálny dátum ako hodnotu vo formáte 'YYYY-MM-DD' alebo RRRRMMDD, v závislosti na tom, či funkcia je použitá v reťazeci alebo číselnom kontexte.

Príklad: 

   SELECT CURDATE()

Výsledok:

CURDATE()
2010-12-15


CURTIME

Vracia aktuálny čas ako hodnotu v 'HH:MM:SS' alebo HHMMSS formáte, v závislosti na tom, či funkcia je použitá v reťazci alebo číselnom kontexte. Táto hodnota je vyjadrená v aktuálnom časovom pásme.

Príklad: 

   SELECT CURTIME()

Výsledok:

CURTIME()
18:31:48


DATEDIFF(D1,D2)

Funkcia DATEDIFF vráti rozdiel v dňoch medzi dátumami D1 a D2.

Príklad: 

   SELECT DATEDIFF('2010-12-31 23:59:59','2010-12-20')

Výsledok:

DATEDIFF('2010-12-31 23:59:59','2010-12-20')
11


DATE(D)

Z dátumu a času D vyberie len dátum.

Príklad: 

   SELECT DATE('2010-12-31 01:02:03')

Výsledok:

DATE('2010-12-31 01:02:03')
2010-12-31


DAY

DAY je synonymom pre DAYOFMONTH.



DAYNAME(D)

Z dátumu D určí názov dňa.

Príklad: 

   SELECT DAYNAME('2010-12-16')

Výsledok:

DAYNAME('2010-12-16')
Thursday


DAYOFWEEK(D)

Vráti číslo dňa v týždni (1 = nedeľa, 2 = pondelok, ... , 7 = sobota). Tieto hodnoty indexu zodpovedajú štandardu ODBC[2].

Príklad: 

   SELECT DAYOFWEEK('2010-12-31 01:02:03')

Výsledok:

DAYOFWEEK('2010-12-31 01:02:03')
6


DAYOFMONTH(D)

Z dátumu D vyberie číslo dňa, v rozmedzí 0 - 31.

Príklad: 

   SELECT DAYOFMONTH('2010-12-31 01:02:03')

Výsledok:

DAYOFMONTH('2010-12-31 01:02:03')
31


DAYOFYEAR

Vracia poradové číslo dňa v roku v rozsahu 1 až 366.

Príklad: 

   SELECT DAYOFYEAR('2010-12-20')

Výsledok:

DAYOFYEAR('2010-12-20')
354


HOUR(T)

Funkcia HOUR vyberie z dátumu/času T len hodinu.

Príklad: 

   SELECT HOUR('2010-12-20 10:12:58.55')

Výsledok:

HOUR('2010-12-20 10:12:58.55')
10


NOW

Zistí aktuálny dátum a čas.

Príklad: 

   SELECT NOW()

Výsledok:

NOW()
2010-11-03 10:45:55


SYSDATE

Vráti aktuálnu hodnotu dátumu a času, podľa systémového času.

Príklad: 

   SELECT SYSDATE()

Výsledok:

SYSDATE()
2010-12-20 12:21:33


WEEK

Z dátumu určí číslo týždňa v intervale 1 až 52.

Príklad: 

   SELECT WEEK('2010-12-18')


Výsledok:

WEEK('2010-12-18')
50

YEAR(D)

Z dátumu D určí rok.

Príklad: 

   SELECT YEAR('2010-12-20')

Výsledok:

YEAR('2010-12-20')
2010

Šifrovacie a komprimačné

Nasledujúce funkcie ponúkajú šifrovanie, dešifrovanie a hašovanie textových dát[3].


AES_ENCRYPT( nezasifrovany_text , kluc )

Funkcie AES_ENCRYPT() a AES_DECRYPT() šifrujú a dešifrujú dáta pomocou oficiálneho algoritmu AES(Advanced Encryption Standard). Dĺžka šifrovacieho kľúča je 128 bitov. Dĺžku kľúča sa môže predĺžiť až na 256 bitov. 

   INSERT INTO test (text,zasifrovane,heslo) VALUES ("kiwiki",AES_ENCRYPT("kiwiki","123456789"),"1234567789")

Výsledok:

Šifrovanie pomocou AES_ENCRYPT


AES_DECRYPT( zasifrovany_text , kluc)

Táto funkcia dešifruje dáta pomocou šifrovacieho algoritmus AES (Advanced Encryption Standard) 

   SELECT text, AES_DECRYPT(zasifrovane,"123456789") AS rozsifrovane ,heslo FROM test

Výsledok:

Dešifrovanie pomocou AES_DECRYPT


COMPRESS( str ), UNCOMPRESS( str )

Komprimuje/dekomprimuje reťazec a vráti výsledok ako binárny reťazec. Táto funkcia vyžaduje konfiguráciu MySQL servara tak, aby boli zostavený s kompresnou knižnicu zlib. V opačnom prípade je návratová hodnota vždy NULL. Komprimovaný reťazec môže byť dekomprimovaný funkciou UNCOMPRESS(). 

   INSERT INTO test (text,zasifrovane) VALUES ("kiwiki",COMPRESS("kiwiki"))

Výsledok:

Výsledok komprimácie pomocou funkcie COMPRESS


   SELECT id, text, UNCOMPRESS(zasifrovane) AS dekomprimovane FROM test

Výsledok:

Výsledok dekomprimácie pomocou funkcie UNCOMPRESS


ENCODE(str,heslo)

Šifruje dáta str pomocou nami zadaného šifrovacieho hesla heslo. Výsledok šifrovania je binárny reťazec rovnakej dĺžky ako pôvodný. Na dešifrovanie sa používa funcia DECODE.

Príklad: 

 SELECT ENCODE("kiwiki", "heslo")

Výsledok:

Zašifrovanie pomocou funkcie ENCODE


DECODE(crypt_str,heslo)

Funkcia DECODE je opak funkcie ENCODE. Dešifruje zašifrované dáta funkciou ENCODE pomocou vloženého hesla pri šifrovaní[4].


Príklad: 

 SELECT DECODE( ENCODE("kiwiki","heslo"), "heslo")


Výsledok:

DECODE( ENCODE("kiwiki","heslo"), "heslo")
kiwiki


DES_DECRYPT( text, [kluc]), DES_ENCRYPT(text , kluc)

Funkcie DES_ENCRYPT() a DES_DECRYPT() šifrujú a dešifrujú dáta pomocou oficiálneho algoritmu DES. Použitie je podobné ako pri funkciách AES_DECRYPT a AES_ENCRYPT.


MD5() (Message-Digest algorithm)

Hašovacia funkcia, mení vstupné dáta na výstupné dáta fixnej dĺžky.

Príklad: 

 SELECT MD5(password) FROM tabulka


Výsledok:

MD5(password)
21232f297a57a5a743894a0e4a801fc3


SHA1(), SHA() (Secure Hash Algorithm)

Hašovacia funkcia, mení vstupné dáta na výstupné dáta fixnej dĺžky.

Príklad: 

 SELECT SHA(password) FROM tabulka
 SELECT SHA1(password) FROM tabulka


Výsledok:

SHA(password)/SHA1(password)
d033e22ae348aeb5660fc2140aec35850c4da997


PASSWORD()

Na základe vtupného textu vypočíta heslo. Táto funkcia je použitá na generovanie hesiel v databáze mysql (tabuľka users). 

  SELECT PASSWORD('kiwiki')

Výsledok:

PASSWORD('kiwiki')
*D94D4484B4D4060225F91D28D7BB131F917F760C

Zdroje a odkazy

  1. ↑ MySQL Useful Functions and Clauses - http://www.tutorialspoint.com/mysql/mysql-useful-functions.htm
  2. ↑ ODBC Scalar Functions - http://msdn.microsoft.com/en-us/library/bb630290.aspx
  3. ↑ Encryption and Compression Functions - http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html
  4. ↑ Decode Demo 1 - http://www.bhatipoglu.com/entry/41/decode-demo-1
Zdroj: „http://www.kiwiki.info/index.php?title=SQL_-_vstavané_funkcie&oldid=8907“