Creating views in MySQL - Visual Walkthrough

Concept Flow - Creating views

Write SELECT query

↓

Create view with CREATE VIEW

↓

View stored in database

↓

Query view like a table

↓

Get results from view

You write a SELECT query, save it as a view with CREATE VIEW, then query the view like a table to get results.

Execution Sample

MySQL

CREATE VIEW top_customers AS
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

SELECT * FROM top_customers;

This creates a view named top_customers showing customers who spent more than 1000, then selects all from it.

Execution Table

Step	Action	Query/Command	Result/State
1	Write SELECT query	SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000;	Query ready to create view
2	Create view	CREATE VIEW top_customers AS [SELECT query]	View 'top_customers' stored in database
3	Query view	SELECT * FROM top_customers;	Returns rows of customers with total_spent > 1000
4	Exit	No more commands	Execution ends

💡 No more commands to execute, view creation and query complete

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
top_customers	Not defined	Defined as SELECT query	Stored as view in database	Queried to return result rows	View exists for future queries

Key Moments - 3 Insights

Why can't I see data immediately after CREATE VIEW?

CREATE VIEW only saves the query, it does not show data until you run SELECT on the view as shown in step 3 of execution_table.

Can I update data directly in a view?

Views are like saved queries; you cannot update data through them unless the view is updatable, which depends on the query. Here, the view is read-only as it uses aggregation.

What happens if the underlying table changes?

The view always shows current data from the underlying tables when queried, so changes in 'orders' affect the view results dynamically.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result after step 2?

A The view returns rows of customers

B The view 'top_customers' is stored in the database

C The SELECT query is written but not saved

D The execution ends

💡 Hint

Check the 'Result/State' column in row with Step 2 in execution_table

At which step do we get the actual data rows from the view?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look for when the SELECT * FROM top_customers query runs in execution_table

If the orders table changes after creating the view, what happens when you query the view?

A The view shows updated data from the orders table

B The view shows old data from when it was created

C The view breaks and shows error

D The view needs to be recreated to update

💡 Hint

Refer to key_moments about underlying table changes affecting view results

Concept Snapshot

CREATE VIEW view_name AS SELECT ...;
- Saves a SELECT query as a virtual table.
- Query the view like a normal table.
- View shows current data from underlying tables.
- Views can simplify complex queries.
- Not all views are updatable.

Full Transcript

Creating views in MySQL involves writing a SELECT query and saving it as a view using CREATE VIEW. The view acts like a virtual table that stores the query, not the data. When you query the view, it runs the saved SELECT query on the current data in the underlying tables and returns the results. Views help simplify complex queries and can be reused easily. However, views do not store data themselves and may not allow direct updates depending on the query. Changes in the underlying tables are reflected in the view results dynamically.