I. What is a View?¶

In MySQL, a View is a virtual table that does not store actual data but dynamically generates results based on the SQL query result set, like a “window” into the data. Imagine repeatedly writing complex queries to filter specific data from multiple tables—views package this repeated query logic into a virtual table, allowing you to query this “packaged table” directly afterward.

II. Why Use Views?¶

The core purpose of views is to simplify queries, reuse logic, and hide complexity. For beginners, the most intuitive benefits are:
- Simplify Repetitive Operations: For example, if you frequently query “names and salaries of IT department employees,” instead of writing SELECT name, salary FROM employees WHERE department='IT' repeatedly, create a view and query it with SELECT * FROM it_employees for quick results.
- Hide Underlying Table Structure: If you only want users to see “customer names and order amounts” without exposing the full order table structure (e.g., order IDs, payment methods), a view can return only necessary fields.
- Security and Permission Control: Restrict user access to specific data via views. For example, salespeople can only view orders in their region, while admins can access all data.

III. How to Create a View?¶

Creating a view is straightforward: use CREATE VIEW to define the view name and specify the underlying query.

Basic Syntax:

CREATE VIEW 视图名 AS 
SELECT 列名1, 列名2... 
FROM 表名 
[WHERE 条件] 
[JOIN 其他表...];

Example: Suppose we have two tables: students (columns: id, name, age, department) and scores (columns: student_id, subject, score). To create a view for student names and scores:

CREATE VIEW student_scores AS
SELECT 
    s.name AS 学生姓名, 
    sc.score AS 分数 
FROM students s 
JOIN scores sc ON s.id = sc.student_id;

After execution, the student_scores view is created. Query it like a table:

SELECT * FROM student_scores;  -- View all student names and scores
SELECT 学生姓名 FROM student_scores WHERE 分数 > 90;  -- Filter high-scoring students

IV. Querying and Updating Views¶

Views are queried like tables using SELECT.

Querying a View:

-- View scores of IT department students (using the student_scores view with a filter)
SELECT * FROM student_scores 
WHERE 学生姓名 IN (SELECT name FROM students WHERE department='IT');

Note: Views do not support direct data updates by default (unless they meet specific conditions, e.g., based on a single table without aggregate functions). To modify view data, update the underlying table first, then the view will reflect the changes indirectly.

V. Advantages and Disadvantages of Views¶

Advantages:
- Performance Reuse: Avoid repeating complex SQL (e.g., multi-table joins, filters), reducing code redundancy.
- Logic Isolation: Hide underlying table complexity (e.g., encapsulate department='IT' in a view for direct reuse).
- Data Security: Control access via view permissions to restrict sensitive data.

Disadvantages:
- Performance Overhead: Views dynamically generate results by re-executing underlying SQL on each query, which may be less efficient than querying tables directly (especially with large datasets).
- Structure Dependency: If the underlying table changes (e.g., adding/deleting columns), the view may become invalid and require manual reconstruction.

VI. Views vs. Tables¶

VII. Common Questions and Tips for Beginners¶

1. Can Views Be Nested?
   Yes, but excessive nesting is not recommended. For example:

   -- Create a view for IT students' scores based on student_scores
   CREATE VIEW it_student_scores AS 
   SELECT * FROM student_scores 
   WHERE 学生姓名 IN (SELECT name FROM students WHERE department='IT');

Excessive nesting reduces query performance; keep views simple.

2. Do Views Occupy Storage Space?
   No! Views only store SQL logic, not data, so they do not consume additional disk space.

3. What If a View Becomes Invalid?
   If the underlying table structure changes (e.g., a column is deleted), the view query will fail. Rebuild the view with CREATE VIEW or update its logic manually.

VIII. Summary¶

Views are a “good helper” in MySQL for simplifying queries and improving reusability, ideal for encapsulating repetitive or complex query logic. However, views are not universal: for large datasets or frequently changing underlying tables, querying tables directly may be more efficient. For beginners, mastering the workflow “create view → query view → use view to simplify logic” will help you quickly apply views.