怎样在 MySQL 8 中使用视图

介绍

在 MySQL 中,视图是用于运行预定义查询的虚拟表。 因此,它们适用于需要严格安全性的数据库应用程序。 例如,您可以设计一个应用程序,允许最终用户使用视图运行复杂查询,而无需直接与基表交互以隐藏敏感信息。 在这种情况下,连接到您的数据库的客户端将仅查看相关列。 这减少了数据干扰,并允许您将一些业务逻辑移动到数据库中。

简单来说,你可以定义 MySQL 用户,只拥有 SELECT 视图权限,没有其他数据库权限。 在本指南中,您将在 MySQL 8.0 数据库服务器上创建、使用和删除视图。

先决条件

要完成本 MySQL 视图教程,您需要:

Vultr Linux 服务器 非 root 用户 A LAMP Stack

1. 创建示例 MySQL 数据库

SSH 到您的 Linux 服务器。

以身份登录到您的 MySQL 服务器 root.

$ sudo mysql -u root -p

输入 root 输入数据库服务器的密码,然后按 ENTER 继续。

创建示例 web_store 数据库。

mysql> CREATE DATABASE web_store;

切换到新数据库。

mysql> USE web_store;

2. 创建和填充示例表

当您实现视图以从多个表运行复杂查询时,视图可以节省您的时间。 首先,通过完成以下步骤创建两个表并填充它们。

创建一个 products 表以在您的示例商店中存储一些项目。

mysql> CREATE TABLE products
       (
           product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
           product_name VARCHAR(50),
           retail_price DOUBLE
       ) ENGINE = InnoDB;

填充 products 表,但不要分配 product_id 列值。 当您向其中插入记录时,MySQL 会自动分配新值 AUTO_INCREMENT 场地。

mysql> INSERT INTO products(product_name, retail_price) VALUES ('CLOUD COMPUTE', 10); 
       INSERT INTO products(product_name, retail_price) VALUES ('BARE METAL', 185); 
       INSERT INTO products(product_name, retail_price) VALUES ('DEDICATED CLOUD', 60); 
       INSERT INTO products(product_name, retail_price) VALUES ('MANAGED DABASES', 15);

验证数据来自 products 桌子。

mysql> SELECT
           product_id,
           product_name,
           retail_price
       FROM products;

输出。

+------------+-----------------+--------------+
| product_id | product_name    | retail_price |
+------------+-----------------+--------------+
|          1 | CLOUD COMPUTE   |           10 |
|          2 | BARE METAL      |          185 |
|          3 | DEDICATED CLOUD |           60 |
|          4 | MANAGED DABASES |           15 |
+------------+-----------------+--------------+
4 rows in set (0.00 sec)

创建一个 sales_products 桌子。 此表存储您的示例商店中销售的产品数量。 要规范化数据库,不要重复 product_names 在这张表中; 只需使用 product_id 来识别产品。 稍后,您将使用 MySQL JOIN 从产品名称中拼出产品名称的声明 products 桌子。

mysql> CREATE TABLE sales_products
       (
           ref_id BIGINT PRIMARY KEY AUTO_INCREMENT,
           product_id BIGINT,
           unit_price DOUBLE,
           qty DOUBLE
       ) ENGINE = InnoDB;

填充 sales_products 包含一些数据的表格。

mysql> INSERT INTO sales_products(product_id, unit_price, qty) VALUES (1, 10, 5); 
       INSERT INTO sales_products(product_id, unit_price, qty) VALUES (4, 15, 18); 
       INSERT INTO sales_products(product_id, unit_price, qty) VALUES (2, 185, 7); 
       INSERT INTO sales_products(product_id, unit_price, qty) VALUES (2, 185, 4); 
       INSERT INTO sales_products(product_id, unit_price, qty) VALUES (4, 15, 1); 
       INSERT INTO sales_products(product_id, unit_price, qty) VALUES (1, 10, 6); 
       INSERT INTO sales_products(product_id, unit_price, qty) VALUES (2, 185, 10); 
       INSERT INTO sales_products(product_id, unit_price, qty) VALUES (2, 185, 11); 
       INSERT INTO sales_products(product_id, unit_price, qty) VALUES (4, 15, 18); 
       INSERT INTO sales_products(product_id, unit_price, qty) VALUES (1, 10, 8); 
       INSERT INTO sales_products(product_id, unit_price, qty) VALUES (2, 185, 4);     

查询 sales_products 表以确认记录。

mysql> SELECT
           product_id,
           unit_price,
           qty
       FROM sales_products;

输出。

