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