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
LIMIT 20;You may notice all view names above end in _vw. This is an example convention some developers adopt to easily distinguish views from tables.
Previous
How to Drop an Index
Finally, a unified workspace for your SQL development
Get more done, together, with PopSQL and MySQL