Most commonly used SQL query statements

First, simple query
A simple Transact-SQL query includes only a select list, a FROM clause, and a WHERE clause. They describe the columns queried, the tables or views queried, and the search criteria.
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 '

(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 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 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 reassign the column headings. The definition format is:
Column Header = column name
Column List title
If you specify a column header that is not a standard identifier format, you should use a quotation mark delimiter, for example, the following statement displays column headings with Chinese characters:
SELECT nickname =nickname, email =email from testtable
4. Delete duplicate rows
The SELECT statement uses the all or the distinct option to display all rows in the table that meet the criteria, or to delete the rows of data that are duplicates, by default. When you use the DISTINCT option, only one row is reserved for all duplicate rows of data in the result collection returned by SELECT.
5. Limit the number of rows returned
Use the top n [PERCENT] option to limit the number of data rows returned, top N to return n rows, and Top n PERCENT to indicate that n is a percentage, specifying the number of rows returned is equal to the percentage of Total row count. For example:
SELECT TOP 2 * from TestTable
SELECT TOP PERCENT * from TestTable

(ii) FROM clause
The FROM clause specifies the SELECT statement query and the table or view related to the query. You can specify a maximum of 256 tables or views in the FROM clause, separated by commas.
When you specify more than one table or view in the FROM clause, you should use the object name to qualify the table or view to which the columns belong if the same column exists in the select list. For example, Cityid columns exist in the usertable and citytable tables and should be qualified with the following statement format when querying Cityid in 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 aliases
For example, the above statement can be represented as an alias format for the table:
SELECT Username,b.cityid
From usertable a,citytable b
WHERE A.cityid=b.cityid
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 example, the result collection returned by Select is given an alias of T, and then the data is retrieved from it.

(iii) using the WHERE clause to set the query criteria
The WHERE clause sets the query criteria to filter out the rows of data that are not needed. For example, the following statement queries 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 the expression is a specified item in the list): In (item 1, item 2 ...)
Not in (item 1, item 2 ...)
Pattern Match (judging if the value matches the specified character wildcard): like, not-like
Null-valued judge (determines whether an expression is empty): Is null, not is NULL
logical operators (logical connections for multiple conditions): not, and, or
1, scope operator Example: age between and 30 equals age>=10 and age<=30
2. List operator Example: Country in (' Germany ', ' China ')
3. Pattern Match example: often used for fuzzy lookups, it determines whether the column value matches the specified string format. Available for type queries such as char, varchar, text, ntext, datetime, and smalldatetime.
You can use the following wildcard characters:
Percent percent: can match any type and length of the character, if it is Chinese, please use two percent sign is a percentage.
Underline _: Matches a single arbitrary character, which is commonly used to limit the character length of an expression.
square brackets []: Specifies a character, string, or range that requires matching objects to be any of them.
[^]: The value is also [] the same, but it requires that the matched object be any character other than the specified character.
For example:
Limit ends with publishing, using like '%publishing '
Restrict to start with a: like ' [a]% '
Limit to a beginning outside: Like ' [^a]% '
4. Null-Valued Judge example where age is NULL
5, logical operators: priority is not, and, or

(iv) Sorting of query results
Use the ORDER BY clause to sort the results returned by a query in one or more columns. The syntax format for the ORDER BY clause is:
ORDER by {column_name [asc| DESC]} [,... N]
where ASC represents ascending, default, desc is descending. Order by cannot be sorted by ntext, text, and image data types. For example:
SELECT *
From Usertable
ORDER by age Desc,userid ASC
In addition, you can sort by an expression.

Second, joint inquiry
The Union operator can combine query result collections of two or more than two SELECT statements into a single result set to display, that is, to execute a federated query. The syntax format for union is:
Select_statement
UNION [All] selectstatement
[UNION [All] selectstatement] [... n]
Where Selectstatement is the SELECT query statement to be federated.
The all option means that all rows are merged into the result collection. When the item is not specified, only one row is retained by the repeating row in the result collection of the union query.
When you federate a query, the column headings of the query result are the column headings of the first query statement. Therefore, to define a column header, you must define it in the first query statement. To sort the results of a union query, you must also use the column name, column header, or column ordinal in the first query statement.
When you use the union operator, you should ensure that you have the same number of expressions in the select list for each union query statement, that each query selection expression should have the same data type, or that you can automatically convert them to the same data type. When auto-converting, for numeric types, the system converts low-precision data types to high-precision data types.
In union statements that include multiple queries, the order in which they are executed is from left to right, using parentheses to change the order of execution. For example:
Query 1 Union (query 2 union query 3)

