Shulou(Shulou.com)05/31 Report--

How to create a view in MYSQL? for this question, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

I. View Overview

A view is a virtual table whose contents are defined by a 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 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.

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, but not the social security number and salary, etc., another reason is that it can make complex queries easy to understand and use.

View: how to view a drawing or document.

A view is 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.

So the view is not a real basic 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:

Viewpoint concentration

Simplify operation

Customized data

Merge split data

Security.

2. Create a view-- create view

1. 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.

two。 Use examples

Eg. This example creates a product table (product) and a purchase record table (purchase), and then queries the purchase details through the 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, 'apple', 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)

3. Matters needing attention

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 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 view-- alter view

1. 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.

two。 Use examples

Eg. 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

When you query the view with the statement: select * from purchase_detail;, the result is as follows:

+-+

This is the answer to the question about how to create a view in MYSQL. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel for more related knowledge.

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.