I haven't written a blog for a long time, and I have been busy looking for a job recently, so I can't calm down. After a recent interview, I have also exposed many problems to myself. One of the most important parts is the database, because of the project, in the last two years, the project team of the Hong Kong Jockey Club in wensi has never used a database, so the database has become a short board for me, A systematic review of database knowledge, I will write a series and stick to it.
Write from the SELECT statement first, because it is the basis for learning T-SQL. First, we will summarize the logic processing sequence of the SELECT statement, as shown in the following figure.
From
Where
Group
Having
Select
Over
Distinct
Top
Order
From clause
The from clause is the first query clause to be processed in the logic processing phase. It is used to specify the name of the table to be queried and the table operator for table operations. The following example shows how to query the orders table in the sales database architecture and check the SQL statement:
SELECT orderid,custid,empid,orderdate,freightFROM Sales.Orders;
Execution result:
Note: 1> the database architecture should always be used to limit the object names in the Code, such as sales. Order.
2> the specified column to be queried should be displayed to avoid extra costs.
3> the select result looks to be returned in a specific order (in ascending order of orderid), but this cannot be ensured.
4> the SQL Server separator uses square brackets, such as [Order Details]
Where clause
It filters the rows returned by the from stage and can specify a predicate or logical expression. For example, in the where stage, only orders placed by customers whose ID is 71 are retained. The SQL code is as follows:
SELECT orderid,custid,empid,orderdate,freightFROM Sales.OrdersWHERE custid=71;
The program output result is:
Group by clause
In the group by phase, you can combine the rows returned in the previous logical query and processing phase by group. Each group is determined by the elements specified in the group by clause. The SQL code is as follows:
SELECT empid , YEAR(orderdate) AS orderyear , SUM(freight) AS totalfreight , COUNT(*) AS numordersFROM Sales.OrdersWHERE custid = 71GROUP BY empid , YEAR(orderdate);
The program output result is:
Note: 1. the operation objects of all stages (including having, select, and order by) after the group by stage are groups. For example, select is used.
2. If an element does not appear in the group by list, it can only be used as input to aggregate functions (count, sum, AVG, Min, and Max), such as the sum and count functions.