MySQL must know-22nd chapter-Working with views

22nd chapter-Working with views

This chapter describes what the views are, how they work, and when to use them. We'll also see how to use the view to simplify some of the SQL operations performed in the previous section.

22.1 views

Requires MySQL 5 MySQL 5 added support for view. Therefore, the contents of this chapter apply to MySQL 5 and later versions. The view is a virtual table. Unlike a table that contains data, a view contains only queries that retrieve data dynamically when used. The best way to understand a view is to look at an example. In the 15th chapter, the following SELECT statement is used to retrieve data from 3 tables:

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 tables. In order to retrieve the same data for another product (or multiple products), the last WHERE clause must be modified. Now, if you can wrap the entire query into a virtual table named Productcustomers, you can easily retrieve the same data as follows:
Select Cust_name,cust_contact from productcustomers where prod_id= ' TNT2 ';
This is how the view works. Productcustomers is a view that, as a view, does not contain any columns or data that should be in the table, it contains an SQL query (the same query as the one above that correctly joins the table).

22.1.1 Why use views

We've seen an example of the view application. Here are some common applications for views.

• Reuse SQL statements.
• simplifies complex SQL operations. After writing a query, it is easy to reuse it without having to know its basic query details.
• Use parts of the table instead of the entire table.
• Protect data. You can give a user access to a specific part of a table rather than an entire table.
• Change the data format and representation. Views can return data that differs from the representation and format of the underlying table. After the view has been created, you can use them in the same way as the tables. You can perform a select operation on the view, filter and sort the data, connect the views to other views or tables, and even add and update data (there are some limitations to adding and updating data. A further introduction to this content will be made later). It is important to know that a view is just a facility for viewing data stored elsewhere. The 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, each time you use the view, you must handle any of the searches that are required to execute the query. If you create complex views or nested views with multiple joins and filters, you may find that performance drops very badly. Therefore, you should test before deploying an application that uses a large number of views.

Rules and restrictions for 22.1.2 views

Here are some of the most common rules and limitations for creating and using views.

• As with tables, the view must be uniquely named (You cannot give the view the same name as another view or table).
• There is no limit to the number of views that can be created.
• In order to create a view, you must have sufficient access rights. These limits are typically granted by database administrators.
• Views can be nested, that is, you can construct a view with queries that retrieve data from other views.
• The order by can be used in the view, but if the data is retrieved from the view the select also contains an order by, then the order by in the view is overwritten.
• Views cannot be indexed or have associated triggers or default values.
• Views can be used with tables. For example, write a SELECT statement that joins tables and views.

22.2 Using views

After understanding what views are (and managing their rules and constraints), let's look at the creation of views.

• The view is created with the CREATE VIEW statement.
• Use show create VIEW viewname to view the statements that created the views.
• Delete the view using drop, whose syntax is drop view viewname;.
• When you update a view, you can use drop again with create, or you can use Create or REPLACE view directly. If the view that you want to update does not exist, the 2nd UPDATE statement creates a view, and if the view that you want to update exists, the 2nd UPDATE statement replaces the original view.

22.2.1 using views to simplify complex joins

One of the most common applications for views is to hide complex SQL, which usually involves joins. Take a look at the following example:

This statement creates a view named Productcustomers that joins three tables to return a list of all customers who have ordered any product. If you execute select * from Productcustomers, customers who have ordered any product are listed. To search for customers who have ordered product TNT2, the following can be done:

This statement retrieves specific data from the view through a WHERE clause. When MySQL processes this query, it adds the specified where clause to the existing WHERE clause in the view query in order to properly filter the data. As you can see, the view greatly simplifies the use of complex SQL statements. With views, you can write the underlying SQL once and then use it as many times as you want. Creating a reusable view creates a view that is not limited by specific data is a good idea. For example, the view created above returns customers who produce all products, not just those who produce TNT2. Extending the scope of the view not only makes it reusable, it is even more useful. Doing so does not require creating and maintaining multiple similar views.

22.2.2 to reformat the retrieved data with a view

As mentioned above, another common use of views is to reformat the retrieved data. The following SELECT statement (from the 10th chapter) returns the vendor name and location in a single combined computed column:

Now, if the result of this format is often required. Instead of performing a junction every time you need to, create a view that you can use every time you need it. To convert this statement into a view, you can do this as follows:

This statement creates a view using the same query as the previous SELECT statement. To retrieve the data to create all mailing labels, you can do the following:

22.2.3 using views to filter unwanted data

Views are also useful for applying a common where clause. For example, you can define a customeremaillist view that filters customers without an e-mail address. For this purpose, you can use the following statement:

Obviously, when sending an e-mail to a mailing list, you need to exclude users who do not have an e-mail address. The WHERE clause here filters those rows in the Cust_email column that have null values so that they are not retrieved. You can now use the view customeremaillist as you would with other tables.

WHERE clause and WHERE clause if a WHERE clause is used when retrieving data from a view, the two sets of clauses (one in the view and the other in the view) are automatically combined.

22.2.4 using views and calculated fields

Views are particularly useful for simplifying the use of calculated fields. The following is a SELECT statement that is described in the 10th chapter. It retrieves the items in a particular order and calculates the total price for each item:

To convert it to a view, proceed as follows:

Analysis output 21321422.2 Use view 161 to retrieve the details of order 20005 (above output), as follows:

As you can see, the view is very easy to create and very good to use. When used correctly, views can greatly simplify complex data processing.

22.2.5 Update View

All views so far have been used with the SELECT statement. However, can the data of the view be updated? The answer will depend on the circumstances. In general, views are updatable (that is, you can use INSERT, UPDATE, and delete for them). Updating a view will update its base table (recall that the view itself has no data). If you add or delete rows to a view, you are actually adding or deleting rows to their base tables. However, not all views are updatable. Basically, if MySQL does not correctly determine the base data being updated, updates (including insertions and deletions) are not allowed. This actually means that if you have the following actions in the view definition, you cannot update the view:

• Grouping (using GROUP by and having);
• Coupling
• Sub-query;
• and
• Aggregation function (Min (), Count (), Sum (), etc.);
• DISTINCT;
• Export (computed) columns. In other words, the views in many examples of this chapter are not updatable. This sounds like a serious limitation, but it's not, because the view is primarily used for data retrieval. The limits listed above for possible changes are correct since MySQL 5. However, future MySQL is likely to remove certain restrictions. To use a view for retrieval, you should use the View for retrieval (SELECT statements) and not for updates (INSERT, update, and delete).

22.3 Summary

The view is a virtual table. They contain queries that are not data but that retrieve data as needed. The view provides a hierarchical package of MySQL SELECT statements that can be used to simplify data 216 processing and reformat the underlying data or protect the underlying data.

MySQL must know-22nd chapter-Working with views