Creating views in MySQL

Introduction

Views let you save a query as a virtual table. This makes it easier to reuse complex queries without rewriting them.

You want to simplify complex queries for yourself or others.

You need to show only specific columns or rows from a table.

You want to hide sensitive data from some users.

You want to reuse the same query in many places without copying it.

You want to organize data in a way that matches how you think about it.

Syntax

MySQL

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

A view acts like a saved SELECT query you can use like a table.

You can select specific columns and filter rows in the view.

Examples

Creates a view showing only active customers with their id, name, and email.

MySQL

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

Creates a view listing product IDs and their prices.

MySQL

CREATE VIEW product_prices AS
SELECT product_id, price
FROM products;

Creates a view of orders placed after January 1, 2024.

MySQL

CREATE VIEW recent_orders AS
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date > '2024-01-01';

Sample Program

This example creates an employees table, inserts data, creates a view for the Sales department, and selects all from that view.

MySQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50),
  salary INT
);

INSERT INTO employees VALUES
(1, 'Alice', 'Sales', 50000),
(2, 'Bob', 'HR', 45000),
(3, 'Charlie', 'Sales', 55000),
(4, 'Diana', 'IT', 60000);

CREATE VIEW sales_team AS
SELECT id, name, salary
FROM employees
WHERE department = 'Sales';

SELECT * FROM sales_team;

Important Notes

Views do not store data themselves; they show data from the original tables.

If the original table changes, the view shows the updated data automatically.

You can use views to improve security by restricting access to certain data.

Summary

Views save queries as virtual tables for easy reuse.

They help simplify complex data and control what users see.

Creating a view uses the CREATE VIEW statement with a SELECT query.