MariaDB Crash Course - Ben Forta [67]
• ORDER BY may be used in a view, but it will be overridden if ORDER BY is also used in the SELECT that retrieves data from the view.
• Views cannot be indexed, nor can they have triggers or default values associated with them.
• Views can be used in conjunction with tables, for example, to create a SELECT statement, which joins a table and a view.
Using Views
So now that you know what views are (and the rules and restrictions that govern them), let’s look at view creation:
• Views are created using the CREATE VIEW statement.
• To view the statement used to create a view, use SHOW CREATE VIEW viewname;.
• To remove a view, the DROP statement is used. The syntax is simply DROP VIEW viewname;.
• To update a view you may use the DROP statement and then the CREATE statement again, or just use CREATE OR REPLACE VIEW, which creates the view if it does not exist and replaces it if it does.
Using Views to Simplify Complex Joins
One of the most common uses of views is to hide complex SQL, and this often involves joins. Look at the following statement:
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 a list of all customers who have ordered any product. If you were to SELECT * FROM productcustomers, you’d list every customer who ordered anything.
To retrieve a list of customers who ordered product TNT2, you can do the following:
Input
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';
Output
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
Analysis
This statement retrieves specific data from the view by issuing a WHERE clause. When MariaDB processes the request, it adds the specified WHERE clause to any existing WHERE clauses in the view query so the data is filtered correctly.
As you can see, views can greatly simplify the use of complex SQL statements. Using views, you can write the underlying SQL once and then reuse it as needed.
* * *
Tip: Creating Reusable Views
It is a good idea to create views that are not tied to specific data. For example, the view created in this example returns customers for all products, not just product TNT2 (for which the view was first created). Expanding the scope of the view enables it to be reused, making it even more useful. It also eliminates the need for you to create and maintain multiple similar views.
* * *
Using Views to Reformat Retrieved Data
As mentioned previously, another common use of views is for reformatting retrieved data. The following SELECT statement (from Chapter 10, “Creating Calculated Fields”) returns vendor 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
+-------------------------+
| vend_title |
+-------------------------+
| ACME (USA) |
| Anvils R Us (USA) |
| Furball Inc. (USA) |
| Jet Set (England) |
| Jouets Et Ours (France) |
| LT Supplies (USA) |
+-------------------------+
Now suppose that you regularly needed results in this format. Rather than perform the concatenation each time it was needed, you could create a view and use that instead. To turn this statement into a view, you can do the following:
Input
CREATE VIEW vendorlocations 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 exact same query as the previous SELECT statement. To retrieve the data to create all mailing labels, simply do the following:
Input
SELECT *
FROM vendorlocations;
Output
+-------------------------+
| vend_title |
+-------------------------+
| ACME (USA)