• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Views

← Previous Next →

When working with databases, you often run into complex queries that you need to reuse frequently. Writing them again and again can be time-consuming and error-prone. That’s where views come in.

A view in MySQL is like a virtual table that stores the result of a query. It doesn’t physically store data — instead, it retrieves it from one or more tables every time you access it. Views make your queries simpler, cleaner, and easier to maintain.

What Is a View in MySQL?

A view is a saved SQL query that behaves like a table. You can select data from it, filter it, and even join it with other tables, just like a regular table.

Think of a view as a “window” into your data. The actual data stays in the original tables, but you can use the view to see exactly what you need.

For example, if your database has multiple tables such as customers, orders, and products, you can create a view that shows a customer’s name, order ID, and total price — all combined in one result.

Why Use Views?

Views are useful for several reasons:

  1. Simplify complex queries – You can store a long query in a view and use it as a shortcut.

  2. Improve readability – Views make SQL queries easier to read and manage.

  3. Enhance security – You can restrict access to sensitive columns by showing only selected data in a view.

  4. Consistency – If multiple users need to see the same filtered data, a view ensures they all get the same results.

  5. Ease of maintenance – Updating the view definition updates the query logic everywhere it’s used.

How to Create a View in MySQL

The basic syntax to create a view is:

CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;

Example:

Let’s say we have a table named employees:

emp_id emp_name department salary city
1 Neha HR 45000 Delhi
2 Riya IT 60000 Mumbai
3 Anjali IT 55000 Pune
4 Priya Sales 40000 Delhi

Now we can create a view that shows only IT employees:

CREATE VIEW it_employees AS
SELECT emp_name, department, salary
FROM employees
WHERE department = 'IT';

This creates a virtual table called it_employees that displays only IT department data.

How to Query Data from a View

Once a view is created, you can use it just like a regular table.

SELECT * FROM it_employees;

Output:

emp_name department salary
Riya IT 60000
Anjali IT 55000

How to Update or Modify a View

If you need to change a view (for example, to include another column or change the filter), you can use the CREATE OR REPLACE VIEW statement.

CREATE OR REPLACE VIEW it_employees AS
SELECT emp_name, department, salary, city
FROM employees
WHERE department = 'IT';

This command updates the existing it_employees view to now include the city column.

How to Drop (Delete) a View

If you no longer need a view, you can delete it using the DROP VIEW statement.

DROP VIEW it_employees;

Once dropped, the view no longer exists, but your original table remains safe.

Creating Views from Multiple Tables

Views can combine data from multiple tables using JOIN statements. This is helpful when you need data that spans across related tables.

Example:

Suppose we have two tables:

customers

cust_id cust_name city
1 Aditi Mumbai
2 Meera Pune
3 Kavya Delhi

orders

order_id cust_id amount order_date
101 1 2500 2025-10-01
102 2 1800 2025-10-05
103 3 3000 2025-10-10

We can create a view to display customer names with their order details:

CREATE VIEW customer_orders AS
SELECT c.cust_name, c.city, o.order_id, o.amount, o.order_date
FROM customers AS c
JOIN orders AS o
ON c.cust_id = o.cust_id;

Now you can simply query:

SELECT * FROM customer_orders;

Output:

cust_name city order_id amount order_date
Aditi Mumbai 101 2500 2025-10-01
Meera Pune 102 1800 2025-10-05
Kavya Delhi 103 3000 2025-10-10

Updating Data Through a View

If your view is based on a single table (and not using complex joins or aggregate functions), you can update, insert, or delete data directly through the view. MySQL will apply the change to the original table automatically.

Example:

UPDATE it_employees
SET salary = 62000
WHERE emp_name = 'Riya';

This updates Riya’s salary in the employees table itself.

However, if the view involves multiple tables or aggregate functions, it becomes non-updatable — meaning you can only read from it, not modify data.

Checking Existing Views

To see all views in your current database, use:

SHOW FULL TABLES WHERE table_type = 'VIEW';

This lists all the views that exist in your database.

Viewing the Definition of a View

If you want to see the query that defines a view, you can use the SHOW CREATE VIEW statement.

SHOW CREATE VIEW customer_orders;

This displays the SQL query used to create the view.

Advantages of Using Views

Benefit Description
Simplicity Complex joins and filters are stored once and reused easily.
Security You can hide sensitive columns from certain users.
Reusability One view can be used by multiple users or queries.
Consistency Everyone sees the same filtered data.
Maintenance Updating a view updates the logic everywhere it’s used.

Limitations of Views

While views are very useful, they have a few restrictions:

  1. You can’t create indexes on views.

  2. Some views can’t be updated (especially those using joins, groups, or aggregate functions).

  3. If the base table is dropped or changed, the view might stop working.

  4. Performance can be slower for complex views because MySQL needs to execute the base query every time.

Practical Example

Let’s combine everything with a practical scenario.

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

INSERT INTO employees (name, department, salary) VALUES
('Neha', 'IT', 60000),
('Anjali', 'HR', 45000),
('Riya', 'Sales', 40000);

-- Create a view to show only IT department employees
CREATE VIEW it_staff AS
SELECT name, salary FROM employees WHERE department = 'IT';

-- Query the view
SELECT * FROM it_staff;

-- Update data using the view
UPDATE it_staff SET salary = 65000 WHERE name = 'Neha';

Here, we created a view to show only IT staff, queried data from it, and even updated the salary through the view.

Summary of the Tutorial

Views in MySQL act as a bridge between complex queries and simple access.
They make your database cleaner, safer, and easier to work with.

In this tutorial, you learned:

  • What a view is and why it’s useful

  • How to create, update, and delete a view

  • How to use views with multiple tables

  • When a view is updatable

  • Advantages and limitations of using views

When you work with large databases, views help you focus only on the data that matters while keeping your queries efficient and organized.

Practice Questions

  1. Create a table named employees with columns emp_id, emp_name, department, and salary. Insert at least 4 sample records.

  2. Write a query to create a view named hr_team that displays only employees from the HR department.

  3. Create a view named high_salary that shows employees whose salary is greater than 50000.

  4. Write a query to display all data from the high_salary view.

  5. Use the CREATE OR REPLACE VIEW statement to modify the hr_team view so it also includes each employee’s salary.

  6. Write a query to delete a view named sales_team from the database.

  7. Create two tables — customers and orders — then create a view named customer_orders that joins both tables and shows the customer name, city, order_id, and amount.

  8. Update the salary of an employee using a view named it_employees. Make sure the change reflects in the original table.

  9. Display all available views in the current database using a MySQL command.

  10. Use SHOW CREATE VIEW to display the SQL definition of a view named customer_orders.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top