Menu
MySQL

Views in MySQL

A view in MySQL is a virtual table created from a SELECT query that provides a way to look at data in one or more underlying tables. A view does not store data itself but dynamically generates data from the base tables whenever the view is queried. Views help in simplifying complex queries, enhancing security, and ensuring data abstraction.

 

 

1. Key Characteristics of Views

  • Virtual Table: A view is not a real table but a query stored in the database. When you query a view, MySQL runs the query and returns the result set as if it were a table.
  • Simplifies Queries: Views can simplify complex queries, especially those involving multiple joins or subqueries.
  • Data Abstraction: Views can hide certain columns or join multiple tables, providing an abstraction layer over the database schema.
  • Security: By creating views, you can control which data a user can access, limiting exposure to sensitive information in the base tables.
  • Updatable Views: In some cases, views can be updated (inserting, updating, or deleting data) if they are created from a single table and adhere to certain conditions.

 

 

2. Creating a View

Syntax:

CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;
 

Example:

Creating a view that shows the first name, last name, and department of employees:

CREATE VIEW employee_view AS
SELECT first_name, last_name, department
FROM employees;

Now, querying employee_view will return the first name, last name, and department of employees.

SELECT * FROM employee_view;

 

 

3. Benefits of Views

Simplifying Complex Queries:

Views allow you to encapsulate complex SQL queries into simple, reusable queries. This reduces the need for users to write complicated SQL and ensures consistency.

Example:

A complex query with multiple joins and filters can be simplified using a view.

CREATE VIEW sales_summary AS
SELECT customers.customer_name, SUM(orders.total_amount) AS total_sales
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_name;

Now, to get the total sales per customer, simply run:

SELECT * FROM sales_summary;
 

 


4. Updating Data Through Views

Some views allow you to insert, update, or delete rows, but this is only possible under certain conditions. The view must be simple, typically derived from a single table without aggregation, GROUP BY, or DISTINCT clauses.

Updatable View Example:

Assuming a view is created from a single table:

CREATE VIEW simple_employee_view AS
SELECT employee_id, first_name, last_name, department
FROM employees;

You can update data through this view:

UPDATE simple_employee_view
SET department = 'Sales'
WHERE employee_id = 5;

 

This will update the department column in the employees table where employee_id = 5.

 

 

 

5. Modifying Views

Updating a View:

You can modify an existing view using the CREATE OR REPLACE VIEW statement.

CREATE OR REPLACE VIEW employee_view AS
SELECT first_name, last_name, department, hire_date
FROM employees;

This will replace the previous definition of employee_view.

Dropping a View:

To remove a view, use the DROP VIEW statement.

DROP VIEW IF EXISTS employee_view;

 

 

 

6. Managing Views

Show All Views in the Database:

To list all views in a database, use the following query:

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE = 'VIEW';
 

View Definition:

To see the SQL definition of a view:

SHOW CREATE VIEW view_name;
 

 

 

MySQL