Missing SQL statements

First, simple query

1. 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.

    Select Top 2 * from TestTable SELECT top PERCENT * from TestTable

2. 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

You can use 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.

Null-valued judge (determines whether an expression is empty): Is null, not is NULL

logical operators (logical connections for multiple conditions): not, and, or

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.

There are three types of connections: inner, outer, and cross-connected.

I: Inner connection (INNER join)

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.

II, external connection

There are three types of left outer joins (either the Ieft OUTER join or the leftmost join), the right outer join, the OUTER join, and the All-out join (full OUTER Join or complete 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.

III. Cross Join

The

Cross join does not take a 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 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.