PHPPamokos.lt


8. MySQL vidinės funkcijos

MySQL sistema turi gausybę naudingų funkcijų, dalį iš jų jau matėme grupavime - tokias kaip COUNT() ar AVG(). Šiandien pažvelgsime į daugiau funkcijų, kurios pagelbėja darbui su tekstiniais laukais, data/laiku ir skaičiais.

Funkcijos darbui su skaičiais

Pradėkime nuo to, kad pačios SQL užklausos viduje galime naudoti matematinius veiksmus - sudėtį, atimtį, daugybą, dalybą. Sakykime, iš kainos litais paverčiame į eurus:

SELECT price/3.4528 AS price_euro FROM products;

O toliau - pagalbinės funkcijos. Pirmiausiai - apvalinimui funkcija ROUND():

SELECT ROUND(price/3.4528) AS price_euro FROM products;

Taip pat funkcija ROUND() turi ir antrą parametrą - kiek skaitmenų po kablelio palikti. Pinigams mums, be abejo, reikia dviejų, taigi:

SELECT ROUND(price/3.4528, 2) AS price_euro FROM products;

Galima daryti ir griežtesnį apvalinimą - funkcija FLOOR() suapvalina į mažesnę pusę (t.y. 5.7 bus suapvalinta į 5), o funkcija CEILING() - iki didesnio (t.y. 5.4 bus suapvalinta iki 6).

SELECT FLOOR(price/3.4528, 2) AS price_euro FROM products;
SELECT CEILING(price/3.4528, 2) AS price_euro FROM products;

Galima ir dar griežčiau - tiesiog palikti norimą skaitmenų skaičių po kablelio - tam skirta funkcija TRUNCATE(). Jos rezultatas toks - sakykime, jei turite skaičių 18.3289 ir darote jam TRUNCATE(18.3829, 1) tai rezultatas bus 18.3.

Funkcijos darbui su tekstinėmis eilutėmis

Tekstiniai laukai duomenų bazėse naudojami turbūt dažniausiai, ir užima daugiausiai vietos diske - tad juos dažnai reikia formatuoti kitaip nei duomenų bazėje. Tam skirtos tekstinės funkcijos. Turbūt dažniausiai teko matyti naudojamą CONCAT(), kuri sudeda dvi ar daugiau eilučių. Ir eilutės gali būti duomenų bazės laukai. Dažniausias pritaikymas - jei duomenų bazėje turite atskirai laukus first_name ir last_name, o jums rezultatą reikia išvesti kaip vieną vardą/pavardę, atskiriant juos tarpu. Tai atrodytų taip:

SELECT CONCAT(first_name, ' ', last_name) as full_name FROM customers;

Kaip matote, CONCAT() funkcijai galite paduoti kiek norite parametrų per kablelį - ji juos visus sudės į vieną eilutę.


Turbūt antra pagal naudojimą yra funkcija REPLACE(), kurios pavadinimas turbūt nusako jos paskirtį - ji pakeičia duotoje eilutėje kažkokį simbolių šabloną į kitą simbolių šabloną. Parametrai yra trys: pradinė eilutė, ką keisti ir į ką keisti. Sakykime, mums reikia produkto pavadinime žodį "Produktas" išvesti kaip anglišką "Product":

SELECT REPLACE(name, 'Produktas', 'Product') as english_name FROM products;

Einame toliau - kaip ištraukti tam tikrus simbolius iš tekstinio lauko? Sakykime, pirmus tris, paskutinius keturis ar kokius penkis, pradedant nuo ketvirtojo? Tam mums padės trys funkcijos:

  • LEFT(name, 3) - grąžina pirmus 3 simbolius iš stulpelio name;
  • RIGHT(name, 3) - grąžina paskutinius 3 simbolius iš stulpelio name;
  • SUBSTRING(name, 5, 4) - grąžina pirmus 4 simbolius iš stulpelio name, pradedant nuo 5-ojo;
SELECT name, SUBSTRING(name, 5, 4) as short_name FROM products;

Norite sužinoti lauko reikšmės ilgį - simbolių skaičių? Nieko nėra paprasčiau - funkcija LENGTH():

SELECT first_name, LENGTH(first_name) FROM customers;

Taip pat gali prireikti rezultatą išvesti tik didžiosiomis arba tik mažosiomis raidėmis - tam padės funkcijos UCASE() ir LCASE():

SELECT first_name, UCASE(first_name) FROM customers;

Funkcijos darbui su datomis

Datos ir laikas formatavimas yra viena didžiausių problemų, dirbant su duomenų bazėmis - ypač tuose web-projektuose, kur reikia išvedinėti nesdandartiniu būdu: sakykime, Lietuvoje yra paplitęs formatas YYYY-MM-DD, Britanijoje DD/MM/YYYY, o Amerikoje - MM/DD/YYYY. Ir pabandyk visiems įtikti. Taigi, ką mums šioje vietoje siūlo MySQL?

