Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
The Language of SQL- P31:Research has shown that, being pressed for time, most readers tend to skip the introduction of any book they happen to read and then proceed immediately to the first real chapter. With that fact firmly in mind, we will only cover relatively unimportant material in the introduction, such as an explanation of what you will and will not learn by reading this book. | 136 Chapter 13 Self Joins and Views FROM Customers LEFT JOIN Orders ON Customers.CustomerID Orders.CustomerID LEFT JOIN Refunds ON Orders.OrderID Refunds.OrderID The only item missing in the above CREATE VIEW is the ORDER BY clause of the original SELECT statement. Since views aren t stored as physical data there is no point in including an ORDER BY clause in a view. Referencing Views When we execute the above CREATE VIEW statement it creates a view called CustomersOrdersRefunds. Creating the view does not return any data. It merely defines the view for later use. To use the view to bring back the same data as before we execute this SELECT statement SELECT FROM CustomersOrdersRefunds This retrieves First Name Last Name Order Date Order Amt Refund Date Refund Amt William Smith 2009-09-01 10.00 2009-09-02 5.00 Natalie Lopez 2009-09-02 12.50 NULL NULL Natalie Lopez 2009-10-03 18.00 2009-10-12 18.00 Brenda Harper 2009-09-15 20.00 NULL NULL Adam Petrie NULL NULL NULL NULL What if you only wanted to see a few columns from the view for one specific customer You could issue a SELECT statement such as SELECT First Name Last Name Order Date FROM CustomersOrdersRefunds WHERE Last Name Lopez Benefits of Views 137 The output is First Name Last Name Order Date Natalie Lopez 2009-09-02 Natalie Lopez 2009-10-03 It is important to note that when you reference columns in this view you need to specify the column alias names that were specified when the view was created. You can no longer reference original column names. For example the view assigns a column alias named First Name for the Customers.FirstName column. In Microsoft SQL Server these column aliases are enclosed in square brackets due to the embedded spaces in the names. DATABASE DIFFERENCES MySQL and Oracle As discussed in Chapter 2 MySQL and Oracle use different characters around columns containing spaces. MySQL uses the accent grave Oracle uses double quotes . Benefits of Views The previous example illustrates one of the