+------------+------------+------+
| product_id | unit_price | qty  |
+------------+------------+------+
|          1 |         10 |    5 |
|          4 |         15 |   18 |
|          2 |        185 |    7 |
|          2 |        185 |    4 |
|          4 |         15 |    1 |
|          1 |         10 |    6 |
|          2 |        185 |   10 |
|          2 |        185 |   11 |
|          4 |         15 |   18 |
|          1 |         10 |    8 |
|          2 |        185 |    4 |
+------------+------------+------+
11 rows in set (0.00 sec)

3. 创建和调用 MySQL 视图

在 MySQL 中,按照以下语法创建视图。

mysql>  CREATE VIEW SAMPLE_VIEW
        AS 
        SAMPLE_SELECT STATEMENT;

例如,创建一个 sold_products 视图链接 productssales_products 表获取 line_total 对于每条记录和各自的 product_name 用一个 JOIN 陈述。 视图的 SQL 查询可能很长,但在调用它时,您将始终受益于运行更简单的查询。

mysql>  CREATE VIEW sold_products
        AS 
        SELECT
            sales_products.product_id,                    
            product_name,
            sales_products.qty,
            sales_products.unit_price,
            (
                sales_products.unit_price * sales_products.qty
            ) as line_total
        FROM sales_products
        LEFT JOIN products
        ON sales_products.product_id = products.product_id;

调用 sold_products 通过运行查看 SELECT 对对象的语句,就像对普通表一样。 如您所见,您的 SQL 命令 sold_products 视图简短而简单。

mysql>  SELECT 
        *                 
        FROM sold_products;

在下面的输出中,您生成了 product_names 从基地 products 表并计算出 line_total 只需执行一个简单的视图语句。

+------------+-----------------+------+------------+------------+
| product_id | product_name    | qty  | unit_price | line_total |
+------------+-----------------+------+------------+------------+
|          1 | CLOUD COMPUTE   |    5 |         10 |         50 |
|          4 | MANAGED DABASES |   18 |         15 |        270 |
|          2 | BARE METAL      |    7 |        185 |       1295 |
|          2 | BARE METAL      |    4 |        185 |        740 |
|          4 | MANAGED DABASES |    1 |         15 |         15 |
|          1 | CLOUD COMPUTE   |    6 |         10 |         60 |
|          2 | BARE METAL      |   10 |        185 |       1850 |
|          2 | BARE METAL      |   11 |        185 |       2035 |
|          4 | MANAGED DABASES |   18 |         15 |        270 |
|          1 | CLOUD COMPUTE   |    8 |         10 |         80 |
|          2 | BARE METAL      |    4 |        185 |        740 |
+------------+-----------------+------+------------+------------+
11 rows in set (0.00 sec)

您还可以使用 MySQL 聚合函数,例如 SUM 创建视图时。 例如,要为您的产品生成销售汇总报告,请创建一个 sales_summary 看法。

mysql>  CREATE VIEW sales_summary
        AS 
        SELECT
            sales_products.product_id,                    
            product_name,
            sum(sales_products.qty) as units_sold,                    
            SUM(
                (
                sales_products.unit_price * sales_products.qty
                )
            ) as total_sales
        FROM sales_products
        LEFT JOIN products
        ON sales_products.product_id = products.product_id
        GROUP BY sales_products.product_id           
        ;

跑过 sales_summary 看法。

mysql>  SELECT 
        *                 
        FROM sales_summary;

您现在应该会看到您已售出的总单位列表以及您在示例商店中产生的总收入。

+------------+-----------------+------------+-------------+
| product_id | product_name    | units_sold | total_sales |
+------------+-----------------+------------+-------------+
|          1 | CLOUD COMPUTE   |         19 |         190 |
|          4 | MANAGED DABASES |         37 |         555 |
|          2 | BARE METAL      |         36 |        6660 |
+------------+-----------------+------------+-------------+
3 rows in set (0.00 sec) 

在上面的输出中,产品 DEDICATED CLOUDproduct_id3 丢失,因为它没有条目 sales_products 桌子。 但是,在 MySQL 中,您可以使用 JOIN 在运行查询时将用户定义的视图与普通表链接的语句。 例如,加入 sales_summary 视图和 products 表以生成更有意义的报告,其中包括具有以下特征的产品 0 销售量。

mysql>  SELECT 
            products.product_id,
            products.product_name,
            IFNULL(sales_summary.units_sold, 0) AS units_sold,  
            IFNULL(sales_summary.total_sales, 0) AS total_sales         
        FROM products
        LEFT JOIN sales_summary
        ON products.product_id = sales_summary.product_id;

您现在可以查看产品,包括那些带有 0 销售情况如下图。

