How to Create a View in MySQL

Views allow to encapsulate or "hide" complexities, or allow limited read access to part of the data.

To create a view, use the CREATE VIEW command:

CREATE OR REPLACE VIEW view_name AS <select statement>;

While optional, the OR REPLACE part is frequently used so the the view is updated if already exists.

Some examples:

-- A view to show only beta users
CREATE VIEW beta_users_vw AS
SELECT * FROM users WHERE beta = 1;

-- A view to limit read access to only certain columns
CREATE VIEW users_basic_vw AS
SELECT first_name, last_name, telephone_number
FROM users;

-- A view for management so they only need to do a "SELECT * FROM top_20_customers_vw" instead of learning a complex SQL
CREATE OR REPLACE VIEW top_20_customers_vw AS
SELECT c.customer_name, sum(p.price*od.quantity) order_total
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_details od USING (order_id)
JOIN products p USING (product_id)
GROUP BY c.customer_name
ORDER BY order_total DESC

You may notice all view names above end in _vw. This is an example convention some developers adopt to easily distinguish views from tables.

database icon
Shared queries and folders ✅ Version history ✅ One-click connection to MySQL ✅
Get more done, together, with PopSQL and MySQL