MariaDB practical features

May 16, 2021 MariaDB

1. MariaDB aggregation function

2. Maria DB age calculation

3. MariaDB string connection

4. MariaDB date/time function

5. MariaDB numeric function

6. MariaDB string function

This chapter contains a list of the most commonly used features, providing definitions, descriptions, and examples.

MariaDB aggregation function

The most commonly used aggregation functions are as follows -

S.No	Name and description
1	COUNT It counts the total 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 Contract GROUP BY Organization;
3	MAX It reveals the maximum value of a set of records. Example - SELECT Organization, MAX (account_size) FROM Contract GROUP BY Organization;
4	Avg It averages a set of records. Example - Select AVG (account_size) FROM contract;
5	SUM It calculates the sum of a set of records. Example - SELECT SUM (account_size) FROM contract;

Maria DB age calculation

The TIMESTAMPDIFF function provides a calculated age -

SELECT CURDATE() AS today;
SELECT ID, DOB, TIMESTAMPDIFF(YEAR,DOB,'2015-07-01') AS age FROM officer_info;

MariaDB string connection

The CONCAT function returns the result string after the connection operation. Y ou can use one or more parameters. C heck its syntax as follows -

SELECT CONCAT(item, item,...);

See the following example -

SELECT CONCAT('Ram', 'bu', 'tan');
Output:Rambutan

MariaDB date/time function

Here are the important date functions -

S.No	Name and description
1	CURDATE() It returns the date in yyyy-mm-dd or yymmdd format. Example - SELECT CURDATE ();
2	DATE() It returns dates in a variety of formats. Example - CREATE TABLE product_release_tbl (x DATE);
3	CURTIME() It returns time in HH:MM:SS or HHMMSS.uuuu. Example - SELECT CURTIME ();
4	DATE_SUB() It increases or decreases the number of days from the specified date. Example - SELECT DATE_SUB ('2016-02-08', INTERVAL 60 DAY);
5	DATEDIFF() It determines the number of days between two dates. Example - SELECT DATEDIFF ('2016-01-01 23:59:59', '2016-01-03');
6	DATE ADD() It increases or subtracts any units of time for dates and times. Example - SELECT DATE_ADD ('2016-01-04 23:59:59', INTERVAL 22 SECOND);
7	EXTRACT() It extracts units from the date. Example - SELECT EXTRACT (YPAR FROM'2016-01-08');
8	NOW() It returns the current date and time in the yyyyy-mm-dd hh:mm:ss or yyyymmdhmmss.uuuu format. Example - SELECT NOW();
9	DATE FORMAT() It formats the date according to the specified format string. Example - SELECT DATE_FORMAT ('2016-01-09 20:20:00', '%W%M%Y');

Here are some important time functions -

The number of seconds it returns the date.

S.No	Name and description
1	HOUR() It returns the hour of the time or the number of hours that have passed. Example - SELECT HOUR ('19:17:09');
2	LOCALTIME() It functions like NOW().
3	MICROSECOND() It returns the microseconds of time. Example - SELECT MICROSECOND ('16:30:00.543876');
4	MINUTE() It returns minutes of time. Example - SELECT MINUTE ('2016-05-22 17:22:01');
5	SECOND() The number of seconds it returns the date. Example - SELECT SECOND ('2016-03-12 16:30:04.000001');
6	TIME_FORMAT() It formats the time according to the specified format string. Example - SELECT TIME_FORMAT ('22:02:20', '%H%k%h%I%l');
7	TIMESTAMP() It provides a timestamp format for the activity in yyyy-mm-dd hh:mm:dd. Example - CREATE TABLE orders_ (ID INT, tmst TIMESTAMP);

MariaDB numeric function

Here are some important numeric functions in MariaDB -

S.No	Name and description
1	TRUNCATE() It returns a truncated number to the scale specified. Example - SELECT TRUNCATE (101.222,1);
2	COS() It returns the cosine of the x-arc. Example - SELECT COS (PI());
3	CEILING() It returns a minimum integer not less than x. Example - SELECT CEILING (2.11);
4	DEGREES() It converts the radian to a degree. Example - SELECT DEGREES (PI());
5	DIV() It performs the integer divide. Example - SELECT 100 DIV 4;
6	EXP() It returns the power of x of e. Example - SELECT EXP (2);
7	FLOOR() It returns a maximum integer that does not exceed x. Example - SELECT FLOOR (2.01);
8	LN() It returns x's natural pair. Example - SELECT LN (3);
9	LOG() It returns a natural or number of pairs for a given base. Example - SELECT LOG (3);
10	SQRT() It returns the square root. Example - SELECT SQRT (16);

MariaDB string function

Important string functions are given below -

S.No	Name and description
1	INSTR() It returns the position of the first instance of a subchain. Example - SELECT INSTR ('rambutan', 'tan');
2	RIGHT() It returns the string character to the far right. Example - SELECT RIGHT ('rambutan', 3);
3	LENGTH() It returns the byte length of the string. Example - SELECT LENGTH ('rambutan');
4	LOCATE() It returns the position of the first instance of a subchain. Example - SELECT LOCATE ('tan', 'rambutan');
5	INSERT() It returns a string, and at some point has a specified substring that is modified. Example - SELECT INSERT ('ramputan', 4, 1, 'b');
6	LEFT() It returns the character on the far left. Example - SELECT LEFT ('rambutan', 3);
7	UPPER() It changes the character to capital. Example - SELECT UPPER (lastname);
8	LOWER() It changes the character to small case. Example - SELECT LOWER (lastname);
9	STRCMP（） It compares strings, and when they are equal, returns 0. Example - Select STRCMP ('egg', 'cheese');
10	REPLACE() It returns a string after replacing the character. Example - SELECT REPLACE ('sully', 'l', 'n');
11	REVERSE() It reverses the characters in the string. Example - SELECT (REVERSE 'racecar');
12	REPEAT() It returns a string that repeats a given character x times. Example - SELECT (REPEAT 'ha', 10);
13	SUBSTRING() It returns a subchain from the string, starting with position x. Example - SELECT SUBSTRING ('rambutan', 3);
14	TRIM() It removes trailing/leading characters from the string. Example - SELECT TRIM (LEADING', 'FROM' _rambutan');