SQL - vstavané funkcie

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)

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

Výsledok: COUNT(*): 3

Vylepšená verzia, pre prehľadnejšie čítanie výsledku:

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

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:

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:

Súbor:Select max.png

AVG()

Funkcia AVG() počíta aritmetický priemet 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 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?

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

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:

   SELECT EXP(3);

Výsledok:

EXP(3)
20.0855369231877

FLOOR

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

Príklad:

  SELECT FLOOR(7.55);

Výsledok:

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:

SELECT FORMAT(423423234.65434453,2); 
FORMAT(423423234.65434453,2)

Výsledok:

423,423,234.65

LOG

LOG10

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

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:

SELECT ROUND(5.693893);
ROUND(5.693893)

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

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:

   SELECT SQRT(16);

Výsledok:

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

   SELECT TAN(PI()/4);

Výsledok:

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

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

LOWER

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

Úloha: konvertuj 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 name na veľké písmená v tabulke city

SELECT upper("name") from city

LTRIM(str)

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

Príklad:

   SELECT LTRIM('   ahoj DBS')

Výsledok:

ahoj DBS

RTRIM(str)

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

Príklad:

   SELECT RTRIM('ahoj DBS   ')

Výsledok:

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:

ahoj DBS

Dátumové funkcie

ADDDATE

ADDTIME

CURRDATE

CURTIME

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:

SELECT DATE

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:

   SELECT DAYOFWEEK()

Výsledok:

DAYOFWEEK('1998-02-03')
3

DAYOFMONTH

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

Príklad:

   SELECT DAYOFMONTH()

Výsledok:

DAYOFMONTH()

DAYOFYEAR

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

Príklad:

   SELECT DAYOFYEAR()

Výsledok:

DAYOFYEAR()
34

HOUR

NOW

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

Príklad:

   SELECT NOW()

Výsledok:

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:

   SELECT YEAR()

Výsledok:

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

   insert into test (text,zasifrovane,heslo) values ("kiwiki",AES_ENCRYPT("kiwiki","123456789"),"1234567789")

Výsledok:

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:

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

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

Výsledok:

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

Výsledok:

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:

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

Výsledok:

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:

 SELECT DECODE("�d�\ny_", "heslo");

Výsledok:

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 DES. Použitie je podobné ako pri funkciách aes_decript a aes_encrypt.

MD5() (Message-Digest algorithm)

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

Dotaz:

 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.

Dotaz:

 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

• 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