MariaDB - Useful Functions
This chapter contains a list of the most frequently used functions, offering definitions, explanations, and examples.
MariaDB Aggregate Functions
Most frequently used aggregate functions are given below −
| Sr.No |
Name & Description |
| 1 |
COUNT
It counts the number of records.
Example − SELECT COUNT(*) FROM customer_table;
|
| 2 |
MIN
It reveals the minimum value of a set of records.
Example − SELECT organization, MIN(account) FROM contracts GROUP BY organization;
|
| 3 |
MAX
It reveals the maximum value of a set of records.
Example − SELECT organization, MAX(account_size) FROM contracts GROUP BY organization;
|
| 4 |
AVG
It calculates the average value of a set of records.
Example − SELECT AVG(account_size) FROM contracts;
|
| 5 |
SUM
It calculates the sum of a set of records.
Example − SELECT SUM(account_size) FROM contracts;
|
MariaDB Age Calculation
The TIMESTAMPDIFF function provides a way to calculate age −
SELECT CURDATE() AS today;
SELECT ID, DOB, TIMESTAMPDIFF(YEAR,DOB,'2015-07-01') AS age FROM officer_info;
MariaDB String Concatenation
The CONCAT function returns the resulting string after a concatenation operation. You can utilize one or more arguments. Review its syntax given below −
SELECT CONCAT(item, item,...);
Review the following example −
SELECT CONCAT('Ram', 'bu', 'tan');
Output:Rambutan
MariaDB Date/Time Functions
Given below are important date functions −
| Sr.No |
Name & Description |
| 1 |
CURDATE()
It returns the date in yyyy-mm-dd or yyyymmdd format.
Example − SELECT CURDATE();
|
| 2 |
DATE()
It returns the date in multiple formats.
Example −CREATE TABLE product_release_tbl (x DATE);
|
| 3 |
CURTIME()
It returns the time in HH:MM:SS or HHMMSS.uuuuuu format.
Example − SELECT CURTIME();
|
| 4 |
DATE_SUB()
It adds or subtracts a number of days from the specified date.
Example − SELECT DATE_SUB('2016-02-08', INTERVAL 60 DAY);
|
| 5 |
DATEDIFF()
It determines the days between two dates.
Example − SELECT DATEDIFF('2016-01-01 23:59:59','2016-01-03');
|
| 6 |
DATE ADD()
It adds or subtracts any unit of time to/from the date and time.
Example − SELECT DATE_ADD('2016-01-04 23:59:59', INTERVAL 22 SECOND);
|
| 7 |
EXTRACT()
It extracts a unit from the date.
Example − SELECT EXTRACT(YEAR FROM '2016-01-08');
|
| 8 |
NOW()
It returns the current date and time in either yyyy-mm-dd hh:mm:ss or yyyymmddhhmmss.uuuuuu format.
Example − SELECT NOW();
|
| 9 |
DATE FORMAT()
It formats the date in accordance with the specified format string.
Example − SELECT DATE_FORMAT('2016-01-09 20:20:00', '%W %M %Y');
|
Following are some important time functions −
| Sr.No |
Name & Description |
| 1 |
HOUR()
It returns the hour of the time, or the hours elapsed.
Example − SELECT HOUR('19:17:09');
|
| 2 |
LOCALTIME()
It functions exactly like NOW().
|
| 3 |
MICROSECOND()
It returns the microseconds of the time.
Example − SELECT MICROSECOND('16:30:00.543876');
|
| 4 |
MINUTE()
It returns the minutes of the time.
Example − SELECT MINUTE('2016-05-22 17:22:01');
|
| 5 |
SECOND()
It returns the seconds of the date.
Example − SELECT SECOND('2016-03-12 16:30:04.000001');
|
| 6 |
TIME_FORMAT()
It formats the time in accordance with the specified format string.
Example − SELECT TIME_FORMAT('22:02:20', '%H %k %h %I %l');
|
| 7 |
TIMESTAMP()
It provides a timestamp for an activity in the format yyyy-mm-dd hh:mm:dd.
Example − CREATE TABLE orders_ (ID INT, tmst TIMESTAMP);
|
MariaDB Numeric Functions
Given below are some important numeric functions in MariaDB −
| Sr.No |
Name & Description |
| 1 |
TRUNCATE()
It returns a truncated number to decimal place specification.
Example − SELECT TRUNCATE(101.222, 1);
|
| 2 |
COS()
It returns the cosine of x radians.
Example − SELECT COS(PI());
|
| 3 |
CEILING()
It returns the smallest integer not below x.
Example − SELECT CEILING(2.11);
|
| 4 |
DEGREES()
It converts radians to degrees.
Example − SELECT DEGREES(PI());
|
| 5 |
DIV()
It performs integer division.
Example − SELECT 100 DIV 4;
|
| 6 |
EXP()
It returns e to the power of x.
Example − SELECT EXP(2);
|
| 7 |
FLOOR()
It returns the largest integer not above x.
Example − SELECT FLOOR(2.01);
|
| 8 |
LN()
It returns the natural logarithm of x.
Example − SELECT LN(3);
|
| 9 |
LOG()
It returns the natural logarithm or the logarithm to a given base.
Example − SELECT LOG(3);
|
| 10 |
SQRT()
It returns the square root.
Example − SELECT SQRT(16);
|
MariaDB String Functions
Important string functions are given below −
| Sr.No |
Name & Description |
| 1 |
INSTR()
It returns the position of the first instance of a substring.
Example − SELECT INSTR('rambutan', 'tan');
|
| 2 |
RIGHT()
It returns the rightmost string characters.
Example − SELECT RIGHT('rambutan', 3);
|
| 3 |
LENGTH()
It returns the byte length of a string.
Example − SELECT LENGTH('rambutan');
|
| 4 |
LOCATE()
It returns the position of the first instance of a substring.
Example − SELECT LOCATE('tan', 'rambutan');
|
| 5 |
INSERT()
It returns a string, with a specified substring at a certain position, that was modified.
Example − SELECT INSERT('ramputan', 4, 1, 'b');
|
| 6 |
LEFT()
It returns the leftmost characters.
Example − SELECT LEFT('rambutan', 3);
|
| 7 |
UPPER()
It changes characters to uppercase.
Example − SELECT UPPER(lastname);
|
| 8 |
LOWER()
It changes characters to lowercase.
Example − SELECT LOWER(lastname);
|
| 9 |
STRCMP()
It compares strings and returns 0 when they are equal.
Example − SELECT STRCMP('egg', 'cheese');
|
| 10 |
REPLACE()
It returns a string after replacing characters.
Example − SELECT REPLACE('sully', 'l', 'n');
|
| 11 |
REVERSE()
It reverses characters in a string.
Example − SELECT REVERSE('racecar');
|
| 12 |
REPEAT()
It returns a string repeating given characters x times.
Example − SELECT REPEAT('ha ', 10);
|
| 13 |
SUBSTRING()
It returns a substring from a string, starting at position x.
Example − SELECT SUBSTRING('rambutan',3);
|
| 14 |
TRIM()
It removes trailing/leading characters from a string.
Example − SELECT TRIM(LEADING '_' FROM '_rambutan');
|
