SQL Technology Insider Three

Select

Analyze a query instance

Select Empid,year (OrderDate) as Orderyear,count (*) as OrderCount

From Dbo.orderinfo

where custid=71

GROUP by Empid,year (OrderDate)

Having Count (*) >1

ORDER BY Empid

The order in which the above statements are executed within SQL Server is From--where--group by--having--select--order by

1.From

The dbo is the schema of the data, and it is best to show that because if the database is not written it will still be parsed automatically, and there will be some extra expense to write down which object of the schema it is used to express.

2.Where

Specify a predicate or logical expression

Note that SQL is a three-valued logic, so true is not exactly the same as non-false, and there is a state in unknown.

3.Group by

After grouping will affect the subsequent having select, etc., the subsequent operations are group-based objects

Because an aggregate function returns only one value for each group, when an element is no longer present in group by, it can only be used as input to the aggregate function, such as count (price), and not as output

The aggregate function ignores null but count does not.

4.having

5.select clause

In this example, there are two function year repeats, but SQL internally recognizes duplicate functions and only calls once

The select query does not guarantee that the returned data is a collection, which is guaranteed to be unique, and can be used distinct

Several disadvantages of using asterisk *, A. Can only be returned in the default column order of the table, which can cause problems when the table is modified by the client by column index. B. The internal need to parse the column names, resulting in overhead.

Column name evaluation is not an alias and can be used in a way that repeats a function call.

6.order by

7.Top

Select TOP (1) percent to select the top 1%

8.Over clauses, grouped but can return all values

Select Orderid,custid,val,sum (Val) over () as Totalvalue,sum (Val) Ove (Partitionby CustID) as Custtotalvalue

From Sales.ordervalues

9.row_number () rank Dense_rank

10. Predicates

In like between

One. case

SQL Technology Insider Three