1, distinct

1) Function

Remove duplicate items

2) Grammar

select distinct Column name  from  Table name;

• give an example

Before use:(Here you can see that 3 repeat)
select client ID from Orders;

After use:(It can be seen here that only once 3)
select distinct customer ID from Orders;

3) Shortcomings

When two columns are selected, it is possible that the first column has no duplicate removal, but the timestamp of the second column is unique

2, Usage of top

1) Function

Get the row data of the top few or the top percentage

2) Grammar

select top The name of the numeric column  
from  Name of the table;
or
select top Name of percentage column  
from  Name of the table;

3) Use examples:

1)Get the first 3 items of the table
select top 3 * 
from Customers;
2)After getting the table, 40%Record of(The last expression is in reverse order)
select top 40 Percent * 
from Customers order by customer ID  desc;
3)Get random 40%Record of
select top 40 Percent * 
from Customers 
order by customer ID NEWID();

3, And and or

• Use examples:

1)and give an example, or It's the same
select * from Customers 
where full name='Zhang San' and city='Beijing'
2)and and or Combined use(The conditions are separated by parentheses)
select * from Orders 
where (client ID=3 OR customer ID=1) AND deliver goods ID=4

4, group by

1) Function

Combined with aggregation function, the result set is grouped according to one or more columns

2) Grammar

select The name of the column,
Aggregate function (column name)
FROM  Table name
WHERE  Column name operator value
GROUP BY Column name

3) Use examples:

Query all city data

1)How many customers live in each city?
SELECT city,
COUNT(*) AS Number of customers
FROM Customers
GROUP BY city
2)Largest customers in different cities ID How much is it?
SELECT city,
MAX(List names, such as customers ID)  AS Largest customer ID
FROM Customers
GROUP BY city

1) Count the number of customers living in each city

2) Check the largest customer ID in different cities

5, having

1) Function

It is mainly used to further filter the grouped data and find records that meet the grouping conditions
(having is generally used in combination with group by. It is usually used in front of the aggregation function to filter the grouped data, (MAX, MIN, COUNT, SUM, etc.)

2) Syntax (Order: after group by, before order by)

SELECT column1,column2
FROM table1,table2
WHERE [conditions]
GROUP BY column1,column2
HAVING [conditions]
ORDER BY column1,column2

3) Use examples:

1)Query all records with city population greater than or equal to 2
SELECT city,COUNT(*) Number  FROM Customers
GROUP BY city
HAVING COUNT(1) >1
2)--Statistics of departments with more than 300 employees
select 
  deptName, 
  count(deptName) 
from 
  user 
having 
  count(deptName) > 300

6, order by

1) Function

Sort results

2) Grammar

SELECT Column name FROM Table name
ORDER BY Column name ASC|DESC

3) Use examples:

1)Ascending order
SELECT * FROM Customers
ORDER BY province ASC
2)Descending order
SELECT * FROM Customers
ORDER BY province DESC
3)Mixed ascending and descending
SELECT * FROM Customers
ORDER BY province ASC, full name DESC

1) Ascending order

2) Descending order

7, LIKE

1) Function

In the WHERE sentence, it is used to specify a pattern to search for data, usually called fuzzy search

2) Grammar

SELECT column1,column2,...
FROM table_name 
WHERE columnN LIKE pattern;

Two common wildcards and LIKE Operators together
% : The percent sign indicates zero, one or more characters
_ : The underline represents a single character

• Usage of LIKE operator

WHERE address LIKE 'a%'  Find to a Any value at the beginning
WHERE address LIKE '%a'  Find to a Any value at the end
WHERE address LIKE '%or%'  Find anywhere containing or Value of
WHERE address LIKE '_r%'  In the second location, find any r Value of
WHERE address LIKE 'a_%_%' Find to a A value that begins with and is 3 characters long
WHERE address LIKE 'a%o'  Found with a Begin with o End value

