The front-desk team needs a staff directory: names, branches, roles. The staff table also holds salaries and PAN numbers, which they should never see. You could write a careful query for them every time, or you can save that query once as a view. A view is a named SELECT that behaves like a table when you query it, but stores no data of its own. Every time someone reads it, MySQL runs the underlying query against the current rows.
The base table
CREATE TABLE staff (
staff_id INT PRIMARY KEY,
name VARCHAR(20),
branch VARCHAR(10),
role VARCHAR(20),
salary DECIMAL(10,2),
pan CHAR(10)
);
INSERT INTO staff VALUES
(1,'Gaurav','Noida','Trainer',61000,'ABCDE1234F'),
(2,'Hema','Noida','Counsellor',38000,'BCDEF2345G'),
(3,'Imran','Pune','Trainer',58000,'CDEFG3456H'),
(4,'Jaya','Pune','Admin',30000,'DEFGH4567J');
The PAN values are made-up samples.
Create a view and query it
CREATE VIEW v_staff_directory AS
SELECT staff_id, name, branch, role FROM staff;
SELECT * FROM v_staff_directory WHERE branch = 'Pune';
| staff_id | name | branch | role |
|---|---|---|---|
| 3 | Imran | Pune | Trainer |
| 4 | Jaya | Pune | Admin |
You can filter, join and sort a view exactly like a table.
Views always show current data
CREATE VIEW v_branch_cost AS
SELECT branch, COUNT(*) AS headcount, SUM(salary) AS monthly_salary
FROM staff
GROUP BY branch;
SELECT * FROM v_branch_cost ORDER BY branch;
| branch | headcount | monthly_salary |
|---|---|---|
| Noida | 2 | 99000.00 |
| Pune | 2 | 88000.00 |
INSERT INTO staff VALUES (5,'Kunal','Pune','Trainer',55000,'EFGHJ5678K');
SELECT * FROM v_branch_cost ORDER BY branch;
| branch | headcount | monthly_salary |
|---|---|---|
| Noida | 2 | 99000.00 |
| Pune | 3 | 143000.00 |
No refresh step is needed. That also means a view over a heavy query is exactly as slow as the query. MySQL has no materialised views; if you need a stored snapshot, build a summary table and refresh it on a schedule, for example with MySQL events.
Use a view to restrict access
CREATE USER 'hr_view'@'%' IDENTIFIED BY 'Str0ng-Pass!';
GRANT SELECT ON vw1.v_staff_directory TO 'hr_view'@'%';
(vw1 is the database used here.) Logged in as hr_view:
SELECT name, role FROM v_staff_directory; -- works
SELECT * FROM staff;
ERROR 1142 (42000): SELECT command denied to user 'hr_view'@'localhost' for table 'staff'
This works because the view runs with its definer's privileges (SQL SECURITY DEFINER, the default). The user needs rights only on the view. Use SQL SECURITY INVOKER when the caller's own rights should apply. You can also show part of a sensitive value:
CREATE OR REPLACE VIEW v_staff_directory AS
SELECT staff_id, name, branch, role,
CONCAT(LEFT(pan, 2), '******', RIGHT(pan, 2)) AS pan_masked
FROM staff;
SELECT * FROM v_staff_directory ORDER BY staff_id LIMIT 2;
| staff_id | name | branch | role | pan_masked |
|---|---|---|---|---|
| 1 | Gaurav | Noida | Trainer | AB******4F |
| 2 | Hema | Noida | Counsellor | BC******5G |
CREATE OR REPLACE VIEW (or ALTER VIEW) changes the definition and keeps existing grants. Privileges are covered in MySQL user management.
Updating through a view
A simple view, with one table, no GROUP BY, no DISTINCT and no aggregates, is updatable. Changes go through to the base table:
UPDATE v_staff_directory SET role = 'Senior Trainer' WHERE staff_id = 3;
SELECT name, role FROM staff WHERE staff_id = 3;
| name | role |
|---|---|
| Imran | Senior Trainer |
An aggregated view is not updatable, because a single headcount cannot be traced back to rows:
UPDATE v_branch_cost SET headcount = 10 WHERE branch = 'Pune';
ERROR 1288 (HY000): The target table v_branch_cost of the UPDATE is not updatable
WITH CHECK OPTION: stop rows escaping the view
CREATE VIEW v_noida_staff AS
SELECT staff_id, name, branch, role FROM staff
WHERE branch = 'Noida'
WITH CHECK OPTION;
INSERT INTO v_noida_staff (staff_id, name, branch, role) VALUES (6, 'Lata', 'Pune', 'Admin');
ERROR 1369 (HY000): CHECK OPTION failed 'vw1.v_noida_staff'
Without the option, a Noida manager could insert a Pune row through "their" view, and the row would then vanish from it. With 'Noida' as the branch, the same insert succeeds. Columns the view does not include (salary, PAN) are left NULL or at their defaults, so make sure that is acceptable.
Inspect your views
SELECT TABLE_NAME, IS_UPDATABLE, CHECK_OPTION, SECURITY_TYPE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'vw1'
ORDER BY TABLE_NAME;
| TABLE_NAME | IS_UPDATABLE | CHECK_OPTION | SECURITY_TYPE |
|---|---|---|---|
| v_branch_cost | NO | NONE | DEFINER |
| v_noida_staff | YES | CASCADED | DEFINER |
| v_staff_directory | YES | NONE | DEFINER |
SHOW CREATE VIEW v_staff_directory; prints the full definition. SHOW FULL TABLES WHERE Table_type = 'VIEW'; lists views only.
When the base table changes
ALTER TABLE staff DROP COLUMN pan;
SELECT * FROM v_staff_directory;
ERROR 1356 (HY000): View 'vw1.v_staff_directory' references invalid table(s) or column(s) or
function(s) or definer/invoker of view lack rights to use them
MySQL let the column be dropped, and the view broke silently until someone queried it. Before you rename or drop columns, search information_schema.VIEWS for views that use them. The same error appears if the view's definer account is deleted.
Common pitfalls
SELECT *in a view is expanded to a fixed column list when the view is created. Columns added to the table later do not appear until you recreate the view.- Views on views on views make performance hard to reason about. Check plans with EXPLAIN.
- A view is not a security boundary by itself. It only helps if users are denied access to the base table.
- Drop with
DROP VIEW IF EXISTS v_name;. This never touches the underlying data.
For a reusable result that lives only for your session, see temporary tables in MySQL. For reusable logic with parameters, see stored procedures.