Common functions of MySQL basics

1. Common function classification

1.1 Single-line function:
1.2 Grouping function:

2. Single-line functions

3. Mathematical functions

4. Date functions

5. Other functions

6. Control Function

1. Common function classification

1.1 Single-line function:

Character functions
Character control functions (CONCAT, SUBSTR, LENGTH...)
Case Control Function
Mathematical functions
Date functions
Flow control functions

1.2 Grouping function:

The grouping functions tend to be statistical, such as AVG(), COUNT(), MAX(), MIN(), SUM()

Single row functions and grouping functions:

(1) Grouping function: It is mainly used for statistics and aggregation. The grouping function receives multiple inputs and returns one output.

(2) Single-row functions: The character functions, mathematical functions, and date functions mentioned above all belong to the category of single-row functions. Single-row functions only transform one row and return one result for each row.

2. Single-line functions

Character functions

concat: character concatenation function

substr: intercept string,

SELECT SUBSTR('Li Mochou fell in love with Lu Zhanyuan',7) out_put; // Index starts at 1, Lu ZhanyuanSELECT SUBSTR('Li Mochou fell in love with Lu Zhanyuan',1,3) out_put; // Li Mochou

instr: Returns the index of the first occurrence of a substring, or 0 if not found.

SELECT INSTR('Yang Bu Yin Liu Xia regretted falling in love with Yin Liu Xia','Yin Liu Xia') AS out_put; // 3

trim: remove the space elements on the left and right

SELECT LENGTH(TRIM(' 张翠山')) AS out_put;
SELECT TRIM('aa' FROM 'aaaaaaaaaa张aaaaaaaaaaaaaCuishanaaaaaaaaaaaaa') AS out_put; //The a in the middle cannot be removed

upper: uppercase conversion string

lower: lowercase conversion string

lpad: Left pad with specified characters to a specified length

SELECT LPAD('尹素素',2,'*') AS out_put; // The number indicates the specified length

rpad: right pad with specified characters to a specified length

replace: replace

3. Mathematical functions

Rounding SELECT ROUND(-1.55); // -2
SELECT ROUND(1.567,2); //Specify the character precision 1.56

Round up SELECT CEIL(-1.02); // -1

Round down SELECT FLOOR(-9.99); // -10

SELECT TRUNCATE(1.69999,1); // 1.6

SELECT MOD(-10,3); // -1
SELECT MOD(-10,-3); // -1
SELECT MOD(10,-3); // 1

4. Date functions

Returns the current system date + time SELECT NOW(); // 2021-03-16 09:00:35

Returns the current system date, excluding time. SELECT CURDATE(); // 2021-03-16

Returns the current time, excluding the date SELECT CURTIME(); // 09:00:35

You can get the specified part, year, month, day, hour, minute, second SELECT YEAR(NOW()) year;
SELECT YEAR('1998-1-1') year;
SELECT MONTH(NOW()) month;
SELECT MONTHNAME(NOW()) MONTH;

Convert characters to dates in the specified format SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put; // 1998-03-02

Convert the date into characters SELECT DATE_FORMAT(NOW(),'%y year%m month%d day') AS out_put;

5. Other functions

SELECT VERSION(); // sql version SELECT DATABASE(); // current database SELECT USER(); // root@localhost

6. Control Function

IF function SELECT IF(10<5,'大','小'); //Usage of small case function 1: case is followed by a parameter, indicating a certain situation case field or expression to be judged when constant 1 then value to be displayed 1 or statement 1;
when constant 2 then value 2 or statement 2 to be displayed;

SELECT salary original salary, department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS newSalary FROM employees;

Use of case function 2: case without parameters indicates a fuzzy interval SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS salarylevel FROM employees;

This is the end of this article about the common functions of MySQL basics. For more related common MySQL functions, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: