Introduction to mysql views and why they are used and their rules and limitations

View introduction

Requires MySQL 5 MySQL 5 adds support for views. Therefore, the contents of this chapter apply to MySQL 5 and later versions.

Views are virtual tables. Unlike tables that contain data, views only contain queries that dynamically retrieve the data when used.

The best way to understand the view is to look at an example:

Input:

select cust_name ,cust_contact from customers,orders,orderitems where customers.cust_id = orders.cust_id and orderutems.order_num = order.orders.order_num and prod_id = 'TNT2';

This query is used to retrieve customers who have ordered a specific product. Anyone who needs this data must understand the structure of the related tables and know how to create queries and join the tables. In order to retrieve the same data for other products (or products), the final WHERE clause must be modified. Now, if you can wrap the entire query into a virtual table called productcustomers, you can easily retrieve the same data as follows:

Enter:

select cust_name ,cust_contact from productcustomers where prod_id = 'TNT2';

This That's what the view is for. productcustomers is a view, and as a view, it does not contain any columns or data that should be in the table, it contains a SQL query (the same query used above to join the tables correctly).

Why use views

We have already seen an example of the application of views. Below are some common applications of views.

1. Reuse SQL statements.

2. Simplify complex SQL operations. After you write a query, you can easily reuse it without knowing its underlying query details.

3. Use parts of the table instead of the entire table.

4. Protect data. A user can be granted access to a specific portion of a table rather than the entire table.

5. Change data format and representation. Views can return data that is represented and formatted differently than the underlying table.

After views are created, they can be utilized in essentially the same way as tables. You can perform SELECT operations on views, filter and sort data, join views to other views or tables, and even add and update data (there are certain restrictions on adding and updating data. More on this later).

It is important to know that a view is simply a facility for viewing data stored elsewhere. Views themselves do not contain data, so the data they return is retrieved from other tables. When you add or change data in these tables, the view returns the changed data.

Performance Issues Because the view does not contain data, every time the view is used, any retrieval required for query execution must be processed. If you create complex views with multiple joins and filters or nested views, you may find that performance degrades significantly. Therefore, before deploying an application that uses a large number of views, you should test it.

Using Views

After understanding what views are (and the rules and constraints that govern them) , let’s take a look at the creation of views.

1. Views are created using the CREATE VIEW statement.

2. Use SHOW CREATE VIEW viewname; to view the statement that creates the view.

3. Use DROP to delete the view, the syntax is DROP VIEW viewname;.

4. When updating a view, you can use DROP first and then CREATE, or you can use CREATE ORREPLACE VIEW directly. If the view to be updated does not exist, the second update statement will create a view; if the view to be updated exists, the second update statement will replace the original view.

Rules and restrictions for views

The following are some of the most common rules and restrictions regarding the creation and use of views.

1. Like tables, views must be uniquely named (views cannot be given the same name as other views or tables).

2. There is no limit to the number of views that can be created.

3. In order to create a view, you must have sufficient access rights. These limits are usually granted by the database administrator.

4. Views can be nested, that is, you can construct a view using queries that retrieve data from other views.

5.ORDER BY can be used in a view, but if the SELECT to retrieve data from the view also contains ORDER BY, the ORDER BY in the view will be overwritten.

6. Views cannot be indexed and cannot have associated triggers or default values.

7. Views can be used together with tables. For example, write a SELECT statement that joins a table and a view.

The above is the detailed content of Introduction to mysql views and why they are used and their rules and limitations. For more information, please follow other related articles on the PHP Chinese website!