Common functions of MySQL basics

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

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

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

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

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

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;

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

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;