mysql basic learning - common functions

1, Single line function

1. Character function

1.1 length gets the number of bytes of the parameter value

SELECT LENGTH('john');          --'4'
SELECT LENGTH('Huo Yuanjia lalala');   --'15'

1.2 concat splice string

SELECT CONCAT(last_name,'_',first_name) full name FROM employees;

1.3 # upper and lower change the string to uppercase or lowercase

SELECT UPPER('john');
SELECT LOWER('JOHN');

1.4 substr and substring intercept string

Note: the index starts with 1

--Intercepts all characters following the specified index
SELECT SUBSTR('yangguo xiaolongnv ',4) out_put;    --'little dragon maiden'

--Intercepts the character with the specified length from the specified index
SELECT SUBSTR('yangguo xiaolongnv ',1,2) out_put;  --'Guo Yang'
--Case: the first character in the name is capitalized, the other characters are lowercase, and then_Splice, show

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))  out_put FROM employees;

1.5. instr returns the index of the first occurrence of the substring. If it cannot be found, it returns 0

SELECT INSTR('Yang Bu regretted falling in love with Yin Liuxia','Yin Baxia') AS out_put;  --'0'

 1.6 trim

--Remove front and back spaces
SELECT LENGTH(TRIM('    Zhang Cuishan    ')) AS out_put;   --'9'
--Before and after removal a
SELECT TRIM('a' FROM 'aaaaaaaaa Zhang aaaaaaaaaaaa Cuishan aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')  AS out_put;  --'Zhang aaaaaaaaaaaa Cuishan'

1.7 lpad fills the specified length with the specified characters

SELECT LPAD('Yin Susu',12,'*') AS out_put;  --'*********Yin Susu'

SELECT LPAD('Yin Susu',2,'*') AS out_put;  --'Yin Su'

1.8 # rpad fills the specified length with the specified character

SELECT RPAD('Yin Susu',12,'ab') AS out_put;  --'Yin Susu ababababa'

1.9 replace

SELECT REPLACE('Zhou Zhiruo Zhou Zhiruo Zhou Zhiruo Zhang Wuji falls in love with Zhou Zhiruo','Zhou Zhiruo','Zhao Min') AS out_put;  --'Zhao Min, Zhao Min, Zhao Min, Zhang Wuji fell in love with Zhao min'

2. Mathematical function

2.1} round

SELECT ROUND(-1.55);    --'-2'
SELECT ROUND(1.567,2);  --'1.57'

2.2 # ceil rounds up and returns > = the smallest integer of the parameter

SELECT CEIL(-1.02);  --'-1'

2.3 "floor" is rounded down and returns < = the maximum integer of the parameter

SELECT FLOOR(-9.99);  --'-10'

2.4 truncate

SELECT TRUNCATE(1.69999,1);  --'1.6'

2.5 mod remainder

SELECT MOD(10,-3);  --'1'
SELECT 10%3;        --'1'

 

3. Date function

3.1 # now returns the current system date + time

SELECT NOW();  --'2021-06-18 15:23:47'

3.2 # current date returns the current system date, excluding time

SELECT CURDATE();  --'2021-06-18'

3.3 curtime returns the current time, excluding the date

SELECT CURTIME();  --'15:25:03'

3.4 ￠ you can get the specified part, such as year, month, day, hour, minute and second

SELECT YEAR(NOW()) year;
SELECT YEAR('1998-1-1') year;

SELECT  YEAR(hiredate) year FROM employees;

SELECT MONTH(NOW()) month;
SELECT MONTHNAME(NOW()) month;

3.5 str_to_date converts characters into dates in the specified format

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;

--The entry date is 1992-4-3 Employee information
SELECT * FROM employees WHERE hiredate = '1992-4-3';

SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

3.6 date_format converts the date to characters

SELECT DATE_FORMAT(NOW(),'%y year%m month%d day') AS out_put;  --'21 June 18'


--Query the employee name and employment date with bonus(xx month/xx day xx year)
SELECT last_name,DATE_FORMAT(hiredate,'%m month/%d day %y year') Entry date
FROM employees
WHERE commission_pct IS NOT NULL;

