Shulou(Shulou.com)06/01 Report--

Friends who have used Microsoft's Access mini database management system may never forget the concept of query, which is also the top priority of the second-level Access exam. Mastered the inquiry part, the second-level clearance will have an absolute assurance. One of the main reasons why queries are important is their flexibility. At the beginning of creating database tables, we can build some basic tables, but because of the complexity of practical requirements, it is difficult to design all the tables that meet all the requirements at once. for example, it is sometimes necessary to create temporary "child tables" based on large tables with a large number of fields. This so-called 'child table' can be designed and implemented using queries. In essence, this kind of query only stores the SQL statements that create these queries, and does not generate the corresponding "child tables" of separate queries. Of course, this also saves storage to some extent.

In large relational databases such as MySQL,MS SQL and Oracle, it is difficult to find the concept of query. Instead, you see the view; it is similar to a query in Access. Therefore, the position of this view in database design is also self-evident.

I. View concept

The MySQL view is a virtual table whose contents are defined by the query. Like a real table, a view contains a series of named columns and rows of data. However, the view does not exist in the database as a set of stored data values. Row and column data to freely define the table referenced by the view's query, and dynamically generated when the view is referenced.

For the underlying tables referenced in it, the MySQL view acts like a filter. Filters that define views can come from one or more tables in the current or other database, or from other views. There are no restrictions on querying through views, and there are few restrictions on data modification through them.

In fact, the view is stored in the database query sql statement, it is mainly for two reasons: security reasons, the view can hide some data, such as: social insurance fund table, you can use the view to show only the name, address, and not show the social security number and salary, etc., another reason is that it can make complex queries easy to understand and use.

In practical development, a view is generally a table derived from one or more tables or views, and its structure and data are based on queries against the table. Like tables, views include several defined data columns and multiple data rows, but in essence these data columns and rows are derived from the tables they reference.

Therefore, the MySQL view is not a real underlying table but a virtual table, and the data corresponding to the view is not actually stored in the database in the view structure, but in the table referenced by the view.

Once the view is defined, it is stored in the database, and the corresponding data is not stored in the database like the table. The data seen through the view is only the data stored in the basic table. The operation on the view is the same as on the table, which can be queried, modified (with certain restrictions), and deleted.

When the data seen through the view is modified, the data of the corresponding basic table will also change, and at the same time, if the data of the basic table changes, the change can be automatically reflected in the view.

Views have many advantages, mainly as follows:

Focus and simplify operations: the view mechanism allows users to focus on the data they care about. If the data does not come directly from the base table, you can define views to make the database look simple and clear, and simplify the user's data query operations. For example, views that define several table joins hide the join operations between tables from the user. In other words, what the user does is simply query a virtual table, and the user does not need to know how the virtual table is obtained.

Custom data: the view mechanism enables different users to view the same data in different ways, and this flexibility is necessary when many different types of users share the same database.

Views provide a degree of logical independence for refactoring the database:

The physical independence of data means that the user's application does not depend on the physical structure of the database. The logical independence of data means that when the database is reconstructed, such as adding new relationships or adding new fields to the existing relationships, the user's application will not be affected. Hierarchical database and mesh database can generally support the physical independence of data, but can not fully support logical independence.

In the relational database, the reconstruction of the database is often inevitable. The most common way to restructure a database is to divide a base table "vertically" into multiple base tables. For example: the student relationship Student (Sno,Sname,Ssex,Sage,Sdept)

It is divided into two relations: SX (Sno,Sname,Sage) and SY (Sno,Ssex,Sdept). At this time, the original table Student is the result of a natural join between the SX table and the SY table. If you create a view Student:

CREATE VIEW Student (Sno,Sname,Ssex,Sage,Sdept) AS SELECT SX.Sno,SX.Sname,SY.Ssex,SX.Sage,SY.Sdept FROM SX,SY WHERE SX.Sno=SY.Sno

In this way, although the logical structure of the database has changed (into SX and SY tables), the application does not need to modify, because the newly established view is defined as the user's original relationship, so that the user's external schema remains unchanged, and the user's application can still find data through the view.

Of course, the view can only provide some logical independence of the data, for example, because the update of the view is conditional, the statements in the application that modify the data may still change due to changes in the basic table construction.

Security: with the view mechanism, you can define different views for different users when designing database applications, so that confidential data does not appear on user views that should not be seen. In this way, the view mechanism automatically provides the security protection function for confidential data. For example, the Student table involves the student data of 15 faculties throughout the university, on which 15 views can be defined, each containing only one department's student data, and only the dean of each department is allowed to query and modify the original department's student view.

2. Create a MySQL view-- create view

Grammar

Create [or replace] [algorithm = {undefined | merge | temptable}] view [db_name.] view_name [(column_list)] as select_statement [with [cascaded | local] check option]

This statement allows you to create a view, which, given [or replace], means that when a view with the same name already exists, the original view will be overwritten. Select_statement is a query statement that can be queried from a table or other view. The view belongs to the database, so you need to specify the name of the database, if not specified, to create a new view in the current database.

Tables and databases share the same namespace in the database, so the database cannot contain tables and views with the same name, and the column names of views cannot be duplicated.

Use examples

This example creates a product table (product) and a purchase record table (purchase), and then queries the purchase details through the MySQL view purchase_detail.

Create table product (product_id int not null, name varchar (50) not null, price double not null); insert into product values (1,1,5.5); create table purchase (id int not null, product_id int not null, qty int not null default 0, gen_time datetime not null); insert into purchase values (1,1,10, now ()) Create view purchase_detail as select product.name as name, product .price as price, purchase.qty as qty, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id

After successful creation, enter: select * from purchase_detail

The running effect is as follows:

+-+

| | name | price | qty | total_value | |

+-+

| | apple | 5.5 | 10 | 55 | |

+-+

1 row in set (0.01 sec)

Points for attention are summarized:

There are the following considerations for creating a view:

(1) to run the statement to create a view, you need to have the permission to create a view (crate view). If you add [or replace], you also need to have the right to delete a view (drop view).

(2) select statements cannot contain subqueries in the from clause

(3) select statements cannot refer to system or user variables

(4) select statements cannot refer to preprocessing statement parameters.

(5) within the storage subroutine, the definition cannot refer to subroutine parameters or local variables.

(6) the table or view referenced in the definition must exist. However, after you create a MySQL view, you can discard the table or view that defines the reference. To check if there is such a problem with the view definition, use the check table statement

(7) temporary table cannot be referenced and temporary view cannot be created in the definition.

(8) the table named in the view definition must already exist

(9) the trigger cannot be associated with the view

(10) order by is allowed in the view definition, but it will be ignored if a selection is made from a particular view that uses a statement with its own order by.

III. Modify the MySQL view-- alter view

Grammar

Alter [algorithm = {undefined | merge | temptable}] view view_name [(column_list)] as select_statement [with [cascaded | local] check option] this statement is used to change the definition of an existing view. Its syntax is similar to create view.

Use examples

Modify the view purchase _ detail created in the previous section to remove the qty column, as follows:

Alter view purchase_detail as select product.name as name, product .price as price, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id

Fourth, important reference articles

Http://blog.csdn.net/lihaitao_1/article/details/51504626

Http://www.2cto.com/database/201508/427083.html

Http://www.jb51.net/article/36363.htm

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.