+------------+-----------------+------------+-------------+
| product_id | product_name    | units_sold | total_sales |
+------------+-----------------+------------+-------------+
|          1 | CLOUD COMPUTE   |         19 |         190 |
|          2 | BARE METAL      |         36 |        6660 |
|          3 | DEDICATED CLOUD |          0 |           0 |
|          4 | MANAGED DABASES |         37 |         555 |
+------------+-----------------+------------+-------------+
4 rows in set (0.00 sec)

4. 使用视图提供数据库安全性

要查看 MySQL 视图的安全方面如何工作,请执行以下步骤。

创建一个新的 MySQL 用户。

mysql> CREATE USER 'sample_v_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';

授予 sample_v_user 运行的特权 sold_productssales_summary 意见。 不要向 sample_v_user.

mysql> GRANT SELECT ON web_store.sold_products TO 'sample_v_user'@'localhost';
       GRANT SELECT ON web_store.sales_summary TO 'sample_v_user'@'localhost';     
       FLUSH PRIVILEGES;

退出你的 root 来自 MySQL 服务器的会话。

mysql> EXIT;

重新登录为 sample_v_user.

$ mysql -u sample_v_user -p

输入密码 sample_v_user 然后按 ENTER 登录。然后,切换到 web_store 数据库。

mysql> USE web_store;

跑过 sold_products 看法。

mysql>  SELECT 
        *                 
        FROM sold_products;

输出。

+------------+-----------------+------+------------+------------+
| product_id | product_name    | qty  | unit_price | line_total |
+------------+-----------------+------+------------+------------+
|          1 | CLOUD COMPUTE   |    5 |         10 |         50 |
|          4 | MANAGED DABASES |   18 |         15 |        270 |
|          2 | BARE METAL      |    7 |        185 |       1295 |
|          2 | BARE METAL      |    4 |        185 |        740 |
|          4 | MANAGED DABASES |    1 |         15 |         15 |
|          1 | CLOUD COMPUTE   |    6 |         10 |         60 |
|          2 | BARE METAL      |   10 |        185 |       1850 |
|          2 | BARE METAL      |   11 |        185 |       2035 |
|          4 | MANAGED DABASES |   18 |         15 |        270 |
|          1 | CLOUD COMPUTE   |    8 |         10 |         80 |
|          2 | BARE METAL      |    4 |        185 |        740 |
+------------+-----------------+------+------------+------------+
11 rows in set (0.00 sec)

跑过 sales_summary 看法。

mysql>  SELECT 
        *                 
        FROM sales_summary;

输出。

+------------+-----------------+------------+-------------+
| product_id | product_name    | units_sold | total_sales |
+------------+-----------------+------------+-------------+
|          1 | CLOUD COMPUTE   |         19 |         190 |
|          4 | MANAGED DABASES |         37 |         555 |
|          2 | BARE METAL      |         36 |        6660 |
+------------+-----------------+------------+-------------+
3 rows in set (0.00 sec)

作为 sample_v_user,您可以运行视图。 现在,查询 productssales_products 基表。

products 桌子。

mysql>  SELECT 
        *                 
        FROM products;

sales_products 桌子。

mysql>  SELECT 
        *                 
        FROM sales_products;

您现在应该会收到以下错误,因为您无权访问这些基表。

ERROR 1142 (42000): SELECT command denied to user 'sample_v_user'@'localhost' for table 'products'
ERROR 1142 (42000): SELECT command denied to user 'sample_v_user'@'localhost' for table 'sales_products'

在生产环境中,您可以使用 MySQL 视图隐藏包含敏感信息(例如用户密码、地址、信用卡号等)的表或列。

退出 MySQL 服务器。

mysql> EXIT;

5. 删除 MySQL 视图

就像其他数据库对象一样,您可以 DROP 一个 MySQL 视图,如果你不再需要它或者你想重新定义它的 SQL 语句。

按照下面的语法 DROP 一个看法。

mysql> DROP VIEW IF EXISTS SAMPLE_VIEW;

例如,到 DROPsales_summary 查看,以root身份重新登录MySQL服务器。

$ sudo mysql -u root -p

输入 root MySQL 服务器的密码,然后按 ENTER 继续。 然后,切换到 web_store 数据库。

mysql> USE web_store;

放下 sales_summary 看法。

mysql> DROP VIEW IF EXISTS  sales_summary;

输出。

Query OK, 0 rows affected (0.01 sec)

结论

在本指南中,您已在 MySQL 8 数据库服务器上创建、执行和删除视图。

注:本教程在Vultr VPS上测试通过,如需部署请前往Vultr.com