Pagrindinė funkcija datos rezultato suformavimui yra DATE_FORMAT(), kuri paima datos reikšmę ir iš jos išveda tik tuos parametrus, kurių paprašome skliausteliuose. Kiekviena datos dalis yra užkoduojama tam tikru simboliu su % ženklu priekyje, sakykime taip:

SELECT DATE_FORMAT(order_time, '%d-%m-%Y') FROM orders;

Kaip matote, šis pavyzdys grąžina rezultatą formatu diena-mėnuo-metai. Kokie dar yra galimi parametrai? Štai lentelė:

Formatas Reikšmė
%d Dienos numeris - pvz 01, 15 ar 31
%D Diena su angliška galūne - 1st, 23rd ar 30th
%m Mėnuo skaičiumi - 01, 04 ar 12
%M Mėnuo angliškai - January, August ar December
%b Mėnuo angliškai sutrumpintai - Jan, Aug ar Dec
%y Metai - tik paskutiniai du skaičiai: 15, 12 ar 00
%Y Metai - pilni keturi skaičiai: 2015, 1999 ar 1984
%W Savaitės diena angliškai - Monday, Wednesday ar Sunday
%a Savaitės diena sutrumpintai - Mon, Wed ar Sun
%H Valanda pagal 24 valandų skalę - 08, 17 ar 23
%h Valanda pagal 12 valandų skalę - pvz 23:00 tampa 11
%p AM arba PM
%i Minutės - 01, 18 ar 49
%s Sekundės - 00, 48 ar 59

Taigi, galite manipuliuoti skirtingomis šių parametrų kombinacijomis ir išvesti datą norimu formatu. Bet jeigu norite, sakykime, turėti konkrečią vieną reikšmę iš datos lauko - yra dar eilė funkcijų, kurios grąžina būtent tai, kas nusakoma pačių funkcijų pavadinime:

YEAR(date), MONTH(date), MONTHNAME(date), HOUR(date), MINUTE(date), SECOND(date), DAYOFMONTH(date), DAYNAME(date), DAYOFYEAR(date).


Taip pat naudingos funkcijos - sužinoti dabartinį laiką. Tam yra funkcijos NOW(), CURTIME() ir CURDATE():

Tiesa, reikia turėti omenyje kad rodomas laikas fiziškai to kompiuterio, kuriame yra MySQL serveris, tad turėkite tai omenyje, jei dirbsite su skirtingomis laiko juostomis ar jei serveris bus kitoje šalyje.


Taip pat kartais reikia su datomis atlikti aritmetinius veiksmus - pridėti kelias valandas, atsukti kelias dienas ir panašiai. Tam skirtos funkcijos DATE_ADD() ir DATE_SUB(). Abi funkcijos turi du parametrus - pačią datą/lauką, ir veiksmo intervalą, kuris užrašomas žodžiu INTERVAL ir po to dar keliais žodžiais. Pavyzdys:

SELECT DATE_ADD(CURDATE(), INTERVAL 60 DAY);

Funkcijos logikos valdymui - IF, CASE ir IFNULL

Nors MySQL nėra programavimo kalba, bet ir jos rėmuose galima rašyti IF-tipo struktūras. Sakykime, jei vienas laukas yra didesnis už X, tada grąžinti jį, o jei ne - tada kitą lauką.

Funkcija IF() turi tris parametrus - sąlygą, ką grąžinti jeigu sąlyga patenkinama, ir ką grąžinti jeigu sąlyga nepatenkinama. Pavyzdys:

SELECT name, price, IF(price > 100, 'Brangu', 'Pigu') FROM products;

Atskiras IF() variantas yra funkcija IFNULL() - ji turi du parametrus: patį lauką/reikšmę, ir ką išvesti tuo atveju jei jis lygus NULL.

SELECT IFNULL(last_name, 'Bepavardenis');

Ir trečia funkcija iš šio sąrašo - sudėtingiausia struktūra CASE, tai net ne funkcija o visas blokas sakinių. Čia išvardinamos galimos reikšmės ir tada užrašoma, ką grąžinti esant vienai ar kitai sąlygai.

SELECT CASE price 
WHEN 100 THEN 'Hundred' 
WHEN 200 THEN 'Two hundred'
END as price_words FROM products;

Kaip matote, kiekviena sąlyga nusakoma bloku WHEN ... THEN .... Svarbi smulkmena - nepamirškite struktūros pabaigoje parašyti žodį END.

Apibendrinimas

Štai tiek naudingų funkcijų siūlo MySQL - tikiuosi pasinaudosite jomis, formatuodami savo duomenis iš bazės.



(c) 2015-2018. Visais klausimais kreipkitės povilas@laraveldaily.com