Khóa học miễn phí MariaDB – Useful Functions nhận dự án làm có lương

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”);

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc