Common database functions

Common functions

Advanced 4. Common functions (single line functions)


Similar to the "method" in java, in order to solve a problem, a series of command sets are encapsulated together, and only the method name is exposed for external calls.

  1. Custom method (function)
  2. Call method (function)

Common functions:

  • Character function
    • concat
    • substr
    • length
    • char_length
    • upper/lower
    • trim
    • left/right
    • lpad/rpad
    • instr
    • strcmp
  • Mathematical function
    • abs
    • ceil
    • mod
    • floor
    • round
    • truncate
  • Date function
    • now
    • str_to_date
    • date_format
    • curdate
    • curtime
    • datediff
  • Process control function

1, Character function (I)

1 CONCAT splice character

SELECT CONCAT('HELLO',',',last_name) remarks FROM employees;

2 LENGTH get byte length

SELECT LENGTH('HELLO,Zhang San');   #In UTF-8, a Chinese character has 3 bytes

3 CHAR_LENGTH gets the number of characters


4 SUBSTRING the selected substring can be abbreviated as SUBSTR

 SUBSTR(str´╝îIntercepted character start index, intercepted character length)
 SQL The index in starts from 1, which is different from 0 in java
SELECT SUBSTR('Outlaw maniac Zhang San',1,4); # Outlaw maniac
SELECT SUBSTR('Outlaw maniac Zhang San',5,2); # Zhang San
SELECT SUBSTR('Outlaw maniac Zhang San',5);   # Zhang San SUBSTR(str, the initial index of the intercepted character) does not write the length, and all the following are intercepted

5 INSTR gets the index when the character first appears

SELECT INSTR('Zhang Sanyi, a fanatic outside the law, finished the excavation. Come on, you lie down and win','Zhang San'); #  5
SELECT INSTR('Zhang Sanyi, a fanatic outside the law, finished the excavation. Come on, you lie down and win','phoenix'); #  15

6 TRIM removes the characters specified before and after the characters, and the default is space

SELECT TRIM('   Zhang    three          ') AS A;           #   Zhang San

7 LPAD/RPAD left fill / right fill

Fill character 2 with the specified character 1 to the required character length

SELECT LPAD('Zhang San',6,'you'); # You Zhang San
SELECT RPAD('Zhang San',6,'you'); # Zhang San, you, you, you

8 UPPER/LOWER uppercase / lowercase


Query the employee's name and send it to first_name initial capital, other lowercase, last_name is capitalized and alias' OUTPUT '

LOWER (SUBSTR(first_name,2)),'_',UPPER (SUBSTR(last_name,1)))
FROM employees ;

9 STRCMP compares the size of two characters

SELECT STRCMP('ABC','AAA'); #The front side is larger than the rear side, which is 1
SELECT STRCMP('ABC','ACA'); #The front edge is smaller than the rear edge, which is - 1
SELECT STRCMP('ABC','ABC'); #The front edge is equal to the back edge, which is 0

10 LEFT/RIGHT intercepts a substring of a specified length from left / right

SELECT LEFT('Outlaw maniac Zhang San',1); # method
SELECT LEFT('Outlaw maniac Zhang San',5); # Outlaw maniac Zhang
SELECT RIGHT('Outlaw maniac Zhang San',5);# Outlaw Zhang San
SELECT RIGHT('Outlaw maniac Zhang San',2);# Zhang San

2, Mathematical function

1 ABS absolute value


2 CEIL rounds up and returns the smallest integer greater than or equal to the parameter

SELECT CEIL(1.28);  # 2
SELECT CEIL(-2.3);  # -2

3 FLOOR rounding down returns the smallest integer less than or equal to the parameter

SELECT FLOOR(1.2); # 1
SELECT FLOOR(-2.34); # -3

4 ROUND. You can choose to keep several decimal places

SELECT ROUND(1.239); # 1
SELECT ROUND(-3.5634235); # -3
SELECT ROUND(-3.5634235,3); # -3.563

5 TRUNCATE specifies to truncate to several decimal places

SELECT TRUNCATE(3.3424,2); # 3.34
SELECT TRUNCATE(3.3424,1); #3.3
SELECT TRUNCATE(3.3424,0); #3

6 MOD remainder

SELECT MOD(-15,4); # -3
SELECT -15%4;      # -3
SELECT -15%-4;     # -3
#  a%b = a-(INT)a/b*b
SELECT MOD(33,3);  # 0

3, Date function

1 NOW current date and time


2 CURDATE current date


3 CURTIME current time


4 days before DateDiff minus days after DateDiff

SELECT DATEDIFF('1999-09-09','2020-08-03');

5 DATE_FORMAT formats the date datetime value in string fmt format

SELECT DATE_FORMAT('1999-09-09','%Y year%m month%d To hold back %H hour%i minute%s second') date of birth;
SELECT DATE_FORMAT(hiredate,'%Y year%m month%d To hold back %H hour%i minute%s second') Entry date
FROM employees;

6 STR_TO_DATE parses a string into a date type in the specified format

SELECT STR_TO_DATE('3/8 1999','%m/%d %Y');# 1999-03-08
SELECT * FROM employees
WHERE hiredate<STR_TO_DATE('3/8 1999','%m/%d %Y');

4, Process control function

The if function is similar to the ternary operator in java

If (expression 1, expression 2, expression 3); If expression 1 holds, then expression 2, otherwise expression 3

SELECT IF(101>70,'GOOD','BAD');SELECT IF(commission_pct IS NOT NULL ,salary*12*commission_pct,'0') bonus FROM employees;

2 CASE function

Case 1: similar to the switch statement, the equivalence judgment can be realized

CASE(expression) WHEN The expression is a value of 1   THEN   Result 1 WHEN The expression is a value of 2   THEN   Result 2 ... ELSE result n END

Case: displays the old salary and new salary of the employee's department number. If the department number is 30, the salary is twice; Department number: 50, salary: 3 times; Department number: 60, salary: 4 times; Otherwise unchanged

SELECT department_id,salary,CASE(department_id)WHEN 30 THEN salary*2WHEN 50 THEN salary*3WHEN 60 THEN salary*4ELSE salaryEND New salary FROM employees;

Case 2: similar to multiple if statements, interval judgment is realized

CASE WHEN Condition 1  THEN Result 1 WHEN Condition 2  THEN Result 2 ... ELSE END

Case: if the salary level is greater than 20000, it is A; if it is greater than 15000, it is B; if it is greater than 10000, it is C; otherwise, it is D

SELECT salary,CASEWHEN salary>20000 THEN 'A'WHEN salary>15000 THEN 'B'WHEN salary>10000 THEN 'C'ELSE 'D'END level FROM employees;

Added by drunkencelt on Tue, 04 Jan 2022 03:47:00 +0200