SQL Server Common statements (by functional) _mssql

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 query's
Tables or views, and 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 display order the data in the query result collection is the same 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, you should use a quotation mark delimiter, for example, the following statement uses Chinese characters to display column headings:
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, by 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
Represents a percentage that specifies the number of rows to be returned equal to the number of total rows.
For example:
SELECT Top 2 *
From TestTable
SELECT Top PERCENT *
From TestTable

Two FROM clause
The FROM clause specifies the SELECT statement query and the table or view associated with the query. You can specify up to 256 tables or views in the FROM clause, separated by commas. When you specify more than one table or view in the FROM clause, if the same column exists in the selection list, you should qualify the columns with the object name
The table or view to which it belongs. For example, Cityid columns exist in both usertable and citytable tables, and when querying for Cityid in two tables, you should
Qualify using the following statement format:
SELECT Username,citytable.cityid
From usertable,citytable
WHERE Usertable.cityid=citytable.cityid
You can specify an alias for a table or view in the FROM clause in the following two formats:
Table name as Alias
Table name Alias

(ii) FROM clause
The FROM clause specifies the SELECT statement query and the table or view associated with the query. You can specify up to 256 tables or views in the FROM clause, separated by commas. When you specify more than one table or view in the FROM clause, if the same column exists in the selection list, you should qualify the columns with the object name
The table or view to which it belongs. For example, Cityid columns exist in both usertable and citytable tables and should be qualified with the following statement format when querying the Cityid in two tables:
SELECT Username,citytable.cityid
From usertable,citytable
WHERE Usertable.cityid=citytable.cityid
You can specify an alias for a table or view in the FROM clause in the following two formats:
Table name as Alias
Table name Alias
For example, the preceding statement can be represented as an alias format for a table:
SELECT Username,b.cityid
From usertable a,citytable b
WHERE A.cityid=b.cityid
A select not only retrieves data from a table or view, it can also query data from a collection of results returned by other query statements.
For example:
SELECT A.au_fname+a.au_lname
From authors A,titleauthor Ta
(SELECT Title_id,title
From titles
WHERE ytd_sales>10000
) as T
WHERE a.au_id=ta.au_id
and ta.title_id=t.title_id
In this case, the result set returned by the Select is given an alias T, and then the data is retrieved from it.

(iii) Use WHERE clause to set query criteria
The WHERE clause sets the query condition to filter out unwanted rows of data. For example, the following statement queries for data older than 20:
SELECT *
From Usertable
WHERE age>20
The WHERE clause can include various conditional operators:
Comparison operators (size comparison):>, >=, =, <, <=, <>,!>,!<
Range operator (whether the expression value is in the specified range): BETWEEN ... And ...
Not BETWEEN ... And ...
List operator (determines whether an expression is a specified item in a list): In (item 1, item 2 ...)
Not in (item 1, item 2 ...)
Pattern-matching character (determines whether the value matches the specified character wildcard format): like, not like
Null-valued identifier (to determine whether an expression is empty): Is null, not is NULL
Logical operators (logical joins for multiple conditions): not, and, or
1. Range operator Example: Age BETWEEN and 30 are equivalent to age>=10 and age<=30
2, List operator Example: Country in (' Germany ', ' the ', ')
3, pattern matching example: Often used in fuzzy lookup, it determines whether the column value and the specified string format match. can be used for char,
Query types such as varchar, text, ntext, datetime, and smalldatetime.
You can use the following wildcard characters:
Percent percent: can match any type and length of characters, if it is Chinese, please use two.