Getting Started with SQL Server 7.0 (3)

Data sorting
The ORDER BY clause sorts the data in the specified order (ordering data). It requires a list of column names or a list of non-negative integers to specify the column positions. Use ASC to represent ascending order and DESC to represent descending order. The default is ASC.

Limit the number of rows returned
It is possible to limit the number of rows in the result without using a WHERE clause. The "TOP" clause can limit the number of rows by a specified number or percentage.

Data Grouping and Calculation Aggregation Functions
The aggregate function calculates the sum of the data in the table. SQL Server provides the following aggregate functions:
· AVG This function calculates the average. The syntax is as follows:
AVG ([ALL | DISTINCT] exPRession)
The keyword DISTINCT is only used to calculate the average of different values. If there are many duplicate values, these values ​​​​are only calculated once, and the default is ALL.
Expression can be an arithmetic expression involving one or more columns.
· MIN This function finds the minimum value in the supplied expression. The syntax is as follows:
MIN (expression)
· MAX The function of this function is to find the maximum value in the provided expression. The syntax is as follows:
MAX (expression)
Note: If the local string type uses MIN and MAX, the output depends on the order defined for SQL Server. MIN and MAX cannot be used on bits.
· SUM SUM calculates the sum of all data values. The syntax is as follows:
SUM ([ALL | DISTINCT] expression)
Note: SUM and AVG can only be used for numeric data types.
· COUNT Counts the number of expression values. The syntax is as follows:
COUNT ([ALL | DISTINCT] expression)
COUNT has another usage, it can return the number of selected rows.
For example: SELECT NumRows = COUNT (*) FROM titles
The aggregate function ignores all null values, except COUNT(*). Although all aggregate functions calculate based on the absence of NULL values, COUNT(*) calculates all rows (including rows with NULL values).

1． GROUP BY clause
GROUP BY clause creates smaller groups based on the defined data, and performs aggregate function calculations on each group. In other words, it produces overall information for each group. GROUP BY can treat more than one column as grouping columns (Grouping Columns). It summarizes information about unique values ​​in a combined column.
A selection list using the GROUP BY clause can only contain the following items:
·Constant values.
· Combined columns.
· Expression. Each expression returns a value for each group (like an aggregate function). If a column is in a select list in addition to being in a combined column, then it has multiple values ​​for each unique value of the combined column. This structure type is not allowed.
2． The GROUP BY and HAVING
HAVING clauses are used to add data filtering criteria to queries using the GROUP BY clause. The usage of HAVING is the same as the WHERE clause in SELECT. It is possible to use the WHERE clause in a query that contains a GROUP BY clause. HAVING and WHERE have the same syntax. The difference between HAVING and WHERE is:
· In the WHERE clause, rows that do not meet the conditions are removed before grouping, while in the HAVING clause, the conditions are applied after grouping.
· HAVING can contain aggregate functions in conditions, but WHERE cannot.
Note: GROUP BY and HAVING clauses cannot use text or image data types.
3． COMPUTE BY clause
COMPUTE BY clause can get detailed or total records. It divides the data into smaller groups and then creates a detailed record result data set for each group (like SELECT). It can also generate total records for each group (like GROUP BY).
In COMPUT BY, it is not necessary to define the BY clause. If the BY clause is not defined, the entire table is considered a group, and only two result data sets are produced, one with all detailed records and the other with only one row, which holds the total records.
Note: When using BY in COMPUTE, it is required to include ORDER BY in all combined columns.

Cube and Rollup operations
CUBE and ROLLUP operations can produce more aggregate values ​​than simple GROUP BY. These operations are useful when producing cross tab reports. If the query uses n combination columns, there are 2n combinations for calculating the aggregation.

Accessing data from multiple tables
We have discussed how to access data from a single table. Accessing data from multiple tables is also possible. Accessing data from multiple tables is called joining a table.
1, CROSS JOIN (Cartesian product)
CROSS JOIN simply combines tables without any constraints. The number of rows in the result after CROSS JOIN is the product of the number of rows in the first two tables joined. If you join two tables with thousands of rows each, the results will be unimaginable.
2, INNER JOIN
INNER JOIN is the most commonly used method to combine two tables. INNER JOIN is based on a discriminant, which is called the connection condition. The join condition is defined together with the WHERE clause. The join condition consists of columns from both tables and uses a comparison condition to compare the values ​​of the columns. The values ​​passed the comparison are included in the result data set, the following is the syntax of Inner JOIN:
Syntax 1: (ANSI 92)
Select
FROM INNER JOIN
ON . = .
Syntax 2:
Select
FROM ,. = .
An alias can be defined for the table in the FROM clause, and the alias can be used in place of the real name anywhere.
Note: If there is a null value in the column used as the join condition, the null value cannot match any value, so the result does not include rows with null values.
3. Left Outer JOIN
In Inner JOIN, only rows that match in the two tables can be in the result data set. But in Left Outer JOIN, all rows in the left table appear in the result data set. If a row in the left table does not have a matching row in the right table, a null value is used to replace the value in the right table and join it.
The syntax is as follows: (ANSI 92)
Select
FROM LEFT OUTER JOIN
ON . = < table2>.
4, Right Outer JOIN
Right Outer JOIN is similar to Left Outer JOIN, except that the right table is used as an external table (all rows in the right table are included in the result data set) .
The syntax is as follows:
Select
FROM RIGHT OUTER JOIN
ON . = .< ;column name>
5, Full Outer JOIN
In a Full Outer JOIN, all rows from both tables are included in the resulting data set.
The syntax is as follows:
Select
FROM FULL OUTER JOIN
ON . = .< ;column name>

Case statement
When different result values ​​are produced for different conditions, the Case statement can be used.
The Case statement calculates all defined conditions and returns the result based on whether the condition is true. The
syntax is as follows:
CASE []
WHEN THEN
[ELSE ]
END
Input_expression is any valid SQL Server expression or Boolean expression.
When_expression is any valid SQL Server expression or Boolean expression. This expression is compared with Input_expression. If Input_expression is not defined, When_expression should be a Boolean expression.
Result_expression is any valid SQL Server expression. If the comparison of When_expression and Input_expression returns TRUE (if Input_expression is defined) or When_expression evaluates to TRUE, the expression is evaluated and its result is returned. Otherwise the expression in Else_expression is evaluated and its result is returned.
For example:
SELECT au_fname,au_lname,
State=CASE state
WHEN 'CA' THEN 'California'
WHEN 'KS' THEN 'Kansas'
END
FROM authors


UNION

The UNION statement combines the results of two or more queries into a result set.
The syntax is as follows:
SELECT
FROM
WHERE
UNION [ALL]
SELECT
FROM < ;table_list>
WHERE
The ALL keyword specifies that duplicate data will also be included in the final result data set. If necessary, there can be many UNION statements in a query. All Select_lists should have the same number of columns and be of the same or compatible data type.

Go command The Go command is used to mark the end of a query batch. A query batch is a collection of TSQL statements that are executed together. Go works with Osql or SQL Server Query Analyzer.

The above is the content of Getting Started with SQL Server 7.0 (Part 3). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!