MySQL Views

A view in MySQL is a virtual table that is based on the result-set of a query. Views allow you to represent data from one or more tables as if it were a single table, which can simplify complex queries and improve readability.

Key Points about Views:

1. Virtual Table: A view does not store data physically. Instead, it stores a SQL query that dynamically generates results when queried.
2. Up-to-date Data: Every time a view is queried, MySQL executes the underlying SQL query to retrieve the latest data, ensuring the view always reflects current data.
3. Simplifies Queries: Views can encapsulate complex joins, filters, and calculations, allowing users to access the data more easily.

MySQL CREATE VIEW Statement

The CREATE VIEW statement is used to create a view in MySQL.

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Examples of Creating Views

1. Creating a View for Customers from Brazil: This view selects customers from the Customers table where the Country is Brazil.

   CREATE VIEW Brazil_Customers AS
   SELECT CustomerName, ContactName
   FROM Customers
   WHERE Country = 'Brazil';
   

   After creating this view, you can query it just like a table:

   SELECT * FROM Brazil_Customers;
   

2. Creating a View for Products Above Average Price: This view selects products from the Products table where the price is above the average price.

   CREATE VIEW Products_Above_Average_Price AS
   SELECT ProductName, Price
   FROM Products
   WHERE Price > (SELECT AVG(Price) FROM Products);
   

   Query the view:

   SELECT * FROM Products_Above_Average_Price;
   

Updating a View

If you need to modify an existing view, use the CREATE OR REPLACE VIEW statement to update it. This statement allows you to replace an existing view with a new definition.

Syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example: Adding a "City" column to the Brazil Customers view:

CREATE OR REPLACE VIEW Brazil_Customers AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = 'Brazil';

Dropping a View

To remove a view from the database, use the DROP VIEW statement.

Syntax:

DROP VIEW view_name;

Example: To drop the Brazil_Customers view:

DROP VIEW Brazil_Customers;

Summary

• Views in MySQL provide a way to simplify querying data by using virtual tables.
• CREATE VIEW creates a new view.
• CREATE OR REPLACE VIEW allows you to update a view.
• DROP VIEW removes a view from the database.
• Views always return up-to-date data because they execute the underlying query each time they are accessed.