Mariadb 简明教程

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

TIMESTAMPDIFF 函数可用来计算年龄 −

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

CONCAT 函数连接操作后的返回结果字符串。您可以利用一个或多个参数。查看其给出的语法 −

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

下面是 MariaDB 中一些重要的数值函数−

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