A simple Transact-SQL query only includes the select list, FROM clause, and WHERE clause. They indicate the queried columns, the queried tables or views, and the search conditions.
A simple Transact-SQL query only includes the select list, FROM clause, and WHERE clause. They indicate the queried columns, the queried tables or views, and the search conditions.
1. Simple Query
A simple Transact-SQL query only includes the select list, FROM clause, and WHERE clause. They respectively describe the queried columns and the queried
Tables or views, and search conditions. For example, the following statement queries the nick name and email fields in the testtable table with the name "Zhang San.
SELECT nickname, email
FROM testtable
WHERE name = 'zhangsan'
(1) Select a list
Select_list indicates the queried column, which can be a list of column names, asterisks, expressions, and variables (including local variables and global variables.
1. Select all columns
For example, the following statement shows the data of all columns in the testtable table:
SELECT *
FROM testtable
2. Select some columns and specify their display order. The data in the query result set is arranged in the same order as the column name in the selection list.
For example:
SELECT nickname, email
FROM testtable
3. Change the column title
In the selection list, you can re-specify the column title. Definition Format:
Column Title = column name
If the specified column title is not in the standard identifier format, use the quotation mark ("). For example, the following statement displays the column title in Chinese characters:
SELECT nickname = nickname, email = email
FROM testtable
4. Delete duplicate rows
Use the ALL or DISTINCT option in the SELECT statement to display ALL rows that meet the conditions in the table or delete duplicate data rows. The default value is
Is ALL. When the DISTINCT option is used, only one row is retained for all duplicate data rows in the result set returned by the SELECT statement.
5. Limit the number of returned rows
Use the TOP n [PERCENT] Option to limit the number of returned data rows. TOP n indicates that n rows are returned, while TOP n indicates that n is
Indicates the one hundred score, and the number of returned rows is equal to a few percent of the total number of rows.
For example:
Select top 2 *
FROM testtable
Select top 20 PERCENT *
FROM testtable
(2) FROM clause
The FROM clause specifies the tables or views related to the query of SELECT statements. Up to 256 tables or views can be specified in the FROM clause. They are separated by commas. When the FROM clause specifies multiple tables or views at the same time, if the same column exists in the selected list, use the object name to limit these columns.
The table or view. For example, if the cityid Column exists in both the usertable and citytable tables
Use the following statement format to limit:
SELECT username, citytable. cityid
FROM usertable, citytable
WHERE usertable. cityid = citytable. cityid
You can specify aliases for tables or views in the FROM clause in the following two formats:
Table name as Alias
Table Name alias
(2) FROM clause
The FROM clause specifies the tables or views related to the query of SELECT statements. Up to 256 tables or views can be specified in the FROM clause. They are separated by commas. When the FROM clause specifies multiple tables or views at the same time, if the same column exists in the selected list, use the object name to limit these columns.
The table or view. For example, if the usertable and citytable tables contain the cityid column at the same time, the following statement format should be used to limit the cityid column when querying the two tables:
SELECT username, citytable. cityid
FROM usertable, citytable
WHERE usertable. cityid = citytable. cityid
You can specify aliases for tables or views in the FROM clause in the following two formats:
Table name as Alias
Table Name alias
For example, the alias format of the table available in the preceding statement is as follows:
SELECT username, B. cityid
FROM usertable a, citytable B
WHERE a. cityid = B. cityid
SELECT can not only retrieve data from tables or views, but also query data from the result set 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 example, the result set returned by SELECT is given an alias t, and then the data is retrieved.
(3) Use the WHERE clause to set query conditions
The WHERE clause sets query conditions to filter out unwanted data rows. For example, the following statement queries data older than 20:
SELECT *
FROM usertable
WHERE age> 20
The WHERE clause can contain various conditional operators:
Comparison operators (size comparison):>, >=, =, <, <=, <>,!> ,! <
Range operator (whether the expression value is in the specified range):... AND...
Not... AND...
List operator (determines whether the expression is a specified item IN the list): IN (item 1, item 2 ......)
Not in (item 1, item 2 ......)
Pattern match character (determine whether the value matches the specified character wildcard format): LIKE, NOT LIKE
NULL Value identifier (whether the expression is null): is null, NOT IS NULL
Logical operators (for multi-condition logical connections): NOT, AND, OR
1. Range operator example: age BETWEEN 10 AND 30 is equivalent to age> = 10 AND age <= 30
2. List operator example: country IN ('Germany ', 'China ')
3. pattern matching example: it is often used for fuzzy search to determine whether the column value matches the specified string format. Can be used for char,
Queries of varchar, text, ntext, datetime, and smalldatetime types.
You can use the following wildcard characters:
Percent sign %: can match any type and length of characters. If it is Chinese, use two.
