How to Create a View in MySQL: Syntax and Examples

In MySQL, you create a view using the CREATE VIEW statement followed by the view name and a SELECT query that defines the data. Views act like virtual tables that simplify complex queries and can be used just like regular tables in your queries.

Syntax

The basic syntax to create a view in MySQL is:

• CREATE VIEW view_name AS SELECT ...: Defines the view name and the query it represents.
• The SELECT statement specifies the columns and rows the view will show.
• You can use CREATE OR REPLACE VIEW to update an existing view.

sql

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Example

This example creates a view named active_customers that shows customers with status 'active'. You can then query this view like a regular table.

sql

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  status VARCHAR(20)
);

INSERT INTO customers VALUES
(1, 'Alice', 'active'),
(2, 'Bob', 'inactive'),
(3, 'Carol', 'active');

CREATE VIEW active_customers AS
SELECT id, name
FROM customers
WHERE status = 'active';

SELECT * FROM active_customers;

Output

id | name
---|-------
1  | Alice
3  | Carol

Common Pitfalls

Common mistakes when creating views include:

• Using SELECT * in views can cause issues if the underlying table changes.
• Trying to update data through views that are not updatable (e.g., views with joins or aggregates).
• Not having proper permissions to create views.
• Forgetting to use CREATE OR REPLACE VIEW when updating an existing view.

sql

/* Wrong: Using SELECT * can break if table changes */
CREATE VIEW bad_view AS
SELECT * FROM customers;

/* Right: Specify columns explicitly */
CREATE OR REPLACE VIEW good_view AS
SELECT id, name, status FROM customers;

Quick Reference

Clause	Description
CREATE VIEW view_name AS SELECT ...	Creates a new view with the given SELECT query.
CREATE OR REPLACE VIEW view_name AS SELECT ...	Creates or updates an existing view.
DROP VIEW view_name;	Deletes the view from the database.
SELECT * FROM view_name;	Queries data from the view like a table.

Key Takeaways

Use CREATE VIEW with a SELECT query to define a virtual table in MySQL.

Specify columns explicitly in views to avoid issues if underlying tables change.

Views simplify complex queries and can be queried like regular tables.

Use CREATE OR REPLACE VIEW to update an existing view safely.

Be aware that not all views are updatable for data modification.