Dropping and altering views in MySQL - Step-by-Step Execution

Concept Flow - Dropping and altering views

Start

↓

Check if view exists

Yes↓

Drop or Alter view

↓

View updated or removed

↓

End

This flow shows checking for a view, then dropping or altering it, and finally confirming the change.

Execution Sample

MySQL

DROP VIEW IF EXISTS my_view;
CREATE OR REPLACE VIEW my_view AS
SELECT id, name FROM users WHERE active = 1;

This code drops a view if it exists, then creates or replaces it with a new definition.

Execution Table

Step	Action	Check/Command	Result
1	Check if view 'my_view' exists	SHOW FULL TABLES LIKE 'my_view';	View exists
2	Drop view	DROP VIEW IF EXISTS my_view;	View 'my_view' dropped
3	Create or replace view	CREATE OR REPLACE VIEW my_view AS SELECT id, name FROM users WHERE active = 1;	View 'my_view' created with new definition
4	Verify view	SELECT * FROM my_view;	Returns active users' id and name
5	End	-	Process complete

💡 Process ends after view is dropped and recreated or altered successfully

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
my_view	Exists	Dropped (does not exist)	Created with new definition	Exists with updated definition

Key Moments - 3 Insights

Why do we use 'DROP VIEW IF EXISTS' before creating or altering a view?

Using 'DROP VIEW IF EXISTS' ensures the view is removed if it exists, preventing errors when creating or replacing it, as shown in execution_table step 2.

What happens if we use 'CREATE OR REPLACE VIEW' without dropping the view first?

'CREATE OR REPLACE VIEW' updates the view if it exists or creates it if not, so dropping first is optional but can be used to clear dependencies, as seen in step 3.

How can we verify that the view was altered correctly?

By running a SELECT query on the view after creation (step 4), we confirm it returns the expected data.

Visual Quiz - 3 Questions

Look at the execution table, what is the state of 'my_view' after step 2?

A The view does not exist

B The view exists with old definition

C The view exists with new definition

D The view is locked

💡 Hint

Check the 'Result' column in step 2 of the execution_table

At which step does the view get created with the new definition?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Action' and 'Result' columns in the execution_table

If the view did not exist initially, which step would be skipped or have a different result?

A Step 3 would not run

B Step 1 would say 'View does not exist'

C Step 2 would fail

D Step 4 would return an error

💡 Hint

Refer to the 'Check if view exists' action in step 1 of the execution_table

Concept Snapshot

DROP VIEW IF EXISTS view_name;  -- removes view if it exists
CREATE OR REPLACE VIEW view_name AS SELECT ...;  -- creates or updates view
Use DROP to remove views safely.
Use CREATE OR REPLACE to alter views without dropping.
Verify changes by selecting from the view.

Full Transcript

This lesson shows how to drop and alter views in MySQL. First, we check if the view exists. If it does, we drop it using DROP VIEW IF EXISTS to avoid errors. Then, we create or replace the view with a new definition using CREATE OR REPLACE VIEW. Finally, we verify the view by selecting data from it. This process ensures views are updated safely and correctly.