Database Operations-query (continued)

1. Common query conditions: (1) comparison ,,,,,! (Not equal to), (not equal ),! (Not greater ),!; Not comparison operator (2) determines the range betweenand, notbetweenand (3) determines the set in, notin (4) matches like, notlike (5) null isnull, isnotnull (6) multiple conditions (

1. Common query conditions: (1) Comparison = ,! = (Not equal to), (not equal ),! (Not greater ),!; Not the comparison operator (2) determines the range between and, not between and (3) determines the set in, not in (4) character match like, not like (5) null is null, is not null (6) multiple conditions (

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] <列名> )/* Count the number of values in a column */
Sum ([distinct | all] <列名> )/* Calculate the sum of values in a column (this column must be numeric )*/
Avg ([distinct | all] <列名> )/* Calculate the average value of a column (this column must be numeric )*/
Max ([distinct | all] <列名> )/* Calculate the maximum value in a column */
Min ([distinct | all] <列名> )/* Calculate 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 :[ <表名1> .] <列名1> <比较运算符> [ <表名2> .] <列名2>
The comparison operators include =,>, <,> =, <=, and ,! = (Or <>)
In addition, the link predicates can also be in the following form:
[ <表名1> .] <列名1> <比较运算符> [ <表名2> .] <列名2> And [ <表名2> .] <列名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