MySQL Views

A view in MySQL is a virtual table based on the result set of a SQL query. It contains rows and columns just like a real table. The fields in a view are fields from one or more real tables in the database.

What is a View?

• A stored query that acts like a table
• Does not store data physically (except materialized views)
• Updates automatically when base tables change
• Can be used to simplify complex queries
• Provides an additional layer of security

Advantages of Using Views

• Security: Hide sensitive columns
• Simplicity: Simplify complex queries
• Consistency: Ensure consistent data access
• Logical Data Independence: Change underlying tables without affecting applications
• Performance: Optimize query execution

Creating Views

Basic Syntax

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

1. Simple View

-- Create a view for active customers
CREATE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email, phone
FROM customers
WHERE status = 'active';

2. View with Joins

-- Create a view combining employee and department data
CREATE VIEW employee_details AS
SELECT 
  e.employee_id,
  e.first_name,
  e.last_name,
  d.department_name,
  e.salary,
  e.hire_date
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

3. View with Calculations

-- Create a view with calculated columns
CREATE VIEW sales_summary AS
SELECT 
  product_id,
  product_name,
  unit_price,
  quantity_in_stock,
  unit_price * 0.8 AS discounted_price,
  unit_price * quantity_in_stock AS total_value
FROM products;

4. View with Aggregation

-- Create a view for department statistics
CREATE VIEW department_stats AS
SELECT 
  d.department_name,
  COUNT(e.employee_id) AS employee_count,
  AVG(e.salary) AS avg_salary,
  MAX(e.salary) AS max_salary,
  MIN(e.salary) AS min_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id;

Using Views

1. Select from View

-- Use like a regular table
SELECT * FROM employee_details WHERE salary > 50000;

2. Join Views

-- Join view with table
SELECT 
  ed.first_name,
  ed.department_name,
  s.sale_amount
FROM employee_details ed
INNER JOIN sales s ON ed.employee_id = s.employee_id;

3. Update Through View

-- Update data through view (if updatable)
UPDATE active_customers 
SET email = 'new@email.com' 
WHERE customer_id = 101;

Managing Views

View All Views

-- Show all views in database
SHOW FULL TABLES WHERE TABLE_TYPE LIKE 'VIEW';

View Definition

-- Show how a view was created
SHOW CREATE VIEW view_name;

Alter View

-- Modify an existing view
CREATE OR REPLACE VIEW view_name AS
SELECT new_column_list
FROM table_name
WHERE new_conditions;

Drop View

-- Remove a view
DROP VIEW view_name;

-- Drop only if exists
DROP VIEW IF EXISTS view_name;

Types of Views

View Security

-- Create view with specific columns (hide sensitive data)
CREATE VIEW public_employee_info AS
SELECT 
  employee_id,
  first_name,
  last_name,
  department_name,
  hire_date
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Grant access only to view, not underlying tables
GRANT SELECT ON public_employee_info TO 'report_user'@'localhost';

Best Practices

• Use descriptive view names
• Document view purposes and dependencies
• Avoid nested views (views based on other views)
• Consider performance implications
• Use views for security, not just convenience
• Test views thoroughly before deployment

Limitations

• Some views are not updatable
• Performance overhead on complex views
• Cannot create indexes directly on views
• Dependencies can cause issues when dropping tables
• MySQL doesn't have built-in materialized views