Table of Contents
Introduction
A view is a database object that has no data. The table that acts as the foundation is the basis for the contents. The rows and columns are identical to those of a real table. A View in MySQL is a virtual table created by joining one or more tables in a query. It functions similarly to the base table, except it does not include any data of its own. A view and a table are fundamentally different in that views are definitions built on top of other tables (or views). The changes in the underlying table are mirrored in the View as well.
Advantages of Views
Simplify a difficult query
It enables the user to make complicated queries more understandable. Instead of repeating the lengthy query, we may create a view based on it and use a simple SELECT command.
Reusability is improved.
We know that View simplifies complex queries and lowers them to a single line of code to use VIEWS. Integrating with our applications is significantly easier with this type of coding. This prevents the same formula from being repeated in each query, making the code more reusable and readable.
Assistance with Data Security
It also allows us to show users only the information they are allowed to see while hiding sensitive data like personal and financial information. We can limit the amount of data that consumers have access to by only providing them with the information they require.
Backward compatibility
A view can also provide backward compatibility in legacy systems. Let’s imagine we want to split a large table into numerous smaller ones without causing any problems for the apps that are already using it. We’ll construct a view with the same name as the real table in this case so that existing apps can refer to it as a table.
MySQL Command Line Client
Using the CREATE VIEW and SELECT statements, we can create a new view. To create a VIEW, SELECT queries are used to extract data from the source table.
Syntax:
CREATE [OR REPLACE] VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];Example:
CREATE VIEW trainer AS SELECT course_name, trainer FROM courses; Using the following syntax, we can see the newly generated view: SELECT * FROM view_name;
Example:
SELECT * FROM trainer;
Output:
MySQL Update VIEW
The syntax for updating an existing view in MySQL is as follows:
ALTER VIEW view_name AS
SELECT columns
FROM table
WHERE conditions;
Example:
ALTER VIEW trainer AS SELECT id, course_name, trainer FROM courses;
MySQL Drop VIEW
The syntax for updating an existing view in MySQL is as follows:
Syntax
DROP VIEW [IF EXISTS] view_name;
Example:
DROP VIEW trainer;
Output:
0 Comments