Common functions
Advanced 4. Common functions (single line functions)
function
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.
- Custom method (function)
- 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');
SELECT LENGTH('HELLO,Zhang San'); #In UTF-8, a Chinese character has 3 bytes
3 CHAR_LENGTH gets the number of characters
SELECT CHAR_LENGTH('HELLO');
SELECT CHAR_LENGTH('HELLO,Zhang San');
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
SELECT TRIM('X'FROM 'XXXXX Zhang XXXXX three XXXXXXXX') AS A; # Zhang XXXXX III
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
case
Query the employee's name and send it to first_name initial capital, other lowercase, last_name is capitalized and alias' OUTPUT '
SELECT CONCAT(UPPER (SUBSTR(first_name,1,1)), LOWER (SUBSTR(first_name,2)),'_',UPPER (SUBSTR(last_name,1))) AS 'OUTPUT' 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
SELECT ABS(-2.3);
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
SELECT NOW();
2 CURDATE current date
SELECT CURDATE();
3 CURTIME current time
SELECT CURTIME();
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;