SQL Server database Study Notes

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

Exists: TRUE if the subquery contains a row

In: If the operand is TRUE or equal to one in the expression list, it is TRUE.

Exists always cannot understand

Select... From... Where...

The where clause is equivalent to a judgment condition. Only when the expression after the where clause returns TRUE, the previous clause can be selected.

EXISTS is used to check whether a subquery returns at least one row of data. This subquery does not actually return any data, but returns True or False.

1: SELECT c. CustomerId, CompanyName 2: FROM Customers c 3: where exists (4: SELECT OrderID FROM Orders o 5: WHERE o. CustomerID = cu. CustomerID)
The SELECT statement in an EXISTS subquery is not important at all, because the subquery only checks whether there are rows in the table that meet the WHERE condition. If yes, TRUE is returned. If no, FALSE is returned, therefore, many EXISTS are SELECT *

When a row is de-judged, EXISTS returns TRUE, that is, yes, and the related information of this row is output.

1: select distinct name from xs 2: where not exists (3: select * from kc 4: where not exists (5: select * from xs_kc 6: where student ID = xs. student ID and course number = kc. course No)
Analyze the preceding query statement:

Obtain the Cartesian product of one row and one row in the same middle layer of a row in the exclusive xs table.

In the innermost layer, if a row in the xs_kc table has a student ID for the row to be tested and a course number for the middle layer to be tested

Returns TRUE, indicating that this student has chosen this course.

If a middle-level course does not receive the returned TRUE message, it means that the name and course match are not in the xs_kc table, or the middle-level select statements indicate that the course information is not selected by the student, if such a course exists, TRUE is returned to the outermost layer.

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

He chose all courses

The row in the outermost layer is checked. If this condition is met, the name of the row is output only once.

I have analyzed this layer by layer. Is there any other way?

2, select... Into @...

And select @... =... It should be the same

3. it seems better to rename a column with "AS" than "=". It is separated from the value assignment.

4. When using compute to summarize data, if the data is summarized based on a certain column, you must first order by this column, then compute the corresponding information, and finally by this column

In group or compute, if a column is selected, the same columns must appear in select.

The difference is that the group aggregate function is in the select row (called the selection list), while the compute aggregate function is in the compute row, and the compute can aggregate all rows without the by function.