SQL Getting Started Classic (vi) view

A view is actually a storage query that focuses on the ability to mix and match data from a base table (or other view) to create the same effect in many ways as another normal table. You can create a simple query that selects only a few columns or rows from one table (another view) and ignores other rows or columns, or you can create a complex query. Connect several table queries to make these connection queries look more like a table.

The use of a view is often either too much or not enough-rarely happens. After learning the view, you should be able to use the view to achieve the following:

1. Reduce significant data complexity for end users.

2. Prevent sensitive columns from being selected, but still provide access to other data.

3. Adding additional indexes to the database improves query performance-even in the view on which the index is not being used.

View basic Syntax : CREATE view <view name> as <select statement>. It's not as simple as it looks. The above syntax, of course, represents the most simplified syntax for most. It's enough. The syntax for the extension is as follows: CREATE VIEW <view name> [with [encryption][,] schmabinding][[,] view_metadata] as <selete statement> [With CHECK OPTION] [;]

Each of the above code is described below, but the simplest view is shown first. Extremely simple view.

Use AdventureWorks--we then used AdventureWorks This database create VIEW Vw_contactphone--Create a view that returns the user's phone Asselect (firstname+ '. ') +lastname) as Username,phone from  person.contact--The command has been successfully completed. SELECT * FROM Vw_contactphone-use view (looks like query table)

Creating a view does not really change anything. Just by filtering the data that is being accessed, the benefit is to reduce data complexity for end users, and there are a lot of tools in the current era that make it easier for users. This doesn't look great, but it does reduce complexity for the user.

Note: be aware that, by default, there is no special need to do things for views. A view is like a command line that executes a query command like a cloud letter. There is no pre-optimized process. This means that an additional layer of overhead is provided between the request for the data and the data being delivered, and the view is always running faster than the directly running internal SELECT statement. That is, the view, the existence of a view for a reason, that is, for the user is safe and simplified. So find a balance between demand and system overhead to fit a particular situation

To use the filter view:

SELECT * from Vw_contactphone WHERE Phone like ' 334% '--With previous CREATE view, query area code start is%334 number (10 results)

use more complex views : Managers want simple queries to understand how customers order those orders, which are placed over those parts. And the account that was ordered. Here is a view that creates a very simple query.

Use Adventureworksgocreate VIEW Vw_customerordersasselect SC. Accountnumber,soh. Salesorderid,soh. Orderdate,sod. Productid,pp. Name,sod. Orderqty,sod. UnitPrice, (Sod. Unitpricediscount*sod. Unitprice*sod. OrderQty) as Tatoldiscount,sod. Linetotalfrom Sales.Customer as Scinner join Sales.SalesOrderHeader as Soh on SC. Customerid=soh. Customeridinner join Sales.SalesOrderDetail as sod on Soh. Salesorderid=sod. Salesorderidinner join Production.Product as PP on sod. Productid=pp. ProductID

There is no need for too much training, and managers can get the data they want.

Restricts the content that is inserted into the view by using the with CHECK OPTION.

With CHECK option is one of the lesser known features in SQL Server. The rules are simple-in order to use views to update or insert data. The result must conform to display in the view results. Again, the inserted or updated row must satisfy the use where condition in the SELECT statement in the view. There is no limit to the base table.

Use pandagocreate VIEW vw_test003as select Id,orderdate,name from dbo.test003 where test002id between 4 and 6with check op The tion--command was successfully completed. Where must be, represents insert,delete,update to meet the conditions to execute the command does not terminate--what happens when you add data and update data under test select name from dbo.test003 where id=7--query data name =testupdate vw_test003 set name= ' Panda_test ' where id=7-(1 rows affected, test002id=4) indicates a successful modification because test002id satisfies test002id between 4 and 6 Update vw_test003 set name= ' Panda_test2 ' where id=8-(0 affected) condition does not satisfy the where. Back Delete,insert look at Insert vw_test003 values (getdate (), ' 221 ');--MSG 550, Level 16, State 1, line 1th attempted insert or update has failed, The reason is that the target view or a view that spans the target view specifies with CHECK option, and one or more result rows of the operation do not conform to the CHECK option constraint. Statement has been terminated. Why is it? Because of foreign key constraints. Let's change the view. ALTER VIEW vw_test003as Select Id,orderdate,name,test002id from dbo.test003 where test002id between 4 and 6with check Opti On--Modify completion in Add data, insert vw_test003 values (' 2016-6-25 ', ' 221 ', 4);--(1 results indicate success) DELETE requires condition and satisfies condition

Data for the first query

Data after a few executions

Delete view: Drop view <view name>,[<view name>,<......,n>];

Let the view and table create the index. Only add: With SCHEMABINDING in front of AS

Create View Vw_contactphoneindex-Creates a view that can be indexed back to the user's phone with schemabindingasselect (firstname+ '. ' +lastname) as Username,phone from Person.Contact--the command has been successfully completed.

Protect code Encryption : only need to add: with encryption in front of AS, remember to back up your own data.

Create View Vw_contactphoneencry-Creates a view of the user's phone with encryptionasselect (firstname+ '. ') +lastname) as Username,phone from Person.Contact--the command has been successfully completed. Goexec sp_helptext Vw_contactphoneencry--The text of the object ' Vw_contactphoneencry ' is encrypted.

SQL Getting Started Classic (vi) view