MariaDB Aggregate Functions
In this topic, we will discuss the useful functions of MariaDB.
Most frequently used aggregate functions are given below −
| Sr.No | Name & Description |
|---|---|
| 1 | COUNTIt counts the number of records.Example − SELECT COUNT(*) FROM customer_table; |
| 2 | MINIt reveals the minimum value of a set of records.Example − SELECT organization, MIN(account) FROM contracts GROUP BY organization; |
| 3 | MAXIt reveals the maximum value of a set of records.Example − SELECT organization, MAX(account_size) FROM contracts GROUP BY organization; |
| 4 | AVGIt calculates the average value of a set of records.Example − SELECT AVG(account_size) FROM contracts; |
| 5 | SUMIt 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('zaf', 'rul', 'khan');
Output:zafrulkhan
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’); |
In this guide, we will learn about the useful Functions of MariaDB. To know more Click here.