04_mysql Common Function _ single-line function

#单行函数细分
1. Character functions
2. Mathematical functions
3. Date function
4. Other functions
5. Process Control function

#单行函数-Character functions
#一, character functions
#1. Length gets the bytes of the parameter
SELECT LENGTH (' John ');
SELECT LENGTH (' Zhang Sanfeng '); #utf-8 encoding, 1 characters 3 bytes

# View the current client's character set
SHOW VARIABLES like '%char% ';

#2. Concat Stitching Characters
SELECT CONCAT (last_name, ' _ ', first_name) from employees;

#3. Upper,lower
SELECT UPPER (' John ');
SELECT LOWER (' JOHN ');

#案例: Capitalize the last name, lowercase the name, and then stitch
Select CONCAT (UPPER (first_name), LOWER (first_name)) as "name" from employees;

#4. SUBSTR, substring (same function)
#4个重载的方法
# Note: The SQL language index starts at 1
# function: Intercepts all characters starting from POS, including POS
SELECT SUBSTR (' Zhangjin ', 6); # Jin

# function: Intercept a substring starting from Pos with a character length of Len
SELECT SUBSTR (' Zhangjin ', 1,5); # Zhang

# case: First letter uppercase in name, other characters lowercase
SELECT CONCAT (UPPER (SUBSTR (last_name,1,1)), LOWER (SUBSTR (last_name,2)))
As "surname"
From employees;

#5. InStr returns the starting index of the first occurrence of a substring, returns 0 if none appears
SELECT INSTR (' Zhangjin ', ' Shay '); # 0
SELECT INSTR (' Zhangjin ', ' Jin '); # 6
SELECT INSTR (' Zhangjinjinjinjinjinjin ', ' Jin '); # 6

#6, Trim
# default is trim left and right space on both sides
SELECT TRIM (' Zhangjin ');

# you can specify the characters to trim
SELECT TRIM (' A ' from ' aaaaaazhangjinaaaa ');

#7, Lpad
#使用指定字符进行左填充, the number of characters after the fill is Len
#len, number of characters, not byte length
SELECT lpad (' Zhang ', 3, ' * '); # * * Zhang

#最终长度由len决定
SELECT Lpad (' Zhang Sanfeng ', 2, ' * '); # Zhang San

#8, Rpad
#使用指定字符进行右填充, the number of characters after the fill is Len
#len, number of characters, not byte length
SELECT rpad (' Zhang ', 3, ' * '); # Zhang * *

#最终长度由len决定
SELECT rpad (' Zhang Sanfeng ', 2, ' * '); # Zhang San

#9, replace
# replace (STR,FROM_STR,TO_STR) all replaced, Replace_all
SELECT REPLACE (' Zhangjin ', ' Zhang ', ' Shay ');
SELECT REPLACE (' Zhangjinzhangjinzhangjin ', ' Zhang ', ' Shay ');

#二, Mathematical functions
#round rounding
SELECT ROUND (1.45);
SELECT ROUND (-1.65);
SELECT ROUND (1.567,2); #保留2位小数

#ceil rounding up, returns the smallest integer greater than or equal to the parameter
SELECT ceil (1.002); #2
SELECT ceil (1.00); #1

#floor rounding down, returns the largest integer less than or equal to the parameter
SELECT floor (1.002); #1

#truncate truncation
SELECT TRUNCATE (1.65,1); #保留1位小数

#mod take the remainder
SELECT MOD (10,3); # 10%3=1
SELECT MOD ( -10,-3); #-1, the divisor is negative, the result is negative

#三, Date function
#now returns the current system date + time
Select Now ();
#curdate returns the current system date, not including the date
Select Curdate ();
#curtime returns the current time, not including the date
Select Curtime ();
#可以获取指定的部分: Years, months, days, hours, minutes, seconds
# Get Year
Select year (now ()) as years;
Select year (' 1986-1-1 ');
Select year (hiredate) from employees;
# Get the Month
Select Month (now ()) as month; # 11
Select MonthName (now ()) as month; # November
# Get the day
Select Day (now ()) as date; # 24
Select Dayname (now ()) as day; # Friday
S
# Date format string, date
# str_to_date (' 24-11-2017 ', '%d-%m-%y ');
Select Str_to_date (' 24-11-2017 ', '%d-%m-%y ');

# Case: Use 4-3 1992 of user input as a query to find the employee who entered the job on April 3, 1992
SELECT * FROM Employees where HireDate = Str_to_date (' 4-3 1992 ', '%c-%d%Y ');

# Date-character
# date_format (' 2018/6/6 ', '%y%m month%d day ')
Select Date_format (' 2018/6/6 ', '%y year%m month%d ');

# Case: Search for Employee name and entry date with bonus (xx month/xx day xx year)
Select Last_name,date_format (hiredate, '%m month/%d day%y Year ') as entry date
From Employees
Where commission_pct is not null;

#四, other functions
# MySQL version query
SELECT VERSION ();
# database queries currently in use
SELECT DATABASE ();
# Query the current user
SELECT USER (); #[email protected]

#五, Process Control functions
#1, if function
# if (EXP1,EXP2,EXP3) EXP1 is true, returns the value of EXP2, otherwise returns the value of EXP3
SELECT last_name, IF (commission_pct is NULL, ' none ', ' have ') from employees;

#2, case control structure use one: the effect of the switch case

/*
Fit: Equivalent judgment
Case control structure in MySQL:
The field or expression to be judged by case
When constant 1 then to display the value 1 or statement 1;
When constant 2 then to display the value 2 or statement 2;
When constant 3 then to display the value 3 or statement 3;
When constant 4 then to display the value 4 or statement 4;
else to display the value n or statement n;
End
*/

/* Case: Query employee's salary, request
Department Number = 30, showing a salary of 1.1 times times
Department Number = 40, showing a salary of 1.2 times times
Department Number = 50, showing a salary of 1.3 times times
Other departments, showing wages as the original wage
*/
SELECT salary,department_id,
Case department_id
When 1.1*salary
When 1.2*salary
When the 1.3*salary
ELSE Salary
END
As "new wage"
From employees;

Use of #3, case control structure two: similar to multiple if

/*
Case control structure in MySQL:
Fit: Interval Judgment (size)

Case
When condition 1 (true,false) to display the value 1 (trailing without semicolon) or statement 1 (trailing plus semicolon)
When Condition 2 (true,false) to display the value 2 (trailing without semicolon) or statement 2 (trailing plus semicolon)
....
else to display the value n (trailing without semicolon) or statement n (trailing plus semicolon)
End
*/

#案例: Check the employee's salary status
/*
Show a level if salary is greater than 20,000
Show B level if salary is greater than 10,005
Show C level if salary is greater than 10,000
Otherwise, the D level is displayed
*/
SELECT salary,
Case
When salary>20000 then ' A '
When salary>15000 then ' B '
When salary>10000 then ' C '
ELSE ' D '
END
As "salary scale"
From employees;

04_mysql Common Function _ single-line function