SQL Server Learning Note Series 7

First, write it in front.

In the blink of an eye is Monday, recalling the days of double Hugh, short and happy, in the sunny afternoon, can do their own to do anything, comfortable comfort, or reading, or sports, or music, when we calm down to slowly feel these, will suddenly find that the original life is so happy! Something to ask for, something to feel, enough! The simple life is actually the most luxurious enjoyment! Forget the unhappy things, do their own life, get rid of trouble, hope that the garden friends, can have a good mood, not liking time, go out, let the mood walk boundless, empty themselves! Believe that good things will happen!

Two. View

Views can be viewed as defined in SQL Virtual tables on the server. The view contains a set of result sets for the query. The general view itself does not store the actual data, but only the metadata of a SELECT statement and the table involved. Using views, you can combine data from multiple tables according to our needs, and once the view is established, it is always available and can be reused.

For example, if we are looking for information about a U.S. customer, then we can create a view, each time just query the U.S. customer information, as long as the view name query on it! Similarly, views need to be defined and queried first. SQL is as follows:

1 CREATE VIEW usa_cusomers2 as 3 (4    SELECT * from 5    sales.customers6    WHERE country= ' USA ' 7)

After the definition is complete, execute the SQL, then the command is created, and then the SQL query statement can be used to query the U.S. customer information.

1 SELECT custid,country from dbo. Usa_cusomers;

Execution Result:

At the same time, we can see in the SQL Object Explorer-----that you have added a name called dbo. Usa_cusomers view, you can iterate through this view for querying.

If you want to delete this view, you can use the SQL statement drop to do the following:

1 DROP VIEW dbo. Usa_cusomers;

Three. Set operation

Collection budget master is used to manipulate the results of a query. The collection (Union), intersection (Intersect), and Difference (Except) are the same as the set operations in mathematics.

(1) A collection of two or more result sets that are queried for presentation.

For example, if we want to query all the country information in the Customer table (sales.customers) and the employee table (hr.employees), then we need to look up all the countries in the Customer table and then query all the countries in the employee table and merge the two result sets. SQL is as follows:

1 Select Country2 from Sales.customers3 UNION ALL4 SELECT country5 from HR. Employees;

Note that the union ALL is used here, so what if the union result is used?

1 Select Country2 from Sales.customers3 UNION 4 SELECT country5 from HR. Employees;

You can see that the result set is different after using union all and union, which is a little bit of a fact:

UNION ALL does not go heavy and contains all the results.

The Union de-weighs, showing the result set of the de-weight later. So the above result set shows 21, which is the result of the de-weight.

(2) intersection, showing the intersection of two or more result sets of the query. It contains the same portion of two result sets.

We also use the intersection of all the national information in the Customer table (sales.customers) and the employee table (hr.employees) to see which countries have both customers and employees. SQL is as follows:

1 Select Country2 from SALES.CUSTOMERS3 intersect 4 SELECT country5 from HR. Employees;

One need to note is: Intersect to seek the intersection, is also the result of the weight.

(3) Difference set, combining the difference set of two or more result sets queried, to find out the result set that one of the collections does not exist in another collection.

For example, we find out which parts of the employee's table (Hr.employees) are not in the customer's table (Sales.customers), that is, to find out which countries have customers without employees. SQL is as follows:

1 Select Country2 from Sales.customers3 EXCEPT 4 SELECT country5 from HR. Employees;

Four. Pivot (pivot)

The so-called perspective, that is, the table transpose, in the database operation, sometimes we encounter the need to achieve "row to column" requirements, statistics of each phase or each quarter, the number of weeks per week, the database may be stored in a row of data format, then we need to use a column to show the specific statistical situation, Perspective (Pivot) is required at this time.

Here we first create a table dbo.orders and insert some data into the table.

1 IF object_id (' dbo.orders ', ' U ') is not NULL 2 DROP TABLE dbo.orders; 3 CREATE TABLE dbo.orders 4 (5    orderid int NOT null  PRIMARY KEY, 6    Empid int. NOT NULL, 7    CustID int. not N ULL, 8    OrderDate datetime, 9    qty int); INSERT into Dbo.orders (orderid,empid,custid,orderdate,qty) VALU ES (30001,3,1, ' 20070802 ', ten),        (30002,2,4, ' 20070601 ', "), (         10001,4,5, ' 20070802 ', +), (         20001,5,2, ' 20070802 ', (+), (         40001,3,2, ' 20070802 ', "), (         30006,5,6, ' 20070802 ',         30008,4,8, ' 20070802 ',         (60001,6,1, ' 20070802 ', 70)

1 SELECT * from Dbo.orders

The query table data is:

Now if there is a demand for the amount that each customer consumes, according to the conventional idea, we can group according to the customer ID, then sum with aggregate function sum, the SQL statement is as follows:

1 SELECT empid,sum (qty) as N ' Customer consumption amount ' 2 from Dbo.orders3 GROUP by Empid;

But now we want to change the amount of customer consumption into a column, that is, the row to column, how to think about it, here we first in the traditional way, use the case when, and then according to the case when the condition of the sum. The SQL statements are as follows:

1 SELECT empid,2 sum (case when empid=2 then qty end) as n ' 2nd customer consumption amount ', 3 SUM (case when Empid=3then qty end) as n ' 3rd customer consumption amount ', 4 SUM (case when empid=4 then qty end) as n ' 4th customer consumption amount ', 5 sum (case when empid=5 then qty end) as n ' 5th customer consumption amount ', 6 sum (case when EM Pid=6 then Qty end) as N ' 6th customer consumption amount ' 7 from Dbo.orders8 GROUP by Empid;

The result of its execution

As you can see from the execution result graph, the customer ID has been converted to a single line, and each customer's consumption is visible in the column. But here we take a new way, it seems to be more useful, that is the pivot way. But pivot helps us do a lot of conversion work, and here we only focus on how pivot works. For the above requirements, we can use pivot in this way:

1 Select Empid,[1],[2],[4],[6],[8] 2  from 3 (   4     -return only columns used in pivot 5    SELECT Empid,qty,custid 6    from Dbo.orders 7) as-T 8 PIVOT (9      SUM (t.qty) for T.custid in ([1],[2],[4],[6],[8])--do column name) as P

I hope that you Daniel give guidance, inappropriate to accept learning! Thank you!

SQL Server Learning Note Series 7