MySQL Views – Why we use them

MySQL Views – introduction

In this article I am using a sample MySQL tables available here, and XAMPP – localhost standard installation package, which you should already be familiar with if you are doing anything at all in the area of LAMP stack web development.

This textbook example of MySQL Views will demonstrate basic create-view and select-view functionality, for the purpose of understanding how and where to use MySQL Views. If you want to explicitly follow the steps stated in this article please download and install these tables, and of course, you should be familiar with using XAMPP localhost server. Let’s say authorization policy of the company states that the HR department can extract agents name, commission and phone number, out of the agents table, and for the purpose of departments daily-weekly work routines. To achieve this you can create a limited View object in a MySQL database:

Please try to write the queries yourself instead of copy-pasting because this is how you will make this stick with you, and with your distracted brain ( yes, you have to leave your smartphone alone for 20 minutes ). You can see that the agents_hr_data object is now visible in database view objects, below regular table listing. Now we can execute a SELECT statement that selects all the fields from this view object and in that way, render data to the HR department only, and keep it restricted for other divisions, for example. The rest of the database can remain restricted so the HR department can only have as much insights as needed for maintaining their daily work routines.

Select from View statement

Data extracted with SELECT statement

We can extract this data now and render it in a form of HTML table with one row for each agent.

Primary mission of MySQL views – save complex queries in a database catalog

Let me tell you about one more very important tip considering MySQL views. MySQL views can significantly ease your life as a database administrator with different INNER JOIN, or similar SQL statements. Why? Well, for example, instead writing complex queries every time, you can save different JOIN queries on your server as VIEWS, and next time when you need data that those queries serve you, instead of using a query that looks like SELECT something FROM something INNER JOIN something, you can simply use SELECT FROM view query, to get the same data. This is cool, isn’t it? You store your complex SQL queries for later and use data within with simple SELECT statements.

Short overview of VIEWS and why we use them

If we use MySQL database storage for our application data management, it is recommended to use VIEWS of the original table structure instead of using the tables directly. This is how we ensure that when we reassemble DB legacy code, we will see the original schema via the view, without breaking the application. If we use VIEWS we will not have to create multiple complex SQL joins because we can put all the complexity into Views. This code will be easier to integrate with our application. This will also eliminate chances for typos and your code will be more readable. Views maintain data security so you can use them to show authorized information to authorized personnel and hide data, like credit card numbers for example. Views do not contain the data that is returned, they are just virtual tables. We can also use INSERT, UPDATE or DELETE on a VIEW. In order for latter to work, we have to create an updatable View. Updatable Views work, but we have to see that some things are left out, in order for this type of View to function. Updatable Views do not support:

• aggregate functions (MIN, MAX, SUM, AVG, and COUNT)
• DISTINCT
• GROUP BY
• HAVING
• UNION and UNION ALL
• left JOIN and outer JOIN
• reference to non-updatable view in the FROM clause
• reference only to literal values
• subquery in the SELECT clause or in the WHERE clause that refers to the table appeared in the FROM clause
• multiple references to any column of the base table
• indexes

Check if View is updatable

If we need to check if a View in a database is updatable, we can do that by querying the is_updatable column from the views table in the information_schema database:

In other systems, like PostgreSQL or Oracle, you can also create materialized Views. That kind of View allows you to store result of a submitted query physically and update the data periodically. MySQL does not utilize this type of views, however, we will cover these options and distributions in one of our future sessions. Hope you like this little excercise and happy coding.