What is MySQL Views: 9 Important Aspects

This blog talks about the different aspects of MySQL View Tables and MySQL View Databases in great detail. This includes the processing algorithms leveraged by MySQL to process views and operations like drop, update, show/list, to name a few. It also gives a brief introduction to MySQL before jumping into the crux of the discussion.

MySQL is a Relational Database Management System. This Open-source tool is one of the best RDBMS available in the market that is being used to develop web-based software applications among others. MySQL is scalable, intuitive, and swift when compared to its contemporaries. It houses a Client-Server architecture. At the core of the MySQL Database lies the MySQL Server. Read our article about replicate MYSQL database. This Server is availed as a separate program and handles all the database instructions, commands, and statements.

Table of Contents

• What is MySQL?
  • Key Features of MySQL
• What is a MySQL View?
  • Syntax of MySQL Views
• How Does MySQL Process Views: 3 Processing Algorithms
  • Merge
  • Undefined
  • TempTable
• Why Should you use a MySQL View?
• How to Create Views in MySQL?
  • Create a View using CREATE VIEW Command
  • Create a View using WITH CHECK Statement
  • Create a View using MySQL Workbench
• How to Update a MySQL Table View?
• How to Drop a MySQL View?
• How to Rename a MySQL View?
• How to Use MySQL Views with Joins?
• How to List/Show MySQL Views?
• Conclusion

What is MySQL?

MySQL was used by more than 39% of developers in the world back in 2019, making it the world’s most popular Database. It certainly lacks the extensive features that are provided by PostgreSQL, but it is still useful for a large number of use cases like web applications. 

Since it comes standard in the LAMP Stack, where LAMP stack is an Open-Source suite of web applications that consists of Linux, Apache HTTP Server, MySQL, and PHP; MySQL is the go-to choice for scalable web applications. Let’s talk about a few salient features that make MySQL such a great catch. Also, see how you can integrate from MySQL to PostgreSQL. 

Key Features of MySQL

• Maintained by Oracle: Oracle owns and maintains MySQL. It also offers premium versions of MySQL with additional services, proprietary plugins, user support, and extensions. 
• Long History: MySQL has been around for over 20 years since its first release in 1995.
• Frequent Updates: MySQL is made more robust with frequent updates with new features and security improvements. The latest release is Version 8.0.23 released on 18 January 2021.
• MVCC Features: MySQL recently started offering MVCC (Multi-Version Concurrency Control) features. 
• A Supportive Community: A devoted community of developers is available to help with troubleshooting as and when needed.
• Open-Source: MySQL is also a Free and Open-Source Relational Database Management System (RDBMS).
• Users: MySQL is used widely by Google, NASA, Flickr, GitHub, Netflix, Tesla, Twitter, Uber, Wikipedia, YouTube, and Zendesk to name a few.

What is a MySQL View?

MySQL Views are defined as the virtual tables that can be generated by the query output. These MySQL Views are considered objects, so they can be easily queried by leveraging the SELECT statement. MySQL Views do not store the physical data on the database. When you run a SELECT statement on a database view, it will carry out the query and populate the data from the underlying tables utilized to create MySQL Views. 

Syntax of MySQL Views

In MySQL, the basic syntax for creating a view is as follows:

CREATE VIEW `view_name` AS SELECT statement;

The parameters are:

• CREATE VIEW: The command “CREATE VIEW ‘view name'” instructs MySQL to create a view object named ‘view name’ in the database.
• AS SELECT: The SQL statements to be packed in the MySQL Views are referred to as “AS SELECT statements.” A SELECT statement can include data from one or more tables.

How Does MySQL Process Views: 3 Processing Algorithms

Here are three processing algorithms that can be leveraged to process MySQL Views:

• Merge
• Undefined
• TempTable

Merge

When you query from a MERGE MySQL View, MySQL processes the steps mentioned below:

• First, merge the input query with the SELECT statement within the definition of MySQL View into a single query.
• Second, carry out the combined query to return the result set.

Here, the combination of the input query and the SELECT statement of the definition of MySQL View into a single query is defined as View Resolution. This is what the syntax for CREATE VIEW with the MERGE clause looks like:

CREATE [OR REPLACE][ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] VIEW 
   view_name[(column_list)]
