1. 含义:
mysql5.1 出现的新特性, 本身是一张虚拟表, 和普通的表一样使用, 它的数据来自于表, 通过执行时动态生成.
2. 视图和表的区别:
使用方式 占用物理空间
视图 完全相同 不占用, 仅仅保存的是 sql 逻辑
表 完全相同 占用
3. 视图的好处:
,sql 语句提高重用性, 效率高
, 和表实现了分离, 提高了安全性
一. 视图的创建
语法:
CREATE VIEW 视图名
AS
查询语句;
案例: 创建一个视图 emp_v1, 要求查询部门的最高工资高于 12000 的部门的信息
- mysql> #创建一个视图 emp_v1, 要求查询部门的最高工资高于 12000 的部门的信息
- mysql> create view emp_v1
- -> as
- -> select max(salary) mx,department_id from employees group by department_id having mx>12000;
- Query OK, 0 rows affected (0.08 sec)
- mysql> select d.*,m.mx from departments d join emp_v1 m on m.department_id = d.department_id;
- +---------------+-----------------+------------+-------------+----------+
- | department_id | department_name | manager_id | location_id | mx |
- +---------------+-----------------+------------+-------------+----------+
- | 20 | Mar | 201 | 1800 | 13000.00 |
- | 80 | Sal | 145 | 2500 | 14000.00 |
- | 90 | Exe | 100 | 1700 | 24000.00 |
- +---------------+-----------------+------------+-------------+----------+
- 3 rows in set (0.15 sec)
注意: 视图我们一般不更新数据, 一般只有简单的视图才能实现数据的增删改. 大多情况视图只做查询, 我们会为视图添加只读权限, 或者说一般视图的查询语句都不是简单地查询的一个表的数据, 当涉及复杂语句时, 视图的数据我们是修改不了的, 会报错的.
二. 视图的增删改查
1, 查看视图的数据 (和表的查看一样道理)
SELECT * FROM 视图名;
- mysql> select * from emp_v1;
- +----------+---------------+
- | mx | department_id |
- +----------+---------------+
- | 13000.00 | 20 |
- | 14000.00 | 80 |
- | 24000.00 | 90 |
- +----------+---------------+
- 3 rows in set (0.00 sec)
- mysql> select * from emp_v1 where mx=13000;
- +----------+---------------+
- | mx | department_id |
- +----------+---------------+
- | 13000.00 | 20 |
- +----------+---------------+
- 1 row in set (0.29 sec)
2, 插入视图的数据
insert into 视图名 (列名...) valuse (...)
3, 修改视图的数据
update 视图名 set 字段 where 筛选条件
4, 删除视图的数据
DELETE FROM 视图名;
创建一个简单视图进行简单修改
- mysql> create view my_v1
- -> as
- -> select last_name ,email
- -> from employees;
- Query OK, 0 rows affected (0.20 sec)
- mysql># 插入数据, 添加'小白'
- mysql> insert into my_v1 values('小白','1234@qq.com');
- Query OK, 1 row affected (0.19 sec)
- mysql> select * from my_v1;
- +-------------+-------------+
- | last_name | email |
- +-------------+-------------+
- | K_ing | SKING |
- ...
- ...
- | Gietz | WGIETZ |
| 小白 | 1234@qq.com |
- +-------------+-------------+
- 108 rows in set (0.00 sec)
- # 也影响了实际表的数据, 我没骗你吧!!!
- mysql> select * from employees;
- +-------------+-------------+-------------+-------------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
- | employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate |
- +-------------+-------------+-------------+-------------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
- | 100 | Steven | K_ing | SKING | 515.123.4567 | AD_PRES | 24000.00 | NULL | NULL | 90 | 1992-04-03 00:00:00 |
- ...
- ...
| 207 | NULL | 小白 | 1234@qq.com | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
- +-------------+-------------+-------------+-------------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
- 108 rows in set (0.00 sec)
- mysql> #修改, 将'小白'名字改为'小黑'
- mysql> update my_v1 set last_name='小黑' where last_name ='小白';
- Query OK, 1 row affected (0.18 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- mysql> select * from my_v1;
- +-------------+-------------+
- | last_name | email |
- +-------------+-------------+
- | K_ing | SKING |
- ...
- ...
- | Gietz | WGIETZ |
| 小黑 | 1234@qq.com |
- +-------------+-------------+
- 108 rows in set (0.06 sec)
- mysql> select * from employees;
- +-------------+-------------+-------------+-------------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
- | employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate |
- +-------------+-------------+-------------+-------------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
- | 100 | Steven | K_ing | SKING | 515.123.4567 | AD_PRES | 24000.00 | NULL | NULL | 90 | 1992-04-03 00:00:00 |
- ...
- ...
| 207 | NULL | 小黑 | 1234@qq.com | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
- +-------------+-------------+-------------+-------------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
- 108 rows in set (0.01 sec)
- # 删除'小黑'
- mysql> delete from my_v1 where last_name = '小黑';
- Query OK, 1 row affected (0.14 sec)
- # 查看会发现没有小黑的信息了就剩 107 条记录了.
- mysql> select * from my_v1;
- +-------------+----------+
- | last_name | email |
- +-------------+----------+
- | K_ing | SKING |
- ...
- ...
- | Gietz | WGIETZ |
- +-------------+----------+
- 107 rows in set (0.00 sec)
- mysql> select * from employees;
- +-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
- | employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate |
- +-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
- | 100 | Steven | K_ing | SKING | 515.123.4567 | AD_PRES | 24000.00 | NULL | NULL | 90 | 1992-04-03 00:00:00 |
- ...
- ...
- | 206 | William | Gietz | WGIETZ | 515.123.8181 | AC_ACCOUNT | 8300.00 | NULL | 205 | 110 | 2016-03-03 00:00:00 |
- +-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
- 107 rows in set (0.00 sec)
四. 某些视图数据不能更新
包含以下关键字的 sql 语句: 分组函数, distinct,group by,having,union 或者 union all
常量视图
Select 中包含子查询
join
from 一个不能更新的视图
where 子句的子查询引用了 from 子句中的表
五. 视图逻辑的更新
# 方式一:
CREATE OR REPLACE VIEW 视图名
AS
查询语句
- CREATE OR REPLACE VIEW test_v1
- AS
- SELECT last_name FROM employees
- WHERE employee_id>100;
- # 方式二:
ALTER VIEW 视图名
AS
查询语句
- ALTER VIEW test_v1
- AS
- SELECT employee_id FROM employees;
- SELECT * FROM test_v1;
六. 视图的删除
DROP VIEW 视图名 1, 视图名 2 ... 视图名 n;(可以删除多个)
七. 视图结构的查看
1.DESC 视图名;
- mysql> desc emp_v1;
- +---------------+--------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------------+--------------+------+-----+---------+-------+
- | mx | double(10,2) | YES | | NULL | |
- | department_id | int(4) | YES | | NULL | |
- +---------------+--------------+------+-----+---------+-------+
- 2 rows in set (0.00 sec)
2.SHOW CREATE VIEW 视图名;(查询出的内容很详细)
- mysql> show create view emp_v1;
- +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
- | View | Create View | character_set_client | collation_connection |
- +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
- | emp_v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `emp_v1` AS select max(`employees`.`salary`) AS `mx`,`employees`.`department_id` AS `department_id` from `employees` group by `employees`.`department_id` having (`mx`> 12000) | utf8 | utf8_general_ci |
- +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
- 1 row in set (0.06 sec)
注: 这是本人的学习笔记及练习, 如果有错误的地方望指出一起讨论, 谢谢!
来源: http://www.jianshu.com/p/a96211c2e2c1