SQL (2)-Delete and change 2

Simple query:
First, the projection
SELECT * FROM table name
Select column 1, column 2 ... from table name
Select DISTINCT column name from table name
Second, screening
Select top numeric column |* from table name
(i) Equivalence and non-equivalence
SELECT * FROM table name where column name = value
SELECT * FROM table name where column name! = value
SELECT * FROM table name where column name > value
SELECT * FROM table name where column name < value
SELECT * FROM table name where column name >= value
SELECT * FROM table name where column name <= value

(ii) Multi-criteria and scope
SELECT * FROM table name where Condition 1 and|or condition 2 ...
SELECT * from table name where between ... and ...
SELECT * FROM table name where column in (Value list)

(c) Fuzzy query like% _
SELECT * FROM table name where column like '%_ .... '

Third, sort
SELECT * FROM table name where condition order by column name asc| DESC, column name asc| DESC

Four, group:
Statistical functions (aggregate functions)
Count (), Max (), Min (), SUM (), AVG ()

COUNT () Total rows counted
COUNT (*) to get all the number of rows
Count (column) gets all the non-null numbers in the column.
Select COUNT (*) from car where brand= ' b003 '

Max (column) This column is the largest, min (column) of the column of the smallest
Select min (price) from car

Sum of this column, AVG (column), the average of this column
Select AVG (price) from car

GROUP By ... having ...

1.group by followed by a column name.
2. Once the group by group is used, the select and from middle cannot be used * and can contain only two categories of things: The column name after group by, and the other is the statistic function
Select Oil,avg (Price) from Car GROUP by oil
For columns generated by statistical functions, the default is no column name, and you can specify the column name by using the following method.
Select oil as fuel consumption, count (*) as quantity, AVG (price) average from Car GROUP by oil

Having the following is generally followed by a statistical function. It is used to further filter the data after grouping.

Complex query:
One, connection query
The columns of multiple tables are combined in one interface view.
Thought: 1. Generate Cartesian product. 2. Sift through the Cartesian product. 3. Select the column to display.
Select Table 1. Column 1, table 1. Column 2, table 2. Column 1, table 2. Column 2 ... from table 1, table 2 where table 1. column = table 2. Column

SELECT * FROM table 1
Join table 2 on table 1. column = table 2. Column
Join table 3 on table 2. column = Table 3. Column

Left join (left join), right-join, fully connected (full join)

Second, joint inquiry
The rows of multiple tables are combined in a single interface view.
Combine two queries with a union. The requirement is that the columns of the two queries correspond to each other.

Third, sub-query (nested query)
(i) Unrelated sub-query:
At least two levels of query, the inside of the query and then write query.
The inner query provides the intermediate content of the query for the outer query.

(b) Related sub-query:

SQL (2)-Delete and change 2