Third, connection query
You can implement multiple table queries by using the Join operator. Connection is the main feature of relational database model, and it is also a sign that distinguishes it from other types of database management system.
In the relational database management system, the relationship between the data is not determined, and all the information of an entity is often stored in a single table. When retrieving data, the connection operation queries the information for different entities that reside in multiple tables. The connection operation gives the user a lot of flexibility and they can add new data types at any time. Create a new table for different entities, and then query through the connection.
A connection can be established in the FROM clause or a WHERE clause in a SELECT statement, and it is plausible to distinguish the join operation from the search condition in the WHERE clause when the connection is indicated in the FROM clause. Therefore, it is recommended to use this method in Transact-SQL.
The connection syntax format for the FROM clause defined by the SQL-92 standard is:
From Join_table Join_type join_table
[On (Join_condition)]
Where join_table indicates the name of the table participating in the JOIN operation, the connection can operate on the same table, or on a multi-table operation, the connection to the same table operation is also called self-connected.
Join_type indicates the type of connection, which can be divided into three types: inner, outer, and cross-connected. An inner join (INNER join) uses comparison operators to perform comparison operations on some (some) column data between tables, and lists the data rows in those tables that match the join criteria. According to the comparison method used, the inner connection is divided into equivalent connection, natural connection and unequal connection three kinds.
The outer joins are divided into three types: Left outer join (either OUTER join or right join), and the outer join, or the all-inclusive (full OUTER join or complete join), and the out-of-the-OUTER join. Unlike an inner join, an outer join lists not only the rows that match the join criteria, but all the data rows in the left table (when the left outer join), the right table (when the right outer joins), or two tables (when you are connected on the outside), all of which match the search criteria.
A cross join has no WHERE clause, which returns the Cartesian product of all data rows in the join table, with the number of rows in the result set equal to the number of data rows in the first table that match the query criteria multiplied by the number of rows in the second table that meet the query criteria.
The ON (join_condition) clause in the JOIN operation indicates the join condition, which consists of columns and comparison operators, logical operators, and so on in the connected table.
Either connection cannot directly connect to the text, ntext, and image data type columns, but you can indirectly connect the three types of columns. For example:
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)

(i) Internal connection
The INNER JOIN query operation lists the data rows that match the join criteria, and it uses comparison operators to compare the column values of the concatenated columns. There are three types of internal connections:
1. Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the joined columns, and the query results list all the columns in the joined table, including the repeating columns.
2, unequal connections: Use a comparison operator other than the equals operator in the join condition to compare the column values of the connected columns. These operators include >, >=, <=, <,!>,!<, and <>.
3. Natural connection: Use the Equals (=) operator in the join condition to compare the column values of the connected column, but it uses the selection list to indicate which columns are included in the query result collection and to delete the duplicate columns in the Join table.
example, the following uses the equivalent connection to list authors and publishers in the same city in the authors and Publishers tables:
SELECT *
From authors as a INNER JOIN publishers as P
On a.city=p.city
If you use a natural connection, delete the authors and Publishers tables in the select list (city and State):
SELECT A.*,p.pub_id,p.pub_name,p.country
From authors as a INNER JOIN publishers as P
On a.city=p.city

(ii) External connection
In the query results collection, only the rows that meet the query criteria (WHERE search condition or having condition) and join conditions are returned. In the case of an outer join, it returns to the query result collection not only the rows that meet the join criteria, but also all data rows in the left table (when left outer joins), the right table (when the right outer joins), or two edge tables (full outer joins).
Use the left OUTER join to connect the forum content and author information as follows:
SELECT a.*,b.* from Luntan left JOIN usertable as B on A.username=b.username
All the authors in the city table and all the authors in the user table, and the cities in which they are located, are used with the full outer join:
SELECT a.*,b.*
From city as a full OUTER JOIN user as B
On A.username=b.username

(iii) Cross-linking
The cross join does not take a WHERE clause, it returns the Cartesian product of all data rows of the two connected tables, and the number of rows returned to the result set equals the number of rows in the first table that match the query criteria multiplied by the number of data rows in the second table that meet the query criteria
example, there are 6 types of books in the titles table, and 8 publishers in the publishers table, the number of records retrieved by the following cross-connection will be equal to the 6*8=48 row.
SELECT Type,pub_name
From the titles cross JOIN publishers
ORDER by Type

Most commonly used SQL query statements