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?
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.
A fully managed No-code Data Pipeline platform like Hevo helps you integrate data from 100+ data sources (including 40+ Free Data Sources) like MySQL to a destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. Its strong integration with umpteenth sources provides users with the flexibility to bring in data of different kinds, in a smooth fashion without having to code a single line.
GET STARTED WITH HEVO FOR FREE
Check Out Some of the Cool Features of Hevo:
- Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
- Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
- Connectors: Hevo supports 100+ Integrations to SaaS platforms, files, databases, analytics, and BI tools. It supports various destinations including Amazon Redshift, Firebolt, Snowflake Data Warehouses; Databricks, Amazon S3 Data Lakes, MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL databases to name a few.
- 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
- Scalable Infrastructure: Hevo has in-built integrations for 100+ sources, that can help you scale your data infrastructure as required.
- 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Simplify your Data Analysis with Hevo today!
SIGN UP HERE FOR A 14-DAY FREE TRIAL!
How Does MySQL Process Views: 3 Processing Algorithms
Here are three processing algorithms that can be leveraged to process MySQL Views:
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
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.
Image Source
To take a peek into the newly created MySQL View, use this query:
SELECT * FROM trainer;
This is what the output looks like:
Image Source
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;
Image Source
- 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:
Image Source
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:
Image Source
- 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:
Image Source
- Step 2: Now, right-click on the Views option visible under testdb, which will take you to a new pop-up screen.
Image Source
- Step 3: Having selected the “Create View” option, you will be shown the following screen where you can develop your MySQL Views.
Image Source
- Step 4: Once you’ve coded your MySQL Views, click on the Apply button to display the following screen:
Image Source
- 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.
Image Source
- 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:
Image Source
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;
Image Source
- 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:
Image Source
- 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:
Image Source
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:
Image Source
- 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:
Image Source
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:
Image Source
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
- 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;
Image Source
- 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%';
Image Source
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:
Image Source
- 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:
Image Source
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.