Common uses for SQL Server DML (SELECT) (ii)

1 Introduction

The previous article describes the basic use of DML in SQL Server, where the SELECT statement is the most commonly used statement, its powerful, complex structure, the following examples, the use of the method is described in detail.

2 Select query statement

The SELECT statement finds data from a datasheet or view, and the select syntax is summarized as follows:

[ with <common_table_expression>]

　　　　SELECT select_list [ into New_table_name]

[ from Table_source] [WHERE search_condition]

[GROUP by group_by_expression]

[ having search_condition]

[ORDER by order_expression [ASC | DESC]]

Suppose that the following three tables are available, named Student, course, and grade, using the following examples to illustrate the use of each clause.

　　　　

Student Course

　　　　

Grade

3 Simple Query

• with clauses

The WITH clause is used to specify a temporary named result set, which is called a common table expression (CTE) from a simple query. In other words, a temporary table is found by using the WITH clause and then queried in that temporary table. The syntax is as follows (syntax format, uppercase keyword, [] optional, [,...] To repeat the previous item):

With Expression_name [(column_name [,...])] As

(cte_query_definition)

CTE_query_definition: Specifies a SELECT statement whose result set populates the common expression.

For example, to find the age distribution of student in the WITH clause, put in the temp table agereport, and then look for the Agereport table, the SQL statement is as follows:

The results are as follows:

• SELECT ... From clause

Select specifies the information to read from the table or tables from which the data is obtained. The column-alias method for the query in select:

Alias = column Name

Column name as Alias

Column name aliases

For example, query the student table for Student_no and name and set the alias to the number and name:

The results are as follows:

• into clauses

Creates a new table and inserts the query results into a new table.

• WHERE clauses

1. logical operators (not, and, or)

[NOT] Boolean_expression

Boolean_expression and Boolean_expression

Boolean_expression OR Boolean_expressio

2. Comparison operators

= > < >= <= <>

For example, look for student with a female age of less than 18:

　　　　　　

　　　　　　

3. Like keyword

Match_expression [NOT] like pattern [ESCAPE escape_expression]

Wildcard% _ [] [^],% matches any 0 or more characters, _ matches one character, [] specifies a range or collection, such as [a-f] or [abcdef] represents one of the characters, [^] is the opposite of [].

For example, find a student surnamed Li:

　　　　　　

　　　　　　

4. Between keyword

Between ... And and not between ... and

5. is (not) NULL keyword

In the WHERE clause, you cannot use = to determine null, only is (not) null

For example, a record in which the score is empty is the super-search score table:

　　　　　　

6. In keyword

Use the In keyword to specify criteria for a list search to determine whether the specified value matches a subquery or a value in the list.

test_expression [NOT] in (subquery | expression [,...])

7. All, SOME, any keywords

Compares scalar values and values in a single column, used with comparison operators and subqueries.

scalar_expression {= | <> | > | >= | < | <=} {ALL | SOME | Any} (subquery)

For example, find students who are older than heart rain and Li Xiao (> All):

8. Exists keyword

Used to specify whether a subquery exists

EXISTS subquery

• GROUP by clauses

Represents a set of selected rows grouped together into a single summary rowset, one row for each group, based on the value of a column or expression.

GROUP by Group_by_expression [,...]

The SELECT clause must be included in a cluster function or a GROUP BY clause. The usual line aggregation functions are as follows:

COUNT (*)		Returns the number of items in a group
COUNT ([All | DISTINCT])		Returns the number of a column
AVG ([All | DISTINCT])		Returns the average of a column
MAX ([All | DISTINCT])		Returns the maximum value of a column
MIN ([All | DISTINCT])		Minimum value
SUM ([All | DISTINCT])		Sum
STDEV ([All | DISTINCT])		Standard deviation
STDEVP ([All | DISTINCT])		Overall standard deviation
VAR ([All | DISTINCT])		Variance
VARP ([All | DISTINCT])		Population variance

For example, students are grouped by gender and counted:

　　

• having clauses

Specifies the search criteria for a group or aggregation, which is typically used in group by.

Having search_condition

For example, students are grouped by gender and the number of girls is counted

• ORDER by clauses

Specifies the sort method to use in the list returned by the SELECT statement. The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries unless you also specify top.

ORDER by {order_by_expression [COLLATE collation_name] [ASC | DESC] [,...]}

[COLLATE Collation_name] is specified as the collation of the collation_name, not the collation defined in the table or view.

