Day 43: MySQL Views - Creating Virtual Tables for Better Querying!
Sarasa Jyothsna Kamireddi
Aspiring Python Developer | Machine Learning Enthusiast | Experienced in Reliability Engineering
Today, let's explore?MySQL Views, a powerful feature that creates virtual tables to simplify complex queries and enhance security!
What is a View in MySQL?
A View is a?virtual table?that represents the result of an SQL query. It doesn't store data itself but?displays data from one or more tables dynamically?when a query is made.
? Key Benefits of Views
?? Simplifies complex queries by storing them as a reusable object
?? Enhances data security by restricting access to certain columns
?? Improves data abstraction by providing a cleaner representation
?? Helps maintain consistent reports without modifying the underlying tables
1. Creating a Simple View
? Example: View to Show Active Customers
CREATE VIEW ActiveCustomers AS
SELECT customer_id, name, email
FROM customers
WHERE status = 'active';
(Provides a filtered view of active customers without exposing the full table.)
? Querying the View
SELECT * FROM ActiveCustomers;
(Returns only active customers.)
2. Creating a View with Joins
? Example: View to Display Order Details with Customer Names
CREATE VIEW OrderDetails AS
SELECT o.order_id, c.name AS customer_name, o.total_amount, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
(Simplifies querying order details with customer names.)
? Fetching Data from the View
SELECT * FROM OrderDetails WHERE total_amount > 500;
(Returns orders above 500 with customer names)
3. Updating Data Using Views
Views based on single tables can sometimes be updated!
? Example: Modify a Customer's Email Using a View
UPDATE ActiveCustomers
SET email = '[email protected]'
WHERE customer_id = 101;
(Changes are reflected in the original table!)
Note: Views containing JOIN, GROUP BY, or DISTINCT are not updatable in MySQL
4. Deleting a View
? Remove an Unused View
DROP VIEW IF EXISTS OrderDetails;
(Deletes the view if it exists)
When Should We Use Views?
?? When we need to simplify complex queries
?? When we want to restrict access to sensitive columns
?? When we want to reuse frequently used queries
#100DaysOfCode #MySQL #SQL #Database #Views #DataSecurity #Learning #BackendDevelopment