AS 
   select-statement;

You can use the following query to create MySQL Views based on the customer’s table bearing the name contactPersons with the MERGE algorithm:

CREATE ALGORITHM=MERGE VIEW contactPersons(
    customerName, 
    firstName, 
    lastName, 
    phone
) AS
SELECT 
    customerName, 
    contactFirstName, 
    contactLastName, 
    phone
FROM customers;

Now, say you display the following statement:

SELECT * FROM contactPersons
WHERE customerName LIKE '%Co%';

Now, MySQL will execute the following steps for the aforementioned query:

• MySQL will first convert the View name contactPersons to the table name customers.
• Asterisk (*) will get converted to a list of column names lastName, firstName, customerName, and phone which will correspond to contactLastName, contactFirstName, customerName, and phone.
• Finally, MySQL will add the WHERE clause to the query.

This is what the resulting statement would look like:

SELECT 
    customerName, 
    contactFirstName, 
    contactLastName, 
    phone
FROM
    customers
WHERE
    customerName LIKE '%Co%';

Undefined

This algorithm is the default algorithm that gets applied when you create a view without explicitly mentioning the algorithm clause. UNDEFINED also comes into the picture if you use the algorithm clause as ALGORITHM=UNDEFINED. This clause allows MySQL to choose from the TEMPTABLE and MERGE clauses. MySQL usually picks the MERGE clause whenever possible, since MERGE is more efficient than the TEMPTABLE clause.

TempTable

When you call a query to a TEMPTABLE MySQL View, here are the steps that are executed:

• First, a temporary table is created to store the result of the SELECT statement in the MySQL View definition.
• Next, the input query is executed against the temporary table.

This algorithm is considered less efficient than the MERGE algorithm because MySQL will have to create a temporary table here to store the result set and migrate the required data from the base table to the temporary table. You cannot update the TEMPTABLE MySQL Views either.

Why Should you use a MySQL View?

Here are a few pivotal reasons why creating MySQL Views might be handy for your business operations:

• Increased Reusability: By leveraging MySQL Views, you can greatly simplify complex queries, turning them into a single line of code. This code can then be seamlessly integrated with your application thus getting rid of the cumbersome effort required in writing the same formula in your query over and over again. This allows the code to be more readable and reusable.
• Simplification of Complex Queries: By using MySQL Views, users can simplify the most sophisticated MySQL queries. If you are using a complex query for a specific purpose, you can create MySQL Views based on it to use a simple SELECT statement as opposed to typing out the intricate query yet again.
• Enabling Backward Compatibility: You can also use MySQL Views to enable backward compatibility within legacy systems. For instance, say you want to dissect a large table into several smaller ones without impacting the current applications that refer to the table. In this scenario, you can create MySQL Views with the same name as the real table so that the current applications can refer to the View as if it were a table. 
• Robust Data Security: You can leverage MySQL Views to depict only authorized information to the users and conceal pivotal data like banking and personal information. You can limit which information can be accessed by your consumers by authoring only the necessary data for them.

How to Create Views in MySQL?

Here are the different ways you can utilize MySQL Create Views:

• Create a View using CREATE VIEW Command
• Create a View using WITH CHECK Statement
• Create a View using MySQL Workbench

Create a View using CREATE VIEW Command

You can create new MySQL Views by using the CREATE VIEW and SELECT commands. You can use SELECT statements to extract data from the source table to create MySQL Views. This is the syntax of the CREATE VIEW command:

CREATE [OR REPLACE] VIEW view_name AS    
SELECT columns    
FROM tables    
[WHERE conditions];   

Say, your database has a table named course, that will become the basis of the MySQL View you are about to create. Here’s the code snippet to create a virtual table that pulls data from the table courses:

CREATE VIEW trainer AS    
SELECT course_name, trainer     
 FROM courses;

Now that the execution of the CREATE VIEW statement becomes successful, MySQL can develop MySQL Views and store them in the database.

To take a peek into the newly created MySQL View, use this query:

SELECT * FROM trainer;

This is what the output looks like:

Create a View using WITH CHECK Statement

