SQL Server Database Learning notes _mssql

1,exists and in understanding (reference http://www.jb51.net/article/28922.htm)

Exists: True if a row is included in a subquery

In: If the operand is true equal to one in the expression list, then true

exists always get confused.

Select ... From ... where ...

Where is equivalent to a judgment condition, only the expression after the where the result of the operation is true, before a select

exists is used to check whether a subquery returns at least one row of data that does not actually return any data, but rather returns a value of TRUE or False

1:select C.customerid, CompanyName 2:from Customers C 3:where EXISTS (4:select OrderID from Orders o 5:where o.custom Erid = cu. CustomerID)
The select in a exists subquery like this doesn't matter at all, because the subquery simply checks that the table has no rows that satisfy the where, and returns true without false, so many exists are select after all

A row to determine, exists return is true, that is, the line of the relevant information output

1:select distinct name from XS 2:where NOT exists (3:select * to KC 4:where NOT EXISTS (5:select * from XS_KC 6:WH Ere the school number =XS. and course number =KC. Course number)
Analyze the query statement above:

Take a line of Cartesian products from the outermost XS table and one line at the same level and get inside to test

In the innermost layer, if a row in the XS_KC table has the number of the line to be tested, and the course number that the middle class takes to examine

Returns True, indicating that this student chose this course.

Middle-tier If a course does not receive a return of true information, stating that the name and course collocation in the XS_KC table does not, or that the middle select out of the students are not selected by the course information, if there is such a course, the outermost return a true

The outermost layer adds not to the return information, that is, the outermost layer is looking for such a student:

He chose all the courses.

The outermost line of the line to detect, if he satisfies this condition, output his name and output only once

This is a layer of analysis, there is no other way?


2,select ... Into @ ...

and select @ ... = ... It's supposed to be the same.

3, with As for rename name seems to be better than =, and the assignment area apart

4, with the compute summary, if it is based on a column summary, first ORDER by this column, and then compute the corresponding information, and finally by this column

Group or compute, if by a column, the same column will appear in the Select

The difference is that the aggregate function of a group is in the select row (called a picklist), and the compute aggregate function is in the compute row, and compute can summarize all rows without by