Views

A view is a stored query that behaves like a table. It holds no data of its own — it runs its SELECT each time you query it.

Creating views

CREATE VIEW v_student_summary AS
SELECT
  s.id, s.name_en, s.cgpa,
  d.name_en AS department,
  COUNT(e.id) AS courses_taken
FROM students s
LEFT JOIN departments d ON s.department = d.code
LEFT JOIN enrollments e ON e.student_id = s.id
GROUP BY s.id, s.name_en, s.cgpa, d.name_en;

SELECT * FROM v_student_summary WHERE cgpa > 3.5;

Managing views

CREATE OR REPLACE VIEW v_active_students AS
SELECT * FROM students WHERE status = 'active';

ALTER VIEW v_active_students AS SELECT id, name_en FROM students WHERE status='active';
DROP VIEW IF EXISTS v_active_students;

SHOW FULL TABLES WHERE table_type = 'VIEW';
SHOW CREATE VIEW v_student_summary\G

Why use views

• Simplify — hide a five-table join behind one name.
• Secure — grant access to a view that excludes salary or national ID columns, without granting access to the base table.
• Stabilise — the application queries the view, so you can restructure the underlying tables and keep the view's output the same.
• Standardise — one definition of "active customer" that every report shares.

Updatable views

A view is updatable if MySQL can map each result row back to exactly one base row. It is not updatable when it contains DISTINCT, GROUP BY, HAVING, aggregate or window functions, UNION, or certain subqueries.

CREATE VIEW v_cse AS
SELECT id, name_en, cgpa, department FROM students WHERE department = 'CSE';

UPDATE v_cse SET cgpa = 3.9 WHERE id = 1;    -- works
INSERT INTO v_cse (name_en, department) VALUES ('New', 'EEE');  -- allowed, but vanishes from the view

WITH CHECK OPTION

Stops writes that would produce rows the view cannot see:

CREATE VIEW v_cse AS
SELECT id, name_en, department FROM students WHERE department = 'CSE'
WITH CHECK OPTION;

INSERT INTO v_cse (name_en, department) VALUES ('X', 'EEE');  -- ERROR now

Algorithms and performance

CREATE ALGORITHM = MERGE VIEW v_fast AS SELECT id, name_en FROM students;

• MERGE folds the view into the outer query, so indexes and WHERE conditions still apply — fast.
• TEMPTABLE materialises the view into a temporary table first — needed for aggregates, but the outer WHERE cannot use base-table indexes.
• UNDEFINED (default) lets MySQL choose.

A view is not a cache: querying it costs the same as running its SELECT. For expensive aggregates that are read often, create a real summary table refreshed on a schedule instead. MySQL has no materialised views.