SQL basic Review Series 1 single table query (select statement)

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.