MySQL example to explain single-row functions and character math date process control

SELECT UPPER('Hello');

SELECT LOWER('Hello');

SELECT LENGTH('Chloroplasts do not forget to breathe aaaa');

SELECT CONCAT('Hello','世界') AS result;

#Start intercepting from the 4th one, intercept all the ones after that SELECT SUBSTR('I believe in light',4);

#Start from the first one and intercept 3 SELECT SUBSTR('I believe in light',1,3);

SELECT INSTR('Chinese national football team will play away against Saudi Arabia on October 13','Saudi Arabia') AS result;

SELECT TRIM('a' FROM 'aaaaachloroplastaaaaa') AS result;

#Remove spaces SELECT TRIM(' Chloroplast aa ') AS result;

SELECT LPAD('chloroplast',9,'a') AS result;

SELECT REPLACE('a叶aaa chloroplastaaaa','a','B') AS result;

#One decimal place is retained by defaultSELECT ROUND(1.62) AS result;

#Keep two decimal placesSELECT ROUND(1.627,2) AS result;

SELECT CEIL(1.002) AS result;

SELECT FLOOR(1.002) AS result;

SELECT TRUNCATE(1.699,1) AS result;

SELECT MOD(10,-3) AS result;

SELECT NOW();

SELECT CURDATE();

SELECT CURTIME();

SELECT YEAR(NOW());

SELECT YEAR('2021-09-30') AS result;

SELECT MONTH('2021-09-30') AS result;

SELECT MONTHNAME('2021-09-30') AS result;

#It is equivalent to parsing: the two parameter formats must match SELECT STR_TO_DATE('9-30 2021','%m-%d %Y') AS result;

#It is equivalent to formatting SELECT DATE_FORMAT(NOW(),'%Y year %m month %d day') AS result;

#Check the version SELECT VERSION();
#View the database SELECT DATABASE();
#View userSELECT USER();

SELECT IF(10>5,'big','small') AS result;

CASE WHEN constant to be judged 1 THEN statement 1; or the value to be displayed 1
...
ELSE statement; or the value to be displayed END

#The example is the value to be displayed, without ';'
SELECT `last_name`,`salary`,`department_id`,
CASE `department_id`
WHEN 100 THEN `salary`*(1+0.8)
WHEN 90 THEN `salary`*(1+0.6)
WHEN 80 THEN `salary`*(1+0.4)
WHEN 70 THEN `salary`*(1+0.2)
ELSE `salary`
END AS finalsalaryFROM employees;

CASE 
WHEN condition 1 THEN SELECT statement 1; or the value to be displayed 1
...
ELSE statement; or the value to be displayed END

#The example is the value to be displayed, without ';'
SELECT `last_name`,`salary`,
CASE
WHEN `salary`>20000 THEN 'A level'
WHEN `salary`>15000 THEN 'B level'
WHEN `salary`>10000 THEN 'C level'
ELSE 'D level'
END AS level FROM employees;