SQL Server queries

From clause

The from should be necessary in a query statement, although it may not, but that is meaningless.

Because the FROM clause is the basis for other parts, all tables to be accessed by the output, where condition, order by, and so on must be included in the FROM clause.

Simple can contain a table, or you can combine data from multiple data sources and data of multiple data types. The maximum number of accesses is 256 tables.

Acceptable data sources: Local SQL Server tables, subqueries, views, table-valued user-defined functions returned rows and columns, distributed data sources ...

Table alias: Table name as Alias in the FROM clause this format is an alias for the table. As can be omitted, but it is better not to do so.

If a table name, alias, column name, and so on are conflicting with the keyword, you can place the name in brackets [] to indicate that it is not a keyword. (non-standard SQL)

Also, if the name of a table, column name, etc. contains spaces in the middle, it can be placed in brackets [], but it is never recommended.

The fully qualified name consists of 4 parts: Server.Database.Schema.Table in the current database does not require the server and database name, that is, in general we need some of the latter two parts. There are two advantages to using qualified names, ① avoid accidental errors, ② improve the performance of queries, so it is recommended to develop good habits.

Where Condition

Filters the output from the FROM clause and restricts the rows returned in the result set.

Comparison operator Standard six >, >=, =, =<, <, <> SQL server-specific three! =,!<,!>

The use clause of algebra is equivalent to the col+20=60 in the condition of col=60-20, but the latter can improve performance, this simple example, the truth is the same, so be sure to pay attention to the writing habits, to break the bad habit.

Other comparison operators between in like are SOME any all

Between x and z equals greater than or equal to x and less than or equal to Z; it is often used in conjunction with dates, datetime

In use case one region in (' Hebei ', ' Henan '); Use case two ' Hebei ' in (Col1,col2,col3 ...)

Like a condition that contains a wildcard character. SQL wildcard characters are different from DOS wildcard characters, avoid confusion, now only say the SQL wildcard:% for any number of arbitrary characters, _ represents an arbitrary character, [] one of the characters attached, [^] is not in the attached character. Example: ' Able ' like ' A% ' "Able ' like ' abl_ ' a ' like ' [a-e] ' a ' like ' ABF ' a ' like ' [^wxyz] '

A logical operator not and or joins multiple where conditions, and the order of operations is not and OR. can be added () to enhance readability.

SELECT ...    WHERE ... SELECT ' abc ' as Col WHERE condition. Returns an ABC if the condition is true, or null if the condition is false.

return Results

The asterisk * Special wildcard represents all columns in the table order, and if multiple tables are all columns of all tables. The tablename.* only includes columns from the table.

Aliases appear in the result set, and expressions and constants are generally displayed as blank column headings if there are no aliases, and aliases that contain spaces or keywords are enclosed in square brackets, single quotes, or double quotes (but preferably not).

Qualified columns qualify columns with table names to prevent misunderstandings and errors.

Sorting Result Sets

Specify sort order by col1,col2 with column name ...

To specify the sort order by expression by using an expression

Use column aliases to specify sorting (WHERE clause does not allow column aliases, OK)

Use the ordinal position of the column order by 1; Not very good, because sometimes the order of the columns will change

Ascending, Descending: Default ascending asc; Descending desc. Example, ORDER by col1 DESC;

Sorting rules

predicate

First explain the meaning of the predicate, according to the definition of "modern Chinese", the words are divided into two categories: nominal including nouns, numerals, quantifiers; predicates include verbs and adjectives. In the context of computer language, predicates refer to the process of returning a true or false evaluation of a conditional expression. predicates in SQL followed by select

All/distinct all returns all rows (default); DISTINCT deletes duplicate rows, preserving one.

Top (n) in the order by condition, Top (n) represents the first n rows of the returned result, and Top (n) PERCENT returns the first n% in the result set.

With TIES in conjunction with top (n), which allows the occurrence of a condition such as a parallel nth name

The row selects order by NewID () and then selects the first row. Newid is a random number, and this method has a certain effect on performance.

SQL Server queries