MySQL - 创建视图

MySQL：创建和使用视图

MySQL 视图是一个存储的 SELECT 查询，它被视为一个虚拟表。它本身不存储数据，但提供了一种命名、可重用且结构化的方式来查看一个或多个底层表中的数据。视图是用于安全性、简单性和抽象的基本数据库对象。

使用视图的主要好处包括：

• 简化复杂性： 视图可以将复杂的联表查询或聚合封装到一个简单、易于查询的虚拟表中。
• 增强安全性： 您可以授予用户访问视图的权限，该视图仅暴露特定列或行，从而隐藏底层表中的敏感数据。
• 提供稳定的 API： 即使底层表结构被重构或规范化，视图也能为应用程序提供一致的接口。

CREATE VIEW 语句

创建视图涉及定义一个 SELECT 语句并为其命名。MySQL 将此定义存储在数据库架构中。

语法

创建视图的基本语法是：

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

• OR REPLACE: 一个可选子句，允许您修改现有视图而无需先 DROP 它。
• column_list: 视图列的可选名称列表。如果省略，名称将从 select_statement 中派生。
• WITH CHECK OPTION: 一个可选子句，用于强制视图的 WHERE 条件对在该视图上执行的任何 INSERT 或 UPDATE 操作生效。

实际示例

首先，让我们创建一个示例 CUSTOMERS 表来操作。

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);

示例 1：活动客户的简单视图

此视图通过仅显示活动客户及其列的子集来简化查询。

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

查询视图

创建后，您可以像查询常规表一样查询视图。

SELECT * FROM active_customers WHERE SALARY > 6000;

结果从视图公开的数据中筛选而出：

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

示例 2：带聚合的视图

视图非常适合汇总数据。此视图计算每个城市的平均工资。

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

查询此视图会为您提供预先计算的报告：

SELECT * FROM salary_by_city ORDER BY average_salary DESC;

注意： 包含聚合（GROUP BY、AVG 等）、DISTINCT、UNION 或联接的视图通常不可更新。

WITH CHECK OPTION

WITH CHECK OPTION 是一个强大的功能，它为可更新视图强制执行数据完整性。它阻止通过视图执行的 INSERT 或 UPDATE 操作创建视图中不可见的行。

示例 3：强制执行业务规则

让我们为 ‘Mumbai’ 的客户创建一个视图，并确保通过此视图插入的任何新记录也必须是针对 ‘Mumbai’ 的。

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

此 UPDATE 语句将成功，因为该行在视图中仍然可见：

UPDATE mumbai_customers SET SALARY = 7000.00 WHERE ID = 4; -- 成功

然而，此 UPDATE 将失败，因为它尝试更改地址，这将使该行对 mumbai_customers 视图不可见。

UPDATE mumbai_customers SET ADDRESS = 'Delhi' WHERE ID = 4;
-- 失败，并出现错误：CHECK OPTION failed 'your_database.mumbai_customers'

在客户端应用程序中管理视图

您可以使用任何语言中的标准 SQL 命令以编程方式管理视图。以下是一些现代示例。

Python 示例 (使用 mysql-connector-python)

import mysql.connector

# 最佳实践：使用配置文件或环境变量
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.")

            # 现在查询新视图
            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}")

# 预期输出：
# 视图 'high_earners' 创建或替换成功。
# - 姓名：Hardik，薪水：8500.00
# - 姓名：Komal，薪水：9000.00

Node.js 示例 (使用 mysql2/promise 和 async/await)

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

const dbConfig = {
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'TUTORIALS',
    multipleStatements: true // 多查询必需
};

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();

// 预期输出：
// 视图 'customer_summary' 已创建/重新创建。
// 视图数据：
// ...（显示首字母和计数器的表格输出）

Java 示例 (使用 JDBC 和 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();
        }
    }
}
// 预期输出：
// 视图 'inactive_customers' 创建成功。

PHP 示例 (使用 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());
}

// 预期输出：
// 视图 'customers_by_age_group' 创建成功。