You can leverage the WITH CHECK clause to ensure the consistency of MySQL Views whenever you amend or develop new MySQL Views. The WITH CHECK clause is an optional clause of the CREATE VIEW statement. By leveraging the clause, you can prevent a view from inserting or amending rows that can be seen through it. This is what the syntax of the WITH CHECK Statement looks like:

CREATE [OR REPLACE VIEW] view_name 
AS
  select_statement
  WITH CHECK OPTION;

• Step 1: Now, for this scenario, create a view called vps based on the employees table to display employees whose job titles are VP. For instance, VP Marketing, VP Sales, to name a few. This is what the code snippet would look like:

CREATE OR REPLACE VIEW vps AS
    SELECT 
        employeeNumber,
        lastname,
        firstname,
        jobtitle,
        extension,
        email,
        officeCode,
        reportsTo
    FROM
        employees
    WHERE
        jobTitle LIKE '%VP%';

• Step 2: Now, you can query the data from the vps MySQL View by leveraging the SELECT statement:

SELECT * FROM vps;

• Step 3: Next, you need to insert a row into the employees table through the vps MySQL View as follows:

INSERT INTO vps(
    employeeNumber,
    firstName,
    lastName,
    jobTitle,
    extension,
    email,
    officeCode,
    reportsTo
) 
VALUES(
    1703,
    'Lily',
    'Bush',
    'IT Manager',
    'x9111',
    'lilybush@classicmodelcars.com',
    1,
    1002
);

• Step 4: You can notice that the newly created employee cannot be viewed through the vps MySQL View since her job title isn’t VP, it’s IT Manager. Now, to ensure consistency of a MySQL View so that users can only update or display data that can be viewed through the MySQL View, you can leverage the WITH CHECK OPTION when you amend or develop the view. Here’s how you do it:

CREATE OR REPLACE VIEW vps AS
    SELECT 
        employeeNumber,
        lastName,
        firstName,
        jobTitle,
        extension,
        email,
        officeCode,
        reportsTo
    FROM
        employees
    WHERE
        jobTitle LIKE '%VP%' 
WITH CHECK OPTION;

• Step 5: Next, introduce a row into the employees table through the vps view with the following query:

INSERT INTO vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo)
VALUES(1704,'John','Smith','IT Staff','x9112','johnsmith@classicmodelcars.com',1,1703);

• Step 6: MySQL rejects this query and issued the following error message:

Error Code: 1369. CHECK OPTION failed 'classicmodels.vps'

• Step 7: Next, insert an employee whose job title is SVP Marketing into the employees table through the vps view to view if it is allowed now as follows:

INSERT INTO vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo)
VALUES(1704,'John','Smith','SVP Marketing','x9112','johnsmith@classicmodelcars.com',1,1076);

• Step 8: Now, MySQL issues the following statement:

1 row(s) affected.

• Step 9: You can verify the insert by querying the data from the vps MySQL View as follows:

SELECT * FROM vps;

This is what the output looks like:

Create a View using MySQL Workbench

To create MySQL Views in the database using the MySQL Workbench, you will first have to launch it and log in with the password and username to the MySQL Server. It will display the following screen:

• Step 1: Hover to the navigation tab and click on the Schema Menu. Here, you can take a look at all the databases that have been created before this session. Choose any database under the schema menu, for instance, testdb. The database will be shown as a pop-up option as shown below:

• Step 2: Now, right-click on the Views option visible under testdb, which will take you to a new pop-up screen.

• Step 3: Having selected the “Create View” option, you will be shown the following screen where you can develop your MySQL Views.

• Step 4: Once you’ve coded your MySQL Views, click on the Apply button to display the following screen:

• Step 5: In this screen, you’ll be reviewing the MySQL query for the MySQL Views you develop. Click on the Apply Button on the database to move on to the next step.

• Step 6: To complete the process of creating MySQL Views by leveraging MySQL Workbench, click on the Finish button. Now the newly created MySQL View can be verified as follows:

How to Update a MySQL Table View?

To create updatable MySQL Views, you can first create the view officeInfo. This MySQL View would be based on the offices table within the sample database. The view as shown below would be referring to three columns of the offices table: phone, officeCode, and city:

CREATE VIEW officeInfo
 AS 
   SELECT officeCode, phone, city
   FROM offices;

• Step 1: Next, you can query the data from the officeInfo view by leveraging the following statement:

