MariaDB Crash Course - Ben Forta [66]
Summary
In this chapter, you learned several new SQL statements. CREATE TABLE is used to create new tables, ALTER TABLE is used to change table columns (or other objects like constraints or indexes), and DROP TABLE is used to completely delete a table. These statements should be used with extreme caution, and only after backups have been made. You also learned about database engines, defining primary and foreign keys, and other important table and column options.
22. Using Views
In this chapter you learn exactly what views are, how they work, and when they should be used. You also see how views can be used to simplify some of the SQL operations performed in earlier chapters.
Understanding Views
Views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.
The best way to understand views is to look at an example. Back in Chapter 15, “Joining Tables,” you used the following SELECT statement to retrieve data from three tables:
Input
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';
That query was used to retrieve the customers who had ordered a specific product. Anyone needing this data would have to understand the table structure, as well as how to create the query and join the tables. To retrieve the same data for another product (or for multiple products), the last WHERE clause would have to be modified.
Now imagine that you could wrap that entire query in a virtual table called productcustomers. You could then simply do the following to retrieve the same data:
Input
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';
This is where views come into play. productcustomers is a view, and as a view, it does not contain any actual columns or data as a table would. Instead, it contains a SQL query—the same query used previously to join the tables properly.
Why Use Views
You’ve already seen one use for views. Here are some other common uses:
• To reuse SQL statements.
• To simplify complex SQL operations. After the query is written, it can be reused easily, without having to know the details of the underlying query itself.
• To expose parts of a table instead of complete tables.
• To secure data. Users can be given access to specific subsets of tables instead of to entire tables.
• To change data formatting and representation. Views can return data formatted and presented differently from their underlying tables.
For the most part, after views are created, they can be used in the same way as tables. You can perform SELECT operations, filter and sort data, join views to other views or tables, and possibly even add and update data. (There are some restrictions on this last item. More on that in a moment.)
The important thing to remember is views are just that, views into data stored elsewhere. Views contain no data themselves, so the data they return is retrieved from other tables. When data is added or changed in those tables, the views will return that changed data.
* * *
Caution: Performance Issues
Because views contain no data, any retrieval needed to execute a query must be processed every time the view is used. If you create complex views with multiple joins and filters, or if you nest views, you may find that performance is dramatically degraded. Be sure you test execution before deploying applications that use views extensively.
* * *
View Rules and Restrictions
Here are some of the most common rules and restrictions governing view creation and usage:
• Like tables, views must be uniquely named. (They cannot be named with the name of any other table or view.)
• There is no limit to the number of views that can be created.
• To create views, you must have security access. This is usually granted by the database administrator.
• Views can be nested; that is, a view may be built using a query that retrieves data from another view.