Lab: Working with Views in MySQL

This lab focuses entirely on Views in MySQL (and MariaDB). You’ll learn how to create, use, and—under certain conditions—update or insert into them. We’ll also discuss how to emulate materialized views, since MySQL does not support them natively.

Views

A view is a virtual table that represents the result of a stored query. The data in a view is not stored separately; instead, the database executes the underlying query each time the view is accessed.

Example: Creating a View

Consider these two tables, randA and randB. We can combine them into a view:

CREATE VIEW joined AS
SELECT randA.A AS link,
       randA.B AS first_B,
       randB.B AS second_B
FROM randA, randB
WHERE randA.A = randB.A
ORDER BY randA.A;

You can query it like a regular table:

SELECT * FROM joined LIMIT 10;

This will return data from both tables dynamically, without storing a separate copy.

Inserting and Updating Through Views

MySQL allows INSERT, UPDATE, and DELETE statements on a view only if the view meets certain conditions. These operations actually modify the underlying base tables, not the view itself.

1. When Can You Insert Into a View?

You can insert into a view if:

• The view is based on a single table.
• The view does not include any of the following:
  • GROUP BY
  • DISTINCT
  • UNION
  • JOIN
  • Aggregate functions (e.g., SUM(), COUNT())
  • Subqueries or derived columns (like col1 + col2) that don’t map directly to base columns
• The view includes all NOT NULL columns of the base table that have no default values.

Example of an Updatable View

CREATE VIEW simple_view AS
SELECT id, name, price
FROM products;

This view can be updated or inserted into, since it’s based on a single table and all columns map directly.

INSERT INTO simple_view (id, name, price)
VALUES (10, 'NEW Item', 14.99);

UPDATE simple_view
SET price = 19.99
WHERE id = 10;

These commands modify the underlying products table.

Example of a Non-Updatable View

CREATE VIEW price_summary AS
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category;

This view cannot be inserted into or updated because it uses GROUP BY and an aggregate function.

Making a View Insertable with CHECK OPTION

If you want to ensure that inserted rows satisfy the view condition, use WITH CHECK OPTION:

CREATE VIEW cheap_items AS
SELECT * FROM products
WHERE price < 100
WITH CHECK OPTION;

Now, if you try to insert a record with a price ≥ 100, MySQL will reject it:

INSERT INTO cheap_items (id, name, price)
VALUES (5, 'Expensive Gadget', 150); -- ❌ Error due to CHECK OPTION

Materialized Views in MySQL

MySQL does not natively support materialized views (unlike Oracle or PostgreSQL). However, you can simulate them by creating a physical table that stores the results of a query and then refreshing it manually or on a schedule.

Creating a Manual Materialized View

CREATE TABLE sales_summary AS
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

This table now contains the aggregated data. To “refresh” it later, you can truncate and repopulate:

TRUNCATE TABLE sales_summary;

INSERT INTO sales_summary
SELECT product_id, SUM(amount)
FROM sales
GROUP BY product_id;

Automating Refresh with a Trigger (Basic Example)

To keep the materialized view in sync, you can use triggers on the base table. For example:

CREATE TRIGGER update_sales_summary
AFTER INSERT ON sales
FOR EACH ROW
  REPLACE INTO sales_summary (product_id, total_sales)
  SELECT product_id, SUM(amount)
  FROM sales
  WHERE product_id = NEW.product_id
  GROUP BY product_id;

This keeps the summary table updated whenever a new sale is inserted. However, for large tables, this approach can impact performance. A better solution is to refresh on a schedule using an external script or a cron job.

Refreshing with an Event Scheduler

MySQL’s Event Scheduler can automatically refresh the materialized view periodically:

CREATE EVENT refresh_sales_summary
ON SCHEDULE EVERY 1 HOUR
DO
  REPLACE INTO sales_summary
  SELECT product_id, SUM(amount)
  FROM sales
  GROUP BY product_id;

This keeps your summary table updated every hour.

Summary

• Views are virtual tables based on queries.
• They simplify complex queries and enhance security by abstracting data.
• Only simple, single-table views are updatable or insertable.
• Use WITH CHECK OPTION to enforce view conditions on inserted data.
• MySQL doesn’t support materialized views directly — emulate them using tables + triggers/events.