2 ways to implement SQL Sever MYSQL view

Pre-Preparation:

1, CREATE TABLE person # Assume that this table is used to collect the basic information of the Earth people.

(ID bigint,

Name varchar (16),

Country varchar (16));

2. Create VIEW Chinise # This view is for querying so there are basic information about Chinese people.

As

Select Id,name,country from person

Where country = ' China ';

3. Find people in China called Zhang San

Select Id,name,country from Chinese where Name = ' Zhang San ';

--------------------------------------------------------------------------------------------------------------- -----------------------------------------

Method 1,

Merging algorithms

In this algorithm, the view data is only in the existence of the table, and is not separated from the table, that is to say, our query for Zhang San will be translated into

Select Id,name,country from Chinese where Name = ' Zhang San ' and country = ' China '; the engine will do it for us.

The merging of conditions. The merge algorithm internally is a mapping relationship, and if the first row of the view is on the X row of the table, the second row of view corresponds to row y of table.

Since it is a mapping, the modification to the first row of the view is the modification to table x row. Delete is the same.

That is, the merge algorithm supports insert update delete select for view;

Then

There are times when a merge algorithm is not done, that is, an INSERT update delete select for a view, and an error is thrown.

Such as: CREATE View viewxxx as Select Name, COUNT (*) from the person group by Name; so there is no one-to-one mapping, and you cannot use the merge algorithm

Method 2,

Temporal table algorithm

is to save the view query data to a temporary table, the future operation of the view is the operation of this table, even if more than one user is a table. The engine will be within the right time

Update this table,

2 ways to implement SQL Sever MYSQL view