Discussion on MySQL view usage

< a > View
1. Advantages

• Viewpoint Focus

• Simplify operations

• Custom data

• Merge split data

• Security

2. Why do I need a view?

A view is an SQL statement of a query stored in a database, and it is primarily for two reasons:

• For security reasons, views can hide some data, such as social insurance fund tables, which can be displayed with a view that displays only names, addresses, and does not show the number of public numbers and wages, etc.

• Make complex queries easy to understand and use.

3. Syntax for creating views

CREATE VIEW view_name as SELECT column_name (s) from table_name WHERE condition

[Note]: The view always displays the most recent data. Each time a user queries the view, the database engine rebuilds the data by using SQL statements.

4. Updating the syntax of a view

CREATE OR REPLACE VIEW view_name as SELECT column_name (s) from table_name WHERE condition

5. Syntax for deleting views

DROP VIEW view_name

< two > Example 1. Create two tables and insert data

Product table

CREATE TABLE product (product_id int NOT NULL, name varchar () is not NULL, and price double is not null); INSERT into product values (1, ' Apple ', 5.5);

Purchase table

CREATE table purchase (ID int not NULL, PRODUCT_ID int. NOT NULL, QTY Int. NOT NULL default 0, Gen_time datetime not n  ULL); INSERT into purchase values (1, 1, ten, Now ());

2. Create a view:

CREATE View Purchase_detail as select Product.name as name, Product.price as Price, Purchase.qty as qty, Product.price * p Urchase.qty as Total_valuefrom product, purchase where product.product_id = purchase.product_id;

3. After creating the view, query:

SELECT * from Purchase_detail;

4. Results:

< three; considerations for creating views

Precautions

• Running the statement that creates the view requires that the user has permission to create the view (Crate view), and if [or replace] is added, the user has permission to delete the view (drop view);

• The SELECT statement cannot contain subqueries from the FROM clause;

• The SELECT statement cannot refer to a system or user variable;

• The SELECT statement cannot reference a preprocessing statement parameter;

• Within a stored subroutine, the definition cannot refer to subroutine parameters or local variables;

• The table or view referenced in the definition must exist. However, after you create the MySQL view, you can discard the table or view that defines the reference. To check whether the view definition has such problems, you can use the Check table statement;

• The temporary table cannot be referenced in the definition, and the temporary view cannot be created;

• A table named in the view definition must already exist;

• You cannot associate a trigger with a view;

• The order by is allowed in the view definition, but it is ignored if a selection is made from a specific view that uses a statement with its own order by.

< four; modifying a view

Using the ALTER view

Alter VIEW Purchase_detail as select Product.name as name, Product.price as Price, Product.price * Purchase.qty as Total_v Alue from product, purchase where product.product_id = purchase.product_id;

Reference: http://database.51cto.com/art/201005/200526.htm

Discussion on MySQL view usage