Detailed explanation of the role of MySQL views (1) - simplifying complex connections and formatting retrieved data

Use views to simplify complex joins

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

Input:

create view productcustomers as select cust_name,cust_contact,prod_id from customers,orders,orderitems where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num;

Analysis: This statement creates a view named productcustomers, which joins three tables to return any products that have been ordered. A list of all customers. If you execute SELECT * FROM productcustomers, you will list customers who have ordered any product.

To retrieve customers who have ordered product TNT2, proceed as follows:

Input:

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

Output:

Analysis: This statement retrieves specific data from the view through the WHERE clause. As MySQL processes this query, it adds the specified WHERE clause to the existing WHERE clauses in the view query so that the data is filtered correctly.

It can be seen that views greatly simplify the use of complex SQL statements. Views allow you to write basic SQL once and then use it as many times as needed.

Create reusable views It is a good idea to create views that are not restricted to specific data. For example, the view created above returns customers who produce all products and not just customers who produce TNT2. Extending the scope of a view not only makes it reusable, but is even more useful. Doing so eliminates the need to create and maintain multiple similar views.

Use views to reformat retrieved data

As mentioned above, another common use of views is to reformat retrieved data. The following SELECT statement returns the supplier name and location in a single combined calculated column:

Input:

select concat(rtrim(vend_name),'(',rtrim(vend_country), ')') as vend_title from vendors order by vend_name;

Output:

Now, If the results in this format are often needed. Instead of performing a join every time you need it, create a view and use it every time you need it. To convert this statement into a view, proceed as follows:

Input:

create view vendorlocation as select concat(rtrim(vend_name),'(',rtrim(vend_country), ')') as vend_title from vendors order by vend_name;

Analysis: This statement creates a view using the same query as the previous SELECT statement. To retrieve the data to create all mailing labels, proceed as follows:

Input:

select * from vendorlocations;

Output:

The above is the detailed content of Detailed explanation of the role of MySQL views (1) - simplifying complex connections and formatting retrieved data. For more information, please follow other related articles on the PHP Chinese website!