ASC means ascending, desc is descending, and ascending by default.

For example, sort students by age:

　　　　

• COMPUTE clauses

The build totals are listed as additional totals now at the end of the result set, and when used with by, the COMPUTE clause generates control interrupts and subtotals in the result set.

COMPUTE

{{AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM} (expression)}[,...]

[By expression [,...]

If you are using the row aggregation function specified with the COMPUTE clause, they are not allowed to use the DISTINCT keyword.

For example, students are sorted by age and average age is calculated:

For example, students are sorted by gender and the average age of different genders is calculated:

• DISTINCT Key Words

Removes duplicate records from the result set of the SELECT statement.

• TOP Key Words

Limits the number of bars displayed by the query structure.

SELECT TOP N [PERCENT] from table WHERE

PERCENT represents a percentage of n.

For example, look for the youngest three students:

　　

　　

4 Union merge multiple query results

Union merging is the merging of rows from two tables into a single table. The number of rows for the new table after merging is the sum of two table rows and the number of columns is the same. The rules need to be met:

1. Two tables must have the same number of columns, the data type on the corresponding column must be compatible
2. Column names and aliases are determined by the first SELECT statement
3. By default, duplicate rows are removed unless you explicitly specify the ALL keyword
4. The ORDER BY clause must be placed after the last select, and the sort column name used must be the column name in the first select

The difference between union and join queries

　　　　

In a merge, the number of columns for two tables must be the same, type compatible; in joins, the columns of the resulting table may come from the first table, the second table, or the two tables.

In a merge, the maximum number of rows in a structure table is the number of rows in a two table, and the maximum row for a join is the product of two tables.

For example, a form for merging students and courses:

　　　　

　　　　

5 subqueries and nested queries

A subquery is a query that is nested within a SELECT, INSERT, UPDATE, or DELETE statement or other subquery, and any place where you use an expression can use a subquery.

A nested query is a query block nested within the WHERE or HAVING clause of another query block, so nested queries belong to subqueries.

Nested queries are often used in conjunction with comparison operators (<, >, and so on) and logical operators (in, any, and so on).

For example, find student information with a math score greater than 90:

　　　　

　　　　

6 Join Query

relational database, often through the primary key, foreign keys to establish a pair of one or one-to-many or many-to-many relational tables. A join query is a query that joins two or more tables that have relationships together.

The join query consists of a Cartesian product operation plus a selection operation. Joins can be divided into inner joins, outer joins, and Cross joins.

• Inner joins

SELECT fieldlist from table1 [INNER] JOIN table2 on table1.column = Table2.column

In the inner join result, all rows that have no matches in the joined table are deleted, so information may be lost.

For example, join the query student and the score table:

　　　　

　　　　

• Outer Joins

1. Left JOIN

SELECT FieldList from table1 left JOIN table2 on table1.column = Table2.column

All items in the left table are retained in the result, and rows with no matches in the right table are deleted.

For example, left join query student and score table

　　　　　　

　　　　　　

More than one row is visible, which has no matching item in the student table in the score table, and the row student table data is all null.

2. Right JOIN

SELECT FieldList from table1 right JOIN table2 on table1.column = Table2.column

All items in the right table are retained in the result, and rows with no matches in the left table are deleted.

For example, right join query students and SCORE tables:

　　　　　　

　　　　　　

A row is visible that has no matching entries in the student table in the score table, and the table data is all null.

3. Full JOIN

SELECT fieldlist from table1 full JOIN table2 on table1.column = Table2.column

All items in the left and right side table are retained in the results

For example, a full join query student and score table:

　　　　

　　　　

Two more lines are visible. Retains two tables of complete data.

• Cross Join

There is no cross join of the WHERE clause, resulting in a Cartesian set of two tables. The product of the behavior source table rows in the result set should avoid cross joins of large lists.

SELECT fieldlist from table1 cross JOIN table2

For example, cross-join courses and student tables, the number of rows in the result set is the product of two table rows.

　　　　

　　　　

• Join multiple tables

SELECT fieldlist from table1, table2, Table3 ... WHERE Table1.column = table2.column and Table2.column = Table3.column ...

Or

SELECT fieldlist from table1 join table2 join Table3 ... On table1.column = Table2.column and Table2.column = Table3.column ...

The ON statement must follow the order of the list after from, which is the corresponding on statement of the table written first.

Common uses for SQL Server DML (SELECT) (ii)