SQL query statement uses
http://www.fixdown.com/Article/Article/1495.htm
First, simple query
Simple Transact-SQL query only includes a selection list, from clause, and WHERE clause. They illustrate the table or view of the query column, query, and 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 column of the query, which can be a list of column names, asterisks, expressions, variables (including local variables, and global variables).
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, you should use the quotation margin, for example, the following statement uses Chinese characters to display column headings:
SELECT nickname = nickname, email = email
From testtable
4, Distinct delete duplicate row
The SELECT statement uses all or the distinct option to display all rows or delete data lines in the table, and the default is ALL. When using the Distinct option, only one row is retained in the result collection of all duplicate data rows in SELECT.
5, the number of rows returned to TOP
Use the top n [percent] option to limit the number of data rows, Top N indicates that the N row is returned, and when top n percent, indicating that N is a one hundred score, 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. In the FROM clause, you can specify up to 256 tables or views, and they are separated by commas between them.
When you specify multiple tables or views at the FROM clause, if there is a list of columns in the selection list, you should use the object name to qualify the table or view of these columns. For example, in the userTable and the CityTable table, there is a cityID column, and should be limited to the following statement format when Query the cityID in the two tables:
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
In this case, the set of the result returned to the Select is given a alias t, and then the data is retrieved.
(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 for type inquiry such as char, varchar, text, ntext, datetime, and smalldatetime.
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 sorted by NTEXT, TEXT, and Image data types.
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 results of two or more SELECT statements into a result set, that is, perform joint queries. 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 this item is not specified, the repeat line of the joint query result will only be retained.
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, there should be the same number of expressions in the selection list of each joint query statement, and each query selection expression should have the same data type, or can automatically convert them to the same data. Types of. When automatic conversion, for numeric types, the system converts low-precision data types to 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 a sign of other types of database management systems.
In the relational database management system, the relationship between each data does not have to be determined when the table is established, and all information about one entity is often stored in a table. When retrieving data, information is queried by the connection operation to save the information stored in multiple tables. Connection operations bring great flexibility to users, they can add new data types at any time. Create a new table for different entities, and then query by the connection.
The connection can be established in the SELECT statement from the FROM clause or the WHERE clause, which is similar to that in the FROM clause indicates that the connection will help to distinguish the connection operation with 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 operation, the connection can be operated on the same table, or a multi-table operation, the connection to the same table, also known as self-connection.
Join_type indicates that the connection type can be divided into three: internal connections, external connections, and cross-connect. Inner Join Using the comparative operator to perform a comparison operation of the data (some) column data, and list the data lines matching the connection conditions in these tables. According to the comparative method used, the inner connection is divided into equivalent connection, natural connection, and not equal connection.
The outer connection is divided into left-way connections (Right Outer Join, Right Outer Join or Right Join, and three full-end connections (Full Outer Join or Full Join). Unlike internal connections, the external connection does not only list rows matching the connection condition, but is listed on the left table (left external connection), the right table (right-external connection) or two tables (all external connection All data lines in line with search criteria are in line.
Cross Join does not have a WHERE clause, it returns the Cartesian of all data lines in the connection table, and the number of data lines in the result set is equal to the number of data lines that meet the query conditions in the first table. The number of data lines in the table is in line with the query conditions.
The on (join_condition) clause in the connection operation indicates the connection condition, which is constructed by the columns and comparison operators, logical operators, and the like in the connected table.
No matter which connection can be directly connected, these three columns can be indirectly connected. 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 connections The connection query operation lists the data lines that match the connection condition, which uses the comparison operator to compare the column values of the columns. The internal connection is divided into three types:
1. Equivalence: Use the column value of the connected column to compare the column value of the connection column in the connection conditions, and the query results are listed in the query results, including the repeat 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 equal to (=) operator to compare the column values of the columns in the connection conditions, but it uses the selection list to point out the columns included in the query 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
When the internal connection, the return of the query result collection is only the line that meets the query condition (WHERE search criteria or Having condition) and the connection condition. When using an external connection, it returns to the query result collection not only contains rows that meet the connection conditions, but also the left table (when connected to the left), the right table (when connected to the right) or two edges (full All data lines in an external connection.
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
The cross-connection does not take the WHERE clause, it returns the two tables of the two tables that are connected, and the number of data lines in the result collection is equal to the number of data lines that meet the query conditions in the first table. The number of data lines that meet the query conditions in a table.
In the case, there are 6 types of books in the Titles table, and 8 publishers in the Publishers table, the number of records retrieved in the following cross-connects will be equal to 6 * 8 = 48 lines.
SELECT TYPE, PUB_NAME
From titles cross Join Publishers
ORDER by Type
/
