Dawn's Blogs

分享技术 记录成长

0%

MySQL基础 (15) 视图

发表于 更新于 分类于

视图概述

视图，一个或者多个数据表里的数据的逻辑显示，视图并不存储数据

  • 视图是一种虚拟表，本身是不含有数据的
    • 视图可以看作是存储起来的SELECT语句
  • 视图建立在已有表的基础上，视图依赖建立的这些表称为基表

视图操作

创建视图

  • 在CREATE VIEW语句中嵌入子查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
CREATE VIEW 视图名称 
AS 查询语句

CREATE VIEW vu_emp1
AS
SELECT employee_id, last_name, salary # 可以使用字段别名
FROM emps;

SELECT * FROM vu_emp1;
+-------------+-------------+----------+
| employee_id | last_name   | salary   |
+-------------+-------------+----------+
|         100 | King        | 24000.00 |
|         101 | Kochhar     | 17000.00 |

CREATE VIEW vu_emp_sal
AS
SELECT department_id, AVG(salary) avg_sal
FROM emps
WHERE department_id IS NOT NULL
GROUP BY department_id;

SELECT * FROM vu_emp_sal;
+---------------+--------------+
| department_id | avg_sal      |
+---------------+--------------+
|            90 | 19333.333333 |
|            60 |  5760.000000 |
|           100 |  8600.000000 |
|            30 |  4150.000000 |
|            50 |  3475.555556 |
|            80 |  8955.882353 |
|            10 |  4400.000000 |
|            20 |  9500.000000 |
|            40 |  6500.000000 |
|            70 | 10000.000000 |
|           110 | 10150.000000 |
+---------------+--------------+
  • 基于视图创建视图
1
2
3
4
5
6
7
8
9
10
11
CREATE VIEW vu_emp2
AS
SELECT employee_id, last_name
FROM vu_emp1;

SELECT * FROM vu_emp2;
+-------------+-------------+
| employee_id | last_name   |
+-------------+-------------+
|         100 | King        |
|         101 | Kochhar     |

查看视图

  • 查看数据库的表对象，视图对象
1
2
3
4
5
6
7
8
9
10
SHOW TABLES;
+--------------------+
| Tables_in_dbtest15 |
+--------------------+
| depts              |
| emps               |
| vu_emp1            |
| vu_emp2            |
| vu_emp_sal         |
+--------------------+
  • 查看视图结构
1
2
3
4
5
6
7
8
DESC vu_emp1;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| employee_id | int         | NO   |     | 0       |       |
| last_name   | varchar(25) | NO   |     | NULL    |       |
| salary      | double(8,2) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
  • 查看视图的详细定义信息
1
SHOW CREATE VIEW vu_emp1

更新视图中的数据

与表的DML操作相同

修改视图

  • CREATE OR REPLACE方式
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE VIEW vu_emp1
AS
SELECT employee_id, last_name, salary, email
FROM emps
WHERE salary > 7000;

SELECT * FROM vu_emp1;
+-------------+------------+----------+----------+
| employee_id | last_name  | salary   | email    |
+-------------+------------+----------+----------+
|         100 | King       | 24000.00 | SKING    |
|         101 | Kochhar    | 20000.00 | NKOCHHAR |
|         102 | De Haan    | 17000.00 | LDEHAAN  |
  • ALTER VIEW方式
1
2
3
4
5
ALTER VIEW vu_emp1
AS
SELECT employee_id, last_name, salary, email
FROM emps
WHERE salary > 7000;

删除视图

1
DROP VIEW IF EXISTS 视图名称

总结

  • 视图优点

    • 简化查询语句
    • 视图本身不存储数据，减少数据冗余
    • 控制用户对数据的访问权限

  • 视图不足

    • 增加维护成本，如果基表的结构变更了，需要对相关视图进行维护