Views

Well-designed applications usually provide an open interface, hiding implementation details, which allows for design changes without affecting end users.

When developing your database, you can achieve a similar result by encapsulating tables and providing access to data only through a set of views.

In this article, we will discuss what views are, how they are created, and what they can be useful for.

What is a view

Views are sometimes called "virtual tables." This is because a view appears to the user as a table, but actually does not store data, but retrieves it from other tables at the time of access.

If the data in the underlying table changes, the user receives up-to-date data when accessing the view that uses this table. Views do not cache query results during operation.

Example of creating a view

As a simple example, suppose you want to partially hide email addresses in the user table (Users).

This can be useful, for example, if your company's policy does not allow everyone to use confidential user information. Therefore, instead of allowing direct access to the user table (Users), you define a view named ViewUsers and require everyone to use it to access user data.

MySQL 8.4
CREATE VIEW ViewUsers AS
    SELECT id,
           name,
           CONCAT(SUBSTR(email, 1, 2), '****', SUBSTR(email, -4)) AS email
FROM Users;

MySQL 8.4
CREATE VIEW ViewUsers AS
    SELECT id,
           name,
           CONCAT(SUBSTR(email, 1, 2), '****', RIGHT(email, 4)) AS email
FROM Users;

The view in an SQL query appears and is used like a regular table.

MySQL 8.4
SELECT * FROM ViewUsers;

MySQL 8.4
DESCRIBE ViewUsers;

MySQL 8.4
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'viewusers';

General syntax of a view

CREATE [OR REPLACE]
VIEW view_name [(view_column [, ...])]
AS select_expression;

CREATE [OR REPLACE] VIEW view_name [(view_column [, ...])]
AS select_expression;

Why are views needed

Simplifying complex queries

Views are used to simplify complex queries and create an abstraction between the user and the database. They can hide the complexity of data structures and provide a simplified interface for accessing data.

Improving performance

Ensuring security

Views can be used to ensure the security of confidential data. Creating views that restrict access to specific columns or rows of data allows administrators to limit access to sensitive information. This helps ensure that only authorized users have access to confidential data.

Conclusion

Views are an important tool in SQL that allows for simplifying complex queries, standardizing data access, improving performance, and ensuring data security

Let's check how well you understood the topic: choose the correct statement for the question "What is a view in a database?"