Views in MySQL

  • By Mahesh Kankrale
  • March 24, 2025
  • SQL
Views in MySQL

Views in MySQL

Views in SQL are a kind of virtual table. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain conditions.

 

What is the Benefit of Using Views in MySQL?

Views help particularly in the following ways:

  1. Simplicity: Instead of writing complex joins & queries, views provide a way of writing simple SELECT statements.
  2. Enhanced Security: Views expose only the data to the third-party apps and hide the internal details like table structure, attributes, etc, thus adding extra security.
  3. Consistency: By writing views instead of common queries, we can write a view that avoids multiple declarations & definitions of the same queries and eventually provides a centralized way.

Syntax :

CREATE VIEW view_name AS 

SELECT col_name1, col_name2..

FROM table_name

WHERE [condition];

 

Example 1:

  1. CREATE VIEW detail_view AS 

SELECT *

FROM employee

WHERE gender = ‘male’ ;

 

Example 2 : 

View for Employees in a Specific Department

This view shows all employees who belong to a specific department, e.g., “Sales”.

CREATE VIEW sales_department_employees AS

SELECT *

FROM employee

WHERE department = ‘Sales‘;

Querying the Sales Department Employees View

SELECT * FROM sales_department_employees;

 

Example 3 : 

Creating a View for High-Salary Employees.

Let’s say we want a view to show the details of employees who earn more than 60,000.

 

CREATE VIEW high_salary_employees AS

SELECT employee_id, name, department, salary

FROM employee

WHERE salary > 60000;

Querying the High Salary View

SELECT * FROM high_salary_employees;

 

Update View in MySQL

There are certain conditions that need to be satisfied to update a view. If any one of these conditions is not met, then we are not allowed to update the view.

  • It is based on a single table.
  • It doesn’t include aggregate functions (SUM, COUNT, etc.).
  • It doesn’t involve DISTINCT, GROUP BY, HAVING, or subqueries.
  • It doesn’t use UNION or JOIN.
  • It includes all non-nullable columns in the SELECT statement.

Syntax :

UPDATE view_name 

SET col_name1 = value1, col_name2= value2 ….

WHERE [condition];

Example 1:

UPDATE employee_age_groups

SET salary = 60000

WHERE emp_id = 101;

 

For Free, Demo classes Call: 020 7117 1500

Registration Link: Click Here!

 

DROP VIEW in MySQL

The DROP VIEW statement is used to delete an existing view from the database. Once a view is dropped, it is permanently removed, and any subsequent attempts to query or modify it will result in an error unless the view is recreated.

Syntax :

DROP VIEW [IF EXISTS] view_name , [view_name2]…;

Example 1:

DROP VIEW employee_age_groups;

Example 2:

DROP VIEW employee_age_groups, top_profitable_products;

 

When to Use DROP VIEW

When a view is no longer needed.

To recreate a view with entirely different logic (although CREATE OR REPLACE VIEW is usually preferable in such cases).

To clean up the database by removing unused or outdated views.

 

Do watch our Channel to learn more: Click Here

Author:

Mahesh Kankrale

Call the Trainer and Book your free demo Class For SQL Call now!!!
| SevenMentor Pvt Ltd.

© Copyright 2021 | SevenMentor Pvt Ltd.