MySQL Funkce: Řetězcové, Číselné, Uživatelsky definované, Uložené
Marcus Allen
Co jsou funkce?
MySQL umí mnohem víc než jen ukládat a načítat data. Můžeme také provádět manipulace s daty před jeho načtením nebo uložením. To je tam kde MySQL Přicházejí funkce. Funkce jsou jednoduše kousky kódu, které provádějí nějaké operace a pak vracejí výsledek. Některé funkce přijímají parametry, zatímco jiné funkce parametry nepřijímají.
Podívejme se krátce na příklad MySQL funkce. Ve výchozím stavu, MySQL ukládá datové typy dat ve formátu „RRRR-MM-DD“. Předpokládejme, že jsme vytvořili aplikaci a naši uživatelé chtějí, aby se datum vrátilo ve formátu „DD-MM-RRRR“, můžeme použít MySQL vestavěná funkce DATE_FORMAT k dosažení tohoto cíle. DATE_FORMAT je jednou z nejpoužívanějších funkcí v MySQL. Podíváme se na to podrobněji, až budeme lekci rozvíjet.
Proč používat funkce?
Na základě příkladu uvedeného v úvodu si lidé se zkušenostmi s počítačovým programováním mohou myslet: „Proč se obtěžovat MySQL Funkce? Stejného efektu lze dosáhnout pomocí skriptovacího/programovacího jazyka? Je pravda, že toho můžeme dosáhnout napsáním některých procedur/funkcí v aplikačním programu.
Vraťme se k našemu příkladu DATE v úvodu, aby naši uživatelé získali data v požadovaném formátu, bude muset obchodní vrstva provést nezbytné zpracování.
To se stává problémem, když se aplikace musí integrovat s jinými systémy. Když používáme MySQL funkce, jako je DATE_FORMAT, pak můžeme mít tuto funkcionalitu začleněnou do databáze a jakákoli aplikace, která potřebuje data, je dostane v požadovaném formátu. Tento snižuje přepracování v obchodní logice a snižuje nekonzistenci dat.
Další důvod, proč bychom měli zvážit použití MySQL funkcí je skutečnost, že může pomoci snížit síťový provoz v aplikacích klient/server. Business Layer bude potřebovat pouze volat k uloženým funkcím bez nutnosti manipulovat s daty. V průměru může použití funkcí výrazně zlepšit celkový výkon systému.
Typy funkcí
Integrované funkce
MySQL je dodáván s řadou vestavěných funkcí. Vestavěné funkce jsou jednoduše funkce, které jsou již implementovány v systému MySQL server. Tyto funkce nám umožňují provádět různé typy manipulací s daty. Vestavěné funkce lze v zásadě roztřídit do následujících nejpoužívanějších kategorií.
- Funkce řetězců – pracovat s datovými typy řetězců
- Číselné funkce – pracovat s číselnými datovými typy
- Funkce data – pracovat s datovými typy dat
- Agregační funkce – pracovat se všemi výše uvedenými datovými typy a vytvářet souhrnné sady výsledků.
- Ostatní funkce - MySQL podporuje také další typy vestavěných funkcí, ale naši lekci omezíme pouze na výše uvedené funkce.
Podívejme se nyní podrobně na každou z výše zmíněných funkcí. Nejpoužívanější funkce vysvětlíme pomocí našeho „Myflixdb“.
Řetězcové funkce
Již jsme se podívali na to, co funkce řetězce dělají. Podíváme se na praktický příklad, který je využívá. V naší tabulce filmů jsou názvy filmů uloženy pomocí kombinací malých a velkých písmen. Předpokládejme, že chceme získat seznam dotazů, který vrací názvy filmů velkými písmeny. K tomu můžeme použít funkci „UCASE“. Vezme řetězec jako parametr a převede všechna písmena na velká písmena. Níže uvedený skript ukazuje použití funkce „UCASE“.
SELECT `movie_id`,`title`, UCASE(`title`) FROM `movies`;
ZDE
- UCASE(`title`) je vestavěná funkce, která přebírá název jako parametr a vrací jej velkými písmeny s názvem aliasu `upper_case_title`.
Spuštění výše uvedeného skriptu v MySQL workbench proti Myflixdb nám dává následující výsledky uvedené níže.
| movie_id | title | UCASE('title') |
|---|---|---|
| 16 | 67% Guilty | 67% GUILTY |
| 6 | Angels and Demons | ANGELS AND DEMONS |
| 4 | Code Name Black | CODE NAME BLACK |
| 5 | Daddy's Little Girls | DADDY'S LITTLE GIRLS |
| 7 | Davinci Code | DAVINCI CODE |
| 2 | Forgetting Sarah Marshal | FORGETTING SARAH MARSHAL |
| 9 | Honey mooners | HONEY MOONERS |
| 19 | movie 3 | MOVIE 3 |
| 1 | Pirates of the Caribean 4 | PIRATES OF THE CARIBEAN 4 |
| 18 | sample movie | SAMPLE MOVIE |
| 17 | The Great Dictator | THE GREAT DICTATOR |
| 3 | X-Men | X-MEN |
MySQL podporuje řadu řetězcových funkcí. Úplný seznam všech vestavěných řetězcových funkcí naleznete na tomto odkazu http://dev.mysql.com/doc/refman/5.0/en/string-functions.html on MySQL webová stránka.
Číselné funkce
Jak již bylo zmíněno, tyto funkce pracují s číselnými datovými typy. Můžeme provádět matematické výpočty na číselných datech v příkazech SQL.
Aritmetické operátory
MySQL podporuje následující aritmatické operátory, které lze použít k provádění výpočtů v příkazech SQL.
| Název | Description |
|---|---|
| DIV | Celé dělení |
| / | Divize |
| - | Odčítání |
| + | Přidání |
| * | Násobení |
| % nebo MOD | modul |
Podívejme se nyní na příklady každého z výše uvedených operátorů
Celočíselné dělení (DIV)
SELECT 23 DIV 6 ;
Spuštění výše uvedeného skriptu nám poskytne následující výsledky.
3
Operátor divize (/)
Podívejme se nyní na příklad operátora divize. Upravíme příklad DIV.
SELECT 23 / 6 ;
Spuštění výše uvedeného skriptu nám poskytne následující výsledky.
3.8333
Operátor odčítání (-)
Podívejme se nyní na příklad operátoru odčítání. Použijeme stejné hodnoty jako v předchozích dvou příkladech
SELECT 23 - 6 ;
Spuštěním výše uvedeného skriptu získáme 17
Operátor přidání (+)
Podívejme se nyní na příklad operátoru sčítání. Upravíme předchozí příklad.
SELECT 23 + 6 ;
Spuštěním výše uvedeného skriptu získáme 29
Operátor násobení (*)
Podívejme se nyní na příklad operátoru násobení. Použijeme stejné hodnoty jako v předchozích příkladech.
SELECT 23 * 6 AS `multiplication_result`;
Spuštění výše uvedeného skriptu nám poskytne následující výsledky.
| výsledek_násobení |
|---|
| 138 |
Modulo operátor (-)
Operátor modulo vydělí N M a dá nám zbytek. Podívejme se nyní na příklad operátora modulo. Použijeme stejné hodnoty jako v předchozích příkladech.
SELECT 23 % 6 ;
OR
SELECT 23 MOD 6 ;
Spuštěním výše uvedeného skriptu získáme 5
Podívejme se nyní na některé běžné numerické funkce v MySQL.
Na podlaze – tato funkce odstraní z čísla desetinná místa a zaokrouhlí jej na nejbližší nejnižší číslo. Skript zobrazený níže ukazuje jeho použití.
SELECT FLOOR(23 / 6) AS `floor_result`;
Spuštění výše uvedeného skriptu nám poskytne následující výsledky.
| Podlaha_výsledek |
|---|
| 3 |
Kolo – tato funkce zaokrouhlí číslo s desetinnými místy na nejbližší celé číslo. Skript zobrazený níže ukazuje jeho použití.
SELECT ROUND(23 / 6) AS `round_result`;
Spuštění výše uvedeného skriptu nám poskytne následující výsledky.
| Kulatý_výsledek |
|---|
| 4 |
Rand – tato funkce slouží ke generování náhodného čísla, jeho hodnota se mění při každém volání funkce. Skript zobrazený níže ukazuje jeho použití.
SELECT RAND() AS `random_result`;
Uložené funkce
Uložené funkce jsou stejné jako vestavěné funkce s tím rozdílem, že uloženou funkci musíte definovat sami. Jakmile je uložená funkce vytvořena, lze ji použít v příkazech SQL stejně jako jakoukoli jinou funkci. Základní syntaxe pro vytvoření uložené funkce je uvedena níže
CREATE FUNCTION sf_name ([parameter(s)]) RETURNS data type DETERMINISTIC STATEMENTS
ZDE
- “VYTVOŘIT FUNKCI sf_name ([parametr(y)]) “ je povinný a vypovídá MySQL server k vytvoření funkce s názvem `sf_name' s volitelnými parametry definovanými v závorkách.
- „VRACÍ typ dat“ je povinný a určuje datový typ, který má funkce vrátit.
- "DETERMINISTICKÝ" znamená, že funkce vrátí stejné hodnoty, pokud jsou jí dodány stejné argumenty.
- "PROHLÁŠENÍ" je procedurální kód, který funkce vykonává.
Podívejme se nyní na praktický příklad, který implementuje vestavěnou funkci. Předpokládejme, že chceme vědět, které vypůjčené filmy jsou po datu návratu. Můžeme vytvořit uloženou funkci, která akceptuje datum návratu jako parametr a poté jej porovná s aktuálním datem v MySQL server. Pokud je aktuální datum menší než datum návratu filmu, vrátíme „Ne“, jinak vrátíme „Ano“. Níže uvedený skript nám k tomu pomáhá.
DELIMITER |
CREATE FUNCTION sf_past_movie_return_date (return_date DATE)
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
DECLARE sf_value VARCHAR(3);
IF curdate() > return_date
THEN SET sf_value = 'Yes';
ELSEIF curdate() <= return_date
THEN SET sf_value = 'No';
END IF;
RETURN sf_value;
END|
Spuštěním výše uvedeného skriptu byla vytvořena uložená funkce `sf_past_movie_return_date`.
Pojďme nyní otestovat naši uloženou funkci.
SELECT `movie_id`,`membership_number`,`return_date`,CURDATE() ,sf_past_movie_return_date(`return_date`) FROM `movierentals`;
Spuštění výše uvedeného skriptu v MySQL workbench proti myflixdb nám dává následující výsledky.
| movie_id | membership_number | return_date | CURDATE() | sf_past_movie_return_date('return_date') |
|---|---|---|---|---|
| 1 | 1 | NULL | 04-08-2012 | NULL |
| 2 | 1 | 25-06-2012 | 04-08-2012 | yes |
| 2 | 3 | 25-06-2012 | 04-08-2012 | yes |
| 2 | 2 | 25-06-2012 | 04-08-2012 | yes |
| 3 | 3 | NULL | 04-08-2012 | NULL |
Uživatelsky definované funkce
MySQL také podporuje uživatelem definované funkce, které se rozšiřují MySQL. Uživatelsky definované funkce jsou funkce, které můžete vytvořit pomocí programovacího jazyka, jako je např C, C++ atd. a poté je přidejte do MySQL server. Po přidání je lze používat stejně jako jakoukoli jinou funkci.
Shrnutí
- Funkce nám umožňují vylepšit možnosti MySQL.
- Funkce vždy vracejí hodnotu a mohou volitelně přijímat parametry.
- Vestavěné funkce jsou funkce, které jsou dodávány s MySQL. Mohou být kategorizovány podle datových typů, se kterými pracují, tj. vestavěných řetězců, data a číselných funkcí.
- Uložené funkce vytváří uživatel uvnitř MySQL serveru a lze je použít v příkazech SQL.
- Uživatelsky definované funkce jsou vytvářeny venku MySQL a lze je začlenit do MySQL serveru.
