Skip to main content
MySQL Basics
CHAPTER 17 Beginner

Views in MySQL

Updated: May 16, 2026
7 min read

# CHAPTER 17

Views in MySQL

1. Introduction

Imagine writing a massive, 30-line SQL query that joins the users, orders, products, and shipping tables together to generate a daily financial report. What happens when your boss asks you to run that report every single morning? Do you copy and paste the 30-line code block every day? Do you force the PHP backend to send 30 lines of complex SQL over the network? No. You save the query permanently inside the database as a View. In this chapter, we explore how to create secure, reusable virtual tables.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Define what a SQL View is.
  • Create a View using CREATE VIEW.
  • Query a View exactly like a normal table.
  • Understand how Views act as a security layer.
  • Update or drop existing Views.

3. What is a View?

A View is a "Virtual Table." It does not physically exist on the hard drive. It does not store data. A View is simply a saved SQL SELECT query that is given a name. Whenever you query the View, MySQL runs the underlying SELECT query on the fly, pulls the live data from the real tables, and presents it to you as if it were a single, flat table.

4. Creating a View

Let's take a complex query that joins Users and Orders to find "High Value Customers" and save it as a View.
sql
1234567 
-- The CREATE VIEW syntax is placed directly above your SELECT statement
CREATE VIEW high_value_customers AS
SELECT u.id, u.first_name, u.email, SUM(o.order_total) AS total_spent
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id
HAVING SUM(o.order_total) > 1000;

*MySQL executes this command instantly. No data is moved or copied. The query itself is just saved into the database's memory.*

5. Using the View

Now that the View is created, you (or your backend PHP code) can treat high_value_customers exactly like a real table!
sql
1234567 
-- You never have to write the JOINs or GROUP BYs again!
SELECT * FROM high_value_customers;

-- You can even filter the View further!
SELECT first_name FROM high_value_customers 
WHERE total_spent > 5000 
ORDER BY total_spent DESC;

6. Views as a Security Layer

Views are not just for convenience; they are critical for enterprise security. Imagine an employees table that contains: id, name, department, and social_security_number. You want to give an intern access to the database so they can generate a list of employee names. If you give them access to the employees table, they can see everyone's SSN!

The Solution: Create a View that hides the sensitive columns, and only give the intern permission to query the View!

sql
1234567 
CREATE VIEW public_employee_directory AS
SELECT id, name, department 
FROM employees;
-- (Notice we intentionally excluded the SSN column).

-- The intern can safely run:
SELECT * FROM public_employee_directory;

7. Updating and Dropping Views

Because a View is just a saved query, modifying it is very easy. It does not affect the underlying physical data.
sql
1234567 
-- To modify the View's underlying logic, use CREATE OR REPLACE
CREATE OR REPLACE VIEW public_employee_directory AS
SELECT id, name, department, email  -- We decided to add email!
FROM employees;

-- To completely delete the View
DROP VIEW public_employee_directory;

8. Updating Data THROUGH a View

Can you run an UPDATE or INSERT statement on a View, and have it update the real underlying table? Sometimes. If the View is extremely simple (a basic 1-to-1 mapping of a single table), MySQL allows you to update data through it. However, if your View contains JOIN, GROUP BY, DISTINCT, or Aggregate functions (SUM, MAX), it is strictly Read-Only. You cannot INSERT into a View that groups data, because MySQL wouldn't know which specific real row to insert the data into.

9. Common Mistakes

  • Assuming Views improve performance: A common misconception is that querying a View is faster than running the complex query manually. It is not. The View does not pre-calculate or cache the data. It runs the exact same raw query on the live tables every single time it is called.
  • Forgetting underlying table changes: If you create a View SELECT first_name..., and a database admin later deletes the first_name column from the real table, your View will "break" and crash the next time anyone tries to query it.

10. Best Practices

  • Naming Conventions: In large databases, it is an industry standard to prefix views with v_ or vw_ (e.g., vw_high_value_customers). This instantly alerts other developers that they are querying a virtual table, not a physical one, preventing them from trying to run INSERT statements on it.

11. Exercises

  1. 1. Write the SQL statement to create a view named vw_active_products that displays all columns from the products table where is_active = 1.
  1. 2. Why is a View containing a GROUP BY clause strictly Read-Only?

12. MCQ Quiz with Answers

Question 1

Where does a MySQL View physically store its data on the hard drive?

Question 2

How can a View be utilized to improve database security for lower-level employees?

13. Interview Questions

  • Q: Explain the concept of a SQL View and list two primary business reasons for implementing them in an enterprise architecture.
  • Q: A developer attempts to execute an INSERT INTO statement on a View, but MySQL throws an error. Detail the architectural reasons why a View might be "Read-Only".

14. FAQs

Q: Do Views update automatically when new data is added to the database? A: Yes! Because Views do not store data, every time you run SELECT * FROM view, it goes and fetches the absolute most up-to-date data from the physical tables in real-time.

15. Summary

Views are the ultimate tool for query encapsulation. By wrapping complex, multi-join, aggregated monstrosities into simple, clean virtual tables, you streamline your application code, protect sensitive data columns from unauthorized access, and adhere to the programming principle of "Don't Repeat Yourself."

16. Next Chapter Recommendation

Our queries are clean, but what happens when the users table hits 50 million rows? Even a simple SELECT * WHERE email = 'john@email.com' will take 10 seconds to execute because MySQL has to read every single row. In Chapter 18: Indexing and Query Optimization, we will learn how to make our database lightning fast.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter Subqueries and Nested Queries
Next Chapter Indexing and Query Optimization

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6