SELECT 
    *
FROM
    officeInfo;

• Step 2: After executing the aforementioned query, you can change the phone number of the office with officeCode 4 within the officeInfo view by leveraging the UPDATE statement as follows:

UPDATE officeInfo 
SET 
    phone = '+33 14 723 5555'
WHERE
    officeCode = 4;

• Step 3: Now, to verify if the change has been adopted, you can query the data from the officeInfo view by running the following query:

SELECT 
    *
FROM
    officeInfo
WHERE
    officeCode = 4;

This is what the output of this query would look like:

• Step 4: MySQL also allows you to check exactly which MySQL Views are updatable. You can do this by querying the is_updatable column from the MySQL Views table within the INFORMATION_SCHEMA Database. You can then use the following query from the classicmodels database to query and display the updatable MySQL Views:

SELECT 
    table_name, 
    is_updatable
FROM
    information_schema.views
WHERE
    table_schema = 'classicmodels';

This is what the output would look like:

How to Drop a MySQL View?

You can leverage the DROP VIEW statement to delete a view entirely from the database. This is what the basic syntax of the DROP VIEW statement looks like:

DROP VIEW [IF EXISTS] view_name;

• Step 1: In this query, you need to mention the name of the view that you wish to drop after the keywords DROP VIEW. You can also leverage the optional IF EXISTS to conditionally remove the view if it exists in your database. If you need to remove various MySQL views in a single statement, you can utilize the following syntax:

DROP VIEW [IF EXISTS] view_name1 [,view_name2]...;

• Step 2: In this query, you are mentioning all the views you wish to eliminate separated by commas after the keyword DROP VIEW. If the list consists of a view that doesn’t exist, the DROP VIEW statement would fail to execute. This means no views would be deleted here. On the other hand, if you use the IF EXISTS clause to supplement your DROP VIEW query, it will return a NOTE for every non-existing view in the database.

• Step 3: Here’s a handy example to demonstrate how the DROP VIEW statement works. First, you will have to create a view called customerPayments based on the tables payments and customers. This is what the tables look like:

• Step 4: Implement the following query to move forward:

CREATE VIEW customerPayments 
AS
    SELECT 
        customerName, 
        SUM(amount) payment
    FROM
        customers
    INNER JOIN payments 
        USING (customerNumber)
    GROUP BY 
        customerName;

• Step 5: Now you can drop the customerPayments view as follows:

DROP VIEW IF EXISTS customerPayments;

• Step 6: Say, you have a view called employeeOffices based on the offices and employees tables. Here is what the tables look like:

CREATE VIEW employeeOffices AS
    SELECT 
        firstName, lastName, addressLine1, city
    FROM
        employees
            INNER JOIN
        offices USING (officeCode);

• Step 7: Now if you try to delete two views at a time, employeeOffices and eOffices using the following code snippet:

DROP VIEW employeeOffices, eOffices;

• Step 8: MySQL would throw an error as follows:

Error Code: 1051. Unknown table 'classicmodels.eoffices'

• Step 9: Now if you add the IF EXISTS clause to the aforementioned query, here is what it would look like:

DROP VIEW IF EXISTS employeeOffices, eOffices;

• Step 10: Now instead of throwing an error, MySQL simply returns a warning as follows:

1 warning(s): 1051 Unknown table 'classicmodels.eoffices'

How to Rename a MySQL View?

Within MySQL tables and views exist in the same namespace. This allows you to leverage the RENAME TABLE statement to rename a view as well. This is what the basic syntax of the RENAME TABLE statement looks like:

RENAME TABLE original_view_name 
TO new_view_name;

In this syntax, you need to mention the name of the view you wish to replace right after the RENAME TABLE keyword. Then, you need to specify the new name right after the TO keyword for the renaming to take effect.

• Step 1: In this example, you can create a new view called productLineSales using the code snippet mentioned below:

CREATE VIEW productLineSales AS
SELECT 
    productLine, 
    SUM(quantityOrdered) totalQtyOrdered
FROM
    productLines
        INNER JOIN
    products USING (productLine)
        INNER JOIN
    orderdetails USING (productCode)
GROUP BY productLine;

• Step 2: Next, you can rename the view productLineSales to productLineQtySales using the following code snippet:

