MySQL Views (or Virtual Tables)

A View is essentially a table in MySQL consisting of rows and columns, just like a regular table. However, the difference is that a View, or Virtual Table, does not physically store data.

Key Points to Learn About Views

Let's go through the key points. First, let's see how to create a View. It's a simple process:

1. Creating a View

Use the following query to create a View easily:

CREATE VIEW VIEWNAME;        

2. Working with Views

Once a View is created, you can use SQL commands like SELECT, DELETE, UPDATE, and INSERT on it just like on a normal table.

3. Advantages of Using Views

Before we dive into an example, let's explore the benefits of using Views:

1. Security Mechanism: Views allow you to expose only the required data, reducing complexity and securing sensitive information.
2. Data Abstraction: You can display specific data to the user as needed while keeping the underlying table structure hidden.
3. Convenience: Views make queries more readable and easier to execute by simplifying complex operations into a single command.

Step 1: Creating a Normal Table

First, create a table named Accounts.

CREATE TABLE Accounts (
    Account_ID INT,
    Company VARCHAR(50),
    Amount DECIMAL(10, 2)
);
        

Step 2: Adding Data to the Table

Now, add a few rows of data to the table.

INSERT INTO Accounts (Account_ID, Company, Amount) VALUES
(1, 'ABC', 450.00),
(2, 'aXYZ', 15000.00),
(3, 'QRT', 5000.00);
        

Step 3: Viewing the Table

Once the data is added, you can view the Accounts table. This is a standard table with the data stored physically.

SELECT * FROM Accounts;        

Step 4: Creating a View

Now, let’s create a View to include all the data from the Accounts table.

CREATE VIEW Debit AS 
SELECT * FROM Accounts;
        

Step 5: Viewing a View

SELECT *FROM Debit;        

You can check the View in your database tool, such as phpMyAdmin, just like you check any physical table. The View, however, does not store data physically; it retrieves it dynamically from the base table whenever accessed.

Advanced Examples

Creating a View with Specific Columns and Where Concept

You can create a View that includes only specific columns:

CREATE VIEW DebitGreater1000 AS 
SELECT Account_ID, Amount FROM Accounts WHERE Amount >1000;        

Summary

• A View is a virtual table that helps you simplify and secure data access.
• You can include specific columns or filter data using conditions while creating Views.
• Views improve query readability, reduce complexity, and enhance data security.