SQL Server (three): SELECT statement

1. The most basic SELECT statement:

Select [Top N [with Ties]]

<*| column_name [as <alias>][, ...n]>

From <Table_name>

Order by <column_name [desc]>[, ... n]

1) * (asterisk) denotes all columns, and you can change the displayed column names in the result set when a specific column is selected

Select   *   from Products

Select Productid,productname,categoryid,unitprice
from Products

Select ProductID as Id,productname as Name,categoryid,unitprice as Price
from Products

2) You can use an expression to evaluate a column in the result set

Select Productid,productname,categoryid,unitprice,
Outprice = UnitPrice * 1.2
from Products

3) Order by sorts the columns in the result set, if reversed, plus desc, if it's multiple columns, sort by the first column, sort by the second column if the first column is the same, and so on

Select Productid,productname,categoryid,unitprice
from Products
Order   by Categoryid,unitprice Desc

4) Top N: Displays the first n rows in the result set, and the order by;top N with Ties when using top N: If there is a value equal to the nth row after the nth row, the rows are also displayed, and when you use top n with Ties, you must have an order by.

Select   Top   A
Productid,productname,categoryid,unitprice
from Products

Select   Top   A   with Ties
Productid,productname,categoryid,unitprice
from Products
Order   by UnitPrice

2. Where CONDITION clause:

When you use where, followed by a conditional expression, the conditional expression can be:

1) Conditions for connecting using comparison operators

2) Conditions for connecting using logical operators

3) conditions for connecting using Between...and:
where C Betweeb v1 and V2 equals where C>=V1 and C<=v2

4) using in:
where C in (V1,V2,V3) is equivalent to where c=v1 or C=v2 or c=v3

5) using is null or is not NULL

6) Use a fuzzy query like string, where the wildcard characters supported are:
Underline, representing any single character;
An asterisk that represents any number of any character;
[<list>], which represents a single character, the character must be a character that exists in the list;
[^<list>], which represents a single character, the character must be a character that does not exist in the list;

3. Summary and subtotals

1) Use aggregation functions for data summarization using GROUP by <column_name [, ...n]> for subtotals

Select   sum (UnitPrice) as   [ SUM ]
from Products

Select CategoryID, sum (UnitPrice) as   [ SUM ]
from Products
Group   by CategoryID

2) The column of the query must be a class that appears in group by

3) must be queried in the order of the conditional statement (where), the Subtotal statement (group by), and the sort statement (order by). The system is also executed in the order of the conditional statement (where), the Subtotal statement (group by), and the sort statement (order by).

Select CategoryID, sum (UnitPrice) as   [ SUM ]
from Products
Where ProductID < -
Group   by CategoryID
Order   by   [ Sum ]   Desc

4) If you implement the criteria for the summary result, use the HAVING clause, you cannot use the Where condition.

4, about the function of ranking, etc.

New functions introduced in SQL Server: Rank, Dense_rank, Row_number, NTile (n)

Select Productid,productname,unitprice,
Rank () Over ( Order   by UnitPrice) as   [ Rank ] ,
Dense_rank () Over ( Order   by UnitPrice) as   [ Dense_rank ] ,
Row_number () Over ( Order   by UnitPrice) as   [ Row_number ] ,
NTile ( Ten )  Over ( Order   by UnitPrice) as   [ NTile ]
from Products

5. Multi-Table Connection

1) where connection is used

Select Productid,productname,categoryname
from products,categories
where Products.CategoryID = Categories.CategoryID

2) Connect using JOIN statement

Select Productid,productname,categoryname
from Products P Join Categories C
on P.categoryid = C.categoryid

3) Join connection type:

(1) Inner connection

(2) External connection

(3) Cross connection

6, sub-query

1) as a single value use: The result of the query is a single row, with the comparison operator.

Declare   @sum   Money
Select   @sum = sum (UnitPrice) from Products
Select   *   from Products
where UnitPrice > @sum

Select   *   from
Where UnitPrice > ( Select   sum (UnitPrice) from Products )

2) as a multi-valued use: The result of a query requires a single column, with the in operator to use.

Select p. *   from
Products P Join Categories C on P.categoryid = C.categoryid
where CategoryName like   ' c% '

Select   *   from Products
where CategoryID inch
( Select CategoryID from Categories
where CategoryName like   ' c% ' )

3) As a result set (also can be simply understood as a "table") use.

Select Productid,productname,unitprice
from
(
Select Productid,productname,unitprice
Row_number () Over ( Order   by UnitPrice) as RowNumber
from prodcuts
)  as T
where RowNumber between   A   and   -

SQL Server (three): SELECT statement