4. Other functions

SELECT VERSION();   --Version number
SELECT DATABASE();  --Current library
SELECT USER();      --Current user

5. Process control function

5.1} if function: effect of if else

SELECT IF(10<5,'large','Small');  --'Small'

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'No bonus, hehe','Bonus, hee hee') remarks
FROM employees;

5.2. Use of case function I: effect of switch case

/*
java in
switch((variable or expression){
    case Constant 1: statement 1;break;
    ...
    default:Statement n;break;


}

mysql in
case Field or expression to judge
when Constant 1 then the value 1 or statement 1 to be displayed; (statement ends with ';')
when Constant 2 then the value 2 or statement 2 to be displayed;
...
else The value n or statement n to be displayed;
end
*/
/*Case: query employee's salary and requirements

Department number = 30, the displayed salary is 1.1 times
 Department number = 40, the displayed salary is 1.2 times
 Department number = 50, the displayed salary is 1.3 times
 For other departments, the displayed salary is the original salary

*/

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 New salary
FROM employees;

5.3 use of case function 2: similar to multiple if

/*
java Medium:
if(Condition 1){
	Statement 1;
}else if(Condition 2){
	Statement 2;
}
...
else{
	Statement n;
}

mysql Medium:

case 
when Condition 1 then the value 1 or statement 1 to display
when Condition 2 then the value 2 or statement 2 to be displayed
. . . 
else Value n or statement n to display
end
*/
/* Case: query the salary of employees
 If salary > 20000, level A is displayed
 If salary > 15000, level B is displayed
 If salary > 10000, level C is displayed
 Otherwise, level D is displayed
*/

SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS Wage scale
FROM employees;

2, Grouping function

  • Function: used for statistics, also known as aggregate function or statistical function or group function
  • Classification: sum, avg average, Max max, min Min, count
  • characteristic:
  1. sum and avg are generally used to deal with numerical type; max, min, count can handle any type
  2. The above grouping functions ignore null values
  3. It can be combined with distinct to realize the operation of de duplication
  4. The count function is introduced separately. Generally, count(*) is used to count the number of rows
  5. The fields queried together with the grouping function are required to be the fields after group by

1. Simple use

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) and,AVG(salary) average,MAX(salary) highest,MIN(salary) minimum,COUNT(salary) number FROM employees;

SELECT SUM(salary) and,ROUND(AVG(salary),2) average,MAX(salary) highest,MIN(salary) minimum,COUNT(salary) number FROM employees;

2. What types of parameters are supported

SELECT SUM(last_name) ,AVG(last_name) FROM employees;  --X
SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;    --X

SELECT MAX(last_name),MIN(last_name) FROM employees;   --'Zlotkey', 'Abel'

SELECT MAX(hiredate),MIN(hiredate) FROM employees;     --'2016-03-03 00:00:00', '1992-04-03 00:00:00'

SELECT COUNT(commission_pct) FROM employees;           --'35'

SELECT COUNT(last_name) FROM employees;                --'107'

3. Ignore null

SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;   --'7.80', '0.222857', '0.222857', '0.072897'

SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;   --'0.40', '0.10'

SELECT COUNT(commission_pct) FROM employees;   --'35'

4. Match with distinct

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;      --'397900.00', '691400.00'

SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;  --'57', '107'

5. Detailed introduction of count function

SELECT COUNT(salary) FROM employees;  --'107'

SELECT COUNT(*) FROM employees;       --'107'

SELECT COUNT(1) FROM employees;       --'107'

Efficiency:

  • Under MYISAM storage engine, COUNT(*) is highly efficient
  • Under the INNODB storage engine, the efficiency of COUNT(*) is similar to that of COUNT(1), which is higher than that of count (field)

6. There are restrictions on the fields queried with the grouping function

SELECT AVG(salary),employee_id  FROM employees;  --employee_id meaningless

 

 

Keywords: MySQL Big Data

Added by shawnplr on Sat, 29 Jan 2022 21:03:17 +0200

Popular Keywords

©2024 Programming VIP