Kiwiki - Príspevky používateľa [sk]

SQL - vstavané funkcie

2010-12-15T09:14:22Z

Michalec:

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

*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)

<source lang="sql">
SELECT COUNT(*) FROM city WHERE CountryCode = "SVK"
</source>

Výsledok: COUNT(*): 3

Vylepšená verzia, pre prehľadnejšie čítanie výsledku: <source lang="sql">
SELECT COUNT(*) AS pocet FROM city WHERE CountryCode = "SVK"
</source>

Výsledok: pocet: 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:

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

Podúloha 1: <source lang="sql">
SELECT MAX(Population) FROM city
</source> Výsledok: 10500000

Podúloha 2: <source lang="sql">
SELECT * FROM city where Population=
(SELECT MAX(Population) FROM city)
</source> Výsledok:

[[Image:Select max.png|center]]

=== AVG() ===

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

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

<source lang="sql">
SELECT AVG(population) FROM city where CountryCode="SVK"
</source>

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?

<source lang="sql">
SELECT SUM(population) FROM city where CountryCode="SVK"
</source>

Výsledok: SUM(population): 784143

=== STD() ===

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

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

<source lang="sql">
SELECT STD(population) FROM city where CountryCode="CZE"
</source>

Výsledok: STD(population): 321564.4992

== Matematické funkcie ==

'''ABS'''

'''SIN'''

'''COS'''

'''CEIL'''

'''EXP'''

Táto funkcia vracia hodnotu e (základ prirodzeného logaritmu), zdvihnutý k mocnine X.

Príklad:
<source lang="sql">
SELECT EXP(3);
</source>

Výsledok:
{| class="wikitable"
|-
!EXP(3)
|-
|20.0855369231877
|}

'''FLOOR'''

Táto funkcia vracia najväčšiu celočíselnú hodnotu, ktorá nie je väčšia ako X.

Príklad: <source lang="sql">
SELECT FLOOR(7.55);
</source>

Výsledok:
{| class="wikitable"
|-
!FLOOR(7.55)
|-
|7
|}

'''FORMAT'''

FORMAT () funkcia sa používa na formátovanie čísla X v nasledujúcom formáte: ###,###,###.## skrátiť na desatinné miesta . Nasledujúci príklad ukazuje použitie a výstup FORMAT () funkcie:

Príklad: <source lang="sql">
SELECT FORMAT(423423234.65434453,2);
FORMAT(423423234.65434453,2)
</source>

Výsledok:

423,423,234.65

'''LOG'''

'''LOG10'''

'''PI'''

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

Príklad:
<source lang="sql">
SELECT PI();
</source>

Výsledok:
{| class="wikitable"
|-
!PI()
|-
|3.141593
|}

'''POW( ''X, Y'' )'''

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

Príklad: <source lang="sql">
SELECT POWER(3,3);
</source>

Výsledok:
{| class="wikitable"
|-
!POWER(3,3)
|-
|27
|}

'''ROUND'''

Táto funkcia vracia X zaokrúhlené na celé číslo. Ak druhý argument D, je dodávaný, X potom vráti funkciu zaokrúhli na desatinné miesta D.

Príklad: <source lang="sql">
SELECT ROUND(5.693893);
ROUND(5.693893)

Príklad: <source lang="sql">
SELECT ROUND(5.693893,2);
ROUND(5.693893,2)
</source>

Výsledok:

6

6,69

'''SQRT(''X'')'''

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

Príklad:
<source lang="sql">
SELECT SQRT(16);
</source>

Výsledok:
{| class="wikitable"
|-
! SQRT(16)
|-
| 4.000000
|}

'''TAN(A)'''

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

Úloha: Aká je hodnota TAN(45)? V uhloviej miere je uhol 45° rovný PI/4
<source lang="sql">
SELECT TAN(PI()/4);
</source>

Výsledok:
{| class="wikitable"
|-
! SELECT TAN(PI()/4)
|-
| 1.619775
|}

'''RAND'''

SELECT RAND( ), RAND( ), RAND( );

+------------------+-----------------+------------------+

| RAND( ) | RAND( ) | RAND( ) |

+------------------+-----------------+------------------+

| 0.45464584925645 | 0.1824410643265 | 0.54826780459682 |

+------------------+-----------------+------------------+

== Textové funkcie ==

'''CHAR'''

Funkcia CHAR() vysvetlí každý argument N ako celé číslo a vytvorí retazec čísiel zložený zo znakov zadaného kodu

SELECT CHAR(77,121,83,81,'76');
+---------------------------------------------------------+
| CHAR(77,121,83,81,'76') |
+---------------------------------------------------------+
| MySQL |
+---------------------------------------------------------+

<source lang="sql">
</source>

'''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''

<source lang="sql">
SELECT concat(name, " - ", countrycode) AS Mesto FROM city
</source>

Výsledok:
{| class="wikitable"
|-
!Mesto
|-
|Košice - SVK
|-
|Prešov - SVK
|-
|Kabul - AFG
|-
|Qandahar - AFG
|-
|Herat - AFG
|-
|Mazar-e-Sharif - AFG
|-
|Amsterdam - NLD
|-
|Rotterdam - NLD
|}

'''FIELD'''

<source lang="sql">
</source>

'''LOWER'''

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

Úloha: konvertuj code na malé písmená v tabuľke country.

<source lang="sql">
SELECT Name,LOWER(code) from country
</source>

Výsledok:
{| class="wikitable"
|-
!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 name na veľké písmená v tabulke city

<source lang="sql">
SELECT upper("name") from city
</source>

 '''LTRIM(''str'')'''

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

Príklad: <source lang="sql">
SELECT LTRIM(' ahoj DBS')
</source>

Výsledok:

ahoj DBS

 '''RTRIM(''str'')'''

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

Príklad: <source lang="sql">
SELECT RTRIM('ahoj DBS ')
</source>

Výsledok:

ahoj DBS

 '''TRIM'''

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

Príklad: <source lang="sql">
SELECT TRIM(' ahoj DBS ')
</source>

Výsledok:

ahoj DBS

== Dátumové funkcie ==

'''ADDDATE'''

'''ADDTIME'''

ADDTIME () pridá na expr2 expr1 a vráti výsledok expr1 a to je čas alebo dátum a čas prejavu, a expr2 je výraz.

<source lang="sql">
SELECT ADDTIME();
</source>
Výsledok:
{| class="wikitable"
|-
!ADDTIME()
|-
|1998-01-02 01:01:01.000001
|}

'''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:
<source lang="sql">
SELECT CURDATE();
</source>

