MySQL Views

View Benefits

CREATE VIEW public_users AS
SELECT id, username FROM users;

CREATE VIEW order_summary AS
SELECT * FROM orders JOIN customers;

-- Use view like a table
SELECT * FROM active_users;

-- Users see view, not tables
CREATE VIEW reports AS ...

🔹 Creating Views

Create views using the CREATE VIEW statement followed by a SELECT query. The view acts like a table but doesn't store data. You can create views from single tables, multiple joined tables, or even from other views for layered abstraction.

-- Simple view from one table
CREATE VIEW employee_list AS
SELECT employee_id, first_name, last_name, department 
FROM employees;

-- View with JOIN
CREATE VIEW employee_details AS
SELECT e.employee_id, e.first_name, d.department_name, d.location
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- View with calculations
CREATE VIEW product_prices AS
SELECT product_name, price, price * 1.1 AS price_with_tax
FROM products;

🔹 Querying Views

Query views exactly like regular tables using SELECT statements. You can filter, sort, join views with other tables or views, and use aggregate functions. Views automatically reflect changes in underlying tables, always showing current data without storing it.

-- Query a view like a table
SELECT * FROM active_users;

-- Filter view results
SELECT * FROM active_users 
WHERE username LIKE 'j%';

-- Join view with table
SELECT a.username, o.order_date 
FROM active_users a
JOIN orders o ON a.id = o.user_id;

-- Aggregate on view
SELECT COUNT(*) FROM active_users;

🔹 Updating Views

Modify view definitions using CREATE OR REPLACE VIEW. This updates the view without dropping it, preserving permissions. You can also use ALTER VIEW for modifications. Some views are updatable, allowing INSERT, UPDATE, and DELETE operations on underlying tables through the view.

-- Replace existing view
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email, created_at 
FROM users 
WHERE status = 'active';

-- Alternative: Drop and recreate
DROP VIEW IF EXISTS active_users;
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE status = 'active';

🔹 Dropping Views

Remove views using the DROP VIEW statement when they're no longer needed. Dropping a view doesn't affect the underlying tables or their data. Use IF EXISTS to prevent errors if the view doesn't exist. Multiple views can be dropped in one statement.

-- Drop a single view
DROP VIEW active_users;

-- Drop if exists (no error if missing)
DROP VIEW IF EXISTS active_users;

-- Drop multiple views
DROP VIEW IF EXISTS view1, view2, view3;

🔹 Viewing All Views

List all views in your database using SHOW FULL TABLES or query the information_schema database. This helps you manage and document your views. You can also see view definitions using SHOW CREATE VIEW to understand how views are constructed.

-- Show all views in current database
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- Get view definition
SHOW CREATE VIEW active_users;

-- Query information schema
SELECT table_name, table_type 
FROM information_schema.tables 
WHERE table_schema = 'your_database' 
AND table_type = 'VIEW';

🔹 View Best Practices

Use views to simplify complex queries and improve code maintainability. Create views for frequently used queries to ensure consistency. Limit view complexity to maintain performance. Document view purposes and dependencies. Avoid creating views on views excessively as it impacts performance and debugging.