How to Create a View in MySQL

Contents
In this article, you will learn how to create a view in MySQL.
Creating a view in MySQL
A view in MySQL is a virtual table based on the result-set of a SELECT statement. A view is a useful way to simplify complex queries, hide sensitive data, and provide a way to reuse commonly used queries.
Here are the steps to create a view in MySQL:
Create the SELECT statement for the view
The first step to create a view is to create a SELECT statement that will be used to define the view. The SELECT statement can contain any valid SQL statement that returns a result set. For example:
SELECT column1, column2, column3 FROM table1 WHERE condition;
Define the view
After creating the SELECT statement for the view, we can define the view using the CREATE VIEW statement. For example:
CREATE VIEW view_name AS
SELECT column1, column2, column3 FROM table1 WHERE condition;
Query the view
After creating the view, we can query the view just like we would query a table. For example:
SELECT * FROM view_name;
Modify the view
We can modify the view using the ALTER VIEW statement. For example, we can add or remove columns, change the SELECT statement, or add a WHERE clause. For example:
ALTER VIEW view_name AS
SELECT column1, column2, column3, column4 FROM table1 WHERE condition AND column3 > 0;
Drop the view
If we no longer need the view, we can drop it using the DROP VIEW statement. For example:
DROP VIEW view_name;
Here’s an example of creating a view:
Let’s say we have a table called orders with columns order_id, customer_id, order_date, and order_total. We want to create a view that shows the customer name, order date, and order total for each order. We can create a view called order_summary using the following SQL statement:
CREATE VIEW order_summary AS
SELECT customers.customer_name, orders.order_date, orders.order_total
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;
Now, we can query the order_summary view just like we would query a table:
SELECT * FROM order_summary;
This will return a result set with columns customer_name, order_date, and order_total.