MySQL视图

841 views

视图概述

视图（View）是一种虚拟存在的表。 视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。 通俗的讲，视图就是一条SELECT语句执行后返回的结果集。 所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图相对于普通的表的优势：

  • 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
  • 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
  • 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

基础语法

在MySQL中，视图的创建、修改和删除都涉及到特定的SQL语法。下面是每种操作的详细语法和示例：

1.创建视图

创建视图使用 CREATE VIEW 语句。基本语法如下：

CREATE VIEW 视图名称 AS
SELECT 选择的列
FROM 表名
WHERE 条件;

示例：

CREATE VIEW employee_view AS
SELECT id, name, dept_id
FROM employees
WHERE dept_id = 1;

这个视图 employee_view 显示了 employees 表中所有 dept_id 为1的员工。

2.修改视图

要修改视图，你可以先删除原有视图，然后重新创建一个新的视图。MySQL不直接支持修改视图的语法，但可以使用 CREATE OR REPLACE VIEW 来更新视图。语法如下：

CREATE OR REPLACE VIEW 视图名称 AS
SELECT 选择的列
FROM 表名
WHERE 条件;

示例：

CREATE OR REPLACE VIEW employee_view AS
SELECT id, name, dept_id, salary
FROM employees
WHERE dept_id = 1;

这个语句将更新 employee_view 视图，添加了 salary 列。

3.删除视图

删除视图使用 DROP VIEW 语句。基本语法如下：

DROP VIEW 视图名称;

示例：

DROP VIEW employee_view;

这个语句将删除 employee_view 视图。

4.视图的其他操作

查看视图结构：

SHOW CREATE VIEW 视图名称;

示例：

SHOW CREATE VIEW employee_view;

列出所有视图：

SHOW FULL TABLES IN 数据库名 WHERE Table_type = 'VIEW';

示例：

SHOW FULL TABLES IN my_database WHERE Table_type = 'VIEW';

这些语法和示例涵盖了MySQL中视图的基本创建、修改和删除操作。通过这些操作，你可以管理和使用视图来简化数据操作和查询。

MySQL视图案例：

1.创建表

首先，我们需要创建两个基本的表：employees（员工） 和 departments（部门）。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

然后，我们需要插入一些数据：

INSERT INTO employees (id, name, dept_id) VALUES
(1, 'John', 1),
(2, 'Lily', 1),
(3, 'Jack', 2),
(4, 'Jacob', 2),
(5, 'Diana', 3);

INSERT INTO departments (id, name) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'Engineering');

2.创建视图

现在我们要创建一个视图employees_dept_view，它将展示每个员工和其所在部门的信息：

CREATE VIEW employees_dept_view AS
SELECT e.id, e.name, d.name AS dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

3.查询视图

创建视图后，我们可以像查询普通表一样查询它：

SELECT * FROM employees_dept_view;

这将返回所有员工的ID、姓名和部门名称。

4.更新视图

使用CREATE OR REPLACE VIEW 修改视图，将内连接查询，修改左外连接查询；

CREATE OR REPLACE VIEW  employees_dept_view  AS
SELECT e.id, e.name, d.name AS dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

5.删除视图

如果你不再需要这个视图，可以删除它：

DROP VIEW employees_dept_view;

这就是创建和使用MySQL视图的一个完整案例。视图在很多场景下非常有用，例如简化复杂的查询、提供数据的封装以及安全访问数据的手段。

视图特点：

  • MySQL 视图是一个虚拟的表，它由一个 SQL 查询定义，并且不存储实际的数据；
  • 视图的定义保存在数据字典内，创建视图所基于的表称为“基表”；
  • 删除视图不会影响到“基表”，删除或修改“基表”会影响到视图；
  • 视图的数据来自于查询执行的结果，并且可以像表一样被查询、更新和删除；
  • 视图提供了一种简化复杂查询的方法，并且可以用来限制用户对数据库中特定数据的访问；
  • 在视图中插入数据，都是基于“基表”的插入条件，会将数据添加到“基表”中的；
  • 在视图中修改、删除数据，也都是基于“基表”的插入条件，也会将数据修改、删除作用到“基表”中的。

视图的使用规则：

  • 视图必须有唯一命名；
  • 在mysql中视图的数量没有限制；
  • 创建视图必须从管理员那里获得必要的权限；
  • 视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图；
  • 在视图中可以使用order by，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的ORDER BY；
  • 视图不能索引，也不能关联触发器或默认值；
  • 视图可以和表同时使用。