Database Operations-query (continued), database-Query

Database Operations-query (continued), database-Query
1. Common query conditions:
(1) Comparison =,>, <, >=, <= ,! = (Not equal to), <> (not equal to),!> (Not greater ),! <; Not + comparison operator
(2) determine the range between and, not between and
(3) determine the set in, not in
(4) character matching like, not like
(5) null is null, is not null
(6) multiple conditions (logical operations) and, or, not
2. Aggregate functions
Count ([distinct | all] *)/* count the number of tuples */
Count ([distinct | all] <column Name>)/* count the number of values in a column */
Sum ([distinct | all] <column Name>)/* calculates the sum of values in a column (this column must be numeric )*/
Avg ([distinct | all] <column Name>)/* calculate the average value of a column (this column must be numeric )*/
Max ([distinct | all] <column Name>)/* calculate the maximum value in a column */
Min ([distinct | all] <column Name>)/* calculates the minimum value in a column */
If the distinct phrase is specified, it indicates that duplicate values in the specified column must be canceled during calculation.
If the all phrase is specified, the recurrence value is not canceled. All is the default value.
3. Connection Query

(1) equi and non-equi join query

Format: [<Table Name 1>.] <column name 1> <comparison operator> [<Table name 2>.] <column name 2>
The comparison operators include =,>, <,> =, <=, and ,! = (Or <>)
In addition, the link predicates can also be in the following form:
[<Table Name 1>.] <column name 1> <comparison operator> [<Table name 2>.] <column name 2> and [<Table name 2>.] <column name 3>
When the link operator is =, it is called equijoin, and other operators are called non-equijoin.
(2) Self-connection
(3) Outer Join: stores discarded tuples In the result link, and fills in the blank value (null) on other attributes. Therefore, this connection is called an outer join.
(4) composite condition join: The where clause contains multiple join conditions
In addition to connecting two tables and connecting a table to itself, a join operation can also be connected to more than two tables. The latter is usually called a multi-table join operation.
4. nested query: A subquery can also be nested with other subqueries.
Note: The order by clause cannot be used in the select statement of a subquery, and the group by clause cannot sort the final query results.
(1) subqueries with IN predicates
(2) subqueries with operators
(3) subqueries with any (some) or all
(4) subqueries with exists