Three-layer View

Level 1:

This is the simplest understanding. Anyone who has learned the view knows that the view is a virtual table, but the data of the original table can be modified through the view.

For example, there is a userinfo table.

Uname upwd usex

Zhang San zhangsan 1

View_user can be created.

Create view view_user as select * From userinfo

If you execute update view_user set uname = 'aaa', you will find that all uname in the userinfo table is changed to AAA

Layer 2:

Further understanding of views: not all views can be modified, and some views are read-only.

For example, another table is sex.

Sexid sexname

1 male

2 female

If you simply view the userinfo table, the gender of Table 3 is 1. Therefore, you need to associate the table with the sex table to view the table.

Create view view_user as select U. uname, U. upwd, S. sexname from userinfo as U, sex as s where u. usex = S. sexid

In this case, view_user is associated with two tables and is read-only and cannot be modified.

Layer 3:

Assume that there is a commodity table merchandise,

Mid (product no.) modeid (FORMAT No)

54226782542 1

Another item format table mode

Mid (format number), mname (format name)

1 VCD

2 DVD

To query the format of a product, you must join the product.

Select M. mid, Mo. mid, Mo. mname from merchaidise as M, mode as Mo where M. modeid = Mo. Mid

Result:

Mid (product number) modeid (format number) mname (format name)

54226782542 1 VCD

Query successful

However, you cannot directly create the preceding statement as a view because the mid column in the mode table conflicts with the mid column in the merchandise table.

Maybe you will ask: Didn't I use M and Mo to separate the two columns? Why?

The answer is no, because Microsoft can write the where condition when querying the view. If the above problem works, execute:

When select * From view_user where mid = 1, is mid a product number or a format number?

Therefore, when creating a multi-table join view, the columns cannot be repeated.