DBA MySQL视图相关

准备数据

视图常用于多表查询中，因此我们需要一些多表数据：

# 部门表
CREATE TABLE department(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
    dep_id INT UNSIGNED NOT NULL COMMENT "部门编号",
    name CHAR(12) NOT NULL UNIQUE COMMENT "部门名称(唯一)"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# 教师表:未做物理层面上的外键关系限制，但是是有逻辑层面的关系存在的
# 这意味着插入数据时必须小心，因为可能会产生非法数据
CREATE TABLE teacher(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
    name CHAR(12) NOT NULL COMMENT "教师名称",
    age TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT "教师年龄",
    gender ENUM("MALE", "FEMALE", "UNKNOW") NOT NULL DEFAULT "MALE" COMMENT "教师性别",
    coaching_age TINYINT(2) UNSIGNED NOT NULL DEFAULT 0 COMMENT "执教年龄",
    salary INT UNSIGNED NOT NULL DEFAULT 0 COMMENT "教师薪资(月)",
    dep_fk_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT "所属部门"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# 插入数据
INSERT INTO
    department(dep_id, name)
VALUES
    (100, "管理部"),
    (200, "教学部"),
    (300, "财务部"),
    (500, "教务部");

INSERT INTO
    teacher(name, gender, age, coaching_age, salary, dep_fk_id)
VALUES
    ("TeacherZhang", "male", 32, 8, 9000, 200),
    ("TeacherLi", "male", 34, 10, 12000, 200),
    ("TeacherYun", "male", 26, 4, 21000, 100),
    ("TeacherZhou", "female", 24, 2, 4000, 300),
    ("TeacherZhao", "female", 32, 12, 23000, 100),
    ("TeacherYang", "male", 28, 6, 3000, 300),
    ("TeacherWang", "female", 22, 1, 3200, 400);
    
# 数据说明：
# 老师表中有个部门编号为400的TeacherWang老师，没有对应的部门。
# 部门表中有个编号为500的教务部，其中没有包含老师。

视图概念

View的概念就是对经常使用的一长串查询语句所出现的虚拟表做一个别名操作，因为多表查询操作语句会很繁琐，复用时每次都要敲很长的代码所以十分的不方便。

举例：没有视图的时候，一张虚拟表当出现被多次应用的场景时在每次调用的时候我们都要大量的书写重复代码，如下我们想取出员工编号，员工姓名，员工性别，部门编号以及部门名字，那么在每次使用这张虚拟表的时候都需要做相同的重复工作。

# 多次需要取出下面虚拟表的数据时
# 每次都要敲很长的代码

SELECT
    emp.id,
    emp.name,
    emp.gender,
    mid.dep_fk_id,
    dep.name
FROM
    employee AS emp
    JOIN emp_dep AS mid ON emp.id = mid.emp_fk_id
    JOIN department AS dep ON dep.dep_id = mid.dep_fk_id;

+----+-------+--------+-----------+-----------+
| id | name  | gender | dep_fk_id | name      |
+----+-------+--------+-----------+-----------+
|  1 | Yunya | MALE   |      1002 | 开发部    |
|  2 | Jack  | MALE   |      1002 | 开发部    |
|  3 | Bella | FEMALE |      1003 | 财务部    |
|  4 | Maria | FEMALE |      1004 | 人事部    |
|  5 | Tom   | MALE   |      1004 | 人事部    |
|  6 | Jason | MALE   |      1001 | 研发部    |
|  6 | Jason | MALE   |      1002 | 开发部    |
|  7 | James | MALE   |      1001 | 研发部    |
|  7 | James | MALE   |      1002 | 开发部    |
|  7 | James | MALE   |      1003 | 财务部    |
|  8 | Lisa  | FEMALE |      1003 | 财务部    |
|  8 | Lisa  | FEMALE |      1004 | 人事部    |
+----+-------+--------+-----------+-----------+

创建视图

创建视图基本语法格式如下：

# 尽量少的去应用视图，因为视图极为依赖真实表数据，因此程序耦合度会又极大的提升
# 另外，在创建视图时不应该出现虚拟表中多列字段同名的情况，这会抛出异常1064

CREATE VIEW 视图表名称 AS
    SQL语句;

以下示例中将展示如何为上述结构的虚拟表创建出视图。

# emp.name与dep.name在虚拟表中字段都是name
# 因此需要AS取不同的字段名

CREATE VIEW view_table AS
    SELECT
        emp.id AS emp_id,
        emp.name AS emp_name,
        emp.gender AS emp_gender,
        mid.dep_fk_id AS mid_dep_fk_id,
        dep.name AS dep_name
    FROM
        employee AS emp
        JOIN emp_dep AS mid ON emp.id = mid.emp_fk_id
        JOIN department AS dep ON dep.dep_id = mid.dep_fk_id;

View其实会按照查询的语句创建一个只有表结构，但没有表数据的视图表。

每一次使用View查询时都会重复执行定义View时的SQL语句来从物理表中获得数据。

查看视图表的物理存储结构：

YunYadeMacBook-Pro:~ yunya$ ls /usr/local/application/mysql57/data/db1 |  grep "view_table.*"
view_table.frm

# 只有表结构文件.frm
# 没有表数据文件.ibd

查看视图

使用以下命令也可对视图创建情况进行查看：

M > SHOW CREATE TABLE view_table\G;
*************************** 1. row ***************************
View: view_table
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_table` AS select `emp`.`id` AS `emp_id`,`emp`.`name` AS `emp_name`,`emp`.`gender` AS `emp_gender`,`mid`.`dep_fk_id` AS `mid_dep_fk_id`,`dep`.`name` AS `dep_name` from ((`employee` `emp` join `emp_dep` `mid` on((`emp`.`id` = `mid`.`emp_fk_id`))) join `department` `dep` on((`dep`.`dep_id` = `mid`.`dep_fk_id`)))
character_set_client: utf8
collation_connection: utf8_general_ci

ERROR:
No query specified

使用视图

视图表的使用应该仅限于查询，不应该对视图表进行插入或更新操作，虽然视图表可以当作正常物理表一样进行使用。

示例演示，正确使用视图进行查询：

M > SELECT * FROM view_table;
+--------+----------+------------+---------------+-----------+
| emp_id | emp_name | emp_gender | mid_dep_fk_id | dep_name  |
+--------+----------+------------+---------------+-----------+
|      1 | Yunya    | MALE       |          1002 | 开发部    |
|      2 | Jack     | MALE       |          1002 | 开发部    |
|      3 | Bella    | FEMALE     |          1003 | 财务部    |
|      4 | Maria    | FEMALE     |          1004 | 人事部    |
|      5 | Tom      | MALE       |          1004 | 人事部    |
|      6 | Jason    | MALE       |          1001 | 研发部    |
|      6 | Jason    | MALE       |          1002 | 开发部    |
|      7 | James    | MALE       |          1001 | 研发部    |
|      7 | James    | MALE       |          1002 | 开发部    |
|      7 | James    | MALE       |          1003 | 财务部    |
|      8 | Lisa     | FEMALE     |          1003 | 财务部    |
|      8 | Lisa     | FEMALE     |          1004 | 人事部    |
+--------+----------+------------+---------------+-----------+

当物理表数据发生改变后，视图表中的数据也会发生改变：

# 修改物理表数据
M > UPDATE employee SET name = "YunYaSir" WHERE id = 1;

# 查询视图表是否产生变化
M > SELECT * FROM view_table WHERE emp_id = 1;
+--------+----------+------------+---------------+-----------+
| emp_id | emp_name | emp_gender | mid_dep_fk_id | dep_name  |
+--------+----------+------------+---------------+-----------+
|      1 | YunYaSir | MALE       |          1002 | 开发部    |
+--------+----------+------------+---------------+-----------+

视图引用

视图表引用的是物理表中的数据，虽然不建议对视图表本身进行增删改等操作，但是视图表可以将引用修改为另一张物理表。

语法格式如下：

ALTER VIEW 视图名称 AS
    SQL语句;

示例如下：

ALTER VIEW view_table AS
    SELECT * FROM employee LIMIT 3;

再次查询视图表：

M > SELECT * FROM view_table;

+----+----------+-----+--------+--------+
| id | name     | age | gender | salary |
+----+----------+-----+--------+--------+
|  1 | YunYaSir |  22 | MALE   |  16000 |
|  2 | Jack     |  25 | MALE   |  18000 |
|  3 | Bella    |  24 | FEMALE |  12000 |
+----+----------+-----+--------+--------+

删除视图

对视图表的删除语法如下：

DROP VIEW 视图表名称;

posted @ 2021-02-20 16:54 云崖君阅读(54) 评论(0) 编辑收藏举报

刷新页面返回顶部

摘星小筑