Basic knowledge of SQL

Four basic grammars
1. Insert into table name (column name) [Values] Value list
Insert into table name values list
"Extend" Inserts multiple lines:
1. INSERT into < table name > (column name)
Select Column Name
From < source table name >

2. Select Column Name
into < table name >
From < source table name >
Note To create the table, copy the structure of the column name and the data into the new table, but this SQL statement can only be used once

3. INSERT into < table name > (column name)
Select Value List Union
Select Value List

2. Delete [from] table name [WHERE condition]

3. Update table name set column name = value, column name 2= value 2 [Where condition]

4. Select Column name from table name [where condition]

Multi-Table query:
Internal connection (inner join):
Select Table 1. column name, table 2. Column name from table name 1 as table 1 inner JOIN table name 2 as table 2
On table 1. Field name = Table 2. Field Name
Inner JOIN table name 3 as table 3
On table 1. Field name = Table 3. Field Name

You can also use another notation

Select Table 1. column name, table 2. Column name from table name 1 as table 1, table name 2 as table 2, table name 3 as table 3

　　　　where table 1. field name = Table 2. Field Name

Table 1. Field name = Table 3. Field Name

External connection:
Left outer:
Select Table 1. column name, table 2. Column name from table name 1 as
Table 1 LEFT OUTER join table name 2 as table 2
On table 1. Field name = Table 2. Field Name
Right outside:
Select Table 1. column name, table 2. Column name from table name 1 as
Table 1 right outer join table name 2 as table 2
On table 1. Field name = Table 2. Field Name

Other knowledge points

1. Three ways of Fuzzy query
First type: Select Column name from table name where column name like ' expression '
/*
Common wildcard characters are listed in the following four ways:
_: One character
%: Characters of any length
[]: Characters within the interval
[^]: characters within a range
*/
Second type: Select Column name from table name where column name in (value 1, value 2)
Third type: Select Column name from table name where column name between value 1 and value 2

Note: A value of 2 is less than the value of 1

2. Determine if it is empty
Null: Column name is null or column name = ' '
Not empty: Column name is a null and column name! = ' '

3. Grouping (group by...having)
Select Column name (Group By column name, or function) from table name
where [condition]
Group By column name
Having conditions
Order by sort [Asc|desc]

Function

1. Aggregation functions
SUM (column name): and
Max (column name): Maximum value
Min (column name): Minimum value
AVG (column name): Average
Count (column name): Number of columns

The aggregate function is the number, value, or

2. Date: Current date: getdate ()
DateDiff (date part, date 1, date 2): Find the difference in the date part
DATEADD (date part, value added, date specified)
DATEPART (date part, specified date)
Datename (date part, specified date)

3. String functions

A. CHARINDEX (look for the character, search column [, start position]);
Search for characters or strings in a paragraph, return to start position (note: Starting at 1, if no return 0 is found)
e.g select * from Student where charindex (' Gold ', studentname) >0
B. Len (String | column name): Returns the string length
Select Len (' Hello World '); Returns 11
C. Upper (String | column name): Converts the passed value to uppercase
Select Upper (' Hello World '); HELLO World
D. LTrim (String | column name): Go to the left space of the string
RTrim (String | column name): The space to the right of the string
E.g:select ltrim (' Hello World ');
Select RTrim (' Hello World ');
E. Right (string, length):
Returns the specified number of characters from the right side of the string
Left (string, length)
Returns the specified number of characters from the left of the string
F. Replace (characters to be searched, characters to be queried, characters to replace)
Replace a character in a string
E.g Select replace (' Hello World ', ' hello ', ' Hi '); result Hi World

Basic knowledge of SQL