Home » Learn » MySQL Tutorial » MySQL Views

MySQL Views

A view is a saved SELECT query that behaves like a table. It stores no data of its own: each time you read the view, MySQL runs the query.

CREATE VIEW top_students AS
SELECT id, name, marks
FROM students
WHERE marks >= 80;
MySQL says

Query OK, 0 rows affected (0.02 sec)

SELECT * FROM top_students ORDER BY id;
Result
id name marks
1 Robin 85
2 Maria 92
4 Asha 88

You can use a view in WHERE, JOIN and other queries, like a normal table. When a student’s marks change in the students table, the view shows the new result.

Why use views?

  • Simpler queries: hide a long JOIN behind a short name.
  • Security: show only some columns to a user, and hide the others.
  • Reuse: write the logic in one place.

Change and drop a view

CREATE OR REPLACE VIEW top_students AS
SELECT id, name, marks
FROM students
WHERE marks >= 85;

DROP VIEW IF EXISTS top_students;
Note: A view is not a copy of the data, so a complex view on big tables can still be slow. Many simple views can be used with INSERT and UPDATE too, but views with JOIN, GROUP BY or DISTINCT cannot.

Summary

  • CREATE VIEW name AS SELECT ...; saves a query as a virtual table.
  • Read it with SELECT, and remove it with DROP VIEW.
  • Next: String functions.

Share this post:

Share on LinkedIn
Congratulations! Chapter Finished. Learn more about the similar topics:
Exercises & Assignments
No Content Found.
Interview Questions & Answers
No Content Found.