8, IN

1) Function

It is allowed to specify multiple values in the WHERE clause, which can be understood as the abbreviation of the OR condition

2) Grammar

1)Grammar I
SELECT column_name(s) FROM table_name
WHERE column_name IN (value1,value2,...);
2)Grammar II
SELECT column_name(s) FROM table_name
WHERE column_name IN (SELECT STATEMENT);

3) Examples of use

1)Find provinces in'Shanghai'，'Beijing'，'Guangdong Province'All users of
SELECT * FROM Customers
WHERE province IN ('Shanghai' , 'Beijing' ,'Guangdong Province');
2)Find not in'Zhejiang Province'，'Shanghai'or'Beijing'All customers in
SELECT * FROM Customers
WHERE province NOT IN ('Zhejiang Province','Shanghai','Beijing');
3)Find suppliers'Suppliers'Customer information from the same city as the customer
SELECT * FROM Customers
WHERE city IN (SELECT city FROM Suppliers);

9, BETWEEN AND

1) Function

Find all data between two values, including start value and end value, which is equivalent to > = and<=

2) Grammar

SELECT column_name(s) FROM table_name
WHERE column_name BETWEEN value1 AND value2;

3) For example:

1)Find items with prices between 30 and 60
SELECT * FROM Products
WHERE Price BETWEEN 30 AND 60;
2)Find items with prices between 30 and 60
SELECT * FROM Products
WHERE Price NOT BETWEEN 30 AND 60;
3)Date boundary problem(Find order date,Remember, the default date is 00:00:00)
SELECT * FROM Orders
WHERE Order date: BETWEEN '2018-06-28' AND '2018-09-28';

10, UNION

1) Function

Combine the result sets of two or more SELECT statements

2) Premise of use

1) Each select statement in UNION must have the same number of columns
2) These columns must also have similar data types
3) The columns in each SELECT statement must also be arranged in the same order

3) Grammar

SELECT column_name(s) FROM table1
UNION (ALL)
SELECT column_name(s) FROM table2;

4) Use examples:

• Data source (two tables)
  
  

1)Select two different cities (only different values)
SELECT city FROM customers_bak
UNION   #De duplication of the two results
SELECT city FROM Suppliers
2)from customers_bak and Suppliers Select all cities in (exclude all values, including duplicate values)
SELCET city FROM customers_bak
UNION ALL  #The two results are not duplicated, and they are spliced together
SELECT city FROM Suppliers;

1)

2)

11, JOIN

1) Function

• concept
  join is used to combine rows from two or more tables and make an association based on the common fields of these tables
• Common join types
  1) INNER JOIN
  2) LEFT JOIN
  3) RIGHT JOIN right link
  4)FULL JOIN
  5)CROSS JOIN

2) Grammar

• Internal connection

SELECT A.Num_a,B.Num_B
FROM A   #Table A is inline with table B, and the INNER here can be omitted
INNER JOIN B
ON A.Num_A = B.Num_B  #ON indicates that this column is used for association

• Left link (first show all the left tables, and then match the similar values on the right)

SELECT A.Num_a,B.Num_B
FROM A  #The coordinates will be displayed, and NULL will be displayed if there is no match
LEFT JOIN B
ON A.Num_A = B.Num_B;

• Full join (both tables are displayed, and null is displayed if there is no match)

SELECT A.Num_a,B.Num_B
FROM A 
FULL JOIN B
ON A.Num_A = B.Num_B;

• Cross join (product matching)

SELECT A.Num_a,B.Num_B
FROM A 
CROSS JOIN B;

• explain
  Each parameter of table A matches each parameter of table B

3) Use examples:

12, SELECT INTO

1) Function

2) Examples:

13, CREATE

14, VIEW usage

15, Usage of constraints

16, SQL execution order

• Sequential ranking

1)SELECT 
2)
3)
4)
5)
6)
7)
8)
9)
10)
11)