#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

MySQL Views Tutorial: Create, Update and Use Views

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_idnamebranchrole
3ImranPuneTrainer
4JayaPuneAdmin

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;
branchheadcountmonthly_salary
Noida299000.00
Pune288000.00
INSERT INTO staff VALUES (5,'Kunal','Pune','Trainer',55000,'EFGHJ5678K');
SELECT * FROM v_branch_cost ORDER BY branch;
branchheadcountmonthly_salary
Noida299000.00
Pune3143000.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_idnamebranchrolepan_masked
1GauravNoidaTrainerAB******4F
2HemaNoidaCounsellorBC******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;
namerole
ImranSenior 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_NAMEIS_UPDATABLECHECK_OPTIONSECURITY_TYPE
v_branch_costNONONEDEFINER
v_noida_staffYESCASCADEDDEFINER
v_staff_directoryYESNONEDEFINER

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

For a reusable result that lives only for your session, see temporary tables in MySQL. For reusable logic with parameters, see stored procedures.

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →