Views

A view is a stored SELECT queried like a table; its columns are fixed at creation, its rows computed on each use. ALGORITHM = MERGE folds the view into your query, so base-table indexes still apply; TEMPTABLE materializes it first; UNDEFINED, the default, prefers MERGE. A view is updatable when each row maps to one base-table row, which aggregates, DISTINCT, GROUP BY, HAVING, UNION and TEMPTABLE rule out:

CREATE VIEW in_stock AS SELECT id, sku, title, price, stock FROM products WHERE stock > 0
  WITH CHECK OPTION;
CREATE VIEW order_summary AS
  SELECT o.id, c.name, o.status, SUM(i.quantity * i.unit_price) AS total
  FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
  JOIN order_items AS i ON i.order_id = o.id GROUP BY o.id;
UPDATE in_stock SET price = 36.00 WHERE sku = 'BK-UX-01';
UPDATE in_stock SET stock = 0 WHERE sku = 'BK-UX-01';
UPDATE order_summary SET status = 'paid' WHERE id = 8;

ERROR 1369 (HY000): CHECK OPTION failed 'shop.in_stock'
ERROR 1288 (HY000): The target table order_summary of the UPDATE is not updatable

The price change reached products, and information_schema.VIEWS lists in_stock as updatable. WITH CHECK OPTION refused a change that would push the row out of the view; CASCADED, the default, also enforces the views beneath it. CREATE MATERIALIZED VIEW is accepted but, outside HeatWave 207 , makes an ordinary view (Denormalization).

By default a view runs with its DEFINER's privileges, so it can be granted as a filter. In a test, an account with only SELECT on customer_directory (id, name, country from customers) read its rows, while the same view declared SQL SECURITY INVOKER checked the caller's rights: ERROR 1356 (HY000): View 'shop.customer_directory_inv' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them.