MySQL Views

 

A MySQL View is essentially a ​virtual table​. It does not store any physical data of its own. Instead, it stores a pre-defined SQL SELECT query that looks and acts just like a regular table when you query it.

Think of it as a saved shortcut or a specialized lens through which you can look at one or more underlying tables (known as the ​base tables​).

Why Use a MySQL View?

Views are incredibly powerful tools in database design, primarily used for three reasons:

1. Simplifying Complex Queries

If you regularly write complex queries with multiple INNER JOIN s, GROUP BY clauses, or subqueries, you can wrap that entire mess into a single view.

• Instead of typing: A 30-line query joining customers, enrollments, and courses.
• You can just type: SELECT * FROM view_enrollments;

2. Enhanced Data Security

Views allow you to restrict what data a user can see. If you want a specific employee to see customer names and emails, but never their passwords or financial history, you can build a view that excludes those sensitive columns and grant the employee access only to the view, not the raw table.

3. Data Consistency (Logical Data Independence)

If your database schema changes over time—such as splitting a large table into two smaller ones—you can update the view's internal query to stitch the data back together. The frontend applications querying the view won't notice a thing, preventing your application code from breaking.

How It Works: Syntax

Creating a View

The syntax to create a view is very straightforward:

SQL

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

Using a View

Once created, you query it exactly like a normal table:

SQL

SELECT * FROM view_name;
​

Updating or Replacing a View

If you need to change the logic inside the view, you use CREATE OR REPLACE:

SQL

CREATE OR REPLACE VIEW view_name AS
SELECT new_column1, new_column2
FROM table_name;
​

Key Things to Keep in Mind

• No Performance Magic: Because views don't store data, MySQL runs the underlying query every single time you call the view. If the underlying query is slow and poorly indexed, the view will be slow too.
• Updatable Views: In some simple cases, you can actually run INSERT, UPDATE, or DELETE statements directly on a view, and MySQL will pass those changes down to the base table. However, if the view contains JOIN s, GROUP BY, DISTINCT, or aggregate functions (like SUM or COUNT), it becomes ​read-only​.
• Dropping a View: If you want to delete a view, it won't affect the real data in your database. You just run: DROP VIEW view_name;.

Are you planning to use views to simplify a specific reporting query, or are you looking to use them mainly for security and access control?

Here is the SQL script to create the view_enrollments view.

Since multiple tables share identical column names (such as customerId, sessionId, courseId, and createdAt), standard relational databases (like MySQL) require unique column names in a view. To prevent "duplicate column name" errors while still including every requested column, the overlapping columns have been given descriptive aliases (e.g., enrollment_createdAt).

CREATE OR REPLACE VIEW view_enrollments AS
SELECT 
    -- Customers Table (Excluding password)
    cu.customerId,
    cu.fullName AS customer_fullName,
    cu.dob AS customer_dob,
    cu.gender AS customer_gender,
    cu.icNo AS customer_icNo,
    cu.primaryEmail AS customer_primaryEmail,
    cu.secondaryEmail AS customer_secondaryEmail,
    cu.phoneNo AS customer_phoneNo,
    cu.createdAt AS customer_createdAt,

    -- Enrollments Table (All columns)
    e.enrolId,
    e.customerId AS enrollment_customerId,
    e.sessionId AS enrollment_sessionId,
    e.paymentStatus,
    e.amountPaid,
    e.price AS enrollment_price,
    e.discount,
    e.tax,
    e.sessionStatus,
    e.createdAt AS enrollment_createdAt,

    -- Sessions Table (All columns)
    s.sessionId,
    s.courseId AS session_courseId,
    s.trainerId,
    s.startDateTime,
    s.endDateTime,
    s.minPax,
    s.maxPax,
    s.status AS session_status,
    s.locationName,
    s.locationCity,
    s.locationState,
    s.pricePerPax AS session_pricePerPax,
    s.createdAt AS session_createdAt,

    -- Courses Table (All columns)
    co.courseId,
    co.courseTitle,
    co.courseDescription,
    co.contactHour,
    co.mode,
    co.pricePerPax AS course_pricePerPax,
    co.level,
    co.minAge,
    co.maxAge

FROM enrollments e
INNER JOIN customers cu ON e.customerId = cu.customerId
INNER JOIN sessions s ON e.sessionId = s.sessionId
INNER JOIN courses co ON s.courseId = co.courseId;
​

Key Details:

• Joins Used: INNER JOIN is utilized to link enrollments to customers via customerId, enrollments to sessions via sessionId, and sessions to courses via courseId.
• Security: The password column from the customers table is completely omitted from the selection.
• Disambiguation: Overlapping fields like createdAt and pricePerPax are explicitly aliased with their table prefixes to ensure the view compiles perfectly without naming conflicts.