MySQL - Create Views

MySQL: Creating and Using Views

A MySQL View is a stored SELECT query that is treated as a virtual table. It doesn’t store data itself but provides a named, reusable, and structured way to look at data from one or more underlying tables. Views are a fundamental database object used for security, simplicity, and abstraction.

Key benefits of using views include:

• Simplifying Complexity: A view can encapsulate a complex multi-table join or aggregation into a single, simple virtual table that is easy to query.
• Enhancing Security: You can grant users access to a view that exposes only specific columns or rows, hiding sensitive data from the underlying tables.
• Providing a Stable API: A view can provide a consistent interface to applications, even if the underlying table structures are refactored or normalized.

The CREATE VIEW Statement

Creating a view involves defining a SELECT statement and giving it a name. MySQL stores this definition in the database schema.

Syntax

The basic syntax for creating a view is:

CREATE [OR REPLACE]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION];

• OR REPLACE: An optional clause that allows you to modify an existing view without having to DROP it first.
• column_list: An optional list of names for the view’s columns. If omitted, the names are derived from the select_statement.
• WITH CHECK OPTION: An optional clause that enforces the WHERE condition of the view on any INSERT or UPDATE operations performed on the view.

Practical Examples

First, let’s create a sample CUSTOMERS table to work with.

CREATE TABLE CUSTOMERS(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(255),
    SALARY DECIMAL(18, 2),
    IS_ACTIVE BOOLEAN DEFAULT TRUE
);

INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY, IS_ACTIVE) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00, TRUE),
('Khilan', 25, 'Delhi', 1500.00, TRUE),
('Kaushik', 23, 'Kota', 2500.00, FALSE),
('Chaitali', 26, 'Mumbai', 6500.00, TRUE),
('Hardik', 27, 'Bhopal', 8500.00, TRUE),
('Komal', 22, 'Hyderabad', 9000.00, FALSE),
('Muffy', 24, 'Indore', 5500.00, TRUE);

Example 1: A Simple View for Active Customers

This view simplifies querying by showing only the active customers and a subset of their columns.

CREATE OR REPLACE VIEW active_customers AS
SELECT ID, NAME, ADDRESS, SALARY
FROM CUSTOMERS
WHERE IS_ACTIVE = TRUE;

Querying the View

Once created, you can query the view just like a regular table.

SELECT * FROM active_customers WHERE SALARY > 6000;

The result is filtered from the data exposed by the view:

ID	NAME	ADDRESS	SALARY
4	Chaitali	Mumbai	6500.00
5	Hardik	Bhopal	8500.00

Example 2: A View with an Aggregation

Views are excellent for summarizing data. This view calculates the average salary per city.

CREATE VIEW salary_by_city AS
SELECT ADDRESS, COUNT(ID) AS number_of_customers, AVG(SALARY) AS average_salary
FROM CUSTOMERS
GROUP BY ADDRESS;

Querying this view gives you a pre-calculated report:

SELECT * FROM salary_by_city ORDER BY average_salary DESC;

Note: Views containing aggregations (GROUP BY, AVG, etc.), DISTINCT, UNION, or joins are generally not updatable.

The WITH CHECK OPTION

The WITH CHECK OPTION is a powerful feature that enforces data integrity for updatable views. It prevents INSERT or UPDATE operations that would create rows not visible through the view.

Example 3: Enforcing a Business Rule

Let’s create a view for customers in ‘Mumbai’ and ensure that any new records inserted through this view must also be for ‘Mumbai’.

CREATE OR REPLACE VIEW mumbai_customers AS
SELECT ID, NAME, ADDRESS, SALARY
FROM CUSTOMERS
WHERE ADDRESS = 'Mumbai'
WITH CHECK OPTION;

This UPDATE statement will succeed because the row remains visible in the view:

UPDATE mumbai_customers SET SALARY = 7000.00 WHERE ID = 4; -- Success

However, this UPDATE will fail because it attempts to change the address, which would make the row invisible to the mumbai_customers view.

UPDATE mumbai_customers SET ADDRESS = 'Delhi' WHERE ID = 4;
-- Fails with error: CHECK OPTION failed 'your_database.mumbai_customers'

Managing Views in Client Applications

You can manage views programmatically using standard SQL commands in any language. Here are some modern examples.

Python Example (with mysql-connector-python)

import mysql.connector

# Best practice: use a config file or environment variables
config = {'user': 'root', 'password': 'your_password', 'host': '127.0.0.1', 'database': 'TUTORIALS'}

create_view_query = """
CREATE OR REPLACE VIEW high_earners AS
SELECT NAME, SALARY
FROM CUSTOMERS
WHERE SALARY >= 8000
"""

try:
    with mysql.connector.connect(**config) as conn:
        with conn.cursor() as cursor:
            cursor.execute(create_view_query)
            print("View 'high_earners' created or replaced successfully.")

            # Now query the new view
            cursor.execute("SELECT * FROM high_earners")
            for row in cursor.fetchall():
                print(f"- Name: {row[0]}, Salary: {row[1]}")

except mysql.connector.Error as err:
    print(f"Error: {err}")

# Expected Output:
# View 'high_earners' created or replaced successfully.
# - Name: Hardik, Salary: 8500.00
# - Name: Komal, Salary: 9000.00

Node.js Example (with mysql2/promise and async/await)

const mysql = require('mysql2/promise');

const dbConfig = {
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'TUTORIALS',
    multipleStatements: true // Required for multiple queries
};

async function manageViews() {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);
        const createViewSql = `
        CREATE OR REPLACE VIEW customer_summary AS
        SELECT
            SUBSTRING(NAME, 1, 1) as initial,
            COUNT(*) as name_count
        FROM CUSTOMERS
        GROUP BY initial;`;

        await connection.query(createViewSql);
        console.log("View 'customer_summary' created/re-created.");

        const [rows] = await connection.query('SELECT * FROM customer_summary ORDER BY initial;');
        console.log("Data from view:");
        console.table(rows);

    } catch (error) {
        console.error('Database operation failed:', error);
    } finally {
        if (connection) await connection.end();
    }
}

manageViews();

// Expected Output:
// View 'customer_summary' created/re-created.
// Data from view:
// ... (table output showing initials and counts)

Java Example (with JDBC and try-with-resources)

import java.sql.*;

public class CreateViewExample {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASS = "your_password";

    public static void main(String[] args) {
        String createViewSql = "CREATE OR REPLACE VIEW inactive_customers AS " +
                               "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE IS_ACTIVE = FALSE";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement()) {

            stmt.executeUpdate(createViewSql);
            System.out.println("View 'inactive_customers' created successfully.");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
// Expected Output:
// View 'inactive_customers' created successfully.

PHP Example (with PDO)

<?php
$dsn = 'mysql:host=localhost;dbname=TUTORIALS;charset=utf8mb4';
$user = 'root';
$pass = 'your_password';

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];

$createViewSql = """
CREATE OR REPLACE VIEW customers_by_age_group AS
    SELECT
        CASE
            WHEN AGE < 25 THEN 'Under 25'
            WHEN AGE BETWEEN 25 AND 30 THEN '25-30'
            ELSE 'Over 30'
        END as age_group,
        COUNT(*) as customer_count
    FROM CUSTOMERS
    GROUP BY age_group;
""";

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    $pdo->exec($createViewSql);
    echo "View 'customers_by_age_group' created successfully.";
} catch (PDOException $e) {
    die("Error creating view: " . $e->getMessage());
}

// Expected Output:
// View 'customers_by_age_group' created successfully.