Výsledok:

{| class="wikitable"
|-
!CURDATE()
|-
|1997-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:
<source lang="sql">
SELECT CURTIME();
</source>
Výsledok:

{| class="wikitable"
|-
!CURTIME()
|-
|18:31:48
|}

'''EXTRACT()'''

'''DATEDIFF()'''

'''DATE'''

Dátum vo formáte RRRR-MM-DD, medzi 1000-01-01 a 9999-12-31. Napríklad, 30. decembra 1973 by sa uloží ako 1973-12-30.

Príklad:
<source lang="sql">
SELECT DATE
</source>

Výsledok:

2003-12-31

'''DAY'''

DAY je synonymom pre DAYOFMONTH.

Príklad:
<source lang="sql">
SELECT DAY('2010-12-16');
</source>

Výsledok:

{| class="wikitable"
|-
! DAY('2010-12-16')
|-
| 16
|}

 '''DAYNAME'''

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

Príklad:
<source lang="sql">
SELECT DAYNAME('2010-12-16');
</source>

Výsledok:

{| class="wikitable"
|-
! DAYNAME('2010-12-16')
|-
| Thursday
|}

 '''DAYOFWEEK'''

Vracia deň v týždni index pre dáta (1 = nedeľa, 2 = pondelok., 7 = sobota). Tieto hodnoty indexu zodpovedajú štandardu ODBC.

Príklad:
<source lang="sql">
SELECT DAYOFWEEK()
</source>

Výsledok:
{| class="wikitable"
|-
! DAYOFWEEK('1998-02-03')
|-
| 3
|}
 '''DAYOFMONTH'''

Vracia deň v mesiaci na deň, v rozmedzí 0 - 31.

Príklad:
<source lang="sql">
SELECT DAYOFMONTH()
</source>

Výsledok:
{| class="wikitable"
|-
! DAYOFMONTH('1998-02-03')
|-
|3
|}

 '''DAYOFYEAR'''

Vracia deň v roku na dátum, v rozsahu 1 až 366.

Príklad:
<source lang="sql">
SELECT DAYOFYEAR()
</source>

Výsledok:
{| class="wikitable"
|-
! DAYOFYEAR()
|-
| 34
|}
 '''HOUR'''

 '''NOW'''

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

Príklad:
<source lang="sql">
SELECT NOW()
</source>

Výsledok:
{| class="wikitable"
|-
! NOW()
|-
| 2010-11-03 10:45:55
|}

 '''SYSDATE'''

 '''WEEK'''

Z dátumu určí číslo týždňa.

Príklad:

<source lang="sql">
SELECT WEEK('2010-12-18');
</source>

Výsledok:

{| class="wikitable"
|-
! WEEK('2010-12-18')
|-
| 50
|}

 '''YEAR'''

Vracia rok pre dáta, v rozmedzí 1000 do 9999, alebo 0 pre. Nula. dátum.

Príklad:
<source lang="sql">
SELECT YEAR()
</source>

Výsledok:
{| class="wikitable"
|-
! YEAR('98-02-03')
|-
| 1998
|}

== Šifrovacie a komprimačné ==

'''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.

<source lang="sql">
insert into test (text,zasifrovane,heslo) values ("kiwiki",AES_ENCRYPT("kiwiki","123456789"),"1234567789")
</source> Výsledok: [[Image:Sql AES ENCRYPT.png|frame|center]]

'''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">
SELECT text, AES_DECRYPT(zasifrovane,"123456789") as rozsifrovane ,heslo from test
</source>

Výsledok: [[Image:Sql AES DECRYPT.png|frame|center]]

'''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().

<source lang="sql">
INSERT INTO test (text,zasifrovane) VALUES ("kiwiki",COMPRESS("kiwiki"))
</source> Výsledok: [[Image:Sql compress.png|frame|center]]

 <source lang="sql">
SELECT id, text, UNCOMPRESS(zasifrovane) AS dekomprimovane FROM test
</source> Výsledok: [[Image:Sql uncompress.png|frame|center]]

 '''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.

Dotaz:
<source lang="sql">
SELECT ENCODE("kiwiki", "heslo");
</source>

Výsledok:
{| class="wikitable"
|-
! encode("kiwiki", "heslo")
|-
| �d�\ny_
|}

'''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í.

Dotaz:
<source lang="sql">
SELECT DECODE("�d�\ny_", "heslo");
</source>

Výsledok:
{| class="wikitable"
|-
! encode("�d�\ny_", "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 [http://en.wikipedia.org/wiki/Data_Encryption_Standard DES]. Použitie je podobné ako pri funkciách aes_decript a aes_encrypt.

 '''MD5()''' [http://sk.wikipedia.org/wiki/Message-Digest_algorithm (Message-Digest algorithm) ]

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

Dotaz:
<source lang="sql">
SELECT MD5(password) FROM tabulka;
</source>

Výsledok:
{| class="wikitable"
|-
! MD5(password)
|-
| 21232f297a57a5a743894a0e4a801fc3
|}

'''SHA1(), SHA()''' [http://sk.wikipedia.org/wiki/Secure_Hash_Algorithm (Secure Hash Algorithm) ]

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

Dotaz:
<source lang="sql">
SELECT SHA(password) FROM tabulka;
SELECT SHA1(password) FROM tabulka;
</source>

Výsledok:
{| class="wikitable"
|-
! 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). <source lang="sql">
SELECT PASSWORD('kiwiki');
</source>

Výsledok:
{| class="wikitable"
|-
! PASSWORD('kiwiki')
|-
| *D94D4484B4D4060225F91D28D7BB131F917F760C
|}
== Zdroje a odkazy ==

*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

SQL - vstavané funkcie

2010-12-13T11:24:50Z

Michalec:

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

*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)

<source lang="sql">
SELECT COUNT(*) FROM city WHERE CountryCode = "SVK"
</source>

Výsledok: COUNT(*): 3

Vylepšená verzia, pre prehľadnejšie čítanie výsledku: <source lang="sql">
SELECT COUNT(*) AS pocet FROM city WHERE CountryCode = "SVK"
</source>

Výsledok: pocet: 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:

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

Podúloha 1: <source lang="sql">
SELECT MAX(Population) FROM city
</source> Výsledok: 10500000

Podúloha 2: <source lang="sql">
SELECT * FROM city where Population=
(SELECT MAX(Population) FROM city)
</source> Výsledok:

[[Image:Select max.png|center]]

=== AVG() ===

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

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

<source lang="sql">
SELECT AVG(population) FROM city where CountryCode="SVK"
</source>

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?

<source lang="sql">
SELECT SUM(population) FROM city where CountryCode="SVK"
</source>

Výsledok: SUM(population): 784143

=== STD() ===

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

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

<source lang="sql">
SELECT STD(population) FROM city where CountryCode="CZE"
</source>

Výsledok: STD(population): 321564.4992

== Matematické funkcie ==

'''ABS'''

'''SIN'''

'''COS'''

'''CEIL'''

'''EXP'''

Táto funkcia vracia hodnotu e (základ prirodzeného logaritmu), zdvihnutý k mocnine X.

Príklad:
<source lang="sql">
SELECT EXP(3);
</source>

Výsledok:
{| class="wikitable"
|-
!EXP(3)
|-
|20.0855369231877
|}

'''FLOOR'''

Táto funkcia vracia najväčšiu celočíselnú hodnotu, ktorá nie je väčšia ako X.

Príklad: <source lang="sql">
SELECT FLOOR(7.55);
</source>

Výsledok:
{| class="wikitable"
|-
!FLOOR(7.55)
|-
|7
|}

'''FORMAT'''

FORMAT () funkcia sa používa na formátovanie čísla X v nasledujúcom formáte: ###,###,###.## skrátiť na desatinné miesta . Nasledujúci príklad ukazuje použitie a výstup FORMAT () funkcie:

Príklad: <source lang="sql">
SELECT FORMAT(423423234.65434453,2);
FORMAT(423423234.65434453,2)
</source>

Výsledok:

423,423,234.65

'''LOG'''

'''LOG10'''

'''PI'''

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

Príklad:
<source lang="sql">
SELECT PI();
</source>

Výsledok:
{| class="wikitable"
|-
!PI()
|-
|3.141593
|}

'''POW( ''X, Y'' )'''

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

Príklad: <source lang="sql">
SELECT POWER(3,3);
</source>

Výsledok:
{| class="wikitable"
|-
!POWER(3,3)
|-
|27
|}

'''ROUND'''

Táto funkcia vracia X zaokrúhlené na celé číslo. Ak druhý argument D, je dodávaný, X potom vráti funkciu zaokrúhli na desatinné miesta D.

Príklad: <source lang="sql">
SELECT ROUND(5.693893);
ROUND(5.693893)

Príklad: <source lang="sql">
SELECT ROUND(5.693893,2);
ROUND(5.693893,2)
</source>

Výsledok:

6

6,69

'''SQRT(''X'')'''

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

Príklad:
<source lang="sql">
SELECT SQRT(16);
</source>

Výsledok:
{| class="wikitable"
|-
! SQRT(16)
|-
| 4.000000
|}

'''TAN(A)'''

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

Úloha: Aká je hodnota TAN(45)? V uhloviej miere je uhol 45° rovný PI/4
<source lang="sql">
SELECT TAN(PI()/4);
</source>

Výsledok:
{| class="wikitable"
|-
! SELECT TAN(PI()/4)
|-
| 1.619775
|}

'''RAND'''

SELECT RAND( ), RAND( ), RAND( );

+------------------+-----------------+------------------+

| RAND( ) | RAND( ) | RAND( ) |

+------------------+-----------------+------------------+

| 0.45464584925645 | 0.1824410643265 | 0.54826780459682 |

+------------------+-----------------+------------------+

== Textové funkcie ==

'''CHAR'''

Funkcia CHAR() vysvetlí každý argument N ako celé číslo a vytvorí retazec čísiel zložený zo znakov zadaného kodu

SELECT CHAR(77,121,83,81,'76');
+---------------------------------------------------------+
| CHAR(77,121,83,81,'76') |
+---------------------------------------------------------+
| MySQL |
+---------------------------------------------------------+

<source lang="sql">
</source>

'''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''

<source lang="sql">
SELECT concat(name, " - ", countrycode) AS Mesto FROM city
</source>

Výsledok:
{| class="wikitable"
|-
!Mesto
|-
|Košice - SVK
|-
|Prešov - SVK
|-
|Kabul - AFG
|-
|Qandahar - AFG
|-
|Herat - AFG
|-
|Mazar-e-Sharif - AFG
|-
|Amsterdam - NLD
|-
|Rotterdam - NLD
|}

'''FIELD'''

<source lang="sql">
</source>

'''LOWER'''

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

Úloha: konvertuj code na malé písmená v tabuľke country.

<source lang="sql">
SELECT Name,LOWER(code) from country
</source>

Výsledok:
{| class="wikitable"
|-
!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 name na veľké písmená v tabulke city

<source lang="sql">
SELECT upper("name") from city
</source>

 '''LTRIM(''str'')'''

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

Príklad: <source lang="sql">
SELECT LTRIM(' ahoj DBS')
</source>

Výsledok:

ahoj DBS

 '''RTRIM(''str'')'''

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

Príklad: <source lang="sql">
SELECT RTRIM('ahoj DBS ')
</source>

Výsledok:

ahoj DBS

 '''TRIM'''

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

Príklad: <source lang="sql">
SELECT TRIM(' ahoj DBS ')
</source>

Výsledok:

ahoj DBS

== Dátumové funkcie ==

'''ADDDATE'''

'''ADDTIME'''

'''CURRDATE'''

'''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:
<source lang="sql">
SELECT CURTIME();
</source>
Výsledok:

{| class="wikitable"
|-
!CURTIME()
|-
|18:31:48
|}

'''EXTRACT()'''

'''DATEDIFF()'''

'''DATE'''

Dátum vo formáte RRRR-MM-DD, medzi 1000-01-01 a 9999-12-31. Napríklad, 30. decembra 1973 by sa uloží ako 1973-12-30.

Príklad:
<source lang="sql">
SELECT DATE
</source>

Výsledok:

2003-12-31

'''DAY'''

DAY je synonymom pre DAYOFMOUTH.

Príklad:
<source lang="sql">
SELECT DAY('2010-12-16');
</source>

Výsledok:

{| class="wikitable"
|-
! DAY('2010-12-16')
|-
| 16
|}

 '''DAYNAME'''

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

Príklad:
<source lang="sql">
SELECT DAYNAME('2010-12-16');
</source>

Výsledok:

{| class="wikitable"
|-
! DAYNAME('2010-12-16')
|-
| Thursday
|}

 '''DAYOFWEEK'''

Vracia deň v týždni index pre dáta (1 = nedeľa, 2 = pondelok., 7 = sobota). Tieto hodnoty indexu zodpovedajú štandardu ODBC.

Príklad:
<source lang="sql">
SELECT DAYOFWEEK()
</source>

Výsledok:
{| class="wikitable"
|-
! DAYOFWEEK('1998-02-03')
|-
| 3
|}
 '''DAYOFMONTH'''

Vracia deň v mesiaci na deň, v rozmedzí 0 - 31.

Príklad:
<source lang="sql">
SELECT DAYOFMONTH()
</source>

Výsledok:
{| class="wikitable"
|-
! DAYOFMONTH('1998-02-03')
|-
|3
|}

 '''DAYOFYEAR'''

Vracia deň v roku na dátum, v rozsahu 1 až 366.

Príklad:
<source lang="sql">
SELECT DAYOFYEAR()
</source>

Výsledok:
{| class="wikitable"
|-
! DAYOFYEAR()
|-
| 34
|}
 '''HOUR'''

 '''NOW'''

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

Príklad:
<source lang="sql">
SELECT NOW()
</source>

Výsledok:
{| class="wikitable"
|-
! NOW()
|-
| 2010-11-03 10:45:55
|}

 '''SYSDATE'''

 '''WEEK'''

Priraďuje číslo k týždňu.Čísla sú od 0 po 52.

Príklad:

<source lang="sql">
SELECT WEEK('2010-12-18');
</source>

Výsledok:

{| class="wikitable"
|-
! WEEK('2010-12-18')
|-
| 50
|}

 '''YEAR'''

Vracia rok pre dáta, v rozmedzí 1000 do 9999, alebo 0 pre. Nula. dátum.

Príklad:
<source lang="sql">
SELECT YEAR()
</source>

Výsledok:
{| class="wikitable"
|-
! YEAR('98-02-03')
|-
| 1998
|}
== Šifrovacie a komprimačné ==

'''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.

<source lang="sql">
insert into test (text,zasifrovane,heslo) values ("kiwiki",AES_ENCRYPT("kiwiki","123456789"),"1234567789")
</source> Výsledok: [[Image:Sql AES ENCRYPT.png|frame|center]]

'''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">
SELECT text, AES_DECRYPT(zasifrovane,"123456789") as rozsifrovane ,heslo from test
</source>

Výsledok: [[Image:Sql AES DECRYPT.png|frame|center]]

'''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().

<source lang="sql">
INSERT INTO test (text,zasifrovane) VALUES ("kiwiki",COMPRESS("kiwiki"))
</source> Výsledok: [[Image:Sql compress.png|frame|center]]

 <source lang="sql">
SELECT id, text, UNCOMPRESS(zasifrovane) AS dekomprimovane FROM test
</source> Výsledok: [[Image:Sql uncompress.png|frame|center]]

 '''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.

Dotaz:
<source lang="sql">
SELECT ENCODE("kiwiki", "heslo");
</source>

Výsledok:
{| class="wikitable"
|-
! encode("kiwiki", "heslo")
|-
| �d�\ny_
|}

'''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í.

Dotaz:
<source lang="sql">
SELECT DECODE("�d�\ny_", "heslo");
</source>

Výsledok:
{| class="wikitable"
|-
! encode("�d�\ny_", "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 [http://en.wikipedia.org/wiki/Data_Encryption_Standard DES]. Použitie je podobné ako pri funkciách aes_decript a aes_encrypt.

 '''MD5()''' [http://sk.wikipedia.org/wiki/Message-Digest_algorithm (Message-Digest algorithm) ]

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

Dotaz:
<source lang="sql">
SELECT MD5(password) FROM tabulka;
</source>

Výsledok:
{| class="wikitable"
|-
! MD5(password)
|-
| 21232f297a57a5a743894a0e4a801fc3
|}

'''SHA1(), SHA()''' [http://sk.wikipedia.org/wiki/Secure_Hash_Algorithm (Secure Hash Algorithm) ]

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

Dotaz:
<source lang="sql">
SELECT SHA(password) FROM tabulka;
SELECT SHA1(password) FROM tabulka;
</source>

Výsledok:
{| class="wikitable"
|-
! 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). <source lang="sql">
SELECT PASSWORD('kiwiki');
</source>

Výsledok:
{| class="wikitable"
|-
! PASSWORD('kiwiki')
|-
| *D94D4484B4D4060225F91D28D7BB131F917F760C
|}
== Zdroje a odkazy ==

*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

SQL - vstavané funkcie

2010-12-12T18:13:27Z

Michalec:

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

*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)

<source lang="sql">
SELECT COUNT(*) FROM city WHERE CountryCode = "SVK"
</source>

Výsledok: COUNT(*): 3

Vylepšená verzia, pre prehľadnejšie čítanie výsledku: <source lang="sql">
SELECT COUNT(*) AS pocet FROM city WHERE CountryCode = "SVK"
</source>

Výsledok: pocet: 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:

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

Podúloha 1: <source lang="sql">
SELECT MAX(Population) FROM city
</source> Výsledok: 10500000

Podúloha 2: <source lang="sql">
SELECT * FROM city where Population=
(SELECT MAX(Population) FROM city)
</source> Výsledok:

[[Image:Select max.png|center]]

=== AVG() ===

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

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

<source lang="sql">
SELECT AVG(population) FROM city where CountryCode="SVK"
</source>

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?

<source lang="sql">
SELECT SUM(population) FROM city where CountryCode="SVK"
</source>

Výsledok: SUM(population): 784143

=== STD() ===

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

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

<source lang="sql">
SELECT STD(population) FROM city where CountryCode="CZE"
</source>

Výsledok: STD(population): 321564.4992

== Matematické funkcie ==

'''ABS'''

'''SIN'''

'''COS'''

'''CEIL'''

'''EXP'''

Táto funkcia vracia hodnotu e (základ prirodzeného logaritmu), zdvihnutý k mocnine X.

Príklad:
<source lang="sql">
SELECT EXP(3);
</source>

Výsledok:
{| class="wikitable"
|-
!EXP(3)
|-
|20.0855369231877
|}

'''FLOOR'''

Táto funkcia vracia najväčšiu celočíselnú hodnotu, ktorá nie je väčšia ako X.

Príklad: <source lang="sql">
SELECT FLOOR(7.55);
</source>

Výsledok:
{| class="wikitable"
|-
!FLOOR(7.55)
|-
|7
|}

'''FORMAT'''

FORMAT () funkcia sa používa na formátovanie čísla X v nasledujúcom formáte: ###,###,###.## skrátiť na desatinné miesta . Nasledujúci príklad ukazuje použitie a výstup FORMAT () funkcie:

Príklad: <source lang="sql">
SELECT FORMAT(423423234.65434453,2);
FORMAT(423423234.65434453,2)
</source>

Výsledok:

423,423,234.65

'''LOG'''

'''LOG10'''

'''PI'''

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

Príklad:
<source lang="sql">
SELECT PI();
</source>

Výsledok:
{| class="wikitable"
|-
!PI()
|-
|3.141593
|}

'''POW( ''X, Y'' )'''

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

Príklad: <source lang="sql">
SELECT POWER(3,3);
</source>

Výsledok:
{| class="wikitable"
|-
!POWER(3,3)
|-
|27
|}

'''ROUND'''

Táto funkcia vracia X zaokrúhlené na celé číslo. Ak druhý argument D, je dodávaný, X potom vráti funkciu zaokrúhli na desatinné miesta D.

Príklad: <source lang="sql">
SELECT ROUND(5.693893);
ROUND(5.693893)

Príklad: <source lang="sql">
SELECT ROUND(5.693893,2);
ROUND(5.693893,2)
</source>

Výsledok:

6

6,69

'''SQRT(''X'')'''

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

Príklad:
<source lang="sql">
SELECT SQRT(16);
</source>

Výsledok:
{| class="wikitable"
|-
! SQRT(16)
|-
| 4.000000
|}

'''TAN(A)'''

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

Úloha: Aká je hodnota TAN(45)? V uhloviej miere je uhol 45° rovný PI/4
<source lang="sql">
SELECT TAN(PI()/4);
</source>

Výsledok:
{| class="wikitable"
|-
! SELECT TAN(PI()/4)
|-
| 1.619775
|}

'''RAND'''

SELECT RAND( ), RAND( ), RAND( );

+------------------+-----------------+------------------+

| RAND( ) | RAND( ) | RAND( ) |

+------------------+-----------------+------------------+

| 0.45464584925645 | 0.1824410643265 | 0.54826780459682 |

+------------------+-----------------+------------------+

== Textové funkcie ==

'''CHAR'''

Funkcia CHAR() vysvetlí každý argument N ako celé číslo a vytvorí retazec čísiel zložený zo znakov zadaného kodu

SELECT CHAR(77,121,83,81,'76');
+---------------------------------------------------------+
| CHAR(77,121,83,81,'76') |
+---------------------------------------------------------+
| MySQL |
+---------------------------------------------------------+

<source lang="sql">
</source>

'''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''

<source lang="sql">
SELECT concat(name, " - ", countrycode) AS Mesto FROM city
</source>

Výsledok:
{| class="wikitable"
|-
!Mesto
|-
|Košice - SVK
|-
|Prešov - SVK
|-
|Kabul - AFG
|-
|Qandahar - AFG
|-
|Herat - AFG
|-
|Mazar-e-Sharif - AFG
|-
|Amsterdam - NLD
|-
|Rotterdam - NLD
|}

'''FIELD'''

<source lang="sql">
</source>

'''LOWER'''

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

Úloha: konvertuj code na malé písmená v tabuľke country.

<source lang="sql">
SELECT Name,LOWER(code) from country
</source>

Výsledok:
{| class="wikitable"
|-
!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 name na veľké písmená v tabulke city

<source lang="sql">
SELECT upper("name") from city
</source>

 '''LTRIM(''str'')'''

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

Príklad: <source lang="sql">
SELECT LTRIM(' ahoj DBS')
</source>

Výsledok:

ahoj DBS

 '''RTRIM(''str'')'''

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

Príklad: <source lang="sql">
SELECT RTRIM('ahoj DBS ')
</source>

Výsledok:

ahoj DBS

 '''TRIM'''

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

Príklad: <source lang="sql">
SELECT TRIM(' ahoj DBS ')
</source>

Výsledok:

ahoj DBS

== Dátumové funkcie ==

'''ADDDATE'''

'''ADDTIME'''

'''CURRDATE'''

'''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:
<source lang="sql">
SELECT CURTIME();
</source>
Výsledok:

CURTIME()
18:31:48

{| class="wikitable"
|-
!CURTIME()
|-
|18:31:48
|}

'''EXTRACT()'''

'''DATEDIFF()'''

'''DATE'''

Dátum vo formáte RRRR-MM-DD, medzi 1000-01-01 a 9999-12-31. Napríklad, 30. decembra 1973 by sa uloží ako 1973-12-30.

Príklad:
<source lang="sql">
SELECT DATE
</source>

Výsledok:

2003-12-31

'''DAY'''

DAY je synonymom pre deň v mesiaci.

Príklad:
<source lang="sql">
SELECT DAY();
</source>

Výsledok:

{| class="wikitable"
|-
! DAY('1998-02-05')
|-
| 5
|}

 '''DAYNAME'''

Vracia názvi dní pre dáta.

Príklad:
<source lang="sql">
SELECT DAYNAME();
</source>

Výsledok:

{| class="wikitable"
|-
! DAYNAME('1998-02-05')
|-
| Thursday
|}

 '''DAYOFWEEK'''

Vracia deň v týždni index pre dáta (1 = nedeľa, 2 = pondelok., 7 = sobota). Tieto hodnoty indexu zodpovedajú štandardu ODBC.

Príklad:
<source lang="sql">
SELECT DAYOFWEEK()
</source>

Výsledok:
{| class="wikitable"
|-
! DAYOFWEEK('1998-02-03')
|-
| 3
|}
 '''DAYOFMONTH'''

Vracia deň v mesiaci na deň, v rozmedzí 0 - 31.

Príklad:
<source lang="sql">
SELECT DAYOFMONTH()
</source>

Výsledok:
{| class="wikitable"
|-
! DAYOFMONTH('1998-02-03')
|-
|3
|}

 '''DAYOFYEAR'''

Vracia deň v roku na dátum, v rozsahu 1 až 366.

Príklad:
<source lang="sql">
SELECT DAYOFYEAR()
</source>

Výsledok:
{| class="wikitable"
|-
! DAYOFYEAR()
|-
| 34
|}
 '''HOUR'''

 '''NOW'''

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

Príklad:
<source lang="sql">
SELECT NOW()
</source>

Výsledok:
{| class="wikitable"
|-
! NOW()
|-
| 2010-11-03 10:45:55
|}

 '''SYSDATE'''

 '''WEEK'''

Táto funkcia vracia číslo týždňa pre dáta. Dvoj-argument WEEK() vám umožňuje určiť, či týždeň začína v nedeľu alebo v pondelok a či návratová hodnota by mala byť v rozsahu 0 až 53 alebo 1 - 53. Ak je režim argument vynechaný, je použitá hodnota premennej default_week_format systém.

Príklad:

<source lang="sql">
SELECT WEEK();
</source>

Výsledok:

{| class="wikitable"
|-
! WEEK('2010-12-18')
|-
| 50
|}

 '''YEAR'''

Vracia rok pre dáta, v rozmedzí 1000 do 9999, alebo 0 pre. Nula. dátum.

Príklad:
<source lang="sql">
SELECT YEAR()
</source>

Výsledok:
{| class="wikitable"
|-
! YEAR('98-02-03')
|-
| 1998
|}
== Šifrovacie a komprimačné ==

'''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.

<source lang="sql">
insert into test (text,zasifrovane,heslo) values ("kiwiki",AES_ENCRYPT("kiwiki","123456789"),"1234567789")
</source> Výsledok: [[Image:Sql AES ENCRYPT.png|frame|center]]

'''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">
SELECT text, AES_DECRYPT(zasifrovane,"123456789") as rozsifrovane ,heslo from test
</source>

Výsledok: [[Image:Sql AES DECRYPT.png|frame|center]]

'''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().

<source lang="sql">
INSERT INTO test (text,zasifrovane) VALUES ("kiwiki",COMPRESS("kiwiki"))
</source> Výsledok: [[Image:Sql compress.png|frame|center]]

 <source lang="sql">
SELECT id, text, UNCOMPRESS(zasifrovane) AS dekomprimovane FROM test
</source> Výsledok: [[Image:Sql uncompress.png|frame|center]]

 '''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.

Dotaz:
<source lang="sql">
SELECT ENCODE("kiwiki", "heslo");
</source>

Výsledok:
{| class="wikitable"
|-
! encode("kiwiki", "heslo")
|-
| �d�\ny_
|}

'''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í.

Dotaz:
<source lang="sql">
SELECT DECODE("�d�\ny_", "heslo");
</source>

Výsledok:
{| class="wikitable"
|-
! encode("�d�\ny_", "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 [http://en.wikipedia.org/wiki/Data_Encryption_Standard DES]. Použitie je podobné ako pri funkciách aes_decript a aes_encrypt.

 '''MD5()''' [http://sk.wikipedia.org/wiki/Message-Digest_algorithm (Message-Digest algorithm) ]

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

Dotaz:
<source lang="sql">
SELECT MD5(password) FROM tabulka;
</source>

Výsledok:
{| class="wikitable"
|-
! MD5(password)
|-
| 21232f297a57a5a743894a0e4a801fc3
|}

'''SHA1(), SHA()''' [http://sk.wikipedia.org/wiki/Secure_Hash_Algorithm (Secure Hash Algorithm) ]

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

Dotaz:
<source lang="sql">
SELECT SHA(password) FROM tabulka;
SELECT SHA1(password) FROM tabulka;
</source>

Výsledok:
{| class="wikitable"
|-
! 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). <source lang="sql">
SELECT PASSWORD('kiwiki');
</source>

Výsledok:
{| class="wikitable"
|-
! PASSWORD('kiwiki')
|-
| *D94D4484B4D4060225F91D28D7BB131F917F760C
|}
== Zdroje a odkazy ==

*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

SQL - vstavané funkcie

2010-12-12T17:36:51Z

Michalec:

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

*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)

<source lang="sql">
SELECT COUNT(*) FROM city WHERE CountryCode = "SVK"
</source>

Výsledok: COUNT(*): 3

Vylepšená verzia, pre prehľadnejšie čítanie výsledku: <source lang="sql">
SELECT COUNT(*) AS pocet FROM city WHERE CountryCode = "SVK"
</source>

Výsledok: pocet: 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:

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

Podúloha 1: <source lang="sql">
SELECT MAX(Population) FROM city
</source> Výsledok: 10500000

Podúloha 2: <source lang="sql">
SELECT * FROM city where Population=
(SELECT MAX(Population) FROM city)
</source> Výsledok:

[[Image:Select max.png|center]]

=== AVG() ===

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

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

<source lang="sql">
SELECT AVG(population) FROM city where CountryCode="SVK"
</source>

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?

<source lang="sql">
SELECT SUM(population) FROM city where CountryCode="SVK"
</source>

Výsledok: SUM(population): 784143

=== STD() ===

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

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

<source lang="sql">
SELECT STD(population) FROM city where CountryCode="CZE"
</source>

Výsledok: STD(population): 321564.4992

== Matematické funkcie ==

'''ABS'''

'''SIN'''

'''COS'''

'''CEIL'''

'''EXP'''

Táto funkcia vracia hodnotu e (základ prirodzeného logaritmu), zdvihnutý k mocnine X.

Príklad:
<source lang="sql">
SELECT EXP(3);
</source>

Výsledok:
{| class="wikitable"
|-
!EXP(3)
|-
|20.0855369231877
|}

'''FLOOR'''

Táto funkcia vracia najväčšiu celočíselnú hodnotu, ktorá nie je väčšia ako X.

Príklad: <source lang="sql">
SELECT FLOOR(7.55);
</source>

Výsledok:
{| class="wikitable"
|-
!FLOOR(7.55)
|-
|7
|}

'''FORMAT'''

FORMAT () funkcia sa používa na formátovanie čísla X v nasledujúcom formáte: ###,###,###.## skrátiť na desatinné miesta . Nasledujúci príklad ukazuje použitie a výstup FORMAT () funkcie:

Príklad: <source lang="sql">
SELECT FORMAT(423423234.65434453,2);
FORMAT(423423234.65434453,2)
</source>

Výsledok:

423,423,234.65

'''LOG'''

'''LOG10'''

'''PI'''

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

Príklad:
<source lang="sql">
SELECT PI();
</source>

Výsledok:
{| class="wikitable"
|-
!PI()
|-
|3.141593
|}

'''POW( ''X, Y'' )'''

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

Príklad: <source lang="sql">
SELECT POWER(3,3);
</source>

Výsledok:
{| class="wikitable"
|-
!POWER(3,3)
|-
|27
|}

'''ROUND'''

Táto funkcia vracia X zaokrúhlené na celé číslo. Ak druhý argument D, je dodávaný, X potom vráti funkciu zaokrúhli na desatinné miesta D.

Príklad: <source lang="sql">
SELECT ROUND(5.693893);
ROUND(5.693893)

Príklad: <source lang="sql">
SELECT ROUND(5.693893,2);
ROUND(5.693893,2)
</source>

Výsledok:

6

6,69

'''SQRT(''X'')'''

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

Príklad:
<source lang="sql">
SELECT SQRT(16);
</source>

Výsledok:
{| class="wikitable"
|-
! SQRT(16)
|-
| 4.000000
|}

'''TAN(A)'''

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

Úloha: Aká je hodnota TAN(45)? V uhloviej miere je uhol 45° rovný PI/4
<source lang="sql">
SELECT TAN(PI()/4);
</source>

Výsledok:
{| class="wikitable"
|-
! SELECT TAN(PI()/4)
|-
| 1.619775
|}

'''RAND'''

SELECT RAND( ), RAND( ), RAND( );

+------------------+-----------------+------------------+

| RAND( ) | RAND( ) | RAND( ) |

+------------------+-----------------+------------------+

| 0.45464584925645 | 0.1824410643265 | 0.54826780459682 |

+------------------+-----------------+------------------+

== Textové funkcie ==

'''CHAR'''

Funkcia CHAR() vysvetlí každý argument N ako celé číslo a vytvorí retazec čísiel zložený zo znakov zadaného kodu

SELECT CHAR(77,121,83,81,'76');
+---------------------------------------------------------+
| CHAR(77,121,83,81,'76') |
+---------------------------------------------------------+
| MySQL |
+---------------------------------------------------------+

<source lang="sql">
</source>

'''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''

<source lang="sql">
SELECT concat(name, " - ", countrycode) AS Mesto FROM city
</source>

Výsledok:
{| class="wikitable"
|-
!Mesto
|-
|Košice - SVK
|-
|Prešov - SVK
|-
|Kabul - AFG
|-
|Qandahar - AFG
|-
|Herat - AFG
|-
|Mazar-e-Sharif - AFG
|-
|Amsterdam - NLD
|-
|Rotterdam - NLD
|}

'''FIELD'''

<source lang="sql">
</source>

'''LOWER'''

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

Úloha: konvertuj code na malé písmená v tabuľke country.

<source lang="sql">
SELECT Name,LOWER(code) from country
</source>

Výsledok:
{| class="wikitable"
|-
!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 name na veľké písmená v tabulke city

<source lang="sql">
SELECT upper("name") from city
</source>

 '''LTRIM(''str'')'''

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

Príklad: <source lang="sql">
SELECT LTRIM(' ahoj DBS')
</source>

Výsledok:

ahoj DBS

 '''RTRIM(''str'')'''

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

Príklad: <source lang="sql">
SELECT RTRIM('ahoj DBS ')
</source>

Výsledok:

ahoj DBS

 '''TRIM'''

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

Príklad: <source lang="sql">
SELECT TRIM(' ahoj DBS ')
</source>

Výsledok:

ahoj DBS

== Dátumové funkcie ==

'''ADDDATE'''

'''ADDTIME'''

'''CURRDATE'''

'''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:
<source lang="sql">
SELECT CURTIME();
</source>
Výsledok:

CURTIME()
18:31:48

{| class="wikitable"
|-
!CURTIME()
|-
|18:31:48
|}

'''EXTRACT()'''

'''DATEDIFF()'''

'''DATE'''

Dátum vo formáte RRRR-MM-DD, medzi 1000-01-01 a 9999-12-31. Napríklad, 30. decembra 1973 by sa uloží ako 1973-12-30.

Príklad:
<source lang="sql">
SELECT DATE
</source>

Výsledok:

2003-12-31

'''DAY'''

 '''DAYNAME'''

 '''DAYOFWEEK'''

Vracia deň v týždni index pre dáta (1 = nedeľa, 2 = pondelok., 7 = sobota). Tieto hodnoty indexu zodpovedajú štandardu ODBC.

Príklad:
<source lang="sql">
SELECT DAYOFWEEK()
</source>

Výsledok:
{| class="wikitable"
|-
! DAYOFWEEK('1998-02-03')
|-
| 3
|}
 '''DAYOFMONTH'''

Vracia deň v mesiaci na deň, v rozmedzí 0 - 31.

Príklad:
<source lang="sql">
SELECT DAYOFMONTH()
</source>

Výsledok:
{| class="wikitable"
|-
! DAYOFMONTH('1998-02-03')
|-
|3
|}

 '''DAYOFYEAR'''

Vracia deň v roku na dátum, v rozsahu 1 až 366.

Príklad:
<source lang="sql">
SELECT DAYOFYEAR()
</source>

Výsledok:
{| class="wikitable"
|-
! DAYOFYEAR()
|-
| 34
|}
 '''HOUR'''

 '''NOW'''

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

Príklad:
<source lang="sql">
SELECT NOW()
</source>

Výsledok:
{| class="wikitable"
|-
! NOW()
|-
| 2010-11-03 10:45:55
|}

 '''SYSDATE'''

 '''WEEK'''

 '''YEAR'''

Vracia rok pre dáta, v rozmedzí 1000 do 9999, alebo 0 pre. Nula. dátum.

Príklad:
<source lang="sql">
SELECT YEAR()
</source>

Výsledok:
{| class="wikitable"
|-
! YEAR('98-02-03')
|-
| 1998
|}
== Šifrovacie a komprimačné ==

'''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.

<source lang="sql">
insert into test (text,zasifrovane,heslo) values ("kiwiki",AES_ENCRYPT("kiwiki","123456789"),"1234567789")
</source> Výsledok: [[Image:Sql AES ENCRYPT.png|frame|center]]

'''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">
SELECT text, AES_DECRYPT(zasifrovane,"123456789") as rozsifrovane ,heslo from test
</source>

Výsledok: [[Image:Sql AES DECRYPT.png|frame|center]]

'''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().

<source lang="sql">
INSERT INTO test (text,zasifrovane) VALUES ("kiwiki",COMPRESS("kiwiki"))
</source> Výsledok: [[Image:Sql compress.png|frame|center]]

 <source lang="sql">
SELECT id, text, UNCOMPRESS(zasifrovane) AS dekomprimovane FROM test
</source> Výsledok: [[Image:Sql uncompress.png|frame|center]]

 '''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.

Dotaz:
<source lang="sql">
SELECT ENCODE("kiwiki", "heslo");
</source>

Výsledok:
{| class="wikitable"
|-
! encode("kiwiki", "heslo")
|-
| �d�\ny_
|}

'''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í.

Dotaz:
<source lang="sql">
SELECT DECODE("�d�\ny_", "heslo");
</source>

Výsledok:
{| class="wikitable"
|-
! encode("�d�\ny_", "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 [http://en.wikipedia.org/wiki/Data_Encryption_Standard DES]. Použitie je podobné ako pri funkciách aes_decript a aes_encrypt.

 '''MD5()''' [http://sk.wikipedia.org/wiki/Message-Digest_algorithm (Message-Digest algorithm) ]

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

Dotaz:
<source lang="sql">
SELECT MD5(password) FROM tabulka;
</source>

Výsledok:
{| class="wikitable"
|-
! MD5(password)
|-
| 21232f297a57a5a743894a0e4a801fc3
|}

'''SHA1(), SHA()''' [http://sk.wikipedia.org/wiki/Secure_Hash_Algorithm (Secure Hash Algorithm) ]

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

Dotaz:
<source lang="sql">
SELECT SHA(password) FROM tabulka;
SELECT SHA1(password) FROM tabulka;
</source>

Výsledok:
{| class="wikitable"
|-
! 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). <source lang="sql">
SELECT PASSWORD('kiwiki');
</source>

Výsledok:
{| class="wikitable"
|-
! PASSWORD('kiwiki')
|-
| *D94D4484B4D4060225F91D28D7BB131F917F760C
|}
== Zdroje a odkazy ==

*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