introduction

Mainly record your own writing sql Use unskilled or some forgotten knowledge points when speaking , It is used to deepen your study and facilitate your review .

Query statement

1. Data De duplication

keyword ：distinct
usage ： When there is duplicate data in the queried field , have access to distinct Deduplication ;

2. Restrict result set

keyword :limit( Parameters 1, Parameters 2) （ Parameter must be an integer ）
usage :

• If only given 1 Parameters , Indicates the maximum number of record lines returned ;

SELECT * FROM table LIMIT 5 // Before retrieval 5 Data 

• If given 2 Parameters , The first parameter refers to the offset of the return record line , The second specifies the maximum number of return record rows ;

SELECT * FROM table LIMIT 5,5 //  Back to page 6 To the first 10 Data 

• If the second parameter is -1, Indicates from the record line of the offset to the end ;

SELECT * FROM table LIMIT 5,-1 // Back to page 6 Data to the end 

3. Filter query - Query criteria

keyword : where
usage ： Use it in combination with query criteria , Here's the picture ：

4. Aggregate functions

1. MAX: Returns the maximum value of the specified data ;
2. COUNT： Returns the number of data in the specified group ;
3. AVG: Returns the average value in the specified group , Null values are ignored ;

5.GROUP BY

Group according to one or more columns
GROUP BY + HAVING
add HAVING Then it mainly filters the data after grouping ;

6.ORDER BY

ORDER BY col ASC: Ascending
ORDER BY col DESC: Descending

7. League table query

1. INNER JOIN( Query the common part of two tables ）：
   table1 INNER JOIN table2 ON table1.xx = table2.xx;
2. LEFT JOIN( Based on the left table ）
3. RIGHT JOIN（ On the right side ）

8.UNION

usage ： Merge multiple SELECT The result set of the query statement （ The results were de duplicated ）
UNION ALL（ The result is no weight loss ）

9.CASE

• Simple CASE

CASE  Test expression 
WHEN  Simple expressions 1 THEN  The result expression 1
WHEN  Simple expressions 2 THEN  The result expression 2 …
WHEN  Simple expressions n THEN  The result expression n
[ ELSE  The result expression n+1 ]
END

example ： Query the class number of students in the class table 、 Class name 、 Department number and head teacher number , And deal with the serial number as follows ：
When the department number is 1 when , Show “ Department of Computer Science ”;
When the department number is 2 when , Show “ Department of software engineering ”;
When the department number is 3 when , Show “ Department of Internet of things ”.

SELECT  Class number , Class name ,
CASE  Department number 
WHEN 1 THEN ' Department of Computer Science '
WHEN 2 THEN ' Department of software engineering '
WHEN 3 THEN ' Department of Internet of things '
END AS  Department number , Head teacher No 
FROM  Class table 

• Search for CASE

CASE
WHEN  Boolean expression 1 THEN  The result expression 1
WHEN  Boolean expression 2 THEN  The result expression 2 …
WHEN  Boolean expression n THEN  The result expression n
[ ELSE  The result expression n+1 ]
END

The above example uses search CASE have to ,

SELECT  Class number , Class name ,
CASE 
WHEN  Department number =1 THEN ' Department of Computer Science '
WHEN  Department number =2 THEN ' Department of software engineering '
WHEN  Department number =3 THEN ' Department of Internet of things '
END AS  Department number , Head teacher No 
FROM  Class table 

==== 7.19 The number is more ======================================

Date function

1. DAY() / DAYOFMONTH()
   Return to the day which is the day of the month （1-31）
2. MONTH()
   Return the month of the date parameter
3. YEAR()
   Returns the year in the date parameter

String interception

1. substring_index
   usage ：
   for example ：str = www.wikibt.com

• substring_index(str,‘.’,1)
  result ：www
• substring_index(str,‘.’,2)
  result :www.wikibt
• substring_index(str,‘.’,-2)
  result :wikibt.com
• substring_index(substring_index(str,‘.’,-2),‘.’,‘1’)
  result :wikibt

usage ：
if count Is an integer , Then count from left to right , The first count Separators , All contents on the left ;
if count It's a negative number , Then count from right to left , The first count Separators , Everything on the right ;

Continuous updates are not finished