Advanced application techniques for SQL query statements

First, simple query

A simple Transact-SQL query includes only a picklist, a FROM clause, and a WHERE clause. They describe the query column, the table or view of the query, and the search criteria, and so on.

For example, the following statement queries the nickname field and the email field whose name is "John" in the TestTable table.

SELECT Nickname,email

From TestTable

WHERE name= ' John '

(i) SELECT list

The select list (select_list) indicates the query column, which can be a list of column names, asterisks, expressions, variables (including local variables and global variables), and so on.

1. Select all Columns

For example, the following statement shows the data for all columns in the TestTable table:

SELECT *

From TestTable

2, select some columns and specify their order of display

The data in the query results collection is arranged in the same order as the column names specified in the select list.

For example:

SELECT Nickname,email

From TestTable

3. Change column headings

In the select list, you can specify the column headings again. The format is defined as:

column heading = column name

Column Name title

If you specify a column heading that is not a standard identifier format, use a quotation mark delimiter, for example, the following statement uses Chinese characters to display columns

Title:

SELECT nickname =nickname, e-mail =email

From TestTable

4. Delete duplicate rows

The SELECT statement uses the all or distinct option to display all rows in the table that meet the criteria, or to delete duplicate rows of data, default to all. When you use the DISTINCT option, only one row is left in the result set returned by SELECT for all duplicate rows of data.

5, limit the number of rows returned

Use the top n [PERCENT] option to limit the number of rows returned, top N to return n rows, and Top n PERCENT to indicate that n is a percentage, and that the number of rows returned is equal to a percentage of the Total row count.

For example:

SELECT Top 2 *

From TestTable

SELECT Top PERCENT *

From TestTable