First, simple query
Simple Transact-SQL query only includes a selection list, from clause, and WHERE clause. They explain the query columns, query
Tables or views, as well as search criteria, etc.
For example, the following statement queries the Nickname field and the Email field named "Zhang San" in the TestTable table.
SELECT NICKNAME, EMAIL
From testtable
WHERE Name = 'Zhang San'
(1) Select a list
The selection list (select_list) indicates the query column, which can be a list of column names, asterisks, expressions, variables (including local changes)
Quantity and global variables), etc.
1, choose all columns
For example, the following statement displays data from all columns in the TestTable table:
SELECT *
From testtable
2, select some columns and specify their display order
The order of data in the query result collection is the same as the sequence of column names specified in the selection list.
E.g:
SELECT NICKNAME, EMAIL
From testtable
3, change column headings
In the selection list, you can reset the column header. Define formats:
Column header = column name
Column name title
If the specified column header is not a standard identifier format, the quotation marks should be used, for example, the following statements use Chinese characters to display columns.
title:
SELECT nickname = nickname, email = email
From testtable
4, delete duplication
Use all or distinct options in the SELECT statement to display all rows or delete data lines in the table, default
For all. When using the Distinct option, only one row is retained in the result collection of all duplicate data rows in SELECT.
5, limit the number of rows returned
Use the top n [percent] option to limit the number of data returned, Top N description returns N row, and Top N Percent, indicating that n is
Represents one hundred points, specifying the number of rows returned equal to a few percent of the total number of lines.
E.g:
SELECT TOP 2 *
From testtable
SELECT TOP 20 percent *
From testtable
(2) from clause
From clause specifying a SELECT statement query and a table or view related to query. Up to 256 tables or views in the FROM clause,
They separated between commas.
When you specify multiple tables or views at the FROM clause, if you exist in the same name in the selection list, you should use the object name to qualify these columns.
The table or view. For example, in the useertable and the citytable table, there is a CityID column in the table, and should check when Query the CITYID in the two tables.
Use the following statement format to limit:
Select Username, CityTable.cityID
From utertable, citytable
Where uterable.cityid = citytable.cityID
In the FROM clause, you can specify an alias for tables or views in the following format:
Name AS alias
Table name
For example, the alias format of the above statement can be expressed as:
SELECT Username, B.CityID
From utertable a, citytable b
Where a.cityid = B.CITYID
SELECT can not only retrieve data from the table or view, but also query data from the result collection returned from other query statements.
E.g:
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 This example is given to the result of the result returned to the result t, and then retrieve data from it.
(3) Setting the query condition using the WHERE clause
The WHERE clause sets the query condition and filter out the unwanted data line. For example, the following statement queries the age of greater than 20:
SELECT *
From utertable
WHERE AGE> 20
The WHERE clause can include various conditional operators:
Compare operators (size comparison):>,> =, =, <, <=, <>,!>,! <
Range operators (whether the expression value is in the specified range): Between ... and ...
NOT BETWEEN ... AND ...
List operator (judging if the expression is specified in the list): in (item 1, Item 2 ...)
Not in (item 1, Item 2 ...)
Mode match (determined whether the value is constructed with the specified character wildfire format): Like, Not Like
Null value determine (judging if the expression is empty): Is Null, Not is Null
Logical operator (for multi-condition logical connections): NOT, AND, OR
1. Range Operator Example: AGE BETWEEN 10 and 30 equivalent to agn> = 10 and age <= 30
2, list operand: country in ('germany', 'China')
3, Mode Matching: Commonly used in the fuzzy lookup, it determines if the column value matches the specified string format. Can be used in char,
VARCHAR, TEXT, NTEXT, DATETIME and SmallDateTime are queried.
You can use the following wild character:
Percentage%: Characters that can match any type and length, if it is Chinese, please use two percent sign to %%.
Underline _: Match a single arbitrary character, it is often used to limit the character length of the expression.
Square brackets []: Specify a character, string, or range that requires the matching object to be any of them.
[^]: The value is also [], but it requires the matching object to be any of the characters other than the specified character.
E.g:
Limited to the end of Publishing, use Like '% Publishing'
Restrictions on A: Like '[A]%'
Limits outside the beginning: Like '[^ a]%'
4, null value judgment case where agn is null
5, logical operator: priority is NOT, AND, OR
(4) Sort query results
Use the ORDER BY clause to query the result returned by a column or multiple columns. The grammatical format of the ORDER BY clause is:
Order by {column_name [ASC | DESC]} [, ... n]
The ASC represents ascending, the default value, DESC is descending. ORDER BY cannot be row according to NTEXT, TEXT, and Image data types
sequence.
E.g:
SELECT *
From utertable
Order by age desc, userid ASC
Alternatively, you can sort according to the expression.
Second, joint inquiry
UNION operators can combine the query result set of two or more above SELECT statements into a result set display, that is, the execution
Inquiry. Union's grammar format is:
SELECT_STATEMENT
Union [all] selectStatement
[Union [all] selectStatement] [... n]
Where selectStatement is the SELECT query statement to be combined.
The all option represents all rows into the result collection. When the item is not specified, the repeated line of the joint query result will only be retained.
Row.
When the query is combined, the column of the query result is the topic title of the first query statement. Therefore, to define the column headings must be defined in the first query statement. To sort the joint query results, you must also use the column name, column header or column sequence number in the first query statement.
When using the UNION operator, you should guarantee the same number of expressions in the selection list of each joint query statement, and each query is selected.
The mission should have the same data type, or can be automatically converted to the same data type. For automatic conversion, for numeric classes
Type, the system converts low-precision data types into high-precision data types.
In the Union statement including multiple queries, the execution order is from left to right, and the use of parentheses can change this execution order. E.g:
Query 1 Union (Query 2 Union Query 3)
Third, join inquiry
Multiple table queries can be implemented by connecting an operator. Connection is the main feature of the relational database model, and it is also different from other types.
A logo of the database management system.
In the relational database management system, the relationship between each data does not have to be determined when the table is established, and all the information of an entity is often stored in
One table. When retrieving data, the information stored in multiple tables is queried when the data is retrieved. Connection * Working to the user
Come big flexibility, they can add new data types at any time. Create a new table for different entities, after the connection
Inquire.
The connection can be established in the FROM clause or WHERE clause of the SELECT statement, which is similar to that it is said to be connected in the FROM clause.
Connect the connection * to distinguish between the search criteria in the WHERE clause. So, this method is recommended in Transact-SQL.
The connection syntomitization of the FROM clause defined by the SQL-92 standard is:
From join_table join_type join_table
[On (join_condition)]
Where Join_Table points to the table name of the connection *, the connection can be made to the same table *, or you can do a multitabinery.
The connection to a table * is also called self-connection.
Join_type indicates that the connection type can be divided into three: internal connections, external connections, and cross-connect. Inner Join usage
Comparison of comparison * of a set of items (s) comparison * compared to the comparison * of the data is made, and the data lines matching the connection conditions are listed. According to the use
The comparison method is different, and the inner connection is divided into equivalent connection, natural connection, and not waiting for three kinds.
The outer connection is divided into left-way connections (LEFT OUTER JOIN or LEFT JOIN), right-external connections (Right Outer Join or Right Join)
Three of full outer connect (Full Outer Join or Full Join). Unlike internal connections, external connections are not only listed with the connection conditions.
The line, but lists the left table (when connected to the left), the right table (right-external connection) or two tables (all-end connection) all matching conditions
Data line.
Cross Join does not have a WHERE clause, it returns a descint of all data lines in the connection table, and its result collection
The number of data lines is equal to the number of data rows that meet the query conditions in the first table multiplied by the number of data lines that meet the query conditions in the second table.
The on (join_condition) clause in connection * indicates the connection condition, which is logically logically, logic, logic, logic, logic, logic
Operator, etc.
No matter which connection can be directly connected to the text, ntext, and the image data type column, but you can indirectly over these columns.
connection. E.g:
SELECT P1.PUB_ID, P2.PUB_ID, P1.PR_INFO
From pub_info as p1 inner join pub_info as p2
On Datalength (p1.pr_info) = DATALENGTH (P2.PR_INFO)
(1) internal connection
Internal connection query * Lists the data lines that match the connection condition, which uses the comparison operator to compare the column values of the columns. Internal connection
Three: 1, Equal Connection: Use the column value of the connected column to compare the column of the connection column in the connection conditions, listed in the query results.
All columns in the table include repeated columns.
2, inequality: Use the column values of the columns connected to the listed columns that are connected to other comparison operators other than operators. These ones
The operators include>,> =, <=, <,!>,!
3, natural connection: use the column value of the connected column using equal to (=) operator in connection conditions, but it uses the selection list to point out the query
The columns included in the result collection and delete the repeat columns in the connection table.
For example, the following uses the equivalent connection to list authors and publishers in the same city in the Publishers table:
SELECT *
From authors as a inner join public publishers asp
ON A.CITY = P.city
For example, use natural connections, remove the authors and publishers tables (City and State) in the selection list:
Select a. *, Pub_id, pub_name, p.country
From authors as a inner join public publishers asp
ON A.CITY = P.city
(2) external connection
In-connection, returning only the query criteria (WHERE search criteria or Having condition) and connection conditions
Row. When using external connections, it returns to the query result collection not only contains rows that meet the connection conditions, but also the left table (left outside)
When connected), all data rows in the right table (when connected to the right) or two edge tables (all external connections).
The forum content and author information are connected to the left outer connection:
Select a. *, B. * From Luntan Left Join UserTable As B
ON A.USERNAME = B.USERNAME
The following uses all the authors in the city table and all the authors in the USER table, as well as their city:
SELECT A. *, B. *
From City As a Full Outer Join User AS B
ON A.USERNAME = B.USERNAME
(3) cross-connect
Cross connections do not bring a WHERE clause, it returns the two tables of the two tables that are connected to the Cartesian, return to the number in the result collection
According to the number of data lines that comply with the query conditions in the first table, multiplied by the number of query conditions in the second table complies with the number of query conditions.
In the case, there are 6 types of books in the Titles table, and 8 publishers in the Publishers table, the following cross-connect retrieved record number will wait
On 6 * 8 = 48 lines.
SELECT TYPE, PUB_NAME
From titles cross Join Publishers
ORDER by Type
