MySQL Views: Create, Join & Drop with Examples

A View in MySQL is a virtual table based on the result of a SQL query. It does not store data itself but provides a dynamic way to simplify complex queries and improve security by limiting direct access to tables.

🔹 Creating a View in MySQL

A view is created using the CREATE VIEW statement. It allows you to store a SQL query as a virtual table.

✅ Example: Create a Simple View

CREATE VIEW user_view AS
SELECT id, name, email FROM users;

✔ This view (user_view) will always return id, name, and email from the users table.

🔸 Selecting Data from a View

You can use the view just like a table:

SELECT * FROM user_view;

🔹 Joining Multiple Tables in a View

A view can combine multiple tables using JOIN.

✅ Example: View with JOIN

CREATE VIEW order_details AS
SELECT orders.id, orders.order_date, customers.name AS customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.id;

✔ This view joins orders with customers to show order_date and customer_name.

🔹 Updating a View

You can modify an existing view using CREATE OR REPLACE VIEW:

CREATE OR REPLACE VIEW user_view AS
SELECT id, name, email, created_at FROM users;

✔ This updates user_view to include the created_at column.

🔹 Dropping a View

To delete a view, use:

DROP VIEW user_view;

🔹 Using Views in JavaScript (Node.js)

To fetch data from a MySQL view using JavaScript with mysql2:

✅ Example: Fetch Data from a View

const mysql = require('mysql2');

const connection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "my_database"
});

connection.connect();

connection.query("SELECT * FROM user_view", (err, results) => {
    if (err) throw err;
    console.log(results);
});

connection.end();

✔ This connects to MySQL and retrieves data from the user_view view.

🎯 Summary

✔ Views simplify queries by storing complex logic.
✔ JOINs in views allow combining multiple tables.
✔ Views do not store data but dynamically fetch results.
✔ CREATE OR REPLACE VIEW updates a view.
✔ DROP VIEW deletes a view.
✔ You can use Node.js & MySQL to query views in JavaScript apps.

🚀 Need more help? Let me know! 😊

Souy Soeng

MySQL Views: Create, Join & Drop with Examples