Select statement in order to use common

Select statement syntax is more complex and complete, and I summarized the common use, from simple to slightly more complicated for the novice reference.
Following the example of table names and field names are used according to the data sheet V3 Dragon Forum.

A simple query

1. Query the table all the fields:

Select * From table name

Example: check the information of all users
Select * From YL_User

2. Querying specific fields:

Select Field 1, Field 2 ... From the table name

Example: Query YL_User the UserName, Password Info
Select UserName, Password From YL_User

3. To change column headings:

Select 'column header' = field name From the table name
Or
Select Field Name As 'column header' From the table name
Or
Select Field Name 'column header' From the table name

Example: Query YL_User the UserName, Password, use the column entitled "User Name", "password" display
Select UserName As 'username', Password As 'password' From YL_User

4. To the results of adding the string:

Select 'string' + field names as column headings From the table name

Example: Query YL_User the UserName information, in the results, by adding "Username:", set the title use the "user name."
Select 'User Name:' + UserName as 'User Name' From [YL_User]

Second, query terms

1. More inquiries:

Select the table name field name From Where conditions
Available comparison operators :=,>,<,>=,<=,<>,!=,!<,!>

Example: Query YL_User table user named "according to Long," the information
Select * From YL_User Where UserName = 'by Dragon'

2. Range queries:

Select the table name field name From Where expression [NOT] Between the end And the value of initial value

Example: Query YL_User table for the 1000-2000 Score User Info
Select * From YL_User Where Score Between 1000 And 2000

3. The list of queries:

Select the table name field name From Where expression [NOT] In (list item 1 list item 2 ...)

Example: Query YL_User table user named "according to Long," "Li Sijie" information
Select * From YL_User Where UserName In ('according to Long', 'Li Sijie')

4. String matching query:

Select the table name field name From Where string expression [Not] Like pattern matching

SQL Server can use the following wildcards: ①% (percent sign): to match a string of arbitrary length and type; ② _ (underscore): match any single character; ③ [] (square brackets): In specified range in brackets, such as [az], etc.; ④ [^]: not specified range.

Example: Query YL_User table user name to "Lee" at the beginning of the user information
Select * From YL_User Where UserName Like 'LI%'

5. Null value judgments query:

Select the table name field name From Where the field name IS [Not] NULL

Example: Query YL_User table TrueName user information for the blank value
Select * From YL_User Where TrueName IS NULL

6. Logical judgments query:

Select the table name field name From Where expression AND | OR [NOT] expression 2

Example: Query YL_User table sex ratio of male and more than 1000 points or prestige of more than 100 user information
Select * From YL_User Where Sex = 'M' And (Score> 1000 Or Power> 100)

7. To eliminate duplicate entries:

Select Distinct From the table name field name

8. Use the Top and Percent limit the result set:

Select Top n [Percent] From the table field names were Where query

Percent n is not specified for the return trip, designated Percent return results that n%.

Example: Query YL_User table top 10% of the user information
Select Top 10 Percent * From YL_User

Third, the use Order By to sort the query results

Order By Sort expression Asc | Desc

Asc: ascending sort; Desc: descending sort.

Example: Query YL_User table all the information, and the results are sorted by ID in descending order

Select * From YL_User Order By ID Desc