Database Operations--query (cont.)

1, the commonly used query conditions:
(1) Comparison =,>,<,>=,<=,!= (not equal to),<> (not equal to),!> (not greater than),!<;not+ above comparison operator
(2) Scope of determination Between and, not between and
(3) Determining the Set In, not in
(4) Character matching Like, isn't like
(5) Null value Is null, was NOT NULL
(6) Multiple conditions (logical operations) and, or, not
2. Aggregation function
COUNT ([DISTINCT | all] *)/* Statistics tuple number */
COUNT ([distinct | all] < column name >)/* Count the number of values in a column */
SUM ([distinct | all] < column name >) /* Calculates the sum of a column of values (this column must be numeric) */
AVG ([DISTINCT | all] < column name >) /* Calculate a column worth averaging (this column must be numeric) */
Max ([distinct | all] < column name >) /* Find the maximum value in a column */
MIN ([distinct | all] < column name >) /* Find the lowest value in a column */
If you specify a distinct phrase, it indicates that duplicate values in the specified column are canceled at the time of calculation
If you specify the all phrase, it means that duplicate values are not canceled. All is the default value
3. Connection Query

(1) equivalent and non-equivalent connection query

Format is:[< table name 1>.] < column name 1>< comparison operator >[< table name 2>.] < column name 2>
Among the comparison operators are: =, >, <, >=, <=,! = (or <>), etc.
The link predicate can also be used 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 an equivalent connection, and the other operator is called a non-equivalent connection
(2) Self-connection
(3) Outer join: The discarded tuple is also saved in the result relationship, and the blank value (NULL) on other attributes, then this connection is called outer join
(4) Compound conditional join: There are multiple join conditions in the WHERE clause, known as compliant connections
In addition to connecting two tables, a table can be connected to itself, or more than two tables, which are often referred to as multi-table joins.
4. Nested query: A subquery can also nest other subqueries
Note: The ORDER BY clause cannot be used in a SELECT statement for a subquery, and the GROUP BY clause cannot sort the results of the final query
(1) subquery with in predicate
(2) Subqueries with operators
(3) Sub-query with any (some) or all
(4) Sub-query with exists

Database Operations--query (cont.)