Querying from views in MySQL

Introduction

Views let you save a query as a virtual table. Querying from views helps you reuse complex queries easily without rewriting them.

You want to simplify repeated complex queries for reports.

You want to hide complex joins and calculations from users.

You want to provide a consistent data format to different users.

You want to restrict access to certain columns or rows by showing only a view.

You want to organize your database queries better for maintenance.

Syntax

MySQL

SELECT column1, column2 FROM view_name WHERE condition;

A view acts like a table but does not store data itself.

You can use WHERE, ORDER BY, and other clauses when querying a view.

Examples

Selects all columns and rows from the view named employee_view.

MySQL

SELECT * FROM employee_view;

Gets names and salaries of employees earning more than 50,000 from the view.

MySQL

SELECT name, salary FROM employee_view WHERE salary > 50000;

Counts employees in each department using the view.

MySQL

SELECT department, COUNT(*) FROM employee_view GROUP BY department;

Sample Program

This creates a view called employee_view that selects some columns from the employees table. Then it queries the view to find employees with salary over 60,000.

MySQL

CREATE VIEW employee_view AS
SELECT id, name, department, salary FROM employees;

SELECT name, salary FROM employee_view WHERE salary > 60000;

Important Notes

Views do not store data themselves; they run the saved query each time you use them.

Updating data through views is possible only if the view is simple and meets certain rules.

Summary

Views let you save and reuse queries as virtual tables.

You query views just like normal tables.

Views help simplify complex queries and improve security.