RENAME TABLE productLineSales 
TO productLineQtySales;

• Step 3: Finally, you can leverage the SHOW FULL TABLES command to verify if the view has been successfully renamed as follows:

SHOW FULL TABLES WHERE table_type = 'VIEW'; 

How to Use MySQL Views with Joins?

In this example, you will be taking a look at a scenario that consists of multiple tables and leverages joins.

• Step 1: The first step here is to package the JOIN clause that gets information from three tables movies, members, and movie rentals. Here is the code snippet for the same:

CREATE VIEW `general_v_movie_rentals` AS SELECT mb.`membership_number`,mb.`full_names`,mo.`title`,mr.`transaction_date`,mr.`return_date` FROM `movierentals` AS mr INNER JOIN `members` AS mb ON mr.`membership_number` = mb.`membership_number` INNER JOIN `movies` AS mo ON mr.`movie_id` = mo.`movie_id`;

• Step 2: This query successfully creates the view general_v_movie_rentals in your database. Next, choose all the fields from the table general_v_movie_rentals:

SELECT * FROM `general_v_movie_rentals`;

Here is what the output of this statement would look like:

A key point to note here is that you didn’t have to scribble up a complex JOIN query to extract information about movies, members, and movie rental details. Here, you simply used the view in an ordinary SELECT statement as you would do for any normal table. These MySQL Views can then be called from anywhere in the application system running on top of your database.

How to List/Show MySQL Views?

In MySQL, you can show/list MySQL Views in any one of the following two ways:

• By Leveraging INFORMATION_SCHEMA Database
• By Leveraging SHOW TABLES Statement

By Leveraging INFORMATION_SCHEMA Database

• Step 1: With the INFORMATION_SCHEMA database, you can get access to MySQL Database metadata such as tables, privileges, databases, or data types of columns. This schema is usually referred to as the system catalog or the database directory. To display the MySQL views of a database, you can leverage the ‘tables’ table from the INFORMATION_SCHEMA as follows:

SELECT * 
FROM information_schema.tables;

• Step 2: For instance, you can leverage the following query to return all the views from the classicmodels database:

SELECT 
    table_name view_name
FROM 
    information_schema.tables 
WHERE 
    table_type   = 'VIEW' AND 
    table_schema = 'classicmodels';

• Step 3: To locate the views that match a specific pattern, you can utilize the table_name column. For instance, the following query can be used to find all the views whose name starts customer:

SELECT 
    table_name view_name
FROM 
    information_schema.tables 
WHERE 
    table_type   = 'VIEW' AND 
    table_schema = 'classicmodels' AND
    table_name   LIKE 'customer%';

By Leveraging SHOW TABLES Statement

• Step 1: MySQL treats the views as tables that are of the type ‘View’. Hence, to display all the MySQL Views present in the current database, you can leverage the SHOW TABLES statement as follows:

SHOW FULL TABLES 
WHERE table_type = 'VIEW';

• Step 2: Since the SHOW FULL TABLES statement gives you both views and tables as a result, you need to supplement the statement with a WHERE clause to ensure the result only consists of MySQL Views. On the other hand, if you want to display all the views present in another database, you can use either the IN or FROM clause as follows:

SHOW FULL TABLES
[{FROM | IN } database_name]
WHERE table_type = 'VIEW';

• Step 3: While using this syntax, you need to specify a database name that contains the views following the IN or FROM clause. For instance, the following statement will display all the views from the sys database:

SHOW FULL TABLES IN sys 
WHERE table_type='VIEW';

This is what the output of the aforementioned statement looks like:

• Step 4: If you want to retrieve views that say match a specified pattern, you can use the LIKE clause in the following code snippet that depicts the syntax:

SHOW FULL TABLES
[{FROM | IN } database_name]
LIKE pattern;

• Step 5: So, you can use the LIKE clause to extract all the views from the sys database, whose name begins with ‘waits’ as follows:

SHOW FULL TABLES 
FROM sys
LIKE 'waits%';

Here is the output for the same:

Conclusion

This blog talks about the different salient aspects of MySQL Views in great detail. It also gives a brief introduction to the key benefits and features of MySQL before diving into the MySQL Views operations.

Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be challenging, and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications like MySQL into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin?SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.