# preface

I studied for three hours this afternoon and didn't finish reading the complete content of the next day, but I feel a lot of knowledge. Put it on the blog. It may increase or open a new post tomorrow.

# text

## 1. Sort query

*Syntax:
select                        (3)
Query list
from                        (1)
surface
where                         (2)
condition
order by sorted field | expression | function | alias [asc|desc]                ( 4)

Execution sequence: as above.

*Features:

1.asc represents ascending order and desc represents descending order.

2. Single field, multiple fields, expressions, functions and aliases can be supported in the order by clause

3. The word order by is usually placed at the end of the query statement, except the word limit.

*Note:
* If there are multiple sorting conditions, the second condition will be judged only when the previous condition values are the same.

Case:

```#Case 1: query employee information and require the salary to be sorted from high to low
SELECT * FROM employees  ORDER BY salary DESC;

#Case 2: query the employee information with department No. > = 90, and sort by employment time [add filter criteria]
SELECT * FROM employees WHERE department_id >=90 ORDER BY hiredate;

#Case 3: display employee information and annual salary by annual salary level [sort by expression] (can be by alias)

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) Annual salary FROM employees ORDER BY Annual salary;

#Case 4: display employee name and salary by name length [sort by function]
SELECT last_name ,salary FROM employees ORDER BY LENGTH(last_name) DESC;

#Case 5: when querying employee information, it is required to sort by salary in descending order and then by employee number in ascending order [sort by multiple fields]
SELECT * FROM employees ORDER BY salary DESC ,employee_id ASC;

```

## 2. Common functions

Function: similar to java method, it encapsulates a group of logical statements in the method body and exposes the method name.
Benefits: 1. Hide the implementation details.     2. Improve code reusability.
Call: SELECT function name (argument list)   [ FROM table];
characteristic:
① What is it called (function name)   ②   What to do (function)
Classification:
1, Single line function
Such as concat, length, ifnull, etc

Single line function classification:
Character function
Mathematical function
Date function
Other functions [ supplement ]
Process control function [ supplement ]

2, Grouping function
Function: used for statistics, also known as statistical function and aggregate function

1. Character function
concat: splicing
substr: intercept substring
upper: convert to uppercase
lower: convert to lowercase
trim: remove the specified spaces and characters before and after
ltrim: go to the left space
rtrim: go to the right space
Replace: replace
instr: returns the index of the first occurrence of a substring
length: get the number of bytes

Case:

```#length gets the number of bytes of the parameter value

SELECT LENGTH('john');
SELECT LENGTH('Zhang Sanfeng hahaha');

SHOW VARIABLES LIKE '%char%';

#2.CONCAT(str1,str2,...) splice string

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

#3.upper, lower;
SELECT UPPER('john');
SELECT LOWER('WQas');

#Example: change the last name to uppercase, the first name to lowercase, and then splice

SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) FROM employees;

#4.SUBSTR(str FROM pos FOR len) ,subString
#  The str string is intercepted from pos to len (character length)
#Note: the index starts at 1

SELECT SUBSTR('Li Mochou fell in love with Lu Zhanyuan',4) out_put;

#Intercept the str string from pos until len (character length) intercepts five characters
SELECT SUBSTR('Li Mochou fell in love with Lu Zhanyuan',4,5) out_put;

#Case: the first character in the name is capitalized, the other characters are lowercase, and then spliced with an underline
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) FROM employees;

#5.INSTR(str,substr)
#Used to return the first index of the substring in the string
SELECT INSTR("Yang Bu regretted falling in love with Yin Liuxia",'Yin Liuxia');

#6.TRIM([remstr FROM] str)

#Remove the characters at the beginning and end of the string
SELECT LTRIM('    Zhang Cuishan    ');

SELECT TRIM('a' FROM 'aaaaaaaa Zhang aaaaaaaaaaaaa Emerald aaaaa mountain aaa');

#7. lpad uses the specified characters to fill the specified length left. The final length is the same as that of the parameter

#9. REPLACE

SELECT REPLACE('Zhang Wuji fell in love with Zhou Zhiruo','Zhou Zhiruo','Zhao Min');
```

-------------------------------------------------------------------------
2. Mathematical function
Round: round
rand: random number
floor: round down
ceil: round up
mod: residual
Truncate: truncate

```#round
#Round the absolute value and take the sign
SELECT ROUND(-1.55);

#Keep two digits after the decimal point
SELECT ROUND(1.567,2);

#ceil rounding up returns > = the smallest integer of the parameter
SELECT CEIL(1.002);

SELECT CEIL(-1.002);

#FLOOR(X) rounds down and returns < = the maximum integer of the parameter
SELECT FLOOR(-9.99);

#TRUNCATE whatever comes after truncation

SELECT TRUNCATE(1.65,1);

#mod remainder

SELECT MOD(-10,-3);

SELECT 10%3;
```

-------------------------------------------------------------------------
3. Date function
now: current system date + time
Current date: current system date
curtime: current system time
str_to_date: converts characters to dates
date_format: convert date to character

```#now returns the current system date + time

#STR_TO_DATE(str,format): converts characters in date format to a date in the specified format

#DATE_FORMAT(date,format) converts the date into a string
SELECT NOW();

#Current date returns the current system date, excluding time

SELECT CURDATE();

#curtime returns the current time, excluding the date
SELECT CURTIME();

#Can get the specified part. Year, month, day, hour, minute, second
SELECT YEAR(NOW());
SELECT YEAR(hiredate) FROM employees;

SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
SELECT MINUTE(NOW());

#STR_TO_DATE(str,format) converts characters into dates in a specified format
SELECT STR_TO_DATE('2000-02-6','%Y-%c-%d');

#Query the employee information whose employment date is April 3, 1992

SELECT * FROM employees WHERE hiredate ='1992-4-3';

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

#DATE_FORMAT(date,format) converts a date into characters
SELECT DATE_FORMAT(NOW(),'%y-%m-%d');

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

-------------------------------------------------------------------------
4. Process control function
if handle double branch
case statements handle multiple branches
Case 1: Processing equivalence judgment
Case 2: judgment of processing conditions

```#1.if function: effect of if else
SELECT IF(10<5,'large','Small');

SELECT  last_name ,commission_pct ,IF(commission_pct is NULL,'No bonus, hehe','There is a bonus. Hee hee')   FROM employees;

#Use of case function I: effect of switch case

/*

mysql in
case Field or expression to judge
WHEN Constant - value 1 or statement 1 to be displayed by THEN;
WHEN Constant 2: value 2 or statement 2 to be displayed by THEN;
....
ELSE The value n or statement n to be realistic;
end;
*/

/*Case: query employee's salary. Requirements:
Department number = 30, the displayed salary is 1.1 times
Department number = 40. The displayed salary is 1.2 times
The department number is 50 and the actual salary is 1.3 times
For other departments, the displayed salary is the original salary
*/
SELECT  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;

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

/*3.case Use of function 2: similar to multiple if
case
WHEN Condition 1 THEN value 1 or statement 1 to display
WHEN Condition 2 THEN value 2 or statement 2 to be realistic
...
ELSE Value n or statement n to display
END
*/

#Case: query employee's salary

/*If salary > 20000, level A is displayed
>15000,B
>10000,C
Otherwise, D

*/

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

-------------------------------------------------------------------------
5. Other functions
Version: version
Database: current database
User: currently connected user

```#Version number
SELECT VERSION();

#View the database currently in use
SELECT DATABASE();

#View current user
SELECT USER();
```

Tags: Database SQL

Posted on Thu, 02 Sep 2021 22:17